PostgreSQL 教程
PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)
PostgreSQL 开发者把它念作 post-gress-Q-L
语法
postgres=# \help SELECT
Command: SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ * | expression [ [ AS ] output_name ] [, ...] ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
from_item 可以是以下选项之一：
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
数据类型
数值类型
数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。
下表列出了可用的数值类型。
| 名字 | 存储长度 | 描述 | 范围 |
|---|---|---|---|
| smallint | 2 字节 | 小范围整数 | -32768 到 +32767 |
| integer | 4 字节 | 常用的整数 | -2147483648 到 +2147483647 |
| bigint | 8 字节 | 大范围整数 | -9223372036854775808 到 +9223372036854775807 |
| decimal | 可变长 | 用户指定的精度，精确 | 小数点前 131072 位；小数点后 16383 位 |
| numeric | 可变长 | 用户指定的精度，精确 | 小数点前 131072 位；小数点后 16383 位 |
| real | 4 字节 | 可变精度，不精确 | 6 位十进制数字精度 |
| double precision | 8 字节 | 可变精度，不精确 | 15 位十进制数字精度 |
| smallserial | 2 字节 | 自增的小范围整数 | 1 到 32767 |
| serial | 4 字节 | 自增整数 | 1 到 2147483647 |
| bigserial | 8 字节 | 自增的大范围整数 | 1 到 9223372036854775807 |
货币类型
money 类型存储带有固定小数精度的货币金额。
numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。
| 名字 | 存储容量 | 描述 | 范围 |
|---|---|---|---|
| money | 8 字节 | 货币金额 | -92233720368547758.08 到 +92233720368547758.07 |
字符类型
下表列出了 PostgreSQL 所支持的字符类型：
| 序号 | 名字 & 描述 |
|---|---|
| 1 | **character varying(n), varchar(n)**变长，有长度限制 |
| 2 | **character(n), char(n)**f定长,不足补空白 |
| 3 | text变长，无长度限制 |
日期/时间类型
下表列出了 PostgreSQL 支持的日期和时间类型。
| 名字 | 存储空间 | 描述 | 最低值 | 最高值 | 分辨率 |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8 字节 | 日期和时间(无时区) | 4713 BC | 294276 AD | 1 毫秒 / 14 位 |
| timestamp [ (p) ] with time zone | 8 字节 | 日期和时间，有时区 | 4713 BC | 294276 AD | 1 毫秒 / 14 位 |
| date | 4 字节 | 只用于日期 | 4713 BC | 5874897 AD | 1 天 |
| time [ (p) ] [ without time zone ] | 8 字节 | 只用于一日内时间 | 00:00:00 | 24:00:00 | 1 毫秒 / 14 位 |
| time [ (p) ] with time zone | 12 字节 | 只用于一日内时间，带时区 | 00:00:00+1459 | 24:00:00-1459 | 1 毫秒 / 14 位 |
| interval [ fields ] [ (p) ] | 12 字节 | 时间间隔 | -178000000 年 | 178000000 年 | 1 毫秒 / 14 位 |
布尔类型
PostgreSQL 支持标准的 boolean 数据类型。
boolean 有"true"(真)或"false"(假)两个状态， 第三种"unknown"(未知)状态，用 NULL 表示。
| 名称 | 存储格式 | 描述 |
|---|---|---|
| boolean | 1 字节 | true/false |
枚举类型
枚举类型是一个包含静态和值的有序集合的数据类型。
PostgtesSQL中的枚举类型类似于 C 语言中的 enum 类型。
与其他类型不同的是枚举类型需要使用 CREATE TYPE 命令创建。
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
创建一周中的几天，如下所示:
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
就像其他类型一样，一旦创建，枚举类型可以用于表和函数定义。
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
name | current_mood
------+--------------
Moe | happy
(1 row)
几何类型
几何数据类型表示二维的平面物体。
下表列出了 PostgreSQL 支持的几何类型。
最基本的类型：点。它是其它类型的基础。
| 名字 | 存储空间 | 说明 | 表现形式 |
|---|---|---|---|
| point | 16 字节 | 平面中的点 | (x,y) |
| line | 32 字节 | (无穷)直线(未完全实现) | ((x1,y1),(x2,y2)) |
| lseg | 32 字节 | (有限)线段 | ((x1,y1),(x2,y2)) |
| box | 32 字节 | 矩形 | ((x1,y1),(x2,y2)) |
| path | 16+16n 字节 | 闭合路径(与多边形类似) | ((x1,y1),…) |
| path | 16+16n 字节 | 开放路径 | [(x1,y1),…] |
| polygon | 40+16n 字节 | 多边形(与闭合路径相似) | ((x1,y1),…) |
| circle | 24 字节 | 圆 | <(x,y),r> (圆心和半径) |
网络地址类型
PostgreSQL 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型。
用这些数据类型存储网络地址比用纯文本类型好， 因为这些类型提供输入错误检查和特殊的操作和功能。
| 名字 | 存储空间 | 描述 |
|---|---|---|
| cidr | 7 或 19 字节 | IPv4 或 IPv6 网络 |
| inet | 7 或 19 字节 | IPv4 或 IPv6 主机和网络 |
| macaddr | 6 字节 | MAC 地址 |
在对 inet 或 cidr 数据类型进行排序的时候， IPv4 地址总是排在 IPv6 地址前面，包括那些封装或者是映射在 IPv6 地址里的 IPv4 地址， 比如 ::10.2.3.4 或 ::ffff:10.4.3.2。
位串类型
位串就是一串 1 和 0 的字符串。它们可以用于存储和直观化位掩码。 我们有两种 SQL 位类型：bit(n) 和bit varying(n)， 这里的n是一个正整数。
bit 类型的数据必须准确匹配长度 n， 试图存储短些或者长一些的数据都是错误的。bit varying 类型数据是最长 n 的变长类型；更长的串会被拒绝。 写一个没有长度的bit 等效于 bit(1)， 没有长度的 bit varying 意思是没有长度限制。
文本搜索类型
全文检索即通过自然语言文档的集合来找到那些匹配一个查询的检索。
PostgreSQL 提供了两种数据类型用于支持全文检索：
| 序号 | 名字 & 描述 |
|---|---|
| 1 | tsvectortsvector 的值是一个无重复值的 lexemes 排序列表， 即一些同一个词的不同变种的标准化。 |
| 2 | tsquerytsquery 存储用于检索的词汇，并且使用布尔操作符 &(AND)，|(OR)和!(NOT) 来组合它们，括号用来强调操作符的分组。 |
JSON 类型
json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据， 这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。
此外还有相关的函数来处理 json 数据：
| 实例 | 实例结果 |
|---|---|
| array_to_json(‘{{1,5},{99,100}}’::int[]) | [[1,5],[99,100]] |
| row_to_json(row(1,‘foo’)) | {“f1”:1,“f2”:“foo”} |
数组类型
PostgreSQL 允许将字段定义成变长的多维数组。
数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。
声明数组
创建表的时候，我们可以声明数组，方式如下：
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。
我们也可以使用 “ARRAY” 关键字，如下所示：
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer ARRAY[4],
schedule text[][]
);
插入值
插入值使用花括号 {}，元素在 {} 使用逗号隔开：
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
访问数组
现在我们可以在这个表上运行一些查询。
首先，我们演示如何访问数组的一个元素。 这个查询检索在第二季度薪水变化的雇员名：
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
name
-------
Carol
(1 row)
数组的下标数字是写在方括弧内的。
修改数组
我们可以对数组的值进行修改：
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';
或者使用 ARRAY 构造器语法：
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Carol';
数组中检索
要搜索一个数组中的数值，你必须检查该数组的每一个值。
比如：
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
pay_by_quarter[2] = 10000 OR
pay_by_quarter[3] = 10000 OR
pay_by_quarter[4] = 10000;
另外，你可以用下面的语句找出数组中所有元素值都等于 10000 的行：
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
或者，可以使用 generate_subscripts 函数。例如：
SELECT * FROM
(SELECT pay_by_quarter,
generate_subscripts(pay_by_quarter, 1) AS s
FROM sal_emp) AS foo
WHERE pay_by_quarter[s] = 10000;
复合类型
复合类型表示一行或者一条记录的结构； 它实际上只是一个字段名和它们的数据类型的列表。PostgreSQL 允许像简单数据类型那样使用复合类型。比如，一个表的某个字段可以声明为一个复合类型。
声明复合类型
下面是两个定义复合类型的简单例子：
CREATE TYPE complex AS (
r double precision,
i double precision
);
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);
语法类似于 CREATE TABLE，只是这里只可以声明字段名字和类型。
定义了类型，我们就可以用它创建表：
CREATE TABLE on_hand (
item inventory_item,
count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
复合类型值输入
要以文本常量书写复合类型值，在圆括弧里包围字段值并且用逗号分隔他们。 你可以在任何字段值周围放上双引号，如果值本身包含逗号或者圆括弧， 你必须用双引号括起。
复合类型常量的一般格式如下：
'( val1 , val2 , ... )'
一个例子是:
'("fuzzy dice",42,1.99)'
访问复合类型
要访问复合类型字段的一个域，我们写出一个点以及域的名字， 非常类似从一个表名字里选出一个字段。实际上，因为实在太像从表名字中选取字段， 所以我们经常需要用圆括弧来避免分析器混淆。比如，你可能需要从on_hand 例子表中选取一些子域，像下面这样：
SELECT item.name FROM on_hand WHERE item.price > 9.99;
这样将不能工作，因为根据 SQL 语法，item是从一个表名字选取的， 而不是一个字段名字。你必须像下面这样写：
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
或者如果你也需要使用表名字(比如，在一个多表查询里)，那么这么写：
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
现在圆括弧对象正确地解析为一个指向item字段的引用，然后就可以从中选取子域。
范围类型
范围数据类型代表着某一元素类型在一定范围内的值。
例如，timestamp 范围可能被用于代表一间会议室被预定的时间范围。
PostgreSQL 内置的范围类型有：
- int4range — integer的范围
- int8range —bigint的范围
- numrange —numeric的范围
- tsrange —timestamp without time zone的范围
- tstzrange —timestamp with time zone的范围
- daterange —date的范围
此外，你可以定义你自己的范围类型。
CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
(1108, '[2010-01-01 14:30, 2010-01-01 15:30)');
-- 包含
SELECT int4range(10, 20) @> 3;
-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
-- 提取上边界
SELECT upper(int8range(15, 25));
-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);
-- 范围是否为空
SELECT isempty(numrange(1, 5));
范围值的输入必须遵循下面的格式：
(下边界,上边界)
(下边界,上边界]
[下边界,上边界)
[下边界,上边界]
空
圆括号或者方括号显示下边界和上边界是不包含的还是包含的。注意最后的格式是 空，代表着一个空的范围（一个不含有值的范围）。
-- 包括3，不包括7，并且包括二者之间的所有点
SELECT '[3,7)'::int4range;
-- 不包括3和7，但是包括二者之间所有点
SELECT '(3,7)'::int4range;
-- 只包括单一值4
SELECT '[4,4]'::int4range;
-- 不包括点（被标准化为‘空’）
SELECT '[4,4)'::int4range;
对象标识符类型
PostgreSQL 在内部使用对象标识符(OID)作为各种系统表的主键。
同时，系统不会给用户创建的表增加一个 OID 系统字段(除非在建表时声明了WITH OIDS 或者配置参数default_with_oids设置为开启)。oid 类型代表一个对象标识符。除此以外 oid 还有几个别名：regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig, 和regdictionary。
| 名字 | 引用 | 描述 | 数值例子 |
|---|---|---|---|
| oid | 任意 | 数字化的对象标识符 | 564182 |
| regproc | pg_proc | 函数名字 | sum |
| regprocedure | pg_proc | 带参数类型的函数 | sum(int4) |
| regoper | pg_operator | 操作符名 | + |
| regoperator | pg_operator | 带参数类型的操作符 | *(integer,integer) 或 -(NONE,integer) |
| regclass | pg_class | 关系名 | pg_type |
| regtype | pg_type | 数据类型名 | integer |
| regconfig | pg_ts_config | 文本搜索配置 | english |
| regdictionary | pg_ts_dict | 文本搜索字典 | simple |
伪类型
PostgreSQL类型系统包含一系列特殊用途的条目， 它们按照类别来说叫做伪类型。伪类型不能作为字段的数据类型， 但是它可以用于声明一个函数的参数或者结果类型。 伪类型在一个函数不只是简单地接受并返回某种SQL 数据类型的情况下很有用。
下表列出了所有的伪类型：
| 名字 | 描述 |
|---|---|
| any | 表示一个函数接受任何输入数据类型。 |
| anyelement | 表示一个函数接受任何数据类型。 |
| anyarray | 表示一个函数接受任意数组数据类型。 |
| anynonarray | 表示一个函数接受任意非数组数据类型。 |
| anyenum | 表示一个函数接受任意枚举数据类型。 |
| anyrange | 表示一个函数接受任意范围数据类型。 |
| cstring | 表示一个函数接受或者返回一个空结尾的 C 字符串。 |
| internal | 表示一个函数接受或者返回一种服务器内部的数据类型。 |
| language_handler | 一个过程语言调用处理器声明为返回language_handler。 |
| fdw_handler | 一个外部数据封装器声明为返回fdw_handler。 |
| record | 标识一个函数返回一个未声明的行类型。 |
| trigger | 一个触发器函数声明为返回trigger。 |
| void | 表示一个函数不返回数值。 |
| opaque | 一个已经过时的类型，以前用于所有上面这些用途。 |
数据库创建及删除
PostgreSQL 创建数据库可以用以下三种方式：
- 1、使用 CREATE DATABASE SQL 语句来创建。
- 2、使用 createdb 命令来创建。
- 3、使用 pgAdmin 工具。
CREATE DATABASE 创建数据库
CREATE DATABASE 命令需要在 PostgreSQL 命令窗口来执行，语法格式如下：
CREATE DATABASE dbname;
例如，我们创建一个 runoobdb 的数据库：
postgres=# CREATE DATABASE runoobdb;
createdb 命令创建数据库
createdb 是一个 SQL 命令 CREATE DATABASE 的封装。
createdb 命令语法格式如下：
createdb [option...] [dbname [description]]
参数说明：
dbname：要创建的数据库名。
description：关于新创建的数据库相关的说明。
options：参数可选项，可以是以下值：
| 序号 | 选项 & 描述 |
|---|---|
| -D tablespace | 指定数据库默认表空间。 |
| -e | 将 createdb 生成的命令发送到服务端。 |
| -E encoding | 指定数据库的编码。 |
| -l locale | 指定数据库的语言环境。 |
| -T template | 指定创建此数据库的模板。 |
| –help | 显示 createdb 命令的帮助信息。 |
| -h host | 指定服务器的主机名。 |
| -p port | 指定服务器监听的端口，或者 socket 文件。 |
| -U username | 连接数据库的用户名。 |
| -w | 忽略输入密码。 |
| -W | 连接时强制要求输入密码。 |
接下来我们打开一个命令窗口，进入到 PostgreSQL 的安装目录，并进入到 bin 目录，createdb 命令位于 PostgreSQL安装目录/bin 下，执行创建数据库的命令：
$ cd /Library/PostgreSQL/11/bin/
$ createdb -h localhost -p 5432 -U postgres runoobdb
password ******
PostgreSQL 删除数据库可以用以下三种方式：
- 1、使用 DROP DATABASE SQL 语句来删除。
- 2、使用 dropdb 命令来删除。
- 3、使用 pgAdmin 工具。
表格创建及删除
PostgreSQL 使用 CREATE TABLE 语句来创建数据库表格。
语法
CREATE TABLE 语法格式如下：
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( 一个或多个列 )
);
CREATE TABLE 是一个关键词，用于告诉数据库系统将创建一个数据表。
表名字必需在同一模式中的其它表、 序列、索引、视图或外部表名字中唯一。
CREATE TABLE 在当前数据库创建一个新的空白表，该表将由发出此命令的用户所拥有。
表格中的每个字段都会定义数据类型，如下：
实例
以下创建了一个表，表名为 COMPANY 表格，主键为 ID，NOT NULL 表示字段不允许包含 NULL 值：
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
接下来我们再创建一个表格，在后面章节会用到：
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
我们可以使用 \d 命令来查看表格是否创建成功：
runoobdb=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | company | table | postgres
public | department | table | postgres
(2 rows)
\d tablename 查看表格信息：
runoobdb=# \d company
Table "public.company"
Column | Type | Collation | Nullable | Default
---------+---------------+-----------+----------+---------
id | integer | | not null |
name | text | | not null |
age | integer | | not null |
address | character(50) | | |
salary | real | | |
Indexes:
"company_pkey" PRIMARY KEY, btree (id)
PostgreSQL 使用 DROP TABLE 语句来删除表格，包含表格数据、规则、触发器等，所以删除表格要慎重，删除后所有信息就消失了。
语法
DROP TABLE 语法格式如下：
DROP TABLE table_name;
PostgreSQL 模式（SCHEMA）
PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。
一个模式可以包含视图、索引、数据类型、函数和操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。
使用模式的优势：
- 允许多个用户使用一个数据库并且不会互相干扰。
- 将数据库对象组织成逻辑组以便更容易管理。
- 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
模式类似于操作系统层的目录，但是模式不能嵌套。
INSERT INTO 语句
PostgreSQL INSERT INTO 语句用于向表中插入新记录。
我们可以插入一行也可以同时插入多行。
语法
INSERT INTO 语句语法格式如下：
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
- column1, column2,…columnN 为表中字段名。
- value1, value2, value3,…valueN 为字段对应的值。
在使用 INSERT INTO 语句时，字段列必须和数据值数量相同，且顺序也要对应。
如果我们向表中的所有字段插入值，则可以不需要指定字段，只需要指定插入的值即可：
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
SELECT 语句
PostgreSQL SELECT 语句用于从数据库中选取数据。
结果被存储在一个结果表中，称为结果集。
语法
SELECT 语句语法格式如下：
SELECT column1, column2,...columnN FROM table_name;
- column1, column2,…columnN 为表中字段名。
- table_name 为表名。
运算符
运算符是一种告诉编译器执行特定的数学或逻辑操作的符号。
PostgreSQL 运算符是一个保留关键字或字符，一般用在 WHERE 语句中，作为过滤条件。
常见的运算符有：
- 算术运算符
- 比较运算符
- 逻辑运算符
- 按位运算符
算术运算符
假设变量 a 为 2，变量 b 为 3，则：
| 运算符 | 描述 | 实例 |
|---|---|---|
| + | 加 | a + b 结果为 5 |
| - | 减 | a - b 结果为 -1 |
| * | 乘 | a * b 结果为 6 |
| / | 除 | b / a 结果为 1 |
| % | 模（取余） | b % a 结果为 1 |
| ^ | 指数 | a ^ b 结果为 8 |
| |/ | 平方根 | |/ 25.0 结果为 5 |
| ||/ | 立方根 | ||/ 27.0 结果为 3 |
| ! | 阶乘 | 5 ! 结果为 120 |
| !! | 阶乘（前缀操作符） | !! 5 结果为 120 |
比较运算符
假设变量 a 为 10，变量 b 为 20，则：
| 运算符 | 描述 | 实例 |
|---|---|---|
| = | 等于 | (a = b) 为 false。 |
| != | 不等于 | (a != b) 为 true。 |
| <> | 不等于 | (a <> b) 为 true。 |
| > | 大于 | (a > b) 为 false。 |
| < | 小于 | (a < b) 为 true。 |
| >= | 大于等于 | (a >= b) 为 false。 |
| <= | 小于等于 | (a <= b) 为 true。 |
逻辑运算符
PostgreSQL 逻辑运算符有以下几种：
| 序号 | 运算符 & 描述 |
|---|---|
| 1 | AND逻辑与运算符。如果两个操作数都非零，则条件为真。PostgresSQL 中的 WHERE 语句可以用 AND 包含多个过滤条件。 |
| 2 | NOT逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。PostgresSQL 有 NOT EXISTS, NOT BETWEEN, NOT IN 等运算符。 |
| 3 | OR逻辑或运算符。如果两个操作数中有任意一个非零，则条件为真。PostgresSQL 中的 WHERE 语句可以用 OR 包含多个过滤条件。 |
SQL 使用三值的逻辑系统，包括 true、false 和 null，null 表示"未知"。
a | b | a AND b | a OR b |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| TRUE | NULL | NULL | TRUE |
| FALSE | FALSE | FALSE | FALSE |
| FALSE | NULL | FALSE | NULL |
| NULL | NULL | NULL | NULL |
a | NOT a |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| NULL | NULL |
位运算符
位运算符作用于位，并逐位执行操作。&、 | 和 ^ 的真值表如下所示：
| p | q | p & q | p | q |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |
表达式
表达式是由一个或多个的值、运算符、PostgresSQL 函数组成的。
PostgreSQL 表达式类似一个公式，我们可以将其应用在查询语句中，用来查找数据库中指定条件的结果集。
语法
SELECT 语句的语法格式如下：
SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];
WHERE 子句
在 PostgreSQL 中，当我们需要根据指定条件从单张表或者多张表中查询数据时，就可以在 SELECT 语句中添加 WHERE 子句，从而过滤掉我们不需要数据。
WHERE 子句不仅可以用于 SELECT 语句中，同时也可以用于 UPDATE，DELETE 等等语句中。
语法
以下是 SELECT 语句中使用 WHERE 子句从数据库中读取数据的通用语法：
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1]
我们可以在 WHERE 子句中使用比较运算符或逻辑运算符，例如 >, <, =, LIKE, NOT 等等。
创建 COMPANY 表（下载 COMPANY SQL 文件 ），数据内容如下：
runoobdb# select * from COMPANY;
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
(7 rows)
AND & OR 运算符
在 PostgreSQL 中，AND 和 OR 也叫连接运算符，在查询数据时用于缩小查询范围，我们可以用 AND 或者 OR 指定一个或多个查询条件。
AND
AND 运算符表示一个或者多个条件必须同时成立。
在 WHERE 子句中，AND 的使用语法如下：
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];
OR
OR 运算符表示多个条件中只需满足其中任意一个即可。
在 WHERE 子句中，OR 的使用语法如下：
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]
UPDATE 语句
如果我们要更新在 PostgreSQL 数据库中的数据，我们可以用 UPDATE 来操作。
语法
以下是 UPDATE 语句修改数据的通用 SQL 语法：
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
- 我们可以同时更新一个或者多个字段。
- 我们可以在 WHERE 子句中指定任何条件。
LIKE 子句
在 PostgreSQL 数据库中，我们如果要获取包含某些字符的数据，可以使用 LIKE 子句。
在 LIKE 子句中，通常与通配符结合使用，通配符表示任意字符，在 PostgreSQL 中，主要有以下两种通配符：
- 百分号 %
- 下划线 _
如果没有使用以上两种通配符，LIKE 子句和等号 = 得到的结果是一样的。
语法
以下是使用 LIKE 子句搭配百分号 % 和下划线 _ 从数据库中获取数据的通用语法：
SELECT FROM table_name WHERE column LIKE 'XXXX%';
或者
SELECT FROM table_name WHERE column LIKE '%XXXX%';
或者
SELECT FROM table_name WHERE column LIKE 'XXXX_';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX_';
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
XXXX 可以是任何数字或者字符。
LIMIT 子句
PostgreSQL 中的 limit 子句用于限制 SELECT 语句中查询的数据的数量。
语法
带有 LIMIT 子句的 SELECT 语句的基本语法如下：
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]
ORDER BY 语句
在 PostgreSQL 中，ORDER BY 用于对一列或者多列数据进行升序（ASC）或者降序（DESC）排列。
语法
ORDER BY 子句的基础语法如下：
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
您可以在 ORDER BY 中使用一列或者多列，但是必须保证要排序的列必须存在。
ASC 表示升序，DESC 表示降序。
GROUP BY 语句
在 PostgreSQL 中，GROUP BY 语句和 SELECT 语句一起使用，用来对相同的数据进行分组。
GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。
语法
下面给出了 GROUP BY 子句的基本语法:
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。
在 GROUP BY 子句中，你可以对一列或者多列进行分组，但是被分组的列必须存在于列清单中。
WITH 子句
在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。
WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表。
WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它。
WITH 子句在使用前必须先定义。
语法
WITH 查询的基础语法如下：
WITH
name_for_summary_data AS (
SELECT Statement)
SELECT columns
FROM name_for_summary_data
WHERE conditions <=> (
SELECT column
FROM name_for_summary_data)
[ORDER BY columns]
name_for_summary_data 是 WITH 子句的名称，name_for_summary_data 可以与现有的表名相同，并且具有优先级。
可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许您在同一个查询中执行多个不同的操作。
WITH 递归
在 WITH 子句中可以使用自身输出的数据。
公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
HAVING 子句
HAVING 子句可以让我们筛选分组后的各组数据。
WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
语法
下面是 HAVING 子句在 SELECT 查询中的位置：
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 语句中基础语法：
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
DISTINCT 关键字
在 PostgreSQL 中，DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。
我们平时在操作数据时，有可能出现一种情况，在一个表中有多个重复的记录，当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。
语法
用于去除重复记录的 DISTINCT 关键字的基本语法如下：
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
约束
PostgreSQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
约束确保了数据库中数据的准确性和可靠性。
约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
以下是在 PostgreSQL 中常用的约束。
-
NOT NULL：指示某列不能存储 NULL 值。
-
UNIQUE：确保某列的值都是唯一的。
-
PRIMARY Key：在设计数据库时，PRIMARY KEY 非常重要。
PRIMARY KEY 称为主键，是数据表中每一条记录的唯一标识。
设置 UNIQUE 的列可能有多个，但是一张表只有一列可以设置 PRIMARY KEY。
我们可以使用主键来引用表中的行，也可以通过把主键设置为其他表的外键，来创建表之间的关系。
主键是非空约束和唯一约束的组合。
一个表只能有一个主键，它可以由一个或多个字段组成，当多个字段作为主键，它们被称为复合键。
如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。
NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
-
FOREIGN Key： FOREIGN KEY 即外键约束，指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。
通常一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)，即维护了两个相关表之间的引用完整性。
保证一个表中的数据匹配另一个表中的值的参照完整性。
-
CHECK： CHECK 约束保证列中的所有值满足某一条件，即对输入一条记录要进行检查。如果条件值为 false，则记录违反了约束，且不能输入到表。保证列中的值符合指定的条件。
-
EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。EXCLUSION 约束确保如果使用指定的运算符在指定列或表达式上比较任意两行，至少其中一个运算符比较将返回 false 或 null。
连接(JOIN)
PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
在 PostgreSQL 中，JOIN 有五种连接类型：
-
CROSS JOIN ：交叉连接
-
INNER JOIN：内连接
-
LEFT OUTER JOIN：左外连接 left代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。
下面是左外连接（ LEFT OUTER JOIN ）的基础语法：
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ... -
RIGHT OUTER JOIN：右外连接 right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。
下面是右外连接（ RIGHT OUT JOIN）的基本语法：
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ... -
FULL OUTER JOIN：全外连接 首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。
下面是外连接的基本语法：
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
UNION 操作符
PostgreSQL UNION 操作符合并两个或多个 SELECT 语句的结果。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
语法
UNIONS 基础语法如下：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL 子句
UNION ALL 操作符可以连接两个有重复行的 SELECT 语句，默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
语法
UINON ALL 子句基础语法如下：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
别名
我们可以用 SQL 重命名一张表或者一个字段的名称，这个名称就叫着该表或该字段的别名。
创建别名是为了让表名或列名的可读性更强。
SQL 中 使用 AS 来创建别名。
语法
表的别名语法:
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
列的别名语法:
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];
触发器
PostgreSQL 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。
下面是关于 PostgreSQL 触发器几个比较重要的点：
- PostgreSQL 触发器可以在下面几种情况下触发：
- 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
- 在执行操作之后（在检查约束并插入、更新或删除完成之后）。
- 更新操作（在对一个视图进行插入、更新、删除时）。
- 触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 FOR EACH STATEMENT，不管修改了多少行，每个语句标记的触发器执行一次。
- WHEN 子句和触发器操作在引用 NEW.column-name 和 OLD.column-name 表单插入、删除或更新时可以访问每一行元素。其中 column-name 是与触发器关联的表中的列的名称。
- 如果存在 WHEN 子句，PostgreSQL 语句只会执行 WHEN 子句成立的那一行，如果没有 WHEN 子句，PostgreSQL 语句会在每一行执行。
- BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
- 要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是 database.tablename。
- 当创建约束触发器时会指定约束选项。这与常规触发器相同，只是可以使用这种约束来调整触发器触发的时间。当约束触发器实现的约束被违反时，它将抛出异常。
语法
创建触发器时的基础语法如下：
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
-- 触发器逻辑....
];
在这里，event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作。您可以在表名后选择指定 FOR EACH ROW。
以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器的语法：
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
-- 触发器逻辑....
];
示例
runoobdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
auditlogfunc() 是 PostgreSQL 一个程序，其定义如下：
CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
RETURN NEW;
END;
$example_table$ LANGUAGE plpgsql;
列出触发器
你可以把从 pg_trigger 表中把当前数据库所有触发器列举出来：
runoobdb=# SELECT * FROM pg_trigger;
如果，你想列举出特定表的触发器，语法如下：
runoobdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';
删除触发器
删除触发器基础语法如下：
drop trigger ${trigger_name} on ${table_of_trigger_dependent};
删除本文上表 company 上的触发器 example_trigger 的指令为：
drop trigger example_trigger on company;
索引
索引是加速搜索引擎检索数据的一种特殊表查询。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。
使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。
索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。
CREATE INDEX 命令
CREATE INDEX （创建索引）的语法如下：
CREATE INDEX index_name ON table_name;
索引类型
单列索引
单列索引是一个只基于表的一个列上创建的索引，基本语法如下：
CREATE INDEX index_name
ON table_name (column_name);
组合索引
组合索引是基于表的多列上创建的索引，基本语法如下：
CREATE INDEX index_name
ON table_name (column1_name, column2_name);
不管是单列索引还是组合索引，该索引必须是在 WHERE 子句的过滤条件中使用非常频繁的列。
如果只有一列被使用到，就选择单列索引，如果有多列就使用组合索引。
唯一索引
使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：
CREATE UNIQUE INDEX index_name
on table_name (column_name);
局部索引
局部索引 是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。基础语法如下：
CREATE INDEX index_name
on table_name (conditional_expression);
隐式索引
隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。
现在，用 \d company 命令列出 COMPANY 表的所有索引：
# \d company
DROP INDEX （删除索引）
一个索引可以使用 PostgreSQL 的 DROP 命令删除。
DROP INDEX index_name;
什么情况下要避免使用索引？
虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。
使用索引时，需要考虑下列准则：
- 索引不应该使用在较小的表上。
- 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
- 索引不应该使用在含有大量的 NULL 值的列上。
- 索引不应该使用在频繁操作的列上。
ALTER TABLE 命令
在 PostgreSQL 中，ALTER TABLE 命令用于添加，修改，删除一张已经存在表的列。
另外你也可以用 ALTER TABLE 命令添加和删除约束。
用 ALTER TABLE 在一张已存在的表上添加列的语法如下：
ALTER TABLE table_name ADD column_name datatype;
在一张已存在的表上 DROP COLUMN（删除列），语法如下：
ALTER TABLE table_name DROP COLUMN column_name;
修改表中某列的 DATA TYPE（数据类型），语法如下：
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
给表中某列添加 NOT NULL 约束，语法如下：
ALTER TABLE table_name ALTER column_name datatype NOT NULL;
给表中某列 ADD UNIQUE CONSTRAINT（ 添加 UNIQUE 约束），语法如下：
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
给表中 ADD CHECK CONSTRAINT（添加 CHECK 约束），语法如下：
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
给表 ADD PRIMARY KEY（添加主键），语法如下：
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
DROP CONSTRAINT （删除约束），语法如下：
ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;
如果是 MYSQL ，代码是这样：
ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;
DROP PRIMARY KEY （删除主键），语法如下：
ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;
如果是 MYSQL ，代码是这样：
ALTER TABLE table_name
DROP PRIMARY KEY;
TRUNCATE TABLE
PostgreSQL 中 TRUNCATE TABLE 用于删除表的数据，但不删除表结构。
也可以用 DROP TABLE 删除表，但是这个命令会连表的结构一起删除，如果想插入数据，需要重新建立这张表。
TRUNCATE TABLE 与 DELETE 具有相同的效果，但是由于它实际上并不扫描表，所以速度更快。 此外，TRUNCATE TABLE 可以立即释放表空间，而不需要后续 VACUUM 操作，这在大型表上非常有用。
PostgreSQL VACUUM 操作用于释放、再利用更新/删除行所占据的磁盘空间。
语法
TRUNCATE TABLE 基础语法如下：
TRUNCATE TABLE table_name;
View（视图）
View（视图）是一张假表，只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句。
View（视图）实际上是一个以预定义的 PostgreSQL 查询形式存在的表的组合。
View（视图）可以包含一个表的所有行或从一个或多个表选定行。
View（视图）可以从一个或多个表创建，这取决于要创建视图的 PostgreSQL 查询。
View（视图）是一种虚拟表，允许用户实现以下几点：
- 用户或用户组认为更自然或直观查找结构数据的方式。
- 限制数据访问，用户只能看到有限的数据，而不是完整的表。
- 汇总各种表中的数据，用于生成报告。
PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。
CREATE VIEW（创建视图）
在 PostgreSQL 用 CREATE VIEW 语句创建视图，视图创建可以从一张表，多张表或者其他视图。
CREATE VIEW 基础语法如下：
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
您可以在 SELECT 语句中包含多个表，这与在正常的 SQL SELECT 查询中的方式非常相似。如果使用了可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。
DROP VIEW （删除视图）
要删除视图，只需使用带有 view_name 的 DROP VIEW 语句。DROP VIEW 的基本语法如下：
runoobdb=# DROP VIEW view_name;
TRANSACTION（事务）
TRANSACTION（事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。
数据库事务通常包含了一个序列的对数据库的读/写操作。包含有以下两个目的：
- 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
- 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。
当事务被提交给了数据库管理系统（DBMS），则 DBMS 需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要回滚，回到事务执行前的状态；同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。
事务的属性
事务具有以下四个标准属性，通常根据首字母缩写为 ACID：
- 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
- 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
- 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
- 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。
事务控制
使用下面的命令来控制事务：
BEGIN TRANSACTION：开始一个事务。COMMIT：事务确认，或者可以使用 END TRANSACTION 命令。ROLLBACK：事务回滚。
事务控制命令只与 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。
BEGIN TRANSACTION 命令
事务可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时，事务处理也会回滚。以下是启动一个事务的简单语法：
BEGIN;
或者
BEGIN TRANSACTION;
COMMIT 命令
COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令，即确认事务。
COMMIT 命令的语法如下：
COMMIT;
或者
END TRANSACTION;
ROLLBACK 命令
ROLLBACK 命令是用于撤消尚未保存到数据库的事务命令，即回滚事务。
ROLLBACK 命令的语法如下：
ROLLBACK;
LOCK（锁）
锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。
在多个用户访问数据库的时候若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
数据库中有两种基本的锁：排它锁（Exclusive Locks）和共享锁（Share Locks）。
如果数据对象加上排它锁，则其他的事务不能对它读取和修改。
如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。
LOCK 命令语法
LOCK 命令基础语法如下：
LOCK [ TABLE ]
name
IN
lock_mode
- name：要锁定的现有表的名称（可选模式限定）。如果只在表名之前指定，则只锁定该表。如果未指定，则锁定该表及其所有子表（如果有）。
- lock_mode：锁定模式指定该锁与哪个锁冲突。如果没有指定锁定模式，则使用限制最大的访问独占模式。可能的值是：ACCESS SHARE，ROW SHARE， ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE。
一旦获得了锁，锁将在当前事务的其余时间保持。没有解锁表命令；锁总是在事务结束时释放。
PostgreSQL 子查询
子查询或称为内部查询、嵌套查询，指的是在 PostgreSQL 查询中的 WHERE 子句中嵌入查询语句。
一个 SELECT 语句的查询结果能够作为另一个语句的输入值。
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，并可使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。
以下是子查询必须遵循的几个规则：
- 子查询必须用括号括起来。
- 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
- ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
- 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
- BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。
SELECT 中子查询使用
子查询通常与 SELECT 语句一起使用。基本语法如下：
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
INSERT 中子查询使用
子查询也可以与 INSERT 语句一起使用。INSERT 语句使用子查询返回的数据插入到另一个表中。
在子查询中所选择的数据可以用任何字符、日期或数字函数修改。
基本语法如下：
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ] ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
UPDATE 中子查询使用
子查询可以与 UPDATE 语句结合使用。当通过 UPDATE 语句使用子查询时，表中单个或多个列被更新。
基本语法如下：
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
DELETE 中子查询使用
子查询可以与 DELETE 语句结合使用，就像上面提到的其他语句一样。
基本语法如下：
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
AUTO INCREMENT（自动增长）
AUTO INCREMENT（自动增长） 会在新记录插入表中时生成一个唯一的数字。
PostgreSQL 使用序列来标识字段的自增长，数据类型有 smallserial、serial 和 bigserial 。这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。
MySQL 是用 AUTO_INCREMENT 这个属性来标识字段的自增。
PostgreSQL 使用序列来标识字段的自增长：
CREATE TABLE runoob
(
id serial NOT NULL,
alttext text,
imgurl text
)
SMALLSERIAL、SERIAL 和 BIGSERIAL 范围：
| 伪类型 | 存储大小 | 范围 |
|---|---|---|
SMALLSERIAL | 2字节 | 1 到 32,767 |
SERIAL | 4字节 | 1 到 2,147,483,647 |
BIGSERIAL | 8字节 | 1 到 922,337,2036,854,775,807 |
语法
SERIAL 数据类型基础语法如下：
CREATE TABLE tablename (
colname SERIAL
);
PRIVILEGES（权限）
无论何时创建数据库对象，都会为其分配一个所有者，所有者通常是执行 create 语句的人。
对于大多数类型的对象，初始状态是只有所有者(或超级用户)才能修改或删除对象。要允许其他角色或用户使用它，必须为该用户设置权限。
在 PostgreSQL 中，权限分为以下几种：
- SELECT
- INSERT
- UPDATE
- DELETE
- TRUNCATE
- REFERENCES
- TRIGGER
- CREATE
- CONNECT
- TEMPORARY
- EXECUTE
- USAGE
根据对象的类型(表、函数等)，将指定权限应用于该对象。
要向用户分配权限，可以使用 GRANT 命令。
GRANT 语法
GRANT 命令的基本语法如下：
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }
- privilege − 值可以为：SELECT，INSERT，UPDATE，DELETE， RULE，ALL。
- object − 要授予访问权限的对象名称。可能的对象有： table， view，sequence。
- PUBLIC − 表示所有用户。
- GROUP group − 为用户组授予权限。
- username − 要授予权限的用户名。PUBLIC 是代表所有用户的简短形式。
另外，我们可以使用 REVOKE 命令取消权限，REVOKE 语法：
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }
时间/日期函数和操作符
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
age(timestamp, timestamp) | interval | 减去参数后的"符号化"结果，使用年和月，不只是使用天 | age(timestamp '2001-04-10', timestamp '1957-06-13') | 43 years 9 mons 27 days |
age(timestamp) | interval | 从current_date减去参数后的结果（在午夜） | age(timestamp '1957-06-13') | 43 years 8 mons 3 days |
clock_timestamp() | timestamp with time zone | 实时时钟的当前时间戳（在语句执行时变化） | ||
current_date | date | 当前的日期； | ||
current_time | time with time zone | 当日时间； | ||
current_timestamp | timestamp with time zone | 当前事务开始时的时间戳； | ||
date_part(text, timestamp) | double precision | 获取子域(等效于extract)； | date_part('hour', timestamp '2001-02-16 20:38:40') | 20 |
date_part(text, interval) | double precision | 获取子域(等效于extract)； | date_part('month', interval '2 years 3 months') | 3 |
date_trunc(text, timestamp) | timestamp | 截断成指定的精度； | date_trunc('hour', timestamp '2001-02-16 20:38:40') | 2001-02-16 20:00:00 |
date_trunc(text, interval) | interval | 截取指定的精度， | date_trunc('hour', interval '2 days 3 hours 40 minutes') | 2 days 03:00:00 |
extract(field from timestamp) | double precision | 获取子域； | extract(hour from timestamp '2001-02-16 20:38:40') | 20 |
extract(field from interval) | double precision | 获取子域； | extract(month from interval '2 years 3 months') | 3 |
isfinite(date) | boolean | 测试是否为有穷日期(不是 +/-无穷) | isfinite(date '2001-02-16') | true |
isfinite(timestamp) | boolean | 测试是否为有穷时间戳(不是 +/-无穷) | isfinite(timestamp '2001-02-16 21:28:30') | true |
isfinite(interval) | boolean | 测试是否为有穷时间间隔 | isfinite(interval '4 hours') | true |
justify_days(interval) | interval | 按照每月 30 天调整时间间隔 | justify_days(interval '35 days') | 1 mon 5 days |
justify_hours(interval) | interval | 按照每天 24 小时调整时间间隔 | justify_hours(interval '27 hours') | 1 day 03:00:00 |
justify_interval(interval) | interval | 使用justify_days和justify_hours调整时间间隔的同时进行正负号调整 | justify_interval(interval '1 mon -1 hour') | 29 days 23:00:00 |
localtime | time | 当日时间； | ||
localtimestamp | timestamp | 当前事务开始时的时间戳； | ||
make_date(year int, month int, day int) | date | 为年、月和日字段创建日期 | make_date(2013, 7, 15) | 2013-07-15 |
make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) | interval | 从年、月、周、天、小时、分钟和秒字段中创建间隔 | make_interval(days := 10) | 10 days |
make_time(hour int, min int, sec double precision) | time | 从小时、分钟和秒字段中创建时间 | make_time(8, 15, 23.5) | 08:15:23.5 |
make_timestamp(year int, month int, day int, hour int, min int, sec double precision) | timestamp | 从年、月、日、小时、分钟和秒字段中创建时间戳 | make_timestamp(2013, 7, 15, 8, 15, 23.5) | 2013-07-15 08:15:23.5 |
make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ]) | timestamp with time zone | 从年、月、日、小时、分钟和秒字段中创建带有时区的时间戳。 没有指定timezone时，使用当前的时区。 | make_timestamptz(2013, 7, 15, 8, 15, 23.5) | 2013-07-15 08:15:23.5+01 |
now() | timestamp with time zone | 当前事务开始时的时间戳； | ||
statement_timestamp() | timestamp with time zone | 实时时钟的当前时间戳； | ||
timeofday() | text | 与clock_timestamp相同，但结果是一个text 字符串； | ||
transaction_timestamp() | timestamp with time zone | 当前事务开始时的时间戳； |
常用函数
PostgreSQL 内置函数也称为聚合函数，用于对字符串或数字数据执行处理。
下面是所有通用 PostgreSQL 内置函数的列表：
- COUNT 函数：用于计算数据库表中的行数。
- MAX 函数：用于查询某一特定列中最大值。
- MIN 函数：用于查询某一特定列中最小值。
- AVG 函数：用于计算某一特定列中平均值。
- SUM 函数：用于计算数字列所有值的总和。
- ARRAY 函数：用于输入值(包括null)添加到数组中。
- Numeric 函数：完整列出一个 SQL 中所需的操作数的函数。
- String 函数：完整列出一个 SQL 中所需的操作字符的函数。
数学函数
下面是PostgreSQL中提供的数学函数列表，需要说明的是，这些函数中有许多都存在多种形式，区别只是参数类型不同。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| abs(x) | 绝对值 | abs(-17.4) | 17.4 | |
| cbrt(double) | 立方根 | cbrt(27.0) | 3 | |
| ceil(double/numeric) | 不小于参数的最小的整数 | ceil(-42.8) | -42 | |
| degrees(double) | 把弧度转为角度 | degrees(0.5) | 28.6478897565412 | |
| exp(double/numeric) | 自然指数 | exp(1.0) | 2.71828182845905 | |
| floor(double/numeric) | 不大于参数的最大整数 | floor(-42.8) | -43 | |
| ln(double/numeric) | 自然对数 | ln(2.0) | 0.693147180559945 | |
| log(double/numeric) | 10为底的对数 | log(100.0) | 2 | |
| log(b numeric,x numeric) | numeric | 指定底数的对数 | log(2.0, 64.0) | 6.0000000000 |
| mod(y, x) | 取余数 | mod(9,4) | 1 | |
| pi() | double | "π"常量 | pi() | 3.14159265358979 |
| power(a double, b double) | double | 求a的b次幂 | power(9.0, 3.0) | 729 |
| power(a numeric, b numeric) | numeric | 求a的b次幂 | power(9.0, 3.0) | 729 |
| radians(double) | double | 把角度转为弧度 | radians(45.0) | 0.785398163397448 |
| random() | double | 0.0到1.0之间的随机数值 | random() | |
| round(double/numeric) | 圆整为最接近的整数 | round(42.4) | 42 | |
| round(v numeric, s int) | numeric | 圆整为s位小数数字 | round(42.438,2) | 42.44 |
| sign(double/numeric) | 参数的符号(-1,0,+1) | sign(-8.4) | -1 | |
| sqrt(double/numeric) | 平方根 | sqrt(2.0) | 1.4142135623731 | |
| trunc(double/numeric) | 截断(向零靠近) | trunc(42.8) | 42 | |
| trunc(v numeric, s int) | numeric | 截断为s小数位置的数字 | trunc(42.438,2) | 42.43 |
三角函数列表
| 函数 | 描述 |
|---|---|
| acos(x) | 反余弦 |
| asin(x) | 反正弦 |
| atan(x) | 反正切 |
| atan2(x, y) | 正切 y/x 的反函数 |
| cos(x) | 余弦 |
| cot(x) | 余切 |
| sin(x) | 正弦 |
| tan(x) | 正切 |
字符串函数和操作符
下面是 PostgreSQL 中提供的字符串操作符列表：
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| string 丨丨 string | text | 字串连接 | ‘Post’ 丨丨 ‘greSQL’ | PostgreSQL |
| bit_length(string) | int | 字串里二进制位的个数 | bit_length(‘jose’) | 32 |
| char_length(string) | int | 字串中的字符个数 | char_length(‘jose’) | 4 |
| convert(string using conversion_name) | text | 使用指定的转换名字改变编码。 | convert(‘PostgreSQL’ using iso_8859_1_to_utf8) | ‘PostgreSQL’ |
| lower(string) | text | 把字串转化为小写 | lower(‘TOM’) | tom |
| octet_length(string) | int | 字串中的字节数 | octet_length(‘jose’) | 4 |
| overlay(string placing string from int [for int]) | text | 替换子字串 | overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4) | Thomas |
| position(substring in string) | int | 指定的子字串的位置 | position(‘om’ in ‘Thomas’) | 3 |
| substring(string [from int] [for int]) | text | 抽取子字串 | substring(‘Thomas’ from 2 for 3) | hom |
| substring(string from pattern) | text | 抽取匹配 POSIX 正则表达式的子字串 | substring(‘Thomas’ from ‘…$’) | mas |
| substring(string from pattern for escape) | text | 抽取匹配SQL正则表达式的子字串 | substring(‘Thomas’ from ‘%#“o_a#”_’ for ‘#’) | oma |
| trim([leading丨trailing 丨 both] [characters] from string) | text | 从字串string的开头/结尾/两边/ 删除只包含characters(默认是一个空白)的最长的字串 | trim(both ‘x’ from ‘xTomxx’) | Tom |
| upper(string) | text | 把字串转化为大写。 | upper(‘tom’) | TOM |
| ascii(text) | int | 参数第一个字符的ASCII码 | ascii(‘x’) | 120 |
| btrim(string text [, characters text]) | text | 从string开头和结尾删除只包含在characters里(默认是空白)的字符的最长字串 | btrim(‘xyxtrimyyx’,‘xy’) | trim |
| chr(int) | text | 给出ASCII码的字符 | chr(65) | A |
| convert(string text, [src_encoding name,] dest_encoding name) | text | 把字串转换为dest_encoding | convert( ‘text_in_utf8’, ‘UTF8’, ‘LATIN1’) | 以ISO 8859-1编码表示的text_in_utf8 |
| initcap(text) | text | 把每个单词的第一个子母转为大写，其它的保留小写。单词是一系列字母数字组成的字符，用非字母数字分隔。 | initcap(‘hi thomas’) | Hi Thomas |
| length(string text) | int | string中字符的数目 | length(‘jose’) | 4 |
| lpad(string text, length int [, fill text]) | text | 通过填充字符fill(默认为空白)，把string填充为长度length。 如果string已经比length长则将其截断(在右边)。 | lpad(‘hi’, 5, ‘xy’) | xyxhi |
| ltrim(string text [, characters text]) | text | 从字串string的开头删除只包含characters(默认是一个空白)的最长的字串。 | ltrim(‘zzzytrim’,‘xyz’) | trim |
| md5(string text) | text | 计算给出string的MD5散列，以十六进制返回结果。 | md5(‘abc’) | |
| repeat(string text, number int) | text | 重复string number次。 | repeat(‘Pg’, 4) | PgPgPgPg |
| replace(string text, from text, to text) | text | 把字串string里出现地所有子字串from替换成子字串to。 | replace(‘abcdefabcdef’, ‘cd’, ‘XX’) | abXXefabXXef |
| rpad(string text, length int [, fill text]) | text | 通过填充字符fill(默认为空白)，把string填充为长度length。如果string已经比length长则将其截断。 | rpad(‘hi’, 5, ‘xy’) | hixyx |
| rtrim(string text [, character text]) | text | 从字串string的结尾删除只包含character(默认是个空白)的最长的字 | rtrim(‘trimxxxx’,‘x’) | trim |
| split_part(string text, delimiter text, field int) | text | 根据delimiter分隔string返回生成的第field个子字串(1 Base)。 | split_part(‘abc@def@ghi’, ‘@’, 2) | def |
| strpos(string, substring) | text | 声明的子字串的位置。 | strpos(‘high’,‘ig’) | 2 |
| substr(string, from [, count]) | text | 抽取子字串。 | substr(‘alphabet’, 3, 2) | ph |
| to_ascii(text [, encoding]) | text | 把text从其它编码转换为ASCII。 | to_ascii(‘Karel’) | Karel |
| to_hex(number int/bigint) | text | 把number转换成其对应地十六进制表现形式。 | to_hex(9223372036854775807) | 7fffffffffffffff |
| translate(string text, from text, to text) | text | 把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。 | translate(‘12345’, ‘14’, ‘ax’) | a23x5 |
类型转换相关函数
| 函数 | 返回类型 | 描述 | 实例 |
|---|---|---|---|
| to_char(timestamp, text) | text | 将时间戳转换为字符串 | to_char(current_timestamp, ‘HH12:MI:SS’) |
| to_char(interval, text) | text | 将时间间隔转换为字符串 | to_char(interval ‘15h 2m 12s’, ‘HH24:MI:SS’) |
| to_char(int, text) | text | 整型转换为字符串 | to_char(125, ‘999’) |
| to_char(double precision, text) | text | 双精度转换为字符串 | to_char(125.8::real, ‘999D9’) |
| to_char(numeric, text) | text | 数字转换为字符串 | to_char(-125.8, ‘999D99S’) |
| to_date(text, text) | date | 字符串转换为日期 | to_date(‘05 Dec 2000’, ‘DD Mon YYYY’) |
| to_number(text, text) | numeric | 转换字符串为数字 | to_number(‘12,454.8-’, ‘99G999D9S’) |
| to_timestamp(text, text) | timestamp | 转换为指定的时间格式 time zone convert string to time stamp | to_timestamp(‘05 Dec 2000’, ‘DD Mon YYYY’) |
| to_timestamp(double precision) | timestamp | 把UNIX纪元转换成时间戳 | to_timestamp(1284352323) |
sql优化常用方法
1. EXPLAIN
type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
key_len列，索引长度。
rows列，扫描行数。该值是个预估值。
extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。
2.SQL语句中IN包含的值不应过多
MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。
3.SELECT语句务必指明字段名称
SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。
4.当只需要一条数据的时候，使用limit 1
这是为了使EXPLAIN中type列达到const类型
如果加上limit1，查找到就不用继续往后找了
5.如果排序字段没有用到索引，就尽量少排序
可以在程序中排序
6.如果限制条件中其他字段没有索引，尽量少用or
or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。
7.尽量用union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。
8.区分in和exists、not in和not exists
select * from 表A where id in (select id from 表B)
上面SQL语句相当于
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？
原SQL语句：
select colname … from A表 where a.id not in (select b.id from B表)
高效的SQL语句：
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
取出的结果集如下图表示，A表不在B表中的数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D5398bR8-1666578368042)(…/…/…/…/…/Documents/Study_Pic/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBASmF2Yei2heelnuS5i-i3rw==,size_17,color_FFFFFF,t_70,g_se,x_16.png)]
http://www.changchenghao.cn/n/174426.html
前提，开启慢sql
一、慢查询
默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启，如下所示：
1、查询慢日志是否开启。
show variables like ‘%slow_query_log%’;
2、开启慢查询日志(OFF 为关闭 ON为开启)
set global slow_query_log=ON;
注意：使用set global slowquerylog=1开启了慢查询日志只对当前数据库生效，MySQL重启后失效。如果要永久生效，就必须修改配置文件my.cnf
3、永久开启慢查询日志
修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器，
slow_query_log =1
slow_query_log_file=/usr/local/mysql/data/localhost-slow.log
4、设置日志路径和未使用索引的查询(有默认值，可以不设置)
log-slow-queries = /usr/local/mysql/data/slow.log #定义慢查询日志路径。
log-queries-not-using-indexes #未使用索引的查询也被记录到慢查询日志中（可选）。
二、慢查询时间设置。默认情况下long_query_time的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。
1、查询慢日志时间。
show variables like ‘long_query_time’;
注：如果设置了日志时间，对当前会话是无效的。所以用全局查询
show global variables like ‘long_query_time’;
2、设置慢查询日志时间。
set global long_query_time=3;
3、查看总执行了多少次慢sql
show global status like ‘%slow%’;
explain
http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html
https://www.cnblogs.com/tufujie/p/9413852.html
sql优化15点
- 避免使用select *
- 用union all 代替union
- 小表驱动大表
- 批量操作
- 多用limit
- in中值太多
- 增量查询
- 高效的分页
- 用链接查询代替子查询
- join数量不宜过多
- join时需要注意
- 控制索引的数量
- 选择合理的字段类型
- 提升group by的效率
- 索引优化
**1.避免使用select ***
实际业务场景中不需要所有的字段，只需要其中一两个，只查找用到
浪费数据库资源，内存，cup
查出来的数据多，通过网络IO传输过程中也会增加传输时间
select * 不会走覆盖索引，会出现回表
2.用union all 代替union
union会排重
排重过程需要遍历，排序，比较，更消耗cpu资源
确定唯一，没有重复数据能用union all尽量用
3.小表驱动大表
in 的话里面驱动外面，in适合里子查询是小表
exist 的话外面驱动里面，适合外面是小表
4.批量插入
mybatis plus 的insertBatch
当然一次插入量也不能太大，可以分批插入。
5.多用limit
6.in中值太多
查询出来数量太大，限制一次最大查询条数
还可以，多线程查询，最后把查询出来的数据汇总。
7.增量查询
select name，age from user where id>#{lastId} limit 100;
查询比上次id 大的100条
8.高效的分页
select id,name,age from user limit 10000, 20;
1
mysql会查询10020条，然后丢弃前面10000条，这个比较浪费资源
可以优化：
select id,name,age from user id>10000 limit 20;
找到上次分页最大id
假如id是连续的，并且有序，可以用between
注意： between要在唯一索引上分页，不然会出现每页大小不一致问题。
9.用连接查询代替子查询
MySQL如果需要在两张以上表中查询数据的话，一般有两种实现方式
- 子查询
- 连接查询
select * from order where user_id in (select id from user where name='zhang');
子查询可以通过in实现，优点：这样简单，
但缺点是，MySQL执行子查询时，需要创建临时表，查询完成后再删除临时表，有一些额外开销。
可以改成连接查询:
select o.* from order o inner join user u on o.user_id = u.id where u.name='zhang';
10.join表不宜过多
阿里巴巴开发者手册规定，join表不宜超过3个
如果join太多，MySQL在选择索引时会非常复杂，很容易选错索引。
并且没有命中，nested loop join 就会分别从两个表读一行数据进行对比，时间复杂度n^2。
11.join时需要注意
join用的最多的时left join 和 inner join
left join：两个表的交集和左表的剩余数据
inner join：两个表的交集
inner join mysql会自动选择小表驱动，
left join 左边的表驱动右边的表
12.控制索引数量
索引不是越多越好，索引需要额外的存储空间，B+树保存索引，额外的性能消耗。
阿里巴巴开发者手册中规定，单表索引数量尽量控制在5个以内，且单个索引字段数量控制在5个以内。
13.选择合理的字段类型
char：固定字符串类型，该类型在的字段在存储空间上是固定的，固定长度的可以用
varchar：变长字符串类型
能用数字类型就不用字符串，字符串处理速度比数字类型慢
尽量用小类型，比如：用bit存布尔值，用tinyint存枚举值等。
长度固定字符串用char，不固定用varchar
14.提升group by效率
主要功能去重，分组
先过滤数据，减少数据，再分组
select id, name ,age from user
group by id
having id <50;
这种写法就不好，
select id, name ,age from user
where id <50
group by id;
15.索引优化
强制走哪个索引
force index
select * from user
force index(索引)
扫一扫
900
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
