SQL Server数据库查询

1、SELECT语法
    SELECT select_list
    [INTO new_table_name ]
    FROM table_list
    [ WHERE search_conditions ]
    [ GROUP BY group_by_list ]
    [ HAVING search_conditions ]
    [ ORDER BY order_list [ ASC | DESC ] ]

2、选择表中的若干列
（1）查询指定列
例1：查询全体学生的学号和姓名
SELECT Sno, Sname FROM Student

例2：查询全体学生的姓名、学号、所在系，查询结果列改名
SELECT Sname AS 姓名, Sno 学号, 所在系 = Sdept FROM Student

（2）查询全部列
例3：查询Student表的全部列
SELECT * FROM Student

（3）查询经过计算的列
例4：查询全体学生的姓名及其出生年份
--GETDATE()：得到系统的当前日期；YEAR()：得到指定日期的年号。
SELECT Sname, YEAR(GETDATE()) – Sage AS 出生年份
FROM Student

3、选择表中的若干元组
（1）取消重复元组
使用DISTINCT短语，可以让相同的元组只显示一个
例5: 查询选修了课程的学生学号

SELECT Sno FROM SC

（2）比较大小
查询满足条件的元组：查询满足指定条件的元组可以通过WHERE子句实现；

例6：查询计算机系全体学生的名单

SELECT Sname FROM Student WHERE Sdept = 'CS'

例7：查询所有年龄在20岁以下的学生姓名及其年龄
SELECT Sname, Sage FROM Student WHERE Sage < 20

例8: 查询考试成绩有不及格的学生的学号
SELECT DISTINCT Sno FROM SC WHERE Grade < 60

（2）确定范围
例9： 查询年龄在20至23岁之间的学生的姓名、系别、和年龄
SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23
SELECT Sname, Sdept, Sage FROM Student WHERE Sage >= 20 AND Sage <= 23

例10: 查询年龄不在20至23岁之间的学生的姓名、系别、和年龄
SELECT Sname, Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23
SELECT Sname, Sdept, Sage FROM Student WHERE Sage < 20 OR Sage > 23

（3）确定集合
例11: 查询信息系(IS)和计算机科学系(CS)的学生的姓名和性别
SELECT Sname, Ssex FROM Student WHERE Sdept IN('IS', 'CS')

例12: 查询既不是信息系(IS)，也不是计算机系(CS)的学生的姓名和性别
SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN('IS', 'CS')

（4）字符匹配
谓词LIKE用来进行全部或部分字符串匹配。
在进行部分字符串匹配时要用通配符“%”和“_”，
“%”匹配0个或多个字符，
“_”匹配1个字符，查询时使用LIKE和通配符，可以实现模糊查询。

例13：查询学号为2017001的学生的情况
--注：若LIKE后面的匹配串不含通配时，则可以用“=”运算符取代谓词LIKE。
SELECT * FROM Student WHERE Sno LIKE '2017001'
SELECT * FROM Student WHERE Sno = '2017001'

例14：查询所有姓“刘”的学生的姓名、学号和性别
SELECT Sname, Sno, Ssex FROM Student
WHERE Sname LIKE '刘%'

例15： 查询所有不姓“刘”的学生姓名
SELECT Sname FROM Student WHERE Sname NOT LIKE '刘%'

例16：查询名字中第二字为“桃”字的学生的姓名和学号
SELECT Sname, Sno FROM Student
WHERE Sname LIKE '_桃%'

例17：查找姓氏在李与王之间的学生信息
SELECT * FROM Student
WHERE Sname LIKE '[李-王]%'

例18：查询DB_Design课程的课程号和学分
通过ESCAPE指定“\”字符是一个转义字符。
SELECT Cno, Ccredit FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\'

（5）涉及空值的查询
例19：查询选修了课程但没有成绩的学生学号和课程号
SELECT Sno, Cno FROM SC
WHERE Grade IS NULL

例20：查询所有有成绩的学生学号和课程号
SELECT Sno, Cno FROM SC
WHERE Grade IS NOT NULL

（6）多重条件查询
使用逻辑运算符AND和OR来联结多个查询条件，AND的优先级高于OR。

例21：查询CS系年龄在20岁以下的学生姓名
SELECT Sname, Ssex FROM Student
WHERE Sdept = 'CS' AND Sage < 20

扩展：SQL注入
用户名：' OR 1=1 --'
密  码：随意
SELECT COUNT(*) FROM Users
WHERE userName = '' OR 1=1 --'' and userPwd = 'AA'

用户名：随意
密  码：' OR ''='
SELECT COUNT(*) FROM Users
WHERE userName = 'AA' and userPwd = '' or ''=''

4、对查询结果排序
如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在数据表中的先后顺序）输出查询结果；
用户也可以用ORDER BY子句指定按照一个或多个属性列的升序(ASC)或降序(DESC)重新排列查询结果，其中升序ASC为缺省值；

