一.定时器+存储过程
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 'faceOnly'@'%' identified BY 'Sumudada9508.';
/*查询数据库所有管理员*/
select host,user,password from user ;
/*查询用户face 的权限*/
show grants for face;
show grants;
/* 创建一个超级用户*/
GRANT ALL PRIVILEGES ON *.* TO xialin@'%' IDENTIFIED BY '757528.muxia' 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