SQL管理数据表.ppt

《SQL管理数据表.ppt》由会员分享，可在线阅读，更多相关《SQL管理数据表.ppt（79页珍藏版）》请在三一办公上搜索。

1、1,第3章 管理数据表及数据完整性,3.1 SQL Server表的概念和数据类型3.2 创建和管理数据表3.3 完整性的概念和约束类型 3.4 约束的创建和查看 3.5 删除约束、使用规则和使用默认值,2,3.1 SQL Server表的概念,1.表的概念 在SQL Server数据库中，表定义为列的集合，与Excel电子表格相似，数据在表中是按行和列的格式组织排列的。每行代表唯一的一条记录，而每列代表记录中的一个域。,关系名(表名),属性（列或字段）,关系模式,学生表,3,3.1 SQL Server表的概念,2.表的设计 在SQL Server创建表有如下限制：每个数据库里最多有20亿个

2、表。每个表上最多可以创建一个聚集索引和249个非聚集索引。每个表最多可以配置1024个字段。每条记录最多可以占8060B，但不包括text字段和image字段。,4,第3章 管理数据表,3.1 SQL Server表的概念3.2 SQL Server中的数据类型3.3 创建数据表3.4 管理数据表,5,3.2 SQL Server 2005数据类型,1.二进制数据 2.字符型数据类型 3.Unicode字符数据 4.日期时间型数据 5.整数型数据,6.精确数值型数据 7.近似数值类型 8.货币数据 9.位类型数据,6,3.2 SQL Server 2005数据类型,1.二进制数据功能：常用于存

3、储图像等数据，包括长二进制数据binary、变长二进制数据varbinary和image三种。,7,3.2 SQL Server 2005数据类型,2.字符型数据类型功能:用于存储汉字、英文字母、数字、标点和各种符号，必须由英文单引号括起来。,8,3.2 SQL Server 2005数据类型,3.Unicode字符数据 功能：存放Unicode字符数据.支持的字符范围更大，存储所需 要的空间也更大。,9,3.2 SQL Server 2005数据类型,4.日期时间型数据功能：用于存储日期和时间数据。,注意:当使用日期格式数据时，在字符串中可以使用 斜杠/,连字符-或句号.作为分隔符。在SQL

4、SERVER2008中增加了date、time、datetime2、datetimeoffset类型,10,3.2 SQL Server 2005数据类型,5.整数型数据功能：用于存储整型数据。,11,3.2 SQL Server 2005数据类型,6.精确数值型数据功能：用于存储带有小数点且小数点后位数确定的实数。,12,3.2 SQL Server 2005数据类型,7.近似数值类型功能：用于存储浮点数。,13,3.2 SQL Server 2005数据类型,8.货币数据,14,3.2 SQL Server 2005数据类型,9.位类型数据功能：位类型数据用于存储整数，只能取 1、0 或N

5、ULL，常用于逻辑数据的存取。说明：在位类型的字段中输入0和1之外的任何值，系统都会 作为1来处理。如果一个表中有8个以下的位类型数据字段，则系统会用一 个字节存储这些字段；如果表中有9个以上16个以下位类型数据 字段，则系统会用两个字节来存储这些字段。,15,第3章 管理数据表,3.1 SQL Server表的概念3.2 SQL Server中的数据类型3.3 创建数据表3.4 管理数据表,16,3.3 创建数据表,1.使用SSMS创建表 创建表的步骤:1）定义表结构：给表的每一列取字段名，并确定每一列的数据类型、数据长度、列数据是否可以为空等。2）设置约束：设置约束是为了限制该列输入值的取

6、值范围，以保证输入数据的正确性和一致性。3）添加数据：表结构建立完成之后，就可以向表中输入数据。,17,3.3 创建数据表,1.使用SSMS创建表 例1:请在student数据库中建立“学生基本信息”表的结构。,注:主键的字段值不能为空,且字段值在表中必须唯一,18,3.3 创建数据表,2.使用T-SQL语句创建表 1）语法：CREATE TABLE database_name.owner.|owner.table_name(column_definition|PRIMARY KEY|UNIQUE,N):=column_name data_type DEFAULT constraint_exp

