数据库的设计

在实际项目开发的时候可能设计到多张表，表与表之间存在一定关系，如何设计表之间的关系让系统更加优化，需要考虑到多表之间关系。

1. 一对一(了解)  * 如：人和身份证  * 分析：一个人只有一个身份证，一个身份证只能对应一个人 2. 一对多(多对一)  * 如：部门和员工  * 分析：一个部门有多个员工，一个员工只能对应一个部门 3. 多对多  * 如：学生和课程  * 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择 

一对多关系

一个部门可以有多个员工，一个员工只能对应一个部门

-- 创建部门表(主表) CREATE TABLE department(  id INT PRIMARY KEY,  NAME VARCHAR(20) ) -- 向部门表中添加数据 INSERT INTO department(id,NAME) VALUES(1,"教研部"); INSERT INTO department(id,NAME) VALUES(2,"学工部"); INSERT INTO department(id,NAME) VALUES(3,"就业部"); SELECT * FROM department; -- 创建员工表(从表) CREATE TABLE employee(  id INT PRIMARY KEY AUTO_INCREMENT,  NAME VARCHAR(20) NOT NULL,  age INT,  dep_id INT, CONSTRAINT emp_depid_fk FOREIGN KEY(dep_id) REFERENCES department(id) ) -- 向员工表中添加数据 INSERT INTO employee(NAME,age,dep_id) VALUES("张三",18,1); INSERT INTO employee(NAME,age,dep_id) VALUES("李四",20,2); INSERT INTO employee(NAME,age,dep_id) VALUES("王五",21,1); INSERT INTO employee(NAME,age,dep_id) VALUES("赵六",19,3); INSERT INTO employee(NAME,age,dep_id) VALUES("田七",24,2); INSERT INTO employee(NAME,age,dep_id) VALUES("周八",23,3); INSERT INTO employee(NAME,age,dep_id) VALUES("黄九",25,1); SELECT * FROM employee; 

多对多关系

一个学生可以选择很多门课程，一个课程也可以被很多学生选择

-- 创建学生表 CREATE TABLE student(  sid INT PRIMARY KEY,  NAME VARCHAR(20),  age INT ) INSERT INTO student(sid,NAME,age) VALUES(1,"张三",20); INSERT INTO student(sid,NAME,age) VALUES(2,"李四",19); INSERT INTO student(sid,NAME,age) VALUES(3,"王五",18); INSERT INTO student(sid,NAME,age) VALUES(4,"赵六",21); INSERT INTO student(sid,NAME,age) VALUES(5,"田七",24); INSERT INTO student(sid,NAME,age) VALUES(6,"周八",23); -- 创建课程表 CREATE TABLE class(  cid INT PRIMARY KEY,  NAME VARCHAR(20) ) INSERT INTO class(cid,NAME) VALUES(1,"Java基础"); INSERT INTO class(cid,NAME) VALUES(2,"HTML"); INSERT INTO class(cid,NAME) VALUES(3,"Javascript"); INSERT INTO class(cid,NAME) VALUES(4,"mysql"); -- 创建外键表 CREATE TABLE foreign_table(  sid INT,  cid INT, CONSTRAINT sid_fk FOREIGN KEY(sid) REFERENCES student(sid), -- 外键sid 关联 student sid主键 CONSTRAINT cid_fk FOREIGN KEY(cid) REFERENCES class(cid) -- 外键cid 关联 class cid主键 ) INSERT INTO foreign_table(sid,cid) VALUES(1,1); INSERT INTO foreign_table(sid,cid) VALUES(1,2); INSERT INTO foreign_table(sid,cid) VALUES(1,3); INSERT INTO foreign_table(sid,cid) VALUES(1,4); INSERT INTO foreign_table(sid,cid) VALUES(2,1); INSERT INTO foreign_table(sid,cid) VALUES(2,2); INSERT INTO foreign_table(sid,cid) VALUES(3,4); INSERT INTO foreign_table(sid,cid) VALUES(3,1); 

一对一关系

一个人有一个身份证号码，一个身份证号码只对应一个人

