创建与使用数据表-SQL创建和操作表

1、 创建与使用数据表创建与使用数据表复习1.主键定义？ 功能？2.外键定义？ 功能？3.其他约束有哪些？ 功能？ 关系表关系表方法：方法：1.使用管理器使用管理器 2.SQL代码代码定义表：定义表：创建、修改、删除创建、修改、删除定义表的约束：定义表的约束：主键、外键、主键、外键、NULL、 默认、默认、Check、唯一性唯一性操作表：操作表：插入、修改、删除插入、修改、删除复 习6.2.3 6.2.3 使用使用Transact-SQLTransact-SQL创建数据表创建数据表使用使用T-SQLT-SQL创建表的语法如下。创建表的语法如下。CREATE TABLE database_name

2、schema_name | schema_name table_name （ | ,n ） on partition_scheme_name ( partition_column_name ) | filegroup | “default” TEXTIMAGE_ON filegroup | “default”其中：其中：包括：包括：column_name FILESTREAM COLLATE collation_name NULL | NOT NULL CONSTRAINT constraint_name DEFAULT constant_expression | IDENTITY ( see

3、d ,increment ) NOT FOR REPLICATION ROWGUIDCOL .n n使用Transact-SQL语句中的CREATE TABLE命令命令创建表，其语法最简格式最简格式如下：CREATE TABLE table_name( Col_name column_properties ,)提示：提示：每个不同项目之间用空格隔开每个不同项目之间用空格隔开列名列名列的数据类型及长度列的数据类型及长度可以有若干列，用可以有若干列，用逗号分割开逗号分割开表名表名切换数据库命令：切换数据库命令：USE LibraryGO提示：提示：GO命令是一个批执行命令，后面会详细解释。执行上述

4、命令后，后续的创建表命令等命令，均在该数据库（Library库）中运行。使用使用Transact-SQL语句创建表语句创建表【例】 利用CREATE TABLE命令创建“book”。USE LibraryGOCREATE TABLE book( 编号 CHAR(8) NOT NULL,ISBN号 CHAR(13) NOT NULL,书名 VARCHAR(200),定价 MONEY,出版社 VARCHAR(200),出版日期 DATETIME )使用使用Transact-SQL语句创建表语句创建表使用Transact-SQL语句中的CREATE TABLE命令命令创建表，其语法较完整格式较完整格

5、式：CREATE TABLE （ ， ， ， ）；）；使用使用Transact-SQL语句创建表语句创建表定义约束的类型的定义约束的类型的1) CHECK约束2) DEFAULT约束3) PRIMARY KEY约束4) FOREIGN KEY约束5) UNIQUE约束6) NULL 7) NOT NULL-检测约束检测约束-默认约束默认约束-主键约束主键约束-外键约束外键约束-唯一约束唯一约束-允许为空约束允许为空约束-非空约束非空约束定义约束是在定义约束是在创创建表或修改表建表或修改表的命的命令中实现的令中实现的在该列数据类型说明后添加在该列数据类型说明后添加CREATE TABLE （专业

6、代码（专业代码 CHAR（4） PRIMARY KEY， 专业名称专业名称 VARCHAR（30） NOT NULL）；）；看看P80页例题6-1【例例6-16-1】使用使用CREATE TABLECREATE TABLE语句创建语句创建“BookBook”表，表，“BookBook”表用存放图书的各种信息，包括图书编号、图书名称、图书类表用存放图书的各种信息，包括图书编号、图书名称、图书类别号、作者、出版社、出版日期、入库日期、价格、状态等属别号、作者、出版社、出版日期、入库日期、价格、状态等属性。性。 USE LibraryCREATE TABLE Book( BookId char(10

7、) not null primary key, BookName varchar(50) not null, BkCateId varchar(6) not null, Author varchar(20) not null, Publish varchar(50) not null, PubTime date null, CheckIn date not null, Price money null, BookStatus char(2) not null ) DEFAULT（0） DEFAULT（男男） DEFAULT（2012-3-12） DEFAULT（合肥合肥） 表的完整性约束主键约

