MySQL 学习笔记——基础 DQL 查询语言

最新推荐文章于 2024-11-16 17:02:51 发布

Huanlis

最新推荐文章于 2024-11-16 17:02:51 发布

阅读量1.2k

点赞数

分类专栏：学习笔记大后端文章标签：数据库 mysql

本文链接：https://blog.csdn.net/Couth_/article/details/116431652

版权

大后端同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

学习笔记

6 篇文章 0 订阅

订阅专栏

本文详细介绍了SQL查询语言的各个方面，包括简单查询、条件查询、排序查询、分组函数、分组查询、多表查询、子查询、分页查询和联合查询。通过实例展示了如何使用这些查询技巧，如等值连接、非等值连接、自连接、左外连接、右外连接、全外连接，以及如何进行多表操作和子查询操作，帮助读者深入理解SQL的查询功能。

摘要由CSDN通过智能技术生成

DQL 查询语言

文章目录

DQL 查询语言

一、简单查询

语法：

select
	查询列表
from
	表名;

1、普通简单查询

-- 1、查询字段
select 字段名 from 表名;
select 字段名1, 字段名2, 字段名3 from 表名;
select * from 表名;

-- 2、查询常量
select 100;
select 'john';

-- 3、查询表达式
select 100*99;  -- 9900

-- 4、查询函数
select version();

-- 5、给字段起别名，使用as和空格都可以。
select 100*99 as result; -- result 9900
select last_name as 姓, first_name as 名 from employee;
select last_name 姓, first_name 名 from employee;

2、特殊的简单查询

-- 1、去重
select distinct 字段名 from 表名;

-- 2、+号的使用
/*
+：运算符，将两个字段相加。
	① 当两个字段都为数值型时，两个数值相加获得结果
	② 当两个字段其中一个为数值型，一个为字符型，将字符型试图转化为数值型。
		如果转换成功，两个数值相加获得结果。
		如果转换失败，将字符型转换为0，相加获得结果	select 'john'+90; 结果为90
	③ 只要其中一个字段为null，运算结果一定为null
*/
select 100+10; -- 110

-- 3、字符串拼接
select concat('a', 'b') as result; -- result ab;
select CONCAT(first_name, last_name) as 结果 from employees; -- 结果 first_name+last_name

二、条件查询

语法：

select
	查询列表
from
	表名
where
	条件;

1、按条件表达式筛选

select * from employees where salery > 12000;        -- 大于
select * from employees where department_id <> 90;   -- 不等于
select * from employees where salery < 10000;        -- 小于
select * from employees where department_id = 90;    -- 等于

2、按逻辑表达式筛选

-- && and
-- || or
-- ! not
select * from employess where salery < 15000 and salery > 10000;
select * from employees where salery < 15000 && salery > 10000;

3、模糊查询

-- 1、like关键字 模糊查询
/*
like通常与通配符搭配使用。常见的通配符有：
	%：任意多个字符，包含0个字符
	_：任意单个字符
在通配符与字符产生冲突时，可以随意指定一个字符为转义符号，然后用escape字段说明。 
*/
select * from employees where last_name like '%a%';
-- 查询员工名中第二个字符为_员工，自定义$为转义字符，使用escape说明
select * from employees where last_name like '_$_%' escape '$';

-- 2、between关键字 范围查询，两个临界值不能交换顺序
select * from employees where employee_id between 100 and 120;

-- 3、in关键字 在指定内容中查询
/*
① in列表里的值必须类型相同或兼容
② in列表里的值不支持通配符，因为相当于job_id = 'AD_VP'。并不是job_id like '%A%'
*/
select * from employees where job_id in('IT_PROT', 'AD_VP', 'AD_PERS');

-- 4、is null 判断是否为空
/*
=或<>不能判断null值
is null或is not null可以判断null值
*/
select * from employees where salery is null;
select * from employees where salery is not null;

-- 5、<=> 安全等于
/*
<=> 安全等于，既可以判断null值也可以判断数值
因为安全等于可读性差，建议使用 is null 代替
*/
select * from employees where salery <=> null;
select * from employees where salery <=> 12000;

三、排序查询

语法：

select 
	查询列表
from 
	表名
[where
	查询条件]
order by 
	排序列表
[asc|desc]
-- asc 升序排列 从低到高排列，asc可以省略。
-- desc 降序排列，从高到低排列，不可以省略。
-- order by，不仅可以支持但字段，也可以支持多个字段，在多个字段时会按照字段顺序执行。
-- order by一般放在语句最后，limit除外。

-- 1、升序或降序查询：查询所欲员工按照薪资排列
select * from employees order by salery asc;
select * from employees order by salery desc;

-- 2、添加筛选条件：查询部门号大于等于90的员工并按照生日的升序排列
select * from employees where department_id >= 90 order by hiredate asc;

-- 3、按表达式排序：按照年薪的高低显示员工信息
/*
CONCAT(employees.first_name, ' ', employees.last_name) as employee      拼接员工姓名
employees.salary*12*(1+IFNULL(employees.commission_pct, 0)) as '年薪'   计算员工年薪
*/
select CONCAT(employees.first_name, ' ', employees.last_name) as employee, employees.salary*12*(1+IFNULL(employees.commission_pct, 0)) as '年薪' from employees ORDER BY '年薪' asc;

-- 4、按函数排序：按照员工姓名长度降序显示员工的姓名和薪资。
select CONCAT(employees.first_name, ' ', employees.last_name) as 姓名, employees.salary from employees ORDER BY LENGTH(姓名) DESC;

