MySQL(二）——引擎和约束

MySQL基础

MySQL引擎

（1）当你使用create table语句时，该引擎用于创建表;

（2）当在你使用select语句或进行其他数据操作时，该引擎在内部处理各种命令请求;

（3）在多数时候，数据库引擎都隐藏在MySQL软件内，开发者不需要过多地关注它，但通常SQL内部数据出现问题时, 就是引擎导致的.

MySQL引擎的分类：

（1）InnoDB是一个可靠的事务处理引擎，但是它不支持全文搜索；

（2）MyISAM是一个性能极强的引擎，它支持全文搜索，但不支持实务处理；

（3）Memory在功能上等同于MyISAM，但由于数据存储在内存中，速度很快，但占用内存大，几乎不使用。

说明：大数据一般使用的是InnoDB引擎。

MyISAM和InnoDB的区别

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作。	行锁，操作时只锁某一行，不对其他行产生影响。特别适合高并发的操作。
缓存	只缓存索引，不缓存真实数据。	不仅缓存索引，还缓存真实数据，对内存要求较高。
表空间	小	大
关注点	性能	事务
是否默认安装	是	是

设定MySQL引擎

create table 表名(
    字段名 数据类型(长度) [约束],
    ...
) engine = 引擎名 default charset utf8;

例：

（1）在MySQL中新建一个数据库school，并设定编码为utf8；

（2）新建一个包含姓名、年龄的学生表1，默认InnoDB引擎；

（3）新建一个包含姓名、年龄的学生表2，并给数据表指定为MyISAM引擎及设定默认编码为utf8；

# （1）新建库
create database if not exists my_school charset utf8;
use my_school;
# （2）创建默认引擎的学生表1
create table if not exists student1(
    name varchar(10),
    age int
);
# （3）创建MyISAM引擎的学生表2
create table if not exists student2(
    name varchar(20),
    age int
) engine = MyISAM default charset utf8;

SQL约束

SQL约束是用于指定字段数据的规则，可以保证数据更加精确、可靠。

create table 表名(
    字段名1 数据类型(长度) [约束],
    字段名2 数据类型(长度) [约束],
    字段名3 数据类型(长度) [约束],
    ...
) engine = 引擎名 default charset utf8;

常见的SQL约束

约束名称	关键字	含义
主键	primary key	MySQL建议所有表的主键字段都叫id，类型为int。
非空	not null	此字段不允许填写空值。
唯一	unique	此字段的值不允许重复。
默认值	default	当不填写字段对应的值时，会使用默认值；如果填写时，则以填写为准。
外键	foreign key	涉及到多张表时才会使用。 对表与表关系字段进行约束，当为表与表关系字段填写值时，会到关联的表中查询此值是否存在。 如果存在，则填写成功；如果不存在，则填写失败。

注意：

（1）SQL约束不是必须的，只是为了SQL更加规范存在；

（2）可以对字段设定多个SQL约束，且都是在创建数据表时设定。

主键约束

create table 表名(
	字段名 数据类型(长度) primary key [其他约束],
	...
) engine = 引擎名 default charset utf8;

也可以单独添加主键约束：

# 创建表后，使用alter table关键字添加主键
alter table 表名 add primary key(字段名);

说明：

（1）给primary key主键约束的字段设定值时，不允许出现重复值；[唯一性]

（2）主键约束列不能包含null值；[非空]

（3）每个表都应该有一个主键约束，且每个表只能有一个主键。

例：

（1）在库中创建游戏表game1，有4个字段：id编号、first_name曾用名、last_name姓名、skill技能；

（2）在创建表时，给id编号添加主键约束；

（3）当建表成功后，使用desc命令查看表结构；

# 创建表
create table if not exists game1(
    id int primary key,  ##添加主键约束
    first_name varchar(20),
    last_name varchar(20),
    skill varchar(255)
) engine = InnoDB default charset utf8;
# 查看表结构
desc game1;

注意：当id这一列在key字段对应的值为PRI，则表示主键约束已经添加成功。

删除主键约束

# 使用alter table关键字删除主键
alter table 表名 drop primary key;

说明：删除主键约束后，该字段依旧是非空约束，不能为空。

例：

（1）先在库中创建游戏表game2，有4个字段：id编号[主键约束]、first_name曾用名、last_name姓名、skill技能；

（2）删除库中的游戏表game2的主键约束；

（3）当删除成功后，使用desc命令查看表结构，观察结果；

# 创建表
create table game2(
    id int primary key,
    first_name varchar(20),
    last_name varchar(20),
    skill varchar(255)
) engine = InnoDB default charset utf8;
# 删除主键约束
alter table game2 drop primary key;
# 查看表结构
desc game2;

自动增长

create table 表名(
	字段名 数据类型(长度) primary key auto_increment,
	...
) engine = 引擎名 default charset utf8;

说明：

（1）自动增长的数据类型必须是整型；

（2）自动增长列必须为主键或外键。

例：

（1）在库中创建游戏表game3，有4个字段：id编号、first_name曾姓名、last_name姓名、skill技能；

（2）在创建表时，给id编号添加主键约束，整型且自动增长；

# 创建表：自增
create table game3(
    id int primary key auto_increment,
    first_name varchar(20),
    last_name varchar(20),
    skill varchar(255)
) engine = InnoDB default charset utf8;
# 查看表结构
desc game3;

（3）给游戏表game3添加2条数据，且都不添加id值，查看数据结果；

（4）思考1：当添加数据时，给主键id传递null值，效果如何？

# 3.添加2条数据
insert into game3(first_name,last_name,skill) values('乔峰','萧峰','背着音箱');
insert into game3(first_name,last_name,skill) values('虚竹','尊主','天山折梅手');

# 4.给id=null
insert into game3 values(null,'段誉','段誉','凌波微步');

（5）思考2：当添加一条设定id = 10的数据后，再不给主键id传值并添加一条新数据，效果如何？

# 5.id=10,增加数据
insert into game3(id,first_name,last_name,skill) values(10,'阿紫','阿紫','阴险');
insert into game3(first_name,last_name,skill) values('慕容复','慕容复','移花接木');

非空约束

create table 表名(
	字段名 数据类型(长度) [其他约束] not null,
	...
) engine = 引擎名 default charset utf8;

说明：

（1）当设定主键约束后，值默认是非空的；

（2）当给某列设定为not null后，在添加数据时必须向该字段传递非null值。

例：

（1）在库中创建游戏表game4，有4个字段：id编号、first_name曾姓名、last_name姓名、skill技能；

（2）在创建表时，给id编号添加主键约束、整型且自动增长；

（3）给last_name姓名字段设定为非空约束；

# 创建表：非空
create table game4(
    id int primary key auto_increment not null,
    first_name varchar(20),
    last_name varchar(20) not null,  # 非空
    skill varchar(255)
) engine = InnoDB default charset utf8;

唯一约束

create table 表名(
	字段名 数据类型(长度) [其他约束] unique,
	...
) engine = 引擎名 default charset utf8;

说明：

（1）每个表可以有多个unique唯一约束，但是每个表只能有一个primary key主键约束；

（2）当设定为某字段唯一约束后，给该字段添加已有值，则报错。

例：

（1）在库中创建游戏表game5，有4个字段：id编号、first_name曾姓名、last_name姓名、skill技能；

（2）在创建表时，给id编号添加主键约束、整型且自动增长；

（3）给last_name字段设定为唯一约束；

# 创建表：唯一
create table game5(
    id int primary key auto_increment unique not null, # 默认唯一且非空
    first_name varchar(20),
    last_name varchar(20) unique,
    skill varchar(255)
) engine = InnoDB default charset utf8;

默认值约束

create table 表名(
	字段名 数据类型(长度) [其他约束] default 值,
	...
) engine = 引擎名 default charset utf8;

说明：

（1）每个表可以有多个默认值约束；

（2）设置了默认值后，若不传递数值，则直接使用默认值。

例：

（1）在库中创建游戏表game6，有4个字段：id编号、first_name曾姓名、last_name姓名、skill技能；

（2）在创建表时，给id编号添加主键约束、整型且自动增长；

（3）给skill字段设定为默认值约束，设定为九阴白骨爪；

# 创建表：默认
create table game6(
    id int primary key auto_increment,
    first_name varchar(20),
    last_name varchar(20),
    skill varchar(255) default '九阴白骨爪'
) engine = InnoDB default charset utf8;
desc game6;

（4）思考1：添加一条数据，并给字段skill技能传递值，观察效果；

（5）思考2：再添加一条数据，不给字段skill技能传递数据值，观察效果。

# 1.添加值, 传递值
insert into game6(first_name, last_name, skill) values ('木婉清','木婉清','剑术~~~');
# 2..不传递值
insert into game6(first_name, last_name) values ('王语嫣','王语嫣');