SQL查询上机题库

 查询（select）

select的使用方法最为复杂，借用一下我们数据库老师的上机实习来说明select如何使用，感谢jjj老师。

 

实习：给出学生-课程数据库的三个表

    ①学生表. Student(Sno,Sname,Ssex,Sage,Sdept) Sno为主键;

       说明：Sno 学号，Sname姓名，Ssex 性别，Sage 年龄，Sdept所在系

    ②课程表. Course(Cno,Cname,Cpno,Credeit) Cno为主键;

       说明：Cno课程号，Cname 课程名，Cpno 先修课号，Credit学分

    ③学生选修表. SC(Sno,Cno,Grade) Sno,Cno,为主键;

       说明：Sno 学号，Cno课程号，Grade成绩

为方便起见，可执行下面的脚本来搭建上述的场景。

create database mydb
go
use mydb
go
create table student
(sno int primary key,
 sname varchar(20),
 ssex nvarchar(1),
 sage tinyint,
 sdept varchar(40)
)
insert into student
values(95001,'李敏勇','男',20,'CS')
insert into student
values(95002,'刘晨','女',19,'IS')
insert into student
values(95003,'王敏','女',18,'MA')
insert into student
values(95004,'张立','男',18,'IS')
insert into student
values(95005,’王敏’,’男’,’IS’)
create table course
(cno int primary key,
 cname varchar(20),
 cpno int,
 credit int 
)
insert into course
values(1,'数据库',5,4)
insert into course
values(2,'数学',null,2)
insert into course
values(3,'信息系统',1,4)
insert into course
values(4,'操作系统',6,3)
insert into course
values(5,'数据结构',7,4)
insert into course
values(6,'数据处理',null,2)
insert into course
values(7,'Pascal语言',6,4)
create table sc
(sno int not null,
 cno int not null,
 grade decimal(10,2),
 testtime date,
 primary key (sno,cno)
)
insert into sc
values (95001,1,92,’2017-6-20’)
insert into sc
values (95001,2,88, ’2017-6-23’)
insert into sc
values (95001,3,88, ’2017-6-26’)
insert into sc
values (95002,2,90, ’2017-6-23’)
insert into sc
values (95002,3,80, ’2017-6-26’)
insert into sc
values (95005,1,99, ’2017-6-20’)
insert into sc
values (95005,2,95, ’2017-6-23’)
insert into sc
values (95005,3,98, ’2017-6-26’)

1. 简单的条件查询

①标准的条件查询

--1. 查询计算机系全体学生的姓名及年龄
select sname,sage--查询什么字段，查询所有字段用select *
from student--从哪张表
where sdept='CS'--条件是什么

②查询条件可以是>,<或者=

--2. 查询所有年龄在20岁以下的学生姓名及其年龄
select sname,sage
from student
where sage<20

③用between、and筛选，还有not between and方法类似

--3. 查询年龄在20到23间（含20和23岁）的学生的姓名,系别及年龄
select sname,sdept,sage
from student
where sage between 20 and 23

④查找两个条件的并集用and连接

--4. 查询不是信息系(IS)和计算机系(CS)学生的姓名和性别
select sname,ssex
from student
where sdept!='IS' and sdept!='CS' 

⑤判断是否为空，要用is null或者is not null，不要用=

--5.查询选修了课程的学生学号
select distinct sno
from sc
where cno is not null

⑥用in查询属性值是否属于一组值之一 ，相对还有not in

--6.查询名字叫李敏勇或者刘晨同学的学号
select sno
from student
where sname in ('李敏勇','刘晨')

⑦使用like和通配符进行字符串匹配

“%”：后面跟0个或者多个字符或数字。

“_”：后面跟任意单个字符或数字。需要注意到LIKE'C\_%'，可以查找到C_加上任意字符串。'\_'表示的是_,而'_'是任意单个字符串。

“[ ]”：在范围内的任意单个字符或数字。

“[^]”：不在范围内的任意单个字符或数字。

--7.1.查询所有姓刘的学生的姓名,学号和性别
select sname,sno,ssex
from student
where sname like '刘%'

--7.2.查找姓王或者姓刘的同学的学号和姓名
select sno,sname
from student
where sname like '[王刘]%'

--7.3.查找年龄在16到19之间的学生学号和姓名,非某个区间用'1[^6-9]'类似
select sno,sname
from student
where sage like '1[6-9]'

⑧order by的升序(asc)和降序(desc)查询，不写desc默认为升序

--8.查询全体学生情况，结果按所在系的号升序排列，同一系中的学生按年龄降序
select *
from student
order by sdept,sage desc

2. 带统计函数的查询

