MySQL复习值代码知识点(2)

╰半夏微凉° 2023-06-05 12:35 17阅读 0赞

# 目录 #

> 总汇表
> 
> order by使用举例
> 
> group by使用举例
> 
> limit举例
> 
> group by 与order by 联合使用
> 
> having的使用
> 
> SQL运行结果查询

## 总汇表 ##

<table> 
 <thead> 
  <tr> 
   <th>order by</th> 
   <th>排序 desc降序 asc升序</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>group by</td> 
   <td>分组 （一般与聚集函数连用）</td> 
  </tr> 
  <tr> 
   <td>having</td> 
   <td>对组进行限制与聚集函数连用</td> 
  </tr> 
  <tr> 
   <td>Limit</td> 
   <td>分页</td> 
  </tr> 
  <tr> 
   <td>Avg</td> 
   <td>聚集函数：求平均值</td> 
  </tr> 
  <tr> 
   <td>Sum</td> 
   <td>聚集函数：求和（<strong>数值</strong>的加法）</td> 
  </tr> 
  <tr> 
   <td>Max</td> 
   <td>聚集函数：求最大值</td> 
  </tr> 
  <tr> 
   <td>Min</td> 
   <td>聚集函数：求最小值</td> 
  </tr> 
  <tr> 
   <td>count</td> 
   <td>聚集函数：相同<strong>列值</strong>求行数的和</td> 
  </tr> 
 </tbody> 
</table>

**where子句作用于表和视图，HAVING 子句作用于组；**

**where子句中不能使用聚集函数，而having子句中可以。**

有如下表：

![1232151-20190920191648596-155704741.png][]

CREATE TABLE `grade`  (
    
      `id` int(11) NOT NULL PRIMARY KEY,
    
      `name` varchar(20),
    
      `grades` int(11) ,
    
    ) ;

## order by使用举例 ##

按照姓名升序排列：

mysql> select * from grade
    
        -> order by name asc;

按照姓名降序排序：

mysql> select * from grade
    
        -> order by name desc;

## group by使用举例 ##

group通常是和别函数一起调用的。（count,sum）

求每个学生姓名的所有成绩总分：

mysql> select name,sum(grades)
    
        -> from grade
    
        -> group by name;

（解释：首先，要求每个学生的总分数，要把每个学生聚集到一起，然后去把聚集在一起**的**每个学生**的**成绩求和）

求每个学生一共有几个成绩：

mysql> select name,count(name)
    
        -> from grade
    
    -> group by name;

（解释：首先把相同name的行聚集到一起，然后用count求出每个name相同的一共有几行）

求每个学生的总成绩：

mysql> select name,sum(grades)
    
        -> from grade
    
    -> group by name;

求每个学生的平均成绩：

mysql> select name,avg(grades)
    
        -> from grade
    
        -> group by name;

求每个学生的成绩最低分：

mysql> select name,min(grades)
    
        -> from grade
    
        -> group by name;

## limit举例 ##

limit 参数1：显示查询结果的前‘参数1’个数据；  
limit 参数1 参数2：跳过查询结果的前‘参数1’个数据，显示剩下的前‘参数2’个数据  
取前三个查询结果：

mysql> select * from grade
    
    -> limit 3;

## group by 与order by 联合使用 ##

求总成绩排名前二的学生：

mysql> select name,sum(grades) as a
    
        -> from grade
    
        -> group by name
    
        -> order by a desc
    
    -> limit 2;

## having的使用 ##

查询所有学生总成绩大于100的学生：

mysql> select name,sum(grades)
    
        -> from grade
    
        -> group by name
    
        -> having sum(grades)>100;

## SQL运行结果查询 ##

### 数据输入 ###

