第四单元(数据库高级查询)
<blockquote>
<p>学习目标</p>
<pre><code>能够知道升序查询和降序查询的关键字</code></pre>
<p><img src="https://note.youdao.com/yws/res/21761/WEBRESOURCE05d18729138376338aa1c796daf29ef7" alt="image.png" /></p>
<h2>1. 排序查询语法</h2>
<p>排序查询语法:</p>
</blockquote>
<pre><code>select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
语法说明:</code></pre>
<blockquote>
<p>先按照列1进行排序,如果列1的值相同时,则按照 列2 排序,以此类推</p>
</blockquote>
<ul>
<li>asc从小到大排列,即升序</li>
<li>desc从大到小排序,即降序</li>
<li>默认按照列值从小到大排列(即asc关键字)</li>
</ul>
<pre><code>例1:查询男生信息,按学号降序:
select * from students where gender=1 order by id desc;
例2:显示所有的学生信息,先按照年龄从大-->小排序,当年龄相同时 按照身高从高-->矮排序:
select * from students order by age desc,height desc;</code></pre>
<blockquote>
<p>小结</p>
</blockquote>
<p>(1)在MySql中,使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。</p>
<p>(2)尤其非常特别重要:默认按升序(ASC)排列。</p>
<p>(3)order by 后可加2个(或多个)字段,字段之间用英文逗号隔开。</p>
<p>(4)若A用升序,B用降序,SQL该这样写:order by A ASC, B DESC; 默认同理,也可以这样写:order by A, B DESC;</p>
<p>(5)若A、B都用降序,必须用两个DESC,order by A DESC, B DESC;</p>
<p>(6)多个字段时,优先级按先后顺序而定</p>
<blockquote>
<p>能够写出查询总行数的SQL语句</p>
</blockquote>
<h2>1. 聚合函数的介绍</h2>
<pre><code>聚合函数又叫组函数,通常是对表中的数据进行统计和计算,一般结合分组(group by)来使用,用于统计和计算分组数据。</code></pre>
<h2>2.常用的聚合函数:</h2>
<ul>
<li>count(col): 表示求指定列的总行数</li>
<li>max(col): 表示求指定列的最大值</li>
<li>min(col): 表示求指定列的最小值</li>
<li>sum(col): 表示求指定列的和</li>
<li>avg(col): 表示求指定列的平均值</li>
</ul>
<blockquote>
<p>求总行数</p>
</blockquote>
<pre><code>返回非NULL数据的总行数.
select count(height) from students;
返回总行数,包含null值记录;
select count(*) from students;`</code></pre>
<blockquote>
<p>求最大值</p>
</blockquote>
<pre><code>查询女生的编号最大值
select max(id) from students where gender = 2;
</code></pre>
<blockquote>
<p>求最小值</p>
<pre><code>查询未删除的学生最小编号
select min(id) from students where is_delete = 0;</code></pre>
<p>求和</p>
</blockquote>
<pre><code>查询男生的总身高
select sum(height) from students where gender = 1;
平均身高
select sum(height) / count(*) from students where gender = 1;</code></pre>
<blockquote>
<p>求平均值</p>
</blockquote>
<pre><code>求男生的平均身高, 聚合函数不统计null值,平均身高有误
select avg(height) from students where gender = 1;
求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;
说明
ifnull函数: 表示判断指定字段的值是否为null,如果为空使用自己提供的值/</code></pre>
<h2>聚合函数的特点</h2>
<pre><code>聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算,必须使用ifnull函数对null值做替换</code></pre>
<blockquote>
<p>小结</p>
</blockquote>
<ul>
<li>count(col): 表示求指定列的总行数</li>
<li>max(col): 表示求指定列的最大值</li>
<li>min(col): 表示求指定列的最小值</li>
<li>sum(col): 表示求指定列的和</li>
<li>avg(col): 表示求指定列的平均值</li>
</ul>
<h2>分组查询</h2>
<blockquote>
<p>学习目标</p>
<pre><code>能够写出分组查询的SQL语句</code></pre>
<p><img src="https://note.youdao.com/yws/res/20917/WEBRESOURCEfd5c17130250ae9d2e5e19505c5e488a" alt="image.png" /></p>
</blockquote>
<h2>1. 分组查询介绍</h2>
<blockquote>
<p>分组查询就是将查询结果按照指定字段进行分组,字段中数据相等的分为一组。</p>
</blockquote>
<pre><code>分组查询基本的语法格式如下:
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
说明:
列名: 是指按照指定字段的值进行分组。
HAVING 条件表达式: 用来过滤分组后的数据。
WITH ROLLUP:在所有记录的最后加上一条记录,显示select查询时聚合函数的统计和计算结果
</code></pre>
<h2>2. group by的使用</h2>
<blockquote>
<p>group by可用于单个字段分组,也可用于多个字段分组</p>
</blockquote>
<pre><code>根据gender字段来分组
select gender from students group by gender;
>根据name和gender字段进行分组
select name, gender from students group by name, gender;</code></pre>
<h2>3. group by + group_concat()的使用</h2>
<pre><code>group_concat(字段名): 统计每个分组指定字段的信息集合,每个信息之间使用逗号进行分割
根据gender字段进行分组, 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;
</code></pre>
<h2>4. group by + 聚合函数的使用</h2>
<pre><code>统计不同性别的人的平均年龄
select gender,avg(age) from students group by gender;
统计不同性别的人的个数
select gender,count(*) from students group by gender;</code></pre>
<h2>5. group by + having的使用</h2>
<pre><code>having作用和where类似都是过滤数据的,但having是过滤分组数据的,只能用于group by
根据gender字段进行分组,统计分组条数大于2的
select gender,count(*) from students group by gender having count(*)>2;</code></pre>
<h2>6. group by + with rollup的使用</h2>
<pre><code>with rollup的作用是:在最后记录后面新增一行,显示select查询时聚合函数的统计和
计算结果
根据gender字段进行分组,汇总总人数
select gender,count(*) from students group by gender with rollup;
根据gender字段进行分组,汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;</code></pre>
<blockquote>
<p>小结</p>
<ul>
<li>group by 根据指定的一个或者多个字段对数据进行分组</li>
<li>group_concat(字段名)函数是统计每个分组指定字段的信息集合</li>
<li>聚合函数在和 group by 结合使用时, 聚合函数统计和计算的是每个分组的数据</li>
<li>having 是对分组数据进行条件过滤</li>
<li>with rollup在最后记录后面新增一行,显示select查询时聚合函数的统计和计算结果</li>
</ul>
</blockquote>