MySQL：基本概念，DDL语句，数据库约束，索引视图

SQL语句是对所有关系数据库都通用的命令语句，而JDBC API只是执行SQL语句的工具，JDBC允许对不同的平台、不同的数据库采用相同的编程接口来执行SQL语句。除标准的SQL语句之外，所有的数据库都会在标准SQL语句基础上进行扩展，增加一些额外的功能，这些额外的功能属于特定的数据库系统，不能在所有的数据库系统上都通用。因此，如果想让数据库应用程序可以跨数据库运行，则应该尽量邵永宁这些属于特定数据库的扩展。

1，关系数据库基本概念和MySQL基本命令

1.1，基本概念

数据库（DB）：仅仅是存放用户数据的地方。当用户访问、操作数据库中的数据时，就需要数据库管理系统的帮助。数据库管理系统（DBMS）和数据库通常称为数据库。

数据库管理系统（DBMS）：是所有数据的知识库，它负责管理数据的存储、安全、一致性、并发、回复和访问等操作。DBMS有一个数据字典，用于存储它所拥有的每个事物的相关信息，例如名字、结构、位置和类型，这种关于数据的数据也被称为元数据。

对于关系型数据库而言，最基本的数据存储单元就是数据表，因此可以简单地把数据库想象成大量数据表的集合。

数据表：是存储数据的逻辑单元，可以把数据表想象成由行和列组成的表格，其中每一行也被称为一条记录，每一列也被称为一个字段。为数据库建表时，通常需要指定该表包含多少列，每列的数据类型信息，无须指定该数据表包含多少行——因为数据库表的行是动态改变的，每行用于保存一条用户数据。除此之外，还应该为每个数据表指定一个特殊列，该特殊列的值可以唯一地标识此行记录，则该特殊的列被称为主键列。

MySQL：是一个小型关系型数据库，支持要求最苛刻的Web，电子商务和联机事务处理（OLTP）应用程序。它是一个全面集成、事务安全、符合ACID的数据库，具备全面提交、回滚、崩溃恢复和行级锁定功能。MySQL凭借其易用性、扩展力和性能，成为全球最受欢迎的开源数据库。

MySQL的优点：

• 可以处理拥有千万条记录的大型数据。
• 支持常见的SQL语句规范。
• 可移植性高，安全简单小巧。
• 良好的运行效率，有丰富的网络支持。
• 调试、管理，优化简单（相较于其他大型数据库）。

1.2，基本命令 

MySQL数据库的一个实例可通知包含多个数据库，MySQL常用基本命令：

show databases;    //查看当前实例下包含多少个数据库
create database [IF NOT EXISTS] 数据库名;    //创建新的数据库
drop database 数据库名;    //删除指定数据库
use 数据库名;    //进入指定数据库
show tables;    //查看当前数据库下包含多少个数据表
desc 表名;    //查看指定数据表的表结构（名称，类型）

数据库中的表的列名和别名不区分大小写，其余皆区分大小写。

1.3，MySQL默认数据库

在MySQL中，数据库也可以称为Schema（模式）。在安装MySQL后，默认有：information_schema、mysql、performance_schema和sys这几个数据库。

• information_schema：information_schema是信息数据库，是MySQL5.0新增的一个数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息。information_schema提供了访问数据库元数据的方式。元数据是关于数据的数据，例如数据库名或表名，列的数据类型，访问权限等。information_schema是一个虚拟数据库，有数个只读表，它们实际上是系统视图而不是基本表，因此，在OS上无法看到与之相关的任何文件。所以，也只有该数据库名在使用时，可以不区分大小写，而上心爱的mysql、performance_shcema和sys在使用时都需要区分大小写。
• mysql：这个是MySQL的核心数据库，主要存储着数据库的用户、权限设置、MySQL自己需要使用的控制和管理信息。它不可以被删除，如果对MySQL不是很了解，那么也不要轻易修改这个数据库里面的表信息。
• performance_schema：从MySQL5.5版本开始新增的一个数据库，主要用于手机数据库服务器性能数据。该库的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。这个功能从MySQL5.6.6开始，默认是开启的，其值为1或ON表示启用，为0或OFF表示关闭。该参数是静态的，写在my.cnf中，不能动态修改。
• sys：MysQL5.7提供了sys系统数据库。sys数据库结合了information_schema和performance_schema的相关数据，里面包含了一系列的的存储过程、自定义函数以及视图来帮助DBA快速了解系统的元数据信息，为DBA解决性能瓶颈提供了巨大帮助。sys数据库目前只包含一个表，表名为sys_config。
• test：用于测试，而在MySQL5.7及其之后的版本中去掉了该库。

