mysql内置函数使用

亦凉 2021-05-10 11:13 700阅读 0赞

### mysql常用函数 ###

*   *  说明
     *  1.聚合函数
     *  2.字符串函数
     *  3.流程控制函数
     *  4.日期和时间函数
     *  5.四舍五入函数
     *  6. using()函数的用法
     *  7. select from where group by having order by limit语句执行顺序
     *  8. mysql的插入函数
     *  9. mysql的删除函数
     *  10. mysql操作数据表

## 说明 ##

mysql是我们日常软件开发最常用最简单的数据库，mybatis是dao层开发的主流框架，  
掌握mysql的常用函数，会给我们日常书写sql带来很大便捷性。

## 1.聚合函数 ##

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>count(col)</td> 
   <td>统计查询结果的行数</td> 
  </tr> 
  <tr> 
   <td>min(col)</td> 
   <td>查询指定列的最小值</td> 
  </tr> 
  <tr> 
   <td>max(col)</td> 
   <td>查询指定列的最大值</td> 
  </tr> 
  <tr> 
   <td>sum(col)</td> 
   <td>求和，返回指定列的总和</td> 
  </tr> 
  <tr> 
   <td>avg(col)</td> 
   <td>求平均值，返回指定列数据的平均值</td> 
  </tr> 
 </tbody> 
</table>

## 2.字符串函数 ##

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>concat(s1,s2…,sn)</td> 
   <td>合并字符串，concat(‘hello’,’ world!’) 返回 hello world！</td> 
  </tr> 
  <tr> 
   <td>LOWER(str)</td> 
   <td>字母转换小写</td> 
  </tr> 
  <tr> 
   <td>UPPER(str)</td> 
   <td>字母转换大写</td> 
  </tr> 
  <tr> 
   <td>TRIM(str)</td> 
   <td>去除字符串左右两边的空格</td> 
  </tr> 
  <tr> 
   <td>REPLACE(str, a, b)</td> 
   <td>使用字符串 b 替换字符串 str 中所有的字符串 a</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING(s, n, l)</td> 
   <td>从字符串s中从位置n开始，截取一个长度同为 l 的子字符串</td> 
  </tr> 
  <tr> 
   <td>REVERSE(str)</td> 
   <td>字符串反转</td> 
  </tr> 
  <tr> 
   <td>LENGTH(s)</td> 
   <td>计算字符串长度函数</td> 
  </tr> 
 </tbody> 
</table>

应用：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAyNTM2Mg_size_16_color_FFFFFF_t_70]

## 3.流程控制函数 ##

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>IF(expr, expr2, expr3)</td> 
   <td>用法类似 java 的三目表达式,IF(expr1,expr2,expr3)，expr1的值为true，返回expr2的值，否则返回expr3</td> 
  </tr> 
  <tr> 
   <td>IFNULL(expr1, expr2)</td> 
   <td>如果expr1不为空，返回expr1，否则返回expr2</td> 
  </tr> 
  <tr> 
   <td>NULLIF(expr1, expr2)</td> 
   <td>如果expr1=expr2返回NULL，否则返回expr1</td> 
  </tr> 
 </tbody> 
</table>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAyNTM2Mg_size_16_color_FFFFFF_t_70 1]

## 4.日期和时间函数 ##

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>date_sub()</td> 
   <td>获取当前时间的前一天select date_sub(curdate(),interval 1 day)</td> 
  </tr> 
  <tr> 
   <td>curdate()</td> 
   <td>返回当前日期</td> 
  </tr> 
  <tr> 
   <td>curtime()</td> 
   <td>返回当前时间</td> 
  </tr> 
  <tr> 
   <td>now()</td> 
   <td>返回当前的日期和时间</td> 
  </tr> 
  <tr> 
   <td>UNIX_TIMESTAMP(date)</td> 
   <td>返回日期的UNIX时间戳，与FROM_UNIXTIME为反函数</td> 
  </tr> 
  <tr> 
   <td>FROM_UNIXTIME()</td> 
   <td>返回UNIX时间戳的日期值</td> 
  </tr> 
  <tr> 
   <td>WEEK(date)</td> 
   <td>返回日期date为一年中的第几周，返回值的范围是否为 0-52 或 1-53</td> 
  </tr> 
  <tr> 
   <td>MONTH(date)</td> 
   <td>返回date得月份，返回值的范围是否为 1-12</td> 
  </tr> 
  <tr> 
   <td>YEAR(date)</td> 
   <td>返回日期date的年份，返回值范围是 1970-2069</td> 
  </tr> 
  <tr> 
   <td>HOUR(time)</td> 
   <td>返回time的小时值</td> 
  </tr> 
  <tr> 
   <td>MINUTE(time)</td> 
   <td>返回time的分钟值</td> 
  </tr> 
  <tr> 
   <td>DATE_FORMAT(date,fmt)</td> 
   <td>格式化指定的日期，根据参数返回指定格式的值</td> 
  </tr> 
  <tr> 
   <td>DAYOFYEAR(date)</td> 
   <td>返回date为一年中的第几天，返回值范围是1-366</td> 
  </tr> 
  <tr> 
   <td>DAYOFMONTH(date)</td> 
   <td>返回date为一个月中的第几天，返回值范围是1-31</td> 
  </tr> 
  <tr> 
   <td>WEEKDAY(date)</td> 
   <td>返回date为一周内的对应的工作日索引，返回值范围是1-7</td> 
  </tr> 
 </tbody> 
