MySQL has a number of built-in functions, which are described below.

Table from: www.runoob.com/mysql/mysql…

Delete from table_name where table_name = 1; INSERT INTO table name (field1, field2,... fieldN ) VALUES ( value1, value2,... valueN ); UPDATE table_name SET title=' learn '; SELECT * from 表名 WHERE author LIKE '%COM%'; Stitching CONCAT (name, '(', id and') ') name_id,Copy the code
DATE_FORMAT(time,'%Y-%m-%d') time CASE WHEN status = 1 THEN 'commit' WHEN status = 1 = 2 THEN 'initiate' ELSE 'unknown' end statusCopy the code

Remove duplicate data distinct

Order by ID ASC indicates ascending order and DESC indicates descending order.

 

Batch replace part of a field in a table

Update table_name set table_name = replace(table_name, ‘replace ‘,’ replace ‘)

The percentage of two values

SELECT 
concat(left(number1/The numerical2 *100.5),The '%') as"Complaint rate"FROM DUAL
Copy the code

things 

BEGIN Start a transaction ROLLBACK transaction COMMIT transaction confirm

SET AUTOCOMMIT=0 SET AUTOCOMMIT=1

begin;

INSERT INTO aa (name) VALUES (333);

INSERT INTO aa (name) VALUES (444);

INSERT INTO aa (name) VALUES (555);

commit;

Group_concat () function

Group_concat () SELECT group_concat(name) FROM AA GROUP BY name.

 

Querying Database Information

//1
SELECTA.table_name Table name, A.table_comment table description, B.collumn_name field name, B.collumn_COMMENT field description, B.collumn_type field type, b.collumn_key constraintFROM
information_schema. TABLES a
LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME
WHERE
a.table_schema = 'hzsf_donation' //The library nameAND a.table_name ='hd_donation_record' //The name of the table in the libraryORDER BY
a.table_name

//2
SELECT
	b.table_comment,
	a.column_name,
	a.is_nullable,
	a.data_type,
	a.column_comment,
	a.column_key,
	a.extra
FROM
	information_schema. COLUMNS a
LEFT JOIN information_schema. TABLES b ON b.table_name = a.TABLE_NAME
WHERE
	a.table_name = 'hd_donation_record'
AND a.table_schema = (SELECT DATABASE())
ORDER BY
	a.ordinal_position
Copy the code

 

MySQL string function

