a第4章 创建和管理SQL Server数据表.ppt

1、第4章 创建和管理数据表, SQL Server 2005,4.2 数据表的创建,4.2.1 使用SQL Server Management Studio 创建表 1. 创建新表 【例4-1】使用SSMS，在数据库library中创建数据表student表。,表4-2 student表,每一个字段都对应一个“列属性”标签，包括以下各属性： 名称 长度 默认值或绑定 数据类型 允许空 RowGuid 排序规则 说明,2. 为字段指定默认值 为表增加记录时，若没有为某字段指定值，则 可以用默认值代替。默认值可以是NULL、常量或 系统函数。,【例4-2】使用SSMS，在数据库library中创建c

2、ard数据表，并为StartDate(起效时间)、State(状态)、Maximum(最大允许借阅数)、BorrwedNum(已借数量)字段指定默认值。,表4-3 Card表,3. 定义计算列,若表中某字段的值是通过对该表中其他字段的值 进行计算得到的，则可以将该字段定义为计算列。 在插入或更新记录时，不允许为计算列指定 字段值。,【例4-3】使用SSMS，在数据库library中创建 book数据表，并将ReturningTime列定义为计 算列。,表4-4 bookshelf表,dateadd(dayofyear,LendingDays,LendingTime),计算所得的列规范,4. 定

