mysql内置函数使用

说明

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

1.聚合函数

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

2.字符串函数

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

应用：

3.流程控制函数

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

4.日期和时间函数

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

5.四舍五入函数

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

示例：

• 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

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

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

语句含义

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

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`