新建数据库:
create database testdb
;查看所有的数据库:
\l
查看所有的表:
\d
切换数据库:
\c testdb
;删除数据库:
drop database testdb
;创建表:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
PRIMARY KEY( one or more columns ) );
删除表:
drop table table_name
;创建模式:
create schema schema_name
;删除模式:
drop schema schema_name
;递归删除模式:
drop schema schema_name cascade
;插入数据:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN)
; 可以同时插入多个值查询语句:
SELECT ID, NAME, AGE, SALARY FROM EMPLOYEES WHERE [condition]
;更新语句:
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition]
;删除语句:
DELETE FROM table_name WHERE [condition]
;Order By:
SELECT column-list
FROM table_name
[WHERE condition]
ORDER BY column1, column2, .. columnN [ASC | DESC];
- 分组:
SELECT column-list
FROM table_name
WHERE [conditions ]
GROUP BY column1, column2....columnN [ORDER BY column1, column2....columnN]
eg:select name,sum(id) from student group by name;
- Having:
SELECT column-list
FROM table_name
WHERE [conditions ]
GROUP BY column1, column2....columnN
Having [ conditions ]
[ORDER BY column1, column2....columnN]
查询条件:
And、Or、Not、Like、In、Not In、Between
Like:以su开头:
“%su”
内连接:
SELECT table1.columns, table2.columns
FROM table1 INNER JOIN table2
ON table1.common_filed = table2.common_field;
- 外连接:
SELECT table1.columns, table2.columns
FROM table1 LEFT OUTER JOIN table2
ON table1.common_filed = table2.common_field;
外连接分为左外连接,右外连接,全外连接
跨连接(叉乘):
SELECT coloums FROM table1 CROSS JOIN table2
创建视图:
CREATE [TEMP | TEMPORARY] VIEW view_name
AS SELECT column1, column2..... FROM table_name
WHERE [condition];
删除视图:
DROP VIEW view_name
;函数:
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
DECLARE
declaration;
[...]
BEGIN
< function_body >
[...]
RETURN { variable_name | value }
END; LANGUAGE plpgsql;
--example
CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
SELECT count(*) into total FROM EMPLOYEES;
RETURN total;
END;
$total$ LANGUAGE plpgsql;
--执行创建的函数
select totalRecords();
- 触发器
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
-- Trigger logic goes here....
];
别名:As 可以列、表添加别名
索引:
- 单列索引:
CREATE INDEX index_name ON table_name (column_name)
; - 多列索引:
CREATE INDEX index_name ON table_name (column1_name, column2_name)
; - 唯一索引:
CREATE UNIQUE INDEX index_name on table_name (column_name)
;
- 单列索引:
Union:两个结合的表必须有相同的列,去重
Union all:两个结合的表必须有相同的列,不去重
Alter
使用ALTER TABLE语句在现有表中添加新列的基本语法如下:
ALTER TABLE table_name ADD column_name datatype;
现有表中ALTER TABLE到DROP COLUMN(删除某个字段)的基本语法如下:
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE更改表中列的DATA TYPE(修改字段类型)的基本语法如下:
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
ALTER TABLE向表中的列添加NOT NULL约束的基本语法如下:
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
ALTER TABLE添加唯一约束ADD UNIQUE CONSTRAINT到表中的基本语法如下:
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
ALTER TABLE将“检查约束”添加到表中的基本语法如下所示:
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
ALTER TABLE添加主键ADD PRIMARY KEY约束的基本语法如下:
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
使用ALTER TABLE从表中删除约束(DROP CONSTRAINT)的基本语法如下:
ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;
使用ALTER TABLE从表中删除主键约束(DROP PRIMARY KEY)约束的基本语法如下:
ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;
截断表:TRUNCATE TABLE table_name;
事务:
锁:LOCK [ TABLE ]name IN lock_mode;
- lock_mode包括:
ACCESS SHARE
,ROW SHARE
,ROW EXCLUSIVE
,SHARE UPDATE EXCLUSIVE
,SHARE
,SHARE ROW EXCLUSIVE
,EXCLUSIVE
,ACCESS EXCLUSIVE(未指定的情况下选择最严格的模式)
。
- lock_mode包括:
自动递增:serial
CREATE TABLE tablename (
colname SERIAL
);
- 权限:
--Grant
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }
--Revoke
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }