MySQL常用進階查詢

一、正則運算式
- 1.1 以特定字串開頭與結尾的記錄
- 1.2 匹配任何單個字符（以“.”代替字串中的任意一個字符）
- 1.3 匹配前面字符任意多次（0個或多個）
- 1.4 匹配前面字符至少一次（1次或多次）
- 1.5 匹配包含指定的字串
- 1.6 匹配包含或者關系的記錄（匹配p1|p2）
- 1.7 匹配指定字符集中的任意一個
- 1.8 匹配不在括號內的任何字符
- 1.9 匹配前面的字串n次
二、運算子（算術、比較、邏輯、位與）
- 2.1 算術運算
- 2.2 比較運算子
- - 2.2.1 等于（=）
  - 2.2.2 不等于（!=或<>）
  - 2.2.3 大于、大于等于、小于、小于等于
  - 2.2.4 判斷一個值為或者不為null（is null、is not null）
  - 2.2.5 兩者之間（between...and...）
  - 2.2.6 兩個或多個引數時回傳最小（least ）或最大值（greatest）
  - 2.2.7 在或不再集合中（in，not in）
  - 2.2.8 通配符匹配
- 2.3 邏輯運算子（布爾運算子）
- - 2.3.1 邏輯非（not或!）
  - 2.3.2 邏輯與（and或&&）
  - 2.3.3 邏輯或（or）
  - 2.3.4 邏輯異或（xor）
- 2.4 位與運算子
- - 2.4.1 按位與運算（&）
  - 2.4.2 按位或運算（|）
  - 2.4.3 按位異或運算（^）
  - 2.4.4 按位取反（~）
- 2.5 運算子的優先級
三、連接查詢（內、左、右）
- 3.1 實驗庫+表創建準備
- 3.2 內連接（系統默認）
- 3.3 左連接（左外連接）
- 3.4 右連接（右外連接）
四、總結
- 4.1 正則
- 4.2 運算子
- 4.3 連接查詢

一、正則運算式

MySQL正則運算式通常是在檢索資料庫記錄的時候，根據指定的匹配模式來匹配記錄中符合要求的特殊字串
MySQL的正則運算式使用regexp這個關鍵字來指定正則運算式的匹配模式

匹配模式	說明
^	匹配文本的開始字符
$	匹配文本的結束字符
.	匹配任何單個字符
*	匹配零個或多個在它前面的字符
+	匹配前面的字符1次或多次
字串	匹配包含指定的字串
p1\|p2	匹配p1或p2
[…]	匹配字符集合中的任意一個字符
[^…]	匹配不在括號中的任何字符
{n}	匹配前面的字串n次
{n,m}	匹配前面的字串最少n次，最多m次
PS	^雖然表示匹配開始字符，但也需要看^所處的位置例如：[^a]b表示不包含括號中的a，則表示以b為開頭

1.1 以特定字串開頭與結尾的記錄

mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 編號   | 姓名   | 得分    | 地址   | 特長   |
+--------+--------+---------+--------+--------+
|      1 | 劉一   |  80.000 | 北京   | 2      |
|      2 | 王二   |  90.000 | 深圳   | 2      |
|      3 | 李四   |  60.000 | 上海   | 4      |
|      4 | 田五   |  99.000 | 南京   | 5      |
|      5 | 教授   | 100.000 | 成都   | 3      |
|      6 | 小明   |  10.000 | 鎮江   | 3      |
|      7 | 劉二   |  80.000 | 北京   | 2      |
+--------+--------+---------+--------+--------+

【在qz2表的編號和姓名列里用正則運算式（regexp）匹配以劉開頭的字符】
mysql> select 編號,姓名 from qz2 where 姓名 regexp '^劉';
+--------+--------+
| 編號   | 姓名   |
+--------+--------+
|      1 | 劉一   |
|      7 | 劉二   |
+--------+--------+

【在qz2表的編號和姓名列里用正則運算式（regexp）匹配以二結尾的字符】
mysql> select 編號,姓名 from qz2 where 姓名 regexp '二$';
+--------+--------+
| 編號   | 姓名   |
+--------+--------+
|      2 | 王二   |
|      7 | 劉二   |
+--------+--------+

1.2 匹配任何單個字符（以“.”代替字串中的任意一個字符）

mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 編號   | 姓名   | 得分    | 地址   | 特長   |
+--------+--------+---------+--------+--------+
|      1 | 劉一   |  80.000 | 北京   | 2      |
|      2 | 王二   |  90.000 | 深圳   | 2      |
|      3 | 李四   |  60.000 | 上海   | 4      |
|      4 | 田五   |  99.000 | 南京   | 5      |
|      5 | 教授   | 100.000 | 成都   | 3      |
|      6 | 小明   |  10.000 | 鎮江   | 3      |
|      7 | 劉二   |  80.000 | 北京   | 2      |
+--------+--------+---------+--------+--------+


【在qz2表的得分列里用正則運算式（regexp）匹配以1開頭，中間5個字符，0結尾的字符】
mysql> select * from qz2 where 得分 regexp '1.....0';
+--------+--------+---------+--------+--------+
| 編號   | 姓名   | 得分    | 地址   | 特長   |
+--------+--------+---------+--------+--------+
|      5 | 教授   | 100.000 | 成都   | 3      |
+--------+--------+---------+--------+--------+

1.3 匹配前面字符任意多次（0個或多個）

【在qz2表的姓名列里用正則運算式（regexp）匹配字符一前面有0個或者多個字符的字符】
mysql> select * from qz2 where 姓名 regexp '一*';
+--------+--------+--------+--------+--------+
| 編號   | 姓名   | 得分   | 地址   | 特長   |
+--------+--------+--------+--------+--------+
|      1 | 劉一   | 80.000 | 北京   | 2      |
+--------+--------+--------+--------+--------+

1.4 匹配前面字符至少一次（1次或多次）

mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 編號   | 姓名   | 得分    | 地址   | 特長   |
+--------+--------+---------+--------+--------+
|      1 | 劉一   |  80.000 | 北京   | 2      |
|      2 | 王二   |  90.000 | 深圳   | 2      |
|      3 | 李四   |  60.000 | 上海   | 4      |
|      4 | 田五   |  99.000 | 南京   | 5      |
|      5 | 教授   | 100.000 | 成都   | 3      |
|      6 | 小明   |  10.000 | 鎮江   | 3      |
|      7 | 劉二   |  80.000 | 北京   | 2      |
+--------+--------+---------+--------+--------+
【在qz2表的姓名列里用正則運算式（regexp）匹配有字符‘二’一次或者多次的字符】
mysql> select * from qz2 where 姓名 regexp '二+';
+--------+--------+--------+--------+--------+
| 編號   | 姓名   | 得分   | 地址   | 特長   |
+--------+--------+--------+--------+--------+
|      2 | 王二   | 90.000 | 深圳   | 2      |
|      7 | 劉二   | 80.000 | 北京   | 2      |
+--------+--------+--------+--------+--------+
【在qz2表的姓名列里用正則運算式（regexp）匹配有字符‘一’一次或者多次的字符】
mysql> select * from qz2 where 姓名 regexp '一+';
+--------+--------+--------+--------+--------+
| 編號   | 姓名   | 得分   | 地址   | 特長   |
+--------+--------+--------+--------+--------+
|      1 | 劉一   | 80.000 | 北京   | 2      |
+--------+--------+--------+--------+--------+

1.5 匹配包含指定的字串

mysql> select * from qz2 where 得分 regexp '0';
+--------+--------+---------+--------+--------+
| 編號   | 姓名   | 得分    | 地址   | 特長   |
+--------+--------+---------+--------+--------+
|      1 | 劉一   |  80.000 | 北京   | 2      |
|      2 | 王二   |  90.000 | 深圳   | 2      |
|      3 | 李四   |  60.000 | 上海   | 4      |
|      4 | 田五   |  99.000 | 南京   | 5      |
|      5 | 教授   | 100.000 | 成都   | 3      |
|      6 | 小明   |  10.000 | 鎮江   | 3      |
|      7 | 劉二   |  80.000 | 北京   | 2      |
+--------+--------+---------+--------+--------+
7 rows in set (0.00 sec)
【在qz2表的得分列里用正則運算式（regexp）匹配包含指定字符‘9’的字符】
mysql> select * from qz2 where 得分 regexp '9';
+--------+--------+--------+--------+--------+
| 編號   | 姓名   | 得分   | 地址   | 特長   |
+--------+--------+--------+--------+--------+
|      2 | 王二   | 90.000 | 深圳   | 2      |
|      4 | 田五   | 99.000 | 南京   | 5      |
+--------+--------+--------+--------+--------+

1.6 匹配包含或者關系的記錄（匹配p1|p2）

mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 編號   | name   | 得分    | 地址   | 特長   |
+--------+--------+---------+--------+--------+
|      1 | 劉一   |  80.000 | 北京   | 2      |
|      2 | 王二   |  90.000 | 深圳   | 2      |
|      3 | 李四   |  60.000 | 上海   | 4      |
|      4 | 田五   |  99.000 | 南京   | 5      |
|      5 | 教授   | 100.000 | 成都   | 3      |
|      6 | 小明   |  10.000 | 鎮江   | 3      |
|      7 | 劉二   |  80.000 | 北京   | 2      |
+--------+--------+---------+--------+--------+
【在qz2表的name列里用正則運算式（regexp）匹配包含字符‘明’或‘授’的字符】
mysql> select * from qz2 where name regexp '明|授';
+--------+--------+---------+--------+--------+
| 編號   | name   | 得分    | 地址   | 特長   |
+--------+--------+---------+--------+--------+
|      5 | 教授   | 100.000 | 成都   | 3      |
|      6 | 小明   |  10.000 | 鎮江   | 3      |
+--------+--------+---------+--------+--------+

1.7 匹配指定字符集中的任意一個

mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 編號   | name   | 得分    | 地址   | 特長   |
+--------+--------+---------+--------+--------+
|      1 | 劉一   |  80.000 | 北京   | 2      |
|      2 | 王二   |  90.000 | 深圳   | 2      |
|      3 | 李四   |  60.000 | 上海   | 4      |
|      4 | 田五   |  99.000 | 南京   | 5      |
|      5 | 教授   | 100.000 | 成都   | 3      |
|      6 | 小明   |  10.000 | 鎮江   | 3      |
|      7 | 劉二   |  80.000 | 北京   | 2      |
+--------+--------+---------+--------+--------+

【在qz2表的得分列里用正則運算式（regexp）匹配的字符包含指定字符集‘9或者6’的字符】
mysql> select * from qz2 where 得分 regexp '[9,6]';
+--------+--------+--------+--------+--------+
| 編號   | name   | 得分   | 地址   | 特長   |
+--------+--------+--------+--------+--------+
|      2 | 王二   | 90.000 | 深圳   | 2      |
|      3 | 李四   | 60.000 | 上海   | 4      |
|      4 | 田五   | 99.000 | 南京   | 5      |
+--------+--------+--------+--------+--------+

1.8 匹配不在括號內的任何字符

mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 編號   | name   | 得分    | 地址   | 特長   |
+--------+--------+---------+--------+--------+
|      1 | 劉一   |  80.000 | 北京   | 2      |
|      2 | 王二   |  90.000 | 深圳   | 2      |
|      3 | 李四   |  60.000 | 上海   | 4      |
|      4 | 田五   |  99.000 | 南京   | 5      |
|      5 | 教授   | 100.000 | 成都   | 3      |
|      6 | 小明   |  10.000 | 鎮江   | 3      |
|      7 | 劉二   |  80.000 | 北京   | 2      |
+--------+--------+---------+--------+--------+


【在qz2表的編號和得分列里用正則運算式（regexp）匹配不包含字符‘3,4，5,6’的字符】
mysql> select 編號,得分 from qz2 where 編號 regexp '[^3,4,5,6]';
+--------+--------+
| 編號   | 得分   |
+--------+--------+
|      1 | 80.000 |
|      2 | 90.000 |
|      7 | 80.000 |
+--------+--------+

1.9 匹配前面的字串n次

mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 編號   | name   | 得分    | 地址   | 特長   |
+--------+--------+---------+--------+--------+
|      1 | 劉一   |  80.000 | 北京   | 2      |
|      2 | 王二   |  90.000 | 深圳   | 2      |
|      3 | 李四   |  60.000 | 上海   | 4      |
|      4 | 田五   |  99.000 | 南京   | 5      |
|      5 | 教授   | 100.000 | 成都   | 3      |
|      6 | 小明   |  10.000 | 鎮江   | 3      |
|      7 | 劉二   |  80.000 | 北京   | 2      |
+--------+--------+---------+--------+--------+


【在qz2表得分列里用正則運算式（regexp）匹配包含字串'9'2次的字符】
mysql> select * from qz2 where 得分 regexp '9{2}';
+--------+--------+--------+--------+--------+
| 編號   | name   | 得分   | 地址   | 特長   |
+--------+--------+--------+--------+--------+
|      4 | 田五   | 99.000 | 南京   | 5      |
+--------+--------+--------+--------+--------+

二、運算子（算術、比較、邏輯、位與）

