MySQL基础2【678910多表查询-函数-子查询-管理表】尚硅谷

【第06章_多表查询】

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

1. 一个案例引发的多表连接

1.1 案例说明

1554974984600

从多个表中获取数据：

1554975020388

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;

1554975097631

查询结果：

+-----------+----------------------+
| last_name | department_name      |
+-----------+----------------------+
| King      | Administration       |
| King      | Marketing            |
| King      | Purchasing           |
| King      | Human Resources      |
| King      | Shipping             |
| King      | IT                   |
| King      | Public Relations     |
| King      | Sales                |
| King      | Executive            |
| King      | Finance              |
| King      | Accounting           |
| King      | Treasury             |
...
| Gietz     | IT Support           |
| Gietz     | NOC                  |
| Gietz     | IT Helpdesk          |
| Gietz     | Government Sales     |
| Gietz     | Retail Sales         |
| Gietz     | Recruiting           |
| Gietz     | Payroll              |
+-----------+----------------------+
2889 rows in set (0.01 sec)

分析错误情况：

SELECT COUNT(employee_id) FROM employees;
#输出107行

SELECT COUNT(department_id)FROM departments;
#输出27行

SELECT 107*27 FROM dual;

我们把上述多表查询中出现的问题称为：笛卡尔积的错误。

1.2 笛卡尔积（或交叉连接）的理解

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

1554975097631

SQL92中，笛卡尔积也称为交叉连接，英文是 CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

1.3 案例分析与问题解决

  • 笛卡尔积的错误会在下面条件下产生：

    • 省略多个表的连接条件（或关联条件）
    • 连接条件（或关联条件）无效
    • 所有表中的所有行互相连接

  • 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

  • 加入连接条件后，查询语法：

    SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column1 = table2.column2;  #连接条件
    • 在 WHERE子句中写入连接条件。

  • 正确写法：

    #案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

  • 在表中有相同列时，在列名之前加上表名前缀。

2. 多表查询分类讲解

分类1：等值连接 vs 非等值连接

等值连接

1554975496900

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

1554975522600

1554975526339

拓展1：多个连接条件与 AND 操作符

1554975606231

拓展2：区分重复的列名

  • 多个表中有相同列时，必须在列名之前加上表名前缀。
  • 在不同表中具有相同列名的列可以用表名加以区分。
SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

拓展3：表的别名

  • 使用别名可以简化查询。

  • 列名前使用表名前缀可以提高查询效率。

SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

阿里开发规范：

【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或 表名）进行限定。

说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。

正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;

反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在 某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出 1052 异常：Column 'name' in field list is ambiguous。

拓展4：连接多个表

1554978354431

总结：连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。

练习：查询出公司员工的 last_name,department_name, city

非等值连接

1554978442447

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

1554978477013

1554978482652

分类2：自连接 vs 非自连接

1554978514321

  • 当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

题目：查询employees表，返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name ,' works for ' 
       , manager.last_name)
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;

1554978684947

1554978690764

练习：查询出last_name为 ‘Chen’ 的员工的 manager 的信息。

分类3：内连接 vs 外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

1554978955659

  • 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

  • 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。

    如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

SQL92：使用(+)创建连接

  • 在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。

  • Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。

    #左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);

#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

  • 而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

3. SQL99语法实现多表查询

3.1 基本语法（内连接）

  • 使用JOIN...ON子句创建连接的语法结构：

    SELECT table1.column, table2.column,table3.column
FROM table1
    JOIN table2 ON table1 和 table2 的连接条件
        JOIN table3 ON table2 和 table3 的连接条件
        --  JOIN前面省略了INNER

    它的嵌套逻辑类似我们使用的 FOR 循环：

    for t1 in table1:
    for t2 in table2:
       if condition1:
           for t3 in table3:
              if condition2:
                  output t1 + t2 + t3

    SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。如果你采用 SQL92，可读性就会大打折扣。

  • 语法说明：

    • 可以使用 ON 子句指定额外的连接条件。
    • 这个连接条件是与其它条件分开的。
    • ON 子句使语句具有更高的易读性。
    • 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

3.2 内连接(INNER JOIN)的实现

  • 语法：
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

题目1：

SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);

1554979073996

1554979079395

题目2：

SELECT employee_id, city, department_name
FROM   employees e 
JOIN   departments d
ON     d.department_id = e.department_id 
JOIN   locations l
ON     d.location_id = l.location_id;

1554979110008

1554979115642

3.3 外连接(OUTER JOIN)的实现

3.3.1 左外连接(LEFT OUTER JOIN)

  • 语法：
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
  • 举例：
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

1554979200961

3.3.2 右外连接(RIGHT OUTER JOIN)

  • 语法：
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
  • 举例：
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

1554979243194

需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用 (+) 表示。

3.3.3 满外连接(FULL OUTER JOIN)

  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
  • 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

4. UNION的使用

合并查询结果
利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION操作符

1554979317187

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符

1554979343634

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

举例：查询部门编号>90或邮箱包含a的员工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

#方式2
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;

举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

5. 【7种SQL JOINS的实现】***

1554979255233

5.7.1 代码实现

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

#左下图：满外连接
# 左中图 + 右上图  A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL  #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右下图
#左中图 + 右中图  A ∪B- A∩B 或者 (A -  A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

5.7.2 语法格式小结

  • 左中图
#实现A -  A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;
  • 右中图
#实现B -  A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;
  • 左下图
#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句

union 

select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;
  • 右下图
#实现A∪B -  A∩B  或   (A -  A∩B) ∪ （B - A∩B）
#使用左外的 (A -  A∩B)  union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

union

select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

6. SQL99语法新特性

6.1 自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

在SQL92标准中：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

6.2 USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

7. 章节小结

表连接的约束条件可以有三种方式：WHERE, ON, USING

  • WHERE：适用于所有关联查询

  • ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。

  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

#关联条件
#把关联条件写在where后面
SELECT last_name,department_name 
FROM employees,departments 
WHERE employees.department_id = departments.department_id;

#把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name 
FROM employees INNER JOIN departments 
ON employees.department_id = departments.department_id;

SELECT last_name,department_name 
FROM employees CROSS JOIN departments 
ON employees.department_id = departments.department_id;

SELECT last_name,department_name  
FROM employees JOIN departments 
ON employees.department_id = departments.department_id;

