MySQL 中级刷题笔记

MySQL 中级刷题笔记

一、增删改查

（一）插入记录

1、批量插入

INSERT INTO exam_record (uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(1002, 9002, '2021-09-04 07:01:02', NULL, NULL);

2、数据备份

明确考点：

插入记录的方式汇总：

1. 普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, …)
2. 普通插入（限定字段）：INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)
3. 多条一次性插入：INSERT INTO table_name (column1, column2, …) VALUES (value1_1, value1_2, …), (value2_1, value2_2, …), …
4. 从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]

思路实现：
本题可采用第四种插入方式，需根据细节剖析的点做稍微改动，改为限定字段插入，即只插入除自增id列以外的列:

INSERT INTO exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
SELECT uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE YEAR(submit_time) < '2021';

当然上面的WHERE里简化了细节剖析里的后两个条件，也可以分开来筛选，写作：

WHERE YEAR(start_time) < '2021' and submit_time IS NOT NULL;

3、插入数据

注意：该数据可能存在

思路实现：

解法一:

REPLACE INTO examination_info
VALUES(NULL,9003,'SQL','hard',90,'2021-01-01 00:00:00');

1. 关键字NULL可以用DEFAULT替代。
2. 掌握replace into···values的用法

replace into 跟 insert into功能类似，不同点在于：replace into 首先尝试插入数据到表中，
如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；
否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

解法二:

DELETE FROM examination_info
WHERE exam_id=9003;
INSERT INTO examination_info
VALUES(NULL,9003, 'SQL','hard', 90, '2021-01-01 00:00:00')

（二）更新记录

1、更新数据

题目： 请把examination_info表中tag为PYTHON的tag字段全部修改为Python。

明确考点：

修改记录的方式汇总：

• 设置为新值：UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]
• 根据已有值替换：UPDATE table_name SET key1=replace(key1, ‘查找内容’, ‘替换成内容’) [WHERE column_name3=value3]

思路实现：
本题采用两种修改方式均可，语义为『当tag为PYTHON时，修改tag为Python』，先用第一种:

UPDATE examination_info
SET tag = "Python"
WHERE tag = "PYTHON";

如果采用第二种，写作：(推荐)

推荐使用：查询时间更短，占用内存更少，与第一种相比

UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag = "PYTHON";

思维扩展：第二种方式不仅可用于整体替换，还能做子串替换，例如要实现将tag中所有的PYTHON替换为Python（如CPYTHON=>CPython），可写作：

UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag LIKE "%PYTHON%";

2、更新数据

明确考点：

修改记录的方式汇总：

• 设置为新值：UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]
• 根据已有值替换：UPDATE table_name SET key1=replace(key1, ‘查找内容’, ‘替换成内容’) [WHERE column_name3=value3]

细节剖析：

1. 只改2021年9月1日之前开始作答的记录；
2. 只改未完成的记录；
3. 改为被动完成：完成时间改为’2099-01-01 00:00:00’，分数改为0

思路实现：
本题宜采用第一种修改方式，满足条件1和条件2就修改:

UPDATE exam_record
SET submit_time='2099-01-01 00:00:00', score=0
WHERE start_time < '2021-09-01 00:00:00' AND score IS NULL;

（三）删除记录

1、删除数据-时间计算

明确考点：

删除记录的方式汇总：

• 根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
• 全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

时间差：

TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：

1. SECOND 秒
2. MINUTE 分钟（返回秒数差除以60的整数部分）
3. HOUR 小时（返回秒数差除以3600的整数部分）
4. DAY 天数（返回秒数差除以3600*24的整数部分）
5. MONTH 月数
6. YEAR 年数

---

细节剖析：

1. 作答时间小于5分钟整的记录；
2. 分数不及格（及格线为60分）的记录；

---

思路实现：
本题采用第一种删除方式，满足条件1和条件2就删除:

DELETE FROM exam_record
WHERE TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
AND score < 60;

2、删除数据-时间计算

细节剖析：

• 未完成作答的记录；
• 或作答时间小于5分钟整的记录；
• 开始作答时间最早的3条记录；

思路实现：

本题采用第一种删除方式，满足条件1或条件2就删除，但只删除3条记录:

DELETE FROM exam_record
OR TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
ORDER BY start_time
LIMIT 3;

3、删除数据-清空数据

题目： 请删除exam_record表中所有记录，并重置自增主键。

明确考点：

删除记录的方式汇总：

• 根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
• 全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

---

详解：truncate table 和 delete

