数据库（二）

一， 数据约束（2）
1.外键约束：当我们在使用一张表的时候，如果有多个数据出现重复值，我们为了节省内存可以利用两张表，当一张表的数据来源于另外一张表的时候，这种情况就会出现一个外键约束。

1，先创建一个数据库，然后在创建一个员工表，用于存储员工的信息CREATE DATABASE day20 DEFAULT CHARACTER SET utf8;-- 创建一张员工表(id,name,age,deptName)CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT,deptName VARCHAR(20));-- 给表中添加几条数据INSERT INTO employee(NAME,age,deptName) VALUES('华仔',50,'软件开发部');INSERT INTO employee(NAME,age,deptName) VALUES('小月月',30,'软件开发部');INSERT INTO employee(NAME,age,deptName) VALUES('孙胖子',40,'软件开发部');INSERT INTO employee(NAME,age,deptName) VALUES('郭德纲',50,'软件维护部');INSERT INTO employee(NAME,age,deptName) VALUES('谦哥',50,'软件维护部');INSERT INTO employee(NAME,age,deptName) VALUES('张学友',55,'秘书部');

分析：在这个过程中我们的deptname 字段出现了大量重复发的值，这个是非常浪费我们的数据库的内存的，俗称数据冗余，我们可以将上述代码进行改造，可以创建两张表，一张用于存储学生的信息，一张专门用于存储部门信息，这样可以为我们的数据库节省内存

-- 在我们的数据库中创建一张部门表CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,deptName VARCHAR(20));-- 给部门表插入数据INSERT INTO dept(deptName) VALUES('软件开发部');INSERT INTO dept(deptName) VALUES('软件维护部');INSERT INTO dept(deptName) VALUES('秘书部');

1.1 给部门加上ID字段后，创建一个改造过后的数据表

-- 给员工表加上部门id之后，改造员工表CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT,deptId INT);-- 给改造后的员工表中插入数据INSERT INTO employee(NAME,age,deptId) VALUES('华仔',50,1);INSERT INTO employee(NAME,age,deptId) VALUES('小月月',30,1);INSERT INTO employee(NAME,age,deptId) VALUES('孙胖子',40,1);INSERT INTO employee(NAME,age,deptId) VALUES('郭德纲',50,2);INSERT INTO employee(NAME,age,deptId) VALUES('谦哥',50,2);INSERT INTO employee(NAME,age,deptId) VALUES('张学友',55,4);

分析：在我们改造过后的数据表中，当再次给员工表中插入一个不存在的ID字段的时候，依然是可以插入的，但是这中情况明显是错的，所以我们要给员工的deptId 字段 设置一个外键约束，让这个字段依赖于部门表的ID字段这样不会出错

CREATE TABLE employee(-- 附表id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT,deptId INT,-- 外键CONSTRAINT emp_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)-- 声明      外键名称   外键        字段   依赖于   那张表的那个字段);

附表：受约束的表
主表：约束附表的表
1.2 什么情况下外键会起作用？
(1)：当我们向附表中插入主表中不存在的数据的时候，
(2)：当我们要删除附表中还在是使用主表的数据的时候，
(3)：当我们将附表中的数据修改为主表中不存在的数据的时候，
1.3 有了外键之后，我们如何操作数据库

-- 1.插入数据的时候，先插入主表，再插入副表-- 需求：给副表中插入刘亦菲，deptId=4,INSERT INTO dept(deptName) VALUES('后勤部');INSERT INTO employee(NAME,age,deptId) VALUES('刘亦菲',30,4);-- 2.当我们修改副表中的数据的时候，先修改主表-- 需求：将华仔的部门改为4UPDATE employee SET deptId=4 WHERE id=1;-- 3.删除主表数据的时候，先得将副表中的数据删除干净，再去删除主表中的数据-- 需求：删除主表中的id为4的部门DELETE FROM employee WHERE id=1;DELETE FROM employee WHERE id=8;DELETE FROM dept WHERE id=4;

1. 级联技术
   当我们修改或者删除主表的时候，附表会同时收到影响，更加方便我们的操作

-- 需要将部门表中的秘书部id改为4-- 创建一张员工表，添加外键约束，并且添加级联(修改的级联技术ON UPDATE CASCADE)CREATE TABLE employee(-- 附表id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT,deptId INT,-- 外键CONSTRAINT emp_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE-- 声明      外键名称   外键        字段   依赖于   那张表的那个字段);-- 修改部门表中的秘书部id该4UPDATE dept SET id=4 WHERE id=3;-- 删除id为4的秘书部,ON DELETE CASCADEDELETE FROM dept WHERE id=4;

二，数据库的三大范式

-- 第一大范式：要求表中的每一个字段都是一个独立的不可拆分的字段-- 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. 1：多表查询的条件
(1)：确定需要查询那些表
(2)：确定需要查询那些字段
(3)：需要足够的连接条件（连接条件的数据量-1）
2.内连接（查询 用的最多）：查询时，只有满足链接条件的结果才能显示出来
需求：查询员工对应的部门名称

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

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

SELECT deptName,NAMEFROM dept dLEFT OUTER JOIN employee eON e.deptId=d.id;

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

SELECT deptName,NAMEFROM employee eRIGHT OUTER JOIN dept dON e.deptId=d.id;

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

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

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

SELECT e.name,b.nameFROM employee eLEFT OUTER JOIN employee bON 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)BEGINSELECT * 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))BEGINSELECT 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;

注意：每个触发器的功能是不一样的，我们必须用不同的触发器来记录不同的操作！
四，数据库的权限问题
1. root用户：是我们的超级管理员，拥有所有的权限管理,它可以对我们的（数据库，表，数据）进行增删改查。
查询mysql数据库的用户信息

SELECT * FROM USER;

1. 当我们查看我们的用户密码的时候，发现它并不是我们原来的密码，这个是因为数据库对我们的密码使用了MD5单向加密算法 。
   加密函数是 ：password();

SELECT PASSWORD('root');

1. 如何修改我们的密码信息

-- 更改root这个用户名的密码‘123456’UPDATE USER SET PASSWORD=PASSWORD('root') WHERE USER='root';

注意：第一个password是一个定义的一个变量，第二个password是我门的password函数
4. 我们也可以为一些用户分配不同的权限，方便管理我们的数据库

-- 创建一个新的用户，并且给新的用户一些特有的权限呢？GRANT SELECT ON day20.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';