2，SQL语句基础

SQL全程是Structured Query Language，也就是结构化查询语言。SQL是操作和检索关系数据库的标准语言，标准的SQL语句可用于操作任何关系数据库。

使用SQL语句，程序员和数据库管理员（DBA）可以完成如下任务：

• 在数据库中检索信息。
• 对数据库的信息进行更新。
• 改变数据库的信息进行更新。
• 更改系统的安全设置。
• 增加或回收用户对数据库、表的许可权限。

一般程序员可以管理前3个任务，后面2个任务通常由DBA来完成。

标准的SQL语句通常可分为如下几种类型：

• 查询语句：主要由select关键字完成，查询语句是SQL语句中最复杂、功能最丰富的语句。
• DML（数据操作语言）：主要由insert、update和delete三个关键字完成。
• DDL（数据定义语言）：主要由create、alter、drop和truncate四个关键字完成。
• DCL（数据控制语言）：主要由grant和revoke两个关键字完成。
• 事务控制语句：主要由commit、rollback和savepoint三个关键字完成。

SQL命令中也需要使用标识符，标识符可用于定义表名、列名，也可用于定义表名、列名，也可用于定义变量等，使用规则如下：

• 标识符通常必须以字母开头。
• 标识符包括字母、数字和三个特殊字符（#_$）。
• 不要使用当前数据库系统的关键字、保留字，通常建议使用多个单词连缀而成，单词之间以_间隔。
• 同一个模式下的对象不应该同名，这里的模式指的是外模式。

3，DDL语句

DDL语句是操作数据库对象的语句，包括创建（create）、删除（drop）和修改（update）数据库对象。常见的数据库对象：

对象名称	对应关键字	描述
表	table	表是存储数据的逻辑单元，以行和列的形式存在；列就是字段，行就是记录。
数据字典		就是系统表，存放数据库相关信息的表。系统表里的数据通常由数据库系统维护，程序员通常应该手动修改系统表及系统表数据，只可查查看系统表数据。
约束	constraint	在执行数据校验的规则，用于保证数据完整性规则。
视图	view	一个或者多个数据表里数据的逻辑显示。视图并不存储数据。
索引	index	用于提高查询性能，相当于书的目录。
函数	function	用于完成一次特定的计算，具有一个返回值。
存储过程	procedure	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境。
触发器	trigger	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成响应的处理。

3.1，创建表

创建表-标准语句：

create table [模式名.]表名(
    # 可以定义多个列定义
    columnName1 datatype [default expr],
)

​​​​圆括号里可以定义多个列定义，每个列定义以英文逗号（,）隔开，最后一个列定义不需要使用英文逗号，而是直接以括号结束。

建立数据表只是建立表结构，就是指定该数据表有多少列，每列的数据类型，所以建表语句的重点就是圆括号里的列定义，列定义由列名、列类型和可选的默认值组成。

列定义有点类似于Java里的变量定义，与变量定义不同的是，列定义时将列名放在前面，列类型放在后面。如果要指定列的默认值，则使用default关键字，而不是等号（=）。

create table test(
    #整形通常用int
    test_id int,
    #小数点数
    test_price decimal,
    #普通长度文本，使用default指定其默认值
    test_name varchar(255) default 'xxx',
    #大文本类型
    test_desc test,
    #图片
    test_img blob,
    #日期
    test_date datetime
)

通过这种方式建立的数据表只是一个空表，该表没有任何数据。如果使用子查询建表，则可以在建表的同时插入数据。子查询建表语法：

create table [模式名.]表明 [column[, column...]]
as subquery;

create table test
as 
select * from user_inf;

3.2，MySQL支持的列类型

列类型	说明
tinyint/smaillint/mediumint int(integer)/bigint	1字节/2字节/3字节/4字节/8字节整数，又可分为有符号和无符号两种。这些整数类型的区别仅仅是表数范围不同。
float/double	单精度、双精度浮点类型。
decimal(dec)	精确小数类型，相当于float和double不会产生进度丢失的问题。
date	日期类型，不能保存时间。把java.util.Date对象保存进date列时，时间部分将会丢失。
time	时间类型，不能保存日期。把java.utill.Date对象保存进time列时，日期部分将会丢失。
datetime	日期、时间类型。
timestamp	时间戳类型。
year	年类型，仅仅保存时间的年份。
char	定长字符串类型。
varchar	可变长度字符串。
binary	定长二进制字符串类型。
varbinary	可变长度的二进制字符串类型，它以二进制形式保存字符串。
tinyblob/blob mediumblob/longblob	1字节/2字节/3字节/4字节的二进制大对象，可用于存储图片、音乐等二进制数据，分别可存储：255B/64KB/16MB/64GB的大小。
tinytext/text mediumtext/longtext	1字节/2字节/3字节/4字节的文本对象，可用于存储超长长度的字符串数据，分别可存储：255B/64KB/16MB/64GB大小的文本。
enum('value1','value2')	枚举类型，该列的值只能是enum后括号里多个值的其中之一。
set('value1','value2')	集合类型，该列的值可以是set后括号里多个值的其中几个。

• 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用datetime，而不要使用timestamp，因为timestamp表示的日期范围比datetime短得多。
• 如果记录的日期需要让不同时区的用户使用，那么最好使用timestamp，因为日期类型中只有它能够和实际时区相对应。

【问题】char 和 varchar 的区别？

【答案】

• char是一种固定长度的数据类型，需要事先指定长度。例如，如果定义一个char(10)的列，则无论该列中实际存储多少字符，它总是会占用10个字符的存储空间。如果存储的字符串长度不足10个字符，则会在其后面补空格，而如果字符串长度超过10个字符，则会被截断。
• varchar是一种可变长度的数据类型，不需要事先指定长度。例如，如果定义一个varchar(10)的列，则它可以存储0到10个字符长度的字符串，实际占用的存储空间根据字符串的长度而变化。与char不同，varchar不会在其后面补空格或截断字符串，因此对于长度不确定的字符串，使用varchar会更加灵活和节省存储空间。

【问题】在MySQL中，varchar和char的区别是什么？varchar(20)中的20代表什么？

【答案】在MySQL中，varchar和varchar(20)都是用来定义可变长度字符串类型的数据类型。它们的区别在于varchar(20)指定了该字段能够存储的最大字符数为20个字符，而varchar则默认为能够存储的最大字符数为65535个字符（MySQL 5.0.3版本之前是255个字符）。

3.3，修改表结构

修改表结构使用：alter table。修改表结构包括增加列定义、修改列定义、删除列、重命名列等操作。

增加列定义的语法如下：

alter table 表名
add(
    #可以有多个列定义
    column_name1 datatype [default expr],
);

如果只是增加一列，则可以省略圆括号，仅在add后紧跟一个列定义即可。另外，在SQL中字符串是通过单引号引起的。

#为test增加一个字段
alter table test
add test_id int;
#为test增加多个字段
alter table test
add(
    aaa varchar(255) default 'xxx',
    bbb varchar(255)
);

PS：如果数据表中已有数据记录，除非给新增的列指定了默认值，否则新增的数据列不可指定非空约束，因为那些已有的记录在新增列上肯定是空。

修改列定义的语法如下：

alter table 表名
modify column_name datatype [default expr] [first|after col_name];

将test表的bbb列修改成int类型：

alter table test
modify bbb int;

如果数据表已有数据记录，这修改列定义非常容易失败，因为有可能修改的列定义规则与原有的数据记录不符合。如果修改数据列的默认值，则只会对以后插入操作有作用，对以前存在的数据不会有任何影响。 

PS：虽然MySQL的一个modify命令不支持一次修改多个列定义，但其他数据库如Oracle支持一个modify命令修改多个列定义，一个modify命令修改多个列定义的语法和一个add命令增加多个列定义的语法非常相似，也需要使用圆括号把多个列定义括起来。如果需要让MySQL支持一次修改多个列定义，则可在alter table后使用多个modify命令。

删除列定义的语法如下：

alter table 表名
drop column_name;

