02-查询语法大全

image-20200812132737977

MySQL执行过程

image-20230605223751557

常规 SQL 语句语法

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列 LIMIT 起始行,总条数;

  • 执行顺序：

    1
    2
    3
    4
    5
    6
    7
    FROM : 指定数据来源表
    WHERE : 对查询数据做第一次过滤
    GROUP BY : 分组
    HAVING : 对分组后的数据第二次过滤
    SELECT : 查询各字段的值
    ORDER BY : 排序
    LIMIT : 限定查询结果

示例数据：
数据源

1. 基本查询

语法：SELECT 列名 FROM 表名

关键字 描述
SELECT 指定要查询的列
FROM 指定要查询的表

1.1 查询部分列

1
2
#查询员工表中所有员工的编号、名字、邮箱
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL FROM t_employees;

1.2 查询所有列

1
2
3
#查询员工表中所有员工的所有信息（所有列），两种方式：
SELECT 所有列的列名 FROM t_employees;
SELECT * FROM t_employees;

1.3 对列中的数据进行运算

需修改表中的 运算列 为可运算类型，如int

1
2
3
4
#查询员工表中所有员工的编号、名字、年薪
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY*12 FROM t_employees;
# 查询员工表中所有员工的编号、名字、日薪（列名/22）会有小数位，且自动保留4位
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY/22 FROM t_employees;
算数运算符 描述
+ 两列做加法运算
- 两列做减法运算
* 两列做乘法运算
/ 两列做除法运算

1.4 列的别名

语法：列名 AS ‘别名’

1
2
# 查询员工表中所有员工的编号、名字、年薪（列名/22）列名为中文
SELECT EMPLOYEE_ID AS '编号',FIRST_NAME AS '姓',LAST_NAME AS '名',SALARY*12 AS '年薪' FROM t_employees;
  • 没有对原表列名造成影响。
  • sql中除了整数，字符串均为单引号。

1.5 查询结果去重

语法：SELECT DISTINCT 列名 FROM 表名

1
2
#查询员工表中所有经理的ID。
SELECT DISTINCT MANAGER_ID FROM t_employees;

2. 排序查询

语法： SELECT 列名 FROM 表名 ORDER BY 排序列 [排序规则]

排序规则 描述
ASC 对前面排序列做升序排序，默认，可省略
DESC 对前面排序列做降序排序

2.1 依据单列排序

1
2
3
4
5
6
# 查询员工编号、名字、薪资，按照工资进行升序排序
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees
ORDER BY SALARY DESC;
# 查询员工编号、名字、薪资，按照姓名进行升序排序
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees
ORDER BY FIRST_NAME ASC;

2.2 依据多列排序

1
2
#查询员工的编号，名字，薪资。按照工资高低进行降序排序（薪资相同时，按照编号进行升序排序）。
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM t_employees ORDER BY SALARY DESC, EMPLOYEE_ID ASC

3. 条件查询

语法：SELECT 列名 FROM 表名 WHERE 条件

关键字 描述
WHERE 条件 在查询结果中，筛选符合条件的查询结果，条件为布尔表达式

3.1 等值判断（=）

1
2
#查询薪资是11000的员工信息（编号、名字、薪资）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees WHERE SALARY=10000;

3.2 逻辑判断（and、or、not）

1
2
3
4
5
6
7
8
9
10
11
# 查询员工工资在6000-10000的员工的信息
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees 
WHERE SALARY>6000 AND SALARY<10000;
# 查询员工工资在9000 或 10000的员工的信息
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees 
WHERE SALARY=9000 OR SALARY=10000;
# 查询员工工资不是10000的员工的信息
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees 
WHERE SALARY!=10000;
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees 
WHERE NOT SALARY=10000;

3.3 不等值判断（> 、< 、>= 、<= 、!= 、<>）

1
2
3
#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees 
WHERE SALARY>6000 AND SALARY<10000;

3.4 区间判断（between and）

