0102 MySQL05

1.约束

1.约束（constraint）：在创建表时，可以给表中的字段加上一些约束，保证表中数据的完整性，有效性

---

常见的约束？

非空约束：not null

唯一性约束：unique

主键约束：primary key（简称PK）

外键约束：foreign key（简称FK）

检查约束：check（MySQL不支持，Oracle支持）

补充：xxx.sql这种文件称为sql脚本文件，执行该脚本文件时，该文件中的所有SQL语句会全部执行，批量的执行SQL语句，可以使用sql脚本文件

怎么执行？mysql>source D:\xxxxx\xxx.sql

---

1.非空约束

not null约束的字段不能为null

drop table if exists t_vip;

create table t_vip(

        id int,

        name varchar(255) not null

);

insert into t_vip(id,name) values(1,'zhangsan');

insert into t_vip(id,name) values(2,'lisi');

insert into t_vip(id) values(3);//报错，name字段没有默认值

---

2.唯一性约束

1.unique约束的字段不能重复

drop table if exists t_vip;

create table t_vip(

        id int,

        name varchar(255) unique,

        email varchar(255)

);

insert into t_vip(id,name,email) values(1,'zhangsan','[e-mail]');

insert into t_vip(id,name,email) values(2,'lisi','[e-mail]');

insert into t_vip(id,name,email) values(3,'zhangsan','[e-mail]');//报错，name值重复

insert into t_vip(id) values(3);//可以为null

2.新需求：name和email两个字段联合起来具有唯一性？

drop table if exists t_vip;

create table t_vip(

        id int,

        name varchar(255) unique,

        email varchar(255) unique

);//不符合需求，表示各自唯一

drop table if exists t_vip;

create table t_vip(

        id int,

        name varchar(255) ,

        email varchar(255) ,

        unique (name,email)

);//两个字段联合起来唯一

测试：

insert into t_vip(id,name,email) values(1,'zhangsan','[e-mail]');

insert into t_vip(id,name,email) values(2,'zhangsan','[e-mail]');

//不报错

约束直接添加到列后面：列级约束

约束没有添加到列后面：表级约束（多个字段联合起来唯一时使用）

not null只有列级约束，没有表级约束

3.unique和not null可以联合吗？

drop table if exists t_vip;

create table t_vip(

        id int,

        name varchar(255) not null unique

);//name为主键

//在MySQL中，如果一个字段同时被not null和unique约束时，该字段自动变成主键字段

（Oracle中不适用）

---

3.主键约束

1.primary key相关术语？

主键约束：一种约束

主键字段：字段添上主键约束，这样的字段

主键值：主键字段中的每一个值

2.什么是主键？有什么用？

主键值是每一行记录的唯一标识，是每一行记录的身份证号

任何一张表都应该有主键，没有主键的表无效

特征：not null + unique(不能是空，也不能重复)

3.给表添加主键约束？

drop table if exists t_vip;

create table t_vip(

        id int primary key,

        name varchar(255)

);

insert into t_vip(id,name) values(1,'zhangsan');

insert into t_vip(id,name) values(1,'lisi');//报错，主键重复

//主键可以使用表级约束

一个字段做主键：单一主键

字段联合做主键：复合主键（在实际开发中不建议使用）

4.主键约束数量？

drop table if exists t_vip;

create table t_vip(

        id int primary key,

        name varchar(255) primary key

);//报错，一张表只能添加一个，即主键只能有一个

5.主键值建议使用类型？

int,bigint,char等类型

不建议使用varchar做主键，一般是数字，且定长

6.自然主键和业务主键？

自然主键：主键值是一个自然数，和业务无关

业务主键：主键值和业务紧密关联，如拿银行卡账号做主键

在实际开发中，使用自然主键较多，因为主键只要做到不重复，不需要有意义，

和业务挂钩，当业务发生变动时，可能影响主键值

7.在MySQL中，有一种机制可以帮助我们自动维护一个主键值？

drop table if exists t_vip;

create table t_vip(

        id int primary key auto_increment,

        name varchar(255) 

);

insert into t_vip(name) values('zhangsan');

insert into t_vip(name) values('zhangsan');

