MySQL教程基础篇（三）：DQL语言

最新推荐文章于 2022-12-10 10:14:39 发布

Nandeska

最新推荐文章于 2022-12-10 10:14:39 发布

阅读量371

点赞数

分类专栏： DataBase

本文链接：https://blog.csdn.net/u013644146/article/details/111560374

版权

DataBase 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

DQL：Data Query Language

首先，创建数据表，可通过如下链接下载：https://pan.baidu.com/s/1nM2-xSsTnPqQXdwXCvHrSw 提取码：kqed

或者如下链接： https://pan.baidu.com/s/1NdoPCx2eBkL_EcCfTChTdg 提取码：jhsn

或者查看这篇博文： https://blog.csdn.net/GongmissYan/article/details/102937816

创建的四张表如下：

mysql> desc departments;
+-----------------+------------+------+-----+---------+----------------+
| Field           | Type       | Null | Key | Default | Extra          |  部门表
+-----------------+------------+------+-----+---------+----------------+
| department_id   | int(4)     | NO   | PRI | NULL    | auto_increment |  部门编号
| department_name | varchar(3) | YES  |     | NULL    |                |  部门名称
| manager_id      | int(6)     | YES  |     | NULL    |                |  部门领导的员工编号
| location_id     | int(4)     | YES  | MUL | NULL    |                |  位置编号
+-----------------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |  员工表
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |  员工编号
| first_name     | varchar(20)  | YES  |     | NULL    |                |  员工的名
| last_name      | varchar(25)  | YES  |     | NULL    |                |  员工的姓
| email          | varchar(25)  | YES  |     | NULL    |                |  邮箱
| phone_number   | varchar(20)  | YES  |     | NULL    |                |  手机号
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |  工种编号
| salary         | double(10,2) | YES  |     | NULL    |                |  工资
| commission_pct | double(4,2)  | YES  |     | NULL    |                |  奖金率
| manager_id     | int(6)       | YES  |     | NULL    |                |  上级领导的员工编号
| department_id  | int(4)       | YES  | MUL | NULL    |                |  部门编号
| hiredate       | datetime     | YES  |     | NULL    |                |  入职时间
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> desc jobs;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |  工种表
+------------+-------------+------+-----+---------+-------+
| job_id     | varchar(10) | NO   | PRI | NULL    |       |  工种编号
| job_title  | varchar(35) | YES  |     | NULL    |       |  工种名称
| min_salary | int(6)      | YES  |     | NULL    |       |  最低工资
| max_salary | int(6)      | YES  |     | NULL    |       |  最高工资
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc locations;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |  位置表
+----------------+-------------+------+-----+---------+----------------+  
| location_id    | int(11)     | NO   | PRI | NULL    | auto_increment |  位置编号
| street_address | varchar(40) | YES  |     | NULL    |                |  街道
| postal_code    | varchar(12) | YES  |     | NULL    |                |  邮编
| city           | varchar(30) | YES  |     | NULL    |                |  城市
| state_province | varchar(25) | YES  |     | NULL    |                |  州/省
| country_id     | varchar(2)  | YES  |     | NULL    |                |  国家编号
+----------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

基础查询

【语法】：select [查询列表] from [表名]

【特点】：

1、查询列表可以是：表中的字段、常量值、表达式、函数

2、查询结果是一个虚拟的表格

例：select last_name, salary, email from employees;

3 、查询函数

例：select version(); --查询MySQL的版本

4、起别名，

方式一：使用 as

例： SELECT last_name AS 姓 FROM employees;

方式二：使用空格

例： SELECT last_name 姓 FROM employees;

别名不能出现空格，也可以通过加 ""的方式进行标注

例： SELECT last_name AS "out put" FROM employees;

5、去重查询，使用 DISTINCT 关键字

例： SELECT DISTINCT last_name FROM employees;

6、+ 的作用

只有一个功能：数学运算符

SELECT 10+90 --两个操作都为数值型，则做加法运算

SELECT "10" + 90 --其中有一个为字符型，则试图将字符转成数值

如果转换成功，则做加法运算

SELECT “Jack” + 90 --如果转换失败，则字符转换成数值 0

SELECT NULL + 10 --只要有一个为NULL，则运算结果为 NULL

SQL语法中的字符拼接使用 CONCAT 函数

例： SELECT CONCAT(first_name, last_name) FROM employees;

7、对查询的列进行非空判断，使用 IFNULL(column, result) 函数

例： SELECT CONCAT(first_name, last_name, IFNULL(salary, 0)) FROM employees; --如果salary的值为 null 的话，则显示为 0

条件查询

【语法】：SELECT [查询列表] FROM [表名] WHERE [筛选条件]

1、使用条件运算符，使用如下运算符 >， < ，= ，!= ，>= ，<=

案例1：查询工资>12000的员工信息

SELECT * FROM employees WHERE salary > 12000;

案例2：查询部门编号不等于90的员工名和部门编号

SELECT last_name, department_id FROM employees WHERE department_id != 90;

2、按逻辑运算符，使用 AND OR NOT 关键字

案例1：查询工资在10000到20000之间的员工名、工资以及奖金

SELECT last_name, salary, commission_pct FROM employees WHERE salary >= 10000 AND salary <= 20000;

案例2：查询部门编号不是在90到100之间，或者工资高于15000的员工信息

SELECT * FROM employees WHERE NOT(department_id > 90 AND department_id < 100) OR salary > 15000;

3、模糊查询，使用 LIKE ，BETWEEN AND ，IN ，IS NULL，IS NOT NULL 关键字

案例1：查询员工last_name中包含字符a的员工信息

SELECT * FROM employees WHERE last_name LIKE '%a%';

注：% 为通配符，表示任意字符，包含0个字符

案例2：查询员工last_name的第二个字符为a，第五个字符为e的员工信息

