SQL语法

1 聚合与排序

1.1 对表进行聚合查询

聚合函数

• count:计算表中的记录数（行数）

shop=# selct count(*) from product; count不会统计null的行数

• sum：计算表中数值列中数据的合计值

shop=# select sum(sale_price), SUM(purchase_price) from product; sum不会加null的值

• Average：计算表中数值列中数据的平均值

shop=# select avg(sale_price), SUM(purchase_price) from product; avg不会统计null的行数

• MAX：求出表中任意列中数据的最大值
• MIN ：求出表中任意列中数据的最小值
• 使用聚合函数删除重复值（关键字 Distinct）

shop=# select count(distinct product_type) from product;先去重，再统计

---

1.2 对表进行分组

GROUP BY

shop=# SELECT PRODUCT_TYPE, COUNT(*) FROM PRODUCT GROUP BY PRODUCT_TYPE; 

PRODUCT_TYPE 为分组列

注意：

1. 在select子句中书写了多余的列；
2. 在group by子句中使用别名；
3. group by 子句的结果能排序吗？
   答：它是随机的，并不知道是按照什么样的规则进行排序。
4. 聚合函数不允许出现在where子句中；
5. 只有select子句和having子句可以使用聚合函数。

---

1.3 为聚合结果指定条件

Having子句

书写顺序为：select—from—where—group by—having
HAVING子句 = 指定组所对应的条件

shop=# SELECT PRODUCT_TYPE, COUNT(*) FROM PRODUCT GROUP BY PRODUCT_TYPE HAVING COUNT(*) = 2; 

shop=# SELECT product_type, AVG(SALE_PRICE) FROM PRODUCT GROUP BY product_type having avg(sale_price)>=2500;

将条件书写在where子句中

shop=# SELECT PRODUCT_TYPE, COUNT(*) FROM PRODUCT where COUNT(*)=2 GROUP BY PRODUCT_TYPE;

1.4 对查询结果进行排序

• ORDER BY子句

书写顺序:select——from——where——group by——having——order by

没有指定排列顺序的随机排序

shop=# SELECT product_id, product_name,sale_price FROM PRODUCT;

• 指定升序或降序

默认为升序

shop=# select product_id, product_name, sale_price, purchase_price from product order by sale_price;

shop=# select product_id, product_name, sale_price, purchase_price from product order by sale_price ASC;

降序

shop=# select product_id, product_name, sale_price, purchase_price from product order by sale_price DESC;

• 指定多个排序键

shop=# select product_id, product_name, sale_price, purchase_price from product order by sale_price, product_id;//优先使用sale_price排序

• NULL的顺序

会在开头或者末尾进行汇总

shop=# select product_id, product_name, sale_price, purchase_price from product order by purchase_price;//purchase_price这一列中存在null

• 在排序键中使用显示用的别名

shop=# select product_id AS id, sale_price AS sp from product order by sp, id;

注意： SQL语句在DBMS内部执行的顺序：FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY

• ORDER BY 子句中可以使用的列

select子句中没有使用的列也可以在order by子句中使用

shop=# select product_name, sale_price, purchase_price from product order by product_id;

shop=# select product_type, COUNT(*) from product group by product_type order by count(*);

• 不要使用列编号

shop=# select product_id, product_name, sale_price, purchase_price from product order by sale_price DESC, PRODUCT_ID;

2 数据更新

shop=# create table pro
shop-# (product_id char(4) not null,
shop(# product_name varchar(100) not null,
shop(# product_type varchar(32) not null,
shop(# sale_price integer ,
shop(# purchase_price integer ,
shop(# regist_date DATE ,
shop(# primary key (product_id));

2.1 数据的插入（INSERT）

insert into pro (product_id, product_name,product_type,sale_price, purchase_price,regist_date)//列清单
VALUES('0001','T恤衫'，'衣服'，1000，500，'2009-09-20');

列清单也可省略

insert into pro VALUES('0002','打孔器','办公用品',500,320,'2009-09-11'),('0003','运动T恤','衣服',4000,2800,NULL);

可以一次插入一行，一次插入多行，列清单也可省略

• 插入NULL

shop=# insert into pro VALUES('0004','叉子','厨房用具',500,NULL,'2009-09-20');

• 插入默认值

shop=# insert into pro VALUES('0005','擦菜板','厨房用具',DEFAULT,790,'2009-04-28');

shop=# insert into pro (product_id,product_name,product_type,purchase_price,regist_date) VALUES('0001','擦菜板','厨房用具',790,'2009-04-28');

无not null 约束的，可以在插入时省略列名，默认值为null
设置了not null的列不能省略

• 从其他表中复制数据

C:\PostgreSQL\9.5\bin\psql.exe -U postgres -d shop

2.2 数据的删除（DELETE）

• DROP TABLE

shop=# drop table fourtwo;\\删除整个表

• DELETE

delete from fourtwo;\\删除表的全部元素，会留下表的容器

DELETE FROM Product WHERE sale_price >= 4000;

DELETE 语句中不能使用GROUP BY、HAVING、ORDER BY子句，只能使用WHERE子句

• TRUNCATE：删除表中的全部数据，处理速度比DELETE语句要快

truncate fourtwo;

! cls 清屏
\d 显式所有的表

2.3 数据的更新（UPDATE）

UPDATE Product SET regist_date = '2009-10-10';

UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';\\指定条件的UPDATE语句,称为搜索型UPDATE语句

• 使用NULL进行更新

UPDATE Product
SET regist_date = NULL
WHERE product_id = '0008';

• 多列更新

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 = '厨房用具';

2.4 事务

• 什么是事务
  事务是需要在同一个处理单元中执行的一系列更新处理的集合

BEGIN TRANSACTION;\\事务开始语句

    -- 运动T恤的销售单价下调1000日元
    UPDATE Product
       SET sale_price = sale_price - 1000
     WHERE product_name = '运动T恤';

    -- T恤的销售单价上浮1000日元
    UPDATE Product
       SET sale_price = sale_price + 1000
     WHERE product_name = 'T恤';

ROLLBACK，COMMIT;\\事务结束语句
COMMIT是提交事务，将事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存，一旦提交就无法恢复到事务开始前的状态了
ROLLBACK是取消处理，将事务包含的全部取消处理的结束指令，相当于文件处理中的放弃保存，一旦回滚，数据库就会恢复到事务开始前的状态

• ACID特性
  原子性:在事务结束时，其中包含的更新处理要么全部执行，要么完全不执行
  一致性：事务中包含的处理要满足数据库提前设置的约束，如主键约束或者NOT NULL约束
  隔离性：不同事务之间互不干扰
  持久性：在事务结束后，DBMS能够保证该时间点的数据状态会被保存

3 复杂查询

3.1 视图

CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

定义视图时不能使用order by子句
删除视图
DROP VIEW ProductSum;
DROP VIEW ProductSum CASCARD;//递归删除

3.2 子查询

SELECT product_id, 
       product_name, 
       sale_price,
       (SELECT AVG(sale_price)
          FROM Product) AS avg_price
  FROM Product;