Mysql学习笔记06-数据库范式

数据库设计的范式

	* 概念：设计数据库时，需要遵循的一些规范。

1.要遵循后边的范式要求，必须先遵循前边的所有范式要求
2.设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

	目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、
	巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

• 分类：
  ① 第一范式（1NF）：每一列都是不可分割的原子数据项（系=系名+系主任）
  
  ②第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
  * 几个概念：
  1. 函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
  例如：学号–>姓名。 （学号，课程名称） --> 分数
  2. 完全函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
  例如：（学号，课程名称） --> 分数
  3. 部分函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
  例如：（学号，课程名称） – > 姓名
  4. 传递函数依赖：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
  例如：学号–>系名，系名–>系主任
  5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
  例如：该表中码为：（学号，课程名称）
  * 主属性：码属性组中的所有属性
  * 非主属性：除过码属性组的属性
  
  ③第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
  

数据库的备份和还原

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

多表查询：

* 查询语法：
	Select  列名列表 from表名列表 where....
* 准备sql
	# 创建部门表
	CREATE TABLE dept(
		id INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(20)
	);
	INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
	# 创建员工表
	CREATE TABLE emp (
		id INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(10),
		gender CHAR(1), -- 性别
		salary DOUBLE, -- 工资
		join_date DATE, -- 入职日期
		dept_id INT,
		FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
	);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
* 笛卡尔积：
	* 有两个集合A,B .取这两个集合的所有组成情况。
	* 要完成多表查询，需要消除无用的数据
* 多表查询的分类：
	1. 内连接查询：
		1. 隐式内连接：使用where条件消除无用数据
			* 例子：
			-- 查询所有员工信息和对应的部门信息
			SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
			-- 查询员工表的名称，性别。部门表的名称
			SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
			
			SELECT 
				t1.name, -- 员工表的姓名
				t1.gender,-- 员工表的性别
				t2.name -- 部门表的名称
			FROM
				emp t1,
				dept t2
			WHERE 
				t1.`dept_id` = t2.`id`;
2. 显式内连接：
		* 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
		* 例如：
* SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;	
* SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;	

		3. 内连接查询：
			1. 从哪些表中查询数据
			2. 条件是什么
			3. 查询哪些字段
	2. 外链接查询：
		1. 左外连接：
			* 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
			* 查询的是左表所有数据以及其交集部分。
			* 例子：
				-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
				SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
		2. 右外连接：
			* 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
			* 查询的是右表所有数据以及其交集部分。
			* 例子：
				SELECT 	* FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

子查询：

• 概念：查询中嵌套查询，称嵌套查询为子查询。
  – 查询工资最高的员工信息
  – 1 查询最高的工资是多少 9000
  SELECT MAX(salary) FROM emp;
  – 2 查询员工信息，并且工资等于9000的
  SELECT * FROM emp WHERE emp.salary = 9000;
  – 一条sql就完成这个操作。子查询
  SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
• 子查询不同情况
  1. 子查询的结果是单行单列的：
     • 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =
       – 查询员工工资小于平均工资的人
       SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
  2. 子查询的结果是多行单列的：
     * 子查询可以作为条件，使用运算符in来判断
     – 查询’财务部’和’市场部’所有的员工信息
     SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’;
     SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
     – 子查询
     SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’);

3. 子查询的结果是多行多列的：
   * 子查询可以作为一张虚拟表参与查询
   – 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
   – 子查询

SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
					WHERE t1.id = t2.dept_id;

– 普通内连接

SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'