MySQL学习笔记二——约束

数据库的备份和还原

备份与还原语句

命令提示符格式

mysqldump ­u 用户名 ­p 密码 备份数据库名 > 保存路径 + 名字

还原操作

还原的时候需要先登录 MySQL, 并选中对应的数据库。

步骤如下：

① 删除数据库中的所有表；

② 登录 MySQL ；

③ 新建数据库，或选中已有的数据库；

④ 使用 SOURCE 命令还原数据；

⑤ 查看还原结果

数据库表的约束

约束的作用

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入 到表中。约束在创建表的时候添加比较合适。

约束种类

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key

主键约束

主键是用来唯一标识数据库中的每一条记录。

例如：表中的 id 字段。

注意：通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使

用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。（身份证，学号不建议

做成主键）

创建：使用关键字 primary key

格式：字段名 字段类型 PRIMARY KEY

特点：非空，不能重复（所以通常把主键设置为自增）

例如：

①创建主键非自增

create table `stu01` (

`id` int ( 3 ) primary key, ­­ id 为主键

`name` varchar ( 20 ),

`age` int ( 3 )

);

②创建主键自增

create table `stu02` (

`id` int ( 3 ) auto_increment primary key, ­­ id 为主键且自增

`name` varchar ( 20 ),

`age` int ( 3 )

);

添加主键

alter table stu01 add primary key(id);

删除主键

alter table stu01 drop primary key;

注意：我们已经知道，主键自增从 1 开始，我们应该如何定义主键从什么地方开始自增呢？？

①创建表时指定从哪里开始自增

CREATE TABLE 表名 (

列名 int primary key AUTO_INCREMENT

) AUTO_INCREMENT= 起始值 ;

②创建好以后修改起始值

alter table stu01 auto_increment = 2000 ;

注意：如果你删除了表中的数据，最后一个数据的 id 为 5 ，那么设置 id 从 6 开始自增，能够实现；如果最后一个数据 的id 为 10 ，那么设置 id 从 5 开始自增，不能够实现。

唯一约束

唯一约束：表中某一列不能出现重复的值，实际创建了 UNIQUE 索引

格式：字段名 字段类型 UNIQUE

例如：

create table `stu03` (

id int ( 11 ),

name varchar ( 20 ) unique

);

向表中插入数据

INSERT INTO stu03 VALUES ( 1 , ' 小明 ' );

再次向表中插入数据，且 name 字段的值也为小明

INSERT INTO stu03 VALUES ( 2 , ' 小明 ' );

出现错误信息

注意：如果插入值为 null ，不会出现这个问题。

如果数据表已经创建，需要添加 UNIQUE ，请使用下面的 SQL：

alter table user add unique(name);

如果删除 unique ，请使用下面的 SQL ：

alter table user drop index name;

非空约束

非空约束：某一列不能为 null

格式：字段名 字段类型 NOT NULL

例如：

create table `stu04` (

id int ,

name varchar ( 20 ) not null ,

gender char ( 1 )

);

向表中插入数据

INSERT INTO stu04 VALUES(2,' 小明 ',' 男 ');

如果字段被设置成为 not null ，怎么避免插入数据出错？？？可以使用设置默认值的方式。

格式：字段名 字段类型 DEFAULT 默认值

例如：

创建一张表， address 设置了默认值

create table `stu05` (

id int ( 10 ),

name varchar ( 20 ),

address varchar ( 20 ) not null default ' 重庆 '

);

如果 default 的值为中文， sql 执行报错，可在最后添加 default character set = utf8 ，如：

create table `stu05` (

id int ( 10 ),

name varchar ( 20 ),

address varchar ( 20 ) not null default ' 重庆 '

)default character set =utf8;

插入一条记录

insert into `stu05` values ( 1 , ' 李四 ' , default);

insert into `stu05`(id,name) values ( 2 , ' 张三 ' );

注意：如果一个字段设置了非空与唯一约束，该字段与主键的区别 ?

①主键在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列；

②自增长只能用在主键上。

外键约束

首先，创建一张员工表， SQL 语句如下

CREATE TABLE IF NOT EXISTS emp (

id bigint AUTO_INCREMENT PRIMARY KEY ,

name VARCHAR ( 30 ),

age INT ,

dept_name VARCHAR ( 30 ),

dept_location VARCHAR ( 30 )

);

添加几条数据

INSERT INTO emp (name, age, dept_name, dept_location) VALUES ( ' 张三 ' , 20 , ' 教学部 ' , ' 重庆 ' );

INSERT INTO emp (name, age, dept_name, dept_location) VALUES ( ' 李四 ' , 21 , ' 教学部 ' , ' 重庆 ' );

INSERT INTO emp (name, age, dept_name, dept_location) VALUES ( ' 王五 ' , 20 , ' 教学部 ' , ' 重庆 ' );

