mysql速查手册 mysql

数据类型

tinyint 1字节 (-128,127) (0,128) 小整数类型 smallint 2 字节 (-32 768，32 767) (0，65 535) mediumint 3 字节 (-8 388 608，8 388 607) (0，16 777 215) int/integer 4 字节 bigint 8字节超大整数 float 4字节单精度浮点数 double 8字节双精度 decimal M+2 定点数 char 0-255字节定长字符串 varchar 0-65535 字节变长字符串 text 0-65 535字节长文本数据 mediumtext 0-16 777 215字节中等长度文本数据 longtext 0-4 294 967 295字节极大文本数据 date 3字节 YYYY-MM-DD日期 time 3字节 HH:MM:SS year 1字节 YYYY年 datetime 字节 YYYY-MM-DD HH:MM:SS timestamp 4字节 YYYYMMDD HHMMSS enum 65535个枚举类型

运算符

+ - * / %取余 = != < <= > >= between 1 and 10; 1到10包含两端 in (1,2,3,4,5) ; not in (1,2,3,4,5) is null ; is not null like "%" 匹配多个 ; like "_" 匹配一个 and or not

常见函数

随机数 rand() 连接字符串 concat('中国','打日本') 转换小写 lcase('ABC') lower 转换大写 upper ucase 去除空格 trim(str) curdate()+0返回20160916 curdate()2016-09-16 curtime() 21:53:23 now()2019-07-10 21:52:23日期时间 unix_timestamp(now())返回时间戳 1562767275 from_unixtime( 1539659520)返回 2018-10-16 11:12:00 from_unixtime(1515980716, '%Y-%m-%d %H:%i:%S')2018-01-15 09:45:16 格式化时间 date_format(now(), '%Y-%m-%d') 2018-01-15 date( now())2019-07-10 提取日期 year('2019-7-10')返回2019 month('2016-04-28')4 day(now()) 11 返回天 hour('2019-12-6 14:7:50')14 minute('2019-12-6 14:7:50') 7 second('2019-12-6 14:7:50')50 last_day('2019-12-6 14:7:50')2019-12-31 给定日期最后一天 quarter('2016-04-28')2 季度,1,2,3,4 dayofweek('2019-7-10')返回4星期三 -1 weekday('2019-7-10')返回2星期三+1 dayofyear('2019-7-10') 返回今天是191天 to_days('2019-12-6 14:7:50')737764计算日期 d 距离 0000 年 1 月 1 日的天数 from_days(733627) 2008-08-08 同上相反 day(last_day(now())) 返回本月天数 date_add('2019-12-6 14:7:50',INTERVAL 1 day) +1天 date_sub('2019-12-6 14:7:50',INTERVAL 1 day) -1天 timestamp('2008-08-08') 2008-08-08 00:00:00 版本version 聚合函数 avg(col) 平均值 count(*) 记录数 min(col) max(col)最小最大值 sum(col)求和

标准查询

set names utf8; 设置编码 select * from biao 基本查询 select distinct id from biao 结果字段不重复 select* from biao order by id desc ,time asc; 升序降序 select* from biao group by sex having; 分组筛选 select* from biao limit 4 返回4条 select* from biao 4,3返回3条,从第5条记录开始 select a,b,c from A inner join B on A.id = B.id; 内连接 select a,b,c from A,B where A.id=B.id; 内连接 select * from A left join B onA.id=B.id; 左连接 select * from A right join B onA.id=B.id; 右连接 select id from Table where id2 in(select id3 from Table2) 子查询 select id from Table wherefind_in_set(type,"ssq,sd,pls")查询分类 selectid as ID from A as a别名 select * from A union all select * from B 合并结果集 select * from A union select * from B 去重复 SELECT id,title FROM article WHERE id<$id ORDER BY id desc LIMIT 1 上一篇 SELECT id,title FROM article WHERE id>$id ORDER BY id ASC LIMIT 1 下一篇 select count(distinct openid) as total from TB 统计记录去重复

高级查询

select * from list where to_days(FROM_UNIXTIME(createtime))=to_days(now()) 今天 where to_days(now())-to_days(FROM_UNIXTIME(createtime))<1 今天 where to_days(now())-to_days(FROM_UNIXTIME(createtime))=1 昨天 whereDATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(FROM_UNIXTIME(createtime))近七天含今天 whereYEARWEEK(date_format(FROM_UNIXTIME(createtime),'%Y-%m-%d')) = YEARWEEK(now()) 本周 whereYEARWEEK(date_format(FROM_UNIXTIME(createtime),'%Y-%m-%d')) = YEARWEEK(now())-1 上周 whereDATE_SUB(CURDATE(), INTERVAL 30 DAY) < date(FROM_UNIXTIME(createtime))近30天含今天 where DATE_FORMAT(FROM_UNIXTIME(createtime), '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') 查询本月 where PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), DATE_FORMAT(FROM_UNIXTIME(createtime),'%Y%m')) =1 上月 where QUARTER(FROM_UNIXTIME(createtime))=QUARTER(NOW()) 本季度 where QUARTER(FROM_UNIXTIME(createtime))=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) 上季度 whereYEAR(FROM_UNIXTIME(createtime))=YEAR(NOW()) 今年 whereYEAR(FROM_UNIXTIME(createtime))=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR)) 去年SELECT @rank := @rank + 1 AS rank,t.* FROM (SELECT @rank := 0) r, user AS t ORDER BY t.score DESC; 相同分数按照id小的在前,排名不重复SELECT rank,score,id FROM ( SELECT USER .*, @c := IF ( @p = score, @c, @r ) AS rank, @p := score, @r := @r + 1 FROM USER, ( SELECT @p := NULL, @r := 1, @c := 0 ) r ORDER BY score DESC ) c; 相同排名重复后去掉后面UPDATE user INNER JOIN (SELECT @rank := @rank + 1 AS rank,t.id FROM (SELECT @rank := 0) r, user AS t ORDER BY t.score DESC) t2 ON t2.id=user.id SET user.rank=t2.rank更新表自身排名,更新前rank都是0,没有重复UPDATE user INNER JOIN (SELECT rank,id FROM ( SELECT user.*, @c := IF ( @p = score, @c, @r ) AS rank, @p := score, @r := @r + 1 FROM user, ( SELECT @p := NULL, @r := 1, @c := 0 ) r ORDER BY score DESC ) c) t2 ON t2.id=user.id SET user.ranking=t2.rank有重复同上UPDATE userSET rank= rank+1 WHERE id=5 自动加一SELECT * FROM user ORDER BY RAND() LIMIT 5; 随机数据性能低下1000以内SELECT * FROM userAS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM user)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 2; 连续的id高效率SELECT * FROM user WHERE id >= ((SELECT MAX(id) FROM user)-(SELECT MIN(id) FROM user)) * RAND() + (SELECT MIN(id) FROM user) limit 2; 随机id不连续select * from user where id<7 order by id desc limit 1; 上一条6 select * from user where id>7 limit 1; 下一条