mysql的高级函数使用 mysql高级编程

文章目录

• 视图
• 存储过程

• 通过java调用存储过程

• 流程控制
• 函数
• 触发器

视图

查询的虚拟表:一条select语句的查询结果集

-- 创建视图，(如果起别名(在视图中只能调用别名))
CREATE VIEW v_viewname 
	AS 
	SELECT column1,column2 alias,column3 
	FROM table_name  
	WHERE 条件(condition)

-- 删除视图
DROP VIEW v_viewname
	
-- 通过视图进行查询寻(通过视图查询时仍然可以起别名)
SELECT column1,alias column2,column3 
	FROM v_viewname

存储过程

1. 存储过程就是一条或者多条SQL语句的集合，利用这些SQL语句完成一个或者多个逻辑功能。
2. 存储过程是已经编译好的代码，在调用的时候不必再次进行编译，从而提高-了程序的运行效率。
3. 存储过程基本语法

-- 创建存储过程
CREATE PROCEDURE 存储过程名()
BEGIN
	过程体;
END

-- 调用存储过程
CALL 存储过程名();

-- 删除存储过程
DROP PROCEDURE 存储过程名;

MySql有3中参数模式:即IN,OUT和INOUT

IN参数为输入参数(接收一个值)，该参数值有调用者传入(在过程中不能修改这个值)，并且只能够被存储过程读取。
OUT参数为输出参数(在调用过程时为空)，该类型的参数值由存储过程写入。OUT类型参数使用于存储过程向调用者返回多条信息的情况。
INOUT类型的参数同时具有IN参数和OUT参数的特性，在过程中可以读取和写入该类型参数。

• 创建带参数的存储过程:

CREATE PROCEDURE 存储过程名(
	[ IN | OUT | INOUT ] 参数1 数据类型,
	[ IN | OUT | INOUT ] 参数2 数据类型,……
)
begin 
	过程体;
end

4.示例代码

DELIMITER$$
CREATE PROCEDURE test() -- 创建存储过程
BEGIN
DECLARE v_name VARCHAR(20) DEFAULT 'tom'; -- 声明变量并赋默认值
   SET v_name = "jim"; -- 给变量赋值
   SELECT v_name;  -- 输出语句
END$$
-- 调用
CALL test();
-- 删除存储过程
DROP PROCEDURE IF EXISTS test

通过java调用存储过程

• 数据库存储过程语句

DELIMITER$$
CREATE PROCEDURE dept_update(IN p_name VARCHAR(10),IN p_adminId INT,OUT v_var INT)
BEGIN
	DECLARE v_count INT DEFAULT 0;-- 定义变量
	-- 搜索name是否存在
	SELECT COUNT(*) INTO v_count FROM t_dept WHERE NAME = p_name;
	IF v_count = 0 THEN
		-- 如果不存在就添加
		INSERT INTO t_dept(NAME,admin_id) VALUES(p_name,p_adminId);
		SET v_var = 0;-- 已经存储
	ELSE
		SET v_var = 1;-- 已经更新
	END IF;
END$$

• jdbc调用存储过程

