数据库原理课件--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 大型数据库SybaseINIXSQLServerOracleDB2INGRES 小型数据库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 建立索引 续 聚簇索引建立聚簇索引后 基本表中数据也需要按指定的聚簇属性值的升序或降序存放 也即聚簇索引的索引项顺序与表中记录的物理