MySQL数据库基础与实例教程 之 MySQL表结构的管理 郭水泉.

Presentation on theme: "MySQL数据库基础与实例教程 之 MySQL表结构的管理 郭水泉."— Presentation transcript:

1 MySQL数据库基础与实例教程 之 MySQL表结构的管理 郭水泉

2 任务：为petstore数据库表选择合适的数据类型
1.用户表account 用户编号、用户名、密码、性别、住址、邮箱、电话 2.商品分类表category 类别编号、分类名称、类别描述 3.商品表product 商品编号、类别编号、商品名、商品介绍、市场价格、成本价格、数量 4.订单表orders 订单号、用户编号、订单日期、商品介绍、订单总价、订单状态 5.订单明细表lineitem 订单号、商品编号、数量、单价

3 内容一览 本章详细讲解“选课系统”数据库各个表的实施过程，通过本章的学习，读者可以掌握表结构管理的相关知识。 1 2 3 4 5
MySQL数据类型 2 创建表 3 修改表结构 4 删除表 5 索引

4 3.1 MySQL数据类型 MySQL提供的数据类型包括数值类型（整数类型和小数类型）、字符串类型、日期类型、复合类型（复合类型包括enum类型和set类型）以及二进制类型 。 MySQL整数类型 1 MySQL小数类型 2 MySQL字符串类型 3 MySQL日期类型 4 MySQL复合类型 5

5 3.1 MySQL数据类型 MySQL提供的数据类型包括数值类型（整数类型和小数类型）、字符串类型、日期类型、复合类型（复合类型包括enum类型和set类型）以及二进制类型 。 6 MySQL二进制类型 7 为字段选择合适的数据类型

6 3.1.1 MySQL整数类型

7 3.1.1 MySQL整数类型 整数类型的数，默认情况下既可以表示正整数又可以表示负整数（此时称为有符号数）。如果只希望表示零和正整数，可以使用无符号关键字“unsigned”对整数类型进行修饰（此时称为无符号整数）。 例如：score tinyint unsigned

8 3.1.1 MySQL整数类型

9 3.1.2 MySQL小数类型

10 3.1.2 MySQL小数类型 decimal(length, precision)用于表示精度确定（小数点后数字的位数确定）的小数类型，length决定了该小数的最大位数，precision用于设置精度（小数点后数字的位数）。 例如： decimal (5,2)表示小数取值范围： ～999.99 decimal (5,0)表示： ～99999的整数。

11 3.1.2 MySQL小数类型

12 3.1.3 MySQL字符串 字符串类型的数据外观上使用单引号括起来，例如学生姓名'张三'、课程名'java程序设计'等。

13 3.1.3 MySQL字符串类型 char()与varchar():存储或检索过程中不进行大小写转换。对于简体中文字符集gbk的字符串而言，varchar(255)表示可以存储255个汉字，而每个汉字占用两个字节的存储空间。假如这个字符串没有那么多汉字，例如仅仅包含一个‘中’字，那么varchar(255)仅仅占用1个字符（两个字节）的储存空间，另加一个字节记录长度. varchar(1000)需要多大空间? 而char(255)则必须占用255个字符长度的存储空间，哪怕里面只存储一个汉字。 Char、archar，当存储或检索尾部带空格的字符串时有什么区别。

14

15

16 3.1.4 MySQL日期类型 date表示日期，默认格式为‘YYYY-MM-DD’； time表示时间，格式为‘HH:mm:ss’；
year表示年份； datetime与timestamp是日期和时间的混合类型，格式为'YYYY-MM-DD HH:mm:ss'

17 3.1.4 MySQL日期类型 datetime（8字节）与timestamp（4字节）都是日期和时间的混合类型，区别在于：
将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间。 同一个timestamp类型的日期或时间，不同的时区，显示结果不同。

18

