本文已参与【新人创作礼】活动，一起开启掘金创作之路。

以下查询语句所用到的表的关系图

编辑

一、基础查询

1、语法

select 查询列表 from 表名;

2、案例

1.查询表中的所有字段
SELECT
	* 
FROM
	employees;
2.查询常量值
 SELECT 100;
 SELECT 'john';
3.查询表达式
 SELECT 100%98;
4.查询函数
 #MySQL版本号
 SELECT VERSION();

3、起别名

方式一：使用as

SELECT
	100 % 98 AS 结果;

SELECT
	last_name AS 姓,
	first_name AS 名 
FROM
	employees;

方式二：使用空格

SELECT
	last_name 姓,
	first_name 名 
FROM
	employees;

4、去重

案例：

查询员工表中涉及到的所有的部门编号
SELECT DISTINCT
	department_id 
FROM
	employees;

5、+号的作用

仅仅只有一个功能：运算符

select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算
select 'john'+90; 如果转换失败，则将字符型数值转换成0，进行加法运算

select null+10; 只要其中一方为null，则结果肯定为null

二、条件查询

1、语法

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

2、案例

1）按条件表达式筛选

简单条件运算符：> < = != <> >= <=

查询工资>12000的员工信息

SELECT
	* 
FROM
	employees 
WHERE
	salary > 12000;

2）按逻辑表达式筛选

逻辑运算符：&& || ! and or not

查询工资z在10000到20000之间的员工名、工资以及奖金
SELECT
	last_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	salary>=10000 AND salary<=20000;

3）模糊查询

like，between and，in，is null

1.like
一般和通配符搭配使用

通配符：% 任意多个字符,包含0个字符，

_任意单个字符

查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT
    last_name,
    salary
FROM
    employees
WHERE
    last_name LIKE '__e_a%';
查询员工名中第二个字符为_的员工名
SELECT
    last_name
FROM
    employees
WHERE
    last_name LIKE '_$_%' ESCAPE '$';
如果我们要匹配“%”或者“_”时，就必须使用“\”进行转义，ESCAPE 关键字的主要作用就是指定一个字符替代“\”的作用。

2.between and

包含临界值

两个临界值不要调换顺序

查询员工编号在100到120之间的员工信息
SELECT
	*
FROM
	employees
WHERE
	employee_id >= 120 AND employee_id<=100;

3.in

in列表的值类型必须一致或兼容

in列表中不支持通配符

查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');

4.is null

= 或 <> 不能用于判断null值

is null或is not null 可以判断null值
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL;
注意：

IS NULL仅仅可以判断NULL值，可读性较高，建议使用

<=>：既可以判断NULL值，又可以判断普通的数值，可读性较低

三、排序查询

1、语法

select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;

2、特点

asc代表的是升序，可以省略，desc代表的是降序
order by子句可以支持单个字段、别名、表达式、函数、多个字段
order by子句在查询语句的最后面，除了limit子句

3、案例

1、添加筛选条件再排序

查询部门编号>=90的员工信息，并按员工编号降序
SELECT
	* 
FROM
	employees 
WHERE
	department_id >= 90 
ORDER BY
	employee_id DESC;
2、按表达式排序
查询员工信息按年薪降序
SELECT
	*,
	salary * 12 *(1 + IFNULL( commission_pct, 0)) 
FROM
	employees 
ORDER BY
	salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC;
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、按多个字段排序

查询员工信息，要求先按工资降序，再按employee_id升序
SELECT
	* 
FROM
	employees 
ORDER BY
	salary DESC,
	employee_id ASC;

四、常见函数

1、字符函数

1. length 获取参数值的字节个
# 4
SELECT LENGTH('john');

# 15
SELECT LENGTH('张三丰hahaha');
一个汉字对应三个字符长度

2、concat 拼接字符串
# 1234
SELECT CONCAT('12','34');
3、upper、lower

upper：小写变大写

lower：大写变小写
# JOHN
SELECT UPPER('john');

# john
SELECT LOWER('JOHN');
4、substr

截取从指定索引处后面所有字符
# 陆展元
SELECT SUBSTR('李莫愁爱上了陆展元',7);
截取从指定索引处指定字符长度的字符
#李莫愁
SELECT SUBSTR('李莫愁爱上了陆展元',1,3);
5、instr 返回子串第一次出现的索引，如果找不到返回0
# 10
SELECT INSTR('杨不殷六侠悔爱上了殷八侠','殷八侠');
6.trim 删除前导和尾随的字符，没有指定为空格
# a张aaaaaaaaaaaa翠山
SELECT TRIM('aa' FROM 'aaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaa');
7.lpad(str,len,padstr)左填充用字符串padstr填补到len字符长度。如果str为大于len长，
返回值被缩短至len个字符(即不能超过 len 长)。
# *********殷素素
SELECT LPAD('殷素素',12,'*'); 

# 殷素
SELECT LPAD('殷素素',2,'*'); 
8.rpad 用指定的字符实现右填充指定长度
# 殷素素ababababa
SELECT RPAD('殷素素',12,'ab'); 
9.replace 替换
# 赵敏赵敏张无忌爱上了赵敏
SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏');

2、数学函数

1.round 四舍五入
# -2
SELECT ROUND(-1.55); 

