MySQL 高级
视图
视图就是将他被存储在数据库中,可以重复使用,视图中并不存储数据
定义视图
CREATE VIEW 视图名 AS SELECT 列 1,列 2... FROM 表(查询语句);
使用视图
SELECT * FROM 视图名
删除视图
drop view 视图名
CREATE VIEW sel_news AS
SELECT
n.id,
n.title,
t.type_name
FROM
news n
LEFT JOIN TYPE t
ON n.typeid = t.typeid;
-- 使用视图
SELECT
*
FROM
sel_news;
-- 删除视图
DROP VIEW sel_news
存储过程
对数据库的认知:里面可以以表尾单位存储数据,使用SQL语言,操作数据
数据库中也可以使用java语言一样有逻辑处理功能
事先被编写好,存储在数据库中,使用时直接调用即可
优点:处理某个逻辑的过程直接存储在数据库中.运行速度较快
缺点:对数据库的依赖程度较高,移植性差
MySQL 存储过程的定义
>
>创建存储过程的语法格式
>
> create procedure 存储过程名([in 变量名 类型,out 参数 2,…])
> begin
>
> [declare 变量名 类型 [DEFAULT 值];]
>
> 存储过程语句块;
>
>
> end;
语法解析:
1.存储过程的参数分为 in,out,inout 三种类型。
2.in 代表输入参数(默认情况下为 in 参数),表示该参数的值必须由调用程序 指定。
3.out 代表输出参数,表示该参数的值经存储过程计算后,将 out 参数的计算结 果返回给调用程序。
4.inout 代表即是输入参数,又是输出参数,表示该参数的值即可以由调用程序 指定,又可以将 inout 参数的计算结果返回给调用程序。
5.存储过程中的语句必须包含在 begin 和 end 之间。
6.declare 中用来声明变量,变量默认赋值使用 default,语句块中改变变量值,使 用 set 变量=值;
eg:
-- ------------------------------------存储过程--------------------------------------------------------------
DELIMITER $$
-- 创建存储过程
CREATE
PROCEDURE demo1()
-- 存储过程体
BEGIN
-- declare 用来声明变量
DECLARE v_name VARCHAR(10) DEFAULT '';
SET v_name = 'jim';
SELECT v_name; -- 测试输出语句
END$$
DELIMITER ;
-- 调用存储过程
CALL demo1();
-- 查询新闻类型为1的有几条 演示入参和出参
DELIMITER $$
CREATE PROCEDURE type_count (IN p_typeid INT, OUT p_count INT) -- 存储过程体
BEGIN
SELECT
COUNT (*) INTO p_count
FROM
news
WHERE typeid = p_typeid;
SELECT
p_count;
END $$
DELIMITER;
-- 在一个存储过程中调用另一个存储过程
CALL type_count (1, @p_count)
-- 存储过程中逻辑判断 if else
DELIMITER $$
CREATE PROCEDURE demo2 (IN p_day INT, OUT p_name VARCHAR (10))
BEGIN
IF p_day = 0
THEN SET p_name = "星期天";
SELECT
p_name;
ELSEIF p_day = 1
THEN
SET p_name="星期一";
SELECT p_name;
ELSE SET p_name= "无效日期";
SELECT p_name;
END IF;
END $$
DELIMITER;
CALL demo2 (0,@p_name);
-- 使用存储过程插入信息
DELIMITER $$
CREATE PROCEDURE save_admin (
IN p_account VARCHAR (10),
IN p_password VARCHAR (50),
OUT p_result VARCHAR (10)
)BEGIN
DECLARE v_count INT DEFAULT 0;
SELECT COUNT(*) INTO v_count FROM admin WHERE account = p_account;
IF v_count=0 THEN
INSERT INTO admin (account,PASSWORD)VALUES(p_account,p_password);
SET p_result = '保存成功';
ELSE
SET p_result = '账号已存在';
SELECT p_result;
END IF;
END $$
-- 测试CALL
CALL save_admin ('001','123',@p_result)
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
admin
WHERE account = p_account;
IF v_count =0
THEN
INSERT INTO admin (account,sex)
VALUES
(p_account,p_sex);
SET res_mark = 0;
ELSE SET res_mark = 1;
END IF;
END $$
-- 测试CALL
CALL save_admin ('001','123',@res_mark)
函数
函数类似于存储过程,但是函数主要用于查询
create function 函数名([参数列表]) returns 数据类型
begin DECLARE 变量;
sql 语句;
return 值;
end;
注意:
1.参数列表包含两部分:参数名 参数类型
2.函数体:肯定会有 return 语句,如果没有会报错
3.函数体中仅有一句话,则可以省略 begin end
4.使用 delimter 语句设置结束标记 设置函数可以没有参数 SET GLOBAL log_bin_trust_function_creators=TRUE;删除函数
DROP FUNCTION 函数名
eg:
-- ------------------------------------------函数--------------------------------------------------------
-- 函数 不带参数的
DELIMITER $$
CREATE FUNCTION test() RETURNS INT
BEGIN DECLARE v_num INT DEFAULT 0;
SELECT
COUNT(*) INTO v_num
FROM
admin;
RETURN v_num;
END $$
DELIMITER
-- 测试
SELECT test ();
-- 带参数的
DELIMITER $$
CREATE
FUNCTION findType(p_type INT) RETURNS VARCHAR(10)
BEGIN
DECLARE v_type VARCHAR(10) DEFAULT '';
IF p_type = 0 THEN
SET v_type = '超级管理员';
ELSE
SET v_type = '管理员';
END IF;
RETURN v_type;
END$$
SELECT account,id,findType(TYPE) FROM admin
DELIMITER $$
CREATE
FUNCTION find_news_type(p_typeid INT) RETURNS VARCHAR(10)
BEGIN
DECLARE v_type VARCHAR(10) DEFAULT '';
SELECT type_name INTO v_type FROM TYPE WHERE typeid = p_typeid;
RETURN v_type;
END$$
SELECT id,title,find_news_type(typeid) FROM news
触发器
类似于存储过程,函数,与表相关,有点像事件
对表新增,修改,删除之前或之后自动触发
触发器具有以下特点:
1.与表相关联
触发器定义在特定的表上,这个表称为触发器表。
2.自动激活触发器
当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时,如果对表上的这
个特定操作定义了触发器,该触发器自动执行,这是不可撤销的。
3.不能直接调用
与存储过程不同,触发器不能被直接调用,也不能传递或接受参数。
4.作为事务的一部分
触发器与激活触发器的语句一起做为对一个单一的事务来对待,可以从触发器中
的任何位置回滚。
定义触发器的语法规则:
CREATE TRIGGER 触发器名称 触发时机 触发事件
ON 表名称
FOR EACH ROW -- 行级触发
BEGIN
语句
END;
语法解析:
1.触发器名称:是用来标识触发器的,由用户自定义。
2.触发时机:其值是 before 或 after。
3.触发事件:其值是 insert,update 和 delete
4.表名称:标识建立触发器的表名,即在哪张表上建立触发器
5.语句:是触发器程序体,触发器程序可以使用 begin 和 end 作为开始和结束, 中间包含多条语句;
eg:
-- 触发器
-- 在admin表中插入一条数据后自动更新admin_log表
DELIMITER $$
CREATE
TRIGGER save_adminLog AFTER
INSERT
ON admin
FOR EACH ROW BEGIN
INSERT INTO admin_log(id,account,oper_time) VALUES(new.id,new.account,NOW());
END$$
DELIMITER ;
INSERT INTO admin(account,PASSWORD)VALUES('s001','1231');
-- 当admin表和role表的主键分别都是admin_role表的外键约束时,
-- 想要删除admin表或role表中的数据,必须先删除admin_role表中的关联关系
DELIMITER $$
CREATE
TRIGGER delete_admin_role
BEFORE
DELETE
ON admin
FOR EACH ROW BEGIN
DELETE FROM admin_role WHERE adminid= old.id;
END$$
DELIMITER ;
DELETE FROM admin WHERE id=3