Java中的MySQL函数的使用

落日映苍穹つ 2022-11-12 09:46 373阅读 0赞

### MySQL中函数的使用 ###

*  基本概念
 *  字符串函数
 *   *  char\_length()
     *  length()
     *  concat()
     *  concat\_ws()
     *  insert()
     *  upper()
     *  lower()
     *  left()
     *  right()
     *  lpad()
     *  rpad()
     *  ltrim()
     *  rtrim()
     *  trim()
     *  trim(from)
     *  repeat()
     *  space()
     *  replace()
     *  strcmp()
     *  substring()
     *  mid()
     *  locate()
     *  position(in)
     *  instr()
     *  reverse()
     *  elt()
     *  export\_set()
     *  field()
     *  find\_in\_set()
     *  make\_set()
     *  substring\_index()
     *  load\_file()
 *  条件函数
 *   *  if(expr,v1,v2)
     *  ifnull(v1,v2)
     *  case
 *  数学函数
 *   *  abs()
     *  ceil()
     *  floor()
     *  rand()
     *  sign()
     *  pi()
     *  truncate()
     *  round()
     *  pow()
     *  sqrt()
     *  exp()
     *  mod()
     *  log()
     *  log10()
     *  radians()
     *  degrees()
     *  sin()
     *  asin()
     *  cos()
     *  acos()
     *  tan()
     *  atan()
     *  cot()
 *  日期时间函数
 *   *  curdate()
     *  curtime()
     *  now()
     *  unix\_timestamp()
     *  from\_unixtime()
     *  utc\_date()
     *  utc\_time()
     *  month()
     *  monthname()
     *  dayname()
     *  dayofweek()
     *  weekday()
     *  week()
     *  dayofyear()
     *  dayofmonth()
     *  quarter()
     *  hour()
     *  minute()
     *  second()
     *  extract()
     *  time\_to\_sec()
     *  sec\_to\_time()
     *  to\_days()
     *  from\_days()
     *  datediff()
     *  adddate()
     *  date\_add()
     *  subdate()
     *  addtime()
     *  subtime()
     *  date\_format()
     *  time\_format()
     *  get\_format()
 *  系统信息函数
 *   *  version()
     *  connection\_id()
     *  database()
     *  user()
     *  charset()
     *  collation()
     *  last\_insert\_id()
 *  加密函数
 *   *  password()
     *  md5
 *  扩展函数
 *   *  格式化函数-fomat(x,n)
     *  不同进制的数字之间进行转换的函数
     *  IP地址与数字相互转换函数
     *  加锁函数和解锁函数
     *  重复执行指定操作的函数
     *  修改指定的字符集函数
     *  转换数据类型函数

# 基本概念 #

*  **MySQL**数据库中包含以下七类函数:
    
     *  字符串函数
     *  条件函数
     *  数学函数
     *  日期时间函数
     *  系统信息函数
     *  加密函数
     *  扩展函数

# 字符串函数 #

*  字符串函数用于处理**MySQL**中的字符串数据

## char\_length() ##

*  返回字符串中字符的个数

select char_length('Chova')

## length() ##

*  返回字符串的长度

select length('我是Chova')

-- 中文字符长度为2,英文字符长度为1 --
    9

## concat() ##

*  将多个字符串合并为一个字符串

select concat('Chova','Vea')

ChovaVea

## concat\_ws() ##

*  将多个字符串合并为一个字符串,并以指定的字符串连接

select concat_ws('-','Chova','Vea')

Chova-Vea

## insert() ##

*  将字符串的指定位置开始的指定长度的位置替换为给定的字符串

select insert('chova',1,1,'C')

Chova

## upper() ##

*  将字符串中的所有字符变为大写

select upper('Chova')

CHOVA

## lower() ##

*  将字符串中的所有字符变为小写字母

select lower('Chova')

chova

## left() ##

*  获取字符串前面的指定个数的字符

select left('Chova',1)

## right() ##

*  获取字符串后面的指定个数的字符

select right('Chova',2)

## lpad() ##

*  使得字符串达到指定的长度在字符串前面填充进指定的字符串

select lpad('Vea',8,'Chova')

ChovaVea

## rpad() ##

*  使得字符串达到指定的长度在字符串的后面填充进指定的字符串

select rpad('Chova',8,'Vea')

ChovaVea

## ltrim() ##

*  去掉字符串前面的空格

## rtrim() ##

*  去掉字符串后面的空格

## trim() ##

*  去掉字符串前面和后面的空格

