MySql的学习

一个商城项目的数据库,需要有很多张表：用户表、分类表、商品表、订单表

容易产生冗余

-- 创建部门表 
-- 一方,主表 
CREATE TABLE department( 
id INT PRIMARY KEY AUTO_INCREMENT, 
dep_name VARCHAR(30), 
dep_location VARCHAR(30) );
-- 创建员工表 
-- 多方 ,从表 
CREATE TABLE employee( 
eid INT PRIMARY KEY AUTO_INCREMENT, 
ename VARCHAR(20), 
age INT, 
dept_id INT 
);

-- 添加2个部门 
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳'); 
SELECt * FROM department;

-- 添加员工,dep_id表示员工所在的部门 
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); 

SELECt * FROM employee;

则生成以下两张表
主表：

从表：

则我们称dept_id是外键字段，外键所在的表叫做从表

插入一条不存在的部门id ,数据依然可以添加

INSERT INTO employee (ename,age,dept_id) VALUES('无名',35,3);

所以我们应设计合理的外键约束

• 外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性
• 外键指的是在从表中与主表的主键对应的字段
• 主表与从表：主表指主键id所在的表；从表就睡外键所在的表，可有多个从表

添加外键约束的语法格式
1. 创建表的时候添加外键

	create table 表名(
		字段...
		[constraint][外键约束名] foreign key(外键字段名) references 主表(主键字段)
	);

- 创建员工表， 添加外键
CREATE TABLE employee(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	age INT,
	dept_id INT, -- 外键字段 指向了主表的主键
	-- 添加外键约束
	CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

创建员工表成功并插入数据

当插入一条错误数据的时候

-- 插入一条有问题的数据 (部门id不存在) 
-- Cannot add or update a child row: a foreign key constraint fails 
INSERT INTO employee (ename, age, dept_id) VALUES ('错误', 18, 3);

就会报错
原因是添加外键约束之后 就会产生一个强制的外键约束检查 这里就是查询部门id是否存在 保证数据的完整性和一致性

2. 创建表之后添加外键
语法格式：

alter table 从表 add constraint emp_dept_fk foreign key(dept_id) references department(id)

-- 不写外键约束名
ALTER TABLE employee ADD CONSTRAINT FOREIGN KEY(dept_id) REFERENCES department(id);

不写外键约束名，会自动生成外键约束 employee_ibfk_1

语法格式
alter table 从表 drop foreign key 外键约束名称
注意是删除外键约束名称，不是外键字段的名称

-- 删除 employee表中 外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

1. 从表的外键类型必须与主表的主键类型一致
2. 添加数据时，应该先添加主表的数据
3. 删除数据时，先删除从表中数据

指的是在删除主表数据的同时，可以删除与之相关的从表中数据
级联删除on delete cascade
具体做法是在创建表的时候添加级联删除

• 一对多（1：n）：班级和学生，部门和员工
  建表原则：在多的一方建立外键 指向一的一方的主键
  

• 多对多（n：n）：学生与课程，演员和角色
  建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
  

• 一对一关系（不常见，仅了解）：身份证和人