表user:
1.(1)全部查询:select * from user
(2)模糊查询:select * from user where time like '%2019-10-10%'
(3)条件查询:select * from user where id=1
(4)非空查询:select * from user where name is not null and name!=''
(5)去重查询:select distinct name from user
2.添加:insert into user(name,sex,time) values('11',1,now())
3.修改:update user set name='12' where id=11
4.删除:delete from user where id=11
5.别名:select id as userid from user(或select id userid from user)
6.(1)分组查性别数量:select sex,count(sex) countsex from user group by sex
(2)分组条件查询性别数量:select sex,count(sex) countsex from user group by sex HAVING count(sex)<6
(3)分组查性别数量及所有数据的次数(coalesce取代 NUll 的名称,with ROLLUP在分组统计数据基础上再进行相同的统计):select coalesce(sex, '总数'),count(sex) from user group by sex with ROLLUP
7.查询今天的数据:select * from user where to_days(time) = to_days(now());
8.查询30天之内的数据:select * from user where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(time)
9.查询本月的数据:select * from user where DATE_FORMAT( time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
10.id求和:select sum(id) from user
11.id求平均值:select AVG(id) from user
12.截取字符串:
(1)按关键字截取字符串(SUBSTRING_INDEX(字段,关键字,关键字出现的次数)):select SUBSTRING_INDEX(time,'-',2) from user
(2)从左开始截取字符串:left(被截取字段,截取长度):select LEFT(time,4) from user
(3)从右开始截取字符串:right(被截取字段,截取长度):select right(time,4) from user
(4)从第几位开始截取字符串:substring(被截取字段,从第几位开始截取)substring(被截取字段,从第几位开始截取,截取长度)(如果位数是负数 如-5 则是从后倒数位数):select substring(time,3) from user ; select substring(time,3,4) from user ; select substring(time,-3,4) from user
13.排序查询(desc倒序,asc升序(只有一个排序条件asc可以省略)):select * from user order by id desc
14.正则表达式:
(1)查询time字段包含2019-10的所有数据:select * from user where time regexp '2019-10'
(2)查询time字段以2019-10开头的所有数据:select * from user where time regexp '^2019-10'
(3)查询time字段以7结尾的所有数据:select * from user where time regexp '7$'
(4)查询time字段以2019-10开头或以1结尾的所有数据:select * from user where time regexp '^2019-10|1$'
(5)匹配所包含的任意一个字符(查询name字段中包含1或包含4的所有数据):select * from user where name regexp '[14]'
(6)匹配确定的 n 次(查询time字段中包含连续2个1的所有数据):select * from user where time regexp '1{2}'
(7)最少匹配 n 次且最多匹配 m (查询time字段中包含连续最少2个1最多3个1的所有数据):select * from user where time regexp '1{2,3}'
15.
(1)查询前3条数据:select * from user limit 3
(2)查询从第3条记录开始的5条记录:select * from user limit 3,5
16.查询以逗号分隔的name字段中包含1的数据:select * from user where FIND_IN_SET('1',name)