数据库基础二BETWEEN ... AND ...：在...和...之间 case函数的使用二：if else if

MySQL基础

DQL（Data Query Language）语言的学习

注意：字符型和日期型的常量值必须使用单引号包裹，数值型除外

基础查询

语法：

select 查询列表 from 表名；
特点：
1. 查询列表可以是：表中的字段、常量值、表达式、函数
2. 查询的结果是一个虚拟的表格
特殊字符、函数
1. AS 取别名
2. CONCAT(...) 拼接字符的函数
3. IFNULL(exp1, exp2) 判断 exp1是否为 null，使用 exp2替换
4. ISNULL(exp)：判断某字段、表达式、函数的值是否为 null，返回结果 1为null，0为非null

示例：

# 注：每次操作某库中的表时最好添加以下语句，打开被操作的库
USE myemployees;

# 1.查询表中的单个字段
SELECT last_name FROM employees;

# 2.查询表中的多个字段，各字段间逗号隔开

SELECT last_name, salary, email FROM employees; 

# 3.查询表中所有字段
SELECT * FROM employees;

# 4.查询常量值
SELECT 100;
SELECT 'john';

# 5.查询表达式
SELECT 100*99;

# 6.查询函数
SELECT VERSION();

# 7.给字段取别名
/*
1.便于理解
2.查询的字段有重名时，可以使用别名区分
*/
# 方式一：使用 AS
SELECT 100%98 AS result;
SELECT last_name AS 姓, first_name AS 名 FROM employees;

# 方式二：使用 空格
SELECT last_name 姓, first_name 名 FROM employees;

# 特殊情况：查询 salary，显示结果为 out put，使用双引号
SELECT salary AS "out put" from employees;

# 8.去重 DISTINCT
# 案例：查询员工表中涉及到的所有部门编号
SELECT DISTINCT department_id FROM employees;

# 9.+号的作用，与 CONCAT(...)函数
/*
在 mysql中 +号 只有一个功能，就是运算符
1.操作数都为数值型，则做加法运算
SELECT 100+90;
2.其中一个为字符型，试图将字符型数值转换成数值型
												如果转换成功，则继续做加法运算
												如果转换失败，将字符型变量转换为 0
SELECT '123'+90; # 213

SELECT 'john'+90; # 90
3.只要一方为 NULL,则结果肯定为 NULL
SELECT NULL+10; # NULL
*/

# 案例：查询员工名和姓连接为一个字段，并显示为 姓名
# SELECT last_name+first_name AS 姓名 FROM employees; ERRORS
# CONCAT(str1,str2,...) 拼接字符串
SELECT CONCAT('a', 'b', 'd') AS result; # abd
SELECT CONCAT(last_name, first_name) AS 姓名 FROM employees;

/*
IFNULL(expr1,expr2)：expr1目标值，expr2替换值
*/
SELECT 
	CONCAT(first_name, ',', last_name, ',', IFNULL(commission_pct, 0)) AS result
FROM
	employees;
# Steven,K_ing,0.00

条件查询 (where)

语法：

select 查询列表 from 表名 where 筛选条件;
代码的执行顺序是 from 表名 -> where 筛选条件 -> select 查询列表

筛选条件分类：

按条件表达式筛选
- 条件运算符：> < = <> >= <=
按逻辑表达式筛选：用于连接条件表达式
- 逻辑运算符：&& || ! 或者 and or not
模糊查询
- LIKE：一般和通配符搭配使用，可以判断字符型或数值型数据
- BETWEEN ... AND ...：在...和...之间
1. 可以提高语句的简洁度
2. 包含临界值
3. 两个临界值不能颠倒
- IN(..., ..., ...)：用于判断某字段值是否属于 IN()参数列表的某一项
1. 语义简洁
2. 参数列表的数据类型要一致或兼容：‘123’->123
3. 参数列表不支持通配符
- IS NULL：判断某个字段值为 NULL
1. =等号或<>，不能判断 NULL值
2. IS NULL、IS NOT NULL 专门用于判断 NULL值
- IS NOT NULL：判断某个字段值不为 NULL
- <=>：安全等于（不推荐），可以判断 NULL值和普通类型的字段值
1. 可读性差
通配符
- % 任意字符
- _ 任意单个字符
- \ 转义符
- 自定义转义符 ESCAPE '_'（推荐）

示例：

USE myemployees;
# 按条件表达式筛选

# 1.查询员工工资大于 12000的员工信息
SELECT * from employees WHERE salary > 12000;

# 2.查询部门编号不等于90的员工名和编号
SELECT first_name, department_id from employees WHERE department_id <> 90;

# 按逻辑表达式筛选
SELECT 
	first_name, department_id 
FROM 
	employees 
WHERE 
	department_id > 90 OR department_id < 90;
# 1.查询工资在 10000到20000之间的员工名、工资、奖金
SELECT
	first_name, salary, salary * IFNULL(commission_pct,0) AS commission
FROM
	employees
WHERE
	salary >= 10000 AND salary <= 20000;

# 2.查询部门编号不在90到110之间的，或者工资高于15000的员工信息
SELECT
	first_name, salary, department_id
FROM
	employees
WHERE
#	(department_id < 90 OR department_id > 110) OR salary > 15000;
	NOT(department_id >= 90 AND department_id <= 110) OR salary > 15000;
        
/*
模糊查询：
注意：
	1.字符型的值，必须使用 单引号
	2. 百分号 %，表示通配符
*/

/*
LIKE：
	1.一般和通配符搭配使用
	2.\ 转义符
	3. 自定义转义符 ESCAPE '_'
	通配符：
		% 任意字符
		_ 任意单个字符
*/
# 查询员工名中包含字符 a的员工信息
SELECT
	last_name, first_name
FROM
	employees
WHERE
	last_name LIKE '%a%';

# 查询员工名中第三个字符为 e，第五个字符为 a的员工和工资
SELECT
	first_name, last_name
FROM
	employees
WHERE
	last_name LIKE '__n_l%';

# 查询员工名中第二个字符为 _ 的员工名
SELECT
	last_name, first_name
FROM
	employees
WHERE
#	last_name LIKE '_\_%';
	last_name LIKE '_$_%' ESCAPE '$';
        
/*
BETWEEN AND：在...之间
注意：
	1.可以提高语句的简洁度
	2.包含临界值
	3.两个临界值不能颠倒
*/
SELECT
	last_name, first_name, employee_id
FROM
	employees
WHERE
# employee_id >= 100 AND employee_id <= 120; 等价于下边
	employee_id BETWEEN 100 AND 120;

/*
IN(..., ..., ...)：用于判断某字段值是否属于 IN()参数列表的某一项
	1.语义简洁
	2.参数列表的数据类型要一致或兼容：‘123’->123
	3.参数列表不支持通配符
*/
# 查询员工工种编号为 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
	last_name, job_id
FROM
	employees
WHERE
#	job_id = 'IT_PROG' OR job_id = 'AD_VP' OR job_id = 'AD_PRES'; 等价与下边
	job_id IN('IT_PROG', 'AD_VP', 'AD_PRES');

/*
IS NULL：判断某个字段值是否为 NULL
	1. =等号或<>，不能判断 NULL值
	2.IS NULL、IS NOT NULL 专门用于判断 NULL值
*/

# 查询没有奖金的员工名和奖金率
SELECT
	last_name, commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL;

/*
安全等于：<=>可以判断 NULL值和 普通类型的字段值
	1.可读性差
*/
SELECT
	last_name, commission_pct
FROM
	employees
WHERE
	commission_pct <=> NULL;
# 查询工资为 12000的员工信息
SELECT
	*
FROM
	employees
WHERE
	salary <=> 12000;

/*
IS NULL pk <=>：
	1.IS NULL：仅仅可以判断 NULL值
	2.<=>：既可以判断 NULL值，也可以判断普通字段值
*/

练习题：

# 查询员工号为 176的员工姓名、部门号和年薪
SELECT
	last_name,
	department_id,
	salary*12*(1 + IFNULL(commission_pct,0)) AS 年薪
FROM
	employees
WHERE
	employee_id = 176;

# 查询没有奖金，且工资小于 18000的 salary、last_name
SELECT
	last_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL AND salary < 18000;

# 查询 employees表中，job_id不为 ‘IT’或者工资为 12000的员工信息
SELECT
	*
FROM
	employees
