进阶SQL

一、DQL

SELECT 想要映射的字段名
[DISTINCT]           //去重
FROM 表名
WHERE 查询条件
[ORDER BY 根据什么字段排序 ASC|DESC]    //默认升序（ASC）
[LIMIT  n   OFFSET   m]  //从第m+1条开始显示n条

（1）指定别名：字段名 as 别名

as可以省略

SELECT eid AS "编号",ename AS "姓名" FROM emp;

（2）去重：DISTINCT 字段名

SELECT DISTINCT dept_name FROM emp;

（3）条件查询：select 列名 from 表名 where 条件;

查询条件	谓词
比较	= , > , < , >= , <= , != , <> , !> , !<
确定范围	BETWEEN AND , NOT BETWEEN AND
确定集合	IN , NOT IN
字符匹配	LIKE , NOT LIKE
空值	IS NULL , IS NOT NULL
逻辑运算	AND , OR , NOT

精准查询：

#1 查询员工姓名为黄蓉的员工信息 
select * from emp where ename = '黄蓉';

#2 查询薪水价格为5000的员工信息
select * from emp where salary = 5000;

#3 查询薪水价格不是5000的所有员工信息 
select * from emp where salary != 5000; 
select * from emp where salary <> 5000; 

#4 查询薪水价格大于6000元的所有员工信息 
select * from emp where salary > 6000; 

#5 查询薪水价格在5000到10000之间的员工信息 
select * from emp where salary >= 5000 and salary <= 10000; 
select * from emp where salary between 5000 and 10000; 

#6 查询薪水价格是3600或7200或者20000的员工信息
select * from emp where salary = 3600 or salary = 7200 or salary = 20000;
select * from emp where salary in (3600, 7200, 20000);

模糊查询：

通配符	说明
%	表示匹配任意多个字符
_	表示匹配任意一个字符

#1 查询含有'八'字的员工信息
select * from emp where ename like '%八%';
 
#2 查询以'孙'字开头的员工信息 
select * from emp where ename like '孙%';

#3 查询第二个字为'兔'的员工信息 
select * from emp where ename like '_兔%';

#4 查询没有部门的员工信息 
select * from emp where dept_name is null;

#5 查询有部门的员工信息
select * from emp where dept_name is not null;

（4）排序：select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];   //-- asc  升序(默认)    -- desc 降序

#升序

SELECT * FROM emp ORDER BY salary;

#降序
SELECT * FROM emp ORDER BY salary DESC;

二、函数

（1）单行函数：单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据。

1.大小写处理函数

函数	描述	实例
LOWER(s)|LCASE(s)	将字符串 s 转换为小写	将字符串 OLDLU转换为小写：SELECT LOWER("OLDLU"); -- oldlu
UPPER(s)|UCASE(s)	将字符串s转换为大写	将字符串 oldlu转换为大写：SELECT UPPER("oldlu"); -- OLDLU

2.字符处理函数

函数	描述	实例
LENGTH(s)	返回字符串 s 的长度	返回字符串oldlu的字符数SELECT LENGTH("oldlu"); -- 5;
CONCAT(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串SELECT CONCAT("sxt ", "teacher ", "oldlu"); -- sxt teacher oldlu;
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len	将字符串 x 填充到 oldlu字符串的开始处：SELECT LPAD('oldlu',8,'x'); -- xxxoldlu
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 oldlu开始处的空格：SELECT LTRIM(" oldlu") ;-- oldlu
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 oldlu 中的字符 o 替换为字符 O：SELECT REPLACE('oldlu','o','O'); -- Oldlu
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来：SELECT REVERSE('abc'); -- cba
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len	将字符串 xx填充到 oldlu字符串的结尾处：SELECT RPAD('oldlu',8,'x'); -- oldluxxx
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 oldlu 的末尾空格：SELECT RTRIM("oldlu "); -- oldlu
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符：SELECT SUBSTR("OLDLU", 2, 3); -- LDL
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("OLDLU", 2, 3); --LDL
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 oldlu 的首尾空格：SELECT TRIM(' oldlu ');-- oldlu

3.数值函数

函数	描述	实例
ABS(num)	返回num的绝对值	SELECT ABS(-1) -- 返回1
CEIL(num)	返回大于num的最小整数（向上取整）	SELECT CEIL(1.5) -- 返回2
FLOOR(num)	返回小于num的最大整数（向下取整）	SELECT FLOOR(1.5) -- 返回1
MOD(num1, num2)	返回num1/num2的余数（取模）	SELECT MOD(5,2) -- 1
PI()	返回圆周率的值
POW(num,n)/POWER(num, n)	返回num的n次方	SELECT POW(2,3) -- 8
RAND(num)	返回0~1之间的随机数	SELECT RAND() --0.93099315644334
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位	SELECT ROUND(1.23456,3) --1.235
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值（与 ROUND 最大的区别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) -- 1.234

4.日期与时间函数

