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 ;
-- 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 ;