-- 5、按多个字段排序：查询员工信息，要求先按工资升序，在按照工资降序
select * from employees order by salary asc, employee_id desc;

四、常见函数

1、单行函数

概念：类似于常见的函数，将一组逻辑语句封装在方法体中，对外暴露方法名。

好处：隐藏实现细节，提高代码重用性。

调用：select 函数名(参数列表) [from 表名]

a、字符函数

-- 1、length() 获取参数值的字节个数
select length('john');  -- 4 英文 一个字符为一个长度
select length('张三丰'); -- 9 中文 一个字符为三个长度 这和字符编码集相关 utf8中文占三个长度 gbk占两个长度

-- 2、concat() 拼接字符串
select concat("hello", ' ', 'world');  -- hello world

-- 3、upper(), lower() 全部变为大写、全部变为小写
select upper('john');  -- JOHN
select lower('John');  -- john
select concat(upper('john'), ' ', lower('John'))   -- JOHN john

-- 4、substr, substring 字符串截取
-- MySQL 索引从1开始。
-- substr(字符串, 开始索引) 从设置的开始索引截取到字符串末尾。
select substr('hello world', 7) as output;    -- world
-- substr(字符串, 开始索引, 截取长度) 截取从字符串开始索引起长度为截取长度的字串
select substr('hello world', 1, 5) as output; -- hello


-- 5、instr 子串在 指定字符串中的起始索引, 如果找不到返回 0
select instr(‘hello world’, 'world');  -- 7

-- 6、trim 删除字符串前后的空格
select trim(' hello world ');          -- hello world
-- 去除字符串前后指定的字符  trim(指定字符 from 字符串)
select trim('a' from 'aaaahello worldaaaa') as output;      -- hello world

-- 7、lpad lpad(字符串, 填充完成后的长度, 填充字符) 用指定的字符左填充字符串，使其达到相应的长度。
-- 如果字符串的现有长度已经超过了指定的填充后的长度，就会将字符串从右边开始阶段。
select lpad('hello', 10, '*') as output;                    -- *****hello

-- 8、rpad 右填充，作用与参数相同
select rpad('hello', 10, '*') as output;                    -- hello*****

-- 9、replace 替换
select replace('hello world', 'o', 't') as output;          -- hellt wtrld

b、数学函数

-- 1、round 四舍五入
select round(1.1);     -- 1
select round(-1.1);    -- -1
select round(1.567, 2) -- 1.57

-- 2、ceil 向上取整
select ceil(1.001)     -- 2

-- 3、floor 向下取整
select floor(-9.99)    -- -10

-- 4、truncate 截断，小数点后保留位数
select truncate(1.65, 1)  -- 1.6

-- 5、mod 取余
select mod(10, 3);     -- 1

c、日期函数

-- 1、now 返回当前系统日期+时间
select now();                   -- 2021-04-30 16:34:50

-- 2、curdate、curtime 返回当前日期 返回当前时间
select curdate();               -- 2021-04-30
select curtime();               -- 16:36:33

-- 3、获取指定的年、月、日、时、分、秒
select year(now());             -- 2021
select month(now());            -- 4
select day(now());              -- 30
select hour(now());             -- 16
select minute(now());           -- 46
select second(now());           -- 33

-- 4、str_to_date: 将日期格式的字符转换成指定格式的日期，第一个参数位日期字符串，第二个参数位该字符串中年月日的位置
select STR_TO_DATE('1997年5月21日', '%Y年%c月%d日')    -- 1997-05-21
select STR_TO_DATE('5月21日1997年', '%c月%d日%Y年')    -- 1997-05-21

-- 5、date_format: 将日期转换为指定格式
select DATE_FORMAT('2021/1/1', '%Y年%c月%d日');       -- 2021年1月01日

日期格式：

d、其他函数

select version();                      -- 显示版本号
select database();                     -- 查看当前数据库
select user();                         -- 查看用户

e、流程控制函数

-- 1、if函数 if(参数1, 参数2, 参数3)，参数1：条件表达式，如果为真，使用参数2。如果为假，使用参数3。
select if(10>4, '大', '小');            -- 大
select last_name, commission_pct, if(commission_pct is null, '没奖金', '有奖金') from employees;

-- 2、case函数
/* case 用法一：
case 表达式或者要判断的字段
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
else 默认情况：要是现实的值或语句。;
end;
案例：查询员工工资，要求
部门号为30，显示工资为1.1倍
部门号为40，显示工资为1.2倍
部门号为50，显示工资为1.3倍
其他部门，显示原来的工资
*/
select salary as 原始工资, department_id as 部门号,
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 用法二：
case
when 条件判断1 then 要显示的值1或语句1;
when 条件判断2 then 要显示的值2或语句2;
else 默认情况：要是现实的值或语句。;
end;
案例：查询员工工资，要求
如果工资>20000，显示A
如果工资>15000，显示B
如果工资>10000，显示C
否则显示D
*/
select salary as 原始工资, department_id as 部门号,
case 
when salary > 20000 then 'A'
when salary > 15000 then 'B'
when salary > 10000 then 'C'
else 'D'
end as 新工资
from employees
order by salary desc;

2、分组函数

功能：用作统计使用，又称为聚合函数或统计函数或分组函数

分类：sum 求和、avg 平均值、max 最大值、 min 最小值、count 计算个数；

特点：

sum、avg一般用于处理数值型，max、min、count可以处理任何一种类型。
以上分组都忽略null值
可以和distinct搭配实现去重操作

-- 1、简单使用
select sum(salary) from employees;
select avg(salary) from employees;
select min(salary) from employees;
select max(salary) from employees;
select count(salary) from employees;

