数据库各项操作（二） ----查询
目录：
1： 单表查询
2： 连接查询
3： 嵌套查询
4： 集合查询
1：单表查询
格式：select + 需要查询的列 + from + 表名 + where + group by + order by
直接来题
student： sno
, sname
, ssex
, sage
, sdept
, email
,birth
course : cno
, cname
, cpno
(先修课
), ccredit
sc : sno
, cno
, grade
1．查询有直接先行课的课程的课程号，课程名和先行课号。
select cno
, cname
, cpno
from course
where cpno
is not null
2．查询所有的计算机系姓李的同学的学号和姓名
select sno
, sname
from student
where sname
like '李%' and sdept
= 'CS'
3．查询不在计算机系和信息系学习的学生的学号和姓名，系别，并对查 询结果按照专业的升序和学号的降序 排序
select sno
,sname
,sdept
from student
where sdept
is not in ['CS','IS']
order by sdept
asc, sno
desc
4．查询年龄不在
18-21之间的计算机系的学生的学号，姓名和年龄
select sno
, sname
, sage
from student
where sage
is not between 18 and 21
5．查询每个学生的学号，姓名，出生年份。
select sno
, sname
, birth
from student
6.统计各专业男生、女生人数
表达方式
1：
select sdept
,ssex
, count(sno
) as '人数'
from student
group by sdept
,ssex
表达方式
2：
select sdept
,ssex
, count(ssex
)"男生人数"
from student
group by sdept
,ssex
having ssex
= '男'
select sdept
,ssex
, count(ssex
)"女生人数"
from student
group by sdept
,ssex
having ssex
= '女'
7.查询平均成绩大于等于
85分的学生学号和平均成绩
select sno
, avg(grade
)
from sc
group by sno
having avg(grade
) >= 85
8.查询选修
1号课程成绩排名第一的同学的学号和成绩
select top 1 sno
,grade
from sc
where cno
= '1'
order by grade
desc
9.查询所有名字中含有’明’或者’丽’的同学的学号，姓名
select sno
,sname
from student
where sname
like '%[明]%' or sname
like '%[丽]%'
10.查询所有不姓李和张的同学的学号和姓名
select sno
,sname
from student
where sname
not like '[李张]%'
2：连接查询
（1）等值连接
1:查询每个学生及其选修课程的情况
select student
.*, sc
.*
from student
,sc
where student
.sno
= sc
.sno
(2) 自身连接
2:查询每一门课的间接先修课（即先修课的先修课）
select first.cno
, second.cpno
from course
first, course
second
where first.cpno
= second.cno
(3) 外连接
左外连接/ 右外连接
3:查询每个学生及其选修课程的情况（同上
select student
.sno
, sname
, ssex
,sage
,sdept
,cno
, grade
from student
left join sc
on
(
student
.sno
= sc
.sno
)
3：嵌套查询
一个 select - from - where 语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句
或 HAVING短语的条件中的查询称为嵌套查询
1:查询与“张丰毅”在同一个系学习的学生
select sno
,sname
,sdept
from student x
where sdept
like
(
select sdept
from student y
where y
.sname
= '张丰毅 '
)
2:查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
select sno
,sname
,sage
from student x
where sage
< any
(
select sage
from student
where sdept
like 'CS'
)
and sdept
not like 'CS'
带有exists的嵌套查询
3：查询没有选修
1号课程的学生姓名
select sname
from student
where not exists
(
select *
from sc
where student
.sno
= sc
.sno
and sc
.cno
= '1'
)
4:查询至少选修了学生
200215122选修的全部课程的学生姓名及所在系
等价于：
不存在有一门课
200215122选了但是该学生没选
select sname
, sdept
from student y
where not exists
(
select cno
from sc
where sc
.sno
= '200215122'
and not exists
(
select cno
from sc
where sno
= y
.sno
and cno
= x
.cno
)
)
5：查询选修了全部课程的学生姓名
等价于：不存在有门课该学生没选:在课程里面寻找
select sname
from student
where not exists
(
select cno
from course
where not exists
(
select *
from sc
where student
.sno
= sc
.sno
and course
.cno
= sc
.cno
)
)
6：查询与学生
200515019有相同选修课程的学生姓名
,学号
即：存在一门课
200515018选了 ，该同学也选了
select sno
,sname
from student
where exists
(
select *
from sc x
where exists
(
select *
from sc y
where y
.sno
= '200515018' and student
.sno
= x
.sno
and x
.cno
= y
.cno
)
)
7:查询与学生
200515018选修课程不重复的学生姓名
即：不存在一门课
200515018选了 ，该同学也选
select sno
,sname
from student
where not exists
(
select *
from sc x
where exists
(
select *
from sc y
where y
.sno
= '200515018' and student
.sno
= x
.sno
and x
.cno
= y
.cno
)
)
补充内容
使用换码字符将通配符转义为普通字符
[例
19] 查询DB_Design课程的课程号和学分。
SELECT Cno，Ccredit
FROM Course
WHERE Cname
LIKE 'DB\_Design' ESCAPE '\‘；
[例20] 查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_
%i_ _
' ESCAPE ' \ ‘；
ESCAPE '＼' 表示“ ＼” 为换码字符