## trim(from) ##

*  去掉前面和后面的指定字符串从指定的字符串中

select trim('Vea' from 'ChovaVea')

Chova

## repeat() ##

*  将指定的字符串重复指定的次数

select repeat('Vea',3)

VeaVeaVea

## space() ##

*  获取指定个数的空格

## replace() ##

*  替换指定字符串中的指定字符串为指定的字符串

select replace('ChovaV','V','Vea')

ChovaVea

## strcmp() ##

*  比较两个字符串

## substring() ##

*  获取指定字符串中指定位置开始的指定长度的字符串

## mid() ##

*  获取指定字符串中指定位置开始的指定长度的字符串

## locate() ##

*  获取指定字符串在指定字符串中的位置

select locate('V','ChovaVea')

## position(in) ##

*  获取指定字符串在指定字符串中的位置

select position('V' in 'ChovaVea')

## instr() ##

*  获取指定字符串中的指定字符串开始的位置

select instr('Chova','v')

## reverse() ##

*  获取指定字符串的反转字符串

select reverse('aev')

vea

## elt() ##

*  获取指定位置的字符串

select elt(2,'Chova','Vea')

Vea

## export\_set() ##

*  **export\_set(bits, on, off, bits, on, off, separator, number of bits, number of bits):** 返回一个指定处理方式的字符串
    
     *  **bits:** 将第一个参数的值转换为二进制的值.然后从右向左检查每个位上的值是**1**还是**0**
     *  **on:** 如果检查位数是**1,** 则返回这里**on**指定的字符串
     *  **off:** 如果检查位数是**0,** 则返回这里**off**指定的字符串
     *  **separator:** 指定返回的字符串的分隔符
     *  **number of bits:** 检查位数的长度.即第一个参数转换为二进制的长度,超过位数在前面用**0**补全

select export_set(5,'Y','N',','6)

-- 5的二进制: 101 --
    -- 检查的位数为6, 二进制数表示为: 000101--
    'Y','N','Y','N','N','N'

## field() ##

*  返回指定的字符串在后面的字符串中匹配的位置

select field('Vea','Chova','Vea')

## find\_in\_set() ##

*  **find\_in\_set(’’,’’):** 返回指定字符串在指定字符串中匹配的位置

## make\_set() ##

*  **MAKE\_SET(bits,str1,str2,…):** 返回一个指定处理方式获取的字符串
    
     *  **bits:** 将第一个参数的值转换为二进制,可能包含逻辑运算.然后从右向左检查每个位上的值是**1**还是**0**
     *  **str:** 每检查一个位后则顺延到下一的字符串

select make_set(1|4,'Chova','Vea')

Chova

## substring\_index() ##

*  **substring\_index(‘s’,‘x’,count):** 返回从指定字符串中按照指定分隔符分隔的指定位置分隔的字符串
    
     *  **count**大于**0,** 返回指定位置分隔符左边的字符串
     *  **count**小于**0,** 返回指定位置分隔符右边的字符串

select substring_index('Chova-Vea','-', 1)

Chova

## load\_file() ##

*  读入文件并以字符串的形式返回文件内容
    
     *  文件必须在服务器上
     *  必须指定到文件的完整路径名
     *  人员必须拥有文件**file**权限
     *  文件必须所有内容都是可读取的并且小于**max\_allowed\_packet**
 *  如果文件不存在或者由于上述原因无法读出则返回**null**

# 条件函数 #

*  条件函数用于**MySQL**中的条件判断

## if(expr,v1,v2) ##

*  如果**expr**条件成立,返回结果**v1**
 *  如果**expr**条件不成立,返回结果**v2**

select if(1>0,'Y','N')

## ifnull(v1,v2) ##

*  如果**v1**的值不为**null,** 则返回**v1**
 *  如果**v1**的值为**null,** 则返回**v2**

select ifnull(null,'Chova')

Chova

## case ##

*  **case**表示函数的开始 **,end**表示函数的结束
    
     *  如果**e1**成立,则返回**v1**
     *  如果**e2**成立,则返回**v2**
     *  如果全部都不成立,则返回**v**
 *  当有一个成立之后,后面的语句就不再执行

case 
    	when e1 then v1
    	when e2 then v2
    	...
    	else v
    end

*  **case**表示函数的开始 **,end**表示函数的结束
    
     *  如果表达式的值等于**e1,** 则返回**v1**
     *  如果表达式的值等于**e2,** 则返回**v2**
 *  当有一个成立之后,后面的语句就不再执行