MySQL的運算子用于對記錄中的欄位值進行運算
MySQL的運算子共有四種（算術、比較、邏輯、位與）

2.1 算術運算

在除法運算和求余運算中，除數不能為0，若除數是0，則回傳的結果為NULL
如果有多個運算子，按照先乘除后加減的優先級進行運算，相同優先級的運算子沒有先后順序

運算子	說明
+	加法
-	減法
*	乘法
/	除法
%	取余

mysql> select 1+2,2-1,3*3,5/2,4/2,6%3;
+-----+-----+-----+--------+--------+------+
| 1+2 | 2-1 | 3*3 | 5/2    | 4/2    | 6%3  |
+-----+-----+-----+--------+--------+------+
|   3 |   1 |   9 | 2.5000 | 2.0000 |    0 |
+-----+-----+-----+--------+--------+------+

【除法默認小數點后4位】
mysql> create table ys1 select 1+2,2-1,3*3,22222225/2,400000/2,6%3;
mysql> desc ys1;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| 1+2        | int(3)        | NO   |     | 0       |       |
| 2-1        | int(3)        | NO   |     | 0       |       |
| 3*3        | int(3)        | NO   |     | 0       |       |
| 22222225/2 | decimal(12,4) | YES  |     | NULL    |       |
| 400000/2   | decimal(10,4) | YES  |     | NULL    |       |
| 6%3        | int(1)        | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

2.2 比較運算子

字串的比較默認不區分大小寫，可使用binary來區分
比較物件：數字或者字符

運算子	說明
=	等于
!=或<>	不等于
like	通配符匹配
>	大于
>=	大于等于
<	小于
<=	小于等于
is null	判斷1個值是否為NULL
is not noull	判斷1個值是否不為NULL
between and	兩者之間
greatest	兩個或多個引數時回傳最大值
least	兩個或多個引數時放回最小值
in	在集合中

2.2.1 等于（=）

等號（=）用來判斷數字、字串和運算式是否相等，如果相等則回傳1，如果不相等則回傳0
如果比較的兩者有一個值是NULL，則比較的結果為NULL
字符的比較是根據ASCII碼來判斷的，如果ASCII碼相等，則表示這兩個字符相同；如果ASCII碼不相等，則表示兩個字符不相同
這里需要注意的ASCII碼：a（97）、A（65）、數字0（48）

mysql> select 3=4,5='5','e'='b',(4+4)=(7+1),'6'=null,'6'='a';
+-----+-------+---------+-------------+----------+---------+
| 3=4 | 5='5' | 'e'='b' | (4+4)=(7+1) | '6'=null | '6'='a' |
+-----+-------+---------+-------------+----------+---------+
|   0 |     1 |       0 |           1 |     NULL |       0 |
+-----+-------+---------+-------------+----------+---------+

通過回傳結果可以看出：如果兩者都是整數，則按照整數值進行比較；
如果一個整數與一個字串比較，則會自動將字串轉換為數字，再進行比較（一般不會把這兩者放一起做對比）
如果兩者都是字串，則按照字串進行比較
如果兩者中至少有一個值是NULL，則結果為NULL

2.2.2 不等于（!=或<>）

用于針對數字、字串和運算式不相等的比較
不相等則回傳1，相等則回傳0，與等于（=）的回傳值相反，同時不等于（!=或<>）無法用于判斷是否為NULL

mysql> select 'qz'<>'qqzz',3<>4,6!=6,3.333!=3,null<>null;
+--------------+------+------+----------+------------+
| 'qz'<>'qqzz' | 3<>4 | 6!=6 | 3.333!=3 | null<>null |
+--------------+------+------+----------+------------+
|            1 |    1 |    0 |        1 |       NULL |
+--------------+------+------+----------+------------+

2.2.3 大于、大于等于、小于、小于等于

大于（>）運算子用來判斷左側的運算元是否大于右側的運算元，若大于回傳1，否則回傳0，同樣也不能用于判斷NULL
小于（<）運算子用來判斷左側的運算元是否小于右側的運算元，若小于回傳1，否則回傳0，同樣也不能用于判斷NULL
大于等于（>=）運算子用來判斷左側的運算元是否大于等于右側的運算元，若大于回傳1，否則回傳0，同樣也不能用于判斷NULL
小于等于（<=）運算子用來判斷左側的運算元是否小于等于右側的運算元，若小于回傳1，否則回傳0，同樣也不能用于判斷NULL

