（本科）第7章MySQL表定义与完整性约束控制ppt课件.pptx

《（本科）第7章MySQL表定义与完整性约束控制ppt课件.pptx》由会员分享，可在线阅读，更多相关《（本科）第7章MySQL表定义与完整性约束控制ppt课件.pptx（58页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、课程主讲人：（本科）第7章-MySQL表定义与完整性约束控制ppt课件中国农业大学 李辉p表的基本概念p数据类型p运算符p表的操作pMySQL约束控制p知识点小结p本章实验p表是数据库中存储数据的基本单位，它由一个或多个字段组成，每个字段需要有对应的数据类型数据类型。pMySQL数据库中表的管理管理，包括表的作用、类型，构成、创建、删除和修改等 。p本章先讲述了表的基本概念，MySQL支持的数据类型和运算符等一些基础，接着又讲述了表的基本操作，有创建、查看、修改、复制、删除。最后讲述了MySQL的约束控制，如何定义和修改字段的约束条件。p表的基本概念表的基本概念p数据类型p运算符p表的操作pM

2、ySQL约束控制p知识点小结p本章实验p数据库与表之间的关系是数据库是由各种数据表组成的，数据表表是数据库中最重要的对象，用来存储存储和操作操作数据的逻辑结构。p表由列和行组成，列是表数据的描述，行是表数据的实例。一个表包含若干字段或记录。p表的操作包括创建创建新表、修改修改表和删除删除表。这些操作都是数据库管理中最基本，也是最重要的操作。1.建表原则原则（1）应按照一定原则对信息进行分类。（2）对表进行规范化设计规范化设计，以消除表中存在的冗余，保证一个表只围绕一个主题，并使表容易维护。2.数据库表的信息存储信息存储分类原则（1）每个表应该只包含关于一个主题的信息。（2）表中不应包含重复信息

3、。p表的基本概念表的基本概念p数据类型数据类型p运算符p表的操作pMySQL约束控制p知识点小结p本章实验p为每张表的每个字段选择合适的数据类型是数据库设计过程中一个重要的步骤。p好处：可以有效地节省数据库的存储空间存储空间，包括内存和外存，同时也可以提升数据的计算性能计算性能，节省数据的检检索索时间。p常用的数据类型如下：pMySQL支持所有的ANSI/ISO SQL 92数字类型（ANSI，American National Standards Institute，美国国家标准局）。p数字分为整数整数和小数小数。 整数：用整数类型表示。 INTEGER、SMALLINT、TINYINT、M

4、EDIUMINT和BIGINT INT与INTEGER两个整数类型是同名词同名词，可以互换。 小数： 浮点数类型：单精度浮点数FLOAT类型和双精度浮点数DOUBLE类型 定点数类型：DECIMAL类型,DEC和DECIMAL这两个定点数类型是同名词。p浮点数和定点数都可以在类型后面加上（M,D）来表示，M表示该数值一共可以显示表示该数值一共可以显示M位数字，位数字，D表示该数值小数表示该数值小数点后的位数。点后的位数。 当在类型后面指定（M,D）时，小数点后面的数值需要按照D来进行四舍五入四舍五入。 当不指定（M,D）时，浮点数将按照实际值实际值来存储，而DECIMAL默认的整数位数为10，

5、小数位数为0。p建表时数字类型的选择应遵循的原则：1)选择最小最小的可用类型，如果改字段的值不会超过127，则使用TINYINT比INT效果好。2)对于完全都是数字的，即无小数点无小数点时，可以选择整数类型，比如年龄。3)浮点类型用于可能具有的小数部分小数部分的数，比如学生成绩。4)在需要表示金额金额等货币类型货币类型时优先选择DECIMAL数据类型pMySQL主要支持的日期日期类型：（1）DATE表示日期，默认格式为YYYY-MM-DD；（2）TIME表示时间，默认格式为HH:ii:ss；（3）YEAR表示年份；（4）DATATIME与=TIMESTAMP是日期和时间的混合类型，默认格式为Y

6、YYY-MM-DD HH:ii:SS。p从形式上来说，MySQL日期类型的表示方法与字符串字符串的表示方法相同（使用单引号括起来）；p本质上，MySQL日期类型的数据是一个数值类型数值类型，可以参与简单的加、减运算。p字符串类型的数据分为： 普通的文本字符串类型普通的文本字符串类型（CHAR和VARCHAR） CHAR类型的长度被固定为创建表所声明的长度，取值在1255之间； VARCHAR类型的值是变长的字符串，取值和CHAR一样。 可变类型可变类型（TEXT和BLOB） 大小可以改变，其中TEXT类型适合存储长文本，而BLOB类型适合存储二进制数据，支持任何数据，如文本、声音和图像等。 特

7、殊类型（复合类型复合类型）（SET和ENUM）。p 特殊类型（SET和ENUM）： ENUM类型的字段只允许从一个集合中取得某一个值只允许从一个集合中取得某一个值，有点儿类似于单选按钮的功能。 Set类型的字段允许从一个集合中取得多个值从一个集合中取得多个值，有点儿类似于复选框的功能。 一个ENUM类型的数据最多可以包含65535个元素，一个SET类型的数据最多可以包含64个元素。 一个ENUM类型只允许从一个集合中取得一个值；而SET类型允许从一个集合中取得任意多个值。p 在创建表时，使用字符串类型时应遵循以下原则：1)从速度速度方面考虑，要选择固定的列，可以使用CHAR类型。2)要节省空间

8、节省空间，使用动态的列，可以使用VARCHAR类型。3)要将列中的内容限制在一种选择限制在一种选择，可以使用ENUM类型。4)允许在一个列中有多于一个的条目有多于一个的条目，可以使用SET类型。5)如果要搜索的内容不区分大小写不区分大小写，可以使用TEXT类型。6)如果要搜索的内容区分大小写区分大小写，可以使用BLOB类型。p MySQL主要支持7种二进制类型：binary、varbinary、bit、tinyblob、blob、mediumblob和longblob。p 二进制类型的字段主要用于存储由O和1组成的字符串，从某种意义上讲，二进制类型的数据是一种特殊格式的字符字符串串。p 二进制

9、类型与字符串类型的区别： 字符串类型的数据按字符字符为单位进行存储，因此存在多种字符集、多种字符序； 除了bit数据类型按位位为单位进行存储，其他二进制类型的数据按字节字节为单位进行存储，仅存在二进制字符集binary。p 选择数据类型的原则：(1)在符合应用要求（取值范围、精度）的前提下，尽量使用“短”数据类型。(2)数据类型越简单简单越好。(3)尽量采用精确小数类型精确小数类型（例如decimal），而不采用浮点数类型。 (4)在MySQL中，应该用内置的内置的日期和时间数据类型，而不是用字符串来存储日期和时间。(5)尽量避免避免NULL字段，建议将字段指定为NOT NULL约束。p表的基

10、本概念p数据类型p运算符运算符p表的操作pMySQL约束控制p知识点小结p本章实验p 运算符是用来连接表达式中各个操作数据的符号，其作用是用来指明对操作数所进行的运算。p MySQL数据库支持运算符的使用，通过运算符可以更加灵活的操作数据表中的数据。p MySQL主要支持算术运算符算术运算符、比较运算符比较运算符、逻辑运算符逻辑运算符和位运算符四种类型。p MySQL数据库支持的算术运算符包括加、减、乘、除和取余运算。他们是最常用的、最简单的一类运算符。p MySQL数据库允许用户对表达式的左边操作数和右边操作数进行比较，比较结果为真返回1，为假返回0，不确定返回NULL。p 逻辑运算符，也称

11、为布尔运算符，判断表达式的真假。p表的基本概念p数据类型p运算符p表的操作表的操作pMySQL约束控制p知识点小结p本章实验p 表是数据库中最为重要的数据库对象。p 创建表前，需要根据数据库设计的结果确定表名、字段名及数据类型、约束等信息。p 还要为每张表选择一个合适的存储引擎存储引擎。p 表的操作： 创建表，查看表，修改表，复制表，删除表p 表管理中的注意事项p 在同一个数据库中，表名不能有重名表名不能有重名。p 创建数据表可使用CREATE TABLE命令。p 语法格式： CREATE TEMPORARY TABLE IF NOT EXISTS table_name ( column_de

12、finition , | index_definition ) table_option select_statement; p 说明：语法格式中“”表示可选的。1)TEMPORARY：使用该关键字表示创建临时表。p 说明（续）：2) IF NOT EXISTS：如果数据库中已经存在某个表，再来创建一个同名的表，这时会出现错误，为了避免错误信息，可以在创建表的前面加上这个判断，只有该表目前不存在时才执行CREATE TABLE操作。( 3)table_name：要创建的表名。( 4)column_definition：字段的定义。包括指定字段名、数据类型、指定字段名、数据类型、是否允许空值，指

