[TOC] ## 庫 ### 新建數據庫 1. 普通的sql語法`create database test`; 2. 進入到bin目錄執行`createdb -h localhost -p 5432 -U postgres runoobdb` ### 刪除數據庫 1. ~~~ DROP DATABASE [ IF EXISTS ] name ~~~ DROP DATABASE 會刪除數據庫的系統目錄項并且刪除包含數據的文件目錄。 DROP DATABASE 只能由超級管理員或數據庫擁有者執行。 2. 進入bin目錄執行`dropdb -h localhost -p 5432 -U postgres runoobdb` ***** 1. 查看數據庫列表`\l` 2. 進入數據庫`\c test` ## 表 ### 創建表 ~~~ CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( 一個或多個列 ) ); ~~~ ### 刪除表 `DROP TABLE table_name;` ***** 1. 查看表`\d` 2. 查看表信息`\d tablename` 3. 列出表中所有索引`\di` ## 模式(SCHEMA) ### 說明 可以看成是表的集合，可以包含視圖、索引、據類型、函數和操作符等 **相同的對象名稱可以被用于不同的模式中而不會出現沖突，例如 schema1 和 myschema 都可以包含名為 mytable 的表** ### 語法 ~~~ CREATE TABLE myschema.mytable ( ... ); ~~~ ### 刪除 刪除空的模式`DROP SCHEMA myschema;` 刪除一個模式以及其中包含的所有對象：`DROP SCHEMA myschema CASCADE;` ## curd **基本sql與其他數據庫類似** ### insert 1. insert可插入`DEFAULT`則插入默認值 ### 從某行開始取幾條 `SELECT * FROM COMPANY LIMIT 3 OFFSET 2;` ### group 在 GROUP BY 子句中，你可以對一列或者多列進行分組，但是被分組的列必須存在于列清單中(mysql對此不嚴格要求，但是建議按此標準)或者為聚合值(min等) ### 通用表達式WITH語句(CTE) ~~~ WITH name_for_summary_data AS ( SELECT Statement) SELECT columns FROM name_for_summary_data WHERE conditions <=> ( SELECT column FROM name_for_summary_data) [ORDER BY columns] ~~~ 1. name_for_summary_data可以與現有的表名相同，并且具有優先級 2. 可以在 WITH 中使用數據 INSERT, UPDATE 或 DELETE 語句，允許您在同一個查詢中執行多個不同的操作 3. 在 WITH 子句中可以使用自身輸出的數據 #### 遞歸 ``` with RECURSIVE cte as ( select a.id,a.name,a.pid from tb a where id='002' union all select k.id,k.name,k.pid from tb k inner join cte c on c.id = k.pid )select id,name from cte; ``` #### 刪除并插入 ``` WITH moved_rows AS ( DELETE FROM COMPANY WHERE SALARY >= 30000 RETURNING * ) INSERT INTO COMPANY1 (SELECT * FROM moved_rows); ``` ### HAVING 1. HAVING 子句必須放置于 GROUP BY 子句后面，ORDER BY 子句前面 ## 約束 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY ### CHECK約束 ~~~ CREATE TABLE COMPANY5( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) ); ~~~ ### EXCLUSION 約束 EXCLUSION 約束確保如果使用指定的運算符在指定列或表達式上比較任意兩行，至少其中一個運算符比較將返回 false 或 null。 ## 索引 * 索引不應該使用在較小的表上。 * 索引不應該使用在有頻繁的大批量的更新或插入操作的表上。 * 索引不應該使用在含有大量的 NULL 值的列上。 * 索引不應該使用在頻繁操作的列上 **** 1. TRUNCATE TABLE 與 DELETE 具有相同的效果，但是由于它實際上并不掃描表，所以速度更快。 此外，TRUNCATE TABLE 可以立即釋放表空間，而不需要后續 VACUUM 操作，這在大型表上非常有用。 ## 鎖[LOCK] 1. 排它鎖（Exclusive Locks）和共享鎖（Share Locks） 2. ~~~ LOCK [ TABLE ] name IN lock_mode ~~~ ~~~ LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE; ~~~ 3. lock_mode包含`ACCESS SHARE，ROW SHARE， ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE` 4. 一旦獲得了鎖，鎖將在當前事務的其余時間保持。沒有解鎖表命令；鎖總是在事務結束時釋放