MySQL（二）

多重循环模型

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

这是一个不相关的多行子查询。我感觉就是外查询的循环和内查询的循环是分开的

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

这是一个相关单行子查询，我认为这是一个典型的多重循环结构，内外查询的循环是嵌套的

八. 聚合函数（多行函数）

mysql不支持聚合函数的嵌套，oracle支持

它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值

前文的单行函数，从声明上看起来传入的是一个字段，好像是传入了一组数组，但依据我猜测的底层实现，在底层，传入的就是对象的一个属性

SELECT id, ABS(disappear)
FROM employees;

for 装employees类对象的集合emp
	print(emp[i].id, ABS(emp[i].disappear));

对于聚合函数：

SELECT AVG(salary)
FROM employees;

虽然看起来和单行函数的使用方法差不多，都是向函数中传入了一个字段。但在底层，相当于是在多重循环中，收集每个对象的某一个属性值，通过处理，最终返回一个值
比如上例，最终就返回员工的平均工资，他收集了每个员工的工资这一属性值

常见的聚合函数

AVG和SUM函数

SELECT SUM(last_name)
FROM employees;

如果传入字段的数据类型不是数值类型，不会报错，返回0

MAX和MIN

求所有对象某属性值的最大值或者最小值

对于字符串类型的字段，按照字典序排序

适用于数值类型，字符串类型，日期时间类型

COUNT

用于统计指定字段在查询结构中出现的个数

SELECT COUNT(last_name),COUNT(1)
FROM employees
WHERE salary > 5000;

其实每次看到这种SELECT 1后面跟的不是字段就觉得听反直觉的，但是呢这种写法是有具体的需求的。比如说想给输出增加一列完全相同的值放在第一列的位置就可以这么写

对应到我猜测的多重循环模型，可能判断了一下是字段还是数值，是数值的话，我猜就不应该是每个对象去调用，而是直接输出了

计算指定字段的个数时，不包含为null的情况，只统计非空的情况：
比如表中有107条数据，x字段有35个数据不为null，那么count(x)的结果为35

对于null空值的处理：

（1）avg，sum，count会自动过滤空值
（2）sum过滤空值得出的结果和我们的目标始终是一致的
（3）count过滤空值可能会导致结果与预期不一致。比如说求平均奖金率，有人有奖金，有人没有。count只计算有奖金的人，但是求平均奖金率需求统计员工的总数

COUNT(IFNULL(commission_pct,0))

使用count的时候需要根据需求判断是否单独处理null
（4）avg的底层应该是调用了两个函数，一个求和，一个求数量。在求均值的时候，可以将为null的数据改为数值类型的0，不影响最后的结果，同时满足需求

AVG(IFNULL(commission_pct,0))

//可以理解为调用了两个函数,两个函数同参
SUM(IFNULL(commission_pct,0)) / COUNT(IFNULL(commission_pct,0))

count(*),count(1),count(具体字段)

哪个效率更高？？这涉及到索引优化，存储引擎（引擎是一个程序或一套系统的支持部分）

实际上我们一直以来在select关键字后边写的字段就是索引

如果使用的是MyISAM存储引擎，则三者效率相同，都是o(1)
如果使用的是InnoDB存储引擎，则三者效率:cOUNT(*) = COUNT (1)> COUNT(字段)

group by的使用

使用场景：要对某一个字段的数据进行分组，就好比是删除数组中的重复元素

order by 具体字段：根据某个字段排序

group：分组

需求：求出员工表中各个部门的平均工资，最高工资

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

值得关注的是department_id这个字段的数据自动去重了

之前讲过字段去重，使用distinct关键字，加在字段前面

SELECT DISTINCT department_id FROM employees;

猜想

我想底层应该是加了一张哈希表，以实现在多重循环的过程中将department_id的数据提取出来并统计

以department_id的数据为key：
（1）如果哈希表没有则加入key，并将key的个数，对应的salary都记录在value里面
（2）如果哈希表中有这个key，则更新value值

