【2018-11-28】常用sql 

 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;

 

posted @ 2018-11-28 11:05  Int64  阅读(161)  评论(0)    收藏  举报