MYSQL 5.7 高级SQL语句（2）——正则表达式、运算符、连接查询

最新推荐文章于 2023-12-27 18:47:09 发布

把倒立的世界腾空

最新推荐文章于 2023-12-27 18:47:09 发布

阅读量3.3k

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库高级sql语句 sql语句

本文链接：https://blog.csdn.net/shengjie87/article/details/108297094

版权

mysql 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

高级SQL语句

一、正则表达式

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式，REGEXP 操作符所支持的匹配模式如表所示。

匹配	描述
^	匹配文本的开始字符
$	匹配文本的结束字符
.	匹配任何单个字符
*	匹配零个或多个在它前面的字符
+	匹配前面的字符 1 次或多次
字符串	匹配包含指定的字符串
p1\|p2	匹配 p1 或 p2
[…]	匹配字符集合中的任意一个字符
[^…]	匹配不在括号中的任何字符
{n}	匹配前面的字符串 n 次
{n,m}	匹配前面的字符串至少 n 次，至多 m 次

查询以sh开头的学生信息

mysql> select id,name from info where name regexp '^sh';
+----+-----------+
| id | name      |
+----+-----------+
|  2 | shangzhen |
|  1 | shidapeng |
+----+-----------+
2 rows in set (0.01 sec)

查询以n结尾的学生信息

mysql> select id,name from info where name regexp 'n$';
+----+-----------+
| id | name      |
+----+-----------+
| 11 | nannan    |
|  2 | shangzhen |
|  3 | tangyan   |
| 10 | zhaobin   |
|  8 | zhaokun   |
+----+-----------+
5 rows in set (0.00 sec)

查询名字中包含an的学生信息

mysql> select id,name from info where name regexp 'an';
+----+-----------+
| id | name      |
+----+-----------+
| 11 | nannan    |
|  2 | shangzhen |
|  3 | tangyan   |
+----+-----------+
3 rows in set (0.00 sec)

查询名字是tangy开头，n结尾，中间不知道是一个什么字符的学生信息

mysql> select id,name from info where name regexp 'tangy.n';
+----+---------+
| id | name    |
+----+---------+
|  3 | tangyan |
+----+---------+
1 row in set (0.00 sec)

查询名字中包含an或者zh的学生信息

mysql> select id,name from info where name regexp 'an|zh';
+----+-----------+
| id | name      |
+----+-----------+
| 11 | nannan    |
|  2 | shangzhen |
|  3 | tangyan   |
| 10 | zhaobin   |
|  8 | zhaokun   |
+----+-----------+
5 rows in set (0.00 sec)

查询名字中有an，g可有可无的学生信息

mysql> select id,name from info where name regexp 'ang*';
+----+-----------+
| id | name      |
+----+-----------+
| 11 | nannan    |
|  2 | shangzhen |
|  3 | tangyan   |
+----+-----------+
3 rows in set (0.01 sec)

查询名字中含有an，g至少出现一次的学生信息

mysql> select id,name from info where name regexp 'ang+';
+----+-----------+
| id | name      |
+----+-----------+
|  2 | shangzhen |
|  3 | tangyan   |
+----+-----------+
2 rows in set (0.00 sec)

查询名字以s-x开头的学生信息

mysql> select id,name from info where name regexp '^[s-x]';
+----+-----------+
| id | name      |
+----+-----------+
|  2 | shangzhen |
|  1 | shidapeng |
|  3 | tangyan   |
|  9 | xiawenjie |
+----+-----------+
4 rows in set (0.00 sec)

查询名字不是caicai的学生信息

mysql> select id,name from info where name regexp '[^caicai]';
+----+-----------+
| id | name      |
+----+-----------+
|  6 | chengu    |
| 11 | nannan    |
|  2 | shangzhen |
|  1 | shidapeng |
|  3 | tangyan   |
|  9 | xiawenjie |
| 10 | zhaobin   |
|  8 | zhaokun   |
+----+-----------+
8 rows in set (0.00 sec)

查询学生名字不以czx各字母开头的学生信息

