SQL——student表数据

简单查询

• 选择表中若干元组
• 选择表中若干列
• order by 子句
• group by子句
• 聚集函数

选择表中若干列

查询全体学生的学号与姓名

select sno,sname
from student

选择表中若干行

消除取值重复的行
DISTINCT
查询选修了课程的学生学号

select distinct sno
from sc

查询成绩有不及格的学生的学号

select distinct sno
from sc
where grade<60

查询年龄不在20-23岁之间的学生姓名、系别和年龄

select sname,sdept,sage
from student
where sage not between 20 and 23

查询信息科学系、数学系、和计算机科学系学生的姓名和性别

select sname,ssex
from student
where sdept in('is','ma','cs')

查询既不是信息科学系、数学系、和计算机科学系学生的姓名和性别

select sname,ssex
from student
where sdept not in ('is','ma','cs')

查询表中姓张、姓李和姓刘的学生的情况

select *
from student
where sname like '[张李刘]%'

查询所有不姓刘的学生姓名、学号和性别

select sname,sno,ssex
from student
where sname not like '刘%'

查询学号的最后一位不是2.3.5的学生情况

select * 
from student 
where sno like '%[^235]'

select *
from student
where sno not like '%[235]'

ORDEY BY子句

ASC:升序 降序：DESC

查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列

select sno,grade
from sc
where cno='3'
order by grade desc

查询全体学生情况，查询结果按所在系的系名升序排列，同一系中的学生按年龄降序排列

select *
from student
order by sdept,sage desc

在使用select语句中，有时只希望列出结果集中的前几行结果，可用top谓词来限制输出结果
查询年龄最大的三个学生的姓名，年龄，及所在系

select top 3 sname,sage,sdept
from student 
order by sage desc

select top 2 with ties *
from student
order by sage

聚集函数

• COUNT(*):统计表中元组个数
• COUNT(列名):统计本列列值个数
• SUM:计算列值综合
• AVG
• MAX
• MIN

上述函数中除了COUNT(*)外，其他函数在计算过程中均忽略NULL值

统计学生总人数

select count(*) 总人数 from student

统计选修了课程的学生的人数

select count(sno) 选课人数
from sc

计算9512101号学生的考试总成绩之和

select sum(grade) 总成绩
from sc
where sno = '9512101'

计算‘c01’号课程学生的考试平均成绩

select avg(grade) 平均成绩
from sc
where cno='c01'

查询选修了‘c01’号课程的学生的最高分和最低分

select max(grade) 最高分,min(grade) 最低分
from sc
where cno='c01'

聚合函数不能出现在where子句中
查询年龄最大的学生的姓名

select sname
from student 
where sage=max(sage)

这种写法是错的！！！！

GROUP BY子句

• 可将计算控制在组一级，分组的目的是细化计算函数的作用对象
• 分组语句位置在where子句的后边

统计每门课程的选课人数，列出课程号和人数

按照每一个课程号，将选课信息分类
统计每一个课程的学号数

select cno 课程号,count(sno) 选课人数
from sc
group by cno

查询每名学生的选课门数和平均成绩

select sno 学号,count(cno) 选课门数, avg(grade) 平均成绩
from sc
group by sno

使用HAVING

• HAVING用于对分组进行赛选，他有点像where子句，但它用于组而不是单个记录
• 在HAVING子句中可以使用计算函数，但在WHERE子句中则不能
• HAVING通常与GROUP BY子句一起使用

查询选修了3门以上课程的学生的学号

SELECT SNO FROM SC
GROUP BY Sno
HAVING COUNT(Cno)>=3

查询修课门数大于或等于4门的学生的平均成绩和选课门数

select sno 学号, avg(grade) 平均成绩,count(cno) 选课门数
from sc
group by sno
having count(cno)>=4

select sno 学号,sum(grade) 总成绩
from sc
group by sno
having sum(grade)>200

查询计算机系和信息科学系的学生人数

select sdept,count(*)学生人数
from student
group by sdept
having sdept in('CS','IS')

select后面的列名必须包含在聚合函数或者group by子句中，having 后面的列名必须包含在聚合函数 group by子句中

select sdept,count(*)学生人数
from student
where sdept in('cs','is')
group by sdept

多表连接查询

• 首先选取表1中的第一个元组，然后从头开始扫描表2，注意查找满足连接条件的元组。
• 找到后就将表2中第一个元组与该院组拼接起来，形成结果表中的一个元组
• 表2全部查询完毕后，再取表1中的第2个元组，然后再从头开始扫描表2.并逐一连接满足条件的元组将其加入结果
• 重复这个过程，直到表1中的全部元组都处理完毕为止

查询每个学生基本信息及其修课的情况

select * 
from student inner join sc
on student.sno=sc.sno