删除test表中的bbb字段：

alter table test
drop bbb;

从数据表删除列定义通常是可以成功，删除列定义时将从每行中删除该列的数据，并释放该列在数据块中占用的空间。所以删除大表中的字段需要比较长的时间，因为还需要回收表空间。

重命名数据表的语法格式如下：

alter table 表名
rename to 新表名;

将test表名改为ysy：

alter table test
rename to ysy;

重命名列名的语法格式如下：

alter table 表名
change old_column_name new_column_name type [default expr] [first|after col_name]

对比change和modify两个选项，不难发现：change选项比modify选项多了一个列名，因为change选项可以改变列名，所以它需要两个列名。一般而言，如果不需要改变列名，使用alter table的modify选项即可，只有当需要修改列名时才会使用change选项。将ysy的aaa字段修改为ccc字段的语句如下：

alter table ysy
change aaa ccc int;

3.4，删除表

删除表的语法格式如下：

drop table 表名;

删除数据表ysy:

drop table ysy;

删除数据表的效果如下：

• 表结构被删除，表对象不再存在。
• 表里的所有数据也被删除。
• 该表所有相关的索引、约束也被删除。

3.5，truncate

对于大部分数据库而言，truncate都被当成DDL处理，truncate被称为“截断”某个表——它的作用是删除该表里的全部数据，但保留表结构。相对于DML里的delete命令而言，truncate的速度要快得多，而且truncate不像delete可以删除指定的记录，truncate只能一次性删除整个表的全部记录。truncate命令语法如下：

truncate 表名;

MySQL对truncate的处理比较特殊——如果使用非InnoDB存储机制，truncate比delete速度要快；如果使用InnoDB存储机制，在MySQL5.0.3之前，truncate和delete完全一样，在5.0.3之后，truncate table比delete效率高，但如果该表被外键约束所参照，truncate又变为delete操作。在5.0.3之后，快速trunate总是可用，即比delete性能要好。

4，数据库约束

所有的关系数据库都支持对数据表使用约束，通过约束可以更好地保证数据表里数据的完整性。约束是在表上强制执行的数据校验规则，约束主要用于保证数据库里数据的完整性。除此之外，当表中数据存在相互依赖性时，可以保护相关的数据不被删除。大部分数据库支持下面5种完整性约束：

• NOT NULL：非空约束，指定某列不能为空。
• UNIQUE：唯一约束，指定某列或者几列组合不能重复。
• PRIMARY KEY：主键，指定该列的值可以唯一地标识该条记录。
• FOREIGN KEY：外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性。
• CHECK：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。

虽然大部分数据库都支持上面5中约束，但MySQL不支持CHECK约束，虽然MySQL的SQL语句也可以使用CHECK约束，但这个CHECK约束不会有任何作用。

虽然约束的作用只是用于保证数据表里数据的完整性，但约束也是数据库对象，并被存储在系统表中，也拥有自己的名字。根据约束对数据列的限制，约束分为如下两类：

• 单列约束：每个约束只约束一列。
• 多列约束：每个约束可以约束多个数据列。

为数据表指定约束有如下两个时机：

• 建表的同时为相应的数据列指定约束。
• 建表后创建，以修改表的方式来增加约束。

4.1，NOT NULL约束

非空约束用于确保指定列不允许为空，非空约束是比较特殊的约束，它只能作为列级约束使用，只能使用列级约束语法定义。SQL中的null不区分大小写，SQL中的null有如下特征：

• 所有数据类型的值都可以是null，包括int、float、boolean等数据类型。
• 与Java类似的是，空字符串不等于null，0也不等于null。

在列定义时增加not null指定非空约束： 

create table test(
    #建立非空约束，这意味着test_id不可以为null
    test_id int not null,
    #MySQL的非空约束不能指名字
    test_name varchar(255) default 'ysy' not null,
    #下面列可以为空，默认就是可以为空
    test_gender varchar(2) null
);

在使用alter table修改表时增加或删除非空约束： 

#增加非空约束
alter table test modify test_gender varchar(2) not null;
#取消非空约束
alter table test modify test_name varchar(255) null;
#取消非空约束，并指定默认值
alter table test modify test_name varchar(255) default 'abc' null;

4.2，UNIQUE约束

