连接查询_01

作者: 御都 | 来源:发表于2019-07-31 23:46 被阅读0次

连接查询_01
mysql连接查询，自关联，子查询
mysql-数据查询语句-多表
python学习笔记-数据库06_连接查询
EF Core 备忘
mysql的用法3
Mysql--连接查询和子查询
52 SQL 复习语句关系代数（三）
第三天下午、自连接、子连接、分页查询
连接查询；外键

【准备】创建boys girls 2张表，每个表中插入6个记录

create table boys ( b_name varchar(20), b_id int);
 create table girls ( gb_name varchar(20), g_id int);
insert into boys values('Tom',1),('Jim',2),('Harry',3),('Snape',4),('Draco',5),('Lucius',6);
insert into girls values('Ada',1),('Bell',2),('Hermione',3),('Marrye',4),('Rose',5),('Cathy',6);

1【内连接】---交集

1.1 2种格式，效果相同
隐式内连接：SELECT * FROM tab1,tab2 WHRER.....;
显式内连接：SELECT * FROM tab1 [INNER] JOIN tab2 ON.....;
省略Inner时可以用where作为刷新条件的关键词

mysql> select * from boys,girls;
+--------+------+----------+------+
| b_name | b_id | gb_name  | g_id |
+--------+------+----------+------+
| Tom    |    1 | Ada      |    1 |
| Jim    |    2 | Ada      |    1 |
| Harry  |    3 | Ada      |    1 |
| Snape  |    4 | Ada      |    1 |
| Draco  |    5 | Ada      |    1 |
| Lucius |    6 | Ada      |    1 |
| Tom    |    1 | Bell     |    2 |
| Jim    |    2 | Bell     |    2 |
| Harry  |    3 | Bell     |    2 |
| Snape  |    4 | Bell     |    2 |
| Draco  |    5 | Bell     |    2 |
| Lucius |    6 | Bell     |    2 |
| Tom    |    1 | Hermione |    3 |
| Jim    |    2 | Hermione |    3 |
| Harry  |    3 | Hermione |    3 |
| Snape  |    4 | Hermione |    3 |
| Draco  |    5 | Hermione |    3 |
| Lucius |    6 | Hermione |    3 |
| Tom    |    1 | Marrye   |    4 |
| Jim    |    2 | Marrye   |    4 |
| Harry  |    3 | Marrye   |    4 |
| Snape  |    4 | Marrye   |    4 |
| Draco  |    5 | Marrye   |    4 |
| Lucius |    6 | Marrye   |    4 |
| Tom    |    1 | Rose     |    5 |
| Jim    |    2 | Rose     |    5 |
| Harry  |    3 | Rose     |    5 |
| Snape  |    4 | Rose     |    5 |
| Draco  |    5 | Rose     |    5 |
| Lucius |    6 | Rose     |    5 |
| Tom    |    1 | Cathy    |    6 |
| Jim    |    2 | Cathy    |    6 |
| Harry  |    3 | Cathy    |    6 |
| Snape  |    4 | Cathy    |    6 |
| Draco  |    5 | Cathy    |    6 |
| Lucius |    6 | Cathy    |    6 |
+--------+------+----------+------+
36 rows in set (0.00 sec)

1.2 交换2张表的位置

mysql> select * from girls,boys;
+----------+------+--------+------+
| gb_name  | g_id | b_name | b_id |
+----------+------+--------+------+
| Ada      |    1 | Tom    |    1 |
| Bell     |    2 | Tom    |    1 |
| Hermione |    3 | Tom    |    1 |
| Marrye   |    4 | Tom    |    1 |
| Rose     |    5 | Tom    |    1 |
| Cathy    |    6 | Tom    |    1 |
| Ada      |    1 | Jim    |    2 |
| Bell     |    2 | Jim    |    2 |
| Hermione |    3 | Jim    |    2 |
| Marrye   |    4 | Jim    |    2 |
| Rose     |    5 | Jim    |    2 |
| Cathy    |    6 | Jim    |    2 |
| Ada      |    1 | Harry  |    3 |
| Bell     |    2 | Harry  |    3 |
| Hermione |    3 | Harry  |    3 |
| Marrye   |    4 | Harry  |    3 |
| Rose     |    5 | Harry  |    3 |
| Cathy    |    6 | Harry  |    3 |
| Ada      |    1 | Snape  |    4 |
| Bell     |    2 | Snape  |    4 |
| Hermione |    3 | Snape  |    4 |
| Marrye   |    4 | Snape  |    4 |
| Rose     |    5 | Snape  |    4 |
| Cathy    |    6 | Snape  |    4 |
| Ada      |    1 | Draco  |    5 |
| Bell     |    2 | Draco  |    5 |
| Hermione |    3 | Draco  |    5 |
| Marrye   |    4 | Draco  |    5 |
| Rose     |    5 | Draco  |    5 |
| Cathy    |    6 | Draco  |    5 |
| Ada      |    1 | Lucius |    6 |
| Bell     |    2 | Lucius |    6 |
| Hermione |    3 | Lucius |    6 |
| Marrye   |    4 | Lucius |    6 |
| Rose     |    5 | Lucius |    6 |
| Cathy    |    6 | Lucius |    6 |
+----------+------+--------+------+
36 rows in set (0.00 sec)