function describe The instance
ASCII(s) Returns the ASCII code for the first character of the string s. Returns the ASCII code for the first letter of the CustomerName field:
SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;
` ` `
CHAR_LENGTH(s) Returns the number of characters in the string s Returns the number of characters in the string RUNOOB
SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString;
` ` `
CHARACTER_LENGTH(s) Returns the number of characters in the string s Returns the number of characters in the string RUNOOB
SELECT CHARACTER_LENGTH(“RUNOOB”) AS LengthOfString;
` ` `
CONCAT(s1,s2… sn) String s1,s2, and other strings are combined into one string Merges multiple strings
SELECT CONCAT(“SQL “, “Runoob “, “Gooogle “, “Facebook”) AS ConcatenatedString;
` ` `
CONCAT_WS(x, s1,s2… sn) With the CONCAT (s1, s2,…). Function, but each string is directly preceded by x, which can be a delimiter Merges multiple strings and adds the delimiter:
SELECT CONCAT_WS(“-“, “SQL”, “Tutorial”, “is”, “fun!” )AS ConcatenatedString;
` ` `
FIELD(s,s1,s2…) Returns the first string s in the string list (s1,s2…) The position of Returns the position of the string c in the list value: ‘ ‘
SELECT FIELD(“c”, “a”, “b”, “c”, “d”, “e”);
` ` `
FIND_IN_SET(s1,s2) Returns the position of the string matching s1 in the string s2 Returns the position of the string c in the specified string:
SELECT FIND_IN_SET(“c”, “a,b,c,d,e”);
` ` `
FORMAT(x,n) The function formats the number x to “#,### #.##”, keeping the x to n decimal places and rounding off the last one. Format numbers “#,###.##” in the form:”
SELECT the FORMAT (250500.5634, 2); – output 250500.56
` ` `
INSERT(s1,x,len,s2) The string s2 replaces the string of length len starting at position X of s1 Replace the six characters starting at the first position in the string with runoob:
SELECT INSERT(“google.com”, 1, 6, “runnob”); — Output: runoob.com
` ` `
LOCATE(s1,s) Gets the starting position of s1 from the string s Gets b’s position in the string ABC: ‘ ‘
SELECT LOCATE(‘st’,’myteststring’); 5 –
` ` `
LCASE(s) Changes all letters of the string S to lowercase The string RUNOOB is converted to lowercase:
SELECT LOWER(‘RUNOOB’) — runoob
` ` `
LEFT(s,n) Returns the first n characters of the string s Returns the first two characters in the string runoob: ‘ ‘
SELECT LEFT(‘runoob’,2) — ru
` ` `
LEFT(s,n) Returns the first n characters of the string s Returns the first two characters of the string abcde: ‘
SELECT LEFT(‘abcde’,2) — ab
` ` `
LOCATE(s1,s) Gets the starting position of s1 from the string s Returns the position of b in the string ABC: ‘ ‘
SELECT LOCATE(‘b’, ‘abc’) — 2
` ` `
LOWER(s) Changes all letters of the string S to lowercase The string RUNOOB is converted to lowercase:
SELECT LOWER(‘RUNOOB’) — runoob
` ` `
LPAD(s1,len,s2) Fill the string s2 at the beginning of the string s1 with len Padding the string xx at the beginning of the ABC string: ‘ ‘
SELECT LPAD(‘abc’,5,’xx’) — xxabc
` ` `
LTRIM(s) Remove the space at the beginning of the string s Remove the space at the beginning of the string RUNOOB:
SELECT LTRIM(” RUNOOB”) AS LeftTrimmedString; — RUNOOB
` ` `
MID(s,n,len) Select SUBSTRING of length from start of string s, same as SUBSTRING(s,n,len) Intercepts three characters from the second position in the string RUNOOB: ‘ ‘
SELECT MID(“RUNOOB”, 2, 3) AS ExtractString; — UNO
` ` `
POSITION(s1 IN s) Gets the starting position of s1 from the string s Returns the position of b in the string ABC: ‘ ‘
SELECT POSITION(‘b’ in ‘abc’) — 2
` ` `
REPEAT(s,n) Repeat the string s n times Repeat the string runoob three times:
SELECT REPEAT(‘runoob’,3) — runoobrunoobrunoob
` ` `
REPLACE(s,s1,s2) Replaces string s1 in string S with string S2 Replaces character A in the string ABC with character x:
SELECT REPLACE(‘abc’,’a’,’x’) –xbc
` ` `
REVERSE(s) Reverse the order of the string s Reverse the order of the string ABC: ‘ ‘ ‘
SELECT REVERSE(‘abc’) — cba
` ` `
RIGHT(s,n) Returns the last n characters of the string s Returns the last two characters of the string runoob:
SELECT RIGHT(‘runoob’,2) — ob
` ` `
RPAD(s1,len,s2) Add the string s2 at the end of string s1 to make the string length reach len Padding the string xx to the end of the ABC string: ‘ ‘
SELECT RPAD(‘abc’,5,’xx’) — abcxx
` ` `
RTRIM(s) Remove the space at the end of the string s Remove trailing whitespace from the string RUNOOB
SELECT RTRIM(“RUNOOB “) AS RightTrimmedString; — RUNOOB
` ` `
SPACE(n) Returns n Spaces Returns 10 Spaces: ‘ ‘
SELECT SPACE(10);
` ` `
STRCMP(s1,s2) Compare the strings s1 and s2, returning 0 if s1 is equal to s2, 1 if S1 >s2, and -1 if s1<s2 Compare strings: ‘ ‘
SELECT STRCMP(“runoob”, “runoob”); – 0
` ` `
SUBSTR(s, start, length) Intercepts a substring of length from the start position of string s Intercepts three characters from the second position in the string RUNOOB: ‘ ‘
SELECT SUBSTR(“RUNOOB”, 2, 3) AS ExtractString; — UNO
` ` `
SUBSTRING(s, start, length) Intercepts a substring of length from the start position of string s Intercepts three characters from the second position in the string RUNOOB: ‘ ‘
SELECT SUBSTRING(“RUNOOB”, 2, 3) AS ExtractString; — UNO
` ` `
SUBSTRING_INDEX(s, delimiter, number) Delimiter returns the substring after the delimiter that appears at the number of the string s. If number is positive, return the string to the left of the number character. If number is negative, return the string to the right of the (absolute value (from right) of number) character. ` ` `
SELECT SUBSTRING_INDEX(‘ab’,’‘,1) — a SELECT SUBSTRING_INDEX(‘ab’,’‘,-1)    — b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘abcde’,’‘, 3),‘,-1)    — c
` ` `
TRIM(s) Remove Spaces at the beginning and end of the string s Remove the leading and trailing Spaces of the string RUNOOB:
SELECT TRIM(‘ RUNOOB ‘) AS TrimmedString;
` ` `
UCASE(s) Converts a string to uppercase Converts the string runoob to uppercase:
SELECT UCASE(“runoob”); — RUNOOB
` ` `
UPPER(s) Converts a string to uppercase Converts the string runoob to uppercase:
SELECT UPPER(“runoob”); — RUNOOB
` ` `

MySQL number function

