概述
抽空总结一下mysql的一些概念性内容,涉及存储过程、函数、视图、触发器等。
一、查看存储过程、函数、视图、触发器、表
1、存储过程
select * from mysql.proc where type='PROCEDURE';
show procedure status;
show create procedure proc_name; //存储过程定义
2、函数
select * from mysql.proc where type='FUNCTION';
show function status;
show create function func_name; //函数定义
3、视图
SELECT * from information_schema.VIEWS
SHOW CREATE VIEW 视图名
4、表
SELECT * from information_schema.TABLES
show create table table_name;
5、触发器
SELECT * FROM information_schema.triggers;
show create trigger trigger_name;
二、函数
mysql自定义函数就是实现程序员需要sql逻辑处理,参数是IN参数,含有RETURNS字句用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。
1、语法:
创建:
CREATE FUNCTION 函数名称(参数列表)
RETURNS 返回值类型
函数体
修改:
ALTER FUNCTION 函数名称 [characteristic ...]
删除:
DROP FUNCTION [IF EXISTS] 函数名称
调用:
SELECT 函数名称(参数列表)
2、实例
CREATE DEFINER=`root`@`%` FUNCTION `getUnitChildList`(employeeCode VARCHAR(30)) RETURNS text CHARSET utf8
BEGIN
#最终返回的组织code字符串
DECLARE codeResult TEXT DEFAULT "-1";
#最终返回的组织id字符串
DECLARE result TEXT DEFAULT "-1";
#组织id的中间字符串
DECLARE sTempChd VARCHAR(10000);
#最上层组织的字符串
DECLARE currentCode VARCHAR(10000) DEFAULT '-1';
#游标是否结束标识
DECLARE endFlag INT DEFAULT 0;
#定义游标-unitCur,查询出当前员工拥有的所有岗位的组织code
DECLARE unitCur CURSOR FOR
SELECT DISTINCT
pos.unit_code
FROM
hr_org_position_b pos
LEFT JOIN hr_employee_assign ass ON pos.POSITION_CODE = ass.POSITION_CODE
LEFT JOIN hr_employee HE ON he.EMPLOYEE_CODE = ass.EMPLOYEE_CODE
WHERE
he.EMPLOYEE_CODE = employeeCode
AND ass.ENABLED_FLAG = 'Y'
AND pos.ENABLED_FLAG = 'Y';
#结束set为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET endFlag = 1;
#开始遍历游标
OPEN unitCur;
REPEAT
FETCH unitCur INTO currentCode;
SET sTempChd = currentCode;
#当前组织也加上
SET result=CONCAT(result,",",sTempChd);
#循环,遍历出当前组织code下的组织code的list,存入字符串,逗号分隔
WHILE sTempChd IS NOT NULL
DO
#拼接结果字符串到result
SELECT GROUP_CONCAT(unit_code) FROM hr_org_unit_b
WHERE FIND_IN_SET(parent_code,sTempChd)>0 INTO sTempChd;
IF sTempChd IS NOT NULL THEN
SET result=CONCAT(result,",",sTempChd);
END IF;
END WHILE;
UNTIL endFlag = 1
END REPEAT;
#关闭游标
CLOSE unitCur;
RETURN result;
END
三、创建存储过程
一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,比一个个执行sql语句效率高,用户通过指定存储过程的名字并给出参数来执行它。参数可以为IN, OUT, 或INOUT
1、语法
创建:
CREATE PROCEDURE 过程名 (参数列表) [characteristic ...]
函数体
修改:
ALTER PROCEDURE 过程名 [characteristic ...]
删除:
DROP PROCEDURE [IF EXISTS] 过程名
调用:
CALL 过程名(参数列表)
2、实例
--2.1、建表
create table user(
id mediumint(8) unsigned not null auto_increment,
name char(15) not null default "",
pass char(32) not null default "",
note text not null,
primary key (id)
)engine=Innodb charset=utf8;
insert into user(name, pass, note) values('sss','123', 'ok');
--2.2、存储过程
delimiter //
create procedure proc_name (in parameter integer)
begin
if parameter=0 then
select * from user order by id asc;
else
select * from user order by id desc;
end if;
end;
//
--2.3、执行:
call proc_name(0);
//
创建: CREATE FUNCTION 函数名称(参数列表)
RETURNS 返回值类型
函数体 修改: ALTER FUNCTION 函数名称 [characteristic ...]
删除: DROP FUNCTION [IF EXISTS] 函数名称 调用: SELECT 函数名称(参数列表)
四、视图
视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图
的查询所引用的表,并且在引用视图时动态生成。对其中所引用的基础表来说,视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。
通过视图进行查询没有任何限制,通过它们进行数据修改时的限制也很少。视图是存储在数据库中的查询的SQL 语句,它主要出于两种原因:安全原因, 视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,另一原因是可使复杂的查询易于理解和使用。这个视图就像一个“窗口”,从中只能看到你想看的数据列。这意味着你可以在这个视图上使用SELECT *,而你看到的将是你在视图定义里给出的那些数据列。
1、语法
创建:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(列名列表)]
AS 查询语句
[WITH [CASCADED | LOCAL] CHECK OPTION]
修改:
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
删除:
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
调用:
select v.pass from my_view v;
2、实例
CREATE ALGORITHM = TEMPTABLE DEFINER = `mysql.sys` @`localhost` SQL SECURITY INVOKER VIEW `host_summary` AS SELECT
IF
( isnull( `performance_schema`.`accounts`.`HOST` ), 'background', `performance_schema`.`accounts`.`HOST` ) AS `host`,
sum( `stmt`.`total` ) AS `statements`,
`sys`.`format_time` ( sum( `stmt`.`total_latency` ) ) AS `statement_latency`,
`sys`.`format_time` ( ifnull( ( sum( `stmt`.`total_latency` ) / nullif( sum( `stmt`.`total` ), 0 ) ), 0 ) ) AS `statement_avg_latency`,
sum( `stmt`.`full_scans` ) AS `table_scans`,
sum( `io`.`ios` ) AS `file_ios`,
`sys`.`format_time` ( sum( `io`.`io_latency` ) ) AS `file_io_latency`,
sum( `performance_schema`.`accounts`.`CURRENT_CONNECTIONS` ) AS `current_connections`,
sum( `performance_schema`.`accounts`.`TOTAL_CONNECTIONS` ) AS `total_connections`,
count( DISTINCT `performance_schema`.`accounts`.`USER` ) AS `unique_users`,
`sys`.`format_bytes` ( sum( `mem`.`current_allocated` ) ) AS `current_memory`,
`sys`.`format_bytes` ( sum( `mem`.`total_allocated` ) ) AS `total_memory_allocated`
FROM
(
(
( `performance_schema`.`accounts` JOIN `sys`.`x$host_summary_by_statement_latency` `stmt` ON ( ( `performance_schema`.`accounts`.`HOST` = `stmt`.`host` ) ) )
JOIN `sys`.`x$host_summary_by_file_io` `io` ON ( ( `performance_schema`.`accounts`.`HOST` = `io`.`host` ) )
)
JOIN `sys`.`x$memory_by_host_by_current_bytes` `mem` ON ( ( `performance_schema`.`accounts`.`HOST` = `mem`.`host` ) )
)
GROUP BY
IF
( isnull( `performance_schema`.`accounts`.`HOST` ), 'background', `performance_schema`.`accounts`.`HOST` )
五、触发器
与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete, update)时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。
1、语法
创建:
CREATE TRIGGER <触发器名称> --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
{ BEFORE | AFTER } --触发器有执行的时间设置:可以设置为事件发生前或后。
{ INSERT | UPDATE | DELETE } --同样也能设定触发的事件:它们可以在执行insert、update或delete的过程中触发。
ON <表名称> --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
FOR EACH ROW --触发器的执行间隔:FOR EACH ROW子句通知触发器 每隔一行执行一次动作,而不是对整个表执行一次。
<触发器SQL语句> --触发器包含所要触发的SQL语句:这里的语句可以是任何合法的语句, 包括复合语句,但是这里的语句受的限制和函数的一样。
删除:
DROP TRIGGER 方案名称.触发器名称
2、实例
CREATE DEFINER = `mysql.sys` @`localhost` TRIGGER sys_config_insert_set_user BEFORE INSERT ON sys_config FOR EACH ROW
BEGIN
IF
@sys.ignore_sys_config_triggers != TRUE
AND NEW.set_by IS NULL THEN
SET NEW.set_by = USER ( );
END IF;
END