Summary of common functions of MySQL (II.)

Sometimes we need to know the current time, and then we can call the time function. Here are some of the commonly used time functions

Here's a look at Date_format (date,format): format can have the following formatting characters: for instance!

Mysql> SELECT Date_format ('2017-10-04 22:23:00','%W%M%Y');+------------------------------------------------+| Date_format ('2017-10-04 22:23:00','%W%M%Y') |+------------------------------------------------+| Sunday October the|+------------------------------------------------+Rowinch Set(0.00sec) MySQL> SELECT date_format (Now (),'%H%k%I%r%T%s%w');+-------------------------------------------+| Date_format (now (),'%H%k%I%r%T%s%w') |+-------------------------------------------+| at  at  One  One: the: $Pm at: the: $  $ 5|+-------------------------------------------+Rowinch Set(0.00Sec

Look again Date_add (Date,interval expr unit): Where INTERVAL is the keyword, expr is an expression, unit is the interval type, and MySQL provides the following interval types:

Mysql> SELECT Date_add ('2000-12-31 23:59:59', INTERVAL1SECOND);+---------------------------------------------------+| Date_add ('2000-12-31 23:59:59', INTERVAL1SECOND) |+---------------------------------------------------+|2001- on- on xx:xx:xx|+---------------------------------------------------+Rowinch Set(0.00sec) MySQL> SELECT Date_add ('2100-12-31 23:59:59', INTERVAL'1:1'minute_second);+--------------------------------------------------------------+| Date_add ('2100-12-31 23:59:59', INTERVAL'1:1'Minute_second) |+--------------------------------------------------------------+|2101- on- on xx: on:xx|+--------------------------------------------------------------+Rowinch Set(0.00Sec

Process functions are also a common class of functions that can be used by users to implement conditional selection in an SQL statement, which improves the efficiency of the statement.

Example:

There are now employee Payroll tables as follows:

Mysql>Select* fromsalary;+----+---------+| ID | Salary |+----+---------+|1|1000.00||2|2000.00||3|3000.00||4|4000.00||5|5000.00||6|6000.00||7|7000.00||8| NULL |+----+---------+rowsinch Set(0.00Sec

(1) When wages above 5000 show high wages, less than 5000 show low wages:

Mysql>SelectIdif(salary> the,'High Wages','Low Wages') asLevel fromsalary;+----+-----------+| ID | Level |+----+-----------+|1| Low Wages | |2| Low Wages | |3| Low Wages | |4| Low Wages | |5| Low Wages | |6| High Wages | |7| High Wages | |8| Low wage |+----+-----------+rowsinch Set(0.00Sec

(2) When there is no salary, use 0 instead:

Select ifnull (Salary,0 from salary; +------------------+| Ifnull (Salary,0) |+------------------+|          1000.00 | |          2000.00 | |          3000.00 | |          4000.00 | |          5000.00 | |          6000.00 | |          7000.00 | |             0.00 |+------------------+inset (0.00 sec)

(3) Use case when to implement the example:

Mysql>SelectId CaseWhen salary> theThen'High Wages' Else 'Low Wages'End fromsalary;+----+-------------------------------------------------------------+| ID | CaseWhen salary> theThen'High Wages' Else 'Low Wages'End |+----+-------------------------------------------------------------+|1| Low Wages | |2| Low Wages | |3| Low Wages | |4| Low Wages | |5| Low Wages | |6| High Wages | |7| High Wages | |8| Low wage |+----+---------------------------------------------------------- ---+rowsinch Set(0.00Sec

(4) When the salary is 1000 for the extra low wage, the salary is 2000 for the low wage, the rest for the high salary:

Mysql>SelectId CaseSalary when +Then'Extra Low Pay'When -Then'Low Wages' Else 'High Wages'End fromsalary;+----+-------------------------------------------------------------------------------------------+| ID | CaseSalary when +Then'Extra Low Pay'When -Then'Low Wages' Else 'High Wages'End |+----+-------------------------------------------------------------------------------------------+|1| Extra Low Wage | |2| Low Wages | |3| High Wages | |4| High Wages | |5| High Wages | |6| High Wages | |7| High Wages | |8| High salary |+----+---------------------------- ---------------------------------------------------------------+rowsinch Set(0.00Sec

Other functions

MySQL built-in functions there are many, here is not introduced, the specific can refer to the official documents, there are also a lot of.

Here are some of the less commonly used custom functions, the custom function syntax is as follows:

CREATE FUNCTION func_name ([parameter1,parameter2 ...])    RETURNS type    runtime_body

Here's how to create a function, such as customizing an addition function for two integers:

DELIMITER $ CREATE FUNCTION INT INT RETURNS INT BEGIN DECLARE int DEFAULT 0  SET=+RETURN(NUM); END $DELIMITER;

The Execute function uses Select to:

Mysql> SELECTMySum1,2);+------------+|MySum1,2)|+------------+|          3 |+------------+Rowinch Set(0.00Sec

To delete a custom function using:

DROP FUNCTION Func_name

For example, delete the function created above:

MySQL>dropfunction0 rows affected (0.00 sec)

Summary of common functions of MySQL (II.)