kill pid
show processlist;
show global variables like "%connecti%
set names utf8;
explain ...
1、select
select * from table;
select count(*) from table;
select fielda,fieldb from table;
select filed as a from table;
select * from table limit 1 \G;
select * from table limit 1 50;
select top 10 * from tablename order by id desc;
select distinct fieldsa,fieldsb from table;
select * from table order by field desc|asc;
select field,count(*) icount from table group by field having icount>n;
select concat("fielda:", fielda," fieldb:", fieldb) from table;
select * from table order by length(field) desc limit 1;
select * from table where filed=’’ and field is not null and filed >n or filed between “2008-10-10 00:00:00” and “2018-10-10 00:00:00”;
select * from table where to_days(now())- to_days("2012-07-30 00:00:00")<=30;
select * from tablea inter join tableb;
select t1.name, t2.salary from employee as t1, info as t2 where t1.name = t2.name;
select sum(if(id<3, id,0)) as value12,sum(if(id=3, id,0)) as value3,sum(if(id>=4, id,0)) as value45 from client_shard ;
select sum(case when status='20' then 1 else 0 end) as waitconfirmcount,sum(case when status='0' then 1 else 0 end) as bookokcount,sum(case when status='12' then 1 else 0 end) as cancelokcount,sum(case when status='1' then 1 else 0 end) as payokcount,sum(case when status='40' then 1 else 0 end) as applychangecount,sum(case when status='30' then 1 else 0 end) as applyrefundmentcount,sum(case when status='31' then 1 else 0 end) as waitrefundmentcount, sum(case when errortype=2 and status = '20' then 1 else 0 end) as pataerrorcount from order_info where category=0;
select rand(now()),md5('a'),right(“xxxxxx”,1,2) , subdate(curdate(),7 );
select max(length(field)) ,sum(field) from table;
select from_unixtime(filed) ,unix_timestamp(filedb) , current_timestamp() from table;
select a,b,c from a where a in (select d from b ) ;
select a,b,c from a where a in (1,2,3);
select * from table where field like ‘%xxx%’;
select * from table where field regexp ‘xxx’;
2、insert
insert into `reloadserverlist` (`ip`,`relationurlid`) values ('l-ttstw9.beta.cn1.qunar.com:8080',52), ('l-ttstw9.beta.cn1.qunar.com:8080',68);
insert into b(a, b, c) select d,e,f from b;
insert into tablename select * from temp;
insert into sms_list_backup (mobile, msg, send_time, client) select mobile , msg, send_time, client from sms_list where send_time < '2011-03-04' and status = 1 ;
insert into table (a,b,c) values (1,2,3) on duplicate key update c=c+1;
3、table
drop table if exists `pay_toplimit`;
create table `pay_toplimit` (... ) engine=myisam auto_increment=139 default charset=utf8;
create table tablea as select * from tableb;
create table like tableb;
lock tables `pay_toplimit` write;
unlock tables;
truncate `roundtrip_policy`;
drop table tablea;
delete from table1 where ..
delete from b where exists( select * from a where a.id=b.id);
update table1 set field1=value1,field2=replace(filed2,”xxx”,”yyy”) where ..
4、coclumn view index
alter table tabname add column col type
alter table tabname add primary key(col)
alter table tabname drop primary key(col)
create [unique] index idxname on tabname(col...)
drop index idxname
create view viewname as select statement
create view hotel.viewname as select seq, max(case name when 'hotelname' then value end) name, max(case name when 'hoteladdress' then value end) address, max(case name when 'gpoint' then value end) gpoint from hotel.hotel_props where seq like 'beijing_city%' group by seq;
drop view viewname
5、database
create database database-name
drop database dbname
use dbname
6、right
show grants for root@'localhost';
drop user 'book'@'192.168.%';
grant select, insert, update, delete on `conffilesdb`.* to 'bookfb'@'192.168.%' identified by 'xxx';
flush privileges
