MySQL数据库（四）SQL之DQL语句

最新推荐文章于 2024-04-20 15:52:33 发布
最新推荐文章于 2024-04-20 15:52:33 发布
阅读量361 收藏 2
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 mariadb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46238755/article/details/106599201
版权

成功不易，加倍努力！

DQL语句

1 单表操作

语法：

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]]

说明：

  • 字段显示可以使用别名：
    col1 AS alias1, col2 AS alias2, … as可省略
  • WHERE子句：指明过滤条件以实现“选择”的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >,>=, <, <=
BETWEEN min_num AND max_num
**IN (element1, element2, ...)   从括号中的元素取一个**
IS NULL
IS NOT NULL
DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;
LIKE:
  % 任意长度的任意字符
  _ 任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：NOT，AND，OR，XOR
  • GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
    常见聚合函数：avg(), max(), min(), count(), sum()
    WHERE 放在分组前面
    HAVING: 对分组聚合运算后的结果指定过滤条件
    一旦分组group by ，select语句后只跟分组的字段，聚合函数
  • ORDER BY: 根据指定的字段对查询结果进行排序
    升序：ASC
    降序：DESC
  • LIMIT [[offset,]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.000 sec)

范例：简单查询

DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2; 
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE ‘t%’
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students

select * from students where classid in (1,3,5);
select * from students where classid not in (1,3,5);

范例：判断是否为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.002 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.000 sec)

MariaDB [hellodb]> select * from students where classid is not null;

范例： 去重

MariaDB [hellodb]> select distinct gender from students ;
+--------+
| gender |
+--------+
| M 	 |
| F 	 |
+--------+
2 rows in set (0.001 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='magedu123';
select * from user where name='admin'; # ' and password='magedu123';

范例：分组统计

#分组统计
select classid,avg(age) as 平均年龄 from students where classid > 3 group by classid having 平均年龄 >30 ;

select gender,avg(age) 平均年龄 from students group by gender having gender='M';
select classid,gender,avg(age) from students group by classid,gender;

范例：排序

select classid,sum(age) from students where classid is not null group by classid order by classid;
select classid,sum(age) from students group by classid having classid is not null order by classid;
select classid,sum(age) from students where classid is not null group by classid order by classid limit 2,3;

#必须先过滤，再排序
select * from students where classid is not null order by gender desc, age asc ;

#多列排序
select * from students order by gender desc, age asc;

范例：正序排序，NULL记录排在最后

#对classid 正序排序，NULL记录排在最后
select * from students order by -classid desc ;

范例：分组和排序

#分组后再排序
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.0000  |
| F 	 | 		 3 | 18.3333  |
| F 	 | 		 6 | 20.0000  |
| F 	 | 		 7 | 18.0000  |
| F 	 | 		77 | 18.0000  |
| F 	 | 		93 | 18.0000  |
| M 	 | 		 1 | 21.5000  |
| M 	 | 		 2 | 35.2000  |
| M 	 | 		 3 | 23.0000  |
| M 	 | 		 4 | 23.6000  |
| M 	 | 		 5 | 46.0000  |
| M 	 | 		 6 | 23.0000  |
| M 	 | 		 7 | 23.0000  |
| M 	 | 		94 | 18.0000  |
+--------+---------+----------+
14 rows in set (0.001 sec)
MariaDB [hellodb]> select * from students order by age limit 10;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name 			| Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 14 	| Lu Wushuang 	| 17  | F 	   | 3 		 | NULL 	 |
| 8 	| Lin Daiyu 	| 17  | F	   | 7 		 | NULL		 |
| 33 	| Miejue Shitai | 18  | F	   | 77		 | NULL		 |
| 32 	| Zhang Sanfeng | 18  | M	   | 94		 | NULL		 |
| 27 	| liudehua 		| 18  | F	   | 1		 | NULL		 |
| 34    | Lin Chaoying  | 18  | F	   | 93		 | NULL		 |
| 19 	| Xue Baochai   | 18  | F	   | 6		 | NULL		 |
| 7 	| Xi Ren 		| 19  | F	   | 3		 | NULL		 |
| 12 	| Wen Qingqing  | 19  | F	   | 1		 | NULL		 |
| 20    | Diao Chan 	| 19  | F	   | 7		 | NULL		 |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.001 sec)

