数据库的高阶知识

数据库的基本知识
数据库的高阶知识

一、case when

  在实际工作中，经常会涉及以下两类问题：

• 数据的映射处理：比如将离散的数字变成有意义的业务说明，或是将连续的数值映射到离散的区间带
• 有针对性的筛选计算：将细颗粒单位转换为粗颗粒单位的聚合运算

  这两类问题，我们通常使用case when语句来解决，case语句其实就是mysql中的控制流语句，类似于其他很多编程工具中的if …then…的分支判断逻辑。
  关于case when的表达方式有两种：“简单case函数法”和“case搜索函数法”。这两种方法各有优点，比如“简单case函数法”的语法更加简洁，但是只能处理等式的问题，“case搜索函数法”更加灵活好用，可以处理等式问题也可以处理不等式问题。

# 简单case函数法
case <name>
	when <when_condition> then <result>
	[when <when_condition> then <result>]
	[......]
	[else <else_result>]
end
#case搜索函数法
case 
	when <bool_condition> then <result>
	[when <bool_condition> then <result>]
	[.......]
	[else <else_result>]
end

  下面以电商数据为例学习CASE WHEN语法在数据查询中几种常见用法，该数据一共包含7个字段和1000条样本，7个字段分别是用户ID，用户出生日期、下单时间、订单ID、支付方式、支付金额和是否享受折扣。数据链接：https://pan.quark.cn/s/af980c28a2d0

-- 离散数值与实际业务含义一一对应
select *,
		case 
		when is_discount = 1 then "享受折扣"
		else "无折扣"
		end discount_new,
		case
		when pay_type = 1 then "支付宝支付"
		when pay_type = 2 then "微信支付"
		else "银行卡支付"
		end pay_type_new
from goods_orders


#根据用户的出生日期将用户分为70后，80后，90后,00后
select *,
		case
		when year(birthday) between 1970 and 1979 then '70后'
		when year(birthday) between 1980 and 1989 then '80后'
		when year(birthday) between 1990 and 1999 then '90后'
		else '00后'
		end AGE_GROUP
from goods_orders

#长形统计表转换成宽形统计表
select month(order_date) as imonth,
				sum(case when pay_type = 1 then pay_amt end) as 'ali_pay',
				sum(case when pay_type = 2 then pay_amt end) as 'wechat',
				sum(case when pay_type = 3 then pay_amt end) as 'bank_card'
from goods_orders		
where year(order_date) = 2023
group by imonth
order by imonth

二、几种常见的嵌套查询

  嵌套查询，也称为子查询，是实际工作中经常用到的一种查询方式。子查询其实就是在已有的查询语句中的where后面再嵌套一层查询语句，也就是把内层查询结果当做外层查询参照的数据表来使用。
  在工作中，经常会遇见4种子查询，即含有比较运算符(>、>=、<、<=、=、!=)、ANY/ALL关键词、IN关键词以及EXISTS关键词的嵌套查询。数据链接 ：https://pan.quark.cn/s/223ade8607a3

2.1 比较运算符

  比较运算符用于比较两个值，返回布尔值（TRUE 或 FALSE）。

#创建学生信息表
CREATE TABLE stu_info (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 学生ID，自增主键
    iname VARCHAR(20),                 -- 学生姓名
    gender CHAR(1),                    -- 性别（M: 男, F: 女）
    department VARCHAR(10),            -- 所属院系
    age INT,                           -- 年龄
    province VARCHAR(10),              -- 省份
    email VARCHAR(50),                 -- 邮箱
    mobilephone CHAR(11)               -- 手机号（11位）
);

INSERT INTO stu_info (iname, gender, department, age, province, email, mobilephone)
VALUES
('张三', 'M', '计算机', 20, '北京', 'zhangsan@example.com', '13800138000'),
('李四', 'M', '数学', 21, '上海', 'lisi@example.com', '13800138001'),
('王五', 'F', '物理', 22, '广东', 'wangwu@example.com', '13800138002'),
('赵六', 'M', '化学', 19, '江苏', 'zhaoliu@example.com', '13800138003'),
('孙七', 'F', '生物', 20, '浙江', 'sunqi@example.com', '13800138004'),
('周八', 'M', '计算机', 21, '北京', 'zhouba@example.com', '13800138005'),
......
('武勇', 'M', '生物', 21, '浙江', 'wuyong@example.com', '13800138049');