SELECT * FROM employees WHERE last_name LIKE '_a__a%' ;

注：_ 为通配符，表示一个字符

案例3：查询员工last_name的第二个字符为_的员工信息

SELECT * FROM employees WHERE last_name LIKE '_\_%';

注： \ 为转义字符，将通配符 _ 转义成字符 _

案例4：查询工资在10000到20000之间的员工名、工资以及奖金

SELECT last_name, salary, commission_pct FROM employees WHERE salary BETWEEN 10000 AND 20000;

注：BETWEEN AND的过滤查询包含边界值10000和20000

案例5：查询员工的工种编号为 IT_PROG，AD_VP，AD_PRES中的一个的员工信息

SELECT * FROM employees WHERE job_id IN('IT_PROG', 'AD_VP', 'AD_PRES');

注：IN 列表中的值必须一致或者兼容，IN列表中不可使用 % _ 等通配符

案例6：查询没有奖金的员工信息

SELECT * FROM employees WHERE commission_pct IS NULL;

案例7：查询有奖金的员工信息

SELECT * FROM employees WHERE commission_pct IS NOT NULL;

注：IS 与 = 不能相互替换

4、安全等于 <=>

案例6：查询没有奖金的员工信息

SELECT * FROM employees WHERE commission_pct <=> NULL;

注：<=> 既可以当 is 用，也可以当 = 用，但可读性差，使用较少

案例8：查询员工号为176的员工的姓名、部门号和年薪

SELECT first_name, last_name, department_id, salary*12*(1 + IFNULL(commission_pct, 0)) AS '年薪' FROM employees WHERE employee_id = '176';
思考：如下两个SQL的结果是否一样？为什么？

select count(*) from employees;

select count(*) from employees where commission_pct like '%%';

答案：不一样，因为%表示0或多个字符，%% 不能匹配 NULL ，这里 like '%%' 等同于 is not null

小结：SELECT [查询列表] FROM [表名] WHERE [查询条件] 的执行顺序是 FROM-->WHERE-->SELECT

排序查询

【语法】：SELECT [查询列表] FROM [表名] WHERE [查询条件] ORDER BY [排序列表] 【ASC| DESC】

【特点】：

1、ASC 为升序，DESC 为降序，如果不写，默认是升序

2、ORDER BY 字句中可以支持单个字段、多个字段、表达式、函数和别名。

3、ORDER BY 一般放在SELECT 语句的最后面，只有 LIMIT 字句除外。

案例1：查询员工信息，按照工资从高到低进行排序

SELECT * FROM employees ORDER BY salary ASC;

案例2：查询部门编号 >= 90 的员工信息，按入职时间进行降序排序

SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate DESC;

案例3：按年薪的高低显示员工的姓名和年薪【按表达式排序】

SELECT first_name, last_name, salary*12*(1 + IFNULL(commission_pct, 0)) AS 年薪 FROM employees ORDER BY salary*12*(1 + IFNULL(commission_pct, 0)) DESC;

注：ODDER BY 后面的表达式也可以改为使用别名

SELECT first_name, last_name, salary*12*(1 + IFNULL(commission_pct, 0)) AS 年薪 FROM employees ORDER BY 年薪 DESC;

案例4：按姓氏长度显示员工的姓名和工资【按函数排序】

SELECT LENGTH(first_name) AS '姓名长度', first_name, salary FROM employees ORDER BY LENGTH(first_name) DESC;

案例5：查询员工信息，要求先按工资升序排列，再按员工编号排序降序排列【多个字段的排序】

SELECT * FROM employees ORDER BY salary ASC, employee_id DESC;

练习

1、查询工资不在 8000到 17000 范围内的员工的姓名和工资，按工资降序排列

第一种写法：不包含边界值 8000 和 17000

SELECT first_name, last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;

第二种写法：包含边界值 8000 和 17000

SELECT first_name, last_name,salary FROM employees WHERE NOT (salary >8000 AND salary <17000) ORDER BY salary DESC;

2、查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC, department_id ASC;

常见函数

【概念】：将一组逻辑语句封装在方法中，对外暴露方法名供SQL调用，类似Java的方法。

【好处】：隐藏实现细节，提高代码重用性

【分类】：

1、单行函数，如：CONCAT，LENGTH，IFNULL等

2、组函数，又称为统计函数，聚合函数，做统计使用

单行单数

一、字符函数

#LENGTH 计算字符长度

【语法】SELECT LENGTH('varchar');

【例】：SELECT LENGTH('张三丰hahaha'); --结果为15，因为一个中文占 3个字节

【注】：可使用 SHOW VARIABLES LIKE '%char%'; 查看字符集

#CONCAT 拼接字符串

【语法】：SELECT CONCAT(last_name, '_', first_name) AS '姓名' FROM employees;

#UPPER，LOWER 大小写转换

【例】：SELECT CONCAT(UPPER(last_name), LOWER(first_name)) FROM employees;

【注】：函数可以嵌套使用

#SUBSTR 字符截取

【语法】：SUBSTR(varchar, 起始位置, 截取的长度) 或 SUBSTR(varchar, 起始位置)

【例】：

SUBSTR('李莫愁爱上了陆展元', 7); --结果为 '陆展元'

SUBSTR('李莫愁爱上了陆展元', 1, 3); --结果为 '李莫愁'

【注】：起始位置的索引值是从1开始

案例：查询所有员工的姓名，将姓名中首字符大写，其他字符小写然后用 _ 拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(first_name, 1, 1)), LOWER(SUBSTR(first_name, 2))) AS '姓名' FROM employees;

#INSTR 返回子串第一次出现的位置，如果找不到则返回 0

【语法】：INSTR(str, 子串)

【例】：SELECT INSTR('杨不悔爱上了殷梨亭', '殷梨亭') AS out_put;

#TRIM 去掉字符串首尾的空格

