PostgreSQL cmd 汇总

最新推荐文章于 2024-07-01 20:55:39 发布

风吹裙起ppl

最新推荐文章于 2024-07-01 20:55:39 发布

阅读量1k

点赞数

文章标签： postgresql

本文链接：https://blog.csdn.net/NicoleZhang2012/article/details/109040195

版权

本文详细介绍了PostgreSQL数据库的基本操作，包括创建、查询、更新和删除数据，使用索引、视图、触发器和事务，以及涉及的约束和锁机制。内容涵盖命令行工具的使用、数据类型、表的操作、查询语法、事务管理和数据库维护等方面。

摘要由CSDN通过智能技术生成

说明

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。号称"世界上最先进的开源关系型数据库"
与其他NoSQL 数据库对比， PostgreSQL 数据类型更丰富，约束、触发器、事务等能解决很多问题。

命令

$ sudo -i -u postgres  // Linux 系统可以直接切换到 postgres 用户来开启命令行工具
$ \help  // 来查看各个命令的语法
$ CREATE DATABASE dbname;  //  创建数据库
$ createdb -h localhost -p 5432 -U postgres runoobdb  // 创建数据库 使用了超级用户 postgres 登录到主机地址为 localhost，端口号为 5432 的 PostgreSQL 数据库中并创建 runoobdb 数据库
$ \l  // 用于查看已经存在的数据库
$ \c + 数据库名  // 来进入数据库
$ DROP DATABASE runoobdb  // 删除数据库
$ dropdb -h localhost -p 5432 -U postgres runoobdb  // 删除数据库
$ CREATE TABLE COMPANY(  // 创建了一个表，表名为 COMPANY 表格，主键为 ID，NOT NULL 表示字段不允许包含 NULL 值
	   ID INT PRIMARY KEY     NOT NULL,
	   NAME           TEXT    NOT NULL,
	   AGE            INT     NOT NULL,
	   ADDRESS        CHAR(50),
	   SALARY         REAL
	);
$ \d  // 命令来查看表格是否创建成功
$ \d tablename  // 查看表格信息
$ DROP TABLE department, company;  // 删除department, company 两个表格
$ CREATE SCHEMA myschema;  // 创建一个名为myschema 的模式
$ create table myschema.company(   // 依赖模式创建表格
	   ID   INT              NOT NULL,
	   NAME VARCHAR (20)     NOT NULL,
	   AGE  INT              NOT NULL,
	   ADDRESS  CHAR (25),
	   SALARY   DECIMAL (18, 2),
	   PRIMARY KEY (ID)
	);
$ select * from myschema.company;  // 查看表格是否建成功 
$ DROP SCHEMA myschema;  // 删除一个为空的模式（其中的所有对象已经被删除）
$ DROP SCHEMA myschema CASCADE;  // 删除一个模式以及其中包含的所有对象

$ INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT 0 1  // 字段列必须和数据值数量相同，且顺序也要对应。如果我们向表中的所有字段插入值，则可以不需要指定字段，只需要指定插入的值即可
$ 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');  // 插入多行
$ SELECT * FROM company;   // 查询插入情况

$ SELECT * FROM COMPANY WHERE SALARY > 50000;  // SALARY 字段大于 50000 的数据
$ SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500  // 读取 AGE 字段大于等于 25 且 SALARY 字段大于等于 6500 的数据
$ SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;  // 读取 SALARY 字段不为 NULL 的数据
$ SELECT COUNT(*) AS "RECORDS" FROM COMPANY  // 查询表格COMPANY 中 RECORDS 的数量总和
$ SELECT CURRENT_TIMESTAMP;  // 日期表达式返回当前系统的日期和时间 
$ SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%'  // 在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据
$ SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );  // 列出了 AGE(年龄) 字段为 25 或 27 的数据
$ SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 )  // 列出了 AGE(年龄) 字段在 25 到 27 的数据
$ SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);  // 使用了 SQL 的子查询，子查询语句中读取 SALARY(薪资) 字段大于 65000 的数据，然后通过 EXISTS 运算符判断它是否返回行，如果有返回行则读取所有的 AGE(年龄) 字段。
$ SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);  // 读取 SALARY(薪资) 字段大于 65000 的 AGE(年龄) 字段数据，然后用 > 运算符查询大于该 AGE(年龄) 字段数据

$ UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3  // 更新 COMPANY 表中 id 为 3 的 salary 字段值
$ UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;  // 同时更新 salary 字段和 address 字段(所有的值都改变)

$ DELETE * FROM COMPANY WHERE ID = 2;  // 删除 ID 为 2 的数据
$ DELETE * FROM COMPANY;  // 删除整张 COMPANY 表

$ SELECT * FROM COMPANY WHERE SALARY::text LIKE '200%'  // 找出 SALARY 字段中以 200 开头的数据。
$ SELECT * FROM COMPANY WHERE SALARY::text LIKE '%2'  // 找出 SALARY 字段中以 2 结尾的数据
$ SELECT * FROM COMPANY WHERE SALARY::text LIKE '%200%'  // 找出 SALARY 字段中含有 200 字符的数据。
$ SELECT * FROM COMPANY WHERE SALARY::text LIKE '_00%'  // 找出 SALARY 字段中在第二和第三个位置上有 00 的数据。
$ SELECT * FROM COMPANY WHERE SALARY::text LIKE '2___3'  // 找出 SALARY 字段中以 2 开头，3 结尾并且是 5 位数的数据
$ SELECT * FROM COMPANY WHERE SALARY::text LIKE '2 % %'  // 找出 SALARY 字段中以 2 开头的字符长度大于 3 的数据。
$ SELECT * FROM COMPANY WHERE SALARY::text LIKE '_2%3'  // 找出 SALARY 字段中 2 在第二个位置上并且以 3 结尾的数据
$ SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';  // 找出 address 字段中含有 - 字符的数据

$ SELECT * FROM COMPANY LIMIT 4;  // 读取 4 条数据
$ SELECT * FROM COMPANY LIMIT 3 OFFSET 2;  // 第三位开始提取 3 个记录

$ SELECT * FROM COMPANY ORDER BY AGE ASC  // 对结果根据 AGE 字段值进行升序排列
$ SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC  // 根据 NAME 字段值和 SALARY 字段值进行升序排序
$ SELECT * FROM COMPANY ORDER BY NAME DESC;  //  根据NAME字段值进行降序排列

$ SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME  // 根据 NAME 字段值进行分组，找出每个人的工资总额,并根据名字排序
$ 不求SUM的话，这个结果是什么？

$ With CTE AS  // COMPANY表中查询数据
	(Select
	 ID
	, NAME
	, AGE
	, ADDRESS
	, SALARY
	FROM COMPANY )
  Select * From CTE;
$ WITH RECURSIVE t(n) AS (  // 查找 SALARY(工资) 字段小于 20000 的数据并计算它们的和
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
	)
  SELECT sum(n) FROM t;
$ WITH moved_rows AS (  // 使用 DELETE 语句和 WITH 子句删除 COMPANY 表中 SALARY(工资) 字段大于等于 30000 的数据，并将删除的数据插入 COMPANY1 表，实现将 COMPANY 表数据转移到 COMPANY1 表中
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
	)
  INSERT INTO COMPANY1 (SELECT * FROM moved_rows);
$ SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;  // 根据 name 字段值进行分组，并且名称的计数大于 1 数据
$ SELECT DISTINCT name FROM COMPANY  // DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录

$ CREATE TABLE DEPARTMENT1(   // FOREIGN KEY
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)
  );
$ CREATE TABLE COMPANY5(  // CHECK KEY
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
  );
$ CREATE TABLE COMPANY7(  // EXCLUDE 
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,  -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
   AGE WITH <>)   -- 其比较的结果是如果整个表边式返回 true，则不允许插入，否则允许
  );
$ ALTER TABLE table_name DROP CONSTRAINT some_name;  // 删除约束

$ SELECT EMP_ID, NAME FROM COMPANY CROSS JOIN DEPARTMENT;  // 交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。量大慎用。
$ SELECT EMP_ID, NAME FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;  // 连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。
$ SELECT EMP_ID, NAME FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;  // 左外连接 COMPANY 中的行都存在
$ SELECT EMP_ID, NAME FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;  // 右外连接 DEPARTMENT 中行都存在
$ SELECT EMP_ID, NAME FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;  // 外连接 COMPANY DEPARTMENT 都存在
$ SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
  SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN        	DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;  // 连接两个SELECT 结果， 对于保留重的可用 UNION ALL
  
$ SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE  C.ID = D.EMP_ID;  // 用别名代替表名
$ SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT  FROM COMPANY AS C, DEPARTMENT AS D WHERE  C.ID = D.EMP_ID;  // 用别名代替列名

$ CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
$ 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;
$ SELECT * FROM pg_trigger;   //  列出所有触发器
$ SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';   // 列出特定表的触发器
$ DROP trigger example_trigger ON company;  // drop trigger example_trigger on company;

$ CREATE INDEX index_name
ON table_name (column1_name, column2_name);  //  创建索引
$ \d company  //  列出company 下所有索引
$ \di 命令列出数据库中所有索引  // 列出所有索引
$  DROP INDEX salary_index;  // 删除索引

$ alter table ${table_name} alter column ${column_name} set not null;  // 给表中某列添加 NOT NULL 约束
$ alter table ${table_name} alter column ${column_name} drop not null;  // 删除表中某列的 NOT NULL 约束
$ ALTER TABLE table_name ADD column_name datatype;  // 已存在的表上添加列
$ ALTER TABLE table_name DROP COLUMN column_name;  // 已存在的表上 DROP COLUMN（删除列）
$ ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype; // 修改表中某列的 DATA TYPE（数据类型）
$ ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);  // 给表中某列 ADD UNIQUE CONSTRAINT（ 添加 UNIQUE 约束）

$ TRUNCATE TABLE COMPANY;  // 清空表的内容但不清表的结构

$ CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;  //  创建视图
$ SELECT * FROM COMPANY_VIEW  // 查询视图
$ DROP VIEW COMPANY_VIEW;  // 删除视图

$ BEGIN;
  DELETE FROM COMPANY WHERE AGE = 25;
  ROLLBACK;  // 从表中删除 age = 25 的记录，最后，我们使用 ROLLBACK 命令撤消所有的更改
$ BEGIN;
  DELETE FROM COMPANY WHERE AGE = 25;
  COMMIT;  // 从表中删除 age = 25 的记录，最后我们使用 COMMIT 命令提交所有的更改
$
$

tips:

在PostgreSQL 中，LIKE子句是只能用于对字符进行比较，因此在上面列子中，我们要将整型数据类型转化为字符串数据类型
GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY子句之前。
在 GROUP BY子句中，你可以对一列或者多列进行分组，但是被分组的列必须存在于列清单中
WITH 子句是在多次执行子查询时特别有用，有助于将复杂的大型查询分解为更简单的表单，便于阅读。允许我们在查询中通过它的名称(可能是多次)引用它。（在使用前必须先定义）
公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
-HAVING 子句可以让我们筛选分组后的各组数据。WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

语句顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

约束 : PostgreSQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止，确保了数据库中数据的准确性和可靠性。约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。约束可以是列级或表级。
UNION ：操作符用于合并两个或多个 SELECT 语句的结果集
触发器: PostgreSQL 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。
索引注意事项：
- 索引不应该使用在较小的表上。
- 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
- 索引不应该使用在含有大量的 NULL 值的列上。
- 索引不应该使用在频繁操作的列上。
PostgreSQL 中 TRUNCATE TABLE 用于删除表的数据，但不删除表结构。DROP TABLE 删除表，连表的结构一起删除。 TRUNCATE TABLE 与 DELETE 具有相同的效果，但是由于它实际上并不扫描表，所以速度更快。此外，TRUNCATE TABLE 可以立即释放表空间，而不需要后续 VACUUM 操作，这在大型表上非常有用。
VACUUM: PostgreSQL VACUUM 操作用于释放、再利用更新/删除行所占据的磁盘空间
视图 只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句
事务 TRANSACTION（事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。数据库事务通常包含了一个序列的对数据库的读/写操作。支持事务的数据库管理系统就是要确保以上两个操作（整个"事务"）都能完成，或一起取消，否则就会出现 100 元平白消失或出现的情况。事务可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时，事务处理也会回滚。
锁：锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。在多个用户访问数据库的时候若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。数据库中有两种基本的锁：排它锁（Exclusive Locks）和共享锁（Share Locks）。如果数据对象加上排它锁，则其他的事务不能对它读取和修改。如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。一旦获得了锁，锁将在当前事务的其余时间保持。没有解锁表命令；锁总是在事务结束时释放。