Java基础87 MySQL数据约束

1、默认值         

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

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

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

2、非空           

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

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

3、唯一         

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

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、主键           

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

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

5、自增长       

  5.1、设置主键自动递增

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

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

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

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

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

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

原有的所有数据，如下：

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

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

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

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

6、外键           

-- 主表(部门表)
CREATE TABLE department
(
 id INT PRIMARY KEY,
 deptName VARCHAR(32)
); -- 先创建主表，再创建从表


-- 从表/副表（员工表）
CREATE TABLE employee
(
 id INT PRIMARY KEY,
 emplName VARCHAR(32),
 deptId INT, -- 把部门名称,改为了部门id
 CONSTRAINT employee_department_fk FOREIGN KEY(deptId) REFERENCES department(id) 
--                 外键名称                      外键              参考表(参考的字段)
);

注意：

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

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

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

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

INSERT INTO employee VALUES(1,'张三',2);
INSERT INTO employee VALUES(2,'李四',1);
INSERT INTO employee VALUES(3,'王五',2);
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

-- 主表(部门表)
CREATE TABLE department
(
 id INT PRIMARY KEY,
 deptName VARCHAR(32)
); -- 先创建主表，再创建从表


-- 从表/副表（员工表）
CREATE TABLE employee
(
 id INT PRIMARY KEY,
 emplName VARCHAR(32),
 deptId INT, -- 把部门名称,改为了部门id                                              级联修改           级联删除
 CONSTRAINT employee_department_fk FOREIGN KEY(deptId) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
--                 外键名称                      外键              参考表(参考的字段)
);

         

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

    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

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