sql（MySQL）易错点、难点

已于 2024-04-15 21:00:41 修改
阅读量187 收藏
点赞数 2
文章标签： sql mysql 数据库
于 2024-04-07 10:57:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2401_83591620/article/details/137455118
版权
文章详细介绍了如何在MySQL中使用DELETEFROM子查询限制、UNION操作处理复杂SQL，以及IN多字段条件下的聚合函数。同时讨论了GROUPBY与窗口函数的使用，以及在查询用户活跃度和成绩时如何结合正则表达式筛选特定用户和试卷。
摘要由CSDN通过智能技术生成

如有侵权，请联系我删除，本文仅作学习交流。

  • delete from

sql语句：mysql 的delete from 子查询限制_delete from where in-CSDN博客

注意：测试中发现可以使用同个表，不像上文参考博客中所说需嵌套别名操作。

  • union

遇到较为复杂的SQL语句（超过1行，测试），union前后需要给SQL语句各加’()'才可允许成功。

  • in多字段的情况

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;

group by 数字

在MySQL中，”GROUP BY 1″表示使用第一个选择列进行分组 

  • 窗口函数排序多字段

示例（来源牛客网习题）：找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。

题目链接（需会员）：每类试卷得分前3名_牛客题霸_牛客网

1）建表语句

drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 78),
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null);

2）查询语句

SELECT tid,uid,ranking
FROM (
    SELECT b.tag as tid,a.uid,
		/*先按照最高分，再按照最低分排，最后按照uid排序 
		隐含uid一定不同，可用row_number*/
    ROW_NUMBER() OVER(PARTITION BY tag ORDER BY max(a.score) DESC,min(a.score) DESC,a.uid DESC) ranking
    FROM exam_record a 
    LEFT JOIN examination_info b using(exam_id)
    GROUP BY b.tag,a.uid) t1
    
WHERE ranking<=3

3）输出结果

 

根据用户活跃记录：求月活客量、每月新增客户数、每月截止当月新增客户数最大值、截止当月累计客户量

示例：来源牛客网

要点1：使用客户最早活跃日期作为客户新增的判断依据。

1）建表语句
drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1002, 9001, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 89),
(1002, 9001, '2020-02-01 12:11:01', '2020-02-01 12:31:01', 83),
(1003, 9001, '2020-03-01 19:01:01', '2020-03-01 19:30:01', 75),
(1004, 9001, '2020-03-01 12:01:01', '2020-03-01 12:11:01', 60),
(1003, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90),
(1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1004, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1003, 9002, '2020-02-02 12:01:01', '2020-02-02 12:31:01', 68),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-03-02 12:11:01', null, null);
2）查询语句
SELECT#第2步：求月活客户数、每月新增客户数、每月截止当月最大新增客户数、截止当月累计客户量
		start_month ,#每个月
		COUNT(DISTINCT uid) mau, #月活用户数   
    SUM(new_day) month_add_uv, #新增用户
    MAX(SUM(new_day)) OVER(ORDER BY start_month) max_month_add_uv, #截止当月的单月最大新增用户数
    SUM(SUM(new_day)) OVER(ORDER BY start_month) cum_sum_uv
FROM (
    SELECT#第1步：月份、用户新增时间
			*,DATE_FORMAT(start_time,'%Y%m') start_month,
			IF(start_time=MIN(start_time) OVER(PARTITION BY uid),1,0) new_day
    FROM exam_record) t1
GROUP BY start_month;
3）输出结果

在SQL中使用正则表达式

参考1：筛选昵称规则和试卷规则的作答记录_牛客题霸_牛客网

参考2：SQL的模糊匹配区别---like,rlike,regexpx_rlike和like的区别 sql-CSDN博客

参考3：mysql中 instr、like、rlike三者的用途区别_sql rlike-CSDN博客

参考4：SQL 正则表达式_sql正则表达式-CSDN博客

参考5（有各种字符匹配的讲解）：SQL中常见正则表达式用法_sql正则匹配-CSDN博客

1.表达式内容讲解

RLIKE后面可以跟正则表达式。

1

正则表达式"^[0-9]+$"的意思：

1、字符^

意义：表示匹配的字符必须在最前边。

例如：^A不匹配“an A”中的‘A’，但匹配“An A”中最前面的‘A’。

2、字符$

意义：与^类似，匹配最末的字符。

例如：t$不匹配“eater”中的‘t’，但匹配“eat”中的‘t’。

3、字符[0-9]

意义：字符列表，匹配列出中的任一个字符。你可以通过连字符-指出字符范围。

例如：[abc]跟[a-c]一样。它们匹配“brisket”中的‘b’和“ache”中的‘c’。

4、字符+

意义：匹配+号前面的字符1次及以上。等价于{1,}。

2.示例

 找到昵称以"牛客"+纯数字+"号"或者纯数字组成的用户对于字母c开头的试卷类别（如C,C++,c#等）的已完成的试卷ID和平均得分，按用户ID、平均分升序排序。

1）建表语句
drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

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

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1', 1900, 2, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 2200, 5, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 2500, 6, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客555号', 2000, 7, 'C++', '2020-01-01 10:00:00'),
  (1006, '666666', 3000, 6, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'C++', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, 'c#', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9003, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89),
(1004, 9002, '2021-08-06 12:01:01', null, null),
(1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 81),
(1002, 9002, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82),
(1002, 9002, '2020-02-02 12:11:01', '2020-02-02 12:31:01', 83),
(1005, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84),
(1006, 9001, '2021-09-01 11:01:01', '2021-09-01 11:31:01', 84),
(1002, 9001, '2021-09-06 12:01:01', '2021-09-06 12:21:01', 80),
(1002, 9001, '2021-09-06 12:01:01', null, null),
(1002, 9001, '2021-09-07 12:01:01', null, null);
2）查询语句
SELECT uid, exam_id, ROUND(AVG(score),0) avg_score FROM exam_record
WHERE uid IN (SELECT uid FROM user_info 
              WHERE nick_name RLIKE "^牛客[0-9]+号$" OR nick_name RLIKE "^[0-9]+$") AND
      exam_id IN (SELECT exam_id FROM examination_info
                 WHERE tag RLIKE "^[cC]")
			AND score IS NOT NULL
GROUP BY uid, exam_id
ORDER BY uid,avg_score
;
3）输出结果

2401_83591620
关注
  • 2
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值