本篇文章主要是对MySQL学习时的一些总结，作为学习笔记记录。

数据来源

存储过程和函数

存储过程和函数有点类似于C/C++中的函数，可以简化操作，提高代码的重用性。

存储过程

存储过程是一组预先编译好的sql语句的集合，可以理解成批处理语句。存储过程的好处主要有：

  • 提高代码的重用性
  • 简化操作
  • 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

创建语法

CREATE PROCEDURE procedurename (arg1, arg2......) 
BEGIN
  procedurebody (一组合法的 SQL 语句) 
END ;

对于存储过程来说，有几点需要注意：

  • 参数列表包含三部分：参数模式、参数名、参数类型
  • 参数模式主要分为in、out、inout
  • in：该参数可以作为输入，需要调用方传入值
  • out：该参数可以作为输出，可以作为返回值
  • inout：该参数既可以作为输入也可以作为输出，既需要传入值，又可以返回值
  • 如果存储过程体只有一句话，则begin end可以省略
  • 存储过程体中的每条sql语句的结尾要求必须加分号
  • 存储过程的结尾可以使用(DELIMITER 结束标记)重新设置

调用语法

CALL procedurename(arg1,arg2......);

空参列表

#案例：插入到admin表中三条记录
DELIMITER $
CREATE PROCEDURE p1 () 
BEGIN
  INSERT INTO admin (username, PASSWORD) 
  VALUES
    ('tom', '0000'),
    ('jane', '1111'),
    ('jack', '2222') ;
END $


#调用
CALL p1()$

创建带in模式参数的存储过程

#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
CREATE PROCEDURE p2 (IN beautyName VARCHAR (20)) 
BEGIN
  SELECT 
    bo.* 
  FROM
    boys AS bo 
    RIGHT JOIN beauty AS b 
      ON b.boyfriend_id = bo.`id` 
  WHERE b.name = beautyName ;
END $

#调用
CALL p2 ('小昭') $


#案例2 ：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE p3 (
  IN username VARCHAR (20),
  IN PASSWORD VARCHAR (20)
) 
BEGIN
  DECLARE result INT DEFAULT 0 ;
  SELECT 
    COUNT(*) INTO result 
  FROM
    admin 
  WHERE admin.`username` = username 
    AND admin.`password` = PASSWORD ;
  SELECT 
    IF(result > 0, 'success', 'failed') ;
END $

#调用
CALL p3('tom','0000')$

创建带out模式参数的存储过程

#案例1：根据输入的女神名，返回对应的男神名
CREATE PROCEDURE p4 (
  IN beautyName VARCHAR (20),
  OUT boyName VARCHAR (20)
) 
BEGIN
  SELECT 
    bo.boyname INTO boyname 
  FROM
    boys AS bo 
    RIGHT JOIN beauty AS b 
      ON b.boyfriend_id = bo.id 
  WHERE b.name = beautyName ;
END $

#调用
CALL p4('小昭',@boyname) $
SELECT @boyname$


#案例2：根据输入的女神名，返回对应的男神名和魅力值
CREATE PROCEDURE p5 (
  IN beautyName VARCHAR (20),
  OUT boyName VARCHAR (20),
  OUT usercp INT
) 
BEGIN
  SELECT 
    bo.boyname,
    bo.usercp INTO boyName,
    usercp 
  FROM
    boys AS bo 
    RIGHT JOIN beauty b 
      ON b.boyfriend_id = bo.`id` 
  WHERE b.name = beautyName ;
END $

#调用
CALL p5('小昭',@boyname,@cpval)$
SELECT @boyname,@cpval$

创建带inout模式参数的存储过程

#案例：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE p6 (INOUT a INT, INOUT b INT) 
BEGIN
  SET a = 2 * a ;
  SET b = 2 * b ;
END $

#调用
SET @m = 10$
SET @n = 20$
CALL p6(@m,@n)$
SELECT @m,@n$

删除语法

DROP PROCEDURE procedurename;

查看语法

SHOW CREATE PROCEDURE procedurename;

 函数

函数也是一组预先编译好的sql语句的集合，可以理解为批处理语句。使用函数主要有以下几个好处：

  • 提高代码的重用性
  • 简化操作
  • 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

存储过程和函数的区别

  • 存储过程可以有0个返回值，也可以有多个返回值，适合做批量插入，批量更新
  • 函数有且仅有一个返回值，适合做处理数据后返回一个结果

创建语法

CREATE FUNCTION functionname (arg1, arg2......) RETURNS returntype 
BEGIN
  functionbody 
END ;

对于函数来说，需要注意的是：

  • 参数列表包含两部分：参数名、参数类型
  • 函数体中要有return语句，如果没有会报错
  • 如果return语句没有放在函数体的最后也不报错，但并不建议
  • 函数体中有且仅有一句话，则可以省略begin end
  • 函数的结尾可以使用(DELIMITER 结束标记)重新设置

调用语法

SELECT functionname(arg1,arg2......);

无参有返回

#案例：返回公司的员工个数
CREATE FUNCTION f1 () RETURNS INT 
BEGIN
  DECLARE num INT DEFAULT 0 ;
  
  SELECT 
    COUNT(*) INTO num 
  FROM
    employees ;

  RETURN num ;
END $ 

#调用
SELECT f1()$

有参有返回

#案例1：根据员工名，返回它的工资
CREATE FUNCTION f2(empName VARCHAR(20)) RETURNS DOUBLE 
BEGIN
  SET @sal = 0;
  
  SELECT salary INTO @sal FROM employees AS e WHERE e.last_name = empName;
  
  RETURN @sal;

END $

#调用
SELECT f2('Popp') $


#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION f3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN

  DECLARE sal DOUBLE DEFAULT 0;

  SELECT 
    AVG(salary) INTO sal 
  FROM
    employees AS e 
    INNER JOIN departments AS d 
      ON e.department_id = d.department_id 
  WHERE d.department_name = deptName ;

  RETURN sal;

END $

#调用
SELECT f3('IT') $

删除语法

DROP FUNCTION functionname;

查看语法

SHOW CREATE FUNCTION functionname;