使用多个列分组

同一部分又可以进一步划分为各个工种
需求：查询各个department_id中各个job_id的平均工资

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

猜想

相比于单列，我猜想增加了哈希表的数量
（1）将department_id作为第一个哈希表的key，将job_id作为第一个哈希表的value
（2）同时，将job_id作为第二个哈希表的key，将key的个数，对应的salary都记录在value里面

交换分组字段的顺序

其实在之前笛卡尔积那个地方，处理如下查询：

SELECT last_name,department_name
FROM employees,departments;

遇到过交换from的两个表结果是否会有变化？？？

当时我猜想，底层可能是判断了两个表的长度，以长度为依据，将更长的表作为外循环

对于group，上面额例子用的是同一张表的两个字段，他们的行数相同

一种错误：行数不对齐

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

删掉了group中的job_id,保留了select的job_id,可以明显看出，job_id是没有过滤掉数据的

mysql对于这种情况不报错，但结果是错误的。oracle就会报错

结论：
（1）SELECT中出现的非组函数的字段必须声明在GROUP BY中。
反之，GROUP BY中声明的字段可以不出现在SELECT中
（2）GROUP BY声明在FROM后面、WHERE后面，ORDER BY前面、LIMIT前面（limit，分页）

SELECT *
FROM employees
LIMIT 0,20;

（3）在group by 后面接上 with rollup，相当于最后再加一行数据，将哈希表的value按列汇总，将所有的key的个数汇总，salary汇总，这样形式上就和哈希表的其他内容统一了，最后执行一样的操作。
具体在这，就是计算所有人的平均工资

（4）当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的
这和rollup的底层实现有关

having的使用（过滤数据）

之前的where，以及实现内外连接的join on都可以过滤数据

和group by配合
需求：查询各个部门的最高工资

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

需求：查询各个部门中最高工资比10000高的部门

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

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

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

我一开始用上面三种方式进行过滤，无一例外，全部报错

我认为是跟底层相关，where按照我的猜想是加了一层if，但是group by需要访问到集合中的每个表对象，加了if就访问不到了

因此，有这么一个结论：
如果过滤条件中使用了聚合函数，则必须使用HAVING来替换wHERE。否则，报错

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

但是用having替换了where还是报错

所以又引出了另一个结论：
having必须声明在group by后面
group by having

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

我觉得可以这样解释，group by要求访问表类的每一个对象，完成之后生成一张哈希表，哈希表的value就是这个每个部门（也就是key）对应的最大工资，最后由having来过滤出value值>10000的数据

将过滤解释为过滤出而不是过滤掉

基于前面两个原则，having要在使用group by的场景下配合使用

需求：查询部门id为10,20,30,40这四个部门中最高工资比10000高的部门

SELECT department_id,MAX(salary) maxsalary
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

SELECT department_id,MAX(salary) maxsalary
FROM employees
GROUP BY department_id
HAVING department_id IN (10,20,30,40) AND MAX(salary) > 10000;

上面方式效率更高，根据我的猜想也比较好理解。生成哈希表需要访问所有类对象
上一种方式会将对象过滤，只将选中的四个部门的对象，加入哈希表
后一种需要访问所有对象从而生成哈希表，再从中进行过滤
很明显后一种处理了更多的对象

having和where的区别：
1.having是分组后，where是分组前
2.where不用使用聚合函数，having可以使用聚合函数。
3.where在分组之前就会进行筛选，过滤掉的数据不会进入分组。

sql底层执行原理

sql92语法

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

sql99语法

SELECT …,…,…
FROM … 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：分页

sql的执行过程

回顾前面我猜想的多重循环，当时使用了这样一个例子：

SELECT last_name,department_name
FROM employees,departments;

输出数据的行数为107*27 = 2889
然后提出了这样一个多重循环的结构