3、义标识列,在定义表字段时，可以将某个字段定义为标识列。 标识列：是指字段的值由SQL SERVER 2005 自动提供。 定义标识列须同时指定种子和增量，或者两者都不 指定；如果两者都不指定，则取默认值为（1,1）， 即种子值为1，增量值为1。 种子：是添加表中的第一个记录该字段所使用的值。 增量：是每次添加记录时，在上次增加记录时 该字段值的基础上增加的幅度。,【例4-4】使用SSMS，在数据库library中创建place数据表，并将PlaceID列定义为标识列，种子和增量分别为1和1。,表4-5 place表,示例：教学管理数据库系统中的学习关系SC,CREATE TABLE SC (S

4、# CHAR(9) NOT NULL, C# CHAR(7) NOT NULL， GRADE INT NOT NULL DEFAULT(0), PRIMARY KEY(S#，C#)， FOREIGN KEY（S#） REFERENCES S（S#）， FOREIGN KEY（C#） REFERENCES C（C#）， CHECK(GRADE BETWEEN 0 AND 100) )；,4.2.2 使用Transact-SQL语句创建表,列完整性约束,表完整性约束,CREATE TABLE （ , , ， , ）；,基本表的定义,4.2.2 使用Transact-SQL语句创建表,CREATE

5、TABLE database_name . schema_name .| schema_name. table_name ( | ,.n ) ON |filegroup |“default” TEXTIMAGE_ONfilegroup|“default” ; ,定义表中各列,计算列的定义,指定表的完整性约束,表存储的分区架构或文件组,表中较大值列的存储机制, :=column_name COLLATE collation_name NULL | NOT NULL CONSTRAINT constraint_name DEFAULT constant_expression | IDENTITY

6、( seed ,increment ) NOT FOR REPLICATION ROWGUIDCOL n ,列名,数据类型,指定列的排序规则,定义default约束,定义标识列,指示新列是GUID列,定义列的完整性约束,示例：教学管理数据库系统中的学习关系SC,CREATE TABLE SC （Sno CHAR(9), Cno CHAR(7)， GRADE INT DEFAULT(0), ）；,缺省值约束,约束名,CONSTRAINT DF_GRADE,【例】创建数据表ex表，并将A列设置为标识列，种子为1，增量为2。 CREATE TABLE ex （A INT IDENTITY(1,2)

7、NOT NULL, B CHAR(7) ）；,示例：定义标识列,种子,增量,【例】创建数据表student表，并将StudentID列设置为GUID列。 CREATE TABLE STUDENT( StudentID uniqueidentifier ROWGUIDCOL ) ONPRIMARY,表中较大值列：数据表中列的数据类型为text、ntext、 image、xml、varchar（max）、nvarchar（max）、 varbinary（max）和CLR用户定义类型的列。 如果表中没有较大值的列，则不允许使用TEXTIMAGE_ON。 【例】创建数据表student表，将Stude

8、ntID的数据类型 设定为text，并将该列（较大值列）存储在默认文件组。 CREATE TABLE STUDENT( StudentID text ) textimage_on default,数据类型为text， 标记该列是较大值列,【例 4-5】在数据库library中创建数据表student表，保存在PRIMARY组中。,CREATE TABLE STUDENT( StudentIDchar(9) NOT NULL, Namevarchar(10) NOT NULL, Sexbit NOT NULL, Birthdaysmalldatetime NOT NULL, BirthPlace

9、varchar(10) NOT NULL, IDchar (18) NOT NULL, Deptchar(3) NOT NULL, RegistrationDatesmalldatetime NOT NULL ) ONPRIMARY,【例 4-6】创建book数据表，保存在PRIMARY组中。 CREATE TABLE book( ISBNvarchar(17) NOT NULL, BokkNamevarchar(50) NOT NULL, Authorvarchar(50) NOT NULL, Publishervarchar(50) NOT NULL, PublisherDatechar(

10、7) NOT NULL, Pricemoney NOT NULL, Classvarchar(10) NOT NULL ) ONPRIMARY,【例 4-7】在数据库library中创建card数据表，保存在PRIMARY组中，并为StartDate(起效时间)、State (状态) 、Maximum (允许最大借阅数) 、BorrowNum(已借阅数)字段指定默认值。,表4-3 Card表,CREATE TABLE card( CardIDchar(8) NOT NULL, StartDatesmalldatetime NOT NULL CONSTRAINTDF_card_StartDate

11、 DEFAULT(getdate(), ExpiredDatesmalldatetime NOT NULL, Statechar(4) NOT NULL CONSTRAINTDF_card_StateDEFAULT (正常), Maximumtinyint NOT NULL CONSTRAINTDF_card_ MaximumDEFAULT (10), ) ONPRIMARY,BorrowedNumtinyint NOT NULL CONSTRAINTDF_card_ BorrowedNum DEFAULT (0), StudentIDchar(9) NOT NULL,Default约束的约束

12、名,DATEADD (datepart ,number,date ) :通过向指定日期添加间隔，返回新的 datetime 值。 参数 datepart :指定要作为新值返回的日期部分的参数。 number :用于增加 datepart 的值。 date :返回可以隐式转换为 datetime 的值的表达式，或者是日期格式的 Unicode 字符串。,【例 4-8】创建bookshelf数据表，并将ReturningTime列定义为计算列。,表4-4 bookshelf表,dateadd(dayofyear,LendingDays,LendingTime),计算所得的列规范,CREATE TA

13、BLE bookshelf( Barcodechar(8) NOT NULL, LendingTimesmalldatetime NULL, LendingDayssmallint NULL, ReturningTime AS(dateadd(dayofyear,LendingDays,LendingTime), Statechar(4) NOT NULL, ISBNvarchar(17) NOT NULL, CardIDchar(8) NULL, PlaceIDtinyint NOT NULL ) ONPRIMARY,定义计算列，计算列无数据类型,【例 4-9】创建place数据表，并将Pl

14、aceID列定义为标识，种子和增量分别为1和1。 CREATE TABLE place( PlaceIDtinyint IDENTITY(1,1) NOT NULL, Descriptionvarchar(50) NOT NULL, ) ONPRIMARY,4.3 管理表,4.3.1 查看表的属性 【例 4-10】使用SSMS查看表的结构。,4.3.2 修改表的结构,【例 4-11】使用SSMS修改表student的结构，将Sex字段名修改为Gender，数据类型定义为char（1）。,4.3.3 重命名、删除表,【例 4-12】使用SSMS将stuent表重命名为teacher表。 【例 4

15、-13】使用SSMS将表stuent删除。,4.4 数据库完整性设计,4.4.1 创建约束 1. PRIMARY KEY约束 PRIMARY KEY约束中的列不允许为空值和重复 的值，即主键中的值不允许为空值和重复。,【例 4-14】使用SSMS将stuent表的StudentID字段设为主键。 CREATE TABLE student (StudentID CHAR(10) PRIMARY KEY(10), SNAME CHAR(10) ) 注意：创建主键时会自动创建名为PK_(表名)的主 键，可以在student表下的键节点中看到该主键。,【例 4-15】 将history表的ISBN、C

16、ardID、LendingTime三个字段联合设为主键。 CREATE TABLE history ( ISBN CHAR(10), CardID CHAR (10), LendingTime SMALLDATETIME, PRIMARY KEY(ISBN,CardID,LendingTime) ),通过ALTER TABLE语句为表添加主键。,ALTER TABLE table_name WITHCHECK|NOCHECK ADD ,添加一个或多个表的完整性约束,WITH CHECK：指定表中已有的数据用新添加的 约束(foreign key约束或是check约束)进行验证。 WITH NO

17、CHECK：指定表中已有的数据不用新添加 的约束(foreign key约束或是check约束)进行验证。,【示例】为学生表S添加一个check约束。 ALTER TABLE S WITH CHECK ADD CONSTRAINT CK_s_sno check(sno=0 and sno=0 and sno=4),约束名,Table_constraint:= CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED (columnASC|DESC,n) ONpartion_scheme_name(partition_column

18、_name)|filegroup|”default” ,为约束创建聚集索引 或非聚集索引,表的完整性约束,定义主键所包含的列， 以及各列的排序规则,索引存储的分区架构或文件组,【例 4-16】使用ALTER TABLE语句，将student表的StudentID字段设置为主键，主键遵守升序排序规则，并建立聚集索引。 ALTER TABLE student ADD CONSTRAINT PK_student PRIMARY KEY CLUSTERED(StudentID ASC) ON PRIMARY,【例 4-17】使用ALTER TABLE语句，将history表的ISBN、CardID、L

19、endingTime三个字段联合设为主键。 ALTER TABLE history ADD CONSTRAINT PK_history PRIMARY KEY CLUSTERED ( ISBN ASC, CardID ASC, LendingTime ASC ) ON PRIMARY,【例 4-18】创建student表时，将studentID字段指定为主键，并在该字段上建立聚集索引。 CREATE TABLE student (StudentID char(9) NOT NULL CONSTRAINT PK_student PRIMARY KEY CLUSTERED ON PRIMARY,

20、NAME varchar(10) NOT NULL, SEX bit NOT NULL ),【例 4-19】创建history表时，将ISBN、CardID、LendingTime三个字段联合设为主键。 CREATE TABLE history ( ISBN CHAR(10), CardID CHAR (10), LendingTime SMALLDATETIME, CONSTRAINT PK_history PRIMARY KEY (ISBN ASC,CardID ASC,LendingTime ASC) ON PRIMARY ),练习,1.修改S1表，将A字段和B字段联合设置为主键，主键均

21、遵守升序排序规则，并建立聚集索引。 2.创建S2表，将C字段设置为主键，主键遵守升序排序规则，并建立聚集索引。将D字段设置为计算列，公式为3+5。,1.ALTER TABLE S1 ADD CONSTRAINT PK_S1 PRIMARY KEY CLUSTERED (A ASC,B ASC) 2.CREATE TABLE S2 (C INT COSTRAINT PK_S2 PRIMARY KEY CLUSTERED, D AS 3+5),4.4.1 创建约束 2. UNIQUE约束 UNIQUE约束确保在非主键列中不输入重复的值。 UNIQUE约束和PRIMARY约束的区别： 1.PRIMA

22、RY KEY约束中的列不允许为空值。 UNIQUE约束中的列允许为空值。 2.一个表只能有一个PRIMARY约束，但可以有多个UNIQUE约束。,【例 4-22】使用ALTER TABLE语句，将student表的ID字段增加UNIQUE约束。 ALTER TABLE student ADD CONSTRAINT IX_student UNIQUE NONCLUSTERED ( ID ASC ) ON PRIMARY,【例 4-23】创建book表时,为BookName、Author、Publisher字段增加UNIQUE约束。 CREATE TABLE book ( ISBN varchar

23、(17) NOT NULL, BookName varchar(50) NOT NULL, Price money NOT NULL, CONSTRAINT IX_book UNIQUE NONCLUSTERED ( BookName ASC,Author ASC,Publisher ASC ) ON PRIMARY )ON PRIMARY,4.4.1 创建约束 3. FOREIGN KEY约束 【例 4-24】使用SSMS，为card表的StudentID字段增加FOREIGN KEY约束。,通过ALTER TABLE语句为表添加外键。,ALTER TABLE table_name WITH

24、CHECK|NOCHECK ADD ,添加一个或多个表的完整性约束,WITH CHECK：指定表中已有的数据用新添加的 约束(foreign key约束或是check约束)进行验证。 WITH NOCHECK：指定表中已有的数据不用新添加 的约束(foreign key约束或是check约束)进行验证。,Table_constraint:= CONSTRAINT constraint_name FOREIGN KEY REFERENCES referenced_table_name (ref_column,n) ON DELETENO ACTION|CASCADE|SET NULL|SET D

25、EFAULT ON UPDATENO ACTION|CASCADE|SET NULL|SET DEFAULT NOT FOR REPLICATION ,定义外键所包含的列,表的完整性约束,【例 4-25】使用ALTER TABLE语句为bookshelf的CardID、ISBN、PlaceID字段增加FOREIGN KEY约束，分别参照card表的CardID字段、book表的ISBN字段、place表的PlaceID字段。对应bookshelf表中的CardID、ISBN、PlaceID字段，其FOREIGN KEY约束的ON DELETE设置为NO ACTION，ON UPDATE分别设置

26、为CASCADE、NO ACTION、NO ACTION。,ALTER TABLE bookshelf WITH CHECK ADD CONSTRAINT FK_bookshelf_book FOREIGN KEY(ISBN) REFERENCES book(ISBN) ON DELETE NO ACTION ON UPDATE NO ACTION,ALTER TABLE bookshelf WITH CHECK ADD CONSTRAINT FK_bookshelf_card FOREIGN KEY(CardID) REFERENCES card(CardID) ON DELETE NO A

27、CTION ON UPDATE CASCADE,ALTER TABLE bookshelf WITH CHECK ADD CONSTRAINT FK_bookshelf_place FOREIGN KEY(PlaceID) REFERENCES place(PlaceID) ON DELETE NO ACTION ON UPDATE NO ACTION,通过CREATE TABLE语句为表添加外键。,column_constraint:= CONSTRAINT constraint_name FOREIGN KEY FOREIGN KEY(PlaceID) REFERENCEschema_na

28、me.referenced_table_name (ref_column) ON DELETENO ACTION|CASCADE|SET NULL| SET DEFAULT ON UPDATENO ACTION|CASCADE|SET NULL| SET DEFAULT NOT FOR REPLICATION ,添加一个或多个表的完整性约束,【例 4-26】使用CREATE TABLE语句为history表的ISBN字段增加FOREIGN KEY约束，参照book表的ISBN字段，ON DELETE和ON UPDATE均设置为CASCADE。,CREATE TABLE history ( IS

29、BN varchar(17) NOT NULL CONSTRAINT FK_history_book FOREIGN KEY REFERENCES book(ISBN) ON DELETE CASCADE ON UPDATE CASCADE, CardID char(8) NOT NULL, LendingTime smalldatetime NOT NULL )ON PRIMARY,【例 4-27】使用CREATE TABLE语句为history表的ISBN字段和CardID字段增加FOREIGN KEY约束，并分别参照book表的ISBN字段和card表的CardID字段，ON DELET

30、E和ON UPDATE均设置为CASCADE。,CREATE TABLE history ( ISBN varchar(17) NOT NULL, CardID char(8) NOT NULL, LendingTime smalldatetime NOT NULL, CONSTRAINT FK_history_book FOREIGN KEY(ISBN) REFERENCES book(ISBN) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_history_card FOREIGN KEY(CardID) REFERENCES car

31、d(CardID) ON DELETE CASCADE ON UPDATE CASCADE )ON PRIMARY,4.4.1 创建约束 4. CHECK约束 【例 4-28】使用SSMS，为card表的State字段增加CHECK约束,限制输入“正常”和“挂失”两个值，限制Maximum字段最大值为20。,CREATE TABLE card (State char(4) CHECK (State=正常 or State=挂失)AND Maximum=20) ),通过ALTER TABLE语句为表添加CHECK约束。,ALTER TABLE table_name WITHCHECK|NOCHECK ADD ,添加一个或多个表的完整性约束,WITH CHECK：指定表中已有的数据用新添加的 约束(