①count计数。count(*)计算所有数据的行数，而在count(sname)中，如果sname为null，则不计入。

--1. 查询学生的总人数
select count(*) as '总人数' from student 

②在计数之前，用distinct去重复

--2. 查询选修了课程的学生人数
select count(distinct sno) as '选课人数'
from sc
where cno is not null

③使用convert函数可以设置输出效果

--3. 计算选2号课程的学生平均成绩
select convert(decimal(4,2),avg(grade)) as '平均分'
from sc
where cno='2'

④max函数找最大，相对min函数找最小

--4. 查询选修3号课程的学生最高分数
select max(grade) as '最高分'
from sc
where cno='3'

⑤sum求和函数

--查询学号为95001学生的总成绩
select SUM(grade) as '总分'
from sc
where sno='95001'

⑥可以多个统计函数一起使用

--5. 查询选修了课程的同学的选课数目、最高分
select sno as '学号', count(cno) as '选课数目', max(grade) as '最高分'
from sc
group by sno--group by是分组查询

3. 分组查询

①group by分组，一般select那一行，没有统计函数的字段，都要加到group by

--1.查询各学分等级的课程数目
select credit as'学分', count(distinct cno) as '该学分对应课程数目'
from course
group by credit

②函数不能相互嵌套，max(count(sno))这样写会报错，利用order by desc和top 1操作替代了max函数

--2.查询人数最多的系及人数
select top 1 sdept as '系名' , count(sno) as '总人数'
from  student
group by sdept
order by  '总人数'desc

4. 连接查询

连接查询基本可分为四种：

（1）交叉查询。表先做笛卡尔积，再根据条件筛选。因为要做笛卡尔积，数据根据表呈指数爆炸增长，再来筛选费时间，因此交叉查询查找效率低，实际运用比较少

--查询李敏勇所有课程的成绩，显示姓名、课程名和成绩
select student.sname, course.cname, sc.grade
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and sname='李敏勇'

（2）内部连接（join 或者inner join）

--查询选修“信息系统”课程的学生姓名
select student.sname as '学生姓名'
from student join sc on sc.sno=student.sno join course on course.cno=sc.cno  
where cname='信息系统'

（3）外部连接，分为左外链接（left join）、右外链接（right join）和全外连接（full join）

①left join，返回左侧表中不匹配的元组，右侧表中以null替代

--1. 查询所有学生的成绩情况，显示的列名包括学生姓名、课程号、考试时间、成绩，注意结果集中也应包括未选修课程的学生。
select sname, sc.sno, testtime, grade
from student left join sc on sc.sno=student.sno

②right join，返回右侧表中不匹配的元组，左侧表中以null替代，和left join相对

--2. 查询所有课程的选课情况，显示课程名、选课人数，如果没有人选，选课人数显示为0
select cname, count(sno) as '选课人数'
from sc right join course on sc.cno=course.cno
group by cname

个人理解：外链接之后会出现一些null，那么只要把多的表连接少的表就会出现null。例如①中，学生表是完整的，学生表就是多的一方；而选课表中有一部分学生未选课，选课表就是少的一方。所以写成，学生表left join选课表，或者选课表right join学生表。这样就能区分左右连接。full join就是左右连接的合并，不再给出脚本举例。

（4）自身连接（self join）

--定义不同名字的表，完成自身连接
select c1.cname '课程名',c2.cname '先修课程'
from course c1,course c2
where c1.cpno=c2.cno

5. 嵌套查询

查询难度开始加大，在练习嵌套查询之前，我们重新搭建数据库。

给出学生-课程数据库的三个表：

（1）学生表. Student(Sno,Sname,Ssex,Sage,Sdept) Sno为主键;

说明：Sno 学号，Sname姓名，Ssex 性别，Sage 年龄，Sdept所在系

（2）课程表. Course(Cno,Cname,Cpno,Credeit) Cno为主键;

说明：Cno课程号，Cname 课程名，Cpno 先修课号，Credit学分

（3）学生选修表. SC(Sno,Cno,Grade,Testtime) Sno,Cno,为主键;

说明：Sno 学号，Cno课程号，Grade成绩，Testtime 考试时间

由于数据与上面的数据有所不同，请执行下面的脚本来重新搭建场景。

