Postgresql数据库基本操作

最新推荐文章于 2024-06-21 15:34:27 发布

灯灯灯灯灯灯灯

最新推荐文章于 2024-06-21 15:34:27 发布

阅读量481

点赞数 1

文章标签：数据库 postgresql mysql sql

本文链接：https://blog.csdn.net/qq_32173423/article/details/130548769

版权

视图
================================================
创建视图

   CREATE [TEMP | TEMPORARY] VIEW view_name AS
   SELECT column1, column2.....
   FROM table_name
   WHERE [condition];

eg.

   CREATE VIEW COMPANY_VIEW AS
   SELECT ID, NAME, AGE
   FROM COMPANY;

通过

SELECT * FROM COMPANY_VIEW; 查询

*类似将表中的部分数据拿出来*

删除视图

DROP VIEW view_name;

索引
==========================================
创建索引

CREATE INDEX index_name ON table_name;

### 索引类型

**单列索引**

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

CREATE INDEX index_name
ON table_name (column_name);

**组合索引**

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

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

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

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

**唯一索引**

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

CREATE UNIQUE INDEX index_name
on table_name (column_name);

**局部索引**

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

CREATE INDEX index_name
on table_name (conditional_expression);

**隐式索引**

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

删除索引

DROP INDEX index_name;

- 索引不应该使用在较小的表上。
- 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
- 索引不应该使用在含有大量的 NULL 值的列上。
- 索引不应该使用在频繁操作的列上。

触发器
================================================
[PostgreSQL 触发器 | 菜鸟教程 (runoob.com)](https://www.runoob.com/postgresql/postgresql-trigger.html)
- PostgreSQL 触发器可以在下面几种情况下触发：

- 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
- 在执行操作之后（在检查约束并插入、更新或删除完成之后）。
- 更新操作（在对一个视图进行插入、更新、删除时）。

- 触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 FOR EACH STATEMENT，不管修改了多少行，每个语句标记的触发器执行一次。

       CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
       ON table_name
       [
       -- 触发器逻辑....
       ];

以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器的语法：

   CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
   ON table_name
   [
   -- 触发器逻辑....
   ];

eg.

CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

auditlogfunc() 是 PostgreSQL 一个程序，其定义如下：

   CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
RETURN NEW;
   END;
   $example_table$ LANGUAGE plpgsql;

   CREATE OR REPLACE FUNCTION my_function_name() RETURNS TRIGGER AS $anyting$
   BEGIN
   -- 函数主体
   END;
   $anything$ LANGUAGE sql;

PostgreSQL GROUP BY 语句
======================================
**GROUP BY** 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。
GROUP BY 在一个 SELECT 语句中，(if have)放在 WHRER 子句的后面，ORDER BY 子句的前面。

eg.
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

PostgreSQL ORDER BY 语句
==============================
**ASC** 表示升序，**DESC** 表示降序。

PostgreSQL INSERT INTO 语句
===========================================
我们可以插入一行也可以同时插入多行。

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) *指定列，不写默认全部*
VALUES (value1, value2, value3,...valueN);

插入多行

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
中间拿逗号分隔

PostgreSQL SELECT 语句
=======
PostgreSQL SELECT 语句用于从数据库中选取数据。

结果被存储在一个结果表中，称为结果集。

SELECT column1, column2,...columnN FROM table_name;

PostgreSQL 运算符
================
运算符是一种告诉编译器执行特定的数学或逻辑操作的符号。

PostgreSQL 运算符是一个保留关键字或字符，一般用在 <mark style="background: #FFF3A3A6;">WHERE </mark>语句中，作为过滤条件。

- 算术运算符
- 比较运算符
- 逻辑运算符
- 按位运算符

PostgreSQL 表达式
=========================
- avg() ：返回一个表达式的平均值
- sum() ：返回指定字段的总和
- count() ：返回查询的记录总数

SELECT COUNT(*) AS "RECORDS" FROM COMPANY;

