MySQL查询语句

刺骨的言语ヽ痛彻心扉 2023-02-21 02:25 24阅读 0赞

查询属性：

<table> 
 <tbody> 
  <tr> 
   <td style="width:187px;">SELECT</td> 
   <td style="width:524px;">读取一条或者多条记录</td> 
  </tr> 
  <tr> 
   <td style="width:187px;">FROM</td> 
   <td style="width:524px;">设定查询来源</td> 
  </tr> 
  <tr> 
   <td style="width:187px;">WHERE</td> 
   <td style="width:524px;">设定查询条件&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:187px;">GROUP BY</td> 
   <td style="width:524px;">设定查询分组（GROUP BY会把相同的值分为一组）</td> 
  </tr> 
  <tr> 
   <td style="width:187px;">HAVING</td> 
   <td style="width:524px;">设定过滤内容</td> 
  </tr> 
  <tr> 
   <td style="width:187px;">ORDER BY</td> 
   <td style="width:524px;">设定查询排序</td> 
  </tr> 
  <tr> 
   <td style="width:187px;">LIMIT</td> 
   <td style="width:524px;"> <p>设定返回的记录数 例如：0,1 下标为0的开始，到下标为1的结束</p> </td> 
  </tr> 
 </tbody> 
</table>

**查询顺序**

select ' ' from '' where '' group by '' having '' order by '' limit ''

### **示例：** ###

**1. FROM**

**查询整个order表**

select * FROM `order`

**查询 order 表下面的order\_numbe**

select `order_number` FROM `order`

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70][]

**2. WHERE**

**查询order 表下面某一条order\_number**

select `order_number` FROM `order` WHERE order_number='A809190953095031'

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 1][]

**查询order 表下面多条order\_numbe**

select `order_number` FROM `order` WHERE order_number IN ('A809190953095031','A809191884980807')

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 2][]

**3. GROUP BY**

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>count</td> 
   <td><u><strong>统计数量</strong></u></td> 
  </tr> 
  <tr> 
   <td>SUM</td> 
   <td><u><strong>求和</strong></u></td> 
  </tr> 
  <tr> 
   <td>MAX</td> 
   <td><u><strong>最大值</strong></u></td> 
  </tr> 
  <tr> 
   <td>MIN</td> 
   <td><u><strong>最小值</strong></u></td> 
  </tr> 
  <tr> 
   <td>AVG</td> 
   <td><u><strong>平均值</strong></u></td> 
  </tr> 
  <tr> 
   <td>first</td> 
   <td><u><strong>第一条记录</strong></u></td> 
  </tr> 
  <tr> 
   <td>last</td> 
   <td><u><strong>最后一条记录</strong></u></td> 
  </tr> 
 </tbody> 
</table>

**a. count - 统计数量**

**查询order下相同订单号（order\_number）的分组及每组数量**

select order_number, count(*) FROM `order` GROUP BY order_number

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 3][]

**b. SUM - 求和**

**查询order下相同订单号（order\_number）的分组及每组价格（price）总和**

select order_number, SUM(price) FROM `order` GROUP BY order_number

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 4][]

**查询order下相同订单号（order\_number）的分组及每组价格（price）和 商品价（goods\_sum\_price）总和**

select order_number, SUM(price) + SUM(goods_sum_price) FROM `order` GROUP BY order_number

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 5][]

** c. MAX - 最大值**

**查询order下相同订单号（order\_number）的分组及每组最大价格**

select order_number, MAX(price) FROM `order` GROUP BY order_number

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 6][]

** d. MIN - 最小值**

**查询order下相同订单号（order\_number）的分组及每组最小价格**

select order_number, MIN(price) FROM `order` GROUP BY order_number

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 7][]

**e. AVG - 平均值**

**查询order下相同订单号（order\_number）的分组及每组价格的平均值**

select order\_number, AVG(price) FROM \`order\` GROUP BY order\_number

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 8][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200628150422961.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/202006281532219.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200628154220753.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200628161324182.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200628171226827.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/2020062817273611.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200628173946779.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200628182028828.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200628182217670.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNjQ4NDUy,size_16,color_FFFFFF,t_70