第五章续 分组及连接查询

1、1数据查询(2)2回顾n数据查询将按照一定的条件对表的的记录进行逐行筛选，然后把符合要求的组合成“记录集”返回给查询的用户，记录集的结构类似于表结构 n可以在查询中使用AS子句或者=重命名列名n判断一行中的数据项是否为空，使用IS NULLn使用TOP子句可以限制查询返回的行数nORDER BY子句用来对查询的结果进行排序，缺省按照升序排列，也可以按降序（DESC）来排列，还可以按照多列来排序n在查询语句中，可以使用常量、表达式和运算符n在查询中使用函数，能够像在程序中那样处理查询得到的数据项34.4.4 转换函数程序代码如下：PRINT 今天的日期是 + CONVERT(VARCHAR(12

2、), GETDATE(),101)PRINT 今年是+CONVERT(VARCHAR(12),Year(Getdate()PRINT 本月是+CONVERT(VARCHAR(12),Month(Getdate()+月PRINT 今天是+CONVERT(VARCHAR(12),day(Getdate()+号PRINT 后天是+CONVERT(VARCHAR(12),DATEADD(Dy,2,getdate(),101)PRINT 与2011年1月27号还差+CONVERT(VARCHAR(12),DATEDIFF(DAy,getdate(),01/27/2011)+天 PRINT 现在是星期+C

3、ONVERT(VARCHAR(12),DATEPART(Dw,getdate()日期和时间函数的使用示例。4目标n使用LIKE、BETWEEN、IN进行模糊查询n在查询中使用聚合函数n使用GROUP BY进行分组查询n进行多表联接查询5模糊查询LIKEn查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容SELECT SName AS 姓名 FROM Students WHERE SName LIKE 张%张果老张果老张飞张飞张扬出去张扬出去思考：以下的SQL语句：SELECT * FROM 数据表 WHERE 编号 LIKE 008%A,C%可能会查询出的编号值为( )。A

4、、9890ACDB、007_AFFC、008&DCGD、KK8C6模糊查询模糊查询-LIKEn示例：查询姓名叫示例：查询姓名叫”王王*军军“的学生信息的学生信息n通配符：通配符：n%：包含零个或多个字符的任意字符串：包含零个或多个字符的任意字符串 n_下划线下划线 ：任何单个字符：任何单个字符 n ：指定范围：指定范围a-f或集合或集合acdef中的任意单个字符中的任意单个字符：不属于：不属于指定范围指定范围 (a-f) 或集合或集合 (abcdef) 的任何单个字符。的任何单个字符。n提问：在提问：在author表中查找以表中查找以 de 开始并且其后的字母不开始并且其后的字母不 为

5、为l 的所有作者的姓氏的所有作者的姓氏 SELECT * FROM STUDENTWHERE StuName LIKE 王王_军军7模糊查询IS NULLn把某一字段中内容为空的记录查询出来SELECT SName As 姓名 SAddress AS 地址 FROM Students WHERE SAddress IS NULL张果老NULL李寻欢NULL令狐冲NULL程灵素NULL猜一猜：把Students表中某些行的SAddress字段值删掉后： 使用IS NULLIS NULL能查询出来这些数据行吗？ 怎么查询出这些行来？8模糊查询BETWEENn把某一字段中内容在特定范围内的记录查询出

6、来SELECT StudentID, Score FROM SCore WHERE Score BETWEEN 60 AND 80777861117617649模糊查询INn把某一字段中内容与所列出的查询内容列表匹配的记录查询出来SELECT SName AS 学员姓名,SAddress As 地址 FROM Students WHERE SAddress IN (北京,广州,上海)李扬广州广州于紫电上海上海李青霜北京北京司马弓上海上海10问题成绩表中存储了所有学员的成绩，我想知道：学员的总成绩、平均成绩、有成绩的学员总共有多少名怎么办？11聚合函数-1SUMAVGSELECT SUM(ytd

7、_sales) FROM titles WHERE type = businessSELECT SUM(ytd_sales) ,Price FROM titles WHERE type = businessSELECT AVG(SCore) AS 平均成绩 From Score WHERE Score =6012聚合函数-2MAX、MINCOUNTSELECT AVG(SCore) AS 平均成绩, MAX (Score) AS 最高分, MIN (Score) AS 最低分 From Score WHERE Score =60SELECT COUNT (*) AS 及格人数 From Sco

8、re WHERE Score=6013问题如果不是统计所有人所有课程的总成绩而是想求每一门课的平均绩或者某个人的所有课的总成绩怎么办？14分组汇总这三个数取平均值第4-6分数取平均值最后三个数取平均值有一个学员参加考试15分组查询GROUP BYSELECT CourseID, AVG(Score) AS 课程平均成绩FROM ScoreGROUP BY CourseID16分组查询思考SELECT StudentID, CourseID, AVG(Score) AS 课程平均成绩FROM ScoreGROUP BY CourseID思考：执行以下的T-SQL： 结果如何？结果如何？17分组查

9、询多列分组第一次内部测试成绩第二次内部测试成绩第三次内部测试成绩补考成绩怎么样来统计每次的内部测试不同学员的成绩？怎么样来统计每次的内部测试不同学员的成绩？18分组查询再看看内部测试成绩还要把这个加上取平均StudentID和和CourseID的组合存在重复的组合存在重复需要按照这两个来进行分组，避免需要按照这两个来进行分组，避免StudentID和和CourseID 同时一样同时一样19分组查询多列分组SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩FROM ScoreGROUP BY StudentID,Co

10、urseID20分组查询问题在以上统计内部测试成绩的基础上，如果只想看补考的学员的成绩，怎么办？21分组查询再看看增加条件：要求该学员的CourseID在分组内出现过一次以上SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩FROM ScoreGROUP BY StudentID,CourseID22分组查询HAVINGSELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩FROM ScoreGROUP BY StudentID,Cours

11、eIDHAVING COUNT(Score)123分组查询对比WHEREGROUP BYHAVINGnWHERE子句从数据源中去掉不符合其搜索条件的数据nGROUP BY子句搜集数据行到各个组中，统计函数为各个组计算统计值nHAVING子句去掉不符合其组搜索条件的各组数据行24分组查询思考SELECT 部门编号, COUNT(*)FROM 员工信息表WHERE 工资 = 2000GROUP BY 部门编号HAVING COUNT(*) 1思考：分析以下思考：分析以下T-SQL的含义的含义25多表联接查询问题学员内部测试成绩查询的每次显示的都是学员的编号信息，因为该表中只存储了学员的编号；实际上

12、最好显示学员的姓名，而姓名存储在学员信息表；如何同时从这两个表中取得数据？26多表联接查询分类n内联接(INNER JOIN)n外联接左外联接 (LEFT JOIN)右外联接 (RIGHT JOIN)完整外联接(FULL JOIN)n交叉联接(CROSS JOIN)27SELECT S.SName,C.CourseID,C.Score From Score AS CINNER JOIN Students AS SON C.StudentID = S.SCodeScore122300100100200297896776300381猜一猜：这样写，返回的查询结果是一样的吗？SELECT S.SNa

13、me,C.CourseID,C.Score From Students AS SINNER JOIN Score AS CON C.StudentID = S.SCode再猜一猜：以下返回多少行？SELECT S.SName,C.CourseID,C.Score From Students AS SINNER JOIN Score AS CON C.StudentID S.SCode多表联接查询内联接-1Stundents梅超风陈玄风陆乘风曲灵风1234查询结果查询结果梅超风陈玄风陈玄风陆乘风00100100200297896776陆乘风0038128多表联接查询内联接-2SELECT Stu

14、dents.SName, Score.CourseID, Score.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID29多表联接查询三表联接SELECT S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩FROM Students AS S INNER JOIN Score AS C ON (S.SCode = C.StudentID)INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)30Score12230010

15、0100200297896776300381多表联接查询左外联接Stundents梅超风梅超风陈玄风陈玄风陆乘风陆乘风曲灵风曲灵风1234查询结果查询结果梅超风梅超风陈玄风陈玄风陈玄风陈玄风陆乘风陆乘风00100100200297896776陆乘风陆乘风00381曲灵风曲灵风NULLNULLSELECT S.SName,C.CourseID,C.Score From Students AS SLEFT JOIN Score AS CON C.StudentID = S.SCode猜一猜：这样写，返回的查询结果是一样的吗？SELECT S.SName,C.CourseID,C.Score Fro

16、m Score AS CLEFT JOIN Students AS SON C.StudentID = S.SCode31多表联接查询右外联接SELECT Titles.Title_id, Titles.Title, Publishers.Pub_nameFROM titles RIGHT OUTER JOIN Publishers ON Titles.Pub_id = Publishers.Pub_id323自身连接 n连接操作不仅可以在两个表之间操作，也可以是一个表与其自己进行连接，这种操作称为自身连接。33【例】 查询每个人的祖父姓名。 假定个人情况表如表所示。 表 Person 34要

17、查出个人的祖父姓名， 必须将这个表做自身连接。 查询语句为： SELECT First. Name， Second. F_name FROM Person First， Person Second WHERE First.F_name=Second.Name 这里First和Second分别是第一个Person表和第二个Person表的别名。 查询结果： 张成全 张福全 李向军 李福来35自联接自联接 提问：查询员工信息以及经理的信息提问：查询员工信息以及经理的信息SELECT A.*, B.EName AS MgrNameFROM Employee A JOIN Employee BON A

18、.Mgr=B.EmpNo36案例分析1要求n在数据库表中，数据行位置并不重要，但是一个单位中要根据奇数行和偶数行的数据来汇总，在这个汇总的基础上再得到一个数值，因此，要查询数据库表的奇数行和偶数行的总数n数据库表名：TBL字段名：A主键字段：IDKEY（标识列，种子：1；增长量：1）37案例分析1分析n只能依靠标识列的值来进行判断和选取n因为数据行可能存在增加、修改和删除，因此标识列的数据值并不“完全可靠”，例如标识列值为3，并不一定是第三行，因为如果第二行被删除了，它就是第二行，实际上也是偶数行n根据我们前面使用过的SELECTINTO，可以创建一张新表，顺便创建新的标识列，再在新的标识列上

19、执行奇偶判断n奇数判断依据：标识列值%2不等于0；偶数判断依据：标识列值%2等于038案例分析1T-SQLSELECT A, IDENTITY(int,1,1) AS ID INTO TEMPTABLE FROM TBLSELECT SUM(A) AS 奇数列汇总 FROM TEMPTABLE WHERE ID%2 0SELECT SUM(A) FROM AS 偶数列汇总 FROMTEMPTABLE WHEREID%2 = 039案例分析2要求n一家银行发行了新的信用卡，刚开始的时候推广得很好，但是逐渐废卡也越来越多（卡上的余额少于2元，并且用户长时间不使用该卡），因此银行在二月份把这些少于2元的卡从都数据库表中删除了，但是很快问题就来了，用户发现他的卡再也不能使用而投诉，因此只能再把这些卡恢复。n编写SQL，把CardID为23、30等在子表中没有的记录插入到子表中，插入的数据Score项值为240案例分析2分析n这是一个数据插入的操作，因此要使用INSERT语句来进行n这种方式的数据插入，不是固定的数据项，而是从其它表中筛选数据再插入，因此要使用到INSERT INTOSELECT结构n插入的数据项是子表中没有的，需要找到子表中没有的这些数据，我们可以把整个语句的INSERT（前半部分）不考虑