MYSQL 学习笔记（二）

MYSQL 学习笔记二：函数篇

本篇笔记收录整理SQL语句中常用的字符串函数，算术函数，聚合函数以及日期类函数。

DUAL说明

NOTE - when you wish to Select something, but the data is not in a table (as the examples below), you can use a dummy table name called DUAL. This table is only recognised by Mysql as a dummy table, and will never appear as an actual structure. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.

SQL FUNCTIONS

分类

​ string functions for searching and manipulating strings.

​ arithmetic functions for performing calculations on numeric values

​ date functions for reformatting and performing data arithmetic

​ aggregate functions for calculations on groups of data.

---

Useful String Functions

1. 大小写转化

方法	说明
LOWER(string)	converts upper case alphabetic characters to lower case. Other characters are not affected
UPPER(string)	converts lowercase letters in a string to uppercase

2. 切割字符串

方法	说明
**SUBSTR(**string,startposition,length)	shows a part of the string starting at the start position of the specified length

案例：

SELECT SUBSTR('ABCDEF',2,3) FROM dual;
# gives BCD

注意这里的起始坐标为1。

3. 定位子串下标

方法	说明
**INSTR(**string1,string2)	finds the start position of one string inside another string

案例：

SELECT INSTR('ABCDEF', 'DEF') FROM dual;
# gives 4

4. 字符串转日期格式

方法	说明
str_to_date (string[,format])	converts the string to a date. A format may optionally be specified (see later)

案例：

SELECT STR_TO_DATE('12-06-1996', ' %d-%m-%Y ') FROM dual;

5. 左右侧填充

方法	说明
LPAD(str,len,padstr)	left pads the string with the specified fill characters to the specified length
RPAD(str,len,padstr)	right pads the string with the specified fill characters to the specified length

案例：

SELECT LPAD('hi',4,'??');
# gives  '??hi'

6. 左右侧/两端去除特定字符

方法	说明
**LTRIM(**string)	Returns the string str with leading space characters removed
**RTRIM(**string)	Returns the string str with trailing space characters removed
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)	Returns a string after removing all prefixes or suffixes from the given string

案例：

SELECT LTRIM('   barbar');
#GIVE 'barbar'

7. 表达式为空的处理函数

方法	说明
IFNULL(expression1, expression2);	takes two expressions and if the first expression is not NULL, it returns the first expression. Otherwise, it returns the second expression.

案例：

SELECT *, IFNULL(Comm, 0) FROM EMP;

8. 字符串长度

方法	说明
**LENGTH(**char)	length in characters of specified string

注意事项：

you must put single quotes round all data items which are strings.

---

Arithmetic Functions

基本函数：

方法	说明
ABS(numeric)	absolute value of the number
MOD(num1, num2)	returns the remainder when num1 is divided by num2
ROUND(numeric[,d])	rounds the number to d decimal places, the rounding can occur to either side of the decimal point
TRUNCATE(numeric[,d]])	truncates to d decimal places
CEIL(numeric)	rounds the number up to the nearest integer
FLOOR(numeric)	truncates the number to the nearest integer
SQRT(numeric)	returns the square root of the number (returns NULL if the number is negative)
TO_CHAR(numeric[,format])	converts a number to a character string in the specified format
DATE_FORMAT(date,format)	Cut and Paste date_format strings for MySQL

举例：

SELECT ROUND(1234.5678,2) FROM dual;

SELECT TO_CHAR(sysdate(),'DY') FROM dual;
SELECT TO_CHAR(sysdate(),'MONTH') FROM dual;

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
SELECT DATE_FORMAT(NOW(),'%m')

注意：

IT IS VERY IMPORTANT TO NOTE THAT IF ANY VARIABLE CONTAINS A NULL VALUE THEN ANY SQL STATEMENT INVOLVING ARITHMETIC WILL IGNORE IT

E.G.

SELECT ABS(SAL-COMM) FROM EMP;

will only produce results for employees who have a non-null commission (or salary)

补充函数：

List of Mathematical Functions

