MySQL之数据库基本查询语句

#查询Author表name列的值
select name from Author;

#查询Author表id,name两列的值
select id,name from Author;

#查询Author表所有列的信息
select * from Author;

#查询Article表所有作者
select distinct author from Article;

#查询Article表前3行的文章类型
select type from Article limit 1,3;
select type from Article limit 3;

#Article表按aid从低到高查询作者姓名和文章类别
select aid,author,type from Article order by aid;

#Article表按aid和粉丝数从低到高查询作者姓名和文章类别
select aid,author,type,fans from Article order by aid,fans;

#按粉丝数降序排列Article信息
select * from Article order by fans desc;

#按fans降序排列Article信息，再对articles排序
select * from Article order by fans desc,articles;
#以上仅对fans降序，articles仍为升序，articles降序需加desc

#根据文章数降序排列查询前三条Article信息
select * from Article order by articles desc limit 3;

#如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY
select * 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;

#根据aid统计文章总数大于5的
select au_id,count(*) as '数目' from Article group by au_id having count(*)>5;

#将Article按author进行分组，再统计每个人的总文章数
select author,sum(articles) as '总文章数' from Article group by author with rollup;

#coalesce 语法：select coalesce(a,b,c);
select coalesce(author,'昵称') ,sum(articles) as '总文章数' from Article group by author with rollup;

#查询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);

#查询粉丝数在400到450之间的Article信息,按文章数降序排列
select * from Article where fans between 400 and 450 order by articles desc;

#查询fans为空的Article信息（没有则返回空表）
select * from Article where fans is null;

#查询粉丝数为450且文章类型为Python的Article信息
select * from Article where fans=450 and type='Python';

#查询粉丝数为300或400的Article信息
select * from Article where fans=300 or fans=400;

#查询文章数在10以上并且粉丝数为300或400的Article信息
select * from Article where (fans=300 or fans =400 )and articles>10;

#查询粉丝数在400和500的Article信息，降序排列
select * from Article where fans in(400,500) order by fans desc;

#查询粉丝数不是300和400的Article信息，按文章数降序排列
select * from Article where fans not in(300,400) order by articles desc;

#查询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;

#匹配单个字符
select * from Article where type like 'Seleni_m';

#查询Type中有SQL的Article信息
select * from Article where type regexp '.SQL';

#查询学生粉丝数为300或400的Article信息，按文章数降序排列
select * from Article where fans regexp '300|400' order by articles desc;

#查询文章类型中e前有L或S的Article信息
select * from Article where type regexp '[SL]e' order by type;

#查询文章数为20-25的Article信息
select * from Article where articles regexp '2[0-5]';

#查询姓名中有*的学生信息
select * from Article where type regexp '\\*';

#将类型和对应的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;

#将Article表的文章类型转换为大写TYPE_UPPER,列出type和TYPE_UPPER
select type,upper(type) as TYPE_UPPER from Article order by type;

#将Article表的文章类型转换为小写TYPE_LOWER,列出type和TYPE_LOWER
select type,lower(type) as TYPE_LOWER from Article order by type;

#计算Article表的文章类型的字符长度
select type,length(type) as TYPE_LENGTH from Article order by type;

#查询类型类似于APP的Article信息
select * from Article where SOUNDEX(type)=SOUNDEX('App');

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;