PostgreSQL语法

1.Mac环境下使用brew安装postgresql

输入以下指令安装postgresql

brew install postgresql

切换postgresql用户

sudo su -postgres

登陆PostgreSQL控制台

psql

这个时候会出现以下情况，是因为没有指定数据库，会根据当前用户名去找，所以找不到

指定数据库

psql -d postgres

此时就进入了PostgreSQL的控制台

退出PostgreSQL控制台，输入以下指令

\q

添加用户，比如wl

create user wl with password '123456';

创建用户数据库

create database templates owner wl

将数据库权限赋予给wl用户

grant all privileges on database templates to wl ;

创建linux普通用户，与刚才新建的数据库用户同名

sudo adduser wl


sudo passwd 123456

2.PostgreSQL语法

进入命令行工具，我们可以使用 \help command_name来查看各个命令的语法

\help select   #可以查看select对应的语法

1.创建数据库

在PostgreSQL 命令窗口输入以下指令创建数据库

create database dbname ;     #此处结尾一定要加分号，不然sql语句无效

createdb命令创建数据库，他是对create databse的封装，语法如下：

createdb [option...] [dbname [description]]

三个参数的说明：

• dbname：要创建的数据库名称

• description：关于新创建的数据库相关的说明

• options：参数可选项，可以是以下值：

1	-D tablespace指定数据库默认表空间。
2	-e将 createdb 生成的命令发送到服务端。
3	-E encoding指定数据库的编码。
4	-l locale指定数据库的语言环境。
5	-T template指定创建此数据库的模板。
6	--help显示 createdb 命令的帮助信息。
7	-h host指定服务器的主机名。
8	-p port指定服务器监听的端口，或者 socket 文件。
9	-U username连接数据库的用户名。
10	-w忽略输入密码。
11	-W连接时强制要求输入密码。

接下来我们打开一个命令窗口，进入到 PostgreSQL 的安装目录，并进入到 bin 目录，createdb 命令位于 PostgreSQL安装目录/bin 下，执行创建数据库的命令：

举个例子（创建字符编码为utf8 的数据库 名为test01）：

createdb -E utf8 test01

2.选择数据库

输入\l用于查看已经存在的数据库

\l

输入\c +数据库名称 进入数据库

3.删除数据库

PostgreSQL删除数据库有以下两种方式：

• 使用

drop databasesql语句来删除

• 使用

dropdb命令来删除

1.在Postgresql命令行窗口执行以下命令

drop database if exists dbname ;   #如果存在此数据库就删除

2.dropdb命令删除数据库

drop命令语法格式如下：

dropdb [connection-option...] [option...] dbname

参数说明：

dbname：要删除的数据库名

options：参数可选项，可以是以下值：

1	-e显示 dropdb 生成的命令并发送到数据库服务器。
2	-i在做删除的工作之前发出一个验证提示。
3	-V打印 dropdb 版本并退出。
4	--if-exists如果数据库不存在则发出提示信息，而不是错误信息。
5	--help显示有关 dropdb 命令的帮助信息。
6	-h host指定运行服务器的主机名。
7	-p port指定服务器监听的端口，或者 socket 文件。
8	-U username连接数据库的用户名。
9	-w连接数据库的用户名。
10	-W连接时强制要求输入密码。
11	--maintenance-db=dbname删除数据库时指定连接的数据库，默认为 postgres，如果它不存在则使用 template1。

打开PostgreSQL的安装目录，进入到bin目录下面执行删除数据库的命令

4.PostgreSQL数据类型

数值类型

名字	存储长度	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

货币类型

名字	存储容量
money	8 字节

字符类型

序号	名字 & 描述
1	character varying(n), varchar(n)变长，有长度限制
2	character(n), char(n)f定长,不足补空白
3	text变长，无长度限制

日期/时间类型

