SQL Server-数据查询
查询
数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。
SELECT语句的一般格式
select [all | distinct] [top n [percent]] <目标列表达式> [别名] [, <目标列表达式> [别名]...] from <表名或视图名> [别名] [, <表名或视图名> [别名]...] [where <条件表达式>] [group by <列名1> [having <条件表达式>] [, <列名2> [having <条件表达式>]...]] [order by <列名1> [asc | desc] [, <列名2> [asc | desc]...]]
常用的聚合函数
| 聚合函数 | 描述 |
| avg(expr) | 列值的平均值。该列只能包含数字数据 |
| count(expr)，count( * ) |
列值的计数(如果将列名指定为expr)或是表或组中所有行的计数(如果指定*)。 count(expr)忽略NULL值，但count( * )在计数中包含NULL值 |
| max( expr) | 列中最大的值(文本数据类型中按字母顺序排在最后的值)。忽略NULL值 |
| min(expr) | 列中最小的值(文本数据类型中按字母顺序排在最前的值)。忽略NULL值 |
| sum(expr) | 列值的合计。该列只能包含数字数据，忽略NULL值 |
查询条件列表
| 查询条件 | 谓词 |
| 比较 | =, >, <, >=, <=, !>, !<, <>, != |
| 确定范围 | between...and..., not between...and... |
| 确定集合 | in, not in |
| 字符匹配 | like, not like |
| 空值 | is null, is not null |
| 多重条件 | and, or |
谓词LIKE在查询条件中的用法
语法格式
[not] like '<匹配串>' [escape '<换码字符>']
匹配符
% ： 代表任意长度的字符串
_ ： 代表任意单个字符
单表查询
1.指定列或全部列查询
(1)查询S表中全体学生的详细记录。
select * from S
(2)查询所有学生的姓名及其出生年份。
select SN 姓名, year(getdate())-AGE 出生年份 from S
2.按条件查询及模糊查询
(1)查询考试成绩有不及格的学生的学号。
select distinct SNO from SC where SCORE < 60
(2)查询年龄在20～23岁之间的学生的姓名、系名、年龄。
select SN, DEPT, AGE from S where AGE between 20 and 23
(3)查询姓李的学生的姓名、学号和性别。
select SN, SNO, SEX from S where SN like '李%'
(4)查询名字中第二个字为“明”字的男学生的姓名和系名。
select SN 姓名, DEPT 系名 from S where SN like '_明%' and SEX = '男'
3.对查询结果排序
(1)查询信息系、计算机系学生的姓名、系名，结果按系名升序，按姓名降序排序。
select SN 姓名, DEPT 系名 from S
where DEPT in ('信息','计算机')
order by DEPT asc, SN desc
(2)查询所有有课程号C2成绩的学生的学号、课程号和成绩，并按成绩降序排序。
select SNO 学号, CNO 课程号, SCORE 成绩 from SC where CNO = 'C2' and SCORE is not null order by SCORE desc
4.使用聚集函数的查询
(1)查询计算机系学生总人数。
select count(*) 计算机系人数 from S where DEPT = '计算机'
(2)查询选修了微机原理课程的学生人数、平均成绩、最高成绩。
select count(*) 人数, avg(SCORE) 平均分, max(SCORE) 最高分 from C, SC where CN = '微机原理' and C.CNO = SC.CNO
(3)查询成绩比该课程平均成绩高的学生的成绩表。
select SNO, CNO, SCORE from SC X
where SCORE > (select avg(Y.SCORE) from SC Y
where Y.CNO = X.CNO)
5.分组统计查询
(1)查询各个课程号及相应的选课人数。
select CNO 课程号, count (SNO) 人数 from SC group by CNO
(2)查询选修了两门以上课程的学生姓名和平均成绩。
select SN, avg(SCORE) from S, SC where S.SNO = SC.SNO group by SN having count (*) > 2
(3)查询选修了两门以上课程的学生名单。
select * from S
where SNO in (select SNO from SC
group by SNO
having count (CNO) > 2)
(4)查询至少有4个同学选修的课程名。
select CN from C, SC where C.Cno = SC.Cno group by CN having count(Sno) >= 4
(5)查询每个学生各门课程的平均成绩和最高成绩，按降序排列输出姓名、平均成绩和最高成绩。
select SN, avg(SCORE) 平均分, max(SCORE) 最高分 from S, SC where S.SNO = SC.SNO group by SN order by SN desc
连接查询
1.连接查询
(1)查询所有选课学生的学号、姓名、选课名称及成绩。
select S.SNO, SN, CN, SCORE from SC, S, C where S.SNO = SC.SNO and C.CNO = SC.CNO
(2)查询每门课程的课程号、任课教师姓名及其选课人数。
select C.CNO, TN, count(SC.SNO) 选课人数 from T, TC, C, SC where T.TNO = TC.TNO and C.CNO = TC.CNO and C.CNO = SC.CNO group by C.CNO, TN
2.自身连接
(1)查询所有比“刘伟”工资高的教师的姓名、工资以及刘伟的工资。
select X.TN 姓名, X.SAL 工资, Y.SAL 刘伟工资 from T X, T Y where X.SAL > Y.SAL and Y.TN = '刘伟'
(2)查询同时选修了“程序设计”和“微机原理”的学生的姓名、课程名。
select SN, C1.CN, C2.CN
from C C1, C C2, SC SC1, SC SC2, S
where C1.CNO = SC1.CNO and C2.CNO = SC2.CNO and
SC1.SNO = S.SNO and SC2.SNO = S.SNO and
C1.CN = '程序设计' and C2.CN = '微机原理'
(3)查询选修了课号为01001的课程且成绩高于课程号为01002的课程的学生的姓名、此两门课程的课程名和成绩。
select SN, C1.CN, A.SCORE, C2.CN, B.SCORE
from SC A, SC B, S, C C1, C C2
where S.SNO = A.SNO and S.SNO = B.SNO and A.CNO = C1.CNO and B.CNO = C2.CNO and
A.CNO = '01001' and B.CNO = '01002' and A.SCORE > B.SCORE
3.外连接
(1)查询所有学生的学号、姓名、选课名称及成绩(没有选课的学生的选课信息显示为空)。
select S.SNO, SN, CN, SCORE from S left outer join SC on S.SNO = SC.SNO left outer join C on SC.CNO = C.CNO
嵌套查询
1.返回一个值的子查询
(1)查询与“刘伟”教师职称相同的教师号，姓名和职称。
select TNO, TN, PROF from T where PROF = (select PROF from T where TN = '刘伟')
2.返回一组值的子查询
(1)使用ANY谓词查询讲授课程号为C5的教师姓名。
select TN from T where TNO = any (select TNO from TC where CNO = 'C5')
(2)使用IN谓词查询讲授课程号为C5的教师姓名。
select TN from T where TNO in (select TNO from TC where CNO = 'C5')
(3)使用IN谓词查询至少有一门课程与“张建国”选课相同的学生的姓名、课程名和系别。
select SN, CN, DEPT from C, S, SC
where C.CNO = SC.CNO and S.SNO = SC.SNO and
C.CNO in (select CNO from SC, S
where SC.SNO = S.SNO and SN = '张建国')
(4)使用NOT IN谓词查询查询所有未修01001号课程的学生名单。
select * from S where SNO not in (select SNO from SC where CNO = '01001')
(5)使用ALL谓词查询其他系中比计算机系所有教师工资都高的教师的姓名、工资和所在系。
select TN, SAL, DEPT from T where SAL > all (select SAL from T where DEPT = '计算机')
(6)使用ALL谓词查询其他系中比“信息系"所有学生年龄都大的学生名单及年龄﹐并按年龄降序输出。
select SNO, SN, AGE from S
where AGE > all (select AGE from S
where DEPT = '信息')
order by AGE desc
(7)使用EXISTS谓词查询与学生张建国同岁的所有学生的学号、姓名和系别。
select SNO, SN, DEPT from S X
where exists (select * from S Y
where Y.SN = '张建国' and X.AGE = Y.AGE and X.SN <> '张建国')
(8)使用NOT EXISTS谓词查询没有讲授课程号为C5的课程的教师姓名，所在系。
select TN, DEPT from T
where not exists (select * from TC
where TC.TNO = T.TNO and CNO = 'C5')
(9)使用NOT EXISTS谓词查询至少选修了学生02选修的全部课程的学生的学号。
select distinct SNO from SC SC1
where not exists (select * from SC SC2
where SC2.SNO = '02' and
not exists (select * from SC SC3
where SC3.SNO = SC1.SNO and SC3.SNO = SC2.SNO))
（SQL Server-包含not exists 谓词的嵌套相关子查询）
(10)使用NOT EXISTS谓词查询所有学生都选修了的课程的课程号和课程名。
select CNO, CN from C
where not exists (select * from S
where not exists (select * from SC
where SC.SNO = S.SNO and C.CNO = SC.CNO))
(11)使用NOT EXISTS谓词查询选修了991102号学生选修了的课程的学生的学号和姓名。
select SN, SNO from S
where not exists (select * from SC SC1
where SNO = '991102' and not exists (select * from SC SC2
where SC2.SNO = S.SNO and SC1.CNO = SC2.CNO))
集合查询
1.查询年龄不大于19岁或者属于计算机系的学生。
select * from S where DEPT = '计算机' union select * from S where AGE <= 19
