1、sql命令分类
在SQL（Structured Query Language）中，根据不同的操作和数据定义，命令通常被分类为不同的组。DDL、DML、DCL 和 DQL 是这些组的主要分类。下面是它们各自代表的含义：
DDL (Data Definition Language):
意义：DDL 代表数据定义语言，用于定义或修改数据库结构。
常用命令：
CREATE：用于创建数据库、表、视图、索引等。
ALTER：用于修改数据库结构，如表结构的修改。
DROP：用于删除数据库、表、视图、索引等。
TRUNCATE：用于删除表中的所有数据，但不删除表本身。
DML (Data Manipulation Language):
意义：DML 代表数据操纵语言，用于查询和修改数据库中的数据。
常用命令：
SELECT：用于从数据库中选择数据。
INSERT：用于向表中插入新数据。
UPDATE：用于更新表中的现有数据。
DELETE：用于从表中删除数据。
MERGE：在某些数据库系统中，用于合并两个表中的行。
DQL (Data Query Language):
意义：DQL 通常被视为DML的一个子集，专门用于查询数据库中的数据。尽管它与DML有重叠，但DQL通常仅指SELECT命令。
常用命令：
SELECT：用于从数据库中选择数据。
DCL (Data Control Language):
意义：DCL 代表数据控制语言，用于控制对数据库和表的访问权限。
常用命令：
GRANT：用于授予用户或角色对数据库对象的访问权限。
REVOKE：用于撤销用户或角色对数据库对象的访问权限。
2、数据类型
PostgreSQL 拥有大量的内置的数据类型用来存储不同类型的数据。
标准类型(Standard)
Boolean and Logic
Strings - char(n), varchar(n), varchar2(n) and text
Numbers - integer, floating point, numeric, number
Date/time - timestamp(), date, time(), interval(), datetime
扩展类型(Extended)
Geometric - point, line, box, etc
Network - inet, cidr, macaddr
Bit - bit, bit varying
数组和符合类型(Arrays and Composite types)
系统类型(System types)
任何基本类型 (不是组合类型或域) 可以用作数组，只要在类型后面加一个中括号 ([]) 。创建一个二维数组如下:
CREATE TABLE a(a int[][5]);
PostgreSQL 不强制要求定义数组大小，甚至维数也可以不定义。所有的数组作为type[].
Arrays 可以为 NULL, 但数组内的元素不能为null
ANSI 语法只支持一维数组，并且需要定义数组大小。但是，和其他数组一样，PostgreSQL对此没有特殊要求。
CREATE TABLE a(a int ARRAY[5]);
输入(Input)
通常形式是 “‘{ value 分隔符 value … }’”
单引号是必须的 (实际上连接成一个字符串)
其内的值要么是一个常量，要么是一个数组
分隔符根据类型而定。 所有的内置类型使用 “,”，长方形类型(box)除外, 它使用 “;”。
输入值需要对应多维数组中的维数
访问
通过下标访问 - SELECT a[2][2];
通过范围访问 - a[1:2][2] = '{{1,2},{4,5}}‘
如果没有写 ([2])，默认下界(Lower range)是1
查询数组下标以外的值返回为 NULL, 而不是错误: a[3][4] IS NULL = true
更新
全部赋值 - UPDATE a SET a = '{10,11}‘
部分赋值 - SET a[2][2:2] = ‘{{2},{5}}’
array_cat(array, array) – 连接两个数组
array_prepend(element, array) – 在数组开头添加一个元素
array_append(array, element) –在数组末尾添加一个元素
或者使用 ||:
array[1,2] || array[3,4] = {1, 2, 3, 4}
array[1,2] || array[[3,4]] = {{1, 2}, {3, 4}}
1 || array[2,3] = {1, 2, 3}
array[1,2] || 3 = {1, 2, 3}
array_dims – 返回数组的维数，生成text结果
array_dims(array[[1,2],[3,4]]) = [1:2][1:2]
值操作符 ANY(array)
SOME 是 ANY 的同意词
如果数组中有任一元素都满足比较条件则返回TRUE.
1 = ANY(array[1,2]) = TRUE
2 < ANY(array[1,2]) = FALSE
如果任一结果返回 TRUE，则表达式返回TRUE。
值操作符 ALL(array)
如果数组中所有的元素都满足比较条件则返回TRUE
1 = ALL(array[1,2]) = FALSE
0 < ALL(array[1,2]) = TRUE
如果任一结果返回 FALSE ，则表达式返回FALSE 。
array_lower, array_upper – 返回指定数组维数的下界或上界
array_upper(array[[1,2],[3,4],[5,6]], 2) = 2
array_to_string – 使用字符串将数组元素连接起来
array_to_string(a, ‘xy’) = 1xy2xy3xy4xy5xy6
string_to_array – 使用指定的分隔符将字符串分割为数组
string_to_array(‘1xy2xy3xy4xy5xy6’,‘xy’) = {1,2,3,4,5,6}
从一系列的输入值中计算出某个单一的值
忽略 NULLs (如果所有的输入是NULL则返回 NULL)
avg, count, max, min, stddev, sum, variance
bit_and, bit_or – 所有非空输入值按位”与/或”
bool_and, every -如果所有输入值都是真，则为真，否则为假
bool_or -如果至少有一个输入值为真，则为真，否则为假
返回多行的函数；使用时和表类似
generate_series(start, stop[, step]) -生成一个数值序列，从 start 到 stop，步长为 step (默认为1)
SELECT * FROM generate_series(1,3) a;
a
1
2
3
约束(Constraints)
条件约束 (Check Constraints)
非空约束 (Not-Null Constraints)
唯一性约束(Unique Constraints)
主键(Primary Keys)
外键(Foreign Keys)
用来生成唯一的键(Unique Key)
语法
CREATE [TEMPORARY | TEMP] SEQUENCE name [INCREMENT [ BY ] increment]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
示例
CREATE SEQUENCE department_id_seq;
nextval() – 输出 sequence的下一个值
currval() -输出 sequence的当前值
setval() –设置 sequence将要输出的下一个值
数值函数
ceil/ceiling, floor
exp (exponential), ln, log
greatest, least
random, setseed
round, truncate
sign
to_number
degrees(radians), radians(degrees)
cos, acos, sin, asin
cot (cotangent), tan, atan
atan2(x, y) = atan(x/y)
数据类型-日期/时间(Date/Time)
可能会以 integers 或者 double 的精度存储
timestamp[§] with time zone (timestamptz)
timestamp[§] without time zone (timestamp)
p - 0 to 6 digits of fractional seconds
- interval
interval, timestamp
特殊值
now – 当前事务开始时间
today, tomorrow, yesterday - 那天的midnight
epoch - unix epoch (midnight, Jan. 1, 1970)
Date
当添加了 timestamp,即假设 midnight
特殊值: now, today/tomorrow/yesterday, epoch - int (days), interval, time (returns timestamp)
- int (days), interval, date (returns int)
time[§] with time zone (timetz)
time[§] without time zone (time)
p - 0 to 6 for 如果使用integer存储，p为0-6；如果使用double存储，p为0-10
特殊值: now, allballs (00:00:00.00 UTC)
- interval, date (returns timestamp)
- interval, time
current_date, current_time[§], current_timestamp[§]
now() – 事务开始的 date/time/timestamp with timezone
localtime[§], localtimestamp[§] -事务开始的 time/timestamp without timezone
timeofday() – 当前时间 (不是事务开始时间). 返回text.
PostgreSQL数据类型之 Strings
Postgres中有3种主要的文本类型： char(n), varchar(n) 和text.
char(n) 和 varchar(n) 大多遵循 ANSI 标准
和其他许多数据库不一样， 在Postgres中，char(n)不存储为fixed-sized。对它的处理和 varchar(n)很像，只是需要被填补(being padded) 。
char(n) 和 varchar(n) - cont’d
char() 或者 char 等同于 char(1) ，和 “char”不同
varchar()或者 varchar等同于 一个没有长度限制的varchar. 这是 Postgres 的扩展(extension)。
模式匹配(Pattern Matching) - LIKE
string [NOT] LIKE pattern [ESCAPE escape-character] (also ~~ and !~~)
ILIKE - case Insensitive LIKE ( ~~, ~~ )
“_” – 单字符通配符 (思考“?”)
“%” – 多字符通配符 (思考 “*”)
匹配整个字符串; 使用 ‘%abc%’ 来查询字符串中任何位置的’abc’
使用 escape-character (通常用“\”)来将一个字面上的(不作为通配符)_或%加入到 pattern中
模式匹配(Pattern Matching) - RegEx
正则表达式(Regular Expression (或者 regex)) 是一个模式匹配(pattern matching)/文本处理(text processing)的语言
string ~ regex – 如果 string 匹配上 regex则返回TRUE, 大小写敏感
“~” -大小写不敏感
“!~” 和 “!~” 如果 string 没有匹配上 regex则返回TRUE
不匹配上整个字符串
SELECT ‘PostgreSQL’ ~ ‘gre’;
?column?
t
模式匹配(Pattern Matching) - SIMILAR TO
string [NOT] SIMILAR TO pattern [ESCAPE escape-character]
和 LIKE操作相同, 只不过 pattern 是 ANSI SQL regex
“_” and “%” instead of “.” and “.”
“.” 不是一个特殊的字符
“|”, “”, “+”, “()”, 和 “[]” 在正则表达式中操作
“{}” 不支持
字符串函数 - General
||连接两个字符串
greatest(value[, …]), least(value[, …]) – 返回最大最小值
lower(), upper() – 将字符串变为小写或大写
length() – 返回字符串中字符的个数. 参见 char_length(), character_length(), octet_length() 和 bit_length()
lpad, rpad(string, length[, fill])
使用填充字符串 “fill”，在左边或右边填充一个字符串到固定的长度”length” (填充字符串fill缺省为空格).如果填充后的字符串比”length”约束的长，则会截断
SELECT rpad(‘pad’, 8, ‘xyz’);
padxyzxy
ltrim, rtrim, btrim(string[, characters])
删除字符串左边或右边或两边的characters字符 (默认是空格)
SELECT btrim(‘xyxtrimyyx’, ‘xy’);
trim
position(substring in string)
substring 在 string中的位置。参见 strpos().
SELECT position(‘DB’ in ‘EnterpriseDB’);
position
11
substring(string[ from start][ for run])
substring(string from pattern)
substring(string from pattern for escape)
根据指定位置返回子串, POSIX regex pattern, or SQL regex pattern. 参见 substr().
数据类型逻辑和比较操作符
AND, OR, 和 NOT
<, >, <=, >=, =,和<> 或者 != 所有都返回boolean值.
BETWEEN和NOT BETWEEN
IS NULL 是唯一标准的方式查看是否为空. IS NOT NULL 是相反的操作符。
NULL != NULL ! NULL = anything 将返回 NULL
将 transform_null_equals 设置为 on 就会使得x = NULL 转换为 x IS NULL
a IS DISTINCT FROM b 等价于 a != b，除非 a 或 b 有一个为空，那就返回TRUE。如果a和b都是 null, 他将返回FALSE. 这将使null的比较变的更简单。
IS TRUE, IS NOT TRUE, 等等…
数据类型条件表达式
SQL中的 CASE 表达式是一种常见的条件表达式，同其他语言的if/else语句很相似:
CASE WHEN condition THEN result
CASE expression
WHEN value THEN result
[WHEN …]
[ELSE result]
END
COALESCE() – 返回第一个非空值(non-null) COALESCE(desc, short_desc, ‘(none)’)
NULLIF(a, b) – 与 coalesce 相反,如果 a = b 则返回NULL，否则返回 a ，NULLIF(a, ‘(none)’)