select *
from student,sc
where student.sno=sc.sno

两个表中的连接结果包含了两个表的全部列，sno列重复了两次，这是不必须要的因此，在写查询语句中应当将这些重复的列去掉

select student.sno,sname,ssex,sage,sdept,cno,grade,xklb
from student,sc
where student.sno=sc.sno

查询信息系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩

select sname,cno,grade
from student,sc
where student.sno=sc.sno
where sdept='信息系'

查询‘信息系’修了VB课程的学生，列出学生姓名，课程名和成绩

select sname 姓名,cname 课程名,grade 成绩
from student ,sc,course
where student.sno=sc.sno and course.cno=sc.cno
where sdept='信息系' and  cname='VB'

外连接

• 只限制一张表中的数据必须满足连接条件，而另一张表数据可以不满足条件
• 可以输出不满足连接条件的元组的信息

查询学生的修课情况，包括修了课程的学生和没有修课的学生

select student.sno,sname,cno,grade
from student left outer join sc on student.sno=sc.sno

也可以用右外连接来实现

select student.sno,sname,cno,grade
from sc right outer join student on student.sno=sc.sno

子查询

• 一个select-from-where语句称为一个查询块
• 将一个查询快嵌套在另一个select，insert、update、delete语句中的拆线呢被称为嵌套查询

select sname 
from student
where sno in (select sno from sc where cno='2')

带有IN谓词的子查询

该表达式的值与集合中的某个值相等， 则此测试为true，如果该表达式与集合中所有的值均不相等，则返回false

确定与刘晨在同一个系学习的学生

此查询要求可以分布来完成

1. 确定刘晨所在系名

select sdept
from student
where sname='刘晨'

2. 查找所有在IS系学习的学生

select sno,sname,sdept
from student 
where sdept='IS'

select sno,sname,sdept
from student
where sdept in(select sdept from student where sname='刘晨') and sname!='刘晨'

先执行子查询，然后在子查询的结果上查找所有在此系学习的学生

查询成绩为大于90分的学生的学号、姓名

select sno 学号,sname 姓名
from student
where sno in(
select sno from sc where grade > 90)

用多表连接查询

select student.sno ,sname
from student,sc 
where student.sno=sc.sno
and grade>90

查询选修了数据库基础课程的学生的学号、姓名

select sno 学号,sname 姓名
from student
where sno in
(select sno from sc,course
where sc.cno=course.cno and cname='数据库基础课程')

用多表连接实现

select student.sno,sname
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno
and cname='数据库基础课程')

查询选修了数据库及应用课程的学生选课门数和平均成绩

select student.sno学号,count(*) 选课门数，avg(grade)
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno
and cname='数据库及应用课程'	
group by student.sno																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																															 

select student,sno学号,count(*) 选课门数,avg(grade) 平均成绩
from student,sc
where student.sno=sc.sno
and cno in(select cno from course where cname='数据库及应用')
group by student.sno

带有比较运算符的子查询

假设一个学生只可能在一个系学习，并且必须属于一个系，则在下列可以用=代替in

select sno,sname,sdept
from student
where sdept=(select sdept from student where sname ='刘晨')

查询修了’c02‘课程且成绩高于此课程的平均成绩的学生的学号和成绩

SELECT sno ,grade
from sc
where cno='c02' and grade>(select avg(grade) from sc where cno='c02')

查询其他系中比信息科学系某一学生年龄小的学生姓名和年龄

select sname,sage
from student
where sage<any(select age from student where sdept='is')
and sdept <>'is'

select sname,sage
from student
where sage<
(select max(sage) from student
where sdept='is')
and sdept<>'is'

查询其他系中比信息科学系所有学生年龄都晓得学生姓名及年龄

select sname,sage
from student
where sage<all(select sage from student where sdept='is' ) and sdept<> 'is'

select sname,sage
from student
where sage<(select min(age) from student where sdept='is') and sdept<> 'is'

不相干子查询概念

用子查询进行基于集合的测试或比较测试时，是先执行子查询，然后再在子查询的结果基础之上执行外层查询，子查询只执行一次，子查询的条件不依赖于外层查询，将这样的子查询成为不相关子查询或嵌套子查询

带有EXISTS谓词的子查询

查询所有选修了c01号课程的学生姓名

select sname  
from studennt
where exists (select * 
from sc where sno=student.sno and cno='c01')

非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。
相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。
故非相关子查询比相关子查询效率高。
查询Booka表中大于该类图书价格平均值的图书信息SElECT 图书名,出版社,类编号,价格

SELECT FROM Books As a
  WHERE 价格 >
  (
    SELECT AVG(价格)
    FROM Books AS b
    WHERE a.类编号=b.类编号
  )