例22：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列
SELECT Sno, Grade FROM SC
WHERE Cno = '3'
ORDER BY Grade DESC

例23：查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列
SELECT * FROM Student
ORDER BY Sdept, Sage DESC

5、使用集函数
SQL提供了许多集函数，主要包括：SUM、COUNT、MAX、MIN、AVG。

例24：查询学生总人数
SELECT COUNT(*) FROM Student

例25：查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) FROM SC

例26：计算1号课程的学生平均成绩
SELECT AVG(Grade) FROM SC
WHERE Cno = '1'

例27： 查询学习1号课程的学生最高分数
SELECT MAX(Grade) FROM SC
WHERE Cno = '1'

6、对查询结果分组
GROUP BY子句可以将查询结果表的各行按一列或多列值分组，值相等的为一组。
对查询结果分组的目的是为了细化集函数的作用对象。
如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。
WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

例28：查询各个课程号与相应的选课人数
SELECT Cno, COUNT(Sno) FROM SC
GROUP BY Cno

例29：查询选修了2门以上课程的学生的学号
先用GROUP BY子句按Sno分组，再用集函数COUNT对每一组计数。HAVING短语指定选择组的条件，只有满足条件（即元组个数>3，表示此学生选修的课超过3门）的组才会被选出来。
SELECT Sno FROM SC
GROUP BY Sno
HAVING COUNT(*) > 2

三、连接查询
若一个查询同时涉及两个以上的表，则称之为连接查询；
连接查询主要包括：① 等值连接、非等值连接查询；② 自身连接查询；③ 外连接查询；④ 复合条件连接查询；

1、等值与非等值连接
例30：查询每个学生及其选修课程的情况
SELECT Student.*, SC.* FROM Student, SC
WHERE Student.Sno = SC.Sno
等价于：
SELECT Student.*, SC.*
FROM Student INNER JOIN SC ON Student.Sno = SC.Sno

例31：自然连接Student和SC表
若属性列在两个表中是唯一的，可以省略表名前缀。
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC WHERE Student.Sno = SC.Sno

2、自身连接
例32：查询每一门课的间接先修课（即先修课的先修课）
SELECT A.Cno, B.Cpno
FROM Course A, Course B
WHERE A.Cpno = B.Cno

3、外连接
内连接 INNER JOIN：结果集仅包含那些满足连接条件的数据行；使用INNER JOIN 连接运算符，使用ON 关键字指定连接条件。
左外连接 LEFT [OUTER] JOIN：左外连接对连接条件中左边的表不加限制；
右外连接 RIGHT [OUTER] JOIN：右外连接对连接条件中右边的表不加限制；
全外连接 FULL [OUTER] JOIN：全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中；
交叉连接 CROSS JOIN：称为笛卡尔成绩，返回两个表的乘积，结果集中包含了所连接的两个表中所有行的全部组合；

例33：查询所有选修了1号的学生的姓名、课程号和成绩
SELECT Sname, Cno, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno AND Cno = '1'

--或者
SELECT Sname, Cno, Grade
FROM Student INNER JOIN SC
ON Student.Sno = SC.Sno AND Cno = '1'

例34：查询每个学生及其选修课程的情况（即使没有选课也列出该学生的基本情况）
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT JOIN SC
ON Student.Sno = SC.Sno

比较下面两条语句的区别
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT JOIN SC
ON Student.Sno = SC.Sno AND Cno = '1'
比较：
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT JOIN SC
ON Student.Sno = SC.Sno
WHERE Cno = '1'

例35：全外连接。查询每个学生及其选修课程的信息，包含未选课的学生 信息及未被选修的课程信息
SELECT Student.Sno, Sname, Course.Cno, Cpno, Ccredit, Cname, Grade
FROM Course FULL JOIN SC ON Course.Cno = SC.Cno
FULL JOIN Student ON Student.Sno = SC.Sno

4、复合条件连接
例36：查询选修2号课程且成绩在85分以上的所有学生
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND Cno = '2' AND Grade > 85

例37：查询每个学生及其选修的课程名及其成绩
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, Course, SC
WHERE Student.Sno = SC.Sno AND Course.Cno = SC.Cno

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

1、无关子查询
子查询的执行不依赖于外部查询；
子查询在外部查询之前执行，然后返回数据提供给外部查询使用；
子查询中不包含对于外部查询的任何引用；
子查询的SELECT 语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果进行排序；

例38：查询与“刘晨”在同一个系学习的学生
SELECT * FROM Student
WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname = '刘晨')

--注：“=”只能用于查询结果中只有一个元组的情况
SELECT * FROM Student
WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname = '刘晨')

例39：查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno, Sname FROM Student WHERE Sno IN(
   SELECT Sno FROM SC WHERE Cno IN(
      SELECT Cno FROM Course WHERE Cname = '信息系统'
   )
)