Functions	Example	Output
abs ( )	abs(-115.36)	115.36
cbrt( )	cbrt ( 343)	7
ceil( )	ceil(-53.7 )	-53
ceiling( )	ceiling(-69.8 )	-69
degrees( )	degrees( .45)	25.783100780887
div( )	div( 19 , 3)	6
exp( )	exp( 2.0 )	7.3890560989306502
floor( )	floor(-53.6 )	-54
ln( )	ln( 3.0)	1.0986122886681097
log( )	log(200.0 )	2.3010299956639812
mod( )	mod( 38,5)	3
pi( )	pi( )	3.14159265358979
power( )	power(7,3 )	343
radians( )	radians(15.0 )	0.261799387799149
random( )	random( )
round( )	round( 67.456)	67
sign( )	sign(-14.321 )	-1
sqrt( )	sqrt(225 )	15
trunc( )	trunc(67.456 )	67

List of Trigonometric Functions

Functions	Syntax	Description
acos ( )	acos(a)	Returns inverse cosine or arc cosine.
asin( )	asin(a)	Returns inverse sine or arc sine.
atan( )	atan(a)	Returns inverse tangent or arc tangent.
atan2( )	atan2(b,a)	Returns inverse tangent of b/a.
cos( )	cos(a)	Returns cosine.
cot( )	cot(a)	Returns cotangent.
sin( )	sin(a)	Returns sine.
tan( )	tan(a)	Returns tangent.

---

AGGREGATE OR GROUPING FUNCTIONS

Name	Description
COUNT	This function returns the number or rows or non NULL values for a column
SUM	This function returns the sum of a selected column.
MAX	This function returns the largest value of a specific column.
MIN	This function returns the smallest value of a specific column.
AVG	This function returns the average value for a specific column.

注意：

还是注意那个null字段，默认被忽视。

NOTE - because Comm can contain a NULL value, be warned that if it does, SQL cannot evaluate it as an arithmetic expression and will ignore that record. Thus the above will obtain a sum for all Salesmen because they all get commission.

Date and Time Functions

1. AGE() function

The age() function subtract arguments, producing a “symbolic” result that uses years and months.

Syntax:

age(timestamp, timestamp)
or
age(timestamp)

Return Type: interval

PostgreSQL Version: 9.3

Pictorial Presentation of PostgreSQL AGE() function

Example 1: PostgreSQL AGE() function

The example below finds the age between two dates specified in the argument.

Code

SELECT age(timestamp '2015-01-15', timestamp '1972-12-28');

Copy

Sample Output:

       age
------------------
 42 years 18 days
(1 row)

Example 2:

The example below finds the age between current date and the date as specified in the argument.

Code

SELECT age(timestamp '2007-10-07');

Sample Output:

          age
-----------------------
 7 years 3 mons 7 days
(1 row)

2. 当前时间

方法	说明
clock_timestamp()	shows current date and time (changes during statement execution)
current_date()	This function is used to return the current date.
current_time()	This function is used to return the current time with a zone.
current_timestamp()	This function is used to return the current date and time.

3. 截取部分信息

方法	说明
date_part(text, timestamp) date_part(text, interval)	The date_part() function is used to return the part of a date and time.

4. 截断时间

方法	说明
date_trunc(text, timestamp)	The date_trunc() function is used to truncate to specified precision.

举例：

SELECT date_trunc('hour', timestamp '2002-09-17 19:27:45');

Output:

 2002-09-17 19:00:00

5. 日期内的单位转化

