day2-mysql数据库-约束和设计

day2约束和设计

一:约束

1. 概念 :对数据库中的数据进行限定,保证数据的正确性,有效性和完整性.

2. 分类:

   1. 非空约束: not null
      
      添加 注意使用modify
      
      ​	alter table 表名 modify 字段名 字段类型
      
      去除约束: modify
      
      ​	ALTER TABLE 表名 MODIFY 字段名 字段类型;
      
      默认值: 非空字段 default
      
      ​	字段名 字段类型 DEFAULT 默认值
      

   2. 唯一约束: unique
      
      创建表时添加
      
      ​	CREATE TABLE 表名(
      ​				 	字段名 字段类型 UNIQUE
      ​					);
      
      去除唯一约束: drop index
      
      ​	ALTER TABLE 表名 DROP INDEX 唯一约束名;
      
      创建表时添加唯一约束: modify
      
      ​	ALTER TABLE 表名 MODIFY 字段名 字段类型 UNIQUE;
      

   3. 主键约束: 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 自增长从新开始
      

   4. 外键约束: 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 
      
      

3. 多表之间的关系

   1. 一对一:实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。
   2. 一对多:* 实现方式：在多的一方建立外键，指向一的一方的主键
   3. 多对多:实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段分别指向两张表的主键
   

4. 案例

   需求： 设计一个旅游网，有许多旅游线路分类，每个分类下有多条旅游线路。用户可以收藏自己喜欢的旅游线路。
   	表设计分析：
   		# 线路分类表 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; 
   
   

5. 范式 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,非主键外的所有字段必须互不依赖,其目的是消除传递函数依赖