数据库管理与开发项目教程---教案--07项目四：使用T-SQL查询表数据-任务3连接查询.docx

项目四使用T-SQL查询表数据任务1简单查询任务2分类汇总实训四使用SELECT语句进行基本查询任务3连接查询任务4子查询实训五使用多表查询和复杂查询实训六分析创建查询sale数据库复习：1、写出SELECT语句的书写格式。简述每个子句后面应该填写什么内容？Select DISTINCT选择列表Into新的表From 表或视图Where检索的条件Group by字段名1 Having内部函数表达式Order by 字段名 2 ASC/DESC 2、聚合函数有哪些，请写出函数名及其功能，他们可以出现在SELECT哪些字句里？3、请说出 computer by 与 group by having 的区别？任务3连接查询笛卡尔积：select * from class, student, stucou多表连接查询分：内连接：等值连接、不等值连接、自联接外连接：左外连接、右外连接、全外连接交叉连接：笛卡尔积【例2. 32USE XkGOSELECT ClassNo, ClassName, Class. DepartNo, DepartNameFROM Class, DepartmentWHERE Class. DepartN。二Department. DepartNoGO【例2. 33USE XkGOSELECT Student. *, ClassNameFROM Class, StudentWHERE Class. ClassNo二Student. ClassNo GO-使用ANSI连接语法的SELECT语句：USE XkGOSELECT Student. *, ClassNameFROM Class JOIN StudentON Class. ClassNo=Student. ClassNoGO【例2. 34USE XkGOSELECT StuNamc, CouName, WillOrdorFROM StuCou, Student, CourseWHERE StuCou. StuNo=Student. StuNo AND StuCou. CouNo=Course. CouNoORDER BY StuName, Will OrderGO使用ANSI连接语法的SELECT语句：USE XkGOSELECT StuNamc, CouName, Wi11Order FROM StuCouJOIN Student ON StuCou. StuNo=Student. StuNoJOIN Course ON StuCou. CouNo二Course. CouNoORDER BY StuName, Will OrderGO【例2. 35USE XkGOSELECT StuName, CouName, TeacherFROM StuCou,Student, Course, DepartmentWHERE StuCou. StuNo=Student. StuNo AND StuCou. CouNo=Course. CouNoAND Course. DepartNo二Department. DepartNoAND Depart Name LIKE 1计算机应用工程系GO【例2. 36USE XkGOSELECT ClassName AS 班级 *, CouName AS 课程名，Kind AS 课程类别,Credit AS 学分,Teacher AS 教师,SchoolTime AS 上课时间WillNum AS 报名人数FROM Class, CourseWHERE Course. DepartNoOClass. DepartNoGO【例2. 37USE XkGOSELECT DISTINCT Cl. CouNo AS 课程编号,CL CouName AS 课程名称，Cl. Kind AS 课程类别Cl. DepartNo AS 系部编号FROM Course Cl, Course C2WHERE Cl.Kind=C2. Kind AND Cl. DepartNoOC2. DepartNoORDER BY课程编号GO【例2. 38一使用左外连接完成，注意Course表的位置USE XkGOSELECT Course. CouNo, CouName, StuNoFROM Course LEFT JOIN StuCouON Course. CouNo二StuCou. CouNoGO使用右外连接完成，注意Course表的位置USE XkGOSELECT Course. CouNo, CouName, StuNoFROM StuCou RIGHT JOIN CourseON Course. CouNo=StuCou. CouNoGO【例2. 39USE XkGOSELECT Course. CouNo, CouName, StuNoFROM Course RIGHT JOIN StuCouON Course. CouNo=StuCou. CouNo(；0【例2. 40USE XkGOSELECT Course. CouNo, CouName, StuNoFROM Course FULL JOIN StuCouON Course. CouNo=StuCou. CouNoGO【例2. 41USE XkGOSELECT StuName, CouName, Wi1lOrderFROM StuCou SC, Student S,Course CWHERE SC. StuNo=S. StuNo AND SC. CouNo=C. CouNoORDER BY StuNamc, WillOiderGO任务4子查询【例2. 29USE XkGOSELECT *FROM CourseWHERE WillNum(SELECT AVG(WillNum) FROM Course)GO【例2. 30USE XkGOSELECT ClassName FROM ClassUNIONSELECT DepartName FROM DepartmentGO如果希望改变列标题USE XkGOSELECT ClassName AS 所有班名和所有系名FROM ClassUNIONSELECT DepartName FROM DepartmentGO【例2. 31USE XkGOSELECT ClassName AS 所有班名和所有系名FROM ClassUNIONSELECT DepartName FROM DepartmentORDER BY ClassName DESCGO