Mysql Storage Program

#1存储过程
Create Procedure greeting ()
BEGIN
# username = + for + +-hostname + 1 for ' @ '
DECLARE user CHAR (CHARACTER) SET UTF8;
SET user = (SELECT current_user ());
IF inster (user, ' @ ') > 0 Then
SET user = Substring_index (user, ' @ ', 1);
END IF;
IF user = "Then
SET = ' earthling ';
END IF;
SELECT CONCAT (' Greetings, ', User, '! ') As Greetig;
END;

#时间存储函数
Delimiter $$
CREATE PROCEDURE Show_time3 ()
BEGIN
SELECT ' Local time is: ', current_timestamp;
SELECT ' UTC time is: ', utc_timestamp;
END $$
DELIMITER;

Delimiter EOF
CREATE PROCEDURE Show_time3 ()
SELECT ' Local time is: ', current_timestamp;
SELECT ' UTC time is: ', utc_timestamp;
END EOF
DELIMITER;

#2存储函数

DELIMITER $
CREATE FUNCTION Show_id_ord (id INT)
RETURNS INT
READS SQL DATA
BEGIN
RETURN (SELECT count (*) from Test where id = ID)
end$
DELIMITER;

Delimiter $
CREATE PROCEDURE update_test_test_id (p_id int, p_date int)
BEGIN
Update Test Set test_id = p_date where id = p_id;
end$
delimiter;

DELIMITER $
CREATE PROCEDURE select_test_id (out p_id int)
BEGIN
SELECT COUNT (*) from Test where id = p_id to p_id;
END $
DELIMITER;

DROP TABLE IF EXISTS ' user_info ';
CREATE TABLE ' User_info ' (
' id ' int (ten) unsigned not NULL auto_increment COMMENT ' user ID ',
' user_name ' varchar (+) not NULL DEFAULT ' COMMENT ' user name ',
' Gender ' tinyint (1) Not NULL DEFAULT ' 0 ' COMMENT ' user sex; 1: Male, 2: female ',
' Add_time ' int (ten) unsigned not NULL DEFAULT ' 0 ' COMMENT ' User registration time ',
' Update_timne ' timestamp not NULL the DEFAULT current_timestamp on update current_timestamp COMMENT ' user last update Time ',
PRIMARY KEY (' id ') USING BTREE,
KEY ' user_name ' (' user_name '),
KEY ' gender ' (' gender '),
KEY ' Add_time ' (' Add_time ')
) Engine=myisam auto_increment=10 DEFAULT charset=utf8 comment= ' User Information table '

#3触发器

Delimiter $
CREATE TRIGGER ai_t after INSERT on User_info
For each ROW BEGIN
Inster into Test (' test_id ') VALUES ((SELECT test_id from test_id ORDER BY id DESC limit 1) +1)
END $
DELIMITER;

Delimiter $
CREATE TRIGGER bi_t before INSERT on t
For each ROW BEGIN
SET New.dt = Current_timestamp;
IF New.parent <0 Then
SET new.parent = 0;
ELSEIF New.parent >100 Then
SET new.parent = 100;
END IF;
end$
DELIMITER;

#4创建事件

CREATE EVENT Id_defined_min
On SCHEDULE every 0.02 HOUR
Do
DELETE from test where ID <9;

Mysql Storage Program