第五章续分组及连接查询

数据查询(2)1回顾数据查询将按照一定的条件对表的的记录进行逐行筛选，然后把符合要求的组合成“记录集”返回给查询的用户，记录集的结构类似于表结构可以在查询中使用AS子句或者=重命名列名判断一行中的数据项是否为空，使用ISNULL使用TOP子句可以限制查询返回的行数ORDERBY子句用来对查询的结果进行排序，缺省按照升序排列，也可以按降序（DESC）来排列，还可以按照多列来排序在查询语句中，可以使用常量、表达式和运算符在查询中使用函数，能够像在程序中那样处理查询得到的数据项24.4.4转换函数程序代码如下：PRINT'今天的日期是'+CONVERT(VARCHAR(12),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'现在是星期'+CONVERT(VARCHAR(12),DATEPART(Dw,getdate()))日期和时间函数的使用示例。3目标使用LIKE、BETWEEN、IN进行模糊查询在查询中使用聚合函数使用GROUPBY进行分组查询进行多表联接查询4模糊查询—LIKE查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容SELECTSNameAS姓名FROMStudentsWHERESNameLIKE'张%'姓名张果老张飞张扬出去思考：以下的SQL语句：SELECT*FROM数据表WHERE编号LIKE'00[^8]%[A,C]%‘可能会查询出的编号值为()。A、9890ACDB、007_AFFC、008&DCGD、KK8C5模糊查询-LIKE示例：查询姓名叫”王**军“的学生信息通配符：%：包含零个或多个字符的任意字符串_下划线：任何单个字符[]：指定范围[a-f]或集合[acdef]中的任意单个字符[^]：不属于指定范围([a-f])或集合([abcdef])的任何单个字符。提问：在author表中查找以de开始并且其后的字母不为l的所有作者的姓氏SELECT*FROMSTUDENTWHEREStuNameLIKE‘王_军‘6模糊查询—ISNULL把某一字段中内容为空的记录查询出来SELECTSNameAs姓名SAddressAS地址FROMStudentsWHERESAddress

ISNULL姓名地址张果老NULL李寻欢NULL令狐冲NULL程灵素NULL……猜一猜：把Students表中某些行的SAddress字段值删掉后：——使用ISNULL能查询出来这些数据行吗？——怎么查询出这些行来？7模糊查询—BETWEEN把某一字段中内容在特定范围内的记录查询出来SELECTStudentID,ScoreFROMSCoreWHEREScore

BETWEEN60AND80StudentIDScore77786111761764……8模糊查询—IN把某一字段中内容与所列出的查询内容列表匹配的记录查询出来SELECTSNameAS学员姓名,SAddressAs地址FROMStudentsWHERESAddress

IN

('北京','广州','上海')学员姓名地址李扬广州于紫电上海李青霜北京司马弓上海……9问题成绩表中存储了所有学员的成绩，我想知道：学员的总成绩、平均成绩、有成绩的学员总共有多少名怎么办？10聚合函数-1SUMAVGSELECTSUM(ytd_sales)FROMtitlesWHEREtype='business'SELECTSUM(ytd_sales),Price

FROMtitlesWHEREtype='business'×SELECTAVG(SCore)AS平均成绩

FromScoreWHEREScore>=6011聚合函数-2MAX、MINCOUNTSELECTAVG(SCore)AS平均成绩,MAX

(Score)AS最高分,MIN(Score)AS最低分FromScoreWHEREScore>=60SELECT

COUNT(*)

AS及格人数FromScoreWHEREScore>=6012问题如果不是统计所有人所有课程的总成绩而是想求每一门课的平均绩或者某个人的所有课的总成绩怎么办？13分组汇总这三个数取平均值第4-6分数取平均值最后三个数取平均值有一个学员参加考试14分组查询—GROUPBYSELECT

CourseID,AVG(Score)

AS课程平均成绩FROMScoreGROUPBY

CourseID15分组查询—思考SELECT

StudentID,CourseID,AVG(Score)

AS课程平均成绩FROMScoreGROUPBY

CourseID思考：执行以下的T-SQL：——结果如何？16分组查询—多列分组第一次内部测试成绩第二次内部测试成绩第三次内部测试成绩补考成绩怎么样来统计每次的内部测试不同学员的成绩？17分组查询—再看看内部测试成绩还要把这个加上取平均StudentID和CourseID的组合存在重复……需要按照这两个来进行分组，避免StudentID和CourseID同时一样18分组查询—多列分组SELECTStudentIDAS学员编号,CourseIDAS内部测试,AVG(Score)AS内部测试平均成绩FROMScoreGROUPBYStudentID,CourseID19分组查询—问题在以上统计内部测试成绩的基础上，如果只想看补考的学员的成绩，怎么办？20分组查询—再看看增加条件：要求该学员的CourseID在分组内出现过一次以上……SELECTStudentIDAS学员编号,CourseIDAS内部测试,AVG(Score)AS内部测试平均成绩FROMScoreGROUPBYStudentID,CourseID21分组查询—HAVINGSELECTStudentIDAS学员编号,CourseIDAS内部测试,AVG(Score)AS内部测试平均成绩FROMScoreGROUPBYStudentID,CourseIDHAVINGCOUNT(Score)>122分组查询—对比WHEREGROUPBYHAVINGWHERE子句从数据源中去掉不符合其搜索条件的数据GROUPBY子句搜集数据行到各个组中，统计函数为各个组计算统计值HAVING子句去掉不符合其组搜索条件的各组数据行23分组查询—思考SELECT

部门编号,COUNT(*)FROM

员工信息表WHERE

工资>=2000GROUPBY

部门编号HAVING COUNT(*)>1思考：分析以下T-SQL的含义24多表联接查询—问题学员内部测试成绩查询的每次显示的都是学员的编号信息，因为该表中只存储了学员的编号；实际上最好显示学员的姓名，而姓名存储在学员信息表；如何同时从这两个表中取得数据？25多表联接查询—分类内联接(INNERJOIN)外联接——左外联接(LEFTJOIN)——右外联接(RIGHTJOIN)——完整外联接(FULLJOIN)交叉联接(CROSSJOIN)26SELECT S.SName,C.CourseID,C.ScoreFrom ScoreASCINNERJOIN StudentsASSON C.StudentID=S.SCodeScoreStudentsIDCourseIDScore122300100100200297896776300381猜一猜：这样写，返回的查询结果是一样的吗？SELECT S.SName,C.CourseID,C.ScoreFrom StudentsASSINNERJOIN ScoreASCON C.StudentID=S.SCode再猜一猜：以下返回多少行？SELECT S.SName,C.CourseID,C.ScoreFrom StudentsASSINNERJOIN ScoreASCON C.StudentID<>S.SCode多表联接查询—内联接-1StundentsSName梅超风陈玄风陆乘风曲灵风SCode1234查询结果SName梅超风陈玄风陈玄风陆乘风CourseIDScore00100100200297896776陆乘风0038127多表联接查询—内联接-2SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHERE

Students.SCode=Score.StudentID28多表联接查询—三表联接SELECT

S.SNameAS姓名,CS.CourseNameAS课程,C.ScoreAS成绩FROMStudentsASSINNERJOINScoreASCON(S.SCode=C.StudentID)INNERJOINCourseASCSON(CS.CourseID=C.CourseID)29ScoreStudentsIDCourseIDScore122300100100200297896776300381多表联接查询—左外联接StundentsSName梅超风陈玄风陆乘风曲灵风SCode1234查询结果SName梅超风陈玄风陈玄风陆乘风CourseIDScore00100100200297896776陆乘风00381曲灵风NULLNULLSELECT

S.SName,C.CourseID,C.ScoreFrom

StudentsASSLEFTJOIN ScoreASCON

C.StudentID=S.SCode猜一猜：这样写，返回的查询结果是一样的吗？SELECT

S.SName,C.CourseID,C.ScoreFrom

ScoreASCLEFTJOIN

StudentsASSON

C.StudentID=S.SCode30多表联接查询—右外联接SELECTTitles.Title_id,Titles.Title,Publishers.Pub_nameFROMtitlesRIGHTOUTERJOINPublishersONTitles.Pub_id=Publishers.Pub_id313．自身连接

连接操作不仅可以在两个表之间操作，也可以是一个表与其自己进行连接，这种操作称为自身连接。32【例】查询每个人的祖父姓名。假定个人情况表如表所示。表Person33要查出个人的祖父姓名，必须将这个表做自身连接。查询语句为：SELECTFirst.Name，Second.F_nameFROMPersonFirst，PersonSecondWHEREFirst.F_name=Second.Name这里First和Second分别是第一个Person表和第二个Person表的别名。查询结果：张成全张福全李向军李福来34自联接

提问：查询员工信息以及经理的信息SELECTA.*,B.ENameASMgrNameFROMEmployeeAJOINEmployeeBONA.Mgr=B.EmpNo35案例分析1—要求在数据库表中，数据行位置并不重要，但是一个单位中要根据奇数行和偶数行的数据来汇总，在这个汇总的基础上再得到一个数值，因此，要查询数据库表的奇数行和偶数行的总数数据库表名：TBL 字段名：A 主键字段：IDKEY（标识列，种子：1；增长量：1）36案例分析1—分析只能依靠标识列的值来进行判断和选取因为数据行可能存在增加、修改和删除，因此标识列的数据值并不“完全可靠”，例如标识列值为3，并不一定是第三行，因为如果第二行被删除了，它就是第二行，实际上也是偶数行根据我们前面使用过的SELECT…INTO，可以创建一张新表，顺便创建新的标识列，再在新的标识列上执行奇偶判断奇数判断依据：标识列值%2不等于0；偶数判断依据：标识列值%2等于037案例分析1—T-SQLSELECT A,IDENTITY(int,1,1)ASID

INTO TEMPTABLEFROM TBLSELECT

SUM(A)AS奇数列汇总FROM TEMPTABLEWHERE ID%2<>0SELECT

SUM(A)FROMAS偶数列汇总

FROM TEMPTABLEWHERE ID%2=038案例分析2—要求一家银行发行了新的信用卡，刚开始的时候推广得很好，但是逐渐废卡也越来越多（卡上的余额少于2元，并且用户长时间不使用该卡），因此银行在二月份把这些少于2元的卡从都数据库表中删除了，但是很快问题就来了，用户发现他的卡再也不能使用而投诉，因此只能再把这些卡恢复。编写SQL，把CardID为23、30等在子表中没有的记录插入到子表中，插入的数据Score项值为2……39案例分析2—分析这是一个数据插入的操作，因此要使用INSERT语句来进行这种方式的数据插入，不是固定的数据项，而是从其它表中筛选数据再插入，因此要使用到INSERTINTO…SELECT结构插入的数据项是子表中没