8、束子句 用于主键多于一个用于主键多于一个(=16)属性PRIMARY KEY（，）外键约束子FOREIGN KEY () REFERENCES ()本表列名本表列名主键表的表名主键表的表名主键表对应列名主键表对应列名单主键约束案例：单主键约束案例：例：例：课程设置表（课号，课程名称，学分）课程设置表（课号，课程名称，学分）CREATE TABLE 课程设置表课程设置表（课号（课号 CHAR（4） ， 课程名称课程名称 varchar(40)， 学分学分 INT DEFAULT（2） ）例：例：成绩表（学号，课号，成绩）成绩表（学号，课号，成绩）CREATE TABLE 成绩表成绩表（学号（学号

9、 CHAR（10），）， 课号课号 CHAR（4），）， 成绩成绩 INT DEFAULT（0） ， PRIMARY KEY （学号，课号）（学号，课号） 多主键约束案例：多主键约束案例：多属性主键多属性主键例：例：成绩表（学号，课号，成绩）成绩表（学号，课号，成绩）CREATE TABLE 成绩表成绩表（学号（学号 CHAR（10），）， 课号课号 CHAR（4），）， 成绩成绩 INT DEFAULT（0） ， PRIMARY KEY （学号，课号）（学号，课号）， FOREIGN KEY （课号）（课号） REFERENCES 课程设置表（课号）课程设置表（课号）， FOREIGN KE

10、Y （学号）（学号） REFERENCES 学生信息表（学号）学生信息表（学号）；）；多主键约束多主键约束+外键案例：外键案例：FOREIGN KEY(课号) REFERENCES 课程设置表(课号), FOREIGN KEY(学号) REFERENCES 学生信息表(学号),6.3 6.3 修改表结构修改表结构 已经建立的表格，如果不符合要求，可以进行修改。已经建立的表格，如果不符合要求，可以进行修改。SQLServer2008SQLServer2008提供两种修改数据表的方法：提供两种修改数据表的方法：SQL Server Management StudioSQL Server Manag

11、ement Studio修改表修改表使用使用Transact-SQLTransact-SQL语句修改表语句修改表6.3.1 6.3.1 使用使用SQL Server Management SQL Server Management StudioStudio修改数据表结构修改数据表结构 修改数据表结构包括修改某列的数据类型、修改数据表结构包括修改某列的数据类型、列宽度、添加和删除某列、修改列的约束等。列宽度、添加和删除某列、修改列的约束等。选中表如选中表如UsercateUsercate右键右键设计设计打开设计界打开设计界面进行面进行各类修改，约束设置各类修改，约束设置6.3.2 6.3.2 使

12、用使用Transact-SQLTransact-SQL修改数据表结构修改数据表结构使用使用T-SQLT-SQL修改数据表结构的语法如下：修改数据表结构的语法如下：ALTER TABLEALTER TABLE database_name database_name schema_name schema_name | schema_name| schema_name table_name table_name ALTER COLUMN column_name ALTER COLUMN column_name -修改列定义修改列定义 new_data_type (precision new_data

13、_type (precision ，scale ) scale ) COLLATE COLLATE NULL | NOT NULL NULL | NOT NULL | ADD | DROP ROWGUIDCOL | ADD | DROP ROWGUIDCOL | ADD | ADD -添加列添加列 | column_name AS computed_column_expression | column_name AS computed_column_expression ，n n | WITH CHECK | WITH NOCHECK ADD | WITH CHECK | WITH NOCHE

14、CK ADD -添加约束添加约束 , ,n n | DROP | DROP CONSTRAINT constraint_name CONSTRAINT constraint_name -删除约束删除约束| COLUMN column ,| COLUMN column ,n n -删除列删除列 | CHECK | NOCHECK CONSTRAINT | CHECK | NOCHECK CONSTRAINT -启用或禁用约束启用或禁用约束 ALL | constraint_name , ALL | constraint_name ,n n | ENABLE | DISABLE TRIGGER |

15、 ENABLE | DISABLE TRIGGER -启用或禁用触发器启用或禁用触发器 ALL | trigger_name , ALL | trigger_name ,n n 【例例6-46-4】在在“UserTbUserTb”表中，为表中，为“UserBkNumUserBkNum”字段添加一个检查约束，所字段添加一个检查约束，所输入的已借书数量必须大于等于零的约束代码输入的已借书数量必须大于等于零的约束代码: :USE LibraryUSE LibraryGoGoALTER TABLE UserTb ADD CONSTRAINT U_check CHECK ( UserBkNum=0 )【