#把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);

#n张表关联，需要n-1个关联条件
#查询员工姓名，基本工资，部门名称
SELECT last_name,job_title,department_name FROM employees,departments,jobs 
WHERE employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

SELECT last_name,job_title,department_name 
FROM employees INNER JOIN departments INNER JOIN jobs 
ON employees.department_id = departments.department_id 
AND employees.job_id = jobs.job_id;

注意：

我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。

来源：阿里巴巴《Java开发手册》

附录：常用的 SQL 标准有哪些

在正式开始讲连接表的种类时，我们首先需要知道 SQL 存在不同版本的标准规范，因为不同规范下的表连接操作是有区别的。

SQL 有两个主要的标准，分别是 SQL92 和 SQL99。92 和 99 代表了标准提出的时间，SQL92 就是 92 年提出的标准规范。当然除了 SQL92 和 SQL99 以外，还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、SQL:2011 和 SQL:2016 等其他的标准。

这么多标准，到底该学习哪个呢？实际上最重要的 SQL 标准就是 SQL92 和 SQL99。一般来说 SQL92 的形式更简单，但是写的 SQL 语句会比较长，可读性较差。而 SQL99 相比于 SQL92 来说，语法更加复杂，但可读性更强。我们从这两个标准发布的页数也能看出，SQL92 的标准有 500 页，而 SQL99 标准超过了 1000 页。实际上从 SQL99 之后，很少有人能掌握所有内容，因为确实太多了。就好比我们使用 Windows、Linux 和 Office 的时候，很少有人能掌握全部内容一样。我们只需要掌握一些核心的功能，满足日常工作的需求即可。

SQL92 和 SQL99 是经典的 SQL 标准，也分别叫做 SQL-2 和 SQL-3 标准。也正是在这两个标准发布之后，SQL 影响力越来越大，甚至超越了数据库领域。现如今 SQL 已经不仅仅是数据库领域的主流语言，还是信息领域中信息处理的主流语言。在图形检索、图像检索以及语音检索中都能看到 SQL 语言的使用。

【第07章_单行函数】

1. 函数的理解

1.1 什么是函数

函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率，又提高了可维护性。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地提高用户对数据库的管理效率。

1554979529525

从函数定义的角度出发，我们可以将函数分成内置函数和自定义函数。在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的，本章及下一章讲解的是 SQL 的内置函数。

1.2 不同DBMS函数的差异

我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即 DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。

1.3 MySQL的内置函数及分类

MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。

MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）。

两种SQL函数

1555433204337

单行函数

  • 操作数据对象
  • 接受参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以嵌套
  • 参数可以是一列或一个值

2. 数值函数

2.1 基本函数

函数 用法
ABS(x) 返回x的绝对值
SIGN(X) 返回X的符号。正数返回1，负数返回-1，0返回0
PI() 返回圆周率的值
CEIL(x)，CEILING(x) 返回大于或等于某个值的最小整数
FLOOR(x) 返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…) 返回列表中的最小值
GREATEST(e1,e2,e3…) 返回列表中的最大值
MOD(x,y) 返回X除以Y后的余数
RAND() 返回0~1的随机值
RAND(x) 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x) 返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y) 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y) 返回数字x截断为y位小数的结果
SQRT(x) 返回x的平方根。当X的值为负数时，返回NULL

举例：

SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;

image-20211025162304844

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

image-20211025162538958

SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;

image-20211025162730421

2.2 角度与弧度互换函数

函数 用法
RADIANS(x) 将角度转化为弧度，其中，参数x为角度值
DEGREES(x) 将弧度转化为角度，其中，参数x为弧度值 
SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;

2.3 三角函数

函数 用法
SIN(x) 返回x的正弦值，其中，参数x为弧度值
ASIN(x) 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(x) 返回x的余弦值，其中，参数x为弧度值
ACOS(x) 返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x) 返回x的正切值，其中，参数x为弧度值
ATAN(x) 返回x的反正切值，即返回正切值为x的值
ATAN2(m,n) 返回两个参数的反正切值
COT(x) 返回x的余切值，其中，X为弧度值

举例：

ATAN2(M,N)函数返回两个参数的反正切值。
与ATAN(X)函数相比，ATAN2(M,N)需要两个参数，例如有两个点point(x1,y1)和point(x2,y2)，使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1))，使用ATAN2(M,N)计算反正切值则为ATAN2(y2-y1,x2-x1)。由使用方式可以看出，当x2-x1等于0时，ATAN(X)函数会报错，而ATAN2(M,N)函数则仍然可以计算。

ATAN2(M,N)函数的使用示例如下：

SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1))
FROM DUAL;

image-20211025163846974

2.4 指数与对数

函数 用法
POW(x,y)，POWER(X,Y) 返回x的y次方
EXP(X) 返回e的X次方，其中e是一个常数，2.718281828459045
LN(X)，LOG(X) 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG10(X) 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG2(X) 返回以2为底的X的对数，当X <= 0 时，返回NULL 
mysql> SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)
    -> FROM DUAL;
+----------+------------+------------------+-------------------+-----------+---------+
| POW(2,5) | POWER(2,4) | EXP(2)           | LN(10)            | LOG10(10) | LOG2(4) |
+----------+------------+------------------+-------------------+-----------+---------+
|       32 |         16 | 7.38905609893065 | 2.302585092994046 |         1 |       2 |
+----------+------------+------------------+-------------------+-----------+---------+
1 row in set (0.00 sec)

2.5 进制间的转换

函数 用法
BIN(x) 返回x的二进制编码
HEX(x) 返回x的十六进制编码
OCT(x) 返回x的八进制编码
CONV(x,f1,f2) 返回f1进制数变成f2进制数 
mysql> SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
    -> FROM DUAL;
+---------+---------+---------+--------------+
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
+---------+---------+---------+--------------+
| 1010    | A       | 12      | 2            |
+---------+---------+---------+--------------+
1 row in set (0.00 sec)

3. 字符串函数

