学习MySQL数据库约束2_为员工表插入数据

 

目录

1约束的作用

2约束种类

2.1主键约束 

2.2唯一约束

2.3 非空约束

2.4 外键约束

2.4.1 外键的级联

2.4.2 外键删除、修改

3.数据库备份和还原

3.1 备份的应用场景

3.2 备份与还原的语句

3.3 还原操作

---

1约束的作用

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

2约束种类

约束名 约束关键字

主键 primary key

唯一 unique

非空 not null

外键 foreign key

2.1主键约束 

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

创建：使用关键字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;

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

CREATE TABLE 表名(

列名 int primary key AUTO_INCREMENT

) AUTO_INCREMENT=起始值;

创建好以后修改起始值

alter table stu01 auto_increment = 2000;

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

2.2唯一约束

唯一约束：表中某一列不能出现重复的值，实际创建了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;

2.3 非空约束

非空约束：某一列不能为 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, '张三');

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

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

自增长只能用在主键上。

2.4 外键约束

首先，创建一张员工表，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);

2.4.1 外键的级联

外键指定的字段中数据更新和删除时是受到外键约束的，默认值为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`))

*/

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

例如：

先删除原来的表emp

DROP TABLE emp;

创建表

create table `emp`(

id int primary key auto_increment,

name varchar(20),

age int,

dept_id int, ­­ 外键对应主表的主键

constraint emp_deptid_fk foreign key (dept_id) references

department(id) on update cascade on delete cascade ­­ 创建外键约束

);

添加记录

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

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

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

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

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

INSERT INTO emp01 (NAME, age, dept_id) VALUES ('小王', 18,2);

删除主表数据，从表数据也会根据外键删除相对应的数据

DELETE FROM department where id=1;

2.4.2 外键删除、修改

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

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

外键不能设为not null；

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

录；

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

3.数据库备份和还原

3.1 备份的应用场景

在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这 时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

3.2 备份与还原的语句

格式：

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

例如：

mysqldump ­uroot ­p112112 mysql_info>d:demo01.sql

3.3 还原操作

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

步骤如下：

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

② 登录 MySQL；

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

④ 使用 SOURCE 命令还原数据；

⑤ 查看还原结果。

例如：

source d:/demo01.sql