【语法】：SELECT CONCAT(TRIM(' 张翠山 ')) AS out_put;

【注】：也可以使用 TRIM('a' FROM 'aaaa123aaaa') 来去掉字符串首尾的 a 字符，去掉之后结果为 123

#LPAD 用指定的字符实现指定的总长度

【语法】LPAD('殷素素', 10, '*') AS out_put; --结果为 '*******殷素素'

【注】：同理，还有一个 RPAD，语法同上

#REPLACE 替换

【语法】：REPLACE('张无忌爱上了周芷若', '周芷若', '赵敏'); --结果为 '张无忌爱上了赵敏'

# RAND 随机数（0~1）

二、数学函数

# ROUND 四舍五入，可指定精度

【语法】

ROUND(1.65); --结果为 2

ROUND(1.657, 2) --结果为 1.66

#CEIL 向上取整，返回 >=参数的最小整数

【例】：SELECT CEIL(-1.65); --结果为 -1

#FLOOR 向下取整，返回 <=参数的最大整数

【例】：SELECT FLOOR(-9.99); --结果为 -10

#TRUNCATE 截断，指定小数点后面保留的位数，没有四舍五入

【例】：SELECT TRUNCATE(1.699999, 1); --结果为 -1.6

#MOD 取余

【语法】MOD(a, b); 等同于 a 除以 b 所得的余数

三、日期函数

#NOW 返回当前系统日期+时间

【语法】 NOW()

#CURTIME 返回当前系统时间，不包含日期

【语法】 CURTIME()

#CURDATE 返回当前系统日期，不包含时间

【语法】 CURDATE()

#也可以获取指定时间的年、月、日、时、分、秒

【例】

YEAR(NOW())

YEAR('1999-12-12')

MONTHNAME(NOW())

DAY(NOW())

#两个常见的日期函数

格式符的表达与释义如下：

案例：查询有奖金的员工名和入职日期（XX月/XX日 XX年）

SELECT last_name, DATE_FORMAT(hiredate,'%m月/%d日 %Y年') AS '入职日期' FROM employees WHERE commission_pct IS NOT NULL;

四、其他函数

SELECT VERSION();

SELECT USER();

SELECT DATABASE();

PASSWORD('字符')：返回该字符的密码形式

MD5('字符')：返回该字符的MD5加密形式

五、流程控制函数

#1. IF函数

【语法】：IF(value, t, f)

【说明】：判断value的值，如果为 true ，返回 t 的值，如果为 false , 返回 f 的值，语法特点类似Java 中的三元表达式

【例】：IF(10>5, '大', '小');

【案例】：查询员工的名字和奖金情况，如果没奖金，显示 '没奖金，呵呵' ，如果有奖金，显示 '有奖金，嘻嘻'

SELECT last_name, commission_pct, IF(commission_pct IS NULL, '没奖金，呵呵', '有奖金，嘻嘻') AS '奖金情况' FROM employees ;

#2. CASE 函数

【语法】：

CASE 要判断的字段或表达式

WHEN 常量1 THEN 要显示的值1或语句1;

WHEN 常量2 THEN 要显示的值2或语句2;

...

ELSE 默认的值(如不满足以上情况的话，返回的就是默认值)

END

【案例1】：查询员工的工资，要求：

部门号=30，显示的工资为 1.1倍

部门号=40，显示的工资为 1.2倍

部门号=50，显示的工资为 1.3倍

其他部门显示原工资

SELECT last_name, salary,department_id,
(
   CASE department_id
   WHEN 30 THEN salary*1.1
   WHEN 40 THEN salary*1.2
   WHEN 50 THEN salary*1.3
   ELSE salary
   END
) AS '备注'
FROM employees;

【案例2】：查询员工的工资情况

如果工资 >20000，显示A级别

如果工资 >15000，显示B级别

如果工资 >10000，显示C级别

其余的显示D级别

SELECT salary,
CASE
   WHEN salary > 20000 THEN 'A'
   WHEN salary > 15000 THEN 'B'
   WHEN salary > 10000 THEN 'C'
   ELSE 'D'
END AS '级别'
FROM employees;

-------------------------------------------------------------

练习

1、查询工号、姓名，工资，以及工资提高20%的结果（NEW salary）

SELECT employee_id AS '工号', last_name AS '姓名', salary AS '工资', salary*1.2 AS '新工资' FROM employees;

2、将员工姓名按首字母排序，并写出姓名的长度

SELECT last_name AS '姓名', LENGTH(last_name) AS '姓名长度' FROM employees ORDER BY LENGTH(last_name) DESC;

-------------------------------------------------------------

分组函数

【功能】：用作统计使用，又称为聚合函数、统计函数、组函数

【分类】：SUM 求和、AVG 平均值、MAX 最大值、MIN 最小值、COUNT 计算个数

【特点】：

1、SUM、AVG 可用于处理数值型

MAX、MIN、COUNT 可用于处理所有类型

2、以上分组函数都忽略 NULL 值

3、可以和 DISTINCT 搭配使用

4、COUNT 函数一般用来统计行数

5、和分组函数一同查询的字段要求都是 GROUP BY 后的字段

#1、简单使用：

SELECT SUM(department_id) FROM employees;
SELECT MAX(department_id) FROM employees;
SELECT MIN(department_id) FROM employees;
SELECT AVG(department_id) FROM employees;
SELECT COUNT(department_id) FROM employees;

#2、参数支持哪些类型

是否支持字符串的使用？SUM、AVG不支持字符串的使用，其他的支持所有类型

SELECT SUM(last_name), AVG(last_name) FROM employees;

是否支持 NULL值？SUM、AVG、COUNT、MIN、MAX 都忽略 NULL 值

SELECT COUNT(commission_pct), AVG(commission_pct), SUM(commission_pct)/35, SUM(commission_pct)/107 FROM employees;