19 3.1.4 MySQL日期类型 任务布置1：理解datetime与timestamp之间的区别。
学会使用now()函数。 注意：now()函数用于获得MySQL服务器的当前时间，该时间与时区的设置密切相关。

20 3.1.5 MySQL复合类型 MySQL 支持两种复合数据类型：enum枚举类型和set集合类型。
enum类型的字段类似于单选按钮的功能，一个enum类型的数据最多可以包含65535个元素。 set 类型的字段类似于复选框的功能，一个set类型的数据最多可以包含64个元素。

21 字符-数字映射表

22 3.1.5 MySQL复合类型 任务布置2：上机操作，完成本书场景描述3的任务要求，理解复合类型的用法。

23 3.1.6 MySQL二进制类型 二进制类型的字段主要用于存储由‘0’和‘1’组成的字符串，因此从某种意义上将，二进制类型的数据是一种特殊格式的字符串。 二进制类型与字符串类型的区别在于：字符串类型的数据按字符为单位进行存储，因此存在多种字符集、多种字符序；而二进制类型的数据按字节为单位进行存储，仅存在二进制字符集binary。

24 3.1.6 MySQL二进制类型

25 Blob和text类型 1.blob二进制大数据存储：声音、图像、图片、视频 2.Text字符主要是文字方面的数据存储:简介、说明等
3.存储或检索过程中不进行大小写转换，排序和索引使用前几个字符：max_short_length 4.最大值由数据类型确定，客户端和服务器之间传递的最大值由可用内存和通信缓存区大小确定：max_allowd_packet修改

26 3.1.7 选择合适的数据类型 选择合适的数据类型，不仅可以节省储存空间，还可以有效地提升数据的计算性能。
（1）更小的通常更好：在符合应用要求（取值范围、精度）的前提下，尽量使用“短”数据类型 （2）简单就好：数据类型越简单越好 （3）在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储日期和时间。

27 3.1.7 选择合适的数据类型 （4）尽量采用精确小数类型（例如decimal），而不采用浮点数类型。使用精确小数类型不仅能够保证数据计算更为精确，还可以节省储存空间，例如百分比使用decimal(4,2)即可。计算时转化为double （5）尽量避免NULL字段，建议将字段指定为NOT NULL约束。Null字段很难优化，使得索引、索引统计和值比较都更复杂。 （6）从存储和计算来说int（1）和int（20）是相同的，但显示不同

28 （7）位数据类型 Bit、set（可以用整数代替） （8）标识列的选择 整型是最好的选择 Char为什么差些？ 标识列与其它有关联的列的类型相同 Ip地址如何存储

29 3.2 创建表 create table 表名( 字段名1 数据类型 [约束条件], … [其他约束条件], [其他约束条件]
)其他选项（例如存储引擎、字符集等选项） 设置约束 1 设置自增型字段 2 其他选项的设置 3 创建“选课系统”数据库表 4 复制一个表结构 5

30 3.2.1 设置约束 1．设置主键（Primary Key）约束
例如： userid char(6) primary key，

31 3.2.1 设置约束 1．设置主键（Primary Key）约束
（2）如果一个表的主键是多个字段的组合（例如字段名1与字段名2共同组成主键），定义完所有的字段后，使用下面的语法规则将（字段名1, 字段名2）设置为复合主键。 primary key (字段名1, 字段名2) 2.设置外键 FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE/update CASCADE

32 CASCADE 删除包含与已删除键值有参照关系的所有记录
SET NULL 修改包含与已删除键值有参照关系的所有记录，使用NULL值替换（只能用于已标记为NOT NULL的字段） RESTRICT 拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置) NO ACTION 啥也不做 请注意，通过ON UPDATE 和 ON DELETE规则，设置MYSQL能够实现自动操作时，如果键的关系没有设置好，可能会导致严重的数据破坏

33 3.设置唯一性 字段名 数据类型 unique `username` varchar(18) NOT NULL unique,

