第五章SQL-server2005数据表的创建与操作

1、1 第五章第五章SQL server2005数据表的创建与操作数据表的创建与操作 学习要求：学习要求： 掌握数据表的创建、修改、删除方法；掌掌握数据表的创建、修改、删除方法；掌握数据表中数据的插入、修改、删除方法。握数据表中数据的插入、修改、删除方法。2 本章内容本章内容 5.1 数据表的设计与创建数据表的设计与创建 5.2 定义约束定义约束 5.3 默认值和规则默认值和规则 第五章第五章 数据表的创建与操作数据表的创建与操作3 本章学习任务本章学习任务q 数据表的设计和创建数据表的设计和创建q 数据表设计与创建数据表设计与创建q 修改表结构修改表结构q 插入、更新和删除表插入、更新和删除表q

2、 数据定义约束数据定义约束q约束的类型约束的类型q 约束的创建、查看、删除默认值和规则约束的创建、查看、删除默认值和规则 默认值的使用与规则的使用默认值的使用与规则的使用4 数据表与电子表格类似，数据在表中是按照数据表与电子表格类似，数据在表中是按照行和列的格式来组织排列的。每行代表一唯一的行和列的格式来组织排列的。每行代表一唯一的记录，每列代表记录中的一个域。数据表由若干记录，每列代表记录中的一个域。数据表由若干个列组成，每一个列的列名在表中应该是唯一的；个列组成，每一个列的列名在表中应该是唯一的；其列值为其列值为SQL Server可选的数据类型；大小则规可选的数据类型；大小则规定列值的最

3、大长度；小数位用于定义数值型的小定列值的最大长度；小数位用于定义数值型的小数位数；是否为空说明列值是否允许为空值；默数位数；是否为空说明列值是否允许为空值；默认值用于定义不经输入所确定的列值。认值用于定义不经输入所确定的列值。 如学生基本信息的数据表，表中的每一行代表如学生基本信息的数据表，表中的每一行代表一个学生，每一列分别表示学生的详细资料，如一个学生，每一列分别表示学生的详细资料，如学号、姓名等。学号、姓名等。 5一、设计、创建数据表一、设计、创建数据表1、student学生管理数据库的表结构学生管理数据库的表结构 该数据库中有以下基本表：该数据库中有以下基本表： 学生基本信息表（学生基

4、本信息表（Stud_info） 教师基本信息表（教师基本信息表（teacher_info） 学生成绩表（学生成绩表（Stud_grade） 课程信息表（课程信息表（Lesson_info） 课程安排表（课程安排表（Teach_schedule） 院系代码表（院系代码表（Dept_code） 专业代码表（专业代码表（Specially_code）6学生基本信息学生基本信息 (stud_info)表表结构结构列名列名数据类型数据类型大小大小小数位小数位是否为空是否为空默认值默认值列名含义列名含义1STUD_IDchar10N0401010101学号学号2NAMEnvarchar4N姓名姓名3BIR

5、THDAYdatetime出生日期出生日期4GENDERnchar1性别性别5ADDRESSnvarchar20家庭地址家庭地址6TELCODEchar12电话电话7ZIPCODEchar6邮编邮编8MARKdecimal30560入学成绩入学成绩72、student学生管理数据库的表数学生管理数据库的表数据据STUD_IDNAMEBIRTHDAYGENDERADDRESSTELCODEZIPCODEMARK0401010811张源张源12-05-1986男男北京市海淀区北京市海淀区010-645723451000805600401020201赵明赵明08-06-1986男男上海市浦东区上海市

6、浦东区021-648972322017005600401030101王刚王刚01-02-1986男男天津市南开区天津市南开区022-835723363000005600401050201陈红陈红10-25-1986女女武汉市汉口区武汉市汉口区027-745653504300005600401040112孙强孙强06-07-1986男男重庆市沙坪坝重庆市沙坪坝023-823423484000005600401020103李伟李伟09-01-1986男男北京市大兴县北京市大兴县010-689029471026005600401010634钱昆钱昆12-06-1986男男广州市海珠区广州市海珠区02

