第五章 DDL&DML&DQL
1.DDL(Data Definition Language)数据定义语言
1.1 创建数据库
1)语法
CREATE DATABASE name [ [WITH] [OWNER [=] dbowner]
[TEMPLATE [=] template]
[ENCODING [=] encoding]
[TABLESPAC [=] tablespace]
[CONNECTIONE LIMIT [=] connlimit ] ]
-
CREATE DATABASE name;
CREATE DATABASE是SQL命令,用于创建一个新的数据库。
name是自定义的数据库名称。这个名称是必须要填写的,而且在当前数据库服务器上必须是唯一的。
-
[WITH] [OWNER [=] dbowner]
这是一个可选项。OWNER指定了新数据库的所有者。如果未指定,新数据库的所有者默认是执行该命令的用户。
dbowner是数据库所有者的用户名。
-
[TEMPLATE [=] template]
这是一个可选项。TEMPLATE指定了用户创建新数据库的模板。在PostgreSQL和GreenPlum中,通常有一个名为template1的默认模板。如果不指定,就会使用这个默认模板。
template是模板数据库的名称。
-
[ENCODING [=] encoding]
ENCODING指定了新数据库的字符编码。这个设置决定了数据库可以存储哪些字符。
encoding是字符编码的名称,例如UTF8
-
[TABLESPAC [=] tablespace]
这是可选项。TABLSPACE指定了新数据库的存储位置。表空间是数据库中存储文件的物理位置。
tablespace是表空间名称。
-
[CONNECTIONE LIMIT [=] connlimit ]
这是可选项。
CONNECTON LIMIT限制了可以同时连接到数据库的最大客户端数量。
connlimit是允许的最大连接数。如果设置为-1,则表示没有限制。
2)创建一个数据库
create database gpdb
with owner gpadmin
encoding 'utf-8'
tablespace pg_default
connection limit 10;
postgres=# create database gpdb
postgres-# with owner gpadmin
postgres-# encoding 'utf-8'
postgres-# tablespace pg_default
postgres-# connection limit 10;
CREATE DATABASE
3)创建schema
schema本质上就是一个分组管理工具,它允许您将相关性质或类型的多个表和其他数据库对象(如试图、索引、存储过程等)组织在一起。也可以把schema看作是数据库内部一个"文件夹"或"命名空间",用于逻辑上组织和隔离数据,以实现更好数据管理和安全控制。
一个database下可以有多个schema。schema在gp中也叫做namespace。
-
1.连接创建完成的数据库
\c gpdb
-
2.创建schema
create schema ods;
postgres=# \c gpdb
You are now connected to database "gpdb" as user "gpadmin".
gpdb=# create schema ods;
CREATE SCHEMA
1.2 查询数据库
1)切换当前数据库
-
数据库服务器命令行操作
\c gpdb
postgres=# \c gpdb You are now connected to database "gpdb" as user "gpadmin".
2)显示数据库
-
数据库服务器命令行操作
查看所有数据库:\l
查看所有schema:\dngpdb=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+---------+----------+------------+------------+--------------------- gpdb | gpadmin | UTF8 | en_US.utf8 | en_US.utf8 | gpperfmon | gpadmin | UTF8 | en_US.utf8 | en_US.utf8 | postgres | gpadmin | UTF8 | en_US.utf8 | en_US.utf8 | template0 | gpadmin | UTF8 | en_US.utf8 | en_US.utf8 | =c/gpadmin + | | | | | gpadmin=CTc/gpadmin template1 | gpadmin | UTF8 | en_US.utf8 | en_US.utf8 | =c/gpadmin + | | | | | gpadmin=CTc/gpadmin zxy | gpadmin | UTF8 | en_US.utf8 | en_US.utf8 | (6 rows) gpdb=# \dn List of schemas Name | Owner ------------+--------- gp_toolkit | gpadmin ods | gpadmin public | gpadmin (3 rows)
-
SQL查询操作
查看所有数据库:select datname from pg_database;
查看所有schema:select schema_name from information_schema.schemata;gpdb=# select datname from pg_database; datname ----------- template1 template0 postgres gpperfmon zxy gpdb (6 rows) gpdb=# select schema_name from information_schema.schemata; schema_name -------------------- pg_toast pg_aoseg pg_bitmapindex pg_catalog public information_schema gp_toolkit ods (8 rows)
1.3 删除数据库
drop database会删除数据库的系统的目录并且删除包含数据的文件目录。可以使用if exists判断数据库是否存在,存在则删除;
drop database if exists yyds
1.4 创建表
1)创建语法说明
CREATE [EXTERNAL] TABLE table_name(
column1 datatype [NOT NULL] [DEFAULT] [CHECK] [UNIQUE],
column2 datatype,
.....
columnN datatype,
[PRIMARY KEY()]
)[ WITH ()]
[LOCATION()]
[FORMAT]
[COMMENT]
[PARTITION BY]
[DISTRIBUTE BY ()];
-
create table
创建一个指定名字的表,如果相同名字的表已经存在,则抛出异常。
-
external
external关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(location)
-
not null
非空约束
-
default
默认值
-
check
为表字段添加检查约束
-
unique
唯一约束,一个表中唯一和主键只能存在一个
-
primary key
主键设置,可以是一个或多个列
-
with
可以添加数据追加方式,压缩格式,压缩级别,行列压缩等
-
location
指定外部表数据存储位置
-
format
存储数据的文本类型
-
partition by
支持两种分区方式,范围分区(range)和列表分区(list)
-
distributed by
为表添加分布键,其必须为主键的子键
-
comment
为表或列添加注释。
2)内部表和外部表介绍
内部表和外部表是两种不同类型的表,它们在数据存储和处理方式上有明细的区别。了解这些区别对于合理的设计和优化GP数据库非常重要。
内部表和外部表在操作和用途上的主要区别。内部表适合存储和管理数据库内的数据,而外部表适用于从外部数据源临时读取数据。
-
内部表
- 数据存储:内部表的数据直接存储在GP数据库的数据文件中,这意味着数据被物理存储在数据库服务器上。
- 事务管理:内部表完全支持事务管理,这包括ACID属性(原子性、一致性、隔离性和持久性),确保数据完整性和可靠性。
- 索引和约束:可以在内部表上创建索引和约束,这有助于提高查询性能和维护数据完整性。
- 管理和维护:内部表可以使用数据库的全部管理和维护功能,如备份和恢复。
- 适用性:适用于需要高性能查询和事务完整性的数据。
1) 创建内部表 gpdb=# CREATE TABLE ods.test ( id SERIAL PRIMARY KEY, name VARCHAR(100)); CREATE TABLE 2) 插入数据 gpdb=# INSERT INTO ods.test (name) VALUES ('zxy'); INSERT 0 1 gpdb=# INSERT INTO ods.test (name) VALUES ('zxy2'); INSERT 0 1 3) 查询结果 gpdb=# select * from ods.test; id | name ----+------ 2 | zxy2 1 | zxy (2 rows)
-
外部表
- 数据存储:外部表的数据存储在数据库外部,如在文件系统、HDFS或任何可以通过SQL/MED(SQL Management of External Data)访问的数据源。外部表仅存储数据的元数据和位置信息。
- 事务管理:外部表不支持事务管理,它们主要用于读取和加载操作,不保证ACID属性。
- 索引和约束:由于数据实际存储在外部,,不能在外部表上创建索引或强制指向数据库级别的约束。
- 管理和维护:外部表的管理相对简单,因为只是对外部数据源的引用。备份和恢复不适用于外部表本身,而是应用于数据源。
- 适用性:适用于ETL操作,即从外部数据源提取数据,然后可能将其转换和加载到内部表中进一步处理。
1)启动gpfdist [gpadmin@sdw1 ~]$ gpfdist -d /home/gpadmin/ -p 8081 -l /home/gpadmin/gpAdminLogs/gpfdist.log & [1] 3232 2)/home/gpadmin/目录下,创建txt目录 1,zxy,18 2,zxy2,20 3)mdw创建外部表 create external table ods.test2 ( id int, name varchar(100), age int ) location ('gpfdist://sdw1:8081/test.txt') format 'text' (delimiter ','); 4)查询外部表 zxy=# select * from ods.test2; id | name | age ----+------+----- 1 | zxy | 18 2 | zxy2 | 20 (2 rows)
1.5 修改表
1)重命名表
alter table table_name rename to new_table_name;
zxy=# alter table ods.test rename to test3;
ALTER TABLE
2)增加列
alter table table_name add column col_name column_type;
zxy=# alter table ods.test3 add column age int;
ALTER TABLE
3)修改列类型
alter table table_name alter column column_name type column_type [using column::column_type]
zxy=# alter table ods.test3 alter column age type varchar(20);
ALTER TABLE
4)删除列
alter table table_name drop column col_name;
zxy=# alter table ods.test3 drop column age;
ALTER TABLE
1.6 清除表
truncate table table_name;
zxy=# truncate table ods.test3;
TRUNCATE TABLE
1.7 删除表
drop table table_name;
zxy=# drop table ods.test3;
DROP TABLE
2.DML(Data Manipulation Language)数据操作语言
2.1 数据导入
1)向表中装在数据(copy)
copy tablename from file_path delimiter '分隔符';
copy:表示加载数据,仅追加;
delimiter:表示读取的数据字段之间的分隔符;
1)创建表
create table ods.test4 (
id int null,
name varchar(10),
age int null
);
2)准备数据
1,zxy,18
2,zxy2,20
3)导入数据
copy ods.test4 from '/home/gpadmin/test.txt' delimiter ',';
4)查询数据
zxy=# select * from ods.test4;
id | name | age
----+------+-----
1 | zxy | 18
2 | zxy2 | 20
(2 rows)
2)向表中插入数据(insert)
insert into tablename(column1,column2...) values(....);
insert into tablename select * from tablename2;
1)insert into tablename(column1,column2...) values(....);
zxy=# insert into ods.test4 values(3,'zxy3',20);
INSERT 0 1
2)insert into tablename select * from tablename2;
zxy=# insert into ods.test4 select * from ods.test2;
INSERT 0 2
3)查询数据
zxy=# select * from ods.test4;
id | name | age
----+------+-----
3 | zxy3 | 20
2 | zxy2 | 20
2 | zxy2 | 20
1 | zxy | 18
1 | zxy | 18
(5 rows)
2.2 数据更新和删除
1)数据更新
update tablename set column1=value1,column2=value2... where [condition];
1)准备数据
zxy=# select * from ods.test4 where id = 1;
id | name | age
----+------+-----
1 | zxy | 18
1 | zxy | 18
(2 rows)
2)修改数据
zxy=# update ods.test4 set name='aaa' where id = 1;
UPDATE 2
3)查询数据
zxy=# select * from ods.test4 where id = 1;
id | name | age
----+------+-----
1 | aaa | 18
1 | aaa | 18
(2 rows)
2)数据删除
delete from tablename where [condition];
1)删除id为1的数据
zxy=# delete from ods.test4 where id = 1;
DELETE 2
2)查询数据
zxy=# select * from ods.test4 where id = 1;
id | name | age
----+------+-----
(0 rows)
2.3 数据导出
外部表数据无法导出。
copy tablename to filepath;
3.DQL(Data Query Language)数据查询语言
3.1 基础语法及执行顺序
SELECT [DISTINCT] colum1, column2, ...
FROM table_name -- 从什么表查
[WHERE condition] -- 过滤
[GROUP BY column_list] -- 分组查询
[HAVING column_list] -- 分组后过滤
[ORDER BY column_list] -- 排序
[LIMIT number] -- 限制输出的行数
3.2 基本查询
1)准备数据
[gpadmin@mdw ~]$ cat dept.txt
10,行政部,1700
20,财务部,1800
30,教学部,1900
40,销售部,1700
50,后勤部,1800
[gpadmin@mdw ~]$ cat emp.txt
7369,张三,研发,800.00,30
7499,李四,财务,1600.00,20
7521,王五,行政,1250.00,10
7566,赵六,销售,2975.00,40
7654,侯七,研发,1250.00,30
7698,马八,研发,2850.00,30
7782,金九,,2450.0,30
7788,银十,行政,3000.00,10
7839,小芳,销售,5000.00,40
7844,小明,销售,1500.00,40
7876,小李,行政,1100.00,10
7900,小元,讲师,950.00,30
7902,小海,行政,3000.00,10
7934,小红明,讲师,1300.00,30
7999,小八,行政,4000.00,10
2)准备表
create table ods.dept (
deptno int, --部门编号
dname text, --部门名称
loc int --部门位置id
) ;
create table ods.emp (
empno int, -- 员工编号
ename text, -- 员工姓名
job text, -- 员工岗位(大数据工程师、前端工程师、java工程师)
sal double precision, -- 员工薪资
deptno int -- 部门编号
) ;
3)导入数据
copy ods.dept from '/home/gpadmin/dept.txt' delimiter ',';
copy ods.emp from '/home/gpadmin/emp.txt' delimiter ',';
1)直接查询
# 使用*,查询所有数据
zxy=# select * from ods.dept;
deptno | dname | loc
--------+--------+------
30 | 教学部 | 1900
20 | 财务部 | 1800
40 | 销售部 | 1700
10 | 行政部 | 1700
(4 rows)
# 查询指定列
zxy=# select deptno,dname from ods.dept;
deptno | dname
--------+--------
20 | 财务部
40 | 销售部
10 | 行政部
30 | 教学部
(4 rows)
2)查询取别名
# 可以使用as,可以不使用
zxy=# select deptno as no1,deptno no2 from ods.dept;
no1 | no2
-----+-----
20 | 20
40 | 40
30 | 30
10 | 10
(4 rows)
3)limit查询
# 1.查询并取三条数据
zxy=# select * from ods.emp limit 3;
empno | ename | job | sal | deptno
-------+-------+------+------+--------
7654 | 侯七 | 研发 | 1250 | 30
7876 | 小李 | 行政 | 1100 | 10
7521 | 王五 | 行政 | 1250 | 10
(3 rows)
# 2.根据empno升序排序,并取三条记录
zxy=# select * from ods.emp order by empno limit 3;
empno | ename | job | sal | deptno
-------+-------+------+------+--------
7369 | 张三 | 研发 | 800 | 30
7499 | 李四 | 财务 | 1600 | 20
7521 | 王五 | 行政 | 1250 | 10
(3 rows)
# 3.根据empno升序排序,从第3行开始取3条数据
zxy=# select * from ods.emp order by empno limit 3 offset 2;
empno | ename | job | sal | deptno
-------+-------+------+------+--------
7521 | 王五 | 行政 | 1250 | 10
7566 | 赵六 | 销售 | 2975 | 40
7654 | 侯七 | 研发 | 1250 | 30
(3 rows)
4)条件查询
zxy=# select * from ods.emp where ename = '王五';
empno | ename | job | sal | deptno
-------+-------+------+------+--------
7521 | 王五 | 行政 | 1250 | 10
(1 row)
5)关系运算符
操作符 | 支持的数据类型 | 描述 |
---|---|---|
A=B | 基本数据类型 | 如果A等于B则返回true,反之返回false |
A<=>B | 基本数据类型 | 如果A和B都为null,则返回true,如果一边为null,返回false |
A<>B, A!=B | 基本数据类型 | A或者B为null则返回null;如果A不等于B,则返回true,反之返回false |
A <B | 基本数据类型 | A或者B为null,则返回null;如果A小于B,则返回true,反之返回false |
A<=B | 基本数据类型 | A或者B为null,则返回null;如果A小于等于B,则返回true,反之返回false |
A>B | 基本数据类型 | A或者B为null,则返回null;如果A大于B,则返回true,反之返回false |
A>=B | 基本数据类型 | A或者B为null,则返回null;如果A大于等于B,则返回true,反之返回false |
A [not] between B and C | 基本数据类型 | 如果A,B或者C任一为null,则结果为null。如果A的值大于等于B而且小于或等于C,则结果为true,反之为false。如果使用not关键字则可达到相反的效果。 |
A is null | 所有数据类型 | 如果A等于null,则返回true,反之返回false |
A is not null | 所有数据类型 | 如果A不等于null,则返回true,反之返回false |
in(数值1,数值2) | 所有数据类型 | 使用 in运算显示列表中的值 |
A [not] like B | string 类型 | B是一个SQL下的简单正则表达式,也叫通配符模式,如果A与其匹配的话,则返回true;反之返回false。B的表达式说明如下:‘x%’表示A必须以字母‘x’开头,‘%x’表示A必须以字母‘x’结尾,而‘%x%’表示A包含有字母‘x’,可以位于开头,结尾或者字符串中间。如果使用not关键字则可达到相反的效果。 |
A rlike B, A regexp B | string 类型 | B是基于java的正则表达式,如果A与其匹配,则返回true;反之返回false。匹配使用的是JDK中的正则表达式接口实现的,因为正则也依据其中的规则。例如,正则表达式必须和整个字符串A相匹配,而不是只需与其字符串匹配。 |
# 1.查询sal等于1500
zxy=# select * from ods.emp where sal = 1500;
empno | ename | job | sal | deptno
-------+-------+------+------+--------
7844 | 小明 | 销售 | 1500 | 40
(1 row)
# 2.查询sal大于等于1500
zxy=# select * from ods.emp where sal >= 1500;
empno | ename | job | sal | deptno
-------+-------+------+------+--------
7566 | 赵六 | 销售 | 2975 | 40
7844 | 小明 | 销售 | 1500 | 40
7788 | 银十 | 行政 | 3000 | 10
7839 | 小芳 | 销售 | 5000 | 40
7499 | 李四 | 财务 | 1600 | 20
7698 | 马八 | 研发 | 2850 | 30
7782 | 金九 | | 2450 | 30
7902 | 小海 | 行政 | 3000 | 10
7999 | 小八 | 行政 | 4000 | 10
(8 rows)
# 3.查询sql小于1500
zxy=# select * from ods.emp where sal < 1500;
empno | ename | job | sal | deptno
-------+--------+------+------+--------
7521 | 王五 | 行政 | 1250 | 10
7900 | 小元 | 讲师 | 950 | 30
7369 | 张三 | 研发 | 800 | 30
7934 | 小红明 | 讲师 | 1300 | 30
7654 | 侯七 | 研发 | 1250 | 30
7876 | 小李 | 行政 | 1100 | 10
(6 rows)
# 4.查询sal在1000和1500之间的
zxy=# select * from ods.emp where sal between 1000 and 1500;
empno | ename | job | sal | deptno
-------+--------+------+------+--------
7654 | 侯七 | 研发 | 1250 | 30
7876 | 小李 | 行政 | 1100 | 10
7521 | 王五 | 行政 | 1250 | 10
7844 | 小明 | 销售 | 1500 | 40
7934 | 小红明 | 讲师 | 1300 | 30
(5 rows)
# 5.查询job为null的
zxy=# select * from ods.emp where job is null;
empno | ename | job | sal | deptno
-------+-------+-----+------+--------
7782 | 金九 | | 2450 | 30
(1 row)
# 6.通配符"_"查询明结尾
zxy=# select * from ods.emp where ename like '_明';
empno | ename | job | sal | deptno
-------+-------+------+------+--------
7844 | 小明 | 销售 | 1500 | 40
(1 row)
# 7.通配符"%"查询明结尾
zxy=# select * from ods.emp where ename like '%明';
empno | ename | job | sal | deptno
-------+--------+------+------+--------
7844 | 小明 | 销售 | 1500 | 40
7934 | 小红明 | 讲师 | 1300 | 30
(2 rows)
6)逻辑运算符
操作符 | 含义 |
---|---|
and | 逻辑并 |
or | 逻辑或 |
not | 逻辑否 |
# 1.查询研发岗位,工资大于1000
zxy=# select * from ods.emp where job = '研发' and sal > 1000;
empno | ename | job | sal | deptno
-------+-------+------+------+--------
7654 | 侯七 | 研发 | 1250 | 30
7698 | 马八 | 研发 | 2850 | 30
(2 rows)
# 2.查询研发岗位,或者工资大于1000
zxy=# select * from ods.emp where job = '研发' or sal > 1000;
empno | ename | job | sal | deptno
-------+--------+------+------+--------
7654 | 侯七 | 研发 | 1250 | 30
7876 | 小李 | 行政 | 1100 | 10
7521 | 王五 | 行政 | 1250 | 10
7566 | 赵六 | 销售 | 2975 | 40
7844 | 小明 | 销售 | 1500 | 40
7788 | 银十 | 行政 | 3000 | 10
7839 | 小芳 | 销售 | 5000 | 40
7369 | 张三 | 研发 | 800 | 30
7499 | 李四 | 财务 | 1600 | 20
7698 | 马八 | 研发 | 2850 | 30
7902 | 小海 | 行政 | 3000 | 10
7934 | 小红明 | 讲师 | 1300 | 30
7782 | 金九 | | 2450 | 30
7999 | 小八 | 行政 | 4000 | 10
(13 rows)
# 3.查询岗位不是研发,行政的
zxy=# select * from ods.emp where job not in ('研发','行政');
empno | ename | job | sal | deptno
-------+--------+------+------+--------
7839 | 小芳 | 销售 | 5000 | 40
7566 | 赵六 | 销售 | 2975 | 40
7844 | 小明 | 销售 | 1500 | 40
7900 | 小元 | 讲师 | 950 | 30
7499 | 李四 | 财务 | 1600 | 20
7934 | 小红明 | 讲师 | 1300 | 30
(6 rows)
7)聚合函数
聚合函数 | 含义 |
---|---|
count() | 表示统计行数 |
max() | 求最大值,不含null,除非所有值都是null |
min() | 求最小值,不包含null,除非所有值都是null |
sum() | 求和,不包含null |
avg() | 求平均值,不包含null |
# 统计emp表有多少条数据,最大sal、最小sal、sal合计、sal平均值
zxy=# select count(*),max(sal) max_sal,min(sal) min_sal,sum(sal) sum_sal,avg(sal) avg_sal from ods.emp;
count | max_sal | min_sal | sum_sal | avg_sal
-------+---------+---------+---------+------------------
14 | 5000 | 800 | 29025 | 2073.21428571429
(1 row)
3.3 分组查询
1)Group By语句
Group By语句通常会和聚合函数一起使用,按照一个或者多个列对结果进行分组,然后执行对应的聚合操作。查询时如果使用Group BY,那么Select查询的字段只能包括Group By后的字段。
# 1.查看各岗位总工资多少
zxy=# select job,sum(sal) sal from ods.emp group by job;
job | sal
------+------
行政 | 8350
讲师 | 2250
| 2450
销售 | 9475
研发 | 4900
财务 | 1600
(6 rows)
# 2.查看各岗位最大工资、最小工资、平均工资
zxy=# select job,max(sal),min(sal),avg(sal) from ods.emp group by job;
job | max | min | avg
------+------+------+------------------
销售 | 5000 | 1500 | 3158.33333333333
研发 | 2850 | 800 | 1633.33333333333
财务 | 1600 | 1600 | 1600
行政 | 3000 | 1100 | 2087.5
讲师 | 1300 | 950 | 1125
| 2450 | 2450 | 2450
(6 rows)
2)Having语句
where后面不能跟分组聚合函数,而having后面可以且只能使用分组聚合函数,不可以使用聚合函数的别名。
having只用于group by分组统计语句。
# 1.查询岗位人数大于3的岗位
zxy=# select job,count(*) from ods.emp group by job having count(*) > 3;
job | count
------+-------
行政 | 4
(1 row)
3.4 联合查询
1)等值JSON
只有两个表中都符合条件的数据才能保留下来;
查询行政部有哪些人?
select *
from ods.dept
join ods.emp on dept.deptno = emp.deptno
where dept.dname = '行政部';
zxy=# select *
zxy-# from ods.dept
zxy-# join ods.emp on dept.deptno = emp.deptno
zxy-# where dept.dname = '行政部';
deptno | dname | loc | empno | ename | job | sal | deptno
--------+--------+------+-------+-------+------+------+--------
10 | 行政部 | 1700 | 7521 | 王五 | 行政 | 1250 | 10
10 | 行政部 | 1700 | 7788 | 银十 | 行政 | 3000 | 10
10 | 行政部 | 1700 | 7902 | 小海 | 行政 | 3000 | 10
10 | 行政部 | 1700 | 7876 | 小李 | 行政 | 1100 | 10
(4 rows)
2)内连接
只有两个表中都符合条件的数据才能保留下来,默认的join即为inner join内连接。
查询行政部有哪些人?
select *
from ods.dept
join ods.emp on dept.deptno = emp.deptno
where dept.dname = '行政部';
zxy=# select *
zxy-# from ods.dept
zxy-# join ods.emp on dept.deptno = emp.deptno
zxy-# where dept.dname = '行政部';
deptno | dname | loc | empno | ename | job | sal | deptno
--------+--------+------+-------+-------+------+------+--------
10 | 行政部 | 1700 | 7521 | 王五 | 行政 | 1250 | 10
10 | 行政部 | 1700 | 7788 | 银十 | 行政 | 3000 | 10
10 | 行政部 | 1700 | 7902 | 小海 | 行政 | 3000 | 10
10 | 行政部 | 1700 | 7876 | 小李 | 行政 | 1100 | 10
10 | 行政部 | 1700 | 7999 | 小八 | 行政 | 4000 | 10
(4 rows)
3)左外连接
左外连接:left join操作符,左边的所有记录将会返回,右表匹配不到的返回空。
select *
from ods.dept
left join ods.emp on dept.deptno = emp.deptno
where dept.dname = '后勤部';
zxy=# select *
zxy-# from ods.dept
zxy-# left join ods.emp on dept.deptno = emp.deptno
zxy-# where dept.dname = '后勤部';
deptno | dname | loc | empno | ename | job | sal | deptno
--------+--------+------+-------+-------+-----+-----+--------
50 | 后勤部 | 1800 | | | | |
(1 row)
4)右外连接
右外连接:right join操作符,右边的所有记录返回,左边匹配不到的为空。
select *
from ods.emp
right join ods.dept on emp.deptno = dept.deptno
where dept.dname = '后勤部';
zxy=# select *
zxy-# from ods.emp
zxy-# right join ods.dept on emp.deptno = dept.deptno
zxy-# where dept.dname = '后勤部';
empno | ename | job | sal | deptno | deptno | dname | loc
-------+-------+-----+-----+--------+--------+--------+------
| | | | | 50 | 后勤部 | 1800
(1 row)
5)全连接
全连接:full join操作符,返回两个表中所有数据,如果有不符合条件的返回空。
select *
from ods.dept
full join ods.emp on dept.deptno = emp.deptno;
zxy=# select *
zxy-# from ods.dept
zxy-# full join ods.emp on dept.deptno = emp.deptno;
deptno | dname | loc | empno | ename | job | sal | deptno
--------+--------+------+-------+--------+------+------+--------
10 | 行政部 | 1700 | 7788 | 银十 | 行政 | 3000 | 10
10 | 行政部 | 1700 | 7902 | 小海 | 行政 | 3000 | 10
10 | 行政部 | 1700 | 7521 | 王五 | 行政 | 1250 | 10
10 | 行政部 | 1700 | 7876 | 小李 | 行政 | 1100 | 10
10 | 行政部 | 1700 | 7999 | 小八 | 行政 | 4000 | 10
30 | 教学部 | 1900 | 7369 | 张三 | 研发 | 800 | 30
30 | 教学部 | 1900 | 7698 | 马八 | 研发 | 2850 | 30
30 | 教学部 | 1900 | 7934 | 小红明 | 讲师 | 1300 | 30
30 | 教学部 | 1900 | 7782 | 金九 | | 2450 | 30
30 | 教学部 | 1900 | 7900 | 小元 | 讲师 | 950 | 30
30 | 教学部 | 1900 | 7654 | 侯七 | 研发 | 1250 | 30
20 | 财务部 | 1800 | 7499 | 李四 | 财务 | 1600 | 20
40 | 销售部 | 1700 | 7844 | 小明 | 销售 | 1500 | 40
40 | 销售部 | 1700 | 7839 | 小芳 | 销售 | 5000 | 40
40 | 销售部 | 1700 | 7566 | 赵六 | 销售 | 2975 | 40
50 | 后勤部 | 1800 | | | | |
(15 rows)
6)笛卡尔积
无条件关联两个表,会产生笛卡尔积,两边数据互相连接。
例如查询行政部,笛卡尔积可以匹配到所有的员工名单
zxy=# select *
zxy-# from ods.dept,ods.emp
zxy-# where dname = '行政部';
deptno | dname | loc | empno | ename | job | sal | deptno
--------+--------+------+-------+--------+------+------+--------
10 | 行政部 | 1700 | 7521 | 王五 | 行政 | 1250 | 10
10 | 行政部 | 1700 | 7566 | 赵六 | 销售 | 2975 | 40
10 | 行政部 | 1700 | 7844 | 小明 | 销售 | 1500 | 40
10 | 行政部 | 1700 | 7900 | 小元 | 讲师 | 950 | 30
10 | 行政部 | 1700 | 7788 | 银十 | 行政 | 3000 | 10
10 | 行政部 | 1700 | 7839 | 小芳 | 销售 | 5000 | 40
10 | 行政部 | 1700 | 7654 | 侯七 | 研发 | 1250 | 30
10 | 行政部 | 1700 | 7876 | 小李 | 行政 | 1100 | 10
10 | 行政部 | 1700 | 7369 | 张三 | 研发 | 800 | 30
10 | 行政部 | 1700 | 7499 | 李四 | 财务 | 1600 | 20
10 | 行政部 | 1700 | 7698 | 马八 | 研发 | 2850 | 30
10 | 行政部 | 1700 | 7902 | 小海 | 行政 | 3000 | 10
10 | 行政部 | 1700 | 7934 | 小红明 | 讲师 | 1300 | 30
10 | 行政部 | 1700 | 7782 | 金九 | | 2450 | 30
10 | 行政部 | 1700 | 7999 | 小八 | 行政 | 4000 | 10
(14 rows)
7)联合查询
union 和 union all都是上下拼接SQL的结果,union会去重,union all不去重。文章来源:https://www.toymoban.com/news/detail-814510.html
# 1.union all不去重
zxy=# select empno,ename from ods.emp where ename = '王五'
zxy-# union all select empno,ename from ods.emp where ename = '王五';
empno | ename
-------+-------
7521 | 王五
7521 | 王五
(2 rows)
# 2.union去重
zxy=# select empno,ename from ods.emp where ename = '王五'
zxy-# union select empno,ename from ods.emp where ename = '王五';
empno | ename
-------+-------
7521 | 王五
(1 row)
3.5 排序
Order By全局排序,默认是asc升序排序,可以指定desc降序排序。文章来源地址https://www.toymoban.com/news/detail-814510.html
# 1.默认根据deptno升序查询表
zxy=# select deptno,dname,loc from ods.dept order by deptno;
deptno | dname | loc
--------+--------+------
10 | 行政部 | 1700
20 | 财务部 | 1800
30 | 教学部 | 1900
40 | 销售部 | 1700
50 | 后勤部 | 1800
(5 rows)
# 2.根据deptno升序查询表
zxy=# select deptno,dname,loc from ods.dept order by deptno asc;
deptno | dname | loc
--------+--------+------
10 | 行政部 | 1700
20 | 财务部 | 1800
30 | 教学部 | 1900
40 | 销售部 | 1700
50 | 后勤部 | 1800
(5 rows)
# 3.根据deptno降序查询表
zxy=# select deptno,dname,loc from ods.dept order by deptno desc;
deptno | dname | loc
--------+--------+------
50 | 后勤部 | 1800
40 | 销售部 | 1700
30 | 教学部 | 1900
20 | 财务部 | 1800
10 | 行政部 | 1700
(5 rows)
到了这里,关于《GreenPlum系列》GreenPlum初级教程-05GreenPlum语言DDL&DML&DQL的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!