数据库（二）--数据约束 数据库设计 多表查询 MySQL存储过程 触发器 数据库权限问题

数据约束

1.给表添加数据约束，从而约束用户操作表的行为。

2.默认值约束（default）：当一个字段有默认值约束时，如果给表中插入数据时没有给该字段插入，则给该字段分配一个默认值。

CREATE TABLE test(    NAME VARCHAR(20),    gender VARCHAR(2) DEFAULT '男')INSERT INTO test(NAME) VALUES('张三');INSERT INTO test(NAME,gender) VALUES('张三',NULL);SELECT * FROM test;

当不插入gender字段的时候，会给他分配一个‘男’默认值。

3.非空约束（not null）：非空约束的字段一定要有值（不能不插入数据，不能为null）。

CREATE TABLE test(    NAME VARCHAR(20) NOT NULL,    gender VARCHAR(2))

4.唯一约束（unique）：唯一约束的字段不能出现重复值。
（1）不能插入重复的值；
（2）唯一约束可以插入多个null，所以唯一约束不能约束null。

CREATE TABLE test(    id INT UNIQUE,    NAME VARCHAR(20))

5.主键约束（primary key）：唯一+非空。
（1）通常情况下，我们会给每张表都会设置一个主键字段，用来标记记录的唯一性；
（2）但是不建议把业务含义字段作为主键，因为随着业务的变化，业务字段可能会出现重复；
（3）建议给每张张独立添加一个叫id的字段，把这个id字段设置成主键，用来作为记录的唯一性。

CREATE TABLE test(    id INT PRIMARY KEY,    NAME VARCHAR(20))

6.自增长约束（auto_increment）：自增长约束的字段初始值为0，每次递增1。

CREATE TABLE test(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20))

注意：
（1）delete from 这种删除数据的方式，无法重置自增长的主键；
（2）删除全表数据的truncate table 表名 ，删除全表数据，这种删除全表数据的方式可以重置主键，即可以把自增长的初始值置为0。

7.外键约束：约束两张表。

CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)    --          外键名称                 外键字段  参考       哪张表的哪个字段   

员工表：

CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    deptName VARCHAR(20))INSERT INTO employee(NAME,deptName) VALUES('张三','软件开发部');INSERT INTO employee(NAME,deptName) VALUES('李四','软件维护部');INSERT INTO employee(NAME,deptName) VALUES('王五','软件开发部');INSERT INTO employee(NAME,deptName) VALUES('陈六','软件开发部');

问题：每次插入员工数据时，部门名称数据会出现重复（冗余）,如果数据出现冗余，那么会浪费数据库存储空间。
为了解决部门名称数据冗余的问题，可以设计一张独立的部门表，把部门名称放单部门表中，并设置外键约束，防止非法数据的插入（例如员工表中插入部门表中没有的部门id）。

CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    deptId INT,    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)    --          外键名称                     外键字段  参考       dept表的id字段   )-- 部门表（主表：约束别人的表）CREATE TABLE dept(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20))SELECT * FROM employee;SELECT * FROM dept;INSERT INTO employee(NAME,deptId) VALUES('陈六',1);INSERT INTO employee(NAME,deptId) VALUES('王五',1);-- 插入数据INSERT INTO employee(NAME,deptId) VALUES('张三',3); -- 违反外键约束：a foreign key constraint fails -- 修改数据UPDATE employee SET deptId=5 WHERE id=2; -- a foreign key constraint fails-- 删除数据DELETE FROM dept WHERE id=1; -- a foreign key constraint fails

主表：约束别人的表。
副表：被别的表约束，外键设置在副表。

外键约束起作用的时候：
（1）插入数据：当往副表插入了主表不存在的数据时，外键起作用；
（2）修改数据：当往副表修改为主表不存在的数据时，外键起作用；
（3）删除数据: 副表中有关联主表的数据的时候，当删除主表数据，外键其作用。

有了外键后，如何管理数据：
（1） 插入数据： 先插入主表的数据，再插入副表数据；
（2） 修改数据： 先修改主表数据，再修改副表数据。
（3）删除数据：先删除副表数据，再删除主表数据。

8.级联技术：当有了外键的时候，我们希望修改或删除数据的时候，修改或删除了主表的数据，同时能够影响副表的数据，这时就可以使用级联。
（1）添加级联修改： ON UPDATE CASCADE
（2）添加级联删除： ON DELETE CASCADE

CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    deptId INT,    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE     --          外键名称                     外键字段  参考        哪张表的哪个字段     级联修改       级联删除)

---

数据库设计

1.第一范式：要求表的每个字段都是独立不可分割的单元。
（1）违反第一范式的学生表：
学生表： student name
张三|张张
李四|张丽

