postgreSQL最全语法整理（可作为工具查询使用）
一、查询基础
1、连接数据库
C:\PostgreSQL\9.5\bin\psql.exe -U postgres -d shop
2、检查数据库连接是否成功
SELECT 1;
3、创建数据库
CREATE DATABASE shop;
4、退出数据库口令
\q
5、创建表
CREATE TABLE <表名>
（<列名1> <数据类型> <该列所需约束>，
<列名2> <数据类型> <该列所需约束>，
<列名3> <数据类型> <该列所需约束>，
<列名4> <数据类型> <该列所需约束>，
…
<该表的约束1>， <该表的约束2>，……）；
6、删除表
DROP TABLE <表名>；
7、修改表-添加列
ALTER TABLE <表名> ADD COLUMN <列的定义>；
注：Oracle和SQL Server中不用写COLUMN： ALTER TABLE <表名> ADD <列名> ；
oracle还可以这样：ALTER TABLE <表名> ADD （<列名>，<列名>，<列名>… ）；
8、修改表-删除列
ALTER TABLE <表名> DROP COLUMN <列名>;
9、插入数据
BEGIN TRANSACTION;
INSERT INTO Product VALUES (‘0001’, ‘T恤衫’, ‘衣服’, 1000, 500, ‘2009-09-20’);
COMMIT;
注：在MySQL中运行时，BEGIN TRANSACTION改写成 START TRANSACTION;在Oracle和DB2中运行时，无需使用BEGIN TRANSACTION;
10、变更表名
ALTER TABLE Product RENAME TO Product;
11、查询语句
(1) 查询全部列
Select * from <表名>;
星号（*）代表全部列的意思。
(2) 查询指定列
SELECT <列名>，<列名>，…… FROM <表名>；
注：查询多列时，需要使用逗号进行分隔。查询结果中列的顺序和
SELECT 子句中的顺序相同
12、用AS为列设置别名
例：SELECT product_id as id,
product_name as name,
purchase_price as price
FROM product;
别名可用中文，但要用“”。
13、SELECT子句可以用常数
例：SELECT ‘商品’ as string,’2019-12-12′ as date,product_id,product_name from product;
注：此处用单引号‘’。
14、查询结果去重：distinct
例：select distinct product_type from product;
注：用distinct时null类型数据也会被作为一类数据；distinct也可以同时合并多列
15、WHERE子句添加查询筛选条件
例：select product_name,product_type from product where product_type=’衣服’;
16、注释的书写方式
1行注释：写在–后面
多行注释：写在/* 和 */之间
17、使用（+、-、*、/）运算符
例：select product_name,sale_price,sale_price * 2 as “sale_price_x2” from product;
18、使用比较运算符
(1) 含义
(2) 字符串类型的数据原则上按字典的顺序排序，不能与数字的大小顺序混淆，如’1-3‘<’2’
(3) 对null数据不能用比较运算符
用特定语句：is null /is not null
19、逻辑运算符
(1) NOT运算符：用来否定某一条件
例：select product_name ,sale_price from product where not sale_price >= 1000;
(2) AND运算符
其两侧条件需同步成立，相当于”并且“
例：select product_name,purchase_price from product where product_type = ‘厨 房用具‘ and sale_price >=3000;
(3) OR运算符
其两侧条件只需一个成立，相当于”或者“
例：select product_name,purchase_price from product where product_type = ‘厨房用具’ or sale_price >=3000;
(4) 运用括号控制运算优先级
例：select product_name,product_type,regist_date from product where product_type = ‘办公用品’ and (regist_date = ‘2009-09-11’ or regist_date = ‘2009-09-20’);
注：AND运算符优先于OR运算符，想要先执行OR语句可以用括号
二、聚合函数：多行汇集成一行
1、COUNT：计算表中的记录数（行数）
例：select count(*) from product;
计算某列非空行：SELECT COUNT(purchase_price) FROM Product;
2、SUM： 计算表中数值列中数据的合计值
例：select sum(sale_price) from product
3、AVG： 计算表中数值列中数据的平均值
例：select avg(sale_price) from product;
4、MAX： 求出表中任意列中数据的最大值
MIN： 求出表中任意列中数据的最小值
例：SELECT MAX(sale_price), MIN(purchase_price) FROM Product;
5、使用聚合函数删除重复值
6、Group by子句
(1) 只能存在 常数、聚合函数group by子句中指定的列名
(2) group by子句中不能用别名
(3) Where 子句中不能用聚合函数
例：select product_type,count(*) from product group by product_type;
7、Haveing 子句 ：取符合条件的组
8、Order by:排序
三、数据更新
1、Insert
2、Delete from 【表名】
3、Update
(1) 多列更新
① — 使用逗号对列进行分隔排列
UPDATE Product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = ‘厨房用具’;
② — 将列用()括起来的清单形式
UPDATE Product
SET (sale_price, purchase_price) = (sale_price * 10,
purchase_price / 2)
WHERE product_type = ‘厨房用具’;
4、事务是需要在同一个处理单元中执行的一系列更新处理的集合。
(1) 语法：
① 事务开始语句
② DML语句1，DML语句2，……
③ 事务结束语句（commit或rollback）
(2) 开启事务语句：
① （SQLserver、postgreSQL）Begin transaction
② (MySQL) start transaction
(3) 提交事务–commit
(4) 取消处理–rollback
5、ACID特性：所有的DBMS都遵循四种特性
(1) 原子性atomicity
(2) 一致性consistency
(3) 隔离性isolation
(4) 持久性durability
四、复杂查询
1．创建视图 view:保存好的select语句
（1）语法：
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>
（2）法则：尽量避免在视图的基础上创建视图，会降低sql效率
（3）法则：定义视图时不能使用order by 语句
（4）法则：部分情况可以对视图进行更新，如既没有聚合也没有结合的select语句
①视图设定为只读，因此需要on insert do instead规则
例：CREATE OR REPLACE RULE insert_rule
AS ON INSERT
TO ProductJim DO INSTEAD
INSERT INTO Product VALUES (
new.product_id,
new.product_name,
new.product_type,
new.sale_price,
new.purchase_price,
new.regist_date);
2．删除视图：drop view 【表名】
3．删除关联视图：DROP VIEW ProductSum CASCADE;
4．关联子查询：在子查询中添加条件
五、函数
1．ABS绝对值：ABS(数值)
2．Mod求余：mod(被除数，除数）
3．Round四舍五入：round(对象数值，保留小数位数）
4．|| 拼接：字符串1||字符串2
5．Length字符串长度：length(字符串)
6．lower小写转换：lower(字符串)
7．upper大写转换：upper(字符串)
8．replace字符串的替换：replace（对象字符串，替换前字符串，替换后字符串）
9．Substring(对象字符串 from 截取的起始位置 for 截取的字符数)
10．Current_date当前日期
11．Current_time当前时间
12．Current_timestamp当前日期和时间
13．Extract(日期元素 from 日期)
14．cast类型转换：cast(转换前的值 as 想要转换的数据类型
15．Coalesce讲null值转换为其他值：coalesce(数值1，数值2，数值3……)
16．like字符串部分一致查询(%代表“0字符以上任意字符串”)
17．Like+指定字符串查询：
select * from samplelike where strcol like ‘abc__’;
查询”abc+任意三个字符”的字符串
18．Between范围查询
19．Is null ,is not null判断是否为null
20．in谓语–or 的简便用法
21．Exists
22．case表达式
语法：case when <求值表达式> then <表达式>
When <求值表达式> then <表达式>
When <求值表达式> then <表达式>
…
Else <>
End
六、集合运算
1．Union表的加法
2．Union all包含重复行
3．Except记录的减法（Oracle中使用minus
4．Inner join内联结
5．Outer join外联结
6．Cross join 交叉联结
七、SQL高级处理
1．窗口函数
（1）语法：<窗口函数> OVER ([PARTITION BY <列清单>]
ORDER BY <排序用列清单>)
PARTITION BY：能够设定排序的对象范围
ORDER BY：能够指定按照哪一列、何种顺序排序
（2）Rank函数：排序时，如存在相同位次的记录会跳过之后的位次（1，1，1，4）
（3）Dense_rank函数：排序时，如果存在相同位次，不会跳过（1，1，2，3）
（4）Row_number函数：赋予唯一连续位次
2．聚合函数作为窗口函数
（1）Sum函数：此处与纯聚合函数不一样，此处为累计
（2）Avg函数：计算移动平均
①Rows 2 preseding:(移动平均：截止到之前2行)
②Rows 2 following :(移动平均：截止到之后2行)
3．Grouping运算符
（1）Rollup同时得出合算和小计
（2）Grouping让null更加容易分辨
（3）Cube用数据搭积木
（4）Grouping sets取得期望的积木
（5） 可以从rollup或者cube的结果中取出部分记录
八、应用程序连接数据库
驱动:可以从网上下载（PostgreSQL JDBC Driver :https://jdbc.postgresql.org/download.html)
（1）ODBC:open database connectivity
（2）JDBC:java database connectivity
（3）C:\PostgreSQL\java\jdk\bin\java -cp D:\soft\PostgreSQL\jdbc\*;. DBConnect1