MySQL practical article

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

Built-in function

MySQL provides a number of built-in functions that satisfy most of our query needs. Here are just some of the common built-in functions and their corresponding uses. The built-in functions of MySQL can be divided into numerical functions, date functions, string functions, aggregate functions, and advanced functions.

Numerical function

1. Calculate the absolute value of ABS(x)

• select ABS(-1)Returns 1

2. Take square root of SQRT(x)

The square root function SQRT(x) returns the quadratic root of non-negative x. If x is negative, there is no square root and the result is NULL

• select SQRT(2)Return 4
• select SQRT(-2)Returns NULL

3.MOD(x,y

MOD(x,y) returns the remainder of x divided by y. MOD() also works for numbers with a decimal part, which returns the remainder of division

• Select MOD(25,4) returns 1

• SELECT MOD(25.5,5.5) returns 3.5

4. Round up CEIL(x) and CEILING(x)

Returns the smallest integer value not less than x (that is, rounded up), which is converted to a BIGINT

• The SELECT CEIL (3.1), CEIL (3.5), CEIL (3.9)Go back to 4,4,4

5.FLOOR(x) is a downward integer

Returns the maximum integer value less than x

• The SELECT FLOOR (3.1), FLOOR (3.13), FLOOR (3.9)Return 3,3,3

6.ROUND(x

ROUND(x,d) returns the nearest integer to the argument x. ROUND(x,d) rounds the argument x to the nearest integer. The value of the ROUND(x,d) returns the d bits specified after the decimal point. If d is negative, keep the d bits to the left of the decimal point, round them first, and then zero the corresponding bits.

• SELECT ROUND(3.1),ROUND(3.5),ROUND(3.9),ROUND(3.1234,2

• SELECT ROUND (93.12 -) 2, 93.12 to ROUND is equal to 93, and the corresponding numerical take 0, back to 100

Date function

1.CURDATE() and CURRENT_DATE() return the current system date

The CURDATE() and CURRENT_DATE() functions serve the same purpose, returning the current system date in yyyY-MM-DD or YYYYMMDD format

• SELECT CURDATE(),CURRENT_DATE() returns the current system date 2021-09-20

• SELECT CURRENT_DATE()+0 returns the current system date 20210920 in YYYYMMDD format

2.CURTIME(), CURRENT_TIME() Returns the current system time

The CURTIME() and CURRENT_TIME() functions serve the same purpose, returning the current time in HH: MM: SS, HHMMSS

• SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME()+0Return 09:54:26, 09:54:26, 095426

3.NOW() and SYSDATE() return the current date and time values

The NOW() and SYSDATE() functions return the current date and time in the format YYYY-MM-DD HH: MM: SS or YYYYMMDDHHMMSS

• SELECT NOW(),SYSDATE(),NOW()+0 return 2021-09-20 09:57:25, 2021-09-20 09:57:25, 20210920095725

• 🚦NOW() differs from SYSDATE() in that the NOW() function always gets the start time of the SQL statement, while the SYSDATE() function dynamically gets the real-time time.

4.MONTH(date) Obtains the MONTH in the specified date

The MONTH(date) function obtains the MONTH of a specified date. The value ranges from 1 to 12

• SELECT MONTH(NOW()) returns the current MONTH 9

• SELECT MONTH(‘2021-11-11’) return MONTH 11

5.MONTHNAME(date) Returns the full name of a month corresponding to date

• SELECT MONTHNAME('2021-11-11')Return to Full November

DAYNAME(date) Returns the English name of the working day corresponding to the date

• SELECT DAYNAME('2021-09-20')Return to Monday

DAYOFWEEK(date) Returns the index (position) corresponding to the date in a week.

DAYOFWEEK(date) Returns the index (position) corresponding to the date of a week. 1 indicates Sunday, 2 indicates Monday… 7 is For Saturday

• SELECT DAYOFWEEK('2021-09-20')Return 2 for Monday

8.WEEKDAY(date) Obtains the index position of the specified date within a week

WEEKDAY(data) Returns the WEEKDAY index corresponding to the specified date. Different from DAYOFWEEK(data), WEEKDAY(data) returns 0 for Monday, 1 for Tuesday,… 6 indicates Sunday.

• SELECT WEEKDAY('2021-09-20')Returns 0, indicating Monday

9.WEEK(Date) Return Date Date is the WEEK of the year

• SELECT WEEK('2021-09-20')Return 38, so 2021-09-20 is the 38th week of the year

10.DAYOFYEAR(d) Returns the date date is the day of the year

Date is the day of a year. The value ranges from 1 to 366

• SELECT DAYOFYEAR('2021-09-20')Return 263, indicating that 2021-09-20 is the 263rd day of the year

DAYOFMONTH(Date) Returns the date date is the day of the month

• SELECT DAYOFMONTH('2021-09-20')Return 20, 2021-09-20 is the 20th day of September

12.ADDTIME(time,expr) Time addition

The ADDTIME(time,expr) function performs time addition, adding expr to the original time and returning the result

• SELECT ADDTIME('09:30:20','01:05:20')Return time 10:35:40

13.SUBTIME(time,expr) Subtraction operation

• SELECT SUBTIME('10:28:30','01:05:20')Return time 9:23:10

14.DATEDIFF(date1, date2) returns the number of days between two dates

DATEDIFF(date1, date2) Returns the number of days between the start time date1 and the end time date2. Date1 and date2 are dates, and only the date portion of these values is used in the calculation.

• SELECT DATEDIFF(NOW(),'1999-03-26')Return to 8214 days

15.DATE_FORMAT(data,format) Formats the specified date

The DATE_FORMAT() function takes two arguments:

• date: is the valid date value to be formatted
• format: Format of all conversions.

The format format

• %aAbbreviation for weekdays (Sun~Sat)
• %bAbbreviated names of months (Jan… Dec)
• %cMonth in numeric format (0 to 12)
• %DDate of the month with English suffixes (0th, 2ST, 3nd…)
• %dDate of the month in numerical form (00~31)
• %eDate of the month in numeric form (0~31)
• %fMicroseconds (000000… 999999).
• %H24 hours in 2 digits (00~23)
• %h12 hours in 2 digits (01 to 12)
• %iMinutes, numerical form (00~59)
• %j— Number of days in a year (001 to 366)
• %kThe value is 24 hours (0 to 23)
• %lIt is expressed in 12 hours (1 to 12)
• %MMonth Name (January~December)
• %mMonth, numeric form (00~12)
• %pMorning (AM) or afternoon (PM)
• %rTime, 12 hours (hh: minutes (mm) : seconds (SS) followed by AM or PM)
• %SSecond in 2-digit form (00~59)
• %TTime, 24 hours (hh: minutes (mm): seconds (SS))
• %UThe value ranges from 00 to 53. Sunday is the first day of a week
• %uThe value ranges from 00 to 53. Monday is the first day of a week
• %VWeek (01-53), Sunday is the first day of each week, and % X is used at the same time
• %vWeek (01 to 53), Where Monday is the first day of each week. This parameter is used with %x
• %WLogo of the week (Sunday, Monday, Tuesday… On Saturday)
• %w— Every day of the week (0= Sunday… 6 = Saturday)
• %XYear of the week, where Sunday is the first day of the week, numeric form, 4 digits, and % V are used simultaneously
• %xThe year of the week, where Monday is the first day of the week, in numeric form, 4 digits, and %v
• %YThe 4-digit form represents the year
• %yThe 2-digit form represents the year
• % %% a literal character

example

• SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s')Returns a formatted dateThe 2021-09-20 10:34:48

16.TO_DAYS(data) Indicates the number of days since January 1, 0000

• SELECT TO_DAYS(NOW())

17.DATE_SUB(date,INTERVAL expr type) Subtracts date data from specified INTERVAL expr

• SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) Returns the date 7 days ago

• SELECT DATE_SUB(‘2021-09-20’, INTERVAL 1 YEAR

18. YEARWEEK(data,model)

The YEARWEEK(data,model) function returns the year and week (0 to 53). In mode, 0 indicates day of the week and 1 indicates Monday

• SELECT YEARWEEK(NOW())Return 202138, indicating that the current day is the 38th week of 2021

example

• Query total sales for the month

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE STATUS = 3 
  AND DATE_FORMAT(payment_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') ;
Copy the code

• Check today’s total sales

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE STATUS = 3 
  AND TO_DAYS(payment_time) = TO_DAYS(NOW()) 
Copy the code

• Query yesterday’s total sales

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE STATUS = 3 
  AND TO_DAYS(NOW()) - TO_DAYS(payment_time) <= 1
Copy the code

• Query the total sales in recent 7 days

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE STATUS = 3 
  AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(payment_time) ;

Copy the code

• Query last year’s total sales

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE YEAR(payment_time) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR))
Copy the code

• Inquire about total sales of last month

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE DATE_FORMAT(payment_time, '%Y-%m') = DATE_FORMAT(
    DATE_SUB(CURDATE(), INTERVAL 1 MONTH),
    '%Y-%m') ;
Copy the code

• Query total sales for the week

SELECT 
  SUM(total_amount) 
FROM
  oms_order 
WHERE STATUS = 3 
  AND YEARWEEK(
    DATE_FORMAT(create_time, '%Y-%m-%d'),
    1
  ) = YEARWEEK(NOW(), 1)
Copy the code

String function

1.CONCAT(s1,s2… Sn) string concatenation

CONCAT(s1,s2… The sn function combines the specified s1,s2, and other strings into a single string.

• SELECT CONCAT (' no ', 'a', 'milk' and 'tea', 'the', 'Programmer) AS the userNameTo return toDon't drink milk tea Programmer

2.CONCAT_WS(x,s1,s2… Sn) string concatenation

This function is also similar to CONCAT(s1,s2…). The function concatenates strings, but with an x between each string, which can be a delimiter.

• SELECT CONCAT_WS('-','1','2','3','4') AS sTo return toThe 1-2-3-4

3.TRIM(s) Remove Spaces at the beginning and end of the string s

• SELECT TRIM(' abc ')

4.UPPER(s), UCASE(s) converts the string to uppercase

• SELECT UPPER('abc') ,UCASE('abc')

5.LOWER(s), LCASE(s) change all letters of the string s to lowercase

• SELECT LOWER('AbC'),LCASE('DeF')

6.STRCMP(s1,s2) String comparison

Compare the strings s1 and s2, returning 0 if s1 is equal to s2, 1 if S1 >s2 and -1 if s1<s2

• SELECT STRCMP('123','234'), returns -1 str1 < str2
• SELECT STRCMP('abc','abc'), returns 0, STR == str2
• SELECT STRCMP('321','123'), returns 1 str1 > str2

7.SUBSTR(s, start, length) String interception

SUBSTR(s, start, length) intercepts a substring of length from the start position of string s.

• SELECT SUBSTR (' abcdefghijklmn ', 2, 3)Return BCD from the stringabcdefghijklmnBegins intercepting a substring of length 3 at position 2 in.

Aggregation function

1.MAX() Queries the maximum value of the specified column

2.MIN() Queries the minimum value of the specified column

3.COUNT() Indicates the number of rows in the query result

• COUNT(*)Calculates the total number of rows in a table, whether a column is numeric or null.
• COUNT(field name)Calculates the total number of rows under the specified columnIgnore the null valueThe rows of

4.SUM() returns the SUM of the specified columns

• The SUM() function returns null instead of 0 if no record is matched

5.AVG() calculates the average value and returns the average value of the specified column data

Senior function

1.CAST(x AS type) Converts the data type

• SELECT CAST('2021-9-20' AS DATE)Converts the DATE represented by a string to a DATE of type DATE

2.COALESCE(expr1, expr2, …. , expr_n)

Returns the first non-null expression in a parameter (from left to right)

• SELECT COALESCE(NULL,NULL,'a','b',NULL)Returns a

3.IF(expr,v1,v2)

If the expression expr is true, the result v1 is returned; otherwise, the result v2 is returned

• SELECT IF(1>2,'true','false')

4.IFNULL(v1,v2)

V1 is returned if the value of v1 is not NULL, otherwise v2 is returned.

• SELECT IFNULL(NULL,'false')

5.ISNULL(expression)

Check whether the expression is NULL. If 1 is returned, it is not NULL. If 0 is returned, it is NULL

• SELECT ISNULL(NULL)

6.LAST_INSERT_ID()

Returns the most recently generated AUTO_INCREMENT value, often used in stored procedures. For example, in a stored procedure, you can use this function to obtain the AUTO_INCREMENT value generated by the previous statement in the current statement.

🏁 the above is a simple introduction to some commonly used built-in functions of MySQL. If there is any error, please leave a comment. If you think this article is helpful to you, please like 👍