34 3.2.2 设置自增型字段 默认情况下，MySQL自增型字段的值从1开始递增，且步长为1。设置自增型字段的语法格式如下。
字段名 数据类型 auto_increment Orderid int(11) NOT NULL AUTO_INCREMENT, 总结约束顺序：字段名 数字类型 数据宽度 是否为空 是否主键 自动增加 默认值 id int(4) not null primary key auto_increment, default ？

35 3.2.3 其他选项的设置 1．设置表的存储引擎，语法格式如下。 engine=存储引擎类型 2．设置该表的字符集，语法格式如下。
default charset=字符集类型

36 3.2.3 其他选项的设置 3．设置索引关键字的压缩方式，可以通过设置pack_keys选项实现（注意仅对MyISAM存储引擎的表有效），语法格式如下。 pack_keys=压缩类型 压缩类型值为default：表示只压缩索引中字符串类型的关键字（例如char、varchar、text等字段），但不压缩数值类型的关键字。 压缩类型值为0：表示取消索引关键字的压缩。

37 3.2.3 其他选项的设置 3．设置索引关键字的压缩方式
压缩类型值为1：表示压缩索引中所有关键字的储存空间，这样做通常会使检索速度加快，更新速度变慢。例如索引中第一个关键字的值为“perform”，第二关键字的值为“performance”，那么第二关键字会被存储为“7,ance”。

38 null,not null,default, default null auto_increment，‘’区别
1、空值是不占用空间的 2、mysql中的NULL其实是占用空间的 打个比方来说，你有一个杯子，空值代表杯子是真空的，NULL代表杯子中装满了空气，虽然杯子看起来都是空的，但是区别是很大的。 3.not null数据能否存放null或‘’。

39 4.default值 如果一个字段中没有指定DEFAULT修饰符，MySQL会依据这个字段是NULL还是NOT NULL自动设置默认值。如果指定字段可以为NULL，则MySQL为其设置默认值为NULL。如果是NOT NULL字段，MySQL对于数值类型插入0，字符串类型插入空字符串，时间戳类型插入当前日期和时间，ENUM类型插入枚举组的第一条。

40 空值不一定等于空字符 CREATE TABLE `test` (
 `col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    `col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL   ) ENGINE = MYISAM ;   INSERT INTO `test` VALUES (null,1);  INSERT INTO `test` VALUES ('',1);   INSERT INTO `test` VALUES ('', NULL);   INSERT INTO `test` VALUES ('1', '2');  

41 统计记录时： SELECT * FROM `test` WHERE col1 IS NOT NULL

42

43 3.2.5 复制一个表结构 复制一个表结构的实现方法有两种。
方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。 create table 新表名 like 源表

44 3.2.5 复制一个表结构 复制一个表结构的实现方法有两种。
方法二、在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。 create table 新表名 as （select * from 源表）

45 create table im ( id int(3) not null, name varchar(25) not null default 'QQ' );
/* 插入数据，不传name字段的值，MySQL会为其设置默认值的 您运行的 SQL 语句已经成功运行了。 */ INSERT INTO im( id, name ) VALUES ( 2, 'MSN' ) ; INSERT INTO im( id ) VALUES ( 3 ) ; SELECT * FROM im LIMIT 0 , 30; /* id name 2 MSN 3 QQ */

46 mysql> create table MyClass( > id int(4) not null primary key auto_increment, > name char(20) not null, > sex int(4) not null default ‘0’, > degree double(16,2))engine=innodb， CHARACTER SET=utf8 ;

47 3.3 修改表结构 成熟的数据库设计，数据库的表结构一般不会发生变化。数据库的表结构一旦发生变化，基于该表的视图、触发器、存储过程将直接受到影响，甚至导致应用程序的修改。 修改字段相关信息 1 修改约束条件 2 修改表的其他选项 3 修改表名 4 4

48 3.3.1 修改字段相关信息 1．删除字段 删除表字段的语法格式如下。 alter table 表名 drop 字段名

