sql

列

计算列

GENERATED ALWAYS AS

定义生成列，指定计算列的计算规则，并将计算结果存储在表中

语法:

columnName: 计算列
dataType: 字段类型
GENERATED ALWAYS AS 定义生成列，指定计算列的计算规则，并将计算结果存储在表中
generationExpression: 计算规则
VIRTUAL 虚拟列,只能用于查询相关的操作, 不能插入更新删除, 动态计算, 不存储实际值, 灵活计算与数据处理
STORED 存储列, 自动计算, 存储在表中, 提高查询性能

columnName dataType [GENERATED ALWAYS] AS (generationExpression) [VIRTUAL | STORED]

示例: 自动计算firstname和lastname列, 把两列的值加入到fullname存储到表中

CREATE TABLE users
(  
    firstname varchar(20),  
    lastname  varchar(20),  
    fullname  text GENERATED ALWAYS AS ( firstname || lastname ) STORED 
);

自增长列

定义自增长列, 指定增长列的初始值, 步长和最大值, 结果存储到表中

语法:

columnName: 计算列
dataType: 字段类型, 必须是数值类型, 可以用INT,BIGINT
GENERATED BY DEFAULT AS 定义自增长列, 指定初始值, 步长, 最大值
generationExpression: 计算规则
GENERATED ALWAYS AS IDENTITY: 生成自增长值
GENERATED BY DEFAULT AS IDENTITY: 生成自增长值, 如果在更新或者插入时不写可以自动加入默认值

columnName dataType [GENERATED  { ALWAYS | BY DEFAULT }] AS IDENTITY [ ( sequenceOptions) ]

示例1: GENERATED ALWAYS AS IDENTITY: 生成自增长值

CREATE TABLE employees (
	id SERIAL,
	name VARCHAR(20),
	salary SERIAL  GENERATED ALWAYS AS IDENTITY,
);
-- 在添加数据时必须写OVERRDING SYSTEM VALUE
INSERT INTO employees(name, salary) OVERRDING SYSTEM VALUE
VALUES ('san', 1)

示例2: GENERATED BY DEFAULT AS IDENTITY: 生成自增长值

CREATE TABLE employees (
	id SERIAL,
	name VARCHAR(20),
	salary SERIAL GENERATED BY DEFAULT AS IDENTITY,
);
INSERT INTO employees(name, salary)
VALUES ('san', 1);

-- 在添加数据时也可以写OVERRDING SYSTEM VALUE
INSERT INTO employees(name, salary) OVERRDING SYSTEM VALUE
VALUES ('san', 1);

函数

聚合函数

适用于字符串类型的函数

|| 或者CONCAT()连接字符

使用逗号分隔两个字符串

SELECT name || ', ' || age
FROM city;

CONCAT()

SELECT CONCAT(name, ', ',country)
FROM city;

LOWER() 字段小写
UPPER() 字段大小
LENGTH() 字符串长度
CONCAT(): 连接两个字符串
AVG()

适用于数值类型的函数

SUM(): 相加

算数运算符

比较数学运算符

= 是否等于
> 是否
< 是否小于
>= 是否大于和等于
<= 是否小于和等于
IN 是否在这个列里在aera列中包含('Shanghai', 'Delin')的数据

SELECT name, aera
FROM city
WHERE aera IN ('Shanghai', 'Delin')

NOT IN 是否不在这个list里: 排除name在'Shanghai', 'Delin'

SELECT name, aera
FROM city
WHERE name NOT IN ('Shanghai', 'Delin')

<> 值是否不相等
!= 值是否不相等
BETWEEN 是否在两个值之间

键

主键Primary Key

语法:

key: 字段
type: 伪类型, 从1开始
PRIMARY KEY, 主键, 从1开始, 自动增加, 该值字段值在表中是唯一的

<key> <type> PRIMARY KEY

示例:

CREATE TABLE photos(
    id SERTAL PRIMARY KEY,
	url VARCHAR(200)
);

外键Foreign Key

语法 PEFERNCES: 引用, 标识与本行的记录, 通常是关联其他表的字段

<key> PEFERNCES <tablename>(column)

示例:

CREATE TABLE photos(
	url VARCHAR(200),
	user_id INTEGER REFERENCES users(id)
);

类型

数值

名称	大小	说明	范围
smalint	2 字节	小范围整数	-32768到32767
integer	4 字节	中范围整数	-2147483648到 2147483648
bigint	8 字节	大范围整数	-9223372036854775808到9223372036854775807
decimal(精度,小数位数)	可变长	用户指定精度, 精确, 用于货币,金融等高精度和准确结果	小数点前 131072 位, 小数点后 16383 位
numeric/numeric(p,s)	可变长	带有 p位数字, 小数后有 s 数字. 用户指定的精度, 精确	小数点前 131072 位, 小数点后 16383 位
real/float8	4 字节	可变精度, 不精确	6 位十进制数字精度
double	8 字节	可变精度, 不精确	15 位十进制数字精度
float(n)		浮点数,精度至少为 n, 最多 8 个字节

