MySQL（三）——常用运算符、常用函数

一、常用运算符

1、算术运算符

SELECT num, num+10, num-3+5, num+36.5 FROM table_test;

SELECT num, num*2, num/2, num/3, num%3 FROM table_test;

//取余，MOD(a,b) 相当于 a%b
SELECT MOD (num,3) FROM temp;

2、逻辑运算符
（1）逻辑非运算（NOT 或者 !）

当操作数为 0（假）时，返回值为 1；
当操作数为非零值时，返回值为 0；
当操作数为 NULL 时，返回值为 NULL。

SELECT NOT 10,NOT 0,NOT NULL;

（2）逻辑与运算符（AND 或者 &&）

当所有操作数都为非零值并且不为 NULL 时，返回值为 1；
当一个或多个操作数为 0 时，返回值为 0；
操作数中有任何一个为 NULL 时，返回值为 NULL。

SELECT 1 AND -1, 1 AND 0, 0 AND 0, 1 AND NULL;

（3）逻辑或运算符（OR 或者 ||）

当两个操作数都为非 NULL 值时，如果有任意一个操作数为非零值，则返回值为 1，否则结果为 0；
当有一个操作数为 NULL 时，如果另一个操作数为非零值，则返回值为 1，否则结果为NULL；
假如两个操作数均为 NULL 时，则返回值为 NULL。

SELECT 1 OR 0, 1 OR NULL, 0 OR NULL, NULL OR NULL;

（4）异或运算（XOR 运算符）

当任意一个操作数为 NULL 时，返回值为 NULL；
对于非 NULL 的操作数，如果两个操作数都是非 0 值或者都是 0 值，则返回值为 0；
如果一个为 0 值，另一个为非 0 值，返回值为 1。

SELECT 1 XOR NULL, 1 XOR 1, 0 XOR 0, 1 XOR 0;

3、比较运算符
（1）等于运算（=）

若有一个或两个操作数为 NULL，则比较运算的结果为 NULL。
若两个操作数都是字符串，则按照字符串进行比较。
若两个操作数均为整数，则按照整数进行比较。
若一个操作数为字符串，另一个操作数为数字，则 MySQL 可以自动将字符串转换为数字。

（2）安全等于运算符（<=>）

和 等于运算（=）符类似，不过 <=> 可以用来判断 NULL 值；
当两个操作数均为 NULL 时，其返回值为 1 而不为 NULL；
而当一个操作数为 NULL 时，其返回值为 0 而不为 NULL。

（3）不等于运算符（<> 或者 !=）

与 = 的作用相反，<> 和 != 用于判断数字、字符串、表达式是否不相等。
对于 <> 和 !=，如果两侧操作数不相等，返回值为 1，否则返回值为 0。
如果两侧操作数有一个是 NULL，那么返回值也是 NULL。

（4）小于等于运算符（<=）

<= 是小于等于运算符，用来判断左边的操作数是否小于或者等于右边的操作数；
如果小于或者等于，返回值为 1，否则返回值为 0；
如果两侧操作数有一个是 NULL，那么返回值也是 NULL。

（5）小于运算符（<）

< 是小于运算符，用来判断左边的操作数是否小于右边的操作数；
如果小于，返回值为 1，否则返回值为 0；
如果两侧操作数有一个是 NULL，那么返回值也是 NULL。

（6）大于等于运算符（>=）

= 是大于等于运算符，用来判断左边的操作数是否大于或者等于右边的操作数；
如果大于或者等于，返回值为 1，否则返回值为 0；
如果两侧操作数有一个是 NULL，那么返回值也是 NULL。

（7）大于运算符（>）

是大于运算符，用来判断左边的操作数是否大于右边的操作数；
如果大于，返回值为 1，否则返回值为 0；
如果两侧操作数有一个是 NULL，那么返回值也是 NULL。

（8）IS NULL(ISNULL) 和 IS NOT NULL 运算符

