MySQL常用函数介绍
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.操作符介绍
1>.操作符优先级
mysql> SELECT 10+20*30; +----------+ | 10+20*30 | +----------+ | 610 | +----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT (10+20)*30; +------------+ | (10+20)*30 | +------------+ | 900 | +------------+ 1 row in set (0.00 sec) mysql> mysql>
2>.对比操作符
• 对比操作符的执行结果为true,false,null三种
• Between A and B 代表检查值是否在A和B之间
• Coalesce()代表返回第一个非Null的值
• =代表相等操作符
• >代表大于操作符
• >=代表大于等于操作符
• Greatest()代表返回最大的值
• In()代表检查值是否在一系列的值之中
• Interval()代表返回比第一个参数小的参数的位置
• is/is not代表检查值是否与布尔值相同/不同 • Is not null代表检查值是否是非NULL
• Is null代表检查值是否是NULL
• Isnull()代表检查参数是NULL
• Least()代表返回最小的参数 • <代表小于操作符
• <=代表小于等于操作符
• Like代表字符匹配
• Not between A and B代表检查值是否不在A和B的范围之内 • !=/<>代表不等于操作符
• Not in()代表检查值是否不在一系列值的当中 • Not like代表检查值是否不匹配
• Strcmp()对比两个字符串
mysql> SELECT 1 = 0; +-------+ | 1 = 0 | +-------+ | 0 | +-------+ 1 row in set (0.01 sec) mysql> mysql> SELECT "0" = 0; +---------+ | "0" = 0 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> mysql> SELECT "0.0" = 0; +-----------+ | "0.0" = 0 | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT "0.0001" = 0; +--------------+ | "0.0001" = 0 | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT ".01" = 0.01; +--------------+ | ".01" = 0.01 | +--------------+ | 1 | +--------------+ 1 row in set (0.01 sec) mysql>
mysql> SELECT ".01" <> "0.01"; +-----------------+ | ".01" <> "0.01" | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT .01 <> "0.01"; +---------------+ | .01 <> "0.01" | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT .01 != "0.01"; +---------------+ | .01 != "0.01" | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT "yinzhengjie" <> "zhengjie"; +-----------------------------+ | "yinzhengjie" <> "zhengjie" | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.00 sec) mysql> SELECT "yinzhengjie" != "zhengjie"; +-----------------------------+ | "yinzhengjie" != "zhengjie" | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT 2 <= 3; +--------+ | 2 <= 3 | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 3 <= 2; +--------+ | 3 <= 2 | +--------+ | 0 | +--------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT 100 < 200; +-----------+ | 100 < 200 | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 100 < 100; +-----------+ | 100 < 100 | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT 100 >= 200; +------------+ | 100 >= 200 | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 200 >= 100; +------------+ | 200 >= 100 | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT 200 > 100; +-----------+ | 200 > 100 | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 100 > 200; +-----------+ | 100 > 200 | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT 100 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; +-------------+------------+-----------------+ | 100 IS TRUE | 0 IS FALSE | NULL IS UNKNOWN | +-------------+------------+-----------------+ | 1 | 1 | 1 | +-------------+------------+-----------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT 200 IS TRUE, 200 IS FALSE, 200 IS UNKNOWN; +-------------+--------------+----------------+ | 200 IS TRUE | 200 IS FALSE | 200 IS UNKNOWN | +-------------+--------------+----------------+ | 1 | 0 | 0 | +-------------+--------------+----------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 0 IS TRUE, 0 IS FALSE, 0 IS UNKNOWN; +-----------+------------+--------------+ | 0 IS TRUE | 0 IS FALSE | 0 IS UNKNOWN | +-----------+------------+--------------+ | 0 | 1 | 0 | +-----------+------------+--------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT NULL IS TRUE, NULL IS FALSE, NULL IS UNKNOWN; +--------------+---------------+-----------------+ | NULL IS TRUE | NULL IS FALSE | NULL IS UNKNOWN | +--------------+---------------+-----------------+ | 0 | 0 | 1 | +--------------+---------------+-----------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT 10 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN; +-------------------+------------------+---------------------+ | 10 IS NOT UNKNOWN | 0 IS NOT UNKNOWN | NULL IS NOT UNKNOWN | +-------------------+------------------+---------------------+ | 1 | 1 | 0 | +-------------------+------------------+---------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 10 IS NOT TRUE,0 IS NOT FALSE,NULL IS NOT UNKNOWN; +----------------+----------------+---------------------+ | 10 IS NOT TRUE | 0 IS NOT FALSE | NULL IS NOT UNKNOWN | +----------------+----------------+---------------------+ | 0 | 0 | 0 | +----------------+----------------+---------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 10 IS NOT FALSE,0 IS NOT TRUE,NULL IS NOT UNKNOWN; +-----------------+---------------+---------------------+ | 10 IS NOT FALSE | 0 IS NOT TRUE | NULL IS NOT UNKNOWN | +-----------------+---------------+---------------------+ | 1 | 1 | 0 | +-----------------+---------------+---------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 10 IS NOT FALSE,0 IS NOT TRUE,100 IS NOT UNKNOWN; +-----------------+---------------+--------------------+ | 10 IS NOT FALSE | 0 IS NOT TRUE | 100 IS NOT UNKNOWN | +-----------------+---------------+--------------------+ | 1 | 1 | 1 | +-----------------+---------------+--------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL; +-----------+-----------+--------------+ | 1 IS NULL | 0 IS NULL | NULL IS NULL | +-----------+-----------+--------------+ | 0 | 0 | 1 | +-----------+-----------+--------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL; +---------------+---------------+------------------+ | 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL | +---------------+---------------+------------------+ | 1 | 1 | 0 | +---------------+---------------+------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1; +-------------------+-------------------+ | 2 BETWEEN 1 AND 3 | 2 BETWEEN 3 and 1 | +-------------------+-------------------+ | 1 | 0 | +-------------------+-------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 1 BETWEEN 2 AND 3; +-------------------+ | 1 BETWEEN 2 AND 3 | +-------------------+ | 0 | +-------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT'b'BETWEEN'a'AND'c'; +---------------------+ | 'b'BETWEEN'a'AND'c' | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 2 BETWEEN 2 AND '3'; +---------------------+ | 2 BETWEEN 2 AND '3' | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 2 BETWEEN 2 AND 'x-3'; +-----------------------+ | 2 BETWEEN 2 AND 'x-3' | +-----------------------+ | 0 | +-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql>
mysql> mysql> SELECT COALESCE(NULL,200); +--------------------+ | COALESCE(NULL,200) | +--------------------+ | 200 | +--------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT COALESCE(NULL,NULL,NULL); +--------------------------+ | COALESCE(NULL,NULL,NULL) | +--------------------------+ | NULL | +--------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT GREATEST(200,0); +-----------------+ | GREATEST(200,0) | +-----------------+ | 200 | +-----------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT GREATEST(18.0,3.1415926,5.2,1314.520); +---------------------------------------+ | GREATEST(18.0,3.1415926,5.2,1314.520) | +---------------------------------------+ | 1314.5200000 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT GREATEST('B','A','C','D','E','F','G'); +---------------------------------------+ | GREATEST('B','A','C','D','E','F','G') | +---------------------------------------+ | G | +---------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT 5 IN (1,5,15,20,25); +---------------------+ | 5 IN (1,5,15,20,25) | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 10 IN (1,5,15,20,25); +----------------------+ | 10 IN (1,5,15,20,25) | +----------------------+ | 0 | +----------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 'zhengjie' IN ('yinzhengjie'); +-------------------------------+ | 'zhengjie' IN ('yinzhengjie') | +-------------------------------+ | 0 | +-------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 'zhengjie' IN ('yinzhengjie','zhengjie'); +------------------------------------------+ | 'zhengjie' IN ('yinzhengjie','zhengjie') | +------------------------------------------+ | 1 | +------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT(20,30) IN ((10,20),(30,40)); +------------------------------+ | (20,30) IN ((10,20),(30,40)) | +------------------------------+ | 0 | +------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT(20,30) IN ((10,20),(30,20)); +------------------------------+ | (20,30) IN ((10,20),(30,20)) | +------------------------------+ | 0 | +------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT(20,30) IN ((10,20),(20,30)); +------------------------------+ | (20,30) IN ((10,20),(20,30)) | +------------------------------+ | 1 | +------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT ISNULL(10*20); +---------------+ | ISNULL(10*20) | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT ISNULL(10/0); +--------------+ | ISNULL(10/0) | +--------------+ | 1 | +--------------+ 1 row in set, 1 warning (0.00 sec) mysql> mysql>
mysql> mysql> SELECT LEAST(10,20,30); +-----------------+ | LEAST(10,20,30) | +-----------------+ | 10 | +-----------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LEAST(9.8,3.1415926,5.20); +---------------------------+ | LEAST(9.8,3.1415926,5.20) | +---------------------------+ | 3.1415926 | +---------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LEAST('D','C','A','F'); +------------------------+ | LEAST('D','C','A','F') | +------------------------+ | A | +------------------------+ 1 row in set (0.00 sec) mysql>
3>.逻辑操作符
逻辑操作符返回1（TRUE），0（FALSE），或者NULL。
mysql> SELECT NOT 100; +---------+ | NOT 100 | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) mysql> mysql> SELECT NOT 0; +-------+ | NOT 0 | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) mysql> mysql> SELECT NOT NULL; +----------+ | NOT NULL | +----------+ | NULL | +----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT !(10+20); +----------+ | !(10+20) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT ! 10 + 12; +-----------+ | ! 10 + 12 | +-----------+ | 12 | +-----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT ! 10 + 1; +----------+ | ! 10 + 1 | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT 1 AND 1; +---------+ | 1 AND 1 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 1 AND 0; +---------+ | 1 AND 0 | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 1 AND NULL; +------------+ | 1 AND NULL | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 0 AND NULL; +------------+ | 0 AND NULL | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT NULL AND 0; +------------+ | NULL AND 0 | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT NULL AND 1; +------------+ | NULL AND 1 | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT 2 OR 3; +--------+ | 2 OR 3 | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT 2 OR 0; +--------+ | 2 OR 0 | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 0 OR 0; +--------+ | 0 OR 0 | +--------+ | 0 | +--------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 0 OR NULL; +-----------+ | 0 OR NULL | +-----------+ | NULL | +-----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 1 OR NULL; +-----------+ | 1 OR NULL | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT 2 XOR 3; +---------+ | 2 XOR 3 | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 2 XOR 0; +---------+ | 2 XOR 0 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 2 XOR NULL; +------------+ | 2 XOR NULL | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 2 XOR 3 XOR 4; +---------------+ | 2 XOR 3 XOR 4 | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) mysql>
4>.分配操作符
所谓的分配操作符就是赋值操作。
mysql> SELECT @name,@age; #MySQL使用“@”来声明一个变量 +-------+------+ | @name | @age | +-------+------+ | NULL | NULL | +-------+------+ 1 row in set (0.00 sec) mysql> mysql> SELECT @name:='yinzhengjie',@age:=18; #MySQL使用":="来进行赋值操作 +----------------------+----------+ | @name:='yinzhengjie' | @age:=18 | +----------------------+----------+ | yinzhengjie | 18 | +----------------------+----------+ 1 row in set, 2 warnings (0.00 sec) mysql> mysql> SELECT @name,@age; +-------------+------+ | @name | @age | +-------------+------+ | yinzhengjie | 18 | +-------------+------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT * FROM yinzhengjie.teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 林俊杰 | 9 | +----+-----------+-----------+ 9 rows in set (0.00 sec) mysql> mysql> SELECT @lines:=COUNT(*) FROM yinzhengjie.teacher; +------------------+ | @lines:=COUNT(*) | +------------------+ | 9 | +------------------+ 1 row in set, 1 warning (0.00 sec) mysql>
mysql> CREATE TABLE students(id INT(11),name VARCHAR(30)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO students(id,name) VALUES(1,'jason'),(2,'jay'),(3,'lady Gaga'),(4,'G.E.M'),(5,'JJ lin'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM students; +------+-----------+ | id | name | +------+-----------+ | 1 | jason | | 2 | jay | | 3 | lady Gaga | | 4 | G.E.M | | 5 | JJ lin | +------+-----------+ 5 rows in set (0.00 sec) mysql> mysql> UPDATE students SET name = 'yinzhengjie' WHERE id = @number:=1; Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> mysql> SELECT * FROM students; +------+-------------+ | id | name | +------+-------------+ | 1 | yinzhengjie | | 2 | jay | | 3 | lady Gaga | | 4 | G.E.M | | 5 | JJ lin | +------+-------------+ 5 rows in set (0.00 sec) mysql> mysql> mysql> SELECT @number; +---------+ | @number | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql
"=" 操作符在两种情况下会被认为是赋值操作，而其他情况下会认 为是对比操作符。
第一：在SET语句中，“=”操作符会被认为是赋值操作；
第二：在“update”语句中的SET子句中，“=”操作符也会被认为是赋值操作。
mysql> SELECT @QQ; +------+ | @QQ | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> mysql> SET @QQ = 1053419035; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT @QQ; +------------+ | @QQ | +------------+ | 1053419035 | +------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> CREATE TABLE students(id INT(11),name VARCHAR(30)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO students(id,name) VALUES(1,'jason'),(2,'jay'),(3,'lady Gaga'),(4,'G.E.M'),(5,'JJ lin'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM students; +------+-----------+ | id | name | +------+-----------+ | 1 | jason | | 2 | jay | | 3 | lady Gaga | | 4 | G.E.M | | 5 | JJ lin | +------+-----------+ rows in set (0.00 sec) mysql> mysql> UPDATE students SET name = 'yinzhengjie' WHERE id = @number:=1; Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> mysql> SELECT * FROM students; +------+-------------+ | id | name | +------+-------------+ | 1 | yinzhengjie | | 2 | jay | | 3 | lady Gaga | | 4 | G.E.M | | 5 | JJ lin | +------+-------------+ rows in set (0.00 sec) mysql> mysql> mysql> SELECT @number; +---------+ | @number | +---------+ | 1 | +---------+ row in set (0.00 sec)
二.常见的函数介绍
1>.流程控制函数
mysql> ? CASE OPERATOR Name: 'CASE OPERATOR' Description: Syntax: CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END The first CASE syntax returns the result for the first value=compare_value comparison that is true. The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part. *Note*: The syntax of the CASE expression described here differs slightly from that of the SQL CASE statement described in [HELP CASE statement], for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END. The return type of a CASE expression result is the aggregated type of all result values: o If all types are numeric, the aggregated type is also numeric: o If at least one argument is double precision, the result is double precision. o Otherwise, if at least one argument is DECIMAL, the result is DECIMAL. o Otherwise, the result is an integer type (with one exception): o If all integer types are all signed or all unsigned, the result is the same sign and the precision is the highest of all specified integer types (that is, TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT). o If there is a combination of signed and unsigned integer types, the result is signed and the precision may be higher. For example, if the types are signed INT and unsigned INT, the result is signed BIGINT. o The exception is unsigned BIGINT combined with any signed integer type. The result is DECIMAL with sufficient precision and scale 0. o If all types are BIT, the result is BIT. Otherwise, BIT arguments are treated similar to BIGINT. o If all types are YEAR, the result is YEAR. Otherwise, YEAR arguments are treated similar to INT. o If all types are character string (CHAR or VARCHAR), the result is VARCHAR with maximum length determined by the longest character length of the operands. o If all types are character or binary string, the result is VARBINARY. o SET and ENUM are treated similar to VARCHAR; the result is VARCHAR. o If all types are JSON, the result is JSON. o If all types are temporal, the result is temporal: o If all temporal types are DATE, TIME, or TIMESTAMP, the result is DATE, TIME, or TIMESTAMP, respectively. o Otherwise, for a mix of temporal types, the result is DATETIME. o If all types are GEOMETRY, the result is GEOMETRY. o If any type is BLOB, the result is BLOB. o For all other type combinations, the result is VARCHAR. o Literal NULL operands are ignored for type aggregation. URL: http://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html Examples: mysql> SELECT CASE 1 WHEN 1 THEN 'one' -> WHEN 2 THEN 'two' ELSE 'more' END; -> 'one' mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; -> 'true' mysql> SELECT CASE BINARY 'B' -> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; -> NULL mysql>
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END • 当value等于compare_value时，则返回result，否则返回else里的result， 如果没有else子句则返回null
mysql> SELECT CASE 100 -> WHEN 100 THEN '一百' -> WHEN 200 THEN '二百' -> ELSE '未知' -> END; +-----------------------------------------------------------------------------+ | CASE 100 WHEN 100 THEN '一百' WHEN 200 THEN '二百' ELSE '未知' END | +-----------------------------------------------------------------------------+ | 一百 | +-----------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT CASE 300 WHEN 100 THEN '一百' WHEN 200 THEN '二百' ELSE '未知' END; +--------------------------------------------------------------------------+ | CASE 300 WHEN 100 THEN '一百' WHEN 200 THEN '二百' ELSE '未知' END | +--------------------------------------------------------------------------+ | 未知 | +--------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql>
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END • 当第一个condition满足时，则返回result，否则返回else里的result，如果 没有else子句时则返回null
mysql> SELECT CASE WHEN 100 > 20 THEN '真的' ELSE '假的' END; +----------------------------------------------------+ | CASE WHEN 100 > 20 THEN '真的' ELSE '假的' END | +----------------------------------------------------+ | 真的 | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT CASE WHEN 10 > 20 THEN 'TRUE' ELSE 'FALSE' END; +------------------------------------------------+ | CASE WHEN 10 > 20 THEN 'TRUE' ELSE 'FALSE' END | +------------------------------------------------+ | FALSE | +------------------------------------------------+ 1 row in set (0.00 sec) mysql>
IF(expr1,expr2,expr3) • 当expr1为1/true时，则返回expr2，否则返回expr3
mysql> SELECT IF(10>5,"大于","小于"); +----------------------------+ | IF(10>5,"大于","小于") | +----------------------------+ | 大于 | +----------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT IF(10>5,"YES","NO"); +---------------------+ | IF(10>5,"YES","NO") | +---------------------+ | YES | +---------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT IF(STRCMP('yinzhengjie','zhengjie'),"YES","NO"); +-------------------------------------------------+ | IF(STRCMP('yinzhengjie','zhengjie'),"YES","NO") | +-------------------------------------------------+ | YES | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT IF(STRCMP('zhengjie','yinzhengjie'),"YES","NO"); +-------------------------------------------------+ | IF(STRCMP('zhengjie','yinzhengjie'),"YES","NO") | +-------------------------------------------------+ | YES | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT IF(STRCMP('yinzhengjie','yinzhengjie'),"YES","NO"); +----------------------------------------------------+ | IF(STRCMP('yinzhengjie','yinzhengjie'),"YES","NO") | +----------------------------------------------------+ | NO | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql>
IFNULL(expr1,expr2)
• 当expr1为非null时，则返回expr1，否则返回expr2
mysql> SELECT IFNULL(10,2); +--------------+ | IFNULL(10,2) | +--------------+ | 10 | +--------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT IFNULL(NULL,2); +----------------+ | IFNULL(NULL,2) | +----------------+ | 2 | +----------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT IFNULL(10/0,2); +----------------+ | IFNULL(10/0,2) | +----------------+ | 2.0000 | +----------------+ 1 row in set, 1 warning (0.00 sec) mysql> mysql> SELECT IFNULL(10/0,'NULL'); +---------------------+ | IFNULL(10/0,'NULL') | +---------------------+ | NULL | +---------------------+ 1 row in set, 1 warning (0.00 sec) mysql>
NULLIF(expr1,expr2) • 当expr1等于expr2时，则返回null，否则返回expr1
mysql> mysql> SELECT NULLIF('yinzhengjie','yinzhengjie'); +-------------------------------------+ | NULLIF('yinzhengjie','yinzhengjie') | +-------------------------------------+ | NULL | +-------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT NULLIF('yinzhengjie','zhengjie'); +----------------------------------+ | NULLIF('yinzhengjie','zhengjie') | +----------------------------------+ | yinzhengjie | +----------------------------------+ 1 row in set (0.00 sec) mysql>
2>.字符串函数
ASCII(str)返回str字符串中最左边字符的ascii码值，如果是空串则返回0，如果str是null则返回nullCHAR(N,...[USINGcharset_name])将括号中的N转化成ascii码对应的字符，返回这些字符组成的字符串，其中的null会被忽略
mysql> SELECT CHAR_LENGTH('yinzhengjie'); +----------------------------+ | CHAR_LENGTH('yinzhengjie') | +----------------------------+ | 11 | +----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CHAR_LENGTH('MySQL'); +----------------------+ | CHAR_LENGTH('MySQL') | +----------------------+ | 5 | +----------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT CONCAT('yin','zheng','jie'); +-----------------------------+ | CONCAT('yin','zheng','jie') | +-----------------------------+ | yinzhengjie | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CONCAT('My','NULL','SQL'); +---------------------------+ | CONCAT('My','NULL','SQL') | +---------------------------+ | MyNULLSQL | +---------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CONCAT('My',NULL,'SQL'); +-------------------------+ | CONCAT('My',NULL,'SQL') | +-------------------------+ | NULL | +-------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CONCAT(3,1415,926); +--------------------+ | CONCAT(3,1415,926) | +--------------------+ | 31415926 | +--------------------+ 1 row in set (0.01 sec) mysql> mysql> SELECT CONCAT(3.1415926); +-------------------+ | CONCAT(3.1415926) | +-------------------+ | 3.1415926 | +-------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT CONCAT_WS('-','First name','NULL','Last name'); +------------------------------------------------+ | CONCAT_WS('-','First name','NULL','Last name') | +------------------------------------------------+ | First name-NULL-Last name | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CONCAT_WS('-','First name',NULL,'Last name'); +----------------------------------------------+ | CONCAT_WS('-','First name',NULL,'Last name') | +----------------------------------------------+ | First name-Last name | +----------------------------------------------+ 1 row in set (0.00 sec) mysql>
INSERT(str,pos,len,newstr) 将str中从pos位置开始后的len个字符替换成newstr字符串INSTR(str,substr) 返回str字符串中第一个出现substr字符串的位置
mysql> SELECT LEFT('yinzhengjie2019',3); +---------------------------+ | LEFT('yinzhengjie2019',3) | +---------------------------+ | yin | +---------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LEFT('yinzhengjie2019',11); +----------------------------+ | LEFT('yinzhengjie2019',11) | +----------------------------+ | yinzhengjie | +----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LEFT('yinzhengjie2019',15); +----------------------------+ | LEFT('yinzhengjie2019',15) | +----------------------------+ | yinzhengjie2019 | +----------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT LENGTH('yinzhengjie'); +-----------------------+ | LENGTH('yinzhengjie') | +-----------------------+ | 11 | +-----------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LENGTH('2019'); +----------------+ | LENGTH('2019') | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LENGTH('yinzhengjie2019'); +---------------------------+ | LENGTH('yinzhengjie2019') | +---------------------------+ | 15 | +---------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT LOAD_FILE('/yinzhengjie/backup/student.bak'); +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOAD_FILE('/yinzhengjie/backup/student.bak') | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1-"尹正杰" 2-"耿宇星" 3-"陈飞" 4-"彭兴旭" 5-"李慧鹏" 6-"孟欣" 7-"鲜惠珊" 8-"陈劲" 9-"居彭阳" 10-"李嘉韵" 11-"石闹闹" 12-"肖风" 13-"刘晓江" | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LOAD_FILE('/yinzhengjie/backup/teacher.bak'); +----------------------------------------------+ | LOAD_FILE('/yinzhengjie/backup/teacher.bak') | +----------------------------------------------+ | 2周杰伦1 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT LOCATE('yin','yinzhengjie'); +-----------------------------+ | LOCATE('yin','yinzhengjie') | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT LOCATE('zheng','yinzhengjie'); +-------------------------------+ | LOCATE('zheng','yinzhengjie') | +-------------------------------+ | 4 | +-------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LOCATE('jie','yinzhengjie'); +-----------------------------+ | LOCATE('jie','yinzhengjie') | +-----------------------------+ | 9 | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LOCATE('2019','yinzhengjie'); +------------------------------+ | LOCATE('2019','yinzhengjie') | +------------------------------+ | 0 | +------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT LOCATE('e','yinzhengjie','1'); +-------------------------------+ | LOCATE('e','yinzhengjie','1') | +-------------------------------+ | 6 | +-------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LOCATE('E','yinzhengjie','1'); +-------------------------------+ | LOCATE('E','yinzhengjie','1') | +-------------------------------+ | 6 | +-------------------------------+ 1 row in set (0.00 sec) mysql> SELECT LOCATE('e','yinzhengjie','8'); +-------------------------------+ | LOCATE('e','yinzhengjie','8') | +-------------------------------+ | 11 | +-------------------------------+ 1 row in set (0.00 sec) mysql> SELECT LOCATE('E','yinzhengjie','8'); +-------------------------------+ | LOCATE('E','yinzhengjie','8') | +-------------------------------+ | 11 | +-------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LOCATE('A','yinzhengjie','8'); +-------------------------------+ | LOCATE('A','yinzhengjie','8') | +-------------------------------+ | 0 | +-------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SET @name1 = 'yinzhengjie'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SET @name2 = BINARY 'YinZhengJie'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT @name1,@name2; +-------------+-------------+ | @name1 | @name2 | +-------------+-------------+ | yinzhengjie | YinZhengJie | +-------------+-------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LOWER(@name1),LOWER(@name2),LOWER(CONVERT(@name2 USING utf8)); +---------------+---------------+-----------------------------------+ | LOWER(@name1) | LOWER(@name2) | LOWER(CONVERT(@name2 USING utf8)) | +---------------+---------------+-----------------------------------+ | yinzhengjie | YinZhengJie | yinzhengjie | +---------------+---------------+-----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> mysql>
mysql> SELECT LPAD('yinzhengjie',20,'-'); +----------------------------+ | LPAD('yinzhengjie',20,'-') | +----------------------------+ | ---------yinzhengjie | +----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LPAD('yinzhengjie',15,'-'); +----------------------------+ | LPAD('yinzhengjie',15,'-') | +----------------------------+ | ----yinzhengjie | +----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LPAD('yinzhengjie',11,'-'); +----------------------------+ | LPAD('yinzhengjie',11,'-') | +----------------------------+ | yinzhengjie | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT LPAD('yinzhengjie',3,'-'); +---------------------------+ | LPAD('yinzhengjie',3,'-') | +---------------------------+ | yin | +---------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> mysql> SELECT LTRIM(' yinzhengjie '); +------------------------------------------------+ | LTRIM(' yinzhengjie ') | +------------------------------------------------+ | yinzhengjie | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT LTRIM('2019 yinzhengjie '); +----------------------------------------------------+ | LTRIM('2019 yinzhengjie ') | +----------------------------------------------------+ | 2019 yinzhengjie | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT REPEAT(' yinzhengjie ',3); +-----------------------------------------+ | REPEAT(' yinzhengjie ',3) | +-----------------------------------------+ | yinzhengjie yinzhengjie yinzhengjie | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT REPEAT(' yinzhengjie ',-1); +----------------------------+ | REPEAT(' yinzhengjie ',-1) | +----------------------------+ | | +----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT REPEAT(' yinzhengjie ',0); +---------------------------+ | REPEAT(' yinzhengjie ',0) | +---------------------------+ | | +---------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT REPEAT(' yinzhengjie ',5); +-------------------------------------------------------------------+ | REPEAT(' yinzhengjie ',5) | +-------------------------------------------------------------------+ | yinzhengjie yinzhengjie yinzhengjie yinzhengjie yinzhengjie | +-------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT REPLACE("www.yinzhengjie.org.cn",'i','I'); +-------------------------------------------+ | REPLACE("www.yinzhengjie.org.cn",'i','I') | +-------------------------------------------+ | www.yInzhengjIe.org.cn | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT REVERSE('尹正杰'); +----------------------+ | REVERSE('尹正杰') | +----------------------+ | 杰正尹 | +----------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT REVERSE('yinzhengjie'); +------------------------+ | REVERSE('yinzhengjie') | +------------------------+ | eijgnehzniy | +------------------------+ 1 row in set (0.00 sec) mysql>
mysql> mysql> SELECT RIGHT('yinzhengjie2019',4); +----------------------------+ | RIGHT('yinzhengjie2019',4) | +----------------------------+ | 2019 | +----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT RIGHT('yinzhengjie2019',7); +----------------------------+ | RIGHT('yinzhengjie2019',7) | +----------------------------+ | jie2019 | +----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT RIGHT('yinzhengjie2019',12); +-----------------------------+ | RIGHT('yinzhengjie2019',12) | +-----------------------------+ | zhengjie2019 | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT RIGHT('yinzhengjie2019',15); +-----------------------------+ | RIGHT('yinzhengjie2019',15) | +-----------------------------+ | yinzhengjie2019 | +-----------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT RPAD('yinzhengjie2019',20,'-'); +--------------------------------+ | RPAD('yinzhengjie2019',20,'-') | +--------------------------------+ | yinzhengjie2019----- | +--------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT RPAD('yinzhengjie2019',18,'-'); +--------------------------------+ | RPAD('yinzhengjie2019',18,'-') | +--------------------------------+ | yinzhengjie2019--- | +--------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT RPAD('yinzhengjie2019',15,'-'); +--------------------------------+ | RPAD('yinzhengjie2019',15,'-') | +--------------------------------+ | yinzhengjie2019 | +--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT RPAD('yinzhengjie2019',11,'-'); +--------------------------------+ | RPAD('yinzhengjie2019',11,'-') | +--------------------------------+ | yinzhengjie | +--------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT RPAD('yinzhengjie2019',3,'-'); +-------------------------------+ | RPAD('yinzhengjie2019',3,'-') | +-------------------------------+ | yin | +-------------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT RTRIM('yinzhengjie '); +-----------------------------+ | RTRIM('yinzhengjie ') | +-----------------------------+ | yinzhengjie | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT RTRIM(' yinzhengjie '); +-----------------------------------+ | RTRIM(' yinzhengjie ') | +-----------------------------------+ | yinzhengjie | +-----------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT SPACE(1); +----------+ | SPACE(1) | +----------+ | | +----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SPACE(10); +------------+ | SPACE(10) | +------------+ | | +------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SPACE(20); +----------------------+ | SPACE(20) | +----------------------+ | | +----------------------+ 1 row in set (0.00 sec) mysql> SELECT SPACE(30); +--------------------------------+ | SPACE(30) | +--------------------------------+ | | +--------------------------------+ 1 row in set (0.00 sec) mysql>
SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len) SUBSTRING(str,pos), SUBSTRING(str FROM pos),SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len) • 如果没有len参数，则返回从pos位置开始的str中的子字符串;如果有len参数，则从pos位置开始返回str中长度为len的子字符串;如果pos为负值，则代表pos从右边开始数
mysql> mysql> SELECT SUBSTR('yinzhengjie2019',5); +-----------------------------+ | SUBSTR('yinzhengjie2019',5) | +-----------------------------+ | hengjie2019 | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT SUBSTRING('yinzhengjie2019',5); +--------------------------------+ | SUBSTRING('yinzhengjie2019',5) | +--------------------------------+ | hengjie2019 | +--------------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT SUBSTR('yinzhengjie2019' FROM 5); +----------------------------------+ | SUBSTR('yinzhengjie2019' FROM 5) | +----------------------------------+ | hengjie2019 | +----------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTRING('yinzhengjie2019' FROM 5); +-------------------------------------+ | SUBSTRING('yinzhengjie2019' FROM 5) | +-------------------------------------+ | hengjie2019 | +-------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTR('yinzhengjie2019',5,8); +-------------------------------+ | SUBSTR('yinzhengjie2019',5,8) | +-------------------------------+ | hengjie2 | +-------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTRING('yinzhengjie2019',5,8); +----------------------------------+ | SUBSTRING('yinzhengjie2019',5,8) | +----------------------------------+ | hengjie2 | +----------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTRING('yinzhengjie2019',-4); +---------------------------------+ | SUBSTRING('yinzhengjie2019',-4) | +---------------------------------+ | 2019 | +---------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTR('yinzhengjie2019',-4); +------------------------------+ | SUBSTR('yinzhengjie2019',-4) | +------------------------------+ | 2019 | +------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTR('yinzhengjie2019',-4,5); +--------------------------------+ | SUBSTR('yinzhengjie2019',-4,5) | +--------------------------------+ | 2019 | +--------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTR('yinzhengjie2019',-4,2); +--------------------------------+ | SUBSTR('yinzhengjie2019',-4,2) | +--------------------------------+ | 20 | +--------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTRING('yinzhengjie2019' FROM -12 FOR 8); +---------------------------------------------+ | SUBSTRING('yinzhengjie2019' FROM -12 FOR 8) | +---------------------------------------------+ | zhengjie | +---------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT SUBSTR('yinzhengjie2019' FROM -12 FOR 8); +------------------------------------------+ | SUBSTR('yinzhengjie2019' FROM -12 FOR 8) | +------------------------------------------+ | zhengjie | +------------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',5); +-------------------------------------------------+ | SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',5) | +-------------------------------------------------+ | www.yinzhengjie.org.cn | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',3); +-------------------------------------------------+ | SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',3) | +-------------------------------------------------+ | www.yinzhengjie.org | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',1); +-------------------------------------------------+ | SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',1) | +-------------------------------------------------+ | www | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',-1); +--------------------------------------------------+ | SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',-1) | +--------------------------------------------------+ | cn | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',-3); +--------------------------------------------------+ | SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',-3) | +--------------------------------------------------+ | yinzhengjie.org.cn | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',-5); +--------------------------------------------------+ | SUBSTRING_INDEX('www.yinzhengjie.org.cn','.',-5) | +--------------------------------------------------+ | www.yinzhengjie.org.cn | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SET @name = 'yinzhengjie'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SET @name2 = BINARY 'YinZhengJie'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT @name,@name2; +-------------+-------------+ | @name | @name2 | +-------------+-------------+ | yinzhengjie | YinZhengJie | +-------------+-------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT UPPER(@name),UPPER(@name2),UPPER(CONVERT(@name2 USING utf8)); +--------------+---------------+-----------------------------------+ | UPPER(@name) | UPPER(@name2) | UPPER(CONVERT(@name2 USING utf8)) | +--------------+---------------+-----------------------------------+ | YINZHENGJIE | YinZhengJie | YINZHENGJIE | +--------------+---------------+-----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>
3>.字符串对比函数
用到的关键词有LIKE，NOT LIKE，STRCMP()，通配符"%"表示匹配0个或多个字符。通配符"_"表示匹配1个字符。
mysql> SELECT 'yin_zheng_jie' LIKE 'yin%'; +-----------------------------+ | 'yin_zheng_jie' LIKE 'yin%' | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 'yin_zheng_jie' LIKE 'yin%'; +-----------------------------+ | 'yin_zheng_jie' LIKE 'yin%' | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 'yin_zheng_jie' LIKE 'yin\_'; +------------------------------+ | 'yin_zheng_jie' LIKE 'yin\_' | +------------------------------+ | 0 | +------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 'yin_zheng_jie' LIKE 'yin\_%'; +-------------------------------+ | 'yin_zheng_jie' LIKE 'yin\_%' | +-------------------------------+ | 1 | +-------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 'yin_' LIKE 'yin|_' ESCAPE '|'; #当匹配字符中有特殊字符时，可以用“”或者ESCAPE来指定特殊字符为普通字符，也就是我们常说当转义。 +--------------------------------+ | 'yin_' LIKE 'yin|_' ESCAPE '|' | +--------------------------------+ | 1 | +--------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 'yin_' LIKE 'yin\_'; +---------------------+ | 'yin_' LIKE 'yin\_' | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec) mysql>
expr NOT LIKE pat [ESCAPE 'escape_char'] 是上一个字符串对比函数的反义 STRCMP(expr1,expr2) 当expr1等于expr2时等于0，当expr1小于expr2时为-1，反之为1
mysql> mysql> SELECT STRCMP(1,2); +-------------+ | STRCMP(1,2) | +-------------+ | -1 | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT STRCMP(3,2); +-------------+ | STRCMP(3,2) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT STRCMP('yinzhengjie','yinzhengjie2019'); +-----------------------------------------+ | STRCMP('yinzhengjie','yinzhengjie2019') | +-----------------------------------------+ | -1 | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT STRCMP('yinzhengjie','yinzhengjie'); +-------------------------------------+ | STRCMP('yinzhengjie','yinzhengjie') | +-------------------------------------+ | 0 | +-------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SET @s1 = _latin1 'x' COLLATE latin1_general_ci; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SET @s2 = _latin1 'X' COLLATE latin1_general_ci; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SET @s3 = _latin1 'x' COLLATE latin1_general_cs; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SET @s4 = _latin1 'X' COLLATE latin1_general_cs; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4); +------------------+------------------+ | STRCMP(@s1, @s2) | STRCMP(@s3, @s4) | +------------------+------------------+ | 0 | 1 | +------------------+------------------+ 1 row in set (0.00 sec) mysql> mysql>
4>.数字函数之算数操作符
/和DIV的区别： /代表除法;div代表整数型除法，相除之后只取整数部分
mysql> SELECT 3/5; +--------+ | 3/5 | +--------+ | 0.6000 | +--------+ 1 row in set (0.00 sec) mysql> SELECT 12345/(2-1-1); +---------------+ | 12345/(2-1-1) | +---------------+ | NULL | +---------------+ 1 row in set, 1 warning (0.00 sec) mysql> mysql> SELECT 5 DIV 2; +---------+ | 5 DIV 2 | +---------+ | 2 | +---------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 10/3; +--------+ | 10/3 | +--------+ | 3.3333 | +--------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 10 DIV 3; +----------+ | 10 DIV 3 | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql>
5>.数字函数
mysql> SELECT ABS(10); +---------+ | ABS(10) | +---------+ | 10 | +---------+ 1 row in set (0.01 sec) mysql> mysql> SELECT ABS(-10); +----------+ | ABS(-10) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT ABS(-200); +-----------+ | ABS(-200) | +-----------+ | 200 | +-----------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT CEILING(1.23); +---------------+ | CEILING(1.23) | +---------------+ | 2 | +---------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CEILING(0.98); +---------------+ | CEILING(0.98) | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CEILING(3.1415926); +--------------------+ | CEILING(3.1415926) | +--------------------+ | 4 | +--------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CEILING(9.8); +--------------+ | CEILING(9.8) | +--------------+ | 10 | +--------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT FLOOR(3.14); +-------------+ | FLOOR(3.14) | +-------------+ | 3 | +-------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT FLOOR(-3.14); +--------------+ | FLOOR(-3.14) | +--------------+ | -4 | +--------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT FLOOR(9.8); +------------+ | FLOOR(9.8) | +------------+ | 9 | +------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT FLOOR(-9.8); +-------------+ | FLOOR(-9.8) | +-------------+ | -10 | +-------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT MOD(10,3); +-----------+ | MOD(10,3) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT MOD(108,10); +-------------+ | MOD(108,10) | +-------------+ | 8 | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 268 % 8; +---------+ | 268 % 8 | +---------+ | 4 | +---------+ 1 row in set (0.00 sec) mysql> SELECT 108 % 10; +----------+ | 108 % 10 | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 10 % 3; +--------+ | 10 % 3 | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 10 MOD 3; +----------+ | 10 MOD 3 | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT 108 MOD 10; +------------+ | 108 MOD 10 | +------------+ | 8 | +------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT FLOOR(10 + (RAND() * 10)); +---------------------------+ | FLOOR(10 + (RAND() * 10)) | +---------------------------+ | 10 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT FLOOR(10 + (RAND() * 10)); +---------------------------+ | FLOOR(10 + (RAND() * 10)) | +---------------------------+ | 17 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT FLOOR(10 + (RAND() * 10)); +---------------------------+ | FLOOR(10 + (RAND() * 10)) | +---------------------------+ | 13 | +---------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT * FROM yinzhengjie.student ORDER BY rand() LIMIT 1; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 2 | 尹正杰 | 1 | +----+-----------+------------+ 1 row in set (0.01 sec) mysql>
mysql> SELECT ROUND(9.8); +------------+ | ROUND(9.8) | +------------+ | 10 | +------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT ROUND(3.14); +-------------+ | ROUND(3.14) | +-------------+ | 3 | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT ROUND(3.1415926,5); +--------------------+ | ROUND(3.1415926,5) | +--------------------+ | 3.14159 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT ROUND(3.1415926,3); +--------------------+ | ROUND(3.1415926,3) | +--------------------+ | 3.142 | +--------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT TRUNCATE(3.1415926,5); +-----------------------+ | TRUNCATE(3.1415926,5) | +-----------------------+ | 3.14159 | +-----------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT TRUNCATE(3.1415926,2); +-----------------------+ | TRUNCATE(3.1415926,2) | +-----------------------+ | 3.14 | +-----------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT TRUNCATE(-3.1415926,2); +------------------------+ | TRUNCATE(-3.1415926,2) | +------------------------+ | -3.14 | +------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT TRUNCATE(1314,-2); +-------------------+ | TRUNCATE(1314,-2) | +-------------------+ | 1300 | +-------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT TRUNCATE(1314,-3); +-------------------+ | TRUNCATE(1314,-3) | +-------------------+ | 1000 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT TRUNCATE(1314,-1); +-------------------+ | TRUNCATE(1314,-1) | +-------------------+ | 1310 | +-------------------+ 1 row in set (0.00 sec) mysql>
6>.日期和时间函数
mysql> SELECT ADDDATE('2014-07-18',INTERVAL 31 DAY); +---------------------------------------+ | ADDDATE('2014-07-18',INTERVAL 31 DAY) | +---------------------------------------+ | 2014-08-18 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT ADDDATE('2014-07-18',31); +--------------------------+ | ADDDATE('2014-07-18',31) | +--------------------------+ | 2014-08-18 | +--------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT ADDTIME('2018-12-31 23:59:59.999999', '1 1:1:1.000002'); +---------------------------------------------------------+ | ADDTIME('2018-12-31 23:59:59.999999', '1 1:1:1.000002') | +---------------------------------------------------------+ | 2019-01-02 01:01:01.000001 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998'); +-----------------------------------------------+ | ADDTIME('01:00:00.999999', '02:00:00.999998') | +-----------------------------------------------+ | 03:00:01.999997 | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00'); +-----------------------------------------------------+ | CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') | +-----------------------------------------------------+ | 2004-01-01 22:00:00 | +-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT CURDATE(); +------------+ | CURDATE() | +------------+ | 2019-01-27 | +------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CURDATE() + 0; +---------------+ | CURDATE() + 0 | +---------------+ | 20190127 | +---------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CURDATE() + 3; +---------------+ | CURDATE() + 3 | +---------------+ | 20190130 | +---------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT CURTIME(); +-----------+ | CURTIME() | +-----------+ | 23:50:57 | +-----------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CURTIME() + 0; +---------------+ | CURTIME() + 0 | +---------------+ | 235111 | +---------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT CURTIME() + 3; +---------------+ | CURTIME() + 3 | +---------------+ | 235117 | +---------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2019-01-27 23:52:56 | +---------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT NOW() + 0; +----------------+ | NOW() + 0 | +----------------+ | 20190127235302 | +----------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT NOW() + 3; +----------------+ | NOW() + 3 | +----------------+ | 20190127235308 | +----------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT DATE('2018-12-31 01:02:03'); +-----------------------------+ | DATE('2018-12-31 01:02:03') | +-----------------------------+ | 2018-12-31 | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); +----------------------------------------------+ | DATEDIFF('2007-12-31 23:59:59','2007-12-30') | +----------------------------------------------+ | 1 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31'); +----------------------------------------------+ | DATEDIFF('2010-11-30 23:59:59','2010-12-31') | +----------------------------------------------+ | -31 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT DATE_ADD('2000-12-31 23:59:59',INTERVAL 1 SECOND); +---------------------------------------------------+ | DATE_ADD('2000-12-31 23:59:59',INTERVAL 1 SECOND) | +---------------------------------------------------+ | 2001-01-01 00:00:00 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DATE_ADD('2010-12-31 23:59:59',INTERVAL 1 DAY); +------------------------------------------------+ | DATE_ADD('2010-12-31 23:59:59',INTERVAL 1 DAY) | +------------------------------------------------+ | 2011-01-01 23:59:59 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> 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-01-01 00:01:00 | +--------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> SELECT DATE_SUB('2005-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND); +---------------------------------------------------------------+ | DATE_SUB('2005-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND) | +---------------------------------------------------------------+ | 2004-12-30 22:58:59 | +---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_ADD('1900-01-01 00:00:00',INTERVAL '-1 10' DAY_HOUR); +-----------------------------------------------------------+ | DATE_ADD('1900-01-01 00:00:00',INTERVAL '-1 10' DAY_HOUR) | +-----------------------------------------------------------+ | 1899-12-30 14:00:00 | +-----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY); +-----------------------------------------+ | DATE_SUB('1998-01-02', INTERVAL 31 DAY) | +-----------------------------------------+ | 1997-12-02 | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',INTERVAL '1.999999' SECOND_MICROSECOND); +-------------------------------------------------------------------------------+ | DATE_ADD('1992-12-31 23:59:59.000002',INTERVAL '1.999999' SECOND_MICROSECOND) | +-------------------------------------------------------------------------------+ | 1993-01-01 00:00:01.000001 | +-------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DATE_ADD('2009-01-30', INTERVAL 1 MONTH); +------------------------------------------+ | DATE_ADD('2009-01-30', INTERVAL 1 MONTH) | +------------------------------------------+ | 2009-02-28 | +------------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT DATE_FORMAT('20190127235308','%Y-%m-%D-%H-%i-%s'); +---------------------------------------------------+ | DATE_FORMAT('20190127235308','%Y-%m-%D-%H-%i-%s') | +---------------------------------------------------+ | 2019-01-27th-23-53-08 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DATE_FORMAT('20190127235308','%Y-%M-%D-%H-%m-%s'); +---------------------------------------------------+ | DATE_FORMAT('20190127235308','%Y-%M-%D-%H-%m-%s') | +---------------------------------------------------+ | 2019-January-27th-23-01-08 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); +------------------------------------------------+ | DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') | +------------------------------------------------+ | Sunday October 2009 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); +------------------------------------------------+ | DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') | +------------------------------------------------+ | Sunday October 2009 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); +------------------------------------------------+ | DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') | +------------------------------------------------+ | 22:23:00 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00','%D %y %a %d %m %b %j'); +-----------------------------------------------------------+ | DATE_FORMAT('1900-10-04 22:23:00','%D %y %a %d %m %b %j') | +-----------------------------------------------------------+ | 4th 00 Thu 04 10 Oct 277 | +-----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w'); +-----------------------------------------------------------+ | DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w') | +-----------------------------------------------------------+ | 22 22 10 10:23:00 PM 22:23:00 00 6 | +-----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); +------------------------------------+ | DATE_FORMAT('1999-01-01', '%X %V') | +------------------------------------+ | 1998 52 | +------------------------------------+ 1 row in set (0.01 sec) mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); +---------------------------------+ | DATE_FORMAT('2006-06-00', '%d') | +---------------------------------+ | 00 | +---------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT DAYOFMONTH('2019-01-30'); +--------------------------+ | DAYOFMONTH('2019-01-30') | +--------------------------+ | 30 | +--------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DAYOFMONTH('2007-02-03'); +--------------------------+ | DAYOFMONTH('2007-02-03') | +--------------------------+ | 3 | +--------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT DAYNAME('2007-02-03'); +-----------------------+ | DAYNAME('2007-02-03') | +-----------------------+ | Saturday | +-----------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DAYNAME('2019-01-30'); +-----------------------+ | DAYNAME('2019-01-30') | +-----------------------+ | Wednesday | +-----------------------+ 1 row in set (0.00 sec) mysql>
mysql> mysql> SELECT DAYOFWEEK('2019-10-31'); +-------------------------+ | DAYOFWEEK('2019-10-31') | +-------------------------+ | 5 | +-------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DAYOFWEEK('2019-01-30'); +-------------------------+ | DAYOFWEEK('2019-01-30') | +-------------------------+ | 4 | +-------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT DAYOFWEEK('2007-02-03'); +-------------------------+ | DAYOFWEEK('2007-02-03') | +-------------------------+ | 7 | +-------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT DAYOFYEAR('2007-02-03'); +-------------------------+ | DAYOFYEAR('2007-02-03') | +-------------------------+ | 34 | +-------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT EXTRACT(YEAR FROM '2009-07-02'); +---------------------------------+ | EXTRACT(YEAR FROM '2009-07-02') | +---------------------------------+ | 2009 | +---------------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> mysql> SELECT EXTRACT(YEAR FROM '2009-07-02'); +---------------------------------+ | EXTRACT(YEAR FROM '2009-07-02') | +---------------------------------+ | 2009 | +---------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03'); +------------------------------------------------+ | EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03') | +------------------------------------------------+ | 200907 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03'); +------------------------------------------------+ | EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03') | +------------------------------------------------+ | 20102 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123'); +--------------------------------------------------------+ | EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123') | +--------------------------------------------------------+ | 123 | +--------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT FROM_UNIXTIME(1447430881); +---------------------------+ | FROM_UNIXTIME(1447430881) | +---------------------------+ | 2015-11-13 11:08:01 | +---------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT FROM_UNIXTIME(1447430881) + 0; +-------------------------------+ | FROM_UNIXTIME(1447430881) + 0 | +-------------------------------+ | 20151113110801 | +-------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x'); +--------------------------------------------------------+ | FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x') | +--------------------------------------------------------+ | 2019 28th January 12:12:06 2019 | +--------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT LAST_DAY('2004-02-05'); +------------------------+ | LAST_DAY('2004-02-05') | +------------------------+ | 2004-02-29 | +------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT LAST_DAY('2004-03-05'); +------------------------+ | LAST_DAY('2004-03-05') | +------------------------+ | 2004-03-31 | +------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT LAST_DAY('2004-03-45'); +------------------------+ | LAST_DAY('2004-03-45') | +------------------------+ | NULL | +------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>
mysql> SELECT NOW(), SLEEP(2), NOW(); +---------------------+----------+---------------------+ | NOW() | SLEEP(2) | NOW() | +---------------------+----------+---------------------+ | 2019-01-28 00:14:36 | 0 | 2019-01-28 00:14:36 | +---------------------+----------+---------------------+ 1 row in set (2.00 sec) mysql> mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE(); +---------------------+----------+---------------------+ | SYSDATE() | SLEEP(2) | SYSDATE() | +---------------------+----------+---------------------+ | 2019-01-28 00:14:46 | 0 | 2019-01-28 00:14:48 | +---------------------+----------+---------------------+ 1 row in set (2.00 sec) mysql>
mysql> SELECT TIME('2003-12-31 01:02:03'); +-----------------------------+ | TIME('2003-12-31 01:02:03') | +-----------------------------+ | 01:02:03 | +-----------------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> SELECT TIME('2003-12-31 01:02:03.000123'); +------------------------------------+ | TIME('2003-12-31 01:02:03.000123') | +------------------------------------+ | 01:02:03.000123 | +------------------------------------+ 1 row in set (0.00 sec) mysql>
mysql> SELECT UNIX_TIMESTAMP(); +------------------+ | UNIX_TIMESTAMP() | +------------------+ | 1548652588 | +------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19'); +---------------------------------------+ | UNIX_TIMESTAMP('2015-11-13 10:20:19') | +---------------------------------------+ | 1447428019 | +---------------------------------------+ 1 row in set (0.00 sec) mysql>
7>.格式转换函数
Cast()和convert()两个函数都可以用来转换数据类型或者转换字符集允许转换的数据类型包括:
• Binary[N] • char[N] • Date • Datetime • decimal[M,[D]] • Time • Signed [integer] • Unsigned [integer]
mysql> SELECT CONVERT(_latin1'Müller' USING utf8); +---------------------------------------+ | CONVERT(_latin1'Müller' USING utf8) | +---------------------------------------+ | Müller | +---------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>
mysql> SELECT CONVERT('test', CHAR CHARACTER SET utf8); +------------------------------------------+ | CONVERT('test', CHAR CHARACTER SET utf8) | +------------------------------------------+ | test | +------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>
mysql> SELECT CAST('test' AS CHAR CHARACTER SET utf8); +-----------------------------------------+ | CAST('test' AS CHAR CHARACTER SET utf8) | +-----------------------------------------+ | test | +-----------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>
mysql> SELECT CAST('2019-01-01' AS DATE); +----------------------------+ | CAST('2019-01-01' AS DATE) | +----------------------------+ | 2019-01-01 | +----------------------------+ 1 row in set (0.00 sec) mysql> mysql>
mysql> SELECT CONVERT('2019-01-01', DATE); +-----------------------------+ | CONVERT('2019-01-01', DATE) | +-----------------------------+ | 2019-01-01 | +-----------------------------+ 1 row in set (0.01 sec) mysql>
8>.聚合函数
聚合函数通常用在存在GROUP BY子句的语句中。
mysql> USE yinzhengjie; Database changed mysql> mysql> CREATE TABLE score_graph( -> id INT(11) PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(30), -> subject VARCHAR(30), -> score INT(3) -> ); Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','英语',120); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','语文',118); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','数学',120); Query OK, 1 row affected (0.01 sec) mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','物理',110); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','化学',112); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('尹正杰','生物',108); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','语文',120); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','英语',110); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','数学',115); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','物理',105); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','化学',102); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('jason','生物',98); Query OK, 1 row affected (0.01 sec) mysql> mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','语文',116); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','数学',112); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','英语',101); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','物理',103); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','化学',112); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO score_graph(name,subject,score) VALUES('耿宇星','生物',78); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ 18 rows in set (0.00 sec) mysql>
mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ 18 rows in set (0.01 sec) mysql> mysql> SELECT name,AVG(score) FROM score_graph GROUP BY name; +-----------+------------+ | name | AVG(score) | +-----------+------------+ | 尹正杰 | 114.6667 | | jason | 108.3333 | | 耿宇星 | 103.6667 | +-----------+------------+ 3 rows in set (0.00 sec) mysql> mysql>
mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ 18 rows in set (0.00 sec) mysql> mysql> SELECT name,COUNT(*) FROM score_graph GROUP BY name; +-----------+----------+ | name | COUNT(*) | +-----------+----------+ | 尹正杰 | 6 | | jason | 6 | | 耿宇星 | 6 | +-----------+----------+ 3 rows in set (0.00 sec) mysql> mysql>
mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ 18 rows in set (0.00 sec) mysql> mysql> SELECT COUNT(DISTINCT id) FROM score_graph; +--------------------+ | COUNT(DISTINCT id) | +--------------------+ | 18 | +--------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT COUNT(DISTINCT name) FROM score_graph; +----------------------+ | COUNT(DISTINCT name) | +----------------------+ | 3 | +----------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT COUNT(DISTINCT subject) FROM score_graph; +-------------------------+ | COUNT(DISTINCT subject) | +-------------------------+ | 6 | +-------------------------+ 1 row in set (0.01 sec) mysql> mysql> SELECT COUNT(DISTINCT score) FROM score_graph;
mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ 18 rows in set (0.00 sec) mysql> mysql> SELECT name,MIN(score),MAX(score) FROM score_graph GROUP BY name; +-----------+------------+------------+ | name | MIN(score) | MAX(score) | +-----------+------------+------------+ | 尹正杰 | 108 | 120 | | jason | 98 | 120 | | 耿宇星 | 78 | 116 | +-----------+------------+------------+ 3 rows in set (0.00 sec) mysql> mysql>
mysql> SELECT * FROM score_graph; +----+-----------+---------+-------+ | id | name | subject | score | +----+-----------+---------+-------+ | 1 | 尹正杰 | 英语 | 120 | | 2 | 尹正杰 | 语文 | 118 | | 3 | 尹正杰 | 数学 | 120 | | 4 | 尹正杰 | 物理 | 110 | | 5 | 尹正杰 | 化学 | 112 | | 6 | 尹正杰 | 生物 | 108 | | 7 | jason | 语文 | 120 | | 8 | jason | 英语 | 110 | | 9 | jason | 数学 | 115 | | 10 | jason | 物理 | 105 | | 11 | jason | 化学 | 102 | | 12 | jason | 生物 | 98 | | 13 | 耿宇星 | 语文 | 116 | | 14 | 耿宇星 | 数学 | 112 | | 15 | 耿宇星 | 英语 | 101 | | 16 | 耿宇星 | 物理 | 103 | | 17 | 耿宇星 | 化学 | 112 | | 18 | 耿宇星 | 生物 | 78 | +----+-----------+---------+-------+ 18 rows in set (0.00 sec) mysql> mysql> SELECT name,SUM(score) FROM score_graph GROUP BY name; +-----------+------------+ | name | SUM(score) | +-----------+------------+ | 尹正杰 | 688 | | jason | 650 | | 耿宇星 | 622 | +-----------+------------+ 3 rows in set (0.00 sec) mysql>