MySQL基础学习篇(2)——DQL

MySql基础学习

注明&＃xff1a;文章笔记均来自哔哩哔哩视频学习

七、数据查询【重点】

7.1 数据库表的基本结构

关系数据库是以表格(Table)进行数据存储,表格由“行”和“列”组成

• 经验&＃xff1a;执行查询语句返回的结果是一张虚拟表

7.2 基本查询

语法:SELECT列名FROM表名

7.2.1 查询部分列

# 查询员工表中所有员工的编号】你名字、邮箱
select employee_id fist_name, email
from t_employee


7.2.2查询所有列

# 查询所有列
# 1、使用*的方式
SELECT * FROM t_employees;
# 2、使用列名的方式
SELECT 所有列的列名 FROM t_employees;


• 注意&＃xff1a;生产环境下&＃xff0c;优先使用列名查询&＃xff0c;*的方式需要转换成全列名。效率低&＃xff0c;可读性差

7.2.3 对列中的数据进行运算

# 查询员工的编号、名字、年薪
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY * 12 FROM t_employees


• % 是占位符。而并非模运算符

7.2.4 列的别名

列 as 列名

SELECT EMPLOYEE_ID &＃39;编号&＃39;,FIRST_NAME AS &＃39;姓氏&＃39;,SALARY * 12 AS &＃39;年薪&＃39; FROM t_employees;


7.2.5 查询结果去重

distinct 列名

# 查询员工表中有多少个经理
SELECT DISTINCT MANAGER_ID FROM t_employees;


7.3 排序查询

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

7.3.1 依据单列排序

# 对员工工资进行升序排序
SELECT EMPLOYEE_ID,salary FROM t_employees ORDER BY salary &＃43; 0 ASC;
# 对员工工资进行降序排序
SELECT EMPLOYEE_ID,salary FROM t_employees ORDER BY salary &＃43; 0 DESC;

7.3.2 依据多列排序

# 按照工资高低进行降序排序&＃xff0c;如果工资相等&＃xff0c;按照员工编号升序进行排序
SELECT EMPLOYEE_ID,salary FROM t_employees ORDER BY salary &＃43; 0 DESC, EMPLOYEE_ID DESC;

7.4 条件查询

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

7.4.1 等值判断

# 查询符合条件的数据
SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY &＃61; 11000SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY &＃61; 24000


• 注意&＃xff1a;与java不同&＃xff08;&＃61;&＃61;&＃xff09;&＃xff0c;mysql中等值判断使用&＃61;

7.4.2 逻辑判断&＃xff08;and 、or、 not&＃xff09;

SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY &＃61; 11000 AND COMMISSION_PCT &＃61; 0.30 AND EMPLOYEE_ID &＃61; &＃39;148&＃39;;SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY &＃61; 11000 OR COMMISSION_PCT &＃61; 0.30;SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE NOT SALARY &＃61; 11000;


7.4.3 不等值判断&＃xff08;>、 <、>&＃61;、<&＃61;、<>、!&＃61;&＃xff09;

SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY !&＃61; 11000;SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY <> 11000;# 查询工资区间在[6000, 10000]之间
SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY >&＃61; 6000 AND salary <&＃61; 10000;


7.4.4 区间判断(between 起始and 终止)

# 查询工资区间在[6000, 10000]之间
SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY BETWEEN 6000 AND 10000;


• 注意&＃xff1a;在区间判断中&＃xff0c;小值在前&＃xff0c;大值在后&＃xff0c;反之&＃xff0c;得不到结果

7.4.5 null值判断&＃xff08;is null、is not null&＃xff09;

# 查询出经理编号为null的员工信息
SELECT employee_id, FIRST_NAME,MANAGER_ID
FROM t_employees
WHERE MANAGER_ID IS NULL;SELECT employee_id, FIRST_NAME,MANAGER_ID
FROM t_employees
WHERE MANAGER_ID IS NOT NULL;


7.4.6 枚举查询&＃xff08;IN&＃xff08;值1&＃xff0c; 值2&＃xff0c;值3&＃xff09;&＃xff09;


# 查询部门编号为70,80,90 的员工信息
SELECT EMPLOYEE_ID, FIRST_NAME,SALARY,department_id
FROM t_employees
WHERE department_id &＃61; 70 OR department_id &＃61; 80 OR department_id &＃61; 90;SELECT EMPLOYEE_ID, FIRST_NAME,SALARY,department_id
FROM t_employees
WHERE department_id IN (70,80,90);
# in 的效率在大数据面前较低


7.4.7 模糊查询

• LIKE_&＃xff08;单个任由字符&＃xff09;

