mysql常见笔试题小结(2)-木庄网络博客

（1）存储过程：
　　指的是一组预先编译好的 sql 语句的集合，可以理解成批处理语句。类似于 Java 中的方法，使用时调用方法名即可。

（2）好处：
　　提高了代码的重用性。
　　简化操作。
　　减少了编译次数、与数据库交互的次数，提高了效率。

（3）语法：

【创建存储过程：】
    DELIMITER $
    CREATE PROCEDURE 存储过程名(参数列表)
    BEGIN
        存储过程体（一组合法的 sql 语句）
    END $
    DELIMITER ;
注：
    参数列表分三个部分，分别为 参数模式、参数名、参数类型
    参数模式：IN、OUT、INOUT。
        IN：指该参数可以作为输入，即接收值（默认）。
        OUT：指该参数可以作为输出，即返回值。
       INOUT：指该参数即可作为输入、又可作为输出。
   
    存储过程体中每条语句必须以分号 ; 结尾。
    DELIMITER 用于设置结束标记，用于存储过程末尾，执行到标记处则存储过程结束。
    
【调用存储过程：】
    CALL 存储过程名(参数列表);
    
【删除存储过程：】
    DROP PROCEDURE 存储过程名;

【查看存储过程结构：】
    SHOW CREATE PROCEDURE 存储过程名;

（4）举例：

# 创建一个 user 表，若已经存在该表，先删除
DROP TABLE IF EXISTS user;
CREATE TABLE user(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    password VARCHAR(20)
);

# 用于设置结束标记，此处 sql 结束标记有 ; 改为 $
DELIMITER $

# 创建存储过程(无参)，用于向 user 表中插入 5 个数据
DROP PROCEDURE IF EXISTS user_no_parameter $
CREATE PROCEDURE user_no_parameter()
BEGIN
    INSERT INTO USER(name, password) VALUES('tom', 'tom123'),('jarry', 'jarry123'),('jack', 'jack123'),('tim', 'tim123'),('rose', 'rose123');
END $

# 创建有参存储过程，根据输入的用户名，找到相应的密码，并返回该用户的 id。
DROP PROCEDURE IF EXISTS user_parameter $
CREATE PROCEDURE user_parameter(IN name VARCHAR(20), OUT password VARCHAR(20), INOUT id INT)
BEGIN
    SELECT user.password, user.id INTO password, id
    FROM user
    WHERE user.name = name;
END $

# 用于设置结束标记，此处 sql 结束标记有 $ 改为 ;
DELIMITER ;

# 调用无参存储过程
CALL user_no_parameter();

# 查看当前表数据
SELECT * FROM user;

# 调用有参存储过程
set @id = 0;
CALL user_parameter('jack', @password, @id);

# 查看有参存储过程执行后的结果
SELECT @password, @id;

14、函数

（1）函数：
　　与存储过程类似，也是一组预先编译好的 sql 语句的集合。
注：
　　与存储过程的区别：
　　　　存储过程可以没有返回值，可以有多个返回值，适合进行批处理（批量插入、删除等）。
　　　　函数有且仅有一个返回值，一般用于处理数据并返回一个结果。

（2）语法：

【创建函数：】
    DELIMITER $
    CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
    BEGIN
        函数体（一组合法的 sql 语句）
    END $
    DELIMITER ;
注：
    参数列表分两个部分，分别为 参数名、参数类型。
    函数体必须包含 return 语句。
    
【调用函数：】
    SELECT 函数名(参数列表);
    
【查看函数：】
    DROP FUNCTION 函数名;

【删除函数：】
    SHOW CREATE FUNCTION 函数名;

（3）举例：

# 用于设置结束标记，此处 sql 结束标记有 ; 改为 $
DELIMITER $

# 创建无参函数，若函数已存在，则先删除再创建
DROP FUNCTION IF EXISTS test1 $
CREATE FUNCTION test1() RETURNS INT
BEGIN
    DECLARE a INT DEFAULT 10;
    DECLARE b INT DEFAULT 10;
    return  a + b;
END $

# 创建有参函数，若函数已存在，则先删除再创建
DROP FUNCTION IF EXISTS test2 $
CREATE FUNCTION test2(a INT, b INT) RETURNS INT
BEGIN
    RETURN a - b;
END $

# 用于设置结束标记，此处 sql 结束标记有 $ 改为 ;
DELIMITER ;

# 调用无参函数
SELECT test1();

# 调用有参函数
SELECT test2(20, 10);

