1. between...and...
区间：左闭右闭
2. like条件
单个字符：_
任意个字符：%
select * from user where name like '_三';
select * from user where name like '%强%';
3. 聚合函数
介绍：将一列数据作为一个整体(即：作用于某一列)，进行纵向计算
常见聚合函数：
- count：统计数量
- sum：求和
- avg：平均值
- max：最大值
- min：最小值
count(*)、count(1)、count(字段)的区别：
-
count(1)：会统计表中的所有记录数，包括字段值为null的记录。
-
count(字段)、count(*)：不会统计表中值为null的记录。
语法：select 聚合函数(字段列表) from 表名;
-- 统计数量：查询用户表用户数量
select count(id) from user;
-- 统计之和：统计所有用户年龄之和
select sum(age) from user;
4. 分组查询
语法：select 字段列表 from 表名 where 条件 group by 分组字段 having;
注意：分组之后，查询的字段一般为复合函数和分组字段，查询其他字段无任何意义。
-- 根据性别分组，统计男性用户 和 女性用户的数量
select gender, count(*) from user group by gender;
--★★★★ 分组之后，查询的字段一般为复合函数和分组字段，查询其他字段无任何意义。如下：还可以查询用户姓名，但没意义。★★★★
select name, gender, count(*) from user group by gender;
-- 分组后加聚合（having）!!!!!!
-- 查询年纪小于45的用户，并根据用户等级进行分组，要求等级大于3的用户
select count(*), grade from user where age < 45 group by grade having grade>3;
5. SQL的编写顺序与执行顺序
-
编写顺序：select * from 表名 where 条件 group by 分组字段 having 分组后条件 order by 排序字段 limit 分页参数
-
执行顺序： from 表名 where 条件 group by 分组字段 having 分组后条件 select 字段 order by 排序字段 limit 分页参数
当我们为一个表设置了别名后，筛选条件就必须用别名，不能再用原本的名字了。
select opus.id, circle.id
from ggx_opus opus
left join ggx_circle circle on opus.circle_id = circle.id -- 正确
select ggx_opus.id, ggx_circle.id
from ggx_opus opus
left join ggx_circle circle on ggx_opus.circle_id = ggx_circle.id -- 错误
6. 字符串函数
concat(s1,s2,s3...sn) 字符串拼接，将多个字符串拼接成一个字符串
lower(s) 将字符串全部转为小写
upper(s) 将字符串全部转为小写
lpad(s, n, pad) 左填充，用字符串pad对s的左边进行填充，达到n个字符串长度
rpad(s, n, pad) 左填充，用字符串pad对s的左边进行填充，达到n个字符串长度
trim(s) 去掉字符串前后空格
substring(s, start, len) 返回字符串s从start起len个长度的字符串
-- lpad， 用*对字符串007的左边进行填充，使007达到5个字符串
select lpad("007", 5, "*") -- 输出结果： **007
-- substring， 索引从1开始，
select substring("hello world", 1, 5) -- 输出结果：hello
7. 数值函数
ceil(x) 向上取整
floor(x) 向下取整
mod(x,y) 返回x/y的模
rand() 返回0~1的随机数
round(x,y) 求参数x的四舍五入的值，保留y位小数
-- ceil
select ceil(1.1) -- 结果：2（不会四舍五入）
-- floor
select floor(1.9) -- 结果：1（不会四舍五入）
-- round
select round(3.1415, 2) -- 结果：3.14
8. 日期函数
curdate() -- 返回当前日期（2024-08-08）
curtime() -- 返回当前时间（08:58:09）
now() -- 返回当前日期和时间（2024-08-08 08:58:09）
year(date) -- 返回指定date的年份（2024）
month(date) -- 返回指定date的月份（7）
day(date) -- 返回指定date的日期（1）
date_add(date, interval expr type) -- 用于向日期添加一个特定的时间间隔。返回一个日期或(日期和时间)值 date：当前时间 interval：固定写法 expr：表达式 type：类型
-- date：要增加时间间隔的日期。
-- expr：要添加的时间值，可以是整数或小数，表示数量。
-- type：用于指定时间单位的类型，例如：SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR 等。 -- date_add
select date_add(now(), interval 10 month) -- 释义：返回当前时间往后推10个月的日期 2025-01-08 08:58:09
datediff(date1, date2) -- 返回起始时间date1和date2之间的天数
select datediff('2024-10-01', '2024-12-01') -- 结果：61 符合 1357810蜡 31天永不差
select datediff('2024-12-01', '2024-10-01') -- 结果：-61
9. 流程函数
意义：可以在sql语句中实现条件筛选，从而提高语句效率。
if(value, t, f) 如果value为true，返回t，否则f
ifnull(value1, value2) 如果value1不为空，返回value1，否则value2
case when [val1] then [res1] else [default] end 若val1为true，返回res1，......否则返回default。
-- case when [val1] then [res1] else [default] end
-- 查询用户姓名和地址，如果是北上广深，则为一线城市。否则都为二线城市
SELECT
NAME,
( CASE address WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END ) as '城市等级'
FROM
USER
case [expr] when [val1] then [res1] else [default] end 若expr的值为val1，返回res1，......否则返回default。
-- case [expr] when [val1] then [res1] else [default] end
SELECT
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
FROM table_name;
10. 多表查询概述
- 内连接：A、B交集部分数据
- 外连接：
左外连接：A表所有数据，及A、B交集部分数据
右外连接：B表所有数据，及A、B交集部分数据
11. 自连接查询
语法：
select 字段列表 from 表A 别名AA join 表B 别名BB on 条件;
示例：
-- 查询菜单名与该菜单的上级菜单
select mu1.name, mu2.name
from menu mu1 join menu mu2 on mu1.parent_id = mu2.id
-- 查询菜单名与该菜单的上级菜单，如果他没有上级菜单，也要将其查询出来
select mu1.name, mu2.name
from menu mu1 left join menu mu2 on mu1.parent_id = mu2.id
12. 联合查询union
概念：就是将多次查询的结果合并起来，形成一个新的查询结果集。
语法：
select 字段列表 from 表A
union [all]
select 字段列表 from 表B
-- 对于联合查询的多张表要求列数必须保持一致。且字段类型也必须保持一致。
-- union all会将多个表的记录直接合并在一起， union 会对合并后的数据去重。
-- 如：查询薪资低于5000的员工，和年龄大于50岁的员工。
-- 1. 先查询薪资低于5000的员工
select * from emp where salary < 5000;
-- 2. 查询年龄大于50的员工
select * from emp where age > 50;
-- 3. 合并
select * from emp where salary < 5000;
union
select * from emp where age > 50;
13. 子查询
概念：在sql语句中嵌套select 语句，称为嵌套查询，又称为子查询。
分类：
- 标量子查询【子查询结果为
单个值】 - 列子查询【子查询结果为
一列】 - 行子查询【子查询结果为
一行】 - 表子查询【子查询结果为
多行多列】
子查询放置位置分类：
- select 后面
- from 后面
- where 后面
13.1 标量子查询
查询结果：单个值
常用操作符：
- =
- !=
- 大于
- 大于等于
- 小于
- 小于等于
-- 查询销售部所有员工信息
-- 1. 查询销售部的部门ID
-- select id from dept where name = '销售部';
-- 2. 根据销售部ID，查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部';)
13.2 列子查询
常用的操作符：
- in
- not in
- any【子查询返回列表中，有任意一个满足即可】
- some【与any等同，使用some的地方也可以使用any】
- all【子查询返回列表的所有值都必须满足】
-- 查询“销售部”和“市场部”的所有员工 【列子查询示例-in】
-- 1. 查询销售部和市场部的id
select id from department where name = '销售部' or name = '市场部'
-- 2. （子查询方式）查询员工的部门id为销售部或市场部的所有员工
select name from emp where department_id in (select id from department where name = '销售部' or name = '市场部')
-- ★. （内连接inner join方式）改造它
SELECT e.name FROM emp e INNER JOIN department d ON e.department_id = d.id WHERE d.name = '销售部' OR d.name = '市场部';
-- 查询比财务部所有人工资都高的员工工资 【列子查询示例-all】
-- 1. 查询财务部部门id
select id from department where name = '财务部'
-- 2. 查询财务部所有人的id
select id from emp where department_id = (select id from department where name = '财务部')
-- 3. （子查询方式）查询比财务部所有人工资都高的员工工资
select id, name, salary from emp where salary > all(select salary from emp where department_id = (select id from department where name = '财务部'))
13.3 行子查询
行子查询查询的结果：一行一列/一行多列
常用的操作符：
- =
- <>
- in
- not in
-- 查询与张三的年龄相同的员工
-- 1. 查询张三的年龄
select age from emp where name = '张三'
-- 2. 查询查询与张三的年龄相同的员工
select * from emp where age = (select age from emp where name = '张三')
13.4 表子查询
表子查询返回的结果是：多行多列。
常用的操作符：
- in
-- 查询入职日期是“2006-01-01”之后的员工信息，及其部门信息。
-- 1. 查询入职时间是“2006-01-01”之后的员工信息
select * from emp where creatime > '2006-01-01'
-- 2. 查询这部分员工所对应的部门信息
select e.*, d.* from (select * from emp where creatime > '2006-01-01') e left join dept d on e.dept_id = d.id;