java全栈学习之路——mysql(3)

Mysql

函数

一、单行函数：

  COUNT(): 查询表中数据条数
  AVG(): 求平均数
  SUN(): 求和
  MAX(): 求最大值
  MIN(): 求最小值
  字符串函数：
  LOWER/UPPER: 将查询出来的结果小写/大写
  CONCAT: 连接字符串
  CHAR_LENGTH/LENGTH: 查询字符串长度
  LPAD/RPAD: 左右填充
  LTRIM/RTRIM/TRIM: 去掉左/右/左右空格。中间无法去除
  REPLACE: 替换
  SUBSTRING: 截取字符串
  数字函数
  ABS/MOD：绝对值/取余
  CEIL: 大于等于其本身的整数值
  FLOOR: 小于等于其本身的整数值
  ROUND: 四舍五入
  日期函数：
  NOW(): 当前时间
  CURDATE(): 当前日期
  CURTIME(): 当前时间
  Extract()：选取日期时间的各个部分
  last_day()： 当前月份最后一天
  日期函数计算：
  DATE_ADD(): 时间相加
  DATE_SUB(): 时间相减
  datediff()：日期相减 
  timediff()：时间相减
  str_to_date(): 字符串转换为日期
  date_format(),time_format()： 日期转为字符串

例子：

字符串函数

1. LOWER/UPPER

SELECT LOWER("AAbccaH") FROM DUAL; #aabccah
SELECT UPPER("AAbccaH") FROM DUAL; #AABCCAH

2. concat 拼接字符

SELECT CONCAT('666','_','111') FROM DUAL; #666_111
SELECT CONCAT('666','111') FROM DUAL;     #666111

3. CHAR_LENGTH/LENGTH

SELECT LENGTH("AAbccaH") FROM DUAL; #7

4. LPAD/RPAD

SELECT LPAD("a","4","cc") FROM DUAL; #ccca
SELECT RPAD("a","4","cc") FROM DUAL; #accc

5. LTRIM/RTRIM/TRIM

SELECT TRIM(' a b c ') FROM DUAL; #a b c
SELECT LTRIM(' a b c ') FROM DUAL; #a b c
SELECT RRIM(' a b c ') FROM DUAL; # a b c

6. REPLACE

//语法
//replace(object,search,replace)
SELECT REPLACE("aaaa","a","c") FROM DUAL; #cccc

7. SUBSTRING

//substring(str, pos)，即：substring(被截取字符串， 从第几位开始截取)
//substring(str, pos, length)，即：substring(被截取字符串，从第几位开始截取，截取长度)
SELECT SUBSTRING("abcd" , 2) FROM DUAL; #bcd
SELECT SUBSTRING("abcd" , 2,2) FROM DUAL; #bc

数字函数

1. ABS/MOD

SELECT ABS(-2) FROM DUAL; #2
SELECT MOD(2,3) FROM DUAL; #2

2. CEIL/FLOOR

SELECT CEIL(102.3) FROM DUAL; #103
SELECT CEIL(102.3) FROM DUAL; #102

3. ROUND

SELECT ROUND(102.3) FROM DUAL; #102
SELECT ROUND(102.5) FROM DUAL; #103

日期函数

1. NOW()/CURDATE()/CURTIME()/Extract()/last_day()

SELECT NOW(),CURDATE(),CURTIME(),EXTRACT(HOUR FROM NOW()),LAST_DAY(NOW()) FROM DUAL;
# '2019-09-16 17:27:59' '2019-09-16' '17:27:59' '17' '2019-09-30'

2. DATE_ADD()/DATE_SUB()

//SELECT DATE_ADD(date,INTERVAL expr unit) FROM DUAL;
SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 MONTH) FROM DUAL; # 2019-09-16 17:37:31 2019-10-16 17:37:31
SELECT NOW(),DATE_SUB(NOW(),INTERVAL 1 MONTH) FROM DUAL; # 2019-09-16 17:40:10 2019-08-16 17:40:10

3. datediff(),timediff() 注意前后格式相同

SELECT DATEDIFF(NOW(),NOW()) FROM DUAL; #0
SELECT TIMEDIFF(NOW(),NOW()) FROM DUAL; #00:00:00
SELECT TIMEDIFF(CURDATE(),"2016-2-3") FROM DUAL; #838:59:59

4. str_to_date()后面与前面对应

SELECT STR_TO_DATE("2018 9 10","%Y %m %d") FROM DUAL; # 2018-09-10
SELECT STR_TO_DATE('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); # 2008-08-09 08:09:30

5. date_format(),time_format()

SELECT DATE_FORMAT('2018-1-2 22:23:01', '%Y年%m月%日 %H时%i分%s秒'); #2018年01月日 22时23分01秒
SELECT TIME_FORMAT('22:23:01', '%H时%i分%s秒'); #22时23分01秒

6. mysql时区转换

//SELECT CONVERT_TZ(dt,from_tz,to_tz);
SELECT NOW(),CONVERT_TZ(NOW(),"+08:00","+00:00");# 2019-09-16 18:02:25 2019-09-16 10:02:25