MySQL学习笔记二

写在前面：
本文为在java黑马教程的学习视频基础上自己整理出的学习笔记
目的是方便以后回顾学习、巩固知识
如有侵权，望联系我删除

一：约束

概念： 对表中的数据进行限定，保证数据的正确性、有效性、和完整性。

分类：
	1.主键约束：primary key
	2.非空约束:not null
	3.唯一约束:unique
	4.外键约束:foreign key

(一)：非空约束(not null)

值不能为空

-- 创建表后，添加非空约束
CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL   -- 约束name非空
);

-- 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);

-- 创建表后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

（二）：唯一约束(unique)

值不能重复

创建表的时候添加唯一约束

-- 创建表时，添加唯一约束
CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20)  UNIQUE  -- 添加唯一约束
);
-- 注意：mysql中，唯一约束限定的列的值可以有多个null

注意：mysql中，唯一约束限定的列的值可以有多个null
删除唯一约束

-- 删除唯一约束
-- alter table stu modify phone_number varchar(20); -- 不能这样删除唯一约束
ALTER TABLE stu DROP INDEX phone_number; -- 正确删除唯一约束的方法

创建表后添加唯一约束

-- 创建表后添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

注意：创建表后添加唯一约束前，应保证表中要添加唯一元素的列的值是唯一的。否则添加唯一元素不成功。

（三）：主键约束(primary key)

主键约束（primary key）含义：非空且唯一
一张表只能有一个字段为主键
主键就是表中记录的唯一标识

在创建表时，添加主键约束

create table stu(
	id int primary key,	-- 给id添加主键约束
	name varchar(20)
);

删除主键约束

-- 删除主键约束
-- alter table stu modify id int; -- 不生效（唯一约束不能这样删除，主键约束更不能这样删除了）
-- 正确方法
ALTER TABLE stu DROP PRIMARY KEY; --  一个表中只有一个主键，故删除表中的主键时，给出表的名称即可。

创建表后添加主键

-- 创建表后添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;

自动增长

概念：如果某一列是数值类型的，使用auto_increment可以完成自动增长（一般配合主键使用）

在创建表时，添加主键约束，并且完成主键自增长

create table stu(
	id int primary key auto_increment, 	-- 给id添加主键约束,并且完成自增长
	name varchar(20)
);

-- 删除自动增长
ALTER TABLE stu MODIFY id INT;	  -- 删除自动增长，主键

-- 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

(四)：外键约束（foreign key）

让表与表之间产生关系，从而保证数据的正确性

在创建表时，可以添加外键

-- 语法：
create table 表名(
	……
	外键列
	[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;
/*
分类：
	1.级联更新：on update cascade
	2.级联删除：on delete cascade
*/

二：数据库的设计

（一）：表之间的关系

分类：

• 一对一：如人和身份证（一个人只有一个身份证，一个身份证只能对应一个人）
• 一对多（多对一）：如部门和员工（一个部门有多个员工，一个员工只能对应一个一个部门）
• 多对多：学生和课程（一个学生可以选择很多门课程，一门课程也可以被很多学生选择）

实现关系：

1. 一对一：
   如：人和身份证
   实现方式：一对一关系，可以在任意一方添加唯一外键指向另一方的主键。或让两张表的主键对应相同。
   

2. 一对多（多对一）：
   如：员工和部分
   实现方式：在多的一方，建立外键，指向一的一方的主键。
   

3. 多对多
   如：学生和课程
   实现方式：多对多关系的实现需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。
   

旅游网数据库设计小案例：
分析：

sql实现代码：

-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
	 cid INT PRIMARY KEY AUTO_INCREMENT,	-- cid:   主键，自增长
	 cname VARCHAR(100) NOT NULL UNIQUE 	-- cname: 非空，唯一
);

-- 创建旅游线路表 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)		-- 创建外键 将线路和分类联系起来
);

/*
创建用户表 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,		-- 非null、唯一
	 PASSWORD VARCHAR(30) NOT NULL,			-- 唯一
	 NAME VARCHAR(100),				
	 birthday DATE,
	 sex CHAR(1) DEFAULT '男',  			-- default:默认为 男 
	 telephone VARCHAR(11),
	 email VARCHAR(100)
);

-- 线路和用户的中间表
/*
创建收藏表 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)
);

（二）：数据库设计的范式

在设计数据库时需要遵循的规范就是数据库设计范式，要遵循后面的范式，必须先遵循前面的范式

百度百科解释：

普通表：

1NF:

2NF:

3NF:

三：数据库的备份和还原

1. 命令行的方式：

/*
语法：
	备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
	还原：
		1.登录数据库
		2.创建数据库
		3.使用数据库
		4.执行文件 	source 文件路径
*/  

备份完成后我们可以看到桌面上多了一个aaa.sql的文件，用记事本打开发现文件中存放的是一些sql语句

还原过程：

2. 图形化工具：
   SQLyog中操作：
   备份：db1->备份/导出->备份数据库，转储到SQL选择路径即可
   还原：[e-mail]>执行SQL脚本