关系数据库标准语言 SQL语句实例.ppt

第三章关系数据库标准语言SQL SQL概述数据操纵数据控制 数据定义数据查询视图 SQL StructuredQueryLanguage 即结构化查询语言 1974年 由Boyce和Chamberlin提出 并在IBM公司研制的关系数据库管理系统原型SystemR上实现 由于SQL简单易学 功能丰富 深受用户及计算机工业界的欢迎 SQL概述 1986年 美国国家标准局 ANSI 的数据库委员会批准了SQL作为关系数据库语言的美国标准 同年公布了SQL标准文本 SQL86 1987年 国际化标准组织 ISO 也通过了这一标准 SQL是集数据定义 数据查询 数据操纵和数据控制功能于一体的语言 具有功能丰富 使用灵活 语言简捷易学等特点 主要特点包括 1 综合统一 2 高度非过程化 3 面向集合的操作方式 4 以同一种语法结构提供多种使用方式 5 语言简洁 易学易用 数据定义 基本表 定义 Createtable 列宽度 列级完整性条件 列宽度 列级完整性条件 注意 1 不同表中相应属性列的类型 宽度要一致 2 注意约束的作用 例5 建立一个 学生 表student CreatetableStudent SnoChar 9 PrimaryKey SnameChar 20 Unique SsexChar 2 SageSmallint SdeptChar 20 例6 建立一个 课程 表Course CreatetableCourse CnoChar 4 PrimaryKey CnameChar 40 CpnoChar 4 CcreditSmallint ForeignKey Cpno ReferencesCourse Cno 例6 建立一个 选课 表SC CreatetableSC SnoChar 9 CnoChar 4 GrradeSmallint PrimaryKey Sno Cno ForeignKey Sno ReferencesStudent Sno ForeignKey Cno ReferencesCourse Cno 常用完整性约束 主码约束 PRIMARYKEY唯一性约束 UNIQUE非空值约束 NOTNULL参照完整性约束思考 PRIMARYKEY与UNIQUE的区别 例 建立一个学生表Student 它由学号Sno 姓名Sname 性别Ssex 年龄Sage 所在系Sdept五个属性组成 其中学号不能为空 值是唯一的 并且姓名取值也唯一 CREATETABLEStudent SnoCHAR 5 NOTNULLUNIQUE SnameCHAR 20 UNIQUE SsexCHAR 1 SageINT SdeptCHAR 15 修改 AltertableAdd 列宽度 完整性约束 DropColumn AlterColumn 列宽度 P87例8 例9 例10注意 使用Alter语句修改列时不能修改列名 例8 向Student表增加 入学时间 列 其数据类型为日期型 ALTERTABLEStudentADDS entranceDATE 不论基本表中原来是否已有数据 新增加的列一律为空值 例9 将年龄的数据类型改为整型 ALTERTABLEStudentAlterColumnSageInt 修改原有的列定义有可能会破坏已有数据 例10 增加课程名必须取唯一值的约束 AlterTableCourseAddUnique Cname 删除 DroptableRestrict Cascade 例11 删除Student表 DropTableStudent SQLServer2005中是如何规定的 有限制 无限制 数据操纵 插入数据删除数据修改数据 插入数据1 插入单个元组 INSERTINTO VALUES P115例1 例2 例3 例1 将一个新学生元组 学号 200215128 姓名 陈冬 性别 男 所在系 IS 年龄 18岁 插入到Student表中 INSERTINTOStudentVALUES 200215128 陈冬 男 IS 18 例2 将学生张成民的信息 200215126 张成民 男 IS 18 插入到Student表中 INSERTINTOStudentVALUES 200215126 张成民 男 18 CS 例3 插入一条选课记录 95020 1 INSERTINTOSC Sno Cno VALUES 95020 1 新插入的记录在Grade列上取空值 或 INSERTINTOSCVALUES 95020 1 Null 2 插入子查询结果 INSERTINTO 子查询 例4 对每一个系 求学生的平均年龄 并把结果存入数据库 INSERTINTODeptage Sdept Avgage SELECTSdept AVG Sage FROMStudentGROUPBYSdept CreateTableDept age SdeptChar 15 Avg ageAmallint 修改数据 语句格式UPDATESET WHERE 功能修改指定表中满足WHERE子句条件的元组 三种修改方式 修改某一个元组的值P117例5修改多个元组的值P117例6带子查询的修改语句P117例7 例5 将学生200215121的年龄改为22岁 UPDATEStudentSETSage 22WHERESno 200215121 例6 将所有学生的年龄增加1岁 UPDATEStudentSETSage Sage 1 例7 将计算机科学系全体学生的成绩置零 UPDATESCSETGrade 0WHERE CS SELETESdeptFROMStudentWHEREStudent Sno SC Sno 删除数据 DELETEFROM WHERE 功能删除指定表中满足WHERE子句条件的元组WHERE子句指定要删除的元组缺省表示要修改表中的所有元组 三种删除方式 删除某一个元组的值P118例8删除多个元组的值P118例9带子查询的删除语句P118例10 例8 删除学号为200215128的学生记录 DELETEFROMStudentWHERESno 95019 例9 删除所有学生的选课记录 DELETEFROMSC 例 删除2号课程的所有选课记录 DELETEFROMSCWHERECno 2 例11 删除计算机科学系所有学生的选课记录 DELETEFROMSCWHERE CS SELETESdeptFROMStudentWHEREStudent Sno SC Sno 数据查询Select语句的一般格式 Select All Distinct From Left Right Full JoinOn Where OrderBy Asc Desc GroupBy Having Select后面的各选项含义 Select 用于选择输出的字段 All 表示输出所有的查询记录包括重复记录 Distinct 表示输出无重复的查询记录 字段名 在此列出要输出的字段名称 如果包括所有字段 则用 来表示 From 子句用于指定记录来源 列出所查的表或视图名 where 用于指明查询结果中的记录应该满足的条件 orderby 用于指定排序依据的字段名Asc 升序Desc 降序groupby 此短语将查询结果按某一列或某几列的值进行分组 Having子句用于与groupby子句配合使用 用于说明分组条件 查询分类 单表查询连接查询嵌套查询集合查询 单表查询 一 选择表中的若干列二 选择表中的若干元组三 对查询结果排序四 使用集函数五 对查询结果分组 是仅涉及一个表的查询 是一种最简单的查询操作 1 查询指定列 例1 查询全体学生的学号与姓名 SELECTSno SnameFROMStudent 例2 查询全体学生的姓名 学号 所在系 SELECTSname Sno SdeptFROMStudent 2 查询全部列 例3 查询全体学生的详细记录 SELECTSno Sname Ssex Sage SdeptFROMStudent 或SELECT FROMStudent 3 查询经过计算的值 例4 查全体学生的姓名及其出生年份 SELECTSname 2000 SageFROMStudent 输出结果 学生表 Student Sno Sname Ssex Sage Sdept 例5 查询全体学生的姓名 出生年份和所有系 要求用小写字母表示所有系名 SELECTSname YearofBirth 2000 Sage LOWER Sdept FROMStudent 输出结果 4 使用TOP关键字 Select Topn TopnPercent 列名1 列名2 列名n From表名Topn 返回最前面的n行TopnPercent 返回最前面的n 行 例 selecttop1 fromcustomers例 selecttop1Percent fromcustomers 5 使用列别名改变查询结果的列标题 SELECTSnameNAME YearofBirth BIRTH 2000 SageBIRTHDAY LOWER Sdept DEPARTMENTFROMStudent 单表查询 一 选择表中的若干列二 选择表中的若干元组三 对查询结果排序四 使用集函数五 对查询结果分组 是仅涉及一个表的查询 是一种最简单的查询操作 1 消除取值重复的行 假设SC表中有下列数据SnoCnoGrade 200215121192200215121285200215121388200215122290200215122380 ALL与DISTINCT 例6 查询选修了课程的学生学号 结果 Sno 200215121200215121200215121200215122200215122 SELECTSnoFROMSC 或 默认ALL SELECTALLSnoFROMSC 2 SELECTDISTINCTSnoFROMSC 结果 Sno 200215121200215122 注意DISTINCT短语的作用范围是所有目标列例 查询选修课程的各种成绩错误的写法SELECTDISTINCTCno DISTINCTGradeFROMSC 正确的写法SELECTDISTINCTCno GradeFROMSC 2 查询满足条件的元组 WHERE子句常用的查询条件 1 比较大小 在WHERE子句的中使用比较运算符 逻辑运算符NOT 比较运算符 例8 查询所有年龄在20岁以下的学生姓名及其年龄 SELECTSname SageFROMStudentWHERESage 20 2 确定范围 使用谓词BETWEEN AND NOTBETWEEN AND 例10 查询年龄在20 23岁 包括20岁和23岁 之间的学生的姓名 系别和年龄 SELECTSname Sdept SageFROMStudentWHERESageBETWEEN20AND23 等价于SELECTSname Sdept SageFROMStudentWHERESage 20ANDSage 23 例11 查询年龄不在20 23岁之间的学生的 姓名 系别和年龄 SELECTSname Sdept SageFROMStudentWHERESageNOTBETWEEN20AND23 等价于SELECTSname Sdept SagFROMStudentWHERESage23 3 确定集合 使用谓词IN NOTIN 用逗号分隔的一组取值 例12 查询信息系 IS 数学系 MA 和计算机科学系 CS 学生的姓名和性别 SELECTSname SsexFROMStudentWHERESdeptIN IS MA CS 等价于SELECTSname SsexFROMStudentWHERESdept IS ORSdept MA ORSdept CS 例13 查询既不是信息系 数学系 也不是计算机科学系的学生的姓名和性别 SELECTSname SsexFROMStudentWHERESdept IS ANDSdept MA ANDSdept CS 等价于SELECTSname SsexFROMStudentWHERESdeptNOTIN IS MA CS 4 字符匹配 NOT LIKE ESCAPE 指定匹配模板匹配模板 固定字符串或含通配符的字符串当匹配模板为固定字符串时 可以用 运算符取代LIKE谓词 用 或运算符取代NOTLIKE谓词 百分号 代表任意长度 长度可以为0 的字符串例 a b表示以a开头 以b结尾的任意长度的字符串 如acb addgb ab等都满足该匹配串 下横线 代表任意单个字符例 a b表示以a开头 以b结尾的长度为3的任意字符串 如acb afb等都满足该匹配串 1 匹配模板为固定字符串 例14 查询学号为95001的学生的详细情况 SELECT FROMStudentWHERESno 95001 等价于 SELECT FROMStudentWHERESnoLIKE 95001 2 匹配模板为含通配符的字符串 例15 查询所有姓刘学生的姓名 学号和性别 SELECTSname Sno SsexFROMStudentWHERESnameLIKE 刘 例16 查询姓 欧阳 且全名为三个汉字的学生的姓名 SELECTSnameFROMStudentWHERESnameLIKE 欧阳 例18 查询所有不姓刘的学生姓名 SELECTSname Sno SsexFROMStudentWHERESnameNOTLIKE 刘 例17 查询名字中第2个字为 阳 字的学生的姓名和学号 SELECTSname SnoFROMStudentWHERESnameLIKE 阳 3 使用换码字符将通配符转义为普通字符 SELECTCno CcreditFROMCourseWHERECnameLIKE DB Design ESCAPE 例19 查询DB Design课程的课程号和学分 SELECT FROMCourseWHERECnameLIKE DB i ESCAPE 例20 查询以 DB 开头 且倒数第3个字符为i的课程的详细情况 5 涉及空值的查询 使用谓词ISNULL或ISNOTNULL ISNULL 不能用 NULL 代替 例21 某些学生选修课程后没有参加考试 所以有选课记录 但没有考试成绩 查询缺少成绩的学生的学号和相应的课程号 SELECTSno CnoFROMSCWHEREGradeISNULL 例22 查所有有成绩的学生学号和课程号 SELECTSno CnoFROMSCWHEREGradeISNOTNULL 6 多重条件查询 用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词 NOT IN NOT BETWEEN AND 例23 查询计算机系年龄在20岁以下的学生姓名 SELECTSnameFROMStudentWHERESdept CS ANDSage 20 单表查询 一 选择表中的若干列二 选择表中的若干元组三 对查询结果排序四 使用集函数五 对查询结果分组 是仅涉及一个表的查询 是一种最简单的查询操作 三 对查询结果排序 使用ORDERBY子句可以按一个或多个属性列排序升序 ASC 降序 DESC 缺省值为升序当排序列含空值时ASC 排序列为空值的元组最后显示DESC 排序列为空值的元组最先显示 例24 查询选修了3号课程的学生的学号及其成绩 查询结果按分数降序排列 SELECTSno GradeFROMSCWHERECno 3 ORDERBYGradeDESC 例25 查询全体学生情况 查询结果按所在系的系号升序排列 同一系中的学生按年龄降序排列 SELECT FROMStudentORDERBYSdept SageDESC 单表查询 一 选择表中的若干列二 选择表中的若干元组三 对查询结果排序四 使用集函数五 对查询结果分组 是仅涉及一个表的查询 是一种最简单的查询操作 四 使用集函数 5类主要集函数计数COUNT DISTINCT ALL COUNT DISTINCT ALL 计算总和SUM DISTINCT ALL 计算平均值AVG DISTINCT ALL 求最大值MAX DISTINCT ALL 求最小值MIN DISTINCT ALL DISTINCT短语 在计算时要取消指定列中的重复值ALL短语 不取消重复值ALL为缺省值 例26 查询学生总人数 SELECTCOUNT FROMStudent SELECTCOUNT DISTINCTSno FROMSC 注 用DISTINCT以避免重复计算学生人数 例27 查询选修了课程的学生人数 2020 3 17 83 可编辑 例28 计算1号课程的学生平均成绩 SELECTAVG Grade FROMSCWHERECno 1 例29 查询选修2号课程的学生最高分数 SELECTMAX Grade FROMSCWHERECno 2 单表查询 一 选择表中的若干列二 选择表中的若干元组三 对查询结果排序四 使用集函数五 对查询结果分组 是仅涉及一个表的查询 是一种最简单的查询操作 五 对查询结果分组 使用GROUPBY子句分组 细化集函数的作用对象未对查询结果分组 集函数将作用于整个查询结果对查询结果分组后 集函数将分别作用于每个组 SELECTCno COUNT Sno FROMSCGROUPBYCno 例30 求各个课程号及相应的选课人数 结果 CnoCOUNT Sno 122234344433548 GROUPBY子句的作用对象是查询的中间结果表 分组方法 按指定的一列或多列值分组 值相等的为一组 使用GROUPBY子句后 SELECT子句的列名列表中只能出现分组属性和集函数 SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT 3 例31 查询选修了3门以上课程的学生学号 SELECTSno COUNT FROMSCWHEREGrade 90GROUPBYSnoHAVINGCOUNT 3 例32 查询有3门以上课程是90分以上的学生的学号及 90分以上的 课程数 只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别 作用对象不同WHERE子句作用于基表或视图 从中选择满足条件的元组 HAVING短语作用于组 从中选择满足条件的组 同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词一般格式 比较运算符 BETWEEN AND 查询分类 单表查询连接查询嵌套查询集合查询 连接查询 多表查询 同时涉及多个表的查询称为连接查询 用来连接两个或多个表的条件称为连接条件或连接谓词 连接查询的含义 从多个相关的表中的行按给定的条件进行拼接从而形成新表再进行查询数据 用from子句实现 连接运算的类型 内连接 掌握 外连接 左外连接 右外连接 全外连接 理解 自连接 理解 连接查询 复习 连接运算 即从行也从列上进行 R S 等值连接 自然连接R S 连接操作的执行过程 嵌套循环法 NESTED LOOP 首先在表1中找到第一个元组 然后从头开始扫描表2 逐一查找满足连接件的元组 找到后就将表1中的第一个元组与该元组拼接起来 形成结果表中一个元组 表2全部查找完后 再找表1中第二个元组 然后再从头开始扫描表2 逐一查找满足连接条件的元组 找到后就将表1中的第二个元组与该元组拼接起来 形成结果表中一个元组 重复上述操作 直到表1中的全部元组都处理完毕 两表连接 SELECTStudent Sno Sname Ssex Sage Sdept Cno GradeFROMStudent SCWHEREStudent Sno SC Sno 多表连接 例 查询每个学生的学号 姓名 选修的课程名及成绩 SELECTStudent Sno Sname Cname GradeFROMStudent SC CourseWHEREStudent Sno SC SnoandSC Cno Course Cno 结果 Student SnoSnameCnameGrade200215121李勇数据库92200215121李勇数学85200215121李勇信息系统88200215122刘晨数学90200215122刘晨信息系统80 1 等值连接与非等值连接 假设Student表 SC表分别有下列数据 Student表 SC表 例33 查询每个学生及其选修课程的情况 SelectStudent Sc FromStudent ScWhereStudent Sno Sc Sno 结果表 Student SnoSnameSsexSageSdeptSC SnoCnoGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122刘晨女19IS200215122290200215122刘晨女19IS200215122380 非等值连接 连接运算符不是 的连接操作 BETWEEN AND 例 SelectR S FromR SWhereC E 2 自身连接 含义 将同一个表的不同行连接起来 自连接可以看作一张表的两个副本之间进行的连接 要求 在自连接中 必须为表指定两个别名 使之在逻辑上成为两张表 由于所有属性名都是同名属性 因此必须使用别名前缀 例35 查询每一门课的间接先修课 即先修课的先修课 SelectFirst Cno Second CpnoFromCourseFirst CourseSecondWhereFirst Cpno Second Cno First表 Course表 Second表 Course表 查询结果173556 3 内连接与外连接 内连接 含义 将多个表中的共享列值进行比较 把表中满足连接条件的行组合起来 作为查询结果 例如 请将xsqk和xs kc表进行连接 思考 这两张表为什么可以进行连接 连接的条件应该是什么 语法格式有两种 SQL连接方式 select列名表from表1 inner join表2on表1 列名1 表2 列名2传统连接方式 select列名表from表1 表2where表1 列名1 表2 列名2 表示内连接 可省略 连接条件 当两个表中的列名相同时 需加上表名修饰 否则 可省去表名 连接条件 例 查询不及格学生的学号 sno 姓名 sname 课程号 cno 成绩 grade 信息 分析 确定要查询内容的来源 确定连接条件 要查询的表通过什么列名相关联 selectstudent sno sname cno gradefromstudent scwherestudent sno sc snoandgrade 60 为什么要加上表名前缀 而其它字段前不要 因学号在两个表中都有 所以需指明所属的表名 课堂练习 查询不及格学生的学号 课程号 成绩 该课程的学分 该课程的先修课 selectsno o ccredit spno Gradefromsc o oandgrade 60 selectsno o ccredit spno Go oandgrade 60 请思考 student course sc三张表应如何连接 例 查询每个学生的学号 姓名 选修的课程名及成绩 SelectStudent Sno Sname Cname GradeFromStudent SC CourseWhereStudent Sno SC SnoandSC Cno Course Cno 或 SelectStudent Sno Sname Cname GradeFromStudentJoinSConStudent Sno SC SnoJoinCourseonSC Cno Course Cno 3 内连接与外连接 外连接 含义 在内连接中 只把两个表中满足条件的行显示出来 而在外连接中 可以将不满足条件的行也显示出来 外连接的类型左外连接 保留左边表中不匹配的行 右外连接 保留右边表中不匹配的行 全外连接 保留两表中不匹配的行 左外连接的查询结果 右外连接的查询结果 全外连接的查询结果 语法格式 select列名表from表1left right full outer join表2on表1 列名1 表2 列名2 例 查询学生选修课程的情况 输出姓名和课程号 如果有学生没有选修课程 也输出该学生的姓名 Selectsname cnoFromStudentLeftJoinSCWhereStudent Sno SC Sno或Selectsname cnoFromSCRightJoinStudentWhereStudent Sno SC Sno 4 复合条件连接 WHERE子句中含多个连接条件时 称为复合条件连接 例37 查询选修2号课程且成绩在90分以上的所有学生的学号 姓名SELECTStudent Sno student SnameFROMStudent SCWHEREStudent Sno SC SnoAND 连接谓词 SC Cno 2 AND 其他限定条件 SC Grade 90 其他限定条件 5 交叉连接 例 查询所有学生选修所有课程的情况 SelectSname CnameFromStudentCrossJoinCourse 查询分类 单表查询连接查询嵌套查询集合查询 嵌套查询 嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词 概述 含义 一个SELECT FROM WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 或父查询 外层的select语句被称为外部查询 内层的select语句被称为内部查询 或子查询 子查询出现的形式 多数情况下 子查询出现在外部查询的where子句中 并与比较运算符 列表运算符in ANY或ALL谓词 存在运算符exists等一起构成查询条件 完成有关操作 SELECTSname外层查询 父查询FROMStudentWHERESnoIN SELECTSno内层查询 子查询FROMSCWHERECno 2 分类 不相关子查询 子查询的查询条件不依赖于父查询 相关子查询 子查询的查询条件依赖于父查询 求解方法 不相关子查询是由里向外逐层处理 即每个子查询在上一级查询处理之前求解 子查询的结果用于建立其父查询的查找条件 将第一步查询嵌入到第二步查询的条件中SELECTSno Sname SdeptFROMStudentWHERESdeptIN SELECTSdeptFROMStudentWHERESname 刘晨 此查询为不相关子查询 DBMS求解该查询时也是分步去做的 相关子查询首先取外层查询中表的第一个元组 根据它与内层查询相关的属性值处理内层查询 若WHERE子句返回值为真 则取此元组放入结果表 然后再取外层表的下一个元组 重复这一过程 直至外层表全部检查完为止 例41 找出每个学生超过他选修课程平均成绩的课程号 SelectSno CnoFromSCxWhereGrade Selectavg grade FromSCyWherey sno x sno 引出子查询的谓词 带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询 1 带有IN谓词的子查询 例 查询与 刘晨 在同一个系学习的学生 此查询要求可以分步来完成 确定 刘晨 所在系名SELECTSdeptFROMStudentWHERESname 刘晨 结果为 SdeptCS 查找所有在IS系学习的学生 SELECTSno Sname SdeptFROMStudentWHERESdept CS 结果为 SnoSnameSdept200215121刘晨CS200215124张立CS 将第一步查询嵌入到第二步查询的条件中SELECTSno Sname SdeptFROMStudentWHERESdeptIN SELECTSdeptFROMStudentWHERESname 刘晨 此查询为不相关子查询 DBMS求解该查询时也是分步去做的 例38 查询选修了课程名为 信息系统 的学生学号和姓名SELECTSno Sname 最后在Student关系中FROMStudent取出Sno和SnameWHERESnoIN SELECTSno 然后在SC关系中找出选FROMSC修了3号课程的学生学号WHERECnoIN SELECTCno 首先在Course关系中找出 信FROMCourse息系统 的课程号 结果为3号WHERECname 信息系统 用连接查询SELECTSno SnameFROMStudent SC CourseWHEREStudent Sno SC SnoANDS