​ 列名 LIKE ‘张__’

• LIKE %(任意长度的任意字符)

  列名 LIKE ‘张%’

• 注意&＃xff1a;模糊查询只能和LIKE关键字结合使用

# 查询L开头的三个字的员工信息
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM t_employees
WHERE FIRST_NAME LIKE &＃39;L__&＃39;;
# 查询所有以L开头的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees
WHERE first_name LIKE &＃39;张%&＃39;;


7.4.8 分支结构查询

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


• 注意&＃xff1a;通过使用case end进行条件判断&＃xff0c;每条数据对应生成一个值
• 经验&＃xff1a;类似java中的switch

# 查询员工信息
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,department_id,
CASEWHEN SALARY >&＃61; 10000 THEN &＃39;A&＃39;WHEN SALARY >&＃61; 8000 AND SALARY <10000 THEN &＃39;B&＃39;WHEN SALARY >&＃61; 6000 AND SALARY <8000 THEN &＃39;C&＃39;WHEN SALARY >&＃61; 4000 AND SALARY <6000 THEN &＃39;D&＃39;ELSE &＃39;E&＃39;
END AS "level"
FROM t_employees;


7.5 时间查询

select 时间函数&＃xff08;[参数列表]&＃xff09;

• 经验&＃xff1a;执行时间函数查询&＃xff0c;会自动生成一张虚表

# 当前系统的时间
SELECT SYSDATE();
# 当前系统的ri期
SELECT CURDATE();
# 获取系统时间
SELECT CURTIME();
# 获取指定日期为一年中的第几周
SELECT WEEK(SYSDATE());
# 获取指定日期忠厚的年份
SELECT YEAR(SYSDATE());
# 获取小时值
SELECT HOUR(CURTIME());
# 获取分钟值
SELECT MINUTE(CURTIME());
# 指定日期之间的相隔天数
SELECT DATEDIFF(&＃39;2020-4-1&＃39;,&＃39;2019-4-1&＃39;);
# 计算date日期加上n天后的日期
SELECT ADDDATE(&＃39;2020-4-1&＃39;, 5);


7.6 字符串查询

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


# 多个字符串拼接
SELECT CONCAT(&＃39;My&＃39;, &＃39;S,&＃39;, &＃39;QAL&＃39;);
SELECT CONCAT(FIRST_NAME,&＃39;-&＃39;, last_name) FROM t_employees;
# 字符串替换
SELECT INSERT(&＃39;这是一个数据库&＃39;,3, 2, &＃39;MySql&＃39;)
# 字符串转小写
SELECT LOWER(&＃39;LOWER&＃39;)
# 字符串转大写
SELECT UPPER(&＃39;shdiuashdui&＃39;)
# 指定内容截取
SELECT SUBSTRING(&＃39;JaavaMySqlStrSub&＃39;,6,6);
# MySqlS


7.7 聚合函数

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

• 经验&＃xff1a;对多条数据的单列进行统计&＃xff0c;返回统计后的一行结果

# 聚合函数
# 求单列所有数据的和
SELECT SUM(SALARY) FROM t_employees;
# 求单列所有数据的平均值
SELECT AVG(SALARY) FROM t_employees;
# 求单列最大值
SELECT MAX(SALARY &＃43; 0) FROM t_employees;
# 求单列最小值
SELECT MIN(salary &＃43; 0) FROM t_employees;
# 求总行数 员工总数
SELECT COUNT(EMPLOYEE_ID) FROM t_employees;
# 统计有提成的人数 会自动忽略null值&＃xff0c;不进行统计
SELECT COUNT(COMMISSION_PCT) FROM t_employees;


• 注意&＃xff1a;聚合函数会自动忽略null值&＃xff0c;不进行统计

7.8 分组查询

语法&＃xff1a;SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据(列);

# 查询各个部门的额总人数
SELECT department_id, COUNT(employee_id)
FROM t_employees
GROUP BY department_id;
# 查询各个部门的平局工资
SELECT department_id, AVG(salary)
FROM t_employees
GROUP BY department_id;
# 查询各个部门、各个岗位的总人数
SELECT department_id,job_id, COUNT(employee_id)
FROM t_employees
GROUP BY department_id, job_id;
# 常见问题&＃xff1a;查询各个部门id&＃xff0c;总人数、first_name
SELECT department_id,COUNT(employee_id), first_name
FROM t_employees
GROUP BY department_id;


• 分组查询中&＃xff0c;select显示的列只能是分组依据列&＃xff0c;或者聚合函数列&＃xff0c;不能出现其他列

7.9 分组过滤查询