49 3.3.1 修改字段相关信息 2．添加新字段 向表添加新字段时，通常需要指定新字段在表中的位置。向表添加新字段的语法格式如下。
alter table 表名 add 新字段名 新数据类型 [ 新约束条件 ] [ first | after 旧字段名]

50 3.3.1 修改字段相关信息 3．修改字段名（或者数据类型） （1）修改表的字段名（及数据类型）的语法格式如下。
alter table 表名 change 旧字段名 新字段名 新数据类型

51 3.3.1 修改字段相关信息 3．修改字段名（或者数据类型） （2）如果仅对字段的数据类型进行修改，可以使用下面的语法格式。
alter table 表名 modify 字段名 新数据类型

52 3.3.2 修改约束条件 1．添加约束条件 向表的某个字段添加约束条件的语法格式如下（其中约束类型可以是唯一性约束、主键约束及外键约束）。
alter table 表名 add constraint 约束名 约束类型 (字段名)

53 3.3.2 修改约束条件 2．删除约束条件 （1）删除表的主键约束条件语法格式比较简单，语法格式如下。
alter table 表名 drop primary key （2）删除表的外键约束时，需指定外键约束名称，语法格式如下（注意需指定外键约束名）。 alter table 表名 drop foreign key 约束名

54 3.3.2 修改约束条件 2．删除约束条件 （3）若要删除表字段的唯一性约束，实际上只需删除该字段的唯一性索引即可，语法格式如下（注意需指定唯一性索引的索引名）。 alter table tabname drop unique COLNAME

55 3.3.3 修改表的其他选项 alter table 表名 engine=新的存储引擎类型
alter table 表名 default charset=新的字符集 alter table 表名 auto_increment=新的初始值 alter table 表名 pack_keys=新的压缩类型

56 3.3.4 修改表名 修改表名的语法格式较为简单，语法格式如下。 rename table旧表名to新表名
该命令等效于：alter table 旧表名 rename 新表名

57 3.4 删除表 删除表的SQL语法格式比较简单，前面也已经讲过，这里不再赘述。这里唯一需要强调的是删除表时，如果表之间存在外键约束关系，此时需要注意删除表的顺序。 Drop table test

58 一、使用create index创建索引 1.对account的 列创建i_em_ind降序普通索引 2.对account创建fullname和address复合索引c_fa_ind 3.对商品表中的name前4个字创建唯一性索引u_na_ind

59 二、使用alter table添加索引 1.对category的catid列创建主建索引，catname添加唯一性索引u_ca_ind 2.对订单明细表中的orderid和itemid创建主键索引，quantity和unitprice添加复合索引c_qu_ind 三、创建购物车表shopcat（购物车编号，客户编号，商品编号，单价，数量），创建主索引购物车编号，复合索引客户编号和商品编号c_up_ind 四、显示表的索引和删除c_up_ind索引。

60 数据完整性约束 1.讲述执行过程 Create table book_ref( 图书编号 varchar（20） null，
书名 varchar（20） not null， 出版日期 date null， Primary key（图书编号）， Foreign key（图书编号） references book（图书编号） on delect restrict on update cascade )

61 2.讲解语句作用 create table student1 ( 学号 char(6) not null, 出生日期 date not null check(出生日期>' ') );

62 create table student2 ( 学号 char(6) not null, 性别 char(1) not null check( 性别 in ( select 性别 from student) ) );

63 3.对商品表中分类编号创建外键，以保证当要删除分类表中catid列的值时，商品表中catid列中还有该值的记录，则拒绝对分类表的删除操作。
4.创建orders表中userid列的外键，要求保证当要删除或更新account表中数据时，只要orders中还有该客户订单，就拒绝对account表进行的删除和更新操作。

64 5.在明细表中创建商品编号的外键，以保证当要删除或更新商品表中的商品编号是，自动删除或更新明细表中匹配的行。
6.在明细表中创建订单号的外键，要求以保证当要删除订单表中的订单号时，自动删除明细表中匹配的行。

