语句"/>
MySQL数据库(四)SQL之DQL语句
成功不易,加倍努力!
- DQL语句
- 1 单表操作
- 2 多表查询
- 2.1 子查询
- 2.2 联合查询:UNION
- 2.3 交叉连接
- 2.4 内连接
- 2.5 左和右外连接
- 2.6 完全外连接
- 2.7 自连接
- 3 SELECT语句处理的顺序
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 studentsselect * 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子句中的子查询
- 用于比较表达式中的子查询;子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
- 用于IN中的子查询:子查询应该单键查询并返回一个或多个值从构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
- 用于EXISTS 和 Not EXISTS
参考链接:.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取别名为aMariaDB [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的记录出现在最终结果集,否则被排除
- 用于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: 0MariaDB [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生成数据库
- 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
- 以ClassID为分组依据,显示每组的平均年龄
- 显示第2题中平均年龄大于30的分组及平均年龄
- 显示以L开头的名字的同学的信息
- 显示TeacherID非空的同学的相关信息
- 以年龄排序后,显示年龄最大的前10位同学的信息
- 查询年龄大于等于20岁,小于等于25岁的同学的信息
- 以ClassID分组,显示每班的同学的人数
- 以Gender分组,显示其年龄之和
- 以ClassID分组,显示其平均年龄大于25的班级
- 以Gender分组,显示各组中年龄大于25的学员的年龄之和
- 显示前5位同学的姓名、课程及成绩
- 显示其成绩高于80的同学的名称及课程
- 取每位同学各门课的平均成绩,显示成绩前三名的同学的姓名和平均成绩
- 显示每门课程课程名称及学习了这门课的同学的个数
- 显示其年龄大于平均年龄的同学的名字
- 显示其学习的课程为第1、2,4或第7门课的同学的名字
- 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
- 统计各班级中年龄大于全校同学平均年龄的同学
更多推荐
MySQL数据库(四)SQL之DQL语句
发布评论