3、搭配 DISTINCT 一起使用，实现去重

SELECT SUM(salary), SUM(DISTINCT(salary)) FROM employees;

SELECT COUNT(salary), COUNT(DISTINCT(salary)) FROM employees;

5、COUNT 函数

SELECT COUNT(*) FROM employees; -- 可以用来统计数据表的总行数

效率：

MYISAMA 存储引擎（5.5版本之前）下， COUNT(*) 效率高

INNODB 存储引擎 (5.5 版本之后，含 5.5)下，COUNT(*) 和 COUNT(1) 差不多，比 COUNT(字段) 效率高

------------------------------------------------------------------------------------------------------------

练习：

查询员工表中的最大入职时间和最小入职时间的相差天数

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;

------------------------------------------------------------------------------------------------------------

分组查询

【语法】：

SELECT column, group_function(column)

FROM table

WHERE condition

GROUP BY group_by_expression

ORDER BY column;

【特点】：

1、分组的筛选条件分为两类：

数据源位置关键字

分组前筛选：原始表 group by 字句前面 where

分组后筛选：分组后的结果集 group by 字句后面 having

①分组函数做条件肯定是放在 having 字句中

②能用分组前筛选的，优先考虑使用分组前筛选（性能更好）

2、一般遇到 '每个' 这种关键字的时候说明需要进行分组了

3、GROUP BY 字句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求）、表达式和函数

4、也可以添加排序（排序放在分组查询之后）

简单的分组

【案例1】：查询每个部门的平均工资

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

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

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

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

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

复杂的分组（添加筛选条件）

【案例1】：查询每个领导手下有奖金的员工的最高工资（添加分组前的筛选条件）

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

【案例2】：查询有哪些部门的员工数量 > 2 (添加分组后的筛选条件)

步骤1：查询每个部门的员工个数

SELECT COUNT(*) AS num, department_id FROM employees GROUP BY department_id;

步骤2：根据1的结果进行筛选，查询哪个部门的员工个数 >2

SELECT COUNT(*) AS num, department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 2;

【案例3】：查询每个工种有奖金的最高工资 > 12000的工种编号和其最高工资

步骤1：查询每个工种有奖金的员工的工种编号和其最高工资

SELECT job_id, MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id ;

步骤2：根据1的结果进行筛选，查询最高工资 > 12000 的结果

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

【案例3】：查询领导编号 > 102 的每个领导手下的最低工资 > 5000 的领导编号，以及其最低工资

步骤1：查询领导编号 > 102 的每个领导的编号，以及该领导下的员工的最低工资

SELECT manager_id, MIN(salary) FROM employees WHERE manager_id > 102 GROUP BY manager_id ;

步骤2：在1的基础上，进一步筛选最低工资 > 5000的

SELECT manager_id, MIN(salary) FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary) > 5000;

【思路与诀窍】：先查询已有的（分组前的，步骤1），再查询无中生有的（分组之后的，步骤2 ）

#按表达式或者函数进行分组

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

步骤1：：按员工姓名长度分组，查询每一组的员工个数

SELECT COUNT(*) FROM employees GROUP BY LENGTH(last_name) ;

步骤2：在步骤1的基础上，筛选员工个数 > 5 的

SELECT COUNT(*) c FROM employees GROUP BY LENGTH(last_name) HAVING c > 5;

#按多个字段分组

将多个字段并行写入，放在 GROUP BY 的字句中，用逗号隔开，不区分先后顺序

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

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

添加排序

【案例1】：查询每个部门每个工种的员工的平均工资，并按平均工资的从高到低显示出来

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

【案例1】：查询每个部门每个工种的员工的平均工资，将平均工资 > 10000 的从高到低显示出来

SELECT AVG(salary), job_id, department_id FROM employees GROUP BY department_id, job_id HAVING AVG(salary) > 10000 ORDER BY AVG(salary) DESC;

-----------------------------------------------------------------------------------------------------------------------------

练习

#查询各job_id的员工工资的最大值、最小值、平均值、总和，并按 job_id 排序

SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees GROUP BY job_id;

#查询员工最高工资和最低工资的差距

SELECT MAX(salary), MIN(salary), MAX(salary) - MIN(salary) AS '差距' FROM employees;

#查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT MIN(salary), manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) >= 6000;

#查询所有部门的编号，员工数量和工资平均值，并按平均工资降序

SELECT department_id, COUNT(*), AVG(salary) FROM employees GROUP BY manager_id ORDER BY AVG(salary) DESC;

#选择具有各个job_id的员工人数

SELECT COUNT(*), job_id FROM employees GROUP BY job_id;

-----------------------------------------------------------------------------------------------------------------------------

连接查询

【含义】：又称多表查询，多表连接

用到的表，表的创建： https://blog.csdn.net/GongmissYan/article/details/102937816

+--------------+-------------+------+-----+---------------------+----------------+
| Field        | Type        | Null | Key | Default             | Extra          |
+--------------+-------------+------+-----+---------------------+----------------+
| id           | int(11)     | NO   | PRI | NULL                | auto_increment |  
| name         | varchar(50) | NO   |     | NULL                |                |  姓名
| sex          | char(1)     | YES  |     | 女                  |                |  性别
| borndate     | datetime    | YES  |     | 1987-01-01 00:00:00 |                |  生日
| phone        | varchar(11) | NO   |     | NULL                |                |  手机号
| photo        | blob        | YES  |     | NULL                |                |  照片
| boyfriend_id | int(11)     | YES  |     | NULL                |                |  男朋友ID
+--------------+-------------+------+-----+---------------------+----------------+

+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |  男朋友ID
| boyName | varchar(20) | YES  |     | NULL    |                |  男朋友姓名
| userCP  | int(11)     | YES  |     | NULL    |                |  CP值
+---------+-------------+------+-----+---------+----------------+

