MySQL高级SQL语句

按关键字排序

• 使用ORDER BY语句来实现排序排序
• 可针对一个或多个字段
• ASC:升序，默认排序方式
• DESC:降序
• ORDER BY的语法结构

SELECT column1, column2,....FROM table_name ORDER BY column1, column2,ASC|DESC;

项目一：排序

按单字段排序

mysql> select * from chengji;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan |      60 |
| 201002 |       18 | lisi     |      70 |
| 201003 |       18 | wangwu   |      80 |
| 201004 |       17 | zhaoliu  |      95 |
| 201005 |       19 | tianqi   |      55 |
| 201006 |       18 | zhangsan |      80 |
| 201007 |       19 | lisi     |      70 |
+--------+----------+----------+---------+
7 rows in set (0.00 sec)

mysql> desc chengji;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| xuehao   | int(10)     | NO   |     | NULL    |       |
| nianling | int(3)      | NO   |     | NULL    |       |
| xingming | varchar(64) | NO   |     | NULL    |       |
| chengji  | int(3)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select xingming,chengji from chengji order by chengji desc;
+----------+---------+
| xingming | chengji |
+----------+---------+
| zhaoliu  |      95 |
| wangwu   |      80 |
| zhangsan |      80 |
| lisi     |      70 |
| lisi     |      70 |
| zhangsan |      60 |
| tianqi   |      55 |
+----------+---------+
7 rows in set (0.00 sec)

按多字段排序

mysql> select xuehao,xingming,chengji from chengji order by xuehao desc,chengji;
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 201007 | lisi     |      70 |
| 201006 | zhangsan |      80 |
| 201005 | tianqi   |      55 |
| 201004 | zhaoliu  |      95 |
| 201003 | wangwu   |      80 |
| 201002 | lisi     |      70 |
| 201001 | zhangsan |      60 |
+--------+----------+---------+
7 rows in set (0.00 sec)

mysql> create table ttt(id int(1),name varchar(64),score int(3));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ttt values(1,'lisi',70),(1,'zhangsan',75);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from ttt order by id desc,score;
+------+----------+-------+
| id   | name     | score |
+------+----------+-------+
|    1 | lisi     |    70 |
|    1 | zhangsan |    75 |
+------+----------+-------+
2 rows in set (0.00 sec)

对结果进行分组

• 使用GROUP BY语句来实现分组
• 通常结合聚合函数一起使用
• 可以按一个或多个字段对结果进行分组
• GROUP BY的语法结构

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

项目二：GROUP BY分组

mysql> select count(xingming),nianling from chengji where chengji >=60 group by nianling;
+-----------------+----------+
| count(xingming) | nianling |
+-----------------+----------+
|               2 |       17 |
|               3 |       18 |
|               1 |       19 |
+-----------------+----------+
3 rows in set (0.00 sec)

项目三：GROUP BY结合ORDER BY

mysql> select count(xingming),nianling from chengji where chengji >=60 group by nianling order by count(xingming) desc;
+-----------------+----------+
| count(xingming) | nianling |
+-----------------+----------+
|               3 |       18 |
|               2 |       17 |
|               1 |       19 |
+-----------------+----------+
3 rows in set (0.00 sec)

限制结果条目

• 只返回SELECT查询结果的第一行或前几行
• 使用LIMIT语句限制条目
• LIMIT语法结构

SELECT column1, column2,....FROM table_name LIMIT [offset,] number;

项目四：LIMIT限制结果条数

注：从0行开始计数

mysql> select * from chengji limit 3;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan |      60 |
| 201002 |       18 | lisi     |      70 |
| 201003 |       18 | wangwu   |      80 |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)

不从第一条开始取值

mysql> select * from chengji limit 1,3;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201002 |       18 | lisi     |      70 |
| 201003 |       18 | wangwu   |      80 |
| 201004 |       17 | zhaoliu  |      95 |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)

设置别名

• 使用AS语句设置别名，关键字AS可省略
• 设置别名时，保证不能与库中其他表或字段名称冲突
• 别名的语法结构

SELECT column _name AS alias_name FROM table_name;
SELECT column_name(s)FROM table_name AS alias_name;