65 3.5 索引 创建数据库表时，初学者通常仅仅关注该表有哪些字段、字段的数据类型及约束条件等信息，数据库表中另一个重要的概念“索引”很容易被忽视。 理解索引 1 索引关键字的选取原则 2 索引与约束 3 4 创建索引 删除索引 5

66 3.5.1 理解索引 想象一下现代汉语词典的使用方法，理解索引的重要性。 1．索引的本质是什么？ 2．MySQL数据库中，数据是如何检索的？
3．一个数据库表只能创建一个索引吗？ 4．什么是前缀索引？ 5．索引可以是字段的组合吗？ 6．能跨表创建索引吗？

67 3.5.1 理解索引 想象一下现代汉语词典的使用方法，理解索引的重要性。 7．索引数据需要额外的存储空间吗？
8．表中的哪些字段适合选作表的索引？什么是主索引？什么是聚簇索引？ 9．索引与数据结构是什么关系？ 10．索引非常重要，同一个表，表的索引越多越好吗？

68 1．索引概念 在数据库中，为了从大量的数据中迅速找到所需的内容，也采用类似于书目录这样的检索技术，使得数据查询时不必一行一行记录地扫描整个数据库中全部的表，就能迅速查到所需要的内容！这就是MySQL的索引技术。

69 2．索引作用 在数据库系统中建立索引主要有以下作用： 快速读取数据； 保证数据记录的唯一性； 实现表与表之间的参照完整性； 在使用GROUP BY、ORDER BY子句进行数据检索时，利用索引可减少排序和分组的时间。

70 在MySQL 5.6中，所有的MySQL列类型都能被索引，但要注意以下几点：
只有当表类型为MyISAM、InnoDB或BDB时，才可以向有NULL、BLOB或TEXT列中添加索引。 一个表最多可有16个索引。最大索引长度是256个字节。 对于CHAR和VARCHAR列，可以索引列的前缀。这样索引的速度更快并且比索引整个列需要较少的磁盘空间。 MySQL能在多个列上创建索引。

71 1．普通索引（INDEX） 这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。 2．唯一性索引（UNIQUE） 这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。 3．主键（PRIMARY KEY） 主键是一种唯一性索引，它必须指定为“PRIMARY KEY”。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。

72 4．全文索引（FULLTEXT） MySQL支持全文检索和全文索引。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。 5．哈希索引（HASH） 当表类型为MEMORY或HEAP时，除了BTREE索引，MySQL还支持哈希索引（HASH）。使用哈希索引，不需要建立树结构，但是所有的值都保存在一个列表中，这个列表指向相关页和行。

73 3.5.2 索引关键字的选取原则 索引的设计往往需要一定的技巧，掌握了这些技巧，可以确保索引能够大幅地提升数据检索效率，弥补索引在数据更新方面带来的缺陷。 原则1：表的某个字段值离散度越高，该字段越适合选作索引的关键字。索引基数 Show indexes from 表名 cardinality 原则2：占用储存空间少的字段更适合选作索引的关键字。

74 3.5.2 索引关键字的选取原则 原则3：较频繁地作为where查询条件的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该创建索引。都有的话可以创建复合索引 原则4：更新频繁的字段不适合创建索引，不会出现在where子句中的字段不应该创建索引。 原则5．最左前缀原则

75 最左前缀：顾名思义，就是最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。 注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。 原则6．尽量使用前缀索引 前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。

76 3.5.3 索引与约束 约束主要用于保证业务逻辑操作数据库时数据的完整性；约束是逻辑层面的概念。
索引则是将关键字数据以某种数据结构的方式存储到外存，用于提升数据的检索性能；索引既有逻辑上的概念，更是一种物理存储方式，且事实存在、需要耗费一定的储存空间。