mysql> select 111>12,'b'>'c',201>=203,(7+8)>=(6+7),5.55<4,2<3,'y'<='z',55<=55.55,'p'>=null;
+--------+---------+----------+--------------+--------+-----+----------+-----------+-----------+
| 111>12 | 'b'>'c' | 201>=203 | (7+8)>=(6+7) | 5.55<4 | 2<3 | 'y'<='z' | 55<=55.55 | 'p'>=null |
+--------+---------+----------+--------------+--------+-----+----------+-----------+-----------+
|      1 |       0 |        0 |            1 |      0 |   1 |        1 |         1 |      NULL |
+--------+---------+----------+--------------+--------+-----+----------+-----------+-----------+

2.2.4 判斷一個值為或者不為null（is null、is not null）

is null 判斷一個值是否為null，如果為則回傳1，否則回傳0
is not null 判斷一個值是否不為null，如果不為null則回傳1，否則回傳0

mysql> select 5 is null,'q' is not null,null is null;
+-----------+-----------------+--------------+
| 5 is null | 'q' is not null | null is null |
+-----------+-----------------+--------------+
|         0 |               1 |            1 |
+-----------+-----------------+--------------+

2.2.5 兩者之間（between…and…）

between比較運算子通常用于判斷一個值是否落在某兩個值之間，是則回復1，不是則回傳0

mysql> select 10 between 10 and 15,10 between 5 and 20,10 between 100 and 200,'b' between 'a' and 'f';
+----------------------+---------------------+------------------------+-------------------------+
| 10 between 10 and 15 | 10 between 5 and 20 | 10 between 100 and 200 | 'b' between 'a' and 'f' |
+----------------------+---------------------+------------------------+-------------------------+
|                    1 |                   1 |                      0 |                       1 |
+----------------------+---------------------+------------------------+-------------------------+

2.2.6 兩個或多個引數時回傳最小（least ）或最大值（greatest）

如果一個為null，則回傳null
數字對比則按大小排列
字母對比，則按從a-b順序，字母越前越“小”

mysql> select least(5,7,9),least('d','e','f'),greatest(5,7,9),greatest('d','e','f');
+--------------+--------------------+-----------------+-----------------------+
| least(5,7,9) | least('d','e','f') | greatest(5,7,9) | greatest('d','e','f') |
+--------------+--------------------+-----------------+-----------------------+
|            5 | d                  |               9 | f                     |
+--------------+--------------------+-----------------+-----------------------+

2.2.7 在或不再集合中（in，not in）

in 判斷一個值是否在對應的串列中，在回傳1，不在回傳0
not in 判斷一個值是否不在對應的串列中，不在回傳1，在回傳0

mysql> select 3 in (1,3,5,7),'e' not in('d','e','f');
+----------------+-------------------------+
| 3 in (1,3,5,7) | 'e' not in('d','e','f') |
+----------------+-------------------------+
|              1 |                       0 |
+----------------+-------------------------+

2.2.8 通配符匹配

like 用來匹配字串，如果匹配成功則回傳1，不成功回傳0
like 支持兩種通配符：’%’：用于匹配任意數目的字符，’ '：只能匹配一個字符
not like 與 like 相反，如果沒有匹配成功則回傳1，如果成功則回傳0

mysql> select 'qwer' like 'qwe_','qwe' like '%e','qwe' not like '%ty';
+--------------------+-----------------+----------------------+
| 'qwer' like 'qwe_' | 'qwe' like '%e' | 'qwe' not like '%ty' |
+--------------------+-----------------+----------------------+
|                  1 |               1 |                    1 |
+--------------------+-----------------+----------------------+

2.3 邏輯運算子（布爾運算子）

邏輯運算子又被稱為布爾運算子，通常用來判斷運算式的真偽，若為真則回傳1，否則回傳0
真和假也可以用true 和 false 表示

運算子	說明
not 或 !	邏輯非
and 或 &&	邏輯與
or	邏輯或
xor	邏輯異或

2.3.1 邏輯非（not或!）

將跟在邏輯非后面的值取反，如果not后面的運算元為0時，則所得值為1；如果運算元為非0時，所得值為0；如果運算元為null時，所得值為null

mysql> select not 3,!4,not 0,!(10-10);
+-------+----+-------+----------+
| not 3 | !4 | not 0 | !(10-10) |
+-------+----+-------+----------+
|     0 |  0 |     1 |        1 |
+-------+----+-------+----------+