for(employees类集合emp)--------------------------------------------->对应from语句中的employees表
	for(departments类集合dep)--------------------------------------->对应from语句中的departments表
		print(emp[i].employee_id,dep[i].department_name)------->对应select语句

我想验证循环的层数是由from确定还是select确定，于是

SELECT last_name
FROM employees,departments;

结果输出数据的行数为107*27 = 2889
这就说明，从from确定了循环的层数

执行顺序：
FROM -> WHERE -> GROUP BY -> HAVING ->
SELECT 的字段 ->
DISTINCT -> ORDER BY -> LIMIT

(8)SELECT (9)DISTINCT (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>

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 的执行过程中，对于我们来说是不可见的

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

（1）FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1
（2）ON：对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。
（3）OUTER(JOIN)：如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.
（4）如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止

所以笛卡尔积错误只是一种形象的说法，本质上，多表连接需要先用多重循环求出笛卡尔积，得到原始的数据

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

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

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

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

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

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

九. 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询

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

需求：查询那些员工的工资比abel高
首先得求出abel的工资
有子查询之前：

SELECT salary "abel's salary"
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

这种写法web服务器和DBMS会进行两次交互，数据的传输通过网络进行

使用自连接：

SELECT other.last_name,other.salary
FROM employees abel,employees other
WHERE abel.`last_name` = 'Abel' AND other.`salary` > abel.`salary`;

使用子查询：

SELECT last_name,salary
FROM employees
WHERE salary > (
			SELECT salary
			FROM employees
			WHERE last_name = 'Abel'
			);

术语

我们知道一个查询语句最后返回一个结果集

外查询，内查询；主查询，子查询

执行

子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用 。

注意事项：
（1）子查询要包含在括号内
（2）将子查询放在比较条件的右侧.这是规范问题，为了可读性
（3）单行操作符对应单行子查询，多行操作符对应多行子查询

分类

单行子查询与多行子查询

如果子查询的结果集只有一条记录，单行子查询

单行操作符：操作符还有这种类型？？就是拿的比较运算符过来

= equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
<> not equal to

需求：：查询工资大于149号员工工资的员工的信息

SELECT last_name,salary
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 149

);

需求：返回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
);

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

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

需求：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，
manager_id，department_id
分析：员工的manager_id和department_id要么和141相同，要么和174相同

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);

这种写法我认为有问题，不是语问题。假设141员工的值是(124,50),174员工的值是(149,80),那么按照上述写法，筛选出的员工的值应该有四种：
124,50
124,80
149,50
149,80
有点求笛卡尔积的感觉
根据需求，不应该出现四种情况。最后输出是对的，也只有两种情况，其原因是表的生成遵从的是物理世界的规则，如果两个员工的上级一样，一般来说他们是一个部门的。
但是，如果两个员工是部门一把手，由大老板直接管理，那么这两个员工就不是一个部门了，所以我觉得上面不严谨，应该严格规定员工的manager_id和department_id要么和141相同，要么和174相同

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

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
);

SELECT department_id,MIN(salary)
之前将select语句翻译成选择的字段，到了今天，翻译成查询部门id，最小工资更为贴切了，对理解需求更有帮助

case中的子查询

case when then else end，相当于java中的if…else if…else…

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

这有个新东西，我们打开的表里没有location这个字段，那怎么输出呢？？

SELECT 1
FROM DUAL;

这样写肯定是不行的，我们还希望location能够变化呢

放个数值类型，字符串类型都行。光放个location搁那，那就默认是字段了，找不到这个字段可不就报错了吗

因此，把location当作别名，前面呢要么放字段，要么放函数（单行函数，多行函数啥的）

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;

写之前我是没想到case when then else end能放在这个位置。

子查询的控制问题

如果子查询没有结果，那么返回的结果集就是0条记录，不会报错

非法使用子查询

使用单行比较操作符连接多行子查询

WHERE salary = (
	SELECT MIN(salary)
	FROM  employees
	GROUP BY department_id
)

从需求到语句和从语句到需求