笛卡尔积错误

SELECT name, boyname from beauty, boys; --查询结果有48行

原因：

SELECT name from boys; --结果有4行

SELECT name from beauty; --结果有12行

没有有效的连接条件导致结果有48行

解决：添加连接条件

SELECT name, boyname from beauty, boys WHERE beauty.boyfriend_id = boys.id;

连接查询的分类

按年代分：

sql 92 标准：仅支持内连接

sql 99 标准【推荐】：MySQL支持内连接 + 外连接（左外和右外）+ 交叉连接

按功能分

内连接：

等值连接

非等值连接

自连接

外连接

左外连接

右外连接

全外连接（MySQL不支持）

交叉连接

一、sql 92 标准

【语法】：

select 查询列表

from 表1 别名，

表2 别名

where 连接条件[表1.字段 = 表2.字段]

and 过滤条件

group by 分组

having 分组后筛选

order by 排序

1、等值连接

① 多表等值连接的结果为多表的交集部分

② n表连接，至少需要 n-1 个连接条件

③ 多表的书写顺序没有要求

④ 可以为表起别名

⑤ 可以搭配筛选、排序、分组等字句使用

【案例1】：查询女神名和对应的男神名

SELECT name, boyname from beauty, boys WHERE beauty.boyfriend_id = boys.id;
【案例2】：查询员工名、工种号、工种名（并为表起别名）

SELECT last_name, e.job_id, jobs.job_title FROM employees e, jobs WHERE e.job_id = jobs.job_id;

【注】：如果为表起了别名，则查询的字段不能使用原来的表名进行限定

错误的示例： SELECT last_name, employees.job_id, jobs.job_title FROM employees e, jobs WHERE e.job_id = jobs.job_id;

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

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

#添加分组条件

【案例4】：查询每个城市的部门个数

SELECT COUNT(*), l.city FROM locations l, departments d WHERE l.location_id = d.location_id GROUP BY l.city;

【案例5】：查询有奖金的每个部门的部门名称、部门的领导编号和该部门的最低工资
SELECT d.department_name, d.manager_id, MIN(salary) FROM departments d, employees e WHERE d.department_id = e.department_id AND e.commission_pct IS NOT NULL GROUP BY d.department_name;

#添加排序
【案例6】查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT COUNT(*), job_title FROM employees e, jobs j WHERE e.job_id = j.job_id GROUP BY e.job_id ORDER BY COUNT(*) DESC;

2、非等值连接

【案例1】：查询员工的工资和工资级别

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

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;

【练习】

#显示员工表的最大工资，工资平均值
SELECT MAX(salary), AVG(salary) FROM employees;

#查询员工表的employee_id，job_id，last_name，按department_id降序，按salary升序
SELECT employee_id, job_id, last_name FROM employees ORDER BY department_id DESC, salary ASC;

#查询员工表中 jobs_id 中包含 a 和 e 的，并且 a 在 e 的前面
SELECT job_id FROM employees where job_id LIKE '%a%e%';

#显示当前日期，以及去前后空格，截取子字符串的函数
SELECT CURRENT_DATE();
SELECT TRIM(" 123 ");
SELECT SUBSTR("123456",2);

#查询90号部门员工的job_id和90号部门的location_id
SELECT e.job_id, d.location_id FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id = '90';

#查询所有有奖金的员工的last_name,departmeng_name,location_id,city
SELECT e.last_name, d.department_name, l.location_id, l.city FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND e.commission_pct IS NOT NULL;

#查询每个工种、每个部门的部门名、工种名和最低工资
SELECT j.job_title, d.department_name, MIN(salary) FROM employees e, departments d, jobs j WHERE e.department_id = d.department_id AND e.job_id = j.job_id GROUP BY d.department_name, j.job_title

#查询city在Toronto工作的员工的last_name,job_id,department_id,department_name
SELECT e.last_name, e.job_id, d.department_id, d.department_name FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.city = 'Toronto';

#选择指定员工的姓名、员工号，以及他的上级的姓名和工号，结果类似如下格式
# employees Emp# manager Mgr#
# john 101 king 100
SELECT e.last_name AS 'employees', e.employee_id AS 'Emp#', m.last_name AS 'manager', m.employee_id AS 'Mgr#' FROM employees e, employees m WHERE e.manager_id = m.employee_id;

#查询每个国家下的部门个数大于2的国家编号
SELECT l.country_id AS '国家编号', COUNT(*) FROM departments d, locations l WHERE d.location_id = l.location_id GROUP BY country_id HAVING COUNT(*) >2;

-----------------------------------------------------------------------------------------------

复习

1、排序查询

【语法】：

select [查询列表]

from [表]

order by [排序列表] [排序方式]

【特点】：

排序方式如果不写的话，默认为升序

排序列表支持单个字段、多个字段、函数、表达式、表名等

order by 一般放在查询语句的最后，如果有limit，则放在limit的前面，其他语句的后面

2、函数

概述：类似Java中的方法，隐藏了实现细节，提高了复用性

调用：select 函数名(实参列表)

单行函数：

1、字符函数

concat：连接

substr：截取子串

upper/lower：转变大小写

replace：替换

length：获取字符长度

trim：去前后空格

lpad/rpad：左/右填充

instr：获取子串第一次出现的索引位置

2、数学函数

ceil：向上取整

floor：向上取整

round：四舍五入

mod：取模

truncate：截断

rand：获取随机数(0~1之间的小数)

3、日期函数

now：当前日期

year/month/day/hour/minute/second：获取年/月/日/时/分/秒

date_format：将日期转换成字符

curdate：获取当前日期

str_to_date：字符转换成日期

curtime：获取当前时间

datediff：返回两个日期相差的天数

monthname：以英文形式返回月

4、其他函数

version：当前数据库版本

database：当前使用的数据库名