truncate table 在功能上，与不带where字句的delete语句相同；二者均删除表中的全部行，但truncate table 比delete速度更快，且使用的系统和事务日志资源少。 truncate 删除表中的所有行，但表的结构及其列，约束，索引等保持不变。新行标识所用的技术值重置为该列的种子。如果想保留标识计数值，轻盖拥delete 。如果要删除表定义及其数据，请使用drop table 语句。

细节剖析：

• 删除exam_record表中所有记录；
• 并重置自增主键；

---

思路实现：

方式一：（推荐使用）

TRUNCATE exam_record;

方式二：

DELETE FROM exam_record;
ALTER TABLE exam_record auto_increment=1;

二、表与索引操作

（一）表的创建、修改与删除

1、创建表

明确考点：

表的创建、修改与删除：

• 1.1 直接创建表：

CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

• 1.2 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old

• 1.3 从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

• 2.1 修改表：ALTER TABLE 表名 修改选项 。选项集合：

{ ADD COLUMN <列名> <类型>  -- 增加列
 | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
 | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
 | MODIFY COLUMN <列名> <类型> -- 修改列类型
 | DROP COLUMN <列名> -- 删除列
 | RENAME TO <新表名> -- 修改表名
 | CHARACTER SET <字符集名> -- 修改字符集
 | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

3.1 删除表：DROP TABLE [IF EXISTS] 表名1 [ ,表名2]

细节剖析：

1. 自增ID：AUTO_INCREMENT；
2. 设置主键：PRIMARY KEY；
3. 唯一性约束：UNIQUE
4. 非空约束：NOT NULL
5. 设置默认值：DEFAULT 0
6. 当前时间戳：CURRENT_TIMESTAMP
7. 评论/注释：COMMENT
8. 如果该表已创建过，正常返回：IF NOT EXISTS

思路实现：

本题采用第1种创建方式，根据细节剖析中的点组织完整创建语句:

CREATE TABLE IF NOT EXISTS user_info_vip (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    nick_name varchar(64) COMMENT '昵称',
    achievement int DEFAULT 0 COMMENT '成就值',
    `level` int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
) CHARACTER SET utf8 COLLATE utf8_general_ci;

2、修改表

题目：

请在用户信息表，字段level的后面增加一列最多可保存15个汉字的字段school；并将表中job列名改为profession，同时varchar字段长度变为10；achievement的默认值设置为0。

思路实现：

alter table user_info add school varchar(15) after level;
# 增加列在某列之后
alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)
 
alter table user_info change job profession varchar(10);
# 更换列的名称及数据类型
alter table user_info change 原列名 修改列名 修改数据类型
 
alter table user_info modify achievement int(11) default 0;
#更改数据类型
alter table 表名 modify 修改列名称 数据类型 默认值等

3、删除表

细节剖析：

• 把2011到2014年的备份表都删掉；
• 如果存在才删除；

思路实现：

本题采用第1种删除方式，根据细节剖析中的点组织完整语句:

DROP TABLE IF EXISTS exam_record_2011, exam_record_2012, exam_record_2013, exam_record_2014;

（二）索引的创建、删除

1、创建索引

题目：

现有一张试卷信息表examination_info，其中包含各种类型试卷的信息。为了对表更方便快捷地查询，需要在examination_info表创建以下索引，规则如下：
在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。

思路实现：

# 方法一：
# 普通索引
CREATE INDEX idx_duration ON examination_info(duration);
# 唯一索引
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
# 全文索引
CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);

# 方法二：(效率更高）
alter table examination_info add index idx_duration(duration);
alter table examination_info add unique uniq_idx_exam_id(exam_id);
alter table examination_info add Fulltext full_idx_tag(tag);

2、删除索引

题目：

请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。

思路实现：

# 方法一：(效率更高）
alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;

# 方法二：
drop index   uniq_idx_exam_id on examination_info;
drop index  full_idx_tag  on examination_info;

三、聚合分组查询

（一）聚合函数

1、聚合函数——查询

明确题意：

计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）

问题分解：

• 关联作答记录和试卷信息：join examination_info using(exam_id)；
• 筛选SQL高难度试卷：where tag=“SQL” and difficulty=“hard”
• 计算截断平均值：(和-最大值-最小值) / (总个数-2): (sum(score) - max(score) - min(score)) / (count(score) - 2)

细节问题：

• 表头重命名：as
• 保留1位小数：round(…, 1)

思路实现：

select tag, difficulty,
    round((sum(score) - max(score) - min(score)) / (count(score) - 2), 1) as clip_avg_score
from exam_record
join examination_info using(exam_id)
where tag="SQL" and difficulty="hard"

2、聚合函数——查询

明确题意：

统计出总作答次数total_pv、试卷已完成作答数complete_pv、已完成的试卷数complete_exam_cnt

