PostgreSQL-7-数据连接

1、通过WHERE进行简单连接

SELECT * FROM company3,department? 不添加WHERE将会显示所有数据

SELECT * FROM company3,department WHERE company3.no = department.id;? 通过WHERE构建匹配逻辑

SELECT company3.name,company3.salary,department.dept

?????? FROM company3,department WHERE company3.no = department.id;? 显示指定字段

通过select+where，创建多个表的连接

?

?

2、内连接(INNER JOIN)

CREATE TABLE employees(

?????? id int PRIMARY KEY,

?????? name text,

?????? age int CHECK(age > 0),

?????? address text,

?????? salary numeric CHECK(salary > 0)

?????? );

INSERT INTO employees VALUES(1,‘王大‘,25,‘beijing‘,10000),(2,‘张三‘,

?????? (3,‘李四‘,21,15000),(4,‘李二‘,28,‘shenzhen‘,

?????? (5,‘王五‘,24,‘shanghai‘,20000),(6,‘杨三‘,19,

?????? (7,‘张四‘,22,(8,‘杨四‘,20,10000);

创建表格1

?

CREATE TABLE department(id int,dept text,fac_id int);

INSERT INTO department VALUES(1,‘IT‘,1);

INSERT INTO department VALUES(2,‘Engineering‘,2);

INSERT INTO department VALUES(3,‘HR‘,7);

INSERT INTO department VALUES(10,‘Market‘,10);

创建表格2

?

SELECT employees.id,employees.name,department.dept

?????? FROM employees INNER JOIN department

?????? ON employees.id = department.id;

?????? 基于两个表格的id字段，连接表格，取交集

?

?

3、全连接(FULL OUTER JOIN)

SELECT employees.id,department.dept

?????? FROM employees FULL OUTER JOIN department

?????? ON employees.id = department.id;

?????? 基于两个表格的id字段，连接表格，取并集，缺失值为NULL

?

?

4、左外连接(LEFT OUTER JOIN)

SELECT employees.id,department.dept

?????? FROM employees LEFT OUTER JOIN department

?????? ON employees.id = department.id;

?????? 连接表格后，保留employees的所有数据条目

?

?

5、右外连接(RIGHT OUTER JOIN)

SELECT employees.id,department.dept

?????? FROM employees RIGHT OUTER JOIN department

?????? ON employees.id = department.id;

?????? 连接表格后，保留department的所有数据条目?????

?

?

6、交叉连接

SELECT employees.id,dept,salary FROM employees CROSS JOIN department;

笛卡尔积：检索出的行的数目为第一个表中的行数乘以第二个表中的行数

当两个表有共同名称的字段（比如id），将会报错，所以这里需要指示，例如employees.id

?

?

多个连接条件

SELECT employees.id,department.dept

?????? FROM employees INNER JOIN department

?????? ON employees.id = department.id

?????? AND employees.id = department.fac_id;

?

?

JOIN连接三个表

CREATE TABLE education(name text,edu text);

INSERT INTO education VALUES(‘王大‘,‘本科‘),(‘张三‘,

?????? (‘李四‘,‘硕士‘),(‘李二‘,

?????? (‘王五‘,‘PHD‘),(‘杨三‘,

?????? (‘张四‘,(‘杨四‘,‘本科‘);

创建表格3

?

SELECT employees.id,employees.age,employees.salary,department.dept

?????? FROM (employees INNER JOIN education ON employees.name = education.name)

?????? FULL OUTER JOIN department ON employees.id = department.id;

?????? 注意嵌套写法