case expr
    	when e1 then v1
    	when e2 then v2
    	...
    	else v
    end

# 数学函数 #

*  数学函数主要用来处理**MySQL**中的数值处理,包括整数和浮点数等

## abs() ##

*  获取数值的绝对值

select abs(-6)

## ceil() ##

*  获取数值的向上取整

select ceil(5.6)

## floor() ##

*  获取数值的向下取证

select floor(6.6)

## rand() ##

*  获取一个**0**和**1**之间的随机数
 *  **rand(x):** 获取一个**0**和**1**之间的随机数,同一个模块下 **,x**值相同时获取到的随机数相同

select rand()

0.93099315644334

select rand(6)
    
    select rand(6)

0.713759689954247
    
    0.713759689954247

## sign() ##

*  获取数值的符号
    
     *  如果数值是负数,则返回\*\*-1\*\*
     *  如果数值是**0,** 则返回**0**
     *  如果数值是正数,则返回**1**

select sign(6)

## pi() ##

*  获取一个圆周率的值

select pi()

3.141593

## truncate() ##

*  获取指定数值保留小数点后面的指定位数.直接获取,不进行四舍五入操作

select truncate(3.141593,2)

3.14

## round() ##

*  获取数值的整数.按照四舍五入进行获取

select round(6.36)

*  **round(x,n):** 获取指定数值保留小数点后面的指定位数.按照四舍五入进行获取

select round(6.686, 2)

6.69

## pow() ##

*  获取指定数值的指定幂次方的值

select pow(2,3)

## sqrt() ##

*  获取指定数值的平方根

select sqrt(36)

## exp() ##

*  获取**e**值的指定幂次方的值

select exp(3)

20.085536923188

## mod() ##

*  获取指定的两个数相除后的余数

select mod(6,5)

## log() ##

*  获取指定数值的自然对数,即以**e**为底

select log(20.085536923188)

## log10() ##

*  获取指定数值以**10**为底的对数

select log10(100)

## radians() ##

*  获取指定角度的弧度

select radians(180)

3.1415926535898

## degrees() ##

*  获取指定弧度的角度

select degrees(3.1415926535898)

180

## sin() ##

*  获取指定弧度的正弦值

select sin(radians(30))

0.5

## asin() ##

*  获取指定弧度的反正弦值

## cos() ##

*  获取指定弧度的余弦值

## acos() ##

*  获取指定弧度的反余弦值

## tan() ##

*  获取指定弧度的正切值

## atan() ##

*  获取指定弧度的反正切值

## cot() ##

*  获取指定弧度的余切值

# 日期时间函数 #

*  日期时间函数主要用于处理**MySQL**中的日期时间

## curdate() ##

*  获取当前的日期

select curdate()

2021-04-02

## curtime() ##

*  获取当前的时间

select curtime()

16:16:16

## now() ##

*  获取当前的日期和时间

select now()

2021-04-02 16:16:16

## unix\_timestamp() ##

*  获取当前时间的时间戳格式

select unix_timestamp()

1617351376

*  **unix\_timestamp(d):** 获取指定日期时间的时间戳格式

select unix_timestamp('2021-04-02 16:16:16')

1617351376

## from\_unixtime() ##

*  获取指定时间戳的日期时间格式

select from_unixtime(1617351376)

2021-04-02 16:16:16

## utc\_date() ##

*  获取**UTC**日期

select utc_date()

2021-04-02

## utc\_time() ##

*  获取**UTC**时间

select utc_time()

08:16:16

## month() ##

*  获取指定日期时间所属的月份,值在**1**和**12**之间

select month('2021-04-02 16:16:16')

## monthname() ##

*  获取指定日期时间所属的月份名称

select monthname('2021-04-02 16:16:16')

April

## dayname() ##

*  获取指定日期时间所属的星期的名称

select dayname('2021-04-02 16:16:16')

Friday

## dayofweek() ##

*  获取指定日期时间是一周中的第几天 **.1**和**7**表示星期日到星期六

select dayofweek('2021-04-02 16:16:16')

## weekday() ##

*  获取指定日期时间是一周中的第几天 **.0**和**6**表示星期一到星期日

select weekday('2021-04-02 16:16:16')

## week() ##

*  获取指定日期时间是当年的第几个星期.值在**0**和**53**之间

select week('2021-04-02 16:16:16')

## dayofyear() ##

*  获取指定日期时间是当年的第几天

select dayofyear('2021-04-02 16:16:16')

## dayofmonth() ##

*  获取指定日期时间是当月的第几天