查询： 现用名中包含‘张’的学生。

select * from student where name like '%张%';

查询结果中会包含曾用名中含‘张’的学生。

（2）符合第一范式的学生表：
学生表： student name old_name
张三 张张
王含 张丽
再查询现用名中包含‘张’的学生就不会出现问题了。

2.第二范式：在第一范式的基础上，要求表的除主键外的其他字段都和主键有依赖关系。一张表只能表达一个意思。
（1）违反第二范式的员工表：
员工表：employee
员工编号 员工姓名 部门名称 订单名称

（2）符合第二范式的员工表：
员工表：employee
员工编号 员工姓名 部门名称

订单表：order
订单编号 订单名称

3.第三范式：在第二范式的基础上，要求标的除主键外的字段都只能和主键有直接决定的依赖关系。
（1）不符合第三范式的员工表（出现数据冗余）：
员工表：employee
员工编号 员工姓名 部门编号 部门名称
1 张三 1 软件开发部
2 李四 1 软件开发部

（2）符合第三范式的员工表（降低数据冗余）：
员工表：employee
员工编号 员工姓名 部门编号
1 张三 1
2 李四 1

部门表： dept
部门编号 部门名称
1 软件开发部

注意：
（1）当我们降低了数据的冗余度的时候，查询效率就会降低；
（2）当数据的存储空间占用的比较大的时候，我们不关心数据冗余这个问题的时候，查询效率比较高。

---

多表查询

多表查询的步骤：
（1）确定查询哪些表；
（2）确定查询哪些字段；
（3）确定连接条件(规则： 表数量-1)。

1.交叉连接查询（笛卡尔乘积: 4*3=12，产生笛卡尔积的原因是没有足够的连接条件）
需求： 查询员工及其部门 。

SELECT employee.name,dept.name FROM employee,dept;

2.内连接查询（使用最多）：只有满足连接条件的数据才会显示出来。

SELECT e.name,d.name    FROM employee e,dept d    WHERE e.deptId=d.id;

另一种写法：

SELECT e.name,d.name    FROM employee e    INNER JOIN dept d    ON e.deptId=d.id;

3.左外连接查询
左外连接查询效果： 左表（部门表）的数据全部显示，右表（员工）的数据当满足连接条件的时候，就显示满足条件的的数据，但是如果不满足连接条件，则显示null。
左表和右表的区分：LEFT OUTER JOIN左边的就叫左表，右边的就叫右表。

需求： 查询部门及其部门的员工（部门全部显示）。
– 预期结果：
– 软件开发部 张三
– 软件开发部 王五
– 软件维护部 李四
– 软件维护部 陈六
– 秘书部 null

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

4.右外连接查询
右外连接查询效果： 右表（部门表）的数据全部显示，左表（员工）的数据当满足连接条件的时候，就显示满足条件的数据，但是如果不满足连接条件，则显示null。

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

5.自连接查询
需求： 查询员工姓名及其上司姓名(没有上司的员工也显示)
– 预期结果：
员工 上司
– 张三 null
– 李四 张三
– 王五 李四
– 陈六 王五

SELECT e.name AS '员工',b.name AS '上司'    FROM employee e    LEFT OUTER JOIN employee b    ON e.bossId=b.id;

---

MySQL存储过程

1.存储过程：带逻辑的多个sql语句。也是sql编程。

2.存储过程特点：
（1）存储过程保存到数据库服务器端，通过数据库客户端工具调用存储过程；
（2）存储过程的效率会非常高，因为存储过程是在数据库服务器端执行；
（3）存储过程的移植性非常差的。

3.创建存储过程：

DELIMITER 结束符号   -- 定义结束符号CREATE PROCEDURE 存储过程名称 (形式参数列表) BEGIN    多个sql语句END 结束符号

4.调用存储过程：

CALL 存储过程名称(实际参数列表);

5.参数类型：
IN： 输入参数，可以携带数据到存储过程中
OUT: 输出参数，可以携带数据到存储过程外面。
INOUT: 输入输出参数。

6.MySQL数据库三种变量：
我们在调用带有输出参数的存储过程的时候，肯定是会返回一个值的，那么我们该使用什么变量去接受存储过程给我返回来的这个值呢？
在我们的数据库中有哪些变量可以充当这个变量使用呢？
（1）全局变量(mysql数据库的系统变量)：随着mysql数据库的启动而存在，随着mysql数据库的关闭而消失。
– 查看mysql数据库的全局变量

SHOW VARIABLES;

– 查看mysql数据库中全部变量和字符相关的

SHOW VARIABLES LIKE 'character%';

– 如何去查看mysql数据库的全局变量

select @@+全局变量名

– 如何去改变数据库的全局变量