语法&＃xff1a;SELECT 列名 FROM 表名 WHERE 条件 GROUNP BY 分组列 HAVING 过滤规则

统计部门的最高工资

#思路
#1.确定分组依据&＃xff08;DEPARTMENT_ID&＃xff09;
#2.对分组后的数据&＃xff0c;过滤出部门编号是60、70、90信息
#3.MAX()函数
SELECT DEPARTMENT_ID,MAX(SALARY)
FROM t_employees GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID IN (60,70,90);
#GROUP确定分组依据DEPARTMENT_ID
#HAVING过滤出60&＃xff0c;70&＃xff0c;90的部门
#SELECT查看部门编号和MAX函数


7.10 限定查询

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

7.10.1 查询前5行记录

#查询表中前五名员工的所有信息
SELECT * FROM t_employees LIMIT 0,5;


• 注意&＃xff1a;起始行是从0开始&＃xff0c;代表了第一行&＃xff0c;第二个参数代表的是从指定行开始查询几行。

7.10.2 查询范围记录

#查询表中从第四行开始&＃xff0c;查询10行
SELECT * FROM t_employees LIMIT 3,10;


7.10.3 LIMIT典型应用

分页查询&＃xff1a;一页显示10条&＃xff0c;一共查询三页。

#思路&＃xff1a;第一页是从0开始&＃xff0c;显示10条
SELECT * FROM t_employees LIMIT 0,10;
#第二页是从10条开始&＃xff0c;显示10条
SELECT * FROM t_employees LIMIT 10,10;
#第三页是从20条开始&＃xff0c;显示10条
SELECT * FROM t_employees LIMIT 20,10;


• 在分页应用场景中&＃xff0c;起始行是变化的&＃xff0c;但是一页显示的页数是不变的

7.11 查询总结

7.11.1 SQL语句编写顺序

COPYSELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列(ASC|DESC) LIMIT 起始行,查询行数

7.11.2 SQL语句执行顺序

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


7.12 子查询&＃xff08;作为条件判断&＃xff09;

语法&＃xff1a;SELECT 列名 FROM 表名 WHERE 条件(子查询结果)

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

#1.先查询到Bruce的工资&＃xff08;一行一列&＃xff09;
SELECT SALARY FROM t_employees WHERE FIRST_NAME &＃61; &＃39;Bruce&＃39;;
#2.查询工资大于Bruce的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;
#3.将前两句整合
SELECT * FROM t_employees
WHERE SALARY >
(SELECT SALARY FROM t_employees WHERE FIRST_NAME &＃61; &＃39;Bruce&＃39;);


• 注意&＃xff1a;将子查询“一行一列”的结果作为外部查询的条件&＃xff0c;做第二次查询。
• 子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值判断条件

7.13 子查询&＃xff08;作为枚举查询的条件&＃xff09;

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

7.13.1 查询与名为“KING”同一部门的员工信息

#思路
#1.先查询KING所在的部门编号
SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME &＃61; &＃39;King&＃39;;#80,90
#2.再查询80&＃xff0c;90号部门的员工信息
SELECT * FROM t_employees WHERE DEPARTMENT_ID IN(80,90);
#3.SQL:合并
SELECT * FROM t_employees
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME &＃61; &＃39;King&＃39;);


• 将子查询“多行一列”的结果作为外部查询的枚举查询条件&＃xff0c;做第二次查询。

7.13.2 工资高于60编号部门的员工信息

#1.查询60编号部门所有人的工资&＃xff08;多行单列&＃xff09;
SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID &＃61; 60;
#2.查询工资高于60编号部门所有人的工资的员工信息&＃xff08;高于所有&＃xff09;
SELECT * FROM t_employees
WHERE SALARY >
ALL(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID &＃61; 60);
#3.查询工资高于60编号部门的员工信息&＃xff08;高于部分&＃xff09;
SELECT * FROM t_employees
WHERE SALARY >
ANY(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID &＃61; 60);


• 注意&＃xff1a;当子查询结果集形式为多行单列时可以使用ANY或ALL关键字。

• ALL表示查询高于部门所有员工工资的员工信息&＃xff1b;ANY表示查询只需要高于部门中任意一名员工工资的员工信息

7.14 子查询&＃xff08;作为一张表&＃xff09;

查询到一张多行多列的临时表&＃xff0c;这张表也可以作为查询数据源。

语法&＃xff1a;SELECT 列名 FROM (子查询的结果集) WHERE 条件;

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

#思路&＃xff1a;
#1.先对所有员工的薪资进行排序&＃xff08;排序后的临时表&＃xff09;
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC;
#2.再查询临时表中前5行员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM (临时表) LIMIT 0,5;
#3.合并
SELECT * FROM
(SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC) AS 临时表 LIMIT 0,5;


