MySQL数据库（三）

MySQL数据库（一）：
https://blog.csdn.net/Veer_c/article/details/103844059
MySQL数据库（二）：
https://blog.csdn.net/Veer_c/article/details/103844537
MySQL数据库（三）：
https://blog.csdn.net/Veer_c/article/details/103844739

数据库的三大范式

-- 第一大范式：要求表中的每一个字段都是一个独立的不可拆分的字段
-- student表
-- id   姓名(曾用名|现用名)     年龄
-- 1    张翔|张含               30
-- 2    王勇|张刚               40
-- 需求：查询曾用名中姓张的学生
-- select * from student where name like '张%'；
-- 为了遵守第一大范式，我们可以将上面的student表进行修改
-- id   oldName   nowName    age
-- 1    张翔      张晗        30
-- 2    王勇      张刚        40
-- 第二大范式：一张表只能表达一个意思
-- student
-- id     name     age    商品id(商品id)
-- 1      郭德纲   40     1
-- 为了满足第二大范式，我们在这里必须重新创建一个商品表
-- product表
-- 商品id     商品名称
-- 1          纸尿裤
-- 2          刮胡刀
-- student表
-- id    name    age
-- 第三大范式：要求表中的每一个字段只能和主键有决定性的关系
-- 员工表，这样做的话不满足第三大范式
-- 员工id     姓名      部门id      部门名称
-- 1          郭德纲     1          软件开发部
-- 2          岳云鹏     2          软件维护部
-- 3          刘德华     3          后勤部
-- 上面的表结构不满足第三大范式，我们对其进行改造,拆分成两张表
-- 员工表
-- id    name  deptId
-- 部门表
-- id   deptName
-- 当我们降低了数据冗余之后，就会形成多张表，在我们进行查询数据的时候，我们是一张表查询数据快呢？
-- 还是多张表查询数据快呢？
-- 结论：存储空间和你的查询效率之间是一个矛盾的东西，当你降低了数据的冗余度的时候你的查询效率就会降低
-- 当数据的存储空间占用的比较大的时候，我们不关心数据冗余这个问题的时候，但是查询效率比较高

多表查询

1.交叉连接查询

 需求：查询每一个员工的姓名以及对应的部门名称
-- 预计结果：
-- 姓名    部门名称
-- 华仔    软件开发部
-- 郭德纲  软件维护部
SELECT NAME,deptName 
FROM employee,dept;

分析：实际出现的结果和我们的预想不一样，实际结果是我们每一个成员都会对应出现每一一部门名称，这个就是因为没有添加多张表的连接条件
出现的结果俗称笛卡尔乘积，计算公式：每个成员的name字段 乘以 部门表的deptName字段；
多表查询的条件：
(1)：确定需要查询那些表
(2)：确定需要查询那些字段
(3)：需要足够的连接条件（连接条件的数据量-1）

2.内连接（查询 用的最多）
查询时，只有满足链接条件的结果才能显示出来
需求：查询员工对应的部门名称

SELECT e.name,d.deptName
-- 需要查询的表
FROM employee e,dept d
-- 连接条件
WHERE e.deptId=d.id;
-- 内连接另一种写法
SELECT NAME,deptName
FROM employee e
INNER JOIN dept d
ON e.deptId=d.id;

3.左外链接
优先显示左表，满足连接条件就给予显示，如果不满足，则显示结果为null，LEFT OUTER JOIN 左边的表就称之为左表，右边的表的为右表。
需求：查询员工对应的部门名称。

SELECT deptName,NAME
FROM dept d
LEFT OUTER JOIN employee e
ON e.deptId=d.id;

**4.右外连接 **
优先显示右表，如果满足条件就显示满足的结果，不满足就显示null，RIGHTER OUTER 左边的为左表，右边的为右表
需求：查询员工对应的部门名称

SELECT deptName,NAME
FROM employee e
RIGHT OUTER JOIN dept d
ON e.deptId=d.id;

5.自连接查询
需求：给员工表插入bossId这个字段，每一个员工对应一个老板，查询每个员工老板的名字。