【因為優先級的問題，所以!1+1回傳1，not 1+1回傳0】
mysql> select !1+1,not 1+1;
+------+---------+
| !1+1 | not 1+1 |
+------+---------+
|    1 |       0 |
+------+---------+

2.3.2 邏輯與（and或&&）

當所有運算元都為非零值并且不為null時，回傳值為1
當有一個或者多個操作為0時，回傳值為0
當運算元中又任何一個為null時，回傳值為null

mysql> select 22 and 33,44 && 0,0 && null,1 and null;
+-----------+---------+-----------+------------+
| 22 and 33 | 44 && 0 | 0 && null | 1 and null |
+-----------+---------+-----------+------------+
|         1 |       0 |         0 |       NULL |
+-----------+---------+-----------+------------+

2.3.3 邏輯或（or）

當兩個運算元都為非null值時，如果有任意一個運算元為非零值，則回傳1，否則回傳0
當有一個運算元為null時，如果另一個運算元為非零值，則回傳1，否則回傳null
如果兩個運算元均為null時，則回傳null

mysql> select 22 or 33,0 or null,null or null;
+----------+-----------+--------------+
| 22 or 33 | 0 or null | null or null |
+----------+-----------+--------------+
|        1 |      NULL |         NULL |
+----------+-----------+--------------+

2.3.4 邏輯異或（xor）

當任意一個運算元為null時，回傳值為null
對于非null的運算元，如果兩個運算元都是非0值或者都是0值，則回傳0
如果一個為0值，一個為非0值，回傳1

mysql> select 22 xor 33,0 xor 11,11 xor 0 ,1 xor null,null xor 33;
+-----------+----------+----------+------------+-------------+
| 22 xor 33 | 0 xor 11 | 11 xor 0 | 1 xor null | null xor 33 |
+-----------+----------+----------+------------+-------------+
|         0 |        1 |        1 |       NULL |        NULL |
+-----------+----------+----------+------------+-------------+

2.4 位與運算子

位與運算子是在二進制數上進行計算的運算子
位與運算會先將運算元變成二進制數，再進行位與運算（也就是兩個數的二進制數相乘）
最后再將計算結果從二進制數變回十進制數

運算子	說明
&	按位與
\|	按位或
^	按位異或
!	取反
<<	左移
>>	右移

2.4.1 按位與運算（&）

按位與運算（&）：當對應的二進制位都是1時，則運算結果為1，否則為0，
因為10轉換為二進制數為1010，15轉換為二進制數為1111，所以10 & 15結果為10

mysql> select 10 & 15;
+---------+
| 10 & 15 |
+---------+
|      10 |
+---------+

2.4.2 按位或運算（|）

當對應的二進制位有一個或兩個為1時，運算結果為1，否則為0
因為10轉換為二進制數為1010，15轉換為二進制數為1111，所以10 | 15結果為15

mysql> select 10 | 15;
+---------+
| 10 | 15 |
+---------+
|      15 |
+---------+

2.4.3 按位異或運算（^）

當對應的二進制位不相同時，運算結果為1，否則為0
因為10轉換為二進制數為1010，15轉換為二進制數為1111，所以10 ^ 15結果為5

mysql> select 10 ^ 15;
+---------+
| 10 ^ 15 |
+---------+
|       5 |
+---------+

2.4.4 按位取反（~）

是將對應的二進制數逐位反轉，即1取反后變為0,0取反后變為1
因為10轉換為二進制數為1010，15轉換為二進制數為1111，所以取反后為0101與0000，再進行求與操作后結果為0000，轉換為十進制為0

mysql> select 10 &~ 15;
+----------+
| 10 &~ 15 |
+----------+
|        0 |
+----------+

2.5 運算子的優先級

不管哪種運算子，在使用程序中都有優先級問題
運算子的優先級決定了不同的運算子在計算程序中的先后順序
級別越高的運算子會先進行計算，如果運算子的基本相同，MySQL則會按照順序從左到右依次進行計算

運算子	優先級
!	1
~	2
^	3
* / %	4
+ -	5
>> <<	6
&	7
\|	8
=、<=>、>=、> <=、<、<>、!= is、like、regexp、in	9
between、case、when then、else	10
not	11
&& and	12
or xor	13
: =	14

三、連接查詢（內、左、右）

MySQL的連接查詢通常是將來自兩個或多個表的行結合起來，基于這些表之間的共同欄位，進行資料的拼接
要先確定一個主表作為結果集，然后將其他表的行有選擇性的連接到選定的主表結果集上