-- 2、多个使用
select sum(salary) 总和, avg(salary) 平均, min(salary) 最小, max(salary) 最大, count(salary) 个数 from employees;

-- 3、去重
select count(distinct salary) as 薪资数量 from employees;      -- 57

-- 4、count函数
-- 统计某一字段的个数
select count(salary) from employees;                          -- 107
-- 统计一个表的行数
select count(*) from employees;                               -- 107
-- 相当于添加了一个字段，这个字段的所有值都为1，统计1的个数
select count(1) from employees;                               -- 107
/*
效率：
在MYISAM存储引擎下，count(*)的效率高。
在INNODB存储引擎下，count(*)和count(1)效率差不多，比count(字段高一些)
*/

-- 5、和分组函数查询的字段没有意义，需要和分组查询结合
select avg(salary), employee_id from employees;               -- avg计算有意义，employee_id无意义。

五、分组查询

语法：

select 字段名, 分组函数
from 表名
[where 条件]
group by 条件
[order by 字段名];

特点：

分组查询分为两类

筛选时机数据源位置关键字
分组前原始表 group by 前 where
分组后分组后的结果集 group by 后 having
- 分组函数做条件肯定放在having字句中。
- 能在分组前筛选的，尽量放在where字句中。
group by 字句支持单个、多个字段分组。字段之间使用逗号分隔，没有顺序要求
也可以添加排序，排序添加句末。

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

1、简单的分组查询

-- 1、查询每个工种的工资最大值和平均值
-- truncate(参数1, 参数2)， 小数点后截取函数
select truncate(avg(salary), 2) 平均值, max(salary) 最大值, job_id 工作id from employees group by job_id;

-- 2、每个位置上的部门个数
select count(*) 数量, location_id 部门id from departments group by location_id;

2、添加筛选条件

-- 1、查询邮箱中包含a字符的，每个部门的平均工资
select truncate(avg(salary), 2) 平均工资, department_id 部门id from employees where email like '%a%' group by department_id;
-- +----------+--------+
-- | 平均工资 | 部门id |
-- +----------+--------+
-- |  7000.00 |   NULL |
-- |  4400.00 |     10 |
-- |  9500.00 |     20 |
-- |  8166.66 |    100 |
-- +----------+--------+


-- 2、查询有奖金的每个领导下员工的最高工资
select max(salary) 最高工资, manager_id 领导id from employees where commission_pct is not null group by manager_id;
-- +----------+--------+
-- | 最高工资 | 领导id |
-- +----------+--------+
-- | 14000.00 |    100 |
-- | 11500.00 |    148 |
-- | 11000.00 |    149 |
-- +----------+--------+

3、添加复杂筛选条件

-- 1、查询员工数量大于2的部门
select count(*) 员工个数, department_id 部门id from employees group by department_id having count(*)>2;
-- +----------+----------+
-- | 工种编号 | 最高工资 |
-- +----------+----------+
-- | SA_MAN   | 14000.00 |
-- +----------+----------+

-- 2、查询领导编号大于102且手下员工的最低工资大于5000的领导编号，以及最低工资。以下两句都可以。
select manager_id 领导ID, min(salary) 最低工资 from employees group by manager_id having manager_id > 102 and 最低工资 > 5000;
select manager_id 领导ID, min(salary) 最低工资 from employees where manager_id > 102 group by manager_id having 最低工资 > 5000;
-- +--------+----------+
-- | 领导ID | 最低工资 |
-- +--------+----------+
-- |    108 |  6900.00 |
-- |    201 |  6000.00 |
-- |    205 |  8300.00 |
-- +--------+----------+

4、按函数或表达式分组

-- 1、按员工姓名长度的长度分组，查询每一组的员工个数，筛选员工个数大于5的分组
select count(*) 员工个数, length(concat(first_name, last_name)) 姓名长度 from employees group by 姓名长度 HAVING 员工个数 > 5 order by 姓名长度 desc;
-- +----------+----------+
-- | 员工个数 | 姓名长度 |
-- +----------+----------+
-- |        6 |       16 |
-- |       10 |       10 |
-- |        7 |        9 |
-- +----------+----------+

5、按多个字段分组

-- 1、查询每个部门每个工种的员工的平均工资
select truncate(avg(salary), 2) 平均工资, job_id 工种, department_id 部门 
from employees group by department_id, job_id order by 部门 asc;
-- +----------+------------+------+
-- | 平均工资 | 工种       | 部门 |
-- +----------+------------+------+
-- |  7000.00 | SA_REP     | NULL |
-- |  4400.00 | AD_ASST    |   10 |
-- | 12000.00 | AC_MGR     |  110 |
-- +----------+------------+------+

六、多表查询

含义：又称为多表查询，当查询的字段涉及到多个表时，就会设计多表查询。

笛卡尔积：表1有m行，表2有n行，进行笛卡尔积得 mn行。当mn时，n的第一行与m的每一样相乘，获得一个表。然后n的第二行与m的每一行相乘。将结果添加到前面的表尾，依次循环。获得n的每一行与m的所有行的乘积。笛卡尔积发生的原因：没有做有效的连接限制。

SQL分类：

按年代分类
- sql92标准：仅支持内连接【MYSQL环境】
- sql99标准【推荐】：支持内连接 + 外连接（左外和右外）+ 交叉连接【MYSQL环境】
按功能分类
- 内连接：等值连接、非等值连接、自连接
- 外连接：左外连接、右外连接、全外连接
- 交叉连接

1、sql92标准——内连接

a、等值连接

特点：

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

