SQL中的数据定义语言.ppt

《SQL中的数据定义语言.ppt》由会员分享，可在线阅读，更多相关《SQL中的数据定义语言.ppt（55页珍藏版）》请在三一办公上搜索。

1、实用数据库技术,第3章 SQL中的数据定义语言,学习内容,表的定义 视图的定义 索引的定义,创建表,CREATE TABLE语句CREATE TABLE Table_Name(column_name datatype(size)，column_name datatype(size)，),关系数据类型,字符串数据数字数据时间数据大型对象,字符串数据,固定长度固定长度的字段总是占据等量的内存空间，不管实际上在它们中间存放的数据量有多少。可变长度 而可变长度的字符串只占据它们的内容所消耗的内存量，不管它们的最大尺寸是多少。,字符串数据示例,CREATE TABLE Studios(name CHAR

2、(20)，city VARCHAR(50)，state CHAR(2)，revenue FLOAT),固定长度,可变长度,数字数据,多数数据库都提供至少2种数字数据类型：一种用于整数，另一种用于浮点数。另外还有一些数据库提供更加独特的数字类型。整数浮点数,数字数据示例,CREATE TABLE Studios(name CHAR(20)，city VARCHAR(50)，state CHAR(2)，revenue FLOAT),数字,时间数据,多数关系数据库支持的另一种独特的数据类型是时间数据：日期时间,日期类型示例,CREATE TABLE CHECKUP_HISTORY(CHECKUP_N

3、O INTEGER NOT NULL,ID_NO INTEGER CHECKUP_TYPE VARCHAR(30),CHECKUP_DATE DATE,DOCTOR_NAME VARCHAR(50),FOREIGN KEY(CHECKUP_TYPE)REFERENCES CHECKUP(CHECKUP_TYPE),PRIMARY KEY(CHECKUP_NO),日期类型,指定键,候选键一定不能在表中任何地方复制每个表可指定多个候选键UNIQUE主键每个表中只能指定一个主键 PRIMARY KEY,指定键,外键指明某一列是引用了其他表中某一列，关键字是REFERENCES,候选键,候选键：Can

4、didate Key UNIQUE关键字用来表明一列(或列的集合)一定不能在表中任何地方复制。在符合UNIQUE限制的一组列中输入值之后，再输入任何复制现存的一组值的值都会被数据库所抛弃。,候选键示例,创建具有键的Studios表,CREATE TABLE Studios(studio_id INTEGER,name CHAR(20),city VARCHAR(50),state CHAR(2),UNIQUE(name),指定name为候选键,主键,要想指定主键而不是候选键，可以使用PRIMARY KEY子句。它可以与UNIQUE子句一起用在同一个CREATE TABLE语句中，不过每个表中只

5、能指定一个主键。,主键示例,创建具有键的Studios表，同时具有主关键宇和候选关键字的表。,CREATE TABLE Studios(studio_id INTEGER,name CHAR(20),city VARCHAR(50),state CHAR(2),PRIMARY KEY(studio_id),UNIQUE(name),指定studio_id为主键,主键示例,还可以在定义列的时候同时指定候选键和主键，以此代替在CREATE TABLE语句结尾处单独的子句中创建键。,CREATE TABLE Studios3(studio_id INTEGER PRIMARY KEY,name CH

6、AR(20)UNIQUE,city VARCHAR(50),state CHAR(2),指定studio_id为主键,外部键,外部键也称外键。允许指明某一列是引用了其他表中某一列的外部键。REFERENCES子句用来创建一个表中一组列与表中被参考的候选键之间的关系。,外键示例,从Movies表到Studios表的关系是怎样在列的级别上使用REFERENCES子句建立的,CREATE TABLE Movies(movie_title VARCHAR(40),studio_id INTEGER REFERENCES Studios(studio_id),具有定义在列定义中的关键字的表,列约束,禁止

7、空值 NOT NULL 其他限制 CHECK关键字,禁止空值,当指定列时，将NOT NULL短语添加到列定义中将需要在新的一行插入的同时在该列中输入一个值。这样做还将防止用户在更新表时将该列中的值设置为空值。,禁止空值示例,NOT NULL是怎样用在Studios表中创建的,CREATE TABLE Studios(studio_id INTEGER PRIMARY KEY,name CHAR(20)NOT NULL,city VARCHAR(50)NOT NULL,state CHAR(2)NOT NULL),定义没有空值的Studios表,其他限制,很多数据库还允许对可以输入到具体列中的数

8、据施加其他限制。当限制放在某一列中时，通过INSERT或UPDATE语句插入的值将会根据列约束中的条件来估算。只有在数据满足限制条件的情况下，语句才会成功。CHECK关键字用来创建列的限制。,CHECK示例 1,假设电影数据库中不包括任何预算小于5万美元的电影,CREATE TABLE Movies(movie_titles VARCHAR(40)PRIMARY KEY,studio_id INTEGER,budget FLOAT CHECK(budget 50000),具有列约束的Movies表,CHECK示例 2,CREATE语句包含了名为studio_id_constraint的限制,C

9、REATE TABLE Movies(movie_title VARCHAR(40),studio_id INTEGER,release_date DATE,CONSTRAINT studio_id_constraintCHECK(studio_id 1000),它只允许将studio_id大于1000的影片插入到数据库中,CHECK 总结,使用CHECK子句放置在列中的限制的类型或多或少有些原始。可以使用称为触发器(tirgger)的存储过程的特殊类型在数据插入或修改之前在具体的字段中对其进行更高级的检测。使用触发器还可以处理插入之前被插入的数据。,默认值,在定义表时，另一个可用的选项是将默

10、认值包括在表的列中。例如，如果希望根据插入日期和时间来标记每个记录，则可以使用DEPAULT关键字来完成。DEPAULT关键字。,默认值示例,具有默认值的CREATE语句,CREATE TABLE Movies(movie_title VARCHAR(40)NOT NULL,release_date DATE DEFAULT SYSDATE NULL,genre VARCHAR(20)DEFAULT ComedyCHECK(genre IN(Horror,Comedy,Drama),当前的系统时间为默认时间（SYSDATE）Comedy”(喜剧)为默认类型,默认值示例,release_date

11、和genre列都具有默认值。在release_date中，发行日期的值默认为当前日期和时间(记住，在Oracle中，不存在日期和时间分开的类型)，不过在列中启用了空值。如果希望将空值插入到release_date列中，就必须专门使用它，因为列中已经具有了另一个默认值。genre列默认为“Comedy”(喜剧)，在列中存在一条限制，确保IN子句中有一个值被插入。,默认值总结,可以把你所喜欢的任何有效的表达式用做表中具体列的默认值。例如，如果希望从当前的日期(即某列的默认值)开始标记一周的日期，可以将SYSDATE+7用做具体列的DEFAULT子句中的值。,修改表,执行 ALTER TABLE 语

12、句可以修改表的结构。添加列的语法：ALTER TABLE Table_Name ADD column_name datatype(size)删除类的语法：ALTER TABLE Table_Name DROP COLUMN column_name,修改表示例,具有ALTER添加列,ALTER TABLE Movies ADD author VARCHAR2(10)NOT NULL,修改表示例,具有ALTER删除列,ALTER TABLE Movies DROP COLUMN author,删除表,执行 DROP TABLE 语句实现删除表，语法如下：DROP TABLE Table_Name,

13、删除表 示例,DROP TABLE实例,DROP TABLE Movies,视图的定义,视图是从一个或几个基本表或其他视图导出的表定义。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。因此视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。,使用视图有以下作用,视图能够简化用户的操作；视图能使用户以多种角度看待同一数据；视图对重构数据库提供了一定程度的逻辑独立性；视图能够对机密数据提供安全保护。,创建视图,在SQL中定义视图的SQL语句如下：CREATE VIEW AS 在浏览数据时，SELECT 语句对一个或

14、多个表进行操作并产生一个结果集，该结果集也是一个表。,创建视图 示例,假设您需要经常列出每个部门的雇员数。您可以使用以下语句得到该列表：,SELECT dept_ID,count(*)FROM employeeGROUP BY dept_ID,创建视图 示例,创建一个名为 DepartmentSize 的视图，该视图包含本节开始处给出的 SELECT 语句的结果：,CREATE VIEW DepartmentSize ASSELECT dept_ID,count(*)FROM employeeGROUP BY dept_ID,创建视图 示例,创建一个名为 DepartmentSize 的视图，

15、该视图包含本节开始处给出的 SELECT 语句的结果：,CREATE VIEW DepartmentSize ASSELECT dept_ID,count(*)FROM employeeGROUP BY dept_ID,创建视图 示例,查询视图视图的使用跟表的使用有一些差别,SELECT*FROM DepartmentSize,创建视图 总结,视图的使用跟表的使用有一些差别，某些视图上允许使用 UPDATE、INSERT 和 DELETE 语句，但另一些视图上却不允许使用，这取决于其关联的 SELECT 语句。您不能更新包含集合函数（例如 COUNT(*)）的视图。您也不能更新 SELECT

16、语句中包含 GROUP BY 子句的视图和包含 UNION 操作的视图。在所有这些情况下，都无法将 UPDATE 转换为对基表的一个操作。,修改视图,ALTER VIEW 语句的语法与 CREATE VIEW 语句的语法相同 ALTER VIEW AS,修改视图 示例,重命名 DepartmentSize 视图的列名（在创建视图一节中已作了介绍），以便它们的名称可以提供详细信息。,ALTER VIEW DepartmentSize(Dept_ID,NumEmployees)AS SELECT dept_ID,count(*)FROM Employee GROUP BY dept_ID,删除视图

17、,执行 DROP VIEW 语句实现，其语法如下：DROP VIEW View_Name,DROP VIEW DepartmentSize,索引的定义,索引可以使你在从数据库中检索数据时获得一些速度上的提高，但在向数据库插入或在其中更新数据时速度则会发生某些降低。表的索引可以与书籍索引相比拟。在索引中，来自表的数据排序后的列表可以与数据在表中所处的位置相对应。这和书籍索引中的主题的排序列表很相似。在运行查询且通过WHERE子句引用索引字段时，值在索引中而不是在表本身中查找查找，之后数据库立即跳到表中存储数据的位置。,创建索引,CREATE INDEX 语句CREATE UNIQUE INDEX

18、 index_name ON table(column)创建惟一的索引创建惟一索引来确保具体的列或列的集合中每一个值都是惟一UNIQUE 关键字,创建索引 示例,创建了索引之后，每当语句的WHERE子句中引用movie_title列时，在表中找到影片片名之前，会在索引中对其进行查找。,CREATE INDEX movie_title_index ON Movies(movie_title),创建索引 示例,我们可以同时创建多列的索引。在这些索引创建的时候，对于查询(其中索引中所有的列都出现在WHERE子句中)的搜索次数可能会大大提高。请看一看使用列组合的索引；在这种情况下，笔者将会为人物的姓和

19、名创建单独的索引。,创建惟一索引,如前所述，可以将约束放在表的列(或一组列)上，这样，那些列的内容必然相对于各行都是惟一的。还可以创建惟一索引来确保具体的列或列的集合中每一个值都是惟一的。当然，这些索引还可以改善用来搜索这些列的查询的性能。语法：CREATE UNIQUE INDEX index_name ON table(column),创建惟一索引 示例,要想为Movie表的movie_title和studio_id列创建惟一的索引，可以使用下面的SQL语句：,CREATE UNIQUE INDEX movie_index ON Movies(movie_title,studio_id),删除索引,删除索引的语法如下：DROP INDEX index_Names,DROP INDEX movie_index,对列作索引的原因,大量值在查询中经常使用在联接操作中经常使用,何时不作索引,不要对几乎不包含不同值的列作索引也不要对从来都不打算在联接操作中使用或在WHERE子句中使用的列作索引当表更新得比需要更新的次数更加频繁时小表从索引中也不会得到多少好处,