</table>

## 5.四舍五入函数 ##

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ROUND(x,y)</td> 
   <td>四舍五入保留y位小数，y默认为0， 可以为负数， 如round(19, -1)返回20</td> 
  </tr> 
  <tr> 
   <td>TRUNCATE(x,y)</td> 
   <td>截断至保留y位小数，y可以为负数， 如trancate(19,-1)返回10</td> 
  </tr> 
 </tbody> 
</table>

示例：

*  ROUND(x,y)

SELECT 
        ROUND(5.45),
        ROUND(-5.5),
        ROUND(124.65, -1),
        ROUND(277.8, -2)
    
    ┌─ROUND(5.45)─┬─ROUND(-5.5)─┬─ROUND(124.65, -1)─┬─ROUND(277.8, -2)─┐
    │           5 │          -6 │               120 │              300 │
    └─────────────┴─────────────┴───────────────────┴──────────────────┘

*  TRUNCATE(x,y)

SELECT 
        TRUNCATE(5.468, 2),
        TRUNCATE(66.56, 1),
        TRUNCATE(2.36, 0),
        TRUNCATE(16.78, -1)
    
    ┌─TRUNCATE(5.468, 2)─┬─TRUNCATE(66.56, 1)─┬─TRUNCATE(2.36, 0)─┬─TRUNCATE(16.78, -1)─┐
    │               5.46 │               66.5 │                 2 │                  10 │
    └────────────────────┴────────────────────┴───────────────────┴─────────────────────┘

## 6. using()函数的用法 ##

1.  在mysql中的使用using，主要用于两个表中有着相同字段查询操作，与join on中的on操作有着相同功能。
2.  以下两个功能结果一样：

select a.title,u.user_name from article as a
    join user as u
    on a.user_id=u.user_id

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAyNTM2Mg_size_16_color_FFFFFF_t_70 2]

select a.title,u.user_name from article as a
    join user as u
    using(user_id)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAyNTM2Mg_size_16_color_FFFFFF_t_70 3]

## 7. select from where group by having order by limit语句执行顺序 ##

*  sql的查询语句主要有select from where group by having order by limit这七个，  
    执行sql的时候select和from是必选的，其他是可选。
 *  在书写sql语句的时候，他们的顺序依次为：  
    select -> from -> where -> group by -> having -> order by -> limit
 *  而在执行sql语句的时候，他们的顺序依次为：  
    from -> where -> group by -> having -> select -> order by -> limit

语句含义

<table> 
 <thead> 
  <tr> 
   <th>语句</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>from</td> 
   <td>从那个表读取数据</td> 
  </tr> 
  <tr> 
   <td>where</td> 
   <td>筛选数据的条件</td> 
  </tr> 
  <tr> 
   <td>group by</td> 
   <td>对筛选后的数据进行分组</td> 
  </tr> 
  <tr> 
   <td>having</td> 
   <td>对分组后的数据进行筛选的条件</td> 
  </tr> 
  <tr> 
   <td>select</td> 
   <td>查询结果集的字段或字段的计算结果</td> 
  </tr> 
  <tr> 
   <td>order by</td> 
   <td>按字段排序</td> 
  </tr> 
  <tr> 
   <td>limit</td> 
   <td>指定返回n行数据，用于分页</td> 
  </tr> 
 </tbody> 
</table>

## 8. mysql的插入函数 ##

1.  **insert ignore into :**  
    当插入数据时，数据库不会检查主键（primary key）或者唯一主键（unique key），如出现重复数据，也不返回错误，可继续往下执行。
2.  **insert into :**  
    当插入数据时，数据库会检查主键（primary key）或者唯一主键（unique key），如果出现重复会报错，无法插入。
3.  **replace into ：**  
    如果存在主键（primary key）或者唯一主键（unique key）相同的记录，则先删除掉再插入新记录。
4.  **insert into … on duplicate key update …**  
    当我们插入一行新记录时，记录不存在就插入。如果你插入的记录导致一个唯一索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句。

## 9. mysql的删除函数 ##

1.  **drop：**  
    直接删除表结构和数据，并且清空表占用的储存空间，语法：drop table tablename if exists
2.  **truncate：**  
    删除表中的数据，再插入数据时自增长的数据id又重新从1开始，语法：truncate table tablename
3.  **delete**  
    删除表中数据，可以在后面添加where字句，与truncate相比，delete相当于是筛选后的truncate，语法：delete from tablename where …

## 10. mysql操作数据表 ##

1.  修改数据表名字：

# 格式 rename table ‘旧表名’ to ‘新表名’
    
    rename table `mysql_table_1` to `mysql_table_2`

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAyNTM2Mg_size_16_color_FFFFFF_t_70]: /images/20210505/8112f750758c45d79763692f41616ed9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAyNTM2Mg_size_16_color_FFFFFF_t_70 1]: /images/20210505/acccd5cc5eb34c108c938073bf6b2e12.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAyNTM2Mg_size_16_color_FFFFFF_t_70 2]: /images/20210505/84f95ce8a2564453ad716e8550249673.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAyNTM2Mg_size_16_color_FFFFFF_t_70 3]: /images/20210505/95704b1a0c14407da2292157aab496d2.png