C语言--查询程序.ppt

1、第六章 数据查询,Chapter 6 Query,第6章 数据查询,6.1 基本查询 6.1.1 选择表中的若干列 6.1.2 带条件查询 6.1.3 查询结果处理 6.1.4 分组统计与筛选 6.1.5 使用COMPUTE子句汇总 6.2 嵌套查询 6.2.1单值嵌套查询 6.2.2多值嵌套查询,6.3 连接查询 6.3.1内连接查询 6.3.2外连接查询 6.3.3交叉连接查询 6.4 小结,查询语法,SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ； SELECT子句：指定要显示的属性列 FROM子

2、句：指定查询对象(基本表或视图) WHERE子句：指定查询条件 GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。 HAVING短语：筛选出只有满足指定条件的组 ORDER BY子句：对查询结果按指定列值的升序或降序排序,6.1 基本查询格式,语法格式： SELECT ALL|DISTINCT TOP n PERCENT select_list FROM table_name select_list：所要查询的选项的集合，多个选项之间用逗号分开。算术表达式、字符串常量、函数、列别名等。 table_name：要查询的表。 ALL：表示输出

3、所有记录，包括重复记录。 DISTINCT：表示输出记录中不包括重复记录，例6-1- TOP n PERCENT : 使用聚集函数,选择若干列举例,例6-1-1 查询员工姓名及住址 SELECT employee_name,address FROM employee 例6-1 查询员工所有信息 SELECT * FROM employee 例6-1-2 查询员工姓名及年龄 SELECT employee_name, YEAR(GETDATE()-YEAR(birth_date) FROM employee 例6-1-3 查询员工姓名及年龄 SELECT employee_name, 年龄, Y

4、EAR(GETDATE()-YEAR(birth_date) FROM employee,基本查询举例(续),例6-1-4查询员工姓名及年龄,并为每列起适当的标题 SELECT employee_name AS 姓名, YEAR(GETDATE()-YEAR(birth_date) AS 年龄 FROM employee 例6-1-5 查询前两个员工姓名及住址 SELECT TOP 2 employee_name,address FROM employee 例6-1-6查询前20%员工信息 SELECT TOP 20 PERCENT * FROM employee,基本查询举例(续2),例6-

5、1-7 查询员工的姓氏 SELECT ALL LEFT(employee_name,1), FROM employee 例6-1-8 查询员工的姓氏(重复的只列一次) SELECT DISTINCT LEFT(employee_name,1) FROM employee,使用集函数(Aggregate Functions),7类主要集函数： (1) 计数 COUNT（DISTINCT|ALL *） COUNT（DISTINCT|ALL ） (2) 计算总和 SUM（DISTINCT|ALL ） (3) 计算平均值 AVG（DISTINCT|ALL ） (4) 求最大值 MAX（DISTINCT

6、|ALL ） (5) 求最小值 MIN（DISTINCT|ALL ） (6) 求标准差 STDEV（DISTINCT|ALL ） (7) 求方差 VAR（DISTINCT|ALL ）,DISTINCT： 在计算时要取消指定列中的重复值 ALL： 不取消重复值 ALL为缺省值,使用集函数（续）,例 查询学生总人数。,SELECT COUNT(*) FROM Student；,例 查询选修了课程的学生人数。,注：用DISTINCT以避免重复计算学生人数,查询结果： count_Sno 5,查询结果： count_Sno ,SELECT COUNT(Sno) FROM SC；,SELECT COUN

7、T(Distinct Sno) FROM SC；,基本查询举例(续),例6-3 对employee表，分别查询公司的员工总数和公司员工的平均收入。 SELECT COUNT(*) AS 总数 FROM employee SELECT AVG(wages) AS 平均收入 FROM employee 等价于 SELECT COUNT(*) AS 总数, AVG(wages) AS 平均收入 FROM employee,带条件查询,语法格式： WHERE search_condition,带条件查询（续1）,例6-4 对employee表，列出月工资在2000以上的员工记录。 SELECT * F

8、ROM employee WHERE wages2000 例6-5 对employee表，求出男员工的平均工资。 SELECT AVG(wages) as 平均工资 FROM employee WHERE sex=男,带条件查询（续2）,使用谓词 IN , NOT IN ：用逗号分隔的一组取值 例6-6-1 对employee表，列出D001和D002部门的员工名单。 SELECT department_id, employee_name FROM employee WHERE department_id IN (D001, D002) 等价于 WHERE (department_id = D

9、001) OR (department_id = D002),带条件查询（续3）,确定范围：BETWEENAND NOT BETWEENAND 例6-7 对employee表，列出月工资在2000到3000之间的员工名单。 SELECT employee_name ,wages FROM employee WHERE wages BETWEEN 2000 AND 3000 等价于 WHERE wages=2000 AND wages=3000,带条件查询（续4）,NOT LIKE ：指定匹配模板。 匹配模板：固定字符串或含通配符的字符串。 当匹配模板为固定字符串时，可以用 = 运算符取代LIK

10、E 谓词，用 != 或 运算符取代 NOT LIKE 谓词。 % (百分号)：代表任意长度（长度可以为0）的字符串 _ (下横线)：代表任意单个字符 例6-8 对employee表，列出所有的姓“张”的员工名单。 SELECT employee_name FROM employee WHERE employee_name LIKE 张% 语句中的WHERE子句还有等价的形式： WHERE LEFT(employee_name,1)= 张,带条件查询（续4）,例6-8-1 对employee表，列出所有的第二个字为文的员工名单。 SELECT employee_name FROM employe

11、e WHERE employee_name LIKE _文% 涉及空值的查询 使用谓词 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替 例6-9 对employee表，列出所有工资为空值的员工编号和姓名。 SELECT employee_id,employee_name FROM employee WHERE wages IS NULL,查询结果处理,排序输出(ORDER BY) 2. 重定向输出(INTO) 3. 输出合并(UNION) 4. 分组统计(GROUP BY)与筛选(HAVING) 5. 使用COMPUTE和COMPUTE BY子句

12、汇总,查询结果处理ORDER,1. 排序输出(ORDER BY) 语法格式： ORDER BY order_by_expression1ASCE|DESC ,order_by_expression2ASCE|DESC , 例6-10-1 对employee表，按性别顺序列出员工的编号、姓名、性别、部门编号及工资。 SELECT employee_id,employee_name,sex,department_id,wages FROM employee ORDER BY sex,查询结果处理（续1）,例6-10 对employee表，按性别顺序列出员工的编号、姓名、性别、部门编号及工资，性别相

13、同的再先按部门后按工资由高到低排序。 SELECT employee_id,employee_name,sex,department_id,wages FROM employee ORDER BY sex,department_id,wages DESC,查询结果处理（续2）,2. 重定向输出(INTO) 语法格式： INTO new_table 例6-11-1 对部门表department和员工表employee,查询出“D001”部门的所有员工的信息，并将结果存入testtable表中。 SELECT employee.* INTO testtable FROM employee WHER

14、E department_id = D001,查询结果处理（续3）,3. 输出合并(UNION) 语法格式： UNION ALL 合并的规则是： (1)不能合并子查询的结果。 (2)两个SELECT语句必须输出同样的列数。 (3)两个表各相应列的数据类型必须相同，数字和字符不能合并。 (4)仅最后一个SELECT语句中可以用ORDER BY子句，且排序选项必须依据第一个SELECT列表中的列。,查询结果处理（续4）,例6-12 对employee表，列出部门编号为“D001”或“D002”的所有员工姓名。 SELECT employee_name,department_id FROM empl

15、oyee WHERE department_id=D001 UNION SELECT employee_name,department_id FROM employee WHERE department_id=D002,分组统计,4. 分组统计(GROUP BY)与筛选(HAVING) GROUP BY group_by_expression1 ,group_by_expression2, 作用：细化集函数的作用对象 未对查询结果分组，集函数将作用于整个查询结果 对查询结果分组后，集函数将分别作用于每个组 分组方法：按指定的一列或多列值分组，值相等的为一组 使用GROUP BY子句后，SELE

16、CT子句的列名列表中只能出现分组属性和集函数,分组统计（续1）,例6-13 对employee表，分别统计男女员工人数。 SELECT sex,COUNT(sex) as 人数 FROM employee GROUP BY sex 例6-14 对employee表，分别统计各部门男女员工的人数。 SELECT department_id, sex,COUNT(*) as 人数 FROM employee GROUP BY department_id,sex,Having子句,只有满足HAVING短语指定条件的组才输出 HAVING短语与WHERE子句的区别：作用对象不同 WHERE子句作用于基

17、表或视图，从中选择满足条件的元组。 HAVING短语作用于组，从中选择满足条件的组。 例6-15 对employee表，列出部门平均工资大于2000的部门编号。 SELECT department_id,AVG(wages) AS 平均工资 FROM employee GROUP BY department_id HAVING AVG(wages)=2000,GROUP BY使用注意事项,使用了分组的查询语句，其SELECT子句中只能出现分组属性和集函数，而不能有其他属性。 原因在于SELECT子句是对分组后的结果集进行输出，参与分组的属性在同一组中取相同值，而不参与分组的属性(不在GROUP

18、 BY子句中出现的属性)则可能取不同的值，分组输出通常是对组的整体描述，而不是组内的详细记录，不参与分组的属性取值不同，故无法输出。 SELECT sex,department_id,COUNT(sex) FROM employee GROUP BY sex,汇总查询,5. 使用COMPUTE和COMPUTE BY子句汇总 语法格式： COMPUTE row_aggregate(column_name),row_aggregate(colornn_name). BY column_name, column_name. . . 例6-16 对employee表中部门编号为“D001”的员工工资，

19、生成汇总行。 SELECT department_id, wages FROM employee WHERE department_id = D001 ORDER BY department_id COMPUTE sum(wages),汇总查询（续1）,例6-17 对employee表中部门编号为“D001”或“D002”的员工工资，按照其部门编号生成分组汇总行和明细行。 SELECT department_id, wages FROM employee WHERE department_id = D001 OR department_id = D002 ORDER BY department

20、_id COMPUTE sum(wages) BY department_id,补充案例（1）,例1 查询全体学生的姓名及其所在的系,SELECT,结果： sname Sdept - - 2005001 CS 2005002 IS 2005003 MA 2005004 IS,Sname，Sdept,FROM,Student,补充案例（2）,例2 查询全体学生的详细记录。 SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或 SELECT */表示将表中的列全部按序输出 FROM Student； 例3 查全体学生的姓名及其出生年份。 SELECT S

21、name,year(getdate()Sage FROM Student；,输出结果： Sname 2007-Sage - - 李勇 1986 刘晨 1987 王敏 1988 张立 1987,补充案例（3）,例4 查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所在系名。 SELECT Sname，Year of Birth:，year(getdate()-Sage，ISLOWER(Sdept) FROM Student,输出结果： Sname Year of Birth: Exp_1 LOWER(Sdept) - - - - 李勇 Year of Birth: 1976 cs 刘晨

22、Year of Birth: 1977 is 王敏 Year of Birth: 1978 ma 张立 Year of Birth: 1977 is,补充案例（4）,例5查询全体学生的姓名、出生年份和所在系，并将输出字段依次更名为：NAME、BIRTH、BIRTHDAY、DEPARTMENT。 SELECT Sname NAME，Year of Birth: BIRTH， Year(getdate()-age BIRTHDAY， LOWER(Sdept) DEPARTMENT FROM Student；,输出结果： NAME BIRTH BIRTHDAY DEPARTMENT - - - -

23、李勇 Year of Birth: 1986 cs 刘晨 Year of Birth: 1987 is 王敏 Year of Birth: 1988 ma 张立 Year of Birth: 1987 is,补充案例（5）,例7 查询计算机系全体学生的信息。 SELECT * FROM Student WHERE Sdept=CS; 例8 查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT Sname，Sage FROM Student WHERE Sage = 20；,补充案例（6）,例9 查询年龄不在2023岁之间的学生姓名、系别和年龄。 SELECT Sname, Sdept,

24、Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23； 例10 查询不是信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。 SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ( IS, MA, CS )；,补充案例（7）,例11 查询姓欧阳且全名为三个汉字的学生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 欧阳_； 例12 查询所有不姓刘的学生姓名。 SELECT Sname FROM Student WHERE Sname

25、NOT LIKE 刘%；,补充案例（8）,例13 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno, Cno FROM SC WHERE Grade IS NULL； 例14 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage20；,补充案例（9）,例15 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。,查询结果： Sno Sname Ssex Sage Sdept - - - - - 200

26、5001 李勇 男 17 CS 2005002 刘晨 女 19 IS 2005004 张立 男 18 IS 2005003 王敏 女 18 MA,查询结果： Sno Sname Ssex Sage Sdept - - - - - 2005001 李勇 男 17 CS 2005003 王敏 女 18 MA 2005004 张立 男 18 IS 2005002 刘晨 女 19 IS,SELECT * FROM Student； ORDER BY Sdept, Sage DESC,SELECT * FROM Student； ORDER BY Sage , Sdept DESC,补充案例（10）,例

27、16 求各个课程号及相应的选课人数。 SELECT Cno, COUNT(Sno) FROM SC GROUPBY Cno ;,查询结果： Cno COUNT(Sno) 1 1 2 2 3 2,例17 查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3；,补充案例（11）,例18 查询有3门以上课程是90分以上的学生的学号及(90分以上的)课程数。 SELECT Sno, COUNT(*) FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3;,第六章 数据

28、查询,基本查询回顾 查询的基本语句： SELECT ALL|DISTINCT | | | AS , FROM WHERE GROUP BY HAVING ORDER BY ASC | DESC 特别注意： 集函数只能用于 SELECT子句和 HAVING短语之中，而绝对不能出现在 WHERE子句中。 WHERE子句用于对查询的表限定，而 HAVING短语用于对结果集中的各分组进行限定。,嵌套查询,一个SELECT-FROM-WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 SELECT employee_name/ 外层查

29、询/父查询 FROM employee WHERE department_id IN ( SELECT department_id / 内层查询/子查询 FROM department WHERE department_name= 市场部）； 子查询的限制不能使用ORDER BY子句 层层嵌套方式反映了SQL语言的结构化 有些嵌套查询可以用连接运算替代,6.2.1单值嵌套查询,子查询的返回结果是一个值的嵌套查询称为单值嵌套查询。 比较运算符：（，=，）。 例6-18 对Sales数据库，列出“市场部”的所有员工的编号。 确定“市场部”的部门编号 (SELECT department_id FR

30、OM department WHERE department_name=市场部) 结果为department_id D002 查找所有在D002部门的员工编号。 SELECT employee_id FROM employee WHERE department_id=D002,Employee表,Department 表,6.2.1单值嵌套查询（续）,SELECT employee_id FROM employee WHERE department_id=D002,(SELECT department_id FROM department WHERE department_name=市场部),

31、上述分步查询可改写成如下嵌套查询,单值嵌套查询举例,例6-18-1查询工资比D001部门人员最高工资还要高的职工名单,SELECT employee_name FROM Employee WHERE wages ,( SELECT MAX（wages） FROM employee WHERE deparment_id D001）,首先在employee关系中找出D001部门最高工资；,然后在employee关系中取出employee_name。,查询执行过程：,思考：例6-18-2 查询工资比市场部工资最高的人还要高的职工名单,Employee表,单值嵌套查询举例（2）,例6-18-2查询工资

32、比市场部人员最高工资还要高的职工名单,SELECT employee_name FROM Employee WHERE wages ,( SELECT MAX（wages） FROM employee WHERE deparment_id ,(SELECT department_id FROM employee WHERE department_name=市场部) ;, 首先在department关系中找出“市场部”的员部门号；, 然后在employee关系中找出市场部最高工资；, 最后在employee关系中取出employee_name。,查询执行过程：,Employee表,Departm

33、ent表,6.2.2 多值嵌套查询,子查询的返回结果是一列值的嵌套查询称为多值嵌套查询。 IN运算符 ALL运算符 ANY运算符 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值 = ANY大于等于子查询结果中的某个值 = ALL大于等于子查询结果中的所有值 ）ANY不等于子查询结果中的某个值（相当于NOT IN ） !=（或）ALL不等于子查询结果中的任何一个值,IN运算符的用法,例6-21 对Sales数据库，列出部门为“市场部”或“销售部”的所有员工的编号。 SELECT employee_id FROM employee WHERE department_id IN (S

34、ELECT department_id FROM department WHERE department_name=市场部 OR department_name=销售部),Employee表,Department表,ANY运算符的用法,例6-19 对Sales数据库，列出D001号部门中工资比D002号部门的员工最低工资高的员工编号和工资。 SELECT employee_id,wages FROM employee WHERE department_id=D001 AND wagesANY (SELECT wages FROM employee WHERE department_id=D00

35、2),Employee表,ALL运算符的用法,例6-20 对Sales数据库，列出部门编号为“D001”的员工，这些员工的工资比部门为“D002”的员工的最高工资还要高的员工的编号和工资。 SELECT employee_id,wages FROM employee WHERE department_id=D001 AND wagesALL (SELECT wages FROM employee WHERE department_id=D002),Employee表,带有IN谓词的子查询(续4),Student表,SC表,COURSE表,补充案例,查询与“刘晨”在同一个系学习的学生。 查询选修

36、了课程名为“数据库”的学生学号和姓名。 查询其他系中比信息系某些学生年龄小的学生姓名和年龄。 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。,带有IN谓词的子查询(续3),例38 查询选修了课程名为“数据库”的学生学号和姓名。,SELECT Sno, Sname FROM Student WHERE Sno IN,( SELECT Sno FROM SC WHERE Cno ,( SELECT Cno FROM Course WHERE Cname= 数据库 ) ) ;, 首先在Course关系中找出“数据库”的课程号；, 然后在SC关系中找出选修了该课程的学生学号；, 最后在Stud

37、ent关系中取出Sno和Sname。,查询执行过程：,二、带有比较运算符的子查询,当能确切知道内层查询返回单值时，可用比较运算符（，=，）。 例37 查询与“刘晨”在同一个系学习的学生。 假设一个学生只可能在一个系学习，并且必须属于一个系，则在例37可以用 = 代替IN ： SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname= 刘晨 )；,结论：是不相关子查询,带有比较运算符的子查询(续1),注：子查询一定要跟在比较符之后！ 错误的例子： SELECT Sno，

38、Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 刘晨 ) = Sdept； 原因：数据库中的属性名应当以变量来看待，而子查询返回的是一个数值(单元素的集合)。,谓词子查询举例,例39 查询其他系中比信息系某些学生年龄小的学生姓名和年龄。 SELECT Sname, Sage FROM Student WHERE Sage IS ; /这是父查询块中的条件 执行过程 1. DBMS执行此查询时，首先处理子查询，找出IS系中所有学生的年龄，构成一个集合(19，18) 2. 处理父查询，找所有不是IS系且

39、年龄小于19 或 18的学生 结论：是不相关子查询,谓词子查询举例2,例39 查询其他系中比信息系某些学生年龄小的学生姓名和年龄。 使用集函数来实现，即其他系中比信息系最大年龄小的学生的姓名和年龄。 SELECT Sname, Sage FROM Student WHERE Sage IS ;,谓词子查询举例3,例40 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。 方法一：用ALL谓词 SELECT Sname, Sage FROM Student WHERE Sage IS ; 方法二：用集函数 SELECT Sname, Sage FROM Student WHERE Sage

40、IS ;,连接查询,连接查询将两个(以上)表连接进行查询 同时涉及多个表的查询,一般格式： WHERE. . FROM 表名1 表名2 ON . .,连接字段 连接谓词中的列名称为连接字段 连接条件中的各连接字段类型必须是可比的，但不必是相同的,连接查询(续1),连接操作的执行过程 (1) 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。 (2) 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一

41、个元组。 (3) 重复上述操作，直到表1中的全部元组都处理完毕,连接查询,内连接 等值连接 自然连接 非等值连接 自身连接 外连接 左外连接 右外连接 全外连接 交叉连接,二、等值连接与非等值连接查询,等值连接:连接运算符为 = 的连接操作,按对应列的共同值将一个表中的记录与另一个表中的记录相连接，包括其中的重复列。 非等值连接:连接运算符为 非= 的连接操作 例31 查询每个学生及其选修课程的情况。 SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno = SC.Sno； /等值连接 或 SELECT Student.Sno, S

42、name, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno = SC.Sno； /自然连接 注： WHERE子句中参与比较的两个属性名若在另一表中存在，则要加所属表名作前辍以区别，无同名则可省前辍。,等值连接举例,Student表,SC表,查询结果：,等值连接举例,employee表,Sell_order 表,SELECT employee.employee_id, employee_name, goods_id, order_num FROM employee,sell_order WHERE employee

43、.employee_id = sell_order.employee_id,E002 东方牧 G00004 10,E002 东方牧 G00005 10,E001 钱达理 G00003 5,E001 钱达理 G00001 20,连接 (例2),goods表,Sell_order 表,SELECT sell_order.employee_id, goods_name, goods.goods_id, order_num FROM sell_order,goods WHERE sell_order.goods_id = goods.goods_id,E002 Canon LBP2900 G00005

44、 10,Canon LBP2900 G00005 21,等值连接（续）,例6-22 对Sales数据库输出所有员工的销售单，要求给出员工编号、姓名、商品编号、商品名和销售数量。 SELECT employee.employee_id, employee.employee_name, goods.goods_id, goods.goods_name, sell_order.order_num FROM employee,sell_order,goods WHERE employee.employee_id = sell_order.employee_id and sell_order.goods

45、_id = goods.goods_id,连接 (例2),goods表,Sell_order 表,SELECT employee.employee_id, employee.employee_name, goods.goods_id, goods_name, order_num FROM employee,sell_order,goods WHERE employee.employee_id = sell_order.employee_id and sell_order.goods_id = goods.goods_id,E002 东方牧 G00005 Canon LBP2900 10,emp

46、loyee表,等值连接,例6-23 Sales数据库中部门表department和员工表employee的等值连接。 SELECT * FROM department INNER JOIN employee ON employee.department_id = department.department_id,Employee表,Department 表,不等值连接,SELECT a.empoyee_id，a.googs_id, a.order_num, b.empoyee_id，b.googs_id, b.order_num FROM sell_order a INNER JOIN sel

47、l_order b ON a.order_num b.order_num AND a.goods_id = b.goods_id,a,b,列出销售数量大于其他员工销售该类产品销售数量的那些员工的销售信息。,不等值连接,例6-24 对Sales数据库，列出销售“G00001”产品的员工中，销售数量大于编号为“E001”的员工销售该类产品销售数量的那些员工的编号和销售数量。 SELECT a.employee_id, a.order_num FROM sell_order a INNER JOIN sell_order b ON a.order_num b.order_num AND a.good

48、s_id = b.goods_id WHERE (b.goods_id = G00001) AND (b.employee_id = E001),a,b,SELECT employee_id, order_num FROM sell_order WHERE (goods_id = G00001) AND order_num (SELECT order_num From sell_order Where employee_id = E001 and goods_id = G00001 ),是否可用嵌套？,自然连接,例6-25 Sales数据库中部门表department和员工表employee的

49、自然连接。 SELECT a.department_name, b.* FROM department a INNER JOIN employee b ON b.department_id = a.department_id,Employee表,Department 表,自身连接,一个表与其自己进行连接 例32 查询每一门课的间接先修课（即先修课的先修课）。,First,Second,SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno ;,自身连接(注意问题),例3

50、3 查询每一门课的间接先修课（即先修课的先修课）。 SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno ; 注： (1) 一个表与其自己进行连接，称为表的自身连接 (2) 需要给表起别名以示区别 (3) 由于所有属性名都是同名属性，因此必须使用别名前缀,外连接（Outer Join）,外连接与内连接的区别 内连接操作只输出满足连接条件的元组 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出,Student表,SC表,外连接2,外连接的构建 在表名

51、后面加外连接操作符指定非主体表 Left Outer Join、Right Outer Join 、Full Join 非主体表有一“万能”的虚行，该行全部由空值组成 虚行可以和主体表中所有不满足连接条件的元组进行连接 由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值,外连接举例,例34-1 查询每个学生及其选修课程的情况(含未选课的学生信息)。 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student Left Outer Join SC On Student.Sno = SC.S

52、no 例34-2 查询每个学生及其选修课程的情况(含未注册学生的选课信息)。 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student Right Outer Join SC On Student.Sno = SC.Sno,右外连接举例,Student表,SC表,右外连接：Student SC,使用左外连接,左外连接通过左向外连接引用左表的所有行。 例6-26 员工信息表employee左外连接销售信息表sell_order。 SELECT a.employee_id, a.employee_name, b.go

53、ods_id, b.order_num, b.send_date FROM employee a LEFT OUTER JOIN sell_order b ON a.employee_id = b.employee_id,E005 张明华 NULL NULL NULL,E004 肖海燕 NULL NULL NULL,E003 郭文斌 NULL NULL NULL,E002 东方牧 G00004 10 2005-8-5,E002 东方牧 G00005 10 2005-8-7,E001 钱达理 G00003 5 2005-7-5,E001 钱达理 G00001 20 2005-7-4,a,b,使用

54、右外连接,例6-27 员工信息表employee右外连接销售信息表sell_order。 为了说明方便，先在sell_order表中插入一条销售信息。 INSERT INTO sell_order (order_id1, goods_id, employee_id, customer_id, transporter_id, order_num, discount, order_date, send_date, arrival_date, cost) VALUES (S00006, G00005, , C0006, T002, 21, 0.5, GETDATE(), GETDATE(), GET

55、DATE(),100) SELECT a.employee_id, a.employee_name, b.goods_id, b.order_num, b.send_date, b.order_id1 FROM employee a RIGHT OUTER JOIN sell_order b ON a.employee_id = b.employee_id,右外连接例子,SELECT a.employee_id, a.employee_name, b.goods_id, b.order_num FROM employee a RIGHT OUTER JOIN sell_order b ON a

56、.employee_id = b.employee_id,a,b,使用全外连接,全外连接返回两个表的所有行。不管两个表的行是否满足连接条件，均返回查询结果集。对不满足连接条件的记录，另一个表相对应字段用NULL代替。 例6-28 员工信息表employee全外连接销售信息表sell_order。 SELECT a.employee_id, a.employee_name, b.goods_id, b.order_num, b.send_date, b.order_id1 FROM employee a FULL OUTER JOIN sell_order b ON a.employee_id

57、= b.employee_id,交叉连接,不带连接谓词的连接 很少使用 例： SELECT Student.* , SC.* FROM Student Cross join SC 或 SELECT Student.* , SC.* FROM Student , SC,student表,SC表,交叉连接结果,交叉连接,41-3 SELECT a.department_id, a.department_name, b.employee_id, b.employee_name FROM department a CROSS JOIN employee b,Employee表,Department 表,查询综合举例及注意事项,一、别名的使用 (1) 别名用于对输出属性列的重命名 例：查询全体学生的姓名、和所在系，并将输出字段依次更名为：姓名、出生年份、所在系。 SELECT Sname 姓名，year(date()-Sage 出生年份， ISLOWER(Sdept) 所在系 FROM Student； (2) 别名用于自身连接查询和对同一表的相关子查询中，用于区别对同一表的不同引用 例：查询每一门课的间接先修课（即先修课的先修课）。 SELECT FIRST