77 3.5.4 创建索引 索引的种类： 主索引、聚簇索引 唯一性索引 普通索引 复合索引 全文索引（fulltext）

78 3.5.4 创建索引 方法一：创建表的同时创建索引 create table 表名( 字段名1 数据类型 [约束条件], …
[其他约束条件], [ unique | fulltext ] index [索引名] ( 字段名 [(长度)] [ asc | desc ] ) ) engine=存储引擎类型 default charset=字符集类型

79 3.5.4 创建索引 create table book( isbn char(20) primary key,
name char(100) not null, brief_introduction text not null, price decimal(6,2), publish_time date not null, unique index isbn_unique (isbn), index name_index (name (20)), fulltext index brief_fulltext (name,brief_introduction), index complex_index (price,publish_time) ) engine=MyISAM default charset=gbk;

80 在创建订单明细表时创建订单号和商品编号的联合主键，并在数量上创建索引。

81 3.5.4 创建索引 方法二、在已有表上创建索引 语法格式一：
create [ unique | fulltext ] index 索引名 on 表名 ( 字段名 [(长度)] [ asc | desc ] ) 语法格式二： alter table 表名 add [ unique | fulltext ] index 索引名 ( 字段名 [(长度)] [ asc | desc ] )

82 在商品表中的商品名列上前6个字符创建一个升序索引name_product.
在订单表中创建一个订单号和用户编号的复合索引order_user 在商品表中的商品名列创建一个普通索引name_product 在商品表中的创建一个商品编号的主键索引，市场价格和数量的复合索引

83 3.5.5 删除索引 删除索引的语法格式如下。 1.drop index 索引名 on 表名 删除name_product.索引
2.Alter table 表名 |drop primary key |drop index 索引名 1.删除商品表中的主键索引和复合索引 2.查看索引 Show index from 表名

84 在B-树中查找给定关键字的方法是，首先把根结点取来，在根结点所包含的关键字K1,…,Kn查找给定的关键字（可用顺序查找或二分查找法），若找到等于给定值的关键字，则查找成功；否则，一定可以确定要查找的关键字在Ki与Ki+1之间，Pi为指向子树根节点的指针，此时取指针Pi所指的结点继续查找，直至找到，或指针Pi为空时查找失败。

85 1．使用索引的好处 1）查找编号1000的商品，若没有索引需要一行一行的扫描，1000行的表需要扫描一千行的比较运算。如果创建了索引，则可以先在索引中找到编号为1000的记录的位置，在找到1000所指向的记录。 2）当执行涉及多个表的连接查询时，索引将更有价值。 假如有3个未索引的表t1、t2、t3，分别只包含列c1、c2、c3，每个表分别由含有数值1～1000的1000行组成。查找对应值相等的表行组合的查询如下： select c1,c2,c3 from t1,t2,t3 where t1.c1=t2.c2 and t1.c1=t3.c3;

86 如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下。
（1）从表t1中选择第一行，查看此行所包含的值。 （2）使用表t2上的索引，直接跳到t2中与来自t1的值匹配的行。类似地，利用表t3上的索引，直接跳到t3中与来自t1的值匹配的行。 （3）进到表t1的下一行并重复前面的过程，直到t1中所有的行已经查询过。 还有就是实现结果排序

87 Extra：using where using filesort（内部进行sort_buffer结果排序，效率较低） Using index（满足所有要求）

88

89 2．索引的弊端 但是，索引也有它的弊端： 首先，索引是以文件的形式存储的，索引文件要占用磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。 其次，在更新表中索引列上的数据时，对索引也需要更新，这可能需要重新组织一个索引，如果表中的索引很多，这是很浪费时间的。也就是说，这样就降低了添加、删除、修改和其他写入操作的效率。表中的索引越多，则更新表的时间就越长。 最后，alter table执行时间很长 但是这些弊端并不妨碍索引的应用，因为索引带来的好处已经基本掩盖了它的缺陷，在表中有很多行数据的时候，索引通常是不可缺少的。

