1、实验四 SELECT 语句连接查询一实验目的1. 观察查询结果, 体会 SELECT 语句实际应用。2. 要求学生能够在查询分析器中使用 SELECT 语句进行连接查询。3熟练掌握多个表的数据查询、数据排序和数据连接查询的操作方法。二、实验类型操作型三、实验准备1. 了解简单 SELECT 语句的用法。2. 比较熟悉查询分析器中的 SQL 脚本运行环境。四、实验指导同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词。一般格式： . .比较运算符：=、=、. BETWEEN . AND .连接字段 连接谓词中的列名称为连接字段 连接条件中的各连接字段类型必须是可比的，但不
2、必是相同的1. 两表连接 不带连接谓词的连接 很少使用例 1：查看全体学生选修课程的情况。SELECT 学生.学号 ，学生.姓名，课程.课程编号，课程.课程名，课程.学时，课程.学分,课程.学期，课程.教师编号FROM 学生，课程ORDER BY 学生.学号 asc；2. 等值与非等值连接查询等值连接、自然连接、非等值连接 等值连接连接运算符为 = 的连接操作 . = . 任何子句中引用表 1 和表 2 中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。 自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。例 2 ：查询每个学生及其选修课程的情况。SELECT
3、学生.学号，姓名，性别，出生日期，籍贯，班级编号，课程编号，成绩FROM 学生，成绩WHERE 学生.学号 = 成绩.学号； 非等值连接查询连接运算符 不是 = 的连接操作.比较运算符：、=、. BETWEEN . AND .3. 自身连接 一个表与其自己进行连接，称为表的自身连接 需要给表起别名以示区别 由于所有属性名都是同名属性，因此必须使用别名前缀例 3：查看和“王雪”籍贯相同的学生情况。SELECT SECOND.学号， SECOND.姓名，SECOND.性别， SECOND.籍贯FROM 学生 FIRST，学生 SECONDWHERE FIRST.姓名=王雪 and FIRST.籍贯
4、 = SECOND.籍贯；4. 外连接（Outer Join） 外连接与普通连接的区别 普通连接操作只输出满足连接条件的元组 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出格式： LEFT （OUTER） JOIN：显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以 NULL 来显示，此称为左外连接； RIGHT （OUTER） JOIN：显示符合条件的数据行以及右边表中不符合条件的数据行，此时左边数据行会以 NULL 来显示，此称为右外连接； FULL （OUTER） JOIN：显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据
5、的数据行会以 NULL 来显示；当将 JOIN 关键词放于 FROM 子句中时，应有关键词 ON 与之相对应，以表明连接的条件。 左外连接：LEFTOUTER JOIN将左表的所有记录分别与右表的每一条记录进行连接组合，结果集中除显示符合条件的数据行以外，还显示左边表中不符合条件的数据行，此时右边数据行会以 NULL 来显示。例 4： 查询每个学生及其选修课程的情况包括没有选修课程的学生SELECT 学生.学号，姓名，性别，出生日期，籍贯，课程编号，成绩FROM 学生right outer join 成绩 on 学生.学号=成绩.学号; 右外连接： RIGHT OUTER JOIN将右表的所有
6、记录分别与左表的每一条记录进行连接组合，结果集中除显示符合条件的数据行以外，还要显示右边表中不符合条件的数据行，此时左边数据行会以 NULL 来显示。 全外连接：FULLOUTER JOIN显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据的数据行会以 NULL 来显示。5. 复合条件连接 当查询中涉及多个条件（连接条件、查询条件）时，称为复合条件连接 WHERE 子句中含多个连接条件例 5：查询选修”02-01“号课程且成绩在 90 分以上的所有学生的学号、姓名、成绩SELECT 学生.学号, 学生.姓名FROM 学生, 成绩WHERE 学生.学号 = 成绩.学号 AN
7、D /* 连接谓词*/成绩.课程编号= 02-01 AND /* 其他限定条件 */成绩.成绩 90； /* 其他限定条件 */五、实验内容本次实验中用到的数据表如下：表 4.1 学生表 字段名称 类 型 宽 度 允许空值 主 键学号 char 10 NOT NULL 是姓名 char 8 NOT NULL性别 char 2 NULL出生日期 smalldatetime 4 NULL籍贯 char 20 NULL班级编号 char 10 Null表 4.2 课程表字段名称 类 型 宽 度 允许空值 主 键课程编号 char 10 NOT NULL 是课程名称 char 20 NOT NULL学
8、时 tinyint 1 NULL学分 tinyint 1 NULL开课学期 tinyint 1 NULL表 4.3 成绩表字段名称 类 型 宽 度 允许空值 主 键学号 char 8 NOT NULL 是课程编号 char 10 NOT NULL 是成绩 tinyint 1 NULL说明：先创建一个“学生信息管理系统”数据库，并创建所用到的数据表。create table 学生表( 学号 char(10) primary key,姓名 char(8) not null,性别 char(2),出生日期 smalldatetime, 籍贯 char(20),班级编号 char(10)create
9、table 课程表( 课程编号 char(10) primary key,课程名称 char(20) not null,学时 tinyint,学分 tinyint,开课学期 tinyint)create table 成绩表(学号 char(10),课程编号 char(10),成绩 tinyint,primary key(学号,课程编号),foreign key(学号) references 学生表(学号),foreign key(课程编号) references 课程表(课程编号)往“学生表”里插入数据：insert into 学生表 values(060101,江鑫,男,1988-01-09
10、,内蒙古,A1010601)insert into 学生表 values(060102,赵盘,男,1988-02-09,北京,A1010601)insert into 学生表 values(060103,刘鹏,男,1988-03-08,北京,A1010601)insert into 学生表 values(060104,刘鑫,女,1988-04-09,上海,A1010601)insert into 学生表 values(060201,罗旭,女,1988-01-09,海南,A1010602)insert into 学生表 values(060202,白涛,男,1988-05-09,上海,A1010
11、602)insert into 学生表 values(060203,邓平,女,1988-06-09,吉林,A1010602)insert into 学生表 values(060204,周康,男,1988-03-09,吉林,A1010602)往“课程表”里插入数据：insert into 课程表 values(01-01,经济法,54,3,1)insert into 课程表 values(01-02,数据库技术,48,2,4)insert into 课程表 values(01-03,网络营销,64,2,5)insert into 课程表 values(01-04,统计学,64,4,2)inser
12、t into 课程表 values(02-01,营销策划,48,3,4)insert into 课程表 values(02-02,大学英语,64,3,2)insert into 课程表 values(02-03,高等数学,72,4,1)往“成绩表”里插入数据：insert into 成绩表 values(060101,01-01,97)insert into 成绩表 values(060101,01-02,null)insert into 成绩表 values(060101,01-03,97)insert into 成绩表 values(060101,01-04,95)insert into
13、成绩表 values(060102,01-01,84)insert into 成绩表 values(060102,01-02,85)insert into 成绩表 values(060102,01-03,76)insert into 成绩表 values(060102,01-04,null)1）查询每个学生的情况以及所选修的课程。2）求学生的学号、姓名、选修的课程名及成绩。3）求选修“01-01”课程且成绩在 90 分以上的学生学号、姓名、课程名称及成绩。4）查询选修但没参加考试(缺成绩)的学生姓名、课程名称及学分。5) 查询选修了 3 门以上课程的学生姓名及平均成绩(不统计不及格课程),并按平均成绩降序排列。说明：需写实验报告(4)。
