常用SQL语句（MySQL）

雨点打透心脏的1/2处 2022-01-29 15:13 382阅读 0赞

### SQL语句 ###

*  执行算术运算
 *  常用函数
 *   *  数学函数
     *  处理字符串
     *  聚合函数
     *  AVG()
     *  MAX()
     *  MIN()
     *  COUNT()
     *  SUM()
 *  子查询
 *  一些关键字
 *   *  GROUP BY
     *  Order By
     *  Limit
     *  In
     *  Having
     *  AS
     *  Like
     *  distinct
 *  多表查询
 *   *  Join

推荐一个练习SQL的网站：[https://mp.weixin.qq.com/s?\_\_biz=MzI4Njc5NjM1NQ==&mid=2247483693&idx=1&sn=9fa301b0076778cd854a924e96cc356e&chksm=ebd63e01dca1b71745dca1f7e1c2aa2b7c80a393185db690b4fdfba22bb10ca87ea2cd6fa774&scene=21\#wechat\_redirect][https_mp.weixin.qq.com_s_biz_MzI4Njc5NjM1NQ_mid_2247483693_idx_1_sn_9fa301b0076778cd854a924e96cc356e_chksm_ebd63e01dca1b71745dca1f7e1c2aa2b7c80a393185db690b4fdfba22bb10ca87ea2cd6fa774_scene_21_wechat_redirect]

# 执行算术运算 #

**说明：** SQL中允许进行加减乘除简单算术运算。  
**示例：**

select 1+1;
    select name from persion where id=1+1;

# 常用函数 #

## 数学函数 ##

**说明：** SQL中内置了一些数学函数使用。  
**示例：** 选了部分常用的示例。

//返回绝对值
    select abs(-1);
    
    //浮点数取整
    select floor(9.001)  //返回9，整数舍去小数位
    select floor(-9.001) //返回-10，负数舍去小数位，个位+1
    
    //四舍五入，根据第二个参数控制小数点精确位数。默认为取整
    select round(1.5) // 返回2
    select round(1.52,1) //返回1.5
    select round(1.56,1) //返回1.6
    
    //判断正负数。整数返回1，负数返回-1，0就返回0
    select sign(param)

## 处理字符串 ##

**示例：**

//将字符串中的字母全部转换为小写
    select lower("ABC12./")  // 返回 abc12./
    //将字符串中的字母全部转换为大写
    select upper("abc123") // 返回ABC123
    
    //去掉字符串头部的空格
    select ltrim(" abc");
    //去掉字符串尾部的空格
    select rtrim("abc ")
    
    //将多个字符串连接成一个字符串。其中如果有一个字符串为null，则返回null。
    select CONCAT("1","2");  //执行结果为12
    
    //同CONCAT，只不过可以指定分隔符，分隔符为第一个参数。如果分隔符为Null，则结果为Null。如果拼接的字符串有null的
    select CONCAT_WS(",","aaa",null,"bbb");  //执行结果为aaa,aaa
    
    // 返回指定字符串从指定位置开始读取的一个字符串
    select substr("hello world!",6)

## 聚合函数 ##

## AVG() ##

**用途：** 返回指定数值列的平均值。列的字段类型如果是字符串会自动转成数字进行计算，并且将非数字的当做0处理。  
**例子：**  
假设表（demo）中数据为：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>state</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>d</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>s</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>b</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>v</td> 
  </tr> 
 </tbody> 
</table>

// 结果为 0.2
    select avg(state) from demo;
    //结果为3
    select avg(id) from demo;

## MAX() ##

**用途：** 返回指定列中的最大值  
**例子：**  
还是AVG中的例子

// 返回5
    select max(id) from demo;

## MIN() ##

**用途：** 返回指定列中的最小值  
**例子：**  
还是AVG中的例子

// 返回1
    select min(id) from demo;

## COUNT() ##

**用途：** 返回查询到的条数  
**例子：**  
还是AVG中的例子

//返回5
    select count(*) from demo;

## SUM() ##

**用途：** 返回指定列中所有数值的和，如果为无法计算的类型当做0处理。  
**例子：**

// 返回15
    select sum(id) from demo;
    //返回1
    select sum(state) from demo;

# 子查询 #

**定义：** 子查询就是嵌套在其他语句中的查询。  
**例子：**  
假设表（demo）中数据为：

select * from demo where id=(select id from demo where state='d')
    
    //将id=2的state设置为id为1的state
    update demo set state=(select state from demo where id=1) where id=2;

# 一些关键字 #

## GROUP BY ##

**用途：** 根据指定的规则对数据进行分组  
**示例：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70]使用group by StuId，那么结果不变，因为StuId每个都不相同。  
如果使用group by ClassId，返回结果变为：  
![在这里插入图片描述][20190522194727542.png]  
那么如果加上成绩的列，我们就可以group by ClassId来根据班级获得每班的最高分最低分等操作。

## Order By ##

**用途：** 对查询到的结果进行排序。  
**示例：**

//根据id降序
    select * from table order by id desc;
    //默认升序 asc
    select * from table order by id ;

## Limit ##

**用途：** 返回指定部分的数据  
**示例：**