7、0-456724535100005600401030213郑芳郑芳08-09-1986女女江苏省南京市江苏省南京市025-845739412100005600401010102袁飞袁飞03-11-1986男男湖南省长沙县湖南省长沙县0731-45723454100005600401040123孔荣孔荣05-31-1986男男云南省昆明市云南省昆明市0871-26475356500006000401050127张军张军11-03-1987男男成都市和平区成都市和平区028-852495676100415700401010704王一明王一明03-03-1986男男甘肃省兰州市甘肃省兰州市0621-

8、2221837590000573学生基本信息表数据（学生基本信息表数据（stud_info）83、创建数据表的实现、创建数据表的实现一般步骤为：一般步骤为：（1）定义表结构：给表的每一列取名，并确定每）定义表结构：给表的每一列取名，并确定每一列的数据类型、数据长度、列数据是否可以一列的数据类型、数据长度、列数据是否可以为空等；为空等；（2）设置约束：限制某列数据的取值范围）设置约束：限制某列数据的取值范围（3）向表中输入数据。）向表中输入数据。创建方法：创建方法：（1）使用对象资源管理器创建）使用对象资源管理器创建（2）使用）使用T-SQL语句创建语句创建9（1）使用对象资源管理器创建）使用对

9、象资源管理器创建例例1：创建学生基本信息表：创建学生基本信息表stud_info。 右键单击右键单击“student”数据库中数据库中“表表”选项，选择选项，选择“新建新建表表”，在打开的表结构中输入列名、数据类型、是否为空等内容在打开的表结构中输入列名、数据类型、是否为空等内容。输入完毕后，单击工具栏上的。输入完毕后，单击工具栏上的“保存保存”按钮，输入表的按钮，输入表的名名称称“stud_info”。10语法格式：语法格式：CREATE TABLE database_name. owner .| owner. table_name( | column_name AS computed_co

10、lumn_expression| := CONSTRAINT constraint_name | PRIMARY KEY | UNIQUE ,.n ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT := column_name data_type COLLATE 11主要参数主要参数参数参数说明说明database_name要在其中创建表的数据库名称。要在其中创建表的数据库名称。database_name必须是现有数必须是现有数据库的名称。如果不指定数据库，据库的名称。如果不指定数据库，database_name默认为当前默认为当前

11、数据库。数据库。table_name新建表的名称。表名必须符合标识符规则。新建表的名称。表名必须符合标识符规则。column_name表中的列名。列名必须符合标识符规则，并且在表内唯一。表中的列名。列名必须符合标识符规则，并且在表内唯一。computed_column_expression定义计算列值的表达式。也就是说，设计表时，表中的某些定义计算列值的表达式。也就是说，设计表时，表中的某些列的数值可由同一表中的其它列通过定义的公式计算得到。列的数值可由同一表中的其它列通过定义的公式计算得到。例如，平均分例如，平均分=总分总分/n。ON filegroup | DEFAULT指定存储表的文件组

12、。如果指定指定存储表的文件组。如果指定filegroup，则表将存储在指定，则表将存储在指定的文件组中。数据库中必须存在该文件组。如果指定的文件组中。数据库中必须存在该文件组。如果指定DEFAULT，或者根本未指定，或者根本未指定ON参数，则表存储在默认文件参数，则表存储在默认文件组中。组中。TEXTIMAGE_ON表示表示text、ntext和和image列存储在指定文件组中的关键字。如列存储在指定文件组中的关键字。如果表中没有果表中没有text、ntext或或image列，则不能使用列，则不能使用TEXTIMAGE ON。如果没有指定。如果没有指定TEXTIMAGE_ON，则，则text、

13、ntext和和image列将与表存储在同一文件组中。列将与表存储在同一文件组中。data_type指定列的数据类型。可以是系统数据类型或用户定义数据类指定列的数据类型。可以是系统数据类型或用户定义数据类型。型。12use studentgocreate table stud_info (stud_id char(10) not null, name nvarchar(4) not null, birthday datetime, gender nchar(1), address nvarchar(20), telcode char(12), zipcode char(6), mark deci

14、mal(3,0) );13 数据表创建以后，在使用过程中可能需要对原数据表创建以后，在使用过程中可能需要对原先定义的表的结构进行修改。修改表的结构也可先定义的表的结构进行修改。修改表的结构也可以通过对象资源管理器和以通过对象资源管理器和T-SQL语句两种方法进语句两种方法进行。对表结构的修改包括：更改表名、增加列、行。对表结构的修改包括：更改表名、增加列、删除列、修改已有列的属性等。删除列、修改已有列的属性等。141、使用对象资源管理器修改表、使用对象资源管理器修改表（1）修改表名（建议一般不修改）修改表名（建议一般不修改）（2）增加列）增加列 右击右击“stud_info”表，选择表，选择“

15、修改修改”，对表，对表结构进行修改即可。结构进行修改即可。（3）删除列）删除列 打开打开“sutd_info”表结构窗口，右击要删除表结构窗口，右击要删除的列，选择的列，选择“删除删除”。15ALTER TABLE table_name ALTER COLUMN column_name new_data_type ( precision , scale ) COLLATE NULL | NOT NULL| ADD ,.n | DROP CONSTRAINT constraint_name | COLUMN column_name ,.n 16参数参数说明说明table_name希望修改结构的表

16、名称希望修改结构的表名称ALTER/ADD/DROP修改、增加、删除现存表中的一个列或约束修改、增加、删除现存表中的一个列或约束new_data_type要修改列的新数据类型，要修改列的新数据类型，precision指定精度，指定精度，scale指定小数位数指定小数位数COLLATE collation_name为更改列指定新的排序规则为更改列指定新的排序规则COLUMN column_name希望修改结构中的列名希望修改结构中的列名CONSTRAINT constraint_name希望修改结构中的列约束名。希望修改结构中的列约束名。17q 用用ALTER TABLE的的ADD子句向表中添加

17、列子句向表中添加列ALTER TABLE table_nameADD column_namenew_data_type (precision ,scale) NULL | NOT NULLq 用用ALTER TABLE的的DROP COLUMN删除列删除列ALTER TABLE table_nameDROP COLUMN column_nameq使用使用DROP语句删除数据表语句删除数据表 DROP TABLE table_name 18例例3 利用利用SQL语句在语句在stud_info中增加一列中增加一列“身份证号身份证号”，数据类型为，数据类型为varChar，允，允许为空值。许为空值。

18、 use student Go Alter table student_info Add 身份证号身份证号 varchar(18) null Go 19例例4 删除表删除表stud_info中的中的“身份证号身份证号”列。列。Use studentGoAlter Table stud_infoDrop Column 身份证号身份证号Go20例例5 利用利用SQL语句删除语句删除stud_info表表211 1、数据库中表的查看、数据库中表的查看q使用对象资源管理器查看表结构使用对象资源管理器查看表结构在在“对象资源管理器对象资源管理器”中，右击需要查看结构的中，右击需要查看结构的表，在弹出的快

19、捷菜单中选择表，在弹出的快捷菜单中选择“修改修改”命令，打命令，打开数据表窗口，即可查看数据表结构信息。开数据表窗口，即可查看数据表结构信息。q 使用对象资源管理器查看表中的数据使用对象资源管理器查看表中的数据在在“对象资源管理器对象资源管理器”中，右击需要查看数据的中，右击需要查看数据的表，弹出快捷菜单，单击表，弹出快捷菜单，单击“打开表打开表”命令，用户命令，用户即可根据表格线和使用滚动条来查看到表中的数即可根据表格线和使用滚动条来查看到表中的数据。据。q 使用使用SELECT语句查看表中的数据语句查看表中的数据22 向数据表中插入数据，就是将一条或多条记向数据表中插入数据，就是将一条或多

20、条记录添加到数据表中。录添加到数据表中。T-SQL中使用中使用INSERT命令命令完成数据插入。完成数据插入。 INSERT INTO table_name(column1,column2)values(value1,value2)参数参数说明说明table_name指定插入数据的表名指定插入数据的表名column1,column2,将要插入数据的列名将要插入数据的列名value1,value2,插入的列值插入的列值23例例6 在表在表stud_info中插入一条记录（中插入一条记录（040104125，傅，傅冰，男，甘肃省天水市，冰，男，甘肃省天水市,564000

21、,627）24UPDATE语句可以更新表中现存记录中的数据语句可以更新表中现存记录中的数据UPDATE table_nameSET column1=modified_value1,column2=modified_values,WHERE column1=value1,column2=value2参数参数说明说明table_name指定要更新数据的表名指定要更新数据的表名SET column1=modified_value1,column2=modified_values,指定要更新的列及该列改变后的值指定要更新的列及该列改变后的值WHERE column1=value1,column2=va

22、lue2指定被更新的记录所应满足的条件指定被更新的记录所应满足的条件25例例7 把表把表stud_info中添加的数据进行修改中添加的数据进行修改26使用使用DELETE语句可以从表中删除一行或多行记语句可以从表中删除一行或多行记录。录。DELETE FROM table_nameWHERE column1=value1,column2=value2其中：其中：WHERE子句用来指定删除行的条件。子句用来指定删除行的条件。例例8 删除数据表删除数据表stud_info 中性别为男的学生记录。中性别为男的学生记录。Delete from stud_info where gender=男男27一、

23、数据完整性一、数据完整性 存储在数据库中数据的一致性和正确性。存储在数据库中数据的一致性和正确性。 为了保证数据完整性，为了保证数据完整性，SQL Server提供了定义、提供了定义、检查和控制数据完整性的机制。检查和控制数据完整性的机制。q 表列中除了具有数据类型和大小属性之外，还表列中除了具有数据类型和大小属性之外，还有其他属性。其他属性是保证数据库中数据完整有其他属性。其他属性是保证数据库中数据完整性和表的引用完整性的重要部分。性和表的引用完整性的重要部分。q 根据数据完整性措施所作用的数据库对象和范根据数据完整性措施所作用的数据库对象和范围不同，数据完整性分为：围不同，数据完整性分为：

24、实体完整性、参照完实体完整性、参照完整性、整性、域完整性和域完整性和用户定义完整性用户定义完整性四种四种。281、 CHECK约束约束 限制输入到一列或多列的值的范围，即一个限制输入到一列或多列的值的范围，即一个列的输入内容必须满足列的输入内容必须满足CHECK约束的条件，否则约束的条件，否则数据无法输入。数据无法输入。2、DEFAULT约束约束 如果没有为列指定数据，系统将默认值赋给列。如果没有为列指定数据，系统将默认值赋给列。3、PRIMARY KEY约束约束 如果某列或多列的值能惟一标识表中的每一行，如果某列或多列的值能惟一标识表中的每一行，这样的列称为表的主键，通过它可以强制表的实这样

25、的列称为表的主键，通过它可以强制表的实体完整性。体完整性。 该约束把某列设为主键。该约束把某列设为主键。二、约束的类型二、约束的类型294、FOREIGN KEY约束约束 外键外键(Foreign Key)是用于建立和加强两个表是用于建立和加强两个表(主表与从表主表与从表)的一列或多列数据之间的链接，当数的一列或多列数据之间的链接，当数据添加、修改或删除时，通过参照完整性保证它据添加、修改或删除时，通过参照完整性保证它们之间数据的一致性。们之间数据的一致性。 定义表之间的参照完整性是先定义主表的主键，定义表之间的参照完整性是先定义主表的主键，再对从表定义外键约束再对从表定义外键约束 。5、UN

26、IQUE约束约束 确保表中的两个数据行在非主键列中没有相确保表中的两个数据行在非主键列中没有相同的列值。同的列值。约束的类型约束的类型301、 CHECK约束的创建、查看和删除约束的创建、查看和删除 在数据库在数据库student中的表中的表stud_info中定义学生中定义学生性别列性别列(GENDER)只能是只能是“男男”或或“女女”，避免，避免用户输入其它的值，需要用到用户输入其它的值，需要用到CHECK约束，使约束，使得学生性别列的值只有得学生性别列的值只有“男男”、“女女”两种可能，两种可能，如果用户输入其它值，系统均提示用户输入无效。如果用户输入其它值，系统均提示用户输入无效。（1

27、）在对象资源管理器中创建删除）在对象资源管理器中创建删除CHECK约束约束三、约束的创建、查看、删除三、约束的创建、查看、删除31（2）使用）使用T-SQL语句创建和删除语句创建和删除CHECK约束约束ALTER TABLE table_nameADD CONSTRAINT constraint_name CHECK (logical_expression)DROP CONSTRAINT constraint_name 32例例9 创建和删除创建和删除stud_info表的表的CHECK约束约束332、创建和删除、创建和删除DEFAULT约束约束ALTER TABLE table_nameAD

28、D CONSTRAINT constraint_name DEFAULT constraint_expressionDROP CONSTRAINT constraint_name（1）对象资源管理器中定义）对象资源管理器中定义约束的创建、查看、删除约束的创建、查看、删除34353、创建和删除、创建和删除PRIMARY KEY约束约束ALTER TABLE table_nameADD CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED constraint_expression DROP CONSTRAINT constra

29、int_name约束的创建、查看、删除约束的创建、查看、删除36374、建立表之间的关系、建立表之间的关系 表和表之间的引用关系可以通过外键约束来实表和表之间的引用关系可以通过外键约束来实现。创建外键约束既可以由现。创建外键约束既可以由FOREIGN KEY子句子句完成，也可以在表设计器中完成。完成，也可以在表设计器中完成。约束的创建、查看、删除约束的创建、查看、删除3839（2）SQL语句创建和删除语句创建和删除FOREIGN KEY约束约束 TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY REFERENCES refe

30、renced_table_name (ref_column) DROP CONSTRAINT FOREIGN KEY constraint_name40例例11 为学生成绩表为学生成绩表stud_grade和课程信息表和课程信息表Lesson_info创建外键约束。创建外键约束。首先为首先为lesson_info表中的表中的course_id列建立名为列建立名为course_id的主键约束。的主键约束。Alter table lesson_infoAdd constraint PK_course_id primary key clusterd(course_id)再为再为stud_grade表

31、中的表中的course_i列建立名为列建立名为FK_course_id的外键约束。的外键约束。Alter table stud_gradeAdd constraint FK_course_id foreign key (course_id) references lesson_info(course_id)41q 创建和删除创建和删除UNIQUE约束约束ALTER TABLE table_nameADD CONSTRAINT constraint_name UNIQUE constraint_expressionCLUSTERED|NONCLUSTEREDALTER TABLE table_n

32、ameDROP CONSTRAINT UNIQUE constraint_name425、UNIQUE约束的创建、查看和删除约束的创建、查看和删除 UNIQUE约束可以确保在非主键列中不输约束可以确保在非主键列中不输入重复的值。可以对一个表定义多个入重复的值。可以对一个表定义多个UNIQUE约束，但只能定义一个约束，但只能定义一个PRIMARY KEY约束。约束。（1）使用对象资源管理器）使用对象资源管理器 43例例12 针对学生基本信息表的针对学生基本信息表的“电话号码电话号码”创建创建UNIQUE约束。约束。44 一、默认值一、默认值 默认值可以先定义好，需要时将它绑定到一列默认值可以先定

33、义好，需要时将它绑定到一列或多列上，在表中插入数据行时，系统自动为没或多列上，在表中插入数据行时，系统自动为没有指定数据的列提供事先定义的默认值。有指定数据的列提供事先定义的默认值。 与与DEFAULT约束不同的是，默认值是一种数约束不同的是，默认值是一种数据库对象，在数据库中只需定义一次后，就可以据库对象，在数据库中只需定义一次后，就可以被一次或多次应用于任意表中的一列或多列，还被一次或多次应用于任意表中的一列或多列，还可以用于用户自定义的数据类型可以用于用户自定义的数据类型。 45q 创建默认值和将其绑定到表上的某列创建默认值和将其绑定到表上的某列CREATE DEFAULT default_nameAS constraint_expression其中：其中