INSERT INTO emp (name, age, dept_name, dept_location) VALUES ( ' 老王 ' , 20 , ' 市场部 ' , ' 成都 ' );

INSERT INTO emp (name, age, dept_name, dept_location) VALUES ( ' 大王 ' , 22 , ' 市场部 ' , ' 成都 ' );

INSERT INTO emp (name, age, dept_name, dept_location) VALUES ( ' 小王 ' , 18 , ' 市场部 ' , ' 成都 ' );

对于 dept_name ， dept_location 两个字段，出现了多次重复情况，造成了数据冗余现象，那么，应该怎么解决

呢？？？可以使用外键关联另外一张表进行查询操作。

例如：

先分别创建员工表和部门表

CREATE TABLE IF NOT EXISTS emp (

id bigint PRIMARY KEY AUTO_INCREMENT,

name VARCHAR ( 30 ),

age INT ,

dept_id bigint

);

create table dept(

id bigint primary key auto_increment,

dept_name varchar ( 30 ),

dept_location varchar ( 30 )

);

①向部门表中插入两个部门信息

insert into dept values ( null , ' 教学部 ' , ' 重庆 ' ),( null , ' 市场部 ' , ' 成都 ' );

②插入员工信息

INSERT INTO emp (NAME, age, dept_id) VALUES ( ' 张三 ' , 20 , 1 );

INSERT INTO emp (NAME, age, dept_id) VALUES ( ' 李四 ' , 21 , 1 );

INSERT INTO emp (NAME, age, dept_id) VALUES ( ' 王五 ' , 20 , 1 );

INSERT INTO emp (NAME, age, dept_id) VALUES ( ' 老王 ' , 20 , 2 );

INSERT INTO emp (NAME, age, dept_id) VALUES ( ' 大王 ' , 22 , 2 );

思考：当我们插入部门编号不存在的数据，数据会插入吗？？肯定可以

INSERT INTO emp (NAME, age, dept_id) VALUES ( ' 小王 ' , 18 , 3 );

我们期望插入的数据， dept_id 的值只能是部门表 id 存在的值，怎么办呢？？这个时候就可以使用外键。

外键：在从表中与主表主键对应的那一列，如：员工表中的 dep_id 。

主表：用来约束别人的表，一方

从表（副表）：被别人约束的表，多方

格式：

①新建表时增加外键

[CONSTRAINT][ 外键约束名称 ] FOREIGN KEY( 外键字段名 ) REFERENCES 主表名 ( 主键字段名 ) ；

例如：

先删除原来的表 emp

DROP TABLE emp;

新建表 emp

create table if not exists emp(

id bigint auto_increment primary key,

name varchar ( 30 ),

age int ,

dept_id bigint ,

constraint emp_dept_id_fk foreign key (dept_id) references dept(id)

) default character set = utf8;

插入数据

insert into emp values ( null , ' 张三 ' , 20 , 1 );

insert into emp values ( null , ' 李四 ' , 21 , 2 );

此时如果在 emp 表中插入 dept 表不存在的 id ，则会报错：.

insert into emp values ( null , ' 王五 ' , 22 , 3 );

/*

dept_id 为 3 ， dept 表中没有这个 id 值

报错：

1452 ­ Cannot add or update a child row: a foreign key constraint fails (`user1`.`emp`,

CONSTRAINT `emp_dept_id_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))

*/

②已有表增加外键

ALTER TABLE 从表 ADD [CONSTRAINT][ 外键约束名称 ] FOREIGN KEY ( 外键字段名 ) REFERENCES 主表 ( 主键字段名);

例如：

删除存在的外键

alter table emp drop foreign key emp_deptid_fk;

添加外键

alter table emp add constraint foreign key (dept_id) references dept(id);

外键的级联

外键指定的字段中数据更新和删除时是受到外键约束的，默认值为 RESTRICT ，如果删除主表中的数据，子表中有关联该父表的记录，则不允许删除父表中的记录

delete from dept where id= 1 ;

/*

报错：

1451 ­ Cannot delete or update a parent row: a foreign key constraint fails

(`user1`.`emp`, CONSTRAINT `emp_dept_id_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept`

(`id`))

*/

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

语法	描述
ON UPDARE CASCADE	级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键列也自动同步更新
ON DELETE CASCADE	级联删除

外键删除、修改

CASCADE ：父表 delete 、 update 的时候，子表会 delete 、 update 掉关联记录；

SET NULL ：父表 delete 、 update 的时候，子表会将关联记录的外键字段所在列设为 null ，所以注意在设计子表时外键不能设为not null ；

RESTRICT ：默认值，如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；

NO ACTION ：同 RESTRICT ，也是首先先检查外键；