02 查询与函数

SQL 的分类

DDL 数据定义语言。CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE

DML 数据操作语言。INSERT \ DELETE \ UPDATE \ SELECT

DCL 数据控制语言。COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

SQL 语言的基本规则

• SQL 可以写在一行或者多行，为了提高可读性，各子句子分行写，必要时使用缩进
• 每条命令以 ; 或者 \g 或者 \G 结束
• 关键字不能被缩写也不能被分行
• 关于标点符号
  • 必须保证所有 ()、单引号、双引号成对出现
  • 必须使用英文状态下的输入方式
  • 字符串和日期类型的数据可以使用单引号表示
  • 列的别名，尽量使用双引号，而且不建议省略as

SQL 大小写规范

• 数据库名、表名、表别名、字段名、字段别名等使用小写
• SQL 关键字、函数名、绑定变量等使用大写

MySQL三种注释方式

-- 单行注释,使用--单行注释时要加空格

# 单行注释

/*
	多行注释
	多行注释
*/

导入数据

在命令行窗口登录 mysql，执行如下命令

source d:\xxx.sql

SELECT … FROM 结构

-- 查看 employee 表中的所有列数据，* 表示所有列或者字段
SELECT * FROM employees;

-- 查看 employees 表中的 employee_id last_name,salary 三列数据
SELECT employee_id,last_name,salary
FROM employees;

列起别名

-- 列的别名，使用空格隔开或者使用as
-- 列的别名可以使用中文表示，但是要使用双引号
SELECT employee_id AS id,last_name as "姓名"
FROM employees;

去除重复项

-- 去除重复项 DISTINCT
SELECT DISTINCT department_id FROM employees;

空值参与运算

-- 空值参与运算
-- null 值参与运算的话结果永远是 null
SELECT employee_id,(salary * (1 + commission_pct) * 12) AS "年工资" 
FROM employees;

IFNULL 解决空值

-- 空值解决办法 IFNULL(expr1,expr2),如果 expr1 为null，则取 expr2 的值参与运算
SELECT employee_id,(salary * (1 + IFNULL(commission_pct,0)) * 12) AS "年工资"
FROM employees;

着重号

-- 着重号 ``,如果自己定义的字段和系统关键字重复，可以使用 `` 将我们定义的字段括起来区分
SELECT * FROM `order`;

查询常数

-- 查询常数
SELECT "songzx",employee_id,last_name FROM employees;

DESC 显示表结构

-- 显示表结构
DESC employees;

WHERE 查询

查询 employees 表中 last_name 等于 King 的数据

SELECT * FROM employees WHERE last_name = 'King';

SQL 算数运算符

符号	作用
+	加
-	减
*	乘
/	除
%	取模

简单示例

SELECT 110,100+50,100*2,100+12.3 FROM DUAL;

SQL 中的 + 没有连接的作用，只有加法的作用，下面的结果是 112

SELECT 100+'12' FROM DUAL;

如果加法不能正常计算，则按照0处理，如下结果是 100

SELECT 100 + 'a' FROM DUAL;

SQL中的除法得出的结果默认保留四位小数

SELECT 100/2,50/5 FROM DUAL;

% 取模运算

SELECT 12%5,100%10,13%-2,-12%5,-12%-5 FROM DUAL;

练习：查询员工id为偶数的数据

SELECT employee_id,last_name,salary 
FROM employees
WHERE employee_id % 2 = 0;

SQL 比较运算符

符号	作用
=	等于
<=>	安全的等于，为 NULL 而生
<> 或者 !=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于

普通等于如果一边有NULL,则结果就是NULL

结果为: 1 1 NULL

SELECT 1 = '1',0 = 'a',2 = NULL
FROM DUAL;

安全等于如果一边有null结果不会返回null

结果为：0 1

SELECT 1 <=> NULL,NULL <=> NULL
FROM DUAL;

其他比较符号如果有 NULL 参与返回的结果都是 NULL

结果为 1 0 0

SELECT 1 != 2,0 != 'a',1 != 1
FROM DUAL;

大于等于判断

结果为 1 1 1 1

SELECT 2 > 1,5 >= 5,1 < 2,1 <= 1
FROM DUAL;

IS NULL 和 ISNULL(expr) 的使用

查询为NULL的数据

-- 查询表中字段是 NULL 的数据
SELECT employee_id,last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
-- 或
SELECT employee_id,last_name,commission_pct
FROM employees
WHERE ISNULL(commission_pct);

