MySQL高级（一）

最新推荐文章于 2022-07-10 21:00:39 发布

追梦的烟火

最新推荐文章于 2022-07-10 21:00:39 发布

阅读量285

点赞数

文章标签：服务器数据库 sql

本文链接：https://blog.csdn.net/m0_61470267/article/details/125267184

版权

视图

视图就是将某个查询语句存储在数据中，并为其命名，视图中并不存储数据，数据还是在基本中存储。

定义视图

CREATE VIEW 视图名

AS SELECT 列 1，列 2... FROM 表(查询语句);

使用视图

SELECT * FROM 视图名

删除视图

drop view 视图

视图与基本表的区别：

视图是已经编译好的sql语句。而表不是
视图没有实际的物理记录。而表有。
表是内容，视图是窗口
表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时的对它进行修改，但视图只能有创建的语句来修改
表是内模式，视图是外模式
视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
视图的建立和删除只影响视图本身，不影响对应的基本表

存储过程

存储过程就把一段处理逻辑存入到数据库中，使用时，由jdbc调用既可。减少应用程序和数据库交互次数，在数据库内部执行，执行效率高。

存储事先需要定义，有3种类型参数： in 入参 out 返回 inout 接收/输出

创建存储过程

create procedure 存储过程名（[in 变量名类型，out 参数 2，…]）

begin

[declare 变量名类型 [DEFAULT 值];]

存储过程语句块;

end;

定义第一个存储过程

-- 开始位置
DELIMITER$$
CREATE PROCEDURE test()
BEGIN
-- 声明变量
DECLARE v_name VARCHAR(20) DEFAULT 'jim';
SET v_name = 'tom'; -- 变量赋值
SELECT v_name; -- 测试输出语句
END$$ -- 结束位置
-- 调用存储过程
CALL test1(

定义一个有参数的存储过程

DELIMITER$$
CREATE PROCEDURE findUserCount(IN p_type INT,OUT p_count INT)
BEGIN
-- 把 sql 中查询的结果赋给 变量
SELECT COUNT(*) INTO p_count FROM USER WHERE TYPE = p_type;
SELECT p_count;
END$$
测试
CALL findUserCount(1,@p_count); -- @p_count 测试输出参数

流程控制语句

DELIMITER$$
CREATE PROCEDURE test(IN p_day INT)
BEGIN
IF p_day=0 THEN
SELECT "星期天";
ELSEIF p_day=1 THEN
SELECT "星期一";
ELSEIF p_day=2 THEN
SELECT "星期二";
ELSE
SELECT "无效日期";
END IF;
END$$
测试
CALL test(2

case when

DELIMITER$$
CREATE PROCEDURE test(IN p_day INT)
BEGIN
CASE WHEN p_day = 0 THEN
SELECT "星期天";
ELSE
SELECT "星期一";
END CASE;
END$$
CALL test(2)

循环

DELIMITER$$
CREATE PROCEDURE test7()
BEGIN
DECLARE v_num INT DEFAULT 0; -- 循环开始
addnum:LOOP
SET v_num = v_num+1; -- 循环语句
-- 循环结束条件
IF v_num = 10 THEN
LEAVE addnum;
END IF;
END LOOP; -- 循环结束
SELECT v_num;
END$$
CALL test7()

使用存储过程插入信息

DELIMITER$$
CREATE PROCEDURE saveUser(IN p_account VARCHAR(20),IN p_sex CHAR(1),OUT res_mark
INT)
BEGIN
DECLARE v_count INT DEFAULT 0;
SELECT COUNT(*) INTO v_count FROM t_user WHERE account = p_account;
IF v_count = 0 THEN
INSERT INTO t_user(account,sex)VALUES(p_account,p_sex);
SET res_mark = 0;
ELSE
SET res_mark = 1;
END IF;
END$$

mybatis 调用存储过程

<parameterMap type="map" id=“usermap">
    <parameter property="addend1" jdbcType="VARCHAR" mode="IN"/>
    <parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>
jdbcType 必须指定
<insert id="saveUserDemo" parameterMap="usermap" statementType="CALLABLE">
    {call saveuser(?, ?)}
</insert>

Map<String, Object> parms = new HashMap<String, Object>();
parms.put("addend1", 3);
userDao.saveUserDemo(parms);
parms.get(“result”);//获得输出参数

函数

函数也是一个特殊的存储过程。存储过程有输入参数，还有输出的参数，没有返回值，里面可以完成逻辑处理.。不能在普通的sql中调用， call 存储过程。

函数一般用于查询场景，里面只有入参，没有输出参数，必须有返回值，可以在普通的sql中调用使用。

语法

create function 函数名([参数列表]) returns 数据类型

begin

DECLARE 变量；

sql 语句;

return 值;

end;

删除函数

DROP FUNCTION 函数名;

不带参数

DELIMITER$$
CREATE FUNCTION test() RETURNS INT
BEGIN
DECLARE v_num INT;
SELECT COUNT(*) INTO v_num FROM t_user;
RETURN v_num;
END$$
测试
select test() from dual;

带参数

DELIMITER$$
CREATE FUNCTION findDeptNameById(p_id INT) RETURNS VARCHAR(10)
BEGIN
    DECLARE v_name VARCHAR(10);
    SELECT NAME INTO v_name FROM dept WHERE id = p_id;
    RETURN v_name;
END$$
SELECT account,findDeptNameById(dept_id) FROM user
DELIMITER$$
CREATE FUNCTION checkUserType(p_type INT) RETURNS VARCHAR(4)
BEGIN
    IF p_type = 0 THEN
    RETURN '管理员';
    ELSE
    RETURN '业务用户';
    END IF;
END$$
SELECT tu.account,checkUserType(tu.type)utype FROM user tu

触发器

什么是触发器？
可以理解为特殊的存储过程，不需要调用，在满足某种条件时自动执行，对表进行增、删、改操作之前或者之后自动执行，类似于事件。作为事务的一部分。

create trigger 触发器名称触发时机（after或before）触发类型（insert、update、delete）

on 表名
for each row //行级触发
begin
//sql语句
end

案例：

删除用户时,自动触发删除用户菜单关系

DELIMITER $$
CREATE TRIGGER delete_user_menu BEFORE DELETE
ON t_user
FOR EACH ROW
BEGIN
DELETE FROM t_user_menu WHERE user_id = old.id;
END$$;

新增用户时，自动向其他表插入数据

DELIMITER $$
CREATE TRIGGER save_user_log AFTER INSERT
ON user
FOR EACH ROW
BEGIN
INSERT INTO test(id,NAME)VALUES(new.id,new.account);
END$$;
//执行

INSERT INTO user(account)VALUES('jim')