数据库管理与开发项目教程---教案--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. 32]USE XkGOSELECT ClassNo, ClassName, Class. DepartNo, DepartNameFROM Class, Department
WHERE Class. DepartN。二Department. DepartNoGO—【例2. 33]
USE XkGOSELECT Student. *, ClassNameFROM Class, StudentWHERE Class. ClassNo二Student. ClassNo GO
-使用ANSI连接语法的SELECT语句：
USE XkGOSELECT Student. *, ClassNameFROM Class JOIN StudentON Class. ClassNo=Student. ClassNoGO—【例2. 34]USE Xk
GOSELECT 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. 35]
USE 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. 36]
USE Xk
GO
SELECT ClassName AS '班级 *, CouName AS '课程名'，Kind AS '课程类别',
Credit AS '学分',Teacher AS '教师',
SchoolTime AS '上课时间\WillNum AS '报名人数’
FROM Class, Course
WHERE Course. DepartNoOClass. DepartNo
GO
—【例2. 37]
USE Xk
GO
SELECT DISTINCT Cl. CouNo AS 课程编号,CL CouName AS 课程名称，
Cl. Kind AS '课程类别\Cl. DepartNo AS '系部编号'
FROM Course Cl, Course C2
WHERE Cl.Kind=C2. Kind AND Cl. DepartNoOC2. DepartNo
ORDER BY课程编号
GO
【例2. 38]
一使用左外连接完成，注意Course表的位置
USE Xk
GO
SELECT Course. CouNo, CouName, StuNo
FROM Course LEFT JOIN StuCou
ON Course. CouNo二StuCou. CouNo
GO
—使用右外连接完成，注意Course表的位置
USE Xk
GO
SELECT Course. CouNo, CouName, StuNo
FROM StuCou RIGHT JOIN Course
ON Course. CouNo=StuCou. CouNo
GO
—【例2. 39]
USE Xk
GO
SELECT Course. CouNo, CouName, StuNo
FROM Course RIGHT JOIN StuCou

ON Course. CouNo=StuCou. CouNo(；0—【例2. 40]USE XkGOSELECT Course. CouNo, CouName, StuNoFROM Course FULL JOIN StuCouON Course. CouNo=StuCou. CouNo
GO—【例2. 41]USE XkGOSELECT StuName, CouName, Wi1lOrderFROM StuCou SC, Student S,Course CWHERE SC. StuNo=S. StuNo AND SC. CouNo=C. CouNoORDER BY StuNamc, WillOi'der
GO任务4子查询—【例2. 29]USE XkGOSELECT *FROM CourseWHERE WillNum>(SELECT AVG(WillNum) FROM Course)
GO
【例2. 30]USE XkGOSELECT ClassName FROM ClassUNIONSELECT DepartName FROM DepartmentGO—如果希望改变列标题
USE XkGOSELECT ClassName AS '所有班名和所有系名'FROM ClassUNION
SELECT DepartName FROM Department
GO
【例2. 31]
USE Xk
GO
SELECT ClassName AS '所有班名和所有系名'FROM Class
UNION
SELECT DepartName FROM Department
ORDER BY ClassName DESC
GO