IS NOT NULL 的使用

查询不是 NULL 的数据

-- 查询表中字段不是 NULL 的数据
SELECT employee_id,last_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

LEAST 和 GREATEST

最小 LEAST 和最大 GREATEST

-- LEAST(value1,value2,...) 和 GREATEST(value1,value2,...)
SELECT LEAST('d','h','a'),GREATEST('d','h','a')
FROM DUAL;

BETWEEN … AND

查询包含在某个区间的数据

练习1：查询员工工资在6000到8000之间的数据,包含6000和8000

SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
-- 或
SELECT employee_id,last_name,salary
FROM employees
WHERE salary >= 6000 AND salary <= 8000;

练习2：查询员工工资不在6000到8000的数据

SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;

IN(val1,val2,…) 和 NOT IN(val1,val2,…)

查询在指定数据内的数据

练习1：查询员工部门在10 20 30的数据

SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id IN(10,20,30);

练习2：查询员工部门不在10 20 30的数据

SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id NOT IN(10,20,30);

LIKE 模糊查询

练习1：查询名字中只要包含a的数据

% 代表不确定个数的字符

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '%a%';

练习2：查询名字以a开头的数据

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE 'a%';

练习3：查询名字以a结尾的数据

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '%a';

练习4：查询名字第二位是a的数据

_ 表示一个不确定的字符

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '_a%';

练习5：查询名字中有a并且有e的数据

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
-- 或
SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

练习6：查询名字第二位是_且第三位是a的员工数据

\ 表示一个转义符

SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '_\_a%';

REGEXP

使用正则表达式

SELECT 'szxstart' REGEXP '^s','szxstart' REGEXP 't$'
FROM DUAL;

逻辑运算符

符号	作用
OR 或 ||	或者
NOT 或 !	非
AND 或 &&	并且
XOR	逻辑异或，前后一真一假才算真

运算练习题

-- 1.查询工资不在5000到12000的员工的姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

-- 2.查询在20或50号部门的员工姓名和部门号 
SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id in (20,50);

-- 3.查询公司中没有管理者的员工姓名及jobid
SELECT employee_id,last_name,job_id
FROM employees
WHERE manager_id IS NULL;
 
-- 4.查询公司中有奖金的姓名，工资，奖金级别 
SELECT employee_id,last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

-- 5.查询员工姓名的第三个字母是a的数据 
SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '__a%';

-- 6.查询姓名中有 a 和 k 的数据 
SELECT employee_id,last_name 
FROM employees
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';

-- 7.查询出employees表中first_name 以 e 结尾的数据 
SELECT employee_id,first_name
FROM employees
WHERE first_name LIKE '%e';
 
-- 8.查询部门编号在 80-100 之间的姓名、工种 
SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

-- 9.查询出 manager_id 是 100,101,110的姓名，工资，管理者id 
SELECT employee_id,last_name,salary,manager_id
FROM employees
WHERE manager_id in (100,101,110);

ORDER BY 排序

排序规则

使用关键字 ORDER BY 进行排序。排序方式有两种

• ASC 升序排序，默认采用这个方式
• DESC 降序排序

单列排序

1.按照员工工资升序排列

SELECT
	* 
FROM
	employees 
ORDER BY
	salary;

2.找出员工工资在6000-9000并按照降序排列

SELECT
	employee_id,
	last_name,
	salary 
FROM
	employees 
WHERE
	salary BETWEEN 6000 AND 9000 
ORDER BY
	salary DESC;

多列排序

找出员工部门在60-80的按照降序排列，并且部门之间员工工资按照升序排列

SELECT
	employee_id,
	last_name,
	salary,
	department_id 
FROM
	employees 
WHERE
	department_id BETWEEN 60 AND 80 
ORDER BY
	department_id DESC,
	salary;

LIMIT 分页

使用 LIMIT 关键字进行排序

1.获取员工表第1页数据，每页显示20条

SELECT *
FROM employees
LIMIT 0,20;

2.获取员工表第2页数据，每页显示20条

SELECT *
FROM employees
LIMIT 20,20;

3.获取员工表第3页数据，每页显示20

总结公式：LIMIT (pageNum-1)*pageSize,pageNum

SELECT *
FROM employees
LIMIT 20,20;

4.WHERE,ORDER BY,LIMIT声明顺序

查询员工工资在6000到8000的数据按照工资降序排序显示前20条

SELECT
	employee_id,
	last_name,
	salary 