相关表概述：employees表包含员工信息，departments表包含部门信息，locations表包含部门所在地城市信息。

-- 1、查员工名对应的部门名
select concat(employees.first_name, ' ', employees.last_name) 姓名, employees.department_name 部门名	
from employees, departments where employees.department_id = departments.department_id;
-- +-------------------+--------+
-- | 姓名              | 部门名 |
-- +-------------------+--------+
-- | Jennifer Whalen   | Adm    |
-- | Michael Hartstein | Mar    |
-- | William Gietz     | Acc    |
-- +-------------------+--------+

-- 2、查询员工名、工种号、工种名
select concat(employees.first_name, ' ', employees.last_name) 员工名, employees.job_id 工种号, jobs.job_title 工种名 
from employees, jobs where employees.job_id = jobs.job_id;
-- +-------------------+------------+---------------------------------+
-- | 员工名            | 工种号     | 工种名                          |
-- +-------------------+------------+---------------------------------+
-- | William Gietz     | AC_ACCOUNT | Public Accountant               |
-- | Shelley Higgins   | AC_MGR     | Accounting Manager              |
-- | Kevin Mourgos     | ST_MAN     | Stock Manager                   |
-- +-------------------+------------+---------------------------------+

-- 3、表名起别名
/*
① 提高语句简洁度
② 区分多个重名的字段
注意：如果为表名起了别名，在所有需要用到表名的地方都应该使用别名，而不是表名。
*/
select concat(e.first_name, ' ', e.last_name) 员工名, e.job_id 工种号, j.job_title 工种名 from employees as e, jobs as j where e.job_id = j.job_id;

-- 4、添加筛选
-- 查询所有有奖金的员工姓名及所在标的部门
select concat(e.first_name, ' ', e.last_name) as 姓名, d.department_id as 部门, e.commission_pct as 奖金 
from employees e, departments d where e.department_id = d.department_id and e.commission_pct is not null;
-- +-------------------+------+------+
-- | 姓名              | 部门 | 奖金 |
-- +-------------------+------+------+
-- | John Russell      |   80 | 0.40 |
-- | Jack Livingston   |   80 | 0.20 |
-- | Charles Johnson   |   80 | 0.10 |
-- +-------------------+------+------+

-- 5、添加分组
-- 查询每个城市的部门数
select count(*) as 部门个数, l.city as 城市 
from departments d, locations l where d.location_id = l.location_id group by l.city;
-- +----------+---------------------+
-- | 部门个数 | 城市                |
-- +----------+---------------------+
-- |        1 | Southlake           |
-- |       21 | Seattle             |
-- |        1 | Munich              |
-- +----------+---------------------+

-- 查询有奖金的每个部门名和部门领导编号和该部门的最低工资
select d.department_name as 部门名, e.manager_id as 领导编号, min(e.salary) as 最低工资 
from employees e, departments d 
where e.department_id = d.department_id and e.commission_pct is not null group by 部门名;
-- +--------+----------+----------+
-- | 部门名 | 领导编号 | 最低工资 |
-- +--------+----------+----------+
-- | Sal    |      100 |  6100.00 |
-- +--------+----------+----------+

-- 6、添加排序
-- 查询每个工种的工种名和员工个数，并且按照员工个数降序排序。
select j.job_title as 工种名, count(*) as 员工个数 
from employees e, jobs j 
where e.job_id = j.job_id group by 工种名 order by 员工个数 desc;
-- +---------------------------------+----------+
-- | 工种名                          | 员工个数 |
-- +---------------------------------+----------+
-- | Sales Representative            |       30 |
-- | Human Resources Representative  |        1 |
-- | Public Accountant               |        1 |
-- +---------------------------------+----------+

-- 7、多表连接
-- 查询员工名，所在部门名，以及部门所在地名。
select concat(e.first_name, ' ', e.last_name) 员工名, d.department_name 部门名, l.city 城市 
from employees e, departments d, locations l 
where e.department_id = d.department_id and d.location_id = l.location_id;
-- +-------------------+--------+---------------------+
-- | 员工名            | 部门名 | 城市                |
-- +-------------------+--------+---------------------+
-- | Jennifer Whalen   | Adm    | Seattle             |
-- | Elizabeth Bates   | Sal    | Oxford              |
-- | William Gietz     | Acc    | Seattle             |
-- +-------------------+--------+---------------------+

b、非等值连接

相关表概述：employees表包含员工信息，job_grades表包含工资等级信息。

-- 1、查询员工的工资和工资级别
select concat(e.first_name, ' ', e.last_name) 员工名, e.salary 工资, g.grade_level 工资等级 from employees e, job_grades g where e.salary between g.lowest_sal and g.highest_sal; 
-- +-------------------+----------+----------+
-- | 员工名            | 工资     | 工资等级 |
-- +-------------------+----------+-------------+
-- | Steven K_ing      | 24000.00 | E           |
-- | Shelley Higgins   | 12000.00 | D           |
-- | William Gietz     |  8300.00 | C           |
-- +-------------------+----------+-------------+

-- 2、查询员工工资和工资级别并按等级排序
select concat(e.first_name, ' ', e.last_name) 员工名, e.salary 工资, g.grade_level 工资等级 from employees e, job_grades g where e.salary between g.lowest_sal and g.highest_sal order by 工资等级; 
-- +-------------------+----------+----------+
-- | 员工名            | 工资     | 工资等级 |
-- +-------------------+----------+----------+
-- | Shelli Baida      |  2900.00 | A        |
-- | Hermann Baer      | 10000.00 | D        |
-- | Lex De Haan       | 17000.00 | E        |
-- +-------------------+----------+----------+

