SQL Server 应用开发（七）

 

第5章 数据查询（2）
5.1 模糊查询：
一、使用LIKE进行模糊查询
    1、查询学生表中姓“张”的学生信息
       SELECT * FROM Students WHERE SName LIKE '张%'

    2、查询不是八月份发行的A卡或C卡：即前二位是0，第三位不能是8，卡号中要存在A或C
       SELECT * FROM Card WHERE ID LIKE '00[^8]%[A,C]%'

二、使用BETWEEN在某个范围内进行查询
    1、选择分数在60到80之间的学员信息
       SELECT * FROM StuScore WHERE Score BETWEEN 60 AND 80

    2、查询不在1992年8月1日到1993年8月1日之间订购的图书列表
       SELECT * FROM Sales WHERE or_date NOT BETWEEN '1992-8-1' AND '1993-8-1'

三、使用IN在列举值内进行查询
    查询家庭住址在“北京，广州，上海”的学生姓名并按地址升序进行排序
    SELECT SName AS 学生姓名 FROM Students WHERE Saddress IN ('北京','广州','上海')

5.2 SQL SERVER 中的聚合函数：
一、SUM()：求和
    这种查询只返回一条记录，因此，不能直接与可能返回多行的一起使用来进行查询在titles表中查询type为business的所有ytd_sales的总和
    SELECT SUM(ytd_sales) FROM titles WHERE type = 'business'

二、AVG()：求平均
    在stuScore表中统计分数大于等于60分的所有学生的平均成绩
    SELECT AVG(Score) AS 平均成绩 FROM stuScore WHERE Score >= 60

三、MAX()：求最大值，MIN()：求最小值
    在stuScore表中统计分数大于等于60分的所有学生的平均成绩和最高分以及最低分
    SELECT AVG(Score) AS 平均成绩, MAX(Score) AS 最高分, MIN(Score) AS 最低分 FROM stuScore WHERE Score >= 60

五、COUNT()：统计
    统计考试成绩及格的人数（记录条数）
    SELECT COUNT(*) FROM stuScore WHERE Score >= 60

5.3 分组查询
一、完整的SELECT查询语句结构
    SELECT <列名>
    FROM <表名>
    [WHERE <查询条件表达式>]
    [GROUP BY <分组字段>]
    [HAVING <过滤表达式>]
    [ORDER BY <排序字段> [ASC|DESC]]

二、出现在GROUP BY中的项目，必须出现在SELECT指定的项目中，或者出现在聚合函数中

三、相关示例：
（一）有“学生考试成绩表”信息如下，现在要查询每个学科（CourseID）的学生考试平均成绩

StudentID	CourseID	Score	ScoreID
22	2	67	1
4	2	78	2
16	2	56	3
23	4	57	4
25	4	89	5
27	4	45	7
28	5	67	8
29	6	78	9
30	6	67	10
31	6	88	11

    先对全部记录按CourseID进行分组后，再对每组的成绩（即每门学科的成绩）进行求平均运算
    SELECT CourseID, AVG(Score) FROM Score GROUP BY CourseID

（二）有“学生考试成绩表”信息如下，前7条记录为第一次内部测试成绩，第8-14条是第二次内部测试成绩，第15-21条是第三次内部测试成绩，对于在前三次内部测试没有及格的学生，需要进行补考，第22-25条是补考成绩，现要查询补考学员最后的成绩（第一次成绩与补考成绩的平均值），想要得到查询结果，要经过二个步骤：
      步骤1：首先学员的编号、内部测试的编号不能存在重复，两项都重复的行的分数值要取平均值。因此，这是一个既按学员编号、又按内部测试编号进行分组的查询
      步骤2：然后要解决只显示“补考过的学员”的成绩问题，这时需要进行分组统计后的条件限制。限制条件为COUNT(Score)>1，也就是相同的学员编号列和内部测试成绩列，至少出现过两次。这时使用WHERE子句不能满足查询要求，因为WHERE子句只能对没有分组统计前的数据进行筛选，对分组后的条件筛选必须使用HAVING子句

StudentID	CourseID	Score	ScoreID
16	1	66	1
23	1	56	2
25	1	67	3
27	1	45	4
28	1	70	5
29	1	71	6
30	1	60	7
16	2	75	8
23	2	80	9
25	2	90	10
27	2	88	11
28	2	78	12
29	2	59	13
30	2	84	14
16	3	70	15
23	3	60	16
25	3	67	17
27	3	68	18
28	3	81	19
29	3	72	20
30	3	56	21
23	1	67	22
27	1	87	23
30	3	68	24
29	2	80	25

    SELECT StudentID AS 学员编号, CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩 FROM Score GROUP BY CourseID,StudentID HAVING COUNT(Score) > 1

（三）在按照部门分类的员工表中，要查询“有多个员工的工资不低于2000的部门编号”
    SELECT 部门编号, COUNT(*) FROM 员工信息表 WHERE 工资>=2000 GROUP BY 部门编号 HAVING COUNT(*) > 1

5.4 多表联查
一、内联查询：多用来查询多表内具体相同字段信息的记录
（一）使用WHERE进行内联查询：性能较差
    查询参加了考试的学员的学员信息，对于有共用字段的二个表，需要用.来引用不同表的同名字段
    SELECT Student.Sname, Score.CourseID, Score.Score FROM Students,Score WHERE Students.Scode = Score.StudentID

（二）使用INNER JOIN ON进行内联查询
    查询参加了考试的学员的学员信息
    SELECT S.Sname, C.CourseID, C.Score FROM Students AS S INNER JOIN Score AS C ON (S.Scode = C.StudentID)

二、外联查询：
（一）左外联查询：LEFT OUTER JOIN ON：查询结果包括左表全部信息     查询全部学员的考试成绩，包括参加考试的和没有参加考试的
    SELECT S.Sname, C.CouresID, C.Sore FROM Students AS S LEFT JOIN Score AS C ON S.Code = C.StudentID

（二）右外联查询：RIGHT OUTER JOIN ON：查询结果包括右表全部信息     在PUBS数据库中，在Titles和Publishers表之间的右向外联接将包括所有的出版商，在Titles表中没有书名的也被列出
    SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name FROM Titles RIGHT JOIN Publishers ON Titles.Pub_id = Publishers.Pub.id

（三）完全外联查询：FULL OUTER JOIN ON：查询结果包括左右表内部信息

三、交叉联接查询：