MariaDB [hellodb]> select * from students order by age limit 3,10;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name 			| Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 34 	| Lin Chaoying  | 18  | F 	   | 93 	 | NULL 	 |
| 19 	| Xue Baochai 	| 18  | F 	   | 6 		 | NULL	 	 |
| 32 	| Zhang Sanfeng | 18  | M 	   | 94		 | NULL		 |
| 27 	| liudehua 		| 18  | F	   | 1		 | NULL		 |
| 20 	| Diao Chan 	| 19  | F	   | 7		 | NULL		 |
| 12 	| Wen Qingqing  | 19  | F 	   | 1		 | NULL		 |
| 10 	| Yue Lingshan  | 19  | F 	   | 3		 | NULL		 |
| 15 	| Duan Yu 		| 19  | M	   | 4		 | NULL		 |
| 7 	| Xi Ren 		| 19  | F 	   | 3		 | NULL		 |
| 29 	| wuyanzu 		| 19  | M 	   | 4		 | NULL		 |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.000 sec)

MariaDB [hellodb]> select distinct age from students order by age limit 3 ;
+-----+
| age |
+-----+
| 17  |
| 18  |
| 19  |
+-----+
3 rows in set (0.001 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.001 sec)

范例：时间字段进行过滤查询

MariaDB [testdb]>create table testdate (id int,date timestamp DEFAULT
CURRENT_TIMESTAMP );
MariaDB [testdb]> insert testdate (id) values(1);
MariaDB [testdb]> insert testdate (id) values(2);

MariaDB [testdb]> select * from testdate;
+----+---------------------+
| id | date				   |
+----+---------------------+
| 1  | 2020-06-03 15:21:03 |
| 2  | 2020-06-03 15:21:12 |
| 3  | 2020-06-03 15:21:14 |
| 4  | 2020-06-03 15:21:17 |
| 5  | 2020-06-03 18:27:39 |
| 6  | 2020-06-03 18:27:44 |
+----+---------------------+
6 rows in set (0.001 sec)

MariaDB [testdb]> select * from testdate where date between '2020-06-03
15:21:12' and '2020-06-03 18:27:40';
+----+---------------------+
| id | 				  date |
+----+---------------------+
| 2  | 2020-06-03 15:21:12 |
| 3  | 2020-06-03 15:21:14 |
| 4  | 2020-06-03 15:21:17 |
| 5  | 2020-06-03 18:27:39 |
+----+---------------------+
4 rows in set (0.000 sec)

MariaDB [testdb]> select * from testdate where date >= '2020-06-03 15:21:12'
and date <= '2020-06-03 18:27:40';
+----+---------------------+
| id | 				 date  |
+----+---------------------+
| 2  | 2020-06-03 15:21:12 |
| 3  | 2020-06-03 15:21:14 |
| 4  | 2020-06-03 15:21:17 |
| 5  | 2020-06-03 18:27:39 |
+----+---------------------+
4 rows in set (0.001 sec)

2 多表查询

在这里插入图片描述
多表查询，即查询结果来自于多张表

  • 子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
  • 联合查询：UNION
  • 交叉连接：笛卡尔乘积
  • 内连接：
    • 等值连接：让表之间的字段以“等值”建立连接关系
      不等值连接
    • 自然连接：去掉重复列的等值连接
  • 外连接：
    • 左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
    • 右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
  • 自连接：本表和本表进行连接查询
2.1 子查询

常用在WHERE子句中的子查询

  1. 用于比较表达式中的子查询；子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
  1. 用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
  1. 用于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，外查询的这一行数据便可作为外查询的结果行返回，否则不能作为结果

Select * from TableA a where Exists （Select * from TableB b where
a.id=b.id and a.name=b.name）;
#TableA a 给表TableA取别名为a

