SQL中的数据定义语言.ppt

实用数据库技术 第3章SQL中的数据定义语言 学习内容 表的定义视图的定义索引的定义 创建表 CREATETABLE语句CREATETABLETable Name column namedatatype size column namedatatype size 关系数据类型 字符串数据数字数据时间数据大型对象 字符串数据 固定长度固定长度的字段总是占据等量的内存空间 不管实际上在它们中间存放的数据量有多少 可变长度而可变长度的字符串只占据它们的内容所消耗的内存量 不管它们的最大尺寸是多少 字符串数据示例 CREATETABLEStudios nameCHAR 20 cityVARCHAR 50 stateCHAR 2 revenueFLOAT 固定长度 可变长度 数字数据 多数数据库都提供至少2种数字数据类型 一种用于整数 另一种用于浮点数 另外还有一些数据库提供更加独特的数字类型 整数浮点数 数字数据示例 CREATETABLEStudios nameCHAR 20 cityVARCHAR 50 stateCHAR 2 revenueFLOAT 数字 时间数据 多数关系数据库支持的另一种独特的数据类型是时间数据 日期时间 日期类型示例 CREATETABLECHECKUP HISTORY CHECKUP NOINTEGERNOTNULL ID NOINTEGERCHECKUP TYPEVARCHAR 30 CHECKUP DATEDATE DOCTOR NAMEVARCHAR 50 FOREIGNKEY CHECKUP TYPE REFERENCESCHECKUP CHECKUP TYPE PRIMARYKEY CHECKUP NO 日期类型 指定键 候选键一定不能在表中任何地方复制每个表可指定多个候选键UNIQUE主键每个表中只能指定一个主键PRIMARYKEY 指定键 外键指明某一列是引用了其他表中某一列 关键字是REFERENCES 候选键 候选键 CandidateKeyUNIQUE关键字用来表明一列 或列的集合 一定不能在表中任何地方复制 在符合UNIQUE限制的一组列中输入值之后 再输入任何复制现存的一组值的值都会被数据库所抛弃 候选键示例 创建具有键的Studios表 CREATETABLEStudios studio idINTEGER nameCHAR 20 cityVARCHAR 50 stateCHAR 2 UNIQUE name 指定name为候选键 主键 要想指定主键而不是候选键 可以使用PRIMARYKEY子句 它可以与UNIQUE子句一起用在同一个CREATETABLE语句中 不过每个表中只能指定一个主键 主键示例 创建具有键的Studios表 同时具有主关键宇和候选关键字的表 CREATETABLEStudios studio idINTEGER nameCHAR 20 cityVARCHAR 50 stateCHAR 2 PRIMARYKEY studio id UNIQUE name 指定studio id为主键 主键示例 还可以在定义列的时候同时指定候选键和主键 以此代替在CREATETABLE语句结尾处单独的子句中创建键 CREATETABLEStudios3 studio idINTEGERPRIMARYKEY nameCHAR 20 UNIQUE cityVARCHAR 50 stateCHAR 2 指定studio id为主键 外部键 外部键也称外键 允许指明某一列是引用了其他表中某一列的外部键 REFERENCES子句用来创建一个表中一组列与表中被参考的候选键之间的关系 外键示例 从Movies表到Studios表的关系是怎样在列的级别上使用REFERENCES子句建立的 CREATETABLEMovies movie titleVARCHAR 40 studio idINTEGERREFERENCESStudios studio id 具有定义在列定义中的关键字的表 列约束 禁止空值NOTNULL其他限制CHECK关键字 禁止空值 当指定列时 将NOTNULL短语添加到列定义中将需要在新的一行插入的同时在该列中输入一个值 这样做还将防止用户在更新表时将该列中的值设置为空值 禁止空值示例 NOTNULL是怎样用在Studios表中创建的 CREATETABLEStudios studio idINTEGERPRIMARYKEY nameCHAR 20 NOTNULL cityVARCHAR 50 NOTNULL stateCHAR 2 NOTNULL 定义没有空值的Studios表 其他限制 很多数据库还允许对可以输入到具体列中的数据施加其他限制 当限制放在某一列中时 通过INSERT或UPDATE语句插入的值将会根据列约束中的条件来估算 只有在数据满足限制条件的情况下 语句才会成功 CHECK关键字用来创建列的限制 CHECK示例1 假设电影数据库中不包括任何预算小于5万美元的电影 CREATETABLEMovies movie titlesVARCHAR 40 PRIMARYKEY studio idINTEGER budgetFLOATCHECK budget 50000 具有列约束的Movies表 CHECK示例2 CREATE语句包含了名为studio id constraint的限制 CREATETABLEMovies movie titleVARCHAR 40 studio idINTEGER release dateDATE CONSTRAINTstudio id constraintCHECK studio id 1000 它只允许将studio id大于1000的影片插入到数据库中 CHECK总结 使用CHECK子句放置在列中的限制的类型或多或少有些原始 可以使用称为触发器 tirgger 的存储过程的特殊类型在数据插入或修改之前在具体的字段中对其进行更高级的检测 使用触发器还可以处理插入之前被插入的数据 默认值 在定义表时 另一个可用的选项是将默认值包括在表的列中 例如 如果希望根据插入日期和时间来标记每个记录 则可以使用DEPAULT关键字来完成 DEPAULT关键字 默认值示例 具有默认值的CREATE语句 CREATETABLEMovies movie titleVARCHAR 40 NOTNULL release dateDATEDEFAULTSYSDATENULL genreVARCHAR 20 DEFAULT Comedy CHECK genreIN Horror Comedy Drama 当前的系统时间为默认时间 SYSDATE Comedy 喜剧 为默认类型 默认值示例 release date和genre列都具有默认值 在release date中 发行日期的值默认为当前日期和时间 记住 在Oracle中 不存在日期和时间分开的类型 不过在列中启用了空值 如果希望将空值插入到release date列中 就必须专门使用它 因为列中已经具有了另一个默认值 genre列默认为 Comedy 喜剧 在列中存在一条限制 确保IN子句中有一个值被插入 默认值总结 可以把你所喜欢的任何有效的表达式用做表中具体列的默认值 例如 如果希望从当前的日期 即某列的默认值 开始标记一周的日期 可以将SYSDATE 7用做具体列的DEFAULT子句中的值 修改表 执行ALTERTABLE语句可以修改表的结构 添加列的语法 ALTERTABLETable NameADDcolumn namedatatype size 删除类的语法 ALTERTABLETable NameDROPCOLUMNcolumn name 修改表示例 具有ALTER添加列 ALTERTABLEMoviesADDauthorVARCHAR2 10 NOTNULL 修改表示例 具有ALTER删除列 ALTERTABLEMoviesDROPCOLUMNauthor 删除表 执行DROPTABLE语句实现删除表 语法如下 DROPTABLETable Name 删除表示例 DROPTABLE实例 DROPTABLEMovies 视图的定义 视图是从一个或几个基本表或其他视图导出的表定义 它本身不独立存储在数据库中 即数据库中只存放视图的定义而不存放视图对应的数据 这些数据仍存放在导出视图的基本表中 因此视图是一个虚表 视图在概念上与基本表等同 用户可以在视图上再定义视图 使用视图有以下作用 视图能够简化用户的操作 视图能使用户以多种角度看待同一数据 视图对重构数据库提供了一定程度的逻辑独立性 视图能够对机密数据提供安全保护 创建视图 在SQL中定义视图的SQL语句如下 CREATEVIEWAS在浏览数据时 SELECT语句对一个或多个表进行操作并产生一个结果集 该结果集也是一个表 创建视图示例 假设您需要经常列出每个部门的雇员数 您可以使用以下语句得到该列表 SELECTdept ID count FROMemployeeGROUPBYdept ID 创建视图示例 创建一个名为DepartmentSize的视图 该视图包含本节开始处给出的SELECT语句的结果 CREATEVIEWDepartmentSizeASSELECTdept ID count FROMemployeeGROUPBYdept ID 创建视图示例 创建一个名为DepartmentSize的视图 该视图包含本节开始处给出的SELECT语句的结果 CREATEVIEWDepartmentSizeASSELECTdept ID count FROMemployeeGROUPBYdept ID 创建视图示例 查询视图视图的使用跟表的使用有一些差别 SELECT FROMDepartmentSize 创建视图总结 视图的使用跟表的使用有一些差别 某些视图上允许使用UPDATE INSERT和DELETE语句 但另一些视图上却不允许使用 这取决于其关联的SELECT语句 您不能更新包含集合函数 例如COUNT 的视图 您也不能更新SELECT语句中包含GROUPBY子句的视图和包含UNION操作的视图 在所有这些情况下 都无法将UPDATE转换为对基表的一个操作 修改视图 ALTERVIEW语句的语法与CREATEVIEW语句的语法相同ALTERVIEWAS 修改视图示例 重命名DepartmentSize视图的列名 在创建视图一节中已作了介绍 以便它们的名称可以提供详细信息 ALTERVIEWDepartmentSize Dept ID NumEmployees ASSELECTdept ID count FROMEmployeeGROUPBYdept ID 删除视图 执行DROPVIEW语句实现 其语法如下 DROPVIEWView Name DROPVIEWDepartmentSize 索引的定义 索引可以使你在从数据库中检索数据时获得一些速度上的提高 但在向数据库插入或在其中更新数据时速度则会发生某些降低 表的索引可以与书籍索引相比拟 在索引中 来自表的数据排序后的列表可以与数据在表中所处的位置相对应 这和书籍索引中的主题的排序列表很相似 在运行查询且通过WHERE子句引用索引字段时 值在索引中而不是在表本身中查找查找 之后数据库立即跳到表中存储数据的位置 创建索引 CREATEINDEX语句CREATE UNIQUE INDEXindex nameONtable column 创建惟一的索引创建惟一索引来确保具体的列或列的集合中每一个值都是惟一UNIQUE关键字 创建索引示例 创建了索引之后 每当语句的WHERE子句中引用movie title列时 在表中找到影片片名之前 会在索引中对其进行查找 CREATEINDEXmovie title indexONMovies movie title 创建索引示例 我们可以同时创建多列的索引 在这些索引创建的时候 对于查询 其中索引中所有的列都出现在WHERE子句中 的搜索次数可能会大大提高 请看一看使用列组合的索引 在这种情况下 笔者将会为人物的姓和名创建单独的索引 创建惟一索引 如前所述 可以将约束放在表的列 或一组列 上 这样 那些列的内容必然相对于各行都是惟一的 还可以创建惟一索引来确保具体的列或列的集合中每一个值都是惟一的 当然 这些索引还可以改善用来搜索这些列的查询的性能 语法 CREATEUNIQUEINDEXindex nameONtable column 创建惟一索引示例 要想为Movie表的movie title和studio id列创建惟一的索引 可以使用下面的SQL语句 CREATEUNIQUEINDEXmovie indexONMovies movie title studio id 删除索引 删除索引的语法如下 DROPINDEXindex Names DROPINDEXmovie index 对列作索引的原因 大量值在查询中经常使用在联接操作中经常使用 何时不作索引 不要对几乎不包含不同值的列作索引也不要对从来都不打算在联接操作中使用或在WHERE子句中使用的列作索引当表更新得比需要更新的次数更加频繁时小表从索引中也不会得到多少好处