1.3 用where连接筛选条件，对笛卡尔积进行筛选。
筛选出g_id > 4

mysql> select * from girls Join boys where g_id>4;
+---------+------+--------+------+
| gb_name | g_id | b_name | b_id |
+---------+------+--------+------+
| Rose    |    5 | Tom    |    1 |
| Cathy   |    6 | Tom    |    1 |
| Rose    |    5 | Jim    |    2 |
| Cathy   |    6 | Jim    |    2 |
| Rose    |    5 | Harry  |    3 |
| Cathy   |    6 | Harry  |    3 |
| Rose    |    5 | Snape  |    4 |
| Cathy   |    6 | Snape  |    4 |
| Rose    |    5 | Draco  |    5 |
| Cathy   |    6 | Draco  |    5 |
| Rose    |    5 | Lucius |    6 |
| Cathy   |    6 | Lucius |    6 |
+---------+------+--------+------+
12 rows in set (0.00 sec)

筛选 g_id>b_id

mysql> select * from girls Join boys where g_id>b_id;
+----------+------+--------+------+
| gb_name  | g_id | b_name | b_id |
+----------+------+--------+------+
| Bell     |    2 | Tom    |    1 |
| Hermione |    3 | Tom    |    1 |
| Marrye   |    4 | Tom    |    1 |
| Rose     |    5 | Tom    |    1 |
| Cathy    |    6 | Tom    |    1 |
| Hermione |    3 | Jim    |    2 |
| Marrye   |    4 | Jim    |    2 |
| Rose     |    5 | Jim    |    2 |
| Cathy    |    6 | Jim    |    2 |
| Marrye   |    4 | Harry  |    3 |
| Rose     |    5 | Harry  |    3 |
| Cathy    |    6 | Harry  |    3 |
| Rose     |    5 | Snape  |    4 |
| Cathy    |    6 | Snape  |    4 |
| Cathy    |    6 | Draco  |    5 |
+----------+------+--------+------+
15 rows in set (0.00 sec)

筛选b_name=Harry

mysql> select * from girls Join boys where b_name='Harry';
+----------+------+--------+------+
| gb_name  | g_id | b_name | b_id |
+----------+------+--------+------+
| Ada      |    1 | Harry  |    3 |
| Bell     |    2 | Harry  |    3 |
| Hermione |    3 | Harry  |    3 |
| Marrye   |    4 | Harry  |    3 |
| Rose     |    5 | Harry  |    3 |
| Cathy    |    6 | Harry  |    3 |
+----------+------+--------+------+
6 rows in set (0.00 sec)

1.4 总结：逗号或者join连接的2张表，会将不满足筛选条件的2张表中的内容都删除，感觉像是一刀切。

2 【左/右连接】-----左表/右边+集合

2 .1 使用ON做为筛选的关键词，左右连接必须和ON语句同时使用，否则会报错

mysql> select * from girls left join boys;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

2.2 作用：
使用LEFT JOIN连接2张表：保持左边表格的完整性，空的部分用NULL填入
RIGHT JOIN连接2张表：保持右边边表格的完整性，空的部分用NULL填入

