数据库：sql基本命令介绍

存储过程

Intro

自定义的数据库API，一系列sql语句的集合（这两个概念非常重要）

查看数据库中的所有存储过程/具体信息查看:

1 2	show procedure status; show create procedure proc_name;

删除存储过程:

1	drop procedure if exists proc_name;

调用存储过程:

1	call proc_name();

存储过程中的变量:

declare a, b, c int default 5;（变量名，类型，默认值）
set a = 'xxx'进行复制操作
set a = (select id from user where username='zheng');
select id into a from user where username='zheng';

Example

注意，在begin…end之间可以执行多条命令，另外注意delimiter的格式

delimiter //
drop procedure if exists proc_radius;
create procedure proc_radius() 
begin 
update radius.radcheck set loginenable =FALSE, oldtime=now(), value=left(value, length(value)-6) 
where timestampdiff(second, oldtime, now())>=100 and loginenable=TRUE; 
end//
delimiter ;

事件机制

时间调度器event schduler和事件event的异同，请理清

查看是否开启了event schedule的方法

SHOW VARIABLES LIKE ‘event_scheduler';
SELECT @@event_scheduler;(on-开启，off-关闭，disable-禁用)
SHOW PROCESSLIST;
# 开启或者关闭event scheduler的方法（有用啊，尤其测试时）：
set GLOBAL event_scheduler = off;
set global event_scheduler = on;
set @@global.event_scheduler = 0;

Update

alter event event_name
on schedule every 60 second
do 
    call proc_name() ;

close/open/delete event

# 关闭
alter event event_name disable;

# 开启
alterr event event_name enable;

# 删除
drop event if exists event_name;

Show

1 2	select * from mysql.event; show create event countsum \G查询事件的创建信息

Call

create event
if not exists event_radius
on schedule every 60 second
do
call proc_radius();

Example

间隔事件内判断某一个表中所有数据的时间差是否大于60秒:

create event
if not exists event_radius
on schedule every at current_timestamp + interval 4 hour 
do
update radius.radcheck
set loginenable =FALSE, oldtime=now(), value=left(value, length(value)-6)
where timestampdiff(second, oldtime, now())>=60 and loginenable=TRUE;

函数机制

Example

获取随机字符串:

drop function if exists rand_string;
DELIMITER //
CREATE  FUNCTION rand_string(n int)  RETURNS varchar(255) 
    BEGIN 
      DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 
      DECLARE return_str varchar(255) DEFAULT ''; 

      DECLARE i INT DEFAULT 0; 

      WHILE i < n DO 
        SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1)); 
        SET i = i +1; 
      END WHILE; 

      RETURN return_str; 
   END//
DELIMITER ;

调用时：

1	select rand_string(6);

如果某一个值长度不为12:

1	update radcheck set value=rand_string(12) where username='zheng' and loginenable=FALSE and length(value) !=12;

布尔类型

boolean（TRUE，FALSE）两种求值

通配符

like

字符%: 表示任意多个字符，匹配任意长度
包含: like ‘%str%’ and like ‘%ptr%’
不包含: not like ‘%str%’

运行状态

线程或者任务

命令：show processlist;

说明：每一个用户只能看到自己所属的运行线程，除非超级用户

输出说明: id-标识，command（sleep-休眠,query-查询,connect-连接), time-该命令该状态持续的时间，state-语句执行中的一个状态，info-sql语句

state

mysql运行命令过程中会有多个状态，详细的显示了命令执行过程或者阶段、碰到的问题等。

checking table: 检查数据库
closing table: 将table修改数据写入到DB，同时关闭table，该操作是一个快速操作，否则磁盘满或者其他问题
copying to tmp table on disk: 临时结果集过大，将临时表数据从内容拷贝到磁盘
creating tmp table: 创建临时表以存放查询结果
deleting from main table: 多表删除，刚刚删除第一个表
deleting from reference table: 正在删除其他表
flushing table: 等待其他线程关闭数据表
waiting for table: 表结构已经被更改，需要重新获取table结构
connect out: 从服务器正在连接主服务器
killed: 正在被杀死
locked: 被其他查询锁住
system lock: 正在等待一个外部锁
upgrading lock:
User lock: 等待GET_LOCK()
sending data: 正在处理select查询记录，并且正在将结果发送到客户端
sorting for group: 正在为GROUP BY排序
sorting for order
Opening tables: 除非被其他因素干扰（alter, locked），否则打开速度很快
Reopen table:
Sleeping: 正在等待客户端发送新的请求，一旦你使用任何客户端连接时都会有该线程任务

嵌套查询

查询两个表中某一个字段的值相同的行信息

select * from A where exists(select * from B where A.a = B.a);

查询A中某一个字段不等于B的所有行信息：

select distinct modulename from ossweb_svrinfo where not exists(
    select * from ossweb_moduleinfo
    where ossweb_moduleinfo.modulename = ossweb_svrinfo.modulename
) order by modulename;