16、例例6-56-5】在在“UserTbUserTb”表中添加一个字段表中添加一个字段“UserOccUserOcc”表示读者的职业，表示读者的职业，varchar(6)varchar(6)。随后再删除掉。随后再删除掉: :ALTER TABLEALTER TABLE UserTb UserTb ADD ADD UserOcc varchar(6) null UserOcc varchar(6) nullGOGOALTER TABLEALTER TABLE UserTb UserTb DROPDROP COLUMN UserOcc COLUMN UserOcc 此处需要注意的是：当使用此处需要注意

17、的是：当使用Alter TableAlter Table 添加列添加列时，如果要求该列不为空，则必须要指定一个默认值，时，如果要求该列不为空，则必须要指定一个默认值，否则不能添加成功。否则不能添加成功。若不允许为空又不需指定默认值，若不允许为空又不需指定默认值，只能先添加一个允许为空的列，再通过只能先添加一个允许为空的列，再通过alter columnalter column修改该列为非空。修改该列为非空。如：如：ALTER TABLEALTER TABLE UserTb UserTb ADD ADD UserOcc varchar(6) nullUserOcc varchar(6) null

18、GOGOALTER TABLEALTER TABLE UserTb UserTb ALTER COLUMNALTER COLUMN UserOcc varchar(6) not null UserOcc varchar(6) not null 即设置了添加的列即设置了添加的列“UserOccUserOcc”非空。非空。 【例例6-66-6】在表在表“UserTbUserTb”中，添加一个禁用，中，添加一个禁用，用于限制在用于限制在【例例6-46-4】中创建的约束。中创建的约束。NOCHECK NOCHECK CONSTRAINTCONSTRAINT与与ALTER TABLEALTER TABL

19、E一起使用，以禁用一起使用，以禁用该约束并使正常情况下会引起约束违规的插入该约束并使正常情况下会引起约束违规的插入操作得以执行。操作得以执行。CHECK CONSTRAINTCHECK CONSTRAINT重新启用该重新启用该约束。约束。 在查询标签页中键入以下命令，并运行即在查询标签页中键入以下命令，并运行即可。可。ALTER TABLEALTER TABLE UserTb UserTb NOCHECKNOCHECK CONSTRAINTCONSTRAINT U_check U_check6.4 6.4 数据表中插入、修改与删除数据数据表中插入、修改与删除数据 创建好数据表的结构之后，表中并

20、没有纪录。本节介绍创建好数据表的结构之后，表中并没有纪录。本节介绍在建立好的表中如何插入、修改和删除数据。在建立好的表中如何插入、修改和删除数据。 方法：方法：1.使用管理器使用管理器 2.SQL代码插入数据时有什么注意事项？6.4.1 6.4.1 向数据表中插入数据向数据表中插入数据选中表选中表右键右键编辑前编辑前200200行行打开数据编辑界打开数据编辑界面面2. 2. 使用使用Transact-SQLTransact-SQL语言向数据表中插入数据语言向数据表中插入数据SQL ServerSQL Server支持多种向数据支持多种向数据表中插入数据的方法，最常用的是使用表中插入数据的方法，

21、最常用的是使用INSERTINSERT语句和语句和INSERTINSERTSELECTSELECT语句。语句。（1 1）使用）使用INSERTINSERT语句语句基本语法为：基本语法为： INSERT INSERT INTO table_or_view_name ( column_list) INTO table_or_view_name ( column_list) VALUES expression_listVALUES expression_list其中：其中：table_or_view_nametable_or_view_name：要插入数据的表或视图名字。：要插入数据的表或视图名字。

22、column_listcolumn_list：由逗号分隔的列名列表，用来指定为其提供数据的列。如：由逗号分隔的列名列表，用来指定为其提供数据的列。如果没有指定，表示向表或视图中的所有列都输入数据。果没有指定，表示向表或视图中的所有列都输入数据。expression_listexpression_list：要插入的数据值的列表。值被指定为逗号分隔的表达：要插入的数据值的列表。值被指定为逗号分隔的表达式列表，表达式的个数、数据类型、精度必须与式列表，表达式的个数、数据类型、精度必须与column_listcolumn_list列表对应的列表对应的列一致。列一致。 使用使用INSERTINSERT语