函数 用法
ASCII(S) 返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s) 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s) 返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,......,sn) 连接s1,s2,......,sn为一个字符串
CONCAT_WS(x, s1,s2,......,sn) 同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x
INSERT(str, idx, len, replacestr) 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b) 用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s) 将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s) 将字符串s的所有字母转成小写字母
LEFT(str,n) 返回字符串str最左边的n个字符
RIGHT(str,n) 返回字符串str最右边的n个字符
LPAD(str, len, pad) 用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad) 用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s) 去掉字符串s左侧的空格
RTRIM(s) 去掉字符串s右侧的空格
TRIM(s) 去掉字符串s开始与结尾的空格
TRIM(s1 FROM s) 去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s) 去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s) 去掉字符串s结尾处的s1
REPEAT(str, n) 返回str重复n次的结果
SPACE(n) 返回n个空格
STRCMP(s1,s2) 比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len) 返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同
LOCATE(substr,str) 返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn) 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn) 返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2) 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s) 返回s反转后的字符串
NULLIF(value1,value2) 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

注意：MySQL中，字符串的位置是从1开始的。

举例：

mysql> SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma')
    -> FROM DUAL;
+----------------------------------+-----------------------------------+
| FIELD('mm','hello','msm','amma') | FIND_IN_SET('mm','hello,mm,amma') |
+----------------------------------+-----------------------------------+
|                                0 |                                 2 |
+----------------------------------+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '');
+-------------------------+---------------------+
| NULLIF('mysql','mysql') | NULLIF('mysql', '') |
+-------------------------+---------------------+
| NULL                    | mysql               |
+-------------------------+---------------------+
1 row in set (0.00 sec)

4. 日期和时间函数

4.1 获取日期、时间

函数 用法
CURDATE() ，CURRENT_DATE() 返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME() 返回当前时间，只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() 返回当前系统日期和时间
UTC_DATE() 返回UTC（世界标准时间）日期
UTC_TIME() 返回UTC（世界标准时间）时间

举例：

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;

image-20211025193742633

4.2 日期与时间戳的转换

函数 用法
UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884
UNIX_TIMESTAMP(date) 将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp) 将UNIX时间戳的时间转换为普通格式的时间

举例：

mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
|            1576380910 |
+-----------------------+
1 row in set (0.01 sec)

mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
|                1576339200 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
|                1576380969 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11')
+---------------------------------------+
| UNIX_TIMESTAMP('2011-11-11 11:11:11') |
+---------------------------------------+
|                            1320981071 |
+---------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT FROM_UNIXTIME(1576380910);
+---------------------------+
| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 11:35:10       |
+---------------------------+
1 row in set (0.00 sec)

4.3 获取月份、星期、星期数、天数等函数

函数 用法
YEAR(date) / MONTH(date) / DAY(date) 返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time) 返回具体的时间值
MONTHNAME(date) 返回月份：January，...
DAYNAME(date) 返回星期几：MONDAY，TUESDAY.....SUNDAY
WEEKDAY(date) 返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date) 返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date) 返回一年中的第几周
DAYOFYEAR(date) 返回日期是一年中的第几天
DAYOFMONTH(date) 返回日期位于所在月份的第几天
DAYOFWEEK(date) 返回周几，注意：周日是1，周一是2，。。。周六是7

举例：

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

image-20211025213504115

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

image-20211025214818623

4.4 日期的操作函数

函数 用法
EXTRACT(type FROM date) 返回指定日期中特定的部分，type指定返回的值

EXTRACT(type FROM date)函数中type的取值与含义：

image-20211012142639469

image-20211012142746444

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

4.5 时间和秒钟转换的函数

函数 用法
TIME_TO_SEC(time) 将 time 转化为秒并返回结果值。转化的公式为：小时*3600+分钟*60+秒
SEC_TO_TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间

举例：

mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
|               78774 |
+--------------------+
1 row in set (0.00 sec)


mysql> SELECT SEC_TO_TIME(78774);
+--------------------+
| SEC_TO_TIME(78774) |
+--------------------+
| 21:52:54            |
+--------------------+
1 row in set (0.12 sec)

4.6 计算日期和时间的函数

第1组：

函数 用法
DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) 返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type) 返回与date相差INTERVAL时间间隔的日期

上述函数中type的取值：

image-20211012143203355

举例：

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;


SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;

第2组：

函数 用法
ADDTIME(time1,time2) 返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
SUBTIME(time1,time2) 返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
DATEDIFF(date1,date2) 返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2) 返回time1 - time2的时间间隔
FROM_DAYS(N) 返回从0000年1月1日起，N天以后的日期
TO_DAYS(date) 返回日期date距离0000年1月1日的天数
LAST_DAY(date) 返回date所在月份的最后一天的日期
MAKEDATE(year,n) 针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second) 将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n) 返回time加上n后的时间

举例：

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;


mysql> SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50)  |
+---------------------+
| 2019-12-15 22:17:47 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDTIME(NOW(), '1:1:1');
+-------------------------+
| ADDTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 23:18:46     |
+-------------------------+
1 row in set (0.00 sec)


mysql> SELECT SUBTIME(NOW(), '1:1:1');
+-------------------------+
| SUBTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 21:23:50     |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME(NOW(), '-1:-1:-1'); 
+----------------------------+
| SUBTIME(NOW(), '-1:-1:-1') |
+----------------------------+
| 2019-12-15 22:25:11        |
+----------------------------+
1 row in set, 1 warning (0.00 sec)


mysql> SELECT FROM_DAYS(366);
+----------------+
| FROM_DAYS(366) |
+----------------+
| 0001-01-01     |
+----------------+
1 row in set (0.00 sec)


mysql> SELECT MAKEDATE(2020,1);
+------------------+
| MAKEDATE(2020,1) |
+------------------+
| 2020-01-01       |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT MAKEDATE(2020,32);
+-------------------+
| MAKEDATE(2020,32) |
+-------------------+
| 2020-02-01        |
+-------------------+
1 row in set (0.00 sec)


mysql> SELECT MAKETIME(1,1,1);
+-----------------+
| MAKETIME(1,1,1) |
+-----------------+
| 01:01:01        |
+-----------------+
1 row in set (0.00 sec)


mysql> SELECT PERIOD_ADD(20200101010101,1);
+------------------------------+
| PERIOD_ADD(20200101010101,1) |
+------------------------------+
|               20200101010102 |
+------------------------------+
1 row in set (0.00 sec)


mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
|          737773 |
+----------------+
1 row in set (0.00 sec)

举例：查询 7 天内的新增用户数有多少？

SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

4.7 日期的格式化与解析

