PostgreSQL

说明

• PostgreSQL手册
• SQL关键字大小写不敏感，即大小写一样
• 字符类型要用单引号（‘AcTarjan’），日期类型要用#号（# 2021-11-08 #）
• SQL语句中值一律加单引号（数字、字符串、日期），列名（无修饰或加双引号）
• SQL语句执行顺序：

登录

su - postgres			#切换到到pg数据库所在用户
psql										#本地登录postgres用户
psql -U <用户名>								#本地登录
psql -U <用户名> -h <主机地址> -p <端口号>		#远程登录

用户

CREATE USER <用户名> WITH PASSWORD 'your password';		#创建用户，需要先登录到超级用户postgres
DROP USER [IF EXISTS] <用户名>;							#删除用户

数据库

CREATE DATABASE [IF NOT EXISTS] <数据库名>;		#创建数据库
DROP DATABASE [IF EXISTS] <数据库名>;			#删除数据库
\l												#查看所有数据库
\c <数据库名>									#选择数据库
\dg												#查看数据库的用户
\dt												#查看数据库内的所有表
\q												#退出数据库

表

\d <表名>								#查看表的结构
CREATE TABLE [IF NOT EXISTS] <表名> (
	id INTEGER PRIMARY KEY,							//主键（即非空唯一）
	name VARCHAR(5) NOT NULL UNIQUE,				//非空唯一
	age INTEGER CHECK (age > 0 AND age < 150),		//检查约束
	math_score NUMERIC DEFAULT 0.0,					//默认值
	total_score NUMERIC，
	CHECK (total_score >= math_score)				//表约束
);
DROP TABLE [IF EXISTS] <表名>;

增删改查

/********************插入*******************/
INSERT INTO <表名> VALUES('AcTarjan',22);				//必须知道表的结构
INSERT INTO <表名>(name,age) VALUES('AcTarjan',22);	
INSERT INTO <表名>(name,age) VALUES('AcTarjan',22),('Bob',DEFAULT);

/********************不存在则插入,存在则更新*******************/
INSERT INTO <表名>(name,age) VALUES($1,$2)
ON CONFLICT(name) DO UPDATE SET age = $2;		#name必须唯一	

/********************删除*******************/
DELETE FROM <表名> WHERE name = 'AcTarjan';
DELETE FROM <表名>;			#表内的所有行将会被删除

/********************修改*******************/
UPDATE <表名> SET age = age + 1 WHERE name = 'AcTarjan';


/********************查询*******************/
SELECT * FROM <表名> WHERE id = 1;
SELECT COALESCE(age,-1) AS age FROM <表名> WHERE id = 1;			#如果age为空则查询结果为-1
SELECT score FROM <表名> ORDER BY <列名> [ASC|DESC];				#asc升序，desc降序
SELECT * FROM <表名> LIMIT 4 OFFSET 2;							#从第3行开始选取不超过4行
SELECT class FROM <表名> GROUP BY class;							#按班级分组
SELECT class,SUM(score) FROM <表名> GROUP BY class HAVING SUM(score) > 1000;	#班级总分数超过1000的班级

函数

• COALESCE(args)：返回参数中从前到后第一个不为空的值
• COUNT(列名)：返回该列的行数，NULL不计入
• SUM(列名)：求和