mysql语句2

结果排序

select * from tbbook order by if(bprice is null, 1, 0), price

select * from tbbook order by field(bname, 'McConell', 'Richard Stallman', 'Linux Trovalds'); field(text, pattern1, pattern2, ...)返回数字与哪一个pattern相同

select * from tbweeks order by cast(weekday as char)

摘要

…………………………

count对null不计数，select count(if(dayofweek(birthday) in (1, 7),1, null) as ... from ... 

count(col)对null不计数，count(*)对null计数

set @tmp = (select ... ) select ... where field = @tmp

select count(fone) from tbname group by fone, ftwo

select bname, bpubtime , max(price) from tbbook group by bname, bpubtime不确保准确

select count(*), bname from tbbok group by bname having count(*) > 3, 已确定群集时可以使用having

select bname, bprice from tbbook order by bname, bprice having count(*) = 1

... with rollup

show columns

show create table tbname

elect * from information_schema.columns where table_schema = 'test' and table_name = 'tbbook' and column_name = 'bid'

show columns from tbbook

mysqldump --no-date dbname tbname > tbname.txt

mysql dbname, ~/.my.cnf仪配置好

select version(), select database(), select user(), select current_user(), select global status, select variables like .. 

select 1 - (Key_read / Key_read_requests)，接近1说明磁盘读取缓存命中率很高

select @@wait_timeout

show engines

load data local infile 'data.txt' into table tbname

mysqlimport --local dbname tbname.txt

>select * from tbpwd into outfile pwd.txt fields terminated by ':' enclosed by = '"' lines terminated by '\r\n'

$ mysql -e "select uid, uname from tbuser" --skip-column-names dbname | tr "\t" "#" > output_file
$ mysql -e "select uid, uname from tbuser" --skip-column-names dbname | sed -e "s/ TAB /#/g" > output_file
$ mysqldump --tab=/tmp --no-create-info --fields-terminated-by="," --fields-enclosed-by="\"" --lines-terminated-by="\r\n" dbname
tbname
grant file on *.* to username@localhost
mysqldump dbname > dbname.txt，导出sql insert语句，以便日后恢复数据--no-create-table--no-data--add-drop-table--no-create-db导入 mysql dbname < dbname.txt
select column_type from information_schema.columns where table_schema =  'sdb' and table_name = 'tbuser' and column_name = 'uid';
auto_increment列，使用null insert，会自动加一并插入
select last_insert_id()
alter table tbuser auto_increment = 1，从当前最大值加一作为下一个插入值


select uid, bname, ctime from tbuser inner join tbbook inner join tbcmt on tbuser.uid = tbbook.buid and tbbook.bid = tbcmt.cbid where uid = 3
inner join tbname，连接表，on选择链接媒介，where限制具体条件
stddev_samp(), var_samp(), max()
select repeat('*', @cnt)

insert ignore into tbname ... 重复时丢弃数据

insert ... on duplicate key update cnt = cnt + 1

replace into tbname ... 重复时取代旧行

select count(*) - count(distinct firstname, lastname) as duplicate_name ...

不是一切事务都可以取消，例如drop database；
start transaction
...
commit


dilimiter $$
create function avg_mail_size(in user varchar(80), out agvsize float)
returns float reads sql data
begin
declare rate decimal(10, 2);
if user is null then
return (select avg(size) from mail);
else
return (select avg(size) from mail where srcuser = user);
endif;
select ifnull(avg(sum(size)), 0) into avgsize from mail;
end;
$$
delimiter ;


create trigger mytri_beins before insert on tbname
for each row 
begin
set new.colname = ...
end


create event event_name on schedule every 5 minutes
do
...
show variables like 'event_scheduler'
set global event_scheduler = 1全局开启，宕机后仍开启，vim  my.cnf [mysqld] event_scheduler = 1
drop event event_name
alter event event_name disable/enable
dt < now() - interval 2 day

mysql日期关注点应是mysql数据库格式与用户期望格式的转换
str_to_date(str, %c)
data_format(field, %c)
select @@global.time_zone, @@session.time_zone set session time_zone = '+04:00'
select curdate(), curtime(), now()
select current_date(), current_time(), current_timestamp()
select utc_date(), utc_time(), utc_timestamp()
timestamp类型自动更新，某一列数据改变时也自动更新，如果不希望update时改变为当前时间，创建表时，ts timestamp default current_timestamp, 1970--2037


year()
month()
hour()
minute()
second()
dayofyear()
dayofmonth()
dayofweek()
weekday()
monthname()
dayname()
extract(hour from ts)
日期、时间变量被当做字符串处理
maketime(vhour, vminute, vsecond)
lpad(hour(ts), 2, '0')


time_to_sec(vtime) sec_to_time(vsec)
to_days(vdate) from_days(vdays)
unix_timestamp(vts) from_unixtime(vut)
floor(vfloat)


datediff(vdate1, vdate2) v1 - v2
timediff(vtime1, vtime2)
timestampdiff(unit, vts1, vts2) unit:frac_second, second, minute, hour, day, month, quarte, year
addtime(vts1, vts2)
timestamp(vdate, vtime)
date_add(vdate, interval cnt unit)
date_sub(vdate, interval cnt unit)
select now(), data_add(now, interval '3 5' day_hour)
timestampadd(unit, cnt, ts) = date_add(ts, interval cnt unit)
mod(val, 1024) = val % 1024
timestamp(unit, vts1, vts2)
convert_tz(ts, vtz1, vtz2)
last_day(vdate) 当月最后一天