常用数据定义语言（DDL）.ppt

1、,SQL Server 2000管理及 应用开发教程,授课教师： 职务：,第7章 常用数据定义语言（DDL）,课程描述 在第3章中，已经介绍了SQL Server数据库的创建和维护。但是这些数据库操作都是在SQL Server企业管理器中手工进行的。这种操作方式非常简单直观，便于学习和掌握。但是，它不能将工作的过程保存下来，每次操作都需要重复进行，操作量大的时候不易使用。,本章知识点,数据库管理语句 表管理语句 索引管理语句 视图管理语句,7.1 数据库管理语句,创建数据库语句 修改数据库语句 删除数据库语句,7.1.1 创建数据库语句,CREATE DATABASE database_nam

2、e ON ,.n , ,.n LOG ON ,.n := PRIMARY ( NAME = logical_file_name , FILENAME = os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) ,.n := FILEGROUP filegroup_name ,.n ,1指定数据库名称 database_name,CREATE DATABASE mytest,2设置存储数据库的磁盘文件,【例7.1】下面的脚本用于创建数据库HrSystem1。 USE

3、 master GO CREATE DATABASE HrSystem1 ON PRIMARY ( NAME = HrDB1, FILENAME = c:program filesmicrosoft sql servermssqldatahrdbdat1.mdf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = HrDB2, FILENAME = c:program filesmicrosoft sql servermssqldatahrdbdat2.ndf, SIZE = 100MB, MAXSIZE = 200, FILEGR

4、OWTH = 20), ( NAME = HrDB3, FILENAME = c:program filesmicrosoft sql servermssqldatahrdbdat3.ndf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20),查看前面创建数据库的数据文件,3设置存储数据库日志的磁盘文件,【例7.2】上面的示例程序如果增加数据库日志部分的设置，将变成以下内容： USE master GO CREATE DATABASE HrSystem2 ON PRIMARY ( NAME = HrDB1, FILENAME = c:program f

5、ilesmicrosoft sql servermssqldatahrdb2dat1.mdf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = HrDB2, FILENAME = c:program filesmicrosoft sql servermssqldatahrdb2dat2.ndf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20),例7.2（2）,( NAME = HrDB3, FILENAME = c:program filesmicrosoft sql servermss

6、qldatahrdb2dat3.ndf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = HrDBlog1, FILENAME = c:program filesmicrosoft sql servermssqldataarchlog1.ldf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = HrDBlog2, FILENAME = c:program filesmicrosoft sql servermssqldataarchlog2.ldf, SIZ

7、E = 100MB, MAXSIZE = 200, FILEGROWTH = 20) GO,查看前面创建数据库的日志文件,7.1.2 修改数据库语句,ALTER DATABASE语句的基本语法结构如下： ALTER DATABASE database ADD FILE ,.n TO FILEGROUP filegroup_name | ADD LOG FILE ,.n | REMOVE FILE logical_file_name | MODIFY FILE | MODIFY NAME = new_dbname ,1指定要添加的数据文件,在ALTER DATABASE语句中： ADD FILE

8、子句表示要添加数据文件。 TO FILEGROUP子句指定要将指定文件添加到的文件组。 filegroup_name是要添加指定文件的文件组名称。,【例7.3】,要在数据库HrSystem2中添加一个数据文件hrdb4，可以使用以下命令： ALTER DATABASE HrSystem2 ADD FILE ( NAME = hrdb4, FILENAME = c:program filesmicrosoft sql servermssqldatahrdbdat4.ndf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) GO 运行结果显示： 以 10

9、0.00 MB 为单位在磁盘 hrdb4 上扩展数据库。,检查“HrSystem2属性”,2添加日志文件,在ALTER DATABASE语句中，ADD LOG FILE子句指定要将日志文件添加到指定的数据库。 【例7.4】要在数据库HrSystem2中添加一个日志文件hrdblog3，可以使用以下命令： ALTER DATABASE HrSystem2 ADD LOG FILE ( NAME = hrdblog3, FILENAME = c:program filesmicrosoft sql servermssqldatahrdblog3.ldf, SIZE = 100MB, MAXSIZE

10、 = 200, FILEGROWTH = 20) GO 运行结果显示： 以 100.00 MB 为单位在磁盘 hrdblog3 上扩展数据库。,3删除文件,在CREATE DATABASE语句中，REMOVE FILE从数据库系统表中删除文件描述并删除物理文件。只有在文件为空时才能删除。 【例7.5】要将hrdb4从HrSystem2数据库中删除，可以使用以下命令： ALTER DATABASE HrSystem2 REMOVE FILE hrdb4 GO 运行结果显示： 文件 hrdb4 已删除。,4修改文件,在CREATE DATABASE语句中，MODIFY FILE子句指定要更改给定的

11、文件，更改选项包括 FILENAME、SIZE、FILEGROWTH 和 MAXSIZE。一次只能更改这些属性中的一种。必须在 中指定 NAME，以标识要更改的文件。如果指定了 SIZE，那么新大小必须比文件当前大小要大。只能为 tempdb 数据库中的文件指定 FILENAME，而且新名称只有在SQL Server重新启动后才能生效。,【例7.6】,要把数据库HrSystem2中的hrdb3的文件大小更改为200MB，可以使用以下命令： ALTER DATABASE HrSystem2 MODIFY FILE ( NAME = HrDB3, SIZE = 200MB) GO,5重命名数据库,

12、使用MODIFY NAME = new_dbname子句可以重命名数据库。 【例7.7】要把数据库HrSystem2重命名为HrSystem3，可以使用以下命令： ALTER DATABASE HrSystem2 MODIFY NAME = GO,7.1.3 删除数据库语句,DROP DATABASE语句从SQL Server中删除一个或多个数据库。删除数据库将删除数据库所使用的数据库文件和磁盘文件。 DROP DATABASE语句的语法结构如下： DROP DATABASE database_name ,.n database_name指要删除的数据库名称。可以同时删除多个数据库。 【例7.

13、8】要将数据库HrSystem2删除，可以执行以下命令： DROP DATABASE HrSystem2 GO,7.2 表管理语句,创建表语句 修改表语句 删除表语句,7.2.1 创建表语句,CREATE TABLE 表名 ( 列名1数据类型, 列名2数据类型, 列名n数据类型 ),【例7.9】,在数据库HrSystem2中创建表EmpInfo，代码如下： USE HrSystem2 GO CREATE TABLE EmpInfo ( Emp_id int, Emp_name varchar (50), Sex char (2), Title varchar (50), Wage float,

14、 IdCard varchar (20), Dep_id tinyint ) GO,1表名的定义,在CREATE TABLE语句中，共有以下3种定义表名的方式： （1）CREATE TABLE 表名。例如CREATE TABLE EmpInfo。 （2）CREATE TABLE 数据库名.表名。例如CREATE TABLE HrSystem2.EmpInfo。 （3）CREATE TABLE 数据库名.属主名.表名。例如CREATE TABLE HrDB2.dbo.EmpInfo。,2NULL / NOT NULL,NULL和NOT NULL参数设置列的空与非空。如果希望某一列必须有数据，则在

15、该列的定义后加上NOT NULL参数。默认情况下，允许列为空，所以不必在列定义后添加NULL参数。 【例7.10】在创建表EmpInfo时，将Emp_Name列定义为非空，可以使用以下代码： CREATE TABLE EmpInfo ( Emp_id int, Emp_name varchar (50) NOT NULL, ),3PRIMARY KEY 约束,定义主键的基本语法为： CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED 【例7.11】在创建表EmpInfo时，将Emp_Id列定义为主键，可以使用以下代码：

16、 CREATE TABLE EmpInfo ( Emp_id int PRIMARY KEY CLUSTERED, Emp_name varchar (50) NOT NULL, ),4UNIQUE约束,定义UNIQUE约束的基本语法为： CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED ,【例7.12】,在创建表EmpInfo时，定义员工身份证号码列Id_Card为UNIQUE约束，可以使用以下代码： CREATE TABLE EmpInfo ( Emp_id int PRIMARY KEY CLUSTERED,

17、Emp_name varchar (50) NOT NULL, Id_Cardvarchar(20)UNIQUE NONCLUSTERED, ),【例7.12】,也可以使用用户自定义名称的方式，代码如下： CREATE TABLE EmpInfo ( Emp_id int PRIMARY KEY CLUSTERED, Emp_name varchar (50) NOT NULL, Id_Cardvarchar(20), CONSTRAINT U_Id_Card UNIQUE NONCLUSTERED, ),5FOREIGN KEY 约束,定义FOREIGN KEY约束的基本语法为： CONST

18、RAINT constraint_name FOREIGN KEY ( column ,.n ) REFERENCES ref_table ( ref_column ,.n ) ,【例7.13】,在EmpInfo表的Dep_Id列和DepInfo表的Dep_Id列之间建立FOREIGN KEY约束，可以使用以下代码： CREATE TABLE EmpInfo ( Emp_id int PRIMARY KEY CLUSTERED, Emp_name varchar (50) NOT NULL, Dep_IdtinyintNOT NULL REFERENCES DepInfo(Dep_Id) ),

19、6CHECK 约束,定义CHECK约束的基本语法为： CONSTRAINT constraint_name CHECK NOT FOR REPLICATION ( search_conditions ),【例7.14】,在创建表EmpInfo时，要求所有员工的工资大于500，则可以对Wage列使用CHECK约束，代码如下： CREATE TABLE EmpInfo ( Emp_id int PRIMARY KEY CLUSTERED, Emp_name varchar (50) NOT NULL, Wage float, CHECK (Wage 500), ),7DEFAULT 定义,如果要把

20、Title列的默认值设置为职员，可以使用以下代码： CREATE TABLE EmpInfo ( Emp_id int PRIMARY KEY CLUSTERED, Emp_name varchar (50) NOT NULL, Title varchar (50), DEFAULT ( 职员 ), ),8计算列,【例7.15】在创建表EmpInfo时增加“年薪”列YearSalary，YearSalary = Wage * 12。创建表的代码如下： CREATE TABLE EmpInfo ( Emp_id int, Emp_name varchar (50), Sex char (2),

21、Title varchar (50), Wage float, IdCard varchar (20), Dep_id tinyint, YearSalary AS Wage * 12 ),7.2.2 修改表语句,1添加列 2删除列 3添加约束 4删除约束 5NOCHECK选项 6使用sp_rename存储过程重命名表或列,1添加列,【例7.16】修改表TestTable，添加列column_b，代码如下： ALTER TABLE TestTable ADD column_b VARCHAR(20),2删除列,【例7.17】修改表TestTable，删除列column_b，代码如下： ALTE

22、R TABLE TestTable DROP COLUMN column_b,3添加约束,【例7.18】在表EmpInfo的Emp_Id列上设置主键PK_Emp_Id，代码如下： ALTER TABLE EmpInfo ADD CONSTRAINT PK_Emp_Id PRIMARY KEY NONCLUSTERED( Emp_Id ),4删除约束,使用ALTER TABLE语句的DROP CONSTRAINT子句可以删除表中已有的约束。例如： ALTER TABLE EmpInfo DROP CONSTRAINT PK_Emp_Id,5NOCHECK选项,如果只是暂停某一约束的作用，而并不想

23、真正地删除此约束，可以使用NOCHECK关键字。例如： ALTER TABLE EmpInfo NOCHECK CONSTRAINT PK_Emp_Id 如果要恢复被暂停的约束，只需使用CHECK关键字。例如 ALTER TABLE EmpInfo CHECK CONSTRAINT PK_Emp_Id,6使用sp_rename重命名表或列,可以使用sp_rename存储过程修改表或列的名称，其基本语法如下： sp_rename 原对象名, 新对象名, 对象类型,【例7.19】,使用存储过程sp_rename将数据库HrSystem中的表EmpInfo重命名为Employees，具体语句如下：

24、USE HrSystem GO EXEC sp_rename EmpInfo,Employees GO 运行结果如下： 注意: 更改对象名的任一部分都可能破坏脚本和存储过程。 object 已重命名为 Employees。,【例7.20】,使用存储过程sp_rename将表Employees中的列Sex重命名为Emp_Sex，具体语句如下： USE HrSystem GO EXEC sp_rename Employees.Sex, Emp_Sex, COLUMN GO 运行结果如下： 注意: 更改对象名的任一部分都可能破坏脚本和存储过程。 COLUMN 已重命名为 Emp_Sex。,7.2.3

25、 删除表语句,DROP TABLE的语法结构非常简单： DROP TABLE 【例7.21】删除数据库HrSystem中的表Employees，具体语句如下： USE HrSystem GO DROP TABLE Employees GO,7.3 索引管理语句,创建索引语句 删除索引语句,7.3.1 创建索引语句,CREATE INDEX语句的功能是为给定的表或视图创建索引。它的基本语法结构如下： CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n ),1

26、必需参数,使用CREATE INDEX语句的最简单格式为： CREATE INDEX ON | (列名) 【例7.22】为表EmpInfo创建基于Wage列的索引au_wage，可以使用以下命令： CREATE INDEX au_wage ON EmpInfo ( wage ),2惟一索引,使用UNIQUE参数创建惟一索引。 【例7.23】为表EmpInfo创建基于Id_Card列的惟一索引au_Id_Card，可以使用以下命令： CREATE UNIQUE INDEX au_Id_Card ON EmpInfo ( Id_Card ),3聚集 / 非聚集索引,使用CLUSTERED和NONC

27、LUSTERED参数创建聚集和非聚集索引。CLUSTERED / NOCLUSTERED可以和UNIQUE同时出现。请看下面的示例： CREATE UNIQUE CLUSTERED INDEX au_Id_Card ON EmpInfo ( Id_Card ),4升序和降序,使用ASC和DESC参数来确定具体某个索引列的升序或降序排序方向。默认设置为 ASC。 【例7.24】对表EmpInfo的Wage列按照降序创建索引，可以使用以下命令： CREATE INDEX au_wage ON EmpInfo ( wage DESC ),7.3.2 删除索引语句,DROP INDEX语句的语法结构如

28、下： DROP INDEX table.index | view.index ,.n 在删除索引时，必须指定索引所在的表或视图。 【例7.25】要删除表EmpInfo上的索引au_wage，可以使用以下命令： DROP INDEX EmpInfo.au_wage,7.4 视图管理语句,创建视图语句 修改视图语句 删除视图语句,7.4.1 创建视图语句,可以使用CREATE VIEW语句来创建视图，其基本语法结构如下： CREATE VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION ,1必需参数,使用CREATE VIEW语句的最简单格式为： CREATE VIEW AS 【例7.26】以表EmpInfo的Emp_Id、Emp_Name和Title列创建视图Title_View，可以使用以下命令： CREATE VIEW Title_View AS SELECT Emp_Id, Emp_Name, Tit