90 快速成为DBA 1.识别性能问题 1.1寻找运行缓慢的sql Show full processlist 1.2确认低效
1.2.1运行语句执行时间 1.2.2生成一个执行计划：explain 语句 \G

91 2.优化语句 2.1不应该做的事情 使用alter table 添加一个索引 考虑alter语句特点

92 2.2确认优化 2.2.1运行语句执行时间 2.2.2生成一个执行计划：explain 语句 \G 2.3正确的方式 添加索引前应做的：查看表结构和表大小 Show create table ‘表名’ Show table status like ‘表名’ Data_length和rows

93

94

95 创建更好的索引：覆盖索引和局部索引

96 Explain命令详解

97 1.key 2.rows Show session status like ‘handler_read%’ 查看实际的行数 3.possible_keys Show index from 表名 查看基数

98 4.key_len 5.table 6.select_type 7.extra 提供优化器路径的一系列额外信息 Using where，using temporary（使用了临时表），using filesort，Using index（索引就满足，不需要直接访问表数据），Using join buffer（表连接没有使用索引）

99 8.id 9.ref 10.type

100 解释explain输出结果 找出没有订单的用户，用三种方式 分析哪种好，随着数据的增长另一种会更高效吗？

101 数据完整性约束 1.主键约束 原则上，任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一些规则。这些规则源自于关系模型理论和MySQL所制定的以下规则： （1）每个表只能定义一个主键。来自关系模型的这一规则也适用于MySQL。 （2）关系模型理论要求必须为每个表定义一个主键。 （3）表中的两个不同的行在主键上不能具有相同的值，即所谓的“唯一性规则”。 （4）如果从一个复合主键中删除一列后，剩下的列构成的主键仍然满足唯一性原则，那么，这个复合主键是不正确的，这条规则称为“最小化规则”（Minimality Rule）。也就是说，复合主键不应包含任何不必要的列。 （5）一个列名在一个主键的列的列表中只能出现一次。

102 2.替代键约束 在MySQL中，替代键和主键的区别主要有以下几点：
（1）一个数据表只能创建一个主键。但一个表可以有若干个UNIQUE键，并且它们甚至可以重合，例如，在C1和C2列上定义了一个替代键，并且在C2和C3上定义了另一个替代键，这两个替代键在C2列上重合了，而MySQL允许这样。 （2）主键字段的值不允许为NULL，而UNIQUE字段的值可取NULL，但是必须使用NULL或NOT NULL声明。 （3）一般创建PRIMARY KEY约束时，系统会自动产生PRIMARY KEY索引。创建UNIQUE约束时，系统自动产生UNIQUE索引。

103 3.参照完整性约束 当指定一个外键的时候，以下的规则适用：
（1）被参照表必须已经用一条CREATE TABLE语句创建了，或者必须是当前正在创建的表。 （2）必须为被参照表定义主键。 （3）必须在被参照表的表名后面指定列名（或列名的组合）。这个列（或列组合）必须是这个表的主键或替代键。 （4）尽管主键是不能够包含空值的，但允许在外键中出现一个空值。这意味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。 （5）外键中的列的数目必须和被参照表的主键中的列的数目相同。 （6）外键中的列的数据类型必须和被参照表的主键中的列的数据类型对应相等。

104 4.CHECK完整性约束 【例5.14】创建表student，只包括学号和性别两列，性别只能是男或女。
create table student ( 学号 char(6) not null, 性别 char(1) not null check(性别 in ('男', '女')) );

105 例如，可以在条件中加入子查询，下面举个例子。
【例5.16】创建表student2，只包括学号和性别两列，并且确认性别列中的所有值都来源于student表的性别列中。 create table student2 ( 学号 char(6) not null, 性别 char(1) not null check( 性别 in ( select 性别 from student) ) );

106 Thanks