mysql> select * from boys,girls where b_id >4;
+--------+------+----------+------+
| b_name | b_id | g_name   | g_id |
+--------+------+----------+------+
| Draco  |    5 | Ada      |    1 |
| Lucius |    6 | Ada      |    1 |
| Draco  |    5 | Bell     |    2 |
| Lucius |    6 | Bell     |    2 |
| Draco  |    5 | Hermione |    3 |
| Lucius |    6 | Hermione |    3 |
| Draco  |    5 | Marrye   |    4 |
| Lucius |    6 | Marrye   |    4 |
| Draco  |    5 | Rose     |    5 |
| Lucius |    6 | Rose     |    5 |
| Draco  |    5 | Cathy    |    6 |
| Lucius |    6 | Cathy    |    6 |
+--------+------+----------+------+
12 rows in set (0.00 sec)

mysql> select * from boys left join girls on  b_id >4;
+--------+------+----------+------+
| b_name | b_id | g_name   | g_id |
+--------+------+----------+------+
| Draco  |    5 | Ada      |    1 |
| Lucius |    6 | Ada      |    1 |
| Draco  |    5 | Bell     |    2 |
| Lucius |    6 | Bell     |    2 |
| Draco  |    5 | Hermione |    3 |
| Lucius |    6 | Hermione |    3 |
| Draco  |    5 | Marrye   |    4 |
| Lucius |    6 | Marrye   |    4 |
| Draco  |    5 | Rose     |    5 |
| Lucius |    6 | Rose     |    5 |
| Draco  |    5 | Cathy    |    6 |
| Lucius |    6 | Cathy    |    6 |
| Tom    |    1 | NULL     | NULL |
| Jim    |    2 | NULL     | NULL |
| Harry  |    3 | NULL     | NULL |
| Snape  |    4 | NULL     | NULL |
+--------+------+----------+------+
16 rows in set (0.00 sec)
mysql> select * from boys rigth join girls on  b_id >4;
+--------+------+----------+------+
| b_name | b_id | g_name   | g_id |
+--------+------+----------+------+
| Draco  |    5 | Ada      |    1 |
| Lucius |    6 | Ada      |    1 |
| Draco  |    5 | Bell     |    2 |
| Lucius |    6 | Bell     |    2 |
| Draco  |    5 | Hermione |    3 |
| Lucius |    6 | Hermione |    3 |
| Draco  |    5 | Marrye   |    4 |
| Lucius |    6 | Marrye   |    4 |
| Draco  |    5 | Rose     |    5 |
| Lucius |    6 | Rose     |    5 |
| Draco  |    5 | Cathy    |    6 |
| Lucius |    6 | Cathy    |    6 |
+--------+------+----------+------+
12 rows in set (0.00 sec)

3【UNION连接】

3.1 作用：将2个select的结果拼接起来，前提是2个select语句中要显示的列数和类型要相同
UNION：去除重复的行进行拼接
UNION ALL：不去除重复的行进行拼接
详情参考：http://www.itxm.net/a/shujuku/2016/1205/841.html

连接查询_01
【准备】创建boys girls 2张表，每个表中插入6个记录 1【内连接】---交集 1.1 2种格式，效果相同...
mysql连接查询，自关联，子查询
mysql支持三种类型的连接查询，分别为：内连接查询，左连接查询，右连接查询内连接查询：左连接查询：右连接查...
mysql-数据查询语句-多表
连接查询连接查询，是关系数据库中最主要的查询，包括等值查询、自然连接查询、非等值查询、自身连接查询、外连接查询和...
python学习笔记-数据库06_连接查询
连接查询可以实现多表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成连接查询可以分为：内连接查询左连接...
EF Core 备忘
模糊查询sql linq 内连接查询sql linq 左连接查询sql linq 左连接查询（连接内带条件）sql...
mysql的用法3
-- ==============连接查询==================-- 1.连接查询: 同时查询多个表...
Mysql--连接查询和子查询
连接查询和子查询一、连接查询 1.1 概念连接查询：也可以叫跨表查询，需要关联多个表进行查询 1.2 根据年代...
52 SQL 复习语句关系代数（三）
多表查询等值连接查询和非等值连接查询 JOIN ON 自然连接自身连接注意，如果属性名在参与连接的各个表中是...
第三天下午、自连接、子连接、分页查询
自连接查询每个员工的编号、姓名、领导姓名外连接查询连接查询代码课下作业题子查询(分页查询) 查找工资最高...
连接查询；外键
连接查询连接查询（join）分类：内连接、外连接、自然连接、交叉连接使用方式：左表 join 右表交叉连接（...