3.1 實驗庫+表創建準備

mysql> create database qz1;
mysql> use qz1
mysql> CREATE TABLE qz1 (q_id int(11) DEFAULT NULL,q_name varchar(32) DEFAULT NULL,q_level int(11) DEFAULT NULL);
mysql> CREATE TABLE qz2 (w_id int(11) DEFAULT NULL,w_name varchar(32) DEFAULT NULL,w_level int(11) DEFAULT NULL);
mysql> insert into qz1(q_id, q_name, q_level) values(1, 'aaa', 15);
mysql> insert into qz1(q_id, q_name, q_level) values(2, 'bbb', 25);
mysql> insert into qz1(q_id, q_name, q_level) values(3, 'ccc', 35);
mysql> insert into qz1(q_id, q_name, q_level) values(4, 'ddd', 45);
mysql> insert into qz2(w_id, w_name, w_level) values(2, 'bbb', 25);
mysql> insert into qz2(w_id, w_name, w_level) values(3, 'ccc', 35);
mysql> insert into qz2(w_id, w_name, w_level) values(5, 'eee', 55);
mysql> insert into qz2(w_id, w_name, w_level) values(6, 'fff', 65);

mysql> select * from qz1;
+------+--------+---------+
| q_id | q_name | q_level |
+------+--------+---------+
|    1 | aaa    |      15 |
|    2 | bbb    |      25 |
|    3 | ccc    |      35 |
|    4 | ddd    |      45 |
+------+--------+---------+

mysql> select * from qz2;
+------+--------+---------+
| w_id | w_name | w_level |
+------+--------+---------+
|    2 | bbb    |      25 |
|    3 | ccc    |      35 |
|    5 | eee    |      55 |
|    6 | fff    |      65 |
+------+--------+---------+

3.2 內連接（系統默認）

MySQL中的內連接就是兩張表或多張表中同時符合某種條件的資料記錄的組合
通常在from子句中使用關鍵字inner join來連接多張表，并使用on子句設定連接條件，內連接也是系統默認的表連接，所以在from子句后可以省略inner關鍵字，只使用關鍵字 join
當同時有多個表時，也可以連續使用inner join來實作多表的內連接，但是為了更好的性能，不建議超過三個表
語法：select 欄位 from 表1 inner join 表2 on 表1.欄位 = 表2.欄位;

mysql> select q_id,q_name,q_level from qz1 q inner join qz2 w on q_id=w_id;
+------+--------+---------+
| q_id | q_name | q_level |
+------+--------+---------+
|    2 | bbb    |      25 |
|    3 | ccc    |      35 |
+------+--------+---------+

3.3 左連接（左外連接）

左連接也可以被稱為左外連接，在from子句中使用left join或者left outer join關鍵字來表示
左連接以左側表（語法中左側）為基礎表，接收左表的所有行，并用這些行與右側參考表中的記錄進行匹配，也就是說匹配左表中的所有行以及右表中符合條件的行
語法1：select * from 表1 left join 表2 on 表1.欄位 = 表2.欄位;
語法2：select * from 表1 left outer join 表2 on 表1.欄位 = 表2.欄位;

【左連接中左表的記錄將會全部展示出來，而右表只會顯示符合搜索條件的記錄，右表記錄不足的地方均展示為NULL】
mysql> select * from qz1 left join qz2 on qz1.q_id=qz2.w_id;
+------+--------+---------+------+--------+---------+
| q_id | q_name | q_level | w_id | w_name | w_level |
+------+--------+---------+------+--------+---------+
|    2 | bbb    |      25 |    2 | bbb    |      25 |
|    3 | ccc    |      35 |    3 | ccc    |      35 |
|    1 | aaa    |      15 | NULL | NULL   |    NULL |
|    4 | ddd    |      45 | NULL | NULL   |    NULL |
+------+--------+---------+------+--------+---------+

mysql> select * from qz1 left outer join qz2 on qz1.q_id=qz2.w_id;
+------+--------+---------+------+--------+---------+
| q_id | q_name | q_level | w_id | w_name | w_level |
+------+--------+---------+------+--------+---------+
|    2 | bbb    |      25 |    2 | bbb    |      25 |
|    3 | ccc    |      35 |    3 | ccc    |      35 |
|    1 | aaa    |      15 | NULL | NULL   |    NULL |
|    4 | ddd    |      45 | NULL | NULL   |    NULL |
+------+--------+---------+------+--------+---------+

3.4 右連接（右外連接）