函数 用法
DATE_FORMAT(date,fmt) 按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt) 按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type) 返回日期字符串的显示格式
STR_TO_DATE(str, fmt) 按照字符串fmt对str进行解析，解析为一个日期

上述非GET_FORMAT函数中fmt参数常用的格式符：

格式符 说明 格式符 说明
%Y 4位数字表示年份 %y 表示两位数字表示年份
%M 月名表示月份（January,....） %m 两位数字表示月份（01,02,03。。。）
%b 缩写的月名（Jan.，Feb.，....） %c 数字表示月份（1,2,3,...）
%D 英文后缀表示月中的天数（1st,2nd,3rd,...） %d 两位数字表示月中的天数(01,02...)
%e 数字形式表示月中的天数（1,2,3,4,5.....）
%H 两位数字表示小数，24小时制（01,02..） %h和%I 两位数字表示小时，12小时制（01,02..）
%k 数字形式的小时，24小时制(1,2,3) %l 数字形式表示小时，12小时制（1,2,3,4....）
%i 两位数字表示分钟（00,01,02） %S和%s 两位数字表示秒(00,01,02...)
%W 一周中的星期名称（Sunday...） %a 一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w 以数字表示周中的天数(0=Sunday,1=Monday....)
%j 以3位数字表示年中的天数(001,002...) %U 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T 24小时制 %r 12小时制
%p AM或PM %% 表示%

GET_FORMAT函数中date_type和format_type参数取值如下：

image-20211012145231321

举例：

mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:57:34                        |
+--------------------------------+
1 row in set (0.00 sec)


SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
FROM DUAL;

SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
FROM DUAL;

SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;


mysql> SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y                |
+-------------------------+
1 row in set (0.00 sec)

SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
FROM DUAL;


mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d'); 
+-----------------------------------------------+
| STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2020-01-01                                    |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

5. 流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

函数 用法
IF(value,value1,value2) 如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2) 如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END 相当于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END 相当于Java的switch...case... 
SELECT IF(1 > 0,'正确','错误')    
->正确


SELECT IFNULL(null,'Hello Word')
->Hello Word


SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0


SELECT CASE 1 
　　WHEN 1 THEN '我是1'
　　WHEN 2 THEN '我是2'
ELSE '你是谁'


SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
				  WHEN salary>=10000 THEN '潜力股'  
				  WHEN salary>=8000 THEN '屌丝' 
				  ELSE '草根' END  "描述"
FROM employees; 


SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款' 
								   WHEN 2 THEN '已付款' 
								   WHEN 3 THEN '已发货'  
								   WHEN 4 THEN '确认收货'  
								   ELSE '无效订单' END 
FROM t_order;


mysql> SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
+---------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END |
+---------------------------------------------------------------------+
| yes                                                                  |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;  
+--------------------------------------------------------------------+
| CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END |
+--------------------------------------------------------------------+
| unknown                                                             |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+------------------------------------------------+
| CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+------------------------------------------------+
|                                               1 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+-------------------------------------------------+
| CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+-------------------------------------------------+
|                                               -1 |
+-------------------------------------------------+
1 row in set (0.00 sec)


SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
FROM employees;


SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       			   ELSE      salary END     "REVISED_SALARY"
FROM   employees;

1554980865631

练习：查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数。

6. 加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。

函数 用法
PASSWORD(str) 返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密
MD5(str) 返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL
SHA(str) 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。
ENCODE(value,password_seed) 返回使用password_seed作为加密密码加密value
DECODE(value,password_seed) 返回使用password_seed作为加密密码解密value

可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。

举例：

mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
+-------------------------------------------+----------------+
| PASSWORD('mysql')                         | PASSWORD(NULL) |
+-------------------------------------------+----------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                |
+-------------------------------------------+----------------+
1 row in set, 1 warning (0.00 sec)


SELECT md5('123')
->202cb962ac59075b964b07152d234b70


SELECT SHA('Tom123')
->c7c506980abc31cc390a2438c90861d0f1216d50


mysql> SELECT ENCODE('mysql', 'mysql');
+--------------------------+
| ENCODE('mysql', 'mysql') |
+--------------------------+
| íg　¼　ìÉ                  |
+--------------------------+
1 row in set, 1 warning (0.01 sec)


mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
+-----------------------------------------+
| DECODE(ENCODE('mysql','mysql'),'mysql') |
+-----------------------------------------+
| mysql                                   |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)

7. MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

函数 用法
VERSION() 返回当前MySQL的版本号
CONNECTION_ID() 返回当前MySQL服务器的连接数
DATABASE()，SCHEMA() 返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER() 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value) 返回字符串value自变量的字符集
COLLATION(value) 返回字符串value的比较规则

举例：

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)


mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+



mysql> SELECT CHARSET('ABC');
+----------------+
| CHARSET('ABC') |
+----------------+
| utf8mb4        |
+----------------+
1 row in set (0.00 sec)


mysql> SELECT COLLATION('ABC');
+--------------------+
| COLLATION('ABC')   |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
1 row in set (0.00 sec)

8. 其他函数

MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。

函数 用法
FORMAT(value,n) 返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位
CONV(value,from,to) 将value的值进行不同进制之间的转换
INET_ATON(ipvalue) 将以点分隔的IP地址转化为一个数字
INET_NTOA(value) 将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr) 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code) 将value所使用的字符编码修改为char_code

举例：

# 如果n的值小于或者等于0，则只保留整数部分
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2); 
+--------------------+--------------------+---------------------+
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
+--------------------+--------------------+---------------------+
| 123.12             | 124                | 123                 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)


mysql> SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
+-----------------+------------------+-------------------+
| CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) |
+-----------------+------------------+-------------------+
| 10000           | 22B8             | NULL              |
+-----------------+------------------+-------------------+
1 row in set (0.00 sec)


mysql> SELECT INET_ATON('192.168.1.100');
+----------------------------+
| INET_ATON('192.168.1.100') |
+----------------------------+
|                 3232235876 |
+----------------------------+
1 row in set (0.00 sec)

# 以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。


mysql> SELECT INET_NTOA(3232235876);
+-----------------------+
| INET_NTOA(3232235876) |
+-----------------------+
| 192.168.1.100         |
+-----------------------+
1 row in set (0.00 sec)