select dayofmonth('2021-04-02 16:16:16')

## quarter() ##

*  获取指定日期时间是当年的第几个季度

select quarter('2021-04-02 16:16:16')

## hour() ##

*  获取指定日期或时间中的小时值

select hour('16:16:16')

## minute() ##

*  获取指定日期或时间中的分钟值

select minute('16:16:16')

## second() ##

*  获取指定日期或之间中的秒钟值

select second('16:16:16')

## extract() ##

*  **extract(type from d):** 获取指定类型在指定日期时间中的值
    
     *  **type**的值:
        
         *  **microsecond**
         *  **second**
         *  **minute**
         *  **hour**
         *  **day**
         *  **week**
         *  **month**
         *  **quarter**
         *  **year**
         *  **second\_microsecond**
         *  **minute\_microsecond**
         *  **minute\_second**
         *  **hour\_microsecond**
         *  **hour\_second**
         *  **hour\_minute**
         *  **day\_microsecond**
         *  **day\_second**
         *  **day\_minute**
         *  **day\_hour**
         *  **year\_month**

## time\_to\_sec() ##

*  获取指定时间的秒值

select time_to_sec('16:16:16')

58576

## sec\_to\_time() ##

*  获取指定的秒值转换的时间的值

select sec_to_time(58576)

16:16:16

## to\_days() ##

*  获取距离**0000**年**01**月**01**日的天数

select to_days('2021-04-02 16:16:16')

738247

## from\_days() ##

*  获取距离**0000**年**01**月**01**日后指定天数的日期

select from_days(738247)

2021-04-02

## datediff() ##

*  获取指定的两个日期之间相隔的天数. 前面的日期减去后面日期的天数

select datediff('2021-05-02', '2021-04-02')

## adddate() ##

*  获取指定日期时间之后指定天数的日期时间

select adddate('2021-04-02', 30)

2021-05-02

*  **adddate(d, interval expr type):** 获取指定日期时间之后指定时间后的日期时间
    
     *  **type**的值:
        
         *  **microsecond**
         *  **second**
         *  **minute**
         *  **hour**
         *  **day**
         *  **week**
         *  **month**
         *  **quarter**
         *  **year**
         *  **second\_microsecond**
         *  **minute\_microsecond**
         *  **minute\_second**
         *  **hour\_microsecond**
         *  **hour\_second**
         *  **hour\_minute**
         *  **day\_microsecond**
         *  **day\_second**
         *  **day\_minute**
         *  **day\_hour**
         *  **year\_month**

## date\_add() ##

*  **date\_add(d, interval expr type):** 获取指定日期时间之后指定时间的日期时间
    
     *  **type**的值:
        
         *  **microsecond**
         *  **second**
         *  **minute**
         *  **hour**
         *  **day**
         *  **week**
         *  **month**
         *  **quarter**
         *  **year**
         *  **second\_microsecond**
         *  **minute\_microsecond**
         *  **minute\_second**
         *  **hour\_microsecond**
         *  **hour\_second**
         *  **hour\_minute**
         *  **day\_microsecond**
         *  **day\_second**
         *  **day\_minute**
         *  **day\_hour**
         *  **year\_month**

## subdate() ##

*  获取指定日期时间之前指定天数的时间

select subdate('2021-05-02',30)

2021-04-02

*  **subdate(d, interval expr type):** 获取指定日期时间之前指定时间的日期时间
    
     *  **type**的值:
        
         *  **microsecond**
         *  **second**
         *  **minute**
         *  **hour**
         *  **day**
         *  **week**
         *  **month**
         *  **quarter**
         *  **year**
         *  **second\_microsecond**
         *  **minute\_microsecond**
         *  **minute\_second**
         *  **hour\_microsecond**
         *  **hour\_second**
         *  **hour\_minute**
         *  **day\_microsecond**
         *  **day\_second**
         *  **day\_minute**
         *  **day\_hour**
         *  **year\_month**

## addtime() ##

*  获取指定日期时间之后指定秒数的日期时间

select addtime('2021-04-02 16:16:16',20)

2021-04-02 16:16:36

## subtime() ##

*  获取指定日期时间之前指定秒数的日期时间

select subtime('2021-04-02 16:16:16',10)

2021-04-02 16:16:06

## date\_format() ##

*  获取指定日期时间的指定格式输出的日期时间

select date_format('2021-04-02 16:16:16','%Y-%m-%d %r')

2021-04-02 04:16:16 PM

## time\_format() ##

