mysql

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,

全局变量

用户不能定义全局变量，只能使用

1. @@version :返回服务器版本账号
2. @@basedir:返回MySQL安装基准目录
3. @@license:返回服务器的许可类型
4. @@port:返回服务器侦听TCP/IP连接所用端口

存储过程和存储函数

一系列SQL语句的集合, 封装到一起保存到数据库中

调用

1. 存储过程: CALL 名称和参数
2. 存储函数: SELECT 名称和参数

区别：

1. 存储过程：没有返回值，参数类型可以是IN、OUT、INOUT
2. 存储函数：必须有返回值,参数类型只能是IN

优点

1. 具有良好的封装性
2. 应用程序与SQL逻辑分离
3. 让SQL具备处理能力
4. 减少网络交互
5. 能够提高系统性能
6. 降低数据出错的概率
7. 保证数据的安全性

创建存储过程

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

游标

只能用在存储过程和存储函数中, 不能单独使用

1. 声明

   DECLARE cursor_name CURSOR FOR select_statement;
   

2. 打开

    OPEN cursor_name;
    -- 打开后，查询结构就会被传送到游标工作区
   

3. 提取数据

   FETCH cursor_name INTO var_name [, var_name] ...
   -- 打开后，游标指针指向结果集的第一行之前
   -- Fetch语句使游标指针指向下一行 
   -- 可以在循环中使用fetch语句，这样每一次循环都会从结果集中读取一行数据，然后进行相同的逻辑处理
   

4. 关闭

   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的改进

1. 所有非主属性对每一个码都是完全函数依赖。
2. 所有的主属性对每一个不包含它的码,也是完全函数依赖。
3. 没有任何属性完全函数依赖于非码的任何一组属性。

如果在关系R中，U为主键，A属性是主键的一个属性，若存在A->Y，Y为主属性，则该关系不属于BCNF

触发器 (Trigger)

触发器是数据库服务器中发生事件时自动执行的一种特殊的存储过程, 为数据库提供了有效的监控和处理机制, 确保了数据的完整性

定义后, 任何用户对表操作均由服务器自动激活相应的触发器, 不能直接调用, 更不允许设置参数和返回值

1. 触发事件
   Insert, Upload, Delete
2. 触发时间
   Before, After
3. 动作
   SQL语句

每个表,每个事件,每个时间只能有一个触发器
MySQL 一个表最多有6个触发器

触发时间

Before 先动作, 后事件

After 先执行事件, 再动作

语法

1. 地点 table
2. 事件
3. 时间

CREATE trigger triggerName
after/before insert/update/delete on 表明
for each row #这句话在Mysql固定
begin
    sql语句;
end;

作用

1. 安全性
2. 审计: 能跟踪用户对数据库的操作
3. 实现复杂的数据完整性规则
4. 同步实时地复制表中的数据
5. 主动计算数据值

NEW &OLD

1. Insert触发器中, NEW 表示将要(before)或已经(After)插入的新数据
2. DELETE触发器中: OLD 用来表示将要或已经被删除的原数据

使用方法: NEW.columnName（columnName为相应数据表某一列名）
另外，OLD 是只读的，而NEW 则可以在触发器中使用SET赋值，这样不会再次触发触发器，造成循环调用

例题

1. 在选课表SC上创建触发器，当向该表中插入记录时，若学生选课门数超过５门时，将插入记录自动删除(限选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;
   

2. 在选课表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;
   

3. 创建一触发器,在删除学生记录的同时删除他所有的选课记录(级联删除)

   create  trigger  tri
   before delete 
   on s
   for each row
   begin
       delete from sc where sc.snum = old.snum;
   end;
   

4. 创建一触发器,在更新教师的职称时，教授不能降级

   CREATE TRIGGER `triupt` BEFORE UPDATE ON `t`
   FOR EACH ROW
   BEGIN
   IF old.ttitle='教授' THEN
   SIGNAL SQLSTATE '45000'
   SET MESSAGE_TEXT='教授不能降级！';
   END IF;
   END