一:数据类型
主要有三大类以及其他一些杂项类型:
数值型、字符型、日期型。
数值型:
名称 | 描述 | 存储大小 | 范围 |
---|---|---|---|
smallint | 存储整数,小范围 | 2字节 | -32768 至 +32767 |
integer | 存储整数。使用这个类型可存储典型的整数 | 4字节 | -2147483648 至 +2147483647 |
bigint | 存储整数,大范围。 | 8字节 | -9223372036854775808 至 9223372036854775807 |
decimal | 用户指定的精度,精确 | 变量 | 小数点前最多为131072个数字; 小数点后最多为16383个数字。 |
numeric | 用户指定的精度,精确 | 变量 | 小数点前最多为131072个数字; 小数点后最多为16383个数字。 |
real | 可变精度,不精确 | 4字节 | 6位数字精度 |
double | 可变精度,不精确 | 8字节 | 15位数字精度 |
serial | 自动递增整数 | 4字节 | 1 至 2147483647 |
bigserial | 大的自动递增整数 | 8字节 | 1 至 9223372036854775807 |
字符型:
数据类型 | 描述 |
---|---|
char(size) | 这里size 是要存储的字符数。固定长度字符串,右边的空格填充到相等大小的字符。 |
character(size) | 这里size 是要存储的字符数。 固定长度字符串。 右边的空格填充到相等大小的字符。 |
varchar(size) | 这里size 是要存储的字符数。 可变长度字符串。 |
character varying(size) | 这里size 是要存储的字符数。 可变长度字符串。 |
text | 可变长度字符串。 |
日期、时间类型:
名称 | 描述 | 存储大小 | 最小值 | 最大值 | 解析度 |
---|---|---|---|---|---|
timestamp [ (p) ] [不带时区 ] | 日期和时间(无时区) | 8字节 | 4713 bc | 294276 ad | 1微秒/14位数 |
timestamp [ (p) ]带时区 | 包括日期和时间,带时区 | 8字节 | 4713 bc | 294276 ad | |
date | 日期(没有时间) | 4字节 | 4713 bc | 5874897 ad | 1微秒/14位数 |
time [ (p) ] [ 不带时区 ] | 时间(无日期) | 8字节 | 00:00:00 | 24:00:00 | 1微秒/14位数 |
time [ (p) ] 带时区 | 仅限时间,带时区 | 12字节 | 00:00:00+1459 | 24:00:00-1459 | 1微秒/14位数 |
interval [ fields ] [ (p) ] | 时间间隔 | 12字节 | -178000000年 | 178000000年 |
1微秒/14位数 ‘ |
其他类型:
布尔型:
名称 | 描述 | 存储大小 |
---|---|---|
boolean | 它指定true 或false 的状态。 |
1字节 |
货币类型:
名称 | 描述 | 存储大小 | 范围 |
---|---|---|---|
money | 货币金额 | 8字节 | -92233720368547758.08 至 +92233720368547758.07 |
几何类型:
名称 | 存储大小 | 表示 | 描述 |
---|---|---|---|
point | 16字节 | 在一个平面上的点 | (x,y) |
line | 32字节 | 无限线(未完全实现) | ((x1,y1),(x2,y2)) |
lseg | 32字节 | 有限线段 | ((x1,y1),(x2,y2)) |
box | 32字节 | 矩形框 | ((x1,y1),(x2,y2)) |
path | 16+16n字节 | 封闭路径(类似于多边形) | ((x1,y1),…) |
polygon | 40+16n字节 | 多边形(类似于封闭路径) | ((x1,y1),…) |
circle | 24字节 | 圆 | <(x,y),r> (中心点和半径) |
二:数据库基本操作
创建数据库:
CREATE DATABASE databaseName
删除数据库:
DROP DATABASE dbname
创建表:
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );
删除表:
DROP TABLE tableName
创建模式:
模式,schema:包含视图,索引,序列,数据类型,运算符和函数。
CREATE SCHEMA schema_name;
三:基本SQL命令
1:insert
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);
2:select
SELECT column1, column2...column FROM table_name
3:update
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
4:delete
DELETE FROM table_name WHERE [condition];
5:order by
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
6:group by
SELECT column-list FROM table_name WHERE [conditions ] GROUP BY column1, column2....columnN //先分组 ORDER BY column1, column2....columnN //后排序
7:having:分组的条件限制
SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2
8:条件查询:用于where 子句
主要有以下几种条件:
- AND 条件
- OR 条件
- NOT 条件
- LIKE 条件:模糊查询,使用%作占位符。
- IN 条件
- NOT IN 条件
- BETWEEN 条件:BETWEEN ... AND ...
四:连接操作
主要有:
- 内连接(INNER JOIN)
- 左外连接(LEFT OUTER JOIN)
- 右外连接(RIGHT OUTER JOIN)
- 全连接(FULL OUTER JOIN)
- 跨连接(CROSS JOIN)
内连接:两表某字段值相等
SELECT table1.columns, table2.columns FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field;
外连接:哪边连接就保留哪边字段。
左外连接:左外连接返回从“ON
”条件中指定的左侧表中的满足条件的行,右侧不满足条件的字段值显示为空。
SELECT table1.columns, table2.columns FROM table1 LEFT OUTER JOIN table2 ON table1.common_filed = table2.common_field;
右外连接:保留右侧表中的字段。
SELECT table1.columns, table2.columns FROM table1 RIGHT OUTER JOIN table2 ON table1.common_filed = table2.common_field;
全外连接:FULL外连接从LEFT手表和RIGHT表中返回所有行。 它将NULL
置于不满足连接条件的位置。
SELECT table1.columns, table2.columns FROM table1 FULL OUTER JOIN table2 ON table1.common_filed = table2.common_field;
跨连接:(CROSS JOIN
)将第一个表的每一行与第二个表的每一行相匹配。 它也被称为笛卡儿积分。 如果table1
具有“x
”列,而table2
具有“y
”列,则所得到的表将具有(x + y
)列。
SELECT coloums FROM table1 CROSS JOIN table2
五:高级操作
1:视图
CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
2:存储过程
存储过程是存储在数据库服务器上并可以使用SQL执行的一组SQL语句和过程语句(声明,分配,循环,控制流程等)。 它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作。通常可以作为高并发数据库操作的优化手段来使用。
CREATE FUNCTION function_name (arguments) RETURNS return_type AS $variable_name$ DECLARE declaration; //变量声明:格式为 varName Type [...] BEGIN < function_body > //函数体:一般为多句sql语句 [...] RETURN { variable_name | value } END; LANGUAGE plpgsql;//指明实现该函数的语言
调用存储过程:
PROCEDURE funcName();
3:触发器
触发器是一组回调函数,它们在指定的表上执行指定的数据库事件(即,INSERT
,UPDATE
,DELETE
或TRUNCATE
语句)时自动运行。
触发器多用于验证输入数据,执行业务规则,保持审计跟踪等。
对于操作修改的每一行,都会调用一个标记为FOR EACH ROWS
的触发器。而标记为FOR EACH STATEMENT
的触发器只对任何给定的操作执行一次,而不管它修改多少行。
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ -- Trigger logic goes here.... ];
您可以选择在表名后指定FOR EACH ROW/FOR EACH STATEMENTS
4:索引
索引是用于加速从数据库检索数据的特殊查找表。 索引为出现在索引列中的每个值创建一个条目。
-
索引使用
SELECT
查询和WHERE
子句加速数据输出,但是会减慢使用INSERT
和UPDATE
语句输入的数据。 - 可以通过使用
CREATE INDEX
语句创建索引,指定创建索引的索引名称和表或列名称。 - 还可以创建一个唯一索引,类似于唯一约束,该索引防止列或列的组合上有一个索引重复的项。
索引的类型:
B-tree
,Hash
,GiST
,SP-GiST
和GIN
等。
每种索引类型根据不同的查询使用不同的算法。 默认情况下,CREATE INDEX
命令使用B树索引。
创建索引:
CREATE INDEX index_name ON table_name;
单列索引:仅对一个表的列创建索引,则将其称为单列索引。
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);
删除性能:
DROP INDEX index_name;
5:时间与日期函数
函数 | 描述 |
---|---|
AGE() |
减去参数 |
CURRENT DATE/TIME() |
它指定当前日期和时间。 |
DATE_PART() |
获取子字段(相当于提取) |
EXTRACT() |
获得子字段 |
ISFINITE() |
测试有限的日期,时间和间隔(非+/-无穷大) |
JUSTIFY |
调整间隔 |
计算两个时间对间隔值:
函数 | 描述 |
---|---|
age(timestamp, timestamp) |
得到参数一减去参数二的时间间隔。 |
age(timestamp) |
得到current_date(午夜) 减去参数值的时间间隔 |
获取当前时间/日期的多种格式:
函数 | 描述 |
---|---|
CURRENT_DATE | 提供当前日期 |
CURRENT_TIME | 提供带时区的值 |
CURRENT_TIMESTAMP | 提供带时区的值 |
CURRENT_TIME(precision) | 可以选择使用precision 参数,这将使结果在四分之一秒的范围内四舍五入到数位数。 |
CURRENT_TIMESTAMP(precision) | 可以选择使用精度参数,这将使结果在四分之一秒的范围内四舍五入到数位数。 |
LOCALTIME | 提供没有时区的值。 |
LOCALTIMESTAMP | 提供没有时区的值。 |
LOCALTIME(precision) | 可以选择使用精度参数,这将使结果在四分之一秒的范围内四舍五入到数位数。 |
LOCALTIMESTAMP(precision) | 可以选择使用精度参数,这将使结果在四分之一秒的范围内四舍五入到数位数。 |
6:unions子句
用于组合两个或多个SELECT语句的结果,而不返回任何重复的行。每个SELECT必须具有相同的列数,相同数量的列表达式,相同的数据类型,并且具有相同的顺序。
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
7:修改表(alert与update的修改对象不同:update是修改表中的记录值,而alert是直接修改表本身)
ALTER TABLE table_name operation;
比如:
使用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;
8:截断表
从现有表中删除所有数据。
您也可以使用DROP TABLE
命令删除完整的表,但会从数据库中删除完整的表结构,如果希望存储某些数据,则需要重新创建此表。
截断表和在表上使用DELETE *
语句具有相同的效果,但由于实际上并不扫描表,所以它的速度更快。 此外,它会立即回收磁盘空间,而不需要后续的VACUUM
操作。 这在大表上是最有用的。
TRUNCATE TABLE table_name;
此语句执行完后,表内记录就会清空。
9:事务
以下命令用于控制事务:
BEGIN TRANSACTION
:开始事务。COMMIT
:保存更改,或者您可以使用END TRANSACTION
命令。ROLLBACK
:回滚更改。
10:锁
在UPDATE
和DELETE
修改的行在事务的持续时间内被自动独占锁定。 这将阻止其他用户更改行,直到事务被提交或回退。
在某些情况下,必须手动控制锁定:
LOCK [ TABLE ] name IN lock_mode
name
:要锁定的现有表的锁名称(可选模式限定)。 如果在表名之前指定了ONLY
,则仅该表被锁定 如果未指定ONLY
,则表及其所有后代表(如果有)被锁定。lock_mode
:锁模式指定此锁与之冲突的锁。 如果未指定锁定模式,则使用最严格的访问模式ACCESS EXCLUSIVE
。 可能的值是:ACCESS SHARE
,ROW SHARE
,ROW EXCLUSIVE
,SHARE UPDATE EXCLUSIVE
,SHARE
,SHARE ROW EXCLUSIVE
,EXCLUSIVE
,ACCESS EXCLUSIVE
。
11:子查询
SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])
12:自增
PostgreSQL具有数据类型smallserial
,serial
和bigserial
; 这些不是真正的类型,而只是在创建唯一标识符列的标志以方便使用。 这些类似于一些其他数据库支持的AUTO_INCREMENT
属性。
13:权限
要为用户分配权限,使用GRANT
命令。
GRANT privilege [, ...] ON object [, ...] TO { PUBLIC | GROUP group | username }
privilege
值可以是:SELECT
,INSERT
,UPDATE
,DELETE
,RULE
,ALL
。object
:要向其授予访问权限的对象的名称。 可能的对象是:表,视图,序列PUBLIC
:表示所有用户的简短形式。GROUP group
:授予权限的组。username
:授予权限的用户的名称。PUBLIC
是表示所有用户的简短形式。
回收权限:
REVOKE privilege [, ...] ON object [, ...] FROM { PUBLIC | GROUP groupname | username }
privilege
值可以是:SELECT
,INSERT
,UPDATE
,DELETE
,RULE
,ALL
。object
: 授予访问权限的对象的名称。 可能的对象是:表,视图,序列。PUBLIC
:表示所有用户的简短形式。GROUP group
:授予权限的组。username
:授予权限的用户的名称。PUBLIC
是表示所有用户的简短形式。