当前位置：网站首页>PostgreSQL相关语法及指令示例

PostgreSQL相关语法及指令示例

2022-08-10 02:17:00 【N阶魔方】

一、增删改查等常用关键字

功能指令示例
CREATE DATABASE
创建数据库		CREATE DATABASECREATE DATABASE dbname;
DROP DATABASE
删除数据库		DROP DATABASE
DROP DATABASE [ IF EXISTS ] name		DROP DATABASE runoobdb;
CREATE TABLE
创建表格		CREATE TABLECREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
DROP TABLE
删除表格		DROP TABLEdrop table department，company;
SCHEMA
模式、相当于不同文件夹		CREATE SCHEMA create schema 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)
);
DROP SCHEMA myschema;
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');
UPDATE
更新表新记录		UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];		UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
删除表新记录DELETE FROM table_name WHERE [condition];DELETE FROM COMPANY WHERE ID =2;
SELECT 语句SELECT column1, column2,...columnN FROM table_name;SELECT ID,NAME FROM company;
 LIKE
模糊匹配		百分号 %   //模糊多个
下划线 _    //模糊单个
SELECT FROM table_name WHERE column LIKE 'XXXX%';		SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';
LIMIT
有限读取		SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]		// 有限读取头4条
ELECT * FROM COMPANY LIMIT 4;

// 从第6位开始提取 3 个记录
SELECT * FROM COMPANY LIMIT 3 OFFSET 5;
ORDER BY
排序		升序（ASC）
降序（DESC）		SELECT * FROM COMPANY ORDER BY AGE ASC;
GROUP BY
相同的数据进行分组		SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件		// 根据 NAME 字段值进行分组，找出每个人的工资总额
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
DISTINCT
去除重复记录		SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]		SELECT DISTINCT name FROM COMPANY;

二、运算符相关

1、算术运算符

运算符描述实例
+加a + b 结果为 5
-减a - b 结果为 -1
*乘a * b 结果为 6
/除b / a 结果为 1
%模（取余）b % a 结果为 1
^指数a ^ b 结果为 8
|/平方根|/ 25.0 结果为 5
||/立方根||/ 27.0 结果为 3
!阶乘5 ! 结果为 120
!!阶乘（前缀操作符）!! 5 结果为 120

2、比较运算符

运算符描述实例
=等于(a = b) 为 false。
!=不等于(a != b) 为 true。
<>不等于(a <> b) 为 true。
>大于(a > b) 为 false。
<小于(a < b) 为 true。
>=大于等于(a >= b) 为 false。
<=小于等于(a <= b) 为 true。

3、逻辑运算符号

运算符描述备注
AND逻辑与运算符。如果两个操作数都非零，则条件为真。PostgresSQL 中的 WHERE 语句可以用 AND 包含多个过滤条件。
NOT逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。PostgresSQL 有 NOT EXISTS, NOT BETWEEN, NOT IN 等运算符。
OR逻辑或运算符。如果两个操作数中有任意一个非零，则条件为真。PostgresSQL 中的 WHERE 语句可以用 OR 包含多个过滤条件。

三、PostgreSQL 高级

关键字说明　
约束NOT NULL：指示某列不能存储 NULL 值。
UNIQUE：确保某列的值都是唯一的。
PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN Key：保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK：保证列中的值符合指定的条件。
EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。		CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00,
   EMP_ID         INT      references COMPANY6(ID)
);
JOIN
连接		CROSS JOIN ：交叉连接
INNER JOIN：内连接
LEFT OUTER JOIN：左外连接
RIGHT OUTER JOIN：右外连接
FULL OUTER JOIN：全外连接		SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
UNION
联合		SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION [ALL]

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]		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;
NULL
未知数据		IS NULL 和 IS NOT NULL SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;
 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;
TRIGGER
是数据库的回调函数		PostgreSQL 触发器可以在下面几种情况下触发：

在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
在执行操作之后（在检查约束并插入、更新或删除完成之后）。
更新操作（在对一个视图进行插入、更新、删除时）。
触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 FOR EACH STATEMENT，不管修改了多少行，每个语句标记的触发器执行一次。

WHEN 子句和触发器操作在引用 NEW.column-name 和 OLD.column-name 表单插入、删除或更新时可以访问每一行元素。其中 column-name 是与触发器关联的表中的列的名称。

