day2约束和设计
一:约束
-
概念 :对数据库中的数据进行限定,保证数据的正确性,有效性和完整性.
-
分类:
-
非空约束: not null 添加 注意使用modify alter table 表名 modify 字段名 字段类型 去除约束: modify ALTER TABLE 表名 MODIFY 字段名 字段类型; 默认值: 非空字段 default 字段名 字段类型 DEFAULT 默认值
-
唯一约束: unique 创建表时添加 CREATE TABLE 表名( 字段名 字段类型 UNIQUE ); 去除唯一约束: drop index ALTER TABLE 表名 DROP INDEX 唯一约束名; 创建表时添加唯一约束: modify ALTER TABLE 表名 MODIFY 字段名 字段类型 UNIQUE;
-
主键约束: primary key 3.1特性: 非空并且唯一 一张表只能有一个字段为在主键 主键时表中记录的唯一标识,能够通过主键确定表中一条数据 3.2创建表时,添加主键约束 CREATE TABLE 表名( 字段名 字段类型 PRIMARY KEY ); 3.3删除主键: ALTER TABLE 表名 DROP PRIMARY KEY; 3.4创建完表后,添加主键 ALTER TABLE 表名 ADD PRIMARY KEY(字段); 3.5主键自动增长(整形主键): CREATE TABLE 表名( 字段名 字段类型 PRIMARY KEY AUTO_INCREMENT, ); 3.6去除自动增长: ALTER TABLE 表名 MODIFY 字段名 字段类型 ; 3.7添加自动增长 ALTER TABLE 表名 MODIFY 字段名 字段类型 AUTO_INCREMENT; 3.8修改起始值 创建表时语法:CREATE TABLE 表名( 字段名 字段类型 PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20) ) AUTO_INCREMENT=起始值; 表创建后语法:ALTER TABLE 表名 AUTO_INCREMENT=起始值; DELETE 和 TRUNCATE 对自增长有什么影响 -- DELETE 对自增长没有影响 -- TRUNCATE 自增长从新开始
-
外键约束: foreign key:让表产生关系,从而保证数据的正确性. * 几个概念: 外键:在从表中与主表主键对应的那一列 主表:一方,用来约束别人的表 从表: 多方,被别人约束的表 * 在创建表时,可以添加外键 [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) * 删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; * 已有表增加外键 ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名); * 级联操作 1. 添加级联操作 语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ; 2. 分类 1) 级联更新: ON UPDATE CASCADE 2) 级联删除: ON DELETE CASCADE
-
-
多表之间的关系
1. 一对一:实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。 2. 一对多:* 实现方式:在多的一方建立外键,指向一的一方的主键 3. 多对多:实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段分别指向两张表的主键
-
案例
需求: 设计一个旅游网,有许多旅游线路分类,每个分类下有多条旅游线路。用户可以收藏自己喜欢的旅游线路。 表设计分析: # 线路分类表 tab_category -- cid 旅游线路分类主键,自动增长 -- cname 旅游线路分类名称非空,唯一,字符串 100 # 旅游线路表 tab_route -- rid 旅游线路主键,自动增长 -- rname 旅游线路名称非空,唯一,字符串 100 -- price 价格 -- rdate 上架时间,日期类型 -- cid 外键,所属分类 # 用户表 tab_user -- uid 用户主键,自增长 -- username 用户名长度 100,唯一,非空 -- password 密码长度 30,非空 -- name 真实姓名长度 100 -- birthday 生日 -- sex 性别,定长字符串 1 -- telephone 手机号,字符串 11 -- email 邮箱,字符串长度 100 # 收藏表 tab_favorite -- rid 旅游线路 id,外键 -- uid 用户 id,外键 -- date 收藏时间 -- 注意:rid 和 uid 不能重复,设置复合主键,同一个用户不能收藏同一个线路两次 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 (11, '2018-01-01', 1), -- 老王选择厦门 (12, '2018-02-11', 1), -- 老王选择桂林 (13, '2018-03-21', 1), -- 老王选择泰国 (12, '2018-04-21', 2), -- 小王选择桂林 (13, '2018-05-08', 2), -- 小王选择泰国 (15, '2018-06-02', 2); -- 小王选择迪士尼 SELECT * FROM tab_favorite;
-
范式 Normal form 简称NF
1. 概念: 设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式。换句话就是一张数据表的表结构所符合的某种设计标准的级别。 2. 范式分类: 六种范式: * 第一范式(INF) * 第二范式(2NF) * 第三范式(3NF) * 巴斯-科德范式(BCNF) * 第四范式(4NF) * 第五范式(5NF,又称完美范式)。 各种范式呈递次规范,越高的范式数据库冗余越小。 3. 1NF 数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等非原子数据项。简而言之,第一范式就是无重复的域。 4. 2NF 第二范式(2NF)在满足第一范式前提下,表中的每个字段都完全依赖于主键。所谓完全依赖是指不能存在仅依赖主键一部分的列。 简而言之,第二范式就是在第一范式的基础上属性完全依赖于主键。 5. 3NF ,第三范式(3NF)在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)要求一个关系中不包含已在其它关系已包含的非主关键字信息。 简而言之,第三范式就是属性不依赖于其它非主属性,也就是在满足2NF的基础上,任何非主属性不得传递依赖于主属性。 6. 优点 规范化的优点是明显的,它避免了大量的数据冗余,节省了存储空间,保持了数据的一致性。 7. 总结: 1NF: 字段是最小的的单元不可再分 2NF:满足1NF,表中的字段必须完全依赖于全部主键而非部分主键,其目的是消除部分函数依赖 3NF:满足2NF,非主键外的所有字段必须互不依赖,其目的是消除传递函数依赖