※食用指南：文章内容为牛客网《专项练习-数据库SQL》167道选择题（上），重点笔记，用于重复思考错题，加深印象
练习传送门：专项练习-数据库SQL-177题
目录：
5、 CASE CHARINDEX WHEN THEN ‘ ’
10、查询购买过goods_id 为1001的用户user_id
1、查询出每门课都大于80 分的学生姓名
学生成绩表score，部分内容如下：
name course grade
张三 操作系统 67
张三 数据结构 86
李四 软件工程 89
SELECT DISTINCT name
FROM score
WHERE name NOT IN (SELECT name
FROM score
WHERE grade <= 80);2、选取数据插入到另一张表
某打车公司要将驾驶里程（drivedistanced）超过5000里的司机信息转存到一张称为seniordrivers的表中，他们的详细情况被记录在表drivers中
SELECT * INTO seniordrivers
FROM drivers
WHERE drivedistanced >=5000①SELECT INTO ：从一张表中选取数据插入到另一张表中，要求目标表不存在，因为在插入时会自动创建
常用于创建表的备份复件或者用于对记录进行存档
②INSERT INTO：用于向一张表中插入新的行，要求目标表存在
3、运算符把NULL的值对应的记录排除掉
表student_table(id,name,birth,sex)，插入如下记录：
('1001' , '' , '2000-01-01' , '男');
('1002' , null , '2000-12-21' , '男');
('1003' , NULL , '2000-05-20' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , '李四' , '2001-12-01' , '女');
①执行以下查询的结果行数是2：李四和逗号
SELECT *
FROM student_table
WHERE name <> '张三' ②查询2001年及之后出生的男生、女生总数
SELECT sex,COUNT(*)
FROM student_table
WHERE birth >='2001'
GROUP BY sex③执行以下查询的结果是1001，1004，1005共3行
LENGHT无法对null做筛选
SELECT *
FROM student_table
WHERE LENGTH(name) >= 04、删除重复记录，保留第一条
表student_table(id,name,birth,sex)
删除name重复的id最大的记录，比如'张三'重复2次，id分别是1、2，则删除id=2的记录，保留id=1的记录
DELETE FROM student_table
WHERE id IN (SELECT id
FROM (SELECT MAX(id) AS id
FROM student_table
GROUP BY name
HAVING COUNT(*) > 1 ) AS t);5、 CASE CHARINDEX WHEN THEN ‘ ’
查询显示雇员的姓名和姓名中是否含有字母A的信息，满足如下条件
①如果字符A在姓名的首位，则显示'字符A在首位'
②如果字符A在姓名的末位，则显示'字符A在末位'
③如果字符A在姓名中不存在，则显示'没有字符A'
④其他情况显示'字符A在中间'
SELECT ename, CASE CHARINDEX(‘A‘,ename)
WHEN 1 THEN ‘字符A在首位‘
WHEN LEN(ename) THEN ‘字符A在末位‘
WHEN 0 THEN ‘没有字符A‘
ELSE‘字符A在中间‘
END 名称类别
FROM emp;CHARINDEX：如果能够找到对应的字符串，则返回该字符串位置i（有效位置范围为1<= i <= length(input)），否则返回0
位置是从1开始
6、运动会比赛目前总积分最高的系名及其积分
运动员ATHLETE（运动员编号 Ano，姓名Aname，性别Asex，所属系名 Adep）
项目 ITEM （项目编号Ino，名称Iname，比赛地点Ilocation）
成绩SCORE （运动员编号Ano，项目编号Ino，积分Score）
SELECT Adep,SUM(Score)
FROM ATHLETE,SCORE
WHERE ATHLETE.Ano=SCORE.Ano
GROUP BY Adep
HAVING SUM(Score)>=ALL(SELECT SUM(Score)
FROM ATHLETE,SCORE
WHERE ATHLETE.Ano=SCORE.Ano
GROUP BY Adep)①ALL()：对所有数据都满足条件，整个条件才成立
>=ALL()等价于MAX
<=ALL()等价于MIN
②ANY()：只要有一条数据满足条件，整个条件成立
>ANY()等价于>MIN
<ANY()等价于<MAX
（SOME的作用和ANY一样）
7、通配符的使用规则
现要选取居住地址Address不以'C'或'O'开头的人员信息
SELECT *
FROM Person Address
REGEXP '^[^CO]';REGEXP、NOT REGEXP 运算符 (RLIKE 和 NOT RLIKE) 来操作正则表达式
8、DELETE语句
要求删除商品表中价格大于3000的商品
DELETE FROM 商品
WHERE 价格>3000DELETE不需要列名或通配符，DELETE删除整行而不是删除列
9、HAVING子句的使用
①HAVING子句中能够使用三种要素：常数、聚合函数、聚合建（GROUP BY子句中指定的列名）
②HAVING子句既可包含聚合函数作用的字段也可包括普通的标量字段
③HAVING子句必须于GROUP BY子句同时使用，不能单独使用；
用GROUP BY子句不一有HAVING子句（它只是一个筛选条件用的）
④没有聚合函数HAVING子句可以和GROUP BY子句一起使用
在STUDENT表中按class_type统计数据行数分组情况后，筛选出数据行数为大于10行的组
SELECT class_type，COUNT（*）
FROM STUDENT
GROUP BY class_type
HAVING COUNT(*)>1010、查询购买过goods_id 为1001的用户user_id
有两张表，如下图所示
表A（仅列出部分数据作参考）
Order_id User_id Add_time
11701245001 10000 1498882474
11701245002 10001 1498882475
表B（仅列出部分数据作参考）
id Order_id goods_id price
1 11701245001 1001 10
2 11701245001 1002 20
3 11701245002 1001 10
在子表元素大于1时， 不能用= 要用IN（子表）
以下两种方法皆可：
SELECT a.user_id
FROM A a,B b
WHERE a.order_id=b.order_id AND b.goods_id='1001'SELECT user_id
FROM A
WHERE order_id IN (SELECT order_id
FROM B
WHERE goods_id = '1001')11、DATE_ADD()函数
函数向日期添加指定的时间间隔
已知某田径运动员某月训练表如下
表drill：
| id | date | kilometer |
| 1 | 2020-07-01 | 10 |
| 2 | 2020-07-02 | 12 |
| 3 | 2020-07-03 | 11 |
| 4 | 2020-07-04 | 15 |
试查找与前一天的日期相比，千米数更高的所有日期的id和kilometer
以下三种方法皆可：
SELECT d2.id,d2.kilometer
FROM drill d1,drill d2
WHERE DATEDIFF(d2.date,d1.date)=1
AND d1.kilometer < d2.kilometerSELECT d2.id,d2.kilometer
FROM drill d1 CROSS JOIN drill d2
ON DATEDIFF(d2.date,d1.date)=1
WHERE d1.kilometer < d2.kilometerSELECT d2.id,d2.kilometer
FROM drill d1
JOIN drill d2 ON(DATE_ADD(d1.date,INTERVAL 1 DAY) = d2.date)
WHERE d1.kilometer < d2.kilometer;12、求高于平均值的商品名
子查询的方式从衬衫表SHIRTABLE中选取出销售单价shirt_price高于全部衬衫的平均价格的衬衫名字
SELECT shirt_id，shirt_name，shirt_price
FROM SHIRTABLE
WHERE shirt_price > (SELECT AVG(shirt_price)
FROM SHIRTABLE)13、三表查询用IN运算符
查询至少有一门课程与sno=1909的学生选择的课程相同的学生的学号和姓名
已知某校数据库中包含如下的表数据：
学生表student(sno,sname,birthday,gender)
课程表course(cid,cname)
成绩表grade(sno,cid,mark)
SELECT DISTINCT a.sno,a.sname
FROM student a,grade b
WHERE a.sno<>1909 AND a.sno=b.sno
AND b.cid IN (SELECT cid
FROM grade
WHERE sno=1909)14、JION、RIGHT JOIN、LEFT JOIN
MySQL中表student_table(id,name,birth,sex)，插入如下记录：
('1004' , '张三' , '2000-08-06' , '男');
('1005' , NULL , '2001-12-01' , '女');
('1006' , '张三' , '2000-08-06' , '女');
('1007' , ‘王五’ , '2001-12-01' , '男');
('1008' , '李四' , NULL, '女');
('1009' , '李四' , NULL, '男');
('1010' , '李四' , '2001-12-01', '女');
SELECT t1.*,t2.*
FROM
(SELECT * FROM student_table WHERE sex = '男' ) t1
RIGHT JOIN
(SELECT * FROM student_table WHERE sex = '女') t2
ON t1.birth = t2.birth ANS t1.name = t2.nameRIGHT JOIN意思是包含INNER JOIN的结果（左右表中的birth、name都不为NULL时才会匹配上），无法匹配t1中一个字段为NULL或两个字段都为NULL的记录（所以t1结果不含有'李四'、‘王五’）
一分钟让你搞明白 left join、right join和join的区别-CSDN博客
注意：MySQL(版本8.0.25)不支持FULL JOIN，会执行报错
15、DROP、TRUNCATE、DELETE
| DROP | TRUSTCATE | DELETE | |
| 处理效率 | 1 | 2 | 3 |
| 删除范围 | 完全删除表，包括表结构 | 只能删除表数据，会保留表结构，而且不能加WHERE | 只删除数据,保留表的结构，而且可以加WHERE，只删除一行或者多行 |
| 高水位线 | / | 会将高水线复位，自增ID变为1 | 不影响自增ID值，高水线保持原位置不动 |
某软件公司正在升级一套水务管理系统。该系统用于县市级供排水企业、供水厂、排水厂中水务数据的管理工作。系统经重新整合后，开发人员决定不再使用一张备份数据表waterinfo001表，需永久删除
DROP TABLE waterinfo00116、UPDATE语句
有一张Course表包含如下数据：
| user_id | course_status | course_date |
| 2 | 学习 Python | 2021-09-30 |
现要把Course表中user_id为2的course_status更新为'学习SQL'，course_date更新为'2021-10-01’
UPDATE语句更改多个字段的状态时，字段中间用逗号，而不用AND
UPDATE Course
SET course_status = '学习SQL', course_date = '2021-10-01'
WHERE user_id = 2;17、第一次登录的时间
在gameList表中(player_id, event_date)是主键，查找出每个player_id的第一次登录的时间（event_date）
| player_id | device_id | event_date | games_played |
| 111 | 21 | 2020-03-01 | 5 |
| 111 | 21 | 2020-01-02 | 6 |
| 212 | 33 | 2020-09-03 | 1 |
| 322 | 11 | 2020-01-21 | 0 |
| 322 | 44 | 2020-03-02 | 5 |
SELECT player_id, MIN(event_date) AS first_login
FROM gameList
GROUP BY player_idMIN()聚合函数，求数字最小，日期最早
MAX()聚合函数，求数字最大，日期最近
18、授予、撤销权限
①GRANT TO：授予权限
已知数据库学生成绩及学生表，现授予用户USER1在学生表上的SELECT权限
USE 学生成绩
GO GRANT SELECT ON 学生表
TO USER1②REVOKE FROM：撤销权限
现在有一个学生表student，需要回收所有机器的nkw用户对学生表student所在数据库user的update和insert权限
@'%' ：是表示任何主机的通配符
REVOKE UPDATE,INSERT ON user.*
FROM 'nkw'@'%';
19、“体育馆”进行比赛的各项目名称及其冠军的姓名
大学生春季运动会的数据库，保存了比赛信息的三个表如下：
运动员 sporter（运动员编号 sporterid，姓名name，性别 sex，所属系号 department）
项目 item（项目编号 itemid，名称 itemname，比赛地点 location）
成绩 grade（运动员编号 id，项目编号 itemid，积分 mark）
解题步骤
①首先找出在“体育馆”中进行的比赛项目id
②然后在成绩表中根据项目id进行分组后找出单个项目最高分
③接下来将上面含有项目id和项目最高分信息的表与另外三张表连接
④最后按要求从连接后的表中选出项目名称和冠军姓名
SELECT i.itemname,s.name
FROM grade g,(SELECT itemid iid,MAX(mark) max
FROM grade WHERE itemid IN (SELECT itemid
FROM item
WHERE location='体育馆') GROUP BY itemid) temp,item i,sporter s
WHERE g.itemid=temp.iid AND g.mark=temp.max
AND temp.iid=i.itemid
AND s.sporterid=g.sporterid;20、最大、最小出生日期
Mysql中表student_table(id,name,birth,sex)，分别查询男生、女生的最大、最小出生日期
SELECT sex,
MAX(birth) AS max_birth,
MIN(birth) AS min_birth
FROM student_table
GROUP BY sex;21、DATEDIFF() 函数
取出BORROW表中日期(RDATE字段)为当天的所有记录
(RDATE字段为DATETIME型，包含日期与时间)
SQL Server的语法：
DATEDIFF() 函数返回两个日期之间的时间
SELECT *
FROM BORROW
WHERE DATEDIFF(dd,RDATE,getdate())=022、查询均分大于等于80分的前五名
现有评分表evaluate（包含班级编号cid和分数point字段），有班级表grade（包含班级编号cid等字段）
查询evaluate 表中有没有班级均分大于等于80分的，如果存在，则查询显示grade表按cid由大到小排名的前五行记录
SELECT *
FROM grade
WHERE EXISTS (SELECT cid,AVG(point) AS avg
FROM evaluate
GROUP BY cid
HAVING avg>=80)
ORDER BY grade.cid DESC
LIMIT 5 ;23、每个学生所选课程的个数
假设有选课表course_relation(student_id, course_id)，其中student_id表示学号，course_id表示课程编号
求和用累加SUM()
求行的个数用累计COUNT()
SELECT student_id, COUNT(course_id)
FROM course_relation
GROUP BY student_id;
24、把某组数据从A表中添加到B表
已知职员表employee(eno,ename,gender,birthday,salary)，现有一张E表，表结构与职员表一致
将E表中没有在职员表中出现的女职员添加到职员表中
INSERT INTO employee(eno,ename,gender,birthday,salary)
SELECT eno,ename,gender,birthday,salary
FROM E
WHERE E.gender='女' AND NOT EXISTS(SELECT *
FROM employee
WHERE employee.eno=E.eno)25、不重复id总数
Mysql中表student_table(id,name,birth,sex)，id字段值可能重复，分别查询男生、女生的不重复id总数
解题步骤：
①CASE WHEN判断男女
②取出id号并去重
③使用COUNT进行id号统计数量
SELECT
COUNT(DISTINCT CASE WHEN sex='男' THEN id ELSE NULL END) AS man_ids,
COUNT(DISTINCT CASE WHEN sex='女' THEN id ELSE NULL END) AS women_ids
FROM student_table;26、行转列
有一张学生成绩表sc（sno 学号，class 课程，score 成绩），示例如下：
查询出每个学生的英语、数学的成绩（行转列，一个学生输出一行记录，比如输出[1, 89, 90]）
SELECT sno,
SUM(IF(class='english',score,0)) AS english,
SUM(IF(class='math',score,0)) AS math
FROM sc
WHERE class IN('english','math')
GROUP BY sno27、至少被订购过两次的productid
有订单表orders，包含字段用户信息userid，字段产品信息productid
SELECT productid
FROM orders
GROUP BY productid
HAVING COUNT（productid）>128、COALESCE
取COALESCE中第一个非NULL的值，如果都是空，返回空值
name1：fat
name2：pig
SELECT COALESCE（NULL，fat，2）AS name1，
COALESCE（pig，test，test）AS name2
FROM NAMETABLE29、统计各个部门的工资条数
departments部门表（注：dept_no是主键）：
dept_emp部门-员工表：
salaries工资表:
统计各个部门的工资条数，按照dept_no、dept_name、sum（工资条数）输出，并按dept_no升
SELECT d.dept_no, d.dept_name,
COUNT(s.salary) AS sum
FROM salaries s
JOIN dept_emp e ON s.emp_no = e.emp_no
JOIN departments d ON d.dept_no = e.dept_no
GROUP BY d.dept_no,d.dept_name
ORDER BY d.dept_no;
30、UNION ALL 操作符
employees表：
dept_manager表：
从employees表和dept_manager表中选取出所有的county值（包含重复值）并按county的升序排列
SELECT country
FROM employees UNION ALL SELECT country
FROM dept_manager
ORDER BY country;
31、窗口函数的排序操作、位运算、两表连接JOIN函数
emloyees表：
根据name排名(按升序排序)，找到排名为奇数对应的name值，输出结果不用排序
SELECT e.name
FROM employees e
JOIN (SELECT name,
DENSE_RANK() OVER(ORDER BY name) AS num
FROM employees) AS t
ON e.name = t.name
WHERE t.num & 1 = 1;
————TBC
标签：GROUP,name,数据库,student,SQL,177,WHERE,id,SELECT From： https://blog.csdn.net/2401_86505958/article/details/140830141