运算符是保留字或主要用于 SQL 语句的 WHERE 子句 中的字符,用于执行操作,例如:比较和算术运算。 这些运算符用于指定 SQL 语句中的条件,并用作语句中多个条件的连词。 常见运算符有以下几种:
算术运算符主要用于数学运算,其可以连接运算符前后的两个数值或表达式,对数值或表达式进行加 (+)、减(-)、乘(*)、除(/)和取模(%)运算。
运算符 | 名称 | 作用 | 示例 |
---|---|---|---|
+ | 加法运算符 | 计算两个值或表达式的和 | SELECT A+B |
- | 减法运算符 | 计算两个值或表达式的差 | SELECT A-B |
* | 乘法运算符 | 计算两个值或表达式的乘积 | SELECT A*B |
/或DIV | 除法运算符 | 计算两个值或表达式的商 | SELECT A/B 或者 SELECT A DIV B |
%或MOD | 求模(求余)运算符 | 计算两个值或表达式的余数 | SELECT A%B 或者 SELECT A DOM B |
加减运算符:
比较运算符用来对表达式左边的操作数和右边的操作数进行比较,比较的结果为真则返回1,比较的结果 为假则返回0,其他情况则返回NULL。 比较运算符经常被用来作为SELECT查询语句的条件来使用,返回符合条件的结果记录。
运算符 | 描述 | 例子 |
---|---|---|
= | 检查两个操作数的值是否相等,如果是,则条件为真(true) | (a = b)is false |
!= | 检查两个操作数的值是否相等,如果值不相等则条件为真(true) | (a != b)is true |
<> | 检查两个操作数的值是否相等,如果值不相等则条件为真(true) | (a <> b)is true |
> | 检查左操作数的值是否大于右操作数的值,如果是,则条件为真(true) | (a > b)is false |
< | 检查左操作数的值是否小于右操作数的值,如果是,则条件为真(true) | (a < b)is true |
>= | 检查左操作数的值是否大于或等于右操作数的值,如果是,则条件为真(true) | (a < b)is false |
<= | 检查左操作数的值是否小于或等于右操作数的值,如果是,则条件为真(true) | (a < b)is true |
!< | 检查左操作数的值是否不小于右操作数的值,如果是,则条件为真(true) | (a < b)is false |
!> | 检查左操作数的值是否不大于右操作数的值,如果是,则条件为真(true) | (a < b)is true |
<=> | 作用与“=”运算符作用一样,区别在于“<=>” 可以用来对NULL进行判断 | (a<=>b)is false |
这是在 SQL 所有的逻辑运算符的列表。
运算符 | 描述 | 作用 | 示例 |
---|---|---|---|
IS NOT NULL | 不为空运算符 | 判断(值、字符串或表达式)是否不为空 | SELECT B FROM TABLE WHERE A IS NOT NULL |
LEAST | 最小值运算符 | 在多个值中放回最小值 | SELECT D FROM TABLE WHERE C LEAST(A,B) |
GREATEST | 最大值运算符 | 在多个值中放回最大值 | SELECT D FROM TABLE WHERE C GREATEST(A,B) |
BETWEEN AND | 两值之间的运算符 | 判断一个值是否在两个值之间 | SELECT D FROM TABLE WHERE C BETWEEN A AND B |
ISNULL | 为空运算符 | 判断一个值、字符串或表达式是否为空 | SELECT B FROM TABLE WHERE A ISNULL |
IN | 属于运算符 | 判断一个值是否为列表中的任意一个值 | SELECT D FROM TABLE WHERE C IN(A,B) |
NOT IN | 不属于运算符 | 判断一个值是否不是一个列表中的任意一个值 | SELECT D FROM TABLE WHERE C NOT IN(A,B) |
LIKE | 模糊匹配运算符 | 判断一个值是否符合模糊匹配规则 | SELECT C FROM TABLE WHERE A LIKE B |
REGEXP | 正则表达式运算符 | 判断一个值是否符合正则表达式的规则 | SELECT C FROM TABLE WHERE A REGEXP B |
RLIKE | 正则表达式运算符 | 判断一个值是否符合正则表达式的规则 | SELECT C FROM TABLE WHERE A RLIKE B |
位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数,然后进行位运算, 最后将计算结果从二进制变回十进制数。 MySQL支持的位运算符如下:
运算符 | 作用 | 示例 |
---|---|---|
& | 按位与(位AND) | SELECT A & B |
| | 按位或(位OR) | SELECT A | B |
^ | 按位异或(位XOR) | SELECT A ^ B |
~ | 按位取反 | SELECT ~ A |
>> | 按位右移 | SELECT A >> 2 |
<< | 按位左移 | SELECT A >> 2 |
1的二进制数为0001,10的二进制数为1010,所以1 & 10的结果为0000,对应的十进制数为0。20的二进制 数为10100,30的二进制数为11110,所以20 & 30的结果为10100,对应的十进制数为20。
1的二进制数为0001,10的二进制数为1010,所以1 | 10的结果为1011,对应的十进制数为11。20的二进 制数为10100,30的二进制数为11110,所以20 | 30的结果为11110,对应的十进制数为30。
1的二进制数为0001,10的二进制数为1010,所以1 ^ 10的结果为1011,对应的十进制数为11。20的二进 制数为10100,30的二进制数为11110,所以20 ^ 30的结果为01010,对应的十进制数为10。
由于按位取反(~)运算符的优先级高于按位与(&)运算符的优先级,所以10 & ~1,首先,对数字1进 行按位取反操作,结果除了最低位为0,其他位都为1,然后与10进行按位
1的二进制数为0000 0001,右移2位为0000 0000,对应的十进制数为0。4的二进制数为0000 0100,右移2 位为0000 0001,对应的十进制数为1。
1的二进制数为0000 0001,左移两位为0000 0100,对应的十进制数为4。4的二进制数为0000 0100,左移 两位为0001 0000,对应的十进制数为16。
数字编号越大,优先级越高,优先级高的运算符先进行计算。
优先级 | 运算符 |
---|---|
1 | :=,=(赋值) |
2 | ‖,OR,XOR |
3 | &&,AND |
4 | NOT |
5 | BETWEEN,CASE,WHEN,THEN 和 ELSE |
6 | =(比较运算符),<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP 和 IN |
7 | | |
8 | & |
9 | << 与 >> |
10 | - 和 + |
11 | *,/,DIV, & 和 MOD |
12 | ^ |
13 | -(负号)和 ~(按位取反) |
14 | ! |
15 | () |
拓展:使用正则表达式查询
正则表达式通常被用来检索或替换那些符合某个模式的文本内容,根据指定的匹配模式匹配文本中符合 要求的特殊字符串。例如,从一个文本文件中提取电话号码,查找一篇文章中重复的单词或者替换用户 输入的某些敏感词语等,这些地方都可以使用正则表达式。正则表达式强大而且灵活,可以应用于非常 复杂的查询。
MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配 列表。
选项 | 说明 | 例子 | 匹配值示例 |
---|---|---|---|
^ | 匹配文本的开始字符 | '^b'匹配以字母 b 开头的字符串 | book,big,banana,bike |
$ | 匹配文本的结束字符 | 'st$'匹配以 st结尾的字符串 | test,resist,persist |
. | 匹配任何单个字符 | 'b.t'匹配任何 b 和 t 之间有一个字符的字符串 | bit,bat,but,bite |
* | 匹配零个或多个在它前面的字符 | 'f*n'匹配字符 n 前面有任意个字符 f 的字符串 | fn,fan,faan,fabcn |
+ | 匹配前面的字符1次或多次 | 'ba+'匹配以b 开头后面紧跟至少有一个a 的字符串 | ba,bay,bare,battle |
<字符串> | 匹配包含指定的字符串的文本 | 'fa'匹配包含fa的字符串 | fan,afa,faad |
[字符集合] | 匹配字符集合中的任何一个字符 | '[xz]'匹配包含 x 或 z的字符串 | dizzy,zebra |
[^] | 匹配不在括号中的任何字符 | '[^abc]'匹配任何不包含a、b或c的字符串 | desk,fox,f8ke |
字符串{n,} | 匹配前面的字符串至少n次 | b{2}匹配 2个或更多的b | bbb,bbbb,bbbbbbb |
字符串{n,m} | 匹配前面的字符至少n次,至多m次,如果 n为0,此参数为可选参数 | b{2,4}匹配含最少2个、最多4个b的字符串 | bb,bbb,bbbb |
# 1.查询以特定字符或字符串开头的记录 字符‘^’匹配以特定字符或者字符串开头的文本。
# 在fruits表中,查询f_name字段以字母‘b’开头的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP '^b';
# 2.查询以特定字符或字符串结尾的记录 字符‘$’匹配以特定字符或者字符串结尾的文本。
# 在fruits表中,查询f_name字段以字母‘y’结尾的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'y$';
# 3.用符号"."来替代字符串中的任意一个字符 字符‘.’匹配任意一个字符。
# 在fruits表中,查询f_name字段值包含字母‘a’与‘g’且两个字母之间只有一个字母的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
# 4.使用""和"+"来匹配多个字符 星号‘’匹配前面的字符任意多次,包括0次。加号‘+’匹配前面的字符至少一次。
# 在fruits表中,查询f_name字段值以字母‘b’开头且‘b’后面出现字母‘a’的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP '^ba*'
# 5.匹配指定字符串 正则表达式可以匹配指定字符串,只要这个字符串在查询文本中即可,如要匹配多个字符串,多个字符串之间使用分隔符‘|’隔开。
# 在fruits表中,查询f_name字段值包含字符串“on”的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'on';
# 在fruits表中,查询f_name字段值包含字符串“on”或者“ap”的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
# 之前介绍过,LIKE运算符也可以匹配指定的字符串,但与REGEXP不同,LIKE匹配的字符串如果在文本中间出现,则找不到它,相应的行也不会返回。
# REGEXP在文本内进行匹配,如果被匹配的字符串在文本中出现,REGEXP将会找到它,相应的行也会被返回。对比结果如下所示。
# 在fruits表中,使用LIKE运算符查询f_name字段值为“on”的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name like 'on'; Empty set(0.00 sec)
# 6.匹配指定字符中的任意一个 方括号“[]”指定一个字符集合,只匹配其中任何一个字符,即为所查找的
文本。
# 在fruits表中,查找f_name字段中包含字母‘o’或者‘t’的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
# 在fruits表中,查询s_id字段中包含4、5或者6的记录,SQL语句如下:
SELECT * FROM fruits WHERE s_id REGEXP '[456]';
# 7.匹配指定字符以外的字符 “[^字符集合]” 匹配不在指定集合中的任何字符。
# 在fruits表中,查询f_id字段中包含字母a~e和数字1~2以外字符的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
# 8.使用{n,}或者{n,m}来指定字符串连续出现的次数 “字符串{n,}”表示至少匹配n次前面的字符;“字符串{n,m}”表示匹配前面的字符串不少于n次,不多于m次。例如,a{2,}表示字母a连续出现至少2次,也可以大于2次;a{2,4}表示字母a连续出现最少2次,最多不能超过4次。
# 在fruits表中,查询f_name字段值出现字母‘x’至少2次的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
# 在fruits表中,查询f_name字段值出现字符串“ba”最少1次、最多3次的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}' ;
排序规则
单列排序
# 根据 hire_date 升序排序排列:
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
# 根据 hire_date 排序降序排列
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;
SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;
多列排序
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
为什么要分页? 原因:查询返回的记录太多了,查看起来很不方便,想自定义返回行数
实现规则
第一个“位置偏移量”参数指示MySQL从哪一行开始显示,是一个可选参数,如果不指定“位置偏移 量”,将会从表中的第一条记录开始(第一条记录的位置偏移量是0,第二条记录的位置偏移量是 1,以此类推);第二个参数“行数”指示返回的记录条数。
#例子
--前10条记录:
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录:
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录:
SELECT * FROM 表名 LIMIT 20,10;
MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”,意思是获取从第5条记录开始后面的3条记录,和“LIMIT 4,3;”返回的结果相同。
SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;
约束返回结果的数量可以 减少数据表的网络传输量 ,也可以 提升查询效率 。如果我们知道返回结果只有 1 条,就可以使用 LIMIT 1 ,告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需 要扫描完整的表,只需要检索到一条符合条件的记录即可返回。
拓展 在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关 键字,而且需要放到 SELECT 语句的最后面。
SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC
SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY
SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;
需要说明的是,这条语句是先取出来前 5 条数据行,然后再按照 hp_max 从高到低的顺序进行排序。但 这样产生的结果和上述方法的并不一样。我会在后面讲到子查询,你可以使用
SELECT rownum, last_name,salary
FROM (
SELECT last_name,salary
FROM employees
ORDER BY salary DESC)
WHERE rownum < 10;
得到与上述方法一致的结果。
MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制 函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里,我将这些丰富的内置函数再分为两类: 单行函数、聚合函数(或分组函数) 。
函数 | 用法 |
---|---|
ASCII(S) | 返回字符串S中的第一个字符的ASCII码值 |
CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
CONCAT(s1,s2,......,sn) | 连接s1,s2,......,sn为一个字符串 |
CONCAT_WS(x, s1,s2,......,sn) | 同CONCAT(s1,s2,...)函数,但是每个字符串之间要加上x |
INSERT(str, idx, len,replacestr) | 将字符串str从第idx位置开始,len个字符长的子串替换为字符串replacestr |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(str,n) | 返回字符串str最左边的n个字符 |
RIGHT(str,n) | 返回字符串str最右边的n个字符 |
LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 |
TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 |
TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 |
REPEAT(str, n) | 返回str重复n次的结果 |
SPACE(n) | 返回n个空格 |
STRCMP(s1,s2) | 比较字符串s1,s2的ASCII码值的大小 |
SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符,作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同 |
LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置,作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到,返回0 |
ELT(m,s1,s2,…,sn) | 返回指定位置的字符串,如果m=1,则返回s1,如果m=2,则返回s2,如 果m=n,则返回sn |
FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 |
FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置。其中,字符串s2是一个以逗号分 隔的字符串 |
REVERSE(s) | 返回s反转后的字符串 |
NULLIF(value1,value2) | 比较两个字符串,如果value1与value2相等,则返回NULL,否则返回 value1 |
注意:MySQL中,字符串的位置是从1开始的。
EXTRACT(type FROM date)函数中type的取值与含义:
上述 非GET_FORMAT 函数中fmt参数常用的格式符:
GET_FORMAT函数中date_type和format_type参数取值如下:
流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
加密与解密函数主要用于对数据库中的数据进行加密和解密处理,以防止数据被他人窃取。这些函数在 保证数据库安全时非常有用。
可以看到,ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。
MySQL中内置了一些可以查询MySQL信息的函数,这些函数主要用于帮助数据库开发或运维人员更好地 对数据库进行维护工作。
MySQL中有些函数无法对其进行具体的分类,但是这些函数在MySQL的开发和运维过程中也是不容忽视 的。
聚合函数作用于一组数据,并对一组数据返回一个值。
聚合函数类型
聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用
AVG:求平均值 SUM:求和 可以对数值型数据使用AVG 和 SUM 函数。
SELECT AVG(salary),SUM(salary)
FROM employees
MIN:求最小值 MAX:求最大值 可以对任意数据类型的数据使用 MIN 和 MAX 函数。
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
COUNT(expr) 返回expr不为空的记录总数。
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;