函数	描述	实例
CURDATE()	返回当前日期	SELECT CURDATE(); -- 2022-07-20
CURTIME()	返回当前时间	SELECT CURTIME(); -> 19:59:02
NOW()	返回当前日期和时间	SELECT NOW() -> 2022-07-20 20:57:43
SYSDATE()	返回该函数执行时的日期和时间	SELECT SYSDATE() -> 2022-07-20 20:57:43
DAYOFYEAR(date)	返回日期date为一年中的第几天	SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315
WEEK(date)/WEEKOFYEAR(date)	返回日期date为一年中的第几周	SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6
DATEDIFF(date1, date2)	返回起始日期date1与结束日期date2之间的间隔天数	SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32
DATE()	从日期或日期时间表达式中提取日期值	SELECT DATE("2017-06-15"); -> 2017-06-15
DAY(d)	返回日期值 d 的日期部分	SELECT DAY("2017-06-15"); -> 15
MONTH(d)	返回日期d中的月份值，1 到 12	SELECT MONTH('2011-11-11 11:11:11') ->11
YEAR(d)	返回年份	SELECT YEAR("2017-06-15"); -> 2017

5.流程控制函数

间隔类型	描述
IF(condition, t, f)	如果条件condition为真，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为null，则返回value1，否则返回value2
NULLIF(value1, value2)	如果value1等于value2，则返回null，否则返回value1
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 ...] [ELSE result] END	如果条件condition1为真，则返回result1，···，否则返回result

6.其它函数

函数	描述
DATABASE()	返回当前数据库名
VERSION()	返回当前MySQL的版本号
USER()	返回当前登录的用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA	返回数字代表的IP地址
PASSWORD(str)	实现对字符串str的加密操作
FORMAT(num, n)	实现对数字num的格式化操作，保留n位小数
CONVERT(data, type)	实现将数据data转换成type类型的操作

（2）多行函数：我们之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值。

        1.多行函数会忽略空值。

        2.多行函数也称为分组函数, 聚合函数。

分组操作：GROUP BY       聚合后再过滤：HAVING

聚集函数：

函数名	COUNT(*)	SUM(列名)	AVG(列名)	MAX(列名)	MIN(列名)
作用	统计个数	求和	平均值	最大值	最小值

 （3）limit：select 字段1, 字段2 ... from 表名 limit offset, length;

作用:

        1. limit是限制的意思, 限制返回的查询结果的函数(通过limit函数，控制查询返回多少行数据)。
        2. limit 语法是 MySql的方言, 用来完成分页。

# 查询emp表中的前5条数据

select * from emp limit 0, 5;

三、SQL执行流程

（1）组成： 

1. 连接管理与安全验证：MySQL有连接池（Connection Pool）管理客户端的连接。客户端连接后会验证用户名、密码、主机信息等

2. 缓存（Cache&Buffer）：缓存中存储了SQL命令的HASH，直接比对SQL命令的HASH和缓存中key是否对应，如果对应，直接返回结果，不再执行其他操作。由于缓存的是SQL的HASH，所以根据Hash特性SQL中空格等内容必须完全一样。缓存里面包含表缓存、记录缓存、权限缓存等。查询语句执行完成后会把查询结果缓存到缓存中。在MySQL中查询缓存默认不开启。考虑到查询缓存性能瓶颈问题，从MySQL8开始已经不支持查询缓存了。

3. 解析器（Parser）主要作用是解析SQL命令。将SQL命令分解成数据结构（解析树），后续的操作都是基于这个结构的。如果在分解过程中遇到错误，出现SQL解析错误。解析时主要检查SQL中关键字，检查关键字是否正确、SQL中关键字顺序是否正确、引号是否对应是否正确等。

4. 预处理器：根据解析器的解析树，进一步检查表是否存在、列是否存在、名字和别名是否有歧义等。

5. 优化器（Optimizer）：根据官网说明在执行SQL查询时会根据开销自动选择最优查询方案。采用“选择-投影-连接”的策略。先选择where中的行数。同时先选择要选择的列，而不是全部列，最后把内容合并到一起。

6. 执行器：包含执行SQL命令。获取返回结果。生成执行计划等。

7. 存储引擎：访问物理文件的媒介

（2）流程：

1. 客户端向服务器端发送SQL命令和连接参数

2. 服务器端连接模块连接并验证

3. 缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行。如果是MySQL 8 是没有查询缓存的。

4. 解析器解析SQL为解析树，检查关键字相关问题，如果出现错误，报SQL解析错误。如果正确，继续执行

5. 预处理器对解析树继续处理检查表、列别名等，处理成功后生成新的解析树。

6. 优化器根据开销自动选择最优执行计划，生成执行计划

7. 执行器执行执行计划，访问存储引擎接口

8. 存储引擎访问物理文件并返回结果

9. 如果开启查询缓存，缓存管理器把结果放入到查询缓存中。

10. 返回结果给客户端