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

最新推荐文章于 2021-12-31 11:50:11 发布

yangruxi

最新推荐文章于 2021-12-31 11:50:11 发布

阅读量1.1k

点赞数 1

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/yangruxi/article/details/72891411

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

数据约束

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()
BEGIN
SELECT * FROM employee;
END$

CALL pro_test();

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

DELIMITER $
CREATE PROCEDURE pro_test2(IN eId INT)
BEGIN
SELECT * FROM employee WHERE id=eId;
END$

CALL pro_test2(3);

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

DELIMITER $
CREATE PROCEDURE pro_outTest(OUT str VARCHAR(0))
BEGIN
SET 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))
BEGIN
SELECT 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 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 NAME='李四';

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

CREATE TRIGGER tri_empDelete AFTER DELETE ON employee FOR EACH ROW
INSERT 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;