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

数据库原理与应用第10章数据库表 学习目的 掌握利用企业管理器 EnterpriseManager 创建表 掌握利用Transact SQL语句中的create命令创建表 重点 难点 重点利用Transact SQL语句中的create命令创建表难点无 第10章数据库表 数据库最重要的功能之一是存储数据 关系型数据库中 数据保存在表中 数据库逻辑结构设计后 得到了一系列的关系模式 进入数据库实施阶段 将把关系模式转换为DBMS的数据库表 数据库表是数据的最基本构成元素 对数据库的操作大多基于数据库表 第10章数据库表 10 1创建数据库表10 2修改数据库表10 3使用约束10 4建立索引10 5删除数据库表 10 1创建数据库表 创建表创建表 就是将逻辑设计阶段得到的关系模式用DBMS所支持的形式表达出来 每一个关系模式就是一张数据 库 表 可看作定义这张表的 表头 如何命名每一列及每一列的属性特点是什么 在SQLServer2000中 每个数据库中最多可以创建200万个表 用户创建数据库表时 最多可以定义1024列 也就是可以定义1024个字段 10 1创建数据库表 SQLServer2000提供了两种方法创建数据库表利用企业管理器 EnterpriseManager 创建表利用Transact SQL语句中的create命令创建表 10 1创建数据库表 利用企业管理器 EnterpriseManager 创建表步骤如下 1 在树形目录中找到要建表的数据库 展开该数据库 2 选择表 单击鼠标右键 在弹出的快捷菜单中选择 新建表 命令 表设计器如图所示 10 1创建数据库表 利用企业管理器 EnterpriseManager 创建表步骤如下 1 在树形目录中找到要建表的数据库 展开该数据库 2 选择表 单击鼠标右键 在弹出的快捷菜单中选择 新建表 命令 表设计器如图所示 3 表设计器的上半部分有一个表格 在这个表格中输入列的属性 表格的每一行对应一列 对每一列都需要进行以下设置 其中前三项是必须在建表时给出的 它们是 列名 即字段名 数据类型 数据类型是一个下拉列表框 其中包括了所有的系统数据类型长度 如果选择的数据类型需要长度 则指定长度 允许空 单击鼠标 可以切换是否允许为空值的状态 勾选说明允许为空值 空白说明不允许为空值 默认状态下是允许为空值的 10 1创建数据库表 利用企业管理器 EnterpriseManager 创建表步骤如下 3 表设计器的上半部分有一个表格 在这个表格中输入列的属性 表格的每一行对应一列 对每一列都需要进行以下设置 其中前三项必须在建表时给出 它们是 列名 即字段名 数据类型 数据类型是一个下拉列表框 其中包括了所有的系统数据类型长度 如果选择的数据类型需要长度 则指定长度 允许空 单击鼠标 可以切换是否允许为空值的状态 勾选说明允许为空值 空白说明不允许为空值 默认状态下是允许为空值的 10 1创建数据库表 利用企业管理器 EnterpriseManager 创建表步骤如下 3 表设计器的上半部分有一个表格 在这个表格中输入列的属性 表格的每一行对应一列 表设计器的下半部分是特定列的详细属性 包括是否是标识列 是否使用默认值等 4 定义好所有列后 单击工具栏上的保存按扭 表就创建完成了 10 1创建数据库表 利用Transact SQL语句中的create命令创建表其语法形式如下 CREATETABLE表名 列名1数据类型 identity notnull null 列名2数据类型 identity notnull null 注意 在创建表之前 要先使用use命令打开需要创建表的数据库 10 1创建数据库表 三个参数的含义 null 表示该列的值可以为空值 即没有存储任何数据 此为默认参数 不能将空值理解为该列的值是0或空字符串等值 notnull 表示该列的值不能为空 identify 计数器 表示该列的值是一组递增的整数数据 初始值默认为1 增长步长默认为1 也可自己指定初始值和增长步长 课本表10 1 10 1创建数据库表 例 在Company数据库中创建一个雇员信息表该表记录了雇员的号码 姓名 性别 出生日期 雇佣日期 职工种类 工资信息 其中 职工号码 整型 不能为空姓名 可变长度字符型 允许为空出生日期 日期型 允许为空雇佣日期 日期型 不能为空职工种类 可变长度字符型 允许为空工资 货币型 允许为空 10 1创建数据库表 其SQL语句的程序清单如下 useCompanygoCREATETABLEemployee numberintnotnull namevarchar 10 NULL birthdaydatetimenull hire datedatetimeNOTNULL professional titlevarchar 10 null salarymoneynull 10 1创建数据库表 表名在同一数据库内 数据库表的名称是惟一的可以使用中文 也可以使用西文符号最多64个字符 一个汉字占两个字符 建立临时表临时表的表名必须以 打头仍然使用CREATETABLE命令例 Createtable demotable user idnumeric 10 0 identity Namevarchar 30 notNULL 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 helpemployee命令 可以查看刚刚创建的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语言中的altertable命令增加 删除和修改字段 10 2修改数据库表 增加列语句格式 ALTERTABLE表名ADD列名列的描述例如 向Company数据库的employee表中添加性别 sex 列 useCompanygoaltertableemployeeaddsexchar 4 null 10 2修改数据库表 增加列例10 4向数据表mag emp中添加一新属性ID 身份证号码 数据类型为字符型 固定长度18 10 2修改数据库表 删除列语句格式 ALTERTABLE表名DROPCOLUMN列名例如 删除employee表的sex列 useCompanygoaltertableemployeedropcolumnsex 10 2修改数据库表 删除列例10 5删除数据表mag emp中属性ID 10 2修改数据库表 修改列定义表中的每一列都有其定义 包括列名 数据类型 数据长度以及是否允许为空值等 这些值都可以在表创建好以后修改 修改列定义的语句格式为 ALTERTABLE表名ALTERCOLUMN列名数据类型列的描述 10 2修改数据库表 修改列定义例如 将employee表的name列改为最大长度为20的varchar型数据 且不允许空值 useCompanygoaltertableemployeealtercolumnnamevarchar 20 notnull默认状态下 列是被设置为允许空值的 将一个原来允许空值的列改为不允许空值 必须在以下两个条件满足时才能成功 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使用约束 建立约束定义约束时使用Createtable语句或使用Altertable语句即可以在定义数据表的数据列时直接定义约束 也可以对已定义的数据表添加约束 10 3使用约束 建立约束使用Createtable语句格式 Createtable表名 列名1数据类型 identity NOTNULL NULL Constraint约束名 约束定义 列名2数据类型 identity NOTNULL NULL Constraint约束名 约束定义 Constraint约束名 约束定义 建立约束时看随数据定义或者单独定义 10 3使用约束 建立约束使用Altertable语句格式 Altertable表名Add Constraint约束名 约束定义约束名是一个可选项 如果用户自己不定义约束名 则系统将按照一定规则命名该约束 不命名约束名时 关键字constraint也要省略 10 3使用约束 1 primarykey主键的添加 删除和修改操作方法有两种 1 企业管理器操作法 在企业管理器中 用右键单击要操作的数据库表 从弹出的快捷菜单中选择设计表选项 然后根据提示操作 如图 10 3使用约束 10 3使用约束 1 primarykey主键的添加 删除和修改操作方法有两种 2 Transact SQL语句操作法其语法形式如下 CONSTRAINTconstraint name PRIMARYKEY 列名1 列名2 当主键由多个列构成时 则将列名依次写在括号中 10 3使用约束 1 primarykey主键的添加 删除和修改操作方法有两种 2 Transact SQL语句操作法例10 9向数据表map dept中添加主键约束 10 3使用约束 注意 此例没有定义约束名 则系统会按照一定规则命名该约束 查看约束名方法 如图 10 3使用约束 1 primarykey使用createtable建立主键约束时两种写法 1 定义列名和数据类型后 直接写上关键字primarykey 2 定义完所有列名和数据类型后 单独写明primarykey 列名1 列名2 当主键有两个或两个以上属性共同构成时 必须用第二种方法 即单独定义 而不能随列的定义而定义 10 3使用约束 1 定义列名和数据类型后 直接写上关键字primarykey例10 10在数据库usedb2中建立数据表map dept 同时定义主键约束 10 3使用约束 2 定义完所有列名和数据类型后 单独写明primarykey 列名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 foreignkey外部键约束用于强制参照完整性 提供单个字段或者多个字段的参照完整性 基本格式为 constraint约束名 foreignkey 列名1 列名2 references表名 列名1 列名2 10 3使用约束 3 foreignkey注意 1 使用createtable命令建表时 若随着列的定义一同定义外键 则可以省略关键字foreignkey及列名 但使用altertable命令增加外部键时不行 2 定义外部键时要保证被定义为外键的属性在参照表中已经被定义为主键 10 3使用约束 3 foreignkey例10 13向数据库MagDb中数据表mag emp的属性DepId添加外键约束 10 3使用约束 3 foreignkey例10 14在数据库usedb2中建立数据表mag emp 同时定义主键和外键 10 3使用约束 4 check一个列级检查约束只能与限制的字段有关 一个表级检查约束只能与限制的表中字段有关 一个表中可以定义多个检查约束 每个CREATETABLE语句中每个字段只能定义一个检查约束 在多个字段上定义检查约束 则必须将检查约束定义为表级约束 当执行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之类的数据 而不能随意输入其他数据 createtablestudent idchar 8 namechar 8 sexchar 2 phonenumint constraintchk sexcheck sexin F M constraintchk phonenumcheck phonenumlike 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 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使用约束 删除约束语句格式 altertable表名dropconstraint约束名注意 定义约束的时候若省略了约束名 则要先查出系统自动命名的约束名然后进行删除 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 是一种逻辑存储方式索引的次序