create database SampleDB
go
use SampleDB
go
create table student
(sno int primary key,
 sname varchar(20),
 ssex nvarchar(1),
 sage tinyint,
 sdept varchar(40)
)
insert into student
values(95001,'李敏勇','男',20,'CS')
insert into student
values(95002,'刘晨','女',19,'IS')
insert into student
values(95003,'王敏','女',18,'MA')
insert into student
values(95004,'张立','男',18,'IS')
insert into student
values(95005,'王敏','男',19,'CS')
insert into student
values(95006,'丁一','女',20,'CS')
insert into student
values(95007,'李响','男',19,'CS')
insert into student 
values(95008,'许磊','男',20,'MA')
go
create table course
(cno int primary key,
 cname varchar(20),
 cpno int,
 credit int 
)
insert into course
values(1,'数据库',5,4)
insert into course
values(2,'数学',null,2)
insert into course
values(3,'信息系统',1,4)
insert into course
values(4,'操作系统',6,3)
insert into course
values(5,'数据结构',7,4)
insert into course
values(6,'数据处理',null,2)
insert into course
values(7,'Pascal语言',6,4)
create table sc
(sno int not null,
 cno int not null,
 grade decimal(10,2),
 testtime date,
 primary key (sno,cno)
)
insert into sc
values (95001,1,92,'2017-6-20')
insert into sc
values (95001,2,88,'2017-6-23')
insert into sc
values (95001,3,88,'2017-6-26')
insert into sc
values (95002,2,90,'2017-6-23')
insert into sc
values (95002,3,80,'2017-6-26')
insert into sc
values (95003,1,92,'2017-6-20')
insert into sc
values (95003,3,85,'2017-6-26')
insert into sc
values (95005,1,99,'2017-6-20')
insert into sc
values (95005,2,95,'2017-6-23')
insert into sc
values (95005,3,98,'2017-6-26')
insert into sc
values (95005,5,56,'2017-6-29')
insert into sc
values (95006,1,89,'2017-6-20')
insert into sc
values (95006,2,75,'2017-6-23')
insert into sc
values (95006,4,92,'2017-6-30')
insert into sc
values (95008,2,78,'2017-6-23')
insert into sc
values (95008,3,88,'2017-6-26')
insert into sc
values (95008,4,46,'2017-6-30')
go

（1）使用IN嵌套查询

①用子查询，查询出一组sno

--1. 查询至少参加了三次考试的学生姓名（用子查询）
select sname as '姓名'
from student
where sno in(select sno from sc group by sno having count(grade)>2)

②子查询，查询结果作为一张表

--2. 查询平均分大于85的学生姓名及选课数目（用子查询）
select sname,t.数量
from student,
(
select sno,count(cno) as '数量'
from sc
group by sno
having avg(grade)>85
) as t
where student.sno=t.sno

③多级子查询嵌套

--4. 查询和李敏勇同一个系的学生人数（含李敏勇本人）
select count(sno) as 'number'
from student
where sno in
(
select sno
from student
where sdept=(select sdept from student where sname='李敏勇')--当查询结果为一项的时候，可以用=
)

（2）用比较运算符（>、<）的嵌套查询

--查询总成绩比李敏勇高的同学的姓名
select sname 
from student
where sno in
(
	select sno
	from sc
	group by sno
	having sum(grade)>
	(
		select sum(grade) '总成绩'
		from sc
		where sno = 
		(
			select sno
			from student
			where sname = '李敏勇'
		)
	)
)

（3）使用BETWEEN的嵌套查询

--查找在数据库这门课中，成绩介于李敏勇和100之间的同学的姓名
select sname
from student
where sno in
(
	select sno 
	from sc
	where grade between
	(
		select grade 
		from sc
		where sno = 
		(
			select sno 
			from student
			where sname = '李敏勇'
		) and cno=
		(
			select cno
			from course
			where cname = '数据库'
		)
	) and 100
	and cno=
		(
			select cno
			from course
			where cname = '数据库'
		)
)

（4）相关子查询，这类子查询的查询条件往往依赖于其父查询的某属性值

--找出没有选修数据库这门课同学的姓名
select sname 
from student s
where not exists
(
	select * from sc
	where s.sno=sc.sno
);

（5）INSERT与子查询的结合

把子查询的结果插入指定的表名中，这样的一条INSERT语句，可以一次插入多条元组

--把李敏勇的所有信息，放入Monitor表中，insert into 中Monitor表需要事先定义
insert into Monitor
select * from student
where sname = '李敏勇'

对比

--select into 表不需要事先定义
select * into Monitor
from student
where sname = '李敏勇'

（6）UPDATE与子查询的结合

--将“刘晨”同学的“数学课”课程成绩下调5分。
update sc
set grade=grade-5
where sno=(select sno from student where sname='刘晨') and cno=(select cno from course where cname='数学')

（7）DELETE与子查询的结合

--删掉所有李敏勇的成绩
delete from sc
where sno = 
(
	select sno from student
	where sname = '李敏勇'	
)