1
2
#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees WHERE SALARY BETWEEN 6000 AND 10000;

注意：在区间判断语法中

  1. 闭区间，包含区间边界的两个值；
  2. 顺序不能颠倒：小值在前，大值在后，反之，得不到正确结果。

3.5 NULL 值判断（IS NULL、IS NOT NULL）

列名 IS NULL

列名 IS NOT NULL

1
2
3
4
5
6
7
8
9
# 查询经理编号为null的员工信息
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, MANAGER_ID FROM t_employees
WHERE MANAGER_ID IS NULL;
# 查询经理编号不为null的员工信息
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, MANAGER_ID FROM t_employees
WHERE MANAGER_ID IS NOT NULL;
# 查询经理编号为null以外员工信息（NOT为取反，两个结果）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, MANAGER_ID FROM t_employees
WHERE NOT MANAGER_ID IS NULL;

3.6 枚举查询（ IN (值 1，值 2，值 3 ) ）

1
2
3
# 查询部门编号为70, 80, 90的员工信息 - 枚举查询(不冗余，效率低)
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM t_employees
WHERE DEPARTMENT_ID IN(70,80,90); # ()中值之间的关系是 or 的关系
  • 注：in的查询效率较低，建议使用多条件拼接。

3.7 模糊查询

  • LIKE _ （_ 代表单个任意字符，_ 的数量就代表字符的数量，模糊内容、精确长度）

    列名 LIKE 'S_' 以s开头长度2 _S 以s结尾长度2 _S_中间s长度为3

  • LIKE %（% 代表任意长度的任意字符，0~n个）

    列名 LIKE 'S%'以s开头的 %S 以s结尾的 %S%只要有s的

  • 注意：模糊查询只能和 LIKE 关键字结合使用

  • 两者可以混合灵活使用

1
2
3
4
5
6
7
# 查询名字以"S"开头的员工信息，长度为 5 个字符（编号，名字，薪资 , 部门编号）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID FROM t_employees
WHERE FIRST_NAME LIKE 'S_____';

# 查询名字以"S"开头的员工信息（编号，名字，薪资 , 部门编号）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID FROM t_employees
WHERE FIRST_NAME LIKE 'S%';
1
2
3
4
5
6
7
8
9
# 全字段模糊查询（包含 "蓝" 的字） + 连表查询 + 排序 + 分页
SELECT * FROM
  (SELECT * FROM tb_stu, tb_grade WHERE tb_stu.gId = tb_grade.gradeId) AS fullTable
  WHERE (stuName LIKE '%蓝%')
    OR (stuBirthday LIKE '%蓝%')
    OR (stuHobby LIKE '%蓝%')
    OR (stuAge LIKE '%蓝%')
  ORDER BY stuId
  LIMIT 0,10;

Java Dao 层 sql 语句拼接注意事项：

image-20200530150505396

3.8 分支结构查询

语法：CASE WHEN 条件 THEN 结果 … END

CASE

? WHEN 条件1 THEN 结果1

? WHEN 条件2 THEN 结果2

? WHEN 条件3 THEN 条件3

? ELSE 结果x

END

  • 注意：通过使用CASE END进行条件判断，每条数据对应生成一个值。
  • 经验：类似 Java 中的switch。
1
2
3
4
5
6
7
8
9
10
#查询员工信息（编号，名字，薪资 , 薪资级别<对应条件表达式生成>）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
  CASE
    WHEN SALARY>=10000 THEN 'A'
    WHEN SALARY>=8000 AND SALARY<10000 THEN 'B'
    WHEN SALARY>=6000 AND SALARY<8000 THEN 'C'
    WHEN SALARY>=2000 AND SALARY<6000 THEN 'D'
    ELSE 'E'
  END AS '薪资级别'
FROM t_employees ORDER BY 薪资级别; # 顺便排个序

4. 时间查询

语法：SELECT 时间函数([参数列表]);

  • 经验：执行时间函数查询，会自动生成一张虚表（一行一列）
