SQLServer教案第07周 数据库和表的创建

1、授课日期 年 月 日 第6周授课形式讲课授课时数4章节名称第06章 数据库和表的创建教学目的与要求理解数据库中的数据文件和日志文件的概念和作用理解SQL Server中系统数据库与用户数据库的概念掌握数据库的创建方法以及创建数据库的Create Database语句掌握在数据库中创建表的Create Table语句和撤销表的DropTable语句掌握为表添加字段、撤销字段的方法和相关语句。理解约束的概念和类型，以及约束的作用掌握为表添加约束、撤销约束的方法和相关语句。掌握用Insert Into语句为数据表插入新记录的语法掌握用Update语句为数据表更新已经数据的语法掌握用Delete Fr

2、om语句为数据表删除记录的语法教学重点教学难点教学方法和手段讲授法结合课堂实例分析讨论教学过程与组织导入新课我们已经学习关系数据库的相关理论。现在，我们以SQL Server 2005数据库为例，介绍如何在DBMS中创建数据库和表。讲授新课第07章 数据库和表的创建第01节 数据库的创建7.1.1 SQL Server数据库的结构1数据库文件和文件组SQL Server 2000用文件来存放数据库，数据库文件有三类。1、 主数据库文件（Primary）：存放数据，每个数据库都必须有一个主数据文件。2、 其他数据文件（Secondary）：存放数据，一个数据库可以没有也可以有多个Secondar

3、y文件。3、 事务日志文件（Transaction Log）：存放事务日志，每个数据库必须有一个或多个日志文件。一般情况下，一个简单的数据库可以只有一个主数据文件和一个日志文件。如果数据库很大，则可以设置多个Secondary文件和日志文件，并将它们放在不同的磁盘上。默认状态下，数据文件名为“数据库名_Data.MDF”，日志文件名为“数据库名_Log.LDF”。数据库的创建者可以在创建时指定其他的路径和文件名，也可以添加Secondary文件和更多的日志文件。文件组允许多个数据库文件组成一个组，并对它们整体进行管理。比如，可以将三个数据文件（data1.mdf、data2.mdf和data3

4、.mdf）分别创建在三个盘上，这三个文件组成文件组fgroup1，在创建表的时候，就可以指定一个表创建在文件组fgroup1上。SQL Server的数据库文件和文件组必须遵循以下规则：（1）一个文件和文件组只能被一个数据库使用。（2）一个文件只能属于一个文件组。（3）数据和事务日志不能共存于同一文件或文件组上。（4）日志文件不能属于文件组。2数据库对象SQL Server 2000数据库中的数据在逻辑上被组织成一系列对象，当一个用户连接到数据库后，他所看到的是这些逻辑对象，而不是物理的数据库文件。SQL Server中有以下数据库对象：表（Table）、视图（View）、存储过程 （Stor

5、ed procedures）、触发器（Triggers）、用户自定义数据类型（User-defined data types）、用户自定义函数（User-defined functions）、索引（Indexes）、规则（Constraints）、默认值（Defaults）等。7.1.2 SQL Server 2005的系统数据库1master数据库master数据库记录了SQL Server系统级的信息，包括系统中所有的登录账号、系统配置信息、所有数据库的信息、所有用户数据库的主文件地址等。每个数据库都有属于自己的一组系统表，记录了每个数据库各自的系统信息，这些表在创建数据库时自动产生。为了

6、与用户创建的表相区别，这些表被称为系统表，表名都以“sys”开头。master数据库中还有很多系统存储过程和扩展存储过程。系统存储过程是预先编译好的程序，所有的系统存储过程的名字都以sp_开头。2tempdb数据库tempdb 数据库用于存放所有连接到系统的用户临时表和临时存储过程以及SQL Server产生的其他临时性的对象。tempdb是SQL Server中负担最重的数据库，因为几乎所有的查询都可能需要使用它。在SQL Server 关闭时，tempdb数据库中的所有对象都被删除，每次启动SQL Server时，tempdb数据库里面总是空的。3model数据库model数据库是系统所有

7、数据库的模板，这个数据库相当于一个模子，所有在系统中创建的新数据库的内容，在刚创建时都和model数据库完全一样。如果SQL Server专门用作一类应用，而这类应用都需要某个表，甚至在这个表中都要包括同样的数据，那么就可以在model数据库中创建这样的表，并向表中添加那些公共的数据，以后每一个新创建的数据库中都会自动包含这个表和这些数据。当然，也可以向model数据库中增加其他数据库对象，这些对象都能被以后创建的数据库所继承。4msdb数据库msdb 数据库被SQL Server代理（SQL Server Agent）来安排报警、作业，并记录操作员。7.1.3 创建数据库1在Manager

