MySQL 数据库函数-KeepStudy

MySQL 数据库函数

## 数学函数
- abs(X)：返回X的绝对值，如传进-1，则返回1。
- ln(X)：返回X的自然相对数。
- log(X,Y)：返回以X的以Y为底的对数。
- log10(X):返回以X基数为10的对数。
- bin(X)：返回X的二进制值。
- oct(X)：返回X的八进制值。
- hex(X)：返回X的十六进制值。
- mod(X,Y)：返回X除以Y的余数。
- ceil(X) | ceiling(X)：返回不小于X的最小整数，如传入1.23，则返回2。
- round(X)：返回X四舍五入的整数。
- floor(X)：返回X向下取整后的值，如传入2.34，会返回2。
- greatest(X1,X2....,Xn)：返回集合中的最大整数值。
- least(X1,X2....,Xn)：返回集合中的最小整数值。
- rand(N)：返回一个0~N``0~1之间的随机小数（不传参默认返回0~1之间的随机小数）。
- sign(X)：传入正数，返回1；传入负数，返回-1；传入0，返回0。
- pow(X,Y) | power(X,Y)：返回X的Y次方值。
- pi()：返回四舍五入后的圆周率，3.141593。
- sin(X)：返回X的正弦值。
- asin(X)：返回X的反正弦值。
- cos(X)：返回X的余弦值。
- acos(X)：返回X的反余弦值。
- tan(X)：返回X的正切值。
- atan(X)：返回X的反正切值。
- cot(X)：返回X的余切值。
- radians(x)：返回x由角度转化为弧度的值。
- degrees(x)：返回x由弧度转化为角度的值。
- sqrt(X)：返回X的平方根。
- exp(e,X)：返回e的x乘方的值。
- truncate(X,N)：返回小数X保留N位精准度的小数。
- format(x,y)：将x格式化位以逗号隔开的数字列表，y是结果的小数位数。
- inet_aton(ip)：将IP地址以数字的形式展现。
- inet_ntoa(number)：显示数字代表的IP地址。

## 字符串函数
- ascii(C)：返回字符C的ASCII码。
- length(S)：返回字符串的占位空间，传入“竹子爱熊猫”，返回15，一个汉字占位3字节。
- bit_length(S)：返回字符串的比特长度。
- concat(S1,S2,...)：合并传入的多个字符串。
- concat_wa(sep,S1,S2...)：合并传入的多个字符串，每个字符串之间用sep间隔。
- position(str,s) | locate(str,s)：返回s在str中第一次出现的位置，没有则返回0。
- find_in_set(S,list)：返回字符串S在list列表中的位置。
- insert(S1,start,end,S2)：使用S2字符串替换掉S1字符串中start~end的内容。
- lcase(S) | lower(S)：将传入的字符串中所有大写字母转换为小写。
- ucase(S) | upper(S)：将传入的字符串中所有小写字母转换为大写。
- left(S,index)：从左侧开始截取字符串S的index个字符。
- right(S,index)：从右侧开始截取字符串S的index个字符。
- trim(S)：删除字符S左右两侧的空格。
- rtrim(S)：删除字符S右侧的空格。
- replace(S,old,new)：使用new新字符替换掉S字符串中的old字符。
- repeat(str,count)：将str字符串重复count次后返回。
- substring(S,index,N)：截取S字符串，从index位置开始，返回长度为N的字符串。
- reverse(S)：将传入的字符串反转，即传入Java，返回avaJ。
- quote(str)：用反斜杠转移str中的英文单引号。
- strcmp(S1,S2)：比较两个字符是否相同。
- lpad(str,len,s)：对str字符串左边填充len个s字符。
- rpad(str,len,s)：对str字符串右边填充len个s字符。