时间函数 描述
SYSDATE() / NOW() 当前系统时间（年-月-日 时:分:秒）
CURDATE() 获取当前日期（年-月-日）
CURTIME() 获取当前时间（时:分:秒）
WEEK(DATE) + 1 获取指定日期为一年中的第几周
YEAR(DATE) / MONTH(DATE) / DAY(DATE) 获取指定日期的年、月、日
HOUR(TIME) / MINUTE(TIME) / SECOND(TIME) 获取指定时间的时、分、秒
DATEDIFF(DATE1,DATE2) 获取DATE1 和 DATE2 之间相隔的天数
ADDDATE(DATE,N) 计算DATE 加上 N 天后的日期

4.1 获取各种日期和时间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
# 1.当前系统时间 2020-03-26 10:55:56
SELECT NOW(); # 默认linux会少8h，在 tomcat->catalina.sh 文件中的 JAVA_OPTS 配置加上 -Duser.timezone=GMT+08
SELECT SYSDATE();
# 2.获得当前日期 2020-03-26
SELECT CURDATE();
# 3.获得当前时间 10:55:56
SELECT CURTIME();
# 4.获得指定日期在一年中为第几周 13
SELECT WEEK(CURDATE())+1;
# 5.获取指定日期中的年 2020
SELECT YEAR(CURDATE());
# 6.获取指定日期中的月 3
SELECT MONTH(CURDATE());
# 7.获取指定日期中的日 26
SELECT DAY(CURDATE());
# 8.获取指定日期中的时 10
SELECT HOUR(SYSDATE());
# 9.获取指定日期中的分 55
SELECT MINUTE(SYSDATE());
# 10.获取指定日期中的秒 56
SELECT SECOND(SYSDATE());
# 11.获取date1和date2之间相隔的天数 366
SELECT DATEDIFF(SYSDATE(), '2019-03-26');
# 12.获取指定天数后的日期 2020-04-01 10:55:56
SELECT ADDDATE(SYSDATE(), 6);
# 13.当月最后一天
SELECT LAST_DAY(SYSDATE()) AS '本月最后1天日期'; # 2020-03-31
# 14.格式化日期格式
select DATE_FORMAT(SYSDATE(), '%Y/%m(%b)/%d(%a) %H:%i:%s %p'); #2020/05(May)/02 10:34:01 AM
/*
DATE_FORMAT(date,format)
依照 format 字符串格式化 date 值。下面的修饰符可被用于 format 字符串中：
修饰符  含义
%M  月的名字 (January..December)
%W  星期的名字 (Sunday..Saturday)
%D  有英文后缀的某月的第几天 (0th, 1st, 2nd, 3rd, etc.)
%Y  年份，数字的，4 位
%y  年份，数字的，2 位
%X  周值的年份，星期日是一个星期的第一天，数字的，4 位，与 ‘%V’ 一同使用
%x  周值的年份，星期一是一个星期的第一天，数字的，4 位，与 ‘%v’ 一同使用
%a  缩写的星期名 (Sun..Sat)
%d  月份中的天数，数字的 (00..31)
%e  月份中的天数，数字的 (0..31)
%m  月，数字的 (00..12)
%c  月，数字的 (0..12)
%b  缩写的月份名 (Jan..Dec)
%j  一年中的天数 (001..366)
%H  小时 (00..23)
%k  小时 (0..23)
%h  小时 (01..12)
%I  小时 (01..12)
%l  小时 (1..12)
%i  分钟，数字的 (00..59)
%r  时间，12 小时 (hh:mm:ss [AP]M)
%T  时间，24 小时 (hh:mm:ss)
%S  秒 (00..59)
%s  秒 (00..59)
%p  AM 或 PM
%w  一周中的天数 (0=Sunday..6=Saturday)
%U  星期 (00..53)，星期日是一个星期的第一天
%u  星期 (00..53)，星期一是一个星期的第一天
%V  星期 (01..53)，星期日是一个星期的第一天。与 ‘%X’ 一起使用
%v  星期 (01..53)，星期一是一个星期的第一天。与 ‘%x’ 一起使用
%%  一个字母 “%”
*/

