postgreSQL笔记-语句速查 （自用）说明参考材料基本概念数据库、表格、数据的建立、删除、修改个案选取：SELECT视图：VIEW日期和时间数据空值：NULL

文章目录

• 说明
• 参考材料
• 基本概念
• 数据库、表格、数据的建立、删除、修改
• • 数据库
  • 表格
  • 行 （数据/个案）
  • 列 （字段）
  • 索引
  • 约束
• 个案选取：``SELECT``
• • 多表查询：``JOIN``
• 视图：``VIEW``
• 日期和时间数据
• 空值：``NULL``
• 基础函数
• 分类汇总
• • ``GROUP BY``
  • ``HAVING``

说明

从大型数据库提取数据并进行分析是非常具体的工作。

考虑到同时进行的临床及其他科研工作的繁重性，顺序遍历教程 （即便入门教程）并手打、测试所有代码，既无必要，也耗时间。

因此建立该速查，仅用于极为扼要地记录语句用途、应用时的重要说明。

参考材料

W3school sql教程

基本概念

• SQL：结构化查询语言，用于访问数据库
• RDBMS：关系型数据库管理系统，其各自运用的SQL语言有一些差别
  • e.g. MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase…

数据库、表格、数据的建立、删除、修改

数据库

• 创建数据库：

  CREATE DATABASE ...

• 删除数据库：

  DROP DATABASE ...

表格

• 创建表格：

  CREATE TABLE

  • 数据类型：整数

    int

    ，小数

    decimal(..., ...)

    字符串

    varchar(...)

    ，日期

    date

    …

CREATE TABLE 表名
(
列名1 数据类型,
列名2 数据类型,
...
)
           

• 清空表格数据

  • DELETE * FROM ...

  • TRUNCATE TABLE ...

• 删除表格 （包括表格结构）：

  DROP TABLE ...

行 （数据/个案）

• 插入新的行
  • 字段齐：

    INSERT INTO ... VALUES (..., ..., ..., ...)

  • 字段不齐：

    INSERT INTO ... (..., ..., ...) VALUES (..., ..., ...)

• 修改现有数据：

  UPDATE ... SET ...=... WHERE ...

• 删除行：

  DELETE FROM ... WHERE ...

列 （字段）

• 添加列：

  ALTER TABLE ... ADD COLUMN 列名称 数据类型

• 删除列：

  ALTER TABLE ... DROP COLUMN ...

• 改变列数据类型：

  ALTER TABLE ... ALTER COLUMN 列名称 数据类型

索引

• 创建索引：

  CREATE INDEX ... ON ... (..., ..., ...)

• 删除索引：

  DROP INDEX ... ON ...

约束

• 约束：非空

  NOT NULL

  ，唯一

  UNIQUE

  ，主键

  PRIMARY KEY

  ，外键

  FOREIGN KEY

  ，取值范围

  CHECK

  ，默认值

  DEFAULT

• 自增 （特殊类型的主键，会自动生成唯一的值，每插入一个数据，就自动+1）：在创建表格时在列数据类型后声明

  AUTO_INCREMENT

  即可

个案选取：

SELECT

• 基本语法：

  SELECT ... FROM ... WHERE ...

• 将生成的数据集保存为另一个表：

  SELECT ... INTO ... FROM ... WHERE ...

• 仅选取不含重复值的数据：

  SELECT DISTINCT ... FROM ...

• 使用逻辑运算符构造复杂表达式

  • SELECT ... FROM ... WHERE ... AND ...

  • SELECT ... FROM ... WHERE ... OR ...

  • 使用圆括号组成复杂表达式：

    SELECT ... FROM ... WHERE (... OR ...) AND ...

• 排序：

  SELECT ... FROM ... WHERE ... ORDER BY ...

• 仅返回最开始若干条数据：

  SELECT ... FROM ... WHERE ... LIMIT ...

• 选取取值在某一集合内的数据

  • SELECT ... FROM ... WHERE ... IN (..., ..., ..., ...)

  • SELECT ... FROM ... WHERE ... BETWEEN ... AND ...

• 选取取值符合某一特征的数据：

  SELECT ... FROM ... WHERE ... LIKE ...

