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