创建数据库表数据库原理.ppt

数据库原理与应用第10章 数据库表,学习目的,掌握利用企业管理器（Enterprise Manager）创建表；掌握利用Transact-SQL语句中的create命令创建表,重点&难点,重点利用Transact-SQL语句中的create命令创建表难点无,第10章 数据库表,数据库最重要的功能之一是存储数据。关系型数据库中，数据保存在表中。数据库逻辑结构设计后，得到了一系列的关系模式，进入数据库实施阶段，将把关系模式转换为DBMS的数据库表。数据库表是数据的最基本构成元素，对数据库的操作大多基于数据库表。,第10章 数据库表,10.1创建数据库表10.2 修改数据库表10.3 使用约束10.4 建立索引10.5 删除数据库表,10.1 创建数据库表,创建表创建表：就是将逻辑设计阶段得到的关系模式用DBMS所支持的形式表达出来。每一个关系模式就是一张数据（库）表。可看作定义这张表的“表头”如何命名每一列及每一列的属性特点是什么。在SQL Server 2000中，每个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列，也就是可以定义1024个字段。,10.1 创建数据库表,SQL Server 2000提供了两种方法创建数据库表利用企业管理器（Enterprise Manager）创建表利用Transact-SQL语句中的create命令创建表,10.1 创建数据库表,利用企业管理器（Enterprise Manager）创建表步骤如下：（1）在树形目录中找到要建表的数据库，展开该数据库。（2）选择表，单击鼠标右键，在弹出的快捷菜单中选择“新建表”命令，表设计器如图所示。,10.1 创建数据库表,利用企业管理器（Enterprise Manager）创建表步骤如下：（1）在树形目录中找到要建表的数据库，展开该数据库。（2）选择表，单击鼠标右键，在弹出的快捷菜单中选择“新建表”命令，表设计器如图所示。（3）表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的每一行对应一列。对每一列都需要进行以下设置，其中前三项是必须在建表时给出的，它们是：列名：即字段名。数据类型：数据类型是一个下拉列表框，其中包括了所有的系统数据类型长度：如果选择的数据类型需要长度，则指定长度。允许空：单击鼠标，可以切换是否允许为空值的状态，勾选说明允许为空值，空白说明不允许为空值，默认状态下是允许为空值的。,10.1 创建数据库表,利用企业管理器（Enterprise Manager）创建表步骤如下：（3）表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的每一行对应一列。对每一列都需要进行以下设置，其中前三项必须在建表时给出，它们是：列名：即字段名。数据类型：数据类型是一个下拉列表框，其中包括了所有的系统数据类型长度：如果选择的数据类型需要长度，则指定长度。允许空：单击鼠标，可以切换是否允许为空值的状态，勾选说明允许为空值，空白说明不允许为空值，默认状态下是允许为空值的。,10.1 创建数据库表,利用企业管理器（Enterprise Manager）创建表步骤如下：（3）表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的每一行对应一列。表设计器的下半部分是特定列的详细属性，包括是否是标识列、是否使用默认值等。（4）定义好所有列后，单击工具栏上的保存按扭，表就创建完成了。,10.1 创建数据库表,利用Transact-SQL语句中的create命令创建表其语法形式如下：CREATE TABLE 表名（列名1 数据类型 identity|not null|null,列名2 数据类型 identity|not null|null,）注意：在创建表之前，要先使用use命令打开需要创建表的数据库,10.1 创建数据库表,三个参数的含义：null：表示该列的值可以为空值，即没有存储任何数据，此为默认参数（不能将空值理解为该列的值是0或空字符串等值）not null：表示该列的值不能为空。identify：计数器，表示该列的值是一组递增的整数数据。初始值默认为1，增长步长默认为1。也可自己指定初始值和增长步长。（课本 表10-1）,10.1 创建数据库表,例：在Company数据库中创建一个雇员信息表该表记录了雇员的号码、姓名、性别、出生日期、雇佣日期、职工种类、工资信息。其中：职工号码：整型，不能为空 姓名：可变长度字符型，允许为空 出生日期：日期型，允许为空 雇佣日期：日期型，不能为空 职工种类：可变长度字符型，允许为空 工资：货币型，允许为空,10.1 创建数据库表,其SQL语句的程序清单如下：use CompanygoCREATE TABLE employee(number int not null,name varchar(10)NULL,birthday datetime null,hire_date datetime NOT NULL,professional_title varchar(10)null,salary money null),10.1 创建数据库表,表名在同一数据库内，数据库表的名称是惟一的 可以使用中文，也可以使用西文符号最多64个字符（一个汉字占两个字符）建立临时表 临时表的表名必须以#打头 仍然使用CREATE TABLE命令例:Create table#demotable(user_id numeric(10,0)identity,Name varchar(30)not NULL),10.1 创建数据库表,例10-1 确定期刊采编系统数据库中各数据表的表名,10.1 创建数据库表,列名表中的每一列也称为字段，所以列名也叫字段名字段名的命名方式基本同数据表名的命名字段名在数据表中要求是惟一的，但在整个数据库中可以重名,10.1 创建数据库表,数据类型定义数据表中的每一个字段必须声明其数据类型，DBMS才能实现对数据的存储管理。为每一个字段选择合适的数据类型和数据长度将直接影响着数据库系统存储空间的利用和系统的性能。在建立数据库表时，要慎重决定每个字段的数据类型。,10.1 创建数据库表,数据类型T-SQL中支持的、常用的数据类型 字符型 日期时间类型 数值类型 二进制型 位型 文本型 图像型 时间戳型,10.1 创建数据库表,数据类型（1）字符型 字符型变量应定义长度允许定义的最大长度是255 有两种固定长度的char()类型可变长度的varchar()类型 输入字符型数据时必须用单引号将数据括起来,10.1 创建数据库表,数据类型（2）日期时间型datetime和smalldatetime是用来存储日期和时间数据,10.1 创建数据库表,数据类型（3）整数类型多用于处理数值类型数据,10.1 创建数据库表,数据类型（4）浮点数类型多用于处理取值范围很大的数字量，使用时有一定的精确度,10.1 创建数据库表,数据类型（5）精确数类型精确数类型有两种形式DecimalNumeric 与浮点类型数据的区别 可自定义精度的位数例 num_col(5,2)可以表示的最大值是999.99,10.1 创建数据库表,数据类型（6）货币型￥、,10.1 创建数据库表,数据类型（7）位数据类型位数据类型bit是一种逻辑数据类型 只有1和0两种数值一般常用作true/false使用 定义bit列时，不允许为NULL，也不能建立索引 多个bit列可占用一个字节,10.1 创建数据库表,数据类型（8）文本型和图像型文本类型text 图像类型image 每行可以存储2GB的二进制大型对象,10.1 创建数据库表,数据类型（9）时间戳型时间戳型表示Timestamp由系统自动赋值的一个计数器数据 记录了数据行的操作顺序 时间戳列的数据保持惟一，它可以惟一标识表中的列 时间戳列的值实际来自于事务日志,10.1 创建数据库表,例10-2 确定期刊采编系统数据库中各数据表中字段的字段名和数据类型,数据表Mag_dept,10.1 创建数据库表,例10-2 确定期刊采编系统数据库中各数据表中字段的字段名和数据类型,数据表mag_emp,10.1 创建数据库表,例10-2 确定期刊采编系统数据库中各数据表中字段的字段名和数据类型,数据表Mag_info,10.1 创建数据库表,例10-2 确定期刊采编系统数据库中各数据表中字段的字段名和数据类型,数据表mag_doc,10.1 创建数据库表,例10-3 建立期刊采编系统数据库中各数据表,创建mag_dept表,10.1 创建数据库表,例10-3 建立期刊采编系统数据库中各数据表,创建mag_emp表,10.1 创建数据库表,例10-3 建立期刊采编系统数据库中各数据表,创建mag_info表,10.1 创建数据库表,例10-3 建立期刊采编系统数据库中各数据表,创建mag_doc表,10.1 创建数据库表,用T-SQL语句查看表定义 1.使用系统存储过程sp_help可以查看表的定义，在查询分 析器中输入 sp_help employee命令，可以查看刚刚创建 的employee表的结构。,补充：,10.1 创建数据库表,2.使用系统存储过程sp_helpdb格式如下：sp_helpdb 数据库名1）不给出参数，将显示服务器中所有数据库的信息，如图所示。,补充：,10.1 创建数据库表,使用系统存储过程sp_helpdb,10.1 创建数据库表,使用系统存储过程sp_helpdb）给出某个数据库名时，只显示这个数据库的信息如图：,10.1 创建数据库表,10.1 创建数据库表,3.使用系统存储过程sp_databases格式如下：sp_databases显示服务器中所有可以使用的数据库的信息，如图所示。注意：sp_helpdb也是显示服务器中所有数据库的信息，两者之间的差别在哪？,补充：,10.1 创建数据库表,10.1 创建数据库表,4.使用系统存储过程sp_helpfile格式如下：sp_helpfile 文件名查看数据库中文件的信息。注意：要先使用use命令打开所要查看的数据库,补充：,10.1 创建数据库表,4.使用系统存储过程sp_helpfile（1）不加文件名，显示数据库中所有文件的信息，如图所示。,补充：,10.1 创建数据库表,10.1 创建数据库表,4.使用系统存储过程sp_helpfile（2）加文件名，显示数据库中某一文件的信息,补充：,10.1 创建数据库表,10.1 创建数据库表,5.使用系统存储过程sp_helpfilegroup格式如下：sp_helpfilegroup 文件组名不加文件组名，显示数据库中所有文件组的信息。用法同sp_helpfile。,补充：,第10章 数据库表,10.1 创建数据库表10.2 修改数据库表10.3 使用约束10.4 建立索引10.5 删除数据库表,10.2 修改数据库表,添加或删除列修改列的属性 更改列名和表名,10.2 修改数据库表,利用企业管理器增加、删除和修改字段。在企业管理器中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择设计表选项，则会出现设计表对话框，在该对话框中，可以利用图形化工具完成增加、删除和修改字段的操作。利用Transact-SQL语言中的alter table命令增加、删除和修改字段。,10.2 修改数据库表,增加列语句格式：ALTER TABLE表名ADD列名 列的描述例如：向Company数据库的employee表中添加性别（sex）列。use Companygoalter table employee add sex char(4)null,10.2 修改数据库表,增加列例10-4 向数据表mag_emp中添加一新属性ID（身份证号码），数据类型为字符型，固定长度18,10.2 修改数据库表,删除列语句格式：ALTER TABLE 表名DROP COLUMN 列名例如：删除 employee表的sex列。use Companygoalter table employee drop column sex,10.2 修改数据库表,删除列例10-5 删除数据表mag_emp中属性ID,10.2 修改数据库表,修改列定义表中的每一列都有其定义，包括列名、数据类型、数据长度以及是否允许为空值等，这些值都可以在表创建好以后修改。修改列定义的语句格式为：ALTER TABLE 表名ALTER COLUMN 列名 数据类型列的描述,10.2 修改数据库表,修改列定义例如：将employee表的name列改为最大长度为20的varchar型数据，且不允许空值。use Companygoalter table employeealter column name varchar(20)not null默认状态下，列是被设置为允许空值的，将一个原来允许空值的列改为不允许空值，必须在以下两个条件满足时才能成功：（1）列中没有存放是空值的记录（2）在列上没有创建索引。,10.2 修改数据库表,修改列定义例10-6 将数据表mag_info中PubDate的数据类型更改为smalldatetime,10.2 修改数据库表,更改列名和表名更改列名sp_rename 表名.列名，新列名更改表名sp_rename 原表名，新表名,10.2 修改数据库表,更改列名和表名例10-7 数据库usedb1中建有数据表test，包含number、stu_name和class三个字段，请将class更名为class_no,10.2 修改数据库表,更改列名和表名例10-8 将数据库usedb1中数据表test更名为student,第10章 数据库表,10.1 创建数据库表10.2 修改数据库表10.3 使用约束10.4 建立索引10.5 删除数据库表,10.3 使用约束,约束的作用实现数据的完整性实体完整性参照完整性用户自定义完整性,10.3 使用约束,约束的类型,10.3 使用约束,建立约束定义约束时使用Create table语句或使用Alter table语句即可以在定义数据表的数据列时直接定义约束，也可以对已定义的数据表添加约束,10.3 使用约束,建立约束使用Create table语句格式：Create table 表名（列名1 数据类型 identity|NOT NULL|NULL Constraint 约束名 约束定义,列名2 数据类型 identity|NOT NULL|NULL Constraint 约束名 约束定义,Constraint 约束名 约束定义）建立约束时看随数据定义或者单独定义,10.3 使用约束,建立约束使用Alter table语句格式：Alter table 表名 Add Constraint 约束名 约束定义约束名是一个可选项，如果用户自己不定义约束名，则系统将按照一定规则命名该约束。不命名约束名时，关键字constraint也要省略。,10.3 使用约束,1.primary key主键的添加、删除和修改操作方法有两种：(1)企业管理器操作法，在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择设计表选项，然后根据提示操作。如图,10.3 使用约束,10.3 使用约束,1.primary key主键的添加、删除和修改操作方法有两种：(2)Transact-SQL语句操作法其语法形式如下：CONSTRAINT constraint_name PRIMARY KEY(列名1,列名2,)当主键由多个列构成时,则将列名依次写在括号中,10.3 使用约束,1.primary key主键的添加、删除和修改操作方法有两种：(2)Transact-SQL语句操作法例10-9 向数据表map_dept中添加主键约束,10.3 使用约束,注意：此例没有定义约束名，则系统会按照一定规则命名该约束。查看约束名方法：如图,10.3 使用约束,1.primary key使用create table建立主键约束时两种写法（1）定义列名和数据类型后，直接写上关键字primary key（2）定义完所有列名和数据类型后，单独写明primary key（列名1，列名2当主键有两个或两个以上属性共同构成时，必须用第二种方法，即单独定义，而不能随列的定义而定义。,10.3 使用约束,（1）定义列名和数据类型后，直接写上关键字primary key例10-10 在数据库usedb2中建立数据表map_dept，同时定义主键约束,10.3 使用约束,（2）定义完所有列名和数据类型后，单独写明primary key（列名1，列名2例10-11 在数据库usedb1中建立数据表grade，表中包括学号sno（char）、课程号cno（char）、成绩gmark（numeric）属性.主键由学号和课程号构成,10.3 使用约束,2.unique唯一性约束用于指定一个或者多个列的组合的值具有唯一性，以防止在列中输入重复的值。当使用唯一性约束时，需要考虑以下几个因素：主键自动具有unique的特性一个表中可以允许有多个唯一性约束。可以把唯一性约束定义在多个字段上。唯一性约束用于强制在指定字段上创建一个唯一性 索引。,10.3 使用约束,2.unique（1）企业管理器操作法,10.3 使用约束,2.unique（2）Transact-SQL语句操作法CONSTRAINT 约束名 UNIQUE(列名1,n)基本用法同主键约束,10.3 使用约束,2.unique例10-12 向数据库usedb2中数据表mag_dept属性DepManager增加UNIQUE约束,10.3 使用约束,3.foreign key外部键约束用于强制参照完整性，提供单个字段或者多个字段的参照完整性。基本格式为:constraint 约束名 foreign key(列名1,列名2)references 表名(列名1,列名2),10.3 使用约束,3.foreign key注意:(1)使用create table命令建表时,若随着列的定义一同定义外键,则可以省略关键字foreign key及列名,但使用alter table命令增加外部键时不行.(2)定义外部键时要保证被定义为外键的属性在参照表中已经被定义为主键.,10.3 使用约束,3.foreign key例10-13 向数据库MagDb中数据表mag_emp的属性DepId添加外键约束,10.3 使用约束,3.foreign key例10-14 在数据库usedb2中建立数据表mag_emp，同时定义主键和外键,10.3 使用约束,4.check一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关。一个表中可以定义多个检查约束。每个CREATE TABLE语句中每个字段只能定义一个检查约束。在多个字段上定义检查约束，则必须将检查约束定义为表级约束。当执行INSERT语句或者UPDATE语句时，检查约束将验证数据。,10.3 使用约束,4.check(1)企业管理器操作法,10.3 使用约束,4.check(2)Transact-SQL语句操作法。其语法形式如下：CONSTRAINT 约束名 CHECK(逻辑表达式),10.3 使用约束,4.check例10-15 将数据库MagDb中数据表mag_emp的属性EmpAge取值范围限定在1-100之间（含1和100），并将此约束命名为ck_age,10.3 使用约束,4.check例子：创建一个学生信息表，其中输入性别字段值时，只能接受“F”或者“M”，并且为phonenum字段创建检查约束，限制只能输入类似80798654之类的数据，而不能随意输入其他数据。,create table student(id char(8),name char(8),sex char(2),phonenum int,constraint chk_sex check(sex in(F,M),constraint chk_phonenum check(phonenum like 0-90-90-90-90-90-90-90-9),10.3 使用约束,5.default用于指定某个属性的默认值。当数据表中的某一列具有大量相同数据时，为了减少数据录入的工作量，可以指定默认值，则该属性自动具有了数据。(1)企业管理器中,10.3 使用约束,5.default(2)T-SQL语句定义default约束基本格式:constraint 约束名default 常量表达式 for 字段名,10.3 使用约束,5.default例10-16 将数据库MagDb中数据表mag_emp的属性EmpRole的默认值指定为“编辑”,10.3 使用约束,5.default使用缺省约束时，应该注意以下几点：每个字段只能定义一个缺省约束。如果定义的缺省值长于其对应字段的允许长度，那么输入到表中的缺省值将被截断。不能加入到带有IDENTITY属性的字段上。,10.3 使用约束,删除约束语句格式:alter table 表名 drop constraint 约束名注意:定义约束的时候若省略了约束名,则要先查出系统自动命名的约束名然后进行删除.,10.3 使用约束,删除约束例10-17 删除数据库usedb2中数据表mag_dept属性DepManager的UNIQUE约束,第10章 数据库表,10.1 创建数据库表10.2 修改数据库表10.3 使用约束10.4 建立索引10.5 删除数据库表,10.4 建立索引,数据库中的索引与书籍中的索引类似。在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。,10.4 建立索引,索引的作用通过创建唯一索引，可以保证数据记录的唯一 性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。,10.4 建立索引,索引的类型聚集索引（clustered）是一种物理存储方式 一个数据表只能建立一个聚集索引 非聚集索引（nonclustered）是一种逻辑存储方式 索引的次序并不影响数据的物理存储顺序 一个数据表最多可以建立249个非聚集索引,10.4 建立索引,创建索引的方法SQL Server 2000创建索引的方法（1）用企业管理器直接创建索引。（2）利用Transact-SQL语句中的CREATE INDEX命令创建索引。,10.4 建立索引,用企业管理器直接创建索引。具体步骤如下：（）在企业管理器中，展开指定的服务器和数据库，选择要创建索引的表，用右键单击该表，从弹出的快捷菜单中选择所有任务项的管理索引选项,10.4 建立索引,用企业管理器直接创建索引。具体步骤如下：出现管理索引对话框，如图所示。在该对话框中，可以选择要处理的数据库和表,管理索引对话框,10.4 建立索引,用企业管理器直接创建索引。具体步骤如下：（）单击“新建”按钮，则出现新建索引对话框，如图所示。,(3)选择完成后单击“确定”按钮，即可生成新的索引；单击“取消”按钮，则取消新建索引的操作。,10.4 建立索引,利用Transact-SQL语句中的CREATE INDEX命令创建索引。建立索引的语法为:create unique clustered|nonclustered index 索引名 on 表名(列名1,列名2)unique:惟一索引默认情况下,创建的索引是非惟一的非聚集索引.必须先建立聚集索引,再建立非聚集索引.,10.4 建立索引,例10-18 对数据表mag_emp属性EmpAge建立非惟一的非聚集索引age_index,注意:(1)当数据表建立主键后,就自动建立惟一聚集索引(2)数据表使用unique约束后,可自动产生一个非聚集索引,10.4 建立索引,创建索引时一般应遵循以下原则 经常要查找的列经常要按顺序检索的列经常用于多个数据表连接的列经常用于进行统计计算（如求极值、求和等）的列；在查询条件中频繁使用的列如果一个列中只有几个不同的数据值，或者被索引的列多于20个字节时，通常不应该建立索引,10.4 建立索引,删除索引的语法 Drop index 表名.索引名 注意使用primary key约束和unique约束建立的索引不能删除,例10-19 删除索引age_index,第10章 数据库表,10.1 创建数据库表10.2 修改数据库表10.3 使用约束10.4 建立索引10.5 删除数据库表,10.5 删除数据库表,删除数据表的语法 Drop table 表名,例10-20 删除usedb2中数据表mag_dept,