*  获取指定时间的指定格式输出的时间

select time_format('06:16:16','%r')

06:16:16 AM

## get\_format() ##

*  **get\_format(type,s):** 获取指定时间在指定国家地区的输出格式
    
     *  **type**的值:
        
         *  **microsecond**
         *  **second**
         *  **minute**
         *  **hour**
         *  **day**
         *  **week**
         *  **month**
         *  **quarter**
         *  **year**
         *  **second\_microsecond**
         *  **minute\_microsecond**
         *  **minute\_second**
         *  **hour\_microsecond**
         *  **hour\_second**
         *  **hour\_minute**
         *  **day\_microsecond**
         *  **day\_second**
         *  **day\_minute**
         *  **day\_hour**
         *  **year\_month**

select get_format(date, 'usa')

%m.%d.%Y

# 系统信息函数 #

*  系统信息函数用来查询**MySQL**数据库相关的系统信息

## version() ##

*  获取数据库的版本号

## connection\_id() ##

*  返回服务器的连接数

## database() ##

*  **database()**
 *  **schema**
    
     *  获取当前的数据库名称

## user() ##

*  **user()**
 *  **system\_user()**
 *  **session\_user()**
 *  **current\_user()**
 *  **current\_user**
    
     *  获取数据库的当前用户

## charset() ##

*  获取指定字符串的字符集

## collation() ##

*  获取指定字符串的字符排列方式

## last\_insert\_id() ##

*  获取最新生成的**AUTO\_INCREMENT**的值

# 加密函数 #

*  加密函数用来对**MySQL**中的数据进行加密

## password() ##

*  获取指定字符串的加密后的值

select password('Chova')

*8C4103E52796CCF4A350218583E396B47CB15311

## md5 ##

*  **md5(str):** 可以对字符串进行散列,可以用于对一些不需要进行解密的数据进行加密

select md5('Chova')

8c94d8f085f29ee2785e796e60e13c07

# 扩展函数 #

## 格式化函数-fomat(x,n) ##

*  获取指定数字的小数点后面的指定位数

select format(3.141592643,5)

3.14159

## 不同进制的数字之间进行转换的函数 ##

*  **ASCII(s):** 返回字符串第一个字符的**ASCII**码值
 *  **BIN(x):** 返回**x**的二进制编码
 *  **HEX(x):** 返回**x**的十六进制编码
 *  **OCT(x):** 返回**x**的八进制编码
 *  **CONV(x,f1,f2):** 返回**f1**进制数变为**f2**进制数

## IP地址与数字相互转换函数 ##

*  **inet\_aton(ip):** 获取指定**IP**字符串的数值表示

select inet_aton('192.168.0.1')

3232235521

*  **inet\_ntoa(n):** 获取指定数值的**IP**字符串表示

select inet_ntoa(3232235521)

192.168.0.1

## 加锁函数和解锁函数 ##

*  **get\_lock(name,time):** 定义一个名称为**name,** 持续时间长度为**time**的锁
    
     *  如果锁定成功,则返回**1**
     *  如果尝试超时,则返回**0**
     *  如果发生错误,则返回**null**

select get_lock('mysql',10)

*  **release\_lock(name):** 释放函数名为**name**的锁
    
     *  如果释放成功,则返回**1**
     *  如果尝试超时,则返回**0**
     *  如果发生错误,则返回**null**

select release_lock('mysql')

*  **is\_free\_lock(name):** 判断是否已经使用过名称为**name**的锁
    
     *  如果已经使用过,则返回**1**
     *  如果未使用过,则返回**0**

select is_free_lock('mysql')

## 重复执行指定操作的函数 ##

*  **benchmark(count,expr):** 将指定的表达式**expr**操作重复执行**count**次,然后返回执行时间. 可以用来判断**MySQL**处理表达式的速度

select benchmark(10000,now())

## 修改指定的字符集函数 ##

*  **convert(s using charset):** 将指定字符串**s**的字符集变为**charset**字符

select charset(convert('Chova' using utf-8))

utf-8

## 转换数据类型函数 ##

*  **cast(x as type)**
 *  **convert(x as type)**
    
     *  转换数据类型只对以下数据类型生效:
        
         *  **binary**
         *  **char**
         *  **date**
         *  **datetime**
         *  **time**
         *  **signed integer**
         *  **unsigned integer**

select cast('565' as unsigned integer) + 1

566

-- MySQL可以默认将字符串类型转换为数字类型 --
    select '565'+1

566

select convert(now(),date)

2021-04-02