名字	存储空间	描述	最低值	最高值	分辨率
timestamp [ (p) ] [ without time zone ]	8 字节	日期和时间(无时区)	4713 BC	294276 AD	1 毫秒 / 14 位
timestamp [ (p) ] with time zone	8 字节	日期和时间，有时区	4713 BC	294276 AD	1 毫秒 / 14 位
date	4 字节	只用于日期	4713 BC	5874897 AD	1 天
time [ (p) ] [ without time zone ]	8 字节	只用于一日内时间	0:00:00	0:00:00	1 毫秒 / 14 位
time [ (p) ] with time zone	12 字节	只用于一日内时间，带时区	00:00:00+1459	24:00:00-1459	1 毫秒 / 14 位
interval [ fields ] [ (p) ]	12 字节	时间间隔	-178000000 年	178000000 年	1 毫秒 / 14 位

布尔类型

名称	存储格式	描述
boolean	1 字节	true/false

几何类型

名字	存储空间	说明	表现形式
point	16 字节	平面中的点	(x,y)
line	32 字节	(无穷)直线(未完全实现)	((x1,y1),(x2,y2))
lseg	32 字节	(有限)线段	((x1,y1),(x2,y2))
box	32 字节	矩形	((x1,y1),(x2,y2))
path	16+16n 字节	闭合路径(与多边形类似)	((x1,y1),...)
path	16+16n 字节	开放路径	[(x1,y1),...]
polygon	40+16n 字节	多边形(与闭合路径相似)	((x1,y1),...)
circle	24 字节	圆	<(x,y),r> (圆心和半径)

5.创建表格

和mysql几乎一样

创建完成后输入

\d指令查看表格是否创建成功，

\d tablename查看表格信息

6.删除表格

删除表格语法和mysql一致

drop table tablename ;    #tablename 是表名

7.PostgreSQL模式

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

• 允许多个用户使用一个数据库并且不会互相干扰。

• 将数据库对象组织成逻辑组以便更容易管理。

• 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套

我们可以使用以下命令来创建模式(schema) :

create schema myschema;

再使用schema创建一个表格

create table myschema.tab02(


id int primary key,

username varchar(10),

phone varchar(11) 

);

删除一个模式以及其中包含的所有对象

drop schema myschema cascade;

删除一个为空的模式(其中的所有对象已经被删除)

drop schema myschema;

显示当前搜索路径,使用以下命令

show search_path;

可以看到以下内容

想要切换模式使用以下命令

set search_path to m2;

8.with语句

在 WITH 子句中可以使用自身输出的数据。

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

这里使用RECURSIVE 关键字和 WITH 子句编写一个查询，查询 薪水大于20的数据并计算它们的和

with RECURSIVE t(n) AS(


VALUES (0)

UNION ALL

select salary from ztable where salary >20

)

select sum(n) from t;

查询结果如下图：

建立一张和 ztable2 表相似的 ztable 表，使用 DELETE 语句和 WITH 子句删除 ztable 表中 SALARY(工资) 字段大于等于 20 的数据，并将删除的数据插入 ztable2 表，实现将 ztable 表数据转移到 ztable2 表中：

首先将表中得ztable的结构复制给ztable2

create table ztable2 (like ztable);

执行成功后输入\d ztable2查看ztable2的结构

执行删除代码

with moved_rows as(


delete from ztable where salary>=20

RETURNING *

)

insert into ztable2 (select * from moved_rows);

然后分别查看ztable和ztable2

9.having语句

可以结合group by语句使用having

按名称分组，并且 name(名称) 字段的计数少于 2 数据

select name  from ztable2 group by name having count(name)<2;

3.PostgreSQL高级

1.约束

无法复制加载中的内容

2.五种join

创建两张表，内容如下:

CROSS JOIN(交叉连接)

交叉连接，就是把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行

INNER JOIN (内连接)

左表和右表共有的部分，取交集

执行内连接

select e.id,e.emp_name,c.com_name,c.com_location  from 


employee e inner join company c on e.company_id=c.com_id;

LEFT JOIN(左外连接)

取A独有的部分和AB共有的部分

执行左外连接sql

select e.id,e.emp_name,c.com_name,c.com_location  from


employee e left  join company c on e.company_id=c.com_id;

