Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the common operators and functions of MYSQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

MySQL character function

(1) CONCAT (): character concatenation.

SELECT CONCAT ('IMOOC','-','MySQL'); / / IMOOC-MySQL

SELECT CONCAT (first_name,last_name) AS fullname FROM test

(2) CONCAT_WS (): use the specified delimiter for character concatenation (the first position specifies the delimiter, followed by the segmented content).

SELECT CONCAT_WS ('%', 'abc','def'); / / the first is the specified delimiter; / / abc%def

(3) FORMAT (): numeric format.

SELECT FORMAT (123560.75); / / 123560.75; if the second parameter is 0 (rounded), the integer is displayed.

(4) LOWER (): convert lowercase.

(5) UPPER (): convert uppercase.

SELECT UPPER ('mysql'); / / MYSQL

(6) LEFT (): get the left character.

(7) RIGHT (): gets the right character.

SELECT LEFT ('MYSQL',2); / / MY

SELECT LOWER (LEFT ('mYsql',2)); / / function nesting; / / my

(8) LENGTH (): gets the string length.

LENGTH ('MYSQL'); / / 6; finally add a space.

(9) LTRIM (): clear the front (left end) space of the string.

(10) RTRIM (): clears the space at the end (right end) of the string.

(11) TRIM (): removes leading, trailing spaces, or specified characters.

SELECT TRIM ('MySQL')

SELECT TRIM (LEADING'?' FROM'? MYSQL???'); / / Delete the leader?

SELECT TRIM (TRAILING'?' FROM'? MYSQL???'); / / Delete follow-up?

SELECT TRIM (BOTH'?' FROM'? MYSQL???'); / / delete both front and back, but cannot delete the middle?

(12) REPLACE (): replace

SELECT REPLACE ('MY???SQL','?', "); / / replace the question mark with a blank, that is, remove it?

SELECT REPLACE ('MY???SQL','?','~~'); / / can you put n? Replace it with m combination symbols.

(13) SUBSTRING (): string interception (starting with 1, programming may start with 0).

SELECT SUBSTRING ('mYSQL',1,2); / / intercepts 2 characters mY from the first position in the string.

SELECT SUBSTRING ('mYSQL',3); / / SQL is intercepted from position 3 to the end.

SELECT SUBSTRING ('mYSQL',-2); / / QL is intercepted from the penultimate position to the end.

SELECT SUBSTRING ('mYSQL',-3,-1); / / F, length cannot be negative, but some programming languages support it, but mysql does not.

(14) [NOT] LIKE: pattern matching (% represents any character; underscore _ represents any character).

Example: "tom%".

SELECT * FROM test WHERE first_name LIKE'% 1%% 'ESCAPE' 1characters; / / remove the compilation after'1' and look for any character of the information'_ 'that contains the'% 'symbol.

MySQL numeric operator function

SELECT CEIL (3.01) = > 4 / * when there is n.xx, it is all rounded up.

SELECT FLOOR (3.99) = > 3 / * all n.xx is rounded down by n-rounding.

SELECT 3 DIV 4 = > 0 / * integer division, take quotient.

SELECT 3 + 4 = > 0.75 / * division.

SELECT 2.1 MOD 2 = > 0.1 / * the remainder (modulo) the divisor is an integer or decimal.

SELECT 21% 2 = > 1 / * take the remainder mod=%.

The fourth power operation of SELECT POWER (3) = > 81 / * 3.

SELECT ROUND (3.1415926) = > 3.1416 / * is rounded to keep 4 decimal places.

SELECT TRUNCATE (123.89) = > 123.8 / * the number of digits after the decimal point is intercepted.

SELECT TRUNCATE (123.89. 0) = > 123 / * the position after the decimal point is truncated, and 0 bit is the integer part.

SELECT TRUNCATE (123.89) = > 120 / * intercept-1, remove the following value from the bit and replace it with 0.

MySQL compare operators and functions

[NOT] BETWEEN... AND... [not] within the range, the result returns 1 or 0.

For example: 15 BETWEEN 10 AND 16; / / 1.

[NOT] IN () lists the value options.

For example: SELECT 10 IN (5, 10, 10, 25) / / 1.

IS [NOT] NULL: only NULL IS [NOT] NULL returns 1, and all other cases (including ", 0) return 0.

MySQL date-time function

(1) SELECT NOW (); / / current date and time.

(2) SELECT CURDATE (); / / current date.

(3) SELECT CURTIME (); / / current time.

(4) SELECT DATE_ADD ('2014-3-12 minute minute [-] 365 DAY); / / 2015-03-12 minute day can also be replaced by WEEK,MONTH,YEAR, etc. DATE_ADD does not mean to increase, but to change. It can be increased or subtracted.

(5) the difference between DATEDIFF () / / dates.

DATEDIFF ('2013-3-12); / / the date difference.

(6) DATE_FORMAT () / / date formatting.

SELECT DATE_FORMAT ('2014-3-2 leading zero% m% d% Y'); / 03Universe 02Universe 2014 ('% 'with a leading 0).

MySQL information function

SELECT CONNECTION_ID (); = > 2 / * Connect to ID.

SELECT DATABASE (); / * the currently open database.

SELECT LAST_INSERT_ID (); / * the last inserted record that writes multiple records at the same time returns only the id of the first entry written. And there must be auto-numbered fields in the table.

DESC tab_name; / * View the database fields and properties, and view the table structure.

SELECT USER (); / * prints the current user information.

SELECT VERSION (); / * prints the current database information.

Aggregate function

There is only one return value

AVG ()-average

SELECT ROUND (AVG (goods_price), 2) AS avg_price FROM tdb_goods

COUNT ()-count

SELECT COUNT (goods_id) as counts FROM tdb_goods

MAX ()-maximum

SELECT MAX (goods_price) as counts FROM tdb_goods

MIN ()-minimum

SUM ()-summation

SELECT SUM (goods_price) as counts FROM tdb_goods

Encryption function

MD5 (): information summary algorithm.

Select md5 ('admin'); / / encrypt admin. If the information in mysql is in preparation for web development, try to use md5.

Password (): cryptographic algorithm.

Set password=password ('1236') changed the current login password to 1236.

This is the end of this article on "what are the common operators and functions of MYSQL?". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.