例40：查询所有未修1号课程的学生姓名
SELECT Sname FROM Student
WHERE Sno NOT IN(
   SELECT Sno FROM SC WHERE Cno = '1'
)

2、相关子查询
子查询的执行依赖于外部查询，多数情况下是子查询的 WHERE 子句中引用了外部查询的表；
子查询的执行过程：（1）子查询为外部查询的每一行执行一次，外部查询将子查询引用的列的值传给子查询；（2）如果子查询的任何行与其匹配，外部查询就返回结果行；（3）再回到第一步，直到处理完外部表的每一行。

例41：查询成绩比该课的平均成绩低得学生的学号，课程号和成绩
SELECT Sno, Cno, Grade FROM SC A
WHERE Grade < (SELECT AVG(Grade) FROM SC B WHERE A.Cno = B.Cno)

例42：查询所有选修了1号课程的学生姓名
带有EXISTS谓词的子查询不返回任何实际数据，它只产生逻辑真值“true”或逻辑假值“false”。
SELECT Sname FROM Student WHERE EXISTS(
SELECT * FROM SC WHERE Sno = Student.Sno and Cno = '1')

或者
SELECT Sname FROM Student WHERE Sno IN(
   SELECT Sno FROM SC WHERE Cno = '1'
)

或者
SELECT Sname FROM Student, SC
WHERE Student.Sno = SC.Sno AND SC.Cno = '1'

例43：查询所有未修1号课程的学生姓名
--用带IN谓词的子查询
SELECT Sname FROM Student WHERE Sno NOT IN(
   SELECT Sno FROM SC WHERE Cno = '1'
)
--用带EXISTS谓词的子查询
SELECT Sname FROM Student WHERE NOT EXISTS(
  SELECT * FROM SC WHERE SC.Sno = Student.Sno AND Cno = '1'
)

例44：查询选修了全部课程的学生姓名
SELECT Sname FROM Student WHERE NOT EXISTS(
    SELECT * FROM Course WHERE NOT EXISTS(
       SELECT * FROM SC WHERE Cno = Course.Cno
AND Sno = Student.Sno
    )
)

五、集合查询
1、并UNION
例45：查询计算机科学系的学生及年龄小于19岁的学生
SELECT * FROM Student WHERE Sdept = 'CS'
UNION
SELECT * FROM Student WHERE Sage < 19

2、交INTERSECT
例46：查询计算机科学系年龄小于19岁的学生
SELECT * FROM Student WHERE Sdept = 'CS'
INTERSECT
SELECT * FROM Student WHERE Sage < 19

3、差EXCEPT
例47：查询计算机科学系年龄大于19岁的学生
SELECT * FROM Student WHERE Sdept = 'CS'
EXCEPT
SELECT * FROM Student WHERE Sage < 19

六、数据更新
1、插入数据
（1）插入一个元组
例48：将一个新学生记录（学号：2017007；姓名：王桃群；性别：女；所在系：CS；年龄：18岁）插入Student表中
INSERT INTO Student VALUES('2017007', '王桃群', '女', 'CS', 18)

例49：插入一条选课记录('2017007 ', '1')
INSERT INTO SC(Sno, Cno) VALUES('2017007', '1')
--或者
INSERT INTO SC VALUES('2017007', '1', NULL)（2）插入多个元组

例50：将每个系学生的平均年龄插入到DeptAge表中
--方法1:先创建DeptAvgAge表
CREATE TABLE DeptAvgAge(
   Sdept CHAR(2),
   AvgAge INT
)
INSERT INTO DeptAvgAge
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept

--方法2:不必先创建DeptAvgAge表
SELECT Sdept, AVG(Sage) AvgAge
INTO DeptAvgAge
FROM Student GROUP BY Sdept

比较下面语句的作用
SELECT  * INTO  TABLE2 FROM  TABLE2 WHERE 1 = 2
INSERT TABLE2 SELECT * FROM TABLE1
SELECT  * INTO  TABLE2 FROM TABLE1

2、修改数据
例51：将学生2017007的年龄改为22岁
UPDATE Student
SET Sge = 22
WHERE Sno = '2017007'

例52：将计算机科学系全体学生的成绩置0
--方法1
UPDATE SC
SET Grade = 0
WHERE Sno IN(
  SELECT Sno
    FROM Student
    WHERE Sdept = 'CS')

--方法2：
UPDATE SC
SET Grade = 0
FROM Student JOIN SC
ON Student.Sno = SC.Sno and Sdept = 'CS'

--方法3
UPDATE SC
SET Grade = 50
FROM Student,  SC
WHERE Student.Sno = SC.Sno and Sdept = 'CS'

3、删除数据
例53：删除所有成绩不及格的选课记录
DELETE FROM SC WHERE Grade < 60

例54：删除所有的选课记录
DETELE FROM SC
--或者
TRUNCATE TABLE SC