c、自连接

相关表概述：employees表包含员工信息，包括员工id，员工奖金，员工的领导编号，员工的薪水等。

-- 查询员工名和领导名
select concat(e.first_name, ' ', e.last_name) as 员工名, concat(m.first_name, ' ', m.last_name) as 领导名 
from employees e, employees m where e.manager_id = m.employee_id; 
-- +-------------------+-------------------+
-- | 员工名            | 领导名            |
-- +-------------------+-------------------+
-- | Neena Kochhar     | Steven K_ing      |
-- | Shelley Higgins   | Neena Kochhar     |
-- | William Gietz     | Shelley Higgins   |
-- +-------------------+-------------------+

2、sql99标准

sql99的语法：

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

连接类型：

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

3、sql99标准——内连接

语法：

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

a、等值连接

特点：

可以添加排序、分组、筛选
inner可以省略
筛选条件放在where后面，连接条件放在on后面，提高了分离性，有利于阅读。
inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集。

-- 1、查员工名对应的部门名
select concat(e.first_name, ' ', e.last_name) 姓名, d.department_name 部门名 
from employees e inner join departments d on e.department_id = d.department_id;
-- +-------------------+--------+
-- | 姓名              | 部门名 |
-- +-------------------+--------+
-- | Jennifer Whalen   | Adm    |
-- | Michael Hartstein | Mar    |
-- | William Gietz     | Acc    |
-- +-------------------+--------+

-- 2、查询员工名、工种号、工种名
select concat(e.first_name, ' ', e.last_name) 员工名, e.job_id 工种号, j.job_title 工种名 
from employees e inner join jobs j on e.job_id = j.job_id;
-- +-------------------+------------+---------------------------------+
-- | 员工名            | 工种号     | 工种名                          |
-- +-------------------+------------+---------------------------------+
-- | William Gietz     | AC_ACCOUNT | Public Accountant               |
-- | Shelley Higgins   | AC_MGR     | Accounting Manager              |
-- | Kevin Mourgos     | ST_MAN     | Stock Manager                   |
-- +-------------------+------------+---------------------------------+

-- 3、查询部门数量大于3的城市
select l.city 城市, d.department_name 部门名, count(*) 部门数量 
from departments d inner join locations l on d.location_id = l.location_id group by 城市 having 部门数量 > 3;
-- +---------+--------+----------+
-- | 城市    | 部门名 | 部门数量 |
-- +---------+--------+----------+
-- | Seattle | Adm    |       21 |
-- +---------+--------+----------+

-- 4、查询员工数量大于3的部门名和员工个数，并按照个数降序。
select d.department_name 部门名, count(*) 员工数量 
from employees e inner join departments d on e.department_id = d.department_id group by 部门名 having 员工数量 > 3 order by 员工数量 desc;
-- +--------+----------+
-- | 部门名 | 员工数量 |
-- +--------+----------+
-- | Shi    |       45 |
-- | Sal    |       34 |
-- | IT     |        5 |
-- +--------+----------+

-- 5、查询员工名、部门名、工种名，并按部门名降序排序，三表连接
select d.department_name 部门名, concat(e.first_name, ' ', e.last_name) 员工名, j.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 order by 部门名 desc;
-- +--------+-------------------+---------------------------------+
-- | 部门名 | 员工名            | 工种名                          |
-- +--------+-------------------+---------------------------------+
-- | Shi    | Winston Taylor    | Shipping Clerk                  |
-- | Shi    | Jean Fleaur       | Shipping Clerk                  |
-- | Acc    | Shelley Higgins   | Accounting Manager              |
-- +--------+-------------------+---------------------------------+

b、非等值连接

-- 查询员工工资级别大于C的员工姓名 工资  工资级别
select concat(e.first_name, ' ', e.last_name) 姓名, e.salary 工资, g.grade_level 工资级别 
from employees e inner join job_grades g on e.salary between g.lowest_sal and g.highest_sal having 工资级别 < 'C' order by e.salary asc;
-- +-------------------+---------+----------+
-- | 姓名              | 工资    | 工资级别 |
-- +-------------------+---------+----------+
-- | TJ Olson          | 2100.00 | A        |
-- | Steven Markle     | 2200.00 | A        |
-- | Kevin Mourgos     | 5800.00 | B        |
-- +-------------------+---------+----------+

c、自连接

-- 查询员工名和领导名
select concat(e.first_name, ' ', e.last_name) as 员工名, concat(m.first_name, ' ', m.last_name) as 领导名 
from employees e  inner join employees m on e.manager_id = m.employee_id; 
-- +-------------------+-------------------+
-- | 员工名            | 领导名            |
-- +-------------------+-------------------+
-- | Neena Kochhar     | Steven K_ing      |
-- | Shelley Higgins   | Neena Kochhar     |
-- | William Gietz     | Shelley Higgins   |
-- +-------------------+-------------------+

4、sql99标准——外连接

特点：

外连接的结果为主表中的所有记录
- 如果主表中有则显示匹配到的值
- 如果主表中没有则显示null值
- 外连接查询结果 = 内连接结果 + 主表中有而从表没有的记录
左外连接：left join 左边的是主表
右外连接：right join 右边的是主表
左外和右外交换两个表的顺序，可以实现同样的效果
全外连接：内连接的部分+主表中有从表中没有的部分+从表中有主表中没有的部分

相关表概述：beauty表包含女生信息，以及是否有男朋友。boys表包含所欲男生信息。

a、左（右）连接