FROM
	employees 
WHERE
	salary BETWEEN 6000 AND 8000 
ORDER BY
	salary DESC 
LIMIT 20;

排序与分页练习

-- 1.查询员工的姓名和部门号以及年薪。按年薪降序，姓名升序显示
SELECT employee_id,last_name,salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC,last_name;

-- 2.选择工资不在8000到17000的员工姓名和工资，按工资降序，显示第21到40的位置
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;

-- 3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT last_name,email,department_id
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id;

多表查询

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

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

两张表关联查询

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

如果要查询的字段在多张表中都存在，则必须指明这个字段来自那个表

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

表的名字过长可以给表起别名，如果给表起了别名就必须使用别名。建议多表查询时给要显示的字段都指明来自那张表，减少 SQL 判断次数，提高查询效率

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

多表查询

多表查询使用 AND 关联

SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_id,
	t3.city 
FROM
	employees t1,
	departments t2,
	locations t3 
WHERE
	t1.department_id = t2.department_id 
	AND t2.location_id = t3.location_id

多表查询的分类

• 等值连接 vs 非等值连接
• 自连接 vs 非自连接
• 内连接 vs 外连接

等值连接 vs 非等值连接

等值连接，查询出员工的具体部门名称

SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1,
	departments t2 
WHERE
	t1.department_id = t2.department_id;

非等值连接，查询员工的工资等级

SELECT
	t1.employee_id,
	t1.last_name,
	t1.salary,
	t2.grade_level 
FROM
	employees t1,
	job_grades t2 
WHERE
	t1.salary BETWEEN t2.lowest_sal 
	AND t2.highest_sal;
	AND t2.highest_sal;

自连接 vs 非自连接

自连接。查询员工姓名已经管理者,让员工的领导id等于领导人的empid

SELECT
	t1.employee_id,
	t1.last_name,
	t2.manager_id,
	t2.last_name 
FROM
	employees t1,
	employees t2 
WHERE
	t1.manager_id = t2.employee_id;

内连接 vs 外连接

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

• 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行

• 外连接的分类：

  • 左外连接：两个表在连接过程中除了返回满足连接条件之外的数据外还返回了左表中不满足条件的数据
  • 右外连接：两个表在连接过程中除了返回满足连接条件之外的数据外还返回了右表中不满足条件的数据
  • 满外连接：两个表在连接过程中除了返回满足连接条件之外的数据外还返回了左表和右表中不满足条件的数据

SQL99语法使用JOIN ON实现内外连接

上述的写法都是SQL92的语法，语法更简洁但不易理解。SQL99语法更复杂但是更容易理解。

在 MySQL 中不支持 SQL92 语法。

使用 JOIN ON 关键字实现内外连接

SQL99内连接

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

SQL99多表内链接

SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name,
	t3.city 
FROM
	employees t1
	JOIN departments t2 ON t1.department_id = t2.department_id
	JOIN locations t3 ON t2.location_id = t3.location_id;

SQL99实现左外连接 LEFT JOIN

SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	LEFT JOIN departments t2 ON t1.department_id = t2.department_id;

SQL99实现右外连接 RIGHT JOIN

SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	RIGHT JOIN departments t2 ON t1.department_id = t2.department_id;

UNION ALL 和 UNION

UNION ALL 将两个查询语句合并为一个查询语句并输出，不会去除重复项。效率高，推荐使用。

UNION 将两个查询语句合并为一个查询语句，会去除重复项。效率低，不推荐使用

七种JOIN的实现

左上图

SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	LEFT JOIN departments t2 ON t1.department_id = t2.department_id;

右上图

SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	RIGHT JOIN departments t2 ON t1.department_id = t2.department_id;

左中图

SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	LEFT JOIN departments t2 ON t1.department_id = t2.department_id 
WHERE
	t1.department_id IS NULL;

中图

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

右中图

SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	RIGHT JOIN departments t2 ON t1.department_id = t2.department_id 
WHERE
	t1.employee_id IS NULL;

左下图

-- 方式一：左上图 UNION ALL 右中图
SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	LEFT JOIN departments t2 ON t1.department_id = t2.department_id UNION ALL
SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	RIGHT JOIN departments t2 ON t1.department_id = t2.department_id 
WHERE
	t1.employee_id IS NULL;
	
-- 方式二：右上图 UNION ALL 左中图
SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	RIGHT JOIN departments t2 ON t1.department_id = t2.department_id UNION ALL
SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	LEFT JOIN departments t2 ON t1.department_id = t2.department_id 
WHERE
	t1.department_id IS NULL;