-- 生日计算转换年龄 - 21
select timestampdiff(year, '1999/09/09', curdate()) as age
-- 生日计算转换年龄 - 21
select date_format(from_days(to_days(now())-to_days('1999-09-09')), '%Y') + 0 AS age

5. 字符串查询

语法： SELECT 字符串函数([参数列表]);

字符串函数 说明
CONCAT(str1,str2,str….) 将 多个字符串连接
INSERT(str,pos,len,newStr) 将str 中指定 pos 位置开始 len 长度的内容替换为 newStr
LOWER(str) 将指定字符串转换为小写
UPPER(str) 将指定字符串转换为大写
SUBSTRING(str,num,len) 将str 字符串指定num位置开始截取 len 个内容

5.1 字符串应用

1
2
3
4
5
6
7
8
9
10
# 1.连接：将多个字符串连接再一起
SELECT CONCAT('My', 'S', 'QL');
# 2.插入替换（字符下标从 1 开始） INSERT(str, pos, len, newstr)
SELECT INSERT('这是MySQL数据库', 3, 5, 'Oracle'); # 这是 Oracle 数据库
# 3.转小写
SELECT LOWER('MySQL');
# 4.转大写
SELECT UPPER('MySQL');
# 5.截取子字符串，4 种参数：(str,pos) / (str FROM pos) / (str, pos, len) / (str FROM pos FOR len)
SELECT SUBSTRING('这是sql数据库', 3, 5); # MySQL

6. 聚合函数

语法：SELECT 聚合函数(列名) FROM 表名;

  • 经验：对多条数据的单列进行统计，返回统计后的一行结果。
聚合函数 说明
COUNT() 求总行数，自动忽略 null 值
SUM() 求所有行中单列结果的总和
AVG() 平均值
MAX() 最大值
MIN() 最小值
  • 高级函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- MySQL 自带函数
-- 算术
select abs(-22); -- 绝对值
select floor(3.14); -- 向下取整
select ceil(3.14); -- 向上取整
select rand(); -- 随机 0~1 之间
select 10 + floor(rand() * 90); -- 2位随机数

-- 字符串
select concat('my', 'sql'); -- mysql
select length('mysql'); -- 5
select substr('mysql', 3, 2); -- sq
select reverse('mysql'); -- lqsym


6.1 总行数

1
2
3
4
#统计员工总数
SELECT COUNT(EMPLOYEE_ID) AS '员工总数' FROM t_employees; #107
SELECT COUNT(MANAGER_ID) AS '经理总数' FROM t_employees; #106
SELECT COUNT(*) FROM t_employees; # 总行数
  • 注意：聚合函数自动忽略null值，不进行统计。

6.2 单列总和

1
2
#统计所有员工每月的工资总和
SELECT SUM(SALARY) FROM t_employees;

6.3 单列平均值

1
2
#统计所有员工每月的平均工资
SELECT AVG(SALARY) FROM t_employees;

6.4 单列最大值

1
2
#统计所有员工中月薪最高的工资
SELECT MAX(SALARY) FROM t_employees;

6.5 单列最小值

1
2
#统计所有员工中月薪最低的工资
SELECT MIN(SALARY) FROM t_employees;

7. 分组查询

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据列;

关键字 说明
GROUP BY 分组依据，如有WHERE必须在 WHERE 之后生效（WHERE条件可省略）

7.1 查询各部门的总人数

1
2
3
4
5
#思路：
#1.先按照部门编号进行分组（分组依据是 department_id）
#2.再针对各部门的人数进行统计（count）
SELECT DEPARTMENT_ID, COUNT(EMPLOYEE_ID) FROM t_employees
GROUP BY DEPARTMENT_ID;

7.2 查询各部门的平均工资