The function name describe The instance
ABS(x) Return the absolute value of x Returns the absolute value of -1:
SELECT ABS(-1) — return 1
` ` `
ACOS(x) Find the inverse cosine of x (in radians) ` ` `
SELECT a cosine (0.25);
` ` `
ASIN(x) Find arcsine (in radians) ` ` `
The SELECT ASIN (0.25);
` ` `
ATAN(x) Find the arctangent (parameter is radians) ` ` `
The SELECT ATAN (2.5);
` ` `
ATAN2(n, m) Find the arctangent (parameter is radians) ` ` `
The SELECT ATAN2 (0.8, 2);
` ` `
AVG(expression) Returns the average value of an expression. Expression is a field Return the average value of the Price field in the Products table: ‘ ‘
SELECT AVG(Price) AS AveragePrice FROM Products;
` ` `
CEIL(x) Returns the smallest integer greater than or equal to x ` ` `
SELECT CEIL(1.5) — returns 2
` ` `
CEILING(x) Returns the smallest integer greater than or equal to x ` ` `
SELECT CEIL(1.5) — returns 2
` ` `
COS(x) Find the cosine (parameter is radians) ` ` `
SELECT COS(2);
` ` `
COT(x) Find the cotangent (parameter is radian) ` ` `
SELECT COT(6);
` ` `
COUNT(expression) Returns the total number of records queried. The expression argument is a field or an asterisk Return the number of Products entries in the Products column: ‘ ‘
SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
` ` `
DEGREES(x) Convert radians to degrees ` ` `
SELECT DEGREES(3.1415926535898) — 180
` ` `
n DIV m Divisible, n is the dividend, m is the divisor Calculate 10 divided by 5:
SELECT 10 DIV 5; 2 –
` ` `
EXP(x) Return e to the x Calculate e to the third power:
The SELECT EXP (3) – 20.085536923188
` ` `
FLOOR(x) Returns the largest integer less than or equal to x Integers less than or equal to 1.5: ‘ ‘
SELECT FLOOR(1.5) — returns 1
` ` `
GREATEST(expr1, expr2, expr3, …) Returns the maximum value in the list Returns the maximum value in the following list of numbers: ‘ ‘
SELECT GREATEST(3, 12, 34, 8, 25); – 34
Returns the maximum value in the following list of strings:
SELECT GREATEST(“Google”, “Runoob”, “Apple”); — Runoob
` ` `
LEAST(expr1, expr2, expr3, …) Returns the minimum value in the list Returns the minimum value in the following list of numbers:
SELECT LEAST(3, 12, 34, 8, 25); – 3
Returns the minimum value in the following list of strings:
SELECT LEAST(“Google”, “Runoob”, “Apple”); — Apple
` ` `
LN Returns the natural logarithm of a number Returns the natural logarithm of 2:
SELECT LN(2); – 0.6931471805599453
` ` `
LOG(x) Return the natural log (log base e) ` ` `
SELECT the LOG (20.085536923188) – 3
` ` `
LOG10(x) Returns logarithm base 10 ` ` `
SELECT LOG10(100) — 2
` ` `
LOG2(x) Returns logarithm base 2 Returns logarithm base 2 of 6:
SELECT LOG2(6); – 2.584962500721156
` ` `
MAX(expression) Returns the maximum value in the field expression Return the maximum value of field Price in table Products: ‘ ‘
SELECT MAX(Price) AS LargestPrice FROM Products;
` ` `
MIN(expression) Returns the minimum value in the field expression Returns the minimum value of field Price in table Products: ‘ ‘
SELECT MIN(Price) AS LargestPrice FROM Products;
` ` `
MOD(x,y) Returns the remainder of x divided by y Remainder of 5 divided by 2:
SELECT the MOD (5, 2) – 1
` ` `
PI() Return PI (3.141593) ` ` `
The SELECT () – 3.141593 PI
` ` `
POW(x,y) Return x to the y Two to the third power:
SELECT POW (2, 3) – 8
` ` `
POWER(x,y) Return x to the y Two to the third power:
SELECT the POWER (2, 3) – 8
` ` `
RADIANS(x) Convert the Angle to radians 180 degrees to radians: ‘ ‘
The SELECT RADIANS (180) – 3.1415926535898
` ` `
RAND() Returns a random number from 0 to 1 ` ` `
The SELECT RAND () – 0.93099315644334
` ` `
ROUND(x) Returns the nearest integer to x ` ` `
SELECT ROUND (1.23456) – 1
` ` `
SIGN(x) Returns the symbol of x, -1 for negative numbers, 0 for positive numbers, and 1 for positive numbers ` ` `
SELECT SIGN(-10) — (-1)
` ` `
SIN(x) Find the sine (in radians) ` ` `
The SELECT SIN (RADIANS (30)) – 0.5
` ` `
SQRT(x) Return the square root of x The square root of 25:
SELECT SQRT(25) — 5
` ` `
SUM(expression) Returns the sum of the specified fields Calculate the sum of Quantity fields in OrderDetails table: ‘ ‘
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
` ` `
TAN(x) Find the tangent value (parameter is radians) ` ` `
The SELECT TAN (1.75); 5.52037992250933 — –
` ` `
TRUNCATE(x,y) Returns the value of x reserved to the y decimal place (ROUND is not rounded) ` ` `
SELECT TRUNCATE (1.23456, 3) – 1.234
` ` `

MySQL date function