右下图

SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	LEFT JOIN departments t2 ON t1.department_id = t2.department_id 
WHERE
	t1.department_id IS NULL UNION ALL
SELECT
	t1.employee_id,
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	RIGHT JOIN departments t2 ON t1.department_id = t2.department_id 
WHERE
	t1.employee_id IS NULL;

多表内外连接查询练习

现有如下两张表

人员表：t_emp

门派表：t_dept

1.所有有门派的人员信息

SELECT
	t1.`name`,
	t2.deptName 
FROM
	t_emp t1
	JOIN t_dept t2 ON t1.deptId = t2.id;

2.列出所有用户，并显示其机构信息

SELECT
	t1.`name`,
	t2.deptName 
FROM
	t_emp t1
	LEFT JOIN t_dept t2 ON t1.deptId = t2.id;

3.列出所有门派

SELECT
	t1.`name`,
	t2.deptName 
FROM
	t_emp t1
	RIGHT JOIN t_dept t2 ON t1.deptId = t2.id;

4.所有不入门派的人员

SELECT
	t1.`name`,
	t2.deptName 
FROM
	t_emp t1
	LEFT JOIN t_dept t2 ON t1.deptId = t2.id 
WHERE
	t1.deptId IS NULL;

5.所有没人入的门派

SELECT
	t1.`name`,
	t2.deptName 
FROM
	t_emp t1
	RIGHT JOIN t_dept t2 ON t1.deptId = t2.id 
WHERE
	t1.deptId IS NULL;

6.列出所有人员和机构的对照关系

SELECT
	t1.`name`,
	t2.deptName 
FROM
	t_emp t1
	LEFT JOIN t_dept t2 ON t1.deptId = t2.id UNION ALL
SELECT
	t1.`name`,
	t2.deptName 
FROM
	t_emp t1
	RIGHT JOIN t_dept t2 ON t1.deptId = t2.id 
WHERE
	t1.deptId IS NULL;

7.列出所有没入派的人员和没人入的门派

SELECT
	t1.`name`,
	t2.deptName 
FROM
	t_emp t1
	LEFT JOIN t_dept t2 ON t1.deptId = t2.id 
WHERE
	t1.deptId IS NULL UNION ALL
SELECT
	t1.`name`,
	t2.deptName 
FROM
	t_emp t1
	RIGHT JOIN t_dept t2 ON t1.deptId = t2.id 
WHERE
	t1.deptId IS NULL;

扩展：USING 的使用

如果两张表中有形同的字段，可以使用 USING 字段声明让那个字段进行等值判断

JOIN ON 的写法

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

USING 写法。查询结果与上面相同

SELECT
	t1.last_name,
	t2.department_name 
FROM
	employees t1
	JOIN departments t2 USING ( department_id );

单行函数

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

数值函数

基本函数

三角函数

指数和对数

进制之间的转换

字符串函数

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

日期和时间函数

获取日期时间

日期和时间戳转换

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

日期的操作函数

时间和秒钟转换的函数

计算日期和时间的函数

日期格式化与解析

流程控制函数

IF(expr1,expr2,expr3)

如果expr1的值为true，则返回expr2的值，否则返回expr3

练习1：返回员工的姓名，工资，奖金，如果奖金为null则返回0

SELECT
	employee_id,
	last_name,
	salary,
IF
	( commission_pct, commission_pct, 0 ) 
FROM
	employees;

IFNULL(expr1,expr2)

如果expr1为true则返回expr1，否则返回expr

练习：返回员工的姓名，工资，奖金，如果奖金为null则返回0

SELECT
	last_name,
	salary,
	IFNULL( commission_pct, 0 ) 
FROM
	employees;

CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 结果3 END

相当于java中的if else if else

练习：输出不同工资对应的等级

SELECT
	last_name,
	salary,
CASE
		
		WHEN salary >= 12000 THEN
		'白骨精' 
		WHEN salary >= 10000 THEN
		'高富帅' 
		WHEN salary >= 8000 THEN
		'小白领' ELSE '加油' 
	END 'details'
	FROM
		employees;

CASE 条件 WHEN 常量1 THEN 结果1 WHEN 常量2 THEN 结果2 ELSE 结果n END

相当于Java中的switch case