IS NULL 或 ISNULL 运算符用来检测一个值是否为 NULL，如果为 NULL，返回值为 1，否则返回值为 0。
ISNULL 可以认为是 IS NULL 的简写，去掉了一个空格而已，两者的作用和用法都是完全相同的。
IS NOT NULL 运算符用来检测一个值是否为非 NULL，如果是非 NULL，返回值为 1，否则返回值为 0。

（9）BETWEEN AND 运算符

BETWEEN AND 运算符用来判断表达式的值是否位于两个数之间，或者说是否位于某个范围内。

4、位运算符
（1）位或运算符 |

参与|运算的两个二进制位有一个为 1 时，结果就为 1，两个都为 0 时结果才为 0。例如1|1结果为 1，0|0结果为0，1|0结果为1，这和逻辑运算中的||非常类似。

eg：

SELECT 10|15,9|4|2;

（2）位与运算符 &

参与&运算的两个二进制位都为 1 时，结果就为 1，否则为 0。例如1|1结果为 1，0|0结果为 0，1|0结果为 0，这和逻辑运算中的&&非常类似。

eg：

SELECT 10 & 15,9 & 4 & 2;

（3）位异或运算符 ^

参与^运算的两个二进制位不同时，结果为 1，相同时，结果为 0。例如1|1结果为 0，0|0结果为 0，1|0结果为1。

eg：

SELECT 10^15,1^0,1^1;

（4）位左移运算符 <<

位左移是按指定值的补码形式进行左移，左移指定位数之后，左边高位的数值被移出并丢弃，右边低位空出的位置用 0 补齐。

eg：

SELECT 1<<2,4<<2;

（5）位右移运算符 >>

位右移是按指定值的补码形式进行右移，右移指定位数之后，右边低位的数值被移出并丢弃，左边高位空出的位置用 0 补齐。

eg：

SELECT 1>>1,16>>2;

（6）位取反运算符 ~

位取反是将参与运算的数据按对应的补码进行反转，也就是做 NOT 操作，即 1 取反后变 0，0 取反后变为 1。

eg：

SELECT ~1,~18446744073709551614;

5、IN 和 NOT IN 用法
（1）IN

IN 运算符用来判断表达式的值是否位于给出的列表中；如果是，返回值为 1，否则返回值为 0。

expr IN ( value1, value2, value3 ... valueN )

eg：

SELECT 2 IN (1,3,5,'thks'),'thks' IN (1,3,5, 'thks');

（2）NOT IN
NOT IN 用来判断表达式的值是否不存在于给出的列表中；如果不是，返回值为 1，否则返回值为 0。

expr NOT IN ( value1, value2, value3 ... valueN )

eg：

SELECT 2 NOT IN (1,3,5));

（3）当 IN 运算符的两侧有一个为空值 NULL 时，如果找不到匹配项，则返回值为 NULL；如果找到了匹配项，则返回值为 1。

SELECT NULL IN (1,3,5),10 IN (1,3,NULL);

（4）NOT IN 恰好相反，当 NOT IN 运算符的两侧有一个为空值 NULL 时，如果找不到匹配项，则返回值为 NULL；如果找到了匹配项，则返回值为 0。
eg：

SELECT NULL NOT IN (1,3,5,),10 NOT IN (1,0,NULL);

二、常用函数

