数据库原理课件--03_SQL(1).ppt

1 数据库系统概论AnIntroductiontoDatabaseSystem第三章关系数据库标准语言SQL 2 第三章关系数据库标准语言SQL 3 1SQL概述3 2学生 课程数据库3 3数据定义3 4数据查询3 5数据更新3 6视图3 7小结 3 3 1SQL概述 3 1 1SQL的产生与发展 Chamberlin SEQUEL SQL 大型数据库SybaseINFORMIXSQLServerOracleDB2INGRES 小型数据库FoxProAccess 3 1SQL概述 SQL语言标准化标准大致页数发布日期SQL 861986 10SQL 89 FIPS127 1 120页1989年SQL 92622页1992年SQL991700页1999年SQL2003360页2003年 5 3 1 2SQL的特点 1 综合统一 SQL集数据定义语言DDL 数据操纵DML 数据控制语言DCL的功能于一体 语言风格统一 可独立完成数据库生命周期中的全部活动 非关系模型的数据语言一般分为 模式数据定义语言 模式DDL 外模式数据定义语言 外模式DDL 数据存储有关的描述语言 DSDL 数据操纵语言 DML 定义关系模式 插入数据 建立数据库 对数据库中的数据进行查询和更新 数据库的重构和维护 数据库安全性 完整性控制 6 3 1 2SQL的特点 续 2 高度非过程化 只要提出 做什么 而无须指明 怎么做 非关系数据模型的数据操纵语言 面向过程 必须制定存取路径SQL只要提出 做什么 无须了解存取路径 存取路径的选择以及SQL的操作过程由系统自动完成 7 3 1 2SQL的特点 续 3 面向集合的操作方式 采用集合操作方式 操作对象和结果都是元组的集合 非关系数据模型采用面向记录的操作方式 操作对象是一条记录SQL采用集合操作方式操作对象 查找结果可以是元组的集合插入 删除 更新操作的对象可以是元组的集合 8 3 1 2SQL的特点 续 4 以同一种语法结构提供两种使用方法 既是独立的语言 又是嵌入式语言 SQL是独立的语言能够独立地用于联机交互的使用方式SQL又是嵌入式语言SQL能够嵌入到高级语言 例如C C Java 程序中 供程序员设计程序时使用 9 3 1 2SQL的特点 续 5 语言简洁 易学易用 类似于英语的自然语言简洁易用 核心功能只用9个动词 10 3 1 3SQL的基本概念 SQL语言支持关系数据库三级模式结构 外模式对应于视图和部分基本表 模式对应于基本表 内模式对应于存储文件 11 SQL的基本概念 续 12 基本表是独立存在的表 一个关系对应一个表 一个 或多个 基本表对应一个存储文件 一个表可带若干个索引 索引也存放在存储文件中 SQL的基本概念 续 返回 存储文件的逻辑结构组成了关系数据库的内模式 存储文件的物理结构是任意的 对用户是透明的 13 3 2学生 课程数据库 本章用到了三个表 学生表 Student Sno Sname Ssex Sdept 课程表 Course Cno Cname Cpno Ccredit 学生选课表 SC Sno Cno Grade 返回 14 3 3数据定义 15 3 3数据定义 注意 SQL的数据定义功能包括模式定义 表定义 视图和索引的定义 但只允许修改表定义 而模式定义 视图定义和索引定义的修改需要先将它们删除再重建 16 3 3 1模式的定义与删除 1 定义模式CREATESCHEMAAUTHORIZATION 在SQL中 一个SQL模式定义为基本表的集合 一个SQL模式由模式名和模式拥有者的用户名或帐号来确定 并包含模式中每一个元素 基本表 视图 索引等 的定义 创建SQL模式 就是定义了一个存储空间 17 定义模式 续 例1 定义一个学生 课程模式S TCREATESCHEMA S T AUTHORIZATIONWANG 为用户WANG定义了一个模式S T 例2 CREATESCHEMAAUTHORIZATIONWANG 隐含为用户名WANG如没有指定 则隐含为 18 定义模式 续 定义模式实际上定义了一个命名空间在这个空间中可以定义该模式包含的数据库对象 例如基本表 视图 索引等 在CREATESCHEMA中可以接受CREATETABLE CREATEVIEW和GRANT子句 CREATESCHEMAAUTHORIZATION 19 定义模式 续 例3 CREATESCHEMATESTAUTHORIZATIONZHANGCREATETABLETAB1 COL1SMALLINT COL2INT COL3CHAR 20 COL4NUMERIC 10 3 COL5DECIMAL 5 2 为用户ZHANG创建了一个模式TEST 并在其中定义了一个表TAB1 20 模式的定义与删除 续 2 删除模式DROPSCHEMA CASCADE和RESTIRCT两者必须选一 CASCADE 级联 在删除模式时连同其中的所有数据库对象一起删除 RESTRICT 限制 如果该模式中已定义了下属的数据库对象 则拒绝执行该删除语句 只有该模式下无任何下属对象时才能执行 21 删除模式 续 例4 DROPSCHEMAZHANGCASCADE 删除模式ZHANG同时该模式中定义的表TAB1也被删除 22 3 3 2基本表的定义 删除与修改 1 定义基本表CREATETABLE 所要定义的基本表的名字 组成该表的各个属性 列 相应属性列的完整性约束条件 一或多个属性列的完整性约束条件 23 例 建立一个 学生 表Student 由学号Sno 姓名Sname 性别Ssex 年龄Sage 所在系Sdept属性组成 其中学号不能为空 值是唯一的 并且姓名取值也唯一 CREATETABLEStudent SnoCHAR 5 NOTNULLUNIQUE SnameCHAR 20 UNIQUE SsexCHAR 1 SageINT SdeptCHAR 15 24 学生表Student 例5 建立 学生 表Student 学号是主码 姓名取值唯一 CREATETABLEStudent SnoCHAR 9 PRIMARYKEY 列级完整性约束条件 SnameCHAR 20 UNIQUE Sname取唯一值 SsexCHAR 2 SageSMALLINT SdeptCHAR 20 主码 25 课程表Course 例6 建立一个 课程 表Course CREATETABLECourse CnoCHAR 4 PRIMARYKEY CnameCHAR 40 CpnoCHAR 4 CcreditSMALLINT FOREIGNKEY Cpno REFERENCESCourse Cno 先修课 Cpno是外码被参照表是Course被参照列是Cno 26 学生选课表SC 例7 建立一个 学生选课 表SC CREATETABLESC SnoCHAR 9 CnoCHAR 4 GradeSMALLINT PRIMARYKEY Sno Cno 主码由两个属性构成 必须作为表级完整性进行定义 FOREIGNKEY Sno REFERENCESStudent Sno 表级完整性约束条件 Sno是外码 被参照表是Student FOREIGNKEY Cno REFERENCESCourse Cno 表级完整性约束条件 Cno是外码 被参照表是Course 27 定义基本表 续 常用完整性约束主码约束 PRIMARYKEY唯一性约束 UNIQUE非空值约束 NOTNULL参照完整性约束 FOREIGNKEY REFERENCES 28 PRIMARYKEY与UNIQUE的区别 可使用UNIQUE约束确保在非主键列中不输入重复值 一个表可以定义多个UNIQUE约束 而只能定义一个PRIMARYKEY约束 允许空值的列上可以定义UNIQUE约束 而不能定义PRIMARYKEY约束 29 2 数据类型 SQL中域的概念用数据类型来实现 定义表的属性时 需要指明其数据类型及长度 选用哪种数据类型取值范围要做哪些运算 30 SQLServer数据类型 整数数据 精确数值 近似浮点数值 日期时间数据 bigint int smallint tinyint numeric和decimal float和real datetime与smalldatetime 31 SQLServer数据类型 字符串数据 Unicode字符串数据 二进制数据 货币数据 char varchar text nchar nvarchar与ntext binary varbinary image money与smallmoney 特殊数据 timestamp和uniqueidentifier 32 3 修改基本表 ALTERTABLE ADD 完整性约束 DROP DROPCOLUMN ALTERCOLUMN 要修改的基本表ADD子句 增加新列和新的完整性约束条件DROP子句 删除指定的完整性约束条件DROPCOLUMN子句 删除列ALTERCOLUMN子句 用于修改原有的列定义 33 例题 例8 向Student表增加 入学时间 列 其数据类型为日期型 ALTERTABLEStudentADDS entranceDATE 不论基本表中原来是否已有数据 新增加列一律为空值 34 例题 例9 将年龄的数据类型改为整型ALTERTABLEStudentALTERCOLUMNSageINT 修改原有的列定义有可能会破坏已有数据 例10 增加课程名称必须取唯一值的约束条件 ALTERTABLECourseADDUNIQUE Cname 35 4 删除基本表 DROPTABLE RESTRICT CASCADE CASCADE 删除基本表的同时 相关的依赖对象如视图等都被一起删除 RESTRICT 若删的基本表被其它表的约束引用 有视图 存储过程 触发器或函数时 就不能被删除 否则可以 缺省值是RESTRICT 36 删除基本表 例5 删除Student表DROPTABLEStudentCASCADE 基本表定义被删除 数据被删除表上建立的索引 视图 触发器等一般也将被删除 37 3 3 3索引的建立与删除 建立索引是加快查询速度的有效手段 索引的分类聚簇索引唯一索引非唯一索引索引是关系数据库的内部实现技术 属于内模式的范畴CREATEINDEX语句定义索引时 可以定义索引是唯一索引 非唯一索引或聚簇索引 非聚簇索引 38 索引的建立与删除 续 谁可以建立索引 DBA或表的属主 即建立表的人 根据需要建立谁维护索引 DBMS自动完成如何使用索引 DBMS自动选择是否使用索引以及使用哪些索引 39 1 建立索引 CREATE UNIQUE CLUSTER INDEXON 用指定要建索引的基本表名字索引可建立在该表的一列或多列上 各列名间用逗号分隔用指定索引值的排列次序 升序 ASC 降序 DESC 缺省值 ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引 40 例题 例14 为学生 课程数据库中的Student Course SC三个表建立索引 其中Student表按学号升序建唯一索引 Course表按课程号升序建唯一索引 SC表按学号升序和课程号降序建唯一索引 CREATEUNIQUEINDEXStusnoONStudent Sno CREATEUNIQUEINDEXCoucnoONCourse Cno CREATEUNIQUEINDEXSCnoONSC SnoASC CnoDESC 41 建立索引 续 唯一值索引对于已含重复值的属性列不能建UNIQUE索引 对某个列建立UNIQUE索引后 插入新记录时DBMS会自动检查新记录在该列上是否取了重复值 这相当于增加了一个UNIQUE约束 42 建立索引 续 聚簇索引建立聚簇索引后 基本表中数据也需要按指定的聚簇属性值的升序或降序存放 也即聚簇索引的索引项顺序与表中记录的物理顺序一致 例13 CREATECLUSTERINDEXStusnameONStudent Sname 在Student表的Sname列上建立一个聚簇索引 而且Student表中的记录将按照Sname值的升序存放 43 2 删除索引 DROPINDEX 删除索引时 系统会从删去有关该索引的描述 返回 例15 删除Student表的Stusname索引 DROPINDEXStusname 44 索引的优点通过创建唯一索引 可以保证数据记录的唯一性 可以大大加快数据检索速度 3 3 2索引的建立与删除 索引的缺点创建索引要花费时间和占用存储空间 建立索引加快了数据检索速度 却减慢了数据修改速度 45 3 4数据查询 3 4 1概述3 4 2单表查询3 4 3连接查询3 4 4嵌套查询3 4 5集合查询3 4 6SELECT语句的一般格式 返回 46 3 4 1概述 SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC SELECT子句 指定要显示的属性列FROM子句 指定查询对象 基本表或视图 WHERE子句 指定查询条件GROUPBY子句 对查询结果按指定列的值分组 该属性列值相等的元组为一个组 通常会在每组中作用聚集函数 47 语句格式 SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC HAVING短语 筛选出只有满足指定条件的组ORDERBY子句 对查询结果表按指定列值的升序或降序排序 返回 48 3 4 2单表查询 查询仅涉及一个表 是一种最简单的查询操作一 选择表中的若干列二 选择表中的若干元组三 ORDERBY子句四 聚集函数五 GROUPBY子句 返回 49 一 选择表中的若干列 查询指定列 例1 查询全体学生的学号与姓名 SELECTSno SnameFROMStudent 例2 查询全体学生的姓名 学号 所在系 SELECTSname Sno SdeptFROMStudent 50 查询全部列 选择表中的若干列 续 例3 查询全体学生的详细记录 SELECTSno Sname Ssex Sage SdeptFROMStudent 或SELECT FROMStudent 51 查询经过计算的列 选择表中的若干列 续 例4 查全体学生的姓名及其出生年份 SELECTSname 2011 SageFROMStudent 输出结果 Sname无列名李勇1984刘晨1985王敏1986张立1985 52 例5 查询全体学生的姓名 出生年份和所有系 要求用小写字母表示所有系名 SELECTSname YearofBirth 2011 Sage LOWER Sdept FROMStudent 选择表中的若干列 续 输出结果 Sname无列名无列名无列名 李勇YearofBirth 1976cs刘晨YearofBirth 1977is王名YearofBirth 1978ma张立YearofBirth 1977is 53 例5 1 使用列别名改变查询结果的列标题 SELECTSnameNAME YearofBirth BIRTH 2000 SageBIRTHDAY LOWER Sdept DEPARTMENTFROMStudent 返回 输出结果 NAMEBIRTHBIRTHDAYDEPARTMENT 李勇YearofBirth 1976cs刘晨YearofBirth 1977is王名YearofBirth 1978ma张立YearofBirth 1977is 54 二 选择表中的若干元组 1 消除取值重复的行 在SELECT子句中使用DISTINCT短语 如没有指定DISTINCT关键词 则缺省为ALL假设SC表中有下列数据SnoCnoGrade 9500119295001285950013889500229095002380 55 例6 查询选修了课程的学生学号 SELECTSnoFROMSC或SELECTALLSnoFROMSC结果 Sno 9500195001950019500295002 2 SELECTDISTINCTSnoFROMSC 结果 Sno 9500195002 95001和95002出现了多次 如何各显示一次 56 2 查询满足条件的元组 WHERE子句常用的查询条件 57 1 比较大小 例7 查询计算机科学系全体学生的名单 SELECTSnameFROMStudentWHERESdept CS 例8 查询所有年龄在20岁以下的学生姓名及其年龄 SELECTSname SageFROMStudentWHERESage 20 例9 查询考试成绩有不及格的学生的学号 SELECTDISTINCTSnoFROMSCWHEREGrade 60 58 2 确定范围 例10 查询年龄在20 23岁 包括20岁和23岁 之间的学生的姓名 系别和年龄 SELECTSname Sdept SageFROMStudentWHERESageBETWEEN20AND23 59 例题 续 例11 查询年龄不在20 23岁之间的学生姓名 系别和年龄 SELECTSname Sdept SageFROMStudentWHERESageNOTBETWEEN20AND23 60 3 确定集合 使用谓词IN NOTIN 用逗号分隔的一组取值 例12 查询信息系 IS 数学系 MA 和计算机科学系 CS 学生的姓名和性别 SELECTSname SsexFROMStudentWHERESdeptIN IS MA CS 61 3 确定集合 例13 查询既不是信息系 数学系 也不是计算机科学系的学生的姓名和性别 SELECTSname SsexFROMStudentWHERESdeptNOTIN IS MA CS 62 4 字符串匹配 NOT LIKE ESCAPE 指定的匹配模板固定字符串含通配符的字符串 63 例题 1 匹配模板为固定字符串 例14 查询学号为95001的学生的详细情况 SELECT FROMStudentWHERESnoLIKE 95001 等价于 SELECT FROMStudentWHERESno 95001 64 通配符 百分号 代表任意长度 长度可以为0 的字符串例 a b表示以a开头 以b结尾的任意长度的字符串 如acb addgb ab等都满足该匹配串 下横线 代表任意单个字符例 a b表示以a开头 以b结尾的长度为3的任意字符串 如acb afb等都满足该匹配串 65 2 匹配模板为含通配符的字符串 例15 查询所有姓刘学生的姓名 学号和性别 SELECTSname Sno SsexFROMStudentWHERESnameLIKE 刘 66 2 匹配模板为含通配符的字符串 例16 查询姓 欧阳 且全名为三个汉字的学生的姓名 SELECTSnameFROMStudentWHERESnameLIKE 欧阳 一个汉字要占两个字符的位置 故要用两个 67 例题 续 例17 查询名字中第2个字为 阳 字的学生的姓名和学号 SELECTSname SnoFROMStudentWHERESnameLIKE 阳 68 例题 续 例18 查询所有不姓刘的学生姓名 SELECTSname Sno SsexFROMStudentWHERESnameNOTLIKE 刘 69 ESCAPE短语 当用户要查询的字符串本身就含有 或 时 要使用ESCAPE 短语对通配符进行转义 使用换码字符将通配符转义为普通字符 例19 查询DB Design课程的课程号和学分 SELECTCno CcreditFROMCourseWHERECnameLIKE DB Design ESCAPE 70 3 使用换码字符将通配符转义为普通字符 例20 查询以 DB 开头 且倒数第3个字符为i的课程的详细情况 SELECT FROMCourseWHERECnameLIKE DB i ESCAPE 71 5 涉及空值的查询 使用谓词ISNULL或ISNOTNULL注意 ISNULL 不能用 NULL 代替 例21 某些学生选修课程后没有参加考试 所以有选课记录 但没有考试成绩 查询缺少成绩的学生的学号和相应的课程号 SELECTSno CnoFROMSCWHEREGradeISNULL 72 例题 续 例22 查所有有成绩的学生学号和课程号 SELECTSno CnoFROMSCWHEREGradeISNOTNULL 73 6 多重条件查询 用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词 NOT IN NOT BETWEEN AND 74 例题 例23 查询计算机系年龄在20岁以下的学生姓名 SELECTSnameFROMStudentWHERESdept CS ANDSage 20 75 改写 例12 例12 查询信息系 IS 数学系 MA 和计算机科学系 CS 学生的姓名和性别 SELECTSname SsexFROMStudentWHERESdeptIN IS MA CS 可改写为 SELECTSname SsexFROMStudentWHERESdept IS ORSdept MA ORSdept CS 76 改写 例10 例10 查询年龄在20 23岁 包括20岁和23岁 之间的学生的姓名 系别和年龄 返回 SELECTSname Sdept SageFROMStudentWHERESageBETWEEN20AND23 可改写为 SELECTSname Sdept SageFROMStudentWHERESage 20ANDSage 23 77 三 ORDERBY子句 可以按一个或多个属性列排序升序 ASC 降序 DESC 缺省值为升序当排序列含空值时ASC 排序列为空值的元组最后显示DESC 排序列为空值的元组最先显示 78 对查询结果排序 续 例24 查询选修了3号课程的学生的学号及其成绩 查询结果按分数降序排列 结果 SnoGrade 9501095024950079295003829501082950097595014619500255 SELECTSno GradeFROMSCWHERECno 3 ORDERBYGradeDESC 79 对查询结果排序 续 例25 查询全体学生情况 查询结果按所在系的系号升序排列 同一系中的学生按年龄降序排列 SELECT FROMStudentORDERBYSdept SageDESC 返回 80 四 使用聚集函数 5类主要聚集函数 计数COUNT DISTINCT ALL COU