WHERE
	NOT(job_id LIKE '%IT%') OR salary = 12000;

# 查看 departments表结构
DESC departments;

# 查询部门表中涉及到了哪些位置编号
SELECT
DISTINCT
	location_id
FROM
	departments;

/*
判断下面两个语句结果是否一样:
不一样，如果判断的字段有 NULL值
*/
SELECT * FROM employees;
SELECT
	*
FROM
	employees
WHERE
	commission_pct LIKE '%%' AND last_name LIKE '%%';

排序查询 (order by 排序列表)

LENGTH(str)：返回 str的字节长度

语法：

select 查询列表 from 表 [where 筛选条件] order by 排序列表 asc | desc;

特点：

asc升序、desc降序；如果不写，默认是升序
order by子句中排序列表可以是单个字段、多个字段、表达式、函数、别名
order by子句一般是放在查询语句的最后面，但 limit子句除外
sql语句的执行顺序：from 表 -> where 筛选条件 -> select 查询列表 -> order by 排序列表

示例：

/*
排序查询：

*/

# 查询员工信息，要求工资从高到低排序

SELECT
	*
FROM
	employees
ORDER BY salary DESC;

# 查询部门编号 >= 90的员工信息，按入职时间先后进行排序（添加筛选条件并排序）
SELECT
	*
FROM
	employees
WHERE
	department_id >= 90
ORDER BY
	hiredate DESC;

# 按年薪的高低显示员工信息和 年薪（按表达式排序）
SELECT
	*, salary * 12 * (1 + IFNULL(commission_pct,0)) AS 年薪
FROM
	employees
ORDER BY
	salary * 12 * (1 + IFNULL(commission_pct,0)) DESC;

# 按年薪的高低显示员工信息和 年薪（按别名排序）
SELECT
	*, salary * 12 * (1 + IFNULL(commission_pct,0)) AS 年薪
FROM
	employees
ORDER BY
	年薪 DESC;

# 按姓名的长度显示员工的姓名和工资（按函数排序）
SELECT
	last_name, LENGTH(last_name) AS 长度, salary
FROM
	employees
ORDER BY
	LENGTH(last_name) DESC;

# 查询员工信息，要求先按工资排序，再按员工编号排序(按多个字段排序)
SELECT
	*
FROM
	employees
ORDER BY
	salary DESC,
	employee_id ASC;

练习题：

USE myemployees;
# 查询员工的姓名、部门号和年薪，按年薪降序、按姓名升序
SELECT
	last_name, department_id, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
	employees
ORDER BY
	年薪 DESC, LENGTH(last_name) ASC;

# 查询工资不在 8000到 17000的员工的姓名和工资，按工资降序
SELECT
	last_name, salary
FROM
	employees
WHERE
#	salary < 8000 OR salary > 17000
#	salary NOT BETWEEN 8000 AND 17000
	NOT(salary BETWEEN 8000 AND 17000)
ORDER BY
	salary DESC;

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

常见函数

概念：将一组逻辑语句封装在方法体中，对外暴露方法名
优点：隐藏了功能的实现细节，提高代码的重用性
调用：select 函数名(实参列表) [ from 表 ]
特点：
1. 叫什么：函数名
2. 干什么：函数的功能

分类：

单行函数：如 concat()、length()、ifnull()

字符函数
数学函数
日期函数
其它函数
流程控制函数

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

单行函数：通过一组值获取对应的一组结果

字符函数

# 字符函数
# LENGTH(str)：获取参数值的字节个数
SELECT LENGTH('张三丰sdfsdb'); # 15，一个汉字占 3个字节，英文字母占 1个字节

SHOW VARIABLES LIKE '%char%'; # 查看当前客户端的字符集：character_set_client utf8mb4

# CONCAT(str1,str2,...)：拼接字符串
SELECT
	CONCAT(last_name, '_', first_name) AS 姓名
FROM
	employees;

# UPPER(str)、LOWER(str)
SELECT UPPER('john') AS 'upper';
SELECT LOWER('JOHN') AS 'lower';
# 将姓大写，名小写，然后拼接
SELECT CONCAT(UPPER(last_name), '_', LOWER(first_name)) AS 姓名 FROM employees;

# 注意：sql中索引从 1开始

# SUBSTR(str, pos, len)：从 pos截取 str的 len个字符

# 截取从指定索引处后面所有字符
SELECT SUBSTR('狼爱上羊啊', 2) AS out_put;

# SUBSTR(str FROM pos FOR len)：截取
# 截取从指定索引处指定 字符长度的字符
SELECT SUBSTR('狼爱上羊啊', 1, 3) AS out_put;

# 案例：姓名中首字符大写，其它字符小写然后用 _拼接，显示出来
SELECT
	CONCAT(UPPER(SUBSTR(last_name, 1, 1)), LOWER(SUBSTR(last_name, 2)), '_', UPPER(SUBSTR(first_name, 1, 1)), LOWER(SUBSTR(first_name, 2))) AS 姓名
FROM
	employees;

# INSTR(str,substr)：返回子串 substr在 str中第一次出现的起始索引，如果找不到返回 0
SELECT INSTR('猪都能上树的时代', '时代') AS out_put; # 7

# TRIM([remstr FROM] str)：去除 str前后的空格
# remstr：自定义被去除的字符
SELECT LENGTH(TRIM('   张翠山   ')) AS out_put; # 9

SELECT TRIM('a' FROM 'aaaaaaaaa张a翠a山aaaaaaaaaa') AS out_put; # 张a翠a山

# LPAD(str,len,padstr)：用指定的字符实现左填充指定字符
SELECT LPAD('天河一号',10,'*') AS out_put; # ******天河一号

# RPAD(str,len,padstr)：用指定的字符实现右填充指定字符

SELECT RPAD('天空之城', 2, '@') AS out_put; # 天空

SELECT RPAD('天空之城', 10, '@') AS out_put; # 天空之城@@@@@@

# REPLACE(str,from_str,to_str)：替换
SELECT REPLACE('灰狼爱上小羊啊', '小羊', '小红帽') AS out_put; # 灰狼爱上小红帽啊

数学函数

# 数学函数

# ROUND(X)：四舍五入
SELECT ROUND(2.4) AS out_put; # 2
SELECT ROUND(2.6) AS out_put; # 3
SELECT ROUND(-2.45) AS out_put; # -2
SELECT ROUND(-2.65) AS out_put; # -3
# ROUND(X,D)：四舍五入，D表示小数点后保留的位数
SELECT ROUND(2.234,2) AS out_put; # 2.23
SELECT ROUND(2.345,2) AS out_put; # 2.35
SELECT ROUND(2.456,5) AS out_put; # 2.456

# CEIL(X)：向上取整，返回 >= X的最小整数
SELECT CEIL(1.23) AS out_put; # 2
SELECT CEIL(1.000) AS out_put; # 1
SELECT CEIL(1.00023) AS out_put; # 2
SELECT CEIL(-1.23) AS out_put; # -1

# FLOOR(X)：向下取整，返回 <= X的最大整数
SELECT FLOOR(1.23) AS out_put; # 1
SELECT FLOOR(1.000) AS out_put; # 1
SELECT FLOOR(1.002) AS out_put; # 1
SELECT FLOOR(-9.99) AS out_put; # -10

# TRUNCATE(X,D)：截断，小数点后保留 D位
SELECT TRUNCATE(1.234,2) AS out_put; # 1.23

# MOD(N,M)：取余
# MOD(N,M)：数学推导 N-N/M*M，-10 - (-10)/(-3)*(-3) = -1

SELECT MOD(1,2) AS out_put; # 1
SELECT MOD(2,3) AS out_put; # 2
SELECT MOD(10,3) AS out_put; # 1
# 被除数为正结果为正，被除数为负结果为负
SELECT MOD(-10,-3) AS out_put; # -1
SELECT MOD(-10,3) AS out_put; # -1
SELECT MOD(10,-3) AS out_put; # 1
SELECT 10 % 3 AS out_put; # 1

日期函数

常用日期函数

NOW()：返回当前系统日期+时间
STR_TO_DATE(str,format)：将日期格式的字符按照指定格式转换成日期
DATE_FORMAT(date,format)：将日期按照指定的格式转换为字符

日期格式表：

