postgresql学习笔记

1.Linux 下安装pg

 

 apt install postgresql 

 

运行 vi  /etc/postgresql/9.5/main/postgresql.conf  

      修改 #listen_addresses='localhost'   为      listen_addresses='*'

       修改 #password_encryption=on       为      password_encryption=on

运行 vi /etc/postgresql/9.5/main/pg_hba.conf

      在 IPv4 下面添加

          host      all      all        0.0.0.0/0     md5

修改postgres账号的密码：

  运行  sudo passwd -d postgres   -清除原来的密码 

运行  sudo -u postgres passwd   -添加新密码

 

2.pg数据库支持的数据类型

Pg 支持 数值类型、货币类型、字符类型、日期/时间类型、布尔类型、枚举类型、几何类型、位串类型、文本搜索类型、UUID类型、XML类型、JSON类型、数组类型、声明类型、范围类型、对象标识符类型

3.pg数据库查询表以及数据库的命令

\l 查看数据库

Create database (createdb)

Drop database(dropdb)

\c  database 选择某个数据库

\d 查看数据库中的表

\d tablename  查看表信息

Drop table tablename1.tablename2 删除表结构及表信息

 

 

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

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

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

使用模式的优势：

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

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

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

 

reate schema myschema;

create table myschema.company(

以上命令创建了一个空的表格，我们使用以下 SQL 来查看表格是否创建：

runoobdb=# select * from myschema.company;

 id | name | age | address | salary ----+------+-----+---------+--------(0 rows)

删除模式

删除一个为空的模式（其中的所有对象已经被删除）：

DROP SCHEMA myschema;

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

DROP SCHEMA myschema CASCADE;

在 PostgreSQL 中，LIKE 子句是只能用于对字符进行比较，因此在上面列子中，我们要将整型数据类型转化为字符串数据类型。

SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';

但是，在某些情况下，可能需要从一个特定的偏移开始提取记录。

下面是一个实例，从第三位开始提取 3 个记录：

runoobdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

 

删除约束

删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用 \d 表名 可以找到这些信息。

通用语法如下：

ALTER TABLE table_name DROP CONSTRAINT some_name;

删除约束

删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用 \d 表名 可以找到这些信息。

通用语法如下：

ALTER TABLE table_name DROP CONSTRAINT some_name;

 

交叉连接

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

由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。

SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

 

内连接

内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。

当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。

内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。

INNER 关键字是可选的。

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

 

左外连接

外部连接是内部连接的扩展。SQL 标准定义了三种类型的外部连接: LEFT、RIGHT 和 FULL, PostgreSQL 支持所有这些。

对于左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行。

下面是左外连接（ LEFT OUTER JOIN ）的基础语法：

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

基于上面两张表，我们可以写个左外连接，如下：

SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

右外连接

首先，执行内部连接。然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行，结果表总是有一行。

下面是右外连接（ RIGHT OUT JOIN）的基本语法：

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

基于上面两张表，我们建立一个右外连接：

SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

外连接

首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。

下面是外连接的基本语法：

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

基于上面两张表，可以建立一个外连接：

SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

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

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

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

 

 

 

NULL 值代表遗漏的未知数据。

默认地，表的列可以存放 NULL 值。

使用is null 或is not null 进行比较

SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;

SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL;

索引类型

单列索引

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

CREATE INDEX index_name

ON table_name (column_name);

组合索引

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

CREATE INDEX index_name

ON table_name (column1_name, column2_name);

不管是单列索引还是组合索引，该索引必须是在 WHEHE 子句的过滤条件中使用非常频繁的列。

如果只有一列被使用到，就选择单列索引，如果有多列就使用组合索引。

唯一索引

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

CREATE UNIQUE INDEX index_name

on table_name (column_name);

局部索引

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

CREATE INDEX index_name

on table_name (conditional_expression);

隐式索引

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

PostgreSQL ALTER TABLE 命令

在 PostgreSQL 中，ALTER TABLE 命令用于添加，修改，删除一张已经存在表的列。

另外你也可以用 ALTER TABLE 命令添加和删除约束。

语法

用 ALTER TABLE 在一张已存在的表上添加列的语法如下：

ALTER TABLE table_name ADD column_name datatype;

在一张已存在的表上 DROP COLUMN（删除列），语法如下：

ALTER TABLE table_name DROP COLUMN column_name;

修改表中某列的 DATA TYPE（数据类型），语法如下：

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

给表中某列添加 NOT NULL 约束，语法如下：

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

给表中某列 ADD UNIQUE CONSTRAINT（ 添加 UNIQUE 约束），语法如下：

ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

给表中 ADD CHECK CONSTRAINT（添加 CHECK 约束），语法如下：

ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

给表 ADD PRIMARY KEY（添加主键），语法如下：

ALTER TABLE table_name

ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

DROP CONSTRAINT （删除约束），语法如下：

ALTER TABLE table_name

DROP CONSTRAINT MyUniqueConstraint;

如果是 MYSQL ，代码是这样：

ALTER TABLE table_name

DROP INDEX MyUniqueConstraint;

DROP PRIMARY KEY （删除主键），语法如下：

ALTER TABLE table_name

DROP CONSTRAINT MyPrimaryKey;

如果是 MYSQL ，代码是这样：

ALTER TABLE table_name

DROP PRIMARY KEY;