# 1.57
SELECT ROUND(1.567,2); 
2.ceil 向上取整,返回>=该参数的最小整数
# -1
SELECT CEIL(-1.02); 
3.floor 向下取整，返回<=该参数的最大整数
# -10
SELECT FLOOR(-9.99);
4.truncate 截断
# 1.6
SELECT TRUNCATE(1.69999,1);
5.mod取余
mod(a,b) ： a-a/b*b
# 1
SELECT MOD(10,-3);

# -1
SELECT MOD(-10,3);

3、日期函数

1.now 返回当前系统日期+时间
# 2022-05-08 16:38:1
SELECT NOW(); 
2.curdate 返回当前系统日期，不包含时间
# 2022-05-08
SELECT CURDATE(); 
3.curtime 返回当前时间，不包含日期
# 16:38:43
SELECT CURTIME(); 
4.可以获取指定的部分，年、月、日、小时、分钟、秒
# 2022
SELECT YEAR(NOW()); 

# 5
SELECT MONTH(NOW()); 
5.str_to_date 将字符通过指定的格式转换成日期
# 1998-03-02
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d'); 
6.date_format 将日期转换成字符
#22年05月08日
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日'); 
编辑

4、其他函数

1.返回数据库的用户
# root@localhost
SELECT USER();
2、返回数据库的版本
# 5.7.36-log
SELECT VERSION();
3、查看当前所在数据库
# myemployees
SELECT DATABASE();

5、流程控制函数

1.if函数
# 小
SELECT IF(10<5,'大','小');
2.case函数

①switch 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;
②多重if效果

查询员工的工资的情况，如果工资>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;
注意salary后面的逗号

五、分组查询

1、语法：

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

2、特点


	针对的表	位置	连接的关键字
分组前筛选	原始表	group by前	where
分组后筛选	group by后的结果集	group by后	having

分组函数做筛选不能放在where后面
一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

3、案例

1.简单的分组

查询每个工种的员工平均工资
SELECT
	AVG( salary ),
	job_id 
FROM
	employees 
GROUP BY
	job_id;
2.分组前筛选

查询邮箱中包含a字符的每个部门的最高工资
SELECT
	MAX( salary ),
	department_id 
FROM
	employees 
WHERE
	email LIKE '%a%' 
GROUP BY
	department_id;
3.分组后筛选

查询哪个部门的员工个数>5
SELECT
	COUNT(*),
	department_id 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	COUNT(*)> 5;
4.按多个字段分组

查询每个工种每个部门的最低工资,并按最低工资降序
SELECT
	MIN( salary ),
	job_id,
	department_id 
FROM
	employees 
GROUP BY
	department_id,
	job_id 
ORDER BY
	MIN( salary ) DESC;

六、连接查询

1、说明

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

2、语法：

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

3、案例

1）内连接

1.等值连接

查询员工名、部门名

SELECT
	last_name,
	department_name 
FROM
	departments d
	JOIN employees e
	ON e.`department_id` = d.`department_id`;

2.非等值连接

查询员工的工资级别

SELECT
	salary,
	grade_level 
FROM
	employees e
	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
	JOIN employees m 
	ON e.`manager_id` = m.`employee_id`;

2）外连接

应用场景：用于查询一个表中有，另一个表没有的记录

外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null。

查询哪个部门没有员工

SELECT
	d.*,
	e.employee_id 
FROM
	departments d
	LEFT OUTER JOIN employees e 
	ON d.`department_id` = e.`department_id`

以下通过图来解释外连接如何连接表的

SELECT
	beauty.id,
	beauty.`name`,
	boys.boyName 
FROM
	beauty
	LEFT OUTER JOIN boys 
	ON beauty.boyfriend_id = boys.id;

编辑

4、总结

编辑

七、子查询

分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面：★
标量子查询（单行） √
列子查询（多行） √
行子查询
exists后面（相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

1、where或having后面

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

谁的工资比 Abel 高?
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);
查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50
);
2、列子查询（多行子查询）

返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name,department_id
FROM employees
WHERE department_id = ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);
ANY：任意一个满足

ALL：都要满足

3、行子查询（结果集一行多列或多行多列）

查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);

2、select后面

仅仅支持标量子查询

SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;

3、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;

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

查询有员工的部门名

SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

八、分页查询

1、语法

select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;

offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数

2、案例

有奖金的员工信息，并且工资较高的前10名显示出来

SELECT 
    * 
FROM
    employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;

九、联合查询

1、说明

要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

2、特点

要求多条查询语句的查询列数是一致的！
要求多条查询语句的查询的每一列的类型和顺序最好一致
union关键字默认去重，如果使用union all 可以包含重复项

3、案例

查询部门编号>90或邮箱包含a的员工信息

SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;

MySQL数据处理之查询

一、基础查询

1、语法

2、案例

3、起别名

4、去重

5、+号的作用

二、条件查询

1、语法

2、案例

1）按条件表达式筛选

2）按逻辑表达式筛选

3）模糊查询

三、排序查询

1、语法

2、特点

3、案例

四、常见函数

1、字符函数

2、数学函数

3、日期函数

4、其他函数

5、流程控制函数

五、分组查询

1、语法：

2、特点

3、案例

六、连接查询

1、说明

2、语法：

3、案例

1）内连接

2）外连接

4、总结

七、子查询

1、where或having后面

2、select后面

3、from后面

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

八、分页查询

1、语法

2、案例

九、联合查询

1、说明

2、特点

3、案例