-- 1、左外连接：查询有男朋友的女生
select b.name, bo.* from beauty b left outer join boys bo on b.boyfriend_id = bo.id where bo.id is not null;
-- +------------+------+---------+--------+
-- | name       | id   | boyName | userCP |
-- +------------+------+---------+--------+
-- | Angelababy |    3 | 黄晓明  |     50 |
-- | 王语嫣     |    4 | 段誉    |    300 |
-- | 赵敏       |    1 | 张无忌  |    100 |
-- +------------+------+---------+--------+

-- 2、右外连接：查询有男朋友的女生
select b.name, bo.* from boys bo right outer join beauty b on b.boyfriend_id = bo.id where bo.id is not null;
-- +------------+------+---------+--------+
-- | name       | id   | boyName | userCP |
-- +------------+------+---------+--------+
-- | Angelababy |    3 | 黄晓明  |     50 |
-- | 王语嫣     |    4 | 段誉    |    300 |
-- | 赵敏       |    1 | 张无忌  |    100 |
-- +------------+------+---------+--------+

-- 查询没有员工部门，以及部门所在的城市。
select d.department_name 部门名, l.city 城市 
from employees e right outer join departments d on e.department_id = d.department_id inner join locations l on d.location_id = l.location_id 
where e.department_id is null; 
-- +--------+---------+
-- | 部门名 | 城市    |
-- +--------+---------+
-- | Ret    | Seattle |
-- | Rec    | Seattle |
-- | Pay    | Seattle |
-- +--------+---------+

b、全外连接

注：MYSQL不支持

select b.*, bo.* from beauty b full outer join boys bo on b.`boyfriend_id` = bo.id;

5、sql99标准——交叉连接

特点：

结果为笛卡尔乘积

select b.*, bo.* from beauty b cross join boys bo;

6、多表连接查询总结

七、子查询

概念：出现在其他查询语句内部的select语句，称为子查询或内查询。内部嵌套其他select语句的查询，称为外查询或主查询。

分类：

按子查询出现的位置：
- select后：仅仅支持标量子查询
- from后：支持表子查询
- where或having后：标量子查询或列子查询，也支持行子查询，但是使用较少。※
- exists后：表子查询
按结果集的行列数不同
- 标量子查询：结果集只有一行一列
- 列子查询：结果集有一列多行
- 行子查询：结果集有一行多列
- 表子查询：结果集一般有多行多列

1、where或having后的子查询

分类及特点：

标量子查询（单行子查询）
- 子查询都会放在小括号内
- 子查询一般放在条件的右侧
- 标量子查询，一般搭配着单行操作符使用，如<、>、>=、<=、=、<>。
列子查询（多行子查询）
- 一般搭配这多行操作符使用，如in、any/some、all
行子查询（多行多列）
子查询一般都优先于主查询执行

a、标量子查询（单行子查询）

-- 1、查询谁的工资比abel高？
select last_name 姓名, salary 工资 from employees where salary > (select salary from employees where last_name = 'Abel');
-- +-----------+----------+
-- | 姓名      | 工资     |
-- +-----------+----------+
-- | K_ing     | 24000.00 |
-- | Kochhar   | 17000.00 |
-- | Higgins   | 12000.00 |
-- +-----------+----------+

-- 2、返回job_id与141号员工相同，工资比143号员工多的员工的姓名、job_id和工资。
select last_name 姓名, job_id 工种ID, salary 工资 
from employees where job_id = (select job_id from employees where employee_id = 141) and salary > (select salary from employees where employee_id = 143);
-- +-------------+----------+---------+
-- | 姓名        | 工种ID   | 工资    |
-- +-------------+----------+---------+
-- | Nayer       | ST_CLERK | 3200.00 |
-- | Mikkilineni | ST_CLERK | 2700.00 |
-- | Davies      | ST_CLERK | 3100.00 |
-- +-------------+----------+---------+

-- 3、查询公司工资最少的员工的last_name，job_id和salary
select last_name, job_id, salary from employees where salary = (select min(salary) from employees);
-- +-----------+----------+---------+
-- | last_name | job_id   | salary  |
-- +-----------+----------+---------+
-- | Olson     | ST_CLERK | 2100.00 |
-- +-----------+----------+---------+

-- 4、查询最低工资大于50号部门的最低工资的部门id和最低工资。
select department_id, min(salary) 
from employees group by department_id having min(salary) > (select min(salary) from employees where department_id = 50);
-- +---------------+-------------+
-- | department_id | min(salary) |
-- +---------------+-------------+
-- |          NULL |     7000.00 |
-- |            10 |     4400.00 |
-- |           110 |     8300.00 |
-- +---------------+-------------+

b、列子查询（多行子查询）

多行操作符：

in、not in：等于列表中的任意一个
any、some：和子查询的返回的某一个值比较。a > any(参数1，参数2，参数3…) <===> a > min(参数1，参数2，参数3…)。some与any类似。
all：和子查询返回的所有值比较。a > all(参数1，参数2，参数3…) <===> a > max(参数1，参数2，参数3…)

-- 1、查询location_id是1400或1700的部门中所有员工姓名
select last_name, department_id  
from employees where department_id in (select distinct department_id from departments where location_id = 1400 or location_id = 1700);
-- +------------+---------------+
-- | last_name  | department_id |
-- +------------+---------------+
-- | Hunold     |            60 |
-- | Higgins    |           110 |
-- | Gietz      |           110 |
-- +------------+---------------+

