数据库精选题（三）（SQL语言精选题）（按语句类型分类）

🌈 个人主页：十二月的猫-CSDN博客
🔥 系列专栏： 🏀数据库

💪🏻 十二月的寒冬阻挡不了春天的脚步，十二点的黑夜遮蔽不住黎明的曙光

 目录

前言

创建语句

创建表

创建视图

创建索引

插入语句

一次插入一条

一次插入多条

删除语句 

修改语句

修改表结构

修改表格内容

查询语句 

分类一：没有任何

分类二：至少有一个 

分类三：满足条件一同时满足条件二但不满足条件三

分类五：模糊查询%的使用

分类五：间接关系（祖孙关系、间接先行课关系）

分类六：找出所有

分类七：存在重复项只记录一次（学生多次考试只取最高成绩）

分类八：存在具体数值要求+分组处理

分类九：超过所有

分类十：聚集函数嵌套的转化方法

分类十一：带条件更新

分类十二： 同表格不同角色匹配问题

 总结 

前言

数据库的操作无非就是增、删、改、查+创建

接下来，我将从以上五个角度来对SQL精选题进行分类，并总结相应解法

创建语句

数据库中创建主要包括：创建表、创建视图、创建索引、创建用户

创建的方式有：直接创建（直接指定创建内容）、间接创建（利用已有视图或表创建）

创建表

直接创建：

create table table1(sid char(12) not null,name varchar(10) not null,age int,birthday date,credit numeric(4,1)primary key(sid)foreign key(name) references student_name(name)
);

先设定属性；再设定主键、外键等键值 

间接创建：

create table table1 as
select sid,cid,score
from student
where sid='202100202052'

两者比较： 

直接创建：需要用小括号确定表结构范围（注意逗号、分号、括号）

间接创建：需要用as，但是会根据查询结果直接创建，不需要用（）确定范围（啥也没有）

创建视图

视图的创建使用as间接创建，因为视图并不实际存储于数据库中，所以是基于已有表进行的

直接创建：

视图不能直接定义创建。因为视图并不存储在数据库中，而是基于已有表进行的创建

间接创建：

create view test3 as
select sid，cid,pub.course_name,score
from pub.student natural join pub.student_course natural join pub.course
where pub.student_name='李龙'

两者比较： 

视图的创建只能用间接创建法：利用as、不用加（）限制范围 

创建索引

索引的创建没有直接创建和间接创建的说法，索引只能定义创建，不能根据已有索引创建

create index index1 on student(sid,name)

插入语句

插入语句分为：一次插入一条记录、一次插入多条记录两种类型

一次插入一条

insert into student values(202100202052,'李华',to_date('19950303 101010','yyyymmdd hh24miss'))

一次插入多条

insert allinto test1_course values('300002','数据库','300001',2.5)into test1_course values('300001','数据结构',null,2)
select * from dual

两者比较：  

1、insert into

2、insert all into