练习：如果部门为30号，则工资乘以1.1倍，如果部门为40，工资乘以1.2，部门为50，工资乘以1.4，其他部门工资不变

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.4 ELSE salary * 1
	END 'detail'
	FROM
		employees;

数据加密

MD5(str)

MD5(str) 将一个字符串进行md5加密

SELECT MD5('mysql') FROM DUAL;

SHA(str)

将一个字符串进行sha加密

SELECT SHA('mysql') FROM DUAL;

函数练习题

1.显示系统时间

SELECT NOW() FROM DUAL;

2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

SELECT
	t1.employee_id,
	t1.last_name,
	t1.salary,
	salary * 1.2 'new salary' 
FROM
	employees t1;

3.将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT
	last_name,
	LENGTH( last_name ) 
FROM
	employees 
ORDER BY
	last_name;

4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT

SELECT
	CONCAT( employee_id, '-', last_name, '-', salary ) AS 'OUT_PUT' 
FROM
	employees;

5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序

SELECT
	last_name,
	EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM hire_date) AS 'job_year',
	DATEDIFF(NOW(),hire_date) AS 'job_day'
FROM
	employees
ORDER BY job_year DESC;

6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空

SELECT
	last_name,
	department_id,
	hire_date 
FROM
	employees 
WHERE
	EXTRACT( YEAR FROM hire_date ) >= 1997 
	AND department_id IN ( 80, 90, 110 )
	AND commission_pct IS NOT NULL;

7.查询公司中入职超过10000天的员工姓名、入职时间

SELECT
	last_name,
	department_id,
	hire_date,
	DATEDIFF( NOW(), hire_date ) AS "job_days"
FROM
	employees 
WHERE
	DATEDIFF( NOW(), hire_date ) > 10000
ORDER BY job_days DESC;

8.根据不同的工种打印出不同的等级

SELECT
  last_name,
	job_id,
CASE
		job_id 
		WHEN 'AD_PRES' THEN
		'A' 
		WHEN 'ST_MAN' THEN
		'B' 
		WHEN 'IT_PROG' THEN
		'C' 
		WHEN 'SA_REP' THEN
		'D' 
		WHEN 'ST_CLERK' THEN
		'E' 
	END "Grade" 
FROM
	employees 
WHERE
	job_id IN ( 'AD_PRES', 'ST_MAN', 'IT_PROG', 'SA_REP', 'ST_CLERK' ) 
ORDER BY
	Grade;

聚合函数

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

AVG 求平均值

SELECT AVG(salary) FROM employees;

SUM 求总和

SELECT SUM(salary) FROM employees;

MAX 最大值

SELECT MAX(salary) FROM employees;

MIN 最小值

SELECT MIN(salary) FROM employees

MAX 和 MIN 也可以对字符串，日期等格式的数据进行比较

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

COUNT 计算总和

COUNT 计算字段的总和，不会考虑null值，如果字段为null则不会计算到总和中

SELECT COUNT(*) AS 'LimitSize' FROM employees;

练习：计算员工的奖金平均率

-- 错误的：因为avg也不会考虑null值，如果员工的commission_pct为null就不会参与到平均值的计算
SELECT AVG(commission_pct) FROM employees;


-- 正确的：计算平均值时先判断这个数据为null，如果是null则用0去参与平均值计算
SELECT AVG(IFNULL(commission_pct,0)) FROM employees;
-- 或者
SELECT SUM(commission_pct) / COUNT(*) FROM employees;

GROUP BY 分组

按照公司的部门进行分组，求出每个部门的平均工资

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

按照公司部门进行分组，求出每个部门的平均工资

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

WITH ROLLUP 的使用

WITH ROLLUP 关键字会在查询出来的基础上之后对查询出来的数据再来一次分组，并求出平均值

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

• 结论1：SELECT 中出现的非组函数的字段必须声明在 GROUP BY 中，反之，GROUP BY 中声明的字段可以不出现在 SELECT 中
• 结论2：GROUP BY 声明在 FROM 后面，WHERE 后面，ORDER BY 前面，LIMIT 前面
• 结论3：使用 WITH ROLLUP 关键字后不能进行排序操作，因为数据之间互斥

HAVING 的使用

当过滤条件中存在聚合函数时，使用 HANING 来添加过滤

例子：查询10,20,30,40这四个部门中的最高工资大于10000的部门有哪些

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

结论：

• 当过滤条件中有聚合函数时，则这个过滤条件必须声明在 HAVING 中
• 当过滤条件中没有聚合函数时，则这个过滤条件声明在 WHERE 中，HAVING 中也可以声明，但是不推荐，效率不高