项目五：AS的用法

字段别名

mysql> select count(*) as num from chengji;
+-----+
| num |
+-----+
|   7 |
+-----+
1 row in set (0.00 sec)

表别名

mysql> select xuehao x,chengji c from chengji as ch;
+--------+------+
| x      | c    |
+--------+------+
| 201001 |   60 |
| 201002 |   70 |
| 201003 |   80 |
| 201004 |   95 |
| 201005 |   55 |
| 201006 |   80 |
| 201007 |   70 |
+--------+------+
7 rows in set (0.00 sec)

mysql> select xuehao x,chengji c from chengji as ch where ch.chengji>=60;
+--------+------+
| x      | c    |
+--------+------+
| 201001 |   60 |
| 201002 |   70 |
| 201003 |   80 |
| 201004 |   95 |
| 201006 |   80 |
| 201007 |   70 |
+--------+------+
6 rows in set (0.00 sec)

mysql> select xuehao x,chengji c from chengji as ch where ch.chengji>=60 limit 3;
+--------+------+
| x      | c    |
+--------+------+
| 201001 |   60 |
| 201002 |   70 |
| 201003 |   80 |
+--------+------+
3 rows in set (0.00 sec)

通配符

• 用于替换字符串中的部分字符
• 通常配合LIKE一起使用，并协同WHERE完成查询
• 常用通配符
  %表示零个、一个或多个
  _表示单个字符

项目六：通配符的用法

%的使用

mysql> select * from score where xingming like 'z%';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan |      60 |
| 201004 |       17 | zhaoliu  |      95 |
| 201006 |       18 | zhangsan |      80 |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)

mysql> select * from score where xingming like '%i';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201002 |       18 | lisi     |      70 |
| 201005 |       19 | tianqi   |      55 |
| 201007 |       19 | lisi     |      70 |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)

_的使用

mysql> select * from score where xingming like 'zhangs_n';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan |      60 |
| 201006 |       18 | zhangsan |      80 |
+--------+----------+----------+---------+
2 rows in set (0.00 sec)

mysql> insert into score values(201008,20,'zhangsun',70);
Query OK, 1 row affected (0.00 sec)

mysql> select * from score where xingming like 'zhangs_n';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan |      60 |
| 201006 |       18 | zhangsan |      80 |
| 201008 |       20 | zhangsun |      70 |
+--------+----------+----------+---------+
3 rows in set (0.00 sec)

子查询

• 也称作内查询或者嵌套查询
• 先于主查询被执行，其结果将作为外层主查询的条件
• 在增删改查中都可以使用子查询
• 支持多层嵌套
• IN语句是用来判断某个值是否在给定的结果集中

附：

• 子查询上也可以使用比较运算符(=、<、>、>=、<=)，这些运算符主要是对运算符前面的表达式和后面的子查询进行比较运算。例如，查询出名字是shirley的记录，并输出其ID、名字和等级信息。
• EXIST这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空，则返回TRUE;反之，则返回FALSE。例如，先通过子查询判断返回是否为TRUE，如果用户shirley存在，则计算整个tmp表的总记录数量。

项目七

查询数值

mysql> select xingming,chengji from chengji where chengji in (select chengji from chengji where chengji >=60);
+----------+---------+
| xingming | chengji |
+----------+---------+
| zhangsan |      60 |
| lisi     |      70 |
| lisi     |      70 |
| wangwu   |      80 |
| zhangsan |      80 |
| zhaoliu  |      95 |
+----------+---------+
6 rows in set (0.00 sec)

插入数值

mysql> create table t1 like chengji;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;
Empty set (0.01 sec)

mysql> insert into t1 select * from chengji where nianling in (select nianling from chengji where nianling >=18);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t1;                                                                   
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201002 |       18 | lisi     |      70 |
| 201003 |       18 | wangwu   |      80 |
| 201006 |       18 | zhangsan |      80 |
| 201005 |       19 | tianqi   |      55 |
| 201007 |       19 | lisi     |      70 |
+--------+----------+----------+---------+
5 rows in set (0.00 sec)

修改数值（联表操作，两个不同表数据相关联）