%Y 四位的年份（2021）
%y 两位的年份（21）
%m 月份（01、02、...）
%c 月份（1、2、...）
%d 日（01、02、...）
%H 小时（24小时制）
%h 小时（12小时制）
%i 分钟（00、01、...）
%s 秒（00、01、...）

# 日期函数

# NOW()：返回当前系统日期+时间
SELECT NOW() AS currentDateTime; # 2021-03-26 10:25:32

# CURDATE()：返回当前系统日期，不包含时间
SELECT CURDATE() AS currentDate; # 2021-03-26

# CURTIME()：返回当前系统时间，不包含日期
SELECT CURTIME() AS currentTime; # 10:30:57

# 可以单独获取指定的 年、月、日、时、分、秒
# 年
SELECT YEAR(NOW()) AS 年; # 2021
SELECT YEAR('1998-1-1') AS 年; # 1998
SELECT YEAR(hiredate) AS 年 FROM employees; # 1992、...

# 月
SELECT MONTH(NOW()) AS 月; # 3
SELECT MONTHNAME(NOW()) AS 月; # March

# 日
SELECT DAY(NOW()) AS 日; # 26

# 时
SELECT HOUR(NOW()) AS 时; # 10

# 分
SELECT MINUTE(NOW()) AS 分; # 46

# 秒
SELECT SECOND(NOW()) AS 秒; # 28

/*
STR_TO_DATE(str,format)：将日期格式的字符按照指定格式转换成日期

*/

SELECT STR_TO_DATE('9-13-2020', '%m-%d-%Y') AS strformatDate; # 2020-09-13
SELECT STR_TO_DATE('2021-3-26', '%Y-%m-%d') AS strformatDate; # 2021-03-26

# 查询入职日期为 1992-4-3的员工信息
SELECT
	*
FROM
	employees
WHERE
	hiredate = '1992-4-3';
SELECT
	*
FROM
	employees
WHERE
	hiredate = STR_TO_DATE('4-3 1992', '%c-%d %Y');

/*
DATE_FORMAT(date,format)：将日期按照指定的格式转换为字符

*/
SELECT DATE_FORMAT('2019/6/6', '%Y年%m月%d日') AS dateformatStr; # 2019年06月06日
# 查询有奖金的员工名和入职日期（月/日 年）
SELECT
#	last_name, commission_pct, DATE_FORMAT(hiredate, '%Y年%m月%d日') AS 日期
	last_name, commission_pct, DATE_FORMAT(hiredate, '%m月/%d日 %Y年') AS 日期
FROM
	employees
WHERE
	commission_pct IS NOT NULL;

其它函数

# 其它函数

# VERSION()：查询 mysql版本
SELECT VERSION(); # 5.5.48
# DATABASE()：查看当前数据库
SELECT DATABASE(); # myemployees
# USER()：查看当前用户
SELECT USER(); # root@localhost

# 字符加密
password('字符') # 返回 字符的加密形式
md5('字符') # 返回该字符的 md5加密形式

流程控制函数

IF(条件表达式, 表达式1, 表达式2)函数：类似于 if else 的效果

# 流程控制函数

# IF(e1, e2, e3)：if else的效果
SELECT IF(10 > 5, '>', '<') AS out_put;
SELECT
#	last_name, commission_pct, IF(IFNULL(commission_pct,0), commission_pct, 0) AS pct
	last_name, commission_pct, IF(commission_pct IS NULL, '无奖金', '有奖金') AS pct
FROM
	employees;

case函数的使用一：switch case 的效果

/*
CASE函数：
方式一：类似于 switch case
CASE 要判断的变量、字段或表达式
WHEN 常量1 THEN 要显示的值1或 语句1;
WHEN 常量2 THEN 要显示的值1或 语句2;
...
WHEN 常量n-1 THEN 要显示的值1或 语句 n-1;
ELSE 要显示的值n或 语句n;
END # 结束语句
*/

/*
查询员工的工资，要求
部门号 = 30，显示工资为1.1倍
部门号 = 40，显示工资为1.2倍
部门号 = 50，显示工资为1.3倍
其他部门，显示工资为原工资
*/

SELECT
	salary AS 原始工资, 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;

case函数的使用二：if else if ... else 的效果

/*
CASE函数的使用二：类似于 多重 if else if ... else

CASE
WHEN 条件1 THEN 要显示的值1或 语句1;
WHEN 条件2 THEN 要显示的值2或 语句2;
...
ELSE 要显示的值n或 语句n;
END

*/

/*
查询员工的工资情况：
如果工资 > 20000,显示A级别
如果工资 > 15000,显示B级别
如果工资 > 10000,显示C级别
否则，显示D级别
*/
SELECT
	salary, last_name,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 级别
FROM
	employees;

练习

阶段练习

# 显示系统时间
SELECT NOW() AS currentTime; # 2021-03-29 10:50:29

# 查询员工号、姓名、工资、以及工资提高 20%后的结果（new salary）
SELECT
#	last_name, employee_id, salary, salary * (1+0.2) AS 'new salary'
	last_name, employee_id, salary, salary * 1.2 AS 'new salary'
FROM
	employees;

# 将员工的姓名按首字母排序，并写出姓名的长度
SELECT
	last_name, first_name, salary, LENGTH(last_name) AS nameLength
FROM
	employees
ORDER BY
	SUBSTR(last_name, 1, 1) ASC;

# King earns 24000 monthly but wants 72000
SELECT 
	CONCAT(last_name, ' earns ', salary, ' monthly but wants ', salary*3) AS dream
FROM
	employees
WHERE
	salary = 24000;

# 使用 CASE -WHEN，按照如下条件显示
# job grade

SELECT
	job_id AS job,
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'
# ELSE可以省略
END AS grade
FROM
	employees
WHERE
	job_id = 'AD_PRES';

分组函数：通过一组值得到一个结果

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
1. sum求和
2. avg平均值
3. max最大值
4. min最小值
5. count计算个数：统计空值 null的个数

# 分组函数

# 简单使用
SELECT
	SUM(salary) AS 'sum'
FROM
	employees; # 691400.00
SELECT
	AVG(salary) AS 'avg'
FROM
	employees; # 6461.682243

SELECT
	MIN(salary) AS 'min'
FROM
	employees; # 2100.00

SELECT
	MAX(salary) AS 'max'
FROM
	employees; # 24000.00

SELECT
	COUNT(salary) AS 'count'
FROM
	employees; # 107

SELECT
	SUM(salary) AS 'sum', ROUND(AVG(salary), 2) AS 'avg', MIN(salary) AS 'min', MAX(salary) AS 'max', COUNT(commission_pct) AS 'count'
FROM
	employees;

# 以上函数支持的数据类型

/*
1.数据类型
SUM(expr)、AVG(exmr)：适合处理数值型
MAX(expr)、MIN(expr)：数值型、字符型、日期型
COUNT(expr)：统计指定字段中非空值的个数，expr可以是任何数据类型

2.以上分组函数都忽略 NULL值
SUM(expr)、AVG(exmr)：忽略 NULL值
MAX(expr)、MIN(expr)：忽略 NULL值
COUNT(expr)：忽略 NULL值

3.可以和 distinct搭配实现去重
4.一般使用 COUNT(*)用于统计行数
5.和分组函数一同查询的字段有限制,要求是 GROUP BY后的字段

*/

/*
SUM(expr)、AVG(exmr)：适合处理数值型
*/

/*
MAX(expr)、MIN(expr)：数值型、字符型、日期型
*/

SELECT
	MAX(last_name), MIN(first_name)
FROM
	employees;

SELECT
	MAX(hiredate), MIN(hiredate)
FROM
	employees; # 2016-03-03 00:00:00、1992-04-03 00:00:00

# 忽略 NULL值

/*
COUNT(expr)：统计指定字段中非空值的个数，expr可以是任何数据类型
*/
SELECT
	COUNT(commission_pct)
FROM
	employees; # 35

SELECT
	SUM(commission_pct), AVG(commission_pct), SUM(commission_pct)/35
FROM
	employees; # 7.80、0.222857、0.222857

# 与 distinct搭配实现去重
SELECT
	SUM(DISTINCT salary)
FROM
	employees; # 397900.00

SELECT
	COUNT(DISTINCT salary) AS 'count'
FROM
	employees; # 57

/*
COUNT(DISTINCT expr,[expr...])函数详解
*/
# 1.统计某表中数据的总行数
SELECT COUNT(*) FROM employees; # 107
# 或者
SELECT COUNT(1) FROM employees;