WHERE 和 HAVING 的对比：

• 从适用范围来讲，HAVING 的使用范围更广
• 如果过滤条件中没有聚合函数，WHERE 的执行效率要更高

SQL 语句的执行过程

FROM ... (LEFT / RIGHT) JOIN ... ON ... WHERE ... GROUP BY ... HAVING ... SELECT ... DISTINCT ... ORDER BY ... LIMIT

聚合函数练习

1.where子句可否使用组函数进行过滤?

答案：不可以

2.查询公司员工工资的最大值，最小值，平均值，总和

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

3.查询各job_id的员工工资的最大值，最小值，平均值，总和

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

4.查询各个job_id下的员工人数

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

5.查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT
	MAX( salary ) - MIN( salary ) AS "DIFFERENCE" 
FROM
	employees;

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

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

7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

SELECT
	t2.department_name,
	t2.location_id,
	COUNT( t1.employee_id ),
	AVG( t1.salary ) AS "avg_salary"
FROM
	employees t1
	RIGHT JOIN departments t2 ON t1.department_id = t2.department_id 
GROUP BY
	t2.department_name,t2.location_id
ORDER BY
	avg_salary DESC;

8.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT
	t2.department_name,
	t1.job_id,
	MIN( t1.salary ) 
FROM
	employees t1
	RIGHT JOIN departments t2 ON t1.department_id = t2.department_id 
GROUP BY
	t1.job_id,
	t2.department_name;

子查询

子查询也被称为内查询，内查询的先与外查询执行

子查询的分类

从内查询的返回结果数量上看分为：单行子查询 VS 多行子查询。

从内查询是否被执行多次，可以分为：相关子查询 VS 不相关子查询

引入子查询

我们往往在查询一个数据时，可能要根据其他的查询结果来帮助我们查询。例如：查询比Abel工资高的员工。我们处理这道题首先要知道Abel的工资是多少才能查询到比Abel工资高的员工

方式一：分开查询

SELECT salary
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

方式二：使用自关联。不通用

SELECT
	t1.last_name,
	t1.salary 
FROM
	employees t1
	JOIN employees t2 ON t1.salary > t2.salary 
WHERE
	t2.last_name = 'Abel';

方式三：使用子查询的方式在一条SQL语句中完成查询

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

使用一个 () 将另外一个查询语句包起来去比较，这种方式称为子查询。

单行子查询

适用于单行子查询的比较符

操作符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
!=	不等于

查询员工工资大于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号员工的manager_id和department_id相同的其他员工的employee_id， manager_id，department_id

SELECT
	employee_id,
	manager_id,
	department_id 
FROM
	employees 
WHERE
	manager_id = ( SELECT manager_id FROM employees WHERE employee_id = 141 ) 
	AND department_id = ( SELECT department_id FROM employees WHERE employee_id = 141 ) 
	AND employee_id != 141;

查询最低工资大于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 );

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

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

多行子查询

符号	含义
IN	等于列表中的任意一个
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

这道题的解题思路：

1.首先查询出各个部门的平均工资，并且给列起一个别名 avg_salary

2.然后将上面查询出的结果作为一个表，并查询出最小值，FROM 后面也可以是一个子查询，这里要给这个表起一个别名 t_avg_salary

3.最后在外查询中查询每个部门的平均工资，让平均工资等于第二步查出来的最低工资即可得到最终结果

SELECT
	department_id,
	AVG( salary ) 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	AVG( salary ) = ( SELECT MIN( avg_salary ) FROM ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id ) t_avg_salary );

还有第二种方法

1.先查询出所有部门的平均工资

2.然后再外查询中也去查询所有部门的平均工资

3.添加过滤条件，让外查询的平均工资小于等于所有子查询的平均工资，这样会自动定位到最低的一个平均工资，然后正确返回查询结果

SELECT
	department_id,
	AVG( salary ) 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	AVG( salary ) <= ALL ( SELECT AVG( salary ) avg_salary FROM employees GROUP BY department_id );

相关子查询

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

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

-- 方式一：使用相关子查询
SELECT 
	e1.last_name, 
	e1.salary, 
	e1.department_id 
FROM 
	employees e1 
WHERE 
	e1.salary > ( SELECT AVG( salary ) FROM employees e2 WHERE e1.department_id = e2.department_id GROUP BY department_id );

