文章目录
MySQL进阶查询
1.1 按关键字排序
- 使用ORDER BY语句来实现排序
- 排序可针对一个或多个字段
- ASC:升序，默认排序方式
- DESC:降序
- ORDER BY的语法结构
select column1，column2，...from table_name order by column1，column2，...asc|desc;
1.1.1 按单字段排序
mysql> select * from mytable;
+----+----------+-----+-------+
| id | name | age | hobby |
+----+----------+-----+-------+
| 1 | zhangsan | 30 | 1 |
| 2 | lisi | 40 | 2 |
| 3 | wangwu | 30 | 1 |
| 4 | zhaoliu | 40 | 2 |
| 5 | qiqi | 50 | 2 |
| 6 | tom | 22 | 3 |
+----+----------+-----+-------+
6 rows in set (0.00 sec)
mysql> select name,age from mytable where age>=30 order by age;
+----------+-----+
| name | age |
+----------+-----+
| zhangsan | 30 |
| wangwu | 30 |
| lisi | 40 |
| zhaoliu | 40 |
| qiqi | 50 |
+----------+-----+
5 rows in set (0.00 sec)
mysql> select name,age from mytable where age>=30 order by age desc;
+----------+-----+
| name | age |
+----------+-----+
| qiqi | 50 |
| lisi | 40 |
| zhaoliu | 40 |
| zhangsan | 30 |
| wangwu | 30 |
+----------+-----+
5 rows in set (0.00 sec)
1.1.2 按多字段排序
mysql> select id,name,age from mytable where age>=30 order by age desc ,id desc;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 5 | qiqi | 50 |
| 4 | zhaoliu | 40 |
| 2 | lisi | 40 |
| 3 | wangwu | 30 |
| 1 | zhangsan | 30 |
+----+----------+-----+
5 rows in set (0.00 sec)
1.2 对结果进行分组
- 使用GROUP BY语句来实现分组
- GROUP BY通常都是结合聚合函数一起使用的，常用的聚合函数包括:计数（COUNT)、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN）。
- GROUP BY分组的时候可以按一个或多个字段对结果进行分组
- GROUP BY的语法结构
SELECT column_name,aggregate_function(column_name)FROM table_name
WHERE column_name operator value GROUP BY column_name;
mysql> select avg(age) from mytable;
+--------------------+
| avg(age) |
+--------------------+
| 35.333333333333336 |
+--------------------+
1 row in set (0.00 sec)
mysql> select count(name),age from mytable where age>=30 group by age;
+-------------+-----+
| count(name) | age |
+-------------+-----+
| 2 | 30 |
| 2 | 40 |
| 1 | 50 |
+-------------+-----+
3 rows in set (0.00 sec)
mysql> select count(name),age from mytable where age>=30 group by age order by age desc;
+-------------+-----+
| count(name) | age |
+-------------+-----+
| 1 | 50 |
| 2 | 40 |
| 2 | 30 |
+-------------+-----+
3 rows in set (0.00 sec)
1.3 限制结果条目
- 只返回SELECT查询结果的第一行或前几行
- 使用LIMIT语句限制条目
- LIMIT语法结构
SELECT column1,column2,..FROM table_name LIMIT [offset,] number;
[offset,]代表的的是位置偏移量，从0开始
number是返回记录行的最大数目
mysql> select id,name,age from mytable limit 3;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 30 |
| 2 | lisi | 40 |
| 3 | wangwu | 30 |
+----+----------+-----+
3 rows in set (0.00 sec)
mysql> select id,name,age from mytable limit 2,3; '从第3条记录开始显示之后的3条数据'
+----+---------+-----+
| id | name | age |
+----+---------+-----+
| 3 | wangwu | 30 |
| 4 | zhaoliu | 40 |
| 5 | qiqi | 50 |
+----+---------+-----+
3 rows in set (0.00 sec)
mysql> select id,name,age from mytable order by age desc limit 3; '排序与分组结合使用'
+----+---------+-----+
| id | name | age |
+----+---------+-----+
| 5 | qiqi | 50 |
| 2 | lisi | 40 |
| 4 | zhaoliu | 40 |
+----+---------+-----+
3 rows in set (0.00 sec)
1.4 设置别名
- 使用AS语句设置别名，关键字AS可省略
- 设置别名时，保证不能与库中其他表或字段名称冲突
- 别名的语法结构
SELECT column_name AS alias_name FROM table_name;
SELECT column_name(s) FROM table_name AS alias_name;
mysql> select count(*) from mytable;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) as 人数 from mytable;
+--------+
| 人数 |
+--------+
| 6 |
+--------+
1 row in set (0.00 sec)
mysql> select * from info; '新建一个info表'
+----+-----------+
| id | hob_name |
+----+-----------+
| 1 | 篮球 |
| 2 | 足球 |
| 3 | 乒乓球 |
+----+-----------+
3 rows in set (0.00 sec)
mysql> select * from mytable inner join info on mytable.hobby=info.id; '将mytable表与info表内连接'
+----+----------+-----+-------+----+-----------+
| id | name | age | hobby | id | hob_name |
+----+----------+-----+-------+----+-----------+
| 1 | zhangsan | 30 | 1 | 1 | 篮球 |
| 2 | lisi | 40 | 2 | 2 | 足球 |
| 3 | wangwu | 30 | 1 | 1 | 篮球 |
| 4 | zhaoliu | 40 | 2 | 2 | 足球 |
| 5 | qiqi | 50 | 2 | 2 | 足球 |
| 6 | tom | 22 | 3 | 3 | 乒乓球 |
+----+----------+-----+-------+----+-----------+
6 rows in set (0.00 sec)
mysql> select mytable.name,info.hob_name from mytable inner join info on mytable.hobby=info.id;
+----------+-----------+
| name | hob_name |
+----------+-----------+
| zhangsan | 篮球 |
| lisi | 足球 |
| wangwu | 篮球 |
| zhaoliu | 足球 |
| qiqi | 足球 |
| tom | 乒乓球 |
+----------+-----------+
6 rows in set (0.00 sec)
mysql> select m.name,i.hob_name from mytable as m inner join info as i on m.hobby=i.id;
+----------+-----------+
| name | hob_name |
+----------+-----------+
| zhangsan | 篮球 |
| lisi | 足球 |
| wangwu | 篮球 |
| zhaoliu | 足球 |
| qiqi | 足球 |
| tom | 乒乓球 |
+----------+-----------+
6 rows in set (0.00 sec)
mysql> select m.name 姓名,i.hob_name 兴趣名称 from mytable m inner join info i on m.hobby=i.id;
+----------+--------------+
| 姓名 | 兴趣名称 |
+----------+--------------+
| zhangsan | 篮球 |
| lisi | 足球 |
| wangwu | 篮球 |
| zhaoliu | 足球 |
| qiqi | 足球 |
| tom | 乒乓球 |
+----------+--------------+
6 rows in set (0.00 sec)
- AS的用法
- AS作为连接语句
mysql> create table newinfo as select * from info;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from newinfo;
+----+-----------+
| id | hob_name |
+----+-----------+
| 1 | 篮球 |
| 2 | 足球 |
| 3 | 乒乓球 |
+----+-----------+
3 rows in set (0.00 sec)
1.5 通配符
- 用于替换字符串中的部分字符
- 通常配合LIKE一起使用，并协同WHERE完成查询
- 常用通配符
- %表示零个、一个或多个
- _表示单个字符
1.5.1 通配符%的用法
mysql> select id,name,age from mytable where name like 'z%';
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 30 |
| 4 | zhaoliu | 40 |
+----+----------+-----+
2 rows in set (0.00 sec)
1.5.2 通配符_的用法
mysql> select id,name,age from mytable where name like 'z_______';
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 30 |
+----+----------+-----+
1 row in set (0.00 sec)
1.5.3 通配符%和_结合使用
mysql> select id,name,age from mytable where name like '_an%';
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 3 | wangwu | 30 |
+----+--------+-----+
1 row in set (0.00 sec)
1.6 子查询
- 也称作内查询或者嵌套查询
- 先于主查询被执行，其结果将作为外层主查询的条件
- 在增删改查中都可以使用子查询
- 支持多层嵌套
- IN语句是用来判断某个值是否在给定的结果集中
- 括号内的为子语句，优先被执行，执行出来的结果作为主语句的条件
1.6.1 select子查询的用法
mysql> select name,hobby from mytable where hobby in (select id from info where hob_name='足球');
+---------+-------+
| name | hobby |
+---------+-------+
| lisi | 2 |
| zhaoliu | 2 |
| qiqi | 2 |
+---------+-------+
3 rows in set (0.00 sec)
mysql> select name,hobby from mytable where hobby=(select id from info where hob__name='足球');
+---------+-------+
| name | hobby |
+---------+-------+
| lisi | 2 |
| zhaoliu | 2 |
| qiqi | 2 |
+---------+-------+
3 rows in set (0.01 sec)
mysql> select name,hobby from mytable where hobby!=(select id from info where hobb_name='足球');
+----------+-------+
| name | hobby |
+----------+-------+
| zhangsan | 1 |
| wangwu | 1 |
| tom | 3 |
+----------+-------+
3 rows in set (0.00 sec)
mysql> select name,hobby from mytable where hobby<>(select id from info where hobb_name='足球');
+----------+-------+
| name | hobby |
+----------+-------+
| zhangsan | 1 |
| wangwu | 1 |
| tom | 3 |
+----------+-------+
3 rows in set (0.00 sec)
mysql> select name,hobby from mytable where hobby in (select id from info);
+----------+-------+
| name | hobby |
+----------+-------+
| zhangsan | 1 |
| lisi | 2 |
| wangwu | 1 |
| zhaoliu | 2 |
| qiqi | 2 |
| tom | 3 |
+----------+-------+
6 rows in set (0.00 sec)
1.6.2 insert子查询的用法
mysql> create table new like mytable;
Query OK, 0 rows affected (0.01 sec)
mysql> desc new;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | varchar(10) | NO | | NULL | |
| hobby | int(3) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into new select * from mytable where id in (select id from mytable);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from new;
+----+----------+-----+-------+
| id | name | age | hobby |
+----+----------+-----+-------+
| 1 | zhangsan | 30 | 1 |
| 2 | lisi | 40 | 2 |
| 3 | wangwu | 30 | 1 |
| 4 | zhaoliu | 40 | 2 |
| 5 | qiqi | 50 | 2 |
| 6 | tom | 27 | 3 |
+----+----------+-----+-------+
6 rows in set (0.00 sec)
1.6.3 update子查询的用法
mysql> update mytable set age=age+5 where age<30;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from mytable;
+----+----------+-----+-------+
| id | name | age | hobby |
+----+----------+-----+-------+
| 1 | zhangsan | 30 | 1 |
| 2 | lisi | 40 | 2 |
| 3 | wangwu | 30 | 1 |
| 4 | zhaoliu | 40 | 2 |
| 5 | qiqi | 50 | 2 |
| 6 | tom | 27 | 3 |
+----+----------+-----+-------+
6 rows in set (0.00 sec)
1.6.4 delete子查询的用法
mysql> delete from new where id in (select a.id from (select id from new where age=30)a);
Query OK, 2 rows affected (0.01 sec)
mysql> select * from new;
+----+---------+-----+-------+
| id | name | age | hobby |
+----+---------+-----+-------+
| 2 | lisi | 40 | 2 |
| 4 | zhaoliu | 40 | 2 |
| 5 | qiqi | 50 | 2 |
| 6 | tom | 27 | 3 |
+----+---------+-----+-------+
4 rows in set (0.00 sec)
1.6.5 exists子查询的用法
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。和shell中的if有点相似。
exists只能执行select语句
mysql> select name,age from mytable where exists (select id from info where hob_name='篮球');
+----------+-----+
| name | age |
+----------+-----+
| zhangsan | 30 |
| lisi | 40 |
| wangwu | 30 |
| zhaoliu | 40 |
| qiqi | 50 |
| tom | 27 |
+----------+-----+
6 rows in set (0.00 sec)
mysql> select name,age from mytable where exists (select id from info whename='游泳');
Empty set (0.00 sec)
1.7 NULL值
在 SQL 语句使用过程中，经常会碰到NULL 这几个字符。通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使用NOT NULL关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有NOT NULL并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意的是，NULL值与数字0或者空白（spaces）的字段是不同的，值为 NULL的字段是没有值的。在SQL语句中，使用IS NULL可以判断表内的某个字段是不是NULL值，相反的用IS NOT NULL可以判断不是NULL值。
mysql> create table tmp (id int(3) not null primary key auto_increment,name varchar(50));
Query OK, 0 rows affected (0.01 sec)
mysql> desc tmp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into tmp values (1,'zhangsan');
mysql> insert into tmp (id) values (2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tmp;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | NULL |
+----+----------+
2 rows in set (0.00 sec)
mysql> select * from tmp where name is null;
+----+------+
| id | name |
+----+------+
| 2 | NULL |
+----+------+
1 row in set (0.00 sec)
mysql> select * from tmp where name is not null;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
提到 NULL 值就不得不说下空值，空值就是在表的字段中存储空字符（’’），那么 NULL
值和空值有什么区别呢？二者的区别如下：
- 空值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的
- IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是空值的
- 空值的判断使用=’’或者<>’’来处理
- 在通过 count()计算有多少记录数时，如果遇到 NULL 值会自动忽略掉，遇到空值会加入到记录中进行计算
1.8 正则表达式
MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL 的正则表达式使用REGEXP这个关键字来指定正则表达式的匹配模式，REGEXP操作符所支持的匹配模式如表所示。
| 匹配模式 | 描述 | 实例 |
|---|---|---|
| ^ | 匹配文本的开始字符 | 'qn$’匹配以qn结尾的字符串 |
| $ | 匹配文本的结束字符 | 'qn$’匹配以qn结尾的字符串 |
| . | 匹配任何单个字符 | 's.t’匹配任何s和t之间有一个字符的字符串 |
| * | 匹配零个或多个在它前面的字符 | fo*t’匹配t前面有任意个o |
| + | 匹配前面的字符1次或多次 | 'hom+’匹配以ho开头，后面至少—个m的字符串 |
| 字符串 | 匹配包含指定的字符串 | 'clo’匹配含有clo的字符串 |
| p1lp2 | 匹配p1或p2 | 'bglfg’匹配 bg或者fg |
| […] | 匹配字符集合中的任意一个字符 | [abc]匹配a或者b或者c |
| [^…] | 匹配不在括号中的任何字符 | [ab]'匹配不包含a或者b的字符串 |
| {n} | 匹配前面的字符串n次 | g{2}’匹配含有2个g的字符串 |
| {n,m} | 匹配前面的字符串至少n次，至多m次 | 'f{1,3}’匹配f最少1次，最多3次 |
以特定字符串开头的记录
mysql> select id,name,age from mytable where name regexp '^zh';
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 30 |
| 4 | zhaoliu | 40 |
+----+----------+-----+
2 rows in set (0.00 sec)
以特定字符串结尾的记录
mysql> select id,name,age from mytable where name regexp 'u$';
+----+---------+-----+
| id | name | age |
+----+---------+-----+
| 3 | wangwu | 30 |
| 4 | zhaoliu | 40 |
+----+---------+-----+
2 rows in set (0.00 sec)
包含指定字符串的记录
mysql> select id,name,age from mytable where name regexp 'ng';
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 30 |
| 3 | wangwu | 30 |
+----+----------+-----+
2 rows in set (0.00 sec)
以“.”代替字符串中的任意一个字符的记录
mysql> select id,name,age from mytable where name regexp 'wa..wu';
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 3 | wangwu | 30 |
+----+--------+-----+
1 row in set (0.00 sec)
匹配包含或者关系的记录
mysql> select id,name,age from mytable where name regexp 'gs|ol';
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 30 |
| 4 | zhaoliu | 40 |
+----+----------+-----+
2 rows in set (0.01 sec)
“*”匹配前面字符的任意多次
mysql> select * from mytable;
+----+----------+-----+-------+
| id | name | age | hobby |
+----+----------+-----+-------+
| 1 | zhangsan | 30 | 1 |
| 2 | lisi | 40 | 2 |
| 3 | wangwu | 30 | 1 |
| 4 | zhaoliu | 40 | 2 |
| 5 | qiqi | 50 | 2 |
| 6 | tom | 27 | 3 |
| 7 | oooo | 41 | 3 |
| 8 | oooooo | 36 | 1 |
+----+----------+-----+-------+
8 rows in set (0.00 sec)
mysql> select id,name,age from mytable where name regexp 'oo*';
+----+---------+-----+
| id | name | age |
+----+---------+-----+
| 4 | zhaoliu | 40 |
| 6 | tom | 27 |
| 7 | oooo | 41 |
| 8 | oooooo | 36 |
+----+---------+-----+
4 rows in set (0.01 sec)
mysql> select id,name,age from mytable where name regexp 'ooo*';
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 7 | oooo | 41 |
| 8 | oooooo | 36 |
+----+--------+-----+
2 rows in set (0.01 sec)
“+”匹配前面字符至少一次
mysql> select id,name,age from mytable where name regexp 'oo+';
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 7 | oooo | 41 |
| 8 | oooooo | 36 |
+----+--------+-----+
2 rows in set (0.00 sec)
匹配指定字符集中的任意一个
例：在 mytable 表中查询包含以 q、r、s开头的 name 字段并打印对应的 id、name 和
age 记录
mysql> select id,name,age from mytable where name regexp '^[q-s]';
+----+------+-----+
| id | name | age |
+----+------+-----+
| 5 | qiqi | 50 |
+----+------+-----+
1 row in set (0.00 sec)
方括号“[]”指定了一个字符集合，只匹配其中的一个字符。“^”不仅可以放到左侧，也可以放到方括号内，放到左侧表示以这些字符集内的字符开头，而放到方括号内则表示不在指 定的字符集合内的字符
1.9 运算符
MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符。
1.9.1 算术运算符
| 运算符 | 描述 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / | 除法 |
| % | 取余数 |
以 SELECT 命令来实现最基础的加减乘除运算
mysql> select 1+3,5-4,4*3,8/3,14%3;
+-----+-----+-----+--------+------+
| 1+3 | 5-4 | 4*3 | 8/3 | 14%3 |
+-----+-----+-----+--------+------+
| 4 | 1 | 12 | 2.6667 | 2 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)
在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为NULL。需要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算符没有先后顺序。
在 MySQL的字段值内还有一种情况：某些字符串类型的字段存储的数字型字符串，这些字段在进行算术运算时将会被自动转换为数字的值。如果字符串的开始部分是数字，在转换时将被转换为这个数字。如果是既包含字符又包含数字得的混合字符串，无法转换为数字时，将被转换为 0。这些细节在进行算术运算时需多加注意。
1.9.2 比较运算符
比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出 表中有哪些记录是符合条件的，如果比较的结果为真则返回 1，如果为假则返回0，比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的，如果要区分大小写可以通过 binary 关键字来实现。常用的比较运算符如表所示
| 运算符 | 描述 | 运算符 | 描述 |
|---|---|---|---|
| = | 等于 | IS NOT NULL | 判断一个值是否不为NULL |
| > | 大于 | BETWEEN AND | 两者之间 |
| < | 小于 | IN | 在集合中 |
| >= | 大于等于 | LIKE | 通配符匹配 |
| <= | 小于等于 | GREATEST | 两个或多个参数时返回最大值 |
| !=或<> | 不等于 | LEAST | 两个或多个参数时返回最小值 |
| IS NULL | 判断一个值是否为NULL | REGEXP | 正则表达式 |
（1）等于运算符
等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同。
mysql> select 3=5,4='4','e'='e',(4+3)=(2+5),'a'=NULL;
+-----+-------+---------+-------------+----------+
| 3=5 | 4='4' | 'e'='e' | (4+3)=(2+5) | 'a'=NULL |
+-----+-------+---------+-------------+----------+
| 0 | 1 | 1 | 1 | NULL |
+-----+-------+---------+-------------+----------+
1 row in set (0.00 sec)
从以上查询可以看出来：
- 如果两者都是整数，则按照整数值进行比较。
- 如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。
- 如果两者都是字符串，则按照字符串进行比较。
- 如果两者中至少有一个值是 NULL，则比较的结果是 NULL。
（2）不等于运算符
不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL。
mysql> select 'set'<>'qqs',2<>3,5!=5,3.5!=3,NULL<>NULL;
+--------------+------+------+--------+------------+
| 'set'<>'qqs' | 2<>3 | 5!=5 | 3.5!=3 | NULL<>NULL |
+--------------+------+------+--------+------------+
| 1 | 1 | 0 | 1 | NULL |
+--------------+------+------+--------+------------+
1 row in set (0.00 sec)
（3）大于、大于等于、小于、小于等于运算符
- 大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL
- 小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL
- 大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL
- 小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL
mysql> select 6>5,'a'>'b',3>=6,(3+4)>=(2+1),3.3<4,'x'<='y','b'>=NULL;
+-----+---------+------+--------------+-------+----------+-----------+
| 6>5 | 'a'>'b' | 3>=6 | (3+4)>=(2+1) | 3.3<4 | 'x'<='y' | 'b'>=NULL |
+-----+---------+------+--------------+-------+----------+-----------+
| 1 | 0 | 0 | 1 | 1 | 1 | NULL |
+-----+---------+------+--------------+-------+----------+-----------+
1 row in set (0.00 sec)
（4）IS NULL、IS NOT NULL
- IS NULL 判断一个值是否为 NULL，如果为 NULL 返回 1，否则返回 0
- IS NOT NULL 判断一个值是否不为 NULL，如果不为 NULL 返回 1，否则返回 0
mysql> select 2 is null,'d' is not null,null is null;
+-----------+-----------------+--------------+
| 2 is null | 'd' is not null | null is null |
+-----------+-----------------+--------------+
| 0 | 1 | 1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)
IS NULL 和 IS NOT NULL 一个判断为空，另一个判断不为空，只是有无 NOT 这个关键字的区别，同时返回值不同
（5）BETWEEN AND
BETWEEN AND比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间
mysql> select 4 between 1 and 5,6 between 3 and 5,'d' between 'b' and 'f';
+-------------------+-------------------+-------------------------+
| 4 between 1 and 5 | 6 between 3 and 5 | 'd' between 'b' and 'f' |
+-------------------+-------------------+-------------------------+
| 1 | 0 | 1 |
+-------------------+-------------------+-------------------------+
1 row in set (0.00 sec)
（6）LEAST、GREATEST
- LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL
- GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL， 则返回结果就为 NULL
若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和
GREATEST 来实现
mysql> select least(3,6,9),least('a','c','e'),greatest(3,6,9),greatest('a','c','ee');
+--------------+--------------------+-----------------+-----------------------+
| least(3,6,9) | least('a','c','e') | greatest(3,6,9) | greatest('a','c','e') |
+--------------+--------------------+-----------------+-----------------------+
| 3 | a | 9 | e |
+--------------+--------------------+-----------------+-----------------------+
1 row in set (0.00 sec)
从以上结果可以看出，LEAST 比较的参数为数字时，返回的是其中最小的值；当比较的参数为字符串时，返回字母表中顺序最靠前的字符。GREATEST 比较的参数为数字时， 返回的是其中最大的值；当比较的参数为字符串时，返回字母表中顺序最靠后的字符。
（7）IN、NOT IN
- IN 判断一个值是否在对应的列表中，如果是返回1，否则返回0。
- NOT IN 判断一个值是否不在对应的列表中，如果不是返回1，否则返回0
判断某数字是否在一组数字中，也可判断某字符是否在一组字符中
mysql> select 5 in (1,2,3,4,5),'d' not in ('a','b','c','d');
+------------------+------------------------------+
| 5 in (1,2,3,4,5) | 'd' not in ('a','b','c','d') |
+------------------+------------------------------+
| 1 | 0 |
+------------------+------------------------------+
1 row in set (0.00 sec)
从以上结果可以看出，IN 和 NOT IN 的返回值正好相反
（8）LIKE、NOT LIKE
LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回0。LIKE支持两种通配符：’%’ 用于匹配任意数目的字符，而’_’只能匹配一个字符。NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回1，反之返回0。
若要判断某字符串能否匹配成功，分单字符匹配和多字符匹配，也可以判断不匹配
mysql> select 'bgp' like 'ebgp','cfg' like '%g','etc' not like '%th';
+-------------------+-----------------+----------------------+
| 'bgp' like 'ebgp' | 'cfg' like '%g' | 'etc' not like '%th' |
+-------------------+-----------------+----------------------+
| 0 | 1 | 1 |
+-------------------+-----------------+----------------------+
1 row in set (0.00 sec)
1.9.3 逻辑运算符
逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回1，否则返回0，真和假也可以用TRUE和FALSE表示。MySQL中支持使用的逻辑运算符有四种， 具体如表所示
| 运算符 | 描述 |
|---|---|
| NOT或! | 逻辑非 |
| AND 或&& | 逻辑与 |
| OR或Ⅱ | XOR |
| XOR | 逻辑异或 |
（1）逻辑非
逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果NOT后面的操作数为0时，所得值为1；如果操作数为非0时，所得值为0；如果操作数为NULL时，所得值为NULL。
例如，对非 0 值和 0 值分别作逻辑非运算
mysql> select not 2,!3,not 0,!(3-3);
+-------+----+-------+--------+
| not 2 | !3 | not 0 | !(3-3) |
+-------+----+-------+--------+
| 0 | 0 | 1 | 1 |
+-------+----+-------+--------+
1 row in set (0.00 sec)
（2）逻辑与
逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回1，否则返回0。逻辑与使用AND或者&&表示。
例如，对非 0 值、0 值和 NULL 值分别作逻辑与运算
mysql> select 3 and 4, 5 && 0,0 && NULL, 1 and NULL;
+---------+--------+-----------+------------+
| 3 and 4 | 5 && 0 | 0 && NULL | 1 and NULL |
+---------+--------+-----------+------------+
| 1 | 0 | 0 | NULL |
+---------+--------+-----------+------------+
1 row in set (0.00 sec)
（3）逻辑或
逻辑或表示包含的操作数，任意一个为非零值并且不是NULL值时，返回 1，否则返回0。逻辑或通常使用OR或者||来表示。
例如，对非 0 值、0 值和 NULL 值分别作逻辑或运算
mysql> select 2 or 3,4 or 0,0 or NULL,1 or NULL;
+--------+--------+-----------+-----------+
| 2 or 3 | 4 or 0 | 0 or NULL | 1 or NULL |
+--------+--------+-----------+-----------+
| 1 | 1 | NULL | 1 |
+--------+--------+-----------+-----------+
1 row in set (0.00 sec)
（4）逻辑异或
两个非NULL值的操作数，如果两者都是0或者都是非0，则返回0；如果一个为0， 另一个为非0，则返回结果为1；当任意一个值为NULL时，返回值为NULL。
例如，对非0 值、0 值和 NULL 值分别作逻辑异或运算
mysql> select 3 xor 4,0 xor 0,0 xor 5,1 xor NULL,NULL xor NULL;
+---------+---------+---------+------------+---------------+
| 3 xor 4 | 0 xor 0 | 0 xor 5 | 1 xor NULL | NULL xor NULL |
+---------+---------+---------+------------+---------------+
| 0 | 0 | 1 | NULL | NULL |
+---------+---------+---------+------------+---------------+
1 row in set (0.00 sec)
1.9.4 位运算符
位运算符实际上是对二进制数进行计算的运算符。MySQL内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看。MySQL 支持 6 种位运算符，具体如表所示
| 运算符 | 描述 |
|---|---|
| & | 按位与 |
| l | 按位或 |
| ~ | 按位取反 |
| ^ | 按位异或 |
| << | 按位左移 |
| >> | 按位右移 |
例如，对数字进行按位与、或和取反运算
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进行求与操作，其结果是二进制的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 | ! | 8 | l |
| 2 | ~ | 9 | =,<=>,>=,>，<=,<,<>,!=,IS,LIKE,REGEXP,IN |
| 3 | ^ | 10 | BETWEEN, CASE,WHEN,THEN,ELSE |
| 4 | *，/(DIV)，%(MOD) | 11 | NOT |
| 5 | +，- | 12 | &&,AND |
| 6 | >>，<< | 13 | ll,OR,XOR |
| 7 | & | 14 | := |
1.10 连接查询
MySQL的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接。
1.10.1 内连接
MySQL中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在FROM子句中使用关键字INNER JOIN来连接多张表，并使用ON子句设置连接条件。
内连接的语法格式如下：
SELECT column_name(s)FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
内连接是系统默认的表连接，所以在FROM子句后可以省略INNER关键字，只使用关键字JOIN。同时有多个表时，也可以连续使用INNER JOIN来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。例如，在 mytable 和info表中使用内连接查询出通过判断m.hobby 和i.id 相等，包含在两个表内的部分
mysql> select m.id,m.name,i.hob_name from mytable m inner join info i on m.hobby==i.id;
+----+----------+-----------+
| id | name | hob_name |
+----+----------+-----------+
| 1 | zhangsan | 篮球 |
| 2 | lisi | 足球 |
| 3 | wangwu | 篮球 |
| 4 | zhaoliu | 足球 |
| 5 | qiqi | 足球 |
| 6 | tom | 乒乓球 |
| 7 | oooo | 乒乓球 |
| 8 | oooooo | 篮球 |
+----+----------+-----------+
8 rows in set (0.00 sec)
上述操作是将mytable表的每一行与info表的每一行进行比较，以检查它们是否都满足条件。当满足条件时，INNER JOIN 将返回由mytable表和info表中的列组成的新行。如果没有匹配项，查询将返回一个空的结果集。INNER JOIN 结果集中的行必须出现在两个表mytable表和info表中，是这两个表的交叉部分.
具体的 INNER JOIN 工作原理如图所示
如果表中至少有一个匹配项，INNER JOIN 关键字将返回一行。如果没有匹配项，则不会有输出结果。
1.10.2 左连接
MySQL除了内连接，还可以使用外连接。区别于MySQL外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分，有左连接和右连接之分。
左连接也可以被称为左外连接，在FROM子句中使用LEFT JOIN 或者LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。例如，从a_player 和 b_player 表中，查询出a_player 表中所有内容，并且查询出通过m.hobby 和 b_id 相等判断出的i.id中的部分，具体操作如下。
mysql> select * from mytable m left join info i on m.hobby=i.id;
+----+----------+-----+-------+------+-----------+
| id | name | age | hobby | id | hob_name |
+----+----------+-----+-------+------+-----------+
| 1 | zhangsan | 30 | 1 | 1 | 篮球 |
| 3 | wangwu | 30 | 1 | 1 | 篮球 |
| 8 | oooooo | 36 | 1 | 1 | 篮球 |
| 2 | lisi | 40 | 2 | 2 | 足球 |
| 4 | zhaoliu | 40 | 2 | 2 | 足球 |
| 5 | qiqi | 50 | 2 | 2 | 足球 |
| 6 | tom | 27 | 3 | 3 | 乒乓球 |
| 7 | oooo | 41 | 3 | 3 | 乒乓球 |
| 9 | jerry | 44 | 5 | NULL | NULL |
+----+----------+-----+-------+------+-----------+
9 rows in set (0.00 sec)
从以上左连接查询结果可以看出，除了匹配的行，m.hobby和i_id是篮球、足球和乒乓球那三行，还包括了左表中有但右表中没有的行，如果有表没有对应值，则使用 NULL 代替。也就是说在左连接查询中，使用 NULL 值表示右表中没有找到与左表中匹配的记录。左连接的查询原理如图所示
从原理图可以看出，左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索 条件的记录，也就是图中交叉的部分。右表记录不足的地方均为 NULL。
1.10.3 右连接
右连接也被称为右外连接，在FROM子句中使用RIGHT JOIN或者RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录。例如，从mytable和info表中，查询出在info表内的所有记录，并且通过判断m.hobby和 i.id 相等，在mytable表内的部分
mysql> select * from mytable m right join info i on m.hobby=i.id;
+------+----------+------+-------+----+-----------+
| id | name | age | hobby | id | hob_name |
+------+----------+------+-------+----+-----------+
| 1 | zhangsan | 30 | 1 | 1 | 篮球 |
| 2 | lisi | 40 | 2 | 2 | 足球 |
| 3 | wangwu | 30 | 1 | 1 | 篮球 |
| 4 | zhaoliu | 40 | 2 | 2 | 足球 |
| 5 | qiqi | 50 | 2 | 2 | 足球 |
| 6 | tom | 27 | 3 | 3 | 乒乓球 |
| 7 | oooo | 41 | 3 | 3 | 乒乓球 |
| 8 | oooooo | 36 | 1 | 1 | 篮球 |
| NULL | NULL | NULL | NULL | 4 | 游泳 |
+------+----------+------+-------+----+-----------+
9 rows in set (0.00 sec)
在右连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹 配的行，这些记录在左表中以NULL补足。右连接的实现原理如图所示
1324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