# 和分组函数一同查询的字段有限制
SELECT
	AVG(DISTINCT salary), employee_id
FROM
	employees;

# 查询部门编号为 90的员工个数
SELECT
	COUNT(*) AS 'count'
FROM
	employees
WHERE
	department_id = 90; # 3
# 查询员工表中最大入职时间和最小入职时间的天数
# DATEDIFF(expr1,expr2)：两个日期相差的天数
SELECT
	MAX(hiredate) AS 'max', MIN(hiredate) AS 'min', DATEDIFF(MAX(hiredate), MIN(hiredate)) AS DIFFERENCE
FROM
	employees;

分组查询 group by 字段、函数、数据

语法：

select 分组函数，列（要求此列必须出现在 group by子句的后面）
from 表
[where 分组前的筛选]
group by 分组的字段
[having 分组后的筛选]
[order by 子句]

注意：查询列表比较特殊，要求是分组函数和 group by子句后出现的字段
和分组函数一同查询的字段，要求是 group by后出现的字段
使用 group by子句将表中数据分为若干组
明确：where一定放在 from之后
sql执行顺序：from 表 -> where -> group by -> having -> select -> order by

特点：

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

筛选	数据源	位置	关键字
分组前的筛选	原始表	group by子句之前	where
分组后的筛选	分组后的结果	group by子句之后	having

一般分组函数做条件肯定是放在 having子句中
能使用分组前筛选的，当优先考虑使用分组前筛选 where子句
group by子句支持多个字段分组，多个字段之间使用逗号隔开，多个字段之间不分顺序
group by子句支持函数或表达式分组
也可以添加排序 order by子句，要放在查询语句的最后，在 limit子句之前

# 分组查询

# 一.简单的分组查询

# 查询每个工种的最高工资
SELECT
	MAX(salary) AS 'max', job_id
FROM
	employees
GROUP BY job_id;

# 查询每个部门的平均工资
SELECT
	AVG(salary) AS 'dep_avg', department_id
FROM
	employees
GROUP BY department_id;

# 查询每个位置上的部门个数
SELECT
	COUNT(*) AS 'count', location_id
FROM
	departments
GROUP BY location_id;

# 添加筛选条件
# 查询邮箱中包含a字符的，每个部门的平均工资

SELECT
	AVG(salary), department_id
FROM
	employees
WHERE
	email LIKE '%a%'
# 查询有奖金的每个领导手下员工的最高工资
SELECT
	MAX(salary), manager_id
FROM
	employees
WHERE
	commission_pct IS NOT NULL
GROUP BY manager_id;

# 二.添加复杂的筛选条件
/*
1.先将问题进行拆分
2.要清楚筛选条件的书写位置 WHERE或 HAVING
*/

# 查询哪个部门的员工个数 >2
/*
1.查询每个部门的员工个数
2.根据上面的结果进行筛选 HAVING
*/
SELECT
#	COUNT(department_id) AS 'count', department_id
	COUNT(*) AS 'count', department_id
FROM
	employees
GROUP BY department_id
HAVING COUNT(*) > 2; # 分组之后再判断，因为员工表中没有

# 查询每个工种有奖金的员工的最高工资 > 12000的工种编号和最高工资
# 1.查询每个工种有奖金的员工的最高工资
SELECT
	MAX(salary) AS 'max', job_id
FROM
	employees
WHERE
	commission_pct IS NOT NULL
GROUP BY job_id
# 2.最高工资 > 12000
HAVING MAX(salary) > 12000;

# 查询领导编号 >102的每个领导手下员工的最低工资 >5000的领导编号是那个，及最低工资
# 1.查询领导编号 >102的每个领导手下员工的最低工资
SELECT
	MIN(salary) AS 'min', manager_id
FROM
	employees
WHERE
	manager_id > 102
GROUP BY manager_id
# 2.最低工资 >5000
HAVING MIN(salary) > 5000;

# 三.按表达式或函数分组

# 按员工姓名长度分组，查询每组员工个数，筛选员工个数 >5的数据
/*
GROUP BY strLength: 支持别名
HAVING cou > 5：支持别名
WHERE：不支持别名
*/
SELECT
	COUNT(*) AS cou, LENGTH(last_name) AS strLength
FROM
	employees
GROUP BY strLength
HAVING cou > 5;

# 四.按多个字段分组
# 查询每个部门每个工种员工的平均工资

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

# 五.分组查询添加排序
# 查询每个部门每个工种员工的平均工资（部门编号不为 NULL），并且平均工资 >10000，并将平均工资降序显示
SELECT
	AVG(salary) AS 'avg_sal', department_id, job_id
FROM
	employees
WHERE
	department_id IS NOT NULL
GROUP BY department_id, job_id
HAVING AVG(salary) > 10000
ORDER BY AVG(salary) DESC;

练习

# 练习

# 查询各 job_id的员工工资的最大值、最小值、平均值、总和、并按 job_id升序
SELECT
	MAX(salary) AS 'max', MIN(salary) AS 'min', AVG(salary) AS 'avg', SUM(salary) AS 'sum', job_id
FROM
	employees
GROUP BY job_id
ORDER BY job_id ASC;

# 查询员工最高工资最低工资的差距
SELECT
	(MAX(salary) - MIN(salary)) AS diff
FROM
	employees;

# 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不算
SELECT
	MIN(salary) AS 'min', manager_id
FROM
	employees
WHERE
	manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;

# 查询所有部门编号、员工数量和平均工资，并按照平均工资降序
SELECT
	department_id, COUNT(*) AS 'count', AVG(salary) AS 'avg'
FROM
	employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;

# 选择具有各个 job_id的员工人数
SELECT
	COUNT(*) AS 'count', job_id
FROM
	employees
WHERE
	job_id IS NOT NULL
GROUP BY job_id;

连接查询（多表查询）

概念：又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询
为了避免笛卡尔乘积现象的出现，需添加有效的连接条件

分类

按年代分类
- sql92标准：仅支持内连接
- sql99标准（推荐）：支持内连接+外连接（左外、右外）+交叉连接
按功能分类
- 内连接
- 外连接
- 交叉连接

sql92标准

语法：

select 查询列表
from 表1 e1, 表2 e2
where 表1.key = 表2.key
[and 筛选条件...]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段]

内连接

等值连接（涉及多张表）

语法：

select 查询列表
from 表1 e1, 表2 e2
where 表1.key = 表2.key
[and 筛选条件...]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段]

多表等值连接的结果为多表的交集部分
n表连接，至少需要 n-1个连接条件
多表的顺序没有要求
一般需要为表其别名 as
可以搭配前面介绍的所有子句使用，如：排序、分组、筛选...

# 等值连接

# 查询员工名和对应的部门名
SELECT
	last_name, first_name, department_name
FROM
	employees, departments
WHERE
	employees.department_id = departments.department_id;
	
/*
为表起别名：

1.提高语句的简洁度
2.区分多个重名字段
3.两个表的顺序可以调换

注意：如果 某表起了别名，则查询的字段就不能使用原来的表名去限定，只能使用别名

*/
# 查询员工名、工种号、工种名
SELECT
	last_name, ele.job_id, job_title
FROM
	employees AS ele, jobs AS js
WHERE
	ele.job_id = js.job_id;
        
# 可以加筛选
# 查询有奖金的员工名、部门名
SELECT
	last_name, department_name, commission_pct
FROM
	employees AS ele, departments AS dep
WHERE
	commission_pct IS NOT NULL AND ele.department_id = dep.department_id;

# 查询城市名中第二个字符为o的部门名和城市名
SELECT
	department_name, city
FROM
	departments AS dep, locations AS loc
WHERE
	city LIKE '_o%' AND loc.location_id = dep.location_id;

# 可以加分组
# 查询每个城市的部门个数
SELECT
	COUNT(*) AS 'count', city
FROM
	locations AS loc, departments AS dep
WHERE
	loc.location_id = dep.location_id
GROUP BY city;

# 查询有奖金的每个部门名和部门领导编号和该部门的最低工资
SELECT
	department_name, dep.manager_id, MIN(salary) AS 'min'
FROM
	employees AS ele, departments AS dep
WHERE
	commission_pct IS NOT NULL
AND
	ele.department_id = dep.department_id