package com.wenhua.jdbc.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCUtil {

	public static void main(String[] args) {
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			String url = 
                "jdbc:mysql://localhost:3306/project_db?characterEncoding=utf-8";
			
			String userName ="root";
		
			String passWord = "123456";

			Connection connection = 
                DriverManager.getConnection(url, userName, passWord);
			
			System.out.println("数据库连接成功");
			
			CallableStatement st =  
                connection.prepareCall("{call dept_update(?,?,?)}");
			
			st.setString(1, "业务");
			st.setInt(2, 14);
			st.registerOutParameter(3, java.sql.Types.INTEGER);
			
			st.executeUpdate();
			
			Integer res = st.getInt(3);
			System.out.println(res);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

流程控制

• if……else
• if……elseif……else
• when
• loop循环

-- if……else
DELIMITER$$
CREATE PROCEDURE test1(IN v_date INT) -- 创建存储过程
BEGIN
-- 声明变量
	DECLARE v_name VARCHAR(20) DEFAULT 'tom';
	IF v_date=1 THEN 
	SELECT '星期一';
	ELSE 
	SELECT '无效数据';
	END IF;
END$$

CALL test1(1);

-- if……elseif……else
DELIMITER$$
CREATE PROCEDURE test2(IN v_date INT)
BEGIN
	IF v_date = 1 THEN
		SELECT '星期一' AS '日期';
	ELSEIF v_date = 2 THEN
		SELECT '星期二' AS '日期';
	ELSEIF v_date = 3 THEN 
		SELECT '星期三' AS '日期';
	ELSE 
		SELECT '无效日期' AS '日期';
	END IF;
END$$

CALL test2(2);

-- when
DELIMITER$$
CREATE PROCEDURE test3(IN v_date INT)
BEGIN
	CASE WHEN v_date = 1 THEN
		SELECT '星期一' AS '日期';
	ELSE 
		SELECT '无效日期' AS '日期';
	END CASE;
END$$

CALL test3(2)

-- loop循环
DELIMITER$$
CREATE PROCEDURE test4(IN v_num INT)
BEGIN
	DECLARE v_stop INT DEFAULT 0;-- 定义变量
	stopNum : LOOP
		SET v_stop = v_stop+1;-- 循环语句
	IF v_stop > v_num -- 循环结束条件
	THEN LEAVE stopNum;
	END IF;
	END LOOP;-- 循环结束
	SELECT v_stop;
END$$

CALL test4(3)

• 控制流程练习

-- 仅一项操作
DELIMITER$$
CREATE PROCEDURE dept_update(IN p_name VARCHAR(10),IN p_adminId INT,OUT v_var INT)
BEGIN
	DECLARE v_count INT DEFAULT 0;-- 定义变量
	-- 搜索name是否存在
	SELECT COUNT(*) INTO v_count FROM t_dept WHERE NAME = p_name;
	IF v_count = 0 THEN
		-- 如果不存在就添加
		INSERT INTO t_dept(NAME,admin_id) VALUES(p_name,p_adminId);
		SET v_var = 0;-- 已经存储
	ELSE
		SET v_var = 1;-- 已经更新
	END IF;
END$$`deptUpdateOrInsert`

CALL deptUpdateOrInsert('业务',15,@v_var);

-- 通过判断进行数据的完善
DELIMITER$$
CREATE PROCEDURE test6(IN p_name VARCHAR(10),IN p_adminId INT,OUT v_var INT)
BEGIN
	DECLARE v_name INT DEFAULT 0;-- 定义变量
	DECLARE v_admin INT DEFAULT 0;-- 定义变量
	-- 搜索name,admin_id是否存在
	SELECT COUNT(*) INTO v_name FROM t_dept WHERE NAME = p_name;
	SELECT COUNT(*) INTO v_admin FROM t_dept WHERE admin_id = p_adminId; 
	IF v_name = 0 AND v_admin = 0 THEN
		INSERT INTO t_dept(NAME,admin_id) VALUES(p_name,p_adminId);-- 如果不存在就添加
		SET v_var = 0;-- 已经存储
	ELSEIF v_name = 1 AND v_admin = 0 THEN
		UPDATE t_dept SET admin_id = p_adminId WHERE NAME = p_name;
		SET v_var = 1;-- 已经更新
	ELSE 
		SET v_var = 2; -- 领导id冲突
	END IF;
	SELECT v_var;
END$$

函数

函数与存储过程的异同点:

• 相同点:

• 将完成某个功能的代码存储起来

• 不同点:

• 存储过程没有返回值，只有输入参数和输出参数。通过call关键字调用(主要用于一些增删改的逻辑处理)
• 函数有返回值，只有输入参数。直接嵌套在sql语句中执行(主要用于封装单行sql语句的查询)

• 基本语法:

-- 开始
DELIMITER$$
-- 创建函数名以及返回值类型
CREATE FUNCTION function_name() RETURNS INT
BEGIN
	-- 定义变量
	DECLARE v_num INT;
	-- sql语句(或赋值)
	SET v_num = 10;
	-- 返回值
	RETURN v_num;
-- 结束
END$$
-- 查询语句
SELECT function_name() FROM DUAL;
-- 删除函数
DROP FUNCTION IF EXISTS function_name

• 案例1

-- 如果不用函数直接向让sql语句更直观(不然显示0和1用户不知道意思)
SELECT account,CASE WHEN TYPE = 0 THEN '管理员'
	ELSE '业务用户' END FROM t_user
	
-- 通过函数封装(单行函数就是每行都会调用)
DELIMITER$$ 
CREATE FUNCTION check_user_type(f_type INT) RETURNS VARCHAR(4)
BEGIN
	IF f_type = 0 THEN
		RETURN '管理员';
	ELSE 
		RETURN '业务用户';
	END IF;
END$$

-- 调用函数
SELECT tu.account,check_user_type(tu.type) FROM t_user tu;

• 案例2

-- 通过用户查询对应的部门名字
SELECT account,td.name FROM t_user tu 
	LEFT JOIN t_dept td ON td.id = tu.dept_id
	
-- 通过函数封装
DELIMITER$$
 CREATE FUNCTION find_dept_name(f_id INT) RETURNS VARCHAR(10)
 BEGIN 
	DECLARE v_name VARCHAR(10);
	SELECT NAME INTO v_name FROM t_dept WHERE id = f_id ;
	RETURN v_name;
END$$

-- 调用函数
SELECT account,find_dept_name(dept_id) FROM t_user;

触发器

触发器是一种特殊的存储过程(把某段处理包装起来)，不需要用户调用，当对表添加，修改，删除之前或者之后自动执行的存储过程。注意new和old关键字，在layUI中表格数据添加时，判断时函数中用到的row代表整条记录的意思。

• 与表关联
• 自动激活触发器
• 不能直接调用
• 作为事务的一部分

基本语法:

DELIMITER$$
-- 创建触发器      触发器名     触发时机          触发事件
CREATE TRIGGER trigger_name AFTER/BEFORE  DELETE/UPDATE/INSERT
--    on连接关联表    行级触发
	ON table_name FOR EACH ROW
-- 开始
BEGIN 
	-- sql语句(old代表删除的行记录)
	DELETE FROM t_user_menu WHERE user_id = old.id;
-- 结束
END$$

• 案例1

DELIMITER$$
-- 创建触发器     触发器名     触发时机 触发事件
CREATE TRIGGER del_user_menu AFTER  DELETE
--  on 关联的表
	ON t_user
-- 行级触发
FOR EACH ROW
-- 开始
BEGIN 
	-- sql语句(old代表删除的行记录)
	DELETE FROM t_user_menu WHERE user_id = old.id;
-- 结束
END$$

-- 删除语句
DELETE FROM t_user WHERE id = 15;

• 案例2

DELIMITER$$
-- 创建触发器     触发器名      触发时机   触发事件
CREATE TRIGGER save_user_log  AFTER    INSERT
--  on 关联的表  行级触发
    ON t_user  FOR EACH ROW
-- 开始
BEGIN 
	-- sql语句(new表示最新插入的数据记录)
	INSERT INTO t_login_log(user_id,oper_time) VALUES(new.id,NOW());
-- 结束
END$$

-- insert语句调用
INSERT INTO t_user(account) VALUES('test12');