select查询语句(3)(4)

23
1. 使用IN或NOT IN关键字 多值子查询是指子查询返回的不是一行而是 一组行数据。则可使用IN 或NOT IN和其外部 查询相联系。IN表示属于的关系，即是否在 所选数据集合之中。NOT IN则表示不属于集 合或不是集合的成员。
17
【例】用UNION子句将student表中学生的学号、姓名及 teacher表中教师号、教师姓名组合在一个结果集中。 USE jwgl GO SELECT student_id , student_name FROM student UNION SELECT teacher_id , teacher_name FROM teacher
11
[例3-49] 查询所有选课学生的学号、姓名、选课名称 及成绩。
SELECT S.SNo,SN,CN,Score FROM S,C,SC WHERE S.SNo=SC.SNo AND SC.CNo=C.CNo
[例3-50] 查询每门课程的课程名、任课教师姓名及其 职务、选课人数。
SELECT CN,TN,Prof,COUNT(SC.SNo) FROM C,T,TC,SC WHERE T.TNo=TC.TNo AND C.CNo=TC.CNo AND SC.CNo=C.CNo GROUP BY SC.CNo
本节首页
9
JION的分类
INNER JOIN 显示符合条件的记录，此为默认值
LEFT（OUTER）JOIN
为左（外）连接，用于显示符合条件的数据行以 及左边表中不符合条件的数据行，此时右边数据 行会以NULL来显示 右（外）连接，用于显示符合条件的数据行以及 右边表中不符合条件的数据行。此时左边数据行 会以NULL来显示 显示符合条件的数据行以及左边表和右边表中不 符合条件的数据行。此时缺乏数据的数据行会以 NULL来显示 将一个表的每一个记录和另一表的每个记录匹配 成新的数据行
16
使用UNION子句
UNION子句的作用是把两个或多个SELECT语句查询的结果 组合成一个结果集。UNION子句的语法形式如下： Select_statement UNION [ALL] Select_statement […n] 使用UNION时，请注意以下4点： • ① UNION中从源表选择的所有列表必须具有相同列数、 相似数据类型和相同的列序。 • ② 列名来自第一个SELECT语句。 • ③ 如果希望整个结果集以特定的顺序出现，则UNION中 应使用ORDER BY子句来指定对结果集的排序顺序。 • ④ 在合并结果时，将从结果集中删除重复行。若使用 ALL，结果集中包含所有的行。
本节首页
18
使用COMPUTE和COMPUTE BY子句
使用COMPUTE和COMPUTE BY就既能浏览数据又看到统 计的结果。 COMPUTE BY子句的语法形式如下： COMPUTE row_aggregate (column_name ) […n] [BY column_name_list] 【例】用COMPUTE子句汇总出student_course表中每个学生 的学号及总成绩。 USE jwgl GO SELECT '学号‘ = student_id , '成绩‘ = grade FROM student_course ORDER BY student_id COMPUTE SUM(grade)
22
单值子查询是指子查询只返回一行数据。可以用“=” 和 其外部查询相联系。 [例3-54] 查询与“刘伟”老师职称相同的教师号、姓名
SELECT TNo,TN FROM T WHERE Prof= ( SELECT Prof FROM T WHERE TN= '刘伟')
使用比较运算符 （=, >, <, >=, <=, !=）
19
COMPUTE类似于总计。如在COMPUTE后加上BY关键 字，则查询的结果为带具体内容的分类统计。 【例】用COMPUTE BY子句按学号汇总出student_course 表中每个学生的的学号及总成绩。 USE jwgl GO SELECT ‘学号‘ = student_id , ’成绩‘ = grade FROM student_course ORDER BY student_id COMPUTE SUM(grade) BY student_id
20
值得注意的是，在使用COMPUTE和COMPUTE BY时， 有如下限制: • ① DISTINCT不允许同集合函数一起用，不能包含text、 ntext、image数据类型。 • ② COMPUTE子句中的列必须在SELECT后面的选择 列表中。 • ③ SELECT INTO不与COMPUTE子句一起使用。 • ④ 若使用了COMPUTE BY，则必须使用ORDER BY。 • ⑤ COMPUTE BY后出现的列必须与ORDER BY后出 现的列相同，或者是它的子集。它必须具有相同的从左 到右顺序并且以相同的表达式开头，不能跳过任何表达 式。
5
⑵ 使用WHERE子句的连接查询语法形式 SQL Server 连接语法形式： SELECT 列名列表 FROM 表1,表2…. WHERE {表1.列名=表2.关联列名,表2.列名=表3.关联 列名,…} [and 查询条件]
6
【例】从student及student_course两个表中检索学生的学号、 姓名、学习课程号及课程成绩。 USE jwgl GO select student.student_id , student.student_name , student_course.course_id , student_course.grade from student , student_course WHERE student.student_id = student_course.student_id
4
【例】从student及student_course两个表中检索学生的学号、 姓名、学习课程号及课程成绩。 USE jwgl GO select student.student_id , student.student_name , student_course.course_id , student_course.grade from student join student_course on student.student_id = student_course.student_id
数据库原理及设计 SQL Server 2005
3.6
数据查询
1
3.6.6 使用T-SQL语句进行高级查询
连接查询 使用UNION子句 使用COMPUTE和COMPUTE BY子句 嵌套查询 在查询的基础上创建新表
本章首页
2
连接查询
• • • • 连接查询：一个查询需要对多个表进行操作 连接字段：数据表之间的联系是通过表的字段值来体现的 连接操作的目的：从多个表中查询数据 表的连接方法 :
SELECT S.SNo,SN,CNo,Score FROM S LEFT OUTER JOIN SC ON S.SNo=SC.SNo
15
连接查询的要点： • ①一般而言，基于主键和外键指定查询条件，连 接条件可使用“主键=外键”。 • ②应尽可能限制连接语句中表的数目，连接的表 越多，查询处理的时间越长。 • ③对于连接表的两个列应有相同或类似的数据类 型。 • ④ 不要使用空值作为连接条件，因为空值计算不 会和其它任何值相等。
8
使用别名 方法一:
use jwgl select s.student_id , s.student_name , s_c.course_id , s_c.grade from student s , student_course s_c WHERE s.student_id = s_c.student_id
10
RIGHT（OUTER） JOIN
Fra Baidu bibliotek
FULL（OUTER）JOIN
CROSS JOIN
• 等值连接与非等值连接
[例3-48] 查询“刘伟”老师所讲授的课程，要求列出 连接条件 ，当比 教师号、教师姓名和课程号。 较运算符为“＝”
时，称为等值连 方法1： 接。其他情况为 SELECT T.TNo,TN,CNo 非等值连接。 FROM T,TC WHERE (T.TNo = TC. TNo) AND (TN='刘伟') 方法2： SELECT T.TNo, TN, CNo FROM T INNER JOIN TC ON T.TNo = TC.TNo WHERE (TN = '刘伟') 引用列名TNo时要加上表名前缀，这是因为两个表中的列名相同， 必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。
13
[例3-52] 检索所有学生姓名，年龄和选课名称。
SELECT SN,Age,CN FROM S,C,SC WHERE S.SNo=SC.SNo AND SC.CNo=C.CNo
14
• 外连接
左外部连接 右外部连接
而在外部连接中，参与连接的表有主从之分，以主表 的每行数据去匹配从表的数据列。 符合连接条件的数据将直接返回到结果集中，对那些 不符合连接条件的列，将被填上NULL值后再返回到结 果集中。 [例3-53] 查询所有学生的学号、姓名、课程号及成绩 （没有选课的同学的选课信息显示为空）。
方法二: select s.student_id , s.student_name , s_c.course_id , s_c.grade from student AS s , student_course AS s_c WHERE s.student_id = s_c.student_id
本节首页
21
嵌套查询
如果先通过一个查询查出一个结果集，再在 这个结果集中进行查询的话就是嵌套查询。 嵌套查询是用一条SELECT语句作为另一条 SELECT语句的一部分。外层的SELECT语句 叫外部查询，内层的SELECT语句叫内部查询 （或子查询）。 嵌套查询的执行流程是，首先执行内部查询， 它查询出来的数据并不被显示出来，而是传 递给外层SELECT语句，作为该SELECT语句 的查询条件使用。子查询可以多层嵌套。
7
【例】从student、course及student_course三个表中检索学 生的学号、姓名、学习课程号、学习课程名及课程成绩。 USE jwgl GO SELECT student.student_id , student.student_name , student_course.course_id , course.course_name , student_course.grade from student , student_course , course WHERE student.student_id = student_course.student_id AND course.course_id = student_course.course_id
表之间满足一定条件的行进行连接时，FROM子句指明进行连接的 表名，WHERE子句指明连接的列名及其连接条件
利用关键字JOIN进行连接：当将JOIN 关键词放于FROM子句中时， 应有关键词ON与之对应，以表明连接的条件
3
1. JOIN ON语句连接查询 ⑴ 使用JOIN ON 语句进行连接查询 SELECT column_list FROM {表1 [join_类型] JOIN 表2 ON 连接条件} [WHERE 查询条件]
12
• 自身连接
[例3-51] 查询所有比“刘伟”工资高的教师姓名、工 资和刘伟的工资。
方法1： SELECT X.TN,X.Sal AS Sal_a,Y.Sal AS Sal_b FROM T AS X ,T AS Y WHERE X.Sal>Y.Sal AND Y.TN='刘伟' 方法2： SELECT X.TN, X.Sal,Y.Sal FROM T AS X INNER JOIN T AS Y ON X.Sal>Y.Sal AND Y.TN='刘伟'