唯一约束用于保证指定列或指定列组合不允许出现重复值。虽然唯一性约束不可以出现重复值，但是可以出现多个null值（因为在数据库中null不等于null)。

同一个表内可建多个唯一约束，唯一约束也可由多列组合而成。当为某列创建唯一约束时，MySQL会为该列相应地创建唯一索引。如果不给唯一约束起名，该唯一约束默认与列名相同。

唯一约束既可以使用列级约束语法建立，也可以使用表级约束语法建立。如果需要为多列建组合约束，或者需要为唯一约束指定约束名，则只能用表级约束语法。

当建立唯一约束时，MySQL在唯一约束所在列或列组合上建立对应的唯一索引。

单列：使用列级约束语法建立唯一约束非常简单，只要简单地在列定义后增加unique关键字即可。SQL语句如下：

create table unique_test(
    test_id int not null,
    test_name varchar(255) unique
);

多列：如果需要为多列组合建立唯一约束，或者想自行指定约束名，则需要使用表级约束语法。表级约束语法格式如下：

[constraint 约束名] 约束定义

上面的表级约束语法格式既可以放在create table语句中与列定义并列，也可放在alter table语句中使用add关键字来添加约束。SQL语句如下：

#建表时创建唯一约束，使用表级约束语法建立约束
create table unique_test2(
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    #使用表级语法建立唯一约束
    unique(test_name),
    #使用表级语法建立唯一索引，而且指定约束名
    constraint test2_uk unique(test_pass)
);

上面的建表语句为test_name、test_pass分别建立了唯一约束，这意味着这两列都不能出现重复值。除此之外，还可以为这两列组合建立唯一约束，这意味着这两列的组合值不能出现重复值。SQL语句如下：

#建表时创建唯一约束，使用表级约束语法建立约束
create table unique_test3(
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    #使用表级语法建立唯一索引，而且指定约束名
    constraint test3_uk unique(test_name,test_pass)
);

也可以在修改表结构时，使用add关键字添加唯一约束：

alter table unique_test3
add unique(test_name,test_pass);

还可以在修改表时使用modify关键字，为单列采用列级约束语法来增加唯一约束：

alter table unique_test3
modify test_name varchar(255) unique;

删除约束：对于大部分数据库而言，删除约束都是在alter table语句后使用“drop constraint 约束名”语法来完成的，但MySQL并不使用这种方式，而是使用“drop index 约束名”的方式来删除约束。

alter table unique_test3
drop index test3_uk;

4.3，PRIMARY KEY约束

主键约束相当于非空约束和唯一约束，即主键约束的列既不允许出现重复值，也不允许出现null值。如果对多列组合建立主键约束，则多列里包含的每一列都不能为空，但只要求这些列组合不能重复主键列的值不能重复。主键列的值可用于唯一标识表中的一条记录。

每一个表中最多允许有一个主键，但这个主键约束可由多个数据列组合而成，主键是表中唯一确定一行记录的字段或字段组合。

建立主键约束时即可使用列级约束语法，也可以使用表级约束语法。如果需要对多个字段建立组合主键约束，则只能使用表级约束语法。使用表级约束语法来建立约束时，可以为该约束指定约束名。但不管用户是否为该主键约束指定约束名，MySQL总是将所有的主键约束命名为PRIMARY。

MySQL允许在建立主键约束时为该约束名，但这个名字没有任何作用，这是为了保持和标准SQL的兼容性。大部分数据库都允许自行指定主键约束的名字，而且一旦指定了主键约束名，则该约束名都是用户指定的名字。

当创建主键约束时，MySQL在主键约束所在的列或列组合上建立对应的唯一索引。创建主键约束的语法和创建唯一约束的语法非常像，一样允许使用列级约束语法为单独的数据列创建主键，如果需要为多列组合建立主键约束或者需要为主键约束名，则应该使用表级约束语法来建立主键约束。

#建表时创建主键约束，使用列级约束语法：
create table primary_test(
    #建立主键约束
    test_id int primary key,
    test_name varchar(255)
);

#建表时创建主键约束，使用表级约束语法
create table primary_test2(
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    constraint test2_pk primary key(test_id)
);