mysql> SELECT BENCHMARK(1, MD5('mysql'));
+----------------------------+
| BENCHMARK(1, MD5('mysql')) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT BENCHMARK(1000000, MD5('mysql')); 
+----------------------------------+
| BENCHMARK(1000000, MD5('mysql')) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (0.20 sec)


mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
+------------------+----------------------------------------+
| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |
+------------------+----------------------------------------+
| utf8mb4          | utf8                                   |
+------------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

【第08章_聚合函数】

我们上一章讲到了 SQL 单行函数。实际上 SQL 函数还有一类，叫做聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

1. 聚合函数介绍

  • 什么是聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

1554980924940

  • 聚合函数类型
    • AVG()
    • SUM()
    • MAX()
    • MIN()
    • **COUNT() **
  • 聚合函数语法

1554981029920

  • 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

1.1 AVG和SUM函数

可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

1554981279723

1.2 MIN和MAX函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;

1554981253194

1.3 COUNT函数

  • COUNT(*)返回表中记录总数，适用于任意数据类型。
SELECT COUNT(*)
FROM	  employees
WHERE  department_id = 50;

1554981241299

  • COUNT(expr) 返回expr不为空的记录总数。
SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;

1554981328678

  • 问题：用count(*)，count(1)，count(列名)谁好呢?

    其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

    Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。

  • 问题：能不能使用count(列名)替换count(*)?

    不要使用 count(列名)来替代 count(*)，count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

    说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

2. GROUP BY

2.1 基本使用

1554981374920

可以使用GROUP BY子句将表中的数据分成若干组

SELECT column, group_function(column)
FROM table
[WHERE	condition]
[GROUP BY	group_by_expression]
[ORDER BY	column];

明确：WHERE一定放在FROM后面

在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;

1554981539408

1554981544191

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;

1554981574152

2.2 使用多个列分组

1554981607442

SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id ;

1554981624864

1554981629733

2.3 GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意：

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

3. HAVING

3.1 基本使用

1554981656798

过滤分组：HAVING子句

  1. 行已经被分组。
  2. 使用了聚合函数。
  3. 满足HAVING 子句中条件的分组将被显示。
  4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

1554981808091

SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000 ;

1554981824564

  • 非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。如下：
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000
GROUP BY department_id;

1554981724375

3.2 WHERE和HAVING的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。

区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

小结如下：

优点 缺点
WHERE 先筛选数据再关联，执行效率高 不能使用分组中的计算函数进行筛选
HAVING 可以使用分组中的计算函数 在最后的结果集中进行筛选，执行效率较低

开发中的选择：

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

4. SELECT的执行过程

4.1 查询的结构

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：
SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

4.2 SELECT执行顺序

你需要记住 SELECT 查询时的两个顺序：

1. 关键字的顺序是不能颠倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

1566872301088

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

4.3 SQL 的执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

  1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
  2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
  3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1，就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2。

然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4。

当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段。

首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2。

当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段，得到虚拟表 vt6。

最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表 vt7。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

【第09章_子查询】

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

1. 需求分析与问题解决

1.1 实际问题

![image-1666666](images/1554991034688.png" alt="1554991034688" style="zoom:80%;" />

现有解决方式：

#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
		);

1554991316599

1.2 子查询的基本使用

  • 子查询的基本语法结构：

1554991054388

  • 子查询（内查询）在主查询之前一次执行完成。
  • 子查询的结果被主查询（外查询）使用 。
  • 注意事项
    • 子查询要包含在括号内
    • 将子查询放在比较条件的右侧
    • 单行操作符对应单行子查询，多行操作符对应多行子查询

1.3 子查询的分类

分类方式1：

我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。

  • 单行子查询

    1554991538719

  • 多行子查询

1554991555669

分类方式2：

我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

2. 单行子查询

2.1 单行比较操作符

操作符 含义
= equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
<> not equal to

2.2 代码示例

题目：查询工资大于149号员工工资的员工的信息

image-20210914232952626

image-20210914232935062

题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT last_name, job_id, salary
FROM   employees
WHERE  job_id =  
                (SELECT job_id
                 FROM   employees
                 WHERE  employee_id = 141)
AND    salary >
                (SELECT salary
                 FROM   employees
                 WHERE  employee_id = 143);

1554991892770

题目：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name, job_id, salary
FROM   employees
WHERE  salary = 
                (SELECT MIN(salary)
                 FROM   employees);

1554991935186

题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id

实现方式1：不成对比较

SELECT  employee_id, manager_id, department_id
FROM    employees
WHERE   manager_id IN
		  (SELECT  manager_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND     department_id IN 
		  (SELECT  department_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND	employee_id NOT IN(174,141);

实现方式2：成对比较

SELECT	employee_id, manager_id, department_id
FROM	employees
WHERE  (manager_id, department_id) IN
                      (SELECT manager_id, department_id
                       FROM   employees
                       WHERE  employee_id IN (141,174))
AND	employee_id NOT IN (141,174);

2.3 HAVING 中的子查询

  • 首先执行子查询。
  • 向主查询中的HAVING 子句返回结果。

题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);

2.4 CASE中的子查询

在CASE表达式中使用单列子查询：

题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。

SELECT employee_id, last_name,
       (CASE department_id
        WHEN
             (SELECT department_id FROM departments
	      WHERE location_id = 1800)           
        THEN 'Canada' ELSE 'USA' END) location
FROM   employees;

2.5 子查询中的空值问题

SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');

1554992067381

子查询不返回任何行

2.5 非法使用子查询

SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);

1554992135819

多行子查询使用单行比较符

3. 多行子查询

  • 也称为集合比较子查询
  • 内查询返回多行
  • 使用多行比较操作符

3.1 多行比较操作符

操作符 含义
IN 等于列表中的任意一个
ANY 需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL 需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME 实际上是ANY的别名，作用相同，一般常使用ANY

体会 ANY 和 ALL 的区别

3.2 代码示例

题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

1554992658876

1554992664594

1554992668429

题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

1554992753654

1554992759467

题目：查询平均工资最低的部门id

#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				) dept_avg_sal
			)


#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
)

3.3 空值问题

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
			SELECT manager_id
			FROM employees
			);

image-20211027195906773

4. 相关子查询

4.1 相关子查询执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

image-1666666

image-1666666

说明：子查询中使用主查询中的列

4.2 代码示例

题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

方式一：相关子查询

1554992986225

方式二：在 FROM 中使用子查询

SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名，
把它当成一张“临时的虚拟的表”来使用。

在ORDER BY 中使用子查询：

题目：查询员工的id,salary,按照department_name 排序

SELECT employee_id,salary
FROM employees e
ORDER BY (
	  SELECT department_name
	  FROM departments d
	  WHERE e.`department_id` = d.`department_id`
	);

题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id

SELECT e.employee_id, last_name,e.job_id
FROM   employees e 
WHERE  2 <= (SELECT COUNT(*)
             FROM   job_history 
             WHERE  employee_id = e.employee_id);

4.3 EXISTS 与 NOT EXISTS关键字

  • 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
  • 如果在子查询中不存在满足条件的行：
    • 条件返回 FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行：
    • 不在子查询中继续查找
    • 条件返回 TRUE
  • NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

题目：查询公司管理者的employee_id，last_name，job_id，department_id信息

方式一：

SELECT employee_id, last_name, job_id, department_id
FROM   employees e1
WHERE  EXISTS ( SELECT *
                 FROM   employees e2
                 WHERE  e2.manager_id = 
                        e1.employee_id);

方式二：自连接

SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM   employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

方式三：

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
		     SELECT DISTINCT manager_id
		     FROM employees
		     
		     );

题目：查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                  FROM   employees
                  WHERE  department_id = d.department_id);

1554993169269

4.4 相关更新

UPDATE table1 alias1
SET    column = (SELECT expression
                 FROM   table2 alias2
                 WHERE  alias1.column = alias2.column);

使用相关子查询依据一个表中的数据更新另一个表的数据。

题目：在employees中增加一个department_name字段，数据为员工对应的部门名称

# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));

# 2）
UPDATE employees e
SET department_name =  (SELECT department_name 
	                       FROM   departments d
	                       WHERE  e.department_id = d.department_id);

4.4 相关删除

 DELETE FROM table1 alias1
 WHERE column operator (SELECT expression
                        FROM   table2 alias2
                        WHERE  alias1.column = alias2.column);

使用相关子查询依据一个表中的数据删除另一个表的数据。

题目：删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees e
WHERE employee_id in  
           (SELECT employee_id
            FROM   emp_history 
            WHERE  employee_id = e.employee_id);

5. 抛一个思考题

问题：谁的工资比Abel的高？

解答：

#方式1：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`


#方式2：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
		);

问题：以上两种方式有好坏之分吗？

解答：自连接方式好！

题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

【第10章_创建和管理表】

1. 基础知识

1.1 一条数据存储的过程

存储数据是处理数据的第一步。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。

那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？ 在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

image-20211007155810920

我们要先创建一个数据库，而不是直接创建数据表呢？

因为从系统架构的层次上看，MySQL 数据库系统从大到小依次是数据库服务器、数据库、数据表、数据表的行与列。

MySQL 数据库服务器之前已经安装。所以，我们就从创建数据库开始。

1.2 标识符命名规则

  • 数据库名、表名不得超过30个字符，变量名限制为29个
  • 必须只能包含 A–Z, a–z, 0–9, _共63个字符
  • 数据库名、表名、字段名等对象名中间不要包含空格
  • 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
  • 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
  • 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

1.3 MySQL中的数据类型

类型 类型举例
整数类型 TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型 FLOAT、DOUBLE
定点数类型 DECIMAL
位类型 BIT
日期时间类型 YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型 ENUM
集合类型 SET
二进制字符串类型 BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型 JSON对象、JSON数组
空间数据类型 单值：GEOMETRY、POINT、LINESTRING、POLYGON；
集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

其中，常用的几类类型介绍如下：

数据类型 描述
INT 从-231到231-1的整型数据。存储大小为 4个字节
CHAR(size) 定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size) 可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D) 单精度，占用4个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D) 双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D) 高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。
DATE 日期型数据，格式'YYYY-MM-DD'
BLOB 二进制形式的长文本数据，最大可达4G
TEXT 长文本数据，最大可达4G

2. 创建和管理数据库

2.1 创建数据库

  • 方式1：创建数据库
CREATE DATABASE 数据库名;
  • 方式2：创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
  • 方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）
CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

2.2 使用数据库

  • 查看当前所有的数据库
SHOW DATABASES; #有一个S，代表多个数据库
  • 查看当前正在使用的数据库
SELECT DATABASE();  #使用的一个 mysql 中的全局函数
  • 查看指定库下所有的表
SHOW TABLES FROM 数据库名;
  • 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G
  • 使用/切换数据库
USE 数据库名;

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

2.3 修改数据库

  • 更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集;  #比如：gbk、utf8等

2.4 删除数据库

  • 方式1：删除指定的数据库
DROP DATABASE 数据库名;
  • 方式2：删除指定的数据库（推荐）
DROP DATABASE IF EXISTS 数据库名;

3. 创建表

3.1 创建方式1

  • 必须具备：
    • CREATE TABLE权限
    • 存储空间
  • 语法格式：
CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

  • 必须指定：
    • 表名
    • 列名(或字段名)，数据类型，长度
  • 可选指定：
    • 约束条件
    • 默认值
  • 创建表举例1：
-- 创建表
CREATE TABLE emp (
  -- int类型
  emp_id INT,
  -- 最多保存20个中英文字符
  emp_name VARCHAR(20),
  -- 总位数不超过15位
  salary DOUBLE,
  -- 日期类型
  birthday DATE
);

DESC emp;

image-20211016160557995

MySQL在执行建表语句时，将id字段的类型设置为int(11)，这里的11实际上是int类型指定的显示宽度，默认的显示宽度为11。也可以在创建数据表的时候指定数据的显示宽度。

  • 创建表举例2：
CREATE TABLE dept(
    -- int类型，自增
	deptno INT(2) AUTO_INCREMENT,
	dname VARCHAR(14),
	loc VARCHAR(13),
    -- 主键
    PRIMARY KEY (deptno)
);

DESCRIBE dept;

image-20211016160643445

在MySQL 8.x版本中，不再推荐为INT类型指定显示长度，并在未来的版本中可能去掉这样的语法。

3.2 创建方式2

  • 使用 AS subquery 选项，将创建表和插入数据结合起来

    1554997882872

  • 指定的列和子查询中的列要一一对应

  • 通过列名和默认值定义列