1
2
3
4
5
#思路：
#1.先按照部门编号进行分组（分组依据department_id）。
#2.针对每个部门进行平均工资统计（avg）。
SELECT DEPARTMENT_ID, AVG(SALARY) AS '平均工资' FROM t_employees
GROUP BY DEPARTMENT_ID;

7.3 查询各个部门、各个岗位的人数

1
2
3
4
5
6
#思路：
#1.先按照部门编号进行分组（分组依据 department_id）。
#2.再按照岗位名称进行分组（分组依据 job_id）。
#3.针对每个部门中的各个岗位进行人数统计（count）。
SELECT DEPARTMENT_ID AS '部门', JOB_ID AS '岗位', COUNT(EMPLOYEE_ID) AS '人数' FROM t_employees
GROUP BY DEPARTMENT_ID, JOB_ID;

7.4 常见问题 - error 示例

1
2
3
#查询各个部门id、总人数、first_name
SELECT DEPARTMENT_ID, COUNT(EMPLOYEE_ID), FIRST_NAME FROM t_employees
GROUP BY DEPARTMENT_ID; #error, 分组依据列：DEPARTMENT_ID  聚合函数列：EMPLOYEE_ID 不能出现其他列
  • 注：分组查询中，select显示的列只能是 分组依据列 或 聚合函数列，不能出现其他列。

8. 分组过滤查询

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则;

关键字 说明
HAVING 过滤规则 过滤规则：定义对分组后的数据进行过滤

8.1 统计部门的最高工资

1
2
3
4
5
6
7
8
9
10
11
#统计60、70、90号部门的最高工资
#思路：
#1). 确定分组依据（department_id）
#2). 对分组后的数据，过滤出部门编号是60、70、90信息
#3). max()函数处理
SELECT DEPARTMENT_ID, MAX(SALARY) FROM t_employees
GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID=60 OR DEPARTMENT_ID=70 OR DEPARTMENT_ID=90;
# group确定分组依据department_id 
# having过滤出60 70 90部门
# select查看部门编号和max函数。

9. 限定查询

语法：SELECT 列名 FROM 表名 LIMIT 起始行,查询行数;

关键字 说明
LIMIT offset_start，row_count 限定查询结果的起始行(0为第1行)，查询总行数

9.1 查询前 5 行记录

1
2
3
4
# 1.查询数据库表中前 5 个员工的信息
SELECT * FROM t_employees LIMIT 0,5; # 0行开始的5行
SELECT * FROM t_employees LIMIT 5,5; # 5行开始的5行
SELECT * FROM t_employees LIMIT 10,5; # 10行开始的5行
  • 注意：起始行是从 0 开始，代表了第 1 行。第二个参数代表的是从指定行开始查询几行。

9.2 查询范围记录

1
2
#查询表中从第 4 条开始，查询 10 行
SELECT * FROM t_employees LIMIT 3,10;

9.3 分页查询

分页查询：一页显示 10 条，一共查询三页

1
2
3
4
5
6
#思路：第一页是从 0 开始，显示 10 条
SELECT * FROM t_employees LIMIT 0,10;
#第二页是从第 10 条开始，显示 10 条
SELECT * FROM t_employees LIMIT 10,10;
#第三页是从 20 条开始，显示 10 条
SELECT * FROM t_employees LIMIT 20,10;

11. 子查询（作为条件判断）

语法：SELECT 列名 FROM 表名 WHERE 条件 ? (子查询结果值)

11.1 查询工资大于Bruce 的员工信息

1
2
3
4
5
6
# 1.先查询到 Bruce 的工资（一行一列）
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce'; #工资是 6000
# 2.查询工资大于 Bruce 的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;
# 3.将 1、2 两条语句整合
SELECT * FROM t_employees WHERE SALARY > （SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce' ）;
  • 注意：将子查询 “一行一列”的结果作为外部查询的条件，做第二次查询；
  • 子查询得到一行一列的结果，才能作为外部查询的等值或不等值判断条件。

