一, 数据约束(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.当我们修改副表中的数据的时候,先修改主表
-- 需求:将华仔的部门改为4
UPDATE 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;
- 级联技术
当我们修改或者删除主表的时候,附表会同时收到影响,更加方便我们的操作
-- 需要将部门表中的秘书部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该4
UPDATE dept SET id=4 WHERE id=3;
-- 删除id为4的秘书部,ON DELETE CASCADE
DELETE 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,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;
注意:每个触发器的功能是不一样的,我们必须用不同的触发器来记录不同的操作!
四,数据库的权限问题
1. root用户:是我们的超级管理员,拥有所有的权限管理,它可以对我们的(数据库,表,数据)进行增删改查。
查询mysql数据库的用户信息
SELECT * FROM USER;
- 当我们查看我们的用户密码的时候,发现它并不是我们原来的密码,这个是因为数据库对我们的密码使用了MD5单向加密算法 。
加密函数是 :password();
SELECT PASSWORD('root');
- 如何修改我们的密码信息
-- 更改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';