mysql> select a.xingming,a.chengji from (select xingming,chengji from chengji where chengji>=80) as a;
+----------+---------+
| xingming | chengji |
+----------+---------+
| wangwu   |      80 |
| zhaoliu  |      95 |
| zhangsan |      80 |
+----------+---------+
3 rows in set (0.00 sec)

mysql> update score set chengji=chengji+5 where chengji in(select a.chengji from (select chengji from chengji where chengji>=80) as a);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from score;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan |      60 |
| 201002 |       18 | lisi     |      70 |
| 201003 |       18 | wangwu   |      85 |
| 201004 |       17 | zhaoliu  |     100 |
| 201005 |       19 | tianqi   |      55 |
| 201006 |       18 | zhangsan |      85 |
| 201007 |       19 | lisi     |      70 |
| 201008 |       20 | zhangsun |      70 |
+--------+----------+----------+---------+
8 rows in set (0.00 sec)

删除数值

mysql> delete from score where chengji in (select a.chengji from (select chengji from chengji where chengji >=60 and chengji<=70)a);
Query OK, 4 rows affected (0.00 sec)

mysql> select * from score;                                                                
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201003 |       18 | wangwu   |      85 |
| 201004 |       17 | zhaoliu  |     100 |
| 201005 |       19 | tianqi   |      55 |
| 201006 |       18 | zhangsan |      85 |
+--------+----------+----------+---------+
4 rows in set (0.00 sec)

查询固定值用‘=‘

NULL值

• 表示缺失的值
• 与数字0或者空白(spaces)是不同的
• 使用IS NULL或IS NOT NULL进行判断
• NULL值和空值的区别
  1、空值长度为0，不占空间;NULL值的长度为NULL，占用空间2、IS NULL无法判断空值
  3、空值使用“="或者“>"来处理
  4、COUNT()计算时，NULL会忽略，空值会加入计算

项目八

mysql> insert into score values(201007,19,'zhaowu',default);
Query OK, 1 row affected (0.00 sec)

mysql> select * from score;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201003 |       18 | wangwu   |      85 |
| 201004 |       17 | zhaoliu  |     100 |
| 201005 |       19 | tianqi   |      55 |
| 201006 |       18 | zhangsan |      85 |
| 201007 |       19 | zhaowu   |    NULL |
+--------+----------+----------+---------+
5 rows in set (0.00 sec)

