MySQL --- 基本语法2

参考文章：http://www.voidme.com/mysql

一.MySQL表的约束和索引

1.主键PRIMARY KEY

（1）也称主键约束，它是一个列或者列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。
（2）选取设置主键约束的字段
①主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符。主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键。
②主键应遵守以下规则：

a.每个表只能定义一个主键。
b.主键值必须唯一标识表中的每一行，且不能为NULL，即表中不可能存在两行数据有相同的主键值。这是唯一性原则。
c.一个列名只能在复合主键列表中出现一次。
d.复合主键不能包含不必要的多余列。当把复合主键的某一列删除后，如果剩下的列构成的主键仍然满足唯一性原则，那么这个复合主键是不正确的。这是最小化原则。

（3）在创建表时设置主键约束
①在定义列的同时指定主键：

<字段名> <数据类型> primary key [默认值];

②在定义完所有列之后，指定主键的语法格式：

[constraint <约束名>] primary key [字段名];

（4）在创建表时设置复合主键

primary key [字段1,字段2,...,字段n];

（5）在修改表时添加主键约束

alter table <数据表名> add primary key (<列名>);

2.外键约束FOREIGN KEY

（1）外键约束用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。
（2）外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。
（3）外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。
（4）外键的主要作用是保持数据一致性、完整性。

a.主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
b.从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

（5）选取设置MySQL外键约束的字段
定义一个外键时，需要遵守以下规则：

a.父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
b.必须为父表定义主键。
c.主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
d.在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
e.外键中列的数目必须和父表的主键中列的数目相同。
f.外键中列的数据类型必须和父表主键中对应列的数据类型相同。

（6）在创建表时设置外键约束

[constraint <外键名>] foreign key 字段名 [,字段名2,...] references <主表名> 主键列1 [,主键列2,...];

a.外键名：定义的外键约束的名称，一个表中不能有相同名称的外键。
b.字段名：表示子表需要添加外键约束的字段列。
c.主表名：被子表外键所依赖的表的名称。
d.主键列：主表中定义的主键列或者列组合。

关联指的是关系数据库中，相关表之间的联系。
它是通过相同的属性或属性组来表示的。
子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时会出现错误。

（7）在修改表时添加外键约束

alter table <数据表名> add constraint <索引名> foreign key (<列名>) references <主表名> (<列名>)；

（8）删除外键约束
外键一旦删除，就会解除主表和从表间的关联关系。

alter table <表名> drop foreign key <外键约束名>;

3.唯一约束UNIQUE KEY

（1）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
（2）在创建表时设置唯一约束

<字段名> <数据类型> unique;

unique与primary key的区别：一个表可以有多个字段声明为unique，但只能有一个primary key声明。声明primary key的列不允许有空值，但是声明为unique的字段允许空值的存在。

（3）在修改表时添加唯一约束

alter table <数据表名> add constraint <唯一约束名> unique (<列名>);

（4）删除唯一约束

alter table <表名> drop index <唯一约束名>;

4.检查约束CHECK

（1）选取设置检查约束的字段

check <表达式>;

a.<表达式>指的是SQL表达式，用于指定需要检查的限定条件。
b.若将check约束子句置于表中某个列的定义之后，则这种约束也称为基于列的check约束。
c.在更新表数据时，系统会检查更新后的数据行是否满足check约束中的限定条件。MySQL可以使用简单的表达式来实现check约束，也允许使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。
d.若将check约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的check约束。该约束可以同时对表中多个列设置限定条件。

（2）在创建表时添加检查约束

check (<检查约束>);

（3）在修改表时添加检查约束

alter table <表名> add constraint <检查约束名> check (<检查约束>);

（4）删除检查约束

alter table <数据表名> drop constraint <检查约束名>;

5.默认值DEFAULT

（1）默认值的完整称呼是默认值约束。MySQL默认值约束用来指定某列的默认值。
（2）在创建表时设置默认值约束

<字段名> <数据类型> default <默认值>;

（3）在修改表时添加默认值约束

alter table <数据表名> change column <字段名> <字段名> <数据类型> default <默认值>;

（4）删除默认值约束

alter table <数据表名> change column <字段名> <字段名> <数据类型> default null;

6.非空约束NOT NULL

（1）在表中某个列的定义后加上关键字not null作为限定词，来约束该列的取值不能为空。
（2）非空约束指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。
（3）在创建表时设置非空约束

<字段名> <数据类型> not null;

（4）在修改表时添加非空约束

alter table <数据表名> change column <字段名> <字段名> <数据类型> not null;

（5）删除非空约束

alter table <数据表名> change column <字段名> <字段名> <数据类型> null;

7.查看表中的约束

show create table <数据表名>;

8.索引简介

（1）索引是MySQL数据库中的重要对象之一，用于快速找出某个列中有某一特定值的行。
（2）为什么要使用索引
①索引是MySQL中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。
②索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。
③访问数据库表的行数据的两种方式：

a.顺序访问：

• 顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。
• 这种方式实现比较简单，但是当表中有大量数据时，效率非常低下，会影响数据库的处理性能。

b.索引访问：

• 索引访问是通过遍历索引来直接访问表中记录行的方式。
• 使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应行的位置，从而快捷地查找到数据。
• 索引存储了指定列数据值的指针，根据指定的顺序对这些指针排序。
• 扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

（3）索引的分类
①索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。
②根据存储方式不同，MySQL中常用的索引在物理上分为以下两类：

a.B-树索引：

• 又称BTREE索引，目前大部分的索引都是采用B-树索引来存储的。它包含的组件主要有：叶子节点、分支节点、根节点。
• 基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。
• B-树索引必须遵循左边前缀原则，要考虑几点约束：
  i.查询必须从索引的最左边的列开始。
  ii.查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
  iii.存储引擎不能使用索引中范围条件右边的列。

