mysql-进阶
6 数据约束
6.1什么是数据约束
用户在操作表的数据时能有效进行约束。
6.2 默认值
作用：当用户对使用默认值的字段不插入值的时候，就使用默认值。
-- 1.1 默认值
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
address VARCHAR(20) DEFAULT '广州天河'
)
-- 当设置默认值的字段没有插入值的时候，mysql自动给该字段分配默认值
INSERT INTO student(id,NAME) VALUES(1,'张三');
-- 注意：设置默认值的字段允许为null
INSERT INTO student(id,NAME,address) VALUE(2,'李四',NULL);
-- 注意：设置默认值的字段允许为非null
INSERT INTO student(id,NAME,address) VALUE(3,'王五','广州番禺');
6.3 非空
作用：限制字段必须赋值
-- 1.2 非空
-- 需求：gender字段必须有值（不为null）
DROP TABLE student;
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
gender VARCHAR(2) NOT NULL
)
-- 非空字段必须赋值
INSERT INTO student(id,NAME) VALUES(1,'李四');
-- 非空字符不能插入null
INSERT INTO student(id,NAME,gender) VALUES(1,'李四',NULL);
6.4 唯一
作用： 限制字段的值不能重复
注意：
1、唯一字段可以插入null
2、唯一字段可以插入多个null
3、只有插入非null的值才会起作用
-- 1.3 唯一
DROP TABLE student;
CREATE TABLE student(
id INT UNIQUE, -- 唯一
NAME VARCHAR(20)
)
INSERT INTO student(id,NAME) VALUES(1,'zs');
INSERT INTO student(id,NAME) VALUES(1,'lisi');
INSERT INTO student(id,NAME) VALUES(2,'lisi');
6.5 主键
作用： 限制字段非空并且唯一
注意：
1）通常情况下，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段。
-- 1.4 主键（非空 + 唯一）
DROP TABLE student;
CREATE TABLE student(
id INT PRIMARY KEY, -- 主键
NAME VARCHAR(20)
)
INSERT INTO student(id,NAME) VALUES(1,'张三');
INSERT INTO student(id,NAME) VALUES(2,'张三');
-- INSERT INTO student(id,NAME) VALUES(1,'李四');
-- INSERT INTO student(name) value('李四');
6.6 自增长
作用： 限制字段为自动递增
-- 1.5 自增长
DROP TABLE student;
CREATE TABLE student(
id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增长，从0开始 ZEROFILL 零填充
NAME VARCHAR(20)
)
-- 自增长字段可以不赋值，自动递增
INSERT INTO student(NAME) VALUES('张三');
INSERT INTO student(NAME) VALUES('李四');
SELECT * FROM student;
-- 不会影响自增长约束
DELETE FROM student;
-- 可以影响自增长约束
TRUNCATE TABLE student;
6.7 外键
作用：约束两张表的数据
-- 1.6 外键约束
-- 员工表
CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptName VARCHAR(20)
)
INSERT INTO employee VALUES(1,'张三','软件开发部');
INSERT INTO employee VALUES(2,'李四','软件开发部');
INSERT INTO employee VALUES(3,'王五','应用维护部');
SELECT * FROM employee;
-- 添加员工，部门名称的数据冗余高
INSERT INTO employee VALUES(4,'陈六','软件开发部');
问题1：数据冗余高
解决方案：将员工表的‘部门’字段独立出一张表：员工表 和 部门表
-- 独立设计一张部门表
CREATE TABLE dept(
id INT PRIMARY KEY,
deptName VARCHAR(20)
)
DROP TABLE employee;
-- 修改员工表
CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptId INT -- 把部门名称改为部门ID
)
INSERT INTO dept(id,deptName) VALUES(1,'软件开发部');
INSERT INTO dept(id,deptName) VALUES(2,'应用维护部');
INSERT INTO dept(id,deptName) VALUES(3,'秘书部');
INSERT INTO dept(id,deptName) VALUES(4,'总经办');
INSERT INTO employee VALUES(1,'张三',1);
INSERT INTO employee VALUES(2,'赵四',2);
INSERT INTO employee VALUES(3,'王五',2);
INSERT INTO employee VALUES(4,'陈六',3);
)
问题2：在插入员工表数据的时候，员工表的部门ID字段可以随便插入
解决方案：使用外键约束员工表的部门ID字段值，在员工表的部门ID字段添加一个外键约束
-- 部门表（主表）
CREATE TABLE dept(
id INT PRIMARY KEY,
deptName VARCHAR(20)
)
DROP TABLE employee;
-- 修改员工表（副表/从表）
CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptId INT,
CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
)
注意：
1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上
2）参考字段通用设置成主表的主键
3）添加数据： 先添加主表，再添加副表
4）修改数据： 先修改副表，再修改主表
5）删除数据： 先删除副表，再删除主表
6.8 级联操作
问题1：当有了外键约束的时候，须先修改或删除副表中的所有关联数据，才能修改或删除主表。
但是，我们希望直接修改或删除主表数据，从而影响副表数据。
解决方案：使用级联操作实现
级联修改： ON UPDATE CASCADE
级联删除： ON DELETE CASCADE
CREATE TABLE employee(
id INT PRIMARY KEY,
empName VARCHAR(20),
deptId INT,
CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
)
注意：级联操作必须在外键基础上使用
-- 级联修改（修改）
-- 直接修改部门
UPDATE dept SET id=5 WHERE id=4;
-- 级联删除
-- 直接删除部门
DELETE FROM dept WHERE id=1;
7 数据库设计
7.1 三大范式
设计原则：建议设计的表尽量遵守三大范式（规范）
第一范式:确保每列的原子性
数据库表中的字段都是单一属性的，不可再分。
第二范式：确保表中的每列都和主键相关
基于第一范式，并且除了主键以外的其它列,都依赖于该主键。
第三范式：确保每列都和主键列直接相关,而不是间接相关
基于第二范式，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。
8 关联查询(多表查询)
8.1 交叉连接查询
-- 需求：查询员工及其所在部门 (显示员工姓名，部门名称)
-- 2.1 交叉连接查询（不推荐：产生笛卡尔乘积现象：4 * 3=12，有些是重复记录）
SELECT empName,deptName FROM employee,dept;
-- 需求：查询员工及其所在部门 (显示员工姓名，部门名称)
8.2 内连接查询
-- 多表查询规则：
1）确定查询哪些表
2）确定查询哪些字段
3）表与表之间连接条件 (规律：连接条件的数量是表数量-1)
-- 2.2 内连接查询：只有满足条件的结果才会显示(使用最频繁)
SELECT empName,deptName -- 2）确定哪些哪些字段
FROM employee,dept -- 1）确定查询哪些表
WHERE employee.deptId=dept.id; -- 3）表与表之间连接条件
-- 内连接的另一种语法
SELECT empName,deptName
FROM employee
INNER JOIN dept
ON employee.deptId=dept.id;
-- 使用别名
SELECT e.empName,d.deptName
FROM employee as e
INNER JOIN dept as d
ON e.deptId=d.id;
8.3 外连接查询
-- 需求：查询每个部门的员工
-- 期望结果：
-- 软件开发部 张三
-- 软件开发部 李四
-- 应用维护部 王五
-- 总经办 null
-- 2.2 左[外]连接查询： 使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，
如果不符合连接条件则显示null （注意： 左外连接：左表的数据一定会完全显示）
SELECT d.deptName,e.empName
FROM dept d
LEFT OUTER JOIN employee e
ON d.id=e.deptId;
-- 2.3 右[外]连接查询: 使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，
如果不符合连接条件则显示null （注意： 右外连接：右表的数据一定会完全显示）
SELECT d.deptName,e.empName
FROM employee e
RIGHT OUTER JOIN dept d
ON d.id=e.deptId;
8.4 自连接查询
-- 2.4 自连接查询：自连接查询可以配合内连接和外连接查询来使用。
-- 需求：查询员工及其上司
ALTER TABLE employee ADD COLUMN bossId INT;
UPDATE employee SET bossId=1 WHERE id=2;
UPDATE employee SET bossId=2 WHERE id=3;
UPDATE employee SET bossId=3 WHERE id=4;
-- 预期结果：
-- 张三 null
-- 李四 张三
-- 王五 赵四
-- 陈六 王五
SELECT e.empName,b.empName
FROM employee e
LEFT OUTER JOIN employee b
ON e.bossId=b.id;
9存储过程
9.1 什么是存储过程
存储过程，带有流程控制语句的sql语句
9.2 存储过程特点
1）执行效率快：存储过程是在数据库的服务器端执行的
2）移植性差：不同数据库的存储过程是不能移植的
9.3 存储过程语法
-- 创建存储过程
DELIMITER $ -- 声明存储过程的结束符
CREATE PROCEDURE pro_test() -- 存储过程名称(参数列表)
BEGIN -- 开始
-- 可以写多个sql语句; -- sql语句 + 流程控制
SELECT * FROM employee;
END $ -- 结束 结束符
-- 执行存储过程
CALL pro_test(); -- CALL 存储过程名称(参数);
参数：
IN： 表示输入参数，可以携带数据在存储过程中
OUT： 表示输出参数，可以从存储过程中返回结果
INOUT 表示输入输出参数，既可以输入功能，也可以输出功能
-- 3.1 带有输入参数的存储过程
-- 需求：传入一个员工的id，查询员工信息
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT) -- IN: 输入参数
BEGIN
SELECT * FROM employee WHERE id=eid;
END $
-- 调用
CALL pro_findById(4);
-- 3.2 带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20)) -- OUT：输出参数
BEGIN
-- 给参数赋值
SET str='hellojava';
END $
-- 删除存储过程
DROP PROCEDURE pro_testOut;
-- 全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）
-- 查看所有全局变量： show variables
-- 查看某个全局变量： select @@变量名
-- 修改全局变量： set 变量名=新值
-- character_set_client: mysql服务器的接收数据的编码
-- character_set_results：mysql服务器输出数据的编码
-- 会话变量：只存在于当前客户端与数据库服务器端的一次连接当中。连接断开，会话变量全部丢失。
-- 定义会话变量: set @变量=值
-- 查看会话变量： select @变量
-- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失。
-- 调用 1)定义一个会话变量name 2)使用name会话变量接收存储过程的返回值
CALL pro_testOut(@NAME);
-- 查看变量值
SELECT @NAME;
-- 3.3 带有输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT) -- INOUT： 输入输出参数
BEGIN
-- 查看变量
SELECT n;
SET n =500;
END $
-- 调用
SET @n=10;
CALL pro_testInOut(@n);
SELECT @n;
-- 3.4 带有条件判断的存储过程
-- 需求：输入一个整数，如果1，则返回“星期一”,如果2，返回“星期二”,如果3，返回“星期三”。其他数字，返回“错误输入”;
DELIMITER $
CREATE PROCEDURE pro_testIf(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_testIf(4,@str);
-- 查看
SELECT @str;
-- 3.5 带有循环功能的存储过程
-- 需求： 输入一个整数，求和。例如，输入100，统计1-100的和
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
-- 定义一个局部变量
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO
SET vsum = vsum+i;
SET i=i+1;
END WHILE;
SET result=vsum;
END $
CALL pro_testWhile(100,@result);
SELECT @result;
-- 3.6 使用查询的结果赋值给变量（INTO）
USE day16;
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT,OUT vname VARCHAR(20) )
BEGIN
SELECT empName INTO vname FROM employee WHERE id=eid;
END $
CALL pro_findById2(1,@NAME);
SELECT @NAME;
10 触发器
10.1 触发器作用
当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成.
例如： 当向员工表插入一条记录时，希望同时往日志表插入数据
CREATE TABLE test_log(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(100)
)
-- 需求：当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据
-- 创建触发器(添加)
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('员工表插入了一条记录');
-- 插入数据
INSERT INTO employee(id,empName,deptId) VALUES(7,'扎古斯',1);
INSERT INTO employee(id,empName,deptId) VALUES(8,'扎古斯2',1);
-- 创建触发器(修改)
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('员工表修改了一条记录');
-- 修改
UPDATE employee SET empName='eric' WHERE id=7;
-- 创建触发器(删除)
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW
INSERT INTO test_log(content) VALUES('员工表删除了一条记录');
-- 删除
DELETE FROM employee WHERE id=7;
