大道之行，天下为公。这篇文章主要讲述#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备相关的知识，希望能为你提供帮助。
@TOC
1.DQL语言的学习接下来的学习按层层渐进的方式。既然我们现在是来学习查询的操作，我们就得要有数据对吧，这里直接把数据先准备给大家吧，这里就可以不用来连插入修改其他的命令，主要是来练习查询的。这里准备的是我在尚硅谷网上视频中学习数据库的数据。非常感谢，我自己也学习得很深刻，讲解的也非常详细。本来是想直接把数据写在这里的，但是因为真的太长了。
文章图片
大家有条件的话，还是去我的百度网盘链接去下载吧，也不大，很快就下载完的了。然后怎么操作就教下大家
文章图片
现在默认大家执行完啦，大家都有数据啦，那进行接下来的学习吧。
进阶1. 基础查询 1.1 基础语法
- 先来看看它的语法吧：
SELECT 查询列表（字段名1， 字段名2...） FROM 表名;
这个查询出来，其实有点类似java中的System.out.println()`打印到控制台一样。
还有一个语法就是，如果我想查询表中所有的字段名，难道我们要一个一个手敲出来吗，当然不是啦。我们可
以用*来查询表中所有的字段名，也就是查询所有。
SELECT * FROM 表名;
- 特点：
- 查询列表可以是常量值、可以是表达式、可以是字段、可以是函数。
- 我们查询完的结果 ，是一个虚拟的表格，不是真实存在。
- 接下来就进行实战操作吧：
-- 1.查询表中的单个字段 SELECT `name` FROM beauty; -- 这里的name是关键字，但是也被我们用来做字段，虽然可以执行，但是我们可以通过加``这个符号，避免歧义。-- 2.查询表中的多个字段 SELECT `name` , sex FROM beauty; -- 3.查询表中的所有字段 SELECT * FROM beauty;
文章图片
- 这里需要注意的一个细节：
因为有些字段它可能是mysql中的关键字，我们可以为了避免歧义，可以加上``这个字符即：`name`。
- 除了查询字段，还可以查询常量值、表达式、函数。
#4.查询常量值 SELECT 100; SELECT \'john\'; #5.查询表达式 SELECT 100%98; #6.查询函数 SELECT VERSION();
这里就不显示结果，自己尝试一下就可以得到结果啦。
- 语法：
SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名;
注意： 这里的关键字AS可以省略，但是其中的空格一定要有，不要就会报错。
- 特点：
- 查询时给列、表指定别名需要使用AS关键字。
- 使用别名的好处是方便观看和处理查询到的数据。
- 如果要查询的字段有重名的情况，使用别名可以区分开来。
- 演示一下：
-- 原来的查询 SELECT last_name, first_name FROM employees; -- 使用 AS起别名 SELECT last_name AS 姓, first_name AS 名 FROM employees; -- 省略AS,空格不能省略 SELECT last_name 姓, first_name 名 FROM employees;
文章图片
DISTINCT。- 语法：
SELECT DISTINCT 字段名 FROM 表名;
- 演示下：
-- 查询员工表中涉及到的所有的部门编号 SELECT department_id FROM employees; SELECT DISTINCT department_id FROM employees;
文章图片
- 语法：
-- 某列数据和固定值运算SELECT 字段名 + 值 FROM 表名; -- 某列数据和其他列数据参与运算SELECT 字段1 + 字段2 FROM 表名; -- 直接运算SELECT 值 + 值;
- 运算符中
+特点：
- 如果两个操作数都为数值型，则做加法运算。
- 只要其中一方为字符型，试图将字符型数值转换成数值型，其中如果转换成功，则继续做加法运算，如果转换失败，则将字符型数值转换成
0。 - 只要其中一方为NULL，则结果肯定为NULL。
- 演示下吧：
-- 某列数据和固定值运算SELECT lowest_sal + 20 FROM job_grades; -- 某列数据和其他列数据参与运算SELECT lowest_sal + highest_sal FROM job_grades; -- 直接运算-- 如果两个操作数都为数值型，则做加法运算。SELECT 90 + 10; -- 只要其中一方为字符型，试图将字符型数值转换成数值型，-- 其中如果转换成功，则继续做加法运算，SELECT \'99\' + 88 ; -- 如果转换失败，则将字符型数值转换成0SELECT \'hello\' + 100; -- 只要其中一方为null，则结果肯定为nullSELECT NULL + 100;
文章图片
WHERE。- 语法：
SELECT 查询列表 FROM 表名 WHERE 筛选条件;
那这个筛选条件怎么写呢？它可以有以下分类：
- 按条件表达式筛选
简单条件运算符：& gt; & lt; = != & lt; & gt; & gt; = & lt; =。
- 按逻辑表达式筛选
逻辑运算符：& & || ! and or not。
- 模糊查询：
like < br/> between andinis null。
怎么使用呢，接下来就详细讲解。
- 按条件表达式筛选
& gt; & lt; =：大于，小于，等于。!= & lt; & gt;：这两个都是不等于。& gt; = & lt; =：大于等于， 小于等于。
-- 查询工资>
12000的员工名和工资
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary >
12000;
-- 查询部门编号不等于90号的员工名和部门编号
SELECT last_name 员工名, department_id 部门编号 FROM employees
WHERE department_id <
>
90;
SELECT last_name 员工名, department_id 部门编号 FROM employees
WHERE department_id != 90;
-- 查询员工编号<
=110 的员工名和员工编号
SELECT last_name 员工名, employee_id 员工编号 FROM employees
WHERE employee_id <
= 110;
文章图片
2.3 逻辑表达式筛选
& & and：多个条件同时满足。|| or：多个条件其中一个满足。! not：不满足。- 一般我们都是用
and or not比较多。
-- 查询工资z在10000到20000之间的员工名、工资
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary >
= 10000 AND salary <
= 20000;
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary >
= 10000 &
&
salary <
= 20000;
-- 查询工资低于10000，或者工资高于20000的员工名、工资
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary <
= 10000 OR salary >
= 20000;
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary <
= 10000 || salary >
= 20000;
-- 查询员工编号不是在100和150之间的员工名、员工编号
SELECT last_name 员工名, employee_id 员工编号 FROM employees
WHERE NOT(employee_id >
= 100 AND employee_id <
= 150);
SELECT last_name 员工名, employee_id 员工编号 FROM employees
WHERE !(employee_id >
= 100 &
&
employee_id <
= 150);
文章图片
2.4 模糊查询 2.4.1 like关键字
LIKE：通常与两个通配符一起使用：
%：任意多个字符,包含0个字符_：任意单个字符
- 语法：
SELECT 查询列表 FROM 表名 WHERE 字段名 LIKE \'通配符字符串\';
- 具体演示下：
-- 查询员工名中以字符a开头的员工名SELECT last_name 员工名 FROM employeesWHERE last_name LIKE \'a%\'; -- 查询员工名中以字符a结尾的员工名SELECT last_name 员工名 FROM employeesWHERE last_name LIKE \'%a\'; -- 查询员工名中包含字符a的员工名SELECT last_name 员工名 FROM employeesWHERE last_name LIKE \'%a%\'; -- 查询员工名中第三个字符为s，第五个字符为i的员工名和工资SELECT last_name 员工名, salary 工资 FROM employeesWHERE last_name LIKE \'__s_i%\'; -- 查询员工名中第二个字符为_的员工名/*这里有个难点就是_这个不是通配符吗，那可以直接使用吗，当然是不行的所以我们也就需要到转义符，如Java一样可以使用\\来转义。MySQL 有个特有的方式 即 定义一个字符 后面加上关键字ESCAPE 然后定义 \'字符\'。*/SELECT last_name 员工名, salary 工资 FROM employeesWHERE last_name LIKE \'_$_%\' ESCAPE \'$\';
注意：如果我们需要查找的字符包含了我们的通配符类似等，那我们可以使用转义字符：
- 如Java一样可以使用
\\来转义。
- MySQL有个特有的方式 即 定义一个字符 后面加上关键字
ESCAPE然后定义字符。如上面所示。
文章图片
- MySQL有个特有的方式 即 定义一个字符 后面加上关键字
between and：包含临界值并且，两个临界值不要调换顺序。
- 直接演示下：
-- 查询员工编号在100到120之间的员工信息SELECT * FROM employeesWHERE employee_id BETWEEN 100 AND 120; -- 错误，不会报错，但是没有结果。SELECT * FROM employeesWHERE employee_id BETWEEN 120 AND 100;
文章图片
in：in列表的值类型必须一致或兼容，而且in列表中不支持通配符。
- 直接演示下：
-- 查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号SELECT last_name, job_id FROM employeesWHERE job_id IN( \'IT_PROT\' ,\'AD_VP\',\'AD_PRES\');
文章图片
is nul：
因为=或& lt; & gt;不能用于判断NULL值，而我们可以使用is nul或is not nul来判断NULL值。
- 直接演示下：
-- 查询没有奖金的员工名和奖金率SELECT last_name 员工名, commission_pct 奖金率 FROM employeesWHERE commission_pct IS NULL; -- 查询有奖金的员工名和奖金率SELECT last_name 员工名, commission_pct 奖金率 FROM employeesWHERE commission_pct IS NOT NULL;
文章图片
- 语法：
SELECT 查询列表 FROM 表名【where筛选条件】ORDER BY 字段名 ASC|DESC;
- 特点：
ASC代表的是升序，可以省略，而DESC代表的是降序。ORDER BY子句可以支持 单个字段（单列）、别名、表达式、函数、多个字段（多列）。ORDER BY子句在查询语句的最后面，除了limit子句。这个后面再讲。
- 实战演示下：
单个字段中的排序即单列排序：
-- 按单个字段排序(单列排序)-- 查询员工名和工资，并按工资降序SELECT last_name, salary FROM employeesORDER BY salary DESC; -- 添加筛选条件再排序-- 查询部门编号> =90的员工名、员工编号，并按员工编号降序SELECT last_name, employee_idFROM employeesWHERE employee_id > = 90ORDER BY employee_id; -- 按表达式排序-- 查询员工名、工资、年薪 按年薪降序-- 年薪= salary*12*(1+IFNULL(commission_pct,0))SELECT last_name, salary, salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC; -- 按别名排序-- 查询员工名、工资、年薪 按年薪升序 SELECT last_name, salary, salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY 年薪 ASC; -- 按函数排序-- 查询员工名、名字的长度，并且按名字的长度降序-- 长度的函数是length() 函数后面会详细介绍SELECT last_name, LENGTH(last_name) 长度FROM employeesORDER BY LENGTH(last_name) ASC;
文章图片
上面演示的都是单个字段中排序，那我们要是排序后，排序列中出现了相同的字段，那其他列不按正常排序怎么办？那我们可以多字段，多列排序吗？不用想，当然。
文章图片
那具体怎么操作呢：
-- 按多个字段排序(多列排序)-- 查询员工名和工资，要求先按工资降序，再按员工名升序SELECT last_name, salaryFROM employeesORDER BY salary DESC, last_name ASC;
文章图片
文章图片
单行函数可以分为：
- 字符函数
- 数学函数
- 日期函数
- 其他函数
- 高级函数（控制语句）
4.1.2 字符函数常见的字符函数：
| 函数 | 描述 |
|---|---|
| LENGTH | 获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节) |
| CHAR_LENGTH | 返回字符串的字符数 |
| CONCAT | 拼接字符串 |
| SUBSTR | 截取字符串 |
| INSTR | 返回子串第一次出现的索引，如果找不到返回0 |
| TRIM | 去前后空格，或者可以去前后指定字符 |
| UPPER、LOWER | 将字母变变大写或者变小写 |
| LPAD、RPAD | 用指定的字符实现（左或右）填充指定长度 |
| REPLACE | 将字符串中的源字符串换成新的字符串 |
-- length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)SELECT LENGTH(\'hello\');
-- 5SELECT LENGTH(\'中国\');
-- 6-- char_length:返回字符串的字符数SELECT CHAR_LENGTH(\'hello\');
-- 5SELECT CHAR_LENGTH(\'中国\');
-- 2-- concat：拼接字符串SELECT CONCAT(last_name, \'_\', first_name) 姓名FROM employees;
-- substr：截取-- 注意：MySQL的索引是从1开始-- 截取从指定索引处后面所有字符SELECT SUBSTR(\'小龙女和杨过\', 5);
-- 杨过-- 截取从指定索引处指定字符长度的字符SELECT SUBSTR(\'小龙女和杨过\', 1, 3);
-- 小龙女-- instr：返回子串第一次出现的索引，如果找不到返回0SELECT INSTR(\'小龙女和杨过\', \'龙女\');
-- 2SELECT INSTR(\'龙女小龙女和杨过\', \'龙女\');
-- 1SELECT INSTR(\'龙女小龙女和杨过\', \'龙男\');
-- 0 找不到返回0-- trim：去前后空格，或者可以去前后指定字符SELECT CHAR_LENGTH(\'北极星\');
-- 9SELECT CHAR_LENGTH(TRIM(\'北极星\'));
-- 3-- 可以去前后指定字符SELECT TRIM(\'a\' FROM \'aaa北aa极星aaa\');
-- 北aa极星 中间的没有去掉-- upper、lower：变大写，变小写SELECT UPPER(\'hello\');
-- HELLOSELECT LOWER(\'heLLo\');
-- hello-- lpad、rpad ：用指定的字符实现左|右填充指定长度SELECT LPAD(\'hello\', 10, \'a\');
-- aaaaahelloSELECT RPAD(\'hello\', 10, \'ab\');
-- helloababa 只到10个字符。-- 测试没有超出本来字符的呢SELECT LPAD(\'hello\', 2, \'a\');
-- he截断SELECT RPAD(\'hello\', 3, \'ab\');
-- hel 截断-- replace: 将字符串中的源字符串换成新的字符串SELECT REPLACE(\'杨过和小龙女和杨过\', \'杨过\', \'我\');
-- 我和小龙女和我需要注意的：
MySQL的索引是从
1开始。4.1.3 数学函数常见的数学函数：
| 函数 | 描述 |
|---|---|
| ROUND | 四舍五入 |
| RAND | 返回 0 到 1 的随机数 |
| CEIL | 向上取整,返回> =该参数的最小整数 |
| FLOOR | 向下取整，返回< =该参数的最大整数 |
| TRUNCATE | 截断 |
| MOD | 取余 |
-- round：四舍五入
SELECT ROUND(1.65);
-- 2
SELECT ROUND(1.45);
-- 1-- rand :返回 0 到 1 的随机数
SELECT RAND();
-- 0.6992990986437528不是固定的-- ceil：向上取整,返回>
=该参数的最小整数
SELECT CEIL(1.2);
-- 2
SELECT CEIL(1.6);
-- 2-- floor：向下取整，返回<
=该参数的最大整数
SELECT FLOOR(1.2);
-- 1
SELECT FLOOR(1.6);
-- 1-- truncate：截断
SELECT TRUNCATE(1.8999, 1);
-- 1.8
SELECT TRUNCATE(RAND(), 1);
-- 0.9 -- mod：取余
SELECT MOD(10, 3);
-- 14.1.4 日期函数常见的日期函数：
| 函数 | 描述 |
|---|---|
| NOW | 返回当前系统日期+时间 |
| CURDATE | 返回当前系统日期，不包含时间 |
| CURTIME | 返回当前时间，不包含日期 |
| DATEDIFF | 计算两个日期相差的天数 |
| YEAR、MONTH、MONTHNAME< br /> DAY、HOUR、MINUTE、SECOND | 获取指定的部分，年、月、日、小时、分钟、秒 |
| STR_TO_DATE | 将字符通过指定的格式转换成日期 |
| DATE_FORMAT | 将日期转换成字符 |
-- now：返回当前系统日期+时间SELECT NOW();
-- curdate：返回当前系统日期，不包含时间SELECT CURDATE();
-- curtime：返回当前时间，不包含日期SELECT CURTIME();
-- datediff : 计算两个日期相差的天数SELECT DATEDIFF(\'2020-01-01\', \'1988-01-01\');
SELECT DATEDIFF(\'1988-01-01\', \'2020-01-01\');
-- 可以获取指定的部分，年、月、日、小时、分钟、秒-- year：获取指定的年SELECT YEAR(NOW());
SELECT YEAR(\'2020-02-02\');
-- month：获取指定的月SELECT MONTH(NOW());
-- monthname：获取指定的月英文单词SELECT MONTHNAME(NOW());
-- day：获取指定的天SELECT DAY(NOW());
-- hour：获取指定的小时SELECT HOUR(NOW());
-- minute：获取指定的分钟SELECT MINUTE(NOW());
-- second：获取指定的秒SELECT SECOND(NOW());
-- str_to_date：将字符通过指定的格式转换成日期SELECT STR_TO_DATE(\'2020-02-02\', \'%Y-%m-%d\');
SELECT STR_TO_DATE(\'02/02 2020\', \'%m/%d %Y\');
-- date_format：将日期转换成字符SELECT DATE_FORMAT(\'2020-02-02\', \'%Y年%m月%d日\');
SELECT DATE_FORMAT(NOW(), \'%y年%c月%d日\');
SELECT DATE_FORMAT(NOW(), \'%m-%d %Y\');
注意：
【#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备】在使用
str_to_date和date_format这两个日期函数的时候，第二个参数定义的格式为：
文章图片
4.1.5 其他函数常见的其他函数：
| 函数 | 描述 |
|---|---|
| version | 返回当前MySQL的版本 |
| database | 返回当前使用的数据库 |
| user | 返回当前使用的用户 |
-- version：返回当前MySQL的版本SELECT VERSION();
-- 5.5.40-- database：返回当前使用的数据库SELECT DATABASE();
-- data_test-- user：返回当前使用的用户SELECT USER();
-- root@localhost4.1.6 高级函数（控制函数）
if函数：可以相当于if else的效果。
- 语法：
IF(条件, \'条件成立返回的值\', \'条件不成立返回的值\')
- 演示下：
SELECT IF(66> 88, \'大\', \'小\'); -- 小-- 查询工资大于20000的员工名，显示哎呦不错呦，否则显示我觉得不行，请继续加油SELECT last_name,IF(salary > 20000, \'哎呦不错呦\', \'我觉得不行，请继续加油\')FROM employees;
- 语法：
case函数：case函数有两种用法：
- 方式1：可以相当于
switch case的效果。
- 语法：
select 字段名1, 字段名2, case 要判断的字段或表达式when 值1 then 返回的值when 值2 then 返回的值...else 要显示的值n或语句n; end别名
- 用法：
/*查询员工的工资，要求部门号=30，显示的工资为2倍部门号=40，显示的工资为3倍部门号=50，显示的工资为4倍其他部门，显示的工资为原工资*/SELECT salary 原始工资,department_id,CASE department_idWHEN 30 THEN salary*2WHEN 40 THEN salary*3WHEN 50 THEN salary*4ELSE salaryEND 新工资FROM employees;
- 方式2：可以相当于多重
if的效果。
- 语法：
select 字段名1, 字段名2, case when 判断条件1 then 要显示的值1或语句1when 判断条件2 then 要显示的值2或语句2...else 要显示的值n或语句nend
- 用法：
/*查询员工的工资的情况如果工资> 20000,显示哎呦不错了如果工资> 15000,显示很可以了如果工资> 10000，显示太棒了否则，显示请继续加油*/SELECT salary,CASE WHEN salary > 20000 THEN \'哎呦不错了\'WHEN salary > 15000 THEN \'很可以了\'WHEN salary > 10000 THEN \'太棒了\'ELSE \'请继续加油\'END 工资描述FROM employees;
- 方式1：可以相当于
文章图片
| 函数 | 描述 |
|---|---|
| COUNT | 统计指定列记录数，记录为NULL的不统计 |
| SUM | 计算指定列的数值和，如果不是数值类型，那么计算结果为0 |
| AVG | 计算指定列的平均值 |
| MAX、MIN | 计算指定列的最大值 |
- 以上五个分组函数都可以忽略NULL值。
sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型。
- 都可以搭配
distinct使用，用于统计去重后的结果。
count的参数可以支持：字段、*、常量值：一般放1。
-- 1、简单使用SELECT SUM(salary) 总和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数FROM employees;
-- 691400.00， 6461.682243， 24000.00， 2100.00， 107-- 2、参数支持哪些类型SELECT SUM(last_name), AVG(last_name) FROM employees;
-- 0 , 0SELECT SUM(hiredate), AVG(hiredate) FROM employees;
-- 虽然都不会报错，但是没有什么意义SELECT MAX(last_name),MIN(last_name) FROM employees;
-- Zlotkey, AbelSELECT MAX(hiredate),MIN(hiredate) FROM employees;
-- 2016-03-03 00:00:00, 1992-04-03 00:00:00SELECT COUNT(commission_pct) FROM employees;
-- 35 SELECT COUNT(last_name) FROM employees;
-- 107 -- 3、是否忽略nullSELECT commission_pct FROM employees;
-- 此行有包含null值SELECT COUNT(commission_pct) FROM employees;
-- 35忽略了null值SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;
-- 0.40， 0.10 忽略了null值SELECT SUM(commission_pct) ,AVG(commission_pct), SUM(commission_pct)/35, SUM(commission_pct)/107 --忽略了null值FROM employees;
-- 7.80，0.222857， 0.222857 ， 0.072897-- 4、和distinct搭配SELECT salary FROM employees;
-- 此行有包含重复值SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
-- 397900.00， 691400.00SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
-- 57 ， 107-- 5、count函数的详细介绍SELECT COUNT(salary) FROM employees;
-- 107SELECT COUNT(*) FROM employees;
-- 107SELECT COUNT(1) FROM employees;
-- 107SELECT COUNT(commission_pct) FROM employees;
-- 35 不记录为null-- 6、和分组函数一同查询的字段有限制SELECT employee_id FROM employees;
-- 多行SELECT AVG(salary),employee_idFROM employees;
-- 6461.682243, 100 只显示一行。注意：
count(*)一般用于统计个数，相对于参数写字段的话，因为字段可能有时候里面会有NULL，所以不会进行统计，无法正确的统计表中全部的数据。- 和分组函数一同查询的字段有限制，因为分组函数查询出来是一行，而其他字段查询的出来是多行，但是MySQL是不会报错，它是直接显示一行数据，所以没什么意义了。如果要加其他字段的话，可以使用
group by后的字段。group by这个下面就来讲解。
那具体如何使用呢？这里分组用到的关键字是：
group by。5.1 语法
- 普通语法格式：
SELECT 查询列表 FROM 表名 GROUP BY 字段名;
这里的查询列表为分组函数，字段名即group by后的字段名。
- 复制语法格式：
加上我们之前学习的查询关键字，我们可以这样写：、
SELECT 查询列表 FROM 表名 【where 筛选条件】 GROUP BY 字段名 【order by 排序的字段】;
-- 简单使用 查询每个部门有多少人SELECT COUNT(*), department_id FROM employees GROUP BY department_id;
-- 添加筛选条件使用 查询部门编号不为Null且部门编号在20~60之间的部门的有多少人SELECT COUNT(*), department_id FROM employeesWHERE department_id IS NOT NULL AND department_id BETWEEN 20 AND 60GROUP BY department_id;
-- 分组再添加筛选条件 这里就需要一个关键字 having-- 查询哪个部门的员工个数>
5SELECT COUNT(*), department_idFROM employeesGROUP BY department_idWHERE COUNT(*) >
5;
-- 分组后筛选就不能调用where了 不然会报错SELECT COUNT(*), department_idFROM employeesGROUP BY department_idHAVING COUNT(*) >
5;
-- 也可以使用别名 再来一个案例来试试吧：-- 每个工种员工的最高工资>
12000的工种编号和最高工资SELECT MAX(salary) 最高工资, job_idFROM employeesGROUP BY job_idHAVING 最高工资 >
12000;
-- 分组后再排序-- 每个工种有奖金的员工的最高工资>
6000的工种编号和最高工资,按最高工资升序SELECT MAX(salary) 最高工资, job_idFROM employeesWHERE commission_pct IS NOT NULLGROUP BY job_idHAVING 最高工资 >
6000ORDER BY 最高工资 ASC;
-- 也可以多个字段分组-- 查询每个工种每个部门的最低工资,并且最低工资大于7000，并按最低工资降序SELECT MIN(salary), job_id, department_idFROM employeesGROUP BY job_id, department_idHAVING MIN(salary) >
7000ORDER BY MIN(salary) DESC;
文章图片
注意：
如果我们分组后还想要再去筛选条件过滤，如果在写
where关键字就会报错，而分组后筛选有一个关键字having。所以两者的区别：
where关键字是作用在原始表上进行筛选，并且位置是在group by前，即分组前。having关键字是作用在group by后的结果集上进行再次筛选，并且位置是在group by后，即分组后。having后面可以使用分组函数，where后面不可以使用分组函数
- 可以按单个字段分组 ，也可以多个字段分组，字段用逗号隔开 。
- 和分组函数一同查询的字段最好是分组后的字段 。
- 分组筛选的关键字是
having。最好就是能用分组前筛选的，尽量使用分组前筛选，提高效率。 having后可以支持别名。- 可以支持排序的使用。
6.1 分类
文章图片
- 按年代分类
- sql92： 仅仅支持内连接（隐式内连接）。
- sql99（更加推荐）：支持内连接（显式内连接）+外连接（左外和右外）+交叉连接。
- 按功能分类
- 内连接：等值连接、非等值连接、自连接
- 外连接：左外连接、右外连接、全外连接
- 交叉连接
我们来查找女神对应的男神信息。这个时候就需要使用多表查询了吧。那我们具体来看看：
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT * FROM beauty, boys;
以上数据其实是左表的每条数据和右表的每条数据组合。左表有12条，右表有4条，最终组合后12*4=48条数据。
左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积。
为什么会发生这种情况呢？
- 因为我们查询的时候没有添加有效的连接条件。有些数据不是我们想得到的吧，一个女神应该对应一个男神，没有男神信息的就不应该显示出来吧。所以需要我们手动过滤掉一些没用的我们不想要的数据。
文章图片
而我们需要添加的筛选条件可以称之为表连接条件。
SELECT * FROM beauty, boysWHERE beauty.boyfriend_id = boys.`id`;
文章图片
6.3 sql926.3.1 内连接多表查询时获取符合条件的数据。
文章图片
- 分类：
- 等值连接
- 非等值连接
- 自连接
- 特点：
- 多表等值连接的结果为多表的交集部分。
n表连接，至少需要n-1个连接条件。- 多表的顺序没有要求，不分主次。
- 一般需要为表起别名，提高阅读性和性能。但是起别名后，是不能用原表的名字做操作。
- 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选。
- 案例演示：
-- 查询员工名和对应的部门名SELECT last_name, department_nameFROM employees, departmentsWHERE employees.`department_id` = departments.`department_id`; -- 可以起别名 查询员工名、工种号、工种名SELECT e.`last_name`, e.`job_id`, j.`job_title`FROM employees e, jobs jWHERE e.`job_id` = j.`job_id`; -- 可以调换表的顺序 查询员工名、工种号、工种名SELECT e.`last_name`, e.`job_id`, j.`job_title`FROMjobs j, employees eWHEREj.`job_id` = e.`job_id`; -- 可以加筛选,分组，排序 -- 查询每个工种的工种名和员工的个数，员工中有奖金，员工个数大于2，并且按员工个数降序SELECT j.`job_title`, COUNT(*) 员工个数FROM employees e, jobs jWHERE e.`job_id` = j.`job_id`AND e.`commission_pct` IS NOT NULL GROUP BY j.`job_title`HAVING 员工个数 > 2ORDER BY 员工个数 DESC; -- 可以2表以上连接查询员工名、部门名和所在的城市，并且城市是以o开头的。SELECT e.`last_name`, d.`department_name`, l.`city`FROM employees e, departments d, locations lWHERE e.`department_id` = d.`department_id`AND d.`location_id` = l.`location_id`AND l.`city` LIKE \'o%\';
文章图片
- 注意：
因为有些字段在不同表中可能回重复，MySQL不知道引用谁的，就会报错。所以要起别名。
在起别名后，就只能使用别名来操作表，原来的表名称就用不了了，用了就会报错。
- 案例演示：
-- 查询员工的工资和工资级别SELECT e.`salary`, g.`grade_level`FROM employees e,job_grades gWHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`ORDER BY g.`grade_level` DESC;
文章图片
- 案例演示
-- 查询员工名和上级的名称SELECT e.`last_name`, m.`last_name`FROM employees e, employees mWHERE e.`manager_id` = m.`employee_id`;
文章图片
6.4.1 语法格式到现在为止，可用的完整的连接查询语法：
```sql\\
select 查询列表from 表1 别名 【连接类型】 join 表2 别名 on 连接条件【where 筛选条件】【group by 分组】【having 筛选条件】【order by 排序列表】
**注意：*** 这里的连接类型取决你是哪种类型：可以分为：
1. 内连接：`inner`关键字，`inner`可以省略。
2. 外连接：左外`left 【outer】`关键字， 右外`right 【outer】`关键字，全外 `full【outer】`关键字`outer`可以省略。
3. 交叉连接：`cross `关键字。###### 6.4.2 内连接使用方法与`sql92 `语法的内连接类似，只是筛选条件放在`where`后面，连接条件放在`on`后面。`inner`关键字可以省略。* 一样可以分为等值连接、非等值连接、自连接。* 实际演示下：把之前`sql92 `语法使用的案例改为`sql99 `的语法格式。```sql
-- 等值连接-- 查询员工名和对应的部门名SELECT last_name, department_nameFROM employeesINNER JOIN departments ONemployees.`department_id` = departments.`department_id`;
-- 可以起别名、inner可以省略-- 查询员工名、工种号、工种名SELECT e.`last_name`, e.`job_id`, j.`job_title`FROM employees eJOIN jobs jON e.`job_id` = j.`job_id`;
-- 可以调换表的顺序 查询员工名、工种号、工种名SELECT e.`last_name`, e.`job_id`, j.`job_title`FROMjobs jJOIN employees eON j.`job_id` = e.`job_id`;
-- 可以加筛选,分组，排序 -- 查询每个工种的工种名和员工的个数，员工中有奖金，员工个数大于2，并且按员工个数降序SELECT j.`job_title`, COUNT(*) 员工个数FROM employees eJOIN jobs jON e.`job_id` = j.`job_id`WHERE e.`commission_pct` IS NOT NULL GROUP BY j.`job_title`HAVING 员工个数 >
2ORDER BY 员工个数 DESC;
-- 可以2表以上连接查询员工名、部门名和所在的城市，并且城市是以o开头的。-- 这种类似三表连接，还是有一定顺序的，如果两者没有连接条件，就不能是连接条件了SELECT e.`last_name`, d.`department_name`, l.`city`FROM employees eJOIN departments d ON e.`department_id` = d.`department_id`JOIN locations l ON d.`location_id` = l.`location_id`WHERE l.`city` LIKE \'o%\';
-- 非等值连接-- 查询员工的工资和工资级别SELECT e.`salary`, g.`grade_level`FROM employees eJOIN job_grades gON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`ORDER BY g.`grade_level` DESC;
-- 自连接-- 查询员工名和上级的名称SELECT e.`last_name`, m.`last_name`FROM employees eJOIN employees mON e.`manager_id` = m.`employee_id`;
- 可以看出，
sql99语法可以更好的看出哪种是连接条件，哪种筛选条件。
- 分类：
- 左外连接：将满足要求的数据显示，左表不满足要求的数据也显示，保证左表的数据全部显示。主表是左表，从表是右表，使用
left 【outer】关键字。
文章图片
简单使用下：
``sq; < br/> -- 左连接SELECT b.name, bo.*FROM beauty bLEFT OUTER JOIN boys boON b.boyfriend_id= bo.idORDER BY bo.id`;
可以看到，得到了内连接的结果+左表中不满足数据的结果。我们就可以以此来得到不在男神表的女神信息了（就是那些有NULL值的女神）。
- 右外连接：将满足要求的数据显示，右表不满足要求的数据也显示，保证左表的数据全部显示。主表是右表，从表是左表，使用
right【outer】关键字。
文章图片
简单使用下：
-- 右连接SELECT b.`name`, bo.*FROM boys boRIGHT JOIN beauty bON b.`boyfriend_id` = bo.`id`ORDER BY bo.`id`;
此方式可以得到与左连接相同的结果。所以两者其实可以互换即可。
- 全外连接：将满足要求的数据显示，将左表不满足要求和右表不满足要求的数据也全部显示，即全部显示。使用
full【outer】关键字。这种方式在MySQL 暂时无法体现。大家知道有这么个概念就行啦。
文章图片
- 左外连接：将满足要求的数据显示，左表不满足要求的数据也显示，保证左表的数据全部显示。主表是左表，从表是右表，使用
- 特点：
- 外连接的查询结果为主表中的所有记录：
如果从表中有和它匹配的，则显示匹配的值，而如果从表中没有和它匹配的，则显示NULL。
可以相当于外连接查询结果=内连接结果+主表中有而从表没有的记录。
- 左外连接，
left join左边的是主表。
右外连接，right join右边的是主表。
- 左外和右外交换两个表的顺序，可以实现同样的效果 。
- 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的。
- 外连接的查询结果为主表中的所有记录：
- 案例演示下：
-- 不在男神表的的女神名SELECT b.nameFROM beauty bLEFT OUTER JOIN boys boON b.`boyfriend_id` = bo.`id`WHERE bo.`id` IS NULL; -- 查询哪个部门没有员工SELECT d.*,e.employee_idFROM employees eRIGHT JOIN departments dON d.`department_id` = e.`department_id`WHERE e.`employee_id` IS NULL;
文章图片
直接演示下吧：
SELECT b.* , bo.*FROM beauty bCROSS JOIN boys bo;
文章图片
进阶7：子查询7.1 分类
文章图片
7.2 简单使用具体怎么使用呢？子查询的结果是作为主查询的一部分，那就代表着子查询是比主查询先得到结果。那我们先了解下如何使用吧。
-- 子查询怎么使用？-- 谁的工资比 Abel 高?-- 先查询出Abel的工资吧SELECT salaryFROM employeesWHERE last_name = \'Abel\';
-- 查询员工信息，筛选工资比Abel的工资高的结果。SELECT * FROM employeesWHERE salary >
(SELECT salaryFROM employeesWHERE last_name = \'Abel\');
可以看到子查询是放在（）里，并且作为了主查询的一部分。
7.3 按结果集的行列数分按我们查询得到的行列数具体来分：
- 标量子查询(一行一列)
SELECT salary FROM employees WHERE last_name = \'Abel\';
文章图片
一般搭配着单行操作符使用有：& lt; & gt; = & gt; = & lt; = & lt; & gt; !=
- 列子查询(一列多行)
SELECT DISTINCT department_id FROM departmentsWHERE location_id IN(1400,1700);
文章图片
一般搭配着多行操作符使用：in any all。
文章图片
- 行子查询(一行多列)
SELECT MIN(employee_id),MAX(salary) FROM employees;
文章图片
- 表子查询(多行多列)
SELECT * FROM employees;
文章图片
- 标量子查询
-- 标量子查询-- 可以实现多个子查询 -- 返回job_id与141号员工相同，salary比143号员工工资多的员工姓名，job_id和工资-- 第一次就比较详细的分析，写出来，后面就直接写出来-- 1.查询141号员工的job_idSELECT job_id FROM employeesWHERE employee_id = 141; -- 2.查询143号员工的salarySELECT salary FROM employeesWHERE employee_id = 143; -- 3.查询员工的姓名，job_id 和工资，要求job_id=1并且salary> 2SELECT last_name, job_id, salaryFROM employeesWHERE job_id = (SELECT job_id FROM employeesWHERE employee_id = 141)AND salary > (SELECT salary FROM employeesWHERE employee_id = 143); -- 返回公司工资最少的员工的last_name,job_id和salary-- 1.查询公司的最低工资 2. 查询last_name,job_id和salary，要求salary=1的结果SELECT last_name, job_id, salaryFROM employeesWHERE salary = (SELECT MIN(salary)FROM employees); -- 在having后-- 查询最低工资大于50号部门最低工资的部门id和其最低工资-- 1.查询50号部门的最低工资2.查询每个部门的最低工资3. 在2的基础上筛选，满足min(salary)> 1的结果SELECT MIN(salary), department_idFROM employeesGROUP BY department_idHAVING MIN(salary) > (SELECTMIN(salary) FROM employeesWHERE department_id = 50);
文章图片
- 列子查询
-- 列子查询 -- 返回location_id是1400或1700的部门中的所有员工姓名 (in) -- 1.查询location_id是1400或1700的部门编号 2. 查询员工姓名，要求部门号是1列表中的某一个 SELECT last_name FROM employees WHERE department_id IN( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400, 1700) ); -- 返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary (any) -- 1.查询job_id为‘IT_PROG’部门任一工资 2. 查询员工号、姓名、job_id 以及salary，salary< (1)的任意一个 SELECT last_name, employee_id, job_id, salary FROM employees WHERE salary < ANY( SELECT DISTINCT salary FROM employees WHERE job_id = \'IT_PROG\' ); -- 返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary (all) -- 思路与上面类似 SELECT last_name, employee_id, job_id, salary FROM employees WHERE salary < ALL( SELECT DISTINCT salary FROM employees WHERE job_id = \'IT_PROG\' );
文章图片
- 行子查询
用处相对来说不是特别的多。
-- 查询员工编号最小并且工资最高的员工信息-- 1. 最小的员工编号 2. 工资最高 3. 在2,1 的基础上筛选-- 以前的做法SELECT * FROM employeesWHERE employee_id = (SELECT MIN(employee_id) FROM employees)AND salary = (SELECT MAX(salary) FROM employees); -- 行子查询的做法SELECT * FROM employeesWHERE (employee_id, salary)=(SELECT MIN(employee_id), MAX(salary)FROM employees);
文章图片
-- select 后面-- 查询每个部门的员工个数SELECT d.*,(SELECT COUNT(*) FROM employees eWHERE e.department_id = d.`department_id` ) 个数 FROM departments d;
-- 查询员工号=102的部门名SELECT (SELECT department_name,e.department_idFROM departments dINNER JOIN employees eON d.department_id=e.department_idWHERE e.employee_id=102 ) 部门名;
文章图片
7.4.3 在from后面
-- from 后面-- 查询每个部门的平均工资的工资等级-- 1.查询每个部门的平均工资SELECT AVG(salary), department_idFROM employeesGROUP BY department_id;
-- 2.连接1的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_salSELECT avg_sa.* , g.`grade_level`FROM (SELECT AVG(salary) sa, department_idFROM employeesGROUP BY department_id) avg_saINNER JOIN job_grades gON avg_sa.sa BETWEEN g.`lowest_sal` AND g.`highest_sal`;
文章图片
7.4.4 exists后面（相关子查询）
- 语法：
exists(完整的查询语句)。
返回的结果一般是1或者0。
-- 查询有员工的部门名SELECT department_idFROM departments dWHERE EXISTS(SELECT * FROM employees eWHERE e.`department_id` = d.`department_id`); -- 查询没有男神的女神信息SELECT b.* FROM beauty bWHERE EXISTS(SELECT bo.`id`FROM boys boWHERE bo.id = b.`boyfriend_id`);
文章图片
文章图片
8.1 语法
select 查询列表 from 表【join type join 表2on 连接条件where 筛选条件group by 分组字段having 分组后的筛选order by 排序的字段】limit 【offset,】size;
解释下：
其中的【】中是可写可不写的。
offset要显示条目的起始索引（起始索引从0开始），size要显示的条目个数。8.2 用法
-- 查询前五条员工信息SELECT * FROMemployees LIMIT 0,5;
-- 如果offset是0的话，可以省略SELECT * FROMemployees LIMIT 5;
-- 查询第11条——第15条员工信息SELECT * FROM employees LIMIT 10, 5;
-- 有奖金的员工信息，并且工资较高的前10名显示出来SELECT * FROM employeesWHERE commission_pct IS NOT NULLORDER BY salary DESCLIMIT 10;
文章图片
8.3 特点
limit语句放在查询语句的最后面中。
offset如果是0的话，可以省略不写。
- 公式：要显示的页数
page，每页的条目数size，可以写成
limit (page-1)*size, size。
查询语句1union查询语句2union...9.2 用法
推荐阅读
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- 灵活运用分布式锁解决数据重复插入问题
- 从RocketMQ的Broker源码层面验证一下这两个点
- 详细了解 InnoDB 内存结构及其原理
- SQL练习（通过练习写出有意思有用的sql）
- #导入MD文档图片#windows下如何安装配置删除MySQL数据库
- Redis哨兵(Sentinel)集群部署
- #导入MD文档图片#使用ora2pg迁移Oracle19c到PG13并使用OGG配置实时同步
- Linux安装Redis
- 面向大规模商业系统的数据库设计和实践