概述
抽空總結一下mysql的一些概念性內容，涉及存儲過程、函數(shù)、視圖、觸發(fā)器等。
一、查看存儲過程、函數(shù)、視圖、觸發(fā)器、表
1、存儲過程
select * from mysql.proc where type='PROCEDURE';show procedure status; show create procedure proc_name; //存儲過程定義
2、函數(shù)
select * from mysql.proc where type='FUNCTION';show function status;show create function func_name; //函數(shù)定義
3、視圖
SELECT * from information_schema.VIEWS SHOW CREATE VIEW 視圖名
4、表
SELECT * from information_schema.TABLES show create table table_name;
5、觸發(fā)器
SELECT * FROM information_schema.triggers;show create trigger trigger_name;
二、函數(shù)
mysql自定義函數(shù)就是實現(xiàn)程序員需要sql邏輯處理，參數(shù)是IN參數(shù),含有RETURNS字句用來指定函數(shù)的返回類型，而且函數(shù)體必須包含一個RETURN value語句。
1、語法：
創(chuàng)建： CREATE FUNCTION 函數(shù)名稱(參數(shù)列表) RETURNS 返回值類型 函數(shù)體修改： ALTER FUNCTION 函數(shù)名稱 [characteristic ...]刪除： DROP FUNCTION [IF EXISTS] 函數(shù)名稱調用： SELECT 函數(shù)名稱(參數(shù)列表)
2、實例
CREATE DEFINER=`root`@`%` FUNCTION `getUnitChildList`(employeeCode VARCHAR(30)) RETURNS text CHARSET utf8BEGIN #最終返回的組織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); #循環(huán)，遍歷出當前組織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
三、創(chuàng)建存儲過程
一組為了完成特定功能的SQL 語句集，存儲在數(shù)據(jù)庫中，經(jīng)過第一次編譯后再次調用不需要再次編譯，比一個個執(zhí)行sql語句效率高，用戶通過指定存儲過程的名字并給出參數(shù)來執(zhí)行它。參數(shù)可以為IN, OUT, 或INOUT
1、語法
創(chuàng)建： CREATE PROCEDURE 過程名 (參數(shù)列表) [characteristic ...] 函數(shù)體修改： ALTER PROCEDURE 過程名 [characteristic ...]刪除： DROP PROCEDURE [IF EXISTS] 過程名調用： CALL 過程名(參數(shù)列表)
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)beginif parameter=0 thenselect * from user order by id asc;elseselect * from user order by id desc;end if;end;// --2.3、執(zhí)行：call proc_name(0);//
四、視圖
視圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行數(shù)據(jù)。但是，視圖并不在數(shù)據(jù)庫中以存儲的數(shù)據(jù)值集形式存在。行和列數(shù)據(jù)來自由定義視圖
的查詢所引用的表，并且在引用視圖時動態(tài)生成。對其中所引用的基礎表來說，視圖的作用類似于篩選。定義視圖的篩選可以來自當前或其它數(shù)據(jù)庫的一個或多個表，或者其它視圖。
通過視圖進行查詢沒有任何限制，通過它們進行數(shù)據(jù)修改時的限制也很少。視圖是存儲在數(shù)據(jù)庫中的查詢的SQL 語句，它主要出于兩種原因：安全原因， 視圖可以隱藏一些數(shù)據(jù)，如：社會保險基金表，可以用視圖只顯示姓名，地址，而不顯示社會保險號和工資數(shù)等，另一原因是可使復雜的查詢易于理解和使用。這個視圖就像一個“窗口”，從中只能看到你想看的數(shù)據(jù)列。這意味著你可以在這個視圖上使用SELECT *，而你看到的將是你在視圖定義里給出的那些數(shù)據(jù)列。
1、語法
創(chuàng)建：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 SELECTIF ( 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 BYIF ( isnull( `performance_schema`.`accounts`.`HOST` ), 'background', `performance_schema`.`accounts`.`HOST` )
五、觸發(fā)器
與表事件相關的特殊的存儲過程，它的執(zhí)行不是由程序調用，也不是手工啟動，而是由事件來觸發(fā)，比如當對一個表進行操作（insert，delete， update）時就會激活它執(zhí)行。
觸發(fā)器經(jīng)常用于加強數(shù)據(jù)的完整性約束和業(yè)務規(guī)則等。
1、語法
創(chuàng)建：CREATE TRIGGER <觸發(fā)器名稱> --觸發(fā)器必須有名字，最多64個字符，可能后面會附有分隔符.它和MySQL中其他對象的命名方式基本相象.{ BEFORE | AFTER } --觸發(fā)器有執(zhí)行的時間設置：可以設置為事件發(fā)生前或后。{ INSERT | UPDATE | DELETE } --同樣也能設定觸發(fā)的事件：它們可以在執(zhí)行insert、update或delete的過程中觸發(fā)。ON <表名稱> --觸發(fā)器是屬于某一個表的:當在這個表上執(zhí)行插入、 更新或刪除操作的時候就導致觸發(fā)器的激活. 我們不能給同一張表的同一個事件安排兩個觸發(fā)器。FOR EACH ROW --觸發(fā)器的執(zhí)行間隔：FOR EACH ROW子句通知觸發(fā)器 每隔一行執(zhí)行一次動作，而不是對整個表執(zhí)行一次。<觸發(fā)器SQL語句> --觸發(fā)器包含所要觸發(fā)的SQL語句：這里的語句可以是任何合法的語句， 包括復合語句，但是這里的語句受的限制和函數(shù)的一樣。刪除：DROP TRIGGER 方案名稱.觸發(fā)器名稱2、實例
CREATE DEFINER = `mysql.sys` @`localhost` TRIGGER sys_config_insert_set_user BEFORE INSERT ON sys_config FOR EACH ROWBEGIN IF @sys.ignore_sys_config_triggers != TRUE AND NEW.set_by IS NULL THEN SET NEW.set_by = USER ( ); END IF;END