-- 方式二：在 FROM 中使用子查询
SELECT
	e1.last_name,
	e1.salary,
	e1.department_id 
FROM
	employees e1
	JOIN ( SELECT department_id, AVG( salary ) avg_salary FROM employees GROUP BY department_id ) e2 ON e1.department_id = e2.department_id 
WHERE
	e1.salary > e2.avg_salary;

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

-- 方式一：使用自连接
SELECT
	e.employee_id,
	e.salary,
	d.department_name 
FROM
	employees e
	JOIN departments d ON e.department_id = d.department_id 
ORDER BY
	d.department_name;
	
-- 方式二：在 ORDER BY 中使用子查询
SELECT
	e.employee_id,
	e.salary 
FROM
	employees e 
ORDER BY
	( SELECT department_name FROM departments d WHERE e.department_id = department_id );

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

-- 根据外查询获取到当前员工id，去job_history表中查找这个员工id的数量大于等于2的，然后把结果返回
SELECT
	employee_id,
	last_name,
	job_id 
FROM
	employees e 
WHERE
	2 <= ( SELECT COUNT(*) FROM job_history j WHERE e.employee_id = j.employee_id );

EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS 操作符一起使用，用来检查子查询中是否存在满足条件的行

• 如果在子查询中不存在满足条件的行

  • 条件返回false
  • 继续在子查询中查找

• 如果在子查询中存在满足条件的行

  • 不在子查询中继续查找
  • 条件返回true

• NOT EXISTS 关键字表示如果不存在某种条件，则返回true，否则返回false

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

-- 方式一：
SELECT DISTINCT
	e2.employee_id,
	e2.last_name,
	e2.job_id,
	e2.department_id 
FROM
	employees e1
	JOIN employees e2 ON e1.manager_id = e2.employee_id;
	
-- 方式二：
SELECT
	employee_id,
	last_name,
	job_id,
	department_id 
FROM
	employees 
WHERE
	employee_id IN (SELECT DISTINCT manager_id FROM employees);
	
-- 方式三：
SELECT
	e1.employee_id,
	e1.last_name,
	e1.job_id,
	e1.department_id 
FROM
	employees e1 
WHERE
	EXISTS (
	SELECT
		* 
	FROM
		employees e2 
	WHERE
		e2.manager_id = e1.employee_id 
	);

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

-- 方式一：
SELECT
	d.department_name,
	d.department_id 
FROM
	departments d 
WHERE
	NOT EXISTS ( SELECT * FROM employees e WHERE e.department_id = d.department_id );

-- 方式二：
SELECT
	d.department_name,
	d.department_id 
FROM
	employees e
	RIGHT JOIN departments d ON e.department_id = d.department_id 
WHERE
	e.employee_id IS NULL;

子查询练习

