关系运算与SQL实例ppt课件.ppt

1 关系运算与SQL实例 金秋乐 2 一 关系代数 传统的集合运算 1 并 Union R S t t R t S 2 交 Intersection R S t t R t S 3 差 Difference R S t t R t S 3 一 关系代数 传统的集合运算 4 广义笛卡儿积 ExtendedCartesianProduct R S trts tr R ts S R S 4 一 关系代数 传统的集合运算 运算结果 R S R S R S 5 一 关系代数 传统的集合运算 运算结果 R S 6 一 关系代数 专门的关系运算 专业标记1 设关系模式为R A1 A2 An 它的一个关系设为R t R表示t是R的一个元组 t Ai 表示元组t中相应于属性Ai的一个分量2 若A Ai1 Ai2 Aik 其中Ai1 Ai2 Aik是A1 A2 An中的一部分 则A称为属性列或域列 t A t Ai1 t Ai2 t Aik 表示元组t在属性列A上诸分量的集合 A则表示 A1 A2 An 中去掉 Ai1 Ai2 Aik 后剩余的属性组 7 一 关系代数 专门的关系运算 专业标记3 R为n目关系 S为m目关系 tr R ts S trts称为元组的连接 Concatenation 它是一个n m列的元组 前n个分量为R中的一个n元组 后m个分量为S中的一个m元组4 给定一个关系R X Z X和Z为属性组 定义 当t X x时 x在R上的象集 ImageSet 为 Zx t Z t R t X x 它表示R中属性组X上值为x的诸元组在Z上分量的集合 8 一 关系代数 专门的关系运算 专门的关系运算1 选择 Selection 又称为限制 Restriction 在关系R中选择满足给定条件的诸元组 记作 F R t t R F t 真 例1 查询信息系 IS系 全体学生 Sdept IS Student 5 IS Student 例2 查询年龄小于20岁的学生 Sage 20 Student 4 20 Student 9 一 关系代数 专门的关系运算 例 Student Couse SC 10 一 关系代数 专门的关系运算 例 Student Couse SC 11 一 关系代数 专门的关系运算 专门的关系运算2 投影 Projection 从R中选择若干个属性列组成新的关系 记作 A R t A t R A是R中的属性列例3 查询学生的姓名和所在的系 Sname Sdept Student 2 5 Student 例4 查询学生关系Student中有哪些系 Sdept Student 12 一 关系代数 专门的关系运算 专门的关系运算3 连接 Join 又称 连接 从两个关系的笛卡儿积中选取属性间满足一定条件的元组 记作 RS trts tr R ts S tr A ts B A和B分别为R和S上度数相等且可比的属性组 为比较运算符特殊的连接 等值连接 RS trts tr R ts S tr A ts B 自然连接 将等值连接中重复的属性列去掉 记作 RS trts tr R ts S tr A ts B A B A B 13 一 关系代数 专门的关系运算 连接例题 R S 14 一 关系代数 专门的关系运算 专门的关系运算4 除 Division R X Y 与S Y Z 的除运算得到一个新的关系P X P是R中满足下列条件的元组在X属性列上的投影 元组在X上分量值x的象集Yx包含S在Y上投影的集合 记作 R S tr X tr R y S Yx 其中Yx为x在R中的象集 x tr X 15 一 关系代数 专门的关系运算 除例题 R S R S 分析 在关系R中A可以取4个值 a1 a2 a3 a4 其中a1的象集为 b1 c2 b2 c3 b2 c1 a2的象集为 b3 c7 b2 c3 a3的象集为 b4 c6 a4的象集为 b6 c6 S在 B C 上的投影为 b1 c2 b2 c1 b2 c3 16 一 关系代数 专门的关系运算 例题 例7 查询至少选修了1号课程和3号课程的学生号码先建立临时关系K Sno Cno SC K Student Couse SC 17 一 关系代数 专门的关系运算 例题 例8 查询选修了2号课程的学生的学号 Sno Cno 2 SC 95001 95002 例9 查询至少选修了一门其直接先行课程为5号课程的学生姓名 Sname Cpno 5 Course SC Sno Sname Student Sno Cno SC Cno Course Sno Sname Student Student Couse SC 18 一 关系代数 专门的关系运算 例题 例10 查询选修了全部课程的学生号码和姓名 Sno Cno SC Cno Course Sno Sname Student Couse SC Student 19 二 SQL 1 SQL概述StructuredQueryLanguage 通用的 功能极强的关系数据库语言特点 1 综合统一 2 高度非过程化 3 面向集合的操作方式 4 以同一种语法结构提供两种使用方式 5 语言简捷 易学易用 20 二 SQL 数据查询SELECT数据定义CREATE DROP数据操纵INSERT UPDATE DELETE数据控制GRANT REVOKE 21 二 SQL 数据查询 SELECTFROM WHERE GROUPBY HAVING内部函数表达式 ORDERBY ASC或DESC 22 二 SQL 数据操纵 1 UPDATEUPDATESET WHERE2 INSERTINSERTINTO 列名 VALUES 表达式或函数 23 二 SQL 数据操纵 2 INSERT 续 INSERTINTOSQL子查询3 DELETEDELETEFROMWHERE 24 二 SQL 实例 查询 例1 查询全体学生的学号和姓名例2 查询全体学生的姓名 学号 所在系 Student Couse SC SELECTSno SnameFROMStudent SELECTSname Sno SdeptFROMStudent 25 二 SQL 实例 查询 例3 查询全体学生的详细记录例4 查询全体学生的姓名及其出生年份 Student Couse SC SELECT FROMStudent SELECTSname 2004 SageFROMStudent 26 二 SQL 实例 查询 例5 查询全体学生的姓名 出生年份和所在系 要求用小写字母表示所在系名例6 查询选修了课程的学生的学号 Couse SC Student SELECTSname YearofBirth 2004 Sage LOWER Sdept FROMStudent SELECTSnoFROMSC SELECTDISTINCTSnoFROMSC 27 二 SQL 实例 查询 例7 查询计算机系全体学生的名单例8 查询所有年龄在20岁以下的学生姓名及其年龄 Couse SC Student SELECTSnameFROMStudentWHERESdept CS SELECTSname SageFROMStudentWHERESage 20 SELECTSname SageFROMStudentWHERENOTSage 20 28 二 SQL 实例 查询 例9 查询考试成绩有不及格的学生的学号例10 查询年龄在20 23岁之间的学生的姓名 系别和年龄 Couse SC Student SELECTDISTINCTSnoFROMSCWHEREGrade 60 SELECTSname Sdept SageFROMStudentWHERESageBETWEEN20AND23 SELECTSname Sdept SageFROMStudentWHERESage 20 29 二 SQL 实例 查询 例11 查询年龄在20 23岁之间的学生的姓名 系别和年龄例12 查询信息系 IS 数学系 MA 和计算机科学系 CS 学生的姓名和性别 Couse SC Student SELECTSname Sdept SageFROMStudentWHERESageNOTBETWEEN20AND23 SELECTSname SsexFROMStudentWHERESdeptIN IS MA CS 30 二 SQL 实例 查询 例13 查询不是信息系 数学系 和计算机科学系学生的姓名和性别例14 查询学号为95001的学生的详细情况 Couse SC Student SELECTSname SsexFROMStudentWHERESdeptNOTIN IS MA CS SELECT FROMStudentWHERESnoLIKE 95001 SELECT FROMStudentWHERESno 95001 31 二 SQL 实例 查询 例15 查询所有姓刘的学生的姓名 学号和性别例16 查询姓 欧阳 且全名为三个汉字的学生的姓名 Couse SC Student SELECTSname Sno SsexFROMStudentWHERESnameLIKE 刘 SELECTSnameFROMStudentWHERESnameLIKE 欧阳 32 二 SQL 实例 查询 例17 查询名字中第2个字为 阳 字的学生的姓名和学号例18 查询所有不姓刘的学生的姓名 Couse SC Student SELECTSname SnoFROMStudentWHERESnameLIKE 阳 SELECTSnameFROMStudentWHERESnameNOTLIKE 刘 33 二 SQL 实例 查询 例19 查询DB Design课程的课程号和学分例20 查询以 DB 开头 且倒数第3个字符为i的课程的详细情况 Couse SC Student SELECTCno CcreditFROMCourseWHERECnameLIKE DB Design ESCAPE SELECT FROMCourseWHERECnameLIKE DB i ESCAPE 34 二 SQL 实例 查询 例21 查询缺少成绩的学生的学号和课程号例22 查询所有有成绩的学生的学号和课程号 Couse SC Student SELECTSno CnoFROMSCWHEREGradeISNULL SELECTSno CnoFROMSCWHEREGradeISNOTNULL 35 二 SQL 实例 查询 例23 查询计算机系年龄在20岁以下的学生姓名例12改写 Couse SC Student SELECTSnameFROMStudentWHERESdept is ANDSage 20 SELECTSname SsexFROMStudentWHERESdept IS ORSdept MA ORSdept CS 36 二 SQL 实例 查询 例24 查询选修了3号课程的学生的学号及其成绩 查询结果按分数的降序排列例25 查询全体学生情况 查询结果按所在系的系号升序排列 同一系中的学生按年龄降序排列 Couse SC Student SELECTSno GradeFROMSCWHERECno 3 ORDERBYGradeDESC SELECT FROMStudentORDERBYSdept SageDESC 37 二 SQL 实例 查询 例26 查询学生总人数例27 查询选修了课程的学生人数 Couse SC Student SELECTCOUNT FROMStudent SELECTCOUNT DISTINCTSno FROMSC 38 二 SQL 实例 查询 例28 计算1号课程的学生平均成绩例29 查询选修了1号课程的学生最高分数 Couse SC Student SELECTAVG Grade FROMSCWHERECno 1 SELECTMAX Grade FROMSCWHERECno 1 39 二 SQL 实例 查询 例30 求各个课程号及相应的选课人数例31 查询选修了3门以上课程的学生学号 Couse SC Student SELECTCno COUNT Sno FROMSCGROUPBYCno SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT 3 40 二 SQL 实例 查询 例32 查询每个学生及其选修课程的情况例33 改写 Couse SC Student SELECTStudent SC FROMStudent SCWHEREStudent Sno SC Sno SELECTStudent Sno Sname Ssex Sage Sdept Cno GradeFROMStudent SCWHEREStudent Sno SC Sno 41 二 SQL 实例 查询 例34 查询每门课程的间接先行课 Couse SC Student CouseFIRST CouseSECOND SELECTFIRST Cno SECOND CpnoFROMCourseFIRST CourseSECONDWHEREFIRST Cpno SECOND Cno 42 二 SQL 实例 查询 例35 查询选修了2号课程且成绩在90分以上的所有学生的学号和姓名 Couse SC Student SELECTStudent Sno SnameFROMStudent SCWHEREStudent Sno SC SnoANDSC Cno 2 ANDSC Grade 90 43 二 SQL 实例 查询 例36 查询每个学生的学号 姓名 选修的课程名及成绩 Couse SC Student SELECTStudent Sno Sname Cname GradeFROMStudent SC CourseWHEREStudent Sno SC SnoANDSC Cno Course Cno 44 二 SQL 实例 查询 例37 查询与 刘晨 在同一个系学习的学生的学号 姓名 所在系 Couse SC Student SELECTSno Sname SdeptFROMStudentWHERESdeptIN SELECTSdeptFROMStudentWHERESname 刘晨 可用连接实现 45 二 SQL 实例 查询 例38 查询选修了课程名为 信息系统 的学生的学号和姓名 Couse SC Student SELECTSno SnameFROMStudentWHERECnoIN SELECTCnoFROMSCWHERECnoIN SELECTCnoFROMCourseWHERECname 信息系统 可用连接实现 46 二 SQL 实例 查询 例37改写 Couse SC Student SELECTSno Sname SdeptFROMStudentWHERESdept SELECTSdeptFROMStudentWHERESname 刘晨 47 二 SQL 实例 查询 例39 查询其他系中比信息系某一学生年龄小的学生的姓名和年龄 Couse SC Student SELECTSname SageFROMStudentWHERESage ANY SELECTSageFROMStudentWHERESdept IS 48 二 SQL 实例 查询 例40 查询其他系中比信息系所有学生年龄小的学生的姓名和年龄 Couse SC Student SELECTSname SageFROMStudentWHERESage ALL SELECTSageFROMStudentWHERESdept IS 49 二 SQL 实例 查询 例41 查询所有选修了1号课程的学生姓名 Couse SC Student SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 1 50 二 SQL 实例 查询 例42 查询没有选修了1号课程的学生姓名 Couse SC Student SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno 1 51 二 SQL 实例 查询 例43 查询选修了全部课程的学生姓名 Couse SC Student SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 52 二 SQL 实例 查询 例44 查询至少选修了学生95002选修的全部课程的学生号码 Couse SC Student SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS SELECT FROMSCSCYWHERESCY Sno 95002 ANDNOTEXISTS SELECT FROMSCSCZWHERESCZ Sno SCX SnoANDSCZ Cno SCY Cno 53 二 SQL 实例 数据更新 例1 将一个新学生记录 学号 95020 姓名 陈东 性别 男 所在系 IS 年龄 18 插入