SQL Server-数据查询

查询

数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。

 

SELECT语句的一般格式

?

1 2 3 4 5

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在查询条件中的用法

语法格式

?

1	[not] like '<匹配串>' [escape '<换码字符>']

　　

匹配符

% ： 代表任意长度的字符串

_ ： 代表任意单个字符

 

单表查询

1.指定列或全部列查询

(1)查询S表中全体学生的详细记录。

?

1	select * from S

　　

(2)查询所有学生的姓名及其出生年份。

?

1	select SN 姓名, year(getdate())-AGE 出生年份 from S

　　

2.按条件查询及模糊查询

(1)查询考试成绩有不及格的学生的学号。

?

1 2	select distinct SNO from SC where SCORE < 60

　　

 (2)查询年龄在20～23岁之间的学生的姓名、系名、年龄。

?

1 2	select SN, DEPT, AGE from S where AGE between 20 and 23

　　

(3)查询姓李的学生的姓名、学号和性别。

?

1 2	select SN, SNO, SEX from S where SN like '李%'

　　

(4)查询名字中第二个字为“明”字的男学生的姓名和系名。

?

1 2	select SN 姓名, DEPT 系名 from S where SN like '_明%' and SEX = '男'

　　

3.对查询结果排序

(1)查询信息系、计算机系学生的姓名、系名，结果按系名升序，按姓名降序排序。

?

1 2 3

select SN 姓名, DEPT 系名 from S where DEPT in ('信息','计算机') order by DEPT asc, SN desc

　　

(2)查询所有有课程号C2成绩的学生的学号、课程号和成绩，并按成绩降序排序。

?

1 2 3

select SNO 学号, CNO 课程号, SCORE 成绩 from SC where CNO = 'C2' and SCORE is not null order by SCORE desc

　　

4.使用聚集函数的查询

(1)查询计算机系学生总人数。

?

1 2	select count(*) 计算机系人数 from S where DEPT = '计算机'

　　

(2)查询选修了微机原理课程的学生人数、平均成绩、最高成绩。

?

1 2	select count(*) 人数, avg(SCORE) 平均分, max(SCORE) 最高分 from C, SC where CN = '微机原理' and C.CNO = SC.CNO

　　

(3)查询成绩比该课程平均成绩高的学生的成绩表。

?

1 2 3

select SNO, CNO, SCORE from SC X where SCORE > (select avg(Y.SCORE) from SC Y                where Y.CNO = X.CNO)

　　

5.分组统计查询

(1)查询各个课程号及相应的选课人数。

?

1 2	select CNO 课程号, count (SNO) 人数 from SC group by CNO

　　

(2)查询选修了两门以上课程的学生姓名和平均成绩。

?

1 2 3

select SN, avg(SCORE) from S, SC where S.SNO = SC.SNO group by SN having count (*) > 2

　　

(3)查询选修了两门以上课程的学生名单。

?

1 2 3 4

select * from S where SNO in (select SNO from SC               group by SNO               having count (CNO) > 2)

　　

(4)查询至少有4个同学选修的课程名。

?

1 2 3

select CN from C, SC where C.Cno = SC.Cno group by CN having count(Sno) >= 4

　

(5)查询每个学生各门课程的平均成绩和最高成绩，按降序排列输出姓名、平均成绩和最高成绩。

?

1 2 3 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)查询所有选课学生的学号、姓名、选课名称及成绩。

?

1 2 3

select S.SNO, SN, CN, SCORE from SC, S, C where S.SNO = SC.SNO and C.CNO = SC.CNO

　　

(2)查询每门课程的课程号、任课教师姓名及其选课人数。

?

1 2 3 4

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)查询所有比“刘伟”工资高的教师的姓名、工资以及刘伟的工资。

?

1 2 3

select X.TN 姓名, X.SAL 工资, Y.SAL 刘伟工资 from T X, T Y where X.SAL > Y.SAL and Y.TN = '刘伟'

　　

(2)查询同时选修了“程序设计”和“微机原理”的学生的姓名、课程名。

?

1 2 3 4 5

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的课程的学生的姓名、此两门课程的课程名和成绩。

?

1 2 3 4

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)查询所有学生的学号、姓名、选课名称及成绩(没有选课的学生的选课信息显示为空)。

?

1 2 3

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)查询与“刘伟”教师职称相同的教师号，姓名和职称。

?

1 2	select TNO, TN, PROF from T where PROF = (select PROF from T where TN = '刘伟')

　　

2.返回一组值的子查询

(1)使用ANY谓词查询讲授课程号为C5的教师姓名。

?

1 2	select TN from T where TNO = any (select TNO from TC where CNO = 'C5')

　　

(2)使用IN谓词查询讲授课程号为C5的教师姓名。

?

1 2	select TN from T where TNO in (select TNO from TC where CNO = 'C5')

　　

(3)使用IN谓词查询至少有一门课程与“张建国”选课相同的学生的姓名、课程名和系别。

?

1 2 3 4

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号课程的学生名单。

?

1 2	select * from S where SNO not in (select SNO from SC where CNO = '01001')

　　

(5)使用ALL谓词查询其他系中比计算机系所有教师工资都高的教师的姓名、工资和所在系。

?

1 2	select TN, SAL, DEPT from T where SAL > all (select SAL from T where DEPT = '计算机')

　　

(6)使用ALL谓词查询其他系中比“信息系"所有学生年龄都大的学生名单及年龄﹐并按年龄降序输出。

?

1 2 3 4

select SNO, SN, AGE from S where AGE > all (select AGE from S                  where DEPT = '信息') order by AGE desc

　　

(7)使用EXISTS谓词查询与学生张建国同岁的所有学生的学号、姓名和系别。

?

1 2 3

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的课程的教师姓名，所在系。

?

1 2 3

select TN, DEPT from T where not exists (select * from TC                   where TC.TNO = T.TNO and CNO = 'C5')

　　

(9)使用NOT EXISTS谓词查询至少选修了学生02选修的全部课程的学生的学号。

?

1 2 3 4 5

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谓词查询所有学生都选修了的课程的课程号和课程名。

?

1 2 3 4

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号学生选修了的课程的学生的学号和姓名。

?

1 2 3 4

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岁或者属于计算机系的学生。

?

1 2 3

select * from S where DEPT = '计算机' union select * from S where AGE <= 19