#1.查询和Zlotkey相同部门的员工姓名和工资 
SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	department_id IN (
	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.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary 
SELECT
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary > ALL ( SELECT salary FROM employees WHERE job_id = 'SA_MAN' );


#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 
SELECT
	employee_id,
	last_name 
FROM
	employees 
WHERE
	department_id IN ( SELECT 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.查询管理者是King的员工姓名和工资 
SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	manager_id IN ( SELECT employee_id FROM employees WHERE last_name = 'King');


#7.查询工资最低的员工信息: last_name, salary 
SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	salary = ( SELECT MIN(salary) FROM employees );

#8.查询平均工资最低的部门信息

-- 方式一：
SELECT
	* 
FROM
	departments 
WHERE
	department_id = (
	SELECT
		department_id 
	FROM
		employees 
	GROUP BY
		department_id 
	HAVING
		AVG( salary ) = ( SELECT MIN( avg_salary ) FROM ( SELECT department_id, AVG( salary ) avg_salary FROM employees GROUP BY department_id ) t_avg_salary ) 
	);
	
-- 方式二：使用 LIMIT 和 FROM 子查询来做
SELECT t1.*
FROM departments t1 JOIN (SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 1) t2
ON t1.department_id = t2.department_id;







#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询） 
SELECT
	d.*,
	AVG( e.salary ) 
FROM
	employees e JOIN departments d
	ON e.department_id = d.department_id
GROUP BY
	e.department_id 
HAVING
	AVG( e.salary ) = ( SELECT MIN( avg_salary ) FROM ( SELECT department_id, AVG( salary ) avg_salary FROM employees GROUP BY department_id ) t_avg_salary );


#10.查询平均工资最高的 job 信息 
SELECT
	t1.* 
FROM
	jobs t1
	JOIN ( SELECT job_id, AVG( salary ) avg_salary FROM employees GROUP BY job_id ORDER BY avg_salary DESC LIMIT 1 ) t2 
	ON t1.job_id = t2.job_id;

	

#11.查询平均工资高于公司平均工资的部门有哪些? 
SELECT department_id, AVG( salary ) 
FROM employees 
WHERE department_id IS NOT NULL
GROUP BY department_id 
HAVING AVG( salary ) > ( SELECT AVG( salary ) FROM employees );


#12.查询出公司中所有 manager 的详细信息 
SELECT
	e1.employee_id,
	e1.last_name,
	e1.salary 
FROM
	employees e1 
WHERE
	EXISTS ( SELECT * FROM employees e2 WHERE e1.employee_id = e2.manager_id );

#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
-- 1.先查询出各个部门的最高工资中最低的哪一个部门
SELECT department_id,MAX(salary) max_sal
FROM employees
GROUP BY department_id
ORDER BY max_sal
LIMIT 1

-- 2.再查询出步骤1所在部门的最低工资
SELECT MIN(t1.salary)
FROM employees t1,( SELECT department_id,MAX(salary) max_sal
										FROM employees
										GROUP BY department_id
										ORDER BY max_sal
										LIMIT 1 ) t2
										WHERE t1.department_id = t2.department_id;



#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

-- 1.先找出平均工资最高的部门
SELECT department_id,AVG(salary) avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC
LIMIT 1

-- 2.找出部门为90的管理者
SELECT DISTINCT t2.*
FROM employees t1,employees t2,(SELECT department_id,AVG(salary) avg_salary
																FROM employees
																GROUP BY department_id
																ORDER BY avg_salary DESC
																LIMIT 1) t3
																WHERE t1.manager_id = t2.employee_id
																AND t2.manager_id IS NULL
																AND t2.department_id = t3.department_id;

#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号 
SELECT DISTINCT department_id
FROM employees
WHERE job_id = 'ST_CLERK';

-- 方式一：先找出jobid等于ST_CLERK的部门有哪些，然后再找出部门id不在这几个部门的数据有哪些
SELECT department_id
FROM departments
WHERE department_id NOT IN (SELECT DISTINCT department_id
														FROM employees
														WHERE job_id = 'ST_CLERK');

-- 方式二：使用 NOT EXISTS
SELECT t1.department_id
FROM departments t1
WHERE NOT EXISTS (SELECT DISTINCT t2.department_id
									FROM employees t2
									WHERE t2.job_id = 'ST_CLERK'
									AND t1.department_id = t2.department_id);


#16. 选择所有没有管理者的员工的last_name 
SELECT
	t1.last_name 
FROM
	employees t1
	LEFT JOIN employees t2 ON t1.manager_id = t2.employee_id 
WHERE
	t1.manager_id IS NULL;

#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan' 
SELECT
	employee_id,
	last_name,
	hire_date,
	salary 
FROM
	employees 
WHERE
	manager_id IN ( SELECT employee_id FROM employees WHERE last_name = 'De Haan' );


#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询） 

SELECT t1.employee_id,t1.last_name,t1.salary
FROM employees t1
WHERE t1.salary >  (
								SELECT AVG(t2.salary)
								FROM employees t2
								WHERE	t1.department_id = t2.department_id
								GROUP BY t2.department_id	
								);






SELECT
	e1.employee_id,
	e1.last_name,
	e1.department_id,
	e1.salary 
FROM
	employees e1 
WHERE
	EXISTS (
	SELECT
		e2.department_id,
		AVG( e2.salary ) 
	FROM
		employees e2 
	WHERE
		e1.department_id = e2.department_id 
	GROUP BY
		e2.department_id 
	HAVING
		e1.salary > AVG( e2.salary ) 
	);



#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询） 
SELECT
	d.department_name 
FROM
	departments d 
WHERE
	EXISTS ( SELECT department_id, COUNT(*) FROM employees e WHERE e.department_id = d.department_id GROUP BY department_id HAVING COUNT(*) > 5 );

#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
SELECT
	l.city,
	l.location_id,
	l.country_id
FROM
	locations l 
WHERE
	EXISTS (
	SELECT
		d.location_id,
		COUNT(*) 
	FROM
		departments d 
	WHERE
		d.location_id = l.location_id 
	HAVING
		COUNT(*) > 2 
	);