The function name describe The instance
ADDDATE(d,n) Compute the start date d plus n days ` ` `
SELECT ADDDATE(“2017-06-15”, INTERVAL 10 DAY); – > 2017-06-25
` ` `
ADDTIME(t,n) Time t plus n seconds ` ` `
SELECT ADDTIME(‘2011-11-11 11:11:11’, 5) ->2011-11-11 11:11:16 (seconds)
` ` `
CURDATE() Return current date ` ` `
SELECT CURDATE(); – > 2018-09-19
` ` `
CURRENT_DATE() Return current date ` ` `
SELECT CURRENT_DATE(); – > 2018-09-19
` ` `
CURRENT_TIME Return current time ` ` `
SELECT CURRENT_TIME(); – > 19:59:02
` ` `
CURRENT_TIMESTAMP() Returns the current date and time ` ` `
SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43
` ` `
CURTIME() Return current time ` ` `
SELECT CURTIME(); – > 19:59:02
` ` `
DATE() Extracts a date value from a date or a date-time expression ` ` `
SELECT DATE(“2017-06-15”); – > 2017-06-15
` ` `
DATEDIFF(d1,d2) Count the number of days between dates D1 ->d2 ` ` `
SELECT DATEDIFF(‘2001-01-01′,’2001-02-02’) -> -32
` ` `
DATE_ADD(d, INTERVAL expr type) Calculates the start date d plus the date after a period ` ` `
SELECT ADDDATE(‘2011-11-11 11:11:11’,1) -> 2011-11-12 11:11:11 (default: 2011-11-11 11:11:11) INTERVAL 5 MINUTE) -> 2011-11-11 11:16:11
` ` `
DATE_FORMAT(d,f) Display date D as required by expression f ` ` `
SELECT DATE_FORMAT(‘2011-11-11 11:11:11′,’%Y-%m-%d %r’) -> 2011-11-11 11:11:11 AM
` ` `
DATE_SUB(date,INTERVAL expr type) The function subtracts the specified interval from the date. Subtract 2 days from OrderDate field in Orders table: ‘ ‘
SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
` ` `
DAY(d) Returns the date portion of the date value d ` ` `
SELECT DAY(“2017-06-15”); – > 15
` ` `
DAYNAME(d) The return date d is the day of the week, such as Monday,Tuesday ` ` `
SELECT DAYNAME(‘2011-11-11 11:11:11’) ->Friday
` ` `
DAYOFMONTH(d) Date D is the day of the month ` ` `
SELECT DAYOFMONTH(‘2011-11-11 11:11:11’) ->11
` ` `
DAYOFWEEK(d) Date D What day is today, Sunday 1, Monday 2, and so on ` ` `
SELECT DAYOFWEEK(‘2011-11-11 11:11:11’) ->6
` ` `
DAYOFYEAR(d) Date D is the day of the year ` ` `
SELECT DAYOFYEAR(‘2011-11-11 11:11:11’) ->315
` ` `
EXTRACT(type FROM d) Gets the specified value from date d, and type specifies the returned value. The value of type can be * 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 EXTRACT(MINUTE FROM ‘2011-11-11 11:11:11’) -> 11

| FROM_DAYS(n)                      | 计算从 0000 年 1 月 1 日开始 n 天后的日期                                                                                                                                                                                                                                                                    | ```
SELECT FROM_DAYS(1111) -> 0003-01-16
```                                                                                                                                             |
| HOUR(t)                           | 返回 t 中的小时值                                                                                                                                                                                                                                                                                      | ```
SELECT HOUR('1:2:3') -> 1
```                                                                                                                                                        |
| LAST_DAY(d)                       | 返回给给定日期的那一月份的最后一天                                                                                                                                                                                                                                                                               | ```
SELECT LAST_DAY("2017-06-20"); -> 2017-06-30
```                                                                                                                                     |
| LOCALTIME()                       | 返回当前日期和时间                                                                                                                                                                                                                                                                                       | ```
SELECT LOCALTIME() -> 2018-09-19 20:57:43
```                                                                                                                                        |
| LOCALTIMESTAMP()                  | 返回当前日期和时间                                                                                                                                                                                                                                                                                       | ```
SELECT LOCALTIMESTAMP() -> 2018-09-19 20:57:43
```                                                                                                                                   |
| MAKEDATE(year, day-of-year)       | 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期                                                                                                                                                                                                                                                     | ```
SELECT MAKEDATE(2017, 3); -> 2017-01-03
```                                                                                                                                          |
| MAKETIME(hour, minute, second)    | 组合时间，参数分别为小时、分钟、秒                                                                                                                                                                                                                                                                               | ```
SELECT MAKETIME(11, 35, 4); -> 11:35:04
```                                                                                                                                          |
| MICROSECOND(date)                 | 返回日期参数所对应的毫秒数                                                                                                                                                                                                                                                                                   | ```
SELECT MICROSECOND("2017-06-20 09:34:00.000023"); -> 23
```                                                                                                                          |
| MINUTE(t)                         | 返回 t 中的分钟值                                                                                                                                                                                                                                                                                      | ```
SELECT MINUTE('1:2:3') -> 2
```                                                                                                                                                      |
| MONTHNAME(d)                      | 返回日期当中的月份名称，如 Janyary                                                                                                                                                                                                                                                                           | ```
SELECT MONTHNAME('2011-11-11 11:11:11') -> November
```                                                                                                                              |
| MONTH(d)                          | 返回日期d中的月份值，1 到 12                                                                                                                                                                                                                                                                               | ```
SELECT MONTH('2011-11-11 11:11:11') ->11
```                                                                                                                                         |
| NOW()                             | 返回当前日期和时间                                                                                                                                                                                                                                                                                       | ```
SELECT NOW() -> 2018-09-19 20:57:43
```                                                                                                                                              |
| PERIOD_ADD(period, number)        | 为 年-月 组合日期添加一个时段                                                                                                                                                                                                                                                                                | ```
SELECT PERIOD_ADD(201703, 5);    -> 201708
```                                                                                                                                       |
| PERIOD_DIFF(period1, period2)     | 返回两个时段之间的月份差值                                                                                                                                                                                                                                                                                   | ```
SELECT PERIOD_DIFF(201710, 201703); -> 7
```                                                                                                                                         |
| QUARTER(d)                        | 返回日期d是第几季节，返回 1 到 4                                                                                                                                                                                                                                                                             | ```
SELECT QUARTER('2011-11-11 11:11:11') -> 4
```                                                                                                                                       |
| SECOND(t)                         | 返回 t 中的秒钟值                                                                                                                                                                                                                                                                                      | ```
SELECT SECOND('1:2:3') -> 3
```                                                                                                                                                      |
| SEC_TO_TIME(s)                    | 将以秒为单位的时间 s 转换为时分秒的格式                                                                                                                                                                                                                                                                           | ```
SELECT SEC_TO_TIME(4320) -> 01:12:00
```                                                                                                                                             |
| STR_TO_DATE(string, format_mask)  | 将字符串转变为日期                                                                                                                                                                                                                                                                                       | ```
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10
```                                                                                                                  |
| SUBDATE(d,n)                      | 日期 d 减去 n 天后的日期                                                                                                                                                                                                                                                                                 | ```
SELECT SUBDATE('2011-11-11 11:11:11', 1) ->2011-11-10 11:11:11 (默认是天)
```                                                                                                            |
| SUBTIME(t,n)                      | 时间 t 减去 n 秒的时间                                                                                                                                                                                                                                                                                  | ```
SELECT SUBTIME('2011-11-11 11:11:11', 5) ->2011-11-11 11:11:06 (秒)
```                                                                                                               |
| SYSDATE()                         | 返回当前日期和时间                                                                                                                                                                                                                                                                                       | ```
SELECT SYSDATE() -> 2018-09-19 20:57:43
```                                                                                                                                          |
| TIME(expression)                  | 提取传入表达式的时间部分                                                                                                                                                                                                                                                                                    | ```
SELECT TIME("19:30:10"); -> 19:30:10
```                                                                                                                                             |
| TIME_FORMAT(t,f)                  | 按表达式 f 的要求显示时间 t                                                                                                                                                                                                                                                                                | ```
SELECT TIME_FORMAT('11:11:11','%r') 11:11:11 AM
```                                                                                                                                  |
| TIME_TO_SEC(t)                    | 将时间 t 转换为秒                                                                                                                                                                                                                                                                                      | ```
SELECT TIME_TO_SEC('1:12:00') -> 4320
```                                                                                                                                            |
| TIMEDIFF(time1, time2)            | 计算时间差值                                                                                                                                                                                                                                                                                          | ```
SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01
```                                                                                                                             |
| TIMESTAMP(expression, interval)   | 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和                                                                                                                                                                                                                                                               | ```
SELECT TIMESTAMP("2017-07-23",  "13:10:11"); -> 2017-07-23 13:10:11
```                                                                                                              |
| TO_DAYS(d)                        | 计算日期 d 距离 0000 年 1 月 1 日的天数                                                                                                                                                                                                                                                                     | ```
SELECT TO_DAYS('0001-01-01 01:01:01') -> 366
```                                                                                                                                     |
| WEEK(d)                           | 计算日期 d 是本年的第几个星期，范围是 0 到 53                                                                                                                                                                                                                                                                     | ```
SELECT WEEK('2011-11-11 11:11:11') -> 45
```                                                                                                                                         |
| WEEKDAY(d)                        | 日期 d 是星期几，0 表示星期一，1 表示星期二                                                                                                                                                                                                                                                                       | ```
SELECT WEEKDAY("2017-06-15"); -> 3
```                                                                                                                                               |
| WEEKOFYEAR(d)                     | 计算日期 d 是本年的第几个星期，范围是 0 到 53                                                                                                                                                                                                                                                                     | ```
SELECT WEEKOFYEAR('2011-11-11 11:11:11') -> 45
```                                                                                                                                   |
| YEAR(d)                           | 返回年份                                                                                                                                                                                                                                                                                            | ```
SELECT YEAR("2017-06-15"); -> 2017
```                                                                                                                                               |
| YEARWEEK(date, mode)              | 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推                                                                                                                                                                                                                                                         | ```
SELECT YEARWEEK("2017-06-15"); -> 201724
```                                                                                                                                         |

***

## MySQL 高级函数

| 函数名                                                                                                                                                   | 描述                                                                                                                     | 实例                                                                                                        |
| ----------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------- |
| BIN(x)                                                                                                                                                | 返回 x 的二进制编码                                                                                                            | 15 的 2 进制编码:```
SELECT BIN(15); -- 1111
```                                                               |
| BINARY(s)                                                                                                                                             | 将字符串 s 转换为二进制字符串                                                                                                       | ```
SELECT BINARY "RUNOOB"; -> RUNOOB
```                                                                 |
| ```
CASE expression     WHEN condition1 THEN result1     WHEN condition2 THEN result2    ...     WHEN conditionN THEN resultN     ELSE result END
``` | CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 | ```
SELECT CASE  　　WHEN 1 > 0 　　THEN '1 > 0' 　　WHEN 2 > 0 　　THEN '2 > 0' 　　ELSE '3 > 0' 　　END ->1 > 0
``` |
| CAST(x AS type)                                                                                                                                       | 转换数据类型                                                                                                                 | 字符串日期转换为日期：```
SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29
```                                       |
| COALESCE(expr1, expr2, ...., expr_n)                                                                                                                  | 返回参数中的第一个非空表达式（从左向右）                                                                                                   | ```
SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com'); -> runoob.com
```                |
| CONNECTION_ID()                                                                                                                                       | 返回服务器的连接数                                                                                                              | ```
SELECT CONNECTION_ID(); -> 4292835
```                                                                |
| CONV(x,f1,f2)                                                                                                                                         | 返回 f1 进制数变成 f2 进制数                                                                                                     | ```
SELECT CONV(15, 10, 2); -> 1111
```                                                                   |
| CONVERT(s USING cs)                                                                                                                                   | 函数将字符串 s 的字符集变成 cs                                                                                                     | ```
SELECT CHARSET('ABC') ->utf-8      SELECT CHARSET(CONVERT('ABC' USING gbk)) ->gbk
```                 |
| CURRENT_USER()                                                                                                                                        | 返回当前用户                                                                                                                 | ```
SELECT CURRENT_USER(); -> guest@%
```                                                                 |
| DATABASE()                                                                                                                                            | 返回当前数据库名                                                                                                               | ```
SELECT DATABASE();    -> runoob
```                                                                   |
| IF(expr,v1,v2)                                                                                                                                        | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。                                                                                      | ```
SELECT IF(1 > 0,'正确','错误')     ->正确
```                                                               |
| [IFNULL(v1,v2)](http://www.runoob.com/mysql/mysql-func-ifnull.html)                                                                                   | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。                                                                                        | ```
SELECT IFNULL(null,'Hello Word') ->Hello Word
```                                                     |
| ISNULL(expression)                                                                                                                                    | 判断表达式是否为 NULL                                                                                                          | ```
SELECT ISNULL(NULL); ->1
```                                                                          |
| LAST_INSERT_ID()                                                                                                                                      | 返回最近生成的 AUTO_INCREMENT 值                                                                                               | ```
SELECT LAST_INSERT_ID(); ->6
```                                                                      |
| NULLIF(expr1, expr2)                                                                                                                                  | 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1                                                                      | ```
SELECT NULLIF(25, 25); ->
```                                                                         |
| SESSION_USER()                                                                                                                                        | 返回当前用户                                                                                                                 | ```
SELECT SESSION_USER(); -> guest@%
```                                                                 |
| SYSTEM_USER()                                                                                                                                         | 返回当前用户                                                                                                                 | ```
SELECT SYSTEM_USER(); -> guest@%
```                                                                  |
| USER()                                                                                                                                                | 返回当前用户                                                                                                                 | ```
SELECT USER(); -> guest@%
```                                                                         |
| VERSION()                                                                                                                                             | 返回数据库的版本号                                                                                                              | ```
SELECT VERSION() -> 5.6.34
```                                                                        |

## 自定义函数

对于任意一个函数，其都包含如下要素：

* 函数名；
* 参数类别（可以为空）；
* 返回值；
* 函数体（作用域）。

根据上面这些函数要素，我们就来尝试创建自定义函数。

### 创建函数

Copy the code

— Basic syntax Create function Function name ([parameter list]) RETURNS Data type BEGIN — Function body — Returns a value. The type is the specified data type end

* We can omit 'begin' and 'end' if we define a function whose body contains only return values. In addition, custom functions and system functions are called in the same way. Run the following statement to test:Copy the code

Create function showLove() returns int return 521; Select showLove();

*! [8](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/f0adc86cf5ad4869837c38e42b201570~tplv-k3u1fbpfcp-zoom-1.image) ### * 'show function status + [like 'pattern']; `! [9](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/98c9866b15a74090905450d92d5a0e73~tplv-k3u1fbpfcp-zoom-1.image) As shown in the figure above, we can see that the 'showLove' function belongs to the 'test' database. This leads to the property of the function that ** belongs to the specific database. Functions defined in a database cannot be used outside of its defined database, but you can see **. * 'show create function + function name; `! [10](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d5893dbe12ab4032a6751e6f29cc6f68~tplv-k3u1fbpfcp-zoom-1.image) A function can only be deleted and then added, and cannot be modified. The basic syntax for dropping functions is: * 'drop function + function name; 'Execute the following statement to test:Copy the code

