Mysql_知识总结02（数据库的约束）

-- 五 数据库约束

-- 5.1 默认值约束(default)
-- 特点:当没有添加某个字段的时候,默认值约束起作用 ,如果添加字段了,不起作用!

CREATE TABLE test(
    id INT ,
    NAME VARCHAR(20),
    gender VARCHAR(2) DEFAULT '男' -- 默认值约束
);

DROP TABLE test ;

-- 给表中插入部分字段
INSERT INTO test(id,NAME) VALUES(1,'张三') ;
 -- insert into test values (2,'李四',null) ;

SELECT * FROM test ;

-- 5.2 非空约束( not null )
-- 防止用户在插入数据的插入null字段 (没有意义)
CREATE TABLE test (
    NAME VARCHAR(20)  NOT NULL ,
    gender VARCHAR(2)
);

-- 非空约束的字段一定要有意义(有值)
INSERT INTO test VALUES('张三','男') ;
-- 注意:非空约束不能插入null值
INSERT INTO test VALUES(NULL,'男') ; -- Column 'name' cannot be null 插入了一个非法数据
SELECT * FROM test ;

-- 5.3 唯一约束(unique)
DROP TABLE test ;
CREATE TABLE test(
    id INT UNIQUE ,
    NAME VARCHAR(20) ,
    gender VARCHAR(2)
);
-- 插入数据
INSERT INTO test VALUES(1,'张三','男') ;
INSERT INTO test VALUES(2,'李四','女') ;
-- id字段值重复了,id的约束unique就会起作用
INSERT INTO test VALUES(1,'张三','男') ; -- Duplicate entry '1' for key 'id'
-- 唯一约束可以不可以给字段加入null值?  唯一约束使用插入null值的
INSERT INTO test VALUES(NULL,'王五','男') ;

-- 5.4 主键约束(primary key 作用:非空+唯一约束)
DROP TABLE test ;
CREATE TABLE test(
    id INT PRIMARY KEY,
    NAME VARCHAR(20),
    gender VARCHAR(2)
) ;

-- 作用1:非空约束:该字段不能是一个空值(null)
INSERT INTO test VALUES(NULL,'张三','女') ; -- Column 'id' cannot be null
-- 作用2:唯一约束:
INSERT INTO test VALUES(1,'李四','男') ;
-- Duplicate entry '1' for key 'PRIMARY' :插入了一个重复id字段
INSERT INTO test VALUES(1,'王五','男') ;

-- 实际开发中的用法,一般情况每张表中都有一个主键约束
-- 1)不要将含义业务逻辑字段设置为主键(name,address...)
-- 2)一般情况每张表中都会设置一个id字段,将id字段非业务逻辑字段作为主键(primary key)


-- 5.5 自增长约束(auto_increment)
DROP TABLE test ;

-- 作用:加了自增长的id字段从0开始,自增1
-- 添加了自增约束字段的不需要开发者去管理,数据库自己维护
CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT ,
    NAME VARCHAR(20)
) ;

INSERT INTO test(NAME) VALUES('张三') ;
INSERT INTO test(NAME) VALUES('李四') ;
INSERT INTO test(NAME) VALUES('王五') ;

-- delete from 和truncate table 的区别? 自增长约束前者不能重置,后者可以重置
DELETE FROM test ;
TRUNCATE TABLE test ;

SELECT * FROM test ;

-- 5.6 外键约束(属于数据库中表的设计)
-- 外键约束:约束两个或者两个以上的表的数据
-- 一般情况:有两种表(主表,副表)

CREATE TABLE employee(NAME VARCHAR(20),deptName VARCHAR(20)) ;

INSERT INTO employee VALUES('张三','软件开发部') ;
INSERT INTO employee VALUES('李四','软件维护部') ;
INSERT INTO employee VALUES('王五','软件开发部') ;
INSERT INTO employee VALUES('陈六','软件维护部') ;

SELECT * FROM  employee ;

-- 问题:创建了员工表,每一个员工对应一个部门,在插入数据的时候,部门名称出现了数据冗余(重复),会浪费数据库的存储空间
-- 如何解决:可以设计一个独立的表,--- 部门表 专门用来存储部门名称,来解决数据冗余的问题


DROP TABLE employee ;

-- 员工表 该表中的deptId字段应该来自于哪里? 来自于部门表中的id字段
-- 需要给员工表中的deptId字段添加外键约束 (有外键约束的表叫副表,就是被别人约束的表)
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT,
    -- 添加外键约束
    CONSTRAINT employee_dept_fk FOREIGN KEY (deptId) REFERENCES dept(id)
 --     声明         外键名称    外键    被约束的字段  关联     部门表中id的字段
);

-- 部门表(主表,约束别人的表)
CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT ,
    NAME VARCHAR(20) -- 部门名称
);

-- 插入几个部门名称
INSERT INTO dept (NAME) VALUES ('软件开发部') ;
INSERT INTO dept (NAME) VALUES ('软件维护部') ;


-- 给员工表中插入数据
INSERT INTO employee (NAME,deptId) VALUES('王五',1) ;
INSERT INTO employee (NAME,deptId) VALUES('陈六',1) ;
INSERT INTO employee (NAME,deptId) VALUES('李四',2) ;

---- 给员工表中插入了一个部门表中不存在的id字段(有问题),就需要使用外键约束
-- 添加外键约束后,往员工表中插入了一个部门表不存在的id=deptId
INSERT INTO employee (NAME ,deptId) VALUES('李四',3) ;-- a foreign key constraint fails 外键起作用

-- 修改数据 在员工表中修改部门表中不存在的id的时候,外键起作用
UPDATE employee SET deptId = 3 WHERE id = 3 ; -- a foreign key constraint fails

-- 删除数据: 不能直接删除主表中的数据,因为副表中的数据和主表中的数据有关联
DELETE FROM dept WHERE id = 2 ;--  a foreign key constraint fails

-- 有了外键约束的了,如何进行插入,修改,和删除的操作!
TRUNCATE TABLE employee ;
-- 插入数据:
 -- 先往主表插入数据,然后在给副表插入数据
INSERT INTO dept (NAME) VALUES('软件测试部');
INSERT INTO employee (NAME,deptId) VALUES('张三',3) ;
 

-- 修改数据
-- 先修改主表数据,在修改副表数据
UPDATE dept  SET id = 2 WHERE id = 3 ;
UPDATE employee SET deptId =2 WHERE id =4 ;

-- 删除数据:先删除副表数据,在删除主表数据
DELETE FROM employee WHERE id = 3 ;
DELETE FROM dept WHERE id = 3 ;

DELETE FROM employee WHERE id = 2;
DELETE FROM dept WHERE id = 2 ;


SELECT * FROM dept ;
SELECT * FROM employee ;