问题分解：

• 总作答次数：count(exam_id) as total_pv；
• 试卷已完成作答数，count(A)会忽略A的值为null的行：count(submit_time) as complete_pv；
• 已完成的试卷数，已完成时才计数用if判断，试卷可能被完成多次，需要去重用distinct：count(distinct if(submit_time is not null, exam_id, null)) as complete_exam_cnt

细节问题：

• 表头重命名：as
• 保留1位小数：round(…, 1)

思路实现：

select
    count(exam_id) as total_pv,
    count(submit_time) as complete_pv,
    count(distinct if(submit_time is not null, exam_id, null)) as complete_exam_cnt
from exam_record

2、聚合函数——查询

明确题意：

• 从试卷作答记录表中找到类别为的SQL试卷得分不小于该类试卷平均得分的用户最低得分
• 其中试卷信息记录在表examination_info（包括试卷ID、类别、难度、时长、发布时间），答题信息记录在表exam_record（包括试卷ID、用户ID、开始时间、结束时间、得分）

问题分解：

1.要找类别为SQL的试卷平均得分：

1、得分信息在exam_record，试卷类别在表examination_info中，因此要将两个表以exam_id连接。知识点：join…on…
2、 从连接后的表中找到类别为SQL的试卷的分数。知识点：select…from…where… 3、
3、 计算得分的平均值。知识点：avg()

2.找到类别SQL的试卷得分大于平均得分的最小值：

1、得分信息在exam_record，试卷类别在表examination_info中，因此要将两个表以exam_id连接。知识点：join…on…
2、从连接后的表中找到类别为SQL的试卷且分数大于刚刚找到的平均分的分数。知识点：select…from…where…and…
3、从中选出最小值。知识点：min()

思路实现：

select min(e_r.score) as min_score_over_avg
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
where e_i.tag = 'SQL'
and score >= (select avg(e1.score)
             from exam_record e1 join examination_info e2
             on e1.exam_id = e2.exam_id
             where tag = 'SQL'
             )

（二）分组查询

1、分组查询

题目主要信息：

计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau
结果保留两位小数

问题拆分：

• 根据提交时间submit_time不为空筛选活跃的的人。知识点：select…from…where…

• 筛选每个月的平均活跃天数和总活跃人数：

  1.根据月份来选择时间。知识点：date_format() 通过这个函数匹配’%Y%m’年份和月份；

  2.计算用户平均活跃天数：

  1、根据不同的日期且不同的用户ID统计每个月用户的总活跃天数。知识点：distinct、count()、date_format()
  2、统计每个月用的总人数。知识点：distinct、count()
  3、总天数/总人数得到每个月的用户平均活跃天数；

  3.计算每月总活跃人数，直接统计每月不同的用户id数。知识点：count()、distinct

• 按照月份分组group by date_format(submit_time, '%Y%m')知识点：group by …

• 保留两位小数。 知识点：round(x,2)

代码实现：

select date_format(submit_time, '%Y%m') as month,
       round((count(distinct uid, date_format(submit_time, '%y%m%d'))) / count(distinct uid), 2) as avg_active_days,
       count(distinct uid) as mau
from exam_record
where submit_time is not null
and year(submit_time) = 2021
group by date_format(submit_time, '%Y%m')

2、分组查询

明确题意：

统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt，以及该年的总体情况

问题拆解：

• 本题主要是考察知识点：group by、date_format、day、last_day、round、union all
• DATE_FORMAT(submit_time, “%Y%m” ) 返回的是202109这样；day(‘2021-08-02 11:41:01’)返回的是2，last_day(‘2021-08-02 11:41:01’)返回的是2021-08-31，last_day(‘2021-09-02 11:41:01’)返回的是2021-09-30
• 先按月份分组
• month_q_cnt = 当月的记录总数
• avg_day_q_cnt = month_q_cnt / 当月的天数，通过day(last_day(…))得到当月的天数
• union all 汇总的数据。多个表union all时不会去重记录，字段名、字段顺序、字段类型要一致。

代码实现：

select coalesce(year_mon,'2021汇总') as submit_month,
count(question_id) as month_q_cnt,
round(count(question_id)/max(t.days_month),3) as avg_day_cnt
from
(select question_id,
dayofmonth(last_day(submit_time)) as days_month,
date_format(submit_time,'%Y%m') as year_mon
from practice_record
where year(submit_time)=2021) as t
group by t.year_mon
with rollup;

2、分组查询

明确题意：

统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5），输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，按未完成试卷数量由多到少排序

问题分解：