-- 2、查询其他部门中比job_id为IT_PROG部门任一员工工资低的员工的工号、姓名、job_id以及工资。
select employee_id, last_name, job_id, salary from 
employees where salary < any(select salary from employees where job_id = 'IT_PROG');
-- | employee_id | last_name   | job_id     | salary  |
-- +-------------+-------------+------------+---------+
-- |         104 | Ernst       | IT_PROG    | 6000.00 |
-- |         105 | Austin      | IT_PROG    | 4800.00 |
-- |         206 | Gietz       | AC_ACCOUNT | 8300.00 |
-- +-------------+-------------+------------+---------+

-- 3、查询其他部门中比job_id为IT_PROG部门所有员工工资都低的员工的工号、姓名、job_id以及工资。
select employee_id, last_name, job_id, salary 
from employees where salary < all(select distinct salary from employees where job_id = 'IT_PROG');
-- +-------------+-------------+----------+---------+
-- | employee_id | last_name   | job_id   | salary  |
-- +-------------+-------------+----------+---------+
-- |         115 | Khoo        | PU_CLERK | 3100.00 |
-- |         198 | OConnell    | SH_CLERK | 2600.00 |
-- |         199 | Grant       | SH_CLERK | 2600.00 |
-- +-------------+-------------+----------+---------+

c、行子查询（多列多行）

-- 查询员工编号最小并且工资最高的员工信息
select employee_id, last_name, salary, job_id 
from employees where salary = (select max(salary) from employees) 
and employee_id = (select min(employee_id) from employees); 
-- 改成行子查询：使用较少
select employee_id, last_name, salary, job_id 
from employees where (salary, employee_id) = (select max(salary), min(employee_id) from employees);
-- +-------------+-----------+----------+---------+
-- | employee_id | last_name | salary   | job_id  |
-- +-------------+-----------+----------+---------+
-- |         100 | K_ing     | 24000.00 | AD_PRES |
-- +-------------+-----------+----------+---------+

2、select后的子查询

-- 1、查询每个部门的个数
select 
d.*, (select count(*) from employees e where e.department_id = d.department_id) 员工数量 
from departments d where manager_id is not null;
-- +---------------+-----------------+------------+-------------+----------+
-- | department_id | department_name | manager_id | location_id | 员工数量 |
-- +---------------+-----------------+------------+-------------+----------+
-- |            40 | Hum             |        203 |        2400 |        1 |
-- |            50 | Shi             |        121 |        1500 |       45 |
-- |            80 | Sal             |        145 |        2500 |       34 |
-- +---------------+-----------------+------------+-------------+----------+

-- 2、查询员工号等于102的部门号
select 
(select department_name from departments d where d.department_id = e.department_id) 部门名 
from employees e where e.employee_id = 102;
-- +--------+
-- | 部门名 |
-- +--------+
-- | Exe    |
-- +--------+

3、from后面的子查询

要求：子查询充当一张表，要求必须起别名

-- 查询每个部门的平均工资的工资等级以及部门名。from后面的子查询，在接一个左连接
select d.department_name, g.grade_level, s.`平均工资` from 
(select avg(e.salary) 平均工资, e.department_id from employees e group by e.department_id) s 
inner join job_grades g on s.`平均工资` between g.lowest_sal and g.highest_sal left join departments d on s.department_id = d.department_id;
-- +-----------------+-------------+--------------+
-- | department_name | grade_level | 平均工资     |
-- +-----------------+-------------+--------------+
-- | NULL            | C           |  7000.000000 |
-- | Adm             | B           |  4400.000000 |
-- | Mar             | C           |  9500.000000 |
-- | Pur             | B           |  4150.000000 |
-- | Hum             | C           |  6500.000000 |
-- | Shi             | B           |  3475.555556 |
-- | IT              | B           |  5760.000000 |
-- | Pub             | D           | 10000.000000 |
-- | Sal             | C           |  8955.882353 |
-- | Exe             | E           | 19333.333333 |
-- | Fin             | C           |  8600.000000 |
-- | Acc             | D           | 10150.000000 |
-- +-----------------+-------------+--------------+

4、exists后的子查询（相关子查询）

语法：exists(完整的查询语句)

结果：0 或 1

-- 查询员工的部门名
-- 使用exists
select d.department_name from departments d where exists(select * from employees e where e.department_id = d.department_id);
-- 使用in
select d.department_name from departments d where d.department_id in (select department_id from employees);
-- +-----------------+
-- | department_name |
-- +-----------------+
-- | Adm             |
-- | Fin             |
-- | Acc             |
-- +-----------------+

5、子查询经典案例

-- 1、查询工资最低的员工信息：last_name, salary
select last_name, salary from employees where salary = (select min(salary) from employees);
-- +-----------+---------+
-- | last_name | salary  |
-- +-----------+---------+
-- | Olson     | 2100.00 |
-- +-----------+---------+

-- 2、查询平均工资最低的部门信息
-- 方式一：推荐
select * from departments where department_id = (select department_id from employees group by department_id order by avg(salary) asc limit 1);
-- 方式二：
select * from departments where department_id = (select department_id from employees group by department_id having avg(salary) = (select min(t.`平均工资`) from (select avg(salary) 平均工资, department_id from employees group by department_id) t));
-- 方式三：
select * from departments where department_id = (select department_id from (select avg(salary) 平均工资, department_id from employees group by department_id) s where s.`平均工资` = (select min(t.`平均工资`) from (select avg(salary) 平均工资, department_id from employees group by department_id) t));
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
|            50 | Shi             |        121 |        1500 |
+---------------+-----------------+------------+-------------+