#学生成绩表
CREATE TABLE stu_score (
    id INT,          -- 学生ID
    Chinese TINYINT, -- 语文成绩（0-100）
    Math TINYINT,    -- 数学成绩（0-100）
    English TINYINT  -- 英语成绩（0-100）
);

INSERT INTO stu_score (id, Chinese, Math, English)
VALUES
(1, 87, 90, 83),
(2, 78, 85, 88),
(3, 92, 76, 81),
......
(50, 87, 80, 83);

# 1.查询年龄超过所有学员平均年龄的学员信息
#第一步先计算学员平均年龄
select avg(age) from stu_info

#筛选超过平均年龄的学员信息
select * from stu_info
where age >= 20.5

#子查询
select * from stu_info
where age >= (select avg(age) from stu_info)

# 2.查询年龄不低于所属系平均年龄的学员信息
select * from stu_info as t1
where age >=(
select avg(age) from stu_info as t2
where t1.department = t2.department)

2.2 ANY/ALL 关键词

  对于含比较运算符的嵌套查询来说，嵌套部分的查询语句只能返回一个值。那如果子查询返回多个值，就需要用到ANY或者ALL关键词了。通常，ANY/ALL关键词经常和比较运算符连用，下面是6种比较运算符与ANY/ALL 关键词的搭配结果:

组合	含义	组合	含义
>ANY	大于子查询结果中的某个值	>ALL	大于子查询结果中的所有值
>=ANY	大于等于子查询结果中的某个值	>=ALL	大于等于子查询结果中的所有值
<ANY	小于子查询结果中的某个值	<ALL	小于子查询结果中的所有值
<=ANY	小于等于子查询结果中的某个值	<=ALL	小于等于子查询结果中的所有值
=ANY	等于子查询结果中的某个值	=ALL	等于子查询结果中的所有值
!=ANY	不等于子查询结果中的某个值	!=ALL	不等于子查询结果中的所有值

# 2.查询非物理系中比物理系任意一个学员年龄小的学员信息
select * from stu_info 
where department != '物理' and
age < any(
select distinct age 
from stu_info
where department = '物理'
)

#3.查询非物理系中比物理系所有学员年龄大的学员信息
select * from stu_info 
where department != '物理' and
age > all(
select distinct age 
from stu_info
where department = '物理'
)

2.3 in 关键词

  IN 用于判断某个值是否在指定的值列表中。它可以替代多个 OR 条件，使查询更简洁。

# 查询计算机和物理系的学员信息
select * from stu_info
where department in ("计算机","物理")

#查询和张三、王五同一个系的学员信息
select * from stu_info
where department in (
select department from stu_info
where iname in("张三","王五"))

#查询语文成绩大于85分的学员信息
select * from stu_info where id in (select id from stu_score
where Chinese >85)

2.4 EXISTS关键词

  EXISTS 用于检查子查询是否返回任何行。如果子查询返回至少一行，则 EXISTS 返回 TRUE，否则返回 FALSE。

#查询语文成绩大于85分的学员信息
select * from stu_info where exists (select * from stu_score
where stu_score.id = stu_info.id and Chinese >85)

2.5 in和exists的异同点

• 使用场景对比
    in适合用于静态值列表或子查询结果集较小的情况。

-- 查询数学成绩为 85、90 或 95 的学生
SELECT * FROM stu_score
WHERE Math IN (85, 90, 95);

-- 查询在某个班级的学生
SELECT * FROM stu_info
WHERE class_id IN (SELECT class_id FROM class WHERE class_name = '计算机');

  exists适合用于动态子查询或子查询结果集较大的情况。

-- 查询有成绩记录的学生
SELECT * FROM stu_info
WHERE EXISTS (
    SELECT 1 FROM stu_score
    WHERE stu_score.id = stu_info.id
);

-- 查询没有成绩记录的学生
SELECT * FROM stu_info
WHERE NOT EXISTS (
    SELECT 1 FROM stu_score
    WHERE stu_score.id = stu_info.id
);

• 性能对比
  IN 的性能：
  当子查询结果集较小时，IN 的性能较好，因为子查询的结果会被缓存，外部查询只需要与缓存的结果进行比较。
  当子查询结果集较大时，IN 的性能较差，因为缓存大量数据会占用内存，且比较操作较慢。
  EXISTS 的性能：
  当子查询结果集较大时，EXISTS 的性能较好，因为它不需要缓存结果，且可以在找到第一个匹配项时立即返回 TRUE。
  当子查询结果集较小时，EXISTS 的性能可能略低于 IN，因为需要为外部查询的每一行执行一次子查询。

