Has it been too long for the sage? -- MySQL continue playing _ MySQL

1. MySQL ----- operator and function ----- character function, numerical operator, comparison operation, etc. ---- character function --- CONCAT () -- character connection CONCAT_WS () -- use the specified separator to connect to mysqlSELECTCONCAT (#39; imooc #39;, #39;-#39 ;,# 39; MySQ .. 1. MySQL

----- Operators and functions -----

Character functions, numeric operators, and comparison operations

---- Character functions

---

CONCAT () -- character connection

CONCAT_WS () -- Use the specified separator to connect characters

Mysql> select concat ('imooc ','-', 'mysql ');

+ ----------------------------- +

| CONCAT ('imooc ','-', 'mysql') |

+ ----------------------------- +

| Imooc-MySQL |

+ ----------------------------- +

Mysql> use test;

Database changed

Mysql> SELECT * from test;

+ ---- + ---------- +

| Id | username |

+ ---- + ---------- +

| 1 | Tom |

| 2 | Ben |

+ ---- + ---------- +

2 rows in set (0.02 sec)

Mysql> select concat (id, username) AS fullname FROM test;

+ ---------- +

| Fullname |

+ ---------- +

| 1Tom |

| 2Ben |

+ ---------- +

The above is the CONCAT () instance, while CONCAT_WS () is as follows:

Mysql> SELECT CONCAT_WS ('-', id, username) FROM test;

+ ---------------------------- +

| CONCAT_WS ('-', id, username) |

+ ---------------------------- +

| 1-Tom |

| 2-Ben |

+ ---------------------------- +

CONCAT_WS () requires at least three parameters. The first one is the separator, and the following is what needs to be connected.

---

FORMAT (), number formatting

Mysql> select format (234234.23423, 2 );

+ ------------------------ +

| FORMAT (234234.23423, 2) |

+ ------------------------ +

| 1, 234,234.23 |

+ ------------------------ +

It is standardization, of course, the set of foreigners.

---

LOWER () & UPPER (), case-insensitive

---

LEFT () & RIGHT (), get the character on the LEFT and RIGHT

Two parameters are required. which field is used, and the left and right sides starting from the nth digit?

Mysql> select upper (LEFT ('Andy ', 1 ));

+ ----------------------- +

| UPPER (LEFT ('Andy ', 1) |

+ ----------------------- +

| A |

+ ----------------------- +

---

LENGTH () to obtain the string LENGTH. Note that spaces are also included

---

LTRIM (), RTRIM (), TRIM (), delete spaces before/after/before

TRIM can also delete other characters

Mysql> select trim (LEADING '! 'From '!!! ANDY !! ');

+ ------------------------------------ +

| TRIM (LEADING '! 'From '!!! ANDY !! ') |

+ ------------------------------------ +

| ANDY !! |

+ ------------------------------------ +

1 row in set (0.00 sec)

Mysql> select trim (BOTH '! 'From '!!! ANDY !! ');

+ --------------------------------- +

| TRIM (BOTH '! 'From '!!! ANDY !! ') |

+ --------------------------------- +

| ANDY |

+ --------------------------------- +

1 row in set (0.00 sec)

Mysql> select trim (TRAILING '! 'From '!!! ANDY !! ');

+ ------------------------------------- +

| TRIM (TRAILING '! 'From '!!! ANDY !! ') |

+ ------------------------------------- +

| !!! ANDY |

+ ------------------------------------- +

1 row in set (0.00 sec)

---

REPLACE ()

Mysql> select replace ('!! ANDY! SDL !! ','! ','');

+ -------------------------------- +

| REPLACE ('!! ANDY! SDL !! ','! ', '') |

+ -------------------------------- +

| ANDYSDL |

+ -------------------------------- +

1 row in set (0.00 sec)

Mysql> select replace ('!! ANDY! SDL !! ','! ', 'Lalal ');

+ ------------------------------------- +

| REPLACE ('!! ANDY! SDL !! ','! ', 'Lalal') |

+ ------------------------------------- +

| LALALLALALANDYLALALSDLLALALLALAL |

+ ------------------------------------- +

1 row in set (0.00 sec)

As you can see, replacement is flexible.

---

SUBSTRING ()

Mysql> select substring ('mysql', '1', '2 ');

+ ---------------------------- +

| SUBSTRING ('mysql', '1', '2') |

+ ---------------------------- +

| MY |

+ ---------------------------- +

1 row in set (0.00 sec)

Note that mysql starts from 1, not from 0.

---

Match

Mysql> SELECT 'mysql' LIKE'm % ';

+ ------------------- +

| 'Mysql' LIKE'm % '|

+ ------------------- +

| 1 |

+ ------------------- +

1 row in set (0.00 sec)

Mysql> SELECT * FROM test WHERE username LIKE '% m % ';

+ ---- + ---------- +

| Id | username |

+ ---- + ---------- +

| 1 | Tom |

+ ---- + ---------- +

Here, % represents arbitrary, similar to * in window *

_ Represents any character

---- Numeric operators and functions

A brief introduction to some common functions

CEIL ()-rounded up --------- FLOOR ()-rounded down

DIV ()-integer division, that is, only integers are returned.

MOD ()-obtain the remainder

POWER ()-POWER operation

ROUND ()-rounding

TRUNCATE ()-number truncation (no rounding)

Either two parameters or one parameter. try it by yourself.

---- Comparison operators and functions

---

[NOT] BETWEEN... AND...

Mysql> SELECT 123 BETWEEN 2 AND 123123;

+ -------------------------- +

| 123 BETWEEN 2 AND 123123 |

+ -------------------------- +

| 1 |

+ -------------------------- +

---

[NOT] IN (), given several intervals for judgment

Mysql> SELECT 123 IN (, 23, 12 );

+ ------------------- +

| 123 IN (123,23, 12) |

+ ------------------- +

| 1 |

+ ------------------- +

1 row in set (0.00 sec)

Mysql> SELECT 123 IN (, 23, 12 );

+ ------------------- +

| 123 IN (120,23, 12) |

+ ------------------- +

| 0 |

+ ------------------- +

---

IS [NOT] NULL. IS it NULL?

Mysql> SELECT 0 is null;

+ ----------- +

| 0 is null |

+ ----------- +

| 0 |

+ ----------- +

It is often used to check whether it is empty

Mysql> SELECT * FROM test WHERE username is null;

Empty set (0.00 sec)

---- Date and time functions

---

NOW (), returns the current date and time

CURDATE (), CURTIME ()

---

DATE_ADD (), changes to the current date

Mysql> SELECT DATE_ADD ('2014-2-23 ', INTERVAL 2012 DAY );

+ ----------------------------------------- +

| DATE_ADD ('2014-2-23 ', INTERVAL 2012 DAY) |

+ ----------------------------------------- +

| 2012-10-14 |

+ ----------------------------------------- +

1 row in set (0.00 sec)

Mysql> SELECT DATE_ADD ('2014-2-23 ', INTERVAL-2012 DAY );

+ ------------------------------------------ +

| DATE_ADD ('2014-2-23 ', INTERVAL-2012 DAY) |

+ ------------------------------------------ +

| 2011-07-04 |

+ ------------------------------------------ +

1 row in set (0.00 sec)

---

DATEDIFF () to obtain the difference between the two days

Mysql> select datediff ('2014-2-2 ', '2014-3-22 ');

+ ---------------------------------- +

| DATEDIFF ('2014-2-2 ', '2014-3-22') |

+ ---------------------------------- +

| 1, 390760 |

+ ---------------------------------- +

1 row in set (0.02 sec)

---

DATE_FORMAT (): format of the date. The format of the date is optional, that is, format conversion of the date.

Mysql> SELECT DATE_FORMAT ('2017-2-22 ',' % m/% d/% Y ');

+ ------------------------------------- +

| DATE_FORMAT ('2017-2-22 ',' % m/% d/% Y') |

+ ------------------------------------- +

| 02/22/13 |

+ ------------------------------------- +

1 row in set (0.00 sec)

---- Information functions

USER (), VERSION (), DATEBASE (), CONNECTION_ID (), LAST_INSERT_ID ()

---- Aggregate functions

Only one returned value is their characteristic

AVG (), COUNT (), MAX (), MIN (), SUM ()

Direct calling may result in errors. this operation is generally performed on fields in the data table.

Mysql> select avg (id) FROM test;

+ --------- +

| AVG (id) |

+ --------- +

| 1, 1.5000 |

+ --------- +

---- Encryption function

MD5 (), PASSWORD ()

Mysql> SELECT MD5 ('helloworld ');

+ ---------------------------------- +

| MD5 ('helloworld') |

+ ---------------------------------- +

| E81e26d88d62aba9ab55b632f25f117d |

+ ---------------------------------- +

1 row in set (0.00 sec)

Mysql> select password ('helloworld ');

+ ------------------------------------------- +

| PASSWORD ('helloworld') |

+ ------------------------------------------- +

| * 3456E7782A7F539BC823C715DB60231B0C7DE847 |

+ ------------------------------------------- +

1 row in set (0.00 sec)

Generally, MD5 is used for web page programming. the password is only used to modify the password of the current user.

----

Focus on the familiarity and understanding of built-in functions, flexible calling and nested application