user：显示当前用户

password('字符')：返回字符的密文

5、流程控制函数

【语法】：

①if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2

②case 情况1

case 变量/表达式/字段

when 常量1 then 值1

when 常量2 then 值2

...

else 值n

end

③case 情况2

case

when 条件1 then 值1

when 条件2 then 值2

...

else 值n

end

分组函数：

【语法】：

select 分组函数，分组后的字段

from 表

[where 分组前的筛选]

group by [分组的字段]

[having 分组后的筛选]

[order by 排序列表]；

执行顺序： from --> where --> group by --> having --> select --> order by

常见的分组函数

min：最小值

max：最大值

sum：求和

avg：求平均值

count：统计个数

【特点】：

1、sum和avg一般用于处理数值型，其他的可处理任意类型

2、分组函数都忽略null

3、都可以搭配distinct使用，实现去重的统计

4、count函数：

count(字段)：统计该字段非空值的个数

5、和分组函数一同查询的字段，必须是group by 后出现的字段

关于分组前的筛选和分组后的筛选

使用关键字筛选的表位置

分组前的筛选 where 原始表 group by 前面

分组后的筛选 having 筛选后的结果 group by 后面

二、sql 99 标准

【语法】：

select 查询列表

from 表1 别名 [连接类型]

join 表2 别名

on 连接条件

where 过滤条件

group by 分组

having 分组后筛选

order by 排序

【连接类型分类】：

内连接：inner

外连接：

左外：left [outer]

右外：right [outer]

全外：full [outer]

交叉连接：cross

【特点】：

①不写连接类型关键字的话，默认使用的是inner连接，内连接的结果等于多表的交集，n表连接至少需要n-1个连接条件

②筛选条件放在where后面，连接条件放在on后面，提高分离性，提高可读性

③inner连接与sql92语法中的等值连接效果一样

④外连接查询一般用于查询除了交集部分的剩余的不匹配的行

内连接

【练习】

#查询员工名、部门名
SELECT last_name, department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;

#查询名字中包含a的员工名和工种名（筛选）
SELECT last_name, job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE last_name LIKE '%e%';

#查询部门个数>3的城市名和部门个数（分组+筛选）
SELECT city, COUNT(*) FROM departments d INNER JOIN locations l ON d.location_id = l.location_id GROUP BY city HAVING COUNT(*) > 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;

#查询员工名、部门名、工种名，并按部门名降序（三表连接的话，需要考虑连接顺序是否能保证有结果）
SELECT last_name, department_name, job_title FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN jobs j ON e.job_id = j.job_id ORDER BY department_name DESC;

非等值连接

#查询员工的工资级别
SELECT last_name, salary, grade_level FROM employees e INNER JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal HAVING grade_level = 'E';

#查询工资级别对应的员工个数>20的工资级别和对应的员工数量，并按工资级别降序排列
SELECT grade_level, COUNT(grade_level) FROM employees e INNER JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.h;

自连接

#查询员工的名字和对应的上级的名字，按员工的名字升序排列
SELECT e.last_name '员工', m.last_name '领导' FROM employees e JOIN employees m ON e.manager_id = m.employee_id ORDER BY e.last_name ASC;

【外连接一般用于查询一个表中有，另一个表中没有的记录】

【特点】：

1、外连接查询的结果为主表中的所有记录

如果从表中有和它匹配的，则显示匹配的值

如果从表中没有和它匹配的，则显示为 null

外连接查询结果=内连接结果+主表中有从表中没有的记录

2、左外连接中，left join 左边的是主表，

右外连接中，right join 右边的是主表

3、左外和右外交换两个表的顺序，可以实现同样的效果

4、全外连接=内连接+表1有但表2没有的部分+表2有但表1没有的部分

左外连接

【练习】：

#查询男朋友不在男神表（boys）的女神名（beauty）

SELECT bea.name, bo.id, bo.boyName FROM beauty bea LEFT JOIN boys bo ON bea.boyfriend_id = bo.id WHERE bo.id IS NULL;

#查询女朋友不在女神表（beauty）的男神名（boys）

SELECT bo.*, bea.* FROM boys bo LEFT JOIN beauty bea ON bo.id = bea.boyfriend_id WHERE bea.id IS NULL;

#查询哪个部门没有员工
SELECT department_name, e.* FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id IS NULL GROUP BY department_name;

右外连接

#查询男朋友不在男神表（boys）的女神名（beauty）

SELECT bea.name, bo.id, bo.boyName FROM boys bo RIGHT JOIN beauty bea ON bea.boyfriend_id = bo.id WHERE bo.id IS NULL;

全外连接

【内连接、左外、右外、全连接的抽象理解】：使用文氏图，内连接为A与B的交集，左外为A圆，右外为B圆，全连接为A与B的并集

交叉连接

SELECT bo.*, b.* FROM beauty b CROSS JOIN boys bo；

交叉连接的结果实际就是两个表的笛卡尔积

【SQL92与QSL99的对比】

1、功能：SQL99支持的较多

2、可读：SQL99实现连接条件与筛选条件分离，可读性更好

【练习】：

#查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，则用null填充
SELECT be.name, b.* FROM beauty be LEFT JOIN boys b ON b.id = be.boyfriend_id WHERE be.id >3;

#查询哪个城市没有部门
SELECT l.city, d.* FROM locations l LEFT JOIN departments d ON d.location_id = l.location_id WHERE d.department_id IS NULL;

#查询部门名为SAL或IT的员工信息

【这里部门名为 IT 的部门总共有两个，其中两个没有对应的员工信息】
SELECT d.department_id, d.department_name, e.* FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE d.department_name IN ('SAL','IT');

子查询

【含义】：出现在其他语句中的 SELECT 语句，成为子查询（也叫内查询），内部嵌套其他SELECT语句的查询，称为外查询或主查询

