mysql 语句记录（定时器,存储过程,索引,触发器,权限设置）

一.定时器+存储过程

1.  是否存在存储过程

/*查看存储过程*/
show PROCEDURE STATUS;
/*删除存储过程*/
DROP PROCEDURE pro_updateGameState;
/*查看存储过程创建代码*/
show CREATE PROCEDURE pro_updateGameState;

2.  创建存储过程

CREATE PROCEDURE pro_updateGameState()
BEGIN

    UPDATE t_game SET state=1 where state=0 AND 
    DATE_ADD(start_time,INTERVAL 
    (SELECT c_value from t_config where c_key='end_time')    SECOND)>NOW();

END;

3.  调用存储过程

  CALL pro_updateGameState();

4. 查看event是否开启

show variables like '%sche%';

5. 开启event_scheduler

set global event_scheduler =1;  

6.  创建定时器操作，每隔30s运行一次

/*是否一定存在定时器*/
show EVENTS; 
/*删除定时器*/
drop  EVENT event_updateGameState  ;
/*创建定时器*/
create event if not exists event_updateGameState  
on schedule every 30 second  
on completion preserve  
do call pro_updateGameState();  

7.  关闭定时器任务 disable

alter event event_updateGameState ON COMPLETION PRESERVE DISABLE; 

8.  开始定时器任务 enable

alter event event_updateGameState ON COMPLETION PRESERVE ENABLE;

二.带输入输出的存储过程

1.删除存储过程

drop procedure pro_finIndex;

2.创建存储过程

 CREATE PROCEDURE pro_finIndex (IN userId INT,OUT count INT)
BEGIN
    /* 定义一个整形变量 */
    DECLARE v1 INT;
    /* 将输入参数的值赋给变量 */
    set v1=userId;

      SELECT count(*) INTO  count  
      FROM t_message
        WHERE
        user_id = v1
    AND
        time > (
        SELECT message_time FROM t_user_time
        WHERE
        user_id = v1);
END

3.插入参数

CALL pro_finIndex(258,@count);

4.查询

select @count;

三.触发器

 1. 删除触发器

DROP TRIGGER IF EXISTS t_afterinsert_on_t_user;

 2. 创建触发器

create trigger  t_afterinsert_on_t_user 
AFTER INSERT on t_user 
for each row
BEGIN 
INSERT into t_user_time (user_id) VALUES (new.user_id);
INSERT INTO t_wallet(user_id) VALUES(new.user_id);
END

 3.查询所有触发器

SELECT * FROM information_schema.`TRIGGERS`;

四.索引

1.创建普通索引

create index message_uid_type on t_message (type) ;

EXPLAIN select * from t_message where user_id=315;

select * from t_message where user_id=315;

EXPLAIN select * from t_message where type=3;

select * from t_message where type=3;

select * from t_message where type=3 AND user_id=315;

2.删除索引

drop index message_uid_type on t_message ;

3.查询索引 

SHOW INDEX FROM table_name;

五.数据库权限

/*创建一个用户*/
CREATE USER 'root'@'%' identified BY '123456.';
/*查询数据库所有管理员*/
select host,user,password from user ;
/*查询用户face 的权限*/
show grants for face;

show grants;

/* 创建一个超级用户*/
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

/*移除face 的一切权限*/
revoke all on *.* from face;
/*赋予 某个数据库的权限*/
grant  all on facebook.* to   face@'%'; 

/**刷新权限*/
FLUSH PRIVILEGES;
/**查看进程*/
show processlist

六.查询不相同的数据模板

SELECT *  
    FROM user AS A  
    WHERE (invite_code IN (SELECT invite_code FROM user AS B WHERE A.user_id