专项练习-数据库SQL-177题（上）

※食用指南：文章内容为牛客网《专项练习-数据库SQL》167道选择题（上），重点笔记，用于重复思考错题，加深印象

练习传送门：专项练习-数据库SQL-177题

目录：

1、查询出每门课都大于80 分的学生姓名

2、选取数据插入到另一张表

3、运算符把NULL的值对应的记录排除掉

4、删除重复记录，保留第一条

5、 CASE CHARINDEX WHEN   THEN ‘ ’

6、运动会比赛目前总积分最高的系名及其积分

7、通配符的使用规则

8、DELETE语句

9、HAVING子句的使用

10、查询购买过goods_id 为1001的用户user_id

11、DATE_ADD()函数

12、求高于平均值的商品名

13、三表查询用IN运算符

14、JION、RIGHT JOIN、LEFT JOIN

15、DROP、TRUNCATE、DELETE

16、UPDATE语句

17、第一次登录的时间

18、授予、撤销权限

19、“体育馆”进行比赛的各项目名称及其冠军的姓名

20、最大、最小出生日期

21、DATEDIFF() 函数

22、查询均分大于等于80分的前五名

23、每个学生所选课程的个数

24、把某组数据从A表中添加到B表

25、不重复id总数

26、行转列

27、至少被订购过两次的productid

28、COALESCE

29、统计各个部门的工资条数

30、UNION ALL 操作符

31、窗口函数的排序操作、位运算、两表连接JOIN函数

---

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) >= 0

4、删除重复记录，保留第一条

表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 价格>3000

DELETE不需要列名或通配符，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(*)>10

10、查询购买过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.kilometer

SELECT d2.id,d2.kilometer
FROM drill d1 CROSS JOIN drill d2
ON DATEDIFF(d2.date,d1.date)=1
WHERE d1.kilometer < d2.kilometer

SELECT 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.name

RIGHT 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 waterinfo001

16、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_id

MIN()聚合函数，求数字最小，日期最早

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())=0

22、查询均分大于等于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 sno

27、至少被订购过两次的productid

有订单表orders，包含字段用户信息userid，字段产品信息productid

SELECT productid 
FROM orders 
GROUP BY productid 
HAVING COUNT（productid）>1

28、COALESCE

取COALESCE中第一个非NULL的值，如果都是空，返回空值

name1：fat

name2：pig

SELECT COALESCE（NULL，fat，2）AS name1，
       COALESCE（pig，test，test）AS name2 
FROM NAMETABLE

29、统计各个部门的工资条数

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