CREATE TABLE emp1 AS SELECT * FROM employees;

CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表

CREATE TABLE dept80
AS 
SELECT  employee_id, last_name, salary*12 ANNSAL, hire_date
FROM    employees
WHERE   department_id = 80;


DESCRIBE dept80;

1554997998148

1554998004494

3.3 查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用DESCRIBE/DESC语句查看数据表结构，也支持使用SHOW CREATE TABLE语句查看数据表结构。

语法格式如下：

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

4. 修改表

修改表指的是修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句可以实现：

  • 向已有的表中添加列

  • 修改现有表中的列

  • 删除现有表中的列

  • 重命名现有表中的列

4.1 追加一个列

语法格式如下：

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

举例：

ALTER TABLE dept80 
ADD job_id varchar(15);

1554998139815

4.2 修改一个列

  • 可以修改列的数据类型，长度、默认值和位置

  • 修改字段数据类型、长度、默认值、位置的语法格式如下：

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
  • 举例：
ALTER TABLE	dept80
MODIFY last_name VARCHAR(30);

ALTER TABLE	dept80
MODIFY salary double(9,2) default 1000;
  • 对默认值的修改只影响今后对表的修改
  • 此外，还可以通过此种方式修改列的约束。这里暂先不讲。

4.3 重命名一个列

使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

举例：

ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15);

4.4 删除一个列

删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP 【COLUMN】字段名

举例：

ALTER TABLE  dept80
DROP COLUMN  job_id;

5. 重命名表

  • 方式一：使用RENAME
RENAME TABLE emp
TO myemp;
  • 方式二：
ALTER table dept
RENAME [TO] detail_dept;  -- [TO]可以省略
  • 必须是对象的拥有者

6. 删除表

  • 在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。

  • 数据和结构都被删除

  • 所有正在运行的相关事务被提交

  • 所有相关索引被删除

  • 语法格式：

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

IF EXISTS的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。

  • 举例：
DROP TABLE dept80;
  • DROP TABLE 语句不能回滚

7. 清空表

  • TRUNCATE TABLE语句：

    • 删除表中所有的数据
    • 释放表的存储空间

  • 举例：

TRUNCATE TABLE detail_dept;

  • TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

  • 对比：

SET autocommit = FALSE;
  
DELETE FROM emp2; 
#TRUNCATE TABLE emp2;
  
SELECT * FROM emp2;
  
ROLLBACK;
  
SELECT * FROM emp2;

阿里开发规范：

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

8. 内容拓展

拓展1：阿里巴巴《Java开发手册》之MySQL字段命名

  • 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

    • 正例：aliyun_admin，rdc_config，level3_name
    • 反例：AliyunAdmin，rdcConfig，level_3_name

  • 【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。

  • 【强制】表必备三字段：id, gmt_create, gmt_modified。

    • 说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新

  • 【推荐】表的命名最好是遵循 “业务名称_表的作用”。

    • 正例：alipay_task 、 force_project、 trade_config

  • 【推荐】库名与应用名称尽量一致。

  • 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

    • 正例：无符号值可以避免误存负数，且扩大了表示范围。

    image-20211024012735469

拓展2：如何理解清空表、删除表等操作需谨慎？！

表删除操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信息提示，因此执行删除操时应当慎重。在删除表前，最好对表中的数据进行备份，这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。

同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的备份，因为数据库的改变是无法撤销的，如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

拓展3：MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即DDL操作要么成功要么回滚。DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表，结果如下：

CREATE DATABASE mytest;

USE mytest;

CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);

SHOW TABLES;

（1）在MySQL 5.7版本中，测试步骤如下：
删除数据表book1和数据表book2，结果如下：

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

再次查询数据库中的数据表名称，结果如下：

mysql> SHOW TABLES;
Empty set (0.00 sec)

从结果可以看出，虽然删除操作时报错了，但是仍然删除了数据表book1。

（2）在MySQL 8.0版本中，测试步骤如下：
删除数据表book1和数据表book2，结果如下：

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

再次查询数据库中的数据表名称，结果如下：

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1            |
+------------------+
1 row in set (0.00 sec)

从结果可以看出，数据表book1并没有被删除。

【第11章_数据处理之增删改】

1. 插入数据

1.1 实际问题

1555425366064

解决方式：使用 INSERT 语句向表中插入数据。

1.2 方式1：VALUES的方式添加

使用这种语法一次只能向表中插入一条数据。

情况1：为表的所有字段按默认顺序插入数据

INSERT INTO 表名
VALUES (value1,value2,....);

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

举例：

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);

INSERT INTO	departments
VALUES		(100, 'Finance', NULL, NULL);

情况2：为表的指定字段插入数据

INSERT INTO 表名(column1 [, column2, …, columnn]) 
VALUES (value1 [,value2, …, valuen]);

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,....valuen需要与column1,...columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

举例：

INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');

情况3：同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：

INSERT INTO table_name 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

或者

INSERT INTO table_name(column1 [, column2, …, columnn]) 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

举例：

mysql> INSERT INTO emp(emp_id,emp_name)
    -> VALUES (1001,'shkstart'),
    -> (1002,'atguigu'),
    -> (1003,'Tom');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：
●　Records：表明插入的记录条数。
●　Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
●　Warnings：表明有问题的数据值，例如发生数据类型转换。

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率更高。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

小结：

  • VALUES也可以写成VALUE，但是VALUES是标准写法。

  • 字符和日期型数据应包含在单引号中。

1.3 方式2：将查询结果插入到表中

INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

基本语法格式如下：

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]
  • 在 INSERT 语句中加入子查询。
  • 不必书写 VALUES 子句。
  • 子查询中的值列表应与 INSERT 子句中的列名对应。

举例：

INSERT INTO emp2 
SELECT * 
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM   employees
WHERE  job_id LIKE '%REP%';

2. 更新数据

1555425824246

  • 使用 UPDATE 语句更新数据。语法如下：
UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

  • 可以一次更新多条数据。

  • 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

  • 使用 WHERE 子句指定需要更新的数据。
UPDATE employees
SET    department_id = 70
WHERE  employee_id = 113;
  • 如果省略 WHERE 子句，则表中的所有数据都将被更新。
UPDATE 	copy_emp
SET    	department_id = 110;
  • 更新中的数据完整性错误
UPDATE employees
SET    department_id = 55
WHERE  department_id = 110;

