数据库的常用操作

字符串函数数学函数日期时间时间类型格式

字符串函数

语法作用备注LOWER(string)转换为小写UPPER(string)转换为大写SUBSTR(string,startposition,length)截取字符串INSERT(str,pos,len,newstr)插入字符串INSTR(string1,string2)子串的位置str_to_date (string[,format])字符串转换为日期格式format日期时间格式LPAD(str,len,padstr)左填充字符串RPAD(str,len,padstr)右填充字符串LTRIM(string)去除左边的空格RTRIM(string)去除右边的空格TRIM(BOTH remstr FROM str)在str中删除所有的remstrBOTH可省略TRIM(LEADING remstr FROM str)在str中删除开头的remstrTRIM(TRAILING remstr FROM str)在str中删除结尾的remstrIFNULL(key,0)空值填补如果key字段为空置为0LENGTH(str)计算字符串长度SELECT DISTINCT(expr)过滤expr中的相同项

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tZ9uWme6-1603191468690)(字符串.jpg)]

数学函数

语法作用备注MAX(expr)最大值从expr式子或者字段中获取最大值MIN(expr)最小值从expr式子或者字段中获取最小值SUM(expr)求和AVG(expr)求平均值COUNT(expr)计数ROUND(X,D)四舍五入对X四舍五入，保留D位小数TRUNCATE(X,D)保留小数位对X直接保留D位小数MOD(N,M)取余N%MFLOOR(X)向上取整CEIL(X)向下取整ABS(X)绝对值SQRT(X)开平方POW(X,Y)求幂X的Y次方log(X)求自然对数等价于lgX/lgeLOG(b,X)求对数等价于lgX/lgbSIGN(X)符号函数X为负返回-1,为正返回1,为0显示0

样例

SELECT ABS(-15) "绝对值" ; SELECT MOD(7,5) "modulo 取余" ; SELECT ROUND (15.193,1) "round 四舍五入保留一位" ; SELECT TRUNCATE(15.79,1) "truncate 直接保留一位" ; SELECT CEIL(10.6) "取上限" ; SELECT FLOOR(10.6) "取下限" ; SELECT SQRT(25) "开方" ; SELECT POW(5,3) ; SELECT LOG(2,16); SELECT SIGN(-5),SIGN(0),SIGN(4);

---

日期时间

(不区分大小写，now()和NOW()一样)

语法功能备注NOW()当前时间返回：2020-10-19 21:00:00HOUR/MINUTE/SECOND(time)获取时/分/秒YEAR/MONTH/DAY(DATE)获取年/月/日DATE_ADD(DATE, INTERVAL expr TYPE)日期相加DATE_FORMAT (date,format)日期格式化LAST_DAY(date)该月的最后一天返回：2020-10-31TIMESTAMPDIFF(INTERVAL,datetime_expr1,datetime_expr2)时间单位为INTERVAL之差expr2-expr1WEEKDAY(DATE)与星期一的相差天数返回0代表周一，返回6代表周日

时间类型

UnitExpression备注DAYDAYSDAY_HOUR‘DAYS HOURS’DAY_MICROSECOND‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’DAY_MINUTE‘DAYS HOURS:MINUTES’DAY_SECOND‘DAYS HOURS:MINUTES:SECONDS’HOURHOURSHOUR_MICROSECOND‘HOURS:MINUTES:SECONDS.MICROSECONDS’HOUR_MINUTE‘HOURS:MINUTES’HOUR_SECOND‘HOURS:MINUTES:SECONDS’MICROSECONDMICROSECONDSMINUTEMINUTESMINUTE_MICROSECOND‘MINUTES:SECONDS.MICROSECONDS’MINUTE_SECOND‘MINUTES:SECONDS’MONTHMONTHSQUARTERQUARTERSSECONDSECONDSSECOND_MICROSECOND‘SECONDS.MICROSECONDS’WEEKWEEKSYEARYEARSYEAR_MONTH‘YEARS-MONTHS’

格式

