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,'张三',25,'beijing',10000),
(3,'李四',21,'beijing',15000),(4,'李二',28,'shenzhen',10000),
(5,'王五',24,'shanghai',20000),(6,'杨三',19,'shanghai',15000),
(7,'张四',22,'shenzhen',20000),(8,'杨四',20,'beijing',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,employees.name,department.dept
FROM employees FULL OUTER JOIN department
ON employees.id = department.id;
基于两个表格的id字段,连接表格,取并集,缺失值为NULL
4、左外连接(LEFT OUTER JOIN)
SELECT employees.id,employees.name,department.dept
FROM employees LEFT OUTER JOIN department
ON employees.id = department.id;
连接表格后,保留employees的所有数据条目
5、右外连接(RIGHT OUTER JOIN)
SELECT employees.id,employees.name,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,employees.name,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'),('杨三','硕士'),
('张四','PHD'),('杨四','本科');
创建表格3
SELECT employees.id,employees.name,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;
注意嵌套写法
最新文章
- 【bzoj4008】 HNOI2015—亚瑟王
- View and Data API Tips: how to make viewer full screen
- R入门<;三>;-R语言实战第4章基本数据管理摘要
- C#中 Request, Request.params , Request.querystring , Request.Form 区别 与联系用法
- css知多少(1)——我来问你来答
- JS中级 - 02:表单、表格
- ytu 1067: 顺序排号(约瑟夫环)
- iOS启动图和开屏广告图,类似网易
- servlet 文件上传简易版
- MySQL如何执行关联查询
- [Head First Python]5. summary
- tp框架设置 mysql数据库的端口号
- html4与html5的区别及html5的一些新特性
- 2329: [HNOI2011]括号修复
- BZOJ 1004: [HNOI2008]Cards [Polya 生成函数DP]
- Xilinx 7 Serial PUDC_B
- 作为一个.NET开发者,怎么看待和选择层出不穷的新技术,新架构?
- python专题 --- 递归
- 《linux内核设计与实现》读书笔记——第三章
- Linux网络管理(一):网卡驱动与Linux内核
热门文章
- 关于JavaScript的事件触发
- 在ubuntu16.04上编译android源码【转】
- FFmpeg滤镜代码级分析
- elasticsearch 中文分词(elasticsearch-analysis-ik)安装
- Ueditor文本编辑工具栏自定义
- Linux下压缩某个文件夹命令
- [FJOI 2016] 神秘数
- NOIP2000提高组(RQNOJ314)方格取数
- 第一行代码笔记的思维导图(http://images2015.cnblogs.com/blog/1089110/201704/1089110-20170413160323298-819915364.png)
- 蓝桥杯 2014本科C++ B组 李白打酒 三种实现方法 枚举/递归