《sql server数据库应用与实践教程》课件第4章-表的管理 -创建与维护.ppt

1、第四章第四章表的管理表的管理表的创建表的创建表的约束表的约束表的添加、删除、修改表的添加、删除、修改数据类型数据类型SQL Server提供了丰富的系统定义的数据类型：提供了丰富的系统定义的数据类型：整型数据类型；整型数据类型；定点数据类型；定点数据类型；浮点数据类型；浮点数据类型；字符数据类型；字符数据类型；日期和时间数据类型；日期和时间数据类型；图形数据类型；图形数据类型；货币数据类型；货币数据类型；位数据类型；位数据类型；二进制数据类型；二进制数据类型；其他数据类型。其他数据类型。1.bigint类型类型 存储大小存储大小:8个字节，取值范围个字节，取值范围:-263263-12.int

2、类型类型 存储大小存储大小:4个字节，取值范围个字节，取值范围:-231231-13.smallint类型类型 存储大小存储大小:2个字节，取值范围个字节，取值范围:-215215-14.tinyint类型类型 存储大小存储大小:1个字节，取值范围个字节，取值范围:0255定点数据类型用于表示定点实数，包括定点数据类型用于表示定点实数，包括numeric和和decimal类型。类型。Numeric等价于等价于decimal。(用于高精度数据存储)格式：格式：decimal(p,s)numeric(p,s)说明说明:n p:表示精度，指定小数点左边和右边十进制数字的最大表示精度，指定小数点左边和

3、右边十进制数字的最大位数，取值在位数，取值在1到到38之间，缺省值为之间，缺省值为18；n s:指定小数点右边十进数的最大位数，取值在指定小数点右边十进数的最大位数，取值在0到到p之间，之间，缺省值为缺省值为0。n 表示范围表示范围:-1038+11038-1浮点数据类型采用科学计数法存储十进制小数，包括浮点数据类型采用科学计数法存储十进制小数，包括real和和float数据类型。数据类型。1.float类型类型格式格式:float(n)说明说明:n n:科学记数法尾数的位数，具体如下表：科学记数法尾数的位数，具体如下表：表示范围表示范围:-1.79E+3081.79E+308尾数位数尾数位数

4、n精度精度存储字节数存储字节数1247位位42553或省略或省略 15位位82.real类型类型 存储大小存储大小:4个字节个字节 取值范围取值范围:-3.40E+383.40E+38 精度精度:7位位 在在SQL Server中，中，real的同义词为的同义词为float(24)。1.char类型类型格式格式:char(n)功能功能:定义长度为定义长度为n个字节的固定长度非个字节的固定长度非Unicode字符数据，字符数据，每个字符占一个字节。每个字符占一个字节。说明说明:n:18000 存储大小存储大小:n个字节（个字节（n个字符）个字符）2.varchar类型类型格式格式:varchar

5、(n)功能功能:定义长度定义长度最多最多为为n个字节的可变长度非个字节的可变长度非Unicode字符数字符数据据,每个字符占一个字节。每个字符占一个字节。说明说明:n:18000。存储大小存储大小:输入字符的实际长度。长度可为零输入字符的实际长度。长度可为零注：注：n的缺省长度为的缺省长度为1。3.nchar类型类型格式格式:nchar(n)功能功能:定义包含定义包含n个字符的固定长度个字符的固定长度Unicode字符数据字符数据说明说明:n:14000 存储大小存储大小:2n个字节个字节4.nvarchar类型类型格式格式:nvarchar(n)功能功能:定义包含最多定义包含最多n个字符的可

6、变长度个字符的可变长度Unicode字符数据字符数据说明说明:n:14000。存储大小存储大小:所输入的字符实际个数的两倍。长度所输入的字符实际个数的两倍。长度可以为零。可以为零。注：注：n的缺省长度为的缺省长度为1。5.text类型与类型与ntext类型类型 （2G）（1G）日期和时间数据类型用于存储日期和时间的结合体。包括日期和时间数据类型用于存储日期和时间的结合体。包括datetime和和smalldatetime两种类型。两种类型。1.datetime类型类型 存储大小存储大小:8个字节个字节 表示范围表示范围:1753年年1月月1日零时日零时9999年年12月月31日日23时时59分

7、分59秒。秒。例例:01/01/98 23:59:59 2000-5-29 12:30:482.smalldatetime 存储大小存储大小:4个字节个字节 表示范围表示范围:1900年年1月月1日日2079年年6月月6日。日。例例:2000/05/08 12:35 2000-05-29 12:35 2000-05-29image数据类型用于存储可变长度二进制数据，其长度界于数据类型用于存储可变长度二进制数据，其长度界于0到到231-1个字节之间。个字节之间。货币数据类型包括货币数据类型包括money和和smallmoney数据类型。货币数数据类型。货币数据存储的精确度为四位小数。据存储的精确

8、度为四位小数。1.money类型类型 存储大小存储大小:8个字节个字节 表示范围表示范围:-922,337,203,685,477.5808 +922,337,203,685,477.58072.smallmoney类型类型 存储大小存储大小:4个字节个字节 表示范围表示范围:-214,748.3648214,748.3647bit数据类型的取值只有数据类型的取值只有False和和True，如果一个表中有不，如果一个表中有不多于多于8个的个的bit列，这些列将作为一个字节存储。如果表中有列，这些列将作为一个字节存储。如果表中有9到到16个个bit列，这些列将作为两个字节存储。更多列的情况依列，

9、这些列将作为两个字节存储。更多列的情况依此类推。此类推。二进制数据类型又可以分为二进制数据类型又可以分为binary和和varbinary类型。类型。1.binary类型类型 格式格式:binary(n)功能功能:定义固定长度的定义固定长度的n个字节二进制数据，当输入的二进制个字节二进制数据，当输入的二进制数据长度小于数据长度小于n时，余下部分填充时，余下部分填充0。说明说明:n:180002.varbinary类型类型格式格式:varbinary(n)功能功能:定义定义n个字节可变长度二进制数据。个字节可变长度二进制数据。说明说明:n:180001.timestamp类型类型 存储大小存储大

10、小:8个字节。个字节。时间戳类型的数据用于提供数据库范围内的惟一值，反时间戳类型的数据用于提供数据库范围内的惟一值，反映数据库中数据修改的相对顺序，相当于一个单调上升的计数映数据库中数据修改的相对顺序，相当于一个单调上升的计数器。当表中的某列定义为器。当表中的某列定义为timestamp类型时，在对表中某行类型时，在对表中某行进行修改或添加行时，相应进行修改或添加行时，相应timestamp类型列的值会自动被类型列的值会自动被更新。更新。2.table类型类型 用于存储对表或者视图处理后的结果集。这种新的数据用于存储对表或者视图处理后的结果集。这种新的数据类型使得用变量就可以存储一个表，从而使

11、函数或过程返回查类型使得用变量就可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。询结果更加方便、快捷。3.cursor类型类型 cursor类型是变量或存储过程类型是变量或存储过程 的的OUTPUT 参数的一参数的一种数据类型，这些参数包含对游标的引用。种数据类型，这些参数包含对游标的引用。表是数据库存储数据的主要对象表是数据库存储数据的主要对象，SQL Server数据库的表由行和列组成。数据库的表由行和列组成。在在SQL Server中，表分为永久表和临时表两中，表分为永久表和临时表两种。种。数据通常存储在永久表中，如果用户不手数据通常存储在永久表中，如果用户不手动删除，永久表

12、和其中的数据将永久存在。动删除，永久表和其中的数据将永久存在。临时表存储在临时表存储在tempdb数据库中，当不再使数据库中，当不再使用时系统会自动删除临时表。用时系统会自动删除临时表。使用企业管理器创建表使用企业管理器创建表 使用使用CREATE TABLE语句创建表语句创建表 列名列名 列的数据类型列的数据类型 是否允许为空（是否允许为空（NULL或或NOT NULL）标识列（标识列（IDENTITY）列说明列说明CREATE TABLE 表名表名(列名列名1 数据类型和长度数据类型和长度1 列说明列说明1,列名列名2 数据类型和长度数据类型和长度2 列说明列说明2,.列名列名n 数据类型

13、和长度数据类型和长度n 列说明列说明n,)列名列名数据类型数据类型说明说明StuIDint标识列标识列StuNamevarchar(50)不允许空不允许空StuSexchar(1)无无StuClasschar（6）不允许空不允许空StuScorefloat无无PostCodechar(6)无无DepIDint不允许空不允许空例例1：有：有Student表结构如下：表结构如下：CREATE TABLE Student(StuID int IDENTITY(1,1),StuName varchar(50)NOT NULL,StuSex char(1),StuClass varchar(50)NOT

14、 NULL,StuScore float,PostCode char(6),DepID int NOT NULL)列名列名数据类型数据类型说明说明StuIDint标识列标识列StuNamevarchar(50)不允许空不允许空StuSexchar(1)无无StuClasschar（6）不允许空不允许空StuScorefloat无无PostCodechar(6)无无DepIDint不允许空不允许空主键（主键（PRIMARY KEY）约束）约束唯一性（唯一性（UNIQUE）约束）约束检查（检查（CHECK）约束）约束默认（默认（DEFAULT）约束）约束外部键（外部键（FOREIGN KEY）约束