特殊的左连接，显示为A独有的部分

select e.id,e.emp_name,c.com_name,c.com_location  from


employee e left  join company c on e.company_id=c.com_id where c.com_id is null;

RIGHT JOIN(右外连接)

取AB共有部分和B独有部分

执行右外连接sql

select e.id,e.emp_name,c.com_name,c.com_location  from employee


e left  join company c on e.company_id=c.com_id;

FULL JOIN全连接

A独有的部分加上B独有的部分加上AB共有的部分

执行全连接

select e.id,e.emp_name,c.com_name,c.com_location  from employee 


e full join company c on e.company_id=c.com_id;

显示两表独有的数据，需要在全连接的代码上进行修改

select e.id,e.emp_name,c.com_name,c.com_location  from employee

e full join company c on e.company_id=c.com_id where e.company_id is null or c.com_id is null ;

下面是展示效果

3.UNION和UNION ALL

UNION

PostgreSQL UNION 操作符合并两个或多个 SELECT 语句的结果。

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

先分别查看两个表中的数据

使用UNION将两张表联合起来

select e.id,e.emp_name,c.com_name from employee e inner join company c 


on e.company_id =c.com_id union select e.id,e.emp_name,c.com_name from employee e 


left join company c on c.com_id=e.company_id;

执行结果如下

UNION ALL

UNION ALL 操作符可以连接两个有重复行的 SELECT 语句，默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL

将刚才的sql连接两表换成UNIION ALL

执行以下sql

select e.id,e.emp_name,c.com_name from employee e inner join company c


on e.company_id=c.com_id union all select e.id,e.emp_name,c.com_name

from employee e left join company c on e.company_id=c.com_id;

执行结果如下图

4.自增键值

AUTO INCREMENT（自动增长） 会在新记录插入表中时生成一个唯一的数字。

PostgreSQL 使用序列来标识字段的自增长，数据类型有 smallserial、serial 和 bigserial 。这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。

以下使用序列创建自增字段

create table order_form ( id serial not null,name varchar(20),location varchar(20));

然后往表里面插入几条数据可以看到主键自增

三种类型的范围：

无法复制加载中的内容

5.索引

索引的分类

索引名称	使用场景
B-tree索引	适合处理那些能够按顺序存储数据，适合重复率低、分散性大（用的最多）
Hash索引	只能处理简单的等于比较
Gist索引	一种索引架构
GIN索引	反转索引，处理包含多个值的键

以下是Postgre的五种索引

• 单列索引

• 组合索引

• 唯一索引

• 局部索引

• 隐式索引

单列索引

单列索引是一个只基于表的一个列上创建的索引，基本语法如下：

create index index_name on order_form (name);

创建完成后可以使用

\d order_form查看当前表的索引情况

组合索引

组合索引是基于表的多列上创建的索引，基本语法如下：

create index company_index on company (com_name,com_location);

唯一索引

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：

create unique index unique_index on company (com_telephone);

隐式索引

隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

局部索引

局部索引 是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行

这里举例一个场景，我只想给部门id大于1003的数据加索引,于是创建局部索引语法如下：

create index part_index on employee(company_id) where company_id>1003 ;

我们使用以下命令来删除索引

drop index part_index;

什么时候避免使用索引？

• 较小的表不使用索引

• 频繁更新或插入操作的表

• 含有大量的NULL值列

• 频繁操作的列

6.联合主键和复合主键

复合主键是指表中的主键含有一个以上的字段组成，不用没有含义的id作为主键

create table student( 

id int,

name varchar(20),

school varchar(20),

primary key(name,id) 

);

name和id组合起来就是student表的复合主键，因为name字段可能会出现重名，所以要加上id字段这样就可以保证你记录的唯一性 ,一般情况下，主键的字段长度和字段数目要越少越好

联合主键就是多个主键联合形成一个主键组合（主键原则上是唯一的，别被唯一值困扰）

举个例子

主键A和主键B组成联合主键