分类：

1、按子查询出现的位置

SELECT后面

仅支持标量子查询

FROM后面

支持表子查询

WHERE或HAVING后面 ★

标量子查询 ✔

列子查询 ✔

行子查询

EXISTS后面

一、放在WHERE 或 HAVING 后面

1、标量子查询（单行子查询）

2、列子查询（多行子查询）

3、行子查询（多列多行）

特点：

①子查询放在小括号内

②子查询一般放在条件的右侧

③标量子查询一般搭配单行操作符使用（> < >= <= <>）

④列子查询，一般搭配多行操作符使用（IN、SOME/ANY、ALL）

标量子查询

【案例】

#1、谁的工资比Abel高？

SELECT * FROM employees e WHERE e.salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

#2、查询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');

#3、查询工资最少的员工的last_name，job_id，salary
SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

#4、查询最低工资大于（部门编号为50的部门最低工资）的部门ID和其最低工资
①查询所有的部门ID与其最低工资
SELECT e.department_id, MIN(salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY e.department_id;
②查询编号为50的部门的最低工资
SELECT MIN(salary) FROM employees WHERE department_id = '50';
③在①的基础上，使用HAVING筛选条件②
SELECT e.department_id, MIN(salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = '50');

列自查询（多行子查询）

any：子查询的任意值， > any(子查询) 等同于 > min(子查询)

all：子查询的所有值， > all(子查询) 等同于 > max(子查询)

【练习】：

#1、查询location_id 是1400或1700的部门中的所有员工姓名
①查询location_id 是1400或1700的部门ID
SELECT location_id FROM departments d WHERE d.location_id IN ('1400', '1700');
②查询部门编号是①列表中的所有部门对应的员工姓名
SELECT last_name FROM employees WHERE department_id IN (SELECT department_id FROM departments d WHERE d.location_id IN ('1400', '1700'));

#2、查询其他工种中比 job_id为'IT_PROG'部门任一工资低的员工的：工号、姓名、job_id以及salary
#①查询job_id为'IT_PROG'部门任一工资
SELECT salary FROM employees WHERE job_id = 'IT_PROG';
#②查询工号、姓名、job_id以及salary，增加筛选条件：salary < (①)的任意一个，使用 ANY
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY(SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

#3、查询其他工种中比 job_id为'IT_PROG'部门所有工资低的员工的：工号、姓名、job_id以及salary
#①查询job_id为'IT_PROG'部门所有工资
SELECT salary FROM employees WHERE job_id = 'IT_PROG';
#②查询工号、姓名、job_id以及salary，增加筛选条件：salary < (①)的所有，使用 ALL
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL(SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

行子查询（结果集为一行多列或多行多列）

【案例】

#1、查询员工编号最小且工资最高的员工信息
#①查询员工最小编号
SELECT MIN(employee_id) FROM employees;
#②查询最高工资
SELECT MAX(salary) FROM employees;
#③查询员工信息，过滤条件为①和②
SELECT * FROM employees WHERE employee_id = (SELECT MIN(employee_id) FROM employees) AND salary = (SELECT MAX(salary) FROM employees);
#使用行自查询实现（必须多个筛选条件都是 = 连接）
SELECT * FROM employees WHERE (employee_id, salary) = (SELECT MIN(employee_id), MAX(salary) FROM employees);

二、放在WHERE 或 HAVING 后面

【注意】：仅支持标量自查询

【案例】

#1、查询每个部门的员工个数
SELECT d.*, (
SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id
) AS '员工个数'
FROM departments d;

#2、查询员工号=102的部门名
SELECT (
SELECT department_name FROM departments d
WHERE d.department_id = e.department_id
) FROM employees e WHERE e.employee_id = 102;

三、放在 FROM 后面

【注意】：将自查询的结果充当一张表，必须取别名

【案例】

#查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary) FROM employees GROUP BY department_id;
#②将①的结果集作为查询的表，查询平均工资对应的等级
SELECT grade_level, s.`平均工资` FROM (SELECT AVG(salary) AS '平均工资' FROM employees GROUP BY department_id) s, job_grades j WHERE s.`平均工资` BETWEEN lowest_sal AND highest_sal;

四、放在 EXISTS 后面（相关自查询）

【语法】：EXISTS（完整的查询语句）

【结果】：1或者0

【案例】

#1、查询有员工的部门名
#①查询所有的部门名
SELECT department_name FROM departments;
#②在①的基础上，筛选有员工的部门名
SELECT department_name FROM departments d WHERE EXISTS
(
SELECT * FROM employees e WHERE e.department_id = d.department_id
);
#用IN来实现
SELECT department_name FROM departments WHERE department_id IN (SELECT department_id FROM employees);

#2、查询没有女朋友的男神信息
#用 IN 实现
SELECT * FROM boys bo WHERE bo.id NOT IN (SELECT boyfriend_id FROM beauty);
#用 EXISTS 实现
SELECT * FROM boys bo WHERE NOT EXISTS (SELECT boyfriend_id FROM beauty bea WHERE bo.id = bea.boyfriend_id);

【小结练习】

#1、查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name, salary FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Zlotkey');

#2、查询工资比平均工资高的员工的员工号，姓名和工资
SELECT employee_id, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

#3、查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
#①查询各部门的平均工资
SELECT AVG(salary), department_id FROM employees GROUP BY department_id;
#②将①的结果集作为表，与employees e2 进行内连接，连接条件为 e.department_id = ①.department_id，同时，通过salary进行筛选过滤
SELECT employee_id, last_name, salary FROM employees e1 INNER JOIN (SELECT AVG(salary) ag, department_id FROM employees GROUP BY department_id) e2 ON e1.department_id = e2.department_id WHERE e1.salary > e2.ag;
#也可以用如下的方式实现
SELECT employee_id, last_name, salary, department_id FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department_id = e2.department_id GROUP BY e2.department_id );

#4、查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名（注意用 DISTINCT 去重）
SELECT employee_id, last_name FROM employees WHERE department_id IN (SELECT DISTINCT(department_id) FROM employees WHERE last_name LIKE '%u%');

#5、查询在部门的location_id为1700的部门工作的员工的员工号和姓名
SELECT employee_id, last_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = '1700');

#6、查询管理者是K_ing的员工姓名和工资
SELECT last_name, salary FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE last_name = 'K_ing');