13、定默认值、主键约束、唯一性约束、注释字是否允许空值，指定默认值、主键约束、唯一性约束、注释字段名、是否为外键，以及字段类型的属性段名、是否为外键，以及字段类型的属性等。p 说明（续）：col_name type NOT NULL | NULL DEFAULT default_valueAUTO_INCREMENT UNIQUE KEY | PRIMARY KEY COMMENT String reference_definition其中col_name：字段名。type：声明字段的数据类型NOT NULL 或者NULL :表示字段是否可以为空值DEFAULT：指定字段的默认值。p 说明（续）：

14、AUTO_INCREMENT：设置自增属性，只有整型类型才能设置此属性RIMARY KEY:对字段指定主键约束UNIQUE KEY:对字段指定唯一性约束reference_definition：指定字段外键约束( 5)index_definition：为表的相关字段指定索引。p 设置表的存储引擎存储引擎、默认字符默认字符集以及压缩类型压缩类型：(1)向create table语句末尾添加engine选项，即设置该表的存储引擎语法格式：Engine=存储引擎类型 (2) 向create table语句末尾添加default charset选项，即设置该表的字符集语法格式：Default char

15、set=字符集类型 (3) 如果希望压缩索引中的关键字，使索引关键字占用更少的存储空间，可以通过设置pack_keys选项实现（注意：该选项仅对MyISAM存储引擎的表有效）语法格式：Pack_keys=压缩类型 1)显示表的名称名称可以使用SHOW TABLES语句来显示指定数据库中存放的所有表名语法格式： SHOW TABLES ; 2)显示表的结构结构查看表结构有简单查询和详细查询，可使用DESCRIBE/DESC语句和SHOW CREATE TABLE语句语法格式： DESCRIBE 表名; DESC 表名; SHOW CREATE TABLE 表名; ALTER TABLE用于更改原

16、有的结构更改原有的结构。语法格式：alter ignore table table_namealter_specification , alter_specificationadd column column_definition first | after col_name/添加字段| alter column col_name set default literal | drop default /修改字段| change column old_col_name column_definition first| after col_name/重命名字段| modify column col

17、umn_definition first | after col_name /修改字段| drop column col_name/删除列| rename to new_table_name/对表重命名| order by col_name/按字段排序| convert to character set character_name collate collation_name /将字段集转化为二进制| default character set charset_name collate collation_name/修改字符集p 可以通过CREATE TABLE 命令复制表的结构和数据复制表

18、的结构和数据p 语法格式create temporary table if not exists table_name () like old_table_name | AS (select_statement);p 比如，CREATE TABLE T_A LIKE T_B；此种方式在将表T_B复制到T_A时候会将表T_B完整的字段结构字段结构和索引索引复制到表T_A中来。p 而CREATE TABLE T_A AS SELECT sn,sname,sage FROM T_B；此种方式只会将表T_B的字段结构复制到表T_A中来，但不会不会复制表T_B中的索引到表T_A中来。这种方式比较灵活可以

19、在复制原表表结构的同时指定要复制哪些字段，并且自身复制表也可以根据需要增加字段结构。p 两种方式在复制表的时候均不会复制权限对表的设置均不会复制权限对表的设置。比如说原本对表B做了权限设置，复制后，表A不具备类似于表B的权限。p 删除表可以用DROP TABLE 命令p 语法格式drop table if exists table_name ,table_name 1）关于空值（NULL）的说明空值通常用于表示未知未知、不可用可用或将在以后添加的数据将在以后添加的数据，切不可将它与数字0或字符类型的空字符混为一谈。2）关于列的标志(IDENTITY)属性任何表都可以创建一个包含系统所生成序号值

20、的标志列。该序号值唯一标志表中的一列，且可以作为键值。每个表中只能有一个列设置为标志属性，并且该列只能是DECIMAL，INT，NUMERIC，SMALLINT，BIGINT或TINYINT数据类型的。3）关于列类型的隐含改变在MySQL中，存在以下一些情形，系统会隐含地改变在CREATE TABEL语句或ALTER TBALE语句中所指定的列类型。（1）长度小于小于4的VARCHAR类型会被改变为CHAR类型。（2）由于只要一个表中存在着任何可变长度的列，都会使表中整个数据列成为变长的，因此当一张表含有任何变长的列时，例如VARCHAR，TEXT，BLOB类型的列，该表中所有大于个字符的其他