MariaDB [hellodb]> select * from students s where EXISTS (select * from teachers t where s.teacherid=t.tid);
+-------+-------------+-----+--------+---------+-----------+
| 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.001 sec)

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 |
+-------+---------------+-----+--------+---------+-----------+
22 rows in set (0.001 sec)

说明：

1、Exists（Not Exists）用在where之后，且后面紧跟子查询语句（带括号）
2、Not Exists(Exists) 只关心子查询有没有结果,并不关心子查询的结果具体是什么
3、把TableA的记录逐条代入到子查询，如果子查询结果集为空，说明不存在，那么这条TableA的记录出现在最终结果集，否则被排除

  1. 用于FROM子句中的子查询

使用格式：

SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;

范例：

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;

范例：子查询

#子查询：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 |
+-------+--------------+-----+
5 rows in set (0.001 sec)

范例：子查询用于更新表

把四号老师的年龄更改为学生的平均年龄
MariaDB [hellodb]> update teachers set age=(select avg(age) from students) where tid=4;
Query OK, 1 row affected (0.003 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  |  29 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.000 sec)
2.2 联合查询：UNION
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;

范例：联合查询

#多表纵向合并union
MariaDB [hellodb]> select * from teachers union select * from students;
MariaDB [hellodb]> select * from teachers union select stuid,name,age,gender from students;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  29 | F      |
|   1 | Shi Zhongyu   |  45 | M      |
|   2 | Shi Potian    |  22 | M      |
|   3 | Xie Yanke     |  77 | 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      |
+-----+---------------+-----+--------+
29 rows in set (0.002 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  |  29 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.001 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  |  29 | F      |
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  29 | F      |
+-----+---------------+-----+--------+
8 rows in set (0.001 sec)
2.3 交叉连接

即笛卡尔乘积，“雨露均沾”，利用 cross join实现

范例：交叉连接

#横向合并，交叉连接（横向笛卡尔）
MariaDB [hellodb]> select * from students cross join teachers;
MariaDB [hellodb]> select * from teachers,students;
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| TID | Name          | Age | Gender | StuID | Name          | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
|   1 | Song Jiang    |  45 | M      |     1 | Shi Zhongyu   |  45 | M      |       2 |         3 |
|   2 | Zhang Sanfeng |  94 | M      |     1 | Shi Zhongyu   |  45 | M      |       2 |         3 |
|   3 | Miejue Shitai |  77 | F      |     1 | Shi Zhongyu   |  45 | M      |       2 |         3 |
|   4 | Lin Chaoying  |  29 | F      |     1 | Shi Zhongyu   |  45 | M      |       2 |         3 |
|   1 | Song Jiang    |  45 | M      |     2 | Shi Potian    |  22 | M      |       1 |         7 |
|   2 | Zhang Sanfeng |  94 | M      |     2 | Shi Potian    |  22 | M      |       1 |         7 |
|   3 | Miejue Shitai |  77 | F      |     2 | Shi Potian    |  22 | M      |       1 |         7 |
|   4 | Lin Chaoying  |  29 | F      |     2 | Shi Potian    |  22 | M      |       1 |         7 |
|   1 | Song Jiang    |  45 | M      |     3 | Xie Yanke     |  77 | M      |       2 |        16 |
|   2 | Zhang Sanfeng |  94 | M      |     3 | Xie Yanke     |  77 | M      |       2 |        16 |
|   3 | Miejue Shitai |  77 | F      |     3 | Xie Yanke     |  77 | M      |       2 |        16 |
|   4 | Lin Chaoying  |  29 | F      |     3 | Xie Yanke     |  77 | M      |       2 |        16 |
|   1 | Song Jiang    |  45 | M      |     4 | Ding Dian     |  32 | M      |       4 |         4 |
|   2 | Zhang Sanfeng |  94 | M      |     4 | Ding Dian     |  32 | M      |       4 |         4 |
|   3 | Miejue Shitai |  77 | F      |     4 | Ding Dian     |  32 | M      |       4 |         4 |
|   4 | Lin Chaoying  |  29 | F      |     4 | Ding Dian     |  32 | M      |       4 |         4 |
|   1 | Song Jiang    |  45 | M      |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|   2 | Zhang Sanfeng |  94 | M      |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|   3 | Miejue Shitai |  77 | F      |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|   4 | Lin Chaoying  |  29 | F      |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|   1 | Song Jiang    |  45 | M      |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|   3 | Miejue Shitai |  77 | F      |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|   1 | Song Jiang    |  45 | M      |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|   3 | Miejue Shitai |  77 | F      |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|   1 | Song Jiang    |  45 | M      |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|   3 | Miejue Shitai |  77 | F      |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|   1 | Song Jiang    |  45 | M      |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|   3 | Miejue Shitai |  77 | F      |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|   1 | Song Jiang    |  45 | M      |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|   1 | Song Jiang    |  45 | M      |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|   1 | Song Jiang    |  45 | M      |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|   1 | Song Jiang    |  45 | M      |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|   1 | Song Jiang    |  45 | M      |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|   1 | Song Jiang    |  45 | M      |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|   1 | Song Jiang    |  45 | M      |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|   1 | Song Jiang    |  45 | M      |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|   1 | Song Jiang    |  45 | M      |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|   1 | Song Jiang    |  45 | M      |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|   1 | Song Jiang    |  45 | M      |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|   1 | Song Jiang    |  45 | M      |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|   1 | Song Jiang    |  45 | M      |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|   1 | Song Jiang    |  45 | M      |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|   3 | Miejue Shitai |  77 | F      |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|   1 | Song Jiang    |  45 | M      |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|   3 | Miejue Shitai |  77 | F      |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|   1 | Song Jiang    |  45 | M      |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|   2 | Zhang Sanfeng |  94 | M      |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|   3 | Miejue Shitai |  77 | F      |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|   4 | Lin Chaoying  |  29 | F      |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
100 rows in set (0.002 sec)


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   |  45 |   1 | Song Jiang    |  45 |
|     1 | Shi Zhongyu   |  45 |   2 | Zhang Sanfeng |  94 |
|     1 | Shi Zhongyu   |  45 |   3 | Miejue Shitai |  77 |
|     1 | Shi Zhongyu   |  45 |   4 | Lin Chaoying  |  29 |
|     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  |  29 |
|     3 | Xie Yanke     |  77 |   1 | Song Jiang    |  45 |
|     3 | Xie Yanke     |  77 |   2 | Zhang Sanfeng |  94 |
|     3 | Xie Yanke     |  77 |   3 | Miejue Shitai |  77 |
|     3 | Xie Yanke     |  77 |   4 | Lin Chaoying  |  29 |
|     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  |  29 |
|     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  |  29 |
|     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  |  29 |
|     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  |  29 |
|     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  |  29 |
|     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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
|    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  |  29 |
+-------+---------------+-----+-----+---------------+-----+
100 rows in set (0.001 sec)
2.4 内连接

范例：内连接,都符合某一条件

#内连接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 |  45 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  29 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.001 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.001 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 |         45 |   3 | Miejue Shitai |         77 |
|     4 | Ding Dian   |         32 |   4 | Lin Chaoying  |         29 |
+-------+-------------+------------+-----+---------------+------------+
3 rows in set (0.001 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 |  45 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  29 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.001 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   |           45 | M            | Miejue Shitai |           77 | F            |
| Shi Zhongyu   |           45 | M            | Lin Chaoying  |           29 | F            |
| Shi Potian    |           22 | M            | Miejue Shitai |           77 | F            |
| Shi Potian    |           22 | M            | Lin Chaoying  |           29 | F            |
| Xie Yanke     |           77 | M            | Miejue Shitai |           77 | F            |
| Xie Yanke     |           77 | M            | Lin Chaoying  |           29 | F            |
| Ding Dian     |           32 | M            | Miejue Shitai |           77 | F            |
| Ding Dian     |           32 | M            | Lin Chaoying  |           29 | F            |
| Yu Yutong     |           26 | M            | Miejue Shitai |           77 | F            |
| Yu Yutong     |           26 | M            | Lin Chaoying  |           29 | F            |
| Shi Qing      |           46 | M            | Miejue Shitai |           77 | F            |
| Shi Qing      |           46 | M            | Lin Chaoying  |           29 | 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  |           29 | 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  |           29 | 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  |           29 | F            |
| Xu Zhu        |           21 | M            | Miejue Shitai |           77 | F            |
| Xu Zhu        |           21 | M            | Lin Chaoying  |           29 | F            |
| Lin Chong     |           25 | M            | Miejue Shitai |           77 | F            |
| Lin Chong     |           25 | M            | Lin Chaoying  |           29 | F            |
| Hua Rong      |           23 | M            | Miejue Shitai |           77 | F            |
| Hua Rong      |           23 | M            | Lin Chaoying  |           29 | 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  |           29 | F            |
| Xu Xian       |           27 | M            | Miejue Shitai |           77 | F            |
| Xu Xian       |           27 | M            | Lin Chaoying  |           29 | F            |
| Sun Dasheng   |          100 | M            | Miejue Shitai |           77 | F            |
| Sun Dasheng   |          100 | M            | Lin Chaoying  |           29 | F            |
+---------------+--------------+--------------+---------------+--------------+--------------+
50 rows in set (0.001 sec)

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.001 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 |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |  45 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  29 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
2 rows in set (0.001 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 |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |  45 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  29 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
2 rows in set (0.000 sec)
2.5 左和右外连接

范例：左，右外连接，outer可以省略

#左外连接
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   |  45 |         3 |    3 | Miejue Shitai |   77 |
|     2 | Shi Potian    |  22 |         7 | NULL | NULL          | NULL |
|     3 | Xie Yanke     |  77 |        16 | NULL | NULL          | NULL |
|     4 | Ding Dian     |  32 |         4 |    4 | Lin Chaoying  |   29 |
|     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 |
+-------+---------------+-----+-----------+------+---------------+------+
25 rows in set (0.002 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     |  77 | 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   |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
22 rows in set (0.001 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   |  45 | M      |       2 |         3 | NULL | NULL | NULL | NULL   |
|     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL | NULL | NULL   |
|     3 | Xie Yanke     |  77 | 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   |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
25 rows in set (0.000 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   |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
20 rows in set (0.001 sec)

#右外连接
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 |   45 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |   32 | M      |       4 |         4 |   4 | Lin Chaoying  |  29 | 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.001 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.000 sec)
2.6 完全外连接

范例：完全外连接，MySQL不支持

#完全外连接 full outer join
MariaDB [hellodb]> select * from students s left join teachers t on s.teacherid=t.tid
    -> union
    -> select * from students s right join teachers t on s.teacherid=t.tid;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age  | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
|     1 | Shi Zhongyu   |   45 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     2 | Shi Potian    |   22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |   77 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     4 | Ding Dian     |   32 | M      |       4 |         4 |    4 | Lin Chaoying  |   29 | F      |
|     5 | Yu Yutong     |   26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     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   |
|  NULL | NULL          | NULL | NULL   |    NULL |      NULL |    2 | Zhang Sanfeng |   94 | M      |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
26 rows in set (0.001 sec)


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   |   45 |    3 | Miejue Shitai |   77 |
|     2 | Shi Potian    |   22 | NULL | NULL          | NULL |
|     3 | Xie Yanke     |   77 | NULL | NULL          | NULL |
|     4 | Ding Dian     |   32 |    4 | Lin Chaoying  |   29 |
|     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 |
|  NULL | NULL          | NULL |    2 | Zhang Sanfeng |   94 |
+-------+---------------+------+------+---------------+------+
26 rows in set (0.001 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     |   77 | 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   |
|  NULL | NULL          | NULL | NULL   |    NULL |      NULL |    2 | Zhang Sanfeng |   94 | M      |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
23 rows in set (0.001 sec)

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.teacherid=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          |
|  NULL | NULL          |      NULL |    2 | Zhang Sanfeng |
+-------+---------------+-----------+------+---------------+
23 rows in set (0.001 sec)
2.7 自连接

即：表自身连接自身

范例：自连接

#自连接
MariaDB [hellodb]> select * from emp;
+----+----------+-----------+
| id | name     | leader_id |
+----+----------+-----------+
|  1 | mage     |      NULL |
|  2 | zhangsir |         1 |
|  3 | wang     |         2 |
|  4 | zhang    |         3 |
+----+----------+-----------+
4 rows in set (0.000 sec)

MariaDB [hellodb]> select e.name 员工姓名,l.name 领导姓名 from emp e inner join emp l on e.leader_id=l.id;
+--------------+--------------+
| 员工姓名      | 领导姓名     |
+--------------+--------------+
| zhangsir     | mage         |
| wang         | zhangsir     |
| zhang        | wang         |
+--------------+--------------+
3 rows in set (0.001 sec)

MariaDB [hellodb]> select e.name 员工姓名,l.name 领导姓名 from emp e left join emp l on e.leader_id=l.id;
+--------------+--------------+
| 员工姓名      | 领导姓名     |
+--------------+--------------+
| mage         | NULL         |
| zhangsir     | mage         |
| wang         | zhangsir     |
| zhang        | wang         |
+--------------+--------------+
4 rows in set (0.000 sec)

MariaDB [hellodb]> select e.name 员工姓名,IFNULL(l.name,'无上级') 领导姓名 from emp e left join emp l on e.leader_id=l.id;
+--------------+--------------+
| 员工姓名     | 领导姓名     |
+--------------+--------------+
| mage         | 无上级       |
| zhangsir     | mage         |
| wang         | zhangsir     |
| zhang        | wang         |
+--------------+--------------+
4 rows in set (0.001 sec)

范例：三表连接

#三张表连接示例
MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
+----------+----------------+
7 rows in set (0.000 sec)

MariaDB [hellodb]> select * from scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
|  1 |     1 |        2 |    77 |
|  2 |     1 |        6 |    93 |
|  3 |     2 |        2 |    47 |
|  4 |     2 |        5 |    97 |
|  5 |     3 |        2 |    88 |
|  6 |     3 |        6 |    75 |
|  7 |     4 |        5 |    71 |
|  8 |     4 |        2 |    89 |
|  9 |     5 |        1 |    39 |
| 10 |     5 |        7 |    63 |
| 11 |     6 |        1 |    96 |
| 12 |     7 |        1 |    86 |
| 13 |     7 |        7 |    83 |
| 14 |     8 |        4 |    57 |
| 15 |     8 |        3 |    93 |
+----+-------+----------+-------+
15 rows in set (0.001 sec)

MariaDB [hellodb]> select st.name,sc.score from students st inner join scores sc on st.stuid=sc.stuid;
+-------------+-------+
| name        | score |
+-------------+-------+
| Shi Zhongyu |    77 |
| Shi Zhongyu |    93 |
| Shi Potian  |    47 |
| Shi Potian  |    97 |
| Xie Yanke   |    88 |
| Xie Yanke   |    75 |
| Ding Dian   |    71 |
| Ding Dian   |    89 |
| Yu Yutong   |    39 |
| Yu Yutong   |    63 |
| Shi Qing    |    96 |
| Xi Ren      |    86 |
| Xi Ren      |    83 |
| Lin Daiyu   |    57 |
| Lin Daiyu   |    93 |
+-------------+-------+
15 rows in set (0.000 sec)

MariaDB [hellodb]> select st.name,sc.CourseID,sc.score from students st inner join scores sc on st.stuid=sc.stuid;
+-------------+----------+-------+
| name        | CourseID | score |
+-------------+----------+-------+
| Shi Zhongyu |        2 |    77 |
| Shi Zhongyu |        6 |    93 |
| Shi Potian  |        2 |    47 |
| Shi Potian  |        5 |    97 |
| Xie Yanke   |        2 |    88 |
| Xie Yanke   |        6 |    75 |
| Ding Dian   |        5 |    71 |
| Ding Dian   |        2 |    89 |
| Yu Yutong   |        1 |    39 |
| Yu Yutong   |        7 |    63 |
| Shi Qing    |        1 |    96 |
| Xi Ren      |        1 |    86 |
| Xi Ren      |        7 |    83 |
| Lin Daiyu   |        4 |    57 |
| Lin Daiyu   |        3 |    93 |
+-------------+----------+-------+
15 rows in set (0.000 sec)

MariaDB [hellodb]> select st.name,co.Course,sc.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.001 sec)

3 SELECT语句处理的顺序

在这里插入图片描述
查询执行路径中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎

SELECT语句的执行流程：

FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER
BY --> LIMIT

练习
导入hellodb.sql生成数据库

  1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
  2. 以ClassID为分组依据，显示每组的平均年龄
  3. 显示第2题中平均年龄大于30的分组及平均年龄
  4. 显示以L开头的名字的同学的信息
  5. 显示TeacherID非空的同学的相关信息
  6. 以年龄排序后，显示年龄最大的前10位同学的信息
  7. 查询年龄大于等于20岁，小于等于25岁的同学的信息
  8. 以ClassID分组，显示每班的同学的人数
  9. 以Gender分组，显示其年龄之和
  10. 以ClassID分组，显示其平均年龄大于25的班级
  11. 以Gender分组，显示各组中年龄大于25的学员的年龄之和
  12. 显示前5位同学的姓名、课程及成绩
  13. 显示其成绩高于80的同学的名称及课程
  14. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
  15. 显示每门课程课程名称及学习了这门课的同学的个数
  16. 显示其年龄大于平均年龄的同学的名字
  17. 显示其学习的课程为第1、2，4或第7门课的同学的名字
  18. 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
  19. 统计各班级中年龄大于全校同学平均年龄的同学
特洛伊CAR
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql，数据库，sql
03-20
常用的DQL语句有： * SELECT：查询指定的数据。 * FROM：指定查询的表。 * WHERE：指定查询的条件。 * GROUP BY：对查询结果进行分组。 * HAVING：对分组结果进行筛选。 DCL（Data Control Language） DCL用于控制...
【MySQL 系列】MySQL 语句篇_DQL 语句
栗筝i的博客
03-09 1806
DQL（Data Query Language），即数据查询语言，用来查询数据记录。DQL 基本结构由 SELECT FROM、WHERE、JOIN 等子句构成。 DQL 语句并不会改变数据库，而是让数据库将查询结果发送结果集给客户端，返回的结果是一张虚拟表。
SQL语言之DQL语句
火桑花终开的博客
09-10 2426
数据库版本:MySQL8
MySQL DQL（数据查询语言）
最新发布
Java 技术专栏，从入门到精通，熟悉企业级开发
04-20 1万+
此为MySQL 专栏文章之一，讲解DQL 通用语法。MySQL 通用语法分类● DQL: 数据查询语言，用来 查询 数据库中表的记录。
数据库基础语言DQL语句
m0_49158934的博客
01-25 1301
1、查看公司所有员工的工资，且工资从低到高排序FROM emp2、按照时间排序时，越早的时间越小，越晚的时间越大FROM emp3、按照升序排序时，可以使用关键字ASC，但是通常不需要写，因为默认就是升序(从小到大)FROM emp4、按照工资从大到小排序(降序)，降序使用关键字DESCFROM emp5、查看每个部门的工资排名ORDER BY可以按照多字段排序，排序优先级时先将结果集按照第一个字段的值排序，保证第一个字段排序顺序的前提下。
SQL_CALC_FOUND_ROWS的用法
papaya的博客
07-26 7170
我们在分页程序中往往这样写 SELECT COUNT(*) from `table` WHERE xxxxxx; // 查出符合条件的记录总数 SELECT * FROM `table` WHERE xxxxxx limit M,N; // 查询当页要显示的数据 这种先统计总数，再统计分页的做法可以替换为下面的写法 SELECT SQL_CALC_FOUND_ROWS * FROM `table` WHERE xxxxxx limit M, N; // 获取指定页的数据，且统计总条数 其中的
mysql sql_calc_found_rows_MySQL的SQL_CALC_FOUND_ROWS
weixin_39948277的博客
02-07 338
分页程序一般由两条SQL组成：SELECT COUNT(*) FROM ... WHERE ....SELECT ... FROM ... WHERE LIMIT ...如果使用SQL_CALC_FOUND_ROWS的话，一条SQL就可以了：SELECT SQL_CALC_FOUND_ROWS ... FROM ... WHERE LIMIT ...在得到数据后，通过FOUND_ROWS()可以得...
7天带你玩转Mysql数据库之SQL语句_MYSQL_
10-02
"7天带你玩转Mysql数据库之SQL语句"的学习资料旨在帮助初学者快速掌握MySQL的基础知识，并通过LeetCode的面试题来提升实战技能。本文将深入探讨MySQL中的SQL语句及其在实际操作中的应用。 SQL（Structured Query ...
mysql.rar_MYSQL数据库_MySQL软件_SQL软件
09-21
作为全球最受欢迎的数据库之一，MySQL在Web应用、小型到大型企业系统中都发挥着重要作用。本资源“mysql.rar”包含了安装和使用MySQL数据库所需的基本组件。 在MySQL数据库中，用户可以通过SQL（Structured Query ...
MySQL常用sql语句生成器
03-22
MySQL SQL语句生成器是数据库管理和开发的有效工具，它使用户能够更轻松地与MySQL数据库交互。了解并熟练掌握SQL语句是每个数据库管理员和开发者的必备技能，而使用SQL语句生成器则可以进一步提高工作效率和准确性。...
MySQL数据库相关课件PPT
02-22
MySQL数据库是全球最受欢迎的开源关系型数据库管理系统之一，它以其高效、可靠和易于维护的特点，在Web开发、企业级应用和数据分析等领域广泛应用。本课件PPT涵盖了MySQL的基础知识到高级特性的深入理解，旨在帮助...
SQL语言之DQL
weixin_46066635的博客
06-30 394
DQL全称是Data Query Language，即数据查询语言，主要是查询数据库中表的记录。
mysql sql_calc_found_rows_MySQL中SQL_CALC_FOUND_ROWS的用法
weixin_39695241的博客
01-30 624
1. SQL_CALC_FOUND_ROWS简述在很多分页的程序中都这样写:#查出符合条件的记录总数SELECT COUNT(*) from [table] WHERE ......;#查询当页要显示的数据SELECT * FROM [table] WHERE ...... limit M,N;但是从Mysql4.0.0开始，我们可以选择使用另外一个方式：SELECT SQL_CALC_FOU...
SQL——DQL
qq_55931044的博客
08-06 430
SQL——DQL的基础语法
MySQL-SQL_CALC_FOUND_ROWS
小知识折射大智慧
07-18 3275
在程序SQL慢查询中遇到SQL_CALC_FOUND_ROWS，记录一下用法。
mysql SQL_CALC_FOUND_ROWS 特性: 一条 sql 语句同时查出总数及分页结果
Just do IT
07-13 624
介绍了如何通过 mysql 的 sql_calc_found_rows 特性一次性查出总数及分页结果
mysql的SQL_CALC_FOUND_ROWS 使用
风轻云淡
01-08 770
在很多分页的程序中都这样写: SELECT COUNT(*) from `table` WHERE...; //查出符合条件的记录总数 SELECT * FROM `table` WHERE ... limit M,N;//查询当页要显示的数据 这样的语句可以改成: SELECT SQL_
SQL语法——DQL (附操作讲解)
Estrella_1的博客
03-28 656
1、编写顺序2、执行顺序。
MySQL中SQL_CALC_FOUND_ROWS的用法
weixin_30652879的博客
01-10 465
1. SQL_CALC_FOUND_ROWS简述 在很多分页的程序中都这样写: #查出符合条件的记录总数 SELECT COUNT(*) from [table] WHERE ......; #查询当页要显示的数据 SELECT * FROM [table] WHERE ...... limit M,N; 但是从Mysql4.0.0开始，我们可以选择使用另外一个方式： SELECT SQ...
MySQL数据库入门与常用SQL语句详解
"MySQL数据库介绍及常用SQL语句（整理） MySQL是一个开源且免费的关系型数据库管理系统，常用于中小型项目，由Oracle公司维护。它提供了一种高效、可靠的方式来存储和管理数据。相比于非关系型数据库，如Redis，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值