• 将子查询“多行多列“的结果作为外部查询的一张表&＃xff0c;做第二次查询。
• 注意&＃xff1a;子查询作为临时表&＃xff0c;需要为其赋予一个临时表名。

7.15 合并查询

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

• 返回的查询结果是将表1的结果和表2的结果做了一个合并。

15.1 合并两张表的结果&＃xff08;去除重复记录&＃xff09;

#合并两张表的结果&＃xff0c;去除重复记录
SELECT * FROM T1 UNION SELECT * FROM T2;


• 注意&＃xff1a;合并结果的两张表&＃xff0c;列数必须相同&＃xff0c;列的数据类型可以不同。

列数必须相同是因为该合并做了一个纵向合并&＃xff0c;两个4列多行的结果集表合并后也是一个4列多行的表&＃xff0c;但是合并后的列名以第一个结果集表为准。

7.15.2 合并两张表的结果&＃xff08;保留重复记录&＃xff09;

#合并两张表的结果&＃xff0c;不去除重复记录&＃xff08;显示所有&＃xff09;
SELECT * FROM T1 UNION ALL SELECT * FROM T2;


该合并结果会保留两张查询表中重复的记录&＃xff0c;而UNION只保留第一个不重复的记录

7.16 表连接查询

语法&＃xff1a;SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件

7.16.1 内连接查询&＃xff08;INNER JOIN ON&＃xff09;

#1.查询所有员工的职员和职位信息 SQL标准
SELECT * FROM t_employees INNER JOIN t_jobs
ON t_employees.JOB_ID &＃61; t_jobs.JOB_ID;
#2.查询所有员工的职员和职位信息 MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID &＃61; t_jobs.JOB_ID;


如果没有指定连接条件&＃xff0c;则会造成笛卡尔积的结果&＃xff0c;第一张表中的每行数据都会和第二张表进行连接。

• 经验&＃xff1a;在MySQL中&＃xff0c;第二种方式也可以作为内连接查询&＃xff0c;但是不符合SQL标准。
• 而第一种属于SQL标准&＃xff0c;与其他关系型数据库通用。

7.16.2 三表连接查询

#查询所有员工工号、名字、部门名称、部门所在国家ID
SELECT EMPLOYEE_ID,FIRST_NAME,t_departments.DEPARTMENT_NAME,t_locations.COUNTRY_ID
FROM t_employees
INNER JOIN t_departments ON
t_employees.DEPARTMENT_ID&＃61;t_departments.DEPARTMENT_ID
INNER JOIN t_locations ON
t_departments.LOCATION_ID&＃61;t_locations.LOCATION_ID;


该语句查询出来的结果没有员工编号为178的员工信息&＃xff0c;该名员工的部门ID为NULL&＃xff0c;不符合连接条件&＃xff0c;所以不包含在匹配出的结果集中&＃xff0c;你可以自己动手尝试并查看结果。之所以提这个是因为可以与下节的语句区分开来。

7.16.3 左外连接&＃xff08;LEFT JOIN ON&＃xff09;

#查询所有员工的工号、名字、工资及其对应的部门名称&＃xff08;没有部门的员工也在查询结果中&＃xff0c;部门名称以NULL值补充&＃xff09;
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_NAME
FROM t_employees
LEFT JOIN t_departments
ON t_departments.DEPARTMENT_ID&＃61;t_employees.DEPARTMENT_ID;


该语句查询出来的结果包含没有部门的178号员工&＃xff0c;DEPARTMENT_NAME的值被填充为NULL。

• 注意&＃xff1a;左外连接&＃xff0c;是以左表作为主表&＃xff0c;依次向右匹配&＃xff0c;匹配到则返回结果&＃xff1b;
• 匹配不到则返回NULL值填充。

7.16.4 右外连接&＃xff08;RIGHT JOIN ON&＃xff09;

#查询所有部门信息&＃xff0c;以及此部门中的所有员工信息&＃xff08;没有员工的部门也在查询结果中&＃xff0c;员工信息以NULL值填充&＃xff09;
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_NAME
FROM t_employees
RIGHT JOIN t_departments
ON t_departments.DEPARTMENT_ID&＃61;t_employees.DEPARTMENT_ID;


该查询结果以部门表作为主表&＃xff0c;有些部门没有员工&＃xff0c;其员工信息全部为NULL。

• 注意&＃xff1a;右外连接&＃xff0c;是以右表为主表&＃xff0c;依次向左匹配&＃xff0c;匹配到返回结果。
• 匹配不到&＃xff0c;则返回NULL值填充。