SELECT
查询单个列
#查询Author表name列的值select name from Author;
查询多个列
#查询Author表id,name两列的值select id,name from Author;
查询所有列
#查询Author表所有列的信息select * from Author;
查询不同的行（distinct去重）
#查询Article表所有作者select distinct author from Article;
限制查询结果（limit分页）
#查询Article表前3行的文章类型select type from Article limit 1,3;select type from Article limit 3;
对单个查询列进行排序（order by）
#Article表按aid从低到高查询作者姓名和文章类别select aid,author,type from Article order by aid;
对多个查询列进行排序（order by a,b:a排序的基础上，b再排序)：
#Article表按aid和粉丝数从低到高查询作者姓名和文章类别select aid,author,type,fans from Article order by aid,fans;
对单个列降序排列（desc降序，从高到低）
#按粉丝数降序排列Article信息select * from Article order by fans desc;
对多个列降序排列
#按fans降序排列Article信息，再对articles排序select * from Article order by fans desc,articles;#以上仅对fans降序，articles仍为升序，articles降序需加desc
select..from..order by ..desc limit..;
#根据文章数降序排列查询前三条Article信息select * from Article order by articles desc limit 3;
根据字符集进行排序
#如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BYselect * from Article order by type; #如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序select * from Article order by convert(type using gbk);
对条件进行分组排序
#分别统计coco和vivi的文章数select au_id,count(*) as '数目' from Article where author='coco' or author='vivi' group by au_id;
分组排序复合查询（having）
#根据aid统计文章总数大于5的select au_id,count(*) as '数目' from Article group by au_id having count(*)>5;
with rollup实现在分组统计数据基础上再进行统计
#将Article按author进行分组，再统计每个人的总文章数select author,sum(articles) as '总文章数' from Article group by author with rollup;
使用 coalesce 来设置一个可以取代 NUll 的名称
#coalesce 语法：select coalesce(a,b,c);select coalesce(author,'昵称') ,sum(articles) as '总文章数' from Article group by author with rollup;
where单个条件查询
#查询Python文章类型的QQ交流群号信息select type,qq_group from Article where type='Python';
#查询粉丝数大于400的Article信息,按降序排列select * from Article where fans>400 order by fans desc;
#查询粉丝数不是450的Article信息select * from Article where fans!=450;select * from Article where fans not in(450);
between ..and..(在什么之间）
#查询粉丝数在400到450之间的Article信息,按文章数降序排列select * from Article where fans between 400 and 450 order by articles desc;
is null(查询某个列的值为空)
#查询fans为空的Article信息（没有则返回空表）select * from Article where fans is null;
and操作符查询多个条件，每多一个条件就多加一个and
#查询粉丝数为450且文章类型为Python的Article信息select * from Article where fans=450 and type='Python';
or操作符 查询匹配任一条件的行
#查询粉丝数为300或400的Article信息select * from Article where fans=300 or fans=400;
and ..or..高级过滤（and计算次序更高，需要用圆括号明确分组操作符）
#查询文章数在10以上并且粉丝数为300或400的Article信息select * from Article where (fans=300 or fans =400 )and articles>10;
in操作符（值由逗号分隔，括在圆括号中）
#查询粉丝数在400和500的Article信息，降序排列select * from Article where fans in(400,500) order by fans desc;
not操作符与in操作符结合
#查询粉丝数不是300和400的Article信息，按文章数降序排列select * from Article where fans not in(300,400) order by articles desc;
like操作符与百分号（%）通配符
#查询QQ群以6开头的Article信息select * from Article where qq_group like '6%';
#查询作者名中有o的Article信息，按粉丝数降序排列select * from Article where author like '%o%' order by fans desc;
#查询aid以2开头、1结尾的Article信息，按文章数降序排列select * from Article where aid like '2%1' order by articles desc;
like操作符与下划线（_）通配符
#匹配单个字符select * from Article where type like 'Seleni_m';
regexp正则提取匹配的字符
#查询Type中有SQL的Article信息select * from Article where type regexp '.SQL';
regexp 正则或（|）
#查询学生粉丝数为300或400的Article信息，按文章数降序排列select * from Article where fans regexp '300|400' order by articles desc;
regexp 正则和（[]）
#查询文章类型中e前有L或S的Article信息select * from Article where type regexp '[SL]e' order by type;
regexp 正则匹配范围[a-b]
#查询文章数为20-25的Article信息select * from Article where articles regexp '2[0-5]';
regexp 正则匹配特殊字符\\转义（.*@!_等）
#查询姓名中有*的学生信息select * from Article where type regexp '\\*';
concat()函数拼接
#将类型和对应的qq群连接起来，并按类型排序（a-z）select concat(type,'(',qq_group,')')from Article order by type;
使用列别名
select concat(type,'(',qq_group,')') as '技术交流QQ群' from Article order by type;
upper()：将文本转换为大写
#将Article表的文章类型转换为大写TYPE_UPPER,列出type和TYPE_UPPERselect type,upper(type) as TYPE_UPPER from Article order by type;
lower（）：将文本转换为小写
#将Article表的文章类型转换为小写TYPE_LOWER,列出type和TYPE_LOWERselect type,lower(type) as TYPE_LOWER from Article order by type;
length()：返回字符串的长度
#计算Article表的文章类型的字符长度select type,length(type) as TYPE_LENGTH from Article order by type;
Soundex()函数:匹配所有类似于要检索的字符串
#查询类型类似于APP的Article信息select * from Article where SOUNDEX(type)=SOUNDEX('App');
Trim()函数去掉字符串左右两边的空格
select concat(TRIM(type),'(',TRIM(qq_group),')')from Article order by type;
执行算术计算
select type,fans,articles,fans/articles as avg_fans from Article order by type desc ;
日期函数
#获取系统当前日期时间 年-月-日 时：分：秒select sysdate(); #获取系统当前日期 年-月-日select curdate(); #获取系统当前时间 时：分：秒select curtime(); #获取给定日期的年份——获取当前系统时间的年份select year(CURDATE()); #获取给定日期的月份——获取当前系统时间的月份select month(CURDATE()); #获取给定日期的天数——获取当前系统时间的天数select day(CURDATE()); #获取当前时间的前一天select date_add(CURDATE(),INTERVAL -1 day); #获取当前时间的后一天select date_sub(CURDATE(),INTERVAL -1 day); #查看文章更新时间为2020-01-01 00:00:00的文章类型select type,update_date from Article where update_date='2020-01-01 00:00:00'; #查看文章更新时间为2020-01-01的文章类型select type,update_date from Article where date(update_date)='2020-01-01';#查询2019年11月更新的文章(两种写法）#写法一：between...and 指定匹配的日期范围select type,update_date from Article where date(update_date) between '2019-11-01' and '2019-11-30';#写法二：year() and month()指定年份和月份select type,update_date from Article where year(update_date)=2019 and month(update_date)=11;
数值处理函数
#abs()返回一个数的绝对值select abs(-5); #cos()返回一个角度的余弦select cos(30); #sin()返回一个角度的正弦select sin(60); #tan()返回一个角度的正切select tan(45); #返回一个数的平方根select sqrt(4); #返回一个除操作的余数（m，n),除以n的余数select mod(5,2); #返回圆周率select pi(); #返回一个随机数（小数）select rand();
聚和函数
#AVG()函数返回列的平均值#计算平均粉丝数select avg(fans) as '平均粉丝数' from Article order by type desc ; #COUNT()函数返回某列的行数#COUNT(*)对表中行的数目进行计数， 不管表列中包含的是空值（ NULL）还是非空值#统计类型总数select count(*) from Article; #COUNT(column)对特定列中具有值的行进行计数，忽略NULL值#统计文章数select count(articles) from Article; #MAX()函数返回某列的最大值#查询阅读量最多的文章类型select max(fans) as '受众最大值' from Article; #MIN()函数返回某列的最小值select min(fans) as '受众最小值' from Article; #SUM()函数返回某列值之和#统计文章总数select sum(articles) from Article;
组合聚集函数
#DISTINCT()函数只考虑不同值的平均值select avg(distinct fans) as '平均粉丝数' from Article order by type desc ;select avg(fans) as '平均粉丝数' from Article order by type desc ; #组合聚集函数select count(*) as '总数',max(articles) as '文章数最大值',min(articles) as '文章数最小值' ,avg(fans) as '平均粉丝数'from Article;
总结：SELECT子句顺序
SELECT:要返回的列或表达式
...
FROM:要检索的数据表
WHERE:行级过滤
...
GROUP BY:分组说明
HAVING：组级过滤
...
ORDER BY：输出时排序
...
LIMIT：要检索的行数
...
最后是今天的分享：Author、Article、ArticleDetail三张表一键建表SQL语句