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

时间:2023-01-31 23:30:49

一.定时器+存储过程

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