#7、查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 '姓名'
SELECT CONCAT(last_name,first_name) AS '姓.名' FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

分页查询

【应用场景】：当要显示的数据，一页显示不全，需要分页提交SQL查询

【语法】：

SELECT 查询列表 ⑦

FROM 表1 ①

连接类型 JOIN 表2 ②

ON 连接条件 ③

WHERE 过滤条件 ④

GROUP BY 分组字段 ⑤

HAVING 分组后筛选 ⑥

ORDER BY 排序的字段 ⑧

LIMIT OFFSET，SIZE； ⑨

【执行顺序】：FROM --> JOIN --> ON --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY --> LIMIT

【OFFSET】：要显示条目的起始索引（从0开始），也叫偏移量

【SIZE】：要显示的条目个数

【特点】：

1、LIMIT 语句放在查询语句的最后

2、公式：假设要显示的页数为 page，每页显示的数量为 size，offset = (page-1)*size

SELECT 查询列表 FROM 表 LIMIT (page-1)*size，size

【练习】

#查询前五条员工信息
SELECT * FROM employees LIMIT 0, 5;

#查询有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 0,10;

【课程小结练习】

#1、查询工资最低的的员工信息
SELECT * FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

#2、查询平均工资最低的部门信息
SELECT * FROM departments WHERE department_id = (SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1);

#3、查询平均工资最低的部门信息和该部门的平均工资
#①查询平均工资最低的部门ID和平均工资，结果集为表1
SELECT AVG(salary) ag, department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1;
#②查询所有部门的部门信息，结果集为表2
SELECT * FROM departments;
#③表1与表2连接，连接条件为 department_id
SELECT d1.*, d2.ag AS '平均工资' FROM departments d1, (SELECT AVG(salary) ag, department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1) d2 WHERE d1.department_id = d2.department_id;

#4、查询平均工资最高的job信息
#①查询平均工资最高的job_id
SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1
#②查询job信息，job_id =①
SELECT * FROM jobs WHERE job_id = (SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1)

#5、查询平均工资高于公司平均工资的部门有哪些
#①查询每个部门的department_id，筛选出大于公司平均工资的
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) > (SELECT MIN(salary) FROM employees);
#查询department_id IN(①)的部门信息
SELECT * FROM departments WHERE department_id IN (
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) > (SELECT MIN(salary) FROM employees)
);

#6、查询公司所有manager的详细信息
SELECT * FROM employees WHERE employee_id IN (SELECT DISTINCT(manager_id) FROM employees);

#7、各个部门中，最高工资最低的部门是哪个？其最低工资是多少
#①查询各个部门的最高工资,最低工资和部门编号
SELECT MAX(salary) max, MIN(salary) min, department_id FROM employees GROUP BY department_id;
#②将部门表与①进行表连接，连接条件为department_id，并通过排序找出部门最高工资中最低的一行
SELECT d.*, s.min AS '部门最低工资' FROM departments d, (SELECT MAX(salary) max, MIN(salary) min, department_id FROM employees GROUP BY department_id) s WHERE d.department_id = s.department_id ORDER BY s.max ASC LIMIT 1;

#8、查询平均工资最高的部门的manager的详细信息：last_name, department_id, email, salary
#①查询平均工资最高的部门编号
SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1
#②查询所有的manager信息
SELECT last_name, e.department_id, email, salary FROM employees e INNER JOIN departments d ON e.employee_id = d.manager_id;
#在②的基础上，过滤出department_id = ①的结果
SELECT last_name, e.department_id, email, salary FROM employees e INNER JOIN departments d ON e.employee_id = d.manager_id WHERE e.department_id = (SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1 ) AND employee_id IN (SELECT DISTINCT(manager_id) FROM employees);

union联合查询

将多表查询语句的结果合并成一个结果（类似于文氏图中的并集）

【语法】：

select 查询语句1

union join

查询语句2 union

......

【案例】：#查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE department_id > 90 UNION SELECT * FROM employees WHERE email LIKE '%a%';

【应用场景】：要查询的结果来自多个表，且多个表之间没有直接的连接关系，但查询的信息一致。

【注意事项】：

1、多条查询语句查询的列数必须一致

2、每一列的类型和顺序最好一致（可读性好）

3、union默认去掉重复项。使用union all可以包含重复项

【意义】：适用于将复杂的查询拆分成多条语句

DQL总结

【语法模板】

SELECT 查询列表 ⑦

FROM 表1 别名 ①

连接类型 JOIN 表2 别名 ②

ON 连接条件 ③

WHERE 筛选条件 ④

GROUP BY 分组函数 ⑤

HAVING 分组后的筛选条件 ⑥

ORDER BY 排序列表 ⑧

LIMIT 起始条目索引，条目数； ⑨

【执行顺序】：①到⑨

Nandeska

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL教程基础篇（三）：DQL语言

DQL：Data Query Language首先，创建数据表，可通过如下链接下载：https://pan.baidu.com/s/1nM2-xSsTnPqQXdwXCvHrSw 提取码：kqed或者如下链接：https://pan.baidu.com/s/1NdoPCx2eBkL_EcCfTChTdg提取码：jhsn或者查看这篇博文：https://blog.csdn.net/GongmissYan/article/details/102937816创建的四张表如下：mysql...
复制链接

扫一扫