GROUP BY department_name, dep.manager_id;

# 查询每个工种的工种名和员工个数，并按员工个数降序
SELECT
	job_title, COUNT(*) AS 'count'
FROM
	employees AS ele, jobs AS j
WHERE
	ele.job_id = j.job_id
GROUP BY j.job_id
ORDER BY COUNT(*) DESC;

# 可以实现三表连接

# 查询员工名、部门名和所在的城市
SELECT
	last_name, department_name, city
FROM
	employees ele, departments dep, locations loc
WHERE
	ele.department_id = dep.department_id
AND
	dep.location_id = loc.location_id
AND
	city LIKE 's%';

非等值连接：除去 '='外的其它筛选条件

语法：

select 查询列表
from 表1 e1, 表2 e2
where 非等值连接条件
[and 筛选条件...]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段]

# 非等值连接
SELECT
	*
FROM
	job_grades;

# 查询员工的工资和工资级别
SELECT
	salary, grade_level
FROM
	employees, job_grades
WHERE
	salary BETWEEN lowest_sal AND highest_sal
AND
	grade_level = 'A';

自连接（类似于等值连接，不过只涉及当前自己一张表）

多使用别名（建议使用双引号 "xxx"）

# 自连接

# 查询员工名和上级的名称
SELECT
	ele.employee_id, ele.last_name, m.employee_id, m.last_name
FROM
	employees AS ele, employees AS m
WHERE
	ele.manager_id = m.employee_id;

sql99标准（推荐）

语法：

select 查询列表
from 表1 别名
连接类型
join 表2 别名...
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序]

连接类型分类：

内连接：inner
左外连接：left [outer]
右外连接：right [outer]
全外连接：full [outer]
交叉连接：cross

内连接：inner

语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件...

特点：

表的连接顺序可以调换
内连接的结果 = 多表的交集
n表连接至少需要 n-1个连接条件

等值连接：

inner 可以省略
sql99与 sql92标准中等值连接的效果一样

# SQL99标准

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

# 查询名字中包含 e的员工名和工种名
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(*) 'count'
-- FROM
-- 	employees e
-- INNER JOIN
-- 	departments d
-- ON
-- 	e.department_id = d.department_id
-- INNER JOIN
-- 	locations l
-- ON
-- 	d.location_id = l.location_id
-- GROUP BY e.department_id
-- HAVING COUNT(*) > 3;

SELECT
	city, COUNT(*) "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(*) "count"
FROM
	employees e
INNER JOIN
	departments d
ON
	e.department_id = d.department_id
GROUP BY d.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;

非等值连接：

# 非等值连接
# 查询员工的工资级别
SELECT
	last_name, salary, grade_level
FROM
	employees e
#INNER JOIN
JOIN
	job_grades jg
ON
	e.salary BETWEEN jg.lowest_sal AND jg.highest_sal;

# 查询每个工资级别的个数》20，并排序
SELECT
	salary, grade_level, COUNT(*) "count"
FROM
	employees e
INNER JOIN
	job_grades g
ON
	e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY g.grade_level
HAVING COUNT(*) > 20
ORDER BY g.grade_level ASC;

自连接：

# 自连接

# 查询员工的名字、上级的名字
SELECT
	e.last_name "name", e.employee_id "eid", m.last_name "manager", m.employee_id "mid"
FROM
	employees e
INNER JOIN
	employees m
ON
	e.manager_id = m.employee_id
WHERE
	e.last_name LIKE '%k%';

外连接

应用场景：查询一个表中有，另一个表中没有的记录

语法：

select 查询列表
from 表1
连接类型
join 表2
on 连接条件
[where 筛选条件]
...

特点：

外连接的查询结果为主表中的所有记录
- 如果从表中有和它匹配的，则显示匹配的值
- 如果从表中没有和它匹配的则显示 null
- 外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录
左外连接：left [outer]，left join 左边的为主表
右外连接：right [outer]，right join 右边的为主表
左外和右外交换两个表的顺序，可以实现同样的结果
全外连接 = 内连接结果 + 表1中有但表2中没有的结果 + 表2中有但表1中没有的结果
一般用于查询除了交集部分的剩余的不匹配的行

分类：

左外连接：left [outer]，left join 左边的为主表
右外连接：right [outer]，right join 右边的为主表
全外连接：full [outer]

# 外连接
/*
应用场景：查询一个表中有，另一个表中没有的记录
*/
# 引入：查询男朋友不在男生表的女神名
# 左外连接
SELECT
	b.name, bs.*
FROM
	beauty b
LEFT OUTER JOIN
	boys bs
ON
	b.boyfriend_id = bs.id
# 没有分组，不用having
WHERE
	bs.id IS NULL;
# 右外连接
SELECT
	b.name, b.boyfriend_id, bs.boyName, bs.id
FROM
	boys bs
RIGHT OUTER JOIN
	beauty b
ON
	b.boyfriend_id = bs.id
WHERE
	bs.id IS NULL;

USE myemployees;

# 查询那个部门没有员工
# 左外
SELECT
	d.department_id, e.employee_id, e.last_name
FROM
	departments d
LEFT OUTER JOIN
	employees e
ON
	d.department_id = e.department_id
WHERE
	e.department_id IS NULL;
# 右外
SELECT
	d.department_id, e.employee_id, e.last_name
FROM
	employees e
RIGHT OUTER JOIN
	departments d
ON
	d.department_id = e.department_id
WHERE
	e.department_id IS NULL;

# 全外连接
USE girls;
SELECT
	b.*, bo.*
FROM
	beauty b
FULL OUTER JOIN
	boys bo
ON
	b.boyfriend = bo.id;

交叉连接（笛卡尔乘积）：cross

语法：

select 查询列表
from 表1 alis
cross join 表2 alis;

特点：

类似于笛卡尔乘积

USE girls;
# 交叉连接
SELECT
	b.*, bo.*
FROM
	beauty b
CROSS JOIN
	boys bo;

练习

# 练习

# 显示员工表的最大工资、工资平均值
SELECT
	MAX(salary) AS 'max', ROUND(AVG(salary),2) AS 'avg'
FROM
	employees;

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

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

# 显示当前日期，以及去前后空格，截取子字符串的函数
SELECT NOW();
SELECT DATE(NOW()); # 2021-04-03
SELECT TRIM('  sfdsfv  '); # sfdsfv

SELECT SUBSTR('afdvafba13v12a31', 2) AS 'subStr'; # fdvafba13v12a31
SELECT SUBSTR('afdvafba13v12a31', 2, 5) AS 'subStr1'; # fdvaf


# 练习 2021.4.5

# 显示所有员工的姓名、部门号和部门名称
SELECT
	last_name, ele.department_id, department_name
FROM
	employees ele, departments dep
WHERE
	ele.department_id = dep.department_id;

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

# 查询所有有奖金的员工的 last_name、department_name、location_id、city
SELECT
	last_name, department_name, loc.location_id, city
FROM
	employees ele, departments dep, locations loc
WHERE
	commission_pct IS NOT NULL
AND
	ele.department_id = dep.department_id
AND
	dep.location_id = loc.location_id;

# 选择 city在 Toronto工作的员工的 last_name, job_id, department_id, department_name
SELECT
	last_name, job_id, ele.department_id, department_name, loc.city
FROM
	employees ele, locations loc, departments dep
WHERE
	loc.city = 'Toronto'
AND
	loc.location_id = dep.location_id
AND
	dep.department_id = ele.department_id;

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

# 查询每个国家下的部门个数 >2的国家编号
SELECT
	country_id, COUNT(*) 'count'
FROM
#	employees ele, departments dep, locations loc error
	departments dep, locations loc
WHERE
	loc.location_id = dep.location_id
#AND
#	dep.department_id = ele.department_id error
GROUP BY country_id
HAVING COUNT(*) > 2;

# 选择指定员工的姓名、员工号，以及他的管理者的姓名和工号
SELECT
#	CONCAT(e.last_name, ' ', e.employee_id) AS 'employees', m.last_name 'Emp#', m.employee_id
	e.last_name AS 'employees', e.employee_id AS 'Emp#', m.last_name 'manager', m.employee_id
FROM
	employees e, employees m
WHERE
	e.last_name = 'kochhar'
AND
	e.manager_id = m.employee_id;

join总结：

join查询情况示例

mysql_join总结1.png