7、ression|IDENTITY(seed,increment),n,19,3.3 创建数据表,语法注释：database_name：指定创建的表所在的数据库，在当前数据库创建表时该项可以省略。owner：指定表的拥有者，如果表的拥有者为当前用户，该项可以省略。table_name：指定新建表的名称。column_definttion：为表中字段的定义表达式。column_name：为表中的字段名。data_type：为字段的数据类型。PRIMARY KEY：为主键约束关键字。UNIQUE：为唯一约束关键字。DEFAULT：为默认约束关键字。IDENTITY：为自动编号标识。Seed：为自动标

8、识的开始值，默认为1。Increment：为自动编号的步长或增量，默认为1。,20,3.3 创建数据表,例2:创建带有参照约束的学生表stud_info，学生表的表结构定义如 下表所示。“学号”字段为学生表的主键。,21,3.3 创建数据表,创建“学生”表的代码如下：USE StudentGOCREATE TABLE stud_info(学号 char(8)PRIMARY KEY,姓名 char(10)NOT NULL,性别 char(2)DEFAULT 男,出生年月 datetime,籍贯 varchar(20),家庭住址 varchar(60),电话 char(15),所属班级 char(

9、8)）GO,22,3.3 创建数据表,例3：在学生管理数据库中用T-SQL语句创建一个学生成绩表 stud_score，表结构如下，其中包含标识列和计算列。,23,3.3 创建数据表,实现的T-SQL语句:CREATE TABLE stud_score(Scoreid int IDENTITY(1,1)PRIMARY KEY,Stud_no char(8)NOT NULL,Math int DEFAULT 0,Chinese int DEFAULT 0,Computer int DEFAULT 0,total_score AS Math+Chinese+computer)GO,24,3.3 创

10、建数据表,设置列的标识属性时，应注意:该列的数据类型必须是decimal,int,numeric,smallint,bigint,tinyint中的一种，才可以设置标识属性。标识列不允许为空值，也不能包含默认属性。每个表中只允许有一个标识列，并且不可以修改。设置计算列时，应注意:在计算列中不能添加如primary key、unique、foreign key、default等约束条件。不能对计算列进行赋值。,25,第3章 管理数据表,3.1 SQL Server表的概念3.2 SQL Server中的数据类型3.3 创建数据表3.4 管理数据表,26,3.4 管理数据表,3.4.1 修改表结构

11、 3.4.2 删除数据表 3.4.3 查看数据表,27,3.4.1 修改表结构,1 使用SSMS修改表结构例4：修改“student”库中“学生基本信息表”表结构。,28,3.4.1 修改表结构,2 使用T-SQL语言修改表 1）语法：ALTER TABLE table_name ALTER COLUMN column_name new_data_type(precision,scale)NULL|NOT NULL|ADD,.n|DROP CONSTRAINT constraint_name|COLUMN column,.n DEFAULT constant_expression WITH V

12、ALUES|IDENTITY(seed,increment),29,修改表结构,2）语法注释：ALTER COLUMN：修改表列属性的子句 ADD：增加列或约束的子句 DROP COLUMN：删除表列的子句 table_name：需要修改表的表格名称 column_name：希望增加的字段名 data_type：需要增加的字段的数据类型名 collation_name：排序规则名,30,修改表结构,例5：使用T-SQL代码修改学生基本信息表stud_info结构,增加字段“QQ号码”，数据类型为varchar，长度为15，可为空；增加入学时间字段，时间/日期类型，默认时间为2006-9-1。U

13、SE studentGOALTER TABLE stud_infoADD QQ号码 varchar(15)GOALTER TABLE stud_infoADD 入学时间 datetime DEFAULT 2006-9-1GO,31,修改表结构,例6：使用T-SQL代码修改学生基本信息表stud_info结构，修改字段“家庭住址”的长度为100。实现的T-SQL语句USE studentGOALTER TABLE stud_infoALTER COLUMN 家庭住址 varchar(100)GO,32,修改表结构,例7：使用T-SQL语句删除学生基本信息表stud_info的QQ号码字段。实现的

14、T-SQL语句USE studentGOALTER TABLE stud_infoDROP COLUMN QQ号码GO,33,3.4 管理数据表,3.4.1 修改表结构 3.4.2 删除数据表 3.4.3 查看数据表,34,3.4.2 删除数据表,1 使用SSMS删除数据表2 使用T-SQL语句删除数据表语法格式为：DROP TABLE table_name例8：使用T-SQL语句删除学生基本信息表。实现的T-SQL语句 USE student GO DROP TABLE 学生基本信息表 GO,35,3.4 管理数据表,3.4.1 修改表结构 3.4.2 删除数据表 3.4.3 查看数据表,3

15、6,3.4.3 查看数据表,1 使用SSMS对象资源管理器查看表属性信息。在对象资源管理器中，右键单击“student”数据库的学生基本信息表stud_info，从弹出的快捷菜单中选择“属性”命令。,37,3.4.3 查看数据表,2 使用T-SQL语句查看数据表 Exec sp_help table_name 例9:查看学生基本信息表stud_info 属性信息。Exec Sp_help stud_info,38,3.5 完整性的概念,完整性的概念 数据完整性指存储在数据库中的数据正确无误并且相关数据具有一致性。数据完整性可分为以下四种：1、实体完整性：要求在表中不能存在两条完全相同的记录。实

16、现实体完整性的方法有：主键约束、唯一约束、指定IDENTITY属性。,39,3.5 完整性的概念,2、域完整性：要求向表中指定列输入的数据必须具有正确的数据类型、格式及有效的数据范围。实现域完整性的方法有：检查约束、外键约束、非空约束、规则及在建表时设置的数据类型。3、参照完整性：指作用于有关联的表通过主键和外键或主键和惟一键间的关系，使表中的键值在相关表中保持一致。实现参照完整性的方法有：外键约束.4、用户定义的完整性：指应用领域需要遵守的约束条件。实现用户定义完整性的方法包括规则、触发器和存储过程等,40,3.6 约束的类型,1.NOT NULL（非空）约束 2.PRIMARY KEY（主

17、键）约束3.UNIQUE（惟一）约束4.CHECK（检查）约束 5.FOREIGN KEY（外健）约束 6.DEFAULT（默认）约束,41,3.6 约束的创建,使用 CREATE TABLE 或者 ALTER TABLECREATE TABLE 是在创建表时创建约束ALTER TABLE 是在一个已有的表上添加约束可添加单列或多列约束若约束应用于单列，称为列级约束若约束引用了多列，称为表级约束，一般此类约束都是在表创建完成后再进行添加约束.,42,3.6 约束的创建,CREATE TABLE table_name(column_name data_type.n DEFAULT constan

18、t_expression,43,创建主键约束,1.创建主键可以在建表时使用CREATE TABLE 命令完成。示范案例1 在student库中，建立一个民族表（民族代码，民族名称），将民族代码指定为主键。其程序清单如下：CREATE TABLE native(native_id char(2)CONSTRAINT pk_mzdm PRIMARY KEY,Native_name varchar(30)NOT NULL）GO,44,创建主键约束,2.为已存在的表创建主键约束，其语法格式如下：ALTER TABLE table_name ADD CONSTRAINT constraint_name

19、PRIMARY KEY CLUSTERED|NONCLUSTERED(column,n),45,创建主键约束,示范案例2 使用T-SQL语句在学生信息管理数据库“班级”表class中，指定字段“班级代号”class_id为表的主键。程序清单如下：ALTER TABLE class ADD CONSTRAINT pk_bjbh PRIMARY KEY(class_id),46,创建主键约束,每张表只能有一个 PRIMARY KEY 约束输入的值必须是惟一的不允许空值将在指定列上创建惟一索引,47,3.6.2 创建惟一约束,为存在的表创建惟一约束，其语法格式如下：ALTER TABLE table

20、_name ADD CONSTRAINT constraint_name UNIQUE CLUSTERED|NONCLUSTERED(column,n),48,3.6.2 创建惟一约束,示范案例3 使用T-SQL语句在学生信息管理数据库student中，为民族表native中的“民族名称”native_name字段创建一个惟一约束。程序清单如下：ALTER TABLE native ADD CONSTRAINT uk_mzmz UNIQUE(native_name)GO,49,3.6.2 创建惟一约束,允许一个空值在一个表上允许多个 UNIQUE 约束可在一个或者多个列上定义是通过一个惟一索引

21、强制约束的,50,3.6.3 创建检查约束,使用SQL语句为已存在的表创建检查约束，其语法格式如下：ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(logical_expression),51,3.6.3 创建检查约束,示范案例4 使用T-SQL语句在学生信息管理数据库student中，为学生成绩表score中的成绩“Sscore”字段创建一个检查约束，以保证输入的数据大于等于0而小于等于100。程序清单如下：ALTER TABLE score ADD CONSTRAINT ck_chengji CHECK(Sscore=

22、0 and Sscore=100),52,3.6.3 创建检查约束,在每次执行 INSERT 或者 UPDATE 语句的时候校验数据值。可以引用同表中的其他列,但不能引用其他表中的列。不能包含子查询。列级 CHECK 约束可省略名字，让系统自动生成。表达式可以用 AND 以及 OR 连接以表示复杂逻辑。CHECK 约束中可使用系统函数。,53,创建默认约束,使用SQL语句为已存在的表创建默认约束，其语法格式如下：ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT constant_expression FOR column

23、_name,54,创建默认约束,示范案例5 使用T-SQL语句在学生信息管理数据库student中，为教师表teachers中的学历tdegree字段创建一个默认约束，其默认值为本科。程序清单如下：ALTER TABLE teachers ADD CONSTRAINT df_xueli DEFAULT 本科 FOR tdegree,55,创建默认约束,DEFAULT约束创建时将检查表中的现存数据。DEFAULT约束只对INSERT语句有效。每列只能定义一个DEFAULT约束。不能和“标识”属性共同使用。为具有PRIMARY KEY或UNIQUE约束的列指定默认值是没有意义的。常量值外面可以加或

24、者不加括号，字符或者日期常量必须加上单引号或双引号。,56,3.6.5 创建外键约束,使用SQL语句创建外键约束的语法格式为：ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY(column_name,)REFERENCES ref_table(ref_column_name,),57,3.6.5 创建外键约束,示范案例6 使用T-SQL语句在学生信息管理数据库student中，为学生基本信息表stud_info中的“所属班级”字段创建一个外键约束，引用班级表class的班级代码class_id字段,从而保证输入有

25、效的班级代码。程序清单如下：ALTER TABLE stud_info ADD CONSTRAINT fk_bjdm FOREIGN KEY(所属班级)REFERENCES class(class_id),58,3.6.5 创建外键约束,提供了单列或多列的引用完整性。FOREIGN KEY子句中指定的列的个数和数据类型必须和REFERENCES子句中指定的列的个数和数据类型匹配。修改数据的时候，用户必须在被FOREIGN KEY约束引用的表上具有SELECT或REFERENCES权限。若引用的是同表中的列，那么可只用REFERENCES子句而省略FOREIGN KEY子句。,59,总结：使用约

26、束的注意事项,SQL Server 里的约束只是“最后防线”当给一个表添加约束的时候，SQL Server 将检查现有数据是否违反约束。建议创建约束的时候指定名称，否则系统将为约束自动产生一个复杂的名称。名称必须惟一，且符合 SQL Server 标识符的规则。,60,3.7 查看约束的定义,1.使用系统存储过程查看约束信息 2.使用SSMS查看约束信息,61,1.使用系统存储过程查看约束信息 1）系统存储过程sp_help用来查看约束的名称、创建者、类型和创建时间，其语法格式为：EXEC sp_help 约束名称 2）如果约束存在文本信息，可以使用sp_helptext来查看，其语法格式为：

27、EXEC sp_helptext 约束名称 例：使用系统存储过程查看学生表上的约束 ck_csrq的文本信息。其程序清单如下：EXEC sp_helptext ck_csrq,3.7 查看约束的定义,62,2.使用SSMS查看约束信息的步骤为：1）在SSMS中，选择要查看约束的表（如学生表），打开表设计器。2）在表设计器中可以查看主键约束、空值约束和默认值约束。3）在表设计器中，右击鼠标，从弹出的快捷菜单中选择“属性”命令。弹出“属性”对话框 4）在“属性”对话框中通过切换选项卡，可以查看主键约束、外键约束与CHECK约束信息。,3.7 查看约束的定义,63,3.8 删除约束,1.使用SSMS

28、删除表约束 2.使用DROP命令删除表约束,64,3.8 删除约束,1.使用SSMS删除表约束 在表设计器的窗口中，可以删除主键，去掉默认值。在表设计器的“属性”窗口中，可以通过切换选项卡，选择约束名称，单击“删除”按钮，分别删除主键约束、外键约束和CHECK约束。,65,3.8 删除约束,66,2.使用DROP命令删除表约束 在T-SQL语言中，也可以方便的删除一个或多个约束，其语法格式为：ALTER TABLE table_name DROP CONSTRAINT constraint_name,n,3.8 删除约束,67,示范案例7 使用T-SQL语句删除Northwind数据库中CK_

29、Quantity约束。程序清单如下：ALTER TABLE order details DROP CONSTRAINT ck_quantity,3.8删除约束,68,决定使用何种约束,69,规则与CHECK约束的比较1、check约束比规则更简明，它可以在建表时由create table语句将其作为表的一部分进行指定。2、规则需要单独创建，然后绑定到列上。3、在一个列上只能应用一个规则，但是却可以应用到多个check约束。4、规则的优点是：一个规则只需要定义一次就已可以多次应用，可以应用到多个表或多个列，还可以应用到用户定义的数据类型上。,3.9 使用规则,70,3.9 使用规则,1.创建规则

30、的命令是CREATE RULE，语法格式为：CREATE RULE rule_name AS condition_expression 2.绑定规则其语法格式为:EXECUTE sp_bindrule 规则名称,表名.字段名|自定义数据类型名,71,3.9 使用规则,示范案例8 在Northwind数据库中创建规则,名称为region_rule，条件表达式是要求变量在(wa,ia,il,ks,mo)范围之内。程序清单如下：CREATE RULE region_rule AS region IN(wa,ia,il,ks,mo),72,3.9 使用规则,示范案例9 将规则“region_rule”

31、绑定到员工“employees”表中的区域“region”字段。程序清单如下：EXEC sp_bindrule region_rule,employees.region,73,3.解绑规则其语法格式为：EXECUTE sp_unbindrule 表名.字段名|自定义数据类型名4.删除规则其语法格式为：DROP RULE 规则名称,n,3.9 使用规则,74,3.9 使用规则,示范案例10 将规则“region_rule”从“employees”表中“region”列解除绑定。程序清单如下：EXEC sp_unbindrule employees.region,75,3.9 使用规则,示范案例1

32、1 将规则“region_rule”删除。程序清单如下：DROP rule region_rule,76,1.创建默认的语法格式如下：CREATE DEFAULT default_name AS default_description 2.绑定默认值其语法格式为：EXECUTE sp_bindefault 默认名称,表名.字段名|自定义数据类型名,3.10 使用默认,77,3.10 使用默认,示范案例12 在“Northwind”数据库中创建默认，名称为phone_default,将其绑定到“customers”表的“phone”字段，使其默认值为“（000）000-0000”。程序清单如下：

33、CREATE DEFAULT phone_default AS(000)000-0000 GO EXEC sp_bindefault phone_default,customers.phone,78,3.10 使用默认,3.解绑默认值其语法格式为：EXECUTE sp_unbindefault 表名.字段名|自定义数据类型名 4.删除默认值其语法格式如下：DROP DEFAULT default_name,n,79,3.10 使用默认,示范案例13 在“Northwind”数据库中将默认“phone_default”从“customers”表中“phone”列解除绑定，并将其删除。程序清单如下：EXEC SP_UNBINDEFAULT customers.phone GO DROP DEFAULT phone_default GO,