• NULL值处理区别
  in：如果子查询的结果中包含 NULL，IN 会忽略 NULL，不会将其与外部查询的值进行比较。

SELECT * FROM stu_score
WHERE Math IN (85, 90, NULL);
#如果 Math 是 NULL，上述查询不会返回该行。

EXISTS：不关心子查询的具体值，只关心是否存在匹配的行。

SELECT * FROM stu_info
WHERE EXISTS (
    SELECT 1 FROM stu_score
    WHERE stu_score.id = stu_info.id AND Math IS NULL
);

三、开窗函数

  结合order by关键词和limit关键词是可以解决很多的topN问题，比如从二手房数据集中查询出某个地区的最贵的10套房，从电商交易数据集中查询出实付金额最高的5笔交易，从学员信息表中查询出年龄最小的3个学员等。但是，如果需求变成从二手房数据集中查询出各个地区最贵的10套房，从电商数据集中查询出每月实付金额最高的5笔交易，从学员信息表中查询出各个科系下年龄最小的3个学员，该如何解决呢?
  其实这类问题的核心就是，筛选出组内的topN，而不是从全部数据集中挑选出topN。遇到这种既需要分组也需要排序的问题，直接上开窗函数就能解决了。
（1）开窗函数定义
  开窗函数是 SQL 中一种特殊的函数，用于在查询结果集的“窗口”（即一组相关行）上执行计算。与普通的聚合函数（如 SUM、AVG）不同，开窗函数不会将多行合并为一行，而是为每一行返回一个计算结果，同时保留原始行的详细信息。 注：MySQL 8.0 及以上版本支持开窗函数，低于版本不支持。
  开窗函数分为两部分，一部分是函数名称，开窗函数的数量比较少，总共才11个开窗函数+聚合函数（所有聚合函数都可以用作开窗函数），根据函数的性质，有的需要写参数，有的不需要写参数。
  另一部分为over语句，over()语句必须要写的，里面的参数都是非必须参数，可以根据需求有选择的使用。

函数名(字段) OVER (
    [PARTITION BY 分组字段] #含义是根据此字段将数据集分为多份
    [ORDER BY 排序字段] #每个窗口的数据依据此字段进行升序或降序排列
    [ROWS/RANGE 窗口范围]
)

函数名：开窗函数的名称，如 ROW_NUMBER()、RANK()、SUM() 等。
OVER：定义窗口的范围和规则。
PARTITION BY：将数据分组，类似于 GROUP BY，但不会合并行。
ORDER BY：指定窗口内的排序规则。
ROWS/RANGE：定义窗口的物理或逻辑范围。

#创建虚拟的业务员销售数据
create table Sales(
						idate date,
						iname char(5),
						sales int)
#向表中插入数据
insert into Sales values('2024-01-01', '张三', 200)


-- 插入50行模拟数据
INSERT INTO Sales (idate, iname, sales)
VALUES
('2024-01-01', '张三', 200),
('2024-01-02', '张三', 650),
('2024-01-03', '王五', 200),
('2024-01-04', '赵六', 250),
('2024-01-05', '王五', 300),
......
('2024-02-18', '苏勇', 2500);

# 一、查询各月中销售业绩最差的业务员
select * from(
select month(idate),iname,sales,row_number() over(partition by month(idate) order by sales) as sales_order from sales) as t  #易错点：在 SQL 中，所有的派生表必须有一个别名，没有指定别名，MySQL 报错
where sales_order= 1

# row_number()、rank()、dense_rank()的区别
select * from(
select month(idate),iname,sales,row_number() over(partition by month(idate) order by sales) as row_order,
								rank() over(partition by month(idate) order by sales) as rank_order,
								dense_rank() over(partition by month(idate) order by sales) as dense_order from sales) as t

# 连续登录的问题：创建虚拟的用户登录表
create table user_login(
				user_id varchar(100),
				login_time datetime);
# 二、查看每位用户连续登录的情况
#1、时间戳格式修改为时间格式
create table user_login_date (
select distinct user_id,date(login_time) login_date
from user_login)

# login_date - irank 相等表示连续登录
-- select * from user_login_date

#2、对用户登录数据进行排序
create table user_login_data_1 (
			select *,rank() over(partition by user_id order by login_date) irank
			from user_login_date)

-- select * from user_login_data_1

