Java基础87 MySQL数据约束

1、默认值

1 -- 创建表student1，设置address字段有默认值
2 create table student1
3 (
4   id int,
5   name varchar(32),
6   address varchar(32) default '广东省深圳市宝安区xxxxx'  -- 设置默认值
7 );

如上表，如果向student1表中插入数据时，address 字段没有给任何值时，则自动插入默认值广东省深圳市宝安区xxxxx。

1 insert into student1 values(1,'李四','广州番禺xxx');
2 insert into student1(id,name)  values(2,'王五'); -- address字段会插入默认值

2、非空

1 -- 创建表student2，设置sex字段必须有值(不为null，也不能是空白)
2 create table student2
3 (
4   id INT,
5   name varchar(32),
6   sex varchar(2) not null default '女'  -- 非空，且设置了默认值'女'，否则MySQL会自动插入空格（总之是空白，什么都没有）
7 );

1 insert into student2(id,name) values(1,'张三');
2 insert into student2 values(2,'李四','男');
3 insert into student2(id,name,sex) values(3,'王五',NULL); -- 报错Column 'sex' cannot be null。不能插入非空字符

3、唯一

1 -- 创建表student3，设置主键的唯一性，即：该字段的值不能重复（唯一性）
2 create table student3
3 (
4   id int unique,-- 唯一
5   name varchar(32)
6 );

1 inert into student3 values(1,'张三');
2 insert into student3 values(1,'李四'); -- 再创建一次，就报错了Duplicate entry '1' for key 'id'，违反了唯一性。
3 insert into student3(name) values('王五'); -- 但是，这条语句可以插入好多次都不报错

4、主键

1 -- 创建表student4，设置id字段为主键（即：非空+唯一）
2 create table student4
3 (
4   id int primary key, -- 主键
5   name varchar(32)
6 );

1 insert into student4(name) values('李斯'); -- id字段不给值的话，则从0开始赋值；主键设置自增长是从1开始赋值
2 insert into student4 values(3,'李四'); -- 这两条语句，不管哪一条，只要执行两遍，就会报错，违反了主键的唯一性（只要id值重复了，就报错）

5、自增长

5.1、设置主键自动递增

1 -- 创建表student5，设置主键自增长
2 create student5
3 (
4   id int primary key auto_increment, -- 自增长,从1开始
5   name varchar(32)
6 );

1 insert into student5(name) values('李斯'); -- id字段不给值的话，则从0开始赋值
2 insert into student5 values(3,'李四'); -- 先执行上面一条语句一次，再执行这条语句一次，最后连续执行上面一条语句2次，不会报错，会自动跳过2号id，但是id会从3开始自增

5.2、设置主键自动递增，并且用零来填充

1 -- 创建表student6，设置主键自增长，并且使用零填充
2 CREATE TABLE student6
3 (
4   id int ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增长,从零开始；zerofill：零填充
5   name VARCHAR(32)
6 );

INSERT INTO student6(NAME) VALUES('狗娃');  -- 执行三次

5.3、处理自增长主键的最后一条数据或全部数据被删除后，已删除的主键被闲置的问题

原有的所有数据，如下：

如果，id为3的数据被删除，或者该表的全部数据都被删除，那么下次再插入数据时，都是从id为4开始自增长

1 -- 如果是下面这种方法删除，那么下次再插入数据时，都是从id为4开始自增长
2 delete from student5;
3 delete from student5 where id = 3; -- 只删除id为3的那条数据

如果想要从id为3或从头（1）开始自增长，那么得使用完全删除数据的方法

1 truncate table student5; -- 完全删除所有数据
2 TRUNCATE TABLE student5 WHERE id = 4; -- 完全删除该条数据

6、外键

 1 -- 主表(部门表)
 2 CREATE TABLE department
 3 (
 4  id INT PRIMARY KEY,
 5  deptName VARCHAR(32)
 6 ); -- 先创建主表，再创建从表
 7 
 8 
 9 -- 从表/副表（员工表）
10 CREATE TABLE employee
11 (
12  id INT PRIMARY KEY,
13  emplName VARCHAR(32),
14  deptId INT, -- 把部门名称,改为了部门id
15  CONSTRAINT employee_department_fk FOREIGN KEY(deptId) REFERENCES department(id) 
16 --                 外键名称                      外键              参考表(参考的字段)
17 );

注意：

1、当有了外键约束时，向表中添加数据的顺序：先添加主表，再添加副表的数据。

2、当有了外键约束时，修改表内数据的顺序：先修改副表，再修改主表的数据。

3、当有了外键约束时，删除表内数据的顺序：先删除副表，再删除主表的数据。

1 INSERT INTO department VALUES(1,'软件开发部');
2 INSERT INTO department VALUES(2,'软件测试部');
3 INSERT INTO department VALUES(3,'销售部');

1 INSERT INTO employee VALUES(1,'张三',2);
2 INSERT INTO employee VALUES(2,'李四',1);
3 INSERT INTO employee VALUES(3,'王五',2);
4 INSERT INTO employee VALUES(4,'王五',3);

SELECT id,emplName,(SELECT deptName FROM department WHERE id = (SELECT deptId FROM employee WHERE id = e.id)) AS deptName FROM employee e; -- 合在一起查

7、级联操作

级联修改：ON UPDATE CASCADE
级联删除：ON DELETE CASCADE

 1 -- 主表(部门表)
 2 CREATE TABLE department
 3 (
 4  id INT PRIMARY KEY,
 5  deptName VARCHAR(32)
 6 ); -- 先创建主表，再创建从表
 7 
 8 
 9 -- 从表/副表（员工表）
10 CREATE TABLE employee
11 (
12  id INT PRIMARY KEY,
13  emplName VARCHAR(32),
14  deptId INT, -- 把部门名称,改为了部门id                                              级联修改           级联删除
15  CONSTRAINT employee_department_fk FOREIGN KEY(deptId) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
16 --                 外键名称                      外键              参考表(参考的字段)
17 );

注意：级联操作必须在外键的基础上使用

7.1、执行级联修改，把部门表的软件开发部的id改为5（注：因为在外键上加上了级联操作关系，故直接对主表进行操作时，也不会报错）

UPDATE department SET id = 5 WHERE id = 1;

结果图

7.2、执行级联删除，把部门表的软件开发部id = 5的部门删除掉

DELETE FROM department WHERE id = 5; -- 删的是部门，但 由于级联关系，员工表对应的员工 所在的部门的该条数据也将删除，即id为2的员工将删除

原创作者：DSHORE

作者主页：http://www.cnblogs.com/dshore123/

原文出自：https://www.cnblogs.com/dshore123/p/10545134.html

欢迎转载，转载务必说明出处。（如果本文对您有帮助，可以点击一下右下角的推荐，或评论，谢谢！）