mysql_join总结2.png

sql99 VS sql92

功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性高

练习-外连接

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

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

# 查询部门名为 SAL或 IT的员工信息
SELECT
	d.department_name, d.department_id, e.department_id, e.last_name
FROM
	departments d
LEFT OUTER JOIN
	employees e
ON
	d.department_id = e.department_id
WHERE
	d.department_name IN('SAL', 'IT');

子查询

概念：

出现在其它语句中（增删改查）内部的 select语句（单独可执行），称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：按子查询出现的位置：

select后：标量子查询
from后：表子查询
where、having后☆（重点）：标量子查询、列子查询、行子查询
exists（相关子查询：1，0）后：标量、列、行、表子查询

按结果集的行列数不同：

标量子查询（单行）：结果集只有一行一列）
列子查询（多行）：结果集只有一列多行）
行子查询（结果集一行多列）
表子查询（结果集一般为多行多列）

where、having后面

标量子查询（单行）：结果集只有一行一列）
列子查询（多行）：结果集只有一列多行）
行子查询（结果集一行多列）

特点：

子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配单行操作符使用
- < > <= >= = <>
列子查询，一般搭配着多行操作符使用
- in/not in：等于列表中的 任意一个
- any/some：和子查询返回的 某一个值比较，如：a > min(...)
- all：和子查询返回的 所有值比较，如：a > max(...)
子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

标量子查询：

注意：
- 非法使用标量子查询：子查询的结果不是一行一列

# 标量子查询

# 谁的工资比 Abel高？
# 1.查询 Abel
# 2.查询员工信息，salary 》1.的
SELECT
	last_name, salary
FROM
	employees
WHERE
	salary > (
		SELECT
			salary
		FROM
			employees
		WHERE
			last_name = 'Abel'
);

# 返回 job_id与 141号员工相同，salary比 143号员工多的员工 姓名、job_id、和工资
SELECT
	last_name, job_id, salary
FROM
	employees e
WHERE
	e.job_id = (

		SELECT
			job_id
		FROM
			employees
		WHERE
			employee_id = 141
)
AND
	e.salary > (

		SELECT
			salary
		FROM
			employees
		WHERE
			employee_id = 143
);

# 返回公司中工资最少员工的 last_name, job_id, 和 salary
SELECT
	last_name, job_id, salary
FROM
	employees e
WHERE
	e.salary = (

	SELECT
		MIN(salary)
	FROM
		employees
);

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

# 1.查询 50号部门的最低工资
SELECT
	MIN(salary)
FROM
	employees
WHERE
	department_id = 50;

# 2.查询各部门的最低工资
SELECT
	department_id, MIN(salary)
FROM
	employees
GROUP BY department_id;
# 3.在 2基础上筛选，满足 MIN(salary)》1的数据
SELECT
	department_id, MIN(salary)
FROM
	employees
GROUP BY department_id
HAVING MIN(salary) > (

	SELECT
		MIN(salary)
	FROM
		employees
	WHERE
		department_id = 50
);

# 非法使用 标量子查询：子查询的结果不是一行一列

列子查询（一列多行）：多行子查询

# 列子查询

# 返回 location_id是 1400或 1700的部门中所有员工姓名
# 方式一、 外连接
SELECT
	e.last_name, e.department_id, d.location_id
FROM
	employees e
LEFT OUTER JOIN
	departments d
ON
	e.department_id = d.department_id
WHERE
	d.location_id IN(1400, 1700);

# 方式二、列子查询
# 1.查找 location_id是 1400或 1700的部门
SELECT
	department_id
FROM
	departments
WHERE
	location_id IN(1400, 1700);

# 2.按部门查找员工
SELECT
	last_name, department_id
FROM
	employees
WHERE
        department_id IN(
        # department_id = ANY( 可以使用其它函数替换
		# DISTINCT：去重
		SELECT DISTINCT
			department_id
		FROM
			departments
		WHERE
			location_id IN(1400, 1700)
);

# 返回其它部门中比 job_id为‘IT_PROG'任一工资低的员工的
# 员工号、姓名、job_id及 salary

# 1.查找 IT_PROG的所有员工工资
# 方式一：列子查询
SELECT
	salary
FROM
	employees
WHERE
	job_id = 'IT_PROG';
# 2.查找其它部门的员工信息
SELECT
	employee_id, last_name, job_id, salary
FROM
	employees
WHERE
	job_id <> 'IT_PROG'
AND
	salary < ANY(

		SELECT DISTINCT
			salary
		FROM
			employees
		WHERE
			job_id = 'IT_PROG'
);
# 方式二：标量子查询
SELECT
	employee_id, last_name, job_id, salary
FROM
	employees
WHERE
	job_id <> 'IT_PROG'
AND
	salary < (

		SELECT DISTINCT
			MAX(salary)
		FROM
			employees
		WHERE
			job_id = 'IT_PROG'
);

# 返回其它部门中比 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'
);

# 方式二：标量子查询
SELECT
	employee_id, last_name, job_id, salary
FROM
	employees
WHERE
	job_id <> 'IT_PROG'
AND
	salary < (

		SELECT
			MIN(salary)
		FROM
			employees
		WHERE
			job_id = 'IT_PROG'
);

行子查询：（一行多列/多行多列，了解）

# 行子查询

# 查询员工编号最小且工资最高的员工信息
# 1.工资最高的员工信息

# 方式一：
SELECT
	last_name, employee_id, salary
FROM
	employees
WHERE
	salary = (

		SELECT
			MAX(salary)
		FROM
			employees
)
AND
	employee_id = (

		SELECT
			MIN(employee_id)
		FROM
			employees
);

# 方式二：行子查询
SELECT
	*
FROM
	employees
WHERE
	(employee_id, salary) = (

		SELECT
			MIN(employee_id), MAX(salary)
		FROM
			employees
);

select后面：标量子查询

特点：

select后面，子查寻的结果必须是标量（一行一列）

# SELECT 后面

# 查询每个部门的员工个数

SELECT
	d.*, (

		SELECT
			COUNT(*)
		FROM
			employees e
		WHERE
			e.department_id = d.department_id
) AS "count"
FROM
	departments d;

# 员工号为 102的部门名
# 1.员工号为 102的部门 id
# 方式一：标量子查询
SELECT
	department_id
FROM
	employees
WHERE
	employee_id = 102;
# 2.
SELECT
	d.department_name
FROM
	departments d
WHERE
	d.department_id = (

		SELECT
			department_id
		FROM
			employees
		WHERE
			employee_id = 102
);
# 方式二：行子查询
SELECT
	(

		SELECT
			d.department_name
		FROM
			departments d
		WHERE
			d.department_id = e.department_id
) AS "department_name"
FROM
	employees e
WHERE
	e.employee_id = 102;

# 方式三：内连接
SELECT
	department_name
FROM
	departments d
INNER JOIN
	employees e
ON
	e.department_id = d.department_id
WHERE
	e.employee_id = 102;

from后面：表子查询

特点：

将查询出的结果集当作一张表放在 form后，且必须起别名

# from后面

# 查询每个部门平均工资的工资等级
# 1.获取每个部门的平均工资
SELECT
	AVG(salary) AS "salary", department_id
FROM
	employees
GROUP BY
	department_id;

# 2.查询工资等级
SELECT
	j.grade_level, j.lowest_sal, j.highest_sal, a.salary, a.department_id
FROM
	job_grades j
INNER JOIN
	(
		SELECT
			AVG(salary) AS "salary", department_id
		FROM
			employees
		GROUP BY
			department_id
) AS a
ON
	a.salary BETWEEN j.lowest_sal AND j.highest_sal;

exists后面（相关子查询）：

exists语法：

exists(子查询)：子查询有结果，则 exists结果为 1，反之为 0

特点：

exists返回结果，不是 1就是 0
exists能解决的问题，in也可以解决

# exists后面

# 查询有员工的部门名
# 方式一：exists
SELECT
	d.department_name
FROM
	departments d
WHERE
	EXISTS(
		SELECT
			employee_id
		FROM
			employees e
		WHERE
			d.department_id = e.department_id
) = 1;

# 方式二：列子查询
SELECT
	d.department_name
FROM
	departments d
WHERE
	d.department_id IN(

		SELECT
			department_id
		FROM
			employees
		WHERE
			employee_id IS NOT NULL
);