12. 子查询（作为枚举查询条件）

语法：SELECT 列名 FROM 表名 WHERE 列名 IN(子查询结果列);

12.1 查询与姓’King’同一部门的员工信息

1
2
3
4
5
6
7
8
9
#思路：
# 1.先查询 King 所在的部门编号（多行单列）
SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME='King'; # 80,90
# 2.将80、90作为枚举查询条件
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees
WHERE DEPARTMENT_ID IN(80,90);
# 整合：查询与姓'King'同一部门的员工信息
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY FROM t_employees
WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME='King'); #N行一列
  • 将子查询 多行单列的结果作为外部查询的枚举查询条件，做第二次查询

12.2 工资高于60部门所有人的信息

1
2
3
4
5
6
7
8
# 1.查询部门编号为 60 的工资信息
SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID=60;
# 2.整合：查询高于部门 60 的员工工资的所有人的信息（ALL）——高于所有
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees
WHERE SALARY > ALL(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID=60);
# 2.整合：查询高于部门 60 的员工工资的所有人的信息（ANY）——高于部分
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees
WHERE SALARY > ANY(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID=60);
  • 注意：当子查询结果集形式为多行单列时可以使用 ANY 或 ALL 关键字

13. 子查询（作为一张表）

SELECT 列名 FROM (子查询结果表) AS 表别名;

13.1 查询员工表中工资排名前 5 名的员工信息

1
2
3
4
5
6
7
8
# 思路：
# 查询员工表中工资排名前 5 的员工信息
# 1.先对所有员工依据工资进行排序，生成表（多行多列）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees ORDER BY SALARY DESC;
# 2.将子查询得到的结果做二次查询，查前 5 行（字表需要定义别名，如 tmp）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM t_employees ORDER BY SALARY DESC) AS tmp
LIMIT 0,5;
  • 将子查询多行多列的结果作为外部查询的一张表，做第二次查询；
  • 子查询返回的表需要一个子表别名才能嵌套查询。

14. 合并查询

  • SELECT * FROM 表名1 UNION SELECT * FROM 表名2

  • SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2

14.1 合并两张表的结果（去除重复记录）

1
2
#合并两张表的结果，去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
  • 注意：合并结果的两张表，列数必须相同，行内容向下追加，列的数据类型可以不同

14.2 合并两张表的结果（保留重复记录）

1
2
#合并两张表的结果，不去除重复记录（显示所有）
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

经验：

  • 使用 UNION 合并结果集，自会动去重，即去除两张表中重复的数据；
  • 使用 UNION ALL 合并结果集，不会自动去重，即不会去除两张表的重复数据。

15. 多表设计和查询

分类：

  • 一对一，将一张表的主键为外键指向另一张表的主键。

  • 一对多，在多的一方，添加一个字段作为外键，指向一的一方的主键。

  • 多对多，创建一个中间表，中间表作为多的一方，表1和表2作为一的一方，建立中间表和表1的一对多关系，建立中间表和表2的一对多关系。

外键约束原因：

  1. 为了在数据库中表现实体之间的关系；
  2. 保证数据的完整性。

15.0 多表设计

① 一对一

表A中的一条记录与表B中的一条记录，相互对应。如 公司(1) - 地址(1)

设计原则：

  1. 用一张表的主键指向另一张表的主键；
  2. 主键作为外键约束。
1
alert table 表B add foreign key(表B的主键) references 表A(表A的主键);

② 一对多

表A里面的一条记录，对应表B里的多条记录；表A是一的一方，表B是多的一方。如 部门(1) - 员工(多)

设计原则：

  1. 在多的一方添加一个字段；
  2. 该字段指向一的一方的主键，即该字段为外键约束，保证了数据的完整性。
1
alert table 多的一方 add foreign key(多的一方的字段名) references 一的一方(主键);

③ 多对多

表A里的一条记录对应表B里多条记录；表B里的一条记录对应表A里多条记录。如 学生(多) - 课程(多)