右連接也稱為右外連接，在from子句中使用right join或者right outer join關鍵字表示
右連接跟左連接正好相反，它是以右表（語法中右側）為基礎表，用于接收右表中的所有行，并用這些記錄與左表中的行進行匹配
語法1：select * from 表1 right join 表2 on 表1.欄位 = 表2.欄位;
語法2：select * from 表1 right outer join 表2 on 表1.欄位 = 表2.欄位;

【在右連接的查詢結果中，除了符合匹配規則的行外，還包括右表中有但是左表中不匹配的行，但是這些記錄在左表中以NULL補足】
mysql> select * from qz1 right join qz2 on qz1.q_id=qz2.w_id;
+------+--------+---------+------+--------+---------+
| q_id | q_name | q_level | w_id | w_name | w_level |
+------+--------+---------+------+--------+---------+
|    2 | bbb    |      25 |    2 | bbb    |      25 |
|    3 | ccc    |      35 |    3 | ccc    |      35 |
| NULL | NULL   |    NULL |    5 | eee    |      55 |
| NULL | NULL   |    NULL |    6 | fff    |      65 |
+------+--------+---------+------+--------+---------+

mysql> select * from qz1 right outer join qz2 on qz1.q_id=qz2.w_id;
+------+--------+---------+------+--------+---------+
| q_id | q_name | q_level | w_id | w_name | w_level |
+------+--------+---------+------+--------+---------+
|    2 | bbb    |      25 |    2 | bbb    |      25 |
|    3 | ccc    |      35 |    3 | ccc    |      35 |
| NULL | NULL   |    NULL |    5 | eee    |      55 |
| NULL | NULL   |    NULL |    6 | fff    |      65 |
+------+--------+---------+------+--------+---------+

四、總結

4.1 正則

MySQL正則運算式的作用：在檢索資料庫記錄的時候，根據指定的匹配模式來匹配記錄中符合要求的特殊字串
MySQL的正則運算式使用regexp這個關鍵字來指定正則運算式的匹配模式

4.2 運算子

MySQL的運算子的作用：用于對記錄中的欄位值進行運算
MySQL的運算子共有四種（算術、比較、邏輯、位與）

4.3 連接查詢

MySQL的連接查詢作用：將來自兩個或多個表的行結合起來，基于這些表之間的共同欄位，進行資料的拼接
連接查詢分為：內查詢（系統默認）、左查詢（左外連接查詢）、右查詢（右外連接查詢）

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/279872.html

標籤：其他

上一篇：MySQL如何解決幻讀和不可重復度？

下一篇：2021-04-24

補鞋匠都能看明了的sql高階陳述句（正則、運算子（算術、比較、邏輯、位與）、連接查詢（內、左、右））

MySQL常用進階查詢

一、正則運算式

1.1 以特定字串開頭與結尾的記錄

1.2 匹配任何單個字符（以“.”代替字串中的任意一個字符）

1.3 匹配前面字符任意多次（0個或多個）

1.4 匹配前面字符至少一次（1次或多次）

1.5 匹配包含指定的字串

1.6 匹配包含或者關系的記錄（匹配p1|p2）

1.7 匹配指定字符集中的任意一個

1.8 匹配不在括號內的任何字符

1.9 匹配前面的字串n次

二、運算子（算術、比較、邏輯、位與）

2.1 算術運算

2.2 比較運算子

2.2.1 等于（=）

2.2.2 不等于（!=或<>）

2.2.3 大于、大于等于、小于、小于等于

2.2.4 判斷一個值為或者不為null（is null、is not null）

2.2.5 兩者之間（between…and…）

2.2.6 兩個或多個引數時回傳最小（least ）或最大值（greatest）

2.2.7 在或不再集合中（in，not in）

2.2.8 通配符匹配

2.3 邏輯運算子（布爾運算子）

2.3.1 邏輯非（not或!）

2.3.2 邏輯與（and或&&）

2.3.3 邏輯或（or）

2.3.4 邏輯異或（xor）

2.4 位與運算子

2.4.1 按位與運算（&）

2.4.2 按位或運算（|）

2.4.3 按位異或運算（^）

2.4.4 按位取反（~）

2.5 運算子的優先級

三、連接查詢（內、左、右）

3.1 實驗庫+表創建準備

3.2 內連接（系統默認）

3.3 左連接（左外連接）

3.4 右連接（右外連接 ）

四、總結

4.1 正則

4.2 運算子

4.3 連接查詢

3.4 右連接（右外連接）