人大金仓多表查询_kingbase查询所有表

作者：在线问答5 | 2024-08-14 18:54:39

踩

kingbase查询所有表

人大金仓多表查询

连接的查询类型

按照结果集的输出方式：

内连接查询、外连接查询、交叉连接查询
1

按照连接条件进行的比较运算符分为：

等值连接查询、非等值连接查询
1

其他特殊连接方式：

自然连接、自连接
1

实验环境的创建

# 创建表t1
create table t1(id1 int,id2 int, id3 int);
# 创建表t2
create table t2(id1 int,id4 int,id5 int);
# 插入表数据
insert into t1(id1,id2,id3) values (1,1,1),(2,2,2),(3,3,3);
insert into t2 values (1,4,4),(2,2,2),(4,6,6);
# 查看表数据
table t1;
 id1 | id2 | id3
-----+-----+-----
   1 |   1 |   1
   2 |   2 |   2
   3 |   3 |   3
# 查看表数据
table t2;
 id1 | id4 | id5
-----+-----+-----
   1 |   4 |   4
   2 |   2 |   2
   4 |   6 |   6

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

内连接查询

INNER JOIN

# 通过id1字段连接表t1和表t2
select * from t1 inner join t2 on t1.id1=t2.id1;
# 通过非标准sql的方式进行连接
select * from t1,t2 where t1.id1=t2.id1 ;
1
2
3
4

外连接

左外连接 LEFT OUTER JOIN
右外连接 RIGHT OUTER JOIN

全外连接 FULL OUTER JOIN

# 左外连接 LEFT OUTER JOIN
select * from t1 left outer join t2  on t1.id1 =t2.id1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   3 |   3 |   3 |     |     |

1
2
3
4
5
6
7
8

# 右外连接 RIGHT OUTER JOIN
select * from t1 right join t2 on t1.id1 = t2.id1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
     |     |     |   4 |   6 |   6
1
2
3
4
5
6
7

# 全外连接 FULL OUTER JOIN
select * from t1 full join t2 on t1.id1 = t2.id1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   3 |   3 |   3 |     |     |
     |     |     |   4 |   6 |   6
1
2
3
4
5
6
7
8

# 使用where对连接的表进行筛选
select * from t1 full join t2 on t1.id1 = t2.id1 where t1.id1=1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4


1
2
3
4
5
6
7

交叉连接（迪卡尔积连接）

select * from t1 cross join t2;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4
   1 |   1 |   1 |   2 |   2 |   2
   1 |   1 |   1 |   4 |   6 |   6
   2 |   2 |   2 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   2 |   2 |   2 |   4 |   6 |   6
   3 |   3 |   3 |   1 |   4 |   4
   3 |   3 |   3 |   2 |   2 |   2
   3 |   3 |   3 |   4 |   6 |   6
# 当命令提供了错无效的命令
select * from t1 inner join t2 on t1.id1>1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   2 |   2 |   2 |   1 |   4 |   4
   3 |   3 |   3 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   3 |   3 |   3 |   2 |   2 |   2
   2 |   2 |   2 |   4 |   6 |   6
   3 |   3 |   3 |   4 |   6 |   6
# 使用都逗号分割表名
select * from t1,t2;
id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   2 |   2 |   2 |   1 |   4 |   4
   3 |   3 |   3 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   3 |   3 |   3 |   2 |   2 |   2
   2 |   2 |   2 |   4 |   6 |   6
   3 |   3 |   3 |   4 |   6 |   6

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

非等值连接

select * from t1 inner join t2 on t1.id1 >= t2.id1;
 id1 | id2 | id3 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   1 |   1 |   1 |   1 |   4 |   4
   2 |   2 |   2 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2 |   2
   3 |   3 |   3 |   1 |   4 |   4
   3 |   3 |   3 |   2 |   2 |   2

1
2
3
4
5
6
7
8
9

自连接

select * from t2 a inner join t2 b on a.id4=b.id1;
 id1 | id4 | id5 | id1 | id4 | id5
-----+-----+-----+-----+-----+-----
   2 |   2 |   2 |   2 |   2 |   2
   1 |   4 |   4 |   4 |   6 |   6

1
2
3
4
5
6

自然连接

select * from t1 natural inner join t2;
 id1 | id2 | id3 | id4 | id5
-----+-----+-----+-----+----- 
   1 |   1 |   1 |   4 |   4
   2 |   2 |   2 |   2 |   2

1
2
3
4
5
6

useing（）关键字

select * from t1 inner join t2  using(id1);
id1 | id2 | id3 | id4 | id5
test-# -----+-----+-----+-----+-----
test-#    1 |   1 |   1 |   4 |   4
test-#    2 |   2 |   2 |   2 |   2

1
2
3
4
5
6

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】