PostgreSQL WHERE 子句
=============================
WHERE 子句不仅可以用于 SELECT 语句中，同时也可以用于 UPDATE，DELETE 等等语句中。

   SELECT column1, column2, columnN
   FROM table_name
   WHERE [condition1]

   ### AND
   ### OR
   ### NOT NULL SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
   ### LIKE SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%'; %为通配符， _为占位1的通配符_
   ### IN SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 ); 25-27
   ### NOT IN SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
   ### BETWEEN SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
   ### 子查询（EXIST） SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

PostgreSQL UPDATE 语句
============================================
如果我们要更新在 PostgreSQL 数据库中的数据，我们可以用 **UPDATE** 来操作。

   UPDATE table_name
   SET column1 = value1, column2 = value2...., columnN = valueN
   WHERE [condition];

PostgreSQL DELETE 语句
=======================================
你可以使用 DELETE 语句来删除 PostgreSQL 表中的数据

DELETE FROM table_name WHERE [condition];

PostgreSQL HAVING 子句
=======================================
HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

   SELECT
   FROM
   WHERE
   GROUP BY
   HAVING
   ORDER BY

SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

PostgreSQL DISTINCT 关键字
=====================
在 PostgreSQL 中，DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。

我们平时在操作数据时，有可能出现一种情况，在一个表中有多个重复的记录，当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

SELECT DISTINCT name FROM COMPANY;

PostgreSQL 约束
=======
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

- **NOT NULL**：指示某列不能存储 NULL 值。
- **UNIQUE**：确保某列的值都是唯一的。
- PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。。
- FOREIGN Key：保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK：保证列中的值符合指定的条件。
- EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。

unique：值唯一

删除约束

ALTER TABLE table_name DROP CONSTRAINT some_name;

PostgreSQL 连接(JOIN)
=====================================
PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段

- CROSS JOIN ：交叉连接
- INNER JOIN：内连接
- LEFT OUTER JOIN：左外连接
- RIGHT OUTER JOIN：右外连接
- FULL OUTER JOIN：全外连接

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

SELECT ... FROM table1 CROSS JOIN table2 ... SELECT的元素可以来自两个表的任意元素

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

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

   SELECT table1.column1, table2.column2...
   FROM table1
   INNER JOIN table2
   ON table1.common_filed = table2.common_field;

eg.

   SELECT table1.column1, table2.column2...
   FROM table1
   INNER JOIN table2
   ON table1.common_filed = table2.common_field;

SELECT中的是展示出来的，ON后的是条件

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

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

行数以T1为准，没匹配到的相应位置上为NULL

##右外连接
---
与左外连接相反，行数以T2为准。

左右外连接对数据产生乱序 ------*如果输出的是大表*

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

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

表项数是ON后的对应条件的所有表项（无论是T1或T2 都要输出），如果相应位置没有匹配到，就NULL

eg

全外连接后
SELECT A.ID, A.Name, B.Age FROM A FULL OUTER JOIN B ON A.ID = B.ID;

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

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

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

   SELECT column1 [, column2 ]
   FROM table1 [, table2 ]
   [WHERE condition]

UNION

   SELECT column1 [, column2 ]
   FROM table1 [, table2 ]
   [WHERE condition]

UNION ALL 几乎一样，只是它会返回所有的表项
而UNION会将相同的表项合并

PostgreSQL NULL 值
===========================
当创建表时，NULL 的基本语法如下：

   CREATE TABLE COMPANY(
   ID INT PRIMARY KEY NOT NULL,
   NAME TEXT NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(50),
   SALARY REAL
   );

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

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

PostgreSQL 别名
====================
我们可以用 SQL 重命名一张表或者一个字段的名称，这个名称就叫着该表或该字段的别名。

创建别名是为了让表名或列名的可读性更强。

SQL 中使用 **AS** 来创建别名。

表的别名语法:

   SELECT column1, column2....
   FROM table_name AS alias_name
   WHERE [condition];

列的别名语法:

   SELECT column_name AS alias_name
   FROM table_name
   WHERE [condition];

SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;

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 ALTER 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;

PostgreSQL TRUNCATE TABLE
==========
PostgreSQL 中 TRUNCATE TABLE 用于删除表的数据，但不删除表结构。

