继《全程软件测试（六十八）：数据库MySQL从零开始入门—读书笔记》文章，此篇接续第三章——DQL语言
第三章 DQL语言3.1、基础查询
一、语法
SELECT 查询列表 FROM 表名;
二、特点
- 查询列表可以是字段、常量、函数、表达式
- 查询结果是一个虚拟表
三、示例
1、查询单个字段
SELECT 字段名 FROM 表名;
2、查询多个字段
SELECT 字段名,字段名 FROM 表名;
3、查询所有字段
SELECT * FROM 表名;
4、查询常量
SELECT 常量值;
注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
5、查询函数
SELECT 函数名(实参列表);
6、查询表达式
SELECT 100/25;
7、起别名
SELECT 字段名 AS "别名" FROM 表名;
注意：别名可以使用单引号、双引号引起来，当只有一个单词时，可以省略引号，当有多个单词且有空格或特殊符号时，不能省略，AS可以省略；
8、去重复
SELECT DISTINCT 字段名 FROM 表名;
9、做加法
SELECT 数值 数值; 直接运算
SELECT 字符 数值; 首先先将字符转换为整数，如果转换成功，则继续运算，如果转换失败，则默认为0，然后继续运算
SELECT NULL 数值; NULL和任何数值参与运算结果都是NULL
10、【补充】ifnull函数
功能：判断某字段或表达式是否为null，如果为null，返回指定的值，否则返回原本的值
SELECT IFNULL(字段名, 指定值) FROM 表名;
11、【补充】isnull函数
功能：判断某字段或表达式是否为null，如果是null，则返回1，否则返回0
SELECT ISNULL(字段名) FROM 表名;
3.2、条件查询
一、语法
SELECT 查询列表 FROM 表名 WHERE 筛选条件;
二、分类
- 条件运算符：>、>=、<、<=、=、<=>、!=、<>
- 逻辑运算符：and、or、not
- 模糊运算符：
- like：%任意多个字符、_任意单个字符，如果有特殊字符，需要使用escape转义
- between and
- not between and
- in
- is null
- is not null
三、演示
1、查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE salary > 12000 ;
2、查询工资>=14000的员工信息
SELECT
*
FROM
employees
WHERE salary >= 14000 ;
3、查询工资<12000的员工信息
SELECT
*
FROM
employees
WHERE salary < 12000 ;
4、查询工资<=14000的员工信息
SELECT
*
FROM
employees
WHERE salary <= 14000 ;
5、查询员工编号=100的员工信息
SELECT
*
FROM
employees
WHERE employee_id = 100 ;
6、查询员工编号<=>100的员工信息
SELECT
*
FROM
employees
WHERE employee_id <=> 100 ;
注意：=只能判断普通类型的数值，而<=>不仅可以判断普通类型的数值还可以判断NULL
7、查询员工编号!=100的员工信息
SELECT
*
FROM
employees
WHERE employee_id != 100 ;
8、查询员工编号<>100的员工信息
SELECT
*
FROM
employees
WHERE employee_id <> 100 ;
注意：!=和<>都是判断不等于的意思，但是MySQL推荐使用<>
9、查询工资>12000&&工资<18000的员工信息
SELECT
*
FROM
employees
WHERE salary > 12000 AND salary < 18000 ;
10、查询工资<=12000||工资>=18000的员工信息
SELECT
*
FROM
employees
WHERE salary <= 12000 OR salary >= 18000 ;
11、查询工资<=12000||工资>=18000的员工信息
SELECT
*
FROM
employees
WHERE NOT (salary > 12000 AND salary < 18000) ;
12、查询员工名中第一个字符为B、第四个字符为d的员工信息
SELECT
*
FROM
employees
WHERE last_name LIKE 'B__d%' ;
注意：last_name指的是员工名
13、查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE employee_id BETWEEN 100 AND 120 ;
14、查询员工编号不在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE employee_id NOT BETWEEN 100 AND 120 ;
15、查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE job_id IN ('IT_PROT', 'AD_VP', 'AD_PRES') ;
注意：in列表的值类型必须一致或兼容，in列表中不支持通配符%和_
16、查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct is null ;
17、查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NOT NULL ;
注意：=、!=不能用来判断NULL、而<=>、is null 、 is not null可以用来判断NULL，但注意<=>也可以判断普通类型的数值；
3.3、排序查询
一、语法
SELECT
查询列表
FROM
表
【WHERE 筛选条件】
order by 排序列表 【asc | desc】 ;
二、注意
- 排序列表可以是单个字段、多个字段、别名、函数、表达式
- asc代表升序，desc代表降序，如果不写，默认是asc
- order by的位置一般放在查询语句的最后（除limit语句之外）
三、示例
1、按单个字段排序：查询员工信息，要求按工资降序
SELECT
*
FROM
employees
ORDER BY salary DESC ;
2、按多个字段查询：查询员工信息，要求先按工资降序，再按员工编号升序
SELECT
*
FROM
employees
ORDER BY salary DESC, employee_id ASC ;
3、按别名排序查询：查询员工信息，要求按员工年薪升序
SELECT
*,
salary * 12 * (1 IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY 年薪 ASC ;
4、按函数排序查询：查询员工信息，要求按员工名字的长度降序
SELECT
LENGTH(last_name),
last_name
FROM
employees
ORDER BY LENGTH(last_name) DESC ;
5、按表达式排序：查询员工信息，要求按员工年薪升序
SELECT
*,
salary * 12 * (1 IFNULL(commission_pct, 0))
FROM
employees
ORDER BY salary * 12 * (1 IFNULL(commission_pct, 0)) ASC ;
3.4、单行函数
一、语法
SELECT 函数名(实参列表) 【FROM 表】;
二、分类
1、字符函数
- concat：连接字符
- substr：截取子串
- replace：替换字符
- upper：变大写
- lower：变小写
- lpad：左填充
- rpad：右填充
- length：获取字节长度
- trim：去除前后空格
- instr：获取子串第一次出现的索引
注意：MySQL中的索引是从1开始的
2、数学函数
- round：四舍五入
- ceil：向上取整
- floor：向下取整
- mod：取模运算（a-a/b*b）
- truncate：保留小数的位数，不进行四舍五入
- rand：获取随机数，返回0-1之间的小数
3、日期函数
- now：返回当前日期 时间
- curdate：返回当前日期
- curtime：返回当前时间
- year：返回年
- month：返回月
- day：返回日
- hour：小时
- minute：分钟
- second：秒
- monthname：以英文形式返回月
- datediff：返回两个日期相差的天数
- date_format：将日期转换成字符
- str_to_date：将字符转换成日期
格式符：
%Y：四位的年份
%y：二位的年份
%m：二位的月份（01，02，...，12）
%c：一位的月份（1，2，...，12）
%d：日（01，02，...，31）
%H：小时（24小时制）
%h：小时（12小时制）
%i：分钟（00，01，02，...，59）
%s：秒（00，01，02，...，59）
4、控制函数
- if：判断函数
- case：分支函数
1、IF(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
2、case的格式一：
CASE 变量或字段或表达式
WHEN 常量1 THEN 值1
WHEN 常量2 THEN 值2
...
ELSE 值n
END ;
3、case的格式二：
CASE
WHEN 条件1 THEN 值1
WHEN 条件2 THEN 值2
...
ELSE 值n
END
5、其它函数
- version：当前数据库的版本
- database：当前打开的数据库
- user：当前登录的用户
- password(‘字符’)：返回该字符的密码形式
- md5(‘字符’)：返回该字符的md5加密形式
三、演示
1、concat——连接字符
SELECT CONCAT('Hello',' ','World') AS out_put;
2、substr——截取子串
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) AS out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS out_put;
3、replace——替换字符
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
4、upper——变大写
SELECT UPPER('john') AS out_put;
5、lower——变小写
SELECT LOWER('john') AS out_put;
6、lpad——左填充
SELECT LPAD('殷素素',10,'*') AS out_put;
7、rpad——右填充
SELECT RPAD('殷素素',10,'*') AS out_put;
8、length——获取字节长度
SELECT LENGTH('john') AS out_put;
9、trim——去除前后空格
#删除指定字符的左右空格
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
#删除指定字符的指定字符
SELECT TRIM('aaa' FROM 'aaaaaaaaa张翠山aaaaaaaaa') AS out_put;
10、instr——获取子串第一次出现的索引
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;
注意：返回子串第一次出现的索引，如果找不到返回0
11、round——四舍五入
#默认四舍五入
SELECT ROUND(-1.55) AS out_put;
#指定小数位数
SELECT ROUND(1.567,2) AS out_put;
注意：四舍五入和符号无关
12、ceil——向上取整
SELECT CEIL(-1.02) AS out_put;
注意：向上取整，返回>=该参数的最小整数
13、floor——向下取整
SELECT FLOOR(-9.99) AS out_put;
注意：向下取整，返回<=该参数的最大整数
14、mod——取模运算（a-a/b*b）
SELECT MOD(10,3) AS out_put;
15、truncate——保留小数的位数，不进行四舍五入
SELECT TRUNCATE(1.69999,1) AS out_put;
16、rand——获取随机数，返回0-1之间的小数
SELECT RAND() AS out_put;
17、now——返回当前日期 时间
SELECT NOW() AS out_put;
18、curdate——返回当前日期
SELECT CURDATE() AS out_put;
19、curtime——返回当前时间
SELECT CURTIME() AS out_put;
20、year——返回年
SELECT YEAR(NOW()) 年;
21、month——返回月
SELECT MONTH(NOW()) 月;
22、day——返回日
SELECT DAY(NOW()) 日;
23、hour——返回时
SELECT HOUR(NOW()) 时;
24、minute——返回分
SELECT MINUTE(NOW()) 分;
25、second——返回秒
SELECT SECOND(NOW()) 秒;
26、monthname——以英文形式返回月
SELECT MONTHNAME(NOW()) 月名;
27、datediff——返回两个日期相差的天数
SELECT DATEDIFF('1995-2-7','1995-2-6') AS out_put;
28、date_format——将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;
29、str_to_date——将字符转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
30、if——判断函数
SELECT IF(10 < 5, '大', '小') AS out_put;
31、case的格式——分支函数（一）
/*
案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其它部门，显示的工资为原工资
*/
SELECT
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 ;
32、case的格式——分支函数（二）
/*
案例：查询员工的工资情况
如果工资>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 ;
33、version——当前数据库的版本
SELECT VERSION();
34、database——当前打开的数据库
SELECT DATABASE();
35、user——当前登录的用户
SELECT USER();
36、password(‘字符’)——返回该字符的密码形式
SELECT PASSWORD('123456');
37、md5(‘字符’)——返回该字符的md5加密形式
SELECT MD5('123456');
3.5、分组函数
一、语法
SELECT 函数名(实参列表) 【FROM 表】;
二、分类
- sum：求和
- avg：平均值
- max：最大值
- min：最小值
- count：计算个数
1、sum、avg一般用于处理数值型，max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算：select sum(distinct 字段) from 表;
4、一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段
三、演示
1、sum——求和
SELECT SUM(salary) FROM employees;
2、avg——平均值
SELECT AVG(salary) FROM employees;
3、max——最大值
SELECT MAX(salary) FROM employees;
4、min——最小值
SELECT MIN(salary) FROM employees;
5、count——计算个数
SELECT COUNT(salary) FROM employees;
3.6、分组查询
一、语法
SELECT
查询列表
FROM
表
【where 筛选条件】
GROUP BY 分组的字段
【having 分组后的筛选】
【order BY 排序的字段】 ;
二、特点
- 和分组函数一同查询的字段必须是group by后出现的字段
- 筛选分为两类：分组前筛选和分组后筛选
|
针对的表 |
语句位置 |
连接的关键字 | |
|
分组前筛选 |
分组前的原始表 |
group by前 |
where |
|
分组后筛选 |
分组后的结果集 |
group by后 |
having |
- 分组可以按单个字段也可以按多个字段
- 分组可以搭配着排序使用
三、演示
1、查询每个工种的员工平均工资
SELECT
AVG(salary),
job_id
FROM
employees
GROUP BY job_id ;
2、查询每个位置的员工部门个数
SELECT
COUNT(*),
location_id
FROM
departments
GROUP BY location_id ;
3、查询有奖金的每个领导手下员工的平均工资
SELECT
AVG(salary),
manager_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id ;
4、查询哪个部门的员工个数>5
SELECT
COUNT(*),
department_id
FROM
employees
GROUP BY department_id
HAVING COUNT(*) > 5 ;
5、查询每个工种有奖金的员工的最高工资>6000的最高工资和公众编号，按最高工资升序
SELECT
MAX(salary) m,
job_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m > 6000
ORDER BY m ;
6、查询每个工种每个部门的最低工资并按最低工资降序
SELECT
MIN(salary),
job_id,
department_id
FROM
employees
GROUP BY job_id, department_id
ORDER BY MIN(salary) DESC ;
3.7、连接查询
一、含义
连接查询又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
二、注意
- 笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
三、分类
1、按年代分类
- SQL92标准：支持内连接
- SQL99标准：支持内连接、部分外连接（左外、右外）、交叉连接
2、按功能分类
- 内连接
等值连接
非等值连接
自连接
- 外连接
左外连接
右外连接
全外连接
- 交叉连接
四、SQL92标准演示
1、SQL92标准：等值连接
#查询员工名和对应的部门名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE employees.`department_id` = departments.`department_id` ;
2、SQL92标准：非等值连接
#查询员工的工资和工资级别
SELECT
salary,
grade_level
FROM
employees e,
job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal` ;
3、SQL92标准：自连接
#查询员工名和它对应上级的名称
SELECT
e.employee_id,
e.last_name,
m.employee_id,
m.last_name
FROM
employees e,
employees m
WHERE e.`manager_id` = m.`employee_id` ;
3.8、SQL99标准
一、语法
SELECT
查询列表
FROM 表1 别名1
【连接类型】 JOIN 表2 别名2 ON 连接条件
【where 分组前筛选条件】
【group BY 分组列表】
【having 分组后筛选条件】
【order BY 排序列表】 ;
二、连接类型
- 1、内连接：inner
- 2、外连接
左外连接：left 【outer】（左边的是主表）
右外连接：right 【outer】（右边的是主表）
全外连接：full 【outer】（两边都是主表，但是MySQL不支持全外连接、Oracle支持）
- 3、交叉连接：cross（交叉连接其实是用SQL99语法实现笛卡尔乘积）
三、演示
1、内连接：等值连接
#查询员工名和对应的部门名
SELECT
last_name,
department_name
FROM
departments d
INNER JOIN employees e ON e.`department_id` = d.`department_id` ;
2、内连接：非等值连接
#查询员工的工资和工资级别
SELECT
salary,
grade_level
FROM
employees e
INNER JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` ;
3、内连接：自连接
#查询员工名和它对应上级的名称
SELECT
e.last_name,
m.last_name
FROM
employees e
INNER JOIN employees m ON e.`manager_id` = m.`employee_id` ;
4、外连接：左外连接
#查询哪个部门没有员工
SELECT
d.`department_name`
FROM
departments d
LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL ;
5、外连接：右外连接
#查询哪个部门没有员工
SELECT
d.`department_name`
FROM
employees e
RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL ;
6、交叉连接
#使用交叉连接进行笛卡尔乘积查询
SELECT
b.*,
bo.*
FROM beauty b
CROSS JOIN boys bo ;
3.9、子查询
一、含义
嵌套在其它语句内部的select语句称为子查询或内查询，外面的语句可以是insert、delete、update、select等，一般select作为外面语句较多，外面如果为select语句，则此语句称为外查询或主查询
二、分类
- 1、按出现的位置划分
select后面：标量子查询
from后面：表子查询
where或having后面
1、标量子查询
2、列子查询
3、行子查询
exists后面
1、标量子查询
2、列子查询
3、行子查询
4、表子查询
- 2、按结果集行列数划分
标量子查询（单行子查询）：结果集为一行一列
列子查询（多行子查询）：结果集为多行一列
行子查询：结果集为多行多列
表子查询：结果集为多行多列
三、特点
- 子查询放在小括号内
- 子查询一般放在条件的右侧
- 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
- 标量子查询，一般搭配着单行操作符使用：>、>=、<、<=、!=、<>、=、<=>
- 列子查询，一般搭配着多行操作符使用：in、not in、any、some、all、exits
四、演示
1、select后面
#查询每个部门的员工个数
SELECT
d.*, (
SELECT
COUNT(*)
FROM
employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM
departments d ;
2、from后面
#查询每个部门平均工资的工资等级
SELECT
ag_dep.*,
g.`grade_level`
FROM (
SELECT
AVG(salary) ag,
department_id
FROM
employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal ;
3、where或having后面
标量子查询：查询最低工资的员工姓名和工资
SELECT
last_name,
salary
FROM
employees
WHERE salary = (
SELECT MIN(salary) FROM employees
) ;
列子查询：
#查询所有是领导的员工姓名
SELECT
last_name
FROM
employees
WHERE employee_id IN (
SELECT DISTINCT manager_id FROM employees
) ;
#返回其它工种中比如job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE salary < ANY (
SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG' ;
#返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE salary < ALL (
SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG' ;
行子查询：查询员工编号最小并且工资最高的员工信息
SELECT
*
FROM
employees
WHERE (employee_id, salary) = (
SELECT MIN(employee_id), MAX(salary) FROM employees
) ;
4、exists后面
#查询有员工的部门名
SELECT
department_name
FROM
departments d
WHERE EXISTS (
SELECT * FROM employees e
WHERE e.`department_id` = d.`department_id`
) ;
3.10、分页查询
一、语法
SELECT
查询列表
FROM
表1 别名1
【连接类型】 JOIN 表2 别名2 ON 连接条件
【WHERE 分组前的筛选】
【GROUP BY 分组字段】
【HAVING 分组后的筛选 】
【ORDER BY 排序字段 ASC|DESC】
LIMIT 【offset, 】size ;
二、特点
- limit语句放在查询语句的最后
- offset代表起始索引，起始索引从0开始，size代表条目个数
- 分页语句：select 查询列表 from 表 limit (page-1)*size,size;
三、演示
#查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
3.11、联合查询
一、语法
查询语句1
union 【all】
查询语句2
union 【all】
...
二、特点
- 要查询的结果来自于多个表且多个表没有直接的连接关系，但查询的信息一致时，可以使用联合查询
- 要求多条查询语句的查询列数是一致的
- 要求多条查询语句的查询的每一列的类型和顺序最好一致
- union关键字默认去重，如果使用union all可以包含重复项
三、演示
#查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';