set @@+全局变量=值

（2）会话变量：存在于某一次会话中，随着会话的结束而消失。
– 如何去查看一个会话变量

select @+变量名 

– 给会话变量设置值

set @变量名=值

（3）局部变量：位于存储过程中，随着存储过程而存在，随着存储过程的调用完毕而消失。
– 给局部变量设置值

set 局部变量=值

– 查看局部变量

select 局部变量

需求：创建一个不带参数的存储过程，并调用。

DELIMITER $CREATE PROCEDURE pro_test()BEGINSELECT * FROM employee;END$CALL pro_test();

需求：创建一个带有输入参数的存储过程，并调用。

DELIMITER $CREATE PROCEDURE pro_test2(IN eId INT)BEGINSELECT * FROM employee WHERE id=eId;END$CALL pro_test2(3);

需求： 创建一个带有输出参数的存储过程，并调用。

DELIMITER $CREATE PROCEDURE pro_outTest(OUT str VARCHAR(0))BEGINSET str='输出参数';END$SET @nn='world';SELECT @nn;CALL pro_outTest(@nn);SELECT @nn;

7.带有判断条件的存储过程：
需求： 输入一个num整数，num=1 ,输出‘星期一’,num=2,输出‘星期二’，num=3,输出‘星期三’，否则，输出‘错误参数’

DELIMITER $CREATE PROCEDURE pro_out(IN num INT,OUT str VARCHAR(20))BEGIN    IF num=1 THEN         SET str='星期一';    ELSEIF num=2 THEN         SET str='星期二';    ELSEIF num=3 THEN        SET str='星期三';    ELSE        SET str='参数错误';    END IF;END$CALL pro_out(2,@s);SELECT @s;

8.带有循环条件的存储过程：
需求： 输入一个num，计算从1到num的总和。

DELIMITER $CREATE PROCEDURE pro_out2(IN num INT,OUT score INT)BEGIN    DECLARE i INT DEFAULT 1;    DECLARE r INT DEFAULT 0;    WHILE i<num DO    SET r=r+i;    SET i=i+1;    END WHILE;    SET score=r;END$CALL pro_out2(100,@score);SELECT @score;

9.携带数据库的数据给输出参数(INTO)：
需求： 传入员工id，查询对应的员工，输出员工姓名

DELIMITER $CREATE PROCEDURE pro_out3(IN eId INT,OUT eName VARCHAR(20))BEGINSELECT NAME INTO eName FROM employee WHERE id=eId;END$CALL pro_out3(3,@eName);SELECT @eName;

10.删除存储过程：

DROP PROCEDURE pro_out;

---

触发器

1.触发器作用：当往员工表插入/修改/删除一条数据的时候，同时往日志表记录下来，这时就要使用触发器完成。

2.触发器语法：
– 创建一个日志表

CREATE TABLE empLog(id INT PRIMARY KEY AUTO_INCREMENT,content VARCHAR(20));

– 创建一个触发器，当往员工表中插入一条数据的时候，自动往日志表中添加一条插入的记录

CREATE TRIGGER tri_empInsert AFTER INSERT ON employee FOR EACH ROWINSERT INTO empLog(content) VALUES('员工表中被插入一条数据');INSERT INTO employee(NAME) VALUES('张三');

– 创建一个触发器，当往员工表中修改一条数据的时候，往日指标中添加一条记录

CREATE TRIGGER tri_empUpdate AFTER UPDATE ON employee FOR EACH ROWINSERT INTO empLog(content) VALUES('员工表中一条数据被修改');UPDATE employee SET NAME='琪琪'WHERE NAME='李四';

– 创建一个触发器，当删除员工表的一条数据的时候，给日志表中添加一天删除的记录

CREATE TRIGGER tri_empDelete AFTER DELETE ON employee FOR EACH ROWINSERT INTO empLog(content) VALUES('员工表中被删除一条数据');DELETE FROM employee WHERE id=7;

– 查看员工表

SELECT * FROM employee;

– 查看部门表

SELECT * FROM dept;

– 查看日志表

SELECT * FROM empLog;

---

数据库权限问题

1.mysql权限问题：
root用户： 超级管理员。
权限： 增删改查（数据库，表，数据）。

2.给mysql数据库分配不同权限的不同用户。

3.mysql数据库：存放mysql配置信息，包括用户信息。

USE mysql;

用户表：

SELECT * FROM USER;

4.加密函数(md5算法–单向加密)：

SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

5.修改用户密码：

UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

6.分配权限给不同的用户：
需求： 分配查询day15数据库的employee表的权限给eric用户，密码为‘123456’

GRANT SELECT ON day15.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';GRANT DELETE ON day15.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';SHOW TABLES;