注：
　　若出现错误 ERROR 1418 (HY000)，修改 log_bin_trust_function_creators 值即可。

【错误：】
    ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

【解决：】
    SET GLOBAL log_bin_trust_function_creators = 1;

15、流程控制语句

（1）IF 语句

【语法1：(IF 函数)】
    IF(表达式1, 结果1, 结果2);
注：
    表达式 1 成立，则返回 结果1.
    表达式 1 不成立，则返回 结果2.
    
【语法2：需要写在 BEGIN - END 中】
    BEGIN
        IF 表达式 1 THEN 结果 1;
        ELSEIF 表达式 2 THEN 结果 2;
        ELSE 结果 3;
        END IF;
    END
    
【举例：】
    DELIMITER $
    DROP FUNCTION IF EXISTS test3 $
    CREATE FUNCTION test3() RETURNS INT
    BEGIN
        IF 2 > 3 THEN RETURN 3;
        ELSEIF 2 > 4 THEN RETURN 4;
        ELSE RETURN 5;
        END IF;    
    END $
    DELIMITER ;
    
    SELECT test3() value1, IF(2 > 3, 2, 3) value2;

（2）CASE 语句

【语法1：相当于 Java 中的 switch 语句】
    CASE 表达式 | 变量
    WHEN 值 1 THEN 返回的结果 1 
    WHEN 值 2 THEN 返回的结果 2
    ELSE 返回的结果 3
    END
    
【语法2：相当于 Java 中的 IF - ELSE 语句】
    CASE 
    WHEN 表达式 1 THEN 返回的结果 1
    WHEN 表达式 2 THEN 返回的结果 2
    ELSE 返回的结果 3
    END
    
【举例：】
SELECT (
    CASE 2 + 3
    WHEN 2 THEN 2
    WHEN 3 THEN 3
    ELSE 4
    END
) value1,
(
    CASE 
    WHEN 2 > 3 THEN 2
    WHEN 2 < 3 THEN 3
    ELSE 4
    END
) value2;

（3）循环

【分类：需要放在 BEGIN - END 里】
    while、loop、repeat

【循环标志：】
    iterate： 类似于 Java 中的 continue，结束本次循环，进行下一次循环。
    leave： 类似于 Java 中的 break，结束当前所有的循环。
    
【while 语法：（先判断再执行循环）】
BEGIN
   [标签：] WHILE 循环条件 DO
        循环体;
    END WHILE [标签];
END
    
【loop 语法：（没有条件的死循环，需使用 leave 退出）】
BEGIN
   [标签：] LOOP
        循环体;
    END LOOP [标签];
END
    
【repeat 语法：（先执行循环再判断）】
BEGIN
   [标签：] REPEAT
        循环体;
    UNTIL 结束循环的条件
    END REPEAT [标签];
END

【举例：】
DELIMITER $

DROP PROCEDURE IF EXISTS test1 $
CREATE PROCEDURE test1(OUT a INT, OUT b INT, OUT c INT)
BEGIN
    # 测试 while 循环，temp >= 10 时退出循环。
    DECLARE temp INT DEFAULT 0;
    testWhile: WHILE temp < 10 DO
        SET temp = temp + 1;
        # 当 temp = 8 时，给 a 赋值并退出 while 循环
        IF temp = 8 THEN SET a = temp; LEAVE testWhile;
        END IF;
    END WHILE testWhile;
    
    # 测试 repeat 循环， temp >= 10 时退出循环。
    SET temp = 0;
    testRepeat: REPEAT 
        SET temp = temp + 1;
        # 当 temp = 7 时，给 b 赋值并退出 repeat 循环
        IF temp = 7 THEN SET b = temp; LEAVE testRepeat;
        END IF;
        # 注意 until 是循环结束条件
        UNTIL temp >= 10
    END REPEAT testRepeat;
    
    # 测试 loop 循环
    SET temp = 0;
    testLoop: LOOP
        SET temp = temp + 1;
        # 当 temp = 6 时，给 c 赋值并退出 loop 循环
        IF temp = 6 THEN SET c = temp; LEAVE testLoop;
        END IF;
    END LOOP testLoop;
END $ 

DELIMITER ;

CALL test1(@a, @b, @c);

SELECT @a, @b, @c;

到此这篇关于mysql常见笔试题小结的文章就介绍到这了,更多相关mysql笔试题内容请搜索以前的文章或继续浏览下面的相关文章，希望大家以后多多支持！

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容