mysql学习笔记-表的创建与约束
1、表的结构
用户表和数据字典是Oracle数据库中的两种类型的表
用户表由用户创建
数据字典由Oracle服务器创建和维护
数据字典包括有关数据库的信息
2、列、行
列称为字段
行又称为记录
每个字段具有 字段名、字段长度、约束、默认值 属性
3、约束
在创建表字段的时候,需要对应给表添加相应的约束
约束用于实施数据完整性,以确保表中的数据的完整
主键约束、外键约束、唯一性约束、检查约束和NOT NULL约束
MySQL可以使用check约束,但check约束对数据验证没有任何作用。
-- 表名和字段名系统默认会转换大写,即不区分大小写 DROP TABLE t_student; create table t_student ( id int PRIMARY KEY NOT NULL, -- 主键必须唯一,不能为空 stu_no INT UNIQUE ,-- 唯一约束,可以为空,除非设置为not NULL,并且可以有多个null stu_name varchar(20) NOT NULL,-- 非空约束 stu_addr varchar(100) , stu_phone varchar(11), stu_sex SMALLINT DEFAULT 1 CHECK(stu_sex in(0,1)),-- 检查约束,check约束 stu_age tinyint(4) NOT NULL DEFAULT 30 CHECK (stu_age BETWEEN 20 AND 60) -- 默认值约束,check约束对数据验证没有任何作用 not null必须在默认值前,否则会报错 )
4、UNIQUE与主键区别
UNIQUE 可以为空,除非设置为not NULL,否则可以有多个null,
主键唯一,不能重复,不能为空
SELECT * FROM t_student; -- 插入报错的insert -- 主键唯一性 INSERT INTO t_student VALUES (1,2314,'张三','深圳南山','13264581265',0,25); -- Duplicate entry '1' for key 'PRIMARY' 主键唯一,不能重复,报错 INSERT INTO t_student VALUES (1,2315,'李四','深圳南山','13264581265',0,25); -- Column 'id' cannot be null 主键不能为空,报错 INSERT INTO t_student VALUES (null,2313,'张三','深圳南山','13264581265',0,25); -- UNIQUE 可以为空,除非设置为not NULL,并且可以有多个null,插入成功 INSERT INTO t_student VALUES (2,null,'李四','深圳南山','13264581265',0,25); INSERT INTO t_student VALUES (3,null,'王五','深圳南山','13264581265',0,25); -- 主键为空时,默认为0 ,第一条记录可以添加成功,第二天失败,因为主键唯一 INSERT INTO t_student (stu_no,stu_name) VALUES (7844,'六六'); -- check约束对数据验证没有任何作用,插入成功,不报错 INSERT INTO t_student VALUES (4,2345,'王五','深圳南山','13264581265',0,80);
5、外键约束
如果想要删除父表,可以有两种方法:
1、先drop子表,再drop父表。
2、先删除约束alter table emp_test drop constraint fk_emp_dept_test;,再drop父表。
6、MySQL外键设置中的的 Cascade、NO ACTION、Restrict、SET NULL
. cascade方式
在父表上update/delete记录时,同步update/delete掉子表的匹配记录
. set null方式
在父表上update/delete记录时,将子表上匹配记录的列设为null
要注意子表的外键列不能为not null
. No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
. Restrict方式
同no action, 都是立即检查外键约束
. Set default方式
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别
DROP TABLE t_dept; CREATE TABLE t_dept( dept_id INT(3) PRIMARY KEY, dept_no INT UNIQUE NOT NULL, dept_name VARCHAR(20) NOT NULL ); DROP TABLE t_employee; CREATE TABLE t_employee( emp_id INT(3) PRIMARY KEY, emp_no INT(3) UNIQUE NOT NULL, emp_name VARCHAR(10) NOT NULL, emp_age tinyint(4) NOT NULL DEFAULT 25 CHECK (emp_age BETWEEN 20 AND 60), sex VARCHAR(1) CHECK (sex in ('男','女')), job VARCHAR(20), sal INT(10), -- inline写法 -- REFERENCES 主表(主表字段) -- dept_no int REFERENCES t_dept(dept_no) -- outline写法 -- 删除外键时,引用外键的记录同步删除 -- dept_no int NOT NULL , -- FOREIGN KEY(dept_no) REFERENCES t_dept(dept_no) ON DELETE CASCADE -- 删除外键时,引用外键的记录设置为空 dept_no int , FOREIGN KEY(dept_no) REFERENCES t_dept(dept_no) ON DELETE SET NULL ); insert into t_dept values(1,10,'IT技术部门'); insert into t_dept values(2,20,'市场部'); insert into t_dept values(3,30,'人事部'); -- 再插入员工数据 INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`) VALUES(1,1,'张三',25,'男','软件开发',8500,10); INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`) VALUES(2,102,'张天三',29,'男','初级软件开发',4500,10); INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`) VALUES(3,103,'张一',36,'男','测试人员',7500,10); INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`) VALUES(4,104,'王张三',63,'男','人事主管',12500,20); INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`) VALUES(5,105,'发呆张三',60,'男','高级程序员',18500,10); INSERT INTO `test`.`t_employee` (`emp_id`,`emp_no`,`emp_name`,`emp_age`,`sex`,`job`,`sal`,`dept_no`) VALUES(6,106,'快乐张三',30,'男','销售经理',10500,30); SELECT * FROM t_employee; SELECT * FROM t_dept; -- MySQL设置了foreign key关联,造成无法更新或删除数据。可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况。 -- Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t_employee`, CONSTRAINT `t_employee_ibfk_1` FOREIGN KEY (`dept_no`) REFERENCES `t_dept` (`dept_no`)) DELETE FROM t_dept WHERE dept_no = '10';
-- 方法一: SET FOREIGN_KEY_CHECKS = 0; DELETE FROM t_dept WHERE dept_no = '30'; -- 删除完后记得外键约束重新打开; SET FOREIGN_KEY_CHECKS = 1; -- 查mysql版本 select version() from dual;
-- 方法二:
定义表时
-- 删除外键时,引用外键的记录同步删除
dept_no int NOT NULL , FOREIGN KEY(dept_no) REFERENCES t_dept(dept_no) ON DELETE CASCADE
-- 删除外键时,引用外键的记录设置为空 dept_no int , FOREIGN KEY(dept_no) REFERENCES t_dept(dept_no) ON DELETE SET NULL