数据类型

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 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; 上一条 6
select * from user where id>7 limit 1;  下一条