USE girls;
# 查询没有女朋友的男神信息

# 方式一：列子查询 not in
SELECT
	bo.*
FROM
	boys bo
WHERE
	bo.id NOT IN(

		SELECT
			boyfriend_id
		FROM
			beauty
);
# 方式二：exists
SELECT
	bo.*
FROM
	boys bo
WHERE
	NOT EXISTS(

		SELECT
			b.boyfriend_id
		FROM
			beauty b
		WHERE
			b.boyfriend_id = bo.id
);

练习 - 子查询

# 练习

# 查询和 Zlotkey相同部门的员工姓名和工资
# 标量子查询
SELECT
	last_name, salary
FROM
	employees
WHERE
	department_id = (

		SELECT
			department_id
		FROM
			employees
		WHERE
			last_name = "Zlotkey"
);

# 查询工资比公司员工平均工资高的员工名、工号、工资
SELECT
	last_name, employee_id, salary
FROM
	employees
WHERE
	salary > (

		SELECT
			AVG(salary)
		FROM
			employees
);

# 查询各部门中工资比本部门平均工资高的员工号、姓名和工资
# 1.计算各部门的平均工资
SELECT
	AVG(salary), department_id
FROM
	employees
GROUP BY department_id;
# 2.
SELECT
	e.employee_id, e.last_name, e.salary
FROM
	(

		SELECT
			AVG(salary) AS 'avg_salary', department_id
		FROM
			employees
		GROUP BY department_id
) AS avgdep
INNER JOIN
	employees AS e
ON
	e.department_id = avgdep.department_id
WHERE
	e.salary > avgdep.avg_salary;

# 查询姓名中包含字母 u的员工在相同部门的员工号和姓名
# 1.查询名字中包含 u的员工姓名的部门
SELECT DISTINCT
	department_id
FROM
	employees
WHERE
	last_name LIKE '%u%';
# 2.
SELECT
	employee_id, last_name, department_id
FROM
	employees
WHERE
	department_id IN (

		SELECT DISTINCT
			department_id
		FROM
			employees
		WHERE
			last_name LIKE '%u%'
);
# 查询在部门的 location_id为 1700的部门工作的员工的工号
# 查询location_id为 1700的部门编号
SELECT DISTINCT
	department_id
FROM
	departments
WHERE
	location_id = 1700;
# 2.
SELECT
	e.employee_id, e.department_id
FROM
	employees AS e
INNER JOIN
	(

		SELECT DISTINCT
			department_id
		FROM
			departments
		WHERE
			location_id = 1700
) AS did
ON
	did.department_id = e.department_id;

# 查询管理者是 King的员工姓名和工资
SELECT
	e.last_name, e.salary,e.manager_id
FROM
	employees AS e
WHERE
	e.manager_id IN (

		SELECT
			employee_id
		FROM
			employees
		WHERE
			last_name = 'K_ing' 
);

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

		SELECT
			max(salary)
		FROM
			employees
);

分页查询 `★`

应用场景：当要显示的数据过多，一页无法完全显示时，需要分页提交 sql请求

语法：

select 查询列表
from 表
[ type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后筛选
order by 排序字段 ]
limit offset, size;
- offset：要显示条目的起始索引（起始索引从 0开始），如果从第一条 0开始，可以省略
- size：要显示的条目个数语句的执行顺序：from 表 -> join 表 on 条件 -> where 筛选条件 -> group by 字段 -> having 分组筛选 -> select 查询列表 -> order by 排序 -> limit 分页

特点：

limit语句在程序执行上和位置上都是放在最后
页数和索引的关系公式：offset-索引、page-当前页数；offset = (page - 1) * size;

# 分页
USE myemployees;

# 查询前五条员工信息

SELECT
	*
FROM
	employees
ORDER BY employee_id DESC
LIMIT
	0, 5;
# 从第一条 0开始，默认可以省略
SELECT
	*
FROM
	employees
LIMIT 5;
# 查询第 11条到 25条的数据
SELECT
	*
FROM
	employees
LIMIT
	11, 15;

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

练习

# 查询所有学员的邮箱的用户名（邮箱中 @前面的字符）
SELECT
	SUBSTR(email, 1, INSTR(email, '@')) AS "用户名"
FROM
	stuinfo;
# 查询男女个数
SELECT
	count(*) 'count', sex
FROM
	stuinfo
GROUP BY
	sex;

# 查询年龄》18的所有学生姓名和年级名
SELECT
	name, gradeName
FROM
	stuinfo s
INNER JOIN
	grade g
ON
	s.gradeId = g.id
WHERE
	s.age > 18;

# 查询哪个年级的学生最小年龄>20岁
SELECT
	MIN(age) age, gradeId
FROM
	stuinfo
GROUP BY
	gradeId
HAVING
	MIN(age) > 20;

# 试说出查询语句中涉及到的所有关键字，及先后执行顺序
select 字段
from 表
类型 join 表2
on 连接条件
where 条件
group by 分组字段
having 条件
order by 排序
limit 偏移, 条目数;
执行顺序：from join on -> where -> group by -> having -> select -> order by -> limit

练习-子查询

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

# 查询平均工资最低的部门信息
# 查找部门信息
SELECT
	*
FROM
	departments
WHERE
	department_id = (

	SELECT
		avgTable1.department_id
	FROM
		(
		SELECT
			AVG(salary) AS avgSalary, department_id
		FROM
			employees
		GROUP BY
			department_id
		HAVING department_id IS NOT NULL
	) AS avgTable1
	WHERE
		avgTable1.avgSalary = (

		SELECT
		#	MIN(avgTable.avgSalary), avgTable.department_id
			MIN(avgTable2.avgSalary)
		FROM
			(
			SELECT
				AVG(salary) AS avgSalary, department_id
			FROM
				employees
			GROUP BY
				department_id
			HAVING department_id IS NOT NULL
		) AS avgTable2
	)
);
# 找到平均工资最低的部门
# 和分组函数一同查询的字段，必须是分组后的
SELECT
#	MIN(avgTable.avgSalary), avgTable.department_id
	MIN(avgTable.avgSalary)
FROM
	(
	SELECT
		AVG(salary) AS avgSalary, department_id
	FROM
		employees
	GROUP BY
		department_id
	HAVING department_id IS NOT NULL
) AS avgTable;
# 3
SELECT
	avgTable1.department_id
FROM
	(
	SELECT
		AVG(salary) AS avgSalary, department_id
	FROM
		employees
	GROUP BY
		department_id
	HAVING department_id IS NOT NULL
) AS avgTable1
WHERE
	avgTable1.avgSalary = (

	SELECT
	#	MIN(avgTable.avgSalary), avgTable.department_id
		MIN(avgTable2.avgSalary)
	FROM
		(
		SELECT
			AVG(salary) AS avgSalary, department_id
		FROM
			employees
		GROUP BY
			department_id
		HAVING department_id IS NOT NULL
	) AS avgTable2
);


# 查询平均工资最低的部门信息和该部门的平均工资
# 1.查找平均工资-部门信息
SELECT
	AVG(salary) AS avg_dep, department_id
FROM
	employees
GROUP BY
	department_id
HAVING department_id IS NOT NULL;
# 左外连接
SELECT
	dep.*, avg_tab.avg_dep
FROM
	departments AS dep
LEFT JOIN
	(
	SELECT
		AVG(salary) AS avg_dep, department_id
	FROM
		employees
	GROUP BY
		department_id
	HAVING department_id IS NOT NULL
) AS avg_tab
ON
	dep.department_id = avg_tab.department_id
WHERE
	avg_tab.avg_dep = (

	SELECT
		MIN(avg_tab1.avg_dep)
	FROM
		(
		SELECT
			AVG(salary) AS avg_dep
		FROM
			employees
		GROUP BY
			department_id
		HAVING department_id IS NOT NULL
		) AS avg_tab1
)

# 查询平均工资最高的 job信息
# 1.查询最高平均工资
SELECT
	AVG(salary) avg_j, job_id
FROM
	employees
GROUP BY
	job_id;

SELECT
	MAX(avg_t.avg_j)
FROM
	(
	SELECT
		AVG(salary) avg_j, job_id
	FROM
		employees
	GROUP BY
		job_id
) AS avg_t;

SELECT
	avg_t.job_id