# 3、date_sub 从指定的日期减去指定的时间间隔
create table user_login_data_2(
select *,date_sub(login_date,interval irank day) idate from user_login_data_1);

-- select * from user_login_data_2
# 4、计算连续登录的用户情况

select user_id,min(login_date) as start_date,
							max(login_date) as end_date,
							count(login_date) as days
from user_login_data_2
group by user_id,idate


#整合代码
select user_id,min(login_date) as start_date,
							max(login_date) as end_date,
							count(login_date) as days
from (select *,date_sub(login_date,interval irank day) idate from (select *,rank() over(partition by user_id order by login_date) irank
			from (select distinct user_id,date(login_time) login_date
from user_login) as c) as b) as a 
group by user_id,idate


# 三、查看每位用户最大连续登录的天数
select user_id,max(days) 
     from(select user_id,min(login_date) as start_date,
							max(login_date) as end_date,
							count(login_date) as days
from (select *,date_sub(login_date,interval irank day) idate from (select *,rank() over(partition by user_id order by login_date) irank
			from (select distinct user_id,date(login_time) login_date
from user_login) as c) as b) as a 
group by user_id,idate) as d
group by user_id

# 四、查看在这段时间内连续登录天数>=5天的用户
#方案1：
select distinct user_id from (select user_id,min(login_date) as start_date,
							max(login_date) as end_date,
							count(login_date) as days
from (select *,date_sub(login_date,interval irank day) idate from (select *,rank() over(partition by user_id order by login_date) irank
			from (select distinct user_id,date(login_time) login_date
from user_login) as c) as b) as a 
group by user_id,idate
having days >=5) d 

#方案2：
#lead()函数：静态窗口函数
select *,lead(login_date,4) over (partition by user_id order by login_date ) as idate5
from user_login_date

#计算第5次登录与当天的差值
select *,datediff(idate5,login_date)+1 days
from (select *,lead(login_date,4) over (partition by user_id order by login_date ) as idate5
from user_login_date) as a 

#查找相差天数为5的记录
select distinct user_id from (select *,datediff(idate5,login_date)+1 days
from (select *,lead(login_date,4) over (partition by user_id order by login_date ) as idate5
from user_login_date) as a )as b 
where days = 5

  DATE_SUB 是 SQL 中用于从日期中减去指定时间间隔的函数。它通常用于计算过去某个时间点的日期。以下是 DATE_SUB 函数的详细用法及解释：

DATE_SUB(date, INTERVAL value unit)
# date：要操作的日期或日期时间值。
# INTERVAL：表示时间间隔的关键字。
# value：要减去的时间间隔值，必须是一个整数。
# unit：时间间隔的单位，支持以下值：秒、分钟、小时、天、周、月、季度、年
示例：
SELECT DATE_SUB('2023-10-01', INTERVAL 5 DAY); # 从 2023-10-01 减去 5 天：结果：2023-09-26

  LEAD() 是 SQL 中的一种 窗口函数（Window Function），用于获取当前行之后的某一行数据。它通常用于分析时间序列数据或计算相邻行之间的差异。

LEAD(column_name, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
)
# column_name：需要获取值的列名。
# offset（可选）：指定要获取的行数偏移量。默认值为 1，表示获取下一行。
# default_value（可选）：如果偏移量超出范围（如最后一行没有下一行），则返回的默认值。如果未指定，默认返回 NULL。
# PARTITION BY（可选）：将数据分组，LEAD() 函数会在每个分组内单独计算。
# ORDER BY（可选）：指定窗口内的排序规则。

  LEAD() 函数的作用：获取当前行之后的某一行数据；常用于计算相邻行之间的差异，或分析时间序列数据。

四、高效查询

  随着数据量的增大，企业对查询的速度要求也越来越高，掌握高效查询的方法对于平时的工作是非常有帮助的。为了提高数据的查询速度，最常用的解决方案就是给表中变量创建索引。可以将索引理解成书的目录，如果一本书没有目录，那检索起来可能就比较麻烦，一旦有了目录，我们就可以根据目录进行索引，很快地找到我们需要的内容。同样的道理，如果数据表中有了索引，就可以大大提高MySQL的执行效率。

4.1、常见索引类型

4.1.1 普通索引

  普通索引是一种没有任何约束的索引，它对表中变量的值不做任何的限制，不管变量的值是否存在重复值或缺失值(也就是NULL值)，所以普通索引是使用最频繁的一种索引。可以通过两种方式来建立普通索引:

• 创建新表时设定某个字段为普通索引
• 基于已有的表添加普通索引

#两种普通索引的创建方法

#1、建表时创建索引
create table <table_name> (field1 data_type1,
						   field2 data_type2,
						   field3 data_type3,
						    .......
						   index <index_name>(field1));

#2、对已有表添加索引，可以通过创建法或者修改法
create table <index_name> on <table_name> (field_list); #基于已有的表创建索引
alter table <table_name> add index <index_name> on (field_list);#基于已有的表修改索引

select * from sales
where iname in ('刘洋','程飞','熊磊') #0.016s
#创建索引
create index cus_sales on sales (iname);

select * from sales
where iname in ('刘洋','程飞','熊磊') #0.013s(数据量小直观不明显，数据量大速度有明显提升)

4.1.2 唯一索引

  相对于普通索引来说，唯一索引对字段或者字段组合是有约束的，也就是必选确保字段或者字段组合的每一个观测值都是唯一的，不能存在重复值。如果字段中还有多个空白字符串，也算是有重复值，因为空字符串代表一种值。
  一个表中可以有多个唯一索引，创建唯一索引的方法也有两种，与创建普通索引类似。可以在创建新表的时候设置唯一索引，也可以对已有表添加唯一索引。

# 两种唯一索引的创建方法
#1、建表时创建索引
create table <table_name> (field1 data_type1,
						   field2 data_type2,
						   field3 data_type3,
						    .......
						   unique <index_name>(field1));

#2、对已有表添加索引，可以通过创建法或者修改法
create unique index <index_name> on <table_name> (field_list); #基于已有的表创建索引
alter table <table_name> add unique <index_name> on (field_list);#基于已有的表修改索引

#无索引的查询
select * from tourism_orders
where userid ='user050'   #0.016

创建一个唯一索引
create unique index id_idx on tourism_orders(userid,orderid)

select * from tourism_orders
where userid ='user050'   #0.014

4.1.3 主键索引

  主键索引对字段的要求最为严格，必须确保字段中的值既不存在重复值也不存在缺失值。与普通索引和唯一索引不同的是，一张表中只能有一个主键索引。关于主键索引的创建语法:
两个表连接时将连接字段设置为主键索引，会处理提升时间。

# 两种唯一索引的创建方法
#1、建表时创建索引
create table <table_name> (field1 data_type1,
						   field2 data_type2,
						   field3 data_type3,
						    .......
						  primary key <index_name>(field1,field2);

#2、对已有表添加索引
alter table <table_name> add primary key <index_name> (field_list);#基于已有的表修改索引

#查看索引 
show index from tourism_orders
#删除索引
drop index <索引名> on <数据表名>
alter table <表名> drop index <索引名>

4.1.4 索引注意事项

1）何时创建索引

• WHERE 关键词后面的字段创建索引，可以加快条件判断速度
• ORDER BY关键词后面的字段创建索引，可以加快排序速度
• 表连接关键词 ON 后面的字段创建索引，可以加快表连接速度
• 包含大量NULL的字段不适合创建索引，因为索引不可以包含NULL值
• 包含大量重复值的字段不适合创建索引，因为基于索引的查询规则，在进行条件筛选的时候可能会产生大量的数据行，此时索引并不能加快数据库搜索过程中的扫描速度

2）索引无效情况

• WHERE 关键词后面的条件表达式中如果使用IN、OR、!=或者<>，均会导致索引无效。解决方法是将"!=“或者”<>“替换为”>AND<“， 将"IS NOT NULL"替换为”>+=CHR(0)”
• 筛选或排序过程中，如果对索引列使用函数，则索引失效。
• 筛选过程中，如果字符型字段写成了数值型的数字，则索引失效(比如，用户id字段是字符型，那筛选的时候需要写whereuid = “1”)
• 使用模糊查询的时候，如果将通配符放在开头，则索引失效(like“%aaa%"不会使用索引而like "aaa%可以使用索引)
• 对于多列的组合索引，遵循左原则，例如对字段A,B,C设置索引 INDEX(A,B,C)，则"A>0"、“A=1 AND B>10”、“A=10 AND B< 6 AND C>100"都可以使组合索引有效，但是"B>10”、"B< 6 AND C>100"都会导致组合索引失效。
• 在JOIN操作中，关键词ON后面的字段类型要保持(也就是左表中这个字段的数据类型和右表中同一字段的数据类型要保持一致)，否则索引无效。