方法	说明
justify_days(interval)	This function is used to adjust interval so 30-day time periods are represented as months.
justify_hours(interval)	This function is used to adjust interval so 24-hour time periods are represented as days.
justify_interval(interval)	This function is used to adjust interval using justify_days and justify_hours, with additional sign adjustments.

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0XhcXERj-1633075384578)(https://www.w3resource.com/w3r_images/postgresql-justify_days-function.png)]

6. 本地时间

方法	说明
localtimestamp	This function is used to get current date and time (start of current transaction).
now()	This function is used to get current date and time (start of current transaction).
statement_timestamp()	This function is used to get current date and time (start of current transaction).
timeofday()	This function is used to get current date and time (like clock_timestamp, but as a text string).

结果：

        timestamp
-------------------------
 2015-01-15 14:02:56.925

            now
----------------------------
 2015-01-15 14:17:25.919-08

    statement_timestamp
----------------------------
 2015-01-15 14:30:28.322-08

              timeofday
-------------------------------------
 Thu Jan 15 14:47:28.937000 2015 PST

实验指导书内的重要方法

1. DATE_FORMAT (date,format)
   It presents a date in the specified format

SELECT DATE_FORMAT(now(), ‘%d-%m-%Y’);
See detailed instruction of DATE_FORMAT attached in the following pages

2. DATE_ADD (start_date, INTERVAL expr unit)
   or start_date + INTERVAL expr unit

It adds an interval to a DATE or DATETIME. Specifically, start_date is a starting DATE or DATETIME value; INTERVAL expr unit is an interval value to be added to the starting date value.

​ SELECT DATE_ADD(now(), INTERVAL 1 day);

Unit	Expression
DAY	DAYS
DAY_HOUR	‘DAYS HOURS’
DAY_MICROSECOND	‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_MINUTE	‘DAYS HOURS:MINUTES’
DAY_SECOND	‘DAYS HOURS:MINUTES:SECONDS’
HOUR	HOURS
HOUR_MICROSECOND	‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_MINUTE	‘HOURS:MINUTES’
HOUR_SECOND	‘HOURS:MINUTES:SECONDS’
MICROSECOND	MICROSECONDS
MINUTE	MINUTES
MINUTE_MICROSECOND	‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND	‘MINUTES:SECONDS’
MONTH	MONTHS
QUARTER	QUARTERS
SECOND	SECONDS
SECOND_MICROSECOND	‘SECONDS.MICROSECONDS’
WEEK	WEEKS
YEAR	YEARS
YEAR_MONTH	‘YEARS-MONTHS’

3. TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

It returns expr1 − expr2 expressed as a value in unit from one date to the other. expr1 and expr2 are date or date-and-time expressions.

select TIMESTAMPDIFF(Day, now(), HIREDATE) from emp;

select TIMESTAMPDIFF(Month, now(), HIREDATE) from emp;

select TIMESTAMPDIFF(Year, now(), HIREDATE) from emp;

4. LAST_DAY(date)

It takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

SELECT LAST_DAY(NOW());

5. DATE(expr)

It extracts the date part of the date or datetime expression expr.

SELECT DATE(‘2003-12-31 01:02:03’);

Similar functions include Time(), Day(), Month(), Year(), etc.

6. DATE_FORMAT(date,format)

The DATE_FORMAT function accepts two arguments:

date : is a valid date value that you want to format

format : is a format string that consists of predefined specifiers. Each specifier is preceded by a percentage character ( % ). See the table below for a list of predefined specifiers.

The following are some commonly used date format strings:

DATE_FORMAT string	Formatted date
%Y-%m-%d	7/4/2019
%e/%c/%Y	4/7/2019
%c/%e/%Y	7/4/2019
%d/%m/%Y	4/7/2019
%m/%d/%Y	7/4/2019
%e/%c/%Y %H:%i	4/7/2019 11:20
%c/%e/%Y %H:%i	7/4/2019 11:20
%d/%m/%Y %H:%i	4/7/2019 11:20
%m/%d/%Y %H:%i	7/4/2019 11:20
%e/%c/%Y %T	4/7/2019 11:20
%c/%e/%Y %T	7/4/2019 11:20
%d/%m/%Y %T	4/7/2019 11:20
%m/%d/%Y %T	7/4/2019 11:20
%a %D %b %Y	Thu 4th Jul 2019
%a %D %b %Y %H:%i	Thu 4th Jul 2019 11:20
%a %D %b %Y %T	Thu 4th Jul 2019 11:20:05
%a %b %e %Y	Thu Jul 4 2019
%a %b %e %Y %H:%i	Thu Jul 4 2019 11:20
%a %b %e %Y %T	Thu Jul 4 2019 11:20:05
%W %D %M %Y	Thursday 4th July 2019
%W %D %M %Y %H:%i	Thursday 4th July 2019 11:20
%W %D %M %Y %T	Thursday 4th July 2019 11:20:05
%l:%i %p %b %e, %Y	7/4/2019 11:20
%M %e, %Y	4-Jul-19
%a, %d %b %Y %T	Thu, 04 Jul 2019 11:20:05

小案例

假如有日期类型的HIREDATE
获取当月的最后一天：LASTDAY(HIREDATE)
获取这周的星期数（1-7）：DAYOFWEEK（HIREDATE）
获取这周的星期数（0-6）：DATE_FORMAT(HIREDATE,"%w")

参考网址：

https://w3resource.com/PostgreSQL/tutorial.php