## 日期和时间函数
- curdate() | current_date()：返回当前系统的日期，如2022-10-21。
- curtime() | current_time()：返回当前系统的时间，如17:30:52。
- now() | sysdate()：返回当前系统的日期时间，如2022-10-21 17:30:59。
- unix_timestamp()：获取一个数值类型的unix时间戳，如1666348711。
- from_unixtime()：将unix_timestamp()获取的数值时间戳，格式化成日期格式。
- month(date)：获取date中的月份。
- year(date)：获取date中的年份。
- hour(date)：获取date中的小时。
- minute(date)：获取date中的分钟。
- second(date)：获取date中的秒数。
- monthname(date)：返回date中月份的英文名称。
- dayname(date)：获取日期date是星期几，如Friday。
- dayofweek(date)：获取date位于一周的索引位置，周日是1、周一是2...周六是7。
- week(date)：获取date是本年的第多少周。
- quarter(date)：获取date位于一年中的哪个季度（1~4）。
- dayofyear(date)：获取date是本年的第多少天。
- dayofmonth(date)：获取date是本月的第多少天。
- time_to_sec(time)：将传入的时间time转换为秒数，比如"01:00:00" = 3600s。
- date_add(date,interval 时间 单位) | adddate(...)：将date与给定的时间按单位相加。
- date_sub(date,interval 时间 单位) | subdate(...)：将date与给定的时间按单位相减。
- addtime(date,time)：将date加上指定的时间，如addtime(now(),"01:01:01")。
- subtime(date,time)：将date减去指定的时间。
- datediff(date1,date2)：计算两个日期之间的间隔天数。
- last_day(date)：获取date日期这个月的最后一天。
- date_format(date,format)：将一个日期格式化成指定格式，format可选项如下：
    - %a：工作日的英文缩写（Sun~Sat）。
    - %b：月份的英文缩写（Jan~Dec）。
    - %c：月份的数字格式（1~12）。
    - %M：月份的英文全称（January~December）。
    - %D：带有英文后缀的数字月份（1th、2st、3nd....）。
    - %d：一个月内的天数，双数形式（01、02、03....31）。
    - %e：一个月内的天数，单数形式（1、2、3、4....31）。
    - %f：微妙（000000~999999）。
    - %H：一天内的小时，24小时的周期（00、01、02...23）。
    - %h | %I：一天内的小时，12小时的周期（01、02、03...12）。
    - %i：一小时内的分钟（00~59）。
    - %j：一年中的天数（001~366）。
    - %k：以24小时制显示时间（00~23）。
    - %l：以12小时制显示时间（01~12）。
    - %m：月份的数字形式，双数形式（01~12）。
    - %p：一天内的时间段（上午AM、下午PM）。
    - %r：12小时制的时间（12:01:09 AM）。
    - %S | %s：秒数，双数形式（00~59）。
    - %T：24小时制的时间（23:18:22）。
    - %U：一年内的周（00~53）。
- time_format(time,format)：将一个时间格式化成指定格式。
- str_to_date(str,format)：将日期字符串，格式化成指定格式。
- timestampdiff(unit,start,end)：计算两个日期之间间隔的具体时间，unit是单位：
    - year：年。
    - quarter：季度。
    - month：月。
    - week：周。
    - day：天。
    - hour：小时。
    - minute：分钟。
    - second：秒数。
    - microsecond：微妙。
- weekday(date)：返回date位于一周内的索引（0是周一...6是周日）。

## 聚合函数
聚合函数一般是会结合select、group by having筛选数据使用。

- max(字段名)：查询指定字段值中的最大值。
- min(字段名)：查询指定字段值中的最小值。
- count(字段名)：统计查询结果中的行数。
- sum(字段名)：求和指定字段的所有值。
- avg(字段名)：对指定字段的所有值，求出平均值。
- group_concat(字段名)：返回指定字段所有值组合成的结果.
- distinct(字段名)：对于查询结果中的指定的字段去重。