-- 创建身份证表 CREATE TABLE card(  id INT PRIMARY KEY,  number VARCHAR(18) ) -- 设置身份证号码唯一 ALTER TABLE card MODIFY number VARCHAR(20) UNIQUE; INSERT INTO card(id,number) VALUES(1,420923199211090512); INSERT INTO card(id,number) VALUES(2,420923199310230807); INSERT INTO card(id,number) VALUES(3,420923199007080908); -- 创建人员表 CREATE TABLE person(  id INT PRIMARY KEY,  NAME VARCHAR(20),  cid INT ) -- 给人员表添加外键 ALTER TABLE person ADD CONSTRAINT cid_kf FOREIGN KEY(cid) REFERENCES card(id); -- 设置外键唯一 ALTER TABLE person MODIFY cid INT UNIQUE -- 向person表中添加数据 INSERT INTO person(id,NAME,cid) VALUES(1,"张三",1); INSERT INTO person(id,NAME,cid) VALUES(2,"李四",2); INSERT INTO person(id,NAME,cid) VALUES(3,"王五",3); 

多表关系案例

旅游分类：如亲子游、爸妈游、蜜月游等 旅游线路：如普吉岛5日游、巴厘岛7日游等 用户：如老王、小王等  分析： 一个分类对应多个线路、一个线路可以被多用用户选择，一个用户也可以选择多个线路。 

-- 创建旅游线路分类表 tab_category -- cid 旅游线路分类主键，自动增长 -- cname 旅游线路分类名称非空，唯一，字符串 100 CREATE TABLE tab_category (  cid INT PRIMARY KEY AUTO_INCREMENT,  cname VARCHAR(100) NOT NULL UNIQUE ); -- 添加旅游线路分类数据： INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游'); SELECT * FROM tab_category; -- 创建旅游线路表 tab_route /* rid 旅游线路主键，自动增长 rname 旅游线路名称非空，唯一，字符串 100 price 价格 rdate 上架时间，日期类型 cid 外键，所属分类 */ CREATE TABLE tab_route(  rid INT PRIMARY KEY AUTO_INCREMENT,  rname VARCHAR(100) NOT NULL UNIQUE,  price DOUBLE,  rdate DATE,  cid INT, FOREIGN KEY (cid) REFERENCES tab_category(cid) ); -- 添加旅游线路数据 INSERT INTO tab_route VALUES (NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499, '2018-01-27', 1), (NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-22', 3), (NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州 往返 特价团】', 1699, '2018-01-27', 2),(NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23', 2), (NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店 暨会议中心标准房 1 晚住宿】', 799, '2018-04-10', 4); SELECT * FROM tab_route; /* 创建用户表 tab_user uid 用户主键，自增长 username 用户名长度 100，唯一，非空 password 密码长度 30，非空 name 真实姓名长度 100 birthday 生日 sex 性别，定长字符串 1 telephone 手机号，字符串 11 email 邮箱，字符串长度 100 */ CREATE TABLE tab_user (  uid INT PRIMARY KEY AUTO_INCREMENT,  username VARCHAR(100) UNIQUE NOT NULL,  PASSWORD VARCHAR(30) NOT NULL,  NAME VARCHAR(100),  birthday DATE,  sex CHAR(1) DEFAULT '男',  telephone VARCHAR(11),  email VARCHAR(100) ); -- 添加用户数据 INSERT INTO tab_user VALUES (NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'), (NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com'); SELECT * FROM tab_user; /* 创建表 tab_favorite rid 旅游线路 id，外键 date 时间 uid 用户 id，外键 rid 和 uid 不能重复，设置复合主键，同一个用户不能同一个线路两次 */ CREATE TABLE tab_favorite (  rid INT, DATE DATETIME,  uid INT, -- 创建复合主键 PRIMARY KEY(rid,uid), FOREIGN KEY (rid) REFERENCES tab_route(rid), FOREIGN KEY(uid) REFERENCES tab_user(uid) ); -- 增加表数据 INSERT INTO tab_favorite VALUES (2, '2018-02-11', 1), -- 老王选择桂林 (3, '2018-03-21', 1), -- 老王选择泰国 (2, '2018-04-21', 2), -- 小王选择桂林 (3, '2018-05-08', 2), -- 小王选择泰国 (4, '2018-06-02', 2); -- 小王选择迪士尼 SELECT * FROM tab_favorite; 

范式【了解】

设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库. 这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。  目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。 

第一范式（1NF）
每一列都是不可分割的原子数据项

第二范式（2NF）

在1NF的基础上，非码属性必须完全依赖于主码（在1NF基础上消除非主属性对主码的部分函数依赖）

第三范式（3NF）
在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）