1 -- 字符串拼接
2 SET @this_year=YEAR(CURDATE());
3 SET @str=CONCAT('select time from tb_history_',@this_year)
4 PREPARE stmt from @str;
5 EXECUTE stmt;
6 DEALLOCATE stmt;
7
8 -- 视图
9 DROP VIEW IF EXISTS ai;
10 CREATE VIEW ai
11 SELECT * FROM tb_history;
12
13 -- 存储过程（无参）
14 DROP PROCEDURE IF EXISTS test;
15 CREATE PROCEDURE test()
16 BEGIN
17 SET @a=1;
18 SELECT @a;
19 end
20 CALL test;
21 -- 存储过程（输入参数）
22 set @a=0;
23 DROP PROCEDURE IF EXISTS test_in;
24 CREATE PROCEDURE test_in(in a INT)
25 BEGIN
26 SELECT a;
27 SET a=2;
28 SELECT a;
29 END;
30 CALL test_in(@a);
31 SELECT @a;
32 -- 存储过程（输出参数）
33 set @a=0;
34 DROP PROCEDURE IF EXISTS test_out;
35 CREATE PROCEDURE test_out(out a INT)
36 BEGIN
37 SELECT a;
38 SET a=2;
39 SELECT a;
40 END;
41 CALL test_out(@a);
42 SELECT @a;
43 -- 存储过程（输入输出参数）
44 SET @a=0;
45 DROP PROCEDURE IF EXISTS test_inout;
46 CREATE PROCEDURE test_inout(inout a INT)
47 BEGIN
48 SELECT a;
49 SET a=2;
50 SELECT a;
51 END;
52 CALL test_inout(@a);
53 SELECT @a;
54
55 -- 查看开启定时器。 注意:服务器重启，定时器会关闭。
56 show VARIABLES LIKE 'event_scheduler';
57 set GLOBAL event_scheduler=ON;
58 -- 定时器
59 CREATE EVENT test_second
60 ON SCHEDULE EVERY 3 SECOND STARTS '2018-01-01 00:00:01'
61 DO CALL test_second();
62 DROP EVENT IF EXISTS test_second;
