MySQL数据库技术
十六、流程控制结构
1.分类:
顺序结构:
程序从上到下依次执行的过程
分支结构:
程序从两条或者多条路径中选择一条去执行的过程
循环结构:
程序在满足一定条件的基础上,重复执行某一段代码
2.分支结构:
① if(表达式1,表达式2,表达式3) 函数
**功能:**实现简单的双分支选择结构
**执行顺序:**如果表达式1成立,则if函数返回表达式2的值,否则返回表达式3的值
select if(3>10,'true','false');
② case结构
**情况一:**类似于java中的switch语句,一般用于实现的等值判断,其语法如下:
case 变量| 表达式 | 字段
when 要判断的值 then 返回的值 1 或语句1;
when 要判断的值 then 返回的值 2 或语句2;
when 要判断的值 then 返回的值 3 或语句3;
……
else 要返回的值 n 或语句n;
end case;
**情况二:**类似于java中的多重if语句,一般用于实现区别的判断,语法如下:
case
when 要判断的条件1 then 返回的值 1 或语句1;
when 要判断的条件2 then 返回的值 2 或语句2;
when 要判断的条件3 then 返回的值 3 或语句3;
……
else 要返回的值 n 或语句n;
end case;
案例:
-- 创建存储过程,根据传入的成绩,来显示等级
-- 90-100优秀,80-89良好,70-79不错,60-69及格,否则不及格
DELIMITER $
CREATE PROCEDURE myp1(IN score INT)
BEGIN
CASE
WHEN score>=90 AND score <=100 THEN SELECT'优秀';
WHEN score>=80 AND score <90 THEN SELECT'良好';
WHEN score>=70 AND score <80 THEN SELECT'不错';
WHEN score>=60 AND score <70 THEN SELECT'及格';
ELSE SELECT'不及格';
END CASE;
END;$
-- 调用
CALL myp1(84);
③ if结构
-- 语法
if 条件1 then 语句1;
elseif 条件2 then 语句2;
elseif 条件2 then 语句2;
……
else 语句n;
end if;
-- 场景应用在begin end中
-- 案例:创建函数,根据传入的成绩,来显示等级
-- 90-100优秀,80-89良好,70-79不错,60-69及格,否则不及格
DELIMITER $
CREATE FUNCTION myfun1(score INT) RETURNS VARCHAR(20)
BEGIN
IF score>=90 AND score <=100 THEN RETURN'优秀';
ELSEIF score>=80 THEN RETURN'良好';
ELSEIF score>=70 THEN RETURN'不错';
ELSEIF score>=60 THEN RETURN'及格';
ELSE RETURN'不及格';
END IF;
END;$
-- 调用函数
SELECT myfun1(76);
3.循环结构
循环结构:while\loop\repeat
循环控制:
iterate 类似于java中的continue,继续,结束本次循环,继续下一次循环
leave 类似于java中的break,跳出,结束当前的循环
while循环
先判断,后执行
-- 语法
标签:while 循环条件 do
循环体
end while 标签;
-- 案例:批量插入数据,要根据插入的次数向某个表添加10条记录
-- 先创建一张空表
create table magic(id int,mname varchar(50));
-- 创建一个存储过程
DELIMITER $
CREATE PROCEDURE mypro1(IN num INT)
BEGIN
DECLARE i INT DEFAULT 1;-- 定义的局部变量
WHILE i<=num DO
INSERT INTO magic VALUES(i,CONCAT('magic_',i)); -- 拼接
SET i = i+1;
END WHILE;
END;$
-- 查询表中数据
CALL mypro1(10);
SELECT * FROM magic;
-- 案例2:批量插入数据,要根据插入的次数向某个表添加多条记录,如果次数等于10次就停止,leave的使用
DELIMITER $
CREATE PROCEDURE mypro2(IN num INT)
BEGIN
DECLARE i INT DEFAULT 11;
a:WHILE i<=num DO
INSERT INTO magic VALUES(i,CONCAT('m_',i));
IF i>=20 THEN LEAVE a;-- leave的使用
END IF;
SET i=i+1;
END WHILE a;
END;$
-- 调用查询
CALL mypro2(100);
SELECT * FROM magic;
由图可知:传入的参数虽然为100,但是只插入了10条数据
-- 案例:批量插入,向magic表中增加多条数据,只插入偶数列,iterate的使用
-- 这里我先删除magic表中数据
delete from magic;
-- 创建存储过
DELIMITER $
CREATE PROCEDURE mypro3(IN num INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=num DO
SET i = i+1;
IF MOD(i,2) != 0 THEN ITERATE a;
END IF;
INSERT INTO magic VALUES(i,CONCAT('哈哈_',i));
END WHILE a;
END;$
-- 测试
CALL mypro3(10);
SELECT * FROM magic;
loop循环
-- 语法:
标签 loop
循环体
end loop 标签;
-- 就是一个没条件的死循环
案例:
-- 案例:累加求和1-100
delimiter $
create procedure mypro4(in num int)
begin
declare sum int default 0;-- 和
declare i int default 1;
loop_name:loop
-- 判断
if i> num then
leave loop_name; -- 判断条件成立则结束当前循环
end if;
set sum=sum+i;-- 累加
set i=i+1;
end loop loop_name;
select sum;-- 输出
end;$
-- 调用
CALL mypro4(100);
repeat循环
类似于java里面的 do-while 先执行再判断
-- 语法
标签 repeat
循环体
until 结束 循环条件
end repeat;
-- 案例:累加求和1-100
delimiter $
create procedure mypro5(in num int)
begin
declare sum int default 0;-- 和
declare i int default 1;
repeat
set sum=sum+i;
set i=i+1;
until i> num end repeat;-- 判断条件成立则结束当前循环
select sum;
end;$
-- 测试
CALL mypro5(100);
十七、MySQL触发器
1.原理概念
mysql的触发器类似于存储过程,可以说是一个特殊的存储过程,不同的是,在执行存储过程中要使用call语句调用,而触发器不需要使用call语句来调用,也不需要手工启动,是一个预定义的事件,触发这个事件才会被mysql数据库自动调用
2.分类
INSERT
UPDATE
DELETE
3.语法
-- 创建触发器
create trigger 触发器名称
before | after
insert | update | delate
on 表名
for each row
触发器主体;
trigger:关键字
before | after :触发器被触发的时间,在激活它的语句之前或之后触发
insert | update | delate
insert:将新行插入表时激活触发器
update:更改表中数据时激活触发器
delete:从表中删除数据时激活触发器
表名:与触发器相关联的表名
for each row:行级触发器,影响每一行
触发器主体:当激活时将要执行的动作,mysql语句;
4.具体操作
案例1:
-- 创建before 类型的触发器
-- 创建一个员工表
create table emp(
eid int primary key,
ename varchar(30) not null,
salary float
)
-- 创建一个tri1 的触发器,触发的条件是向数据表emp中插入数据之前,对新插入的salary字段值 进行求和
create trigger mytri1
before insert on emp
for each row
set @sum=@sum+new.salary
-- 使用
-- 先声明变量
set @sum =0;
--插入数据
insert into emp values(1,'admin',5000),(2,'magic',4800);
-- 查询变量
SELECT @sum;
案例2:
-- 创建after 类型的触发器
-- 创建一个员工表
create table emp2(
eid int primary key,
ename varchar(30) not null,
salary float
)
-- 创建一个员工表
create table emp3(
eid int primary key,
ename varchar(30) not null,
salary float
)
-- 创建一个触发器,触发条件:向数据表emp2中插入数据之后,在向数据表emp3中插入相同的数据
create trigger mytri2
after insert on emp2
for each row
insert into emp3 values(new.eid,new.ename,new.salary)
-- new:新增行的某列数据,新值
-- old:删除或修改行时的某列数据,旧值
-- 插入数据
INSERT INTO emp2 VALUES(1,'tom',5000),(2,'rose',4500);
-- 测试
SELECT * FROM emp2;
SELECT * FROM emp3;
案例3:
-- old的使用
-- 创建一个触发器,触发条件:向数据表emp2中修改数据之后,在向数据表emp3中插入之前的数据
create trigger mytri3
after update on emp2
for each row
insert into emp3 values(old.eid,old.ename,old.salary)
-- 修改数据
update emp2 set ename='jack',salary=500 where eid=3;
--测试
SELECT * FROM emp2;
SELECT * FROM emp3;
emp2:
emp3:
这里我说明一下:我在做测试old的时候,把触发器2删除了,并且提前添加了bob,防止触发器2的作用干扰
十八、数据库的三大范式
1.理论
从理论上来讲数据库的设计范式其核心的本质在于可以设计出方便扩展,并且存储精确的数据结构,并且有效满足我们程序开发中的需求
所有给出的设计范式只能够作为一种参考出现,在初期结构之中可以根据范式进行设计,但是在最终交付的时候会发现所有的范式都几乎会被打破,根据业务的需要尽可能的减少多表查询或者复杂查询
所有说如果从另一个层次来讲,数据库设计没有具体模式,所有说,核心只有一个:保证数据的有效存储,保证查询的性能就好
2.第一设计范式(操作单表)
设计要求:“数据表中的每一个字段都是不可再分的”
-- 例如:现在有如下一张用户的数据表
create table 用户(
用户编号 int,
用户姓名 varchar(30),
联系方式 varchar(200),
constraink pk_用户编号 primary key(用户编号)
)
联系方式可以进一步拆分,可以由地址、邮箱、微信、qq、email、手机等,所有我们发现某一个字段依然可以拆分,那么这样的设计就不符合第一设计范式,因为有的字段还可以拆分;
-- 例如:现在有如下一张用户的数据表
create table 用户(
用户编号 int,
用户姓名 varchar(30),
email varchar(200),
联系电话 varchar(200),
地址 varchar(100),
constraink pk_用户编号 primary key(用户编号)
)
这个时候表的字段就不可再分了,所以符合第一设计范式;
但是需要提醒的是,第一范式严格来讲是属于使用标准的数据类型定义的表,但有些数据类型不能拆分,比如:
create table 用户(
用户编号 int,
用户姓名 varchar(30),
email varchar(200),
联系电话 varchar(200),
地址 varchar(100),
生日_年 varchar(4),
生日_月 varchar(2),
生日_年 varchar(2),
constraink pk_用户编号 primary key(用户编号)
)
年月日我们可以使用一个数据类型Date来表示,所以像这样的数据类型就不再进行拆分了
3.第二范式
设计原则:“数据表中不存在非关键字段对任意一候选字段的部分函数依赖”
要理解这句话,我们从以下入手!!!
①函数关系:所谓的函数关系,我们可以简单点理解就是数据表之中的两个列之间不要存在数学关系
-- 例如:现在有一张订单表
create table 订单(
订单编号 int,
订单日期 date,
商品单价 int,
商品数量 int,
商品总价 int,
)
这个时候,我们发现设计的表中存在以下关系:商品总结=商品单价*商品数量
②函数依赖:指的是通过某几个字段可以成功的推演出唯一的一个字段,比如现在有
帅哥、阳光、技术好、有上进心、好学习——>可以推演出一个结果,这不就是我吗🤭。但是我们只有一个字段,能不能找到唯一的结果呢?
-- 例如:设计一个学生选课数据表,一个学生可以选择参加多门课程,一门课程可以有多个学生参加,并且每个学生参加完了每个课程都有自己的一个成绩:
-- 现在要求设计如下操作:如果这个时候我们按照第一范式来设计,设计如下:
create table 学生选课(
学生编号 int,
学生姓名 varchar(30),
学生年龄 int,
课程名称 varchar(50),
课程学分 int,
考试成绩 float,
)
这个设计符合第一范式,但是如果真这样使用会有哪些问题呢?我们添加一些数据来观察
insert into 学生选课 values(1,'张三',18,'mysql',2,90);
insert into 学生选课 values(2,'李四',19,'java',1,89);
insert into 学生选课 values(3,'王五',17,'oracle',3,86);
insert into 学生选课 values(1,'张三',18,'java',1,94);
如果按照第一范式的要求去设计,当前的代码会存在以下问题:
- 无法设置一个合适的字段作为主键,因为一个学生可以选择多门课程;
- 数据重复,课程信息重复,因为一个课程的名称是不会被改变的,学分信息也是一样的
- 数据更新麻烦,当某一个课程的名称要做更新,更新之后那么则更新n行记录
- 如果说现在有一门课程无人选择,那么这个课程信息就彻底消息了
很明显现在的关系是一个多对多的处理关系,因为一个学生可以参加多门课程,一门课程可以有多个学生选择,这属于一种明显的多对多关系,所以说上面的设计太糟糕
改进:
-- 学生表
create table 学生(
学生编号 int primary key,
学生姓名 varchar(30),
学生年龄 int
)
-- 课程表
create table 课程(
课程编号 int primary key,
课程名称 varchar(50),
课程学分 int
)
-- 学生选课表
create table 学生选课(
学生编号 int references 学生(学生编号),
课程编号 int references 课程(课程编号),
考试成绩 float
)
-- 增加数据
insert into 学生 values(1,'张三',18);
insert into 学生 values(2,'赵四',20);
insert into 学生 values(3,'王五',19);
insert into 课程 values(001,'mysql',2);
insert into 课程 values(002,'java',1);
insert into 课程 values(003,'oracle',3);
insert into 学生选课 values(1,001,90);
insert into 学生选课 values(2,002,89);
insert into 学生选课 values(3,003,86);
insert into 学生选课 values(1,002,94);
这样的设计解决了之前的那些问题,如果要修改某一门课程的名称那么就去修改修改表即可,修改方便,查询也很方便,不会出现数据冗余,所有的数据都可以进行各自的实体表的维护
4.第三范式
设计原则:“数据表之中不存在非关键字段对任意一候选关键字段的传递函数依赖”
实际上传递函数依赖就好比层级关系一样
员工编号 | 姓名 | 职位 | 部门名称 | 位置 | 工资 |
---|---|---|---|---|---|
0001 | 张三 | 经理 | 技术部 | 西安 | 30000.00 |
这个设计符合第一设计范式,不可再分,但是以上这个范式存在有一个函数传递关系;
通过员工编号可以知道他的部门名称,但是通过部门名称又可以找到所在的部门位置,那么这三个字段就存在传递关系,所以说这样的设计就不符合第三范式
-- 案例:要求设计一个学生和学校的关系数据表,很明显一个学校有多个学生
-- 分析:第一范式:如果学校人数很多,创建表时,学校名称、学校地址要重复很多次,会出现冗余,若学校更名则修改的数量相当大
-- 第二范式:一个学校可以有多个学生,一个学生可以在多个学生,显然也不符合!
-- 采用第三范式:
-- 一个学校有多个学生,多个学生属于一个学校,员工表里面有一个外键约束
这个过程也就是属于一对多的关系!!!
5.总结
第一范式就是单表设计原则
第二范式就是多对多的关系
第三范式就是一对多的关系
实际开发中,我们一般都采取第三范式