伪类型

自动生成唯一的key

关键字	长度	范围
smallserial	2字节	1 到 32，767
serial	4字节	1 到 2，147，483，647
bigserial	8字节	1 至 9，223，372，036，854，775，807

字符

名称	大小	说明
char(n)	固定长度	如果没占满,则会填充空白,插入的字符串长度超过列的长度，PostgreSQL 将发出错误
chararcter(n)	可变长度	根据字符数计算，不受编码格式影响，但占用空间比`varchar`大
varchar(n)	可变长度	根据字节数来限制，根据实际字节数占用不同的大小，默认为utf8编码，中文占3个字符
charcter varying(n)
text(n)	可变长度	长度不受限制的字符串

时态

|名称|说明|示例| |--|--|--|--| |time|仅存储时间值|| |date|仅存储日期|| |timestamp|存储时间值与日期|| |timestamptz|时区感知时间戳数据类型,带时区的时间戳的缩写|| |interval|存储时间段||

数组

声明
text[]
char[]
integer[]

JSON

关键字	说明
JSON	纯JSON数据,不格式化,每次处理都需要重新解析
JSONB	以二进制格式存储 JSON 数据,处理速度快,但插入慢, 支持索引

UUID

UUID 允许您存储由 RFC 4122 定义的通用唯一标识符。这些 UUID 值保证了比并可用于隐藏向公众公开的敏感数据（例如 URL 中的 id 值）更好的 SERIAL 唯一性。

特殊数据类型

box 矩形盒子
line 一组点
point 一对几何数字
lseg 线段
polygon 封闭的几何形状
inet IP4地址
macaddr mac地址

使用

分页

LIMIT n: 获取n行数据 OFFSET n: 跳过n行数据

示例: 跳过前10条, 获取第11到第20条记录

SELECT *
FROM users
LIMIT 10
OFFSET 10

排序

名为employees的表，你想按last_name列的字母顺序对员工进行排序: ASC: 升序, 默认 DESC: 降序

SELECT * 
FROM employees 
ORDER BY last_name 
ASC;

2.按多个列排序，例如先按last_name排序，再按first_name排序:

SELECT *
FROM users
ORDER BY last_name, first_name,

列操作

ALTER 对表添加新列

ALTER TABLE videos ADD COLUMN author jsonb;

COPY

复制

从文本文件中装载大量数据。这种方式通常更快，因为COPY命令就是为这类应用优化的，只是比 INSERT少一些灵活性语法:

COPY <TABLE> FORM '<filepath>'

SELECT

选择

语法:

SELECT <> FROM <tablename>

示例1: 任意表达式: 将温度最高度与最低取平均值,并命名为temp_avg

SELECT city,(temp_hi+temp_lo)/2 AS temp_avg, date FROM weather

WHERE

条件

AND 与
OR 或
NOT 非

将温度最高度与最低取平均值且temp_lo为 30 的与temp_hi大于20 的行,并命名为temp_avg,

SELECT city,(temp_hi+temp_lo)/2 AS temp_avg, date FROM weather
	WHERE temp_lo = 30 AND temp_hi > 20

排序

ORDER BY 排序,根据时间,数字,字符串等此类可排序的类型进行排序,默认从小到大

消除重复值

DISTINCT

注: DISTINCT消除的重复值要与排序的值相同

SELECT DISTINCT city FROM weather
	ORDER BY city

INSTERT

向表添加新行语法:

INSTERT INTO <tablename> [(key1,key2,keyN)] 
VALUES (value1,value2,valueN),(value1,value2,valueN),...
[RETURNING] <Colname>

除了数字类型,其他类型都使用单引号''来包裹
point类型的Value 值为'(-1.0,43.0)'

[RETURNING] 输出插入的值

使用*可返回所有插入的值
使用插入的列名可返回对应列值

Create

创建数据库

create [if not exitis] database <DATABASE>

创建表

create table <TABLE> ()

Delete

删除数据库

drop database <DATABASE>

删除表

drop table <table>

列​

计算列​

自增长列​

函数​

聚合函数​

适用于字符串类型的函数​

适用于数值类型的函数​

算数运算符​

比较数学运算符​

键​

主键Primary Key​

外键Foreign Key​

类型​

数值​

伪类型​

字符​

时态​

数组​

JSON​

UUID​

特殊数据类型​

使用​

分页​

排序​

列操作​

COPY​

SELECT​

WHERE​

INSTERT​

Create​

Delete​

列

计算列