数学函数
主要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数和获得随机数的函数等。
1、ABS(x) 返回 x 的绝对值
2、SQRT(x) 返回非负数 x 的二次方根
3、MOD(x,y) 返回 x 被 y 除后的余数，MOD() 对于带有小数部分的数值也起作用，它返回除法运算后的余数。
4、CEIL(x) 和 CEILING(x) 的意义相同，返回不小于 x 的最小整数值，返回值转化为一个 BIGINT。
5、FLOOR(x) 函数返回小于 x 的最大整数值。
6、RAND() 函数被调用时，可以产生一个在 0 和 1 之间的随机数。
7、ROUND(x) 函数返回最接近于参数 x 的整数；ROUND(x,y) 函数对参数x进行四舍五入的操作，返回值保留小数点后面指定的y位。
8、SIGN(x) 返回参数的符号，x 的值为负、零和正时返回结果依次为 -1、0 和 1。
9、POW(x,y) 函数和 POWER(x,y) 函数用于计算 x 的 y 次方。
10、SIN(x) 返回 x 的正弦值，其中 x 为弧度值。
11、ASIN(x) 返回 x 的反正弦值，若 x 不在 -1 到 1 的范围之内，则返回 NULL。
12、COS(x) 返回 x 的余弦值，x 为弧度值。
13、 ACOS(x) 。x 值的范围必须在 -1 和 1 之间，否则返回 NULL。
14、TAN(x) 返回 x 的正切值，x 为给定的弧度值。
15、ATAN(x) 返回 x 的反正切值，正切为 x 的值。
16、COT(x) 返回 x 的余切值，x 是给定的弧度值。

字符串函数
主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母都变成小写或大写字母的函数和获取子串的函数等。
1、LENGTH(str) 函数的返回值为字符串的字节长度，使用 uft8（UNICODE 的一种变长字符编码，又称万国码）编码字符集时，一个汉字是 3 个字节，一个数字或字母是一个字节。
2、CONCAT(sl，s2，…) 函数返回结果为连接参数产生的字符串，或许有一个或多个参数。
3、 INSERT(s1，x，len，s2) 返回字符串 s1，子字符串起始于 x 位置，并且用 len 个字符长的字符串代替 s2。
4、 LOWER(str) 可以将字符串 str 中的字母字符全部转换成小写。
5、UPPER(str) 可以将字符串 str 中的字母字符全部转换成大写。
6、LEFT(s，n) 函数返回字符串 s 最左边的 n 个字符。
7、RIGHT(s，n) 函数返回字符串 s 最右边的 n 个字符。
8、TRIM(s) 删除字符串 s 两侧的空格。
9、REPLACE(s，s1，s2) 使用字符串 s2 替换字符串 s 中所有的字符串 s1。
10、SUBSTRING(s，n，len) 带有 len 参数的格式，从字符串 s 返回一个长度同 len 字符相同的子字符串，起始于位置 n。
11、REVERSE(s) 可以将字符串 s 反转，返回的字符串的顺序和 s 字符串的顺序相反。