mysql> select count(chengji) from score;
+----------------+
| count(chengji) |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> insert into score values(201007,19, ' ' ,77);
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(201007,19, ' ' ,0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from score;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201003 |       18 | wangwu   |      85 |
| 201004 |       17 | zhaoliu  |     100 |
| 201005 |       19 | tianqi   |      55 |
| 201006 |       18 | zhangsan |      85 |
| 201007 |       19 | zhaowu   |    NULL |
| 201007 |       19 |          |      77 |
| 201007 |       19 |          |       0 |
+--------+----------+----------+---------+
7 rows in set (0.00 sec)

mysql> select count(xingming) from score;
+-----------------+
| count(xingming) |
+-----------------+
|               7 |
+-----------------+
1 row in set (0.00 sec)

mysql> select count(chengji) from score;
+----------------+
| count(chengji) |
+----------------+
|              6 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from score where chengji is null;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201007 |       19 | zhaowu   |    NULL |
+--------+----------+----------+---------+
1 row in set (0.00 sec)

mysql> select * from score where chengji is not null;
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201003 |       18 | wangwu   |      85 |
| 201004 |       17 | zhaoliu  |     100 |
| 201005 |       19 | tianqi   |      55 |
| 201006 |       18 | zhangsan |      85 |
| 201007 |       19 |          |      77 |
| 201007 |       19 |          |       0 |
+--------+----------+----------+---------+
6 rows in set (0.00 sec)

正则表达式

• 根据指定的匹配模式匹配记录中符合要求的特殊字符
• 使用REGEXP关键字指定匹配模式
• 常用匹配模式
  1、^匹配开始字符
  2、p1|p2匹配p1或p2
  3、$ 匹配结束字符
  4、[…]匹配字符集中的任意一个字符
  5、.匹配任意单个字符
  6、[^…]匹配不在中括号内的任何字符
  7、*匹配任意个前面的字符
  8、{n}匹配前面的字符串n次
  9、＋匹配前面字符至少1次
  10、{n,m} 匹配前面的字符串至少n次，
  至多m次

项目九

匹配开始字符

mysql> select * from  score where xingming REGEXP '^w';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201003 |       18 | wangwu   |      85 |
+--------+----------+----------+---------+
1 row in set (0.00 sec)

匹配结束字符

mysql> select * from  score where xingming REGEXP 'i$';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201005 |       19 | tianqi   |      55 |
+--------+----------+----------+---------+
1 row in set (0.00 sec)

匹配字符集中的任意一个字符

mysql> select * from score where xingming REGEXP '[a-z]';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201003 |       18 | wangwu   |      85 |
| 201004 |       17 | zhaoliu  |     100 |
| 201005 |       19 | tianqi   |      55 |
| 201006 |       18 | zhangsan |      85 |
| 201007 |       19 | zhaowu   |    NULL |
+--------+----------+----------+---------+
5 rows in set (0.00 sec)

匹配不在中括号内的任何字符

mysql> select * from score where xingming REGEXP '[^a-z]';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201007 |       19 |          |      77 |
| 201007 |       19 |          |       0 |
+--------+----------+----------+---------+
2 rows in set (0.00 sec)

匹配前面的字符串n次

mysql> insert into chengji values(201008,20,'lii',66);
Query OK, 1 row affected (0.01 sec)

mysql> select * from chengji where xingming REGEXP 'i{2}';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201008 |       20 | lii      |      66 |
+--------+----------+----------+---------+
1 row in set (0.00 sec)

p1|p2匹配p1或p2

mysql> select * from chengji where xingming REGEXP 'lisi|lisisi';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201002 |       18 | lisi     |      70 |
| 201007 |       19 | lisi     |      70 |
+--------+----------+----------+---------+
2 rows in set (0.00 sec)

＋匹配前面字符至少1次

mysql> select * from chengji where xingming REGEXP 's+';
+--------+----------+----------+---------+
| xuehao | nianling | xingming | chengji |
+--------+----------+----------+---------+
| 201001 |       17 | zhangsan |      60 |
| 201002 |       18 | lisi     |      70 |
| 201006 |       18 | zhangsan |      80 |
| 201007 |       19 | lisi     |      70 |
+--------+----------+----------+---------+
4 rows in set (0.00 sec)

运算符

• 用于对记录中的字段值进行运算
• 运算符分类
  算术运算符
  比较运算符
  逻辑运算符
  位运算符

算术运算符

• MySQL支持的算术运算符
  +加法
  -减法
  *乘法
  /除法
  %取余数

项目十

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

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

mysql> create table js select 1+2,2-1,1*2,2/1,1%2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

比较运算符

• 字符串的比较默认不区分大小写，可使用binary来区分
• 常用比较运算符
  1、= 等于
  2、LIKE 通配符匹配
  3、> 大于
  4、IS NULL 判断一个值是否为NULL
  5、< 小于
  6、IS NOTNULL判断一个值是否不为NULL
  7、>= 大于等于
  8、BETWEEN AND 两者之间
  9、<= 小于等于
  10、GREATEST 两个或多个参数时返回最大值
  11、!= 或 <> 不等于
  12、LEAST 两个或多个参数时返回最小值
  13、IN在集合中

注

• 等号(=）
  等号是用来判断数字、字符串和表达式是否相等的，如果相等则返回1，如果不相等则返回0。如果比较的两者有一个值是NULL，则比较的结果就是NULL。其中字符的比较是根据ASCII码来判断的，如果ASCI码相等，则表示两个字符相同;如果 ASCIl码不相等，则表示两个字符不相同。例如，等于运算符在数字、字符串和表达式上的使用。
• 不等于运算符
  不等于号有两种写法，分别是<>或者=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回1，如果相等则返回0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断NULL。例如，关于数字、字符串和表达式的不等于运算符的使用。
• 大于、大于等于、小于、小于等于运算符
  大于(>)运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回1，否则返回0，同样不能用于判断NULL。.
  小于(<)运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回1，否则返回0，同样不能用于判断NULL。.
  大于等于(>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回1，否则返回0，不能用于判断NULL。.
  小于等于(<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回1，否则返回0，不能用于判断NULL。.
• **IS NULL、IS NOT NULL. **
  IS NULL判断一个值是否为NULL…如果为NULL返回1，否则返回0。.
  IS NOT NULL判断一个值是否丕为NULL…如果丕为 NULL返回1，否则返回0。
• **BETWEEN AND **
  BETWEEN AND比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间。
• LEAST、GREATEST
  LEAST:当有两个或者多个参数时，返回其中的最小值。如果其中一个值为NULL，则返回结果就为NULL。.
  GREATEST:当有两个或者多个参数时，返回其中的最大值。如果其中一个值为NULL，则返回结果就为NULL。.
  附
  从以上结果可以看出，LEAST比较的参数为数字时，返回的是其中最小的值;当比较的参数为字符串时，返回字母表中顺序最靠前的字符。GREATEST比较的参数为数字时，返回的是其中最大的值;当比较的参数为字符串时，返回字母表中顺序最靠后的字符。.
• IN、NOT
  IN 判断一个值是否在对应的列表中，如果是返回1，否则返回0。
  NOT IN 判断一个值是否不在对应的列表中.如果不是返回1，否则返回0。。例如，判断某数字是否在一组数字中，也可判断某字符是否在一组字符中。
• LIKE、NOT LIKE
  LIKE用来匹配字符串，如果匹配成功则返回1，反之返回0。LIKE支持两种通配符: %用于匹配任意数目的字符.而’_'只能匹配一个字符.。NOTLIKE正好跟LIKE相反…如果没有匹配成功则返回1，反之返回0。
  附：
• 如果两者都是整数，则按照整数值进行比较。
• 如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。
• 如果两者都是字符串，则按照字符串进行比较。
• 如果两者中至少有一个值是NULL，则比较的结果是NULL。

项目十一

等号运算符

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

不等于运算符

mysql> select 2!=1,2!=2,3<>'3','a'<>'b';
+------+------+--------+----------+
| 2!=1 | 2!=2 | 3<>'3' | 'a'<>'b' |
+------+------+--------+----------+
|    1 |    0 |      0 |        1 |
+------+------+--------+----------+
1 row in set (0.00 sec)

大于、大于等于、小于、小于等于运算符

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

mysql> select 2<3;
+-----+
| 2<3 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> select 2<=3;
+------+
| 2<=3 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

IS NULL、IS NOT NULL

mysql> select 1 is NULL;
+-----------+
| 1 is NULL |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> select 1 is not NULL;
+---------------+
| 1 is not NULL |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

BETWEEN AND

mysql> select 2 between 2 and 6;
+-------------------+
| 2 between 2 and 6 |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> select 6 between 2 and 6;
+-------------------+
| 6 between 2 and 6 |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> select 'a' between 'a' and 'c';
+-------------------------+
| 'a' between 'a' and 'c' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select 'd' between 'a' and 'c';
+-------------------------+
| 'd' between 'a' and 'c' |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

LEAST、GREATEST

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

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

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

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

IN、NOT IN

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

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

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

mysql> select 'z' in ('a','b','c');
+----------------------+
| 'z' in ('a','b','c') |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

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

逻辑运算符

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

运算符	描述
NOT或!	逻辑非
AND或&&	逻辑与
OR或ll	逻辑或
XOR	逻辑异或

• 逻辑异或
  同为真或同为假都为假，一个真一个假时才为真。

项目十二

逻辑或

mysql> select 1 OR 0;
+--------+
| 1 OR 0 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select 5 OR 0;
+--------+
| 5 OR 0 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select 10 OR 0;
+---------+
| 10 OR 0 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

逻辑非

mysql> select !5;
+----+
| !5 |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

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

逻辑与

mysql> select 5 && 3;
+--------+
| 5 && 3 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select 5 && 0;
+--------+
| 5 && 0 |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

逻辑异或

mysql> select 1 XOR 0;
+---------+
| 1 XOR 0 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select 5 XOR 0;
+---------+
| 5 XOR 0 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select 0 XOR 0;
+---------+
| 0 XOR 0 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

逻辑运算符NULL值使用

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

位运算符

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

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

项目十三

按位乘

mysql> select 5 & 3;
+-------+
| 5 & 3 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

按位或

mysql> select 5 | 3;
+-------+
| 5 | 3 |
+-------+
|     7 |
+-------+
1 row in set (0.00 sec)

按位取反

mysql> select ~5;
+----------------------+
| ~5                   |
+----------------------+
| 18446744073709551610 |
+----------------------+
1 row in set (0.00 sec)

将5改成二进制，自动补全前面的所有0，因此取反数值很大

按位异或

mysql> select 5 ^ 3;
+-------+
| 5 ^ 3 |
+-------+
|     6 |
+-------+
1 row in set (0.00 sec)

按位左移

mysql> select 3 << 2;
+--------+
| 3 << 2 |
+--------+
|     12 |
+--------+
1 row in set (0.00 sec)

mysql> select 5 << 2;
+--------+
| 5 << 2 |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)

按位右移

mysql> select 1 >> 1;
+--------+
| 1 >> 1 |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

mysql> select 10 >> 2;
+---------+
| 10 >> 2 |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

运算符的优先级

优先级	运算符	优先级	运算符
1	!	8	管道符
2	~	9.	=,<=>,>=,>,<=,<,<>,l=,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	:=

内连接

定义

• 两张或多张表中同时符合某种条件的数据记录组合
• FROM子句中使用INNER JOIN关键字连接多张表，并使用ON设置连接条件
• 是系统默认的表连接方式，可以省略INNER关键字
• 多表支持连续使用INNER JOIN，建议不超过三个表
• 语法结构

SELECT column name(s) FROM table1
INNERJOIN table2 ON table1.column_name = table2.column_name;

实现原理

项目十四

添加新表，共同字段为1、2、3

mysql> create table t2(id int(1),num int(3));
Query OK, 0 rows affected (0.00 sec)

mysql> create table t3(id int(1),num int(3));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values(1,17),(2,18),(3,18),(4,17),(5,19);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t3 values(1,305),(2,306),(3,307),(8,308),(9,309),(10,310);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t2 inner join t3 on t2.id=t3.id;
+------+------+------+------+
| id   | num  | id   | num  |
+------+------+------+------+
|    1 |   17 |    1 |  305 |
|    2 |   18 |    2 |  306 |
|    3 |   18 |    3 |  307 |
+------+------+------+------+
3 rows in set (0.00 sec)

左两列为t2，右两列为t3

外连接

左连接

• 也被称为左外连接
• 在FROM子句中使用LEFT JOIN关键字来表示
• 匹配左表中所有行及右表中符合条件的行
• 实现原理
  

项目十五

mysql> select * from t2 left join t3 on t2.id=t3.id;
+------+------+------+------+
| id   | num  | id   | num  |
+------+------+------+------+
|    1 |   17 |    1 |  305 |
|    2 |   18 |    2 |  306 |
|    3 |   18 |    3 |  307 |
|    4 |   17 | NULL | NULL |
|    5 |   19 | NULL | NULL |
+------+------+------+------+
5 rows in set (0.00 sec)

注：左表5行都显示，右表没有的用NULL表示

右连接

• 也被称为右外连接
• 在FROM子句中使用RIGHT JOIN关键字来表示
• 匹配右表中所有行及左表中符合条件的行
• 实现原理
  

项目十六

右表5行都显示，左表没有的用NULL表示
与左连接相反

mysql> select * from t2 right join t3 on t2.id=t3.id;
+------+------+------+------+
| id   | num  | id   | num  |
+------+------+------+------+
|    1 |   17 |    1 |  305 |
|    2 |   18 |    2 |  306 |
|    3 |   18 |    3 |  307 |
| NULL | NULL |    8 |  308 |
| NULL | NULL |    9 |  309 |
| NULL | NULL |   10 |  310 |
+------+------+------+------+
6 rows in set (0.00 sec)