SQL 数据查询-连接查询

zhangwuyan

贡献于2014-08-17

字数:0 关键词: SQL

2010年10月27日 数据库系统概论 第三章 关系数据库标准语言 SQL 主要内容 3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 视图 3.4 数据查询 „ 语句格式 SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] … FROM <表名或视图名>[, <表名或视图名> ] … [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ]; 分类: 单表查询 连接查询 嵌套查询 集合查询 3.4 数据查询 3.4.2 连接查询 „ 查询同时涉及两个及以上的表,可以通过连接查询实现 ¾一、等值与非等值连接查询 ¾二、复合条件连接查询 ¾三、自身连接查询 ¾四、外连接 „ 引例1:查询学生成绩信息,包括学号,姓名,课程 号,成绩。 连接查询导入 查询结果中的列 涉及哪些表? „ 引例2:查询选了1号课程的学生的学号,姓名。 查询结果中的列和 查询条件中的列涉 及哪些表? 广义笛卡尔积 SELECT * FROM student, sc 笛卡尔积中的 数据都有现实 意义吗? 由笛卡尔积到连接查询 SELECT * FROM student, sc WHERE student.sno=sc.sno 注意:连接查询相当于笛卡尔积上做选择 思考:元组数量 由什么决定? 一、等值与非等值连接查询 [例] 查询学生成绩信息,包括学号,姓名,课程号,成绩。 SELECT student.sno, sname, cno, grade FROM student, sc WHERE student.sno=sc.sno 注意:选择查询结果希望出现的列,由于sno在两个表中都 有,所以要指明来自哪个表,对于内连接任意一个均可。 „关于连接查询条件: ¾连接查询中用来连接两个表的条件称为连接条件或连接谓词, 其一般格式为: [<表名1>.]<列名> <比较运算符> [<表名2>.]<列名> ¾比较运算符主要有: = 、>、 <、 >=、 <=、 !=(或<>) ¾有效的连接查询条件: [<表名1>.]<列名> 和 [<表名2>.]<列名> 是兼容类型 ¾有意义的连接查询条件: [<表名1>.]<列名> <比较运算符> [<表名2>.]<列名>有意义 SELECT student.sno, sname, cno, grade FROM student, sc WHERE student.sno=sc.sno SQL_92的写法: SELECT student.sno, sname, cno, grade FROM student inner join sc on student.sno=sc.sno 连接查询的多种语法: SELECT sno, course.cno,cname, grade FROM course, sc WHERE course.cno=sc.cno SELECT sno, course.cno,cname, grade FROM course inner join sc on course.cno=sc.cno 课堂练习 : „ 查询学生成绩信息,包括学生的学号、课程号,课程 名,考试成绩。 要求:分别用两种语法表达。 „ 注意: ¾索引对于连接查询的作用 ¾自然连接 二、复合条件连接查询 [例]查询‘CS’系的学生的成绩信息,包括学号,姓名,课程 号,成绩。 SELECT student.sno, sname, cno, grade FROM student, sc WHERE student.sno=sc.sno and sdept='CS' SQL_92的写法: SELECT student.sno, sname, cno, grade FROM student inner join sc on student.sno=sc.sno WHERE sdept='CS' 连接查询中给表名指定别名 „查询 ‘CS’系的学生的成绩信息,包括学号,姓名,课程号, 成绩。 SQL_92的写法: SELECT student.sno, sname, cno, grade FROM student inner join sc on student.sno=sc.sno WHERE sdept='CS' 指定别名后: SELECT S.sno, sname, cno, grade FROM student S inner join sc P on S.sno=P.sno WHERE sdept='CS' 多表查询 SELECT student.sno, sname, course.cno, cname,grade FROM student inner join sc on student.sno=sc.sno inner join course on sc.cno=course.cno 或者 SELECT student.sno, sname, course.cno, cname,grade FROM student, sc , course WHERE student.sno=sc.sno and sc.cno=course.cno 查询选修了课程的学生的学号、姓名、课程号、课程名及 考试成绩。 „ 查询 ‘CS’系的选修了课程的学生的学号、姓名、课程号、课程 名及考试成绩。 SELECT student.sno, sname, course.cno, cname,grade FROM student inner join sc on student.sno=sc.sno inner join course on sc.cno=course.cno WHERE sdept='CS' 三、自身连接查询 查询出生日期比学号为‘s001’的学生大的学生学号和姓名: 学号 姓名 出生日期 … S001 王芳 1980-6-3 S002 唐广书 1981-9-22 S003 李霞 1978-8-1 S004 王忠强 1979-9-5 S005 李明 1982-12-31 学生基本信息表 SELECT M.学号,M.姓名 FROM 学生基本信息表 M, 学生基本信息表 P WHERE M.出生日期>P.出生日期 and p.学号=‘s001’ M P 学号 姓名 出生日期 … S001 王芳 1980-6-3 S002 唐广书 1981-9-22 S003 李霞 1978-8-1 S004 王忠强 1979-9-5 S005 李明 1982-12-31 学号 姓名 出生日期 … S001 王芳 1980-6-3 S002 唐广书 1981-9-22 S003 李霞 1978-8-1 S004 王忠强 1979-9-5 S005 李明 1982-12-31 Select M.学号,M.姓名 From 学生基本信息表 M, 学生基本信息表 P Where M.出生日期>P.出生日期 and p.学号=‘s001’ M 筛选后的P 学号 姓名 出生日期 … S001 王芳 1980-6-3 S002 唐广书 1981-9-22 S003 李霞 1978-8-1 S004 王忠强 1979-9-5 S005 李明 1982-12-31 学号 姓名 出生日期 … S001 王芳 1980-6-3 SELECT S1.Sno,S1.Sname,S1.Sdept FROM Student S1,Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = '刘晨'; 学号 姓名 性别 年龄 所在系 200215121 李勇 男 21 cs cs is 200215122 刘晨 女 20 200215123 王敏 女 21 练习1:查询与“刘晨”在同一个系学习的学生。 用自身连接实现本查询: 学号 姓名 性别 年龄 所在系 200215121 李勇 男 21 cs cs is 200215122 刘晨 女 20 200215123 王敏 女 21 s1 s2 习题2:查询课程信息,包括课程号,课程名,先修课 程号,先修课程名称。 Select s.cno, s.cname, s.cpno, p.cname From course s inner join course p on s.cpno=p.cno 演示 四、外连接查询 学号 姓名 性别 年龄 0001 李晓红 女 21 0002 王玲 女 20 0003 张建国 男 21 学生信息表 课程号 课程名 学分 001 操作系统 3 002 多媒体 3 003 数据库 3 004 网络 2 课程信息表 学号 课程号 成绩 0001 001 85 0001 002 90 0002 003 89 0002 001 80 学生选课 查询选修了课程的学生的学号、姓名、课程号及考试成绩: SELECT M.学号,姓名,课程号,成绩 FROM 学生信息表 M inner join 学生选课表 P ON M.学号=P.学号 ORDER BY M.学号, 课程号 学号 姓名 课程号 成绩 0001 李晓红 001 85 0001 李晓红 002 90 0002 王玲 001 80 0002 王玲 003 89 思考:为什么 没有学生0003 的相关信息 查询学生选课信息(学生的学号、姓名、课程号及考试成绩): SELECT M.学号,姓名,课程号,成绩 FROM 学生信息表 M left outer join 学生选课表 P ON M.学号=P.学号 ORDER BY M.学号, 课程号 学号 姓名 课程号 成绩 0001 李晓红 001 85 0001 李晓红 002 90 0002 王玲 003 89 0002 王玲 001 80 0003 张建国 NULL NULL 学号 姓名 性别 年龄 0001 李晓红 女 21 0002 王玲 女 20 0003 张建国 男 21 学生信息表 学号 课程号 成绩 0001 001 85 0001 002 90 0002 003 89 0002 001 80 学生选课 0001 李晓红 001 85 学号 姓名 课程号 成绩 0001 李晓红 002 90 学号 姓名 性别 年龄 0001 李晓红 女 21 0002 王玲 女 20 0003 张建国 男 21 学生信息表 学号 课程号 成绩 0001 001 85 0001 002 90 0002 003 89 0002 001 80 学生选课 学号 姓名 课程号 成绩 0001 李晓红 001 85 0001 李晓红 002 90 0002 王玲 003 89 0002 王玲 001 80 学号 姓名 性别 年龄 0001 李晓红 女 21 0002 王玲 女 20 0003 张建国 男 21 学生信息表 学号 课程号 成绩 0001 001 85 0001 002 90 0002 003 89 0002 001 80 学生选课 学号 姓名 课程号 成绩 0001 李晓红 001 85 0001 李晓红 002 90 0002 王玲 003 89 0002 王玲 001 80 0003 张建国 NULL NULL 查询学生选课信息(学生的学号、姓名、课程号及考试成绩): SELECT P.学号,姓名,课程号,成绩 FROM 学生信息表 M left outer join 学生选课表 P ON M.学号=P.学号 ORDER BY M.学号, 课程号 学号 姓名 课程号 成绩 0001 李晓红 001 85 0001 李晓红 002 90 0002 王玲 003 89 0002 王玲 001 80 NULL 张建国 NULL NULL 思考:如果select语句中是P.学号而不是M.学号会怎样? 查询课程被选修情况(课程号、课程名、学生的学号、成绩): SELECT P . 课程号,课程名, 学号,成绩 FROM 学生选课表 M right outer join 课程信息表 P ON M.课程号=P.课程号 ORDER BY P . 课程号, 学号 课程号 课程名 学号 成绩 001 操作系统 0001 85 001 操作系统 0002 80 003 数据库 0002 89 002 多媒体 0001 90 004 网络 NULL NULL 外连接查询__FULL OUTER JOIN 学校数据库中有学生和宿舍两个关系: 学生(学号,姓名) 宿舍(楼号,房间号,床位号,学号) 假设有的学生不住宿,床位也可能空闲。如果要列出 所有学生住宿和宿舍分配的情况,包括没有住宿的学生和 空闲的床位,则应执行FULL OUTER JOIN Select 学生.学号,姓名 ,楼号,房间号,床位号 From 学生 FULL OUTER JOIN 宿舍 on 学生.学号=宿舍.学号 SELECT s.sno, sname,cno, grade FROM student s left outer join sc p on s.sno=p.sno 课堂练习 : „ 查询学生选课信息(学生的学号、姓名、课程号及考试 成绩) 连接查询总结 连接类型 Inner join Inner join outer join Left outer join Right outer join Full outer join 连接与集合查询 查询平均成绩在85分及其以上的学生的学号,姓名和平均成 绩,按照平均成绩的降序排列。 SELECT s.sno, sname, AVG(grade) 平均成绩 FROM student S inner join sc P on s.sno=p.sno GROUP BY s.sno, sname HAVING AVG(grade)>=85 ORDER BY 平均成绩 DESC; 演示 内容回顾 以上详细讲解了连接查询: 一、等值与非等值连接查询 二、复合条件连接查询 三、自身连接查询 四、外连接 作业: „ 实验: ¾教材以及讲义中的例题 ¾实验12 简单数据查询 x 二、练习内容Æ2.对实验9中的EDUC数据库实现 以下查询: (7)~(10) ¾实验13 复杂数据查询 x 二、练习内容Æ2.对实验9中的EDUC数据库实现 以下聚集查询: 2010年10月27日

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档

相关文档