insert into t_vip(name) values('zhangsan');

//auto_increment表示自增，从1开始递增

---

4.外键约束

1.foreign key相关术语：

外键约束、外键字段、外键值

2.业务背景：

设计数据库表，描述‘班级‘和’学生‘的信息？

第一种方案：班级和学生存储在一张表中t_student

no(pk)	name	classno	classname
1	jack	001	高三1班
2	tom	001	高三1班
3	zhangsan	002	高三2班
4	wangwu	002	高三2班

以上方案的缺点：数据冗余，空间浪费

第二种方案：班级一张表，学生一张表t_class,t_student

classno(pk)	classname
001	高三1班
002	高三2班

no(pk)	name	cno(班级编号)
1	jack	001
2	tom	001
3	zhangsan	002
4	wangwu	002

当班级编号没有任何约束时，可能导致数据无效

为了保证学生表中班级编号有效，需要添加外键约束

那么classno就是外键字段，字段中的每一个值就是外键值

注：被引用的t_class的是父表，t_student是子表

删除表，删除数据顺序：先删子表，再删父表

创建表，插入数据顺序：先创父表，再创子表

drop table if exists t_student;

drop table if exists t_class;

create table t_class(

        classno int primary key,

        classname varchar(255)

);

create table t_student(

        no int primary key auto_increment,

        name varchar(255),

        cno int,

        foreign key(cno) references t_class(classno)

);

insert into t_class(classno,classname) values(001,高三1班);

insert into t_class(classno,classname) values(002,高三2班);

insert into t_student(name,cno) values(jack,001);

insert into t_student(name,cno) values(tom,001);

insert into t_student(name,cno) values(zhangsan,002);

insert into t_student(name,cno) values(wangwu,002);

3.子表中的外键引用父表中的某个字段，被引用的这个字段必须是主键吗？

不一定是主键，但至少具有unique唯一性

外键可以为null吗？

insert into t_student(name) values（lisi);//可以为null

---

2.存储引擎（了解）

1.存储引擎：是MySQL中特有的术语，是一个表存储数据的方式，不同的存储引擎，表存储数据的方式不同

2.怎么给表指定’存储引擎‘？

show create table t_student;

可以在建表的时候给表指定存储引擎，可以在最后小括号’）‘的右边使用：

ENGINE来指定存储引擎

CHARSET来指定这张表的字符编码方式

MySQL默认的存储引擎是InnoDB,默认字符编码方式utf8

create table t_product(

        id int primary key,

        name varchar(255)

)engine=InnoDB default charset=gbk;

3.怎么查看MySQL支持哪些存储引擎？

show engines \G

MySQL支持九大存储引擎

4.常用的存储引擎？（了解）

MyISAM存储引擎，它管理表具有以下特征：

使用三个文件表示每个表：

        格式文件：存储表结构的定义（mytable.frm）

        数据文件：存储表行的内容（mytable.MYD） 

        索引文件：存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围

对于一张表来说，只要是主键或有unique约束的字段会自动创建索引

优势：可被转化为压缩、只读表来节省空间

不支持事务，安全性低

---

InnoDB存储引擎

是MySQL默认的存储引擎，支持事务，支持数据库崩溃后自动恢复机制

它管理表具有以下特征：

        每个InnoDB表在数据库目录中以.fm格式文件表示

        InnoDB表空间tablespace被用于存储表的内容（表空间是逻辑名称，表空间存储数据+索引）

        提供一组用来记录事务性活动的日志文件

        用COMMIT（提交）、SAVEPOINT及ROLLBACK（回滚）支持事务处理

        支持外键及引用的完整性，包括级联删除和更新

特点：非常安全

---

MEMORY存储引擎（之前被称为HEAP引擎）

其数据存储在内存中，且行的长度固定，这两个特点使MEMORY存储引擎非常快

它管理表具有以下特征：

        在数据库目录内，每个表均以.frm格式的文件表示

        表数据及索引被存储在内存中（查询快）

        表级锁机制

        不能包含TEXT或BLOB字段

优点：查询效率最高

不安全，关机之后数据消失，因为数据和索引都在内存