3、date类型的插入利用to_data('20080715 101010‘，‘yyyymmdd hh24miss’）

删除语句 

1、删除语句和插入语句的格式是相对应的

2、本部分的精选题重点在于各种全新的语句用法（不仅仅限于and、from、where等一般语句）

总结如下：

• regexp_like（sid,'^[0-9]+$')
• where score between 0 and 100
• length(sid)
• like '% %'
• where 后面跟(cid,tid）这种整体匹配语句

删除语句对应的是插入语句（一个是增一个删），因此两者的语法也类似。insert into和delete from相匹配

题目一：

删除表中的学号不全是数字的那些错误数据，学号应该是数字组成，不能够包含字母空格等非数字字符。方法之一：用substr函数，例如Substr(sid,1,1)返回学号的第一位，判断是否是数字。

delete from test3_01
where sid not in(select sidfrom test3_01where regexp_like(sid, '^[0-9]+$'))

题目二：

 删除表中的性别有错误的那些错误数据（性别只能够是“男”、“女”或者空值）。

delete from test3_03
where sex <> '男' and sex <> '女' and sex is not null

题目三：

删除表中的院系名称有空格的、院系名称为空值或者院系名称小于3个字的那些错误数据。

delete from test3_04 
where dname like '% %' or
dname is null or
length(dname) < 3

题目四： 

删除其中的错误数据，错误指如下情况：课程号和教师编号在教师授课表pub.teacher_course中不同时存在的，即没有该教师教该课程； 

delete from test3_08 
where (cid, tid) not in(select cid, tidfrom pub.teacher_course
)

题目五：

 删除其中的错误数据，错误指如下情况：成绩数据有错误（需要先找到成绩里面的错误）。

delete from test3_09
where score not between 0 and 100

修改语句

在创建数据库表格并往表格内插入数据后，我们可能还需要对表格进行修改，因此修改语句也是必要的。修改语句包括：修改表结构、修改表格内容

修改表结构

alter table test4_02
add avg_score numeric(3,1)

alter table test4_02
drop column avg_score 

修改表格内容

update test4_01 Sset sum_score=(select sum(score)from pub.student_course Twhere S.sid=T.sid)

update test4_07
set sex= replace( sex,'性','')

update test4_10
set age=(select (2012-extract (year from birthday))from pub.student_42where test4_10.sid=sid
)
where test4_10.age is null;

 两者联系及感悟：  

1、只有修改表结构和创建表结构时需要指定table和view

2、修改表格内容时用update以及set函数

3、replace函数常用来update不规范数据

4、extract函数用来提取date类型数据中的year、month、day都可以

重点来啦！！！数据库SQL语言中最难的部分就是查询。数据库查询大约占数据库语言使用的80%（李晖老师说的），所以查询语句写的好/不好对整体数据库性能的影响是很大的

查询语句 

编写语句的思考流程：

1、确定要查询的变量

2、根据要查询的变量确定要查询的关系表（哪几个表、是否要自然连接处理等）

3、写查询的条件（最难的部分）

分类一：没有任何

没有任何=完全否定=全集-只要有一个在里面

使用minus实现

找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学号、姓名(不包含这名同学)。 

select sid,name
from pub.student
where sid in((select distinct sidfrom pub.student_coursewhere cid in (select cidfrom pub.student_coursewhere sid='200900130417'))minus(select sidfrom pub.studentwhere sid='200900130417')
)

分类二：至少有一个 

in：或关系，只要有一个就是满足的

至少有一个=只要一个就满足=或关系

找出至少选修了一门其先行课程号为“300002”号课程的学生的学号、姓名。

 select sid, namefrom  pub.studentwhere sid in(select sidfrom pub.student_coursewhere cid in ( select cidfrom pub.student_coursewhere pcid='300002')
)

分类三：满足条件一同时满足条件二但不满足条件三

满足多个条件/不满足多个条件的处理：

1、对于关系集合用minus、intersect来求交并集；

2、对于在where查询条件后的用and、or等来处理

3、一个属性上多条件——》交并集（minus、intersect）；多个属性上多条件（and、or）

找出选修了“操作系统”并且也选修了“数据结构”，但是没有选修“程序设计语言”的学生的学号、姓名。

select sid，namefrom pub.studentwhere sid in((select sidfrom pub.student_coursewhere cid=(select cidfrom pub.coursewhere name='操作系统'))intersect(select sidfrom pub.student_coursewhere cid=(select cidfrom pub.coursewhere name='数据结构'))minus(select sidfrom pub.student_coursewhere cid=(select cidfrom pub.coursewhere name='程序设计语言')))

查询2010级、计算机科学与技术学院、操作系统的学生成绩表，内容有学号、姓名、成绩。

  select sid,name,scorefrom pub.student_course natural join pub.studentwhere class='2010' and dname='计算机科学与技术学院' and cid=(select cidfrom pub.coursewhere name='操作系统')

分类五：模糊查询%的使用

%：就是模糊查询；模糊查询与like以及not like绑定使用

条件字符串部分限制、部分自由

查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name

select sid,name
from pub.student
where name not like '张%'
and name not like '李%'
and name not like '王%'

分类五：间接关系（祖孙关系、间接先行课关系）

两种方法：

1、利用笛卡尔积实现

2、利用嵌套结构实现

比较和联系：

1、笛卡尔积A*B实现本质就是让A中的每一个元组和B中的每一个元组比较

2、嵌套结构先找出B的所有元组，利用in来将A中的每个元组和B的每个元组比较比较

找出有间接先行课的所有课程的课程号、课程名称。

select cid ,name
from pub.course
where fcid in (select cidfrom pub.coursewhere fcid is not NULL
)

已知所有的父子关系fstable表，找出里面的爷孙关系的名字

select s1.father,s2.son
from fstable s1,fstable s2
where s1.son=s2.father

分类六：找出所有

所有关系：用除运算实现，除运算找出的就是集合和其子集的关系（所有关系）

在SQL中不能使用除运算，改为用not exists实现

找出选修了所有课程的学生的学号、姓名。

select sid,name
from pub.student
where not exists((select cidfrom pub.course)minus(select cidfrom pub.student_coursewhere pub.student_course.sid=pub.student.sid)
)

分类七：存在重复项只记录一次（学生多次考试只取最高成绩）

表格1存在重复项，想要计算没有重复项参与的表格1中的数据

1、找中介人：找到一个不存在重复项的表格2，利用表格2实现对表格1的剔除工作。此时，表格2作用为：剔除者、观察者；表格1作用为：实际要找寻的资料原件

使用update语句,利用pub.student_course、pub.course，统计 “总学分”；

（这是需要注意：成绩及格才能够计算所得学分,一门课多个成绩都及格只计一次学分）

update test4_03 Sset sum_credit=(select sum(credit)from pub.coursewhere cid in(select distinct cidfrom pub.student_course SCwhere S.sid=SC.sidand SC.score>=60))

分类八：存在具体数值要求+分组处理

分组处理：需要用表中的某一个元素将其他信息聚合起来（出版社/会员）

对于每个出版商，找出每个借了该出版商五本书以上的会员的编号和姓名

select memb_no,name
from member m
where memb_no in(select memb_nofrom borrowed bw,book bkwhere bw.isbn=bk.isbngroup by publisher,memb_nohaving (count(*)>5)
)

having：后面跟的应该是bool类型的表达式结果，having和where一样是筛选条件

having的作用范围：分组后的组内

分类九：超过所有

超过所有=超过最大值——》利用max来求解最大值

 找出所有收入超过 "小型银行公司 "每个员工的员工 ID

select id
from work
where salary >
（select max(salary)from workswhere company_name='Small Bank Corporation'
）

假设一个公司可以在好几个城市有分部。找出位于“Small Bank Corporation”所在城市的所有公司（company_name是主键，所以一家公司只能在一个城市）

Select company_name
From Company
Where city in (select cityFrom companyWhere company_name = 'Small Bank Corporation'
)

上题的难度在于对题目的理解：位于“Small Bank Corporation”所在城市。

1、找出“Small Bank Corporation”的城市

2、利用in来实现位于动作

分类十：聚集函数嵌套的转化方法

有时候我们会遇到需要使用两个聚集函数的情况，但是在一个关系中聚集函数是不能够嵌套使用的，因此需要用别的方法来代替聚集函数嵌套使用

常见的嵌套场景：count+max（求count元组数中的最大值）

找出雇员最多的公司名称（或公司名称、在最多员工数相同的情况下）的公司名称

1、首先需要求解每个公司的雇员——需要count聚集函数

2、求解雇员数最大的公司——需要max函数对count结果使用

转化：雇员最多=雇员比最多的还多（count+max）=雇员比所有的都多（count+all），成功避开聚集函数的嵌套

select company_name
from works
group by company_name
having count(id)>=all(select count(id)from worksgroup by company_name
)

找出工资总额最小的公司

select company_name
from works
group by company_name
having (sum(salary)<all(select sum(salary)from worksgroup by company_name)
)

分类十一：带条件更新

存在一类更新是带有条件的，不同条件下的元组更新的方式也不同

更新无非增加/减少

为了防止非法更新两次：需要满足增加时先加大的，减少时先减少的

给 "第一银行公司 "的每位经理加薪 10%，除非其工资超过 100000 美元。在这种情况下，只加薪 3%（这两个update的顺序不可以更换）（增加时先加大的，减少时先减少的；防止有的记录被操作两次）

update works as T
set T.salary=t.salary*1.03
where works.id in (select idfrom manages
)
and salary>100000
and company_name='First Bank Corporation'update works as T
set T.salary=t.salary*1.1
where works.id in (select idfrom manages
)
and salary<100000
and company_name='First Bank Corporation'

分类十二： 同表格不同角色匹配问题

匹配问题=比较是否相等=比较问题

比较只能发生在两个关系模式之间

1、此时两个角色进行比较，两个角色对应的表格是同一个

2、因此拿相同关系模式当作两个不同的关系模式做笛卡尔积，再比较结果

3、笛卡尔后判断相应的属性是否符合要求

查找与其经理住在同一城市同一条街上的每位员工的 ID 和姓名

select e.id,e.person_name
from employee e natural join managers m,employee e2
where m.manager_id=e2.id and e.city=e2.city and e.street=e2.street

简化写法：

SELECT e.id, e.person_name
FROM employee e
JOIN managers m ON e.id = m.id
JOIN employee e2 ON m.manager_id = e2.id
WHERE e.city = e2.city AND e.street = e2.street;

 总结 

本文的所有知识点、图片均来自《数据库系统概念》（黑宝书）、山东大学李晖老师PPT。不可用于商业用途转发。