概述
抽空總結一下mysql的一些概念性?xún)热?，涉及存儲過(guò)程、函數、視圖、觸發(fā)器等。
一、查看存儲過(guò)程、函數、視圖、觸發(fā)器、表
1、存儲過(guò)程
select * from mysql.proc where type='PROCEDURE';show procedure status; show create procedure proc_name; //存儲過(guò)程定義
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、觸發(fā)器
SELECT * FROM information_schema.triggers;show create trigger trigger_name;
二、函數
mysql自定義函數就是實(shí)現程序員需要sql邏輯處理，參數是IN參數,含有RETURNS字句用來(lái)指定函數的返回類(lèi)型，而且函數體必須包含一個(gè)RETURN value語(yǔ)句。
1、語(yǔ)法：
創(chuàng )建： CREATE FUNCTION 函數名稱(chēng)(參數列表) RETURNS 返回值類(lèi)型 函數體修改： ALTER FUNCTION 函數名稱(chēng) [characteristic ...]刪除： DROP FUNCTION [IF EXISTS] 函數名稱(chēng)調用： SELECT 函數名稱(chēng)(參數列表)
2、實(shí)例
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，查詢(xún)出當前員工擁有的所有崗位的組織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;#開(kāi)始遍歷游標 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; #關(guān)閉游標 CLOSE unitCur; RETURN result;END
三、創(chuàng )建存儲過(guò)程
一組為了完成特定功能的SQL 語(yǔ)句集，存儲在數據庫中，經(jīng)過(guò)第一次編譯后再次調用不需要再次編譯，比一個(gè)個(gè)執行sql語(yǔ)句效率高，用戶(hù)通過(guò)指定存儲過(guò)程的名字并給出參數來(lái)執行它。參數可以為IN, OUT, 或INOUT
1、語(yǔ)法
創(chuàng )建： CREATE PROCEDURE 過(guò)程名 (參數列表) [characteristic ...] 函數體修改： ALTER PROCEDURE 過(guò)程名 [characteristic ...]刪除： DROP PROCEDURE [IF EXISTS] 過(guò)程名調用： CALL 過(guò)程名(參數列表)
2、實(shí)例
--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、存儲過(guò)程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、執行：call proc_name(0);//
四、視圖
視圖是一個(gè)虛擬表，其內容由查詢(xún)定義。同真實(shí)的表一樣，視圖包含一系列帶有名稱(chēng)的列和行數據。但是，視圖并不在數據庫中以存儲的數據值集形式存在。行和列數據來(lái)自由定義視圖
的查詢(xún)所引用的表，并且在引用視圖時(shí)動(dòng)態(tài)生成。對其中所引用的基礎表來(lái)說(shuō)，視圖的作用類(lèi)似于篩選。定義視圖的篩選可以來(lái)自當前或其它數據庫的一個(gè)或多個(gè)表，或者其它視圖。
通過(guò)視圖進(jìn)行查詢(xún)沒(méi)有任何限制，通過(guò)它們進(jìn)行數據修改時(shí)的限制也很少。視圖是存儲在數據庫中的查詢(xún)的SQL 語(yǔ)句，它主要出于兩種原因：安全原因， 視圖可以隱藏一些數據，如：社會(huì )保險基金表，可以用視圖只顯示姓名，地址，而不顯示社會(huì )保險號和工資數等，另一原因是可使復雜的查詢(xún)易于理解和使用。這個(gè)視圖就像一個(gè)“窗口”，從中只能看到你想看的數據列。這意味著(zhù)你可以在這個(gè)視圖上使用SELECT *，而你看到的將是你在視圖定義里給出的那些數據列。
1、語(yǔ)法
創(chuàng )建：CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(列名列表)]AS 查詢(xún)語(yǔ)句[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、實(shí)例
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ā)器
與表事件相關(guān)的特殊的存儲過(guò)程，它的執行不是由程序調用，也不是手工啟動(dòng)，而是由事件來(lái)觸發(fā)，比如當對一個(gè)表進(jìn)行操作（insert，delete， update）時(shí)就會(huì )激活它執行。
觸發(fā)器經(jīng)常用于加強數據的完整性約束和業(yè)務(wù)規則等。
1、語(yǔ)法
創(chuàng )建：CREATE TRIGGER <觸發(fā)器名稱(chēng)> --觸發(fā)器必須有名字，最多64個(gè)字符，可能后面會(huì )附有分隔符.它和MySQL中其他對象的命名方式基本相象.{ BEFORE | AFTER } --觸發(fā)器有執行的時(shí)間設置：可以設置為事件發(fā)生前或后。{ INSERT | UPDATE | DELETE } --同樣也能設定觸發(fā)的事件：它們可以在執行insert、update或delete的過(guò)程中觸發(fā)。ON <表名稱(chēng)> --觸發(fā)器是屬于某一個(gè)表的:當在這個(gè)表上執行插入、 更新或刪除操作的時(shí)候就導致觸發(fā)器的激活. 我們不能給同一張表的同一個(gè)事件安排兩個(gè)觸發(fā)器。FOR EACH ROW --觸發(fā)器的執行間隔：FOR EACH ROW子句通知觸發(fā)器 每隔一行執行一次動(dòng)作，而不是對整個(gè)表執行一次。<觸發(fā)器SQL語(yǔ)句> --觸發(fā)器包含所要觸發(fā)的SQL語(yǔ)句：這里的語(yǔ)句可以是任何合法的語(yǔ)句， 包括復合語(yǔ)句，但是這里的語(yǔ)句受的限制和函數的一樣。刪除：DROP TRIGGER 方案名稱(chēng).觸發(fā)器名稱(chēng)2、實(shí)例
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