sql是一门类似于英语的结构化查询语言，在实际的开发场景中，我们经常需要做两件事：从需求到语句和从语句到需求

像前面我猜测底层的代码实现，把查询语句用中文更清楚的表述出来，说明像where，join on，having这种过滤应该是过滤出而不是过滤掉，以上这些行为都是在做从需求到语句和从语句到需求

了解了执行顺序，我们就知道了查询语句最后输出的字段是select决定的，虽然说后面还有什么distinct, order by,limit，但是输出的字段已经决定好了

多行子查询

如果子查询返回的结果集有多条记录，则为多行子查询

使用多行比较操作符：

in,any,all,some

in的测试

WHERE salary IN (
	SELECT MIN(salary)
	FROM  employees
	GROUP BY department_id
)

any/all的测试
题目：查询其他job_id中比job_id为‘IT_PROG’部门任意工资低的员工的员工号，姓名，job_id,salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
);

需求：题目：查询其他job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号，姓名，job_id,salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
);

查询返回的也是一张表（三重查询）

需求：查询平均工资最低的部门id

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) < ALL (
	SELECT MIN(AVG(salary))
	FROM employees
	GROUP BY department_id 
)

我最开始的写法如上所示，但是在SELECT MIN(AVG(salary))报错了，聚合函数不能嵌套

经过前面已经知道，查询返回的结果集也是一张表。因此，子查询返回的也是一张表。既然是一张表，那么可不可以from呢？？？？

SELECT MIN(avg_sal)
FROM (
	SELECT AVG(salary) avg_sal
	FROM employees
	GROUP BY department_id 
) avg_table

这里可以说的地方还是挺多的：
（1）将查询的结果作为一张表，这张表的字段是select后面的部分
（2）查询可以用函数作为字段，但是子查询必须取别名。SELECT AVG(salary) avg_sal。如果子查询用了函数作为字段且没有写别名，那么主查询使用的字段就是函数，相当于主查询自己单独去做一个运算。按照上例，主查询就会去查salary字段，但是子查询返回的结果集是没有salary这个字段的，因此报错
（3）子查询返回的结果集作为一张表时，必须取别名

因此最终的查询为：

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 
		) avg_table
);

这是一个三重查询

还可以再简化，使用多行操作运算符all
前面虽然用了，但是用错了。使用之前需要想明白怎么过滤出最小值

最小值的性质是<=组中所有的元素，想明白这点就可以了

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) < ALL (
	SELECT AVG(salary)
	FROM employees
	GROUP BY department_id 
)

空值

场景：查询管理者的姓名

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

结果集是0条记录，原因在于内查询的结果集包含一个null，这种情况下，外查询就返回空

这个我解释不了了，我猜想他还是一个多重循环的底层，但是子查询和开多张表那种应该在底层是区分了的。而且和null的位置无关，即便我把null放在最后，结果集仍然是0条记录

相关子查询和不相关子查询

子查询被执行多次就是相关子查询

子查询中的表用到了外部的表

相关子查询的需求：查询工资大于本部门平均工资的员工信息
先求各部门平均工资
再遍历各部门员工工资

SELECT last_name, salary, department_id
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees e2
	WHERE department_id = e1.`department_id`
);

WHERE department_id = e1.department_id这一句体现的就是本部门

方式二：在from中写子查询，将子查询的结果集当作一张新表

SELECT e.last_name,e.salary,e.department_id
FROM employees e, (
			SELECT department_id,AVG(salary) avg_sal
			FROM employees
			GROUP BY department_id
			) t_depa_avg_sal
