查看所有數據庫

查看所有表 \d

查看函數定義

查看所有用戶 select usename from pg_user;

1.數據庫創建管理

CREATE DATABASE test;

2.數據庫用戶創建管理

CREATE USER tom PASSWORD 'Root123456.';

3.表的創建及管理

3.1.創建表

CREATE TABLE test(ID INTEGER PRIMARY KEY, NAME TEXT);

3.2.數據的增刪改查

--增insert into test values (1,'xiaoming'),(2,'xiaohong')，,(3,'xiaoqiang');
--刪DELETE FROM test WHERE ID<2;--改UPDATE test SET ID = ID*2 WHERE ID=2;--查SELECT * FROM test LIMIT 1;

3.3.表的刪除

DROP TABLE test;

--創建基表，并插入數據。
CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl values (1, 'insertTable');
--創建視圖
REATE VIEW ro_view1 AS SELECT a, b FROM base_tbl;
--視圖插入、更新和刪除數據
INSERT INTO ro_view1 values (2, 'insertView');
UPDATE ro_view1 SET b = 'updateView' WHERE a = 1;
DELETE FROM ro_view1 WHERE a= 2;--創建check option視圖
CREATE VIEW ro_view2 AS SELECT a, b FROM base_tbl WHERE a > 10 WITH CHECK OPTION;
--插入、更新視圖不可見數據失敗
INSERT INTO ro_view2 values (5, 'insertView');
INSERT INTO base_tbl values (15, 'insertTable');
UPDATE ro_view2 SET a = 5 WHERE a = 15;

4.SQL語法實驗

4.1.DDL

定義數據庫:CREATE DATABASE、ALTER DATABASE、DROP DATABASE

定義模式：CREATE SCHEMA、ALTER SCHEMA、DROP SCHEMA

定義表空間：CREATE TABLESPACE、ALTER TABLESPACE、DROP TABLESPACE

定義表：CREATE TABLE、ALTER TABLE、DROP TABLE

定義分區表：CREATE TABLE PARTITION、ALTER TABLE PARTITION

定義索引：CREATE INDEX、DROP INDEX

定義存儲過程：CREATE PROCEDURE、ALTER PROCEDURE、DROP PROCEDURE

定義函數:CREATE FUNCTION、ALTER FUNCTION、DROPFUNCTION

定義視圖：CREATE VIEW、ALTER VIEW、DROP VIEW

4.2.DML

INSERT。

UPDATE。

SELECT。

DELETE\TRUNCATE。

COPY。

CREATE TABLE test(ID INTEGER PRIMARY KEY, NAME TEXT);
insert into test values (1,'xiaoming'),(2,'xiaohong')，,(3,'xiaoqiang');
COPY test TO '/home/omm/ds_ship_mode.dat'

LOCK。

CALL。

4.3.DCL

定義角色CREATE/ALTER/DROP ROLE

定義用戶CREATE/ALTER/DROP USER

授權GRANT

收回權限REVOKE

設置默認權限ALTER DEFAULT PRIVILEGES

關閉當前節點SHUTDOWN

5.創建和管理其他數據庫對象

5.1.schema

CREATE SCHEMA ds;
ALTER SCHEMA ds RENAME TO ds_new;
ALTER SCHEMA ds_new OWNER TO tom;
DROP SCHEMA ds_new;

5.2.索引

create schema tpcds;
CREATE TABLE tpcds.ship_mode_t1
(SM_SHIP_MODE_SK           INTEGER               NOT NULL,SM_SHIP_MODE_ID           CHAR(16)              NOT NULL,SM_TYPE                   CHAR(30)                      ,SM_CODE                   CHAR(10)                      ,SM_CARRIER                CHAR(20)                      ,SM_CONTRACT               CHAR(20)
) 
;
--在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上創建普通的唯一索引。
CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
--在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上創建指定B-tree索引。
CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
--在表tpcds.ship_mode_t1上SM_CODE字段上創建表達式索引。
CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
--設置索引不可用。
ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;
--刪除一個現有的索引。
DROP INDEX tpcds.ds_ship_mode_t1_index2;
DROP TABLE tpcds.ship_mode_t1;
DROP SCHEMA tpcds;

5.3.視圖

--創建基表，并插入數據。
CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl values (1, 'insertTable');
--創建視圖
REATE VIEW ro_view1 AS SELECT a, b FROM base_tbl;
--視圖插入、更新和刪除數據
INSERT INTO ro_view1 values (2, 'insertView');
UPDATE ro_view1 SET b = 'updateView' WHERE a = 1;
DELETE FROM ro_view1 WHERE a= 2;--創建check option視圖
CREATE VIEW ro_view2 AS SELECT a, b FROM base_tbl WHERE a > 10 WITH CHECK OPTION;
--插入、更新視圖不可見數據失敗
INSERT INTO ro_view2 values (5, 'insertView');
INSERT INTO base_tbl values (15, 'insertTable');
UPDATE ro_view2 SET a = 5 WHERE a = 15;

5.4.存儲過程

CREATE PROCEDURE proc_insert_sql()
AS BEGIN insert into test values (1,'xiaoming'),(2,'xiaohong'),(3,'xiaoqiang');
END
/
CALL proc_insert_sql();
SELECT * FROM test;
DROP PROCEDURE proc_insert_sql;

5.5.函數

CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer
AS BEGIN 
RETURN num1 + num2;
END
/
SELECT func_add_sql(4,2);
DROP FUNCTION func_add_sql;

6.用戶權限控制

6.1.角色

CREATE ROLE paul IDENTIFIED BY 'Root123456.';
ALTER ROLE paul IDENTIFIED BY 'Root123456&' REPLACE 'Root123456.';

6.2.權限

CREATE USER jack PASSWORD 'Root123456.';
create schema tpcds;
ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds GRANT INSERT ON TABLES TO jack;

6.3.授權

GRANT ALL PRIVILEGES TO jack;
GRANT jack TO paul WITH ADMIN OPTION;

6.4.權限回收

REVOKE paul FROM jack;
REVOKE ALL PRIVILEGES FROM jack;
DROP ROLE paul;
DROP OWNED BY jack;
DROP USER jack;