• 通配符：

  %

  代表≥1个任意字符，

  _

  代表1个任意字符，

  [...]

  代表方框中的任意字符，

  [!...]

  代表不在方框中的任意字符
• 建立别名并在结果集中使用
  • 列别名：

    SELECT ... AS ... FROM ... WHERE ...

  • 表别名：

    SELECT ... FROM ... AS ... WHERE ...

• 从几个不同的表抓取数据合并在一起：

  UNION

  • SELECT ... FROM ... UNION SELECT ... FROM ...

  • 必须有相同的列数、列数据属性，列名遵循第1个表的列名

多表查询：

JOIN

• INNER JOIN：取交集
• FULL JOIN：取并集
• LEFT JOIN：右加入到左
• RIGHT JOIN：左加入到右

SELECT ...
FROM ... INNER/FULL/LEFT/RIGHT JOIN ... # 指定参与索引的两个表
ON ...=... # 指定相互索引的列名
WHERE ...
           

视图：

VIEW

• 创建视图：

  CREATE VIEW AS ... SELECT ... FROM ... WHERE ...

• 更新视图：

  REPLACE VIEW AS ... SELECT ... FROM ... WHERE ...

• 查询视图：

  SELECT * FROM 视图名

  • 类似地，也可使用

    WHERE

    执行条件查询
• 删除视图：

  DROP VIEW ...

日期和时间数据

• 提取日期部分：

  DATE(...)

• 提取日期、时间数据的单独部分：

  EXTRACT(时间单位 FROM ...)

• 加上/减去特定时长：

  DATE_ADD/DATE_SUB(..., INTERVAL 时间数值 时间单位)

• 计算两个日期之间的天数：

  DATEDIFF(..., ...)

  （仅日期部分参与运算）
• 更换日期、时间输出形式：

  DATE_FORMAT(..., 输出形式)

空值：

NULL

• 查找空值：

  SELECT ... FROM ... WHERE ... IS NULL

• 查找非空值：

  SELECT ... FROM ... WHERE ... IS NOT NULL

• 定义空值参与运算时的替代方案

  • ISNULL(), NVL(), IFNULL(), COALESCE()

  • 通用语法：

    ISNULL/NVL/IFNULL/COALESCE(变量,为NULL时的替代值)

基础函数

• 均值：

  AVG()

  • 计算均值：

    SELECT AVG(...) AS ... FROM ...

  • 均值参与逻辑表达式：

    SELECT ... FROM ... WHERE f(SELECT AVG(...) FROM ...)

    （其中均值的表述必须完整写为

    SELECT AVG(...) FROM ...

    而不能简单写为

    AVG(...)

    ）
• 计数：

  COUNT()

  • 计算指定列的数据数 （空值不算）：

    SELECT COUNT(...) FROM ...

  • 计算指定列非重复数据数：

    SELECT COUNT(DISTINCT ...) FROM ...

  • 计算所有个案数：

    SELECT COUNT(*) FROM ...

  • 计算符合特定条件的个案数，加上

    WHERE

    限定即可
• 四舍五入：

  ROUND(列名,保留小数位数)

• 返回记录中第一个值/最后一个值：

  SELECT FIRST/LAST(...) FROM ... ORDER BY ...

• 返回最大/最小值：

  SELECT MAX/MIN(...) FROM ...

• 求和：

  SELECT SUM(...) FROM ...

• 全部转小写/大写：

  LCASE/UCASE()

• 返回字符串长度：

  LEN()

• 字符串中提取子字符串：

  MID()

  • SELECT MID(列名,起始字符位置,字符串长度) FROM ...

  • 若不声明字符串长度，则默认提取剩下全部内容

分类汇总

GROUP BY

SELECT 列名,函数名
FROM 表名
GROUP BY 列名
           

举例：按性别统计汇总订单总数：

SELECT Sex,SUM(OrderPrice) FROM Table GROUP BY Sex

HAVING

相当于

GROUP BY

中对最终结果加以限定的

WHERE

语句。

• 限定满足一定条件的个案加入计算用

  WHERE

• 限定计算结果满足一定条件才输出用

  HAVING

SELECT 列名,函数名
FROM 表名
WHERE 列限定条件
GROUP BY 列名
HAVING 函数输出结果限定条件