[大数据]MySQL（八）高级SQL语句中

目录

一、视图

1、视图定义

2、视图创建、查询、修改应用

二、null值与空值

三、mysql中正则表达式的运用

四、运算符

1、算术运算符

2、比较运算符

3、逻辑运算符(布尔值)

4、位运算符

5、运算符优先级

总结

一、视图

1、视图定义

?????? 视图是从一个或多个表中导出来的表，是一种虚拟存在的表，这张虚拟表是真实数据的一个动

态映射，包含真实数据，只是做了映射。这张虚拟表能动态的保存结果集，因为视图和真实表之间

是动态同步的关系，所以我们修改虚拟表的同时，真实数据也会收到影响。但是视图除了名字什么

都没有，是一个投影，所以不占空间。我们只需要对其做权限的设置，就可保证其安全性，我们可

以为视图定义展示的条件，为不同的人群展示不同的网内容。

????????视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用看到

整个数据库中的数据，而之关心对自己有用的数据。数据库中只存放了视图的定义，而没有存放视

图中的数据，这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出

对应的数据。视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据

也会发生改变。视图能够保障数据安全性，提高查询效率。

视图的作用

①、使操作简单化，可以对经常使用的查询定义一个视图，使用户不必为同样的查询操作指定条件。

②、增加数据的安全性，通过视图，用户只能查询和修改指定的数据。

③、提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。

2、视图创建、查询、修改应用

?

?

二、null值与空值

????????在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失 的

值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使 用 NOT

NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有

NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意 的是，NULL值与数

字 0 或者空白（spaces）的字段是不同的，值为NULL的字段是没有值的。在SQL语句中，使用 IS

NULL 可以判断表内的某个字段是不是NULL值，相反的用IS NOT NULL可以判断不是NULL值。

NULL和空值的区别：

?空值长度为0，不占空间；

?NULL值的长度为null，占用空间；

?is null 无法判断空值；

?空值使用“=”或者“<>”来处理；

?count()计算时，NULL会被忽略，空值会加入计算。

mysql> select * from tt3;   #现阶段使用的表
+----+----------+-------+------------+-------+------+
| id | name     | score | address    | hobby | addr |
+----+----------+-------+------------+-------+------+
|  1 | liuyi    | 80.00 | beijing    | 2     | NULL |
|  2 | wangwu   | 90.00 | shengzheng | 2     | NULL |
|  4 | tianqi   | 99.00 | hangzhou   | 5     | NULL |
|  5 | jiaoshou | 98.00 | laowo      | 3     | NULL |
+----+----------+-------+------------+-------+------+
4 rows in set (0.00 sec)

mysql> update tt3 set addr='' where id=4;  #将id=4中addr的值改为空值
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update tt3 set addr='0' where id=1;  #将id=1中addr的值改为0
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from tt3;    #修改后的tt3表
+----+----------+-------+------------+-------+------+
| id | name     | score | address    | hobby | addr |
+----+----------+-------+------------+-------+------+
|  1 | liuyi    | 80.00 | beijing    | 2     | 0    |
|  2 | wangwu   | 90.00 | shengzheng | 2     | NULL |
|  4 | tianqi   | 99.00 | hangzhou   | 5     |      |
|  5 | jiaoshou | 98.00 | laowo      | 3     | NULL |
+----+----------+-------+------------+-------+------+
4 rows in set (0.00 sec)

mysql> select count(addr) from tt3;   #统计数量：检测null和空值是否会加入统计中
+-------------+
| count(addr) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from tt3 where addr is null;  #查询null值的记录
+----+----------+-------+------------+-------+------+
| id | name     | score | address    | hobby | addr |
+----+----------+-------+------------+-------+------+
|  2 | wangwu   | 90.00 | shengzheng | 2     | NULL |
|  5 | jiaoshou | 98.00 | laowo      | 3     | NULL |
+----+----------+-------+------------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from tt3 where addr is not null;   #查询不为空的值的记录
+----+--------+-------+----------+-------+------+
| id | name   | score | address  | hobby | addr |
+----+--------+-------+----------+-------+------+
|  1 | liuyi  | 80.00 | beijing  | 2     | 0    |
|  4 | tianqi | 99.00 | hangzhou | 5     |      |
+----+--------+-------+----------+-------+------+
2 rows in set (0.00 sec)

mysql>

?

三、mysql中正则表达式的运用

?????? MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要

求的特殊字符串。MySQL的正则表达式使用REGEXP这个关键字来指定正则表达式的匹配模式，

REGEXP 操作符所支持的匹配模式如表所示。

^匹配开始字符

p1|p2匹配p1或p2

$匹配结束字符

[…]匹配字符集中的任意一个字符

.匹配任意单个字符

[^…]匹配不在中括号内的任何字符

*匹配任意个前面的字符

{n}匹配前面的字符串n次

＋匹配前面字符至少1次

{n,m} 匹配前面的字符串至少n次,至多m次

#查询以sh开头的学生信息
select id,name from info where name regexp '^sh';

#查询以n结尾的学生信息
select id,name from info where name regexp 'n$';

#查询名字中包含an的学生信息
select id,name from info where name regexp 'an';

#查询名字是tangy开头，n结尾，中间不知道是一个什么字符的学生信息
select id,name from info where name regexp 'tangy.n';

#查询名字中包含an或者zh的学生信息
select id,name from info where name regexp 'an|zh';

#查询名字中含有an，g至少出现一次的学生信息
select id,name from info where name regexp 'ang+';

#查询名字以s-x开头的学生信息
select id,name from info where name regexp '^[s-x]';

#查询名字不是caicai的学生信息
select id,name from info where name regexp '[^caicai]';

#查询学生名字不以czx各字母开头的学生信息
select id,name from info where name regexp '^[^czx]';

四、运算符

????????MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算

术运算符、比较运算符、逻辑运算符和位运算符。

1、算术运算符

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

mysql> select 2+3,4-1,3*4,6/2,5%3;     #算术运算符运算符
+-----+-----+-----+--------+------+
| 2+3 | 4-1 | 3*4 | 6/2    | 5%3  |
+-----+-----+-----+--------+------+
|   5 |   3 |  12 | 3.0000 |    2 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

mysql> select 2+4-3*4;   #运算时按优先级
+---------+
| 2+4-3*4 |
+---------+
|      -6 |
+---------+
1 row in set (0.00 sec)

mysql> create table mmt as select 1+2,2-1,3*2,4/2,5%2;  #可以将运算的结果导入到新建的表中
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from mmt;    #查看运算结果
+-----+-----+-----+--------+------+
| 1+2 | 2-1 | 3*2 | 4/2    | 5%2  |
+-----+-----+-----+--------+------+
|   3 |   1 |   6 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

mysql> desc mmt;   #查看表结构
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| 1+2   | int(3)       | NO   |     | 0       |       |
| 2-1   | int(3)       | NO   |     | 0       |       |
| 3*2   | int(3)       | NO   |     | 0       |       |
| 4/2   | decimal(5,4) | YES  |     | NULL    |       |
| 5%2   | int(1)       | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>

2、比较运算符

????????比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出表中

有哪些记录是符合条件的，如果比较的结果（以布尔值的方式进行返回判断）为真则返回 1，如果

为假则返回 0，比较的结果如果不确定则返回NULL。其中字符串在进行比较的时候默认是不区分

大小写的，如果要区分大小写可以通过binary关键字来实现。

????????①? #等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1(true)，如

果不相等则返回0（flase)。如果比较的两者有一个值是NULL，则比较的结果就是NULL。其中字符

的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相

等，则表示两个字符不相同。

◆? 如果两者都是整数，则按照整数值进行比较。

◆ 如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。（在程序中，一般是不

会吧这两者进行相比较的）。

◆ 如果两者都是字符串，则按照字符串进行比较。

◆ 如果两者中至少有一个值是 NULL，则比较的结果是 NULL。

????????②? #不等于（<>，!=）不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表

达式不相等的比较。 如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需

要注意的是不等于运算符不能用于判断 NULL。

?

??????? ③? #大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返

回0，同样不能用于判断NULL。小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，

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

等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断NULL。小于等于（<=）判断

左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断

NULL。

??????? ④? #判断一个值为/不为null ( IS NULL、IS NOT NULL )

#IS NULL判断一个值是否为 NULL，如果为NULL返回1，否则返回0。

#IS NOT NOLL判断一个值是否不为 NULL，如果不为NULL返回1，否则返回 0。

??????? ⑤? #BETWEEN AND比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数

字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间，具体操作，条件符

合返回1，否则返回0。

?

注：Between and 覆盖的范围是>= 和 <=关系。

?????? ⑥? #least 和greatest（取最小值、取最大值）

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

为 NULL。

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

结果就为 NULL。

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

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

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

??????? ⑧? #LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0

LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符（*匹配的是前面一个字符），而；’_’只能匹

配一个字符。

NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0。

3、逻辑运算符(布尔值)

????????逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则返回

0，真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种。

①、逻辑非

????????逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面

的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果

操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL。

②、逻辑与

当所有的操作数都为非0值且不为null时，返回值为1，否则为0。

（null与0比较特殊）逻辑与使用 AND 或者&&表示。

?由结果可看出and 和&& 的作用相同，

1 and -1 没有0 或 null，所以返回值为1

1 and 0 中由有0，所以返回值为0

1 and null 有Null，所以返回值为null

null and 0 返回值为0

③、逻辑或（or）

????????逻辑或通常使用 OR ，逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL值时，返

回 1，否则返回 0。当有一个操作数为null时，如果另一个操作数为非0值，则返回值为1，否则为

null。如两个操作数均为null，则返回值为null。

?

④、逻辑异或

????????两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0， 另一

个为非 0，则返回结果为 1；当任意一个值为 NULL 时，返回值为 NULL。

4、位运算符

????????位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制

格式（1010 1111），然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户

查看。MySQL 支持 6 种位运算符。

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进行求与操作，其结

果是二进制的 0100，转换为十进制就是 4。

5、运算符优先级

????????不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运 算符在计

算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺

序从左到右依次进行计算，优先级如下表所示。

总结

?