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 為分組列

注意：

在select子句中書寫了多余的列；
在group by子句中使用別名；
group by 子句的結果能排序嗎？
答：它是隨機的，并不知道是按照什么樣的規則進行排序，
聚合函式不允許出現在where子句中；
只有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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/232514.html

標籤：其他

上一篇：MongoDB匯入匯出以及資料庫備份

下一篇：使用mysqldump備份資料庫

SQL語法