// 从查询结果的第0个开始返回两个数据
    select * from table limit 0,2;
    //等同于
    select * from table limit 2;
    //从插叙结果的第1个开始返回4个数据
    select * from table limit 1,4;

## In ##

**用途：** 用where表达式中，列表项支持多个选择（用多个or不太优雅）  
**示例：**

// 查询table1中name为admin，tom，Sally的记录
    select * from table1 where name in (‘admin’,'tom','Sally')
    // 查询table1中name不为admin，tom，Sally的记录
    select * from table1 where name not in (‘admin’,'tom','Sally')
    
    // in中可以使用子查询，使用的是查询的结果来
    select * from table1 where name in(select name from table1 where id<3)

## Having ##

\*\*用途：\*\*用于过滤分组后的数据，用法和Where大致相同，只是作用域不同。  
**示例：**

// 查询地图表中每个地区的人口综合，过滤出人口大于1亿的数据。having是针对分组后的数据。这里无法使用where，因为where作用
    //时地区人口还没计算出来 
    select region,sum(populationd) from map group by region having populationd>100000000;

## AS ##

\*\*用途：\*\*可以为表名，列名，指定一个别名。主要是为了提高可读性。  
**例子：**

//列的别名
    select name AS n,age AS a from persion
    //表的别名
    select name,age from persion AS p

## Like ##

**用途：** 常用于模糊搜索  
**示例：**

*  ike关键字通常和通配符%配合使用。如果不加%，表示精确匹配就和 = 的效果相同了。
 *  like匹配字母的时候默认是不区分大小写的，如果需要区分则加入binary关键字，它会转换成码表中的二进制数进行对比，出现中文无法匹配时也可以使用

// 匹配name是以王开头的记录
    select * from table1 where name like '王%'
    // 匹配name是以王结尾的记录
    select * from table1 where name like '%王'
    // 匹配name中含有王这个字的记录
    select * from table1 where name like '%王%'
    
    //使用binary
    select * from table1 where name like binary '%王'

## distinct ##

**用途：** 用来查询不重复记录的条数  
**例子：**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>tom</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>tom</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>sally</td> 
  </tr> 
 </tbody> 
</table>

//查询的结果为tom,sally
    select distinct  name from table1;
    
    //注意distinct必须放在开头。下面是错误示范，会报错的
    select id,distinct name from table1;

# 多表查询 #

## Join ##

MySQL中有三种：

*  **Inner Join**（内连接、等值连接）：用于获取两个表的交集  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 1]
 *  **Left Join**（左外连接）：获取左表中全部匹配的记录，右表中只获取和左表有交集的部分，没有的话为null  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 2]\- **Right Join（右外连接）**：与左外连接相反，获取右表中全部匹配的记录，左表中只获取和右表有交集 的部分  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 3]

**例子：**  
student表：  
![在这里插入图片描述][2019052219104822.png]

class表：  
![在这里插入图片描述][20190522191101751.png]  
**内连接查询：**

select s.StuId,s.StuName,s.ClassId,c.ClassName from student s inner join class c on s.ClassId=c.ClassId;

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 4]stuId=7和8的不在交集中没有被查询 出来。

**左外连接查询：**

select s.StuId,s.StuName,s.ClassId,c.ClassName from student s left join class c on s.ClassId=c.ClassId;

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 5]stuId=7和8表示的ClassId在右侧的表中没有交集，所以显示为null。

**右外连接查询：**

select s.StuId,s.StuName,s.ClassId,c.ClassName from student s right join class c on s.ClassId=c.ClassId;

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 6]

[https_mp.weixin.qq.com_s_biz_MzI4Njc5NjM1NQ_mid_2247483693_idx_1_sn_9fa301b0076778cd854a924e96cc356e_chksm_ebd63e01dca1b71745dca1f7e1c2aa2b7c80a393185db690b4fdfba22bb10ca87ea2cd6fa774_scene_21_wechat_redirect]: https://mp.weixin.qq.com/s?__biz=MzI4Njc5NjM1NQ==&mid=2247483693&idx=1&sn=9fa301b0076778cd854a924e96cc356e&chksm=ebd63e01dca1b71745dca1f7e1c2aa2b7c80a393185db690b4fdfba22bb10ca87ea2cd6fa774&scene=21#wechat_redirect
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70]: /images/20220129/8754754e6cbd416bb46547d6f2a5eea1.png
[20190522194727542.png]: /images/20220129/469aafcaf3a949b7a2bc16531a19486a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 1]: /images/20220129/30f41eecfcfa45d994184b3afd341018.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 2]: /images/20220129/97cfce9cafb74d92822c55641fc95189.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 3]: /images/20220129/01429447968c47d88f29ef55f182ca8e.png
[2019052219104822.png]: /images/20220129/beb12a3991bf42b3b5e8bf733cbedc60.png
[20190522191101751.png]: /images/20220129/b31af3cf6fc64f7bab0cd9a187f88406.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 4]: /images/20220129/f02033722e7e4b1f8a672252e3f5670c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 5]: /images/20220129/1dee60c6ed4d483797a5c2be123875b4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE4NDc2OQ_size_16_color_FFFFFF_t_70 6]: /images/20220129/be5b075b7ff742b28e11d278007219ff.png