SQL Server菜鸟入门

二、数据查询

数据查询格式：

SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]
FROM<表名或视图名>[,<表名或视图名>]|(<SELECT 语句>[AS]<别名>)
[WHERE <条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
--GROUP BY 语句将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。
[ORDER BY<列名2>[ASC|DESC]];
--ORDER BY语句，结果表按<列名2>的值的升序或降序排序

1. 单表查询

1. 选择表中的若干列

1. 查询指定列

--查询部分属性列
SELECT Sno,Sname
FROM Student;
--查询全部列
SELECT*
FROM Student;
--查询经过计算的值
SELECT Sname,2014-Sage
FROM Student

--定义别名
SELECT Sname NAME,'Year of Birth:' BIRTH,2014-Sage BIRTHDAY
FROM Student;

2. 选择表中的若干元组

--消除取值重复的行
SELECT DISTINCT Sno
FROM SC;
--没有指定DISTINCT关键字则默认为ALL

--查询满足条件的元组
关键字WHERE
SELECT Sname
FROM Student
WHERE Sdept='CS'

WHERE子句常用查询条件

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>(不等于),!>,!<;NOT+上述比较运算符
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE
空值	IS NULL ,IS NOT NULL
多重条件（逻辑运算）	AND, OR, NOT

(1 比较大小

(2 确定范围

BETWEEN AND,NOT BETWEEN AND用来查找属性值在（不在）指定范围内的元组，BETWEEN后是范围的下限，AND后是范围的上限

(3 确定集合

谓词IN用来查找属性值属于指定集合的元组

--查询计算机科学系（CS）、数学系（MA）、信息系
--（IS）学生的姓名和性别
SELECT Sname,Ssex
FROM Student
WHERE Sdent IN ('CS','MA','IS');

(4 字符匹配(待写)

WHERE Sno LIKE'21312'

(5 涉及空值的查询

SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;

(6 多重条件查询

逻辑运算符AND和OR可以用来连接多个查询条件。AND的优先级高于OR，但用户可以 用括号改变优先级

SELECT Sname
FROM Student
WHERE Sdept='CS'AND Sage<20;

3. ORDER BY子句

用ORDER BY 子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认值为升序。

--查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
SELSECT*
FROM Student
ORDER BY Sdept,Sage DESC;

4. 聚集函数

--统计元组个数
COUNT(*)
--统计一列中元组个数
COUNT([DISTINCT|ALL]<列名>)
--计算一列值的总和（此列必须是数值型）
SUM([DISTINCT|ALL]<列名>)
--计算一列值的平均值（此列必须是数值型）
AVG([DISTINCT|ALL]<列名>)
--计算一列值的最大值
MAX([DISTINCT|ALL]<列名>)
--计算一列值的最大值
MIN([DISTINCT|ALL]<列名>)

可以指定DISTINCT短语在计算时取消指定列中的重复值，不指定则默认ALL。

--查询选修了课程的学生人数
SELSECT COUNT*(DISTINCT Sno)
FROM SC;

当聚集函数遇到空值时，除COUNT(*)外，都跳过空值只处理非空值。聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句

5. GROUP BY子句

GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

--查询选修了三门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3

--查询平时成绩大于等于90分的学生学号和平时成绩
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90

WHERE子句不能用聚集函数作为条件表达式，WHERE 子句下写一般条件，聚集函数用HAVING

2. 连接查询

1. 等值与非等值连接查询

连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词

--格式：
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
--或者
[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

来连接谓词中的列名称为连接字段。

在等值连接中把目标列中重复的属性列去掉则为自然连接（即只在SELECT中列出需要的列名）。

WHERE子句可以由连接谓词和选择谓词组成，进而同时完成选择和连接查询。

--查询选修课2号课程且成绩在90分以上的所有学生的学号和姓名
SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND
--连接谓词
        SC.Cno='2'AND SC.Grade>90;
--其他限定条件

2. 自身连接

自身连接要给表取不同的别名

-自身连接
SELECT FIRST.Cno,SECOND.Cpno
FROM Course cou,Course couse
--别名即在表名后空格输入即可
WHERE FIRST.Cpno=SECOND.Cno;

3. 外连接

不满足连接条件，但仍然保存在结果关系中，在属性上填空值

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno.Grade
FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno)
--也可以使用USING来去掉结果中的重复值：FROM　Student　LEFT　OUTER JOIN SC　USING（Sno）;

4. 多表连接

与两个以上的表进行连接被称为多表连接

--查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno AND SC.Cno=Course.Cno;

3. 嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

SELECT Sname   --外层查询或父查询
FROM Student
WHERE Sno IN
            (SELECT Sno  --内层查询或子查询
             FROM SC
             WHERE Cno='2');

1. 带有IN谓词的子查询

--查询与“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname='刘晨'

不相关子查询：子查询的查询条件不依赖于父查询。先完成子查询，再完成父查询

相关子查询：子查询条件依赖于父查询。整个查询语句称为相关嵌套查询。由于子查询与父查询有关，必须反复求值（与不相关子查询不同）。

2. 带有比较运算符的子查询

子查询返回单值可以用比较运算符，但返回多值需要用比较运算符加上ANY（有的系统用ＳＯＭＥ）或ALL

3. 带有ANY(SOME)或ALL谓词的子查询

比较运算符语义

>ANY	大于子查询结果中的某个值
>ALL	大于子查询结果中的所有值
<ANY	小于子查询结果中的某个值
<ALL	小于子查询结果中的所有值
>=ANY	大于等于子查询结果中的某个值
>=ALL	大于等于子查询结果中的所有值
<=ANY	小于等于子查询结果中的某个值
<=ALL	小于等于子查询结果中的所有值
=ANY	等于子查询结果中的某个值
=ALL	等于子查询结果中的所有值(通常没有实际意义)
!=ANY	不等于子查询结果中的某个值
!=ALL	不等于子查询结果中的任何一个值

--查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
               FROM Student
               WHERE Sdept='CS')
AND Sdept!='CS';

说明：用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高

ANY（或ＳＯＭＥ） ALL谓词与聚集函数 IN谓词的等价转换关系

	=	<>!=	<	<=	>	>=
ANY	IN	--	<MAX	<=MAX	>MIN	>=MIN
ALL	--	NOT IN	<MIN	<=MIN	>MAX	>=MAX

4. 带有EXISTS谓词的子查询

EXISTS 代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”和逻辑假值“false”

--查询所有选修了1号课程的学生姓名
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='1';
--若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值

NOT EXISTS:若内层查询结果非空，则外层的WHERE子句返回假值，否则返回真值

4. 集合查询

集合操作主要包括并操作UNION、交操作INTERSECT、差操作EXCEPT（注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同）

（待写交并差集合）

--查询计算机科学系的学生及年龄不大于19岁的学生
SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT*
FROM Studet
WHERE Sage<=19;
--使用UNION将多个查询结果合并起来时，系统会自动去掉重复元祖。如果保留重复元祖则用UNION ALL操作符

5. 基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表称为主查询的查询对象

--找出每个学生超过他自己选修课程平均成绩的课程号
SELECT Sno,Cno
FROM SC,(SELECT Sno,Avg(Grade)FROM SC GROUP BY Sno)
        AS Avg_sc(Avg_ano,avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno AND SC.Grade>=Avg_sc.avg_grade;

说明：通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名，对于基本表，别名是可选择项。