21、CHAR类型列被改变为VARCHAR类型列，而这不影响用户如何使用这些列。p表的基本概念p数据类型p运算符p表的操作pMySQL约束控制约束控制p知识点小结p本章实验p MySQL约束控制: 数据完整性约束 字段的约束 删除约束p 各种完整性约束是作为数据库关系模式定义的一部分，可通过CREATE TABLE或ALTER TABLE语句来定义。p 一旦定义了完整性约束，MySQL服务器会随时检测处于更新状态随时检测处于更新状态的数据库内容是否符合相关的完整性约束，从而保证数据的一的数据库内容是否符合相关的完整性约束，从而保证数据的一致性与正确性。致性与正确性。如此，既能有效地防止对数据库的意外

22、破坏，又能提高完整性检测的效率，还能减轻数据库编程人员的工作负担。p 数据的完整性总体来说可分为以下4类，即实体完整性、参照完实体完整性、参照完整性、域完整性和用户自定义完整性整性、域完整性和用户自定义完整性p 实体完整性: 实体的完整性强制表的标识符列或主键的完整性（通过约束，唯一约束，主键约束或标识列属性）。p 参照完整性:在删除和输入记录时，引用完整性保持表之间已定义的关系，引用完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值。如果一个键值更改了，那么在整个数据库中，对该键值的引用要进行一致的更改改。p 域完整性:限制类型（数据类型），格式（检查约束和规则），可能值范围（

23、外键约束，检查约束，默认值定义，非空约束和规则）。p 用户自定义完整性:用户自己定义的业务规则。p 在MySQL数据库中不支持检查约束。可以在语句中对字段添加检不支持检查约束。可以在语句中对字段添加检查约束，不会报错，但该约束不起作用。查约束，不会报错，但该约束不起作用。p 设计数据库时，可以对数据库表中的一些字段设置约束条件，由数据库管理系统（例如MySQL）自动检测输入的数据是否满足约束条件，不满足约束条件的数据，数据库管理系统拒绝录入。p MySQL支持的常用约束条件有7种：主键（primary key）约束、外键（foreign key）约束、非空(not NULL)约束、唯一性( u

24、nique )约束、默认值( default)约束、自增约束（auto_increment）以及检查( check)约束。p 其中，检查检查( check)约束需要借助触发器或者约束需要借助触发器或者MySQL复合数据类复合数据类型实现。型实现。p 设计数据库时，建议为所有的数据库表都定义一个主键，用于保证数据库表中记录的唯一性唯一性。一张表中只允许设置一个主键，当然这个主键可以是一个字段，也可以是一个字段组（不建议使用复合主键）。在录入数据的过程中，必须在所有主键字段中输入数据，即任何主键字段的值不允许为NULL。p 可以在创建表的时候创建主键，也可以对表已有的主键进行修改或者增加新的主键。

25、p 设置主键通常有两种方式：表级表级完整性约束和列级列级完整性约束。(1)如果一个表的主键是单个字段ID如果用表级完整性约束，就是用PRIMARY KEY命令单独设置主键为ID列。语法规则: PRIMARY KEY(字段名)(1)如果一个表的主键是单个字段ID（续）如果用列级列级完整性约束，就是直接在该字段的数据类型或者其他约束条件后加上“primary key”关键字，即可将该字段设置为主键约束语法规则: 字段名 数据类型其他约束条件 primary key (2)如果一个表的主键是多个字段的组合（例如，字段名1与字段名2共同组成主键），定义完所有的字段后，使用下面的语法规则设置复合主键。语

26、法规则: primary key（字段名1，字段名2） p 修改主键：利用alter tablep 外键约束主要用于定义表与表之间的某种关系定义表与表之间的某种关系。表A外键字段的取值，要么是NULL，要么是来自于表B主键字段的取值（此时将表A称为表B的子表，表B称为表A的父表）。p 由于子表和父表之间的外键约束关系（1）如果子表的记录“参照”了父表的某条记录，那么父表这一条记录的删除（delete）或修改（update）操作可能以失败告终。（2）如果试图直接插入（insert）或者修改(update)子表的“外键值”，子表中的“外键值”必须是父表中的“主键值”，要么是NULL，否则插入( i

27、nsert)或者修改( update)操作失败。p 设置外键的两种方式： 一种是在表级表级完整性下定义外键约束foreign key（表A的字段名列表）references表B（字段名列表） on delete cascade| restrict |set null | no action on update cascade| restrict |set null | no action 级联选项有4种取值，其意义如下。1）cascade:父表记录的删除( delete)或者修改(update)操作，会自动删除或修改子表中与之对应的记录。2） set null：父表记录的删除(delete )