mysql> select id,name from info where name regexp '^[^czx]';
+----+-----------+
| id | name      |
+----+-----------+
| 11 | nannan    |
|  2 | shangzhen |
|  1 | shidapeng |
|  3 | tangyan   |
+----+-----------+
4 rows in set (0.00 sec)

二、运算符

MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符

1、算术运算符

以 SELECT 命令来实现最基础的加减乘除运算，MySQL 支持使用的算术运算符，如表所示：

运算符	描述
+	加法
-	减法
*	乘法
/	除法
%	取余

在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。需要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算符没有先后顺序。

mysql> select 1+2,2-1,3*4,4/2,5%2;
+-----+-----+-----+--------+------+
| 1+2 | 2-1 | 3*4 | 4/2    | 5%2  |
+-----+-----+-----+--------+------+
|   3 |   1 |  12 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

2、比较运算符

比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出表中有哪些记录是符合条件的，如果比较的结果为真则返回 1，如果为假则返回 0，比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的，如果要区分大小写可以通过 binary关键字来实现

运算符	描述
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
!=或<>	不等于
is null	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
BETWEEN AND	两者之间
IN	在集合中
LIKE	通配符匹配
GREATEST	两个或多个参数时返回最大值
LEAST	两个或多个参数时返回最小值
REGEXP	正则表达式

等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同

mysql> select 2=4,2=2,2='2','e'='e','r'=null;
+-----+-----+-------+---------+----------+
| 2=4 | 2=2 | 2='2' | 'e'='e' | 'r'=null |
+-----+-----+-------+---------+----------+
|   0 |   1 |     1 |       1 |     NULL |
+-----+-----+-------+---------+----------+
1 row in set (0.00 sec)

如果两者都是整数，则按照整数值进行比较。
如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。
如果两者都是字符串，则按照字符串进行比较。
如果两者中至少有一个值是 NULL，则比较的结果是 NULL。

不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL

mysql> select 'abc'<>'cba',2<>2,3!=2,null<>null;
+--------------+------+------+------------+
| 'abc'<>'cba' | 2<>2 | 3!=2 | null<>null |
+--------------+------+------+------------+
|            1 |    0 |    1 |       NULL |
+--------------+------+------+------------+
1 row in set (0.00 sec)

大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL。
小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL。
大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL。
小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL

mysql> select 5>4,3<4,'a'<'b',4.4<5,'u'>=null;
+-----+-----+---------+-------+-----------+
| 5>4 | 3<4 | 'a'<'b' | 4.4<5 | 'u'>=null |
+-----+-----+---------+-------+-----------+
|   1 |   1 |       1 |     1 |      NULL |
+-----+-----+---------+-------+-----------+
1 row in set (0.00 sec)

mysql> select 2 is null,'a' is not null,null is null;
+-----------+-----------------+--------------+
| 2 is null | 'a' is not null | null is null |
+-----------+-----------------+--------------+
|         0 |               1 |            1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)

BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间

mysql> select 4 between 2 and 5,'c' between 'a' and 'b';
+-------------------+-------------------------+
| 4 between 2 and 5 | 'c' between 'a' and 'b' |
+-------------------+-------------------------+
|                 1 |                       0 |
+-------------------+-------------------------+
1 row in set (0.00 sec)

LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。
GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为NULL，则返回结果就为 NULL。

若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和 GREATEST 来实现

mysql> mysql> select least(1,2,3),greatest(1,2,3),least('a','b','c'),greatest('a','b','c');
+--------------+-----------------+--------------------+-----------------------+
| least(1,2,3) | greatest(1,2,3) | least('a','b','c') | greatest('a','b','c') |
+--------------+-----------------+--------------------+-----------------------+
|            1 |               3 | a                  | c                     |
+--------------+-----------------+--------------------+-----------------------+
1 row in set (0.01 sec)

IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。
NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0

mysql> select 1 in (1,2,3), 2 not in ('a','b','c');
+--------------+------------------------+
| 1 in (1,2,3) | 2 not in ('a','b','c') |
+--------------+------------------------+
|            1 |                      1 |
+--------------+------------------------+
1 row in set, 3 warnings (0.00 sec)

LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0。LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符，而’_’只能匹配一个字符。NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0。

mysql> select 'abc' like 'ab%','abc' like '_bc','abc' not like 'a%';
+------------------+------------------+---------------------+
| 'abc' like 'ab%' | 'abc' like '_bc' | 'abc' not like 'a%' |
+------------------+------------------+---------------------+
|                1 |                1 |                   0 |
+------------------+------------------+---------------------+
1 row in set (0.00 sec)

3、逻辑运算符

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则返回 0，真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种，具体如表所示。

运算符	描述
not 或 !	逻辑非
and 或 &&	逻辑与
or 或 \|\|	逻辑或
xor	逻辑异或

（1）逻辑非
逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL

mysql> select not 2,!3,not 0;
+-------+----+-------+
| not 2 | !3 | not 0 |
+-------+----+-------+
|     0 |  0 |     1 |
+-------+----+-------+
1 row in set (0.00 sec)

（2）逻辑与
逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示

mysql> select 2 and 3,4 && 0,0 && null,1 and null;
+---------+--------+-----------+------------+
| 2 and 3 | 4 && 0 | 0 && null | 1 and null |
+---------+--------+-----------+------------+
|       1 |      0 |         0 |       NULL |
+---------+--------+-----------+------------+
1 row in set (0.00 sec)

（3）逻辑或
逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回 0。逻辑或通常使用 OR 或者||来表示

mysql> select 2 or 3,4 or 0,0 or null,1 || null;
+--------+--------+-----------+-----------+
| 2 or 3 | 4 or 0 | 0 or null | 1 || null |
+--------+--------+-----------+-----------+
|      1 |      1 |      NULL | NULL      |
+--------+--------+-----------+-----------+
1 row in set (0.00 sec)

（4）逻辑异或
两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0，另一个为非 0，则返回结果为 1；当任意一个值为 NULL 时，返回值为 NULL
mysql> select 2 xor 3,1 xor 0,0 xor null,null xor null;

+---------+---------+------------+---------------+
| 2 xor 3 | 1 xor 0 | 0 xor null | null xor null |
+---------+---------+------------+---------------+
|       0 |       1 |       NULL |          NULL |
+---------+---------+------------+---------------+
1 row in set (0.00 sec)

4、位运算符

位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。MySQL 支持 6 种位运算符，具体如表所示。

运算符	描述
&	按位与
\|	按位或
~	按位取反
^	按位异或
<<	按位左移
>>	按位右移

mysql> select 10 & 15,10 | 15,10 ^ 15,5 &~1;
+---------+---------+---------+-------+
| 10 & 15 | 10 | 15 | 10 ^ 15 | 5 &~1 |
+---------+---------+---------+-------+
|      10 |      15 |       5 |     4 |
+---------+---------+---------+-------+
1 row in set (0.00 sec)

10 转换为二进制数是 1010, 15 转换为二进制数是 1111
按位与运算（&），是对应的二进制位都是 1 的，它们的运算结果为 1，否则为 0，所以 10 & 15 的结果为 10。
按位或运算（|），是对应的二进制位有一个或两个为 1 的，运算结果为 1，否则为 0，所以 10 | 15 的结果为 15。
按位异或运算（^），是对应的二进制位不相同时，运算结果 1，否则为 0，所以 10 ^ 15 的结果为 5。
按位取反（~），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001，取反后变为 1110, 数字 5 的二进制是 0101，将 1110 和 0101
第 30 页共 44 页进行求与操作，其结果是二进制的 0100，转换为十进制就是 4

mysql> select 1<<2,2<<2,10>>2,15>>2;
+------+------+-------+-------+
| 1<<2 | 2<<2 | 10>>2 | 15>>2 |
+------+------+-------+-------+
|    4 |    8 |     2 |     3 |
+------+------+-------+-------+
1 row in set (0.00 sec)