Drop function showLove; Show function status like ‘showLove’\G;

*! [11](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6eb6767a22474f50bc6d5f9fdc5ffdb5~tplv-k3u1fbpfcp-zoom-1.image) For parameters of a function, there are two types, namely parameters and arguments. Parameters can be understood as parameters used in defining functions, and parameters must specify data types. Arguments can be understood as values or variables passed in when a function is called. * 'function name (parameter name and parameter type) returns data type. Next, we define a function that fulfills a simple requirement: find the sum from' 1 'to a specified value. The code is as follows:Copy the code

Delimiter $$create function addAll(num int) returns int begin — define the conditional variable set @i = 1; set @res = 0; Set @res = @res + @i; set @res = @res + @i; set @res = @res; Set @i = @i + 1; end while; Return @res; end

delimiter ; As shown above, the function has been defined successfully. Next, execute the following statement to test: — call the function summation select addAll(100); Select @res, @i; select @res, @i; ` ` ` *! [13](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/66ed695a61074e70a3d8c64bd1b0d9c9~tplv-k3u1fbpfcp-zoom-1.image) As shown in the figure above, the summation function ‘addAll’ has executed correctly. In addition, we find that the variables’ @res’ and ‘@i’ defined inside the function can also be viewed and used outside the function. This indicates that ** variables defined using the ‘@’ symbol are global variables **. In MySQL, there are two types of scope for variables: global and local. Global variables can be used anywhere. Local variables can only be used inside functions. * Global variables: defined with the ‘set’ keyword and identified with the ‘@’ symbol; Local variables: Declare with the ‘DECLARE’ keyword, and local variables used must be declared before the start of the function body. Next, we define a function using local variables to fulfill a simple requirement of summing ‘1’ to a specified value, requiring that multiples of ’10’ be left unadded. Delimiter $$CREATE function addAll2(num int) returns int BEGIN — Declare variables, including loop variables and result variables declare I int default 1; Declare RES int default 0; Mywhile :while I <= num do then — set I = I + 1; Iterate mywhile; end if; Set res = res + I; Set I = I + 1; end while; Return res; end

delimiter ;

*! [14](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4347809b7d7f460490d925357976f3f1~tplv-k3u1fbpfcp-zoom-1.image) As shown in the figure above, the function has been defined successfully. Next, execute the following statement to test:Copy the code

Select addAll(100), addAll2(100);


* ![15](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/e6cfad50aa1540b6a3ccede85aa9929d~tplv-k3u1fbpfcp-zoom-1.image)

如上图所示，函数已经正确执行。

***

**温馨提示**：符号`[]`括起来的内容，表示可选项；符号`+`，则表示连接的意思。

**delimiter：**

delimiter就是告诉mysql解释器，该段命令是否已经结束了，是否可以执行，默认情况下，delimiter是分号，遇到分号就执行，后面的双美元符号 就是告诉mysql，遇到双美元符号再执行。

 

# [递归查询](https://www.cnblogs.com/xiaoxi/p/5942805.html)

 

```sql
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_areainfo
-- ----------------------------
DROP TABLE IF EXISTS `t_areainfo`;
CREATE TABLE `t_areainfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `level` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parentId` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of t_areainfo
-- ----------------------------
INSERT INTO `t_areainfo` VALUES ('1', '0', '中国', '0', '0');
INSERT INTO `t_areainfo` VALUES ('2', '0', '华北区', '1', '0');
INSERT INTO `t_areainfo` VALUES ('3', '0', '华南区', '1', '0');
INSERT INTO `t_areainfo` VALUES ('4', '0', '北京', '2', '0');
INSERT INTO `t_areainfo` VALUES ('5', '0', '海淀区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('6', '0', '丰台区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('7', '0', '朝阳区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('8', '0', '北京XX区1', '4', '0');
INSERT INTO `t_areainfo` VALUES ('9', '0', '北京XX区2', '4', '0');
INSERT INTO `t_areainfo` VALUES ('10', '0', '北京XX区3', '4', '0');
INSERT INTO `t_areainfo` VALUES ('11', '0', '北京XX区4', '4', '0');
INSERT INTO `t_areainfo` VALUES ('12', '0', '北京XX区5', '4', '0');
INSERT INTO `t_areainfo` VALUES ('13', '0', '北京XX区6', '4', '0');
INSERT INTO `t_areainfo` VALUES ('14', '0', '北京XX区7', '4', '0');
INSERT INTO `t_areainfo` VALUES ('15', '0', '北京XX区8', '4', '0');
INSERT INTO `t_areainfo` VALUES ('16', '0', '北京XX区9', '4', '0');
INSERT INTO `t_areainfo` VALUES ('17', '0', '北京XX区10', '4', '0');
INSERT INTO `t_areainfo` VALUES ('18', '0', '北京XX区11', '4', '0');
INSERT INTO `t_areainfo` VALUES ('19', '0', '北京XX区12', '4', '0');
INSERT INTO `t_areainfo` VALUES ('20', '0', '北京XX区13', '4', '0');
INSERT INTO `t_areainfo` VALUES ('21', '0', '北京XX区14', '4', '0');
INSERT INTO `t_areainfo` VALUES ('22', '0', '北京XX区15', '4', '0');
INSERT INTO `t_areainfo` VALUES ('23', '0', '北京XX区16', '4', '0');
INSERT INTO `t_areainfo` VALUES ('24', '0', '北京XX区17', '4', '0');
INSERT INTO `t_areainfo` VALUES ('25', '0', '北京XX区18', '4', '0');
INSERT INTO `t_areainfo` VALUES ('26', '0', '北京XX区19', '4', '0');
INSERT INTO `t_areainfo` VALUES ('27', '0', '北京XX区1', '4', '0');
INSERT INTO `t_areainfo` VALUES ('28', '0', '北京XX区2', '4', '0');
INSERT INTO `t_areainfo` VALUES ('29', '0', '北京XX区3', '4', '0');
INSERT INTO `t_areainfo` VALUES ('30', '0', '北京XX区4', '4', '0');
INSERT INTO `t_areainfo` VALUES ('31', '0', '北京XX区5', '4', '0');
INSERT INTO `t_areainfo` VALUES ('32', '0', '北京XX区6', '4', '0');
INSERT INTO `t_areainfo` VALUES ('33', '0', '北京XX区7', '4', '0');
INSERT INTO `t_areainfo` VALUES ('34', '0', '北京XX区8', '4', '0');
INSERT INTO `t_areainfo` VALUES ('35', '0', '北京XX区9', '4', '0');
INSERT INTO `t_areainfo` VALUES ('36', '0', '北京XX区10', '4', '0');
INSERT INTO `t_areainfo` VALUES ('37', '0', '北京XX区11', '4', '0');
INSERT INTO `t_areainfo` VALUES ('38', '0', '北京XX区12', '4', '0');
INSERT INTO `t_areainfo` VALUES ('39', '0', '北京XX区13', '4', '0');
INSERT INTO `t_areainfo` VALUES ('40', '0', '北京XX区14', '4', '0');
INSERT INTO `t_areainfo` VALUES ('41', '0', '北京XX区15', '4', '0');
INSERT INTO `t_areainfo` VALUES ('42', '0', '北京XX区16', '4', '0');
INSERT INTO `t_areainfo` VALUES ('43', '0', '北京XX区17', '4', '0');
INSERT INTO `t_areainfo` VALUES ('44', '0', '北京XX区18', '4', '0');
INSERT INTO `t_areainfo` VALUES ('45', '0', '北京XX区19', '4', '0');
INSERT INTO `t_areainfo` VALUES ('46', '0', 'xx省1', '1', '0');
INSERT INTO `t_areainfo` VALUES ('47', '0', 'xx省2', '1', '0');
INSERT INTO `t_areainfo` VALUES ('48', '0', 'xx省3', '1', '0');
INSERT INTO `t_areainfo` VALUES ('49', '0', 'xx省4', '1', '0');
INSERT INTO `t_areainfo` VALUES ('50', '0', 'xx省5', '1', '0');
INSERT INTO `t_areainfo` VALUES ('51', '0', 'xx省6', '1', '0');
INSERT INTO `t_areainfo` VALUES ('52', '0', 'xx省7', '1', '0');
INSERT INTO `t_areainfo` VALUES ('53', '0', 'xx省8', '1', '0');
INSERT INTO `t_areainfo` VALUES ('54', '0', 'xx省9', '1', '0');
INSERT INTO `t_areainfo` VALUES ('55', '0', 'xx省10', '1', '0');
INSERT INTO `t_areainfo` VALUES ('56', '0', 'xx省11', '1', '0');
INSERT INTO `t_areainfo` VALUES ('57', '0', 'xx省12', '1', '0');
INSERT INTO `t_areainfo` VALUES ('58', '0', 'xx省13', '1', '0');
INSERT INTO `t_areainfo` VALUES ('59', '0', 'xx省14', '1', '0');
INSERT INTO `t_areainfo` VALUES ('60', '0', 'xx省15', '1', '0');
INSERT INTO `t_areainfo` VALUES ('61', '0', 'xx省16', '1', '0');
INSERT INTO `t_areainfo` VALUES ('62', '0', 'xx省17', '1', '0');
INSERT INTO `t_areainfo` VALUES ('63', '0', 'xx省18', '1', '0');
INSERT INTO `t_areainfo` VALUES ('64', '0', 'xx省19', '1', '0');
Copy the code

3. Downward recursion:

Recursive query using find_in_set() and group_concat() functions:

 

DROP FUNCTION IF EXISTS queryChildrenAreaInfo;
CREATE FUNCTION queryChildrenAreaInfo(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_areainfo WHERE FIND_IN_SET(parentId,sTempChd)>0;
END WHILE;
RETURN sTemp;
END;
Copy the code

 

4. Call method:

SELECT queryChildrenAreaInfo(1);
Copy the code

Query all nodes whose ID is below “4”

SELECT * FROM t_areainfo WHERE FIND_IN_SET(id,queryChildrenAreaInfo(4));
Copy the code

5. Upward recursion:

 

DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;
CREATE FUNCTION queryChildrenAreaInfo1(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);
SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
WHILE sTempChd <> 0 DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
END WHILE;
RETURN sTemp;
END;
Copy the code

6. Call Method:

Query all upper-layer nodes of node “7” :

SELECT * from t_areainfo where FIND_IN_SET(id,queryChildrenAreaInfo1(7));
Copy the code