MySQL之数据库基本查询语句

——————·今天距2020年43天·——————

这是ITester软件测试小栈第80次推文

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;

SELECT

ORDER BY子句

对单个查询列进行排序（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 BY
select * from Article order by type;


#如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序
select * from Article order by convert(type using gbk);

SELECT

grop by子句

对条件进行分组排序

#分别统计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;

SELECT

WHERE子句

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;

SELECT

数据过滤

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;

SELECT

通配符进行过滤查询

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';

SELECT

正则表达式进行过滤查询

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 '\*';

SELECT

使用函数处理数据

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_UPPER
select type,upper(type) as TYPE_UPPER from Article order by type;

lower（）：将文本转换为小写

#将Article表的文章类型转换为小写TYPE_LOWER,列出type和TYPE_LOWER
select 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子句顺序