Skip to main content

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(): 相加

算数运算符​

  • /
  • %

比较数学运算符​

  1. = 是否等于
  2. > 是否
  3. < 是否小于
  4. >= 是否大于和等于
  5. <= 是否小于和等于
  6. IN 是否在这个列里 在aera列中包含('Shanghai', 'Delin')的数据
SELECT name, aera
FROM city
WHERE aera IN ('Shanghai', 'Delin')
  1. NOT IN 是否不在这个list里: 排除name在'Shanghai', 'Delin'
SELECT name, aera
FROM city
WHERE name NOT IN ('Shanghai', 'Delin')
  1. <> 值是否不相等
  2. != 值是否不相等
  3. 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)
);

类型​

数值​

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

伪类型​

自动生成唯一的key

关键字长度范围
smallserial2字节1 到 32，767
serial4字节1 到 2，147，483，647
bigserial8字节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地址

使用​

列操作​

  • 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] 输出插入的值

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

Create​

创建数据库

create [if not exitis] database <DATABASE>

创建表

create table <TABLE> ()

Delete​

删除数据库

drop database <DATABASE>

删除表

drop table <table>