23、句每次只能向数据表中输入一条记录。语句每次只能向数据表中输入一条记录。 使用使用INSERT语句向表中插入数据语句向表中插入数据即：即：1.1.全部列输入且与值表顺序一致可以省略全部列输入且与值表顺序一致可以省略2.2.部分列输入：部分列输入：1 1）要列名；）要列名；2 2）省略部分需）省略部分需允许为空允许为空【例例6-76-7】向读者表中添加一行新记录（向读者表中添加一行新记录（T003T003，刘刚，刘刚，truetrue，研，研究生，计算机系，究生，计算机系，3-1183-118，3456734567，当前时间，当前时间，2 2）。）。INSERT into UserTb(UserI

24、d,UserName,UserSex,CateName,UserReg,UserBkNum)VALUES (T004,刘刚刘刚,1,研究生研究生,default,2)练习：练习：UserCate(UserCate(读者类型读者类型, ,借书数量，借书期限借书数量，借书期限) ) 向读者类型表插入一条记录向读者类型表插入一条记录 部分列输入：1.要列名；2.省略部分需允许为空【例例6-76-7】向读者表中添加一行新记录（向读者表中添加一行新记录（T003T003，刘刚，刘刚，truetrue，研究生，计算机系，研究生，计算机系，3-1183-118，3456734567，当前时间，当前时间，2

25、2）。）。INSERT into INSERT into UserTbUserTb VALUESVALUES (T003, (T003,刘刚刘刚,1,1,研究生研究生,计计算机系算机系,3-,3- 118,34567,default,2) 118,34567,default,2) 如果没有指定列表，则如果没有指定列表，则VALUESVALUES子句中指定值得顺子句中指定值得顺序必须与表中列的顺序一致。采用默认值的记录字段，序必须与表中列的顺序一致。采用默认值的记录字段，在值列表中要用在值列表中要用“defaultdefault”表示。表示。 列名表省略，要求列名表省略，要求次序一致次序一致（2

26、 2）使用）使用INSERTINSERTSELECTSELECT语句语句基本语法为：基本语法为： I N S E R T I N T O t a b l e _ o r _ v i e w _ n a m e I N S E R T I N T O t a b l e _ o r _ v i e w _ n a m e ( column_list) ( column_list) SELECT ( select_list ) SELECT ( select_list ) FROM table_name FROM table_name WHERE search_conditions WHERE s

27、earch_conditions INSERT INSERT语句中的语句中的SELECTSELECT子查询可用于将一个或多子查询可用于将一个或多个其它表的值添加到表中并可以一次性插入多行。子个其它表的值添加到表中并可以一次性插入多行。子查询的查询的select_listselect_list必须与必须与INSERTINSERT的的 column_listcolumn_list相相匹配。匹配。 【例例6-86-8】将所有机械工业出版社的图书记录插入到将所有机械工业出版社的图书记录插入到一个机械工业出版社表中（假设机械工业出版社表已一个机械工业出版社表中（假设机械工业出版社表已经存在，且结构与经存

28、在，且结构与“BookBook”表相同。表相同。INSERTINSERTINTO INTO 机械工业出版社机械工业出版社 (BookId, BookName, Publish) (BookId, BookName, Publish) SELECT BookId,BookName,PublishSELECT BookId,BookName,Publish FROM Book FROM Book WHERE Publish= WHERE Publish=机械工业出版社机械工业出版社 6.4.2 6.4.2 修改数据表中数据修改数据表中数据1.1.使用使用SQL Server Management

29、StudioSQL Server Management Studio修改表中的数据修改表中的数据使用使用SQL Server SQL Server Management StudioManagement Studio修改表中的数据很方便，方法如下：修改表中的数据很方便，方法如下：1) 1) 在在“对象资源管理器对象资源管理器”中打开要修改的数据表，并用鼠标定位在要修改中打开要修改的数据表，并用鼠标定位在要修改 的数据项上。的数据项上。2) 2) 将数据项内容按要求修改后可以直接离开修改的行，系统自动保存修将数据项内容按要求修改后可以直接离开修改的行，系统自动保存修 改后符合要求的数据。改后符合

30、要求的数据。2.2.使用使用Transact-SQLTransact-SQL语句修改数据项语句修改数据项使用使用Transact-SQLTransact-SQL语句修改数据项的基语句修改数据项的基本语法如下：本语法如下：UPDATE table_nameUPDATE table_nameSET column_name= expression | DEFAULT | NULL SET column_name= expression | DEFAULT | NULL FROM table_nameFROM table_name WHERE search_conditions WHERE searc