1. 关联作答记录和试卷信息：left join examination_info on using(exam_id)；（题中exam_record中的exam_id在examination_info均存在，所以用left join和inner join效果一样）
2. 筛选2021年的记录：where year(start_time)=2021
3. 获取各用户的tag,start_time及未完成标记和已完成标记，如果该作答记录交卷了则已完成标记为1，未完成标记为0，否则相反：if(submit_time is null, 1, null) as incomplete
4. 按用户分组：group by uid
5. 统计未完成试卷作答数和已完成试卷作答数：count(incomplete) as incomplete_cnt
6. 统计作答过的tag集合：
   对于每条作答tag，用:连接日期和tag：concat_ws(':', date(start_time), tag)
   对于一个人（组内）的多条作答，用;连接去重后的作答记录：group_concat(distinct concat_ws(':',date(start_time), tag) SEPARATOR ';')
7. 筛选未完成试卷作答数大于1的有效用户：having complete_cnt >= 1 and incomplete_cnt BETWEEN 2 and 4
   1、完成试卷作答数至少为1：complete_cnt >= 1
   2、未完成数小于5：incomplete_cnt < 5
   3、未完成试卷作答数大于1：incomplete_cnt > 1

细节问题：

• 表头重命名：as
• 按未完成试卷数量由多到少排序：order by incomplete_cnt DESC

代码实现：

SELECT uid, count(incomplete) as incomplete_cnt,
    count(complete) as complete_cnt,
    group_concat(distinct concat_ws(':', date(start_time), tag) SEPARATOR ';') as detail
from (
    SELECT uid, tag, start_time,
        if(submit_time is null, 1, null) as incomplete,
        if(submit_time is null, null, 1) as complete
    from exam_record 
    left join examination_info using(exam_id)
    where year(start_time)=2021
) as exam_complete_rec
group by uid
having complete_cnt >= 1 and incomplete_cnt BETWEEN 2 and 4
order by incomplete_cnt DESC

四、多表查询

（一）嵌套子查询

1、嵌套子查询

明确题意：

统计月均完成试卷数不小于3的用户爱作答的类别及作答次数，按次数降序输出

问题拆解：

1. 筛选完成了的试卷的记录。知识点：where
2. 筛选月均完成数不小于3的用户。知识点：
   1、按用户分组group by uid；
   2、统计当前用户完成试卷总数count(exam_id)；
   3、统计该用户有完成试卷的月份数count(distinct DATE_FORMAT(start_time, "%Y%m"))；
   4、分组后过滤having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3；
3. 关联试卷作答记录表和试卷信息表。知识点：join examination_info using(exam_id)
4. 筛选满足条件的用户。知识点：where uid in (…)
5. 统计这些用户作答的类别及计数。知识点：按用户分组group by uid；计数count(tag)；
6. 按次数降序输出。知识点：order by tag_cnt desc

代码实现：

select tag, count(tag) as tag_cnt
from exam_record
join examination_info using(exam_id)
where uid in (
    select uid
    from exam_record
    where submit_time is not null 
    group by uid
    having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3
)
group by tag
order by tag_cnt desc

2、嵌套子查询

明确题意：

计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score； 按人数降序，相同人数的按平均分升序

问题分解：

1. 获取每张SQL类别试卷发布日期，作为子查询：
   1、筛选试卷类别：WHERE tag = “SQL”
   2、获取试卷ID和发布日期：SELECT exam_id, DATE(release_time)
2. 筛选发布当天的作答记录：WHERE (exam_id, DATE(start_time)) IN (…)
3. 筛选5级以上的用户：AND uid IN (SELECT uid FROM user_info WHERE level > 5)
4. 按试卷ID分组：GROUP BY exam_id
5. 计算作答人数：count( DISTINCT uid ) AS uv
6. 计算平均分（保留1位小数）：ROUND(avg( score ), 1) AS avg_score

细节问题：

• 表头重命名：as
• 按人数降序，按平均分升序：ORDER BY uv DESC, avg_score ASC

代码实现：

SELECT
    exam_id,
    count( DISTINCT uid ) AS uv,
    ROUND(avg( score ), 1) AS avg_score
FROM exam_record 
WHERE (exam_id, DATE(start_time)) IN (
    SELECT exam_id, DATE(release_time)
    FROM examination_info WHERE tag = "SQL"
) AND uid IN ( SELECT uid FROM user_info WHERE `level` > 5 )
GROUP BY exam_id
ORDER BY uv DESC, avg_score ASC;

（二）合并查询

（三）连接查询

五、窗口函数

（一）专用窗口函数

1、创建表

（二）聚合窗口函数

六、其他常用操作

（一）空值处理

1、创建表

（二）高级条件语句

（三）限量查询

（四）文本转换函数

关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。