WHERE e.`department_id` = t_depa_avg_sal.department_id
AND e.`salary` > t_depa_avg_sal.avg_sal;

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`
		);

乍一看没看懂：
（1）order by的子查询返回的是什么？？？
是employees表每个员工对应的department_name
（2）order by是依据什么来排序？？？
字段。但是之前用的都是select里边用到的字段或者是from打开的表里面有的字段。上面的例子用的是另一张表里的字段，所以非常地反直觉
所以我做了一些测试，

SELECT employee_id,salary,department_name
FROM employees,departments

这个很熟了，开两张表，求笛卡尔积

SELECT employee_id,salary,department_name
FROM employees,departments
WHERE employee_id=100 AND salary=24000
ORDER BY department_name

然后用第二张表的字段来排序

所以呢，如果有一张表要根据某个字段排序，这个字段排序地字段隐式地作为这张表的一列，无论他来自哪张表。

这个说法挺拗口，我想说的就是下面这个结论
（3）order by可以直接使用来自其他表的字段吗？？？
比如说开两张表：

SELECT employee_id,salary,department_name
FROM employees,departments
ORDER BY department_name

很明显会遇到笛卡尔积的问题

不能直接使用有两方面原因：一是两张表没有关联，二是待排序的表和用于排序的字段的行数不一致

所以才需要去查询每位员工的department_name，而不能直接使用department_name

相关子查询

子查询的位置–结论：在查询语句中，除了group by和limit，其他位置都可以使用子查询
sql99语法
SELECT …,…,…
FROM … JOIN …
ON 多表的连接条件
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY …,…
HAVING 包含组函数的过滤条件
ORDER BY … ASC/DESC
LIMIT …,…

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

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

exists与not exists关键字

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

我感觉子查询和之前的关联查询在底层上至少都是多重循环

需求：查询公司管理者的employee_id，last_name，job_id，department_id信息
自连接的写法

SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id

使用子查询的写法

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

使用exists的写法

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

（1）如果在子查询中不存在满足条件的行：
条件返回 FALSE
继续在子查询中查找

（2）如果在子查询中存在满足条件的行：
不在子查询中继续查找
条件返回 TRUE

NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

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

SELECT d.department_id,d.department_name
FROM departments d
WHERE NOT EXISTS (
	SELECT e.department_id
	FROM employees e
	WHERE d.department_id = e.`department_id`
)

相关更新

相关删除

不相关子查询

不相关子查询的需求：查询工资大于本公司平均工资的员工信息

优化

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

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

十. 创建和管理表DDL（定义）

DDL,DML,DCL

MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数据表的行与列

保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

mysql的数据类型

创建和管理数据库

如何创建

方式1：创建数据库

CREATE DATABASE 数据库名;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）

CREATE DATABASE IF NOT EXISTS 数据库名;

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

if exists和if not exists关键字

当判断的表不存时，我可以执行创建数据库，创建表，增加列，可以执行相应的SQL语句；
而if exists同理判断，首先判断查询结果是否存在，如果存在执行判断后面的语句，查询的数据库，表，列的方法相同

使用数据库

查看当前所有的数据库SHOW DATABASES; #有一个S，代表多个数据库

查看当前正在使用的数据库SELECT DATABASE(); #使用的一个 mysql 中的全局函数

查看指定库下所有的表SHOW TABLES FROM 数据库名;

查看数据库的创建信息SHOW CREATE DATABASE 数据库名; 或者： SHOW CREATE DATABASE 数据库名\G

切换数据库USE 数据库名;

修改数据库字符集

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

alter：改变

删除指定的数据库（ 推荐 ）

DROP DATABASE IF EXISTS 数据库名;

drop：删除，丢弃

创建表

定点数：decimal。精度更高，比double更高

json：服务器和客户端的交互采用的文件格式

如何创建

USE atguigudb;

SHOW CREATE DATABASE atguigudb; #默认使用的是utf8

SHOW TABLES;

方式1："白手起家"的方式

CREATE TABLE IF NOT EXISTS myemp1( #需要用户具备创建表的权限。
id INT,

emp_name VARCHAR(15), #使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。

hire_date DATE
);

#查看数据表结构
DESC myemp1;

#查看创建数据表结构(详细定义信息)
SHOW CREATE TABLE myemp1; #如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集。

MySQL支持使用 DESCRIBE/DESC 语句查看数据
表结构，也支持使用 SHOW CREATE TABLE 语句查看数据表结构

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

#查看表数据
SELECT * FROM myemp1;

方式2：基于现有的表，同时导入数据

CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;

（1）查询语句中字段的别名，可以作为新创建的表的字段的名称。因为查询语句返回的结果集也可以作为一张表
（2）此时的查询语句可以结构比较丰富，使用前面章节讲过的各种SELECT

需求：复制一张表，但是不包括数据
分析：过滤掉全部数据，写法是很多的

SELECT *
FROM employees_blank;

修改表

添加一个字段

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

ALTER TABLE myemp1
ADD salary DOUBLE(10,2); #默认添加到表中的最后一个字段的位置
10表示一共多少位，2表示精度，保留两位小数

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;
first表示将字段添加到第一列

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;

修改一个字段：数据类型、长度、默认值、位置（略）

ALTER TABLE 表名 
MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

字段的位置也是可以改的

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ;

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT ‘aaa’;

重命名字段

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

ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);

删除字段

ALTER TABLE 表名 
DROP 【COLUMN】字段名

ALTER TABLE myemp1
DROP COLUMN my_email;

重命名表

rename to

#方式1：
RENAME TABLE myemp1
TO myemp11;
rename to
重命名表x为表y

#方式2：
ALTER TABLE myemp2
RENAME TO myemp12;
改变表x，重命名为表y

删除表

#不光将表结构删除掉，同时表中的数据也删除掉，释放表空间
DROP TABLE IF EXISTS myemp2;

DROP TABLE IF EXISTS myemp12;

清空表

注意，y可以是负数。比如说143.26，保留-1位小数就是140.看小数点的位置

这里的y同上，也可以是负数，效果是一样的

函数的嵌套：其实就是把一个函数的结果作为另一个函数的参数

SELECT TRUNCATE(ROUND(143.25,-1),-2);

清空表，表示清空表中的所有数据，但是表结构保留。

SELECT * FROM employees_copy;

TRUNCATE TABLE employees_copy;

DCL中的commit和rollback

COMMIT:提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。

ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

这里说的commit之后数据不能回滚，有点抽象。有数据，只要commit了，就不能回退额

start TRANSACTION;
delete from article where aid= 3;
COMMIT;
 
ROLLBACK; -- 此时回滚无效
 
-----------------------------------------------
 
start TRANSACTION;
delete from article where aid= 3;
ROLLBACK;  -- 此时回滚有效

truncate和delete

相同点：都可以实现对表中所有数据的删除，同时保留表结构。

不同点：
TRUNCATE TABLE：一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的。
DELETE FROM：一旦执行此操作，表数据可以全部清除（不带WHERE）。同时，数据是可以实现回滚的。

DDL 和 DML 的说明

ddl：数据定义语言，dml：数据操作语言，dcl：数据控制语言
① DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL
操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的。)

② DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了
SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

底层有个参数，表示是否自动commit

delete from的演示

COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
DELETE FROM myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

truncate的演示

#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
TRUNCATE TABLE myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

第四步TRUNCATE TABLE myemp3;一定会执行一次commit，rollback只能回滚到这一次commit

阿里mysql命名规范

表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字

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

表的命名最好是遵循 “业务名称_表的作用”。
正例：alipay_task 、 force_project、 trade_config

库名与应用名称尽量一致

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

mysql8新特性-DDL的原子化

CREATE DATABASE mytest;

USE mytest;

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

SHOW TABLES;

DROP TABLE book1,book2;

SHOW TABLES;

在mysql5.7中，对于DROP TABLE book1,book2;，虽然book2这张表不存在，但是book1已经被删掉了

在mysql8中，book1最后没有被删掉。在底层，先删掉了book1，再去删book2。结果删除book2失败了，book1就回滚了

原子化意味着一系列DDL事务，一荣俱荣，一损俱损。是与的关系，要么都成，要么都不成