b.哈希索引

• 哈希，就是把任意长度的输入（又叫做预映射pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。
• MySQL仅有MEMORY存储引擎和HEAP存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B- 树索引和 HASH 索引，且将 HASH 当成默认索引。
• 哈希索引是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点就是访问速度快，但也存在一些缺点：
  i.MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B- 树索引来说，建立哈希索引会耗费更多的时间。
  ii.不能使用哈希索引排序。
  iii.HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
  iv.HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

③根据索引的具体用途，MySQL中的索引在逻辑上分为以下五类：

a.普通索引：
最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是INDEX或KEY。

b.唯一性索引：
不允许索引列具有相同索引值的索引。
如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一性索引。

c.主键索引：
一种唯一性索引，即不允许值重复或值为空，并且每个表只能有一个主键。
主键可以在创建表时指定，也可以通过修改表的方式添加，必须指定关键字PRIMARY KEY。

d.空间索引：
主要用于地理空间数据类型GEOMETRY。

e.全文索引：
只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。

④索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

a.单列索引：索引只包含原表的一个列。
b.组合索引也称为复合索引或多列索引，相对于单列索引，组合索引时将原表的多个列共同组成一个索引。
c.一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

（4）索引的使用原则和注意事项
①过多地使用索引会造成以下弊端：

a.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
b.除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
c.当对表中的数据进行增加、删除、修改时，索引也要动态地维护，这样就降低了数据的维护速度。

②建立索引时应遵循以下原则：

a.在经常需要搜索的列上建立索引，可以加快搜索速度。
b.在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。
c.在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度。
d.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。
e.在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。
f.在经常使用WHERE子句的列上创建索引，加快条件的判断速度。

③不适合创建索引的环境：

a.对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。
b.对于那些只有很少数据值的列也不应该创建索引。因为这些列的取值很少，例如人事表的性别列。查询结果集的数据行占了表中数据行的很大比例，增加索引并不能明显加快检索速度。
c.对于那些定义为 TEXT、IMAGE 和 BIT 数据类型的列不应该创建索引。因为这些列的数据量要么相当大，要么取值很少。
d.当修改性能远远大于检索性能时，不应该创建索引。因为修改性能和检索性能是互相矛盾的。当创建索引时，会提高检索性能，降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

9.创建索引CREATE INDEX

（1）创建索引
①方法一：
可以使用专门用于创建索引的create index 语句在一个已有的表上创建索引，但该语句不能创建主键。

create <索引名> on <表名> (<列名> [<长度>] [ASC | DESC]);

a.索引名：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
b.表名：指定要创建索引的表名。
c.列名：指定要创建索引的列名。通常可以考虑查询语句中在JOIN子句和WHERE子句里经常出现的列作为索引列。
d.长度：可选项。指定使用列前的length个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
e.ASC | DESC：可选项。ASC：索引按照升序来排列；DESC：索引按照降序来排列，默认为ASC。

②方法二：
索引也可以在创建表的同时创建。
a.在create table语句中添加此语句，表示在创建新表的同时创建该表的主键。

constraint primary key [索引类型] (<列名>,...)

b.在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的索引。

key | index [<索引名>] [<索引类型>] (<列名>,...)

c.在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。

unique [index | key] [<索引名>] [<索引类型>] (<列名>,...)

d.在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的外键。

foreign key <索引名> <列名>

备注：在使用 CREATE TABLE 语句定义列选项的时候，可以通过直接在某个列定义后面添加 PRIMARY KEY的方式创建主键。而当主键是由多个列组成的多列索引时，则不能使用这种方法，只能用在语句的最后加上一个 PRIMARY KRY(<列名>，…)子句的方式来实现。

③方法三：
alter table语句。可以在一个已有的表上创建索引。
a.在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加索引。

add index  [<索引名>] [<索引类型>] (<列名>,…)

b.在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加主键。

add primary key  [<索引类型>] (<列名>,…)

c.在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。

add unique [index | key] [<索引名>] [<索引类型>] (<列名>,…)

d.在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加外键。

add foreign key [<索引名>] (<列名>,…)

（2）查看索引

show index from <表名> [from <数据库名>];

a.表名：要显示索引的表；
b.数据库名：要显示的表所在的数据库。

该语句会返回一张结果表，该表有如下几个字段：
a.Table：表的名称。
b.Non_unique：用于显示该索引是否是唯一索引。若不是唯一索引，则该列的值显示为1；若是唯一索引，则该列的值显示为0。
c.Key_name：索引的名称。
d.Seq_in_index：索引中的列序列号，从1开始计数。
e.Column_name：列名称。
f.Collation：显示列以何种顺序存储在索引中。在MySQL中，升序显示值“A”，若显示为NULL，则表示无分类。
g.Cardinality：显示索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
h.Sub_part：若列只是被部分编入索引，则为被编入索引的字符的数目。若整列被编入索引，则为NULL。
i.Packed：指示关键字如何被压缩。若没有被压缩，则为NULL。
j.NULL：用于显示索引列中是否包含NULL。若列含有NULL，则显示为YES。若没有则显示为NO。
k.Index_type：显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
l.Comment：显示评注。

10.修改和删除索引DROP INDEX

（1）方法一：

drop index <索引名> on <表名>;

a.索引名：要删除的索引名。
b.表名：指定该索引所在的表名。

（2）方法二：

将alter table语句的语法中部分指定为以下子句中的某一项。
DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
DROP INDEX index_name：表示删除名称为 index_name 的索引。
DROP FOREIGN KEY fk_symbol：表示删除外键。

如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。