数据库各项操作（二） ----查询

数据库各项操作（二） ----查询

目录：

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的嵌套查询

--带有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
    )
)

补充内容

3. 使用换码字符将通配符转义为普通字符

  [例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 '＼' 表示“ ＼” 为换码字符