1555426069578

说明：不存在 55 号部门

3. 删除数据

1555426124751

  • 使用 DELETE 语句从表中删除数据

1555426162264

DELETE FROM table_name [WHERE <condition>];

table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。

  • 使用 WHERE 子句删除指定的记录。
DELETE FROM departments
WHERE  department_name = 'Finance';
  • 如果省略 WHERE 子句，则表中的全部数据将被删除
DELETE FROM  copy_emp;
  • 删除中的数据完整性错误
DELETE FROM departments
WHERE       department_id = 60;

1555426258516

说明：You cannot delete a row that contains a primary key that is used as a foreign key in another table.

4. MySQL8新特性：计算列

什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。

举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。
首先创建测试表tb1，语句如下：

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

插入演示数据，语句如下：

INSERT INTO tb1(a,b) VALUES (100,200);

查询数据表tb1中的数据，结果如下：

mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| NULL |  100 |  200 |  300 |
+------+------+------+------+
1 row in set (0.00 sec)

更新数据中的数据，语句如下：

mysql> UPDATE tb1 SET a = 500;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

5. 综合案例

# 1、创建数据库test01_library

# 2、创建表 books，表结构如下：
字段名 字段说明 数据类型
id 书编号 INT
name 书名 VARCHAR(50)
authors 作者 VARCHAR(100)
price 价格 FLOAT
pubdate 出版日期 YEAR
note 说明 VARCHAR(100)
num 库存 INT 
# 3、向books表中插入记录

# 1）不指定字段名称，插入第一条记录
# 2）指定所有字段名称，插入第二记录
# 3）同时插入多条记录（剩下的所有记录）
id name authors price pubdate note num
1 Tal of AAA Dickes 23 1995 novel 11
2 EmmaT Jane lura 35 1993 joke 22
3 Story of Jane Jane Tim 40 2001 novel 0
4 Lovey Day George Byron 20 2005 novel 30
5 Old land Honore Blade 30 2010 law 0
6 The Battle Upton Sara 30 1999 medicine 40
7 Rose Hood Richard haggard 28 2008 cartoon 28 
# 4、将小说类型(novel)的书的价格都增加5。

# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。

# 6、删除库存为0的记录。

# 7、统计书名中包含a字母的书

# 8、统计书名中包含a字母的书的数量和库存总量

# 9、找出“novel”类型的书，按照价格降序排列

# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列

# 11、按照note分类统计书的数量

# 12、按照note分类统计书的库存量，显示库存量超过30本的

# 13、查询所有图书，每页显示5本，显示第二页

# 14、按照note分类统计书的库存量，显示库存量最多的

# 15、查询书名达到10个字符的书，不包括里面的空格

# 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话

# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货

# 18、统计每一种note的库存量，并合计总量

# 19、统计每一种note的数量，并合计总量

# 20、统计库存量前三名的图书

# 21、找出最早出版的一本书

# 22、找出novel中价格最高的一本书

# 23、找出书名中字数最多的一本书，不含空格

答案：

#1、创建数据库test01_library
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';

#指定使用哪个数据库
USE test01_library;

#2、创建表 books
CREATE TABLE books(
	id INT,
	name VARCHAR(50),
	`authors` VARCHAR(100) ,
	price FLOAT,
	pubdate YEAR ,
	note VARCHAR(100),
	num INT
);

#3、向books表中插入记录
# 1）不指定字段名称，插入第一条记录
INSERT INTO books 
VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);
# 2）指定所有字段名称，插入第二记录
INSERT INTO books (id,name,`authors`,price,pubdate,note,num)
VALUES(2,'EmmaT','Jane lura',35,1993,'Joke',22);
# 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books (id,name,`authors`,price,pubdate,note,num) VALUES
(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'Law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);

# 4、将小说类型(novel)的书的价格都增加5。
UPDATE books SET price=price+5 WHERE note = 'novel';

# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE books SET price=40,note='drama' WHERE name='EmmaT';

# 6、删除库存为0的记录。
DELETE FROM books WHERE num=0;

# 7、统计书名中包含a字母的书
SELECT * FROM books WHERE name LIKE '%a%';

# 8、统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*),SUM(num) FROM books WHERE name LIKE '%a%';

# 9、找出“novel”类型的书，按照价格降序排列
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC;

# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT * FROM books ORDER BY num DESC,note ASC;

# 11、按照note分类统计书的数量
SELECT note,COUNT(*) FROM books GROUP BY note;

# 12、按照note分类统计书的库存量，显示库存量超过30本的
SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)>30;

# 13、查询所有图书，每页显示5本，显示第二页
SELECT * FROM books LIMIT 5,5;

# 14、按照note分类统计书的库存量，显示库存量最多的
SELECT note,SUM(num) sum_num FROM books GROUP BY note ORDER BY sum_num DESC LIMIT 0,1;

# 15、查询书名达到10个字符的书，不包括里面的空格
SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(name,' ',''))>=10;

/*
16、查询书名和类型，
 其中note值为 novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
*/
SELECT name AS "书名" ,note, CASE note 
 WHEN 'novel' THEN '小说'
 WHEN 'law' THEN '法律'
 WHEN 'medicine' THEN '医药'
 WHEN 'cartoon' THEN '卡通'
 WHEN 'joke' THEN '笑话'
 END AS "类型"
FROM books;


# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
SELECT name,num,CASE 
  WHEN num>30 THEN '滞销'
  WHEN num>0 AND num<10 THEN '畅销'
  WHEN num=0 THEN '无货'
  ELSE '正常'
  END AS "库存状态"
FROM books;

# 18、统计每一种note的库存量，并合计总量
SELECT IFNULL(note,'合计总库存量') AS note,SUM(num) FROM books GROUP BY note WITH ROLLUP;

# 19、统计每一种note的数量，并合计总量
SELECT IFNULL(note,'合计总数') AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP;

# 20、统计库存量前三名的图书
SELECT * FROM books ORDER BY num DESC LIMIT 0,3;

# 21、找出最早出版的一本书
SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1;

# 22、找出novel中价格最高的一本书
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC LIMIT 0,1;

# 23、找出书名中字数最多的一本书，不含空格
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC LIMIT 0,1;
posted @ 2022-10-22 18:39  起跑线小言  阅读(116)  评论(0)  编辑  收藏  举报