设计原则：

  1. 创建中间表；
  2. 表A和表B为一的一方，中间表为多的一方；
  3. 表A和中间表、表B和中间包，分别建立一对多的外键约束关系。
1
2
alert table 中间表 add foreign key(中间表对应A的字段名) references 一的一方(表A主键);
alert table 中间表 add foreign key(中间表对应B的字段名) references 一的一方(表B主键);

在这里插入图片描述

语法：SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件

  • 内连接方式: INNER JOIN … ON …
  • 三表内连接: INNER JOIN … INNER JOIN … ON …
  • 左外连接: LEFT JOIN … ON …
  • 右外连接: RIGHT JOIN … ON …

15.1 内连接查询（INNER JOIN … ON …）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 如果只做了连接，那么产生的表叫笛卡尔积：
# A={a, b}  B={1, 2}  >>  {(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}    error:不是我们需要的

# 1.查询两张表所有有部门的员工信息（不包括没有部门的员工） 
# MySQL 标准
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_NAME FROM t_employees, t_departments
WHERE t_employees.`DEPARTMENT_ID` = t_departments.`DEPARTMENT_ID`;
# SQL 标准【通用】
# 注意：① 两张表查询，要有关联条件。但关联条件的列重复了，需要明确查询是哪个表的列
# 注意：② 表名比较长，表名多次重复出现容易混淆，可以给表一个别名
SELECT EMPLOYEE_ID, FIRST_NAME, d.`DEPARTMENT_ID`, DEPARTMENT_NAME FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`;

# 2.查询两张表所有岗位的员工信息，显示岗位名称
SELECT EMPLOYEE_ID, FIRST_NAME, j.`JOB_ID`, JOB_TITLE FROM t_employees AS e
INNER JOIN t_jobs AS j
ON e.`JOB_ID` = j.`JOB_ID`; # 列内容元素重合
  • 经验： SQL 标准，与其他关系型数据库通用

15.2 三表连接查询（INNER JOIN … INNER JOIN … ON …）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 三表连接查询 >>
# 查询所有员工工号、名字、部门名称、部门所在国家ID
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_NAME, CITY FROM t_employees AS e
INNER JOIN t_departments AS d  # 中间表承上启下
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID` # 元素重合列
INNER JOIN t_locations AS l
ON d.`LOCATION_ID` = l.`LOCATION_ID`; # 元素重合列

# 多表连接查询 >>
# 查询所有员工工号、名字、部门名称、部门所在城市、城市所在国家
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_NAME, CITY, COUNTRY_NAME FROM t_employees AS e
INNER JOIN t_departments AS d  # 中间表承上启下
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID` # 元素重合列
INNER JOIN t_locations AS l # 中间表承上启下
ON d.`LOCATION_ID` = l.`LOCATION_ID` # 元素重合列
INNER JOIN t_countries AS c
ON l.`COUNTRY_ID` = c.`COUNTRY_ID`; # 元素重合列
  • 经验：多表查询时，需要明确哪个表与其他表之间有关联的列（列元素重合）

15.3 左外连接（LEFT JOIN … ON …）

1
2
3
4
# 查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中,部门名称以 NULL 填充）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_NAME FROM t_employees AS e
LEFT JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`;  # 匹配项,左表为主
  • 注意：左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果
  • 匹配不到，则返回 NULL 值填充

15.4 右外连接（RIGHT JOIN … ON …）

1
2
3
4
# 查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中，员工信息以 NULL 填充）
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_NAME FROM t_employees AS e
RIGHT JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`; # 匹配项,右表为主
  • 注意：右外连接，是以右表为主表，依次向左匹配，匹配到，返回结果
  • 匹配不到，则返回 NULL 值填充
05_数据库 > 01_MySQL
#语法 #MySQL
02-查询语法大全
https://janycode.github.io/2017/06/18/05_数据库/01_MySQL/02-查询语法大全/
作者
Jerry(姜源)
发布于
2017年6月18日
许可协议