FROM
	(
	SELECT
		AVG(salary) avg_j, job_id
	FROM
		employees
	GROUP BY
		job_id
) AS avg_t
WHERE
	avg_t.avg_j = (
	SELECT
		MAX(avg_t1.avg_j)
	FROM
		(
		SELECT
			AVG(salary) avg_j, job_id
		FROM
			employees
		GROUP BY
			job_id
	) AS avg_t1
)
# 2.
SELECT
	j.*
FROM
	jobs AS j
WHERE
	j.job_id = (
	SELECT
		avg_t.job_id
	FROM
		(
		SELECT
			AVG(salary) avg_j, job_id
		FROM
			employees
		GROUP BY
			job_id
	) AS avg_t
	WHERE
		avg_t.avg_j = (
		SELECT
			MAX(avg_t1.avg_j)
		FROM
			(
			SELECT
				AVG(salary) avg_j, job_id
			FROM
				employees
			GROUP BY
				job_id
		) AS avg_t1
	)
)

# 平均工资高于公司平均工资的部门
# 1.部门平均工资
SELECT
	AVG(salary) AS avg_d, department_id
FROM
	employees
GROUP BY
	department_id
HAVING department_id IS NOT NULL;
# 2.公司平均工资
SELECT
	AVG(salary)
FROM
	employees;
# 3.筛选部门
SELECT
	dep_avg.department_id
FROM
	(
SELECT
	AVG(e.salary) AS avg_d, e.department_id
FROM
	employees AS e
GROUP BY
	department_id
HAVING department_id IS NOT NULL
) AS dep_avg
WHERE
	dep_avg.avg_d > (
SELECT
	AVG(e1.salary)
FROM
	employees AS e1
);
# 4.
SELECT
	d.*
FROM
	departments AS d
WHERE
	d.department_id IN (
SELECT
	dep_avg.department_id
FROM
	(
SELECT
	AVG(e.salary) AS avg_d, e.department_id
FROM
	employees AS e
GROUP BY
	department_id
HAVING department_id IS NOT NULL
) AS dep_avg
WHERE
	dep_avg.avg_d > (
SELECT
	AVG(e1.salary)
FROM
	employees AS e1
)
)

# 查询 manager的详情
# 1.查找 manager
SELECT
	DISTINCT m.manager_id
FROM
	employees AS m
WHERE
	m.manager_id IS NOT NULL;
# 2.
SELECT
	*
FROM
	employees
WHERE
	employee_id IN (
SELECT
	DISTINCT m.manager_id
FROM
	employees AS m
WHERE
	m.manager_id IS NOT NULL
);

# 各个部门中最高工资中最低的那个部门的最低工资
# 1.各部门的的最高工资
SELECT
	MAX(salary) AS max_dep, department_id
FROM
	employees
GROUP BY
	department_id
HAVING department_id IS NOT NULL;

# 2.获取最高工资中最低的工资
SELECT
	MIN(max_table.max_dep)
FROM
	(
	SELECT
		MAX(salary) AS max_dep, department_id
	FROM
		employees
	GROUP BY
		department_id
	HAVING department_id IS NOT NULL
) AS max_table;
# 3.获取最低工资的部门
SELECT
	max_table.department_id
FROM
	(
	SELECT
		MAX(salary) AS max_dep, department_id
	FROM
		employees
	GROUP BY
		department_id
	HAVING department_id IS NOT NULL
) AS max_table
WHERE
	max_table.max_dep = (

	SELECT
		MIN(max_table1.max_dep)
	FROM
		(
		SELECT
			MAX(salary) AS max_dep, department_id
		FROM
			employees
		GROUP BY
			department_id
		HAVING department_id IS NOT NULL
	) AS max_table1
);
# 4.获取工资最低的部门表
SELECT
	*
FROM
	employees
WHERE
	department_id = (
	SELECT
		max_table.department_id
	FROM
		(
		SELECT
			MAX(salary) AS max_dep, department_id
		FROM
			employees
		GROUP BY
			department_id
		HAVING department_id IS NOT NULL
	) AS max_table
	WHERE
		max_table.max_dep = (

		SELECT
			MIN(max_table1.max_dep)
		FROM
			(
			SELECT
				MAX(salary) AS max_dep, department_id
			FROM
				employees
			GROUP BY
				department_id
			HAVING department_id IS NOT NULL
		) AS max_table1
	)
);
SELECT
	MIN(min_table.salary)
FROM
	(
	SELECT
		*
	FROM
		employees
	WHERE
		department_id = (
		SELECT
			max_table.department_id
		FROM
			(
			SELECT
				MAX(salary) AS max_dep, department_id
			FROM
				employees
			GROUP BY
				department_id
			HAVING department_id IS NOT NULL
		) AS max_table
		WHERE
			max_table.max_dep = (

			SELECT
				MIN(max_table1.max_dep)
			FROM
				(
				SELECT
					MAX(salary) AS max_dep, department_id
				FROM
					employees
				GROUP BY
					department_id
				HAVING department_id IS NOT NULL
			) AS max_table1
		)
	)
) AS min_table;

# 或
# 1.找到部门编号
SELECT
	department_id
FROM
	employees
GROUP BY
	department_id
ORDER BY
	MAX(salary) ASC
LIMIT 0, 1;
# 2.找到最低工资
SELECT
	MIN(salary)
FROM
	employees
WHERE
	department_id = (
	SELECT
		department_id
	FROM
		employees
	GROUP BY
		department_id
	ORDER BY
		MAX(salary) ASC
	LIMIT 0, 1
);
	
# 查询平均工资最高的部门的 manager的详细信息
# 1.获取平均工资最高的部门id
SELECT
	AVG(salary), department_id
FROM
	employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 0, 1;
# 2.通过部门id获取manager_id的详情
SELECT
	last_name, d.department_id, email, salary
FROM
	employees e
INNER JOIN
	departments d
ON
	d.manager_id = e.employee_id
WHERE
	e.department_id = (

	SELECT
		department_id
	FROM
		employees
	GROUP BY department_id
	ORDER BY AVG(salary) DESC
	LIMIT 0, 1
);

union（联合/合并）查询

将多条查询语句的结果合并成一个结果

语法：

查询语句1
union
查询语句2
union
...;

应用场景：

被查询的信息一致，且结果来自于多个表
且多个表之间没有连接关系

注意事项：

联合查询中，查询语句的查询列数必须一致
查询语句中查询的每一列的类型和顺序要一致
union关键字默认去重，使用 union all可以包含重复项

# 联合查询

# 查询部门编号 》90或邮箱中包含a的员工信息
# 方式一
SELECT
	e.department_id, e.email
FROM
	employees AS e
WHERE
	e.department_id > 90 OR e.email LIKE '%a%';

# 方式二
SELECT
	e.*
FROM
	employees AS e
WHERE
	e.email LIKE '%a%'
UNION
SELECT
	e1.*
FROM
	employees AS e1
WHERE
	e1.department_id > 90;

# 查询各国中男性的信息
SELECT
	id, cname, csex
FROM
	t_ca
WHERE
	csex = '男'
UNION
SELECT
	t_id, tName, tGender
FROM
	t_ua
WHERE
	tGender = 'male'

查询总结：

语法：

select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选条件 ④
group by 分组列表 ⑤
having 筛选条件 ⑥
order by 排序列表 ⑧
limit 起始索引, 条目数; ⑨

数据库 基础 二

MySQL基础

DQL（Data Query Language）语言的学习

基础查询

条件查询 (where)

排序查询 (order by 排序列表)

常见函数

字符函数

数学函数

日期函数

其它函数

流程控制函数

练习

分组查询 group by 字段、函数、数据

练习

连接查询（多表查询）

sql92标准

内连接

sql99标准（推荐）

内连接：inner

外连接

交叉连接（笛卡尔乘积）：cross

练习

sql99 VS sql92

练习-外连接

子查询

where、having后面

select后面：标量子查询

from后面：表子查询

exists后面（相关子查询）：

练习 - 子查询

分页查询 ★

练习

练习-子查询

union（联合/合并）查询

查询总结：

DML（Data Manipulation Language）语言的学习

插入语句

修改语句

删除语句

DDL（Data Define Language）语言的学习

库和表的管理

常见数据类型介绍

常见约束

TCL（Transaction Control Language）语言的学习

事物和事物处理

视图的讲解

存储过程和函数

流程控制结构

数据库基础二

分页查询 `★`