-- 3、查询平均工资最低的部门和该部门的平均工资
select d.*, s.`平均工资` from (select avg(salary) 平均工资, department_id from employees group by department_id order by avg(salary) asc limit 1) s left join departments d on s.department_id = d.department_id;
-- +---------------+-----------------+------------+-------------+-------------+
-- | department_id | department_name | manager_id | location_id | 平均工资    |
-- +---------------+-----------------+------------+-------------+-------------+
-- |            50 | Shi             |        121 |        1500 | 3475.555556 |
-- +---------------+-----------------+------------+-------------+-------------+


-- 4、查询平均工资最高的job信息
select * from jobs where job_id = (select job_id from employees group by job_id order by avg(salary) desc limit 1);
-- +---------+-----------+------------+------------+
-- | job_id  | job_title | min_salary | max_salary |
-- +---------+-----------+------------+------------+
-- | AD_PRES | President |      20000 |      40000 |
-- +---------+-----------+------------+------------+

-- 5、查询平均工资高于公司平均工资的部门信息
select d.*, avg(e.salary) from departments d left join employees e on e.department_id = d.department_id group by e.department_id having avg(e.salary) > (select avg(salary) from employees);
-- +---------------+-----------------+------------+-------------+---------------+
-- | department_id | department_name | manager_id | location_id | avg(e.salary) |
-- +---------------+-----------------+------------+-------------+---------------+
-- |            20 | Mar             |        201 |        1800 |   9500.000000 |
-- |            40 | Hum             |        203 |        2400 |   6500.000000 |
-- |           110 | Acc             |        205 |        1700 |  10150.000000 |
-- +---------------+-----------------+------------+-------------+---------------+

-- 6、查询公司中所有manger的详细信息
-- 方式一：
select employee_id, last_name, department_id, job_id, salary from employees where employee_id = any (select distinct manager_id from employees where manager_id is not null);
-- 方式二：
select e.employee_id, e.last_name, e.department_id, e.job_id, e.salary from employees e right join (select distinct manager_id from employees where manager_id is not null) s on e.employee_id = s.manager_id;
-- +-------------+-----------+---------------+---------+----------+
-- | employee_id | last_name | department_id | job_id  | salary   |
-- +-------------+-----------+---------------+---------+----------+
-- |         100 | K_ing     |            90 | AD_PRES | 24000.00 |
-- |         201 | Hartstein |            20 | MK_MAN  | 13000.00 |
-- |         205 | Higgins   |           110 | AC_MGR  | 12000.00 |
-- +-------------+-----------+---------------+---------+----------+

-- 7、各个部门中，最高工资中最工资最低的部门的最低工资
select d.*, max(e.salary) 最低工资 from employees e left join departments d on e.department_id = d.department_id group by e.department_id order by max(e.salary) asc limit 1;
-- +---------------+-----------------+------------+-------------+----------+
-- | department_id | department_name | manager_id | location_id | 最低工资 |
-- +---------------+-----------------+------------+-------------+----------+
-- |            10 | Adm             |        200 |        1700 |  4400.00 |
-- +---------------+-----------------+------------+-------------+----------+

-- 8、查询平均工资最高的部门的manger的详细信息：last_name, department_id, email, salary;
select e.last_name, e.department_id, e.email, e.salary from employees e left join departments d on e.employee_id = d.manager_id where d.department_id = (select department_id from employees group by department_id order by avg(salary) desc limit 1);
-- +-----------+---------------+-------+----------+
-- | last_name | department_id | email | salary   |
-- +-----------+---------------+-------+----------+
-- | K_ing     |            90 | SKING | 24000.00 |
-- +-----------+---------------+-------+----------+

八、分页查询

语法：

select 查询列表
from 表名
[
    join type join 表2
    on 连接条件
    where 筛选条件
    group by 分组条件
    order by 排序条件
]
limit [offset], size;
-- offset 要显示条目的其实索引，索引从0开始。默认为0
-- size 要显示的数量

特点：

limit语句放在查询语句最后
公式：要显示的页数page，每页的条目数size。 select 查询列表 from 表名 limit (page - 1), size

-- 1、显示前五条员工信息
select employee_id, first_name, job_id, department_id from employees limit 0, 5;
-- +-------------+------------+---------+---------------+
-- | employee_id | first_name | job_id  | department_id |
-- +-------------+------------+---------+---------------+
-- |         100 | Steven     | AD_PRES |            90 |
-- |         101 | Neena      | AD_VP   |            90 |
-- |         102 | Lex        | AD_VP   |            90 |
-- +-------------+------------+---------+---------------+

-- 2、查询第11条到第25条
select employee_id, first_name, job_id, department_id from employees limit 10, 15;

-- 3、有奖金的员工信息，并且工资较高的前10名员工信息。
select employee_id, first_name, job_id, salary from employees where commission_pct is not null order by salary desc limit 10;
-- +-------------+------------+--------+----------+
-- | employee_id | first_name | job_id | salary   |
-- +-------------+------------+--------+----------+
-- |         145 | John       | SA_MAN | 14000.00 |
-- |         150 | Peter      | SA_REP | 10000.00 |
-- |         156 | Janette    | SA_REP | 10000.00 |
-- +-------------+------------+--------+----------+

九、联合查询

union 联合合并：将多条查询语句的结果合并为一个结果

语法：

查询语句1
union 
查询语句2

应用场景及特点：

要查询的结果来自多个表，且多个表没有直接的连接关系，但是查询的信息一致。
要求多条语句的查询列数是一致的
要求多条查询语句查询的每一列的类型和顺序最好一致。
union关键字默认去重，如果使用union all可以包含重复项

-- 1、查询部门号大于90或邮箱含有a的员工信息
select * from employees where department_id > 90 or email like '%a%';
select * from employees where department_id > 90 union select * from employees where email like '%a%';