ALTER TABLE employee ADD COLUMN bossId INT;
SELECT e.name,b.name   
FROM employee e,employee b
WHERE e.bossId=b.id;
只能查询出满足条件的结果

使用内连接的时候：我们本质上使用一张表，为了满足链接条件，我们会虚拟出来一张表， 即上述代码的 employee b 。
利用左外链接改造上述代码：

SELECT e.name,b.name
FROM employee e
LEFT OUTER JOIN employee b
ON e.bossId=b.id;

存储过程（类似于Java中的方法）

存储过程：多个sql语句组成的具有一定逻辑的语句，即sql编程。
存储过程的特点：
1.保存在mysql数据库的服务端
2.我们可以直接在客户端发送指令，去调用我们在服务端的存储过程
3.存储过程的移植性非常差

当我们需要程序一个员工的信息的时候，我们可以：
SELECT * FROM employee WHERE id=2;
SELECT * FROM employee WHERE id=3;
SELECT * FROM employee WHERE id=4;
这样显非常麻烦 ，于是我们可以将上述过程抽取成为一个存储过程，然后再去调用它

1.存储过程的语法
	-- delimiter $  -- 声明一个结束符
	-- create procedure pro_Test(输入或者输出参数)
	-- begin
	-- 带有逻辑的sql语句
	-- end$
	-- 调用存储过程：
	-- call pro_Test(实参);
	-- 存储过程的输入输出参数如何表示呢？
	-- in 输入参数的变量名称 类型
	-- out 输出参数的名称 类型
	-- inout 输入输出参数名称 类型
2.创建一个带有输入参数的存储过程，并调用
	DELIMITER $
	CREATE PROCEDURE pro_QueryEmpById(IN eId INT)
	BEGIN
	SELECT * FROM employee WHERE id=eId;
	END$
	-- 调用上面的带有输入参数的存储过程
	-- 需求，查询id为4的员工信息
	CALL pro_QueryEmpById(4);
3.当我们在调用一个输出参数的存储过程的时候，总会有一个返回值的，我们应该利用什么来接受这个返回值，数据库中有哪些变量可以供我们使用？
	-- 1.全局变量(mysql数据库的系统变量)：随着mysql数据库的启动而存在，随着mysql数据库的关闭而消失
	-- 查看mysql数据库的全局变量
	SHOW VARIABLES;
	-- 查看mysql数据库中全部变量和字符相关的
	SHOW VARIABLES LIKE 'character%';
	-- 如何去查看mysql数据库的全局变量
	-- select @@+全局变量名
	-- 如何去改变数据库的全局变量
	-- set @@+全局变量=值
	SELECT @@character_set_client;
	SET @@character_set_client='utf8';
	-- 2.会话变量：存在于某一次会话中，随着会话的结束而消失
	-- 如何去查看一个会话变量
	-- select @+变量名 
	-- 给会话变量设置值
	-- set @变量名=值
	SET @n='hello';
	SELECT @n;
	-- 3.局部变量：位于存储过程中，随着存储过程而存在，随着存储过程的调用完毕而消失
	-- 给局部变量设置值
	-- set 局部变量=值
	-- 查看局部变量
	-- select 局部变量
4.创建一个带有输出参数的存储过程
	DELIMITER $
	CREATE PROCEDURE pro_TestOut(OUT str VARCHAR(20))
	BEGIN
	-- 给输出参数赋值
	SET str='我是输出参数';
	END$
	在调用这个存储过程时，它会返回一个字符串值
	我们应该来定义一个回话变量，用来接受这个值
	CALL pro_TestOut(@nn)
	SELECT @nn;
5.创建一个带有判断的存储过程
	-- 需求： 输入一个num整数，num=1 ,输出‘星期一’,num=2,输出‘星期二’，
	-- num=3,输出‘星期三’，否则，输出‘错误参数’
	DELIMITER $
	CREATE PROCEDURE pro_TestDay(IN num INT,OUT d VARCHAR(20))
	BEGIN
	    IF num=1 THEN
	        SET d='星期一';
	    ELSEIF num=2 THEN
	        SET d='星期二';
	    ELSEIF num=3 THEN
	        SET d='星期三';
	    ELSE 
	        SET d='参数错误';
	    END IF;
	END$
	-- 调用上面的这个带有输入和输出参数的存储过程
	CALL pro_TestDay(4,@d);
	SELECT @d;
