MySQL编程
用户变量
作用域: 针对当前链接有效
不用声明,直接使用
赋值
@num为变量名, value为值
SET @num = value
SET @num := value
select
SELECT 字段INTO @变量名FROM 表名WHERE
使用
SELECT @num
赋值并使用
SELECT 字段INTO @变量名FROM 表名WHERE
SELECT @num1:=value1, @num2:=value2,…
局部变量
作用域: 在其声明的begin…end块内
声明: declare 变量名 变量类型[default…]
赋值
num为变量名,value为值
SET num=value
SET num:=value
SELECT INTO
SELECT num
赋值并使用
SELECT 字段INTO 变量名FROM 表名WHERE
SELECT num1:=value1, num2:=value2,
全局变量
用户不能定义全局变量,只能使用
- @@version :返回服务器版本账号
- @@basedir:返回MySQL安装基准目录
- @@license:返回服务器的许可类型
- @@port:返回服务器侦听TCP/IP连接所用端口
存储过程和存储函数
一系列SQL语句的集合, 封装到一起保存到数据库中
调用
- 存储过程: CALL 名称和参数
- 存储函数: SELECT 名称和参数
区别:
- 存储过程:没有返回值,参数类型可以是IN、OUT、INOUT
- 存储函数:必须有返回值,参数类型只能是IN
优点
- 具有良好的封装性
- 应用程序与SQL逻辑分离
- 让SQL具备处理能力
- 减少网络交互
- 能够提高系统性能
- 降低数据出错的概率
- 保证数据的安全性
创建存储过程
CREATE PROCEDURE sp_name ([proc_parameter[,…]])[characteristic …] routine_body
proc_parameter: 参数列表[ IN| OUT| INOUT] param_name type
routine_body: SQL执行体, 使用BEGIN…END封装
CREATE PROCEDURE p_selq()
BEGIN
SELECT * FROM s;
END
-- 输入参数是学号, 显示该同学的平均成绩
DROP PROCEDURE IF EXISTS ps;
CREATE PROCEDURE ps(IN sid CHAR(12), OUT avgs DECIMAL)
BEGIN
SELECT AVG(score)
INTO avgs
FROM sc
WHERE sc.snum = sid
END;
--
CALL ps('202205010229', @s);
SELECT @s;
创建存储函数
参数只能是输入的
CREATE FUNCTION func_name ([func_parameter[,…]]) RETURNS type[characteristic …] routine_body
func_parameter: 参数列表, 只能是IN类型
RETURNS type: 创建函数时指定的返回数据类型
routine_body: 函数的SQL执行体
-- 信任存储程序的创建者
set global log_bin_trust_function_creators = 1
-- 输入参数是学号, 显示该同学的平均成绩
DROP FUNCTION IF EXISTS ps;
CREATE FUNCTION fs(IN sid CHAR(12))
RETURNS DECIMAL(4, 1)
BEGIN
RETURN (SELECT AVG(score) FROM sc WHERE sc.snum = sid);
END;
SELECT fs('202205010229');
控制流程
IF
注意ELSEIF 应该连在一起
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
CASE
模板
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
CASE
[ELSE statement_list]
END CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
例子
SELECT snum, sname, CASE ssex
WHEN '男' THEN '小帅'
WHEN '女' THEN '小美'
ELSE '小妖'
END
FROM s;
LOOP
语法
[begin_label:] LOOP
statement_list
END LOOP [end_label]
LEAVE
从被标注的label流程结果中退出
LEAVE label
ITERATE
跳过本次循环,而执行下次循环操作
只可出现在LOOP、REPEAT和WHILE语句内
其中,label表示被标注的流程标志。
ITERATE label
例子
CREATE PROCEDURE p_c()
BEGIN
DECLARE x INT DEFAULT 0;
x_loop:LOOP
SET x=x+1;
IF x<5 THEN
ITERATE x_loop;
ELSEIF x>=10 THEN
LEAVE x_loop;
END IF;
SELECT x;
END LOOP x_loop; -- 这里的x_loop可以省略
END
REPEAT
语法
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
begin_label和end_label为循环的标志,可省略
当search_condition条件为true时,退出循环
例子
REPEAT
CREATE PROCEDURE p_c()
BEGIN
DECLARE x INT DEFAULT 0;
REPEAT
SET x=x+1;
UNTIL x>=10
END REPEAT;
SELECT x;
END
WHILE
语法
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
begin_label和end_label为循环的标志,可省略
当search_condition条件为true时,继续执行循环体
例子
CREATE PROCEDURE p_c()
BEGIN
DECLARE x INT DEFAULT 0;
WHILE x<10 DO
SET x=x+1;
END WHILE;
SELECT x;
END
游标
只能用在存储过程和存储函数中, 不能单独使用
- 声明
DECLARE cursor_name CURSOR FOR select_statement;
- 打开
OPEN cursor_name; -- 打开后,查询结构就会被传送到游标工作区
- 提取数据
FETCH cursor_name INTO var_name [, var_name] ... -- 打开后,游标指针指向结果集的第一行之前 -- Fetch语句使游标指针指向下一行 -- 可以在循环中使用fetch语句,这样每一次循环都会从结果集中读取一行数据,然后进行相同的逻辑处理
- 关闭
CLOSE cursor_name
CREATEPROCEDUREp_demo(snCHAR(12))
BEGIN
DECLARE sid CHAR(12);
DECLARE cid CHAR(12);
DECLARE sco INT;
DECLARE rcount INT;
DECLARE i INT DEFAULT0;
DECLARE cur_sc CURSOR FOR SELECTsnum, cnum, score
FROM sc WHERE snum = sn;
SELECT COUNT(*) INTO rcount FROM sc WHERE snum=sn;
OPEN cur_sc;
WHILE i < rcount DO
FETCH cur_sc INTO sid, cid, sco;
SELECT sid, cid, sco;
SET i = i + 1;
END WHILE;
CLOSEcur_sc;
END;
CALLp_demo('s030101');
CREATE PROCEDURE p_demo(sn CHAR(12))
BEGIN
DECLARE sid CHAR(12);
DECLARE cid CHAR(12);
DECLARE sco INT;
DECLARE flagTINYINTDEFAULTFALSE;
DECLARE cur_sc CURSOR FOR SELECT snum, cnum, score FROM sc WHERE snum = sn;
DECLARE CONTINUE HANDLER FOR NOTFOUND SET flag = TRUE;
OPEN cur_sc;
-- x:LOOP
-- FETCH cur_sc INTO sid, cid, sco;
-- IF flag THEN
-- LEAVE x;
-- ENDIF;
-- SELECT sid,cid,sco;
-- END LOOP;
FETCH cur_sc INTO sid, cid, sco;
WHILE flag DO
SELECT sid, cid, sco;
FETCH cur_sc INTO sid, cid, sco;
END WHILE;
CLOSE cur_sc;
END
异常处理
要么继续, 要么停止
DECLARE 错误处理类型 HANDLER FOR 错误条件 错误处理程序
例子
CREATE FUNCTION f_insert(snum char(12),cnum char(12),score int)
RETURNS varchar(20)
BEGIN
DECLARE EXIT HANDLER FOR 1062
RETURN '违反主键约束!’;
END
INSERT INTO sc VALUES(snum, cnum, score);
RETURN'插入成功!';
SELECT INSERT INTO
关系数据依赖
定义
主属性
一个属性只要在任何一个候选码中出现过,这个属性就是主属性。
非主属性
与上面相反,没有在任何候选码中出现过,这个属性就是非主属性。
实体
现实世界中客观存在并可以被区别的事物。比如“一个学生”、“一本书”、“一门课”等等。值得强调的是这里所说的“事物”不仅仅是看得见摸得着的“东西”,它也可以是虚拟的,比如说“老师与学校的关系”。
属性
教科书上解释为:“实体所具有的某一特性”,由此可见,属性一开始是个逻辑概念,比如说,“性别”是“人”的一个属性。在关系数据库中,属性又是个物理概念,属性可以看作是“表的一列”。
元组
表中的一行就是一个元组
分量
元组的某个属性值。在一个关系数据库中,它是一个操作原子,即关系数据库在做任何操作的时候,属性是“不可分的”。否则就不是关系数据库了
码
表中可以唯一确定一个元组的某个属性(或者属性组), 如果这样的码有不止一个, 那么大家都叫候选码, 我们从候选码中挑一个出来做老大, 它就叫主码
全码
如果一个码包含了所有的属性,这个码就是全码
主属性
一个属性只要在任何一个候选码中出现过,这个属性就是主属性
非主属性
与上面相反,没有在任何候选码中出现过,这个属性就是非主属性
外码
一个属性(或属性组),它不是码,但是它别的表的码,它就是外码
函数依赖
完全函数依赖
定义:设X, Y是关系R的两个属性集合,X’是X的真子集,存在X→Y,但对每一个X’都有X’!→Y,则称Y完全函数依赖于X
比如通过学号 -> 姓名
两个男人A和B;一个女人L
男人A和B一起能满足女人L的需求,
而男人A或B任意一个人都不能满足女人L的需求,要两个人一起才能满足,
叫做女人L完全依赖于两个男人A和B
男人A和B共同一起成为主属性,女人L当成非主属性
部分函数依赖
定义:设X,Y是关系R的两个属性集合,存在X→Y,若X’是X的真子集,存在X’→Y,则称Y部分函数依赖于X。
两个男人A和B;一个女人L
男人A和B一起能满足女人L的需求,
但是男人A或B其中一个人就能满足女人L的需求,叫做女人L部分依赖于两个男人A和B
传递函数依赖
设X,Y,Z是关系R中互不相同的属性集合,存在X→Y(Y !→X),Y→Z,则称Z传递函数依赖于X
在关系R(学号, 宿舍, 费用)中, (学号) -> (宿舍), 宿舍 != 学号,(宿舍) -> (费用), 费用 != 宿舍, 所以符合传递函数的要求
范式
第一范式
列不能再分
第二范式
在第一范式的基础上, 消除部分依赖(非主属性完全依赖于主属性)
第三范式
在第二范式的基础上, 消除传递依赖(每个非主属性都不传递函数依赖于主属性)
BCNF范式
3NF的改进
- 所有非主属性对每一个码都是完全函数依赖。
- 所有的主属性对每一个不包含它的码,也是完全函数依赖。
- 没有任何属性完全函数依赖于非码的任何一组属性。
如果在关系R中,U为主键,A属性是主键的一个属性,若存在A->Y,Y为主属性,则该关系不属于BCNF
触发器 (Trigger)
触发器是数据库服务器中发生事件时自动执行的一种特殊的存储过程, 为数据库提供了有效的监控和处理机制, 确保了数据的完整性
定义后, 任何用户对表操作均由服务器自动激活相应的触发器, 不能直接调用, 更不允许设置参数和返回值
- 触发事件
Insert, Upload, Delete - 触发时间
Before, After - 动作
SQL语句
每个表,每个事件,每个时间只能有一个触发器
MySQL 一个表最多有6个触发器
触发时间
Before 先动作, 后事件
After 先执行事件, 再动作
语法
- 地点 table
- 事件
- 时间
CREATE trigger triggerName
after/before insert/update/delete on 表明
for each row #这句话在Mysql固定
begin
sql语句;
end;
作用
- 安全性
- 审计: 能跟踪用户对数据库的操作
- 实现复杂的数据完整性规则
- 同步实时地复制表中的数据
- 主动计算数据值
NEW &OLD
- Insert触发器中, NEW 表示将要(before)或已经(After)插入的新数据
- DELETE触发器中: OLD 用来表示将要或已经被删除的原数据
使用方法: NEW.columnName(columnName为相应数据表某一列名)
另外,OLD 是只读的,而NEW 则可以在触发器中使用SET赋值,这样不会再次触发触发器,造成循环调用
例题
- 在选课表SC上创建触发器,当向该表中插入记录时,若学生选课门数超过5门时,将插入记录自动删除(限选5门)
create trigger tri_sc after insert on sc for each row begin IF(select count(*) from sc where sc.snum = new.snum) > 5 then signal sqlstate '45000' # signal语法抛出异常 #状态45000是表示"未处理的用户定义的异常"的通用状态 SET MESSAGE_TEXT= '你选的课程已经超过5门,不能再选了!'; end if; end;
- 在选课表SC上创建触发器,当向该表中插入记录时,学生成绩在[0,100]
CREATE TRIGGER tri BEFORE INSERT ON sc FOR EACH ROW BEGIN IF(new.score > 100) THEN SIGNAL SQLSTATE '45000' SET message_text = '输入的成绩>100', new.scroe = 100 ELSE IF (new.score < 0) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '输入的成绩<0' END IF; END;
- 创建一触发器,在删除学生记录的同时删除他所有的选课记录(级联删除)
create trigger tri before delete on s for each row begin delete from sc where sc.snum = old.snum; end;
- 创建一触发器,在更新教师的职称时,教授不能降级
CREATE TRIGGER `triupt` BEFORE UPDATE ON `t` FOR EACH ROW BEGIN IF old.ttitle='教授' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='教授不能降级!'; END IF; END