MySQL基础篇（3）常用函数和运算符
一、字符串函数（索引位置都从1开始）
1 CONCAT(S1,S2,...Sn): 连接S1,S2,...Sn为一个字符串，任何字符串与NULL进行连接的结果都是NULL 2 INSERT(str,x,y,instr)：将字符串str从第x位置开始，y个字符长的子串替换为字符串instr 3 LOWER/UPPER(str)：将字符串str中所有字符变为小/大写 4 LEFT/RIGHT(str,x)：返回字符串str最左/右边的x个字符，如果x为null，则不返回任何字符串 5 LPAD/RPAD(str,n,pad)：用字符串pad对str最左/右边进行填充，直到长度为n个字符长度 6 LTRIM/RTRIM(str)：去掉字符串str左/右侧的空格 7 REPEAT(str,x)：返回str重复x次的结果 8 REPLACE(str,a,b)：用字符串b替换字符串str中所有出现的字符串a 9 STRCMP(s1,s2)：比价字符串s1和s2，如果s1比s2小，则返回-1；相等返回0，s1比s2大，返回1 10 TRIM(str）：去掉字符串行尾和行头的空格 11 SUBSTRING(str,x,y）：返回从字符串str x位置其y个字符长度的子串
举例：
mysql> select concat('aaa','bbb','ccc'), concat('aaa',null); +---------------------------+--------------------+ | concat('aaa','bbb','ccc') | concat('aaa',null) | +---------------------------+--------------------+ | aaabbbccc | NULL | +---------------------------+--------------------+ 1 row in set (0.00 sec) mysql> select INSERT('beijing2008you',12,3,'me'); +------------------------------------+ | INSERT('beijing2008you',12,3,'me') | +------------------------------------+ | beijing2008me | +------------------------------------+ 1 row in set (0.00 sec) mysql> select LOWER('BEIJING2008'), UPPER('beijing2008'); +----------------------+----------------------+ | LOWER('BEIJING2008') | UPPER('beijing2008') | +----------------------+----------------------+ | beijing2008 | BEIJING2008 | +----------------------+----------------------+ 1 row in set (0.00 sec) mysql> select LEFT('beijing2008',7), LEFT('beijing2008',null), RIGHT('beijing2008',4); +-----------------------+--------------------------+------------------------+ | LEFT('beijing2008',7) | LEFT('beijing2008',null) | RIGHT('beijing2008',4) | +-----------------------+--------------------------+------------------------+ | beijing | NULL | 2008 | +-----------------------+--------------------------+------------------------+ 1 row in set (0.00 sec) mysql> select LPAD('2008',20,'beijing'), RPAD('beijing',20,'2008'); +---------------------------+---------------------------+ | LPAD('2008',20,'beijing') | RPAD('beijing',20,'2008') | +---------------------------+---------------------------+ | beijingbeijingbe2008 | beijing2008200820082 | +---------------------------+---------------------------+ 1 row in set (0.00 sec) mysql> select LTRIM(' |beijing'), RTRIM('beijing| '); +----------------------+----------------------+ | LTRIM(' |beijing') | RTRIM('beijing| ') | +----------------------+----------------------+ | |beijing | beijing| | +----------------------+----------------------+ 1 row in set (0.00 sec) mysql> select REPEAT('beijing', 3); +-----------------------+ | REPEAT('beijing', 3) | +-----------------------+ | beijingbeijingbeijing | +-----------------------+ 1 row in set (0.00 sec) mysql> select REPLACE('beijing_2010', '_2010','2008'); +-----------------------------------------+ | REPLACE('beijing_2010', '_2010','2008') | +-----------------------------------------+ | beijing2008 | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> select STRCMP('a','b'), STRCMP('b','b'), STRCMP('c','b'); +-----------------+-----------------+-----------------+ | STRCMP('a','b') | STRCMP('b','b') | STRCMP('c','b') | +-----------------+-----------------+-----------------+ | -1 | 0 | 1 | +-----------------+-----------------+-----------------+ 1 row in set (0.00 sec) mysql> select TRIM(' |beijing| '); +--------------------------+ | TRIM(' |beijing| ') | +--------------------------+ | |beijing| | +--------------------------+ 1 row in set (0.00 sec) mysql> select SUBSTRING('beijing2008',8,4), SUBSTRING('beijing2008',1,7); +------------------------------+------------------------------+ | SUBSTRING('beijing2008',8,4) | SUBSTRING('beijing2008',1,7) | +------------------------------+------------------------------+ | 2008 | beijing | +------------------------------+------------------------------+ 1 row in set (0.00 sec)
二、数值函数
1 ABS(x)：返回x的绝对值 2 CEIL(x)：返回大于x的最小整数值 3 FLOOR(x)：返回小于x的最大整数值 4 MOD(x,y)：返回x/y的模，x和y任何一个为NULL结果都为NULL 5 RAND()：返回0~1内的随机值，使用CEIL(100*RAND())可以产生0~100内的任意随机整数 6 ROUND(x,y)：返回参数x的四舍五入的有y位小数的值，如果不写y，则默认y为0，即将四舍五入后取整 7 TRUNCATE(x,y)：返回数字x截断为y位小数的结果，和ROUND的区别是，仅仅是截断，不四舍五入取整
举例：
mysql> select ABS(-0.8), ABS(0.8); +-----------+----------+ | ABS(-0.8) | ABS(0.8) | +-----------+----------+ | 0.8 | 0.8 | +-----------+----------+ 1 row in set (0.00 sec) mysql> select CEIL(-0.8), CEIL(0.8); +------------+-----------+ | CEIL(-0.8) | CEIL(0.8) | +------------+-----------+ | 0 | 1 | +------------+-----------+ 1 row in set (0.00 sec) mysql> select FLOOR(-0.8), FLOOR(0.8); +-------------+------------+ | FLOOR(-0.8) | FLOOR(0.8) | +-------------+------------+ | -1 | 0 | +-------------+------------+ 1 row in set (0.00 sec) mysql> select MOD(15,10), MOD(1,11), MOD(null,10),MOD(10,null); +------------+-----------+--------------+--------------+ | MOD(15,10) | MOD(1,11) | MOD(null,10) | MOD(10,null) | +------------+-----------+--------------+--------------+ | 5 | 1 | NULL | NULL | +------------+-----------+--------------+--------------+ 1 row in set (0.00 sec) mysql> select RAND(), RAND(); +--------------------+---------------------+ | RAND() | RAND() | +--------------------+---------------------+ | 0.9831737400807196 | 0.41050817017490804 | +--------------------+---------------------+ 1 row in set (0.00 sec) mysql> select ceil(100*RAND()); +------------------+ | ceil(100*RAND()) | +------------------+ | 11 | +------------------+ 1 row in set (0.00 sec) mysql> select ceil(100*RAND()); +------------------+ | ceil(100*RAND()) | +------------------+ | 29 | +------------------+ 1 row in set (0.00 sec) mysql> select ROUND(1.1), ROUND(1), ROUND(1.1,2), ROUND(1,2); +------------+----------+--------------+------------+ | ROUND(1.1) | ROUND(1) | ROUND(1.1,2) | ROUND(1,2) | +------------+----------+--------------+------------+ | 1 | 1 | 1.10 | 1 | +------------+----------+--------------+------------+ 1 row in set (0.00 sec) mysql> select ROUND(1.235,2), TRUNCATE(1.235,2); +----------------+-------------------+ | ROUND(1.235,2) | TRUNCATE(1.235,2) | +----------------+-------------------+ | 1.24 | 1.23 | +----------------+-------------------+ 1 row in set (0.00 sec)
三、日期和时间函数
CURDATE()：返回当前日期 CURTIME()：返回当前时间 NOW()：返回当前的日期和时间 UNIX_TIMESTAMP(date)：返回日期date的UNIX时间戳 FROM_UNIX(time)：返回UNIX时间戳的日期值 WEEK(date)：返回日期date为一年中的第几周 YEAR(date)：返回日期date的年份 HOUR(time)：返回time的小时值 MINUTE(time)：返回time的分钟值 MONTHNAME(date)：返回date的月份名 DATE_FORMAT(date,fmt)：返回按字符串fmt格式化日期date值 DATE_ADD(date, INTERVAL expr type)：返回一个日期或时间值加上一个时间间隔的时间值 DATEDIFF(expr,expr2)：返回从expr2经过了多少天才能到expr
举例：
mysql> select CURDATE(); +------------+ | CURDATE() | +------------+ | 2018-09-04 | +------------+ 1 row in set (0.00 sec) mysql> select CURTIME(); +-----------+ | CURTIME() | +-----------+ | 20:06:34 | +-----------+ 1 row in set (0.00 sec) mysql> select NOW(); +---------------------+ | NOW() | +---------------------+ | 2018-09-04 20:06:44 | +---------------------+ 1 row in set (0.00 sec) mysql> select UNIX_TIMESTAMP(now()); +-----------------------+ | UNIX_TIMESTAMP(now()) | +-----------------------+ | 1536062823 | +-----------------------+ 1 row in set (0.00 sec) mysql> select FROM_UNIXTIME(1536062823); +---------------------------+ | FROM_UNIXTIME(1536062823) | +---------------------------+ | 2018-09-04 20:07:03 | +---------------------------+ 1 row in set (0.00 sec) mysql> select WEEK(now()), YEAR(now()); +-------------+-------------+ | WEEK(now()) | YEAR(now()) | +-------------+-------------+ | 35 | 2018 | +-------------+-------------+ 1 row in set (0.00 sec) mysql> select HOUR(curtime()), MINUTE(curtime()); +-----------------+-------------------+ | HOUR(curtime()) | MINUTE(curtime()) | +-----------------+-------------------+ | 20 | 9 | +-----------------+-------------------+ 1 row in set (0.00 sec) mysql> select MONTHNAME(now()); +------------------+ | MONTHNAME(now()) | +------------------+ | September | +------------------+ 1 row in set (0.00 sec) mysql> select DATE_FORMAT(now(), '%M,%D,%Y'); +--------------------------------+ | DATE_FORMAT(now(), '%M,%D,%Y') | +--------------------------------+ | September,4th,2018 | +--------------------------------+ 1 row in set (0.00 sec) mysql> select DATEDIFF('2008-08-08',now()); +------------------------------+ | DATEDIFF('2008-08-08',now()) | +------------------------------+ | -3679 | +------------------------------+ 1 row in set (0.00 sec) mysql> select DATEDIFF('2008-08-08','2008-8-10'); +------------------------------------+ | DATEDIFF('2008-08-08','2008-8-10') | +------------------------------------+ | -2 | +------------------------------------+ 1 row in set (0.00 sec) mysql> select DATEDIFF(now(), '2008-08-08'); +-------------------------------+ | DATEDIFF(now(), '2008-08-08') | +-------------------------------+ | 3679 | +-------------------------------+ 1 row in set (0.00 sec)
另外，要着重看的函数就是DATE_ADD(date, INTERVAL expr type)，其中INTERVAL是间隔类型关键字，expr是一个表达式，type是间隔类型。
同时，在expr中，使用正数来返回之后的某个日期时间，使用负数来返回之前的某个日期时间。
mysql> select -> now() as current, -> DATE_ADD(now(),INTERVAL '31' DAY) as after31days, -> DATE_ADD(now(),INTERVAL '-31' DAY) as behind31days, -> DATE_ADD(now(),INTERVAL '1_2' YEAR_MONTH) as afterOneYearTwoMonth, -> DATE_ADD(now(),INTERVAL '-1_-2' YEAR_MONTH) as BehindOneYearTwoMonth; +---------------------+---------------------+---------------------+----------------------+-----------------------+ | current | after31days | behind31days | afterOneYearTwoMonth | BehindOneYearTwoMonth | +---------------------+---------------------+---------------------+----------------------+-----------------------+ | 2018-09-04 21:11:23 | 2018-10-05 21:11:23 | 2018-08-04 21:11:23 | 2019-11-04 21:11:23 | 2017-07-04 21:11:23 | +---------------------+---------------------+---------------------+----------------------+-----------------------+ 1 row in set (0.00 sec)
其中，13中间隔类型分别为：
HOUR：hh MINUTE：mm SECOND：ss YEAR：YY MONTH：MM DAY：DD YEAR_MONTH：YY-MM DAY_HOUR：DD hh DAY_MINUTE：DD hh:mm DAY_SECOND：DD hh:mm:ss HOUR_MINUTE：hh:mm HOUR_SECOND：hh:ss MINUTE_SECOND:mm:ss
四、流程函数
- IF(value,t,f)：如果value是真，返回t；否则，返回f
- IFNULL(value1, value2)：如果value1不为空，返回value1；否则，返回value2
- CASE WHEN[value1] THEN[result1] ...ELSE[default] END：如果value1为真，返回result1，否则返回default
- CASE[expr] WHEN[value1] THEN[result1]...ELSE[default] END：如果expr等于value1，返回result1，否则返回default
mysql> create table salary(userid int, salary decimal(9,2)); Query OK, 0 rows affected (0.04 sec) mysql> insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from salary; +--------+---------+ | userid | salary | +--------+---------+ | 1 | 1000.00 | | 2 | 2000.00 | | 3 | 3000.00 | | 4 | 4000.00 | | 5 | 5000.00 | | 1 | NULL | +--------+---------+ 6 rows in set (0.00 sec) mysql> select IF(salary>2000, 'high','low') as HighOrLow from salary; +-----------+ | HighOrLow | +-----------+ | low | | low | | high | | high | | high | | low | +-----------+ 6 rows in set (0.00 sec) mysql> select IFNULL(salary, 0) as IsNullOrNot from salary; +-------------+ | IsNullOrNot | +-------------+ | 1000.00 | | 2000.00 | | 3000.00 | | 4000.00 | | 5000.00 | | 0.00 | +-------------+ 6 rows in set (0.00 sec) mysql> select CASE WHEN salary<=2000 THEN 'low' ELSE 'high' END as HighOrLow from salary; +-----------+ | HighOrLow | +-----------+ | low | | low | | high | | high | | high | | high | +-----------+ 6 rows in set (0.00 sec) mysql> select CASE salary WHEN 1000 THEN 'low' WHEN 2000 THEN 'mid' ELSE 'high' END as HighOrMidOrLow from salary; +----------------+ | HighOrMidOrLow | +----------------+ | low | | mid | | high | | high | | high | | high | +----------------+ 6 rows in set (0.00 sec)
五、其他常用函数
- DATABASE()：返回当前数据库名
- VERSION()：返回当前数据库版本
- USER()：返回当前登录用户名
- INET_ATON(IP)：返回IP地址的数字表示
- INET_NTOA(num)：返回数字代表的IP地址
- PASSWORD(str)：此函数只用来设置系统用户的密码，但是不能用来对应用的数据进行加密，如果要对应用数据进行加密，可以使用MD5等加密函数来实现。
- MD5(str)：返回字符串str的MD5值
mysql> select DATABASE(); +------------+ | DATABASE() | +------------+ | tmz | +------------+ 1 row in set (0.00 sec) mysql> select VERSION(); +-----------+ | VERSION() | +-----------+ | 5.7.20 | +-----------+ 1 row in set (0.00 sec) mysql> select USER(); +--------+ | USER() | +--------+ | root@ | +--------+ 1 row in set (0.00 sec) mysql> select PASSWORD('123456'); +-------------------------------------------+ | PASSWORD('123456') | +-------------------------------------------+ | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select MD5('123456'); +----------------------------------+ | MD5('123456') | +----------------------------------+ | e10adc3949ba59abbe56e057f20f883e | +----------------------------------+ 1 row in set (0.00 sec)
这里要着重关注一下与IP地址有关的下面的两个函数：
- INET_ATON(IP)：返回IP地址的数字表示，返回32位无符号整数，不是字符串，因此应该用无符号整数来存储。
- INET_NTOA(num)：返回数字代表的IP地址
mysql> select INET_ATON('192.168.1.1'), INET_NTOA(3232235777); +--------------------------+-----------------------+ | INET_ATON('192.168.1.1') | INET_NTOA(3232235777) | +--------------------------+-----------------------+ | 3232235777 | 192.168.1.1 | +--------------------------+-----------------------+ 1 row in set (0.00 sec)
可以看到，MySQL采用的是大端序，也就是说返回的是网络字节序。
mysql> select 192*POW(256,3)+168*POW(256,2)+1*POW(256,1)+1*POW(256,0); +---------------------------------------------------------+ | 192*POW(256,3)+168*POW(256,2)+1*POW(256,1)+1*POW(256,0) | +---------------------------------------------------------+ | 3232235777 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
六、扩展关于网络字节序和主机字节序的问题：
1.数据在两个主机a和b之间通过网络进行传输的过程是：
存储在主机a上的数据-------------> 在网络中进行传输--------------->数据存储到主机b上。
2.字节序
大端字节序（big-endian）：按照内存增长的方向，高位数据存储在低位内存中。
小端字节序（little-endian）：按照内存增长的方向，低位数据存储在低位内存中。
3.网络字节序
在网络传输中，TCP/IP协议在保存IP地址这个32位二进制数时，协议规定采用在低位存储地址中包含数据的高位字节的存储顺序（大端字节序），这种顺序存储格式就称为网络字节顺序格式。与具体的CPU类型、操作系统等无关。
3.主机字节序
不同的CPU、操作系统、和平台等有不同的字节序类型，这些字节序是指整数在内存中保存的顺序，叫做主机字节序。
即使是同一台机器上的两个进程（比如一个由C语言，另一个由Java编写）通信，也要考虑字节序的问题。
例如，a主机是JVM，其采用大端字节序；而b主机为c++ windows平台，其采用小端字节序。
（JVM无论平台，都采用大端字节序，而linux，unix采用大端序，windows采用小端序）
假设要将a主机上的int i = 0x12345678通过网络传输大主机b上，那么首先看一下i在两个主机当中的存储方式：
-----------------------------------------------> 内存增长方向（低->高）
主机a采用大端字节序：（数据高位）12 34 56 78
主机b采用小端字节序：（数据低位）78 56 34 12
4.为什么要进行字节转换
假设网络（TCP/IP）和主机a（JVM）都采用大端字节序，那么在经过网络发送后，主机b接收到的数据顺序是12 34 56 78，而放到内存中的数据也是12 34 56 78，由于主机b是按照自己的小端字节序进行解析的，因此拼好之后就是int i = 0x78563412，这里c++平台转换为网络字节序（大端序）即可。
因此，只要数据要从一个地方的内存通过网络传输到另一个地方的内存，只需要将两端的主机字节序都转换成网络字节序就可以保持数据的一致性。
七、MySQL中的运算符
1.算术运算符
加法（+）、减法（-）、乘法（*）、除法取商（/，DIV）、除法取余（%，MOD）
2.比较运算符
等于(=)、不等于(<>或!=)、NULL安全的等于(<=>)、小于(<)、小于等于(<=)、大于(>)、大于等于(>=)、存在于指定范围(BETWEEN)、存在于指定集合(IN)、为NULL(IS NULL)、不为NULL(IS NOT NULL)、通配符匹配(LIKE)、正则表达式匹配(REGEXP或RLIKE)
mysql> select 1=0, 1=1, NULL=NULL; +-----+-----+-----------+ | 1=0 | 1=1 | NULL=NULL | +-----+-----+-----------+ | 0 | 1 | NULL | +-----+-----+-----------+ 1 row in set (0.00 sec) mysql> select 1<>0, 1<>1, NULL<>NULL; +------+------+------------+ | 1<>0 | 1<>1 | NULL<>NULL | +------+------+------------+ | 1 | 0 | NULL | +------+------+------------+ 1 row in set (0.00 sec) mysql> select 1<=>0, 1<=>1, NULL<=>NULL; +-------+-------+-------------+ | 1<=>0 | 1<=>1 | NULL<=>NULL | +-------+-------+-------------+ | 0 | 1 | 1 | +-------+-------+-------------+ 1 row in set (0.00 sec) mysql> select 'a' < 'b', 'a'<'a', 'a'<'c',1<2; +-----------+---------+---------+-----+ | 'a' < 'b' | 'a'<'a' | 'a'<'c' | 1<2 | +-----------+---------+---------+-----+ | 1 | 0 | 1 | 1 | +-----------+---------+---------+-----+ 1 row in set (0.00 sec) mysql> select 'bdf'<='b', 'b'<='b',0<=1; +------------+----------+------+ | 'bdf'<='b' | 'b'<='b' | 0<=1 | +------------+----------+------+ | 0 | 1 | 1 | +------------+----------+------+ 1 row in set (0.00 sec) mysql> select 10 BETWEEN 10 AND 20, 9 BETWEEN 10 AND 20; +----------------------+---------------------+ | 10 BETWEEN 10 AND 20 | 9 BETWEEN 10 AND 20 | +----------------------+---------------------+ | 1 | 0 | +----------------------+---------------------+ 1 row in set (0.00 sec) mysql> select 1 IN(1,2,3), 't' in ('t','a','b','e'), 0 in (1,2); +-------------+--------------------------+------------+ | 1 IN(1,2,3) | 't' in ('t','a','b','e') | 0 in (1,2) | +-------------+--------------------------+------------+ | 1 | 1 | 0 | +-------------+--------------------------+------------+ 1 row in set (0.00 sec) mysql> select 0 IS NULL, null IS NULL; +-----------+--------------+ | 0 IS NULL | null IS NULL | +-----------+--------------+ | 0 | 1 | +-----------+--------------+ 1 row in set (0.00 sec) mysql> select 0 IS NOT NULL, null IS NOT NULL; +---------------+------------------+ | 0 IS NOT NULL | null IS NOT NULL | +---------------+------------------+ | 1 | 0 | +---------------+------------------+ 1 row in set (0.00 sec) mysql> select 123456 like '123%', 123456 like '%123%', 123456 like '%321%'; +--------------------+---------------------+---------------------+ | 123456 like '123%' | 123456 like '%123%' | 123456 like '%321%' | +--------------------+---------------------+---------------------+ | 1 | 1 | 0 | +--------------------+---------------------+---------------------+ 1 row in set (0.00 sec) mysql> select 'abcdef' REGEXP 'ab', 'abcdef' REGEXP 'k'; +----------------------+---------------------+ | 'abcdef' REGEXP 'ab' | 'abcdef' REGEXP 'k' | +----------------------+---------------------+ | 1 | 0 | +----------------------+---------------------+ 1 row in set (0.00 sec)
3.逻辑运算符
非(NOT或!)、与(AND或&&)、或(OR或||)、异或(XOR)
4.位运算符
位与(&)、位或(|)、位异或(^)、位取反(~)、位右移(>>)、位左移(<<)
儿女情长什么的，最影响我们闯荡江湖了。