主键A跟主键B的数据可以完全相同，用2个字段(或者多个字段,后面具体都是用2个字段组合)来确定一条记录，联合就在于主键A跟主键B形成的联合主键是唯一的

下例主键A数据是1，主键B数据也是1，联合主键其实是11，这个11是唯一值，绝对不充许再出现11这个唯一值。(这就是多对多关系)

主键A数据 主键B数据

1 1

2 2

3 3

主键A 和主键B的组合最多也就是

11

12

13

21

22

23

31

32

33

4.CSV数据导入导出到PostgreSQL数据库

1.CSV导入数据

首先创建和csv文件结构相同的表

CREATE TABLE "dau" (

  appid       BIGINT ,

  ip          character varying(64) DEFAULT '' ,

  uuid        character varying(64) DEFAULT '' ,

  udid        character varying(64) DEFAULT '' ,

  channel     character varying(32) DEFAULT '' ,

  subchannel  character varying(32) DEFAULT '' ,

  referrer    character varying(128) DEFAULT '' ,

  country     character varying(32) DEFAULT '' ,

  region      character varying(32) DEFAULT '' ,

  city        character varying(32) DEFAULT '' ,

  locale      character varying(32) DEFAULT '' ,

  version     character varying(16) DEFAULT '' ,

  os          character varying(16) DEFAULT '' ,

  browser     character varying(16) DEFAULT '' ,

  screen      character varying(15) DEFAULT '' ,

  server_id   INT ,

  event_date  INT ,

  is_new_user SMALLINT ,

  origin_date INT ,

  status_1    character varying(32) DEFAULT '' ,

  status_2    character varying(32) DEFAULT '' ,

  status_3    character varying(32) DEFAULT '' ,

  status_4    character varying(32) DEFAULT '' ,

  status_5    character varying(32) DEFAULT '' ,

  status_6    character varying(32) DEFAULT '' ,

  status_7    character varying(32) DEFAULT '' ,

  status_8    character varying(32) DEFAULT '' ,

  status_9    character varying(32) DEFAULT '' ,

  status_10   character varying(32) DEFAULT '' ,

  area        character varying(16) DEFAULT '' ,

  lang        character varying(8) DEFAULT '' ,

  package     character varying(64) DEFAULT '' ,

  country_code    character varying(8) DEFAULT ''

);

在copy关键字后面指定表和列名称，如果csv文件包括所有列，则不需要显示指定列, '|' 表示csv里面的数据是以 | 进行分隔的 ,header 的作用是让copy命令忽略文件第一行记录。

copy dau from '/Users/wangliang/Documents/dau-1214/00_1639411346002379949_167794940.csv' delimiter '|' csv header ;

2.CSV导出数据

标准的导出数据

copy dau to '/Users/wangliang/Documents/dau-1214/aa.csv' delimiter '|' csv header;

使用sql结果查询导出数据

copy (select * from dau) to '/Users/wangliang/Documents/dau-1214/aa.csv' delimiter '|' csv header;

5.批量处理功能

1.insert ignore

这个功能主要就是根据条件如果存在就忽略

展示company表

展示company_bak表

1.创建规则，如果存在主键id重复，就忽略，不进行插入

create rule r_company_ignore as on insert to company  where exists (select 1 from company  where com_id = new.com_id) do instead nothing;

2.执行插入操作

insert into company select * from company_bak ;

查看表中的数据，发现如果存在就忽略了，并没有执行插入

2.insert update

这个功能主要是插入更新操作

复制一个company表为c2

创建插入更新规则

create rule r_c2_update as on insert to c2  where exists (select 1 from c2  where com_id = new.com_id) do instead update c2 set com_id=new.com_id,com_name=new.com_name,com_location=new.com_location,com_telephone=new.com_telephone where com_id=new.com_id ;

查看company_bak表的数据和company表中的数据

我们将company_bak中的数据插入到c2表中

3.删除两个表中交叉数据

查看两个表中的数据

使用以下语句进行删除

delete from company_bak cb using company c where cb.com_id=c.com_id;

再次查看company_bak表