第4章 数据表的创建与更新.ppt

第4章数据表的创建与更新 数据表是关系数据库中操作的级别对象 在关系数据库中 通过数据表来存储数据记录 在数据表中会经常用到主键 外键 约束和索引 这一章将对数据表中涉及到的数据类型以及主键 外键 约束和索引等概念和使用方法做一个全面的介绍 另外还将介绍数据表的创建方法 以及如何对表中的记录进行增加和修改操作 如何删除数据表等内容 主键 外键 约束 索引的概念数据表的创建约束的使用索引的使用与优化更新数据表删除数据表数据库test STInfo中数据表的介绍 4 1数据库中的表 在关系数据库中 数据表是存储数据的基本单元 由行和列两部分组成的 它是根据数据库设计阶段的E R图转换而来 在数据表中除了行 列 数据记录 属性 字段等基本概念外 还会涉及到主键 外键 索引 约束等概念 这些概念在数据表的设计和创建的过程中会经常的用到 本节就对这些概念做一个简单的介绍 4 1 1数据记录 属性 字段 列 行 在关系数据库中 数据表是存储数据的基本单元 由行和列两部分组成的 其中 行用来描述实体中的具体数据 在数据表中每一行中的数据被称为一条数据记录 也可以简称为记录 字段是表中的一列 用来保存数据表中某一条记录中的特定信息 字段在关系数据库中也可以称为列 行是数据表中的一条记录 4 1 1数据记录 属性 字段 列 行 4 1 2主键 为了保证在一张数据表中不会出现两个完全相同的数据记录 需要为每一张数据表都定义一个主键 主键作为数据表中的唯一标示 保证了一条记录的唯一性 通过主键可以区分数据表中的每一条记录 在创建数据表时 需要保证被定义为主键的列的列值唯一 并且不能为空值 即NULL值 主键在关系模型中用来约束实体完整性 所谓实体完整性约束是指对数据表中行的完整性约束 在实体完整性中 需要有一个主键来唯一标示数据表中每一行的数据记录 数据表中的主键唯一且不能为空值 4 1 2主键 4 1 3外键 外键是用来定义表与表之间的关系的 在数据表中 外键是这样定义的 如果属性列F是关系B中一个属性 并不是关系B的主键 并且属性列F是关系A中的主键 则F就是关系B的外键 关系A中的表被称为主表 关系B中的表被称为主表的从表 4 1 3外键 4 1 3外键 外键在关系模型中用来约束参照完整性 所谓参照完整性约束是指表与表之间的约束 在参照完整性中 从表中的每一条数据记录中的外键值都必须存在于主表中 对于建立了关联关系的两个数据表来说 对其中一个数据表的增加 修改或者删除数据的操作都会对另一个数据表中的记录产生影响 4 1 4索引 在实际应用中 为了加快访问速度 节省访问时间 一般都需要使用索引进行查询 例如 在使用电话薄查询电话时 为了减少查询的时间 一般都会使用电话薄中提供的企业名称作为索引来查询 在使用一本书学习某一部分知识的时候 一般都需要翻看书后提供的索引 一般书后的索引都会以字母顺序将相关的主题信息列出 通过这个索引的指引 读者可以很快查找到想要的信息 而不需要为了查询某一个知识点而将书中所有的内容都翻看一遍 节省了查阅的时间 也保证了学习效率 4 1 4索引 数据库中的索引与书后提供的索引的功能相同 在数据库的应用中 往往一张数据表中会包含上千条甚至上万条记录 因此为了加快对数据表的访问 通常需要在数据表中建立适当的索引 通过建立索引 在查询数据表中的数据时 数据库可以很快的将其找到 而不用扫描整个数据表 4 1 4索引 索引是一个指向数据表中数据的指针 指向索引字段在数据表中的物理位置 如果在执行查询操作时 WHERE子句中指定的字段是被设置为索引的字段 则数据库会首先在索引中对指定的值进行查询 并返回查询的数据在数据表中的位置 如果在执行查询操作时 WHERE子句中指定的字段没有设置为索引的字段 那么数据库会查询数据表中的每一行数据记录 进行全部扫描 因此适当的创建索引 可以加快数据的检索速度 提高对数据的访问效率 提供数据查询的性能 4 1 4索引 当然 索引本身也有一些弊端 例如 索引会占用大量的硬盘空间 随着数据列的增加 创建和维护索引的时间也会随之增加 在对数据进行增加 删除和修改等更新操作的时候 需要对索引进行维护 降低更新数据的速度 因此 对那些不是在查询过程中经常用到的列以及在数据表中经常需要进行增加 删除和修改等更新操作的列就不适合建立索引 4 1 4索引 虽然创建索引可以提高查询的速度 但是由于索引本身会占用物理空间以及维护索引可能带来的时间的损耗 所以在为数据表中的列创建索引时 并不是为数据表中的每一个列都要创建索引 那样做反而不会起到提高查询效率的作用 因此需要在数据表的适当的列上创建索引 一般可以在下面这些列中创建索引 4 1 4索引 在主键列中创建索引多表连接时 在经常使用的连接列上创建索引在经常使用WHERE子句查询的列上创建索引在经常进行分组 GROUPBY 和排序 OREDRBY 的列上创建索引 4 1 5约束 为了保证数据的完整性 需要使用数据库约束 完整性约束包括对表的约束和对列的约束 表约束主要包括唯一约束 主键约束 外键约束和CHECK约束 列约束除了包括唯一约束 参照约束和CHECK约束之外 还有非空约束唯一约束 UNIQUE 保证使用唯一约束的某一列或者一组列中没有相同的值 即保证列的值的唯一性 但是唯一约束中可以允许在列中插入空值 即NULL值 主键约束 PRIMARYKEY 保证使用主键约束的列中只能有唯一的值 并且不能包含空值 数据表中每一列只能定义一个PRIMARYKEY 4 1 5约束 外键约束 FOERINGKEY 保证表的参照完整性确保对一个表的数据操作不会对与之关联的表造成不利的影响 检查约束 CHECK 限制列的取值范围或者取值条件 可以为一个列定义多个CHECK约束 非空约束 NOTNULL 只用来约束列 在向该列插入数据时不允许插入空值 4 2创建数据表 在创建完数据库之后 就可以在数据库中创建数据表了 创建数据表可以通过使用CREATETABLE语句 使用CREATTABLE语句创建数据表的语法格式如下 CREATETABLEtable name column name1datatype1 constraint condition1 column name2datatype2 constraint condition2 4 2创建数据表 例 创建表名为T student的学生信息表 CREATETABLET student stuIDVARCHAR 15 PRIMARYKEY stuNameVARCHAR 10 NOTNULL ageINTNOTNULL sexVARCHAR 2 NOTNULL birthDATETIMENOTNULL 4 3使用约束 为了保证数据的完整性 需要使用数据库约束 约束主要包括唯一约束 UNIQUE 主键约束 PRIMARYKEY 外键约束 FOREIGNKEY 检查约束 CHECK 和非空约束 NOTNULL 这一节将介绍这几种约束的作用以及它们在创建数据表的过程中是如何定义的 4 3 1唯一约束 唯一约束 UNIQUE 用来保证某一列或者一组列中没有相同的值 如果为列定义了唯一约束 则该列中不允许出现重复的值 但是允许列中存在空值 即NULL值 唯一约束即可以定义的表级上 也可以定义在列级上 一般在为列创建唯一约束后 数据库会自动为该列建立一个唯一索引 其索引名与约束名是相同的 4 3 2主键约束 主键约束 PRIMARYKEY 是用来保证使用主键约束的某一列或者一组列中有唯一的值 并且不能包含空值 即NULL值 数据表中每一列只能定义一个PRIMARYKEY 一般在为列创建主键约束后 数据库会自动为该列建立一个主索引 其索引名与约束名是相同的 在创建数据表时 如果希望将多个列组合起来作为一个数据表中的主键 可以在PRIMARYKEY关键字后使用括号 将需要定义为主键的列放到PRIMARYKEY关键字后面的括号中 括号中的多个列之间需要使用逗号分割 4 3 2主键约束 创建成绩信息表 CREATETABLET result stuIDVARCHAR 15 curIDVARCHAR 15 resultDOUBLE PRIMARYKEY stuID curID 4 3 3外键约束 首先来考虑这样一个问题 现在由于某种原因需要将一名学生在学校的全部信息删除 这里涉及学生信息的一共有两个表 一个是学生信息表 一个是成绩信息表 学生信息表保存的是学生的基本信息 成绩信息表保存的是学生选课的课程成绩信息 如果没有定义外键约束 将该名学生从学生信息表中删除时 而没有将该名学生的选课的课程成绩信息删除 这样就会造成两个关联表之间数据的不一致 同样的问题也可能出现在对两个关联表中数据的修改操作中 如果是多个表之间存在关联关系的话 不定义外键约束 类似上面的情况很可能会发生 为了避免由于操作不当而引起的关联表中数据的不一致 有必要为关联表之间定义外键约束 4 3 3外键约束 外键约束 FOERINGKEY 主要是用来定义两个表之间的关系 外键约束保证了表的参照完整性 确保对一个表的数据操作不会对与之关联的表造成不利的影响 定义外键的语法格式如下 FOREIGNKEY 表名1 列名1 REFERENCES表名2 列名2 ONUPDATE CASCADE SETNULL RESTRICT ONDELETE CASCADE SETNULL RESTRICT 4 3 3外键约束 CASCADE 级联删除 如果主表中的一条数据记录被删除 那么从表中与之相对应的的数据也将被一起删除 SETNULL 置空删除 如果主表中的一条数据记录被删除 那么从表中与之相对应的的数据也将被设置为空值 RESTRICT 受限删除 如果主表中的一条数据记录被删除 则在执行DELETE命令时数据库管理系统会报错 通知用户与主表相对应的该数据在从表中仍然存在 但是与主表相对应的该数据在从表中不会被删除 它是默认的方式 4 3 3外键约束 例为成绩信息表定义外键 CREATETABLET result stuIDVARCHAR 15 curIDVARCHAR 15 resultDOUBLE FOREIGNKEY stuID REFERENCEST student stuID ONDELETECASCADE PRIMARYKEY stuID curID 4 3 4检查约束 检查约束 CHECK 是用来限制列的取值范围或者取值条件 使用CHECK约束可以保证数据规则的一致性 可以为一个列定义多个CHECK约束 当为列定义了CHECK约束后 该列中所对应的数据必须满足指定的约束条件 4 3 4检查约束 例定义课程信息表 CREATETABLET curriculum curIDVARCHAR 15 PRIMARYKEY curNameVARCHAR 10 creditINT CHECK creditBETWEEN3AND8 4 3 5非空约束 非空约束 NOTNULL 是用来保证在向该列插入数据时不允许插入空值 即NULL值 非空约束只能用来约束列 4 3 5非空约束 例创建教师信息表 CREATETABLET teacher teaIDVARCHAR 15 PRIMARYKEY teaNameVARCHAR 10 NOTNULL ageINTNOTNULL sexVARCHAR 2 NOTNULL deptIDVARCHAR 15 deptVARCHAR 20 NOTNULL professionVARCHAR 10 4 4使用索引 在实际应用中 为了加快查询的效率 都会为数据表创建适当的索引 索引就是一个指向数据表中数据的指针 通过为数据表建立适当的索引可以提高SQL语句对数据表的访问速度 这一章就来介绍如何在数据库中使用SQL语句创建和删除索引 有关优化索引的内容可以参看26 1节适当创建索引 4 4 1索引的分类 索引主要可以分为唯一索引 主索引 单列索引 复合索引以及聚簇索引等 下面分别来介绍一下这几种索引 唯一索引 在数据表中使用UNIQUE关键字可以为一个数据列定义一个唯一索引 唯一索引中每一个索引值只对应数据表中的一条记录 它保证了数据列中记录的唯一性 如果在向数据表中插入一条记录时 数据库会对要插入的这条记录进行检查 如果发现该条记录中的值在定义了唯一索引的列中出现过 那么数据库管理系统就不会将这条记录插入到数据表中 一般在为列创建唯一约束后 数据库会为该列建立一个唯一索引 其索引名与约束名是相同的 4 4 1索引的分类 主索引 在数据表中使用PRIMARYKEY关键字可以为一个数据列定义一个主索引 所谓主索引 就是在定义的主键列中创建的索引 主索引也保证了数据列中记录的唯一性 一般在为列创建主键约束后 数据库会为该列建立一个主索引 其索引名与约束名是相同的 单列索引 定义在数据表中一个数据列上的索引就是单列索引 一般在数据查询时 如果WHERE子句中经常用到数据表中的某一列作为查询条件 为了提高查询的效率 可以为该列创建单列索引 4 4 1索引的分类 复合索引 索引可以定义在一个数据表的多个数据列上 像这样的索引被称为复合索引 一般在数据查询时 如果WHERE子句中经常用到数据表中的某几个列作为查询条件 为了提高查询的效率 可以为这多个列创建复合索引 聚簇索引 为了提高SQL语句对数据表的查询效率 可以为数据表创建一个聚簇索引 聚簇索引中索引项的顺序与数据表中数据记录的物理顺序保持一致 聚簇索引在每一个数据表中只能创建一个 4 4 2创建与删除索引 在数据表中 创建和删除索引一般是由数据库管理员或者是数据表的创建者来完成的 如果想创建索引 可以使用CREATEINDEX语句完成 创建索引的语法格式如下 CREATE UNIQUE CLUSTER INDEX索引名ON表名 列名 排序方式 4 4 2创建与删除索引 删除索引的语法格式如下 DROPINDEX索引名ON表名 4 5修改数据库中的表 如果在创建完数据表之后 还需要对数据表结构做一些修改 例如向数据表中增加某一列 删除某一列 为数据表中的某一列增加约束条件或者索引 修改数据表中某一列的数据类型等等 这个时候就需要使用ALTERTABLE语句 这一节就来介绍如何使用ALTERTABLE语句修改数据表结构 4 5 1向表中增加一列 在实际应用中 根据实际需要 有些时候需要为数据表增加一个指定的列 用来完善数据表信息 向数据表增加一列的语法格式如下 ALTERTABLEtable nameADD column namedatatype constraint condition 其中 ALTERTABLE是修改表结构的关键字 关键字ALTERTABLE之后跟的table name表的名字 ADD是关键字 用来指定数据表中要增加的列 关键字ADD后面的括号中需要指定新增加的列名 该列对应的数据类型和完整性约束条件 column name表示新增加的列名 datatype用来指定该列的数据类型 constraint condition用来表示该列的完整性约束条件 它是可选的 4 5 2增加一个约束条件 如果在创建一个数据表时没有为指定的列定义约束条件 在数据表创建完成后 可以使用ALTERTABLE语句中的ADD子句为某一列增加一个约束条件 其语法格式如下 ALTERTABLEtable nameADDconstraint type column name 其中 ALTERTABLE是修改表结构的关键字 关键字ALTERTABLE之后跟的table name表的名字 ADD是关键字 用来指定数据表中要增加的列 关键字ADD后面的括号中需要指定约束条件和新增加的列名 constraint type表示指定的约束条件 column name1表示新增加的列名 4 5 3增加一个索引 如果在创建一个数据表时没有为指定的列定义索引 在数据表创建完成后 可以使用ALTERTABLE语句中的ADD子句为列增加一个索引 其语法格式如下 ALTERTABLEtable nameADDINDEX column name1 column name2 其中 ALTERTABLE是修改表结构的关键字 关键字ALTERTABLE之后跟的table name表的名字 ADD是关键字 用来指定数据表中要增加的列 INDEX关键字表示要为数据表增加一个索引 column name1表示为增加索引指定的列 指定列可以是一列 也可以是多列 4 5 4修改表中的某一列 在实际应用中 有些时候需要对数据表中的某一列的数据类型 默认值等内容进行修改 这时就需要使用ALTERTABLE中的MODIFY子句 其语法格式如下 ALTERTABLEtable nameMODIFYcolumn namedatatype其中 ALTERTABLE是修改表结构的关键字 关键字ALTERTABLE之后跟的table name表的名字 MODIFY是关键字 用来表示要对数据表中的某一列进行修改操作 关键字MODIFY后面需要指定要修改列的名字和该列对应的数据类型 column name表示列的名字 datatype表示列的数据类型 4 5 5删除表中某一列 如果在数据表中的某一列不需要使用了 可以使用CREATETABLE语句中的DROP子句将该列删除 其删除的语法格式如下 ALTERTABLEtable nameDROPcolumn name其中 ALTERTABLE是修改表结构的关键字 关键字ALTERTABLE之后跟的table name表的名字 DROP是关键字 用来表示要对数据表中的某一列进行删除操作 关键字DROP后面需要指定要删除列的名字 4 5 6删除一个约束条件 如果在数据表中将某一个列的约束条件删除 可以使用CREATETABLE语句中的DROP子句 其删除约束条件的语法格式如下 ALTERTABLEtable nameDROPconstraint type其中 ALTERTABLE是修改表结构的关键字 关键字ALTERTABLE之后跟的table name表的名字 DROP是关键字 用来表示要将数据表中列的约束条件删除 关键字DROP后面需要指定要删除约束条件的名字 constraint type表示要删除的约束条件 4 6删除数据库中的表 如果在实际使用中某一个数据表已经不再需要时 可以使用DROPTABLE语句将其删除 其语法格式如下 DROPTABLEtable name CASCADECONSTRAINTS 其中 DROPTABLE是用于删除