#建表时创建主键约束，以多列组合主键，只能使用表级约束
create table primary_test3(
    test_name varchar(255),
    test_pass varchar(255),
    primary key(test_name,test_pass)
);

#删除主键约束
alter table primary_test3
drop primary key;

#使用表级约束语法增加主键语法
alter table primary_test3
add primary key(test_name,test_pass);

#使用列级约束语法增加主键约束
alter table primary_test3
modify test_name varchar(255) primary key;

#建立主键约束，使用自增长
create table primary_table4(
    test_id int auto_increment primary key,
    test_name varchar(255),
    test_pass varchar(255)
);

4.4，FOREIGN KEY约束

外键约束主要用于保证一个或两个数据表之间的参照完整性，外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。外键确保了相关的两个字段的参照关系：子表外键列的值必须在主表被参照列的值范围之内，或者空。

当主表的记录被从表记录参照时，主表记录不允许被删除，必须先把从表里的参照该记录的所有记录全部删除后，才可以删除主表的该记录。还有一种方式，删除主表记录时级联删除从表中所有参照该记录的从表记录。

从表外键参照的只能是主表主键列或者唯一键列，这样才可以保证从表记录可以准确定位到被参照的主表记录。同一个表内可以拥有多个外键。建立外键约束，MySQL也会为该列建立索引。

外键约束通常用于定义另两个实体之间的一对多、一对一的关联关系。对于一对多的关联关系，通常在多的一端增加外键列，例如老师-学生。为了建立他们之间的关联关系，可以在学生表中增加一个外键列，增加外键列的表被称为从表，只要为外键列增加唯一约束就可表示一对一的关联关系。对于多对多的关联关系，则需要额外增加一个连接表来记录它们的关联关系。

建立外键约束同样可以采用列级约束语法和表级约束语法。如果仅对单独的数据列建立外键约束，则使用列级约束语法即可；如果需要对多列组合创建外键约束，或者需要为外键约束指定名字，则必须使用表级约束语法。

采用列级约束语法建立外键约束直接使用references关键字，references指定该列参照哪个主表，以及参照主表的哪一列。

#为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int references teacher_table(teacher_id)
);

虽然MySQL支持使用列级约束语法来建立外键约束，但这种列级约束语法建立的外键不会生效，MySQL提供这种列级约束语法仅仅是为了和标准SQL保持良好的兼容性。因此，如果要使MySQL中的外键约束生效，则应使用表级约束语法：

#为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table1(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table1(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    foreign key(java_teacher) references teacher_table1(teacher_id)
);

使用表级约束语法，则需要使用foreign key来指定本表的外键列，并使用references来指定参照哪个主表的哪个数据列。使用表级约束语法可以为外键约束指定约束名，如果创建外键约束时没有指定约束名，则MySQL会为该外键约束命名为table_name_ibfk_n，其中table_name是从表的表名，而n是从1开始的整数。

如果需要显式指定外键约束的名字，则可使用constraint来指定名字。

#为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table2(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table2(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    constraint student_teacher_fk foreign key(java_teacher) references teacher_table2(teacher_id)
);

如果需要建立多列组合的外键约束，则必须使用表级约束语法。

#为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table3(
    teacher_name varchar(255),
    teacher_pass varchar(255),
    primary key(teacher_name,teacher_pass)
);
create table student_table3(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher_name varchar(255),
    java_teacher_pass varchar(255),
    foreign key(java_teacher_name, java_teacher_pass) references teacher_table3(teacher_name,teacher_pass)
);

如果想定义当删除主表记录时，从表记录也会随之删除，则需要在建立外键约束后添加on delete cascade或添加on delete set null，第一种是删除主表记录时，把参照该主表记录的从表记录全部级联删除；第二种是指定当前删除主表记录时，把参照该主表记录的从表记录设为null。 

create table teacher_table4(
    teacher_id int auto_increment,
    teacehr_name varchar(255),
    refer_id int,
    foreign key(refer_id) references foreign_test(foreign_id)
);
create table student_table4(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    foreign key(java_teacher) references teacher_table4(teacher_id)on delete cascade
);

删除外键约束：

alter table student_table3 
drop foreign key student_table3_ibfk1;

增加外键约束：

alter table student_table3 
add foreign key(java_teacher_name,java_teacher_pass) 
references teacher_table3(teacher_name,teacher_pass);

