SELECT
子查询（ subquery）即嵌套查询 ，嵌套在其他查询中的查询。
查询粉丝数大于400的用户的作者QQ号
select name,au_id,qq from Author where au_id in(select au_id from Article where fans>400);
统计每类文章下的文章数目
select type,author,(select count(*) from ArticleDetail where ArticleDetail.ar_id=Article.ar_id) as '文章数目' from Article;
查询每篇文章的阅读次数，所属类型，作者
select ArticleDetail.title,ArticleDetail.reade_times,Article.type,Article.author from ArticleDetail,Article where ArticleDetail.ar_id=Article.ar_id ;
查询文章粉丝数大于400的所有文章类型及作者姓名，QQ
select Ar.type,Au.name,Au.qq,Ar.fansfrom Article Ar,Author Auwhere Ar.au_id=Au.au_idand Ar.fans>400order by fans desc;
内联结：inner join... on...
左联结：left join... on...
右联结：rigth join... on...
读取ArticleDetail表中所有文章(title)在Article表中对应的类型，作者
select ArticleDetail.title,ArticleDetail.reade_times,Article.type,Article.author,Article.update_date from ArticleDetail inner join Article on ArticleDetail.ar_id=Article.ar_id ;
#LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。#ArticleDetail为左表，Article为右表select ArticleDetail.title,ArticleDetail.reade_times,Article.type,Article.author,Article.update_date from ArticleDetail left join Article on ArticleDetail.ar_id=Article.ar_id ;
#right JOINselect ArticleDetail.title,ArticleDetail.reade_times,Article.type,Article.author,Article.update_date from ArticleDetail right join Article on ArticleDetail.ar_id=Article.ar_id ;
查询点赞量为5的文章标题，作者微信
select ArticleDetail.title,ArticleDetail.praise_times,Author.name,Author.wechatfrom ArticleDetail,Article,Author where ArticleDetail.ar_id=Article.ar_id and Article.au_id=Author.au_id and ArticleDetail.praise_times=5;#注：尽量不要联结不必要的表，联结的表越多，性能下降越厉害
查询阅读次数大于400的文章标题，作者QQ号（使用表别名）
select AD.title,AD.reade_times,Au.name,Au.qqfrom ArticleDetail AD,Article Ar ,Author Auwhere AD.ar_id=Ar.ar_id and Ar.au_id=Au.au_id and AD.reade_times>400;
自联结语法：
SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；
SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；
UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）
列出Author表中和Article中所有不同的作者名：每个列出现一次
select name from Authorunion select author from Article order by name;
列出Author表中和Article中所有的作者名:每个列出现多次
select name from Authorunion allselect author from Article order by name;
#通过where进一步检索select name,qq from Author where name='coco'union allselect author,qq_group from Article where author='coco'order by name;
查询文章评论次数不小于10且赞赏次数为2或5的文章信息
#一般写法select ar_id,title,comments_times,praise_times from ArticleDetail where comments_times>=10 or praise_times in (5,8);
#自联结写法select ar_id,title,comments_times,praise_times from ArticleDetail where comments_times>=10union select ar_id,title,comments_times,praise_times from ArticleDetail where praise_times in (5,8);
UNION ALL包含或取消重复的行
select ar_id,title,comments_times,praise_times from ArticleDetail where comments_times>10union allselect ar_id,title,comments_times,praise_times from ArticleDetail where praise_times in (5,8);
对组合查询结果排序
select ar_id,title,comments_times,praise_times from ArticleDetail where comments_times>=10union select ar_id,title,comments_times,praise_times from ArticleDetail where praise_times in (5,8) order by ar_id desc,title;
最后是今天的分享：Author、Article、ArticleDetail三张表一键建表SQL语句