INSERT INTO `grade` VALUES (0, '1', 10);
    INSERT INTO `grade` VALUES (1, '1', 10);
    INSERT INTO `grade` VALUES (2, '2', 20);
    INSERT INTO `grade` VALUES (3, '3', 30);
    INSERT INTO `grade` VALUES (4, '4', 40);
    INSERT INTO `grade` VALUES (5, '1', 100);
    INSERT INTO `grade` VALUES (6, '2', 100);

### 运行结果查询： ###

按照姓名升序排列：
    mysql>  select * from grade
        -> order by name asc;
    +----+------+--------+
    | id | name | grades |
    +----+------+--------+
    |  0 | 1    |     10 |
    |  1 | 1    |     10 |
    |  5 | 1    |    100 |
    |  2 | 2    |     20 |
    |  6 | 2    |    100 |
    |  3 | 3    |     30 |
    |  4 | 4    |     40 |
    +----+------+--------+
    按照姓名降序排序：
    mysql> select * from grade
        -> order by name desc;
    +----+------+--------+
    | id | name | grades |
    +----+------+--------+
    |  4 | 4    |     40 |
    |  3 | 3    |     30 |
    |  2 | 2    |     20 |
    |  6 | 2    |    100 |
    |  0 | 1    |     10 |
    |  1 | 1    |     10 |
    |  5 | 1    |    100 |
    +----+------+--------+
    求每个学生姓名的所有成绩总分：
    mysql> select name,sum(grades)
        -> from grade
        -> group by name;
    +------+-------------+
    | name | sum(grades) |
    +------+-------------+
    | 1    | 120         |
    | 2    | 120         |
    | 3    | 30          |
    | 4    | 40          |
    +------+-------------+
    求每个学生一共有几个成绩：
    mysql> select name,count(name)
        -> from grade
        -> group by name;
    +------+-------------+
    | name | count(name) |
    +------+-------------+
    | 1    |           3 |
    | 2    |           2 |
    | 3    |           1 |
    | 4    |           1 |
    +------+-------------+
    求每个学生的总成绩：
    mysql> select name,sum(grades)
        -> from grade
        ->  group by name;
    +------+-------------+
    | name | sum(grades) |
    +------+-------------+
    | 1    | 120         |
    | 2    | 120         |
    | 3    | 30          |
    | 4    | 40          |
    +------+-------------+
    求每个学生的平均成绩：
    mysql> select name,avg(grades)
        -> from grade
        -> group by name;
    +------+-------------+
    | name | avg(grades) |
    +------+-------------+
    | 1    | 40.0000     |
    | 2    | 60.0000     |
    | 3    | 30.0000     |
    | 4    | 40.0000     |
    +------+-------------+
    求每个学生的成绩最低分：
    mysql> select name,min(grades)
        -> from grade
        -> group by name;
    +------+-------------+
    | name | min(grades) |
    +------+-------------+
    | 1    |          10 |
    | 2    |          20 |
    | 3    |          30 |
    | 4    |          40 |
    +------+-------------+
    取前三个查询结果：
    mysql> select * from grade
        -> limit 3;
    +----+------+--------+
    | id | name | grades |
    +----+------+--------+
    |  0 | 1    |     10 |
    |  1 | 1    |     10 |
    |  2 | 2    |     20 |
    +----+------+--------+
    求总成绩排名前二的学生：
    mysql> select name,sum(grades) as a
        -> from grade
        -> group by name
        -> order by a desc
        -> limit 2;
    +------+-----+
    | name | a   |
    +------+-----+
    | 1    | 120 |
    | 2    | 120 |
    +------+-----+
    查询所有学生总成绩大于100的学生：
    mysql> select name,sum(grades)
        -> from grade
        -> group by name
        -> having sum(grades)>100;
    +------+-------------+
    | name | sum(grades) |
    +------+-------------+
    | 1    | 120         |
    | 2    | 120         |
    +------+-------------+

转载于:https://www.cnblogs.com/Anxc/p/11558963.html

[1232151-20190920191648596-155704741.png]: /images/20230601/67b357bb74a94c2eb34a28eb2af8b984.png