连接查询(内连,外连,交叉)
来源:互联网 发布:测试tcp端口 编辑:程序博客网 时间:2024/06/02 12:17
mysql> select * from t1;+----+----------+---------+| id | nickname | playNum |+----+----------+---------+| 1 | 1 | 10 || 2 | 2 | 20 || 3 | 3 | 30 |+----+----------+---------+mysql> select * from t2;+----+----------+---------+| id | nickname | playNum |+----+----------+---------+| 1 | 1 | 10 || 2 | 2 | 200 || 3 | 33 | 300 |+----+----------+---------+inner join:mysql> SELECT t1.* FROM t1 inner JOIN t2 ON t1.playNum = t2.playNum;+----+----------+---------+| id | nickname | playNum |+----+----------+---------+| 1 | 1 | 10 |+----+----------+---------+left join:mysql> SELECT t1.* FROM t1 left JOIN t2 ON t1.playNum = t2.playNum;+----+----------+---------+| id | nickname | playNum |+----+----------+---------+| 1 | 1 | 10 || 2 | 2 | 20 || 3 | 3 | 30 |+----+----------+---------+right join:mysql> SELECT t1.* FROM t1 right JOIN t2 ON t1.playNum = t2.playNum;+------+----------+---------+| id | nickname | playNum |+------+----------+---------+| 1 | 1 | 10 || NULL | NULL | NULL || NULL | NULL | NULL |+------+----------+---------+
内连: inner join ... on
SQL> select * from joina;
ID NAME
---------- -------------------
1 1
2 2
3 3
4 4
5 5
5 5
SQL> select * from joinb;
ID NAME
---------- ----------------
1 1
2 2
3 3
4 4
6 6
6 6
SQL> select a.id from joina a inner join joinb b on (a.id = b.id);
ID
----------
1
2
3
4
外连又分为3种:
left join ... on
SQL> select a.id from joina a left join joinb b on (a.id = b.id);
ID
----------
1
2
3
4
5
5
SQL> select a.id from joina a , joinb b where a.id = b.id(+);
ID
----------
1
2
3
4
5
5
right join ...on
SQL> select a.id from joina a right join joinb b on (a.id = b.id);
ID
----------
1
2
3
4
已选择6行。
SQL> select a.id from joina a , joinb b where a.id(+) = b.id;
ID
----------
1
2
3
4
已选择6行。//有2行为NULL值
full join ...on
SQL> select a.id from joina a full join joinb b on (a.id = b.id);
ID
----------
1
2
3
4
5
5
已选择8行。//有2行为NULL值
交叉连:cross join 笛卡尔积
SQL> select * from joina;
ID
----------
1
2
3
SQL> select * from joinb;
ID
----------
4
5
6
SQL> select a.id ,b.id from joina a cross join joinb b;
ID ID
---------- ----------
1 4
2 4
3 4
1 5
2 5
3 5
1 6
2 6
3 6
已选择9行。
自然连接:natural join 注意:自然连接只能发生在两个表中有相同名字和数据类型的列上。
SQL> select * from n1;
ID NAME ID2
---------- -------------------- ----------
1 a 3
2 b 6
3 c 7
SQL> select * from n2;
ID3 NAME ID4
---------- -------------------- ----------
2 c 7
3 d 5
1 a 3
SQL> select * from n1 natural join n2;
NAME ID ID2 ID3 ID4
-------------------- ---------- ---------- ---------- ----------
c 3 7 2 7
a 1 3 1 3
自然连接分为3步:
1:表n1 和n2的笛卡尔积
SQL> set lin 150
SQL> select * from n1 cross join n2;
ID NAME ID2 ID3 NAME ID4
---------- -------------------- ---------- ---------- -------------------- ----------
1 a 3 2 c 7
1 a 3 3 d 5
1 a 3 1 a 3
2 b 6 2 c 7
2 b 6 3 d 5
2 b 6 1 a 3
3 c 7 2 c 7
3 c 7 3 d 5
3 c 7 1 a 3
已选择9行。
2:然后就是选择n1.name=n2.name
SQL> select * from n1 a cross join n2 b where b.name=a.name;
ID NAME ID2 ID3 NAME ID4
---------- -------------------- ---------- ---------- -------------------- ----------
3 c 7 2 c 7
1 a 3 1 a 3
3:去掉相同列且值也相同的重复列
SQL> select a.id ,a.name ,a.id2 ,b.id3 ,b.id4 from n1 a cross join n2 b where b.name=a.name;
ID NAME ID2 ID3 ID4
---------- -------------------- ---------- ---------- ----------
3 c 7 2 7
1 a 3 1 3
- 连接查询(内连,外连,交叉)
- SQL连表查询:内连接、左联接、右连接、全连接、交叉连接讲解
- Mysql连表查询(内连接、外连接)
- SQL 左连 右连 内连和外连
- Day38_数据库、内连、外连
- sql中的左连,右连,内连,全连接---经常忘记,稍微记录一下
- 直连,交叉!
- 内连接,外连接,交叉连接--数据库查询语句学习
- 多表连接查询(内,外,交叉连接)
- 多表连接查询(内,外,交叉连接)
- 多表查询的分类,连接查询,内连接,外连接,交叉连接,子查询
- 内连函数
- mysql 内连实例
- iptables 状态策略 允许内网连接外网 拒绝外网主动连入内网
- SQL 表合并,内连,左连,右连
- SQL连接查询 内连接,左外连接,右外连接,全连接,交叉连接
- RS232串口交叉直连
- c++连数据库查询
- (ios实现)动态界面支持iphone5
- win7 64位下使用regsvr32注册activex dll
- 信息安全资讯网站总结
- 转载自贴吧的一个C语言程序,比较有深意
- 网络广告定价模常用术语解释
- 连接查询(内连,外连,交叉)
- 应用程序本地化
- IDS集成套件easyIDS安装
- 动态改变页面显示风格办法
- gdb命令和使用示例
- Core Data入门
- 黑马程序员——抽象类和接口
- Git学习资料汇总
- TCP三次握手过程详解(一)