DATE_FORMAT stringFormatted date备注%a星期的缩写星期一显示：Mon%b月份的缩写10月：Oct%c月份的数字10月：10%d日期的数字19日：19%D日期的缩写19日：19th%e日期的数字19日：19%H24小时制的小时22点：22%h12小时制的小时22点：10%i两位数的分钟22时4分：04%l一位数的月份7月：7%m月份的数字10月：10%M月份的全称10月：October%p显示早晚AM/PM22时：PM%T时分秒22时4分55秒：22:06:55%W星期的全称星期一显示：Monday%w星期的数字星期一显示： 1%Y四位数的年份2020年：2020%y两位数的年份2020年：20

日期格式

DATE_FORMAT stringFormatted date%Y-%m-%d7/4/2019%e/%c/%Y4/7/2019%c/%e/%Y7/4/2019%d/%m/%Y4/7/2019%m/%d/%Y7/4/2019%e/%c/%Y %H:%i4/7/2019 11:20%c/%e/%Y %H:%i7/4/2019 11:20%d/%m/%Y %H:%i4/7/2019 11:20%m/%d/%Y %H:%i7/4/2019 11:20%e/%c/%Y %T4/7/2019 11:20%c/%e/%Y %T7/4/2019 11:20%d/%m/%Y %T4/7/2019 11:20%m/%d/%Y %T7/4/2019 11:20%a %D %b %YThu 4th Jul 2019%a %D %b %Y %H:%iThu 4th Jul 2019 11:20%a %D %b %Y %TThu 4th Jul 2019 11:20:05%a %b %e %YThu Jul 4 2019%a %b %e %Y %H:%iThu Jul 4 2019 11:20%a %b %e %Y %TThu Jul 4 2019 11:20:05%W %D %M %YThursday 4th July 2019%W %D %M %Y %H:%iThursday 4th July 2019 11:20%W %D %M %Y %TThursday 4th July 2019 11:20:05%l:%i %p %b %e, %Y7/4/2019 11:20%M %e, %Y4-Jul-19%a, %d %b %Y %TThu, 04 Jul 2019 11:20:05

---

样例

SELECT HOUR("2020-12-31 20:13:14") "提取小时"; SELECT MINUTE("2020-12-31 20:13:14") "提取分钟"; SELECT SECOND("2020-12-31 20:13:14") "提取秒钟"; SELECT DATE("2020-12-31 20:13:14") "提取日期"; SELECT YEAR("2020-12-31 20:13:14") "提取年份"; SELECT MONTH("2020-12-31 20:13:14") "提取月份"; SELECT TIMESTAMPDIFF(DAY,"2020-2-29",NOW()); SELECT LAST_DAY(NOW()) "本月的最后一天"; SELECT LAST_DAY("2020-9-21") "九月份的最后一天"; SELECT HOUR(NOW()); SELECT MINUTE(NOW()); SELECT DATE_FORMAT("2020-12-31 20:13:14","%Y-%m-%d") "格式1"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%Y/%m/%d") "格式2"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%e-%c-%Y") "格式3.1"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%c-%e-%Y") "格式3.2"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%d-%m-%Y") "格式3.3"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%m-%d-%Y") "格式3.4"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%Y-%m-%d %T") "格式4"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%Y-%m-%d %H") "格式4.1"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%Y-%m-%d %H:%i") "格式4.2"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%Y-%m-%d %H:%i:%s") "格式4.3"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%a %b %e %Y") "格式5"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%a %b %e %Y %H:%i") "格式5.1.0"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%W %b %e %Y %H:%i") "格式5.1.1"; SELECT DATE_FORMAT("2020-12-1 20:13:14","%W %M %e %Y %H:%i") "格式5.1.2"; SELECT DATE_FORMAT("2020-12-1 20:13:14","%W %M %d %Y %H:%i") "格式5.1.3"; SELECT DATE_FORMAT("2020-2-1 2:3:4","%W %D %M %Y") "格式5.2"; SELECT DATE_FORMAT("2020-12-31 20:13:14","%M %e,%Y") "格式5.3";