6.创建一个带有循环过程的存储过程
	-- 需求： 输入一个num，计算从1到num的总和。
	DELIMITER $
	CREATE PROCEDURE pro_TestSum(IN num INT,OUT score INT)
	BEGIN
	    DECLARE i INT DEFAULT 1;
	    DECLARE res INT DEFAULT 0;
	    WHILE i<=num DO
	    SET res=res+i;
	    SET i=i+1;
	    END WHILE;
	    -- 将局部变量res的值赋值给score
	    SET score=res;
	END$
	-- 调用上面的存储过程
	CALL pro_TestSum(200,@score);
	SELECT @score;
7.携带数据库的数据给输出参数（into），本质上就是把一个给另一个参数的赋值过程
	-- 需求： 传入员工id，查询对应的员工，输出员工姓名
	DELIMITER $
	CREATE PROCEDURE pro_QueryNameById(IN eId INT,OUT eName VARCHAR(20))
	BEGIN
	SELECT NAME INTO eName FROM employee WHERE id=eId;
	END$
	-- 调用这个存储过程
	CALL pro_QueryNameById(2,@eName);
	SELECT @eName;
8.删除存储过程：drop + 存储过程名
	DROP PROCEDURE pro_QueryEmpById;

触发器

当我们在向一张表中，插入，删除，修改数据的时候，我们可以将这些操作记录在一张表上，这就需要触发器。

-- 创建一个日志表
CREATE TABLE empLog(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20)
);
-- 创建一个触发器，当往员工表中插入一条数据的时候，自动往日志表中添加一条插入的记录
CREATE TRIGGER tri_empInsert AFTER INSERT ON employee FOR EACH ROW 
INSERT INTO empLog(content) VALUES('员工表中被插入一条数据');
-- 给员工表中插入一条数据
INSERT INTO employee(NAME) VALUES('周华健');
-- 创建一个触发器，当往员工表中修改一条数据的时候，往日指标中添加一条记录
CREATE TRIGGER tri_empUpdate AFTER UPDATE ON employee FOR EACH ROW 
INSERT INTO empLog(content) VALUES('员工表中被修改一条数据');
-- 修改员工表中的一条数据
UPDATE employee SET NAME='成龙' WHERE id=7;
-- 创建一个触发器，当删除员工表的一条数据的时候，给日志表中添加一天删除的记录
CREATE TRIGGER tri_empDelete AFTER DELETE ON employee FOR EACH ROW 
INSERT INTO empLog(content) VALUES('员工表中被删除一条数据');
-- 删除周华健
DELETE FROM employee WHERE id=7;
SELECT * FROM empLog;
SELECT * FROM employee;

注意：每个触发器的功能是不一样的，我们必须用不同的触发器来记录不同的操作！

数据库的权限问题

root用户：
是我们的超级管理员，拥有所有的权限管理，它可以对我们的（数据库，表，数据）进行增删改查。

1.查询mysql数据库的用户信息
	SELECT * FROM USER;
2.当我们查看我们的用户密码的时候，发现它并不是我们原来的密码，这个是因为数据库对我们的密码使用了MD5单向加密算法 。
	加密函数是 ：password();
	SELECT PASSWORD('root');
3.如何修改我们的密码信息
	-- 更改root这个用户名的密码‘123456’
	UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
4. 我们也可以为一些用户分配不同的权限，方便管理我们的数据库
	-- 创建一个新的用户，并且给新的用户一些特有的权限呢？
	GRANT SELECT ON day20.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

注意：第一个password是一个定义的一个变量，第二个password是password函数

MySQL数据库（一）：
https://blog.csdn.net/Veer_c/article/details/103844059
MySQL数据库（二）：
https://blog.csdn.net/Veer_c/article/details/103844537
MySQL数据库（三）：
https://blog.csdn.net/Veer_c/article/details/103844739