MySQL学习总结（四）——DQL(数据查询语言总结【二】）_mysql数据查询语言心得体会

所有数据库学习总结文章均是根据B站尚硅谷视频总结而来，视频链接如下：
https://www.bilibili.com/video/BV12b411K7Zu.

一、分组查询

1、语法格式

SELECT  分组函数，字段 #要求为GROUP BY后边出现的字段
FROM    表
[WHERE  筛选条件]
GROUP BY 列字段
[ORDER BY  子句]
1
2
3
4
5

注意：查询的列表必须特殊，要求是分组函数和group by后面出现的字段

2、特点

①分组查询中的筛选条件分为两类：

注意：分组函数的结果做筛选条件肯定放在having子句后；能用分组前筛选的优先使用分组前筛选。

②GROUP BY 子句支持单个字段分组、多个字段分组（多个字段之间用逗号隔开没有顺序要求）、表达式或函数（较少使用）。

③可以添加排序，排序放在整个分组函数的最后。

3、运用实例

① 简单的分组查询

案例1：查询每个工种的最高工资。

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
1
2
3

运行结果为：

案例2：查询每个位置上的部门个数。

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
1
2
3

②添加分组前的筛选条件查询

案例1：查询邮箱中包含a字符的，每个部门的平均工资。

SELECT AVG(salary),department_id
FROM  employees
WHERE email LIKE '%a%'
GROUP BY department_id;
1
2
3
4

案例2：查询有奖金的每个领导手下员工的最高工资。

SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
1
2
3
4

③添加分组完成后的筛选条件，关键词HAVING

案例1：查询哪个部门的员工个数大于2。

#①查询每个部门的员工个数
SELECT COUNT(*), department_id 
FROM  employees 
GROUP BY department_id ;

#②根据①查询结果查询那个部门大于2
SELECT  COUNT(*), department_id 
FROM  employees 
GROUP BY department_id 
HAVING COUNT(*)>2;
1
2
3
4
5
6
7
8
9
10

运行结果为：

案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资。

SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
1
2
3
4
5

案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个及最低工资.

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
1
2
3
4
5

④按表达式或函数分组

案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些。

SELECT   COUNT(*),  LENGTH(last_name) len_name 
FROM  employees 
GROUP BY LENGTH(last_name) 
HAVING COUNT(*)>5;
1
2
3
4

⑤按多个字段分组

案例：查询每个部门每个工种的员工的平均工资。

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

运行结果：

⑥添加排序

案例：查询哪些部门和工种的员工的平均工资高于10000，并且按平均工资的高低显示。

SELECT AVG(salary) a,department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING a>10000
ORDER BY a DESC;
1
2
3
4
5
6

二、连接查询（又称为多表连接、多表查询）

SQL笛卡尔乘积现象：表1有m行，表二有N行，结果为m*n行，即当我们查询多个表时，没有添加有效的连接条件，导致多个表的所有行实现完全连接，就是笛卡尔积。为了防止这种情况需要添加有效连接条件。

1、分类

（1）按年代分：

①SQL92标准：MySQL中仅仅支持内连接。
②SQL99标准【推荐使用】：MySQL中支持内连接、外连接（左外、右外、全外）、交叉连接。

注意：
①SQL92标准也支持一部分外连接，但效果不太好(主要用于oracle、sqlserver 数据库中，mysql不支持)。
②SQL99标准中MySQL外连接不支持全外。

（2）按功能分：

①内连接：等值连接、非等值连接、自连接
②外连接：左外连接、右外连接、全外连接
③交叉连接。

2、 SQL92标准

语法：

SELECT 查询列表
FROM 表1 【别名】,表2 【别名】
WHERE 连接条件
【AND 筛选条件
GROUP BY 分组列表
HAVING 筛选条件   #分组后的筛选
ORDER BY 排序字段】;
#【】中的为可选内容
1
2
3
4
5
6
7
8

（1）等值连接

等值连接即两表的连接条件为等于。
特点：
①多表等值连接的结果为多表的交集部分；
②n表连接需要n-1个连接条件；
③多表的顺序没有要求（即from后表的顺序随意）；
④一般需要为表起别名；
⑤可以搭配排序、分组、筛选等使用；

①为表起别名

案例1：查询员工名所对应的部门名。

SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;
1
2
3

结果为：

案例2：查询员工名、工种号、工种名。

SELECT last_name,e.job_id,job_title
#为表起别名，两个表的顺序可以交换过来写
FROM employees AS e,jobs j   
WHERE e.`job_id`=j.`job_id`;
1
2
3
4

为表起别名的好处：
①提高语句简洁度；
②区分不同表中的多个重名字段
注意：如果为表起了别名，就不能用原来的表名去限定

②添加筛选条件。

案例1：查询有奖金的员工名和部门名。

SELECT last_name,department_name
FROM employees e,departments d
WHERE e.epartment_id = d.epartment_id 
AND comission_pct IS NOT NULL;
1
2
3
4

运行结果：

案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE '_o%';
1
2
3
4

运行结果：

③添加分组

案例1：查询每个城市的部门个数

SELECT COUNT(*),city
FROM locations l,departments d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;
1
2
3
4

运行结果：

案例2：查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资

SELECT MIN(salary),department_name,e.manager_id
FROM employees e,departments d
WHERE e.department_id = d.department_id 
AND commission_pct IS NOT NULL
GROUP BY department_name,e.manager_id;
1
2
3
4
5

查询结果为：

④添加排序

案例1：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT COUNT(*) 员工个数,job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY 员工个数 DESC;
1
2
3
4
5

查询结果：

⑤实现多表连接

案例1：案例：查询城市名首字母是s的员工名、部门名和所在城市,并按部门名降序

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;
1
2
3
4
5
6

查询结果：

（2）非等值连接

案例1：查询工资级别为A的员工的工资

SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.lowest_sal AND j.highest_sal 
 AND j.grade_level = 'A';
/*创建等级表
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);
 
INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

查询结果为：

（3）自连接

自连接相当于一个表里查两次。

案例1：查询员工名和上级的名称.

SELECT e.employee_id 员工id,
	   e.last_name 员工名,
	   m.employee_id 管理者id,
	   m.`last_name` 管理者名
FROM employees e,employees m
WHERE e.`manager_id`= m.`employee_id`;
1
2
3
4
5
6

查询结果：

3、 SQL99标准

语法

【】中的内容为可选部分

SELECT 查询列表
FROM 表1 【别名】 连接类型 #连接类型见下表格
JOIN 表2 【别名】 
ON 连接条件
【WHERE 筛选条件
GROUP BY 分组列表
HAVING 筛选条件
ORDER BY 排序字段】;
1
2
3
4
5
6
7
8

(1)内连接

①等值连接：

特点：
a、可以添加排序、分组、筛选inner可以省略。
b、连接条件放在on后面，筛选条件放在where后面，提高分离性，便于阅读。
c、inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集。

案例1：查询员工名、部门名。

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
1
2
3
4

案例2：查询名字中包含e的员工名和工种名（添加筛选）

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE e.last_name LIKE '%e%';
1
2
3
4
5

案例3：查询哪个部门的部门员工个数大于3的部门名和员工个数，并按个数降序（分组+排序)

SELECT department_name,COUNT(*) 员工个数
FROM employees e 
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
1
2
3
4
5
6
7

②非等值连接：

案例1：查询每个工资级别的个数，并查看个数大于20的，并 按工资级别降序排列。

SELECT grade_level,COUNT(*)
FROM employees e
INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY grade_level 
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
1
2
3
4
5
6
7

③自连接：
案例1：查询姓名中包含字符k的员工的名字和上级的名字

SELECT e.last_name 员工名,m.last_name 领导名
FROM employees e
INNER JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%K%';
1
2
3
4
5

(2)外连接

①应用场景：一个表中有另一个表中没有。

②特点：

a、外连接查询结果为主表的所有记录，如果从表有和他匹配的显示匹配值，没有和他匹配的显示Null。 外连接查询结果=内连接结果+主表中有而从表中没有的记录。
b、左外连接，left join左边的是主表；右外连接，right join右边的是主表。
c、左外和右外交换两个表的顺序，可以实现同样的效果
d、全外连接=内连接得结果+表一中有2没有的+表二有表一没有的。（SQL99不支持全外连接）

③案例1：查询哪个部门没有员工。主表：部门表，从表员工表。

方法一：左外连接

SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id`IS NULL;
1
2
3
4
5

方法二：右外连接

SELECT d.*,e.employee_id
FROM employees e
RIGHT JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id`IS NULL;
1
2
3
4
5

执行结果为：

这个案例不可以使用内连接查询，因为内连接查询结果为两个表的交集，没办法查询到没有员工的部门。

(3)交叉连接

交叉连接实际是：使用99语法实现笛卡尔乘积,效果和92语法的逗号类似。

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
1
2
3

4、sql92和sql99比较

1、功能：sql99支持的较多。
2、可读性：sql99实现连接条件和筛选条件的分离。

5、练习案例

案例1：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有用null填充。

SELECT b.id,b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
WHERE b.id>3;
1
2
3
4
5

案例2：查询哪个城市没有部门。主表：位置表，从表部门表。

SELECT l.city ,d.department_id
FROM departments d
RIGHT OUTER JOIN locations l
ON l.`location_id`=d.`location_id`
WHERE d.department_id IS NULL;
1
2
3
4
5

案例3：查询门名为SAL或IT的员工信息

SELECT e.*,department_name
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE department_name IN('SAL','IT');
1
2
3
4
5