自关联：外键约束不仅可以参照其他表，而且可以参照自身，这种参照自身的情况通常被称为自关联。例如，使用一个表保存某个公司的所有员工记录，员工之间有部门经理和普通员工之分，部门经理和普通员工之间存在一对多的关联关系，但他们都是保存在同一个数据表里的记录。

create table foreign_test(
    foreign_id int auto_increment primary key,
    foreign_name varchar(255),
    refer_id int,
    foreign key(refer_id) references foreign_test(foreign_id)
);

4.5，CHECK约束

当前版本的MySQL支持建表时指定CHECK约束，但这个CHECK约束不会有任何作用。建立CHECK约束的语法很简单，只要在建表的列定义后增加check即可：

create table check_test(
    emp_id int auto_increment,
    emp_name varchar(255),
    emp_salary decimal,
    primary key(emp_id),
    check(emp_salary>0)
);

MySQL作为一个开源、免费的数据库系统，对有些功能的支持确实不好，比如CHECK等。如果希望MySQL创建的数据表有CHECK约束，甚至有更复杂的完整性约束，则可借助于触发器机制。 

5，索引

索引是存放在模式中的一个数据库对象，虽然索引总是从属于数据表，但它也和数据表一样属于数据库对象。创建索引的唯一作用就是加速对表的查询，索引通过使用快速路径访问方法来快速定位数据，从而减少了磁盘的I/O。索引作为数据库对象，在数据字典中独立存放，但不能独立存在，必须从属于某个表。

创建索引的方式有两种：

• 自动：当在表上定义主键约束、唯一约束和外键约束时，系统会为该数据列自动创建对应的索引。
• 手动：用户可以通过create index...语句来创建索引。

删除索引的方式有了两种：

• 自动：数据表被删除时，该表上的索引自动被删除。
• 手动：用户可以通过drop index...语句来删除指定数据表上的指定索引。

创建索引的语法格式如下：

create index index_name
on table_name(column[,column]...);

下面索引将会提高对employees表基于last_name字段的查询速度：

create index emp_last_name_idx
on employees(last_name);

对多列建立索引的语法格式如下：

create index emp_last_name_idx2
on employees(first_name,last_name);

MySQL中删除索引需要指定表，采用如下语法格式：

drop index 索引名 on 表名;

如下SQL语句删除了employees表上的emp_last_name_idx2索引：

drop index emp_last_name_idx2
on employees;

有些数据库删除索引时无须指定表明，因为它们要求建立索引时每个索引都有唯一一个名字，所以无须指定表名，例如Oracle就采用这种策略。但MySQL只要求同一个表内的索引不能同名，所以删除索引时必须指定表名。

索引的好处是可以加速查询。但索引也有如下两个坏处：

• 与书的目录类似，当数据库中的记录被添加、删除、修改时，数据库系统需要维护索引，因此有一定的系统开销。
• 存储索引信息需要一定的磁盘空间。

6，视图

视图看上去非常像一个数据表，但它不是数据表，因为它不能存储数据。视图只是一个或多个数据表的逻辑显示。使用视图有如下几个好处：

• 可以限制对数据的访问。
• 可以使复杂的查询变得简单。
• 提供了数据的独立性。
• 提供了对相同数据的不同显示。

因为视图只是数据表中数据的逻辑显示——也就是一个查询结果，所以创建视图就是建立视图名和查询语句的关联。创建视图的语法如下：

create or replace view 视图名 as subquery;

通过建立视图的语法规则不难看出，所谓视图的本质，其实就是一条被命名的SQL查询语句。一旦建立视图以后，使用该视图与使用数据表就没有什么区别了，但通常只是查询视图数据，不会修改视图里的数据，因为视图本身没有存储数据。

#创建一个简单的视图
create or replace view view_test
as 
select teacher_name, teacher_pass from teacher_table;

通常不推荐直接改变视图的数据，因为视图并不存储数据，它只是相当于一条命名的查询语句而已，为了强制不允许改变视图的数据，MySQL允许在创建视图时with check option子句，使用该子句创建的视图不允许修改：

create or replace view view_test
as
select teacher_name from teacher_table
with check option;

删除视图使用如下语句：

drop view 视图名;

drop view view_test;