左移或右移运算符，都是将数转换为二进制后，然后在左移或右移指定的位数，超出的位数将被移除并丢弃，空出来的位置用 0 补齐。例如，“2<<2”将数字 2 的二进制数 0010，向左移动两位后变成 10，右侧用 00 补齐，最终变为二进制的 1000，转换为十进制是 8。 “15>>2”将数字 15 转换为二进制是 1111，向右移动两位，右侧的两位 11 被丢弃，变为 11，左侧用 00 补齐，最终变为二进制的 0011，转换为十进制就是 3

以上不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算，优先级如下表所示：

优先级	运算符
1	！
2	~
3	^
4	*、/、%
5	+,-
6	>>,<<
7	&
8	\|
9	=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
10	BETWEEN,CASE,WHEN,THEN,ELSE
11	NOT
12	&&,AND
13	\|\|,OR,XOR
14	:=

三、连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接

1、内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表

（1）语法

SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

mysql> select * from infos;
+-----------+-------+---------+
| name      | score | address |
+-----------+-------+---------+
| tangyan   | 88.00 | beijing |
| shangzhen | 80.00 | nanjing |
| shengjie  | 90.00 | nanjing |
+-----------+-------+---------+
3 rows in set (0.00 sec)

mysql> select info.id,info.name from info inner join infos on info.name=infos.name;
+----+-----------+
| id | name      |
+----+-----------+
|  3 | tangyan   |
|  2 | shangzhen |
+----+-----------+
2 rows in set (0.00 sec)

2、左连接

左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。

mysql> select * from info left join infos on info.name=infos.name;
+----+-----------+-------+----------+-------+-----------+-------+---------+
| id | name      | score | address  | hobby | name      | score | address |
+----+-----------+-------+----------+-------+-----------+-------+---------+
|  3 | tangyan   | 98.00 | shanghai |     1 | tangyan   | 88.00 | beijing |
|  2 | shangzhen | 80.00 | beijing  |     1 | shangzhen | 80.00 | nanjing |
|  1 | shidapeng | 90.00 | nanjing  |     2 | NULL      |  NULL | NULL    |
|  6 | chengu    | 88.00 | nanjing  |     1 | NULL      |  NULL | NULL    |
|  7 | caicai    | 70.00 | hangzhou |     1 | NULL      |  NULL | NULL    |
|  8 | zhaokun   | 80.00 | hangzhou |     2 | NULL      |  NULL | NULL    |
|  9 | xiawenjie | 80.00 | hangzhou |     1 | NULL      |  NULL | NULL    |
| 10 | zhaobin   |  NULL | shanghai |     1 | NULL      |  NULL | NULL    |
| 11 | nannan    | 76.00 |          |     2 | NULL      |  NULL | NULL    |
+----+-----------+-------+----------+-------+-----------+-------+---------+
9 rows in set (0.00 sec)

左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为 NULL。

3、右连接

右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配

mysql> select * from info right join infos on info.name=infos.name;
+------+-----------+-------+----------+-------+-----------+-------+---------+
| id   | name      | score | address  | hobby | name      | score | address |
+------+-----------+-------+----------+-------+-----------+-------+---------+
|    3 | tangyan   | 98.00 | shanghai |     1 | tangyan   | 88.00 | beijing |
|    2 | shangzhen | 80.00 | beijing  |     1 | shangzhen | 80.00 | nanjing |
| NULL | NULL      |  NULL | NULL     |  NULL | shengjie  | 90.00 | nanjing |
+------+-----------+-------+----------+-------+-----------+-------+---------+
3 rows in set (0.01 sec)

在右连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹配的行，这些记录在左表中以 NULL 补足

把倒立的世界腾空

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
2
评论
MYSQL 5.7 高级SQL语句（2）——正则表达式、运算符、连接查询

高级SQL语句一、正则表达式二、运算符1、算术运算符2、比较运算符3、逻辑运算符4、位运算符三、连接查询1、内连接（1）语法2、左连接3、右连接一、正则表达式MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式，REGEXP 操作符所支持的匹配模式如表所示。匹配描述^匹配文本的开始字符$匹配文本的结束字符.匹配任何单个字符*
复制链接

扫一扫