数据库技术及应用教程6.ppt

SQL语言基础 DML语言 Select多表查询 SELECTemployee fname employee lname jobs job desc jobs job id jobs min lvl obs max lvlFROMemployee jobs 格式Select 表名称 字段名 from 表名称列表 Select嵌套查询 SELECTemployee fname employee lname jobs job desc jobs job id jobs min lvl obs max lvlFROMemployee jobsWherejobs job id selecttop1fromjobswheremin lvl 10 4 数据表连接及连接查询 连接查询 一个查询需要对多个表进行操作表的连接方法 表之间满足一定条件的行进行连接时 FROM子句指明进行连接的表名 WHERE子句指明连接的列名及其连接条件利用关键字JOIN进行连接 当将JOIN关键词放于FROM子句中时 应有关键词ON与之对应 以表明连接的条件 5 JION的分类 6 等值连接与非等值连接 例3 48 查询 刘伟 老师所讲授的课程 要求列出教师号 教师姓名和课程号 方法1 SELECTT TNo TN CNoFROMT TCWHERE T TNo TC TNo AND TN 刘伟 方法2 SELECTT TNo TN CNoFROMTINNERJOINTCONT TNo TC TNoWHERE TN 刘伟 连接条件 当比较运算符为 时 称为等值连接 其他情况为非等值连接 引用列名TNo时要加上表名前缀 这是因为两个表中的列名相同 必须用表名前缀来确切说明所指列属于哪个表 以避免二义性 7 例3 49 查询所有选课学生的学号 姓名 选课名称及成绩 SELECTS SNo SN CN ScoreFROMS C SCWHERES SNo SC SNoANDSC CNo C CNo 例3 50 查询每门课程的课程名 任课教师姓名及其职务 选课人数 SELECTCN TN Prof COUNT SC SNo FROMC T TC SCWHERET TNo TC TNoANDC CNo TC CNoANDSC CNo C CNoGROUPBYSC CNo 8 自身连接 例3 51 查询所有比 刘伟 工资高的教师姓名 工资和刘伟的工资 方法1 SELECTX TN X SalASSal a Y SalASSal bFROMTASX TASYWHEREX Sal Y SalANDY TN 刘伟 方法2 SELECTX TN X Sal Y SalFROMTASXINNERJOINTASYONX Sal Y SalANDY TN 刘伟 方法3 SELECTR1 TN R1 Sal R2 SalFROM SELECTTN SalFROMS ASR1INNERJOIN SELECTSalFROMTWHERETN 刘伟 ASR2ONR1 Sal R2 Sal 9 例3 52 检索所有学生姓名 年龄和选课名称 方法1 SELECTSN Age CNFROMS C SCWHERES SNo SC SNoANDSC CNo C CNo 方法2 SELECTR3 SNo R3 SN R3 Age R4 CNFROM SELECTSNo SN AgeFROMS ASR3INNERJOIN SELECTR2 SNo R1 CNFROM SELECTCNo CNFROMC ASR1INNERJOIN SELECTSNo CNoFROMSC ASR2ONR1 CNo R2 CNo ASR4ONR3 SNo R4 SNo 10 外连接而在外部连接中 参与连接的表有主从之分 以主表的每行数据去匹配从表的数据列 符合连接条件的数据将直接返回到结果集中 对那些不符合连接条件的列 将被填上NULL值后再返回到结果集中 例3 53 查询所有学生的学号 姓名 选课名称及成绩 没有选课的同学的选课信息显示为空 SELECTS SNo SN CN ScoreFROMSLEFTOUTERJOINSCONS SNo SC SNoLEFTOUTERJOINCONC CNo SC CNo 左外部连接右外部连接 11 子查询 在WHERE子句中包含一个形如SELECT FROM WHERE的查询块 此查询块称为子查询或嵌套查询 返回一个值的子查询 例3 54 查询与 刘伟 老师职称相同的教师号 姓名SELECTTNo TNFROMTWHEREProf SELECTProfFROMTWHERETN 刘伟 使用比较运算符 12 返回一组值的子查询使用ANY 例3 55 查询讲授课程号为C5的教师姓名 SELECTTNFROMTWHERE TNo ANY SELECTTNoFROMTCWHERECNo C5 使用ANY或ALL SELECTTNFROMT TCWHERET TNo TC TNoANDTC CNo C5 IN 13 例3 56 查询其他系中比计算机系某一教师工资高的教师的姓名和工资 SELECTTN SalFROMTWHERE Sal ANY SELECTSalFROMTWHEREDept 计算机 AND Dept 计算机 SELECTTN SalFROMTWHERESal SELECTMIN Sal FROMTWHEREDept 计算机 ANDDept 计算机 14 使用ALL 例3 58 查询其他系中比计算机系所有教师工资都高的教师的姓名和工资 SELECTTN SalFROMTWHERE Sal ALL SELECTSalFROMTWHEREDept 计算机 AND Dept 计算机 例3 59 查询不讲授课程号为C5的教师姓名 SELECTDISTINCTTNFROMTWHERE C5 ALL SELECTCNoFROMTCWHERETNo T TNo Sal SELECTMAX Sal NOTIN 常用函数 SumMax minCountLenGetdate 使用函数 Selectsum royalty asRoyfromtitlesWheretype business Selectmax royalty asABCfromtitleswheretypelike p Count Selectcount fromtitlesWheretype business 18 例3 37 求学号为S1学生的总分和平均分 SELECTSUM Score ASTotalScore AVG Score ASAveScoreFROMSCWHERE SNo S1 例3 38 求选修C1号课程的最高分 最低分及之间相差的分数 SELECTMAX Score ASMaxScore MIN Score ASMinScore MAX Score MIN Score ASDiffFROMSCWHERE CNo C1 例3 40 求学校中共有多少个系 SELECTCOUNT DISTINCTDept ASDeptNumFROMS DISTINCT消去重复行 北京林业大学软件教研室 19 例3 41 统计有成绩同学的人数 SELECTCOUNT Score FROMSC成绩为零的同学他计算在内 没有成绩 即为空值 的不计算 例3 42 利用特殊函数COUNT 求计算机系学生的总数 SELECTCOUNT FROMSWHEREDept 计算机 COUNT 用来统计元组的个数 不消除重复行 不允许使用DISTINCT关键字 having 与where相似 作为分组的条件设置当在使用了groupby之后不允许使用where 只能使用having having Selectau id au lname stateFromauthorsGroupbyau id au lname stateHavingstate CA 查询结果处理 插入新表Selectau id au lname stateIntoseachedtableFromauthorsWheresatte CA Insert语句 Insert插入数据 格式insertinto表名 字段名 values 对应的数值 Insertintotable1 field1 field2 field3 field4 values 123 abc a88 中国 Insert插入数据 选择插入数据Insertintotable1 table1 field1 table1 field2 table1 field3 table1 field4 Selsettable2 field1 table2 field2 table2 field3 table2 field4fromTable2whereTable2 filed1 123 26 例3 65 在S表中添加一条学生记录 学号 S7 姓名 郑冬 性别 女 年龄 21 系别 计算机 INSERTINTOS SNo SN Age Sex Dept VALUES S7 郑冬 21 女 计算机 必须用逗号将各个数据分开 字符型数据要用单引号括起来 如果INTO子句中没有指定列名 则新添加的记录必须在每个属性列上均有值 且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致 Update更新数据 格式updatetable1setfield1 abc wherefield2 123 28 例3 68 把刘伟老师转到信息系UPDATETSETDept 信息 WHERESN 刘伟 例3 69 将所有学生的年龄增加1岁UPDATESSETAge Age 1 修改多行 修改一行 例3 71 把讲授C5课程的教师的岗位津贴增加100元 UPDATETSETComm Comm 100WHERE TNoIN SELECTTNoFROMT TCWHERET TNo TC TNoANDTC CNo C5 用子查询选择要修改的行 用子查询提供要修改的值 例3 72 把所有教师的工资提高到平均工资的1 2倍 UPDATETSETSal SELECT1 2 AVG Sal FROMT Delete删除数据 格式Deleteform表