15、）约束CREATE TABLE 表名表名(列名列名 列类型列类型 列说明列说明,n):=CONSTRAINT 约束名约束名 约束类型约束类型 约束条件约束条件 n主键是表中的一列或一组列，它们的值可以主键是表中的一列或一组列，它们的值可以唯一地标识表中的每一行。在创建和修改表唯一地标识表中的每一行。在创建和修改表时，可以定义主键约束。主键列的值不允许时，可以定义主键约束。主键列的值不允许为空。为空。语法：语法：CONSTRAINT 约束名约束名 PRIMARY KEY(列名列名1,列名列名2,列名列名n)示例：示例：1.使用使用CREATE TABLE语句创建语句创建“Student”表，同时

16、表，同时定义定义“StuID”列为主键。列为主键。2.创建表创建表Employee(EmpID,ProjID,Duration)解决方案：解决方案：1.CREATE TABLE Student (StuID int identity(1,1)CONSTRAINT pkStuID PRIMARY KEY,StuName varchar(50)解决方案：解决方案：2.CREATE TABLE Employee (EmpID int,ProjID int CONSTRAINT pkEmpPro PRIMARY KEY(EmpID,ProjID),Duration int )唯一性约束可以保证除主键外

17、的其他一个或唯一性约束可以保证除主键外的其他一个或多个列的数据唯一性，以防止在列中输入重多个列的数据唯一性，以防止在列中输入重复的值。唯一性约束允许一个空值。复的值。唯一性约束允许一个空值。语法：语法：CONSTRAINT 约束名约束名 UNIQUE示例：示例：1.使用使用CREATE TABLE语句创建语句创建“Student”表，同时表，同时定义定义“StuID”列为主键，列为主键，”StuName”列为唯一性约束。列为唯一性约束。解决方案：解决方案：1.CREATE TABLE Student (StuID int identity(1,1)CONSTRAINT pkID PRIMARY

18、 KEY,StuName varchar(50)CONSTRAINT uqeStuName UNIQUE )检查约束指定表中一列或多列可以接受的数据值或格式。检查约束指定表中一列或多列可以接受的数据值或格式。例如，表例如，表“Student”中的中的“StuScore”列的值应该大于列的值应该大于或等于或等于0。语法：语法：CONSTRAINT 约束名约束名 CHECK(逻辑表达式逻辑表达式)|(字段名字段名 IN|BETWEEN|LIKE)示例：示例：1.使用使用CREATE TABLE语句创建语句创建“Student”表，同时创建表，同时创建检查约束，定义检查约束，定义“StuScore”

19、列的值大于或等于列的值大于或等于0。解决方案：解决方案：CREATE TABLE Student(StuID int CONSTRAINT pkStuID PRIMARY KEY,StuName varchar(50)CONSTRAINT uniStuName UNIQUE,StuScore float CONSTRAINT chkStuScore CHECK(StuScore=0)语法：语法：CONSTRAINT 约束名约束名 CHECK(逻辑表达式逻辑表达式)|(字段名字段名 IN|BETWEEN|LIKE)示例：示例：2.使用使用CREATE TABLE语句创建语句创建“Student”

20、表，同时定义表，同时定义“StuSex”列的值只能是列的值只能是“男男”或或“女女”。”StuScore”列为列为0到到100之间。之间。解决方案：解决方案：CREATE TABLE Student(StuID int CONSTRAINT pkStuID PRIMARY KEY,StuName varchar(50)CONSTRAINT uquStuName UNIQUE,StuSex char（2）CONSTRAINT chkStuSex CHECK(StuSex IN（男男,女女）),StuScore float CONSTRAINT chkStuScore CHECK(StuScore

21、 between 0 and 100)示例：示例：3.使用使用CREATE TABLE语句创建语句创建“Student”表，同时表，同时定义定义“PostCode”列的值是由列的值是由6位数字组成的字符串。位数字组成的字符串。解决方案：解决方案：CREATE TABLE Student(StuID int CONSTRAINT pkStuID PRIMARY KEY,StuScore float CONSTRAINT chkStuScore CHECK(StuScore between 0 and 100)PostCode char(6)CONSTRAINT chkPostCode CHECK

22、(PostCode LIKE 0-90-90-90-90-90-9)语法：语法：CONSTRAINT 约束名约束名 CHECK(逻辑表达式逻辑表达式)|(字段名字段名 IN|BETWEEN|LIKE)默认约束可以为指定列定义一个默认值。在输入数据时，默认约束可以为指定列定义一个默认值。在输入数据时，如果没有输入该列的值，则将该列的值设置为默认值。如果没有输入该列的值，则将该列的值设置为默认值。语法：语法：CONSTRAINT 约束名约束名 DEFAULT 约束表达式约束表达式示例：示例：1.使用使用CREATE TABLE语句创建语句创建“Student”表，同时定表，同时定义义”StuSex

23、”列的取值为列的取值为”男男”或或”女女”，且默认值为，且默认值为”男男”。解决方案：解决方案：1.CREATE TABLE Student (StuID int CONSTRAINT pkStuID PRIMARY KEY,StuSex char（2）CONSTRAINT chkStuSex CHECK(StuSex IN(男男,女女)CONSTRAINT dftStuSex DEFAULT(男男)注注:一个字段可以添加多个约束，一个字段可以添加多个约束，约束之间不用逗号约束之间不用逗号外键约束是用于建立两个表之间的联系。通过将当外键约束是用于建立两个表之间的联系。通过将当前表中的某一列关联

24、到另一个表的主键列，可创建前表中的某一列关联到另一个表的主键列，可创建两个表之间的连接。当前表中的列就成为外键。两个表之间的连接。当前表中的列就成为外键。Student表：表：Department表：表：StuIDStuNameDepID1Tom1DepIDDepName1Computer主表主表从表从表外键外键必须是主键必须是主键才能被参照才能被参照外键约束是用于建立两个表之间的联系。通过将当外键约束是用于建立两个表之间的联系。通过将当前表中的某一列关联到另一个表的主键列，可创建前表中的某一列关联到另一个表的主键列，可创建两个表之间的连接。当前表中的列就成为外键。两个表之间的连接。当前表中的

25、列就成为外键。语法：语法：CONSTRAINT 约束名约束名 FOREIGN KEY REFERENCES 关联关联表表(关联列名关联列名)示例：示例：1.已存在表已存在表“Department”，其中主键列，其中主键列“DepID”，创建创建“Student”表的外键表的外键“DepID”，使其参照主键，使其参照主键列列。解决方案：解决方案：1.CREATE TABLE Student (StuID int CONSTRAINT pkStuID PRIMARY KEY,DepID int CONSTRAINT fkDepID FOREIGN KEY REFERENCES Department

26、(DepID)列名列名数据类型数据类型说明说明EmpIDchar(5)主键，员工号以主键，员工号以E打头，后面跟打头，后面跟4位数字位数字EmpNamevarchar(50)不允许空不允许空EmpAgeint介于介于20-60之间之间EmpTitlechar(4)只能是高级、中级或初级只能是高级、中级或初级DepIDint外键，参照外键，参照Department表的表的DepID字段字段创建创建Department表和表和Employee表表列名列名数据类型数据类型说明说明DepIDint主键主键DepNamevarchar(50)不允许空不允许空创建创建SC表（表（StuID，CourseI

27、D，Grade）解决方案：解决方案：create table SC(StuID int constraint fkStuID foreign key references student(StuID),CourseID int constraint fkCourseID foreign key references course(CourseID)constraint pkSnoCno primary key(StuID,CourseID),Grade int constraint chkGrade check(Grade between 0 and 100)使用企业管理器修改表。使用企业管理

28、器修改表。使用使用ALTER TABLE语句修改表语句修改表(1)向表中添加列向表中添加列 ALTER TABLE 表名表名 ADD 列名列名 数据类型和长度数据类型和长度 列属性列属性 例例:ALTER TABLE Student ADD Address varchar(50)NULL(2)修改列属性修改列属性 ALTER TABLE 表名表名 ALTER COLUMN 列名列名 新数据类型和长度新数据类型和长度 新列属性新列属性 例例:ALTER TABLE Student ALTER COLUMN Address varchar(30)NOT NULL(3)删除列删除列 ALTER TA

29、BLE 表名表名 DROP COLUMN 列名列名 例：例：ALTER TABLE Student DROP COLUMN Address注：不可用以下方式修改表，否则编译器报错！注：不可用以下方式修改表，否则编译器报错！ALTER TABLE Student(StuID int IDENTITY(1,1),StuName varchar(50)NOT NULL,StuSex char(1),DepID int NOT NULL)可以使用可以使用sp_rename存储过程修改表或列的名存储过程修改表或列的名称。称。sp_rename 原对象名原对象名,新对象名新对象名例：将例：将Student

30、表名改为表名改为StudentInfosp_rename Student,StudentInfo使用使用DROP TABLE语句删除语句删除“Student”表。表。DROP TABLE Student注意：使用注意：使用DROP TABLE语句删除表时，不需语句删除表时，不需要用户确认，因此在使用时要慎重。要用户确认，因此在使用时要慎重。数据的插入数据的插入数据的修改数据的修改数据的删除数据的删除语法：语法：INSERT INTO 表名表名 (列名列名1,列名列名2,.,列名列名n)VALUES(值值1,值值2,.,值值n)解决方案：解决方案：INSERT INTO Employee(Emp

31、ID,EmpName,EmpSex,DepID)VALUES(1,Tom,f,1)EmpIDEmpNameEmpSexDepID1Tomf1在在Employee(EmpID,EmpName,EmpSex,DepID)表中插入一条记录：表中插入一条记录：思考：思考：假设表中的列有几十甚至上假设表中的列有几十甚至上百个，需要将所有的列都一一写在百个，需要将所有的列都一一写在表名之后吗？表名之后吗？当所有字段均需赋值时，表名之后的列名可以不写，默认为全部字段。当所有字段均需赋值时，表名之后的列名可以不写，默认为全部字段。INSERT INTO EmployeeVALUES(1,Tom,f,1)解决方

32、案：解决方案：INSERT INTO Employee(EmpName,EmpSex,DepID)VALUES(Jack,f,1)EmpIDEmpNameEmpSexDepID2Jackf13maryNULL2在在Employee(EmpID,EmpName,EmpSex,DepID)表中插入如下记录：表中插入如下记录：语法：语法：INSERT INTO 表名表名 (列名列名1,列名列名2,.,列名列名n)VALUES(值值1,值值2,.,值值n)INSERT INTO Employee(EmpName,DepID)VALUES(mary,2)IDENTITY属性属性 在插入数据时，还需要考虑

33、到表的约束等因素，如果插入在插入数据时，还需要考虑到表的约束等因素，如果插入的数据违反表约束或规则，则无法正常插入数据。例如：的数据违反表约束或规则，则无法正常插入数据。例如：（1）不允许设置标识列的值）不允许设置标识列的值（2）不允许向唯一性约束列中插入相同的数据）不允许向唯一性约束列中插入相同的数据（3）不能违反检查约束）不能违反检查约束（4）不能违反外键约束）不能违反外键约束EmpIDEmpNameEmpSexDepID2Nancyabc14BiffNULL200语法：语法：UPDATE 表名表名 SET 列名列名1=值值1,列名列名2=值值2,.,列名列名n=值值n WHERE 更新条

34、件更新条件 示例：示例：使用使用UPDATE语句将语句将“Student”表中所有班级为表中所有班级为”112051”的学生的成绩加的学生的成绩加10分。分。解决方案：解决方案：UPDATE Student SET StuScore=StuScore+10 WHERE StuClass=112051语法：语法：UPDATE 表名表名 SET 列名列名1=值值1,列名列名2=值值2,.,列名列名n=值值n WHERE 更新条件更新条件 示例：示例：使用使用UPDATE语句将语句将“Student”表中所有班级为表中所有班级为”112051”的学生的成绩加的学生的成绩加10分分,系号改为系号改为5

35、系。系。解决方案：解决方案：UPDATE Student SET StuScore=StuScore+10,DepID=5 WHERE StuClass=112051EmpIDEmpNameEmpSexDepID3Nancym14BiffNULL2EmpIDEmpNameEmpSexDepID2Nancyabc14BiffNULL200在修改数据时，还需要考虑到表的约束等因素，如果修改的数据违反表在修改数据时，还需要考虑到表的约束等因素，如果修改的数据违反表约束或规则，则无法正常修改数据。例如：约束或规则，则无法正常修改数据。例如：（1）不允许设置标识列的值）不允许设置标识列的值（2）不允许向

36、唯一性约束列中设置相同的数据）不允许向唯一性约束列中设置相同的数据（3）不能违反检查约束）不能违反检查约束（4）不能违反外键约束）不能违反外键约束语法：语法：DELETE FROM 表名表名 WHERE 删除条件删除条件示例：示例：使用使用DELETE语句从语句从”Student”表中删除表中删除”StuID”为为2的记的记录。录。解决方案：解决方案：DELETE FROM Student WHERE StuID=2注：注：DELETE和和FROM之间不加任何字段，否则编译器报错！之间不加任何字段，否则编译器报错！数据库中有数据库中有”Student”表及表及”Department”表，两表之

37、表，两表之间的联系依赖间的联系依赖DepID字段，现要删除字段，现要删除”Department”表中表中1系，如何实现？系，如何实现？注：建表时，按照主表注：建表时，按照主表-从表的顺序创建，删除表时，按照从表从表的顺序创建，删除表时，按照从表-主表的顺序删除。主表的顺序删除。StuIDStuNameDepID1Tom1DepIDDepName1Computer 数据库中有数据库中有”Student”表及表及”Department”表，两表之表，两表之间的联系依赖间的联系依赖DepID字段，现要删除字段，现要删除”Department”表中表中1系，如何实现？系，如何实现？解决方案：解决方案：

38、DELETE FROM Student WHERE DepID=1 DELETE FROM Department WHERE DepID=1数据库中有以下几张表，说出建表及删除表顺序。数据库中有以下几张表，说出建表及删除表顺序。Student(StuID,StuName,StuSex,DepID)Department(DepID,DepName)SC(StuID,CourseID,Score)Course(CourseID,CourseName)解决方案：解决方案：建表：建表：DepartmentStudentCourseSC删除表：删除表：SC CourseStudentDepartment

39、 使用使用TRUNCATE TABLE语句可以清空表中语句可以清空表中所有数据所有数据示例：示例：清空清空“Student”表表TRUNCATE TABLE Student1.创建表创建表Department列名列名数据类型数据类型说明说明DepIDintPrimary KeyDepNamevarchar(20)NOT NULL2.创建表创建表Course列名列名数据类型数据类型说明说明CourseIDintPrimary KeyCourseNamevarchar(20)NOT NULL列名列名数据类型数据类型说明说明StuIDintPrimary KeyStuNamevarchar(20)N

40、OT NULLStuAgeintStuAge在15到100之间StuSexchar(1)只能取值为男或女，默认男StuCityvarchar(20)无StuGradefloatStuGrade在0到100之间DepIDint参照Department表主键3.创建表创建表Student4.创建表创建表SC列名列名数据类型数据类型说明说明StuIDint主键，外键CourseIDint主键，外键Scorefloat介于0到100之间5.使用使用insert语句语句为各表添加若干记录为各表添加若干记录6.使用使用update语句将语句将1系学生的系学生的StuGrade值值加加107.删除删除1系。系。表的创建表的创建带有约束规则的表创建带有约束规则的表创建表维护表维护创建表创建表表维护表维护StuIDStuNameStuAgeStuSexStuCityStuScoreDepIDA00001Tom21男男NanJing901A00002Mary10女女NanJing902A00003Nancy30女女NanJing95100A00004Jack21男男NULL901