日期和时间函数
主要用于处理日期和时间。其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。
1、CURDATE() 和 CURRENT_DATE() 函数的作用相同，将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回，具体格式根据函数用在字符串或数字语境中而定。
2、CURTIME() 和 CURRENT_TIME() 函数的作用相同，将当前时间以“HH：MM：SS”或“HHMMSS”格式返回，具体格式根据函数用在字符串或数字语境中而定。
3、NOW() 和 SYSDATE() 函数的作用相同，都是返回当前日期和时间值，格式为“YYYY-MM-DD HH：MM：SS”或“YYYYMMDDHHMMSS”，具体格式根据函数用在字符串或数字语境中而定。
4、UNIX_TIMESTAMP(date) 若无参数调用，返回一个无符号整数类型的 UNIX 时间戳（'1970-01-01 00:00:00’GMT之后的秒数）。
5、 FROM_UNIXTIME(date) 函数把 UNIX 时间戳转换为普通格式的日期时间值，与 UNIX_TIMESTAMP () 函数互为反函数。
6、MONTH(date) 函数返回指定 date 对应的月份，范围为 1～12。
7、MONTHNAME(date) 函数返回日期 date 对应月份的英文全名。
8、DAYNAME(date) 函数返回 date 对应的工作日英文名称，例如 Sunday、Monday 等。
9、DAYOFWEEK(d) 函数返回 d 对应的一周中的索引（位置）。1 表示周日，2 表示周一，……，7 表示周六。这些索引值对应于ODBC标准。
10、WEEK() 函数计算日期 date 是一年中的第几周。WEEK(date,mode) 函数允许指定星期是否起始于周日或周一，以及返回值的范围是否为 0～52 或 1～53。
11、DAYOFYEAR(d) 函数返回 d 是一年中的第几天，范围为 1～366。
12、DAYOFMONTH(d) 函数返回 d 是一个月中的第几天，范围为 1～31。
13、YEAR() 函数可以从指定日期值中来获取年份值。
14、TIME_TO_SEC(time) 函数返回将参数 time 转换为秒数的时间值，转换公式为“小时 ×3600+ 分钟 ×60+ 秒”。
15、SEC_TO_TIME(seconds) 函数返回将参数 seconds 转换为小时、分钟和秒数的时间值。
16、DATE_ADD(date,INTERVAL expr type) 和 ADDDATE(date,INTERVAL expr type) 两个函数的作用相同，都是用于执行日期的加运算。
17、DATE_SUB(date,INTERVAL expr type) 和 SUBDATE(date,INTERVAL expr type) 两个函数作用相同，都是执行日期的减法运算。
18、ADDTIME(time,expr) 函数用于执行时间的加法运算。添加 expr 到 time 并返回结果。
19、SUBTIME(time,expr) 函数用于执行时间的减法运算。
20、DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。计算时只用到这些值的日期部分。
21、DATE_FORMAT(date，format) 函数是根据 format 指定的格式显示 date 值。
22、WEEKDAY(d) 返回 d 对应的工作日索引。0 表示周一，1 表示周二，……，6 表示周日。

条件判断函数
主要用于在 SQL 语句中控制条件选择。其中包括 IF 语句、CASE 语句和 WHERE 语句等。
1、MAX() 函数是用来返回指定列中的最大值。
2、MIN() 函数是用来返回查询列中的最小值。
3、COUNT() 函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数
4、SUM() 是一个求总和的函数，返回指定列值的总和。
5、AVG() 函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
6、IF 语句允许您根据表达式的某个条件或值结果来执行一组 SQL 语句。
7、IFNULL 函数是 MySQL 控制流函数之一，它接受两个参数，如果不是 NULL，则返回第一个参数。 否则，IFNULL 函数返回第二个参数。两个参数可以是文字值或表达式。

8、简单的 CASE 语句就是指使用简单 CASE 语句来检查表达式的值与一组唯一值的匹配。

CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END CASE;

eg：

SELECT CASE WEEKDAY(NOW()) WHEN 0 THEN '星期一' WHEN 1 THEN '星期二' WHEN 2 THEN '星期三' WHEN 3 THEN '星期四' WHEN 4 THEN '星期五' WHEN 5 THEN '星期六' ELSE '星期天' END AS  COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW());

9、可搜索的 CASE 语句。简单 CASE 语句仅允许将表达式的值与一组不同的值进行匹配。 为了执行更复杂的匹配，如范围，则可以使用可搜索 CASE 语句。 可搜索 CASE 语句等同于 IF 语句，但是它的构造更加可读。

CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE commands
END CASE;

eg：

SELECT CASE WHEN WEEKDAY(NOW())=0 THEN '星期一' WHEN WEEKDAY(NOW())=1 THEN '星期二'  WHEN WEEKDAY(NOW())=2 THEN '星期三' WHEN WEEKDAY(NOW())=3 THEN '星期四' WHEN WEEKDAY(NOW())=4 THEN '星期五' WHEN WEEKDAY(NOW())=5 THEN '星期六' WHEN WEEKDAY(NOW())=6 THEN '星期天' END AS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW());

系统信息函数
主要用于获取 MySQL 数据库的系统信息。其中包括获取数据库名的函数、获取当前用户的函数和获取数据库版本的函数等。

加密函数
主要用于对字符串进行加密解密。其中包括字符串加密函数和字符串解密函数等。

其他函数主要包括格式化函数和锁函数等。