这里稍微介绍一个日常业务中碰到次数较多的需求：
```sql
select * from zz_users;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
|       4 | 黑熊      | 男       | 8888     | 2022-09-17 23:48:29 |
|       8 | 猫熊      | 女       | 8888     | 2022-09-27 17:22:29 |
|       9 | 棕熊      | 男       | 0369     | 2022-10-17 23:48:29 |
+---------+-----------+----------+----------+---------------------+

-- 基于性别字段分组，然后显示各组中的所有ID
select 
    convert(
        group_concat(user_id order by user_id asc separator ",") 
    using utf8) as "分组统计" 
from `zz_users` group by user_sex;
+-------------+
| 分组统计    |
+-------------+
| 1,8         |
| 2,3,4,9     |
+-------------+
上述利用了group_concat()、group by实现了按照一个字段分组后，显示对应分组的所有ID。
```

## 控制流程函数
- if(expr,r1,r2)：expr是表达式，如果成立返回r1，否则返回r2。
- ifnull(v,r)：如果v不为null则返回v，否则返回r。
- nullif(v1,v2)：如果v1 == v2，则返回null，如果不相等则返回V1。

```sql
-- if的用例
select if(user_id > 3,"√","×") from zz_users;

-- ifnull的用例
select ifnull(user_id,"×") from zz_users;

-- case语法1：
case <表达式>
   when <值1> then <操作>
   when <值2> then <操作>
   ...
   else <操作>
end;
-- 用例：判断当前时间是星期几
select case weekday(now()) 
	when 0 then '星期一' 
	when 1 then '星期二' 
	when 2 then '星期三' 
	when 3 then '星期四' 
	when 4 then '星期五' 
	when 5 then '星期六'
	else '星期天'
end as "今天是星期几？";

-- case语法2：
case
    when <条件1> then <命令>
    when <条件2> then <命令>
    ...
    else commands
end;
-- 用例：判断今天是星期几
select case
	when weekday(now()) = 0 then '星期一' 
	when weekday(now()) = 1 then '星期二' 
	when weekday(now()) = 2 then '星期三' 
	when weekday(now()) = 3 then '星期四' 
	when weekday(now()) = 4 then '星期五' 
	when weekday(now()) = 5 then '星期六'
	else '星期天'
end as "今天是星期几？";
```

简单聊一下CASE语法，第一种语法就类似于Java中的switch，而第二种语法就类似于多重if，通过CASE语法能够让SQL更加灵活，完成类似于存储过程的工作。

## 加密函数
- password(str)：将str字符串以数据库密码的形式加密，一般用在设置DB用户密码上。
- md5(str)：对str字符串以MD5不可逆算法模式加密。
- encode(str,key)：通过key密钥对str字符串进行加密（对称加密算法）。
- decode(str,key)：通过key密钥对str字符串进行解密。
- aes_encrypt(str,key)：通过key密钥对str字符串，以AES算法进行加密。
- aes_decrypt(str,key)：通过key密钥对str字符串，以AES算法进行解密。
- sha(str)：计算str字符串的散列算法校验值。
- encrypt(str,salt)：使用salt盐值对str字符串进行加密。
- decrypt(str,salt)：使用salt盐值对str字符串进行解密。

## 系统函数
- version()：查询当前数据库的版本。
- connection_id()：返回当前数据库连接的ID。
- database() | schema()：返回当前连接位于哪个数据库，即use进入的库。
- user()：查询当前的登录的所有用户信息。
- system_user()：返回当前登录的所有系统用户信息。
- session_user()：查询所有连接的用户信息。
- current_user()：查询当前连接的用户信息。
- charset(str)：返回当前数据库的编码格式。
- collation(str)：返回当前数据库的字符排序规则。
- benchmark(count,expr)：将expr表达式重复运行count次。
- found_rows()：返回最后一个select查询语句检索的数据总行数。
- cast(v as 类型)：将v转换为指定的数据类型。

顶部

MySQL命令

目录

相关推荐