也可以用 DROP TABLE 删除表，但是这个命令会连表的结构一起删除，如果想插入数据，需要重新建立这张表。

TRUNCATE TABLE table_name;

PostgreSQL View（视图）
=========================
View（视图）是一张假表
View（视图）可以从一个或多个表创建
视图创建可以从一张表，多张表或者其他视图。

   CREATE [TEMP | TEMPORARY] VIEW view_name AS
   SELECT column1, column2.....
   FROM table_name
   WHERE [condition];

多个表中选取可以用JOIN语句

删除

DROP VIEW view_name;

PostgreSQL TRANSACTION（事务）
======
## BEGIN TRANSACTION 命令

事务可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时，事务处理也会回滚。以下是启动一个事务的简单语法：

BEGIN;

或者

BEGIN TRANSACTION;

## COMMIT 命令

COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令，即确认事务。

COMMIT 命令的语法如下：

COMMIT;

或者

END TRANSACTION;

### ROLLBACK 命令

ROLLBACK 命令是用于撤消尚未保存到数据库的事务命令，即回滚事务。

ROLLBACK 命令的语法如下：

ROLLBACK;

   BEGIN;
   DELETE FROM COMPANY WHERE AGE = 25;
   COMMIT;

   BEGIN;
   DELETE FROM COMPANY WHERE AGE = 25;
   ROLLBACK;

PostgreSQL 子查询
=============
[PostgreSQL 子查询 | 菜鸟教程 (runoob.com)](https://www.runoob.com/postgresql/postgresql-sub-queries.html)
指的是在 PostgreSQL 查询中的 WHERE 子句中嵌入查询语句。

子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，并可使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。

以下是子查询必须遵循的几个规则：

- 子查询必须用括号括起来。

- 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。

- ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。

- 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。

- BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。

       SELECT column_name [, column_name ]
       FROM table1 [, table2 ]
       WHERE column_name OPERATOR
   (SELECT column_name [, column_name ]
   FROM table1 [, table2 ]
   [WHERE])

好多where后面跟的OPERATOR都是IN

PostgreSQL PRIVILEGES（权限）
=============================
在 PostgreSQL 中，权限分为以下几种：

- SELECT
- INSERT
- UPDATE
- DELETE
- TRUNCATE
- REFERENCES
- TRIGGER
- CREATE
- CONNECT
- TEMPORARY
- EXECUTE
- USAGE
要向用户分配权限，可以使用 GRANT 命令。

### GRANT 语法

GRANT 命令的基本语法如下：

   GRANT privilege [, ...]
   ON object [, ...]
   TO { PUBLIC | GROUP group | username }

- privilege − 值可以为：SELECT，INSERT，UPDATE，DELETE， RULE，ALL。
- object − 要授予访问权限的对象名称。可能的对象有： table， view，sequence。
- PUBLIC − 表示所有用户。
- GROUP group − 为用户组授予权限。
- username − 要授予权限的用户名。PUBLIC 是代表所有用户的简短形式。

我们可以使用 REVOKE 命令取消权限，REVOKE 语法：

   REVOKE privilege [, ...]
   ON object [, ...]
   FROM { PUBLIC | GROUP groupname | username }

创建一个用户eg.

CREATE USER runoob WITH PASSWORD 'password';
CREATE ROLE(反馈的)
信息 CREATE ROLE 表示创建了一个用户 "runoob"。

授予COMPANY表中所有对象的权限和所有操作给用户

GRANT ALL ON COMPANY TO runoob;

撤销

REVOKE ALL ON COMPANY FROM runoob;

删除用户

DROP USER runoob;

IN的使用
=============================

主要是用于where子句中
eg.

   SELECT
   customer_id,
rental_id,
return_date
   FROM
rental
   WHERE
   customer_id IN (10, 12)
   ORDER BY
return_date DESC;

   SELECT
first_name,
last_name
   FROM
customer
   WHERE
customer_id IN (
SELECT
customer_id
FROM
rental
WHERE
CAST (return_date AS DATE) = '2005-05-27'
);