很開心我今天又開設(shè)了一個新的專題癞埠，那就是數(shù)據(jù)庫，俗話說不想當(dāng)dba的程序員不是一個好前端聋呢。苗踪。。我這里不是mysql入門講堂坝冕，講的都是有一定基礎(chǔ)的東西徒探。
1 存儲過程
話不多說，咱們舉個例子簡單了解下喂窟。
這是一張學(xué)生表测暗，含有3個數(shù)據(jù)。
DROP PROCEDURE if EXISTS Proc;
DELIMITER //
CREATE PROCEDURE Proc()
BEGIN
SELECT * FROM student;
END//
DELIMITER ;
CALL Proc();
我們通過CREATE PROCEDURE創(chuàng)建了一個存儲過程Proc磨澡，語句是查詢student表內(nèi)容碗啄，調(diào)用通過call，返回內(nèi)容就是表的數(shù)據(jù)稳摄。
DROP PROCEDURE if EXISTS Proc;
DELIMITER //
CREATE PROCEDURE Proc(IN id INT)
BEGIN
SELECT * FROM student WHERE age > id;
END//
DELIMITER ;
CALL Proc(23);
我們將這個存儲過程改為了查詢年齡大雨23歲的稚字。
其實到這里大概也知道了，存儲過程到底是干嘛的，其實就是編寫一個函數(shù)讓我們可以更輕松的拿取我們想要的數(shù)據(jù)胆描。
DROP PROCEDURE if EXISTS Proc;
DELIMITER //
CREATE PROCEDURE Proc(IN id INT ,OUT count INT)
BEGIN
SELECT count(*) into count FROM student WHERE age > id;
END//
DELIMITER ;
CALL Proc(21,@count);
select @count;
這個存儲過程里面???個參數(shù)瘫想，一個輸出，一個輸入昌讲，注意輸出用@国夜，count最后代表的是大于21的人個數(shù)。
我們現(xiàn)在有個需求就是往這個student表里添加10個學(xué)生短绸，我們這次是使用存儲過程來做的车吹。
DELIMITER $$
DROP PROCEDURE IF EXISTS `addStudent` $$
CREATE PROCEDURE addStudent( IN studentCount INT )
BEGIN
DECLARE i INT DEFAULT 0; -- 計數(shù)器
DECLARE returnMsg VARCHAR(50) DEFAULT ''; -- 返回值信息
DECLARE rowCount int DEFAULT 0; -- 操作sql的時候影響行數(shù)
outer_label:BEGIN
START TRANSACTION;
WHILE i < studentCount DO
SET i=i+1;
INSERT INTO `student` (`name`,`age`) VALUES ('****',i+1);
SELECT row_count() INTO rowCount;
IF rowCount<=0 THEN
LEAVE outer_label;
END IF;
END WHILE;
END outer_label; -- 只要是在outer_label代碼塊內(nèi) 任意位置 Leave outer_label,那么Leave后的代碼將不再執(zhí)行
IF i=studentCount THEN
COMMIT;
SET returnMsg = i;
ELSE
ROLLBACK;
SET returnMsg = 'error';
END IF;
SELECT returnMsg;
END $$
DELIMITER ;
其實這就是在mysql里面寫了一回js代碼，注意while和if的用法即可醋闭。通過addStudent(10)插入10條數(shù)據(jù)窄驹。
上面的例子比較綜合，咱們以一個簡單例子結(jié)尾证逻。
DELIMITER //
CREATE PROCEDURE proc1 (IN parameter1 INTEGER)
BEGIN
DECLARE variable1 CHAR(10);
IF parameter1 = 17 THEN
SET variable1 = 'birds';
ELSE
SET variable1 = 'beasts';
END IF;
INSERT INTO table1 VALUES (variable1);
END //
DELIMITER ;
在存儲過程間傳遞全局范圍的用戶變量
1. mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';
2. mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);
3. mysql> CALL p1( );
4. mysql> CALL p2( );
5. +-----------------------------------------------+
6. | CONCAT('Last procedure was ',@last_proc |
7. +-----------------------------------------------+
8. | Last procedure was p1 |
9. +-----------------------------------------------+
查看當(dāng)前數(shù)據(jù)庫的存儲過程：
show procedure status;
來看這段代碼
DROP PROCEDURE IF EXISTS proc3;
DELIMITER //
CREATE PROCEDURE proc3()
begin
declare x1 varchar(5) default 'outer';
begin
declare x1 varchar(5) default 'inner';
select x1 as qwe;
end;
select x1 as qwe;
end //
DELIMITER ;
CALL proc3();
此時顯示的是x1=inner...
一個簡單的流程控制的存儲過程
drop procedure if exists proc4;
DELIMITER //
CREATE PROCEDURE proc4 (in parameter INT)
begin
declare var int;
declare asd int;
set var=parameter+1;
case var
when 0 then
set asd = 11;
select asd as qwe;
when 1 then
set asd = 12;
select asd as qwe;
else
set asd = 13;
select asd as qwe;
end case;
end //
DELIMITER ;
call proc4(0);
存儲過程的概念其實很簡單乐埠，但是想要寫出很好的存儲過程需要一定的coding skill，大家可以慢慢學(xué)習(xí)瑟曲。饮戳。
2 觸發(fā)器
觸發(fā)器是一個特殊的存儲過程，不同的是存儲過程要用CALL來調(diào)用洞拨，而觸發(fā)器不需要使用CALL，也不需要手工啟動负拟，只要當(dāng)一個預(yù)定義的事件發(fā)生的時候烦衣，就會被MYSQL自動調(diào)用。
drop trigger ins_sum
SET @qqq=0;
CREATE TABLE account2(acct_num INT ,amount INT);
CREATE TRIGGER ins_sum BEFORE INSERT ON account2
FOR EACH ROW SET @qqq=@qqq+new.amount;
INSERT INTO account2 VALUES(1,3),(2,5);
SELECT @qqq as qwe;
這個觸發(fā)器的意思是說每次往account2里面插入數(shù)據(jù)時掩浙，更改@qqq的值花吟，注意new是值的插入的那一行。
觸發(fā)器中new和old的作用
- 針對update操作厨姚，new表示的是更新后的值衅澈，old表示的是原來的數(shù)據(jù)。
- 針對insert操作谬墙，new表示的是插入的值今布。
- 針對delete操作，old表示的是刪除后的值拭抬。
本例子是insert部默，操作是before。
關(guān)于mysql變量定義可以看這篇文章.
創(chuàng)建有多個執(zhí)行語句的觸發(fā)器造虎，語法如下：
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
|
一次插入操作導(dǎo)致3個表發(fā)生變化傅蹂。。。
我們來考慮一個更為實際的問題份蝴，有一張表記錄著我進(jìn)貨水果詳細(xì)犁功，還有一張表是對我進(jìn)貨水果種類的統(tǒng)計，此時我進(jìn)貨了10個apple回來婚夫，那么這2張表該如何變化波桩。
CREATE TRIGGER FRUIT BEFORE INSERT ON FRUIT_DETAIL
FOR EACH ROW BEGIN
UPDATE FRUIT_STATISTIC SET COUNT = COUNT + NEW.IN_NUM
WHERE FRUIT_NAME = NEW.NAME
END
上面這個就表示了這個過程。
下面這個例子表示了一個刪除的觸發(fā)器
-- 刪除觸發(fā)器
mysql> delimiter $$
mysql> create trigger tab1_delete_after after delete
-> on tab1 for each rows
-> BEGIN
-> delete from tab2 where tab2_id = old.tab1_id;
-> END
-> $$
Query OK, 0 rows affected (0.01 sec)
delete from tab1 where tab1_id = 12
mysql> delimiter ;
注意這里我們用的是old请敦，刪除時就使用old镐躲。
下面這個例子表示了一個更改的觸發(fā)器
-- 更新學(xué)生表的同時也更新記錄表
delimiter $$
mysql> create trigger student1_update_after after update
-> on student1 for each row
-> BEGIN
-> if new.student_id != old.student_id then
-> update update_student1 set student_id = new.student_id, update_date = now()
-> where student_id = old.student_id;
-> end if;
-> END
-> $$
delimiter ;
update student1 set student_id = 23 where name = 小明
我們把名為小明的學(xué)生學(xué)號給改了。侍筛。萤皂。那么這時候會在學(xué)生記錄表也作出變更。
今天的講解就到這里匣椰，相信大家覺得這些對比后臺代碼還是很簡單的裆熙。