28、或者修改(update)操作，会将子表中与之对应记录的外键值自动设置为null.值。3）no action:父表记录的删除( delete)或修改(update)操作，如果子表存在与之对应的记录，那么删除或修改操作将失败。4）restrict:与no action功能相同，且为级联选项的默认值。 如果表已经建好，那么可以通过alter table命令添加 语法如下:alter table table_nameadd constraint 外键名 foregin key id (index_col_name, )references table_name(index_col_name, ) on

29、 delete cascade| restrict |set null | no action on update cascade| restrict |set null | no action 如果表还没建立，那么可以在create table中指定。 另一种是在列级列级完整性下定义外键约束 在列级完整性上定义外键约束，就是直接在列的后面添加references命令p 表级完整性约束和列级完整性约束都是在CREATE TABLE语句中定义。p 另外一种方式：就是使用完整性约束命名字句完整性约束命名字句CONSTRAINT,用来对完整性约束条件命名，从而可以灵活的增加、删除一个完整性约束条件。

30、p 完整性约束命名字句格式：constraint PRIMARY KEY 短语| FOREIGN KEY 短语 | CHECK 短语p 创建表时，建议先创建父表，然后再创建子表先创建父表，然后再创建子表，并且建议子表的外键字段与父表的主键字的数据类型（包括长度）相似或者可以相互转换（建议外键字段与主键字数据类型相同）。p 如果某个字段满足非空约束的要求（例如学生的姓名不能取NULL值），则可以向该字段添加非空约束。p 若设置某个字段的非空约束，直接在该字段的数据类型后加上“not null”关键字即可。p 非空约束限制该字段的内容不能为空不能为空，但可以是空白可以是空白。p 语法规则: 字段名

31、 数据类型 not null p 如果某个字段满足唯一性约束要求，则可以向该字段添加唯一性约束。与主键约束不同，一张表中可以存在多个唯一性约束多个唯一性约束，并且满足唯一性约束的字段可以取NULL值。p 若设置某个字段为唯一性约束，直接在该字段数据类型后加上“unique”关键字即可。p 语法规则如下: 字段名 数据类型uniquep 如果某个字段存在多种约束条件，约束条件的顺序是随意随意的。p 唯一性约束实质上是通过唯一性索引实现的，因此唯一性约束的字段一旦创建，那么该字段将自动创建唯一性索引。如果要删除唯一性约束，只需删除对应的唯一性索引即可。p 如果某个字段满足默认值约束要求，可以向该字

32、段添加默认值约束，例如，可以将课程course表的人数上限up_limit字段设置默认值60。p 若设置某个字段的默认值约束，直接在该字段数据类型及约束条件后加上“default默认值”即可。p 语法规则: 字段名 数据类型其他约束条件 default默认值 p AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当为数据库表中插入新记录时，字段上的值会自动生成唯一的ID。在具体设置AUTO_INCREMENT约束时，一个数据库表中只能有一个字段使用该约束，该字段的数据类型必须是整型类型。由于设置auto_increment约束后的字段会生成唯一的ID，所以该字段也经常会设置为PK主

33、键。p MySQL中通过SQL语句的AUTO_INCREMENT来实现CREATE TABLE table_name( 属性名 数据类型 AUTO_INCREMENT, )； p 检查约束： 检查约束是用来检查数据表中字段值的有效性的一个手段，例如，学生信息表中的年龄字段是没有负数的，并且数值也是有限制的，当前大学生的年龄一般在1530岁之间。 其中，前面讲述的默认值约束和非空约束可以看作是特殊的检查约束。 在创建表时设置列的检查约束有两种：设置列级列级约束和表级表级约束。p 删除约束： 在MySQL 数据库中，一个字段的所有约束都可以用alter table 命令删除。p表的基本概念p数据类型p运算符p表的操作pMySQL约束控制p知识点小结知识点小结p本章实验本章知识小结：p 表的基本概念p MySQL支持的数据类型和运算符p 表的基本操作，有创建、查看、修改、复制、删除p MySQL的约束控制，如何定义和修改字段的约束条件。p表的基本概念p数据类型p运算符p表的操作pMySQL约束控制p知识点小结p本章实验本章实验p 实验内容：见教材“7.8 MySQL表定义与完整性约束控制实验”谢谢！中国农业大学 李辉 Email：