postgreSQL语法

查询基础

1、连接数据库
C:\PostgreSQL\9.5\bin\psql.exe -U postgres -d shop
2、检查数据库连接是否成功

SELECT 1;

3、创建数据库

CREATE DATABASE shop;

4、退出数据库口令

\q

5、\d 数据库 ——得到所有表的名字

6、\d 表名 —— 得到表结构

7、创建表

CREATE TABLE <表名>

（<列名1> <数据类型> <该列所需约束>，

<列名2> <数据类型> <该列所需约束>，

<列名3> <数据类型> <该列所需约束>，

<列名4> <数据类型> <该列所需约束>，

…

<该表的约束1>， <该表的约束2>，……）；

8、删除表

DROP TABLE <表名>；

9、修改表-添加列

ALTER TABLE <表名> ADD COLUMN <列的定义>；

注：Oracle和SQL Server中不用写COLUMN： ALTER TABLE <表名> ADD <列名> ；oracle还可以这样：ALTER TABLE <表名> ADD （<列名>，<列名>，<列名>... ）；

10、修改表-删除列

ALTER TABLE <表名> DROP COLUMN <列名>;

11、插入数据

BEGIN TRANSACTION;

INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');

COMMIT;

注：在MySQL中运行时，BEGIN TRANSACTION改写成 START TRANSACTION;在Oracle和DB2中运行时，无需使用BEGIN TRANSACTION;

12、变更表名

ALTER TABLE Product RENAME TO Product;

13、查询语句

(1) 查询全部列

  Select  *  from  <表名>;

(2) 查询指定列

SELECT <列名>，<列名>，…… FROM <表名>；

注：查询多列时，需要使用逗号进行分隔。查询结果中列的顺序和

SELECT 子句中的顺序相同
14、用AS为列设置别名

SELECT  product_id as id,
product_name as name,
purchase_price as price
FROM  product;

注：别名可用中文，但要用“”。

15、SELECT子句可以用常数

SELECT '商品' as string,'2019-12-12' as date,
product_id,product_name 
from product;

注：此处用单引号‘’。

16、查询结果去重：distinct

select distinct product_type from product;

注：用distinct时null类型数据也会被作为一类数据；distinct也可以同时合并多列

17、WHERE子句添加查询筛选条件

select product_name,product_type from product where product_type='衣服';

18、注释的书写方式
1行注释：写在–后面

多行注释：写在/* 和 */之间

--一行注释
/*
多行注释
*/

19、使用（+、-、*、/）运算符

select product_name,sale_price,sale_price * 2 as “sale_price_x2” from product;

20、使用比较运算符

(1) 含义
(2) 字符串类型的数据原则上按字典的顺序排序，不能与数字的大小顺序混淆，如’1-3‘<’2’

(3) 对null数据不能用比较运算符

用特定语句：is null /is not null

21、逻辑运算符

(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()：计算表中的记录数（行数）
2、SUM()： 计算表中数值列中数据的合计值
3、AVG()： 计算表中数值列中数据的平均值
4、MAX()： 求出表中任意列中数据的最大值
5、MIN()： 求出表中任意列中数据的最小值
6、使用聚合函数删除重复值
7、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:排序
9、STRING_AGG(str,‘,’)
配合group by 实现把同一id的一列数据合并成一个字段.

select id,STRING_AGG(name,',')  
from test
group by id

数据更新

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．IFNULL：在PostgreSQL中，IFNULL函数不存在，可以使用 COALESCE 函数来达到类似的效果，它会返回参数列表中的第一个非 NULL 值

SELECT COALESCE(column_name, 'default_value') FROM table_name;

23．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