31、h_conditions 其中：其中：SETSET子句包含要更新的列和新值的列，子句包含要更新的列和新值的列，FROMFROM子句指定为子句指定为SETSET子句中的子句中的 表达式提供值的表，表达式提供值的表，WHEREWHERE子句指定条件限定所要更新的行，如果省略子句指定条件限定所要更新的行，如果省略WHEREWHERE子句，则表示要修改所有的记录。子句，则表示要修改所有的记录。使用使用UPDATE语句修改数据语句修改数据【例例6-96-9】将将UserTbUserTb读者表中，学号为读者表中，学号为“s001s001”的学生类别改为的学生类别改为“研究生研究生”。由下列语句实现：由下列

32、语句实现：UPDATE UserTb SET CateName=研究生研究生 WHERE UserId=S001-有条件要求，一条记录被修改有条件要求，一条记录被修改。练习：练习： UserTb (读者编号，姓名，性别，读者读者编号，姓名，性别，读者类别名，工作单位，住址，。类别名，工作单位，住址，。)设计：设计：修改类别名、工作单位修改类别名、工作单位【例例6-106-10】将所有读者类别的借书数量增加一本。将所有读者类别的借书数量增加一本。UPDATE UserCate SET BorrNum=BorrNum+1 没有条件，表中所有记录均被修改。没有条件，表中所有记录均被修改。练习：练习：

33、成绩表（学号，课号，成绩）成绩表（学号，课号，成绩） 设计设计1：修改成绩：修改成绩+5 设计设计2：修改某课号的成绩：修改某课号的成绩+5 问题：问题：2者区别？者区别？6.4.3 删除数据表中数据删除数据表中数据 当数据表中的数据已经过时或者没有存在的意义的时候，可以将表中当数据表中的数据已经过时或者没有存在的意义的时候，可以将表中数据删除。数据删除。1. 使用使用SQL Server Management Studio删除表中的数据删除表中的数据使用使用“SQL Server Management StudioSQL Server Management Studio”工具删除表中的数据的

34、方法如下：工具删除表中的数据的方法如下：1) 在在“对象资源管理器对象资源管理器”中打开要删除记录的数据表，并用鼠标定位在要中打开要删除记录的数据表，并用鼠标定位在要 删除的行上。删除的行上。2) 在要删除的行上单击鼠标右键，从弹出的快捷菜单中选择在要删除的行上单击鼠标右键，从弹出的快捷菜单中选择“删除删除”菜单菜单 项即可。如图项即可。如图6-20所示。所示。2. 2. 使用使用Transact-SQLTransact-SQL语句删除数据表中数据语句删除数据表中数据 使用使用Transact-SQLTransact-SQL语句删除数据表中数据时，常语句删除数据表中数据时，常用的方法是使用用的

35、方法是使用DELETEDELETE语句和语句和TRUNCATETRUNCATE语句。下面来语句。下面来分别介绍这两种方式。分别介绍这两种方式。(1) DELETE(1) DELETE语句删除表中指定记录。语句删除表中指定记录。基本格式是：基本格式是：DELETEDELETEFROM table_nameFROM table_name WHERE search_condition WHERE search_condition 使用使用DELETEDELETE删除的记录会存放在日志中，不是一删除的记录会存放在日志中，不是一种永久删除的方式。种永久删除的方式。 即：即：【例例6-116-11】删除读

36、者号是删除读者号是“T003T003”的读者。的读者。DELETE FROMDELETE FROM UserTb UserTbWHERE WHERE UserId=T003UserId=T003提示：提示：慎用没有条件的删除命令慎用没有条件的删除命令！练习：练习：UserTb (读者编号，姓名，性别，读者类别名，读者编号，姓名，性别，读者类别名，工作单位，住址，。工作单位，住址，。)设计：设计：删除某读者类别的全部记录删除某读者类别的全部记录(2) TRUNCATE(2) TRUNCATE语句删除数据语句删除数据 TRUNCATE TABLETRUNCATE TABLE语句也可以删除数据表中语句也可以删除数据表中的数据，它只针对整个数据页的释放，与的数据，它只针对整个数据页的释放，与DELETEDELETE语句相同，只删除表内数据，表结构保语句相同，只删除表内数据，表结构保留。留。TRUNCATETRUNCATE语句的语法是：语句的语法是： TRUNCATE TABLE table_nameTRUNCATE TABLE table_name警告！警告！由于TRUNCATE TABLE语句是一种无日志记录