DQL : 数据查询语言 , 用来查询数据库中表的记录
1. 运算符
MySQL所提供的运算符可以直接对表中数据或字段进行运算 , 进而实现用户的新需求 , 增强了MySQL的功能。
每种数据库都支持SQL语句 , 但是它们也都有各自支持的运算符。我们除了需要学会使用SQL语句外 ,还需要掌握各种运算符。
运算符主要在DQL语句中使用。
MySQL 支持 4 种运算符 , 分别是 :
算术运算符 : 执行算术运算 , 例如 : 加、减、乘、除等。
比较运算符 : 包括大于、小于、等于或不等于、等等。主要用于数值的比较、字符串的匹配等方面。
逻辑运算符 : 包括与、或、非和异或、等逻辑运算符。其返回值为布尔型 , 真值（1 或 true）和假值（0 或 false）。
位运算符 : 包括按位与、按位或、按位取反、按位异或、按位左移和按位右移等位运算符。位运算必须先将数据转换为补码 , 然后在根据数据的补码进行操作。运算完成后 , 将得到的值转换为原来的类型（十进制数） , 返回给用户。
1.1 算术运算符
算术运算符是SQL中最基本的运算符 , MySQL 支持的运算符包括加、减、乘、除和取余运算 , 它们是最常用、最简单的一类运算符。下表列出了这些运算符的作用和使用方法。
示例 1
创建表temp , 定义数据类型为INT的字段num , 并插入值64 , 对num值进行算术运算。
创建temp表语法如下:
CREATE TABLE temp(num INT);向字段num插入数据64 , 语法如下:
INSERT INTO temp VALUE (64);对num的值进行加法和减法运算 :
mysql> SELECT num,num+10,num-3+5,num+36.5 FROM temp;
+------+--------+---------+----------+
| num | num+10 | num-3+5 | num+36.5 |
+------+--------+---------+----------+
| 64 | 74 | 66 | 100.5 |
+------+--------+---------+----------+
1 row in set (0.01 sec)上面计算是对temp表中的num字段的值进行加法和减法的运算 , 而且由于+和-的优先级相同 , 因此先加后减或者先减后加之后的结果是相同的。
示例 2
下面对temp表中的num进行乘法、除法运算 , 运行结果如下:
mysql> SELECT num,num*2,num/2,num/3,num%3 FROM temp;
+------+-------+---------+---------+-------+
| num | num*2 | num/2 | num/3 | num%3 |
+------+-------+---------+---------+-------+
| 64 | 128 | 32.0000 | 21.3333 | 1 |
+------+-------+---------+---------+-------+
1 row in set (0.00 sec)由上面计算结果可以看出 , 对num进行除法运算时 , 由于64无法被3整除 , 因此MySQL对num/3求商的结果保存到了小数点后面四位 , 结果为21.3333 ; 64除以3的余数为1 , 因此取余运算num%3的结果为1。
对于取余运算 , 还可以使用MOD(a,b)函数 , MOD(a,b)相当于a%b , 运行结果如下 :
mysql> SELECT MOD (num,3) FROM temp;
+-------------+
| MOD (num,3) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)示例 3
数学运算中 , 除数为0的除法是没有意义的。所以在除法运算和取余运算中 , 如果除数为0 , 那么返回结果为NULL。 在除法运算和取余运算中 , 除数为 0 的运行结果如下所示 :
mysql> SELECT num,num/0,num%0 FROM temp;
+------+-------+-------+
| num | num/0 | num%0 |
+------+-------+-------+
| 64 | NULL | NULL |
+------+-------+-------+
1 row in set (0.00 sec)从上面运行结果可以看到 , 对num进行除法求商或者求余运算的结果均为NULL。
1.2 逻辑运算符
逻辑运算符又称为布尔运算符 , 用来确定表达式的真和假。MySQL 中支持的逻辑运算符如下表所示:
1.2.1 逻辑非运算 NOT/!运算符
NOT和!都是逻辑非运算符 , 返回和操作数相反的结果 , 具体语法规则为:
当操作数为
0(假)时 , 返回值为1;当操作数为非零值时 , 返回值为
0;当操作数为
NULL时 , 返回值为NULL。
【实例】分别使用非运算符NOT 或者!进行逻辑判断 , 运行结果如下 :
mysql> SELECT NOT 10,NOT(1-1),NOT-5,NOT NULL,NOT 1+1;
+--------+----------+-------+----------+---------+
| NOT 10 | NOT(1-1) | NOT-5 | NOT NULL | NOT 1+1 |
+--------+----------+-------+----------+---------+
| 0 | 1 | 0 | NULL | 0 |
+--------+----------+-------+----------+---------+
1 row in set (0.00 sec)
mysql> SELECT !10,!(1-1),!-5,!NULL,!1+1;
+-----+--------+-----+-------+------+
| !10 | !(1-1) | !-5 | !NULL | !1+1 |
+-----+--------+-----+-------+------+
| 0 | 1 | 0 | NULL | 1 |
+-----+--------+-----+-------+------+
1 row in set (0.00 sec)
mysql> SELECT !1+1;
+------+
| !1+1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)由运行结果可以看出 , NOT 1+1和! 1+1的返回值不同 , 这是因为 NOT 与 ! 的优先级不同 :
NOT的优先级低于+, 因此NOT 1+1相当于NOT(1+1), 先计算1+1, 然后再进行 NOT 运算 , 由于操作数不为 0 , 因此NOT 1+1的结果是 0；相反 ,
!的优先级别要高于+, 因此! 1+1相当于(!1)+1, 先计算!1结果为 0 , 再加 1 , 最后结果为 1。
读者在使用运算符运算时 , 一定要注意运算符的优先级 , 如果不能确定计算顺序 , 最好使用括号 , 以保证运算结果的正确。
1.2.2 逻辑与运算符 AND/&&运算符
AND和&&都是逻辑与运算符 , 具体语法规则为 :
当所有操作数都为非零值并且不为
NULL时 , 返回值为1；当一个或多个操作数为
0时 , 返回值为0;操作数中有任何一个为
NULL时 , 返回值为NULL。
【实例】分别使用与运算符AND和&&进行逻辑判断 , 运行结果如下:
mysql> SELECT 1 AND -1,1 AND 0,1 AND NULL, 0 AND NULL;
+----------+---------+------------+------------+
| 1 AND -1 | 1 AND 0 | 1 AND NULL | 0 AND NULL |
+----------+---------+------------+------------+
| 1 | 0 | NULL | 0 |
+----------+---------+------------+------------+
1 row in set (0.00 sec)
mysql> SELECT 1 && -1,1&&0,1&&NULL,0&&NULL;
+---------+------+---------+---------+
| 1 && -1 | 1&&0 | 1&&NULL | 0&&NULL |
+---------+------+---------+---------+
| 1 | 0 | NULL | 0 |
+---------+------+---------+---------+
1 row in set (0.00 sec)由结果可以看到 , AND和&&的作用相同。1 AND-1中没有0或者NULL 所以返回值为 1；1 AND 0中有操作数 0 , 所以返回值为0 ; 1 AND NULL虽然有NULL , 所以返回值为NULL。
注意 :
AND运算符可以有多个操作数 , 但要注意多个操作数运算时 ,AND两边一定要使用空格隔开 , 不然会影响结果的正确性。
1.2.3 逻辑或运算符 OR/||运算符
OR和||都是逻辑或运算符 , 具体语法规则为 :
当两个操作数都为非
NULL值时 , 如果有任意一个操作数为非零值 , 则返回值为1, 否则结果为0;当有一个操作数为
NULL时 , 如果另一个操作数为非零值 , 则返回值为1, 否则结果为NULL;假如两个操作数均为
NULL时 , 则返回值为NULL。
【实例】分别使用或运算符OR和|| 进行逻辑判断 , 运行结果如下:
mysql> SELECT 1 OR -1 OR 0,1 OR 2,1 OR NULL, 0 OR NULL, NULL OR NULL;
+--------------+--------+-----------+-----------+--------------+
| 1 OR -1 OR 0 | 1 OR 2 | 1 OR NULL | 0 OR NULL | NULL OR NULL |
+--------------+--------+-----------+-----------+--------------+
| 1 | 1 | 1 | NULL | NULL |
+--------------+--------+-----------+-----------+--------------+
1 row in set (0.00 sec)
mysql> SELECT 1 || -1 || 0,1||2,1||NULL,0||NULL,NULL||NULL;
+--------------+------+---------+---------+------------+
| 1 || -1 || 0 | 1||2 | 1||NULL | 0||NULL | NULL||NULL |
+--------------+------+---------+---------+------------+
| 1 | 1 | 1 | NULL | NULL |
+--------------+------+---------+---------+------------+
1 row in set (0.00 sec)由结果可以看到 , OR和||的作用相同。下面是对各个结果的解析:
1 OR -1 OR 0含有0, 但同时包含有非0的值1和-1, 所以返回结果为1;1 OR 2中没有操作数0, 所以返回结果为1;1 OR NULL虽然有NULL, 但是有操作数1,所以返回结果为1;0 OR NULL中没有非0值 , 并且有NULL, 所以返回值为NULL;NULL OR NULL中只有NULL, 所以返回值为NULL。
1.2.4 异或运算 XOR 运算符
XOR表示逻辑异或 , 具体语法规则为(相同为1,不同为0) :
当任意一个操作数为
NULL时 ,返回值为NULL;对于非
NULL的操作数 , 如果两个操作数都是非0值或者都是0值 , 则返回值为0;如果一个为
0值 , 另一个为非0值 , 返回值为1。
a⊕b = (¬a ∧ b) ∨ (a ∧¬b) 【实例】使用异或运算符XOR进行逻辑判断 , SQL语句如下 :
mysql> SELECT 1 XOR 1,0 XOR 0,1 XOR 0,1 XOR NULL,1 XOR 1 XOR 1;
+---------+---------+---------+------------+---------------+
| 1 XOR 1 | 0 XOR 0 | 1 XOR 0 | 1 XOR NULL | 1 XOR 1 XOR 1 |
+---------+---------+---------+------------+---------------+
| 0 | 0 | 1 | NULL | 1 |
+---------+---------+---------+------------+---------------+
1 row in set (0.00 sec)由结果可以看到 : 1 XOR 1和0 XOR 0中运算符两边的操作数都为非零值 ,或者都是零值 , 因此返回0 ; 1 XOR 0中两边的操作数 , 一个为0值 , 另一个为非0值 , 所以返回值为1 ; 1 XOR NULL中有一个操作数为NULL , 所以返回值为NULL ; 1 XOR 1 XOR 1中有多个操作数 , 运算符相同 , 因此运算顺序从左到右依次计算 , 1 XOR 1的结果为0 , 再与1进行异或运算 , 所以返回值为1。
提示 :
a XOR b的计算等同于(a AND (NOT b))或者((NOT a) AND b)。
1.3 比较运算符
当使用SELECT语句进行查询时 , MySQL允许用户对表达式的左边操作数和右边操作数进行比较 , 比较结果为真 ,则返回1 , 为假则返回0 , 比较结果不确定则返回NULL。
MySQL支持的比较运算符如下表所示:
比较运算符可以用于比较数字、字符串和表达式的值。注意 , 字符串的比较是不区分大小写的。
1.3.1等于运算(=)
=运算符用来比较两边的操作数是否相等 , 相等的话返回1 , 不相等的话返回0。
具体的语法规则如下 :
若有一个或两个操作数为
NULL, 则比较运算的结果为NULL。若两个操作数都是字符串 , 则按照字符串进行比较。
若两个操作数均为整数 ,则按照整数进行比较。
若一个操作数为字符串 , 另一个操作数为数字 , 则
MySQL可以自动将字符串转换为数字。
注意 :
NULL不能用于=比较。
【实例】使用 = 进行相等判断 , SQL语句如下 :
mysql> SELECT 1=0,'2'=2,2=2,'0.02'=0,'b'='b',(1+3)=(2+2),NULL=null;
+-----+-------+-----+----------+---------+-------------+-----------+
| 1=0 | '2'=2 | 2=2 | '0.02'=0 | 'b'='b' | (1+3)=(2+2) | NULL=null |
+-----+-------+-----+----------+---------+-------------+-----------+
| 0 | 1 | 1 | 0 | 1 | 1 | NULL |
+-----+-------+-----+----------+---------+-------------+-----------+
1 row in set (0.01 sec)对运行结果的分析 :
2=2和'2' =2的返回值相同 ,都为1, 因为在进行判断时 ,MySQL自动进行了转换 , 把字符'2'转换成了数字2。'b'='b'为相同的字符比较 , 因此返回值为1。表达式
1+3和表达式2+2的结果都为4, 因此结果相等 , 返回值为1;由于
=不能用于空值NULL的判断 , 因此NULL=null的返回值为NULL。
1.3.2 安全等于运算符(<=>)
<=>操作符和=操作符类似 , 不过<=>可以用来判断NULL值 , 具体语法规则为:
当两个操作数均为
NULL时 , 其返回值为1而不为NULL;而当一个操作数为
NULL时 , 其返回值为0而不为NULL。
【实例】使用<=> 进行相等的判断 , SQL语句如下:
mysql> SELECT 1<=>0,'2'<=>2,2<=>2,'0.02'<=>0,'b'<=>'b',(1+3) <=> (2+1),NULL<=>NULL;
+-------+---------+-------+------------+-----------+-----------------+-------------+
| 1<=>0 | '2'<=>2 | 2<=>2 | '0.02'<=>0 | 'b'<=>'b' | (1+3) <=> (2+1) | NULL<=>NULL |
+-------+---------+-------+------------+-----------+-----------------+-------------+
| 0 | 1 | 1 | 0 | 1 | 0 | 1 |
+-------+---------+-------+------------+-----------+-----------------+-------------+
1 row in set (0.003 sec)
由结果可以看到 , <=>在执行比较操作时和= 的作用是相似的 , 唯一的区别是<=>可以用来对NULL进行判断 ,两者都为NULL时返回值为 1。
1.3.3 不等于运算符(<> 或者 !=)
与=的作用相反 , <>和!=用于判断数字、字符串、表达式是否不相等。对于<>和!= , 如果两侧操作数不相等 ,返回值为1 , 否则返回值为0 ; 如果两侧操作数有一个是NULL , 那么返回值也是NULL。
【实例】使用<>和!=进行不相等的判断 , SQL语句如下:
mysql> SELECT 'good'<>'god',1<>2,4!=4,5.5!=5,(1+3)!=(2+1),NULL<>NULL;
+---------------+------+------+--------+--------------+------------+
| 'good'<>'god' | 1<>2 | 4!=4 | 5.5!=5 | (1+3)!=(2+1) | NULL<>NULL |
+---------------+------+------+--------+--------------+------------+
| 1 | 1 | 0 | 1 | 1 | NULL |
+---------------+------+------+--------+--------------+------------+
1 row in set (0.00 sec)由结果可以看到 , 两个不等于运算符作用相同 , 都可以进行数字、字符串、表达式的比较判断。
1.3.4 小于等于运算符（<=）
<=是小于等于运算符 , 用来判断左边的操作数是否小于或者等于右边的操作数 ; 如果小于或者等于 , 返回值为1 ,否则返回值为0 ; 如果两侧操作数有一个是NULL , 那么返回值也是NULL。
【实例】使用<=进行比较判断 , SQL语句如下:
mysql> SELECT 'good'<='god',1<=2,4<=4,5.5<=5,(1+3)<=(2+1),NULL<=NULL;
+---------------+------+------+--------+--------------+------------+
| 'good'<='god' | 1<=2 | 4<=4 | 5.5<=5 | (1+3)<=(2+1) | NULL<=NULL |
+---------------+------+------+--------+--------------+------------+
| 0 | 1 | 1 | 0 | 0 | NULL |
+---------------+------+------+--------+--------------+------------+
1 row in set (0.00 sec)由结果可以看到:
左边操作数小于或者等于右边时 , 返回值为
1, 例如4<=4;当左边操作数大于右边时 , 返回值为
0, 例如“good”第3个位置的"o"字符在字母表中的顺序大于"god"中的第3个位置的"d"字符 , 因此返回值为0;同样 , 比较
NULL值时返回NULL。
1.3.5 小于运算符(<)
<是小于运算符 , 用来判断左边的操作数是否小于右边的操作数 ; 如果小于 , 返回值为1 , 否则返回值为0 ; 如果两侧操作数有一个是NULL , 那么返回值也是NULL。
【实例】使用<进行比较判断 , SQL语句如下 :
mysql> SELECT 'good'<'god',1<2,4<4,5.5<5,(1+3)<(2+1),NULL<NULL ;
+--------------+-----+-----+-------+-------------+-----------+
| 'good'<'god' | 1<2 | 4<4 | 5.5<5 | (1+3)<(2+1) | NULL<NULL |
+--------------+-----+-----+-------+-------------+-----------+
| 0 | 1 | 0 | 0 | 0 | NULL |
+--------------+-----+-----+-------+-------------+-----------+
1 row in set (0.02 sec)
MariaDB> SELECT 'gocd'<'god' ,1<2 ,4<4 ,5.5<5 ,(1+3)<(2+1) ,NULL<NULL ;
+--------------+-----+-----+-------+-------------+-----------+
| 'gocd'<'god' | 1<2 | 4<4 | 5.5<5 | (1+3)<(2+1) | NULL<NULL |
+--------------+-----+-----+-------+-------------+-----------+
| 1 | 1 | 0 | 0 | 0 | NULL |
+--------------+-----+-----+-------+-------------+-----------+
1 row in set (0.000 sec)由结果可以看到:
当左边操作数小于右边时 , 返回值为
1, 例如1<2;当左边操作数大于右边时 , 返回值为
0, 例如"good"第3个位置的"o"字符在字母表中的顺序大于"god"中的第3个位置的"d"字符 , 因此返回值为0;同样 , 比较
NULL值时返回NULL。
1.3.6 大于等于运算符(>=)
>= 是大于等于运算符 , 用来判断左边的操作数是否大于或者等于右边的操作数 ; 如果大于或者等于 , 返回值为1 , 否则返回值为0 ; 如果两侧操作数有一个是NULL , 那么返回值也是NULL。
【实例】使用>=进行比较判断 , SQL语句如下:
mysql> SELECT 'good'>='god' , 1>=2 , 4>=4 , 5.5>=5 , (1+3)>=(2+1) , NULL>=NULL;
+---------------+------+------+--------+--------------+------------+
| 'good'>='god' | 1>=2 | 4>=4 | 5.5>=5 | (1+3)>=(2+1) | NULL>=NULL |
+---------------+------+------+--------+--------------+------------+
| 1 | 0 | 1 | 1 | 1 | NULL |
+---------------+------+------+--------+--------------+------------+
1 row in set (0.00 sec)由结果可以看到 :
左边操作数大于或者等于右边时 , 返回值为
1, 例如4>=4;当左边操作数小于右边时 , 返回值为
0, 例如1>=2;同样 , 比较
NULL值时返回NULL。
1.3.7 大于运算符(>)
> 是大于运算符 , 用来判断左边的操作数是否大于右边的操作数 ; 如果大于 , 返回值为1 , 否则返回值为0 ; 如果两侧操作数有一个是NULL , 那么返回值也是NULL。
【实例】使用>进行比较判断 , SQL语句如下 :
mysql> SELECT 'good'>'god' , 1>2 , 4>4 , 5.5>5 , (1+3)>(2+1) , NULL>NULL；
+--------------+-----+-----+-------+-------------+-----------+
| 'good'>'god' | 1>2 | 4>4 | 5.5>5 | (1+3)>(2+1) | NULL>NULL |
+--------------+-----+-----+-------+-------------+-----------+
| 1 | 0 | 0 | 1 | 1 | NULL |
+--------------+-----+-----+-------+-------------+-----------+
1 row in set (0.00 sec)由结果可以看到 :
左边操作数大于右边时 , 返回值为
1, 例如5.5>5;当左边操作数小于右边时 , 返回
0, 例如1>2;同样 ,比较
NULL值时返回NULL。
1.3.8 IS NULL(ISNULL) 和 IS NOT NULL 运算符
IS NULL或 ISNULL运算符用来检测一个值是否为NULL , 如果为NULL , 返回值为1 , 否则返回值为0。ISNULL 可以认为是IS NULL的简写 , 去掉了一个空格而已 , 两者的作用和用法都是完全相同的。
IS NOT NULL运算符用来检测一个值是否为非NULL , 如果是非NULL , 返回值为1 , 否则返回值为0。
【实例】使用IS NULL、ISNULL和IS NOT NULL判断NULL值和非NULL值,SQL语句如下:
mysql> SELECT NULL IS NULL , ISNULL(NULL) , ISNULL(10) , 10 IS NOT NULL;
+--------------+--------------+------------+----------------+
| NULL IS NULL | ISNULL(NULL) | ISNULL(10) | 10 IS NOT NULL |
+--------------+--------------+------------+----------------+
| 1 | 1 | 0 | 1 |
+--------------+--------------+------------+----------------+
1 row in set (0.01 sec)由结果可以看到 , IS NULL和ISNULL的作用相同 , 只是写法略有不同。ISNULL和IS NOT NULL的返回值正好相反。
1.3.9 BETWEEN AND 运算符
BETWEEN AND运算符用来判断表达式的值是否位于两个数之间 , 或者说是否位于某个范围内 , 它的语法格式如下:
expr BETWEEN min AND maxexpr表示要判断的表达式 , min表示最小值, max表示最大值。如果expr 大于等于min并且小于等于max , 那么返回值为1 , 否则返回值为0。
【实例】使用BETWEEN AND 进行值区间判断 , 输入SQL语句如下:
mysql> SELECT 4 BETWEEN 2 AND 5,4 BETWEEN 4 AND 6,12 BETWEEN 9 AND 10;
+-------------------+-------------------+---------------------+
| 4 BETWEEN 2 AND 5 | 4 BETWEEN 4 AND 6 | 12 BETWEEN 9 AND 10 |
+-------------------+-------------------+---------------------+
| 1 | 1 | 0 |
+-------------------+-------------------+---------------------+
1 row in set (0.00 sec)由结果可以看到:
4在端点值区间内或者等于其中一个端点值 ,BETWEEN AND表达式返回值为1;12并不在指定区间内 , 因此返回值为0;对于字符串类型的比较 , 按字母表中字母顺序进行比较 , "
x"不在指定的字母区间内 , 因此返回值为0,而"b" 位于指定字母区间内 , 因此返回值为1。
1.4 位运算符
所谓位运算 , 就是按照内存中的比特位(Bit)进行操作 , 这是计算机能够支持的最小单位的运算。程序中所有的数据在内存中都是以二进制形式存储的 , 位运算就是对这些二进制数据进行操作。
位运算一般用于操作整数 , 对整数进行位运算才有实际的意义。整数在内存中是以补码形式存储的 , 正数的补码形式和原码形式相同 , 而负数的补码形式和它的原码形式是不一样的 , 这一点大家要特别注意 ; 这意味着 , 对负数进行位运算时 , 操作的是它的补码 , 而不是它的原码。
MySQL中的整数字面量(常量整数 , 也就是直接书写出来的整数)默认以8个字节(Byte)来表示 , 也就是64位( Bit )。
原码 : 最简单的机器数表示法 , 用最高位表示符号位 , 其他位存放该数的二进制的绝对值。最高位为1则为负数,为0则为正数
反码 : 正数的反码还是等于原码；负数的反码就是它的原码除符号位外 , 按位取反。
补码 : 正数的补码等于它的原码；负数的补码等于反码+1
例如 , 5的二进制形式为: 0000 0000 ... 0000 0101 , 省略号部分都是0 ,101前面总共有61个0。
注意 :为了方便大家阅读 , 本节在介绍正数的补码时 , 省略了前面的
0。
MySQL支持6种位运算符 , 如下表所示。
位运算中的&、|、~和逻辑运算中的&&、||、!非常相似。
1.4.1 位或运算符 |
参与|运算的两个二进制位有一个为1时 , 结果就为1 , 两个都为0时结果才为0。例如1|1结果为 1 , 0|0结果为0 , 1|0结果为1 , 这和逻辑运算中的||非常类似。
使用位或运算符进行正数运算 , SQL语句如下:
mysql> SELECT 10|15,9|4|2;
+-------+-------+
| 10|15 | 9|4|2 |
+-------+-------+
| 15 | 15 |
+-------+-------+
1 row in set (0.00 sec)10 的补码为1010 , 15的补码为1111 , 按位或运算之后 , 结果为 1111 , 即整数15 ; 9的补码为1001 , 4的补码为0100 , 2的补码为0010 , 按位或运算之后 , 结果为 1111 , 即整数15。
使用位或运算符进行负数运算 , SQL语句如下:
mysql> SELECT -7|-1;
+----------------------+
| -7|-1 |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)-7的补码为60个'1'加1001 , -1 的补码为64个'1' , 按位或运算之后 , 结果为64个'1' ,即整数 18446744073709551615。
可以发现 , 任何数和-1进行位或运算时 , 最终结果都是-1的十进制数。
1.4.2 位与运算符 &
参与&运算的两个二进制位都为 1 时 ,结果就为1 , 否则为0。例如1|1结果为 1 , 0|0结果为0 , 1|0结果为 0 , 这和逻辑运算中的&&非常类似。
使用位与运算符进行正数运算 , SQL语句如下:
mysql> SELECT 10 & 15,9 & 4 & 2;
+---------+-----------+
| 10 & 15 | 9 & 4 & 2 |
+---------+-----------+
| 10 | 0 |
+---------+-----------+
1 row in set (0.00 sec) 10的补码为1010 , 15的补码为1111 , 按位与运算之后 , 结果为1010 , 即整数10 ; 9的补码为1001 , 4的补码为 0100 , 2的补码为0010 , 按位与运算之后 , 结果为0000 , 即整数0。
使用位与运算符进行负数运算 , SQL语句如下:
mysql> SELECT -7&-1;
+----------------------+
| -7&-1 |
+----------------------+
| 18446744073709551609 |
+----------------------+
1 row in set (0.01 sec)-7 的补码为60个'1'加1001 , -1的补码为64个'1' , 按位与运算之后 , 结果为60个'1'加1001 , 即整数 18446744073709551609。
可以发现 , 任何数和-1进行位与运算时 , 最终结果都为任何数本身的十进制数。
1.4.3 位异或运算符 ^
参与^运算的两个二进制位不同时 , 结果为1 , 相同时 , 结果为0。例如1|1结果为0 , 0|0结果为0 , 1|0结果为1。
使用位异或运算符进行正数运算 , SQL 语句如下 :
mysql> SELECT 10^15,1^0,1^1;
+-------+-----+-----+
| 10^15 | 1^0 | 1^1 |
+-------+-----+-----+
| 5 | 1 | 0 |
+-------+-----+-----+
1 row in set (0.00 sec) 10的补码为1010 , 15的补码为1111 , 按位异或运算之后 , 结果为0101 , 即整数5 ; 1的补码为0001 , 0的补码为0000 , 按位异或运算之后 , 结果为0001 ; 1 和1本身二进制位完全相同 , 因此结果为0。
使用位异或运算符进行负数运算 , SQL语句如下 :
mysql> SELECT -7^-1;
+-------+
| -7^-1 |
+-------+
| 6 |
+-------+
1 row in set (0.00 sec) -7的补码为60个'1'加 1001 , -1 的补码为 64 个‘1’ , 按位异或运算之后 , 结果为 110 , 即整数 6。
1.4.4 位左移运算符 <<
位左移是按指定值的补码形式进行左移 , 左移指定位数之后 , 左边高位的数值被移出并丢弃 , 右边低位空出的位置用0补齐。
位左移的语法格式为:
expr << n其中 , n 指定值 expr 要移位的位数 , n 必须为非负数。
使用位左移运算符进行正数计算 , SQL 语句如下 :
mysql> SELECT 1<<2,4<<2;
+------+------+
| 1<<2 | 4<<2 |
+------+------+
| 4 | 16 |
+------+------+
1 row in set (0.00 sec) 1 的补码为 0000 0001 , 左移两位之后变成 0000 0100 , 即整数 4；4 的补码为 0000 0100 , 左移两位之后变成 0001 0000 , 即整数 16。
使用位左移运算符进行负数计算 , SQL 语句如下 :
mysql> SELECT -7<<2;
+----------------------+
| -7<<2 |
+----------------------+
| 18446744073709551588 |
+----------------------+
1 row in set (0.00 sec)-7的补码为60个'1'加 1001 ,左移两位之后变成56个'1'加1110 0100 , 即整数18446744073709551588。
1.4.5 位右移运算符 >>
位右移是按指定值的补码形式进行右移 , 右移指定位数之后 , 右边低位的数值被移出并丢弃 , 左边高位空出的位置用 0 补齐。
位右移语法格式为:
expr >> n其中 , n指定值expr要移位的位数 , n必须为非负数。
使用位右移运算符进行正数运算 , SQL语句如下 :
mysql> SELECT 1>>1,16>>2;
+------+-------+
| 1>>1 | 16>>2 |
+------+-------+
| 0 | 4 |
+------+-------+
1 row in set (0.00 sec)1的补码为0000 0001 , 右移1位之后变成0000 0000 ,即整数0 ; 16的补码为0001 0000 , 右移两位之后变成0000 0100 , 即整数4。
使用位右移运算符进行负数运算 , SQL 语句如下 :
mysql> SELECT -7>>2;
+---------------------+
| -7>>2 |
+---------------------+
| 4611686018427387902 |
+---------------------+
1 row in set (0.00 sec)-7 的补码为60个'1'加1001 , 右移两位之后变成 0011加56个'1'加1110 , 即整数4611686018427387902。
1.4.6 位取反运算符 ~
位取反是将参与运算的数据按对应的补码进行反转 , 也就是做NOT操作 即1取反后变0 , 0取反后变为1。
下面看一个经典的取反例子 , 对 1 进行位取反运算 , 具体如下所示 :
mysql> SELECT ~1,~18446744073709551614;
+----------------------+-----------------------+
| ~1 | ~18446744073709551614 |
+----------------------+-----------------------+
| 18446744073709551614 | 1 |
+----------------------+-----------------------+
1 row in set (0.00 sec)常量1的补码为63个'0'加 1 个'1' , 位取反后就是63个'1'加一个'0', 转换为二进制后就是18446744073709551614。
可以使用BIN() 函数查看 1 取反之后的结果 , BIN() 函数的作用是将一个十进制数转换为二进制数, SQL语句如下:
mysql> SELECT BIN(~1);
+------------------------------------------------------------------+
| BIN(~1) |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)1 的补码表示为最右边位为 1 , 其他位均为0, 取反操作之后 , 除了最低位 其他位均变为1。
使用位取反运算符进行运算 , SQL 语句如下 :
mysql> SELECT 5 & ~1;
+--------+
| 5 & ~1 |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec) 逻辑运算5&~1 中 , 由于位取反运算符~的级别高于位与运算符& , 因此先对1 进行取反操作 , 结果为63个1加一个0 , 然后再与整数5 进行与运算 , 结果为0100 , 即整数 4。
1.5 运算符优先级
运算符的优先级决定了不同的运算符在表达式中计算的先后顺序 , 下表列出了 MySQL 中的各类运算符及其优先级。
可以看出 , 不同运算符的优先级是不同的。一般情况下 , 级别高的运算符优先进行计算 , 如果级别相同 , MySQL 按表达式的顺序从左到右依次计算。
另外 , 在无法确定优先级的情况下 , 可以使用圆括号“()”来改变优先级 , 并且这样会使计算过程更加清晰。
2. 单表操作
语法
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]说明
别名 , 表名和字段均可以支持别名
where子句: 指明过滤条件以实现"选择"的功能过滤条件: 布尔型表达式
算法操作符:
+,-,*,/,%比较操作符:
=,<=>(相等或都为空) ,<>,!=(非标准SQL),>,>=,<,<=BETWEEN min_num AND max_numIN(element1,element2,...)IS NULLIS NOT NULLDISTINCT去除重复行 , 范例:SELECT DISTINCT gender FROM students;LIKE:%,任意长度的任意字符;_,任意单个字符。RLIKE: 正则表达式,索引失效,不建议使用。REGEXP: 匹配字符串可用正则表达式书写模式, 同上逻辑操作符 :
NOT,AND,OR,XOR
GROUP: 根据指定的条件把查询结果进行“分组"以用于做"聚合"运算常见聚合函数:
avg(),max(),min(),count(),sum()HAVING: 对分组聚合运算后的结果指定过滤条件一旦分组
group by,select语句后只跟分组的字段,聚合函数。ORDER BY:根据指定的字段对查询结果进行排序升序 :
ASC降序:
DESClimit [[offset,]row_count]: 对查询的结果进行输出行数数量限制offset: 表示从哪行开始查。行是从0开始计算row_count: 一共要查几行对查询结果中的数据请求施加”锁“
FOR UPDATE:写锁, 独占或排它锁,只有一个读和写操作LOCK IN SHARE MODE: 读锁,共享锁,同时多个读操作。
范例 : 字段别名
MariaDB [hellodb]> select stuid 学员ID, name as 姓名, gender 性别 from students;
+----------+---------------+--------+
| 学员ID | 姓名 | 性别 |
+----------+---------------+--------+
| 1 | Shi Zhongyu | M |
| 2 | Shi Potian | M |
| 3 | Xie Yanke | M |
| 4 | Ding Dian | M |
| 5 | Yu Yutong | M |
| 6 | Shi Qing | M |
| 7 | Xi Ren | F |
| 8 | Lin Daiyu | F |
| 9 | Ren Yingying | F |
| 10 | Yue Lingshan | F |
| 11 | Yuan Chengzhi | M |
| 12 | Wen Qingqing | F |
| 13 | Tian Boguang | M |
| 14 | Lu Wushuang | F |
| 15 | Duan Yu | M |
| 16 | Xu Zhu | M |
| 17 | Lin Chong | M |
| 18 | Hua Rong | M |
| 19 | Xue Baochai | F |
| 20 | Diao Chan | F |
| 21 | Huang Yueying | F |
| 22 | Xiao Qiao | F |
| 23 | Ma Chao | M |
| 24 | Xu Xian | M |
| 25 | Sun Dasheng | M |
+----------+---------------+--------+
25 rows in set (0.00 sec)2.1 简单查询
范例: 简单查询
#插入数据,
INSERT INTO students VALUES(1,'tom' , 'm'),(2,'alice','f');
MariaDB [hellodb]> INSERT INTO students VALUES(26,'tom',26,'F',NULL,NULL),(27,'alice',33,'M',NULL,NULL);
#指定列插入数据
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
MariaDB [hellodb]> DESC students ;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
#查看StuID<3的内容
MariaDB [hellodb]> SELECT * FROM students WHERE StuID < 3 ;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
#查看性别为m的行
MariaDB [hellodb]> SELECT * FROM students WHERE gender='m';
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
#按照名字进行排序
MariaDB [hellodb]> select * from students order by name ;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
#按照名字name进行排序,并查看最开始两行
MariaDB [hellodb]> select * from students order by name limit 2;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+-----------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
#按照名字name进行排序,跨过第一行,并查看接下来两行
MariaDB [hellodb]> select * from students order by name limit 1,2;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 15 | Duan Yu | 19 | M | 4 | NULL |
+-------+-----------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)范例 : SQL 注入攻击
select * from user where name='admin' and password='' or '1'='1';
select * from user where name='admin' and password='' or '1=1';
select * from user where name='admin'; -- ' and password='testpasswrod';
select * from user where name='admin'; # ' and password= 'testpassword';2.2 筛选判断
范例: 判断是否为null
MariaDB [hellodb]> SELECT * FROM students where classid is null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> SELECT * FROM students where classid <=> null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> select * FROM students where classid is not null;范例 : 安全等于运算符
MariaDB [(none)]> SELECT 1<=>0,'2'<=>2,2<=>2,'0.02'<=>0,'b'<=>'b',(1+3) <=> (2+1),NULL<=>NULL;
+-------+---------+-------+------------+-----------+-----------------+-------------+
| 1<=>0 | '2'<=>2 | 2<=>2 | '0.02'<=>0 | 'b'<=>'b' | (1+3) <=> (2+1) | NULL<=>NULL |
+-------+---------+-------+------------+-----------+-----------------+-------------+
| 0 | 1 | 1 | 0 | 1 | 0 | 1 |
+-------+---------+-------+------------+-----------+-----------------+-------------+
1 row in set (0.00 sec)
# <=> 在执行比较操作时和 = 的作用是相似的 , 唯一的区别是 <=> 可以用来对 NULL 进行判断,两者都为 NULL 时返回值为 1。范例 : 通过范围筛选
#查看stuid <=2 和stuid>=4 的行
MariaDB [hellodb]> select * from students where stuid >=2 and stuid <=4;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
#查看stuid在2到4之间的内容
MariaDB [hellodb]> select * from students WHERE stuid BETWEEN 2 AND 4 ;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
#查看班级ID在1,3,5中的内容
MariaDB [hellodb]> select * from students where classid in (1,3,5);
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+--------------+-----+--------+---------+-----------+
9 rows in set (0.00 sec)
#查看classid不在1,3,5的行
MariaDB [hellodb]> select * from students where classid not in (1,3,5);
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
14 rows in set (0.00 sec)范例 : 通过匹配方式筛选
#查看名字以t开头的行
MariaDB [hellodb]> SELECT * FROM students WHERE name LIKE 't%';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 13 | Tian Boguang | 33 | M | 2 | NULL |
+-------+--------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
#按照正则表达式的方式匹配内容, 查看名字含有l后者o字母的行
MariaDB [hellodb]> SELECT * FROM students WHERE name RLIKE '.*[lo].*';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+--------------+-----+--------+---------+-----------+
13 rows in set (0.00 sec)范例 : 时间字段进行过滤查询
MariaDB [hellodb]> create table testdate (id int ,date timestamp DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.07 sec)
MariaDB [hellodb]> insert testdate (id) values(1);
MariaDB [hellodb]> insert testdate (id) values(2);
MariaDB [hellodb]> insert testdate (id) values(3);
MariaDB [hellodb]> insert testdate (id) values(4);
MariaDB [hellodb]> insert testdate (id) values(5);
MariaDB [hellodb]> select * from testdate;
+------+---------------------+
| id | date |
+------+---------------------+
| 1 | 2022-03-03 17:48:41 |
| 2 | 2022-03-03 17:48:44 |
| 3 | 2022-03-03 17:48:46 |
| 4 | 2022-03-03 17:48:59 |
| 5 | 2022-03-03 17:49:01 |
+------+---------------------+
5 rows in set (0.00 sec)
MariaDB [hellodb]> select * from testdate where date between '2022-03-03 17:48:40' and '2022-03-03 17:49:00';
+------+---------------------+
| id | date |
+------+---------------------+
| 1 | 2022-03-03 17:48:41 |
| 2 | 2022-03-03 17:48:44 |
| 3 | 2022-03-03 17:48:46 |
| 4 | 2022-03-03 17:48:59 |
+------+---------------------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select * from testdate where date >= '2022-03-03 17:48:40' and date <= '2022-03-03 17:49:00';
+------+---------------------+
| id | date |
+------+---------------------+
| 1 | 2022-03-03 17:48:41 |
| 2 | 2022-03-03 17:48:44 |
| 3 | 2022-03-03 17:48:46 |
| 4 | 2022-03-03 17:48:59 |
+------+---------------------+
4 rows in set (0.00 sec)2.3 去重
2.3.1 语法
在 SELECT 语句中使用 DISTINCT 关键字会返回一个没有重复记录行的结果集。 DISTINCT 的用法如下 :
SELECT DISTINCT
columns_list
FROM
table_name说明 :
DISTINCT关键字位于SELECT关键字的后面。columns_list指定要查询的字段列表 , 也是DISTINCT评估记录行是否唯一的字段。columns_list可以是一个字段 , 也可以是多个字段。columns_list也可以是*。
范例 : 去重
MariaDB [hellodb]> SELECT distinct gender FROM students;
+--------+
| gender |
+--------+
| M |
| F |
+--------+
2 rows in set (0.00 sec)2.3.2 DISTINCT 单个字段
姓氏重复是一个很正常的现象。我们先使用 SELECT 语句检索以下 actor 表中的姓氏。
SELECT last_name FROM actor;+--------------+
| last_name |
+--------------+
| AKROYD |
| AKROYD |
| AKROYD |
| ALLEN |
| ALLEN |
...
| ZELLWEGER |
| ZELLWEGER |
+--------------+
200 rows in set (0.01 sec)然后我们使用 DISTINCT 删除重复的数据 , 返回唯一的姓氏列表 :
SELECT DISTINCT last_name FROM actor;+--------------+
| last_name |
+--------------+
| AKROYD |
| ALLEN |
| ASTAIRE |
| BACALL |
| BAILEY |
...
| ZELLWEGER |
+--------------+
121 rows in set (0.00 sec)我们可以看到输出的结果集中已经没有了重复数据。结果集的行数也从原来的 200 行变成了 121 行。
2.3.3 DISTINCT 多个字段
DISTINCT 还可以清除多个字段的重复值。当指定多个字段值时 , DISTINCT 使用多个字段组合确定记录行的唯一性。
现实中 , 我们不但存在姓氏重复的情况 , 名字也可能重复。我们检索 actor 表中的名字和姓氏 :
SELECT last_name, first_name FROM actor;+--------------+-------------+
| last_name | first_name |
+--------------+-------------+
| GUINESS | PENELOPE |
| WAHLBERG | NICK |
| CHASE | ED |
| DAVIS | JENNIFER |
| LOLLOBRIGIDA | JOHNNY |
| NICHOLSON | BETTE |
...
| TEMPLE | THORA |
+--------------+-------------+
200 rows in set (0.01 sec)然后我们使用 DISTINCT 删除重复的数据 :
SELECT DISTINCT last_name, first_name FROM actor;+--------------+-------------+
| last_name | first_name |
+--------------+-------------+
| GUINESS | PENELOPE |
| WAHLBERG | NICK |
| CHASE | ED |
| DAVIS | JENNIFER |
| LOLLOBRIGIDA | JOHNNY |
...
| TEMPLE | THORA |
+--------------+-------------+
199 rows in set (0.01 sec)我们可以看到输出结果集的行数也从原来的 200 行变成了 199 行。这说明原来的结果集中有一行重复记录。我们通过以下的分组汇总语句也能证明这一点 :
SELECT last_name, first_name, COUNT(*)
FROM actor
GROUP BY last_name , first_name
HAVING COUNT(*) > 1;+-----------+------------+----------+
| last_name | first_name | COUNT(*) |
+-----------+------------+----------+
| DAVIS | SUSAN | 2 |
+-----------+------------+----------+
1 row in set (0.00 sec)注意: 上面的 SQL 语句使用了 GROUP BY, HAVING, COUNT() 对数据进行了分组汇总。
2.3.4 DISTINCT与NULL
当 DISTINCT 遇到 NULL 值时 , 只保留一个 NULL 值。因为 DISTINCT 认为所有的 NULL 值都是相同的 , 这与字段的类型无关。
例如下面的 SQL 使用返回多行 NULL 记录 :
SELECT *
FROM (
SELECT NULL
UNION ALL
SELECT NULL
UNION ALL
SELECT NULL
) t;+------------+
| NULL |
+------------+
| NULL |
| NULL |
| NULL |
+------------+
3 rows in set (0.00 sec)当使用 DISTINCT 之后 :
SELECT DISTINCT *
FROM (
SELECT NULL
UNION ALL
SELECT NULL
UNION ALL
SELECT NULL
) t;+------------+
| NULL |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)本例使用 UNION关键字模拟包含多个 NULL 记录值的记录集。
2.4 分组查询
GROUP BY 子句用于将结果集根据指定的字段或者表达式进行分组。在MySQL中，GROUP BY语句用于结合聚合函数（如SUM、COUNT、AVG、MIN、MAX等）对数据进行分组统计。
什么是分组查询？
将查询结果按照1个或多个字段进行分组 , 字段值相同的为一组其实就是按照某一列进行分类
GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里。GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。分组使用
GROUP BY子句是SELECT语句的可选子句。GROUP BY子句语法如下:SELECT column1[, column2, ...], aggregate_function(ci) FROM table [WHERE clause] GROUP BY column1[, column2, ...]; [HAVING clause]说明 :
column1[, column2, ...]是分组依据的字段 , 至少一个字段 , 可以多个字段。aggregate_function(ci)是聚合函数。这是可选的 , 但是一般都用得到。SELECT后的字段必须是分组字段中的字段。WHERE子句是可选的 , 用来过滤结果集中的数据。HAVING子句是可选的 , 用来过滤分组数据。
示例
SELECT gender from employee GROUP BY gender;根据
gender字段来分组 ,gender字段的全部值只有两个('男'和'女'),所以分为了两组当group by单独使用时 , 只显示出每一组的第一条记录所以group by单独使用时的实际意义不大MariaDB [hellodb]> select * from students ; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [hellodb]> select * from students group by classid; +-------+--------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ | 24 | Xu Xian | 27 | M | NULL | NULL | | 2 | Shi Potian | 22 | M | 1 | 7 | | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | +-------+--------------+-----+--------+---------+-----------+ 8 rows in set (0.01 sec) #只显示了每一组第一条记录聚合函数
MariaDB [hellodb]> select gender,group_concat(name) from students group by gender; +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | gender | group_concat(name) | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | F | Lin Daiyu,Yue Lingshan,Ren Yingying,Wen Qingqing,Xi Ren,Diao Chan,Huang Yueying,Xiao Qiao,Lu Wushuang,Xue Baochai | | M | Hua Rong,Shi Zhongyu,Ma Chao,Xu Xian,Lin Chong,Xu Zhu,Duan Yu,Tian Boguang,Yuan Chengzhi,Shi Qing,Yu Yutong,Ding Dian,Xie Yanke,Shi Potian,Sun Dasheng | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)having
用来分组查询后制定一些条件来输出查询结果
having的作用和where一样 , 但having只能用于group by书写流程
范例 : 分组统计
#按照班级进行分组 ,查看classid >3 时所有班级的平均年龄
MariaDB [hellodb]> select classid,avg(age) as 平均年龄 from students where classid > 3 group by classid;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+--------------+
4 rows in set (0.00 sec)
#查看所有平均年纪大于30的classid的行
MariaDB [hellodb]> select classid,avg(age) as 平均年龄 from students where classid > 3 group by classid having 平均年龄 > 30;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 5 | 46.0000 |
+---------+--------------+
1 row in set (0.00 sec)
#查看gender为M的平均年龄
MariaDB [hellodb]> select gender,avg(age) 平均年龄 from students group by gender having gender='M';
+--------+--------------+
| gender | 平均年龄 |
+--------+--------------+
| M | 33.0000 |
+--------+--------------+
1 row in set (0.00 sec)范例 : 简单group by示例
SELECT last_name
FROM actor
GROUP BY last_name;+--------------+
| last_name |
+--------------+
| AKROYD |
| ALLEN |
| ASTAIRE |
| BACALL |
| BAILEY |
...
| ZELLWEGER |
+--------------+
121 rows in set (0.00 sec)本例中 , 使用 GROUP BY 子句按照 last_name 字段对数据进行分组。
本例的输出结果与以下使用 DISTINCT 的 SQL 输出结果完全一样 :
SELECT DISTINCT last_name FROM actor;范例 : GROUP BY与聚合函数示例
我们使用 GROUP BY 子句和聚合函数 COUNT() 查看 actor 表中的姓氏列表以及每个姓氏的次数。
SELECT last_name, COUNT(*)
FROM actor
GROUP BY last_name
ORDER BY COUNT(*) DESC;+--------------+----------+
| last_name | COUNT(*) |
+--------------+----------+
| KILMER | 5 |
| NOLTE | 4 |
| TEMPLE | 4 |
| AKROYD | 3 |
| ALLEN | 3 |
| BERRY | 3 |
...
| WRAY | 1 |
+--------------+----------+
121 rows in set (0.00 sec)本例中 , 执行的顺序如下 :
首先使用
GROUP BY子句按照last_name字段对数据进行分组。然后使用聚合函数
COUNT(*)汇总每个姓氏的行数。最后使用
ORDER BY子句按照COUNT(*)降序排列。
这样 , 数量最多的姓氏排在最前面。
范例 : GROUP BY , LIMIT , 聚合函数实例
以下实例使用 GROUP BY 子句 , LIMIT子句和聚合函数 SUM() 返回总消费金额排名前 10 位的客户。
SELECT customer_id, SUM(amount) total
FROM payment
GROUP BY customer_id
ORDER BY total DESC
LIMIT 10;+-------------+--------+
| customer_id | total |
+-------------+--------+
| 526 | 221.55 |
| 148 | 216.54 |
| 144 | 195.58 |
| 137 | 194.61 |
| 178 | 194.61 |
| 459 | 186.62 |
| 469 | 177.60 |
| 468 | 175.61 |
| 236 | 175.58 |
| 181 | 174.66 |
+-------------+--------+
10 rows in set (0.02 sec)本例中 , 执行的顺序如下 :
首先使用
GROUP BY子句按照customer_id字段对数据进行分组。然后使用聚合函数
SUM(amount)汇总每个客户的amount字段 , 并使用total作为别名。然后使用
ORDER BY子句按照total降序排列。最后使用
LIMIT 10子句返回前 10 个记录行。
范例 : GROUP BY和HAVING实例
以下实例使用 GROUP BY 子句 , HAVING 子句和聚合函数 SUM() 返回总消费金额在 180 以上的客户。
SELECT customer_id, SUM(amount) total
FROM payment
GROUP BY customer_id
HAVING total > 180
ORDER BY total DESC;+-------------+--------+
| customer_id | total |
+-------------+--------+
| 526 | 221.55 |
| 148 | 216.54 |
| 144 | 195.58 |
| 137 | 194.61 |
| 178 | 194.61 |
| 459 | 186.62 |
+-------------+--------+
6 rows in set (0.02 sec)本例中 , 执行的顺序如下 :
首先使用
GROUP BY子句按照customer_id字段对数据进行分组。然后使用聚合函数
SUM(amount)汇总每个客户的amount字段 , 并使用total作为别名。然后使用
HAVING过滤结果集中total大于180的数据行。最后使用
ORDER BY子句按照total降序排列。
HAVING 子句用来过滤 GROUP BY 分组的数据 , 需要使用逻辑表达式作为条件 , 其中逻辑表达式中的字段或表达式只能使用分组使用的字段和聚合函数。
2.5 排序
在指定字段或按升序或降序排列的字段上对查询的结果集进行排序。
SELECT
column1, column2, ...
FROM
table_name
[WHERE clause]
ORDER BY
column1 [ASC|DESC],
column2 [ASC|DESC],
...;说明 :
ORDER BY子句可以指定一个或多个字段。[ASC|DESC]代表排序是升序还是降序 , 这是可选的。ASC代表升序 ,DESC代表降序。通过在
column1之前添加-可以实现降序排序未指定
[ASC|DESC]时 , 默认值是ASC。即 , 默认是按指定的字段升序排序。当指定多个列时 , 首先按照前面的字段排序 , 其次按照后面的字段排序。
可以添加
WHERE...LIKE子句来设置条件
2.5.1 ORDER BY 排序规则说明
ORDER BY column ASC;此
ORDER BY子句对结果集按column字段的值升序排序。ORDER BY column DESC;此
ORDER BY子句对结果集按column字段的值降序排序。ORDER BY column;此
ORDER BY子句对结果集按column字段的值升序排序。这个语句等效于 :ORDER BY column ASC;。ORDER BY column1, column2;此
ORDER BY子句对结果集先按column1字段的值升序排序 , 然后再按column2字段的值升序排序。也就是说主排序按
column1字段升序排序 , 在主排序的基础上 , 对column1字段相同的行 , 再按column2字段升序排序。ORDER BY column1 DESC, column2;此
ORDER BY子句对结果集先按column1字段的值降序排序 , 然后再按按column2字段的值升序排序。也就是说主排序按
column1字段降序排序 , 在主排序的基础上 , 对column1字段相同的行 , 再按column2字段升序排序。
2.5.2 按自定义顺序排序
有时候单纯的按照字段的值排序并不能满足要求 , 我们需要按照自定义的顺序的排序。比如 , 我们需要按照电影分级 'G', 'PG', 'PG-13', 'R', 'NC-17' 的顺序对影片进行排序。
对于这样的需求 , 它可以理解为按照列表中元素的索引位置进行排序。我们分别使用 CASE 子句或 FIELD() 函数实现它。
假设您要根据影片的等级按照的 'G', 'PG', 'PG-13', 'R', 'NC-17' 顺序对影片进行排序。
2.5.2.1 使用CASE实现自定义排序
SELECT
film_id, title, rating
FROM
film
ORDER BY CASE rating
WHEN 'G' THEN 1
WHEN 'PG' THEN 2
WHEN 'PG-13' THEN 3
WHEN 'R' THEN 4
WHEN 'NC-17' THEN 5
END;+---------+-----------------------------+--------+
| film_id | title | rating |
+---------+-----------------------------+--------+
| 2 | ACE GOLDFINGER | G |
| 4 | AFFAIR PREJUDICE | G |
...
| 1 | ACADEMY DINOSAUR | PG |
| 6 | AGENT TRUMAN | PG |
...
| 7 | AIRPLANE SIERRA | PG-13 |
| 9 | ALABAMA DEVIL | PG-13 |
...
| 8 | AIRPORT POLLOCK | R |
| 17 | ALONE TRIP | R |
...
| 3 | ADAPTATION HOLES | NC-17 |
| 10 | ALADDIN CALENDAR | NC-17 |
...
1000 rows in set (0.00 sec)在这个例子中 , 我们使用 CASE 将电影的等级转换为一个索引数字。然后使用 ORDER BY 按照这个数字进行排序。
可能您觉得 CASE 子句写起来很复杂 , 特别是列表值很多的时候。那么 , 请使用如下的 FIELD() 函数。
2.5.2.2 使用 FIELD() 函数实现自定义排序
对于上面实例中的 CASE 语句 , 我们可以如下的使用 FIELD()代替。
SELECT
*
FROM
film
ORDER BY FIELD(rating, 'G', 'PG', 'PG-13', 'R', 'NC-17');输出结果与上面实例完全相同。
在本例中 , 我们使用 FIELD(rating, 'G', 'PG', 'PG-13', 'R', 'NC-17') 作为 ORDER BY 排序的表达式。其中 FIELD(value, value1, value2, ...) 函数返回 value 在 value1, value2, ... 列表中的位置。
2.5.3 ORDER BY 和NULL
在 MySQL 中的升序排序中 , NULL 值出现在非 NULL 值之前。
我们下面的实例使用以下临时数据作为演示 :
SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT 0 AS v
UNION ALL
SELECT 1 AS v;+------+
| v |
+------+
| A |
| B |
| NULL |
| 0 |
| 1 |
+------+当我们使用 ORDER BY 子句升序 ASC 排序时 , NULL 值排在非 NULL 值的前面。如下 :
SELECT *
FROM (
SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT 0 AS v
UNION ALL
SELECT 1 AS v
) t
ORDER BY v;+------+
| v |
+------+
| NULL |
| 0 |
| 1 |
| A |
| B |
+------+当我们使用 ORDER BY 子句降序 DESC 排序时 , NULL 值排在非 NULL 值的后面。如下 :
SELECT *
FROM (
SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT 0 AS v
UNION ALL
SELECT 1 AS v
) t
ORDER BY v DESC;+------+
| v |
+------+
| B |
| A |
| 1 |
| 0 |
| NULL |
+------+范例 :
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid order by classid;
+---------+----------+
| classid | sum(age) |
+---------+----------+
| 1 | 82 |
| 2 | 108 |
| 3 | 81 |
| 4 | 99 |
| 5 | 46 |
| 6 | 83 |
| 7 | 59 |
+---------+----------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> select classid,sum(age) from students group by classid having classid is not null order by classid;
+---------+----------+
| classid | sum(age) |
+---------+----------+
| 1 | 82 |
| 2 | 108 |
| 3 | 81 |
| 4 | 99 |
| 5 | 46 |
| 6 | 83 |
| 7 | 59 |
+---------+----------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> select classid,sum(age) from students group by classid having classid is not null order by classid desc;
+---------+----------+
| classid | sum(age) |
+---------+----------+
| 7 | 59 |
| 6 | 83 |
| 5 | 46 |
| 4 | 99 |
| 3 | 81 |
| 2 | 108 |
| 1 | 82 |
+---------+----------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> select classid,sum(age) from students group by classid having classid is not null order by classid asc;
+---------+----------+
| classid | sum(age) |
+---------+----------+
| 1 | 82 |
| 2 | 108 |
| 3 | 81 |
| 4 | 99 |
| 5 | 46 |
| 6 | 83 |
| 7 | 59 |
+---------+----------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid order by classid limit 2,3;
+---------+----------+
| classid | sum(age) |
+---------+----------+
| 3 | 81 |
| 4 | 99 |
| 5 | 46 |
+---------+----------+
3 rows in set (0.00 sec)
#先过滤再排序
MariaDB [hellodb]> select * from students where classid is not null order by gender desc,age asc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.01 sec)
#多列排序
MariaDB [hellodb]> select * from students order by gender desc,age asc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students order by classid ;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.01 sec)
#通过负号-实现降序排列
MariaDB [hellodb]> select * from students order by -classid ;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
#正序排序,NULL记录排在最后
MariaDB [hellodb]> select * from students order by -classid desc ;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)范例 : 分组和排序
#分组后再排序
MariaDB [hellodb]> select gender,classid,avg(age) from students where classid is not null group by gender,classid order by gender,classid;
+--------+---------+----------+
| gender | classid | avg(age) |
+--------+---------+----------+
| F | 1 | 19.5000 |
| F | 3 | 18.3333 |
| F | 6 | 20.0000 |
| F | 7 | 18.0000 |
| M | 1 | 21.5000 |
| M | 2 | 36.0000 |
| M | 3 | 26.0000 |
| M | 4 | 24.7500 |
| M | 5 | 46.0000 |
| M | 6 | 23.0000 |
| M | 7 | 23.0000 |
+--------+---------+----------+
11 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students order by age limit 10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students order by age limit 3,10;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
MariaDB [hellodb]> select distinct age from students order by age limit 3;
+-----+
| age |
+-----+
| 17 |
| 18 |
| 19 |
+-----+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select distinct age from students order by age limit 3,5;
+-----+
| age |
+-----+
| 20 |
| 21 |
| 22 |
| 23 |
| 25 |
+-----+
5 rows in set (0.00 sec)2.6 别名
MySQL中的列别名 , 表别名和派生表别名 , 以及使用别名来简化SQL和提高SQL的可读性。
如果在一个SQL中涉及到多个表 , 我们需要使用 table_name.column_name 这样的方式来引用每个表的字段 , 这有时候会让SQL变的臃肿和难以阅读。而使用别名 , 则可以简化SQL和提高SQL的可读性。
在MySQL中 , 我们可以使用的别名包括 : 列别名 , 表别名和派生表别名。
2.6.1 列别名
很多时候 , 一个表的列名的可读性并不好而且难理解。而使用列别名 ,则可以自定义输出列的名称。
以下语句说明了如何使用列别名 :
SELECT column_name AS `alias`
FROM table_name;要为列提供别名, 请使用AS关键字后跟别名。如果别名包含空格 , 则必须按以下方式引用它(使用`):
列别名使用说明 :
AS关键字后面跟的是列的别名alias当别名
alias中包含空格时 , 必须使用' 将别名引起来 , 即 alias。如果没有空格这种特殊字符 , 可以不使用''符号AS关键字是可选的。除了为字段指定别名 , 还可以为表达式指定别名。
SELECT NOW() `Current Time` FROM dual
示例
未指定别名查询数据
SELECT first_name, last_name, CONCAT(first_name, ', ', last_name) FROM actor LIMIT 5;+------------+--------------+-------------------------------------+ | first_name | last_name | CONCAT(first_name, ', ', last_name) | +------------+--------------+-------------------------------------+ | PENELOPE | GUINESS | PENELOPE, GUINESS | | NICK | WAHLBERG | NICK, WAHLBERG | | ED | CHASE | ED, CHASE | | JENNIFER | DAVIS | JENNIFER, DAVIS | | JOHNNY | LOLLOBRIGIDA | JOHNNY, LOLLOBRIGIDA | +------------+--------------+-------------------------------------+ 5 rows in set (0.00 sec)这样的列名可读性很差并很难理解。
指定别名查询数据
SELECT first_name `First Name`, last_name `Last Name`, CONCAT(first_name, ', ', last_name) `Full Name` FROM actor LIMIT 5;+------------+--------------+----------------------+ | First Name | Last Name | Full Name | +------------+--------------+----------------------+ | PENELOPE | GUINESS | PENELOPE, GUINESS | | NICK | WAHLBERG | NICK, WAHLBERG | | ED | CHASE | ED, CHASE | | JENNIFER | DAVIS | JENNIFER, DAVIS | | JOHNNY | LOLLOBRIGIDA | JOHNNY, LOLLOBRIGIDA | +------------+--------------+----------------------+ 5 rows in set (0.00 sec)在本例中 , 我们为以下列指定了别名 :
指定
first_name列的别名为First Name。指定
last_name列的别名为Last Name。指定
CONCAT(first_name, ', ', last_name)表达式的别名为Full Name。
从输出的结果来看 , 指定别名后 , 输出数据的可读性更高。因为大多数人不懂 CONCAT(first_name, ', ', last_name) 的具体含义。
2.6.2 表别名
除了为列指定别名 , 我们还可以为表指定别名 , 如下 :
table_name AS alias其中 AS 关键字是可选的 , 因此您可以省略它。
当SQL语句中涉及到多个表时 , 使用表别名相当重要。特别是当多个表含有相同的字段名时。不指定表别名时 ,为了区分来自不同表的同名字段 , 只能通过 table_name.column_name 这样的方式来引用每个表的字段。当指定了别名时 , 则可以通过 alias.column_name 引用字段。
SELECT *
FROM language
WHERE EXISTS(
SELECT *
FROM film
WHERE film.language_id = language.language_id
);上面的例子中 , 没有使用表别名 , 我们使用 film.language_id 和 language.language_id 分别引用 film 和 language 的 language_id 字段。
现在我们使用别名改造这个SQL , 如下 :
SELECT *
FROM language l
WHERE EXISTS(
SELECT *
FROM film f
WHERE f.language_id = l.language_id
);2.6.3 派生表
派生表是一个由表达式生成的表 , 使用派生表就像使用表一样。常见的派生表由子查询产生。派生表必须指定别名。例如以下语句:
SELECT
AVG(t.customer_total) customer_avg
FROM
(SELECT
customer_id, SUM(amount) customer_total
FROM
payment
GROUP BY customer_id
HAVING customer_total > 180) t;+--------------+
| customer_avg |
+--------------+
| 201.585000 |
+--------------+
1 row in set (0.02 sec)在本例中 , 我们为派生表:
(SELECT
customer_id, SUM(amount) customer_total
FROM
payment
GROUP BY customer_id
HAVING customer_total > 180)指定了别名 t。
使用派生表必须指定别名。因为 , FROM 子句中的所有表都必须有一个名字。
3. 多表操作
子查询 : 在
SQL语句嵌套着查询语句 , 性能较差 , 基于某语句的查询结果再次进行的查询联合查询 :
UNION交叉连接 : 笛卡尔乘积
内连接 :
等值连接 : 让表之间的字段以“等值”建立连接关系
不等值连接
自然连接 : 去掉重复列的等值连接
外连接 :
左外连接 :
FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col右外连接 :
FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col自连接 : 本表和本表进行连接查询
Join 是连接的意思 , 顾名思义 , SQL JOIN子句用于将两个或者多个表联合起来进行查询。
联合表时需要在每个表中选择一个字段 , 并对这些字段的值进行比较 , 值相同的两条记录将合并为一条。联合表的本质就是将不同表的记录合并起来 , 形成一张新表。当然 , 这张新表只是临时的 , 它仅存在于本次查询期间。
数据库中的表可以通过键将彼此联合起来 , 一个典型的例子是 , 将一个表的主键和另一个表的外键进行匹配。在表中, 每个主键的值都是唯一的, 这样做的目的是在不重复每个表中所有记录的情况下, 将表之间的数据交叉捆绑在一起。
如果不加任何修饰词 , 只写JOIN , 那么默认为INNER JOIN。
using()用于两张表的join查询 , 要求using()指定的列在两个表中均存在 , 并使用之用于join的条件。
3.1 子查询
子查询(Subquery)是指嵌套在其他SQL语句( SELECT、INSERT、UPDATE、DELETE等)中的查询语句。子查询也称为内查询(inner query) , 必须位于括号之中 ; 包含子查询的查询也称为外查询(outer query)。子查询支持多层嵌套 , 也就是子查询中包含其他子查询。
子查询可以包含普通select可以包括的任何子句 , 比如 : distinct、 group by、order by、limit、join和union等 ; 但是对应的外部查询必须是以下语句之一 select、insert、update、delete、set或者do。
MySQL 中的子查询可以分为以下四种类型 :
标量子查询(
Scalar Subquery) : 返回单个值（一行一列）的子查询。上面的示例就是一个标量子查询。行子查询(
Row Subquery) : 返回单行结果（一行多列）的子查询 , 标量子查询是行子查询的一个特例。表子查询(
Table Subquery) : 返回一个虚拟表（多行多列）的子查询 , 行子查询是表子查询的一个特例。列子查询 : 结果集只有一列多行）
按子查询出现在主查询中的不同位置分
select后面 :仅仅支持标量子查询。
from后面 :支持表子查询。将子查询的结果集充当一张表 , 要求必须起别名 , 否者这个表找不到。
然后将真实的表和子查询结果表进行连接查询。
where或having后面 :支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行）
子查询放在小括号内。
子查询一般放在条件的右侧。
标量子查询 , 一般搭配着单行操作符使用 , 多行操作符 >、<、>=、<=、=、<>、!=
列子查询 , 一般搭配着多行操作符使用
exists后面（即相关子查询） : 表子查询（多行、多列）
exists查询结果 :1或0,exists查询的结果用来判断子查询的结果集中是否有值。一般来说 , 能用
exists的子查询 , 绝对都能用in代替 , 所以exists用的少。和前面的查询不同 , 这先执行主查询 , 然后主查询查询的结果 ,在根据子查询进行过滤 , 子查询中涉及到主查询中用到的字段 , 所以叫相关子查询。
子查询可以嵌入SELECT、INSERT、UPDATE 和 DELETE 语句中 , 也可以和=、<、>、IN、BETWEEN、EXISTS 等运算符一起使用。
其中 , 操作符可以是比较运算符和IN、NOT IN、EXISTS、NOT EXISTS等关键字。
子查询语句可以嵌套在
SQL语句中任何表达式出现的位置在
SELECT语句中 , 子查询可以被嵌套在SELECT语句的列、表和查询条件中 , 即SELECT子句 、FROM子句、WHERE子句、GROUP BY子句和HAVING子句。前面已经介绍了 WHERE 子句中嵌套子查询的使用方法 , 下面是子查询在
SELECT子句和FROM子句中的使用语法。嵌套在
SELECT语句的SELECT子句中的子查询语法格式如下。SELECT (子查询) FROM 表名;提示 : 子查询结果为单行单列 , 但不必指定列别名。
嵌套在 SELECT 语句的 FROM 子句中的子查询语法格式如下:
SELECT * FROM (子查询) AS 表的别名;注意 : 必须为表指定别名。一般返回多行多列数据记录 , 可以当作一张临时表。
只出现在子查询中而没有出现在父查询中的表不能包含在输出列中
多层嵌套子查询的最终数据集只包含父查询（即最外层的查询）的
SELECT子句中出现的字段 , 而子查询的输出结果通常会作为其外层子查询数据源或用于数据判断匹配。常见错误如下 :
SELECT * FROM (SELECT * FROM result);这个子查询语句产生语法错误的原因在于主查询语句的 FROM 子句是一个子查询语句 , 因此应该为子查询结果集指定别名。正确代码如下。
SELECT * FROM (SELECT * FROM result) AS Temp;
常用在WHERE子句中的子查询
用于比较表达式中的子查询: 子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM teachers); #查询students中age大于 teachers表中平均年龄的所有人的姓名和年龄 MariaDB [hellodb]> select Name,Age FROM students WHERE Age>(SELECT avg(age) FROM students); +--------------+-----+ | Name | Age | +--------------+-----+ | Xie Yanke | 53 | | Ding Dian | 32 | | Shi Qing | 46 | | Tian Boguang | 33 | | Sun Dasheng | 100 | +--------------+-----+ 5 rows in set (0.00 sec)用于IN中的子查询: 子查询应该单独查询并返回一个或多个值重新构成列表
#查询students表中age与teachers age相同的行 SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers); MariaDB [hellodb]> select Name,Age FROm students WHERE age in (SELECT age FROm teachers); +-------+-----+ | Name | Age | +-------+-----+ | xiong | 77 | | test | 93 | +-------+-----+ 2 rows in set (0.00 sec)用于
EXISTS和Not EXISTS参考链接 : https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html
EXISTS(包括NOT EXISTS)子句的返回值是一个BOOL值。EXISTS内部有一个子查询语句(SELECT ... FROM...) , 将其称为EXIST的内查询语句。其内查询语句返回一个结果集。EXISTS子句根据其内查询语句的结果集空或者非空 , 返回一个布尔值。将外查询表的每一行 , 代入内查询作为检验 , 如果内查询返回的结果为非空值 , 则EXISTS子句返回TRUE, 外查询的这一行数据便可作为外查询的结果行返回 , 否则不能作为结果#简单示例 mysql> SELECT exists(SELECT employee_id FROM employees WHERE salary = 300000) AS 'exists返回1或者0'; +----------------------+ | exists返回1或者0 | +----------------------+ | 0 | +----------------------+ 1 row in set (0.00 sec) MariaDB [hellodb]> +-------+-------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+-------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | +-------+-------------+-----+--------+---------+-----------+ 3 rows in set (0.00 sec) #说明: 1. EXISTS(或 NOT EXISTS)用在where之后,且后面紧跟子查询语句(带括号) 2. EXISTS(或 NOT EXISTS)只关心子查询有没有结果,并不关心子查询的结果具体是什么 3. 上述语句把students的记录逐条代入到EXISTS后面的子查询中,如果子查询结果集不为空,即说明存在,那么这条students的记录出现在最终结果集,否则被排除 MariaDB [hellodb]> SELECT * from students s where NOT EXISTS ( SELECT * from teachers t where s.teacherid=t.tid); +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 26 | xiong | 77 | M | NULL | NULL | | 27 | test | 93 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 24 rows in set (0.00 sec) /*exists入门案例*/ SELECT exists(SELECT employee_id FROM employees WHERE salary = 300000) AS 'exists返回1或者0'; /*查询所有员工部门名*/ SELECT department_name FROM departments a WHERE exists(SELECT 1 FROM employees b WHERE a.department_id = b.department_id); /*使用in实现*/ SELECT department_name FROM departments a WHERE a.department_id IN (SELECT department_id FROM employees);用于
FROM子句中的子查询使用格式 :
SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;范例
MariaDB [hellodb]> select s.aage,s.classid from (SELECT avg(Age) AS aage,classID FROM students WHERE classid is not null group by classid) AS s where s.aage > 30; +---------+---------+ | aage | classid | +---------+---------+ | 36.0000 | 2 | | 46.0000 | 5 | +---------+---------+ 2 rows in set (0.00 sec)
范例 : 子查询
#子查询: SELECT的执行结果,被其他SQL调用
MariaDB [hellodb]> SELECT stuid,name,age from students where age > (select avg(age) from students);
+-------+--------------+-----+
| stuid | name | age |
+-------+--------------+-----+
| 3 | Xie Yanke | 53 |
| 4 | Ding Dian | 32 |
| 6 | Shi Qing | 46 |
| 13 | Tian Boguang | 33 |
| 25 | Sun Dasheng | 100 |
| 26 | xiong | 77 |
| 27 | test | 93 |
+-------+--------------+-----+
7 rows in set (0.00 sec)范例 : 子查询用于更新表
MariaDB [hellodb]> update teachers set age=(select avg(age) from students ) where tid=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 32 | F |
+-----+---------------+-----+--------+
4 rows in set (0.01 sec)范例 : 标量子查询
#select 后面的标量子查询
#查询每个部门员工的个数
SELECT a.*,
(SELECT count(*) FROM employees b WHERE b.department_id = a.department_id) AS 员工个数
FROM departments a;
#查询员工号=102的部门名称
SELECT (SELECT a.department_name
FROM departments a, employees b
WHERE a.department_id = b.department_id
AND b.employee_id = 102) AS 部门名;
#FROM后面的子查询
#查询每个部门平均工资
SELECT
department_id,
avg(a.salary)
FROM employees a
GROUP BY a.department_id;
#薪资等级表
SELECT *
FROM job_grades;
#将上面2个结果连接查询 , 筛选条件:平均工资 between lowest_sal and highest_sal;
mysql> SELECT
t1.department_id,
sa AS '平均工资',
t2.grade_level
FROM (SELECT
department_id,
avg(a.salary) sa
FROM employees a
GROUP BY a.department_id) t1, job_grades t2
WHERE
t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
+---------------+--------------+-------------+
| department_id | 平均工资 | grade_level |
+---------------+--------------+-------------+
| NULL | 7000.000000 | C |
| 10 | 4400.000000 | B |
| 20 | 9500.000000 | C |
| 30 | 4150.000000 | B |
| 40 | 6500.000000 | C |
| 50 | 3475.555556 | B |
| 60 | 5760.000000 | B |
| 70 | 10000.000000 | D |
| 80 | 8955.882353 | C |
| 90 | 19333.333333 | E |
| 100 | 8600.000000 | C |
| 110 | 10150.000000 | D |
+---------------+--------------+-------------+
12 rows in set (0.00 sec)
#标量子查询
/*①查询abel的工资【改查询是标量子查询】*/
SELECT salary
FROM employees
WHERE last_name = 'Abel';
/*②查询员工信息 , 满足salary>①的结果*/
SELECT *
FROM employees a
WHERE a.salary > (SELECT salary
FROM employees
WHERE last_name = 'Abel');
#多个标量子查询
/*返回job_id与141号员工相同 , salary比143号员工多的员工、姓名、job_id和工资*/
/*①查询141号员工的job_id*/
SELECT job_id
FROM employees
WHERE employee_id = 141;
/*②查询143好员工的salary*/
SELECT salary
FROM employees
WHERE employee_id = 143;
/*③查询员工的姓名、job_id、工资 , 要求job_id=① and salary>②*/
SELECT
a.last_name 姓名,
a.job_id,
a.salary 工资
FROM employees a
WHERE a.job_id = (SELECT job_id
FROM employees
WHERE employee_id = 141)
AND
a.salary > (SELECT salary
FROM employees
WHERE employee_id = 143);
#子查询+分组函数
/*查询最低工资大于50号部门最低工资的部门id和其最低工资【having】*/
/*①查询50号部门的最低工资*/
SELECT min(salary)
FROM employees
WHERE department_id = 50;
/*②查询每个部门的最低工资*/
SELECT
min(salary),
department_id
FROM employees
GROUP BY department_id;
/*③在②的基础上筛选 , 满足min(salary)>①*/
SELECT
min(a.salary) minsalary,
department_id
FROM employees a
GROUP BY a.department_id
HAVING min(a.salary) > (SELECT min(salary)
FROM employees
WHERE department_id = 50);
/*错误示例*/
mysql> SELECT
min(a.salary) minsalary,
department_id
FROM employees a
GROUP BY a.department_id
HAVING min(a.salary) > (SELECT salary
FROM employees
WHERE department_id = 500000);
ERROR 1242 (21000): Subquery returns more than 1 row
/*说明 : 上面的子查询只支持最多一列一行记录。*/范例 : 列子查询
/*返回location_id是1400或1700的部门中的所有员工姓名*/
/*方式1*/
/*①查询location_id是1400或1700的部门编号*/
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700);
/*②查询员工姓名 , 要求部门是①列表中的某一个*/
SELECT a.last_name
FROM employees a
WHERE a.department_id IN (SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700));
/*方式2 : 使用any实现*/
SELECT a.last_name
FROM employees a
WHERE a.department_id = ANY (SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700));
/*拓展 , 下面与not in等价*/
SELECT a.last_name
FROM employees a
WHERE a.department_id <> ALL (SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700));
/*返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id、salary*/
/*①查询job_id为'IT_PROG'部门任-工资*/
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
/*②查询员工号、姓名、job_id、salary , slary<①的任意一个*/
SELECT
last_name,
employee_id,
job_id,
salary
FROM employees
WHERE salary < ANY (SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
/*或者*/
SELECT
last_name,
employee_id,
job_id,
salary
FROM employees
WHERE salary < (SELECT max(salary)
FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';范例 : 行子查询
/*查询员工编号最小并且工资最高的员工信息*/
/*①查询最小的员工编号*/
SELECT min(employee_id)
FROM employees;
/*②查询最高工资*/
SELECT max(salary)
FROM employees;
/*③方式1 : 查询员工信息*/
SELECT *
FROM employees a
WHERE a.employee_id = (SELECT min(employee_id)
FROM employees)
AND salary = (SELECT max(salary)
FROM employees);
/*方式2*/
SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) = (SELECT
min(employee_id),
max(salary)
FROM employees);
/*方式3*/
SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) in (SELECT
min(employee_id),
max(salary)
FROM employees);3.2 联合查询: UNION
默认地 , UNION运算符会过滤掉两个结果集中重复的记录 , 只保留其中一条 , 也就是对两个结果集进行并集操作 ;此外 , UNION 还会按照默认规则对结果集进行排序。
如果您希望保留原始结果 , 请使用UNION ALL。UNION ALL 只是对结果集进行简单粗暴的合并 , 不会过滤重复的记录 , 也不会进行排序。
Union必须遵循以下规则:在所有查询中 , 列的数量和顺序应相同。
每个
select查询的相应列位置必须具有兼容的数据类型。在不同的
SELECT查询中选择的列名的顺序必须相同。第一个
SELECT查询的列名将是输出的列名。
语法
SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;范例 : 联合查询
#多表纵向合并union
MariaDB [hellodb]> select * from teachers union select * from students;
MariaDB [hellodb]> select tid as id,name,age,gender from teachers union select stuid,name,age,gender from students;
+----+---------------+-----+--------+
| id | name | age | gender |
+----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 32 | F |
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
| 26 | xiong | 77 | M |
| 27 | test | 93 | M |
+----+---------------+-----+--------+
31 rows in set (0.00 sec)
MariaDB [hellodb]> select * from teachers union select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 32 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select * from teachers union all select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 32 | F |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 32 | F |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)join与union区别
union和join子句不同: union始终垂直组合结果集,join水平追加输出 ; JOIN 组合来自多个不同表的数据 , 而 UNION组合来自多个相似表的数据。我们可以通过下图查看区别 :
union和union all区别
UNION和UNION ALL是数据库中用于合并多个表中的结果集的两个最基本的SQL运算符。这些运算符允许我们使用多个 SELECT 查询 , 检索所需的结果 , 然后将它们组合到最终输出中。在本文中 , 我们将看到它们彼此之间的差异。
UNION ALL运算符主要作用: 组合来自多个 SELECT 查询的两个或多个结果 , 并将所有记录返回到单个结果集中。 它不会删除结果集中的重复行。
主要差异:
3.3 交叉连接
交叉连接用于组合两个或多个表的所有可能性 , 并返回包含所有参与表的每一行的结果。交叉连接也称为笛卡尔连接 , 它提供所有关联表的笛卡尔乘积。笛卡尔积可以解释为第一个表中的所有行乘以第二个表中的所有行。它类似于内部联接 , 其中联接条件不适用于该子句。
即笛卡儿乘积, "雨露均沾“, 利用cross join实现
交叉连接返回表1和表2中的所有记录 , 每一行都是这两个表的行的组合。
在MySQL中 , 当CROSS JOIN不使用WHERE子句时 , CROSS JOIN产生了一个结果集 , 该结果集是两个关联表的行的乘积。通常 , 如果每个表分别具有n和m行 , 则结果集将具有n*m行
语法
SELECT column-lists FROM table1 CROSS JOIN table2;
范例 : 交叉连接
#横向合并,交叉连接(横向笛卡尔)
MariaDB [hellodb]> select * from students cross join teachers;
MariaDB [hellodb]> select * from students ,teachers;
MariaDB [hellodb]> select stuid,students.name student_name,students.age,tid,teachers.name teacher_name,teachers.age from teachers cross join students;
+-------+---------------+-----+-----+---------------+-----+
| stuid | student_name | age | tid | teacher_name | age |
+-------+---------------+-----+-----+---------------+-----+
| 1 | Shi Zhongyu | 22 | 1 | Song Jiang | 45 |
| 1 | Shi Zhongyu | 22 | 2 | Zhang Sanfeng | 94 |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 1 | Shi Zhongyu | 22 | 4 | Lin Chaoying | 32 |
| 2 | Shi Potian | 22 | 1 | Song Jiang | 45 |
| 2 | Shi Potian | 22 | 2 | Zhang Sanfeng | 94 |
| 2 | Shi Potian | 22 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | 4 | Lin Chaoying | 32 |
| 3 | Xie Yanke | 53 | 1 | Song Jiang | 45 |
| 3 | Xie Yanke | 53 | 2 | Zhang Sanfeng | 94 |
| 3 | Xie Yanke | 53 | 3 | Miejue Shitai | 77 |
| 3 | Xie Yanke | 53 | 4 | Lin Chaoying | 32 |
| 4 | Ding Dian | 32 | 1 | Song Jiang | 45 |
| 4 | Ding Dian | 32 | 2 | Zhang Sanfeng | 94 |
| 4 | Ding Dian | 32 | 3 | Miejue Shitai | 77 |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 32 |
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 5 | Yu Yutong | 26 | 2 | Zhang Sanfeng | 94 |
| 5 | Yu Yutong | 26 | 3 | Miejue Shitai | 77 |
| 5 | Yu Yutong | 26 | 4 | Lin Chaoying | 32 |
| 6 | Shi Qing | 46 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | 2 | Zhang Sanfeng | 94 |
| 6 | Shi Qing | 46 | 3 | Miejue Shitai | 77 |
| 6 | Shi Qing | 46 | 4 | Lin Chaoying | 32 |
| 7 | Xi Ren | 19 | 1 | Song Jiang | 45 |
| 7 | Xi Ren | 19 | 2 | Zhang Sanfeng | 94 |
| 7 | Xi Ren | 19 | 3 | Miejue Shitai | 77 |
| 7 | Xi Ren | 19 | 4 | Lin Chaoying | 32 |
| 8 | Lin Daiyu | 17 | 1 | Song Jiang | 45 |
| 8 | Lin Daiyu | 17 | 2 | Zhang Sanfeng | 94 |
| 8 | Lin Daiyu | 17 | 3 | Miejue Shitai | 77 |
| 8 | Lin Daiyu | 17 | 4 | Lin Chaoying | 32 |
| 9 | Ren Yingying | 20 | 1 | Song Jiang | 45 |
| 9 | Ren Yingying | 20 | 2 | Zhang Sanfeng | 94 |
| 9 | Ren Yingying | 20 | 3 | Miejue Shitai | 77 |
| 9 | Ren Yingying | 20 | 4 | Lin Chaoying | 32 |
| 10 | Yue Lingshan | 19 | 1 | Song Jiang | 45 |
| 10 | Yue Lingshan | 19 | 2 | Zhang Sanfeng | 94 |
| 10 | Yue Lingshan | 19 | 3 | Miejue Shitai | 77 |
| 10 | Yue Lingshan | 19 | 4 | Lin Chaoying | 32 |
| 11 | Yuan Chengzhi | 23 | 1 | Song Jiang | 45 |
| 11 | Yuan Chengzhi | 23 | 2 | Zhang Sanfeng | 94 |
| 11 | Yuan Chengzhi | 23 | 3 | Miejue Shitai | 77 |
| 11 | Yuan Chengzhi | 23 | 4 | Lin Chaoying | 32 |
| 12 | Wen Qingqing | 19 | 1 | Song Jiang | 45 |
| 12 | Wen Qingqing | 19 | 2 | Zhang Sanfeng | 94 |
| 12 | Wen Qingqing | 19 | 3 | Miejue Shitai | 77 |
| 12 | Wen Qingqing | 19 | 4 | Lin Chaoying | 32 |
| 13 | Tian Boguang | 33 | 1 | Song Jiang | 45 |
| 13 | Tian Boguang | 33 | 2 | Zhang Sanfeng | 94 |
| 13 | Tian Boguang | 33 | 3 | Miejue Shitai | 77 |
| 13 | Tian Boguang | 33 | 4 | Lin Chaoying | 32 |
| 14 | Lu Wushuang | 17 | 1 | Song Jiang | 45 |
| 14 | Lu Wushuang | 17 | 2 | Zhang Sanfeng | 94 |
| 14 | Lu Wushuang | 17 | 3 | Miejue Shitai | 77 |
| 14 | Lu Wushuang | 17 | 4 | Lin Chaoying | 32 |
| 15 | Duan Yu | 19 | 1 | Song Jiang | 45 |
| 15 | Duan Yu | 19 | 2 | Zhang Sanfeng | 94 |
| 15 | Duan Yu | 19 | 3 | Miejue Shitai | 77 |
| 15 | Duan Yu | 19 | 4 | Lin Chaoying | 32 |
| 16 | Xu Zhu | 21 | 1 | Song Jiang | 45 |
| 16 | Xu Zhu | 21 | 2 | Zhang Sanfeng | 94 |
| 16 | Xu Zhu | 21 | 3 | Miejue Shitai | 77 |
| 16 | Xu Zhu | 21 | 4 | Lin Chaoying | 32 |
| 17 | Lin Chong | 25 | 1 | Song Jiang | 45 |
| 17 | Lin Chong | 25 | 2 | Zhang Sanfeng | 94 |
| 17 | Lin Chong | 25 | 3 | Miejue Shitai | 77 |
| 17 | Lin Chong | 25 | 4 | Lin Chaoying | 32 |
| 18 | Hua Rong | 23 | 1 | Song Jiang | 45 |
| 18 | Hua Rong | 23 | 2 | Zhang Sanfeng | 94 |
| 18 | Hua Rong | 23 | 3 | Miejue Shitai | 77 |
| 18 | Hua Rong | 23 | 4 | Lin Chaoying | 32 |
| 19 | Xue Baochai | 18 | 1 | Song Jiang | 45 |
| 19 | Xue Baochai | 18 | 2 | Zhang Sanfeng | 94 |
| 19 | Xue Baochai | 18 | 3 | Miejue Shitai | 77 |
| 19 | Xue Baochai | 18 | 4 | Lin Chaoying | 32 |
| 20 | Diao Chan | 19 | 1 | Song Jiang | 45 |
| 20 | Diao Chan | 19 | 2 | Zhang Sanfeng | 94 |
| 20 | Diao Chan | 19 | 3 | Miejue Shitai | 77 |
| 20 | Diao Chan | 19 | 4 | Lin Chaoying | 32 |
| 21 | Huang Yueying | 22 | 1 | Song Jiang | 45 |
| 21 | Huang Yueying | 22 | 2 | Zhang Sanfeng | 94 |
| 21 | Huang Yueying | 22 | 3 | Miejue Shitai | 77 |
| 21 | Huang Yueying | 22 | 4 | Lin Chaoying | 32 |
| 22 | Xiao Qiao | 20 | 1 | Song Jiang | 45 |
| 22 | Xiao Qiao | 20 | 2 | Zhang Sanfeng | 94 |
| 22 | Xiao Qiao | 20 | 3 | Miejue Shitai | 77 |
| 22 | Xiao Qiao | 20 | 4 | Lin Chaoying | 32 |
| 23 | Ma Chao | 23 | 1 | Song Jiang | 45 |
| 23 | Ma Chao | 23 | 2 | Zhang Sanfeng | 94 |
| 23 | Ma Chao | 23 | 3 | Miejue Shitai | 77 |
| 23 | Ma Chao | 23 | 4 | Lin Chaoying | 32 |
| 24 | Xu Xian | 27 | 1 | Song Jiang | 45 |
| 24 | Xu Xian | 27 | 2 | Zhang Sanfeng | 94 |
| 24 | Xu Xian | 27 | 3 | Miejue Shitai | 77 |
| 24 | Xu Xian | 27 | 4 | Lin Chaoying | 32 |
| 25 | Sun Dasheng | 100 | 1 | Song Jiang | 45 |
| 25 | Sun Dasheng | 100 | 2 | Zhang Sanfeng | 94 |
| 25 | Sun Dasheng | 100 | 3 | Miejue Shitai | 77 |
| 25 | Sun Dasheng | 100 | 4 | Lin Chaoying | 32 |
| 26 | xiong | 77 | 1 | Song Jiang | 45 |
| 26 | xiong | 77 | 2 | Zhang Sanfeng | 94 |
| 26 | xiong | 77 | 3 | Miejue Shitai | 77 |
| 26 | xiong | 77 | 4 | Lin Chaoying | 32 |
| 27 | test | 93 | 1 | Song Jiang | 45 |
| 27 | test | 93 | 2 | Zhang Sanfeng | 94 |
| 27 | test | 93 | 3 | Miejue Shitai | 77 |
| 27 | test | 93 | 4 | Lin Chaoying | 32 |
+-------+---------------+-----+-----+---------------+-----+
108 rows in set (0.00 sec)
MariaDB [hellodb]>MariaDB [hellodb]> select stuid,s.name student_name,s.age student_age,tid,t.name teacher_name, t.age teacher_age from teachers t cross join students s;3.4 内连接
INNER JOIN子句将一个表中的行与其他表中的行进行匹配 , 并允许从两个表中查询包含列的行记录。
在使用INNER JOIN子句之前 , 必须指定以下条件 :
首先 , 在
FROM子句中指定主表。其次 , 表中要连接的主表应该出现在
INNER JOIN子句中。理论上说 , 可以连接多个其他表。 但是 , 为了获得更好的性能 , 应该限制要连接的表的数量(最好不要超过三个表)。第三 , 连接条件或连接谓词。连接条件出现在
INNER JOIN子句的ON关键字之后。连接条件是将主表中的行与其他表中的行进行匹配的规则。
语法
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;范例 : 内连接
#内连接inner join
MariaDB [hellodb]> select * from students inner join teachers on students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 32 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
#如果表定义了别名,原表名将无法使用
MariaDB [hellodb]> select stuid,s.name as student_name,tid,t.name as teacher_name from students as s inner join teachers as t on s.teacherid=t.tid;
+-------+--------------+-----+---------------+
| stuid | student_name | tid | teacher_name |
+-------+--------------+-----+---------------+
| 5 | Yu Yutong | 1 | Song Jiang |
| 1 | Shi Zhongyu | 3 | Miejue Shitai |
| 4 | Ding Dian | 4 | Lin Chaoying |
+-------+--------------+-----+---------------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select stuid,s.name studentname,s.age studentage,tid,t.name as teachername,t.age teacherage from students as s inner join teachers t on s.teacherid=t.tid;
+-------+-------------+------------+-----+---------------+------------+
| stuid | studentname | studentage | tid | teachername | teacherage |
+-------+-------------+------------+-----+---------------+------------+
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 32 |
+-------+-------------+------------+-----+---------------+------------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students ,teachers where students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 32 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select s.name 学生姓名, s.age 学生年龄, s.gender 学生性别, t.name 老师姓名,t.age 老师年龄, t.gender 老师性别 from students s inner join teachers t on s.gender <> t.gender;
+---------------+--------------+--------------+---------------+--------------+--------------+
| 学生姓名 | 学生年龄 | 学生性别 | 老师姓名 | 老师年龄 | 老师性别 |
+---------------+--------------+--------------+---------------+--------------+--------------+
| Shi Zhongyu | 22 | M | Miejue Shitai | 77 | F |
| Shi Zhongyu | 22 | M | Lin Chaoying | 32 | F |
| Shi Potian | 22 | M | Miejue Shitai | 77 | F |
| Shi Potian | 22 | M | Lin Chaoying | 32 | F |
| Xie Yanke | 53 | M | Miejue Shitai | 77 | F |
| Xie Yanke | 53 | M | Lin Chaoying | 32 | F |
| Ding Dian | 32 | M | Miejue Shitai | 77 | F |
| Ding Dian | 32 | M | Lin Chaoying | 32 | F |
| Yu Yutong | 26 | M | Miejue Shitai | 77 | F |
| Yu Yutong | 26 | M | Lin Chaoying | 32 | F |
| Shi Qing | 46 | M | Miejue Shitai | 77 | F |
| Shi Qing | 46 | M | Lin Chaoying | 32 | F |
| Xi Ren | 19 | F | Song Jiang | 45 | M |
| Xi Ren | 19 | F | Zhang Sanfeng | 94 | M |
| Lin Daiyu | 17 | F | Song Jiang | 45 | M |
| Lin Daiyu | 17 | F | Zhang Sanfeng | 94 | M |
| Ren Yingying | 20 | F | Song Jiang | 45 | M |
| Ren Yingying | 20 | F | Zhang Sanfeng | 94 | M |
| Yue Lingshan | 19 | F | Song Jiang | 45 | M |
| Yue Lingshan | 19 | F | Zhang Sanfeng | 94 | M |
| Yuan Chengzhi | 23 | M | Miejue Shitai | 77 | F |
| Yuan Chengzhi | 23 | M | Lin Chaoying | 32 | F |
| Wen Qingqing | 19 | F | Song Jiang | 45 | M |
| Wen Qingqing | 19 | F | Zhang Sanfeng | 94 | M |
| Tian Boguang | 33 | M | Miejue Shitai | 77 | F |
| Tian Boguang | 33 | M | Lin Chaoying | 32 | F |
| Lu Wushuang | 17 | F | Song Jiang | 45 | M |
| Lu Wushuang | 17 | F | Zhang Sanfeng | 94 | M |
| Duan Yu | 19 | M | Miejue Shitai | 77 | F |
| Duan Yu | 19 | M | Lin Chaoying | 32 | F |
| Xu Zhu | 21 | M | Miejue Shitai | 77 | F |
| Xu Zhu | 21 | M | Lin Chaoying | 32 | F |
| Lin Chong | 25 | M | Miejue Shitai | 77 | F |
| Lin Chong | 25 | M | Lin Chaoying | 32 | F |
| Hua Rong | 23 | M | Miejue Shitai | 77 | F |
| Hua Rong | 23 | M | Lin Chaoying | 32 | F |
| Xue Baochai | 18 | F | Song Jiang | 45 | M |
| Xue Baochai | 18 | F | Zhang Sanfeng | 94 | M |
| Diao Chan | 19 | F | Song Jiang | 45 | M |
| Diao Chan | 19 | F | Zhang Sanfeng | 94 | M |
| Huang Yueying | 22 | F | Song Jiang | 45 | M |
| Huang Yueying | 22 | F | Zhang Sanfeng | 94 | M |
| Xiao Qiao | 20 | F | Song Jiang | 45 | M |
| Xiao Qiao | 20 | F | Zhang Sanfeng | 94 | M |
| Ma Chao | 23 | M | Miejue Shitai | 77 | F |
| Ma Chao | 23 | M | Lin Chaoying | 32 | F |
| Xu Xian | 27 | M | Miejue Shitai | 77 | F |
| Xu Xian | 27 | M | Lin Chaoying | 32 | F |
| Sun Dasheng | 100 | M | Miejue Shitai | 77 | F |
| Sun Dasheng | 100 | M | Lin Chaoying | 32 | F |
| xiong | 77 | M | Miejue Shitai | 77 | F |
| xiong | 77 | M | Lin Chaoying | 32 | F |
| test | 93 | M | Miejue Shitai | 77 | F |
| test | 93 | M | Lin Chaoying | 32 | F |
+---------------+--------------+--------------+---------------+--------------+--------------+
MariaDB [hellodb]> select stuid,s.name,tid,t.name from students s, teachers t where s.teacherid=t.tid;
+-------+-------------+-----+---------------+
| stuid | name | tid | name |
+-------+-------------+-----+---------------+
| 5 | Yu Yutong | 1 | Song Jiang |
| 1 | Shi Zhongyu | 3 | Miejue Shitai |
| 4 | Ding Dian | 4 | Lin Chaoying |
+-------+-------------+-----+---------------+
3 rows in set (0.00 sec)
#内连接后过滤数据
MariaDB [hellodb]> select * from students s inner join teachers t on s.teacherid=t.tid and s.age > 30;
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 32 | F |
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from students s inner join teachers t on s.teacherid=t.tid where s.age > 30;
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 32 | F |
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from students inner join teachers using(gender);
等同于
MariaDB [hellodb]> select * from students inner join teachers on students.gender = teachers.gender;3.5 左外连接
MySQL中的左连接用于从多个表中查询记录。该子句类似于内部联接子句 , 可与FROM关键字后面的SELECT语句一起使用。当我们使用Left Join子句时 , 它将返回第一个(左侧)表中的所有记录 , 返回从第二个（右侧）表中找到匹配的记录。如果在右边的表中找不到任何匹配记录 , 则返回Null。
换句话说 , Left Join子句返回左表中的所有行和右表中的匹配记录 , 如果没有找到匹配记录 , 则返回Null。这个连接也可以称为左外连接子句。因此 , Outer是用于Left Join的可选关键字。
语法 :
SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON Join_Condition;
范例 : 左外连接
#左外连接
MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid;
+-------+---------------+-----+-----------+------+---------------+------+
| stuid | name | age | teacherid | tid | name | age |
+-------+---------------+-----+-----------+------+---------------+------+
| 1 | Shi Zhongyu | 22 | 3 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | 7 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | 16 | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | 4 | Lin Chaoying | 32 |
| 5 | Yu Yutong | 26 | 1 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | NULL | NULL | NULL | NULL |
| 26 | xiong | 77 | NULL | NULL | NULL | NULL |
| 27 | test | 93 | NULL | NULL | NULL | NULL |
+-------+---------------+-----+-----------+------+---------------+------+
27 rows in set (0.00 sec)
#左外连接扩展
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid where t.tid is null;
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 26 | xiong | 77 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 27 | test | 93 | M | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
24 rows in set (0.00 sec)
#多个条件的左外连接
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid and s.teacherid is null ;
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | NULL | NULL | NULL | NULL |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 | NULL | NULL | NULL | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 | NULL | NULL | NULL | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 26 | xiong | 77 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 27 | test | 93 | M | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
27 rows in set (0.00 sec)
#先左外连接,再过滤
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid where s.teacherid is null;
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 26 | xiong | 77 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 27 | test | 93 | M | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
22 rows in set (0.00 sec)3.6 右外连接
右联接用于联接两个或多个表 , 并返回右表中的所有行 , 以及仅返回另一个表中满足联接条件的结果。如果从左侧表中找到不匹配的记录 ,则返回空值。它类似于左连接 , 只是它给出了联接表的相反结果。它也被称为右外连接。所以 , Outer是与Right Join一起使用的可选子句。
语法
SELECT column_list FROM Table1 RIGHT [OUTER] JOIN Table2 ON join_condition;
范例 :
#右外连接
MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid=t.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 32 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
#右外连接的扩展用法
MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid=t.tid where s.teacherid is null;
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
1 row in set (0.00 sec)3.7 完全外连接
当左(表1)或右(表2)表记录匹配时 , FULL OUTER JOIN关键字将返回所有记录。 FULL JOIN 即为LEFT JOIN与 RIGHT JOIN的合集。mysql安装包中没有FULL JOIN命令, 但是可以用UNION来模拟实现。
语法
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
范例 : 完全外连接
#完全外连接 full outer join
MariaDB [hellodb]> select * from students left join teachers on students.teacherid=teachers.tid
-> union
-> select * from students right join teachers on students.teacherid=teachers.tid;
MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left join teachers as t on s.teacherid=t.tid
-> union
-> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right join teachers as t on s.teacherid=t.tid;
+-------+---------------+------+------+---------------+------+
| stuid | name | age | tid | name | age |
+-------+---------------+------+------+---------------+------+
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 32 |
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | NULL | NULL | NULL |
| 26 | xiong | 77 | NULL | NULL | NULL |
| 27 | test | 93 | NULL | NULL | NULL |
| NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 |
+-------+---------------+------+------+---------------+------+
28 rows in set (0.00 sec)
#完全外连接的扩展示例
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid where t.tid is null union select * from students s right outer join teachers t on s.teacherid=t.tid where s.teacherid is null;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL
MariaDB [hellodb]> select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s left outer join teachers t on s.teacherrid=t.tid union select s.stuid,s.name,s.teacherid,t.tid,t.name from students s right outer join teachers t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;
+-------+---------------+-----------+------+---------------+
| stuid | s_name | teacherid | tid | t_name |
+-------+---------------+-----------+------+---------------+
| 2 | Shi Potian | 7 | NULL | NULL |
| 3 | Xie Yanke | 16 | NULL | NULL |
| 6 | Shi Qing | NULL | NULL | NULL |
| 7 | Xi Ren | NULL | NULL | NULL |
| 8 | Lin Daiyu | NULL | NULL | NULL |
| 9 | Ren Yingying | NULL | NULL | NULL |
| 10 | Yue Lingshan | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | NULL | NULL | NULL |
| 12 | Wen Qingqing | NULL | NULL | NULL |
| 13 | Tian Boguang | NULL | NULL | NULL |
| 14 | Lu Wushuang | NULL | NULL | NULL |
| 15 | Duan Yu | NULL | NULL | NULL |
| 16 | Xu Zhu | NULL | NULL | NULL |
| 17 | Lin Chong | NULL | NULL | NULL |
| 18 | Hua Rong | NULL | NULL | NULL |
| 19 | Xue Baochai | NULL | NULL | NULL |
| 20 | Diao Chan | NULL | NULL | NULL |
| 21 | Huang Yueying | NULL | NULL | NULL |
| 22 | Xiao Qiao | NULL | NULL | NULL |
| 23 | Ma Chao | NULL | NULL | NULL |
| 24 | Xu Xian | NULL | NULL | NULL |
| 25 | Sun Dasheng | NULL | NULL | NULL |
| 26 | xiong | NULL | NULL | NULL |
| 27 | test | NULL | NULL | NULL |
| NULL | NULL | NULL | 2 | Zhang Sanfeng |
+-------+---------------+-----------+------+---------------+
25 rows in set (0.00 sec)3.8 自连接
自连接是用于将表与自身联结。一般情况下,如果需要将数据与同一个表中的其他数据结合起来 ,需要使用自连接。使用过程中,可以使用表别名的方式规避查询过程中多次使用同一个表名报错的问题。
语法
SELECT s1.col_name, s2.col_name... FROM table1 s1, table1 AS s2 WHERE s1.common_col_name = s2.common_col_name;SELECT s1.col_name, s2.col_name... FROM table1 s1 INNER JOIN table1 AS s2 on s1.common_col_name = s2.common_col_name;
范例 : 自连接
MariaDB [hellodb]> create table emp ( id int unsigned auto_increment primary key, name varchar(20) not null , leaderid int unsigned );
MariaDB [hellodb]> insert emp ( id , name ) values ( 1 , "leader");
MariaDB [hellodb]> insert emp ( id , name , leaderid ) values ( 2, "emp1" , 1 );
MariaDB [hellodb]> insert emp ( id , name , leaderid ) values ( 3, "emp2" , 2 );
MariaDB [hellodb]> insert emp ( id , name , leaderid ) values ( 4, "emp3" , 3 );
MariaDB [hellodb]> select * from emp;
+----+--------+----------+
| id | name | leaderid |
+----+--------+----------+
| 1 | leader | NULL |
| 2 | emp1 | 1 |
| 3 | emp2 | 2 |
| 4 | emp3 | 3 |
+----+--------+----------+
4 rows in set (0.00 sec)
#自连接配置
MariaDB [hellodb]> select * from emp as e inner join emp as l on e.leaderid=l.id;
+----+------+----------+----+--------+----------+
| id | name | leaderid | id | name | leaderid |
+----+------+----------+----+--------+----------+
| 2 | emp1 | 1 | 1 | leader | NULL |
| 3 | emp2 | 2 | 2 | emp1 | 1 |
| 4 | emp3 | 3 | 3 | emp2 | 2 |
+----+------+----------+----+--------+----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select e.name,l.name leader_name from emp as e inner join emp as l on e.leaderid=l.id;
+------+-------------+
| name | leader_name |
+------+-------------+
| emp1 | leader |
| emp2 | emp1 |
| emp3 | emp2 |
+------+-------------+
3 rows in set (0.00 sec)
#左外自连接
MariaDB [hellodb]> select * from emp as e left join emp as l on e.leaderid=l.id;
+----+--------+----------+------+--------+----------+
| id | name | leaderid | id | name | leaderid |
+----+--------+----------+------+--------+----------+
| 1 | leader | NULL | NULL | NULL | NULL |
| 2 | emp1 | 1 | 1 | leader | NULL |
| 3 | emp2 | 2 | 2 | emp1 | 1 |
| 4 | emp3 | 3 | 3 | emp2 | 2 |
+----+--------+----------+------+--------+----------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select e.name ,l.name from emp as e left join emp as l on e.leaderid=l.id;
+--------+--------+
| name | name |
+--------+--------+
| leader | NULL |
| emp1 | leader |
| emp2 | emp1 |
| emp3 | emp2 |
+--------+--------+
4 rows in set (0.01 sec)
MariaDB [hellodb]> select e.name emp ,IFNULL(l.name ,'无上级') leader from emp as e left join emp as l on e.leaderid=l.id;
+--------+-----------+
| emp | leader |
+--------+-----------+
| leader | 无上级 |
| emp1 | leader |
| emp2 | emp1 |
| emp3 | emp2 |
+--------+-----------+
4 rows in set (0.00 sec)范例 : 三表连接
MariaDB [hellodb]> select name,course,score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.CourseID;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
15 rows in set (0.00 sec)3.9 DELETE JOIN
3.9.1 使用INNER JOIN进行删除联接
内部联接查询可以与删除查询一起使用 , 以删除一个表中满足指定条件的表中的行和另一表中满足指定条件的匹配行。
语法
DELETE target table FROM table1 INNER JOIN table2 ON table1.joining_column= table2.joining_column WHERE condition在这里, 目标是一个表名 , 我们要通过匹配指定条件从中删除行。假设您要从表
T1和T2中删除行 , 其中student_id = 2, 则可以将其写为以下语句:DELETE T1, T2 FROM T1 INNER JOIN T2 ON T1.student_id=T2.student.id WHERE T1.student_id=2;在以上语法中 , 目标表(
T1和T2)写在DELETE和FROM关键字之间。如果我们从那里省略任何表名 , 那么delete语句只会从单个表中删除行 。用ON关键字编写的表达式是与要删除的表中的行匹配的条件。范例
假设我们有两个表 students和 contacts , 其中包含以下数据:
表:
students
表:
contacts
执行以下查询以了解使用内部联接删除。该语句删除两个表中具有相同
ID的行。DELETE students, contacts FROM students INNER JOIN contacts ON students.student_id=contacts.college_id WHERE students.student_id = 4;成功执行后 , 它将给出以下消息:
现在 , 运行以下查询以验证已成功删除行。
mysql> SELECT * FROM students; mysql> SELECT * FROM contacts;您可以看到
student_id = 4所在的行已删除。
3.9.2 使用左联接删除JOIN
LEFT JOIN该子句从左(第一个)返回所有行表和另一个表中匹配或不匹配的行。同样 , 我们也可以使用带有DELETE 关键字的LEFT JOIN子句从左(第一个)表中删除行 , 而右(第二个)表中没有匹配的行。
以下查询对此进行了解释更清楚地说明DELETE语句使用LEFT JOIN从Table1 中删除在 Table2中没有匹配行的行:
DELETE Table1 FROM Table1
LEFT JOIN Table2 ON Table1.key = Table2.key
WHERE Table2.key IS null;在上面的查询中 , 请注意 , 我们将仅将Table1与DELETE关键字一起使用 , 而不是像INNER JOIN语句一样使用。
示例
让我们创建一个包含以下数据的数据库中的" contacts"表和" customers":
表: contacts
表: customers
执行以下语句 , 删除没有 手机号码的客户:
DELETE customers FROM customers
LEFT JOIN contacts ON customers.customer_id = contacts.contact_id
WHERE cellphone IS null;成功执行后 , 它将给出以下消息:
现在 , 运行以下查询以验证已成功删除行。
mysql> SELECT * FROM customers;您可以看到客户没有手机号的行已删除。
3.10 UPDATE JOIN
MySQL中的更新查询是用于修改表数据的DML语句。UPDATE查询必须需要SET和WHERE子句。SET子句用于更改WHERE子句中指定的列的值。
UPDATE JOIN是一个MySQL语句 , 用于执行跨表更新 , 这意味着我们可以使用另一个具有JOIN子句条件的表来更新一个表。此查询将根据主键和外键以及指定的连接条件更新和更改连接多个表的数据。我们可以使用UPDATE 查询一次更新单列或多列。
语法
UPDATE Tab1, Tab2, [INNER JOIN | LEFT JOIN] Tab1 ON Tab1.C1 = Tab2.C1 SET Tab1.C2 = Tab2.C2, Tab2.C3 = expression WHERE Condition;
MySQL中的UPDATE JOIN工作过程与以上语法中描述。但是有时 , 我们会发现此查询仅执行交叉表更新而没有涉及任何联接。 以下语法是使用另一个表更新一个表的另一种方式:
UPDATE Tab1, Tab2,
SET Tab1.C2 = Tab2.C2, Tab2.C3 = expression
WHERE Tab1.C1 = Tab2.C1 AND condition;上面的UPDATE语句产生的结果与带有INNER JOIN或LEFT JOIN子句的UPDATE JOIN相同。这意味着我们可以将上面的语法重新编写为上面显示的UPDATE JOIN语法 。
UPDATE Tab1,Tab2
INNER JOIN Tab2 ON Tab1.C1 = Tab2.C1
SET Tab1.C2 = Taba2.C2, Tab2.C3 = expression
WHERE condition范例
表:
PerformanceCREATE TABLE Performance ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) );接下来,使用
INSERT语句将记录填充到表中INSERT INTO Performance (performance, percentage) VALUES(101,0), (102,0.01), (103,0.03), (104,0.05), (105,0.08);查验数据:
表:
EmployeesCREATE TABLE Employees ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, salary FLOAT DEFAULT NULL, CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES Performance (performance) );接下来 , 使用
INSERT语句将记录填充到表中。INSERT INTO Employees (name, performance, salary) VALUES('Mary', 101, 55000), ('John', 103, 65000), ('Suzi', 104, 85000), ('Gracia', 105, 110000), ('Nancy Johnson', 103, 95000), ('Joseph', 102, 45000), ('Donald', 103, 50000);验证数据结果
使用INNER JOIN示例进行更新JOIN
假设我们要根据 雇员的工资来更新他们的工资。因为绩效 percentage 存储在绩效表中 , 所以我们可以在
UPDATE表中使用UPDATE INNER JOIN语句来更新雇员的工资。在上面的表中 , 我们必须使用 绩效字段加入"员工和绩效"表。
在查询中 , 我们仅在
UPDATE子句之后指定了Employees表。这是因为我们只想更改Employees表中的记录 , 而不要同时更改两个表中的记录。该查询更新了
Employees表中的所有记录 , 因为我们没有在UPDATE JOIN查询中指定WHERE子句。使用LEFT JOIN示例的UPDATE JOIN
要了解使用
LEFT JOIN进行UPDATE JOIN, 我们首先需要在Employees表中插入两行:需要在
Employees表中插入两行:INSERT INTO Employees (name, performance, salary) VALUES('William', null, 73000), ('Rayan', null, 92000);
如果我们要更新工资对于新雇用的员工 , 我们不能使用
UPDATE INNER JOIN查询。这是由于"性能"表中的性能数据不可用。因此 , 我们将使用UPDATE LEFT JOIN语句来满足此需求。当另一个表的对应行中没有记录时 ,
MySQL中的UPDATE LEFT JOIN语句用于更新表中的行。例如 , 如果我们想将新雇用的员工的薪水提高2.5％ , 则可以在以下语句的帮助下进行:
UPDATE Employees e LEFT JOIN Performance p ON e.performance = p.performance SET salary = salary + salary * 0.025 WHERE p.percentage IS null;
我们可以看到新雇用员工的薪水已成功更新。
4. SELECT 语句处理的顺序
关于本图需要说明的是 , 虽然图中给出的顺序是DISTINCT比ORDER BY先执行 , 这也是网上流传的版本。但其实 , 在DISTINCT和ORDER BY之间的顺序没有严格的界限 , 甚至ORDER BY的顺序要优先于DISTINCT 。
SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT < limit_number >查询执行路径中的组件 : 查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