※食用指南:文章内容为牛客网《专项练习-数据库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) >= 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