数据类型
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:SSyear 1字节 YYYY年datetime 字节 YYYY-MM-DD HH:MM:SStimestamp 4字节 YYYYMMDD HHMMSSenum 65535个 枚举类型
运算符
+ - * / %取余= != < <= > >=between 1 and 10; 1到10蕴含两端in (1,2,3,4,5) ;not in (1,2,3,4,5)is null ; is not nulllike "%" 匹配多个 ; like "_" 匹配一个and or not
常见函数
随机数 rand()连贯字符串 concat('中国','打日本')转换小写 lcase('ABC') lower转换大写 upper ucase去除空格 trim(str)curdate()+0 返回20160916curdate() 2016-09-16curtime() 21:53:23now() 2019-07-10 21:52:23日期工夫unix_timestamp(now()) 返回工夫戳 1562767275from_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-15date( now()) 2019-07-10 提取日期year('2019-7-10') 返回2019 month('2016-04-28') 4day(now()) 11 返回天hour('2019-12-6 14:7:50') 14minute('2019-12-6 14:7:50') 7second('2019-12-6 14:7:50') 50last_day('2019-12-6 14:7:50') 2019-12-31 给定日期最初一天quarter('2016-04-28') 2 季度,1,2,3,4dayofweek('2019-7-10') 返回4 星期三 -1weekday('2019-7-10') 返回2 星期三+1dayofyear('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 on A.id=B.id; 左连贯select * from A right join B on A.id=B.id;右连贯select id from Table where id2 in(select id3 from Table2) 子查问select id from Table where find_in_set(type,"ssq,sd,pls") 查问分类select id 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 昨天where DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(FROM_UNIXTIME(createtime))近七天含明天where YEARWEEK(date_format(FROM_UNIXTIME(createtime),'%Y-%m-%d')) = YEARWEEK(now()) 本周where YEARWEEK(date_format(FROM_UNIXTIME(createtime),'%Y-%m-%d')) = YEARWEEK(now())-1 上周where DATE_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)) 上季度where YEAR(FROM_UNIXTIME(createtime))=YEAR(NOW()) 往年where YEAR(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 user AS 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; 上一条6select * from user where id>7 limit 1; 下一条