如果存在 WHEN 子句，PostgreSQL 语句只会执行 WHEN 子句成立的那一行，如果没有 WHEN 子句，PostgreSQL 语句会在每一行执行。

BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。

要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是 database.tablename。

当创建约束触发器时会指定约束选项。这与常规触发器相同，只是可以使用这种约束来调整触发器触发的时间。当约束触发器实现的约束被违反时，它将抛出异常。		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;
INDEX
索引		索引是加速搜索引擎检索数据的一种特殊表查询

CREATE UNIQUE INDEX index_name
on table_name (column_name);		在 COMPANY 表的 SALARY 列上创建索引CREATE INDEX salary_index ON COMPANY (salary);
ALTER TABLE
表控制		添加，修改，删除一张已经存在表的列
添加和删除约束		ALTER TABLE table_name ALTER column_name datatype NOT NULL;
子查询SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])
//-------------------------------------
INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]		SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY  WHERE SALARY > 45000) ;

INSERT INTO COMPANY_BKP SELECT * FROM COMPANY  WHERE ID IN (SELECT ID FROM COMPANY) ;
 

四、PostgreSQL 函数

关键字说明
常用函数COUNT 函数：用于计算数据库表中的行数。
MAX 函数：用于查询某一特定列中最大值。
MIN 函数：用于查询某一特定列中最小值。
AVG 函数：用于计算某一特定列中平均值。
SUM 函数：用于计算数字列所有值的总和。
ARRAY 函数：用于输入值(包括null)添加到数组中。
Numeric 函数：完整列出一个 SQL 中所需的操作数的函数。
String 函数：完整列出一个 SQL 中所需的操作字符的函数。
数学函数abs(x)  绝对值
三角函数列表
sin(x) ~ cos(x)
….

字符串函数和操作符		string 丨丨 string text 字串连接
lower(string) text 把字串转化为小写
substring(string from pattern) text 抽取匹配 POSIX 正则表达式的子字串

很多，自行查询使用
类型转换相关函数to_char(timestamp, text) text 将时间戳转换为字符串 to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text) text 将时间间隔转换为字符串 to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text) text 整型转换为字符串 to_char(125, '999')
to_char(double precision, text) text 双精度转换为字符串 to_char(125.8::real, '999D9')
to_char(numeric, text) text 数字转换为字符串 to_char(-125.8, '999D99S')
to_date(text, text) date 字符串转换为日期 to_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text) numeric 转换字符串为数字 to_number('12,454.8-', '99G999D9S')
to_timestamp(text, text) timestamp 转换为指定的时间格式 time zone convert string to time stamp to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double precision) timestamp 把UNIX纪元转换成时间戳 to_timestamp(1284352323)
日期/时间函数clock_timestamp()  实时时钟的当前时间戳
current_date  当前的日期；
current_time  当日时间；
…..

很多，自行查询使用

五、程序实例

//  AGE(年龄) 字段大于等于 25，并且 SALARY(薪资) 字段大于等于 65000 的数据
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;


//AGE(年龄) 字段大于等于 25，或者 SALARY(薪资) 字段大于等于 65000 的数据：
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;


//IS NULL 用来查找为 NULL 值的字段。
//下面是 IS NULL 操作符的用法，列出 SALARY（薪资） 值为空的记录
SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL;


// 分别用 C 和 D 表示 COMPANY 表和 DEPAERMENT 表的别名
SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE  C.ID = D.EMP_ID;

//用 COMPANY_ID 表示 ID 列，COMPANY_NAME 表示 NAME 列，来展示列别名的用法
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;

// pg_trigger 表中把当前数据库所有触发器列举出来
SELECT * FROM pg_trigger;

// 在 COMPANY 表上创建一个触发器
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;

// 在 COMPANY 表的 SALARY 列上创建索引
CREATE INDEX salary_index ON COMPANY (salary);


//把 COMPANY 表中所有 AGE 大于 27 的客户的 SALARY 更新为原来的 0.50 倍
UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );

原网站

版权声明
本文为[N阶魔方]所创，转载请带上原文链接，感谢
https://blog.csdn.net/zhouqt/article/details/126214647

边栏推荐

猜你喜欢

随机推荐