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地址

使用​

分页​

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

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

SELECT *
FROM users LIMIT 10
OFFSET 10

排序​

  1. 名为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] 输出插入的值

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

Create​

创建数据库

create
[if not exitis] database <DATABASE>

创建表

create table <TABLE> ()

Delete​

删除数据库

drop
database <DATABASE>

删除表

drop table < table >