8、Studio中创建数据库略。2使用T-SQL创建数据库使用T-SQL语句创建数据库的语法格式如下：CREATE DATABASE 数据库名ON(NAME = 数据文件逻辑名称,FILENAME = 存取路径数据文件名,SIZE = 数据文件初始大小,FILEGROWTH = 数据文件增长率%)LOG ON(NAME = 日志文件逻辑名称,FILENAME = 存取路径日志文件名,SIZE = 日志文件初始大小,FILEGROWTH = 日志文件增长率%)注意：在SQL Server 2005/2008中，数据文件和日志文件的初始大小至少要为3M。通过执行以下的语句可以创建LWZZ数据库。CRE

9、ATE DATABASE LWZZON(NAME = LWZZ_Data,FILENAME = C:Program FilesMicrosoftMSSQLdataLWZZ_Data.MDF,SIZE = 3,FILEGROWTH = 10%)LOG ON(NAME = LWZZ_Log,FILENAME = C: Program FilesMicrosoft MSSQLdataLWZZ_Log.LDF,SIZE = 3,FILEGROWTH = 10%)我们也可以简单地来创建数据库：CREATE DATABASE mytest由于没有指定主文件名，在默认的情况下，命名主文件为mytest.md

10、f，日志文件名为mytest_log.log。同时由于按复制Model数据库的方式来创建新的数据库，主文件和日志文件的大小都同Model数据库的主文件和日志文件大小一致，并且可以自由增长。7.1.4 修改数据库修改数据库包括增减数据文件和日志文件、修改文件属性（包括更改文件名和文件大小）、修改数据库选项等。1在Manager Studio中修改数据库略2使用T-SQL语句修改数据库（1）用ALTER DATABASE语句增减数据文件和日志文件。增加数据文件的语法如下：ALTER DATABASE 数据库名 ADD FILE ( NAME = 逻辑数据文件名, FILENAME = 存取路径数据

11、文件名 SIZE = 数据文件初始大小, MAXSIZE = 文件大小最大值, FILEGROWTH = 文件增长率)修改数据文件的大小的语法如下：ALTER DATABASE 数据库名 MODIFY FILE ( NAME = 逻辑数据文件名, SIZE = 数据文件初始大小, MAXSIZE = 文件大小最大值, FILEGROWTH = 文件增长率)例如：ALTER DATABASE LWZZ ADD FILE ( NAME =LWZZ_data2, FILENAME=C: Program FilesMicrosoft MSSQLdataLWZZ_data2.ndf SIZE=5MB,

12、MAXSIZE=50MB, FILEGROWTH=1MB)其中ADD FILE 是指增加一个数据文件，还可以是ADD LOG FILE、REMOVE FILE、MODIFY FILE 等，分别代表增加日志文件、删除数据库文件和修改文件信息。比如，以下的语句将LWZZ 数据库的第二个数据文件LWZZ_data2的初始大小修改为20MB。ALTER DATABASE LWZZ MODIEY FILE ( NAME = LWZZ_data2, SIZE=20MB)关于ALTER DATABASE 语句的更详细用法可以参考SQL Server 2000的在线手册。（2）用ALTER DATABASE语

13、句修改数据库选项，例如ALTER DATABASE LWZZ SET AUTO_SHRINK ON（3）使用系统存储过程sp_dboption修改数据库选项Sp_dboption LWZZ, autoshrink, true除了设置选项外，Sp_dboption还有很多其他的作用。如运行不带参数的Sp_dboption，可以得到所有数据库选项列表。如果希望看到某个数据库当前的选项设置情况，可以给Sp_dboption加上一个数据库名作为参数，SQL Server将显示所有在该数据库中设置为True的选项。如果想查看某个特定选项的当前设置值，可以输入数据库名和选项名。7.1.5 删除数据库1在M

14、anager Studio中删除数据库略2使用T-SQL语句删除数据库使用SQL语句删除数据库需要使用DROP DATABASE语句。使用DROP DATABASE语句可以一次删除多个数据库。语法格式为：DROP DATABASE 数据库名列表例如：DROP DATABASE northwind , pubs第02节 数据表的创建7.2.1 创建表结构1在Manager Studio中创建数据表略2使用T-SQL语句创建数据表使用SQL语句定义表的语句格式为：CREATE TABLE 表名 ( 列名 数据类型 列级完整性约束定义 ,列名 数据类型 列级完整性约束定义 ,表级完整性约束定义 )S

15、QL语句中有定义这三类完整性的相关语法成份，完整性约束又分为列级约束和表级约束。（1）实体完整性约束，用于定义表中的主码约束。在本列的列名、数据类型之后，写上以下的英文短语，就是将本列定义为主码。PRIMARY KEY（2）参照完整性约束，用于定义表中的外码约束。在本列的列名、数据类型之后，写上以下的英文短语，就是将本列定义为外码。FOREIGN KEY (列名) REFERENCES 外表名(外表中被引用的列名)（3）惟一性约束。在某一列的列名、数据类型说明之后，写上以下的英文短语，就是将本列定义为惟一性约束(值唯一，无重复)。UNIQUE（5）（非）空值约束。将某列定义为（非）空值约束的方

16、法是，在某一列的列名、数据类型之后，加上以下成份。（NOT） NULL（6）CHECK约束。当需要限制某列的取值范围时，通常使用这一约束。方法是，在某一列的列名、数据类型之后，加上以下成份。CHECK（约束表达式）凡是在某一列的列名、数据类型之后定义的约束，都叫“列级约束”。特别地，默认值约束和（非）空值约束只能定义为列级约束。凡是在一个表的全部列定义之后再定义的约束，都叫“表级约束”。多数情况下，一个表中的主码是惟一的列，在本列将它定义为主码约束即可。（7）默认值严格来说，默认值不是约束。将某列定义为默认值约束的方法是，在某一列的列名、数据类型之后，加上以下成份。DEFAULT 常量下面给出

17、创建表的实例：系信息表Department结构如下：列名说明类型和长度约束Deno系编号字符串，6位主键Dname系名称字符串，最长20位非空、唯一Tel联系电话字符串，最长11位唯一CREATE TABLE Department( DeNo char(6) CONSTRAINT Department_DeNo_PK Primary Key, DName varchar(20) CONSTRAINT Department_DName_NN Not NULL CONSTRAINT Department_DName_UK UNIQUE, Tel varchar(11) CONSTRAINT Dep

18、artment_Tel_UK UNIQUE)学生信息表Student结构如下：列名说明类型和长度约束Sno学号字符串，8位主键Sname学生姓名字符串，最长12位非空、唯一Ssex性别字符串，2位取男或女Birth出生日期日期时间大于1900且小于当前时间Depart所属系字符串，6位外键，默认为DAddr联系地址字符串，最长50位允许为空CREATE TABLE Student( SNo char(8) CONSTRAINT Student_SNo_PK Primary Key, SName varchar(12) CONSTRAINT Student_SName_NN Not NULL,

19、SSex char(2) CONSTRAINT Student_SSex_CK Check(Ssex=男 OR Ssex=女), Birth Datetime CONSTRAINT Student_Birth_CK Check(Birth=1900 AND Birth=0 And CCredit=0 And Period=0 AND Grade=100), CONSTRAINT SC_SNo_CNo_PK PRIMARY KEY(Sno,Cno)7.2.2 修改表结构创建完一个表以后，难免要对表进行修改。1在Manager Studio中修改表结构略2使用T-SQL语句修改表结构使用SQL语句

20、定义表的语句格式为：（1）添加列向表中增加一列时，应使新增加的列有默认值或允许为空值，SQL Server将向表中已存在的行填充新增列的默认值或空值，如果既没有提供默认值也不允许为空值，那么新增列的操作将出错，因为SQL Server不知道该怎么处理那些已经存在的行。向表中添加列的语句格式如下：ALTER TABLE表名ADD列名 列的描述例如：为SC表新增一个新列，列名为“ScType”，表示选课类别，4位定长字符串，允许为空。ALTER TABLE SC ADD ScType char(4) NULL（2）删除列删除一列的语句格式为：ALTER TABLE表名DROP COLUMN列名例如

21、：删除SC表的ScType列。ALTER TABLE SC DROP COLUMN ScType（3）修改列定义表中的每一列都有其定义，包括列名、数据类型、数据长度以及是否允许为空值等，这些值都可以在表创建好以后修改。修改列定义的语句格式为：ALTER TABLE 表名ALTER COLUMN 列名 列的描述例如：将Student表的Addr列改为最大长度为60的varchar型数据。ALTER TABLE Student ALTER COLUMN Addr varchar(60)（4）修改表约束 添加主码约束添加主码约束的SQL语句的格式如下：ALTER TABLE 表名 ADD CONST

22、RAINT 约束名 PRIMARY KEY ( 列名， n ). 添加外键约束添加外键约束的SQL语句的格式如下：ALTER TABLE 表名 ADD CONSTRAINT 约束名FOREIGN KEY (列名) REFERENCES 被引用表名(被引用列名). 添加惟一性约束添加惟一性约束的SQL语句的格式如下：ALTER TABLE 表名ADD CONSTRAINT 约束名 UNIQUE(列名， n). 添加CHECK约束添加CHECK约束的SQL语句的格式如下：ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(约束条件). 禁用约束将某张表的某个约束暂时禁用

23、，语法如下ALTER TABLE 表名 NOCHECK CONSTRAINT 约束名. 重新启用被禁用的约束将某张表的某个被禁用的约束再次禁用，语法如下ALTER TABLE 表名 CHECK CONSTRAINT 约束名（5）添加列的默认值添加DEFAULT默认值的SQL语句的格式如下：ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT 常量 FOR 列名（6）修改表名修改表名的语句格式为：EXEC sp_rename 原表名, 新表名7.2.3 撤销表结构当不再需要某个表时，可以将其删除。一旦一个表被删除，那么它的数据、结构定义、约束、索引都将被永久地删除，

24、以前用来存储数据和索引的空间可以用来存储其他的数据库对象了。1在Manager Studio中撤销表结构略2使用T-SQL语句撤销表结构使用SQL语句撤销表的语句格式为：DROP TABLE 表名 ，表名 例：删除表名为Test1和Test2的两张表DROP TABLE Test1, Test2第03节 数据更新6.3.1 数据行的插入1在Manager Studio中为表插入数据行略2使用T-SQL语句为表插入数据行使用SQL语句为表插入数据行的语句格式为：INSERT INTO 表名 (列名列表) VALUES (值列表)其中：（1）表名：是要插入数据的表名。（2）列名列表；是要插入的数据

25、列的各列名称，必须是表中有定义的列名，各列名之间用逗号隔开。（3）值列表：插入各列的实际数据。当语句中存在列名列表时，值列表中数据的个数、顺序、数据类型，要保持严格的一一对应关系。如果要插入空值，可用NULL表示。例：将记录(g001,计算机系)插入表DEPARTMENT中INSERT INTO DEPARTMENT (DeNO,DName,Tel) VALUES (g001,计算机系,NULL)例：将新生记录(,张国立,男,1988-05-16,g002)插入Student表中INSERT INTO STUDENT (SNo,SName,SSex,Birth,Depart) VALUES (

26、,张国立,男,1988-05-16,g002)例：将一个新的学生选课(, C01)插入表SC中INSERT INTO SC(SNo, CNo) VALUES (, C01)例：将记录(, C01,90)插入表SC中INSERT INTO SC(SNo, CNo, GRADE) VALUES (, C01,90)6.3.2 数据修改1在Manager Studio中为表修改数据略2使用T-SQL语句为表修改数据使用SQL语句为表修改数据的语句格式为：UPDATE 表名 SET 列名 = 表达式 ,列名2 = 表达式2 WHERE 条件表达式 如果不使用WHERE子句，所以对发表时间表中某一个字段

27、的修改影响到表中的每一行。例：将Course表中所有课程的学时都加2UPDATE Course SET Period = Period+2例：将Student表中的姓名为“王丹凤”的出生日期改为1985-08-24UPDATE Student SET Birth=1985-08-24 WHERE SName=王丹凤6.3.3 数据行的删除1在Manager Studio中为表删除数据行略2使用T-SQL语句为表删除数据行使用SQL语句为表删除数据行的语句格式为： DELETE使用T-SQL中的DELETE语句可以删除数据表中的一个或多个记录。DELETE语句最简单的形式如下：DELETE 表名 WHERE 条件其中，表名是要删除数据的表的名字。如果DELETE语句中没有WHERE子句限制，表中的所有记录都将被删除。例： 删除表SC中全部的记录DELETE FROM SC例：删除SC表中所有不及格学生的选课记录DELETE FROM SC WHERE Grade60TRUNCATE TABLE语句T