较好的文档
mysql -h127.0.0.1 -uroot -p -P 3306
[gaowanlu@vhost02]$ mysql -uroot -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2270
Server version: 5.6.51 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Byedocker
# docker pull postgres:14.19-alpine3.21
# docker run --name some-postgres -p 127.0.0.1:5432:5432 -e POSTGRES_PASSWORD=root -d postgres:14.19-alpine3.21
# docker exec -it some-postgres bash
# psql -U postgres
# docker stop some-postgres
# docker rm some-postgrespsql
root@ser745692301841:/dev_dir/mc_like/mapsvr/dbsvrgo/sql# docker exec -it some-postgres bash
c8a16719e1d9:/# psql -U postgres
psql (14.19)
Type "help" for help.
postgres=# \q
c8a16719e1d9:/# postgres可以通过 help 查看SQL语法帮助
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 [ ALL | DISTINCT ] 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 | WITH TIES } ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
--More-- 数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。下表列出了可用的数值类型。
| 名字 | 存储长度 | 描述 | 范围 |
|---|---|---|---|
| smallint | 2 字节 | 小范围整数 | -32768 到 +32767 |
| integer | 4 字节 | 常用的整数 | -2147483648 到 +2147483647 |
| bigint | 8 字节 | 大范围整数 | -9223372036854775808 到 +9223372036854775807 |
| decimal(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 |
money 本质上是定点数实现，精度固定两位小数。真做金融系统，很多团队还是更偏向用 numeric(precision, scale) 明确控制精度。
下表列出了 PostgreSQL 所支持的字符类型：
| 序号 | 名字 | 描述 |
|---|---|---|
| 1 | character varying(n), varchar(n) | 变长，有长度限制 |
| 2 | character(n), char(n) | 定长，不足补空白 |
| 3 | text | 变长，无长度限制 |
varchar(n) 和 text 在大多数现代数据库（比如 PostgreSQL）性能几乎没区别，差别主要是长度约
下表列出了 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 |
枚举类型是一个包含静态和值的有序集合的数据类型。
PostgreSQL 中的枚举类型类似于 C 语言中的 enum 类型。
与其他类型不同的是枚举类型需要使用 CREATE TYPE 命令创建。
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 | tsvector | tsvector 的值是一个无重复的 lexeme 排序列表，即同一词不同变形的标准化结果 |
| 2 | tsquery | tsquery 存储用于检索的词汇，使用布尔操作符 &(AND)、|(OR)、!(NOT) 组合，括号可用于分组 |
uuid 数据类型用来存储 RFC 4122，ISO/IEF 9834-8:2005 以及相关标准定义的通用唯一标识符（UUID）。 （一些系统认为这个数据类型为全球唯一标识符，或GUID。） 这个标识符是一个由算法产生的 128 位标识符，使它不可能在已知使用相同算法的模块中和其他方式产生的标识符相同。 因此，对分布式系统而言，这种标识符比序列能更好的提供唯一性保证，因为序列只能在单一数据库中保证唯一。
UUID 被写成一个小写十六进制数字的序列，由分字符分成几组， 特别是一组8位数字+3组4位数字+一组12位数字，总共 32 个数字代表 128 位， 一个这种标准的 UUID 例子如下：
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11xml 数据类型可以用于存储XML数据。 将 XML 数据存到 text
类型中的优势在于它能够为结构良好性来检查输入值，
并且还支持函数对其进行类型安全性检查。
要使用这个数据类型，编译时必须使用
configure --with-libxml。
xml 可以存储由XML标准定义的格式良好的”文档”， 以及由 XML 标准中的 XMLDecl? content 定义的”内容”片段， 大致上，这意味着内容片段可以有多个顶级元素或字符节点。 xmlvalue IS DOCUMENT 表达式可以用来判断一个特定的 xml 值是一个完整的文件还是内容片段。
创建XML值，使用函数 xmlparse
XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')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;声明复合类型，下面是两个定义复合类型的简单例子：
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;
# 正确写法
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;范围数据类型代表着某一元素类型在一定范围内的值。
例如，timestamp 范围可能被用于代表一间会议室被预定的时间范围。
PostgreSQL 内置的范围类型有：
此外，可以定义自己的范围类型
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 | 已过时类型，曾用于以上用途 |
postgres=# CREATE DATABASE testdb;
CREATE DATABASE使用格式
createdb [option...] [dbname [description]]| 选项 | 描述 |
|---|---|
| -D tablespace | 指定数据库默认表空间 |
| -e | 将createdb生成的命令发送给服务端 |
| -E encoding | 指定数据库的编码 |
| -l locale | 指定数据库的语言环境 |
| -T template | 指定创建此数据库的模板 |
| –help | 显示createdb命令的帮助信息 |
| -h host | 指定服务器的主机名 |
| -p port | 指定服务器监听的端口，或者socket文件 |
| -U username | 连接数据库的用户名 |
| -w | 忽略输入密码 |
| -W | 连接时强制要求输入密码 |
c8a16719e1d9:/# pg_config --bindir
/usr/local/bin
c8a16719e1d9:/usr/local/bin# ls
clusterdb pg_basebackup pg_test_timing
createdb pg_checksums pg_upgrade
createuser pg_config pg_verifybackup
docker-enforce-initdb.sh pg_controldata pg_waldump
docker-ensure-initdb.sh pg_ctl pgbench
docker-entrypoint.sh pg_dump postgres
dropdb pg_dumpall postmaster
dropuser pg_isready psql
ecpg pg_receivewal reindexdb
gosu pg_recvlogical su-exec
initdb pg_resetwal vacuumdb
oid2name pg_restore vacuumlo
pg_amcheck pg_rewind
pg_archivecleanup pg_test_fsync例如上面的 /usr/local/bin 下有一个 createdb
的可执行文件。
c8a16719e1d9:/usr/local/bin# createdb -h localhost -p 5432 -U postgres testdb上面命令，使用超级用户 postgres 登录到主机地址为 localhost，端口号为 5432 的 PostgreSQL 数据库中并创建 testdb 数据库。
使用 \l 用于查看已经存在的数据库：
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
koyebdb | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
(5 rows)使用 \c + 数据库名 进入数据库
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# 系统命令行
在系统的命令行查看，可以在连接数据库后面添加数据库名来选择数据库：
c8a16719e1d9:/usr/local/bin# psql -h localhost -p 5432 -U postgres testdb
psql (14.19)
Type "help" for help.
testdb=# DROP DATABASE 会删除数据库的系统目录项并且删除包含数据的文件目录，只能由超级管理员或数据库拥有者执行。
DROP DATABASE [ IF EXISTS ] name;c8a16719e1d9:/usr/local/bin# psql -U postgres
psql (14.19)
Type "help" for help.
postgres=# DROP DATABASE testdb;
DROP DATABASE
postgres=# dropdb 是 DROP DATABASE 的包装器。用于删除 PostgreSQL 数据库。命令只能由超级管理员或数据库拥有者执行。
dropdb [connection-option...] [option...] dbname| 选项 | 描述 |
|---|---|
| -e | 显示dropdb生成的命令并发送到数据库服务器 |
| -i | 在做删除的工作之前发出一个验证提示 |
| -V | 打印dropdb版本并退出 |
| –if-exists | 如果数据库不存在则发出提示信息，而不是错误信息 |
| –help | 显示有关dropdb命令的帮助信息 |
| -h host | 指定运行服务器的主机名 |
| -p port | 指定服务器监听的端口，或者socket文件 |
| -U username | 连接数据库的用户名 |
| -w | 连接时忽略输入密码 |
| -W | 连接时强制要求输入密码 |
| –maintenance-db=dbname | 删除数据库时指定连接的数据库，默认为postgres,如果它不存在则使用template1 |
进入到 PostgreSQL 安装目录，使用 dropdb
dropdb -h localhost -p 5432 -U postgres testdb
passsword ******以上命令使用了超级用户 postgres 登录到主机地址为 localhost，端口号为 5432 的 PostgreSQL 数据库中并删除 testdb 数据库。
使用 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 命令来查看当前数据库下有哪些表
testdb=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | company | table | postgres
public | department | table | postgres
(2 rows)\d tablename 查看表格信息
testdb=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | company | table | postgres
public | department | table | postgres
(2 rows)
testdb=# \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 table_name;实例：
testdb=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | company | table | postgres
public | department | table | postgres
(2 rows)删除表 company 和 department
testdb=# DROP TABLE company, department;
DROP TABLE
testdb=# \d
Did not find any relations.
testdb=# PostgreSQL 模式（SCHEMA）可以看作是一个表的集合。
一个模式可以包含视图、索引、数据类型、函数 和 操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable的表。
使用模式的优势：
模式类似操作系统层的目录，但是模式不能嵌套。
创建模式
CREATE SCHEMA myschema (
...
);创建表
CREATE TABLE myschema.mytable (
column1 datatype1,
column2 datatype2,
...
);上述语句将在 myschema 模式下创建一个名为 mytable 的表。
实例
create schema myschema;
CREATE SCHEMA
create table myschema.company(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
select * from myschema.company;
id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)删除模式
# 删除一个为空的模式（其中的所有对象已经被删除）
DROP SCHEMA myschema;
# 删除一个模式以及其中包含的所有对象
DROP SCHEMA myschema CASCADE;postgreSQL INSERT INTO 语句用于向表中插入新记录，可以插入一行也可以同时插入多行。
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);字段列必须和数据值数量相同，且顺序也要对应。如果向表中的所有字段插入值，则可以不需要指定字段，只需要指定插入的值即可
INSERT INTO TABLE_NAME VALUES (value1, value2, value3, ..., valueN);执行插入后返回结果的说明：
| 输出信息 | 描述 |
|---|---|
| INSERT oid 1 | 只插入一行并且目标表具有OID的返回信息，那么oid是分配给被插入行的OID |
| INSERT 0 # | 插入多行返回的信息，#为插入的行数 |
实例
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL,
JOIN_DATE DATE
);在 COMPANY 表中插入以下数据
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT 0 1以下插入语句忽略 SALARY 字段
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');
INSERT 0 1以下插入语句 JOIN_DATE 字段使用 DEFAULT 子句来设置默认值，而不是指定值
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );
INSERT 0 1以下实例插入多行
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
INSERT 0 2SELECT 语句查询表格数据
testdb=# SELECT * FROM COMPANY;
1 | Paul | 32 | California | 20000 | 2001-07-13
2 | Allen | 25 | Texas | | 2007-12-13
3 | Teddy | 23 | Norway | 20000 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13PostgreSQL SELECT 语句用于从数据库中选取数据。结果被存储在一个结果表中，称为结果集。
SELECT 语句语法格式如下
SELECT column1, column2,...columnN FROM table_name;如果想读取表中的所有数据，可以使用
SELECT * FROM table_name;实例
testdb=# \d company
id | integer | | not null |
name | text | | not null |
age | integer | | not null |
address | character(50) | | |
salary | real | | |
join_date | date | | |
testdb=# SELECT * FROM company;
1 | Paul | 32 | California | 20000 | 2001-07-13
2 | Allen | 25 | Texas | | 2007-12-13
3 | Teddy | 23 | Norway | 20000 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
testdb=# SELECT id,name FROM company;
1 | Paul
2 | Allen
3 | Teddy
4 | Mark
5 | David假设变量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 |
实例
testdb=# select 2+3;
5
testdb=# select 2*3;
6
testdb=# select 10/5;
2
testdb=# select 12%5;
2
testdb=# select 2^3;
8
testdb=# select |/25.0;
5
testdb=# select ||/27.0;
3假设变量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。 |
实例
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)读取SALARY字段大于50000的数据
SELECT * FROM COMPANY WHERE SALARY > 50000;
id | name | age |address | salary
----+-------+-----+-----------+--------
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(2 rows)读取SALARY字段等于20000的数据
SELECT * FROM COMPANY WHERE SALARY = 20000;
id | name | age | address | salary
----+-------+-----+-------------+--------
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
(2 rows)读取SALARY字段不等于20000的数据
SELECT * FROM COMPANY WHERE SALARY != 20000;
id | name | age | address | salary
----+-------+-----+-------------+--------
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(5 rows)
SELECT * FROM COMPANY WHERE SALARY <> 20000;
id | name | age | address | salary
----+-------+-----+------------+--------
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(5 rows)读取SALARY字段大于等于 65000 的数据
SELECT * FROM COMPANY WHERE SALARY >= 65000;
id | name | age | address | salary
----+-------+-----+-----------+--------
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(2 rows)PostgreSQL 逻辑运算符有以下几种
| 运算符 | 描述 |
|---|---|
| AND | 逻辑与运算符。如果两个操作数都非零，则条件为真。PostgresSQL 中的 WHERE 语句可以用 AND 包含多个过滤条件。 |
| NOT | 逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。PostgresSQL 有 NOT EXISTS, NOT BETWEEN, NOT IN 等运算符。 |
| OR | 逻辑或运算符。如果两个操作数中有任意一个非零，则条件为真。PostgresSQL 中的 WHERE 语句可以用 OR 包含多个过滤条件。 |
实例
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)读取AGE字段大于等于25且SALARY字段大于等于6500的数据
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;
id | name | age | address | salary
----+-------+-----+-----------------------------------------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(4 rows)读取AGE字段大于等于25或SALARY字段大于6500的数据
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 6500;
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
8 | Paul | 24 | Houston | 20000
9 | James | 44 | Norway | 5000
10 | James | 45 | Texas | 5000
(10 rows)读SALARY字段不为NULL的数据
SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;
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
8 | Paul | 24 | Houston | 20000
9 | James | 44 | Norway | 5000
10 | James | 45 | Texas | 5000
(10 rows)位运算符作用于位，并逐位执行操作
假设如果 A = 60，且 B = 13，现在以二进制格式表示，它们如下所示：
A = 0011 1100
B = 0000 1101
-----------------
A&B = 0000 1100
A|B = 0011 1101
A^B = 0011 0001
~A = 1100 0011实例
# 按位或
testdb=# select 60|13;
61
# 按位与
testdb=# select 60&13;
12
# 取反运算符
testdb=# select (~60);
-61
# 二进制左移运算符
testdb=# select (60<<2);
240
# 二进制右移运算符
testdb=# select (60>>2);
15
# 异或运算符
testdb=# select 60#13;
49表达式是由一个或多个的值、运算符、PostgresSQL函数组成的。
表达式类似一个公式，可以将其应用在查询语句中，用来查找数据库中指定条件的结果集。
SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];postgreSQL的表达式可以有不同类型。
布尔表达式是根据一个指定条件来读取数据
SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;以下使用了布尔表达式 SALARY=10000 来查询数据
SELECT * FROM COMPANY WHERE SALARY = 10000;
id | name | age | address | salary
----+-------+-----+----------+--------
7 | James | 24 | Houston | 10000
(1 row)数字表达式常用于查询语句中的数学运算
SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;numerical_expression 是一个数学运算表达式，实例如下
SELECT (17 + 6) AS ADDITION;
addition
----------
23
(1 row)此外PostgreSQL还内置了一些数学函数，如：
avg() 返回一个表达式的平均值sum() 返回指定字段的总和count() 返回查询的记录总数以下实例查询表的记录总数
SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
RECORDS
---------
7
(1 row)日期表达式返回当前系统的日期和时间，可用于各种数据操作，以下实例查询当前时间
SELECT CURRENT_TIMESTAMP;
current_timestamp
-------------------------------
2019-06-13 10:49:06.419243+08
(1 row)在PostgreSQL中，当需要根据指定条件从单张表或多张表中查询数据时，可以在SELECT语句中添加WHERE子句，从而过滤掉不需要数据。
WHERE子句不仅可以用于SELECT语句中，同时也可以用于 UPDATE、DELETE等等语句中。
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1]可以在 WHERE 子句中使用 比较运算符、逻辑运算符，例如
> < = LIKE NOT 等等。
找出 AGE 字段大于等于25，并且 SALARY 字段大于等于 65000 的数据
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
id | name | age | address | salary
----+-------+-----+------------+--------
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(2 rows)找出 AGE 字段大于等于25，或者 SALARY 字段大于等于 65000 的数据
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
id | name | age | address | salary
----+-------+-----+-------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(4 rows)在表中找出 AGE 字段不为空的记录
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
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)在表中找出 NAME 字段中以Pa开头的数据
SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';
id | name | age |address | salary
----+------+-----+-----------+--------
1 | Paul | 32 | California| 20000在SELECT语句列出AGE字段为 25 或 27 的数据
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
id | name | age | address | salary
----+-------+-----+------------+--------
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(3 rows)以下SELECT语句列出了AGE字段不为25、27的数据
SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(4 rows)以下SELECT语句列出了AGE字段在25到27的数据
SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
id | name | age | address | salary
----+-------+-----+------------+--------
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(3 rows)以下的 SELECT 语句使用了 SQL 的子查询，子查询语句中读取 SALARY 字段大于65000的数据，然后通过 EXISTS 运算符判断它是否返回行，如果有返回行则读取所有的AGE字段。
testdb=# select * from company;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
1 | Paul | 32 | California | 20000 | 2001-07-13
2 | Allen | 25 | Texas | | 2007-12-13
3 | Teddy | 23 | Norway | 20000 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
6 | Kim | 22 | South-Hall | 45000 |
7 | James | 24 | Houston | 10000 |
(7 rows) # 如果表中有 SALARY>65000的行，则把表中的所有行的AGE返回
testdb=# SELECT AGE FROM COMPANY
testdb-# WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
age
-----
32
25
23
25
27
22
24
(7 rows)testdb=# SELECT AGE FROM COMPANY WHERE SALARY >= 65000;
age
-----
25
27
(2 rows)
testdb=# SELECT * FROM company WHERE age > (SELECT AGE FROM COMPANY WHERE SALARY >= 65000);
ERROR: more than one row returned by a subquery used as an expression
testdb=# SELECT * FROM company WHERE age > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
id | name | age | address | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
1 | Paul | 32 | California | 20000 | 2001-07-13
(1 row)
testdb=# 在 postgreSQL 中，AND和OR也叫连接运算符，在查询数据时用于缩小查询范围，可以用 AND 或者 OR 指定一个或多个查询条件。
AND运算符表示一个或多个条件必须同时成立
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];以下实例读取 AGE 字段大于 25 且 SALARY 字段大于等于 65000 的所有记录：
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
id | name | age | address | salary
----+-------+-----+------------+--------
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(2 rows)OR运算符表示多个条件中只需满足其中任意一个即可，在WHERE子句中，OR的使用语法如下
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]以下实例读取AGE字段大于等于25或SALARY字段大于等于65000的所有记录
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(4 rows)如果需更新在 PostgreSQL 数据库中的数据，可以用 UPDATE 来操作。
UPDATE语句修改数据的通用SQL语法
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];以下实例将更新company表中id为3的salary字段值
UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;以下实例将同时更新 salary 字段 和 address 字段的值
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;可以使用DELETE语句来删除 PostgreSQL 表中的数据。
以下是DELETE语句删除数据的通用语法
DELETE FROM table_name WHERE [condition];如果没有指定WHERE子句，PostgreSQL 表中的所有记录将被删除。
删除ID为2的数据
DELETE FROM COMPANY WHERE ID = 2;以下语句将删除整张company表的记录
DELETE FROM company;在PostgreSQL数据库中，如果要获取包含某些字符的数据，可以使用LIKE子句
在LIKE子句中，通常与通配符结合使用，通配符表示任意字符，在PostgreSQL中，主要有以下两种通配符
% 匹配0或多个任意字符_ 匹配1个任意字符下面是一些例子
WHERE SALARY::text LIKE '200%'
# 找出 SALARY 字段中以200开头的数据WHERE SALARY::text LIKE '%200%'
# 找出 SALARY 字段中含有200字符的数据WHERE SALARY::text LIKE '_00%'
# 找出SALARY字段中在第二和第三个位置上有0的数据WHERE SALARY::text LIKE '2_%_%'
# 找出 SALARY 字段中以 2 开头的字符长度大于 3 的数据WHERE SALARY::text LIKE '%2'
# 找出 SALARY 字段中以 2 结尾的数据WHERE SALARY::text LIKE '_2%3'
# 找出 SALARY 字段中 2 在第二个位置上并且以 3 结尾的数据WHERE SALARY::text LIKE '2___3'
# 找出 SALARY 字段中以 2 开头，3 结尾并且是 5 位数的数据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]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)下面实例将找出限定数量的数据，即读取4条数据
SELECT * FROM COMPANY LIMIT 4;得到以下结果
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
(4 rows)在某些情况下，可能需要从一个特定的偏移开始提取记录，下面实例，从第三位开始提取3个记录
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
id | name | age | address | salary
----+-------+-----+-----------+--------
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(3 rows)在 PostgreSQL 中，ORDER BY用于对一列或者多列数据进行升序（ASC）或者 降序（DESC）排列。
ORDER BY 子句的基础语法如下:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];可以在 ORDER BY 中使用一列或多列，但是必须保证要排列的列必须存在。
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)对结果根据AGE字段值进行升序排列
SELECT * FROM COMPANY ORDER BY AGE ASC;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
6 | Kim | 22 | South-Hall | 45000
3 | Teddy | 23 | Norway | 20000
7 | James | 24 | Houston | 10000
4 | Mark | 25 | Rich-Mond | 65000
2 | Allen | 25 | Texas | 15000
5 | David | 27 | Texas | 85000
1 | Paul | 32 | California | 20000
(7 rows)下面实例对结果根据NAME字段值和SALARY字段值进行升序排列,先按照NAME升序，同NAME内按SALARY升序。
SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
2 | Allen | 25 | Texas | 15000
5 | David | 27 | Texas | 85000
7 | James | 24 | Houston | 10000
6 | Kim | 22 | South-Hall | 45000
4 | Mark | 25 | Rich-Mond | 65000
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
(7 rows)下面实例将对结果根据NAME字段进行降序排列
SELECT * FROM COMPANY ORDER BY NAME DESC;
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
3 | Teddy | 23 | Norway | 20000
1 | Paul | 32 | California | 20000
4 | Mark | 25 | Rich-Mond | 65000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
5 | David | 27 | Texas | 85000
2 | Allen | 25 | Texas | 15000
(7 rows)在 PostgreSQL 中，GROUP BY语句和 SELECT 语句一起使用，用来对相同数据进行分组，GROUP BY在一个SELECT语句中，放在WHERE子句的后面，ORDER BY子句的前面。
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN表内记录
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)下面实例将根据 NAME 字段值进行分组，找出每个人的工资总额
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
name | sum
-------+-------
Teddy | 20000
Paul | 20000
Mark | 65000
David | 85000
Allen | 15000
Kim | 45000
James | 10000
(7 rows)现在添加使用下面语句在 company 表中添加三条记录
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);现在 Company 表中存在重复 Name 的名称，数据如下
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
8 | Paul | 24 | Houston | 20000
9 | James | 44 | Norway | 5000
10 | James | 45 | Texas | 5000
(10 rows)再根据 Name 字段进行分组，找出每个客户的工资总额
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
name | sum
-------+-------
Teddy | 20000
Paul | 40000
Mark | 65000
Kim | 45000
James | 20000
David | 85000
Allen | 15000
(7 rows)在 PostgreSQL 里，WITH 用来定义 公共表表达式（Common Table Expression，简称 CTE）。
说白了就是：先定义一个临时结果集，然后在主查询里像表一样使用它。
WITH 查询的基础语法
WITH 临时表名 AS (
子查询
)
SELECT ...
FROM 临时表名;
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 子句中可以使用自身输出的数据。公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n + 1 FROM t WHERE n < 5
)
SELECT * FROM t;
# 结果
1
2
3
4
5
# 执行过程
初始值
t = 1
递归
1 -> 2
2 -> 3
3 -> 4
4 -> 5
停止实例
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)下面将使用 WITH 子句在上表中查询数据
With CTE AS
(Select
ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;
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)接下来让我们使用 RECURSIVE 关键字和WITH子句编写一个查询，查找 SALARY 字段小于 20000 的数据并计算它们的和：
WITH RECURSIVE t(n) AS (
VALUES (0)
UNION ALL
SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;
sum
-------
25000
(1 row)下面例子。建立一张和 COMPANY 表相似的 COMPANY1表，使用 DELETE语句和WITH子句删除COMPANY表中 SALARY字段大于等于 30000 的数据，并将删除的数据插入 COMPANY1表，实现将 COMPANY 表数据转移到COMPANY1表中。
CREATE TABLE COMPANY1(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
WITH moved_rows AS (
DELETE FROM COMPANY
WHERE
SALARY >= 30000
RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);
# 结果
INSERT 0 3
SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
7 | James | 24 | Houston | 10000
(4 rows)
SELECT * FROM COMPANY1;
id | name | age | address | salary
----+-------+-----+-------------+--------
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
(3 rows)甚至可以定义多个临时结果
WITH
a AS (
SELECT * FROM company WHERE salary > 10000
),
b AS (
SELECT * FROM a WHERE salary < 20000
)
SELECT * FROM b;
company
↓
a (salary > 10000)
↓
b (10000 < salary < 20000)像搭积木一样写 SQL，可读性比嵌套子查询高很多。
在旧版本 PostgreSQL里，WITH默认会物化（materialize）
先算完，存成临时表，再查询
从PostgreSQL 12开始，优化器可能会inline，不一定真的生成临时表，如果要强制物化
# 强制物化
WITH t AS MATERIALIZED (...)
# 希望优化器展开
WITH t AS NOT MATERIALIZED (...)把 WITH 当成，SQL里的局部变量。
auto a = query1();
auto b = query2(a);
auto c = query3(b);SQL 版
WITH a AS (...),
b AS (...),
c AS (...)
SELECT ...HAVING子句可以筛选分组后的各组数据
WHERE 子句在所选列上设置条件，而 HAVING 子句则在由GROUP BY子句创建的分组上设置条件。
下面是 HAVING 子句在 SELECT 查询中的位置
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BYHAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 语句中基础语法。
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2表内数据
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)下面实例将找出根据 NAME 字段值进行分组，并且 name 字段的计数少于2数据
SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;
name
-------
Teddy
Paul
Mark
David
Allen
Kim
James
(7 rows)如果表内记录数据为
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
8 | Paul | 24 | Houston | 20000
9 | James | 44 | Norway | 5000
10 | James | 45 | Texas | 5000
(10 rows)下面找出根据 name 字段值进行分组，并且名称的计数大于1数据
SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
name
-------
Paul
James
(2 rows)在 PostgreSQL 中，DISTINCT 关键字 与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]表内记录
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
8 | Paul | 32 | California | 20000
9 | Allen | 25 | Texas | 15000
(9 rows)可见表中 Paul、Allen 各有两条记录，找出 Company 表中的所有 Name
SELECT name FROM COMPANY;
name
-------
Paul
Allen
Teddy
Mark
David
Kim
James
Paul
Allen
(9 rows)在 SELECT 语句中使用 DISTINCE 子句
SELECT DISTINCT name FROM COMPANY;
name
-------
Teddy
Paul
Mark
David
Allen
Kim
James
(7 rows)可见，重复数据已经被过滤。
查询 company 表 返回 name、age、salary 并且去掉完全重复的行, 三列值都相同才算重复，不是只对 name 去重。
SELECT DISTINCT name, age, salary
FROM company;PostgreSQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE语句），或者在表创建之后规定（通过ALTER TABLE语句）。
约束确保了数据库中数据的准确性和可靠性。约束可以是列级或表级，列级约束仅适用于列，表记约束被应用到整个表。
以下是 PostgreSQL 中常用的约束。
默认情况下，列可以保存为 NULL 值。如果您不想某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。
NULL 与没有数据是不一样的，它代表着未知的数据。
实例
下面实例创建了一张新表叫 COMPANY1，添加了 5 个字段，其中三个 ID，NAME，AGE 设置不接受空置：
CREATE TABLE COMPANY1(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);UNIQUE 约束可以设置列是唯一的，避免同一列出现重复值。背后就是自动建索引。
实例
下面实例创建了一张新表叫 COMPANY3，添加了 5 个字段，其中 AGE 设置为 UNIQUE，因此你不能添加两条有相同年龄的记录：
CREATE TABLE COMPANY3(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
);在设计数据库时，PRIMARY KEY 非常重要。
PRIMARY KEY 称为主键，是数据表中每一条记录的唯一标识。
设置 UNIQUE 的列可能有多个，但是一张表只有一列可以设置 PRIMARY KEY。
可以使用主键来引用表中的行，也可以通过把主键设置为其他表的外键，来创建表之间的关系。
主键是非空约束和唯一约束的组合。
一个表只能有一个主键，它可以由一个或多个字段组成，当多个字段作为主键，它们被称为复合键。如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。
实例
下面创建 COMAPNY4 表，其中 ID 作为主键：
CREATE TABLE COMPANY4(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);FOREIGN KEY 即外键约束，指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。
通常一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)，即维护了两个相关表之间的引用完整性。
实例
下面实例创建了一张 COMPANY6 表，并添加了5个字段。
CREATE TABLE COMPANY6(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);下面实例创建一张 DEPARTMENT1 表，并添加3个字段，EMP_ID
就是外键，参照 COMPANY6 的ID：
CREATE TABLE DEPARTMENT1(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references COMPANY6(ID)
);CHECK 约束保证列中的所有值满足某一条件，即对输入一条记录要进行检查。如果条件值为 false，则记录违反了约束，且不能输入到表。
实例：下面实例建一个新的表 COMPANY5，增加了五列。在这里，为 SALARY 列添加 CHECK，所以工资不能为零：
CREATE TABLE COMPANY5(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0)
);任何两行记录，用指定运算符比较时，不能同时为 TRUE。
EXCLUDE USING index_method(
column WITH operator
)意思：对于任意两行 r1 和 r2 必须满足
NOT (r1.column operator r2.column)如果表达式为 TRUE，数据库就会拒绝插入。
经典例子：防止时间段重叠
假设有个会议室预约表
CREATE TABLE reservation(
room_id int,
during tsrange
);如果用 UNIQUE，你只能防止完全一样的数据
但现实的需求是，同一个房间的时间段不能重叠。这时候就用 EXCLUSION
CREATE EXTENSION btree_gist;
CREATE TABLE reservation (
room_id int,
during tsrange,
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
)
);含义
room_id 相等
AND
时间范围重叠不能同时成立，运算符 && 的意思是
range overlap
区间重叠# 允许的数据
room 1 [10:00,11:00]
room 1 [11:00,12:00]
room 2 [10:00,11:00]
# 不允许的数据
room 1 [10:00,11:00]
room 1 [10:30,11:30]为什么必须用GiST
会看到语法里有
USING gist因为 EXCLUSION 需要 索引支持运算符。常见索引
| 索引 | 支持 |
|---|---|
| btree | = < > |
| gist | range / geometry |
| spgist | 空间数据 |
| gin | 全文 |
而 range overlap 只能用 GiST
EXCLUSION 常见支持的运算符
| 运算符 | 含义 |
|---|---|
| = | 相等 |
| <> | 不等 |
| && | 范围重叠 |
| <-> | 距离 |
删除约束必须知道约束名称，已经知道名称来删除约束很简单，如果不知道名称，则需要找到系统生成的名称，使用
\d 表名 可以找到这些信息。
语法
ALTER TABLE table_name DROP CONSTRAINT some_name;PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段，在 PostgreSQL 中，JOIN 有五种连接类型：
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
8 | Paul | 24 | Houston | 20000
9 | James | 44 | Norway | 5000
10 | James | 45 | Texas | 5000
(10 rows)Department表
id | dept | emp_id
----+-------------+--------
1 | IT Billing | 1
2 | Engineering | 2
3 | Finance | 7交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。
由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
下面是 CROSS JOIN 的基础语法：
SELECT ... FROM table1 CROSS JOIN table2 ...基于上面的表，我们可以写一个交叉连接（CROSS JOIN），如下所示：
SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;得到结果如下
# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
emp_id | name | dept
--------+-------+--------------------
1 | Paul | IT Billing
1 | Allen | IT Billing
1 | Teddy | IT Billing
1 | Mark | IT Billing
1 | David | IT Billing
1 | Kim | IT Billing
1 | James | IT Billing
1 | Paul | IT Billing
1 | James | IT Billing
1 | James | IT Billing
2 | Paul | Engineering
2 | Allen | Engineering
2 | Teddy | Engineering
2 | Mark | Engineering
2 | David | Engineering
2 | Kim | Engineering
2 | James | Engineering
2 | Paul | Engineering
2 | James | Engineering
2 | James | Engineering
7 | Paul | Finance内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。
当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。
内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。
INNER 关键字是可选的。
下面是内连接（INNER JOIN）的语法。
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;基于上面的表，可以写一个内连接
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+--------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
(3 rows)外部连接是内部连接的扩展。SQL 标准定义了三种类型的外部连接: LEFT、RIGHT 和 FULL, PostgreSQL 支持所有这些。
对于左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行。
下面是左外连接（ LEFT OUTER JOIN ）的基础语法：
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...基于上面两张表，我们可以写个左外连接，如下：
# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 rows)首先，执行内部连接。然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行，结果表总是有一行。
下面是右外连接（ RIGHT OUT JOIN）的基本语法：
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...基于上面两张表，建立一个右外连接
# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+-----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
(3 rows)首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。
下面是外连接的基本语法：
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...例子
# 假设有两个表
CREATE TABLE employees (
id INT,
name TEXT
);
INSERT INTO employees VALUES
(1,'Alice'),
(2,'Bob'),
(3,'Charlie');
CREATE TABLE salaries (
emp_id INT,
salary INT
);
INSERT INTO salaries VALUES
(1,10000),
(2,12000),
(4,15000);执行 FULL OUTER JOIN
SELECT *
FROM employees
FULL OUTER JOIN salaries
ON employees.id = salaries.emp_id;结果
| id | name | emp_id | salary |
|---|---|---|---|
| 1 | Alice | 1 | 10000 |
| 2 | Bob | 2 | 12000 |
| 3 | Charlie | NULL | NULL |
| NULL | NULL | 4 | 15000 |
PostgreSQL UNION 操作符合并两个或多个SELECT语句的结果。
UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]实例：COMPANY表和DEPARTMENT表
# 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)
# SELECT * from DEPARTMENT;
id | dept | emp_id
----+-------------+--------
1 | IT Billing | 1
2 | Engineering | 2
3 | Finance | 7
4 | Engineering | 3
5 | Finance | 4
6 | Engineering | 5
7 | Finance | 6
(7 rows)现在，在SELECT语句中使用 UNION 子句将两张表连接起来
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+--------------
5 | David | Engineering
6 | Kim | Finance
2 | Allen | Engineering
3 | Teddy | Engineering
4 | Mark | Finance
1 | Paul | IT Billing
7 | James | Finance
(7 rows)UNION ALL 操作符可以连接两个有重复行的 SELECT 语句，默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
emp_id | name | dept
--------+-------+--------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
3 | Teddy | Engineering
4 | Mark | Finance
5 | David | Engineering
6 | Kim | Finance
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
3 | Teddy | Engineering
4 | Mark | Finance
5 | David | Engineering
6 | Kim | Finance
(14 rows)NULL 值代表遗漏的未知数据。默认地，表的列可以存放NULL值。
语法，当创建表时，NULL的基本语法如下
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);在查询数据时，NULL 值可能会导致一些问题，因为一个未知的值去与其他任何值比较，结果永远是未知的。
另外无法比较 NULL 和 0，因为它们是不等价的。
# 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)使用 UPDATE 把几个可设置为空的字段设置为NULL
# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
# 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 | |
7 | James | 24 | |
(7 rows)用 IS NOT NULL 操作符把所有 SALARY（薪资） 值不为空的记录列出来：
# SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;
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
(5 rows)IS NULL 用来查找为 NULL 值的字段。列出 SALARY（薪资） 值为空的记录：
# SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL;
id | name | age | address | salary
----+-------+-----+---------+--------
6 | Kim | 22 | |
7 | James | 24 | |
(2 rows)可以用 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];Company 表，数据内容如下：
# 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)Department 表，数据内容如下：
# SELECT * from DEPARTMENT;
id | dept | emp_id
----+-------------+--------
1 | IT Billing | 1
2 | Engineering | 2
3 | Finance | 7
4 | Engineering | 3
5 | Finance | 4
6 | Engineering | 5
7 | Finance | 6
(7 rows)分别用 C 和 D 表示 COMPANY 表和 DEPAERMENT 表的别名：
runoobdb=# SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
id | name | age | dept
----+-------+-----+------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
3 | Teddy | 23 | Engineering
4 | Mark | 25 | Finance
5 | David | 27 | Engineering
6 | Kim | 22 | Finance
(7 rows)用 COMPANY_ID 表示 ID 列，COMPANY_NAME 表示 NAME 列，来展示列别名的用法：
# SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
company_id | company_name | age | dept
------------+--------------+-----+------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
3 | Teddy | 23 | Engineering
4 | Mark | 25 | Finance
5 | David | 27 | Engineering
6 | Kim | 22 | Finance
(7 rows)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
[
-- 触发器逻辑....
];假设一个情况，要为被插入到新创建的 COMPANY 表（如果已经存在，则删除重新创建）中的每一个记录保持审计试验：
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);为了保持审计试验，我们将创建一个名为 AUDIT 的新表。每当 COMPANY 表中有一个新的记录项时，日志消息将被插入其中：
CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);ID 是 AUDIT 记录的 ID，EMP_ID 是来自 COMPANY 表的
ID，DATE 将保持 COMPANY 中记录被创建时的时间戳。所以，现在让我们在
COMPANY 表上创建一个触发器，如下所示：
# 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;开始往 COMPANY 表中插入数据：
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );这时，COMPANY 表中插入了一条记录：
同时， AUDIT 表中也插入了一条记录，因为我们在插入 COMPANY 表时创建了一个触发器。相似的，我们也可以根据需求在更新和删除时创建触发器：
emp_id | entry_date
--------+-------------------------------
1 | 2013-05-05 15:49:59.968+05:30
(1 row)可以把从 pg_trigger
表中把当前数据库所有触发器列举出来：
SELECT * FROM pg_trigger;想列举出特定表的触发器，语法如下：
SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';得到结果如下：
tgname
-----------------
example_trigger
(1 row)删除触发器基础语法如下：
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 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(column_list)
WHERE condition;在这里，index_name
是你想要创建的索引的名称，table_name
是包含你想要索引的列的表的名称，column_list
是你想要索引的列的列表，而 condition
是一个布尔表达式，用于定义哪些行将被包含在索引中。
在 PostgreSQL 中，隐式索引是在创建对象时，由数据库服务器自动创建的索引。这类索引通常为主键约束和唯一约束自动创建。当在创建表时声明一个列为主键、唯一约束或外键时，PostgreSQL 会自动为该列创建一个隐式索引。这样做的好处是简化了索引管理，并且提高了数据库的性能。
例如，如果在创建一个名为 “users” 的表时，声明了一个名为 “userid” 的列为主键，PostgreSQL会自动为 “userid” 列创建一个隐式索引，这意味着在插入新记录时，数据库会自动为 “userid” 列生成一个唯一的索引值。
隐式索引的创建和管理是由 PostgreSQL 自动完成的，用户不需要手动干预，这使得数据库管理变得更加简单和高效。
实例将在 COMPANY 表的 SALARY 列上创建索引：
CREATE INDEX salary_index ON COMPANY (salary);现在，用 \d company 命令列出 COMPANY 表的所有索引
\d company得到结果
# \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)
"salary_index" btree (salary)可以使用 \di 命令列出数据库中所有索引：
# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
public | salary_index | index | postgres | company
(3 rows)一个索引可以使用 PostgreSQL 的 DROP 命令删除。
DROP INDEX index_name;可以使用下面的语法来删除之前创建的索引
# DROP INDEX salary_index;删除后，可以看到 salary_index
已经在索引的列表中被删除：
runoobdb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
(2 rows)虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。
使用索引时，需要考虑下列准则：
在 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;PostgreSQL 中 TRUNCATE TABLE 用于删除表的数据，但不删除表结构。
也可以用 DROP TABLE 删除表，但是这个命令会连表的结构一起删除，如果想插入数据，需要重新建立这张表。
TRUNCATE TABLE 与 DELETE 具有相同的效果，但是由于它实际上并不扫描表，所以速度更快。 此外，TRUNCATE TABLE 可以立即释放表空间，而不需要后续 VACUUM 操作，这在大型表上非常有用。
PostgreSQL VACUUM 操作用于释放、再利用更新/删除行所占据的磁盘空间。
TRUNCATE TABLE table_name;PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。
视图创建可以从一张表，多张表或者其他视图。CREATE VIEW 基础语法如下：
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];您可以在 SELECT 语句中包含多个表，这与在正常的 SQL SELECT 查询中的方式非常相似。如果使用了可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。
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)
CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM COMPANY;
SELECT * FROM COMPANY_VIEW;
id | name | age
----+-------+-----
1 | Paul | 32
2 | Allen | 25
3 | Teddy | 23
4 | Mark | 25
5 | David | 27
6 | Kim | 22
7 | James | 24
(7 rows)要删除视图，只需使用带有 view_name 的 DROP VIEW 语句。DROP VIEW 的基本语法如下：
DROP VIEW view_name;事务具有以下四个标准属性，通常根据首字母缩写为 ACID：
某人要在商店使用电子货币购买100元的东西，当中至少包括两个操作：
支持事务的数据库管理系统就是要确保以上两个操作（整个”事务”）都能完成，或一起取消，否则就会出现 100 元平白消失或出现的情况。
事务可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时，事务处理也会回滚。以下是启动一个事务的简单语法：
BEGIN;
或者
BEGIN TRANSACTION;COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令，即确认事务。
COMMIT 命令的语法如下：
COMMIT;
或者
END TRANSACTION;ROLLBACK 命令是用于撤消尚未保存到数据库的事务命令，即回滚事务。
ROLLBACK 命令的语法如下：
开始一个事务，并从表中删除 age = 25 的记录，最后，我们使用 ROLLBACK 命令撤消所有的更改。
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)
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;
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开始另一个事务，从表中删除 age = 25 的记录，最后我们使用 COMMIT 命令提交所有的更改。
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;
# 检查 COMPANY 表，记录已被删除：
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(5 rows)锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。
在多个用户访问数据库的时候若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
数据库中有两种基本的锁：排它锁（Exclusive Locks）和 共享锁（Share Locks）。
如果数据对象加上排它锁，则其他的事务不能对它读取和修改。
如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。
LOCK [ TABLE ]
name
IN
lock_modelock_mode
：锁定模式指定该锁与哪个锁冲突。如果没有指定锁定模式，则使用限制最大的访问独占模式。可能的值是：ACCESS SHARE，ROW SHARE，
ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE，
SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE。一旦获得了锁，锁将在当前事务的其余时间保持。没有解锁表命令；锁总是在事务结束时释放。
当两个事务彼此等待对方完成其操作时，可能会发生死锁。尽管 PostgreSQL 可以检测它们并以回滚结束它们，但死锁仍然很不方便。为了防止应用程序遇到这个问题，请确保将应用程序设计为以相同的顺序锁定对象。
PostgreSQL 提供了创建具有应用程序定义含义的锁的方法。这些被称为咨询锁。由于系统不强制使用它们，所以正确使用它们取决于应用程序。咨询锁对于不适合 MVCC 模型的锁定策略非常有用。
例如，咨询锁的一个常见用途是模拟所谓”平面文件”数据管理系统中典型的悲观锁定策略。虽然存储在表中的标志可以用于相同的目的，但是通知锁更快，避免了表膨胀，并且在会话结束时由服务器自动清理。
Company表，数据内容如下
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)下面的示例将数据库中的 Company表锁定为 ACCESS EXCLUSIVE 模式，LOCK 语句只在事务模式下工作
BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;
# 上面操作将得到下面结果：
LOCK TABLE上面的消息指示表被锁定，直到事务结束，并且要完成事务，您必须回滚或提交事务。
子查询或称为内部查询、嵌套查询，指的是在 PostgreSQL 查询中的 WHERE 子句中嵌入查询语句。
一个 SELECT 语句的查询结果能够作为另一个语句的输入值。
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE
语句一起使用，并可使用运算符如
=、<、>、>=、<=、IN、BETWEEN 等。
以下是子查询必须遵循的几个规则：
子查询通常与 SELECT 语句一起使用。基本语法如下：
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])实例
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)
SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;
id | name | age | address | salary
----+-------+-----+-------------+--------
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
(2 rows)子查询也可以与 INSERT 语句一起使用。INSERT 语句使用子查询返回的数据插入到另一个表中。
在子查询中所选择的数据可以用任何字符、日期或数字函数修改。
基本语法如下：
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ] ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]实例
INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;子查询可以与 UPDATE 语句结合使用。当通过 UPDATE 语句使用子查询时，表中单个或多个列被更新。
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]实例
UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );子查询可以与 DELETE 语句结合使用，就像上面提到的其他语句一样。
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]实例
DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );AUTO INCREMENT（自动增长） 会在新记录插入表中时生成一个唯一的数字。
PostgreSQL 使用序列来标识字段的自增长，数据类型有 smallserial、serial 和 bigserial 。
这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。
使用 MySQL 设置自动增长的语句如下:
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;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
);实例
CREATE TABLE COMPANY(
ID SERIAL PRIMARY KEY,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );
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无论何时创建数据库对象，都会为其分配一个所有者，所有者通常是执行 create 语句的人。
对于大多数类型的对象，初始状态是只有所有者(或超级用户)才能修改或删除对象。要允许其他角色或用户使用它，必须为该用户设置权限。
在 PostgreSQL 中，权限分为以下几种：
根据对象的类型(表、函数等)，将指定权限应用于该对象。
要向用户分配权限，可以使用 GRANT 命令。
GRANT 命令的基本语法如下：
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }另外，我们可以使用 REVOKE 命令取消权限，REVOKE 语法：
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }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)现在给用户 “runoob” 分配权限：
runoobdb=# GRANT ALL ON COMPANY TO runoob;
GRANT信息 GRANT 表示所有权限已经分配给了 “runoob”。
下面撤销用户 “runoob” 的权限：
runoobdb=# REVOKE ALL ON COMPANY FROM runoob;
REVOKE你也可以删除用户：信息 DROP ROLE 表示用户 “runoob” 已经从数据库中删除。
runoobdb=# DROP USER runoob;
DROP ROLE基本算术操作符的行为
| 操作符 | 例子 | 结果 |
|---|---|---|
| + | date ‘2001-09-28’ + integer ‘7’ | date ‘2001-10-05’ |
| + | date ‘2001-09-28’ + interval ‘1 hour’ | timestamp ‘2001-09-28 01:00:00’ |
| + | date ‘2001-09-28’ + time ‘03:00’ | timestamp ‘2001-09-28 03:00:00’ |
| + | interval ‘1 day’ + interval ‘1 hour’ | interval ‘1 day 01:00:00’ |
| + | timestamp ‘2001-09-28 01:00’ + interval ‘23 hours’ | timestamp ‘2001-09-29 00:00:00’ |
| + | time ‘01:00’ + interval ‘3 hours’ | time ‘04:00:00’ |
| - | - interval ‘23 hours’ | interval ‘-23:00:00’ |
| - | date ‘2001-10-01’ - date ‘2001-09-28’ | integer ‘3’ (days) |
| - | date ‘2001-10-01’ - integer ‘7’ | date ‘2001-09-24’ |
| - | date ‘2001-09-28’ - interval ‘1 hour’ | timestamp ‘2001-09-27 23:00:00’ |
| - | time ‘05:00’ - time ‘03:00’ | interval ‘02:00:00’ |
| - | time ‘05:00’ - interval ‘2 hours’ | time ‘03:00:00’ |
| - | timestamp ‘2001-09-28 23:00’ - interval ‘23 hours’ | timestamp ‘2001-09-28 00:00:00’ |
| - | interval ‘1 day’ - interval ‘1 hour’ | interval ‘1 day -01:00:00’ |
| - | timestamp ‘2001-09-29 03:00’ - timestamp ‘2001-09-27 12:00’ | interval ‘1 day 15:00:00’ |
| * | 900 * interval ‘1 second’ | interval ‘00:15:00’ |
| * | 21 * interval ‘1 day’ | interval ‘21 days’ |
| * | double precision ‘3.5’ * interval ‘1 hour’ | interval ‘03:30:00’ |
| / | interval ‘1 hour’ / double precision ‘1.5’ | interval ‘00:40:00’ |
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| 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 | 按指定精度截断 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 | 判断日期是否为有限值（不是 ±infinity） | 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 天 = 1 个月调整 interval | justify_days(interval ‘35 days’) | 1 mon 5 days |
| justify_hours(interval) | interval | 按 24 小时 = 1 天调整 interval | justify_hours(interval ‘27 hours’) | 1 day 03:00:00 |
| justify_interval(interval) | interval | 同时进行 days 和 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 | 从多个字段创建 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 | 创建带时区时间戳 | 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 内置函数的列表：
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| abs(x) | same as input | 绝对值 | abs(-17.4) | 17.4 |
| cbrt(double) | double | 立方根 | cbrt(27.0) | 3 |
| ceil(double/numeric) | same as input | 不小于参数的最小整数 | ceil(-42.8) | -42 |
| degrees(double) | double | 把弧度转为角度 | degrees(0.5) | 28.6478897565412 |
| exp(double/numeric) | same as input | 自然指数 | exp(1.0) | 2.71828182845905 |
| floor(double/numeric) | same as input | 不大于参数的最大整数 | floor(-42.8) | -43 |
| ln(double/numeric) | same as input | 自然对数 | ln(2.0) | 0.693147180559945 |
| log(double/numeric) | same as input | 以10为底的对数 | log(100.0) | 2 |
| log(b numeric, x numeric) | numeric | 指定底数的对数 | log(2.0, 64.0) | 6.0000000000 |
| mod(y, x) | same as input | 取余数 | 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) | same as input | 四舍五入为最接近整数 | round(42.4) | 42 |
| round(v numeric, s int) | numeric | 保留 s 位小数 | round(42.438,2) | 42.44 |
| sign(double/numeric) | same as input | 返回符号 (-1,0,+1) | sign(-8.4) | -1 |
| sqrt(double/numeric) | same as input | 平方根 | sqrt(2.0) | 1.4142135623731 |
| trunc(double/numeric) | same as input | 截断（向 0 靠近） | 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) | 正切 |
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| 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 | 去掉字符串两端字符 | 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 | 删除两端指定字符 | btrim(‘xyxtrimyyx’,‘xy’) | trim |
| chr(int) | text | ASCII 转字符 | chr(65) | A |
| convert(string text, [src_encoding name,] dest_encoding name) | text | 转换字符编码 | convert(‘text_in_utf8’,‘UTF8’,‘LATIN1’) | LATIN1编码 |
| initcap(text) | text | 每个单词首字母大写 | initcap(‘hi thomas’) | Hi Thomas |
| length(string text) | int | 字符数 | length(‘jose’) | 4 |
| lpad(string text, length int [, fill text]) | text | 左侧填充 | lpad(‘hi’,5,‘xy’) | xyxhi |
| ltrim(string text [, characters text]) | text | 左侧删除字符 | ltrim(‘zzzytrim’,‘xyz’) | trim |
| md5(string text) | text | 计算 MD5 哈希 | md5(‘abc’) | 900150983cd24fb0d6963f7d28e17f72 |
| repeat(string text, number int) | text | 重复字符串 | repeat(‘Pg’,4) | PgPgPgPg |
| replace(string text, from text, to text) | text | 替换子字符串 | replace(‘abcdefabcdef’,‘cd’,‘XX’) | abXXefabXXef |
| rpad(string text, length int [, fill text]) | text | 右侧填充 | rpad(‘hi’,5,‘xy’) | hixyx |
| rtrim(string text [, character text]) | text | 删除结尾字符 | rtrim(‘trimxxxx’,‘x’) | trim |
| split_part(string text, delimiter text, field int) | text | 按分隔符取第 field 个部分 | split_part(‘abc@def@ghi’,‘@’,2) | def |
| strpos(string, substring) | int | 子字符串位置 | strpos(‘high’,‘ig’) | 2 |
| substr(string, from [, count]) | text | 提取子字符串 | substr(‘alphabet’,3,2) | ph |
| to_ascii(text [, encoding]) | text | 转为 ASCII | to_ascii(‘Karel’) | Karel |
| to_hex(number int/bigint) | text | 转为十六进制 | to_hex(9223372036854775807) | 7fffffffffffffff |
| translate(string text, from text, to text) | text | 字符映射替换 | 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 | 按指定格式把字符串转换为时间戳 | to_timestamp(‘05 Dec 2000’, ‘DD Mon YYYY’) |
| to_timestamp(double precision) | timestamp | 将 UNIX 时间戳转换为时间 | to_timestamp(1284352323) |
USE sql_store;#使用表 sql_store
SELECT first_name#从 customers 表中检索所有列
FROM customers;
# 检索多个列
SELECT DISTINCT first_name,last_name#使用 DISTINCT 避免列数据重复
FROM customers
LIMIT 5 OFFSET 5;# 只检索从第 5 行开始的 5 行
# 从 customers 表中检索所有列
SELECT *
FROM customers;
-- WHERE customer_id = 1;# 注释可以使用--
# 检索 customer_id 的那一行
# 总结:选择符合要求的列，选择符合要求的行
#SQL 语句中的注释
#使用# -- /**/USE sql_store;
SELECT *
FROM customers;
#使用 ORDER BY 进行排序（应该保证 ORDER BY 放在语句的最后面）
USE sql_inventory;
SELECT *
FROM products
ORDER BY quantity_in_stock;
#按照多个列进行行排序
USE sql_inventory;
SELECT *
FROM products
ORDER BY quantity_in_stock,unit_price;# 先按照 quantity_in_stock 排序再按照 unit_price 排序
#不用指定列的名字，指定是第几列
USE sql_inventory;
SELECT *
FROM products
ORDER BY 3,4;# quantity_in_stock 为第 3 行,unit_price 为第 4 行 alter
#先按照第三列排序在按照第四列排序
#指定排序的方向是升序还是降序
USE sql_inventory;
SELECT *
FROM products
ORDER BY 3 DESC;# quantity_in_stock 为第 3 行
#想为多个列进行排序指定排序方向必须为每个列指定 DESC
USE sql_inventory;
SELECT *
FROM products
ORDER BY 3 DESC,4 DESC;
#使用计算字段在 ORDER BY
SELECT *
FROM products
ORDER BY quantity_in_stock * unit_price DESC;# WHERE 子句
USE sql_inventory;
SELECT *
FROM products
WHERE unit_price = 1.21;
# WHERE 子句操作符
/* 符号 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
!< 不小于
> 大于
>= 大于等于
!> 不大于
BETWEEN A AND B 在指定的 A B 之间
IS NULL 为 NULL 值
*/USE sql_inventory;
SELECT *
FROM products;
# AND 操作符
SELECT *
FROM products
WHERE product_id>=5 AND quantity_in_stock>=70;
# OR 操作符
SELECT *
FROM products
WHERE product_id>=5 OR quantity_in_stock>=70;
# AND 与 OR 组合(AND 优先级大于 OR)
SELECT *
FROM products
WHERE (product_id>=5 OR quantity_in_stock>=70) AND unit_price>=2;
# IN 操作符
SELECT *
FROM products
WHERE quantity_in_stock IN (98,26,6);
# 等价于 quantity_in_stock==98 OR ...==26 OR ...==6
# NOT 操作符
SELECT *
FROM products
WHERE NOT (quantity_in_stock IN (98,26,6));
# BETWEEN AND 操作符
SELECT *
FROM products
WHERE quantity_in_stock BETWEEN 6 AND 90;
# IS NULL | IS NOT NULL
SELECT *
FROM products
WHERE quantity_in_stock IS NOT NULL;-- 同理 IS NULLUSE sql_inventory;
SELECT *
FROM products
WHERE name LIKE '%b%';#使用通配符%
SELECT *
FROM products
WHERE name LIKE '_o%';#_只匹配一个字符
-- 正则表达式 REGEXP
SELECT *
FROM products
WHERE name REGEXP 'filed';-- name 中含有 filed 的数据
SELECT *
FROM products
WHERE name REGEXP '^filed';-- name 以 filed 开头的数据
SELECT *
FROM products
WHERE name REGEXP 'filed$';-- name 以 filed 结尾的数据
SELECT *
FROM products
WHERE name REGEXP 'filed|mac|rose';-- name 中含有 filed 或 mac 或 rose 的数据
SELECT *
FROM products
WHERE name REGEXP '[gio]c';-- name 中含有 gc 或 ic 或 oc 的数据,同理可以 c[iod]
SELECT *
FROM products
WHERE name REGEXP '[a-z]c';-- ac bc dc ..........zc# 复习上一节，使用通配符
USE sql_inventory;
6
SELECT *
FROM products
WHERE unit_price<=5 AND ( name LIKE '%t%');
#这一节学习的是创建计算字段
#拼接字段（不同地数据库管理系统中的 SQL 语句语法有所不同）
SELECT concat('name: ',name,' price: ',unit_price)
FROM products
ORDER BY unit_price DESC;
#RTRIM()、LTRIM()、TRIM()函数
#函数功能:去掉值右边的空格、去掉值左边的空格、去掉值左边和右边的空格
SELECT concat(trim(name),trim(unit_price))
FROM products;
SELECT concat(rtrim(name),rtrim(unit_price))
FROM products;
#使用别名（或者称导出列）
SELECT concat(trim(name)) AS ProductName
FROM products;
#执行算数计算
SELECT name,quantity_in_stock*unit_price AS quantity_in_stockmulunit_price
FROM products;
#SQL 算数操作符 加+ 减- 乘* 除/
SELECT curdate(),name,quantity_in_stock/unit_price AS quantity_in_stockdivunit_price
FROM products;USE sql_inventory;
SELECT *
FROM products;
# UPPER()函数-字符全部变大写
SELECT upper(name) AS UPPERNAME
FROM products;
#常用的文本处理函数
/*
LEFT() 返回字符串左边的字符
7
LENGTH() 返回字符串的长度
LOWER() 将字符串换为小写
LTRIM() 去掉字符串左边的空格
RTRIM() 去掉字符串右边的空格
RIGHT() 返回字符串右边的字符
SUBSTR() 提取字符串的组成成分
SOUNDEX() 返回字符串的 SOUNEDx 值 alter （对英文友好）
UPPER() 将字符串转换为大写
*/
# 日期与时间处理函数
# MySQL 使用 YEAR() 函数从日期中提取年份
USE sql_invoicing;
SELECT *
FROM invoices
WHERE YEAR(invoice_date) != 2019;
USE sql_invoicing;
SELECT *
FROM invoices
WHERE YEAR(invoice_date) = 2019;
#数值处理函数
/*
ABS() 返回一个数的绝对值
COS() 返回一个角度的余弦
EXP() 返回一个数的指数值
PI() 返回圆周率
SIN() 返回一个角度的正弦
SQRT() 返回一个数的平方根
TAN() 返回一个角度的正切
*/
#获得当前时间
/*
NOW()
*/
--内建函数非常多，我们遇见问题时去搜索就好了USE sql_invoicing;
SELECT *
FROM invoices;
8
# 聚集函数
# 对某些行运行的函数，计算并返回一个值
/*
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
*/
# 使用样例
SELECT
AVG(invoice_total) AS avg,
COUNT(*) AS count,
MAX(invoice_total) AS max,
MIN(invoice_total) AS min,
SUM(invoice_total) AS sum
FROM invoices
WHERE invoice_total>110;
#添加 WHERE 子句，是先执行 WHERE 条件，然后再进行 SELECT 语句里函数计算
#聚集不同值
#DISTINCT 与 ALL（ALL 是默认的）
SELECT AVG(ALL client_id) AS avg_client_id
FROM invoices;
SELECT AVG(DISTINCT client_id) AS avg_client_id
FROM invoices;#复习上一节内容（汇总数据）ＡＶＧ ＳＵＭ ＭＩＮ ＭＡＸ ＣＯＵＮT 函数的使用
USE sql_invoicing;
SELECT AVG(distinct
client_id),SUM(payment_total),MIN(payment_total),MAX(payment_total),COUNT(*)
FROM invoices;
#本节内容
SELECT COUNT(*) AS client_idCOUNT#输出 17
FROM invoices;
SELECT COUNT(*) AS client_idCOUNT#输出 6,统计 client_id=5 的行共有 6 个
FROM invoices
WHERE client_id=5;
SELECT *#输出 17
9
FROM invoices;
#创建分组 GROUP BY 子句
#GROUP BY 子句的位置必须在 WHERE 子句之后 ORDER BY 子句之前
SELECT client_id,payment_total,COUNT(*) AS num
FROM invoices
GROUP BY client_id,payment_total;
/*
num client_id
5 1 即 client_id 为 3 的行有 5 个，为 5 的有 6 个
1 2
5 3
6 5
GROUP BY 子句更像是
SELECT COUNT(*) AS num
FROM
WHERE alient_id=something
的加强版
*/
SELECT client_id,payment_total,COUNT(*) AS num
FROM invoices
GROUP BY client_id,payment_total;#像 ORDER BY 一样也可以使用数字来指定列
#将会按照 client_id、payment_total 依次分组
#HAVING 过滤分组（HAVING 放在 GROUP 之后 ORDER BY 之前）
#例如只要组内数量超过一定数量的信息
SELECT client_id,payment_total,COUNT(*) AS num
FROM invoices
GROUP BY client_id,payment_total
HAVING COUNT(*)>=2#HAVING 以组为单位进行操作：则 COUNT(*)则是统计组内有多少行
ORDER BY num,payment_total,client_id;#分组后过滤然后排序输出
/*
下列语句顺序级大致用途
SELECT 返回的列或者表达式
FROM 从中检索数据的表
WHERE 行级过滤
GROUP BY 分组过滤
HAVING 组级过滤
ORDER BY 输出排序顺序
*/
--WITH ROLLUP
10
SELECT client_id,SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP;
client_id total_sales
1 802.89
2 101.79
3 705.90
5 980.02
2590.60
-- ALL KEYWORD
SELECT *
FROM invoices
WHERE invoice_total>(
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id=3
);
SELECT *
FROM invoices
WHERE invoice_total>ALL(
SELECT invoice_total
FROM invoices
WHERE client_id=2;
);
-- ANY
SELECT *
FROM invoices
WHERE invoice_total>ANY(
SELECT invoice_total
FROM invoices
WHERE client_id=2;
);
--EXISTS
SELECT *
FROM clients c
WHERE EXISTS(
SELECT client_id
FROM invoices
WHERE client_id=c.client_id
11
);# 子查询的两种主要使用目的
# 1、利用子句查询进行过滤
# 2、作为计算字段使用子查询
USE sql_invoicing;
SELECT *
FROM invoices;
# 利用子句查询进行过滤
SELECT invoice_total
FROM invoices
WHERE invoice_total>=170;#输出列 invoice_total:[175.32 189.12 172.17 180.17]
SELECT client_id
FROM invoices
WHERE invoice_total IN (#等于将子查询返回的一列作为筛选项使用
SELECT invoice_total
FROM invoices
WHERE invoice_total>=170
);
# 输出列 client_id[ 5 1 5 5]
#注意：可以多层嵌套，每个 SELECT 可以查询不同的表
#每个子查询返回的必须是一列，是多列会报错
# 作为计算字段使用子查询
USE sakila;
SELECT COUNT(*) AS SUM
FROM film
WHERE rental_duration>=6;
/*输出列:SUM [ 32 ]*/
#统计了 rental_duration>=6 的共有多少行
SELECT film_id,title,(SELECT description FROM film WHERE film.film_id = film_text.film_id)
AS film_description
FROM film_text;
/*分析:
从表 film_text 拿 title 与 film_id 在表 film 中拿 film_text 每行的 film_id 对应的 description
*/
# IF()
SELECT
12
IF (COUNT(*)>2,'yes','no') AS u;
#CASE WHEN
SELECT
CASE WHEN COUNT(*)>100 THEN '>100'
WHEN COUNT(*)<10 THEN '<10'
ELSE 'NULL'
END
AS judge;
--FROM 中的子查询
SELECT *
FROM(
SELECT client_id,name,...
FROM ..
) AS kkk
WHERE kkk.......#等值联结
SELECT vend_name,Vendors.prod_name,Products.prod_price
FROM Cendors,Products
WHERE Vendors.vend_id=Products.vend_id;
# 在联结两个表时，实际要做的是将表中的每一行与其他表的每一行进行配对
# 但会出现，在变的 id 有另一个没有，也就是可能会出现不能一一匹配
#内联结
SELECT vend_name,prod_name,prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id=Products.vend_id;-- 不写 INNER 则默认 INNER
JOIN
#联结多个表
SELECT prod_name,vend_name,prod_price,quantity
FROM OrderItems,Products,Vendors
WHERE Products.vend_id=Vendors.vend_id
AND OrderItems.prod_id=Products.prod_id
AND order_num=20007;
#先联结，后 order_num=20007 过滤行
13
#为表起别名（只是举例）
SELECT *
FROM order_items o
JOIN products p
ON o.name = p.name;
# 跨库联结
-- 例
-- 需要在表的前面加库名 lib4.testtable 代理 lib4 库下的 testtable 表
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
ON oi.product_id=p.product_id;
#自联结
SELECT *
FROM order_items oi
JOIN order_items e
ON oi.product_id=e.product_id;-- 没有意义,应在具体情况下使用
-- 库.表.列
#联结多张表
SELECT *
FROM atable a
JOIN btable b ON a.name=b.name
JOIN ctable ON a.id=c.id;
#多条联结条件
SELECT *
FROM atable a
JOIN btable b ON a.id=b.id AND a.name=b.name;
#外联结-OUTER JOIN
# |-左联结 LEFT OUTER JOIN 可缩写为 LEFT JOIN
# |-右联结 RIGHT OUTER JOIN 可缩写为 RIGHT JOIN
#多张表 外连接 自我外联结 （自我）左外联结 （自我）右外联结 略 与内联结差不多少
#MySQL USING
SELECT *
FROM a
14
JOIN b
USING(name,id);-- 等价于 ON a.name=b.name AND a.id=b.id
#自然联结
SELECT *
FROM orders o
NATURAL JOIN customers c;
#交叉联结
SELECT *
FROM atable a
CROSS btable b;
#集合交 UNION
# MySQL 貌似不支持 intersect except(但可以使用嵌套查询)
SELECT name
FROM a
WHERE age>10;
UNION
SELECT name
FROM b
WHERE age<6;USE sql_invoicing;
SELECT *
FROM clients;
SELECT *
FROM customers;
# 使用表别名
SELECT *
FROM clients AS A_TABLE,customers AS B_TABLE;
#使用不同类型的联结
#自联结(self-join)
SELECT c1.cust_id,c1.cust_name,c1.cust_contact
FROM customers AS c1,customers AS c2
15
WHERE c1.cust_name=c2.cust_name AND c2.cust_contact="Jim Jones";
#自然联结
SELECT C.cust_id,B.*
FROM customers AS C, clients AS B
WHERE C.cust_id=B.client_id+10000;
#左外联结
SELECT *
FROM customers
LEFT OUTER JOIN clients ON customers.cust_id=clients.client_id+10000;
#选中 OUTER JOIN 左边的表的全部行,哪怕没有关联行
#右外联结
SELECT *
FROM customers
RIGHT OUTER JOIN clients ON customers.cust_id=clients.client_id+10000;
#选中 OUTER JOIN 左边的表的全部行，哪怕没有关联行
#使用带聚集函数的联结
SELECT customers.cust_id,COUNT(clients.name) AS num
FROM customers
INNER JOIN clients ON customers.cust_id=clients.client_id+10000
GROUP BY customers.cust_id;
SELECT customers.cust_id,COUNT(clients.name) AS num
FROM customers
LEFT OUTER JOIN clients ON customers.cust_id=clients.client_id+10000
GROUP BY customers.cust_id;
#先联结，在分组，再 SELECT 统计计算等等USE sql_invoicing;
SELECT *
FROM clients;
# 使用 UNION ALL 不去掉重复的
SELECT *
FROM clients
WHERE clients.client_id<=3
UNION ALL
SELECT *
16
FROM clients
WHERE clients.client_id>=3;
# 使用 UNION 默认去掉重复的
SELECT *
FROM clients
WHERE clients.client_id<=3
UNION
SELECT *
FROM clients
WHERE clients.client_id>=3;USE sql_invoicing;
SELECT *
FROM clients;
# 插入完整的行
#INSERT INTO clients
#VALUES(10,"刘微","china","anyang","CN","133-456-8956");
#更规范的写法
#INSERT INTO clients(client_id,name,address,city,state,phone)
#VALUES
#(11,"刘微","china","anyang",DEFAULT,NULL),
#(13,"刘","china","anyang",DEFAULT,NULL);
#插入部分行
#只需要使用上面更规范的写法，进而可以指定哪些插入值
#插入检索出的数据
#INSERT INTO clients(client_id,name,address,city,state,phone)
#SELECT client_id,name,address,city,state,phone
#FROM clients;
#从一个表复制到另一个表
CREATE TABLE custcpoy AS SELECT * FROM clients;
SELECT *
FROM custcpoy;#添加行
17
INSERT INTO clients
VALUES(6,"刘微","china","anyang","CN","133-456-8956");
USE sql_invoicing;
SELECT *
FROM clients;
#更新数据 UPDATE SET
UPDATE clients
SET name="HELLO WORLD",address="USA"
WHERE client_id>=6;
USE sql_invoicing;
SELECT *
FROM clients;
#删除行
DELETE FROM clients
WHERE client_id>=6;
USE sql_invoicing;
SELECT *
FROM clients;
USE sql_invoicing;
#删除表
DROP TABLE Orders1;
#创建表
CREATE TABLE Orders1
(
order_num INTEGER NOT NULL DEFAULT 1,
order_date DATETIME NOT NULL,
cust_id CHAR(10) NULL
);
#使用 DEFAULT 指定默认值
# 不允许 NULL 值的列不接受没有列值的行，换句话说，在插入或更新行时，该列必须有值
# NOT NULL,NULL（默认值为 NULL）
INSERT INTO Orders1
VALUE(1,current_date(),"HELLO");
INSERT INTO Orders1
VALUE(2,current_date(),"HELLO");
SELECT *
FROM Orders1;
#更新表
18
#为表添加新的列
ALTER TABLE Orders1
ADD name CHAR(20) NULL DEFAULT "高万禄";
SELECT *
FROM Orders1;
#为表删除列
ALTER TABLE Orders1
DROP COLUMN cust_id;
SELECT *
FROM Orders1;
#表重命名
#RENAME Orders1 TO Orders;
#新版 MySQL 已经不支持 RENAMEUSE sql_invoicing;
SELECT *
FROM clients;
#删除表
DROP TABLE new_table;
#创建表
CREATE TABLE new_table
(
order_num CHAR(10) NOT NULL DEFAULT 1,
order_date CHAR(10) NOT NULL,
cust_id CHAR(10) NULL
);
#插入行
INSERT INTO new_table
VALUE("123","ddi","HELLO");
#显示表
SELECT *
FROM new_table;
#删除视图
19
#-覆盖(或更新)视图，必须先删除它，然后再重新创建
DROP VIEW new_table_view;
#创建视图（视图是在 SQL 解析时定义的虚拟的表，视图并不存在数据库,但对视图的数据修改会同步操
作表，视图像接口减少耦合）
CREATE VIEW new_table_view AS
SELECT *
FROM new_table;
#使用视图
SELECT *
FROM new_table_view;
#用视图重新格式化检索出的数据
DROP VIEW temp_view;
CREATE VIEW temp_view AS
SELECT concat(RTRIM(order_num),RTRIM(order_date),RTRIM(cust_id)) AS data
FROM new_table;
SELECT *
FROM temp_view;DELIMITER //
#删除存储过程
DROP procedure test;
#参数种类
#IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
#OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
#INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
#创建存储过程
DELIMITER $$
create procedure test(in a integer,in b integer,OUT num INTEGER)
BEGIN
SELECT *
FROM clients;
set a=2*a;
SELECT @a;#用户变量
SELECT a+b;
num=a+b;
END$$
20
DELIMITER ;
SET @num=0;
CALL test(1,2,@num);
SELECT @num;
#删除存储过程
DROP PROCEDURE IF EXISTS test;
#IF THEN ELSE END IF
DELIMITER $$
create procedure test(in a integer,in b integer)
BEGIN
IF a IS NULL THEN
SET a=2;
ELSE
SET b=100;
END IF;
SELECT *
FROM clients;
set a=2*a;
SELECT @a;#用户变量
SELECT a+b;
END$$
DELIMITER ;
#删除存储过程
DROP PROCEDURE IF EXISTS test;
#使用 SIGNAL 抛出异常
BEGIN
IF pay<=0 THEN
SIGNAL SQLSTATE '22003'
SET MESSAGE_TEXT='Invalid pay amount';
END IF;
END
#变量
-- |-User or Session variables
-- |SET @num=10;set @a=20;
-- |-Local variable
-- |DECLARE risk FLOAT4 DEFAULT 0;
-- SELECT COUNT(*),SUM(invoice_total)
-- INTO @num,@a
-- FROM invoices;
#局部变量的声明一定放在存储过程的开始
#DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
21
#形如 MySQL 的数据类型，如: int, float, date,varchar(length)
#DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
#变量赋值
#SET 变量名 = 表达式值 [,variable_name = expression ...]
#用户变量有全局性，有点像全局变量
#通常以@开头
#调用存储过程
set @a=2;
call test(2,2);
#if-then-else-endif 语句
#if __ then
# todo
#else
# todo
#end if;
#case 语句
# -> case var
# -> when 0 then
# -> insert into t values(17);
# -> when 1 then
# -> insert into t values(18);
# -> else
# -> insert into t values(19);
# -> end case;
#循环语句
# -> while var<6 do
# -> insert into t values(var);
# -> set var=var+1;
# -> end while;
#do while 语句
# -> repeat
# -> insert into t values(v);
22
# -> set v=v+1;
# -> until v>=5
# -> end repeat;
#loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件,
leave 语句的意义是离开循环。
# -> LOOP_LABLE:loop
# -> insert into t values(v);
# -> set v=v+1;
# -> if v >=5 then
# -> leave LOOP_LABLE;
# -> end if;
# -> end loop;
# ITERATE 迭代
#ITERATE 通过引用复合语句的标号,来从新开始复合语句:
# -> LOOP_LABLE:loop
# -> if v=3 then
# -> set v=v+1;
# -> ITERATE LOOP_LABLE;
# -> end if;
# -> insert into t values(v);
# -> set v=v+1;
# -> if v>=5 then
# -> leave LOOP_LABLE;
# -> end if;
# -> end loop;
-- FUNCTIONS
-- 建立自己的函数：像聚集函数一样例如 MIN MAX SUM 等
-- 函数与存储过程很像，但是区别就是，函数只能返回单一的值
-- 与存储过程不同，函数不能返回有行有列的结果集
CREATE FUNCTION get_risk_factor_for_client
(
client_id INT
)
RETURNS INTEGER
--DETERMINISTIC
READS SQL DATA
MODIFIES SQL DATA
BEGIN
23
RETURN 1;
END
--删除函数
DROP FUNCTION IF EXISTS get_risk_factor_for_client;
#创建触发器:例
DELIMITER $$
CREATE TRIGGER payments_after_insert
AFTER--BEFORE
DELETE-- INSERT UPDATE
ON payments
FOR EACH ROW-- 加入插入了 5 行，每行都会执行，否则执行一次
BEGIN
UPDATE invoices
SET payment_total=payment_total+NEW.amount
WHERE invoice_id=NEW.invoice_id;
END$$
DELIMITER ;
-- NEW:新行元组 OLD:老行元组
-- 查看触发器
SHOW TRIGGERS LIKE 'payments%';
--删除触发器
DROP TRIGGER IF EXISTS payments_after_insert;
--事件 Events
--定时执行
SHOW VARIABLES;--mysql 中的环境变量
--开启事件
SHOW VARIABLES LIKE 'event%';
--event_scheduler ON
SET GLOBAL event_scheduler=ON--OFF
#创建事件
DELIMITER $$
CREATE EVENT yearly_delete_audit_rows
ON SCHEDULE
AT '2021-05-26'
--EVERY 1 HOUR-- 2 DAY --2 YEAR
24
--EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
DO BEGIN
DELETE FROM payments_audit
WHERE action_date<NOW()-INTERVAL 1 YEAR;
END $$
DELIMITER ;
-- 查看事件
SHOW EVENTS;
-- 删除事件
DROP EVENT IF EXISTS yearly_delete_audit_rows;
--修改事件
DELIMITER $$
ALTER EVENT yearly_delete_audit_rows
ON SCHEDULE
AT '2021-05-28'
--EVERY 1 HOUR-- 2 DAY --2 YEAR
--EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
DO BEGIN
DELETE FROM payments_audit
WHERE action_date<NOW()-INTERVAL 1 YEAR;
END $$
DELIMITER ;
--启动事件
ALTER EVENT yearly_delete_audit_rows ENABLE;
--关闭事件
ALTER EVENT yearly_delete_audit_rows DISABLE;-- 原子性 一致性 隔离性 持久性
25
USE gaowanlu;
#如何利用 COMMIT 和 ROLLBACK 语句管理事务处理;
#事务(transaction)指一组 SQL 语句
#回退(rollback)指撤销指定 SQL 语句的过程
#提交(commit)指将为存储的 SQL 语句结果写入数据库表
#保留点(savepoint)指事务处理中设置的临时占位符(placeholder)
#可以对它发布退回(与回退整个事务处理不同)
#可以回退哪些语句?
/*事务处理用来管理 INSERT UPDATE DELETE 语句。不能回退 SELECT 语句（回退 SELECT 语句也没
有必要）,
也不能回退 CREATE DROP 操作，事务处理中可以使用这些操作，但进行回退时，这些操作也不撤回。
*/
#控制事务处理
START TRANSACTION;
INSERT INTO person VALUE(5,"wangming");
INSERT INTO person VALUE(6,"wangming");
ROLLBACK;#撤销 INSERT UPDATE DELETE 操作
COMMIT; #使用 COMMIT 提交事务处理结果
#使用保留点
START TRANSACTION;
INSERT INTO person VALUE(5,"wangming");
SAVEPOINT addwangming;
INSERT INTO person VALUE(6,"xiao");
ROLLBACK TO addwangming;
COMMIT;
SELECT *
FROM person;如果不同的事务操纵了相同的数据（同时要进行），不可能二这同时进行，谁先执行，会将其上锁，后面
的只能等待解锁，在进行操作
26
-- 并发问题:数据丢失 读脏数据 不可重复读 幻读
-- 事务隔离等级
SHOW VARIABLES LIKE 'transaction_isolation';
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 也可以在当前的 session 或者连接中修改隔离等级;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 也可以为所有 session 的所有事务设置全局等级
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--未提交读取等级:可以读到另一个事务为提交 但已经改变的数据 未提交读取是最低的隔离等级
USE db;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--提交读取等级:解决读脏数据问题 只能读取提交完毕的数据 但可能得到不可重复，或者说不稳定的
读取
USE db;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
--可重复读取等级
USE db;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 幻读用 可重复读取等级 都不能解决
-- 序列化等级
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
27
-- 死锁
A 等待 B B 等待 A
当遇到死锁时一般系统会回滚事务 A 与事务 B
-- MySQL 数据类型
String \Numeric \Date and Time\Blob(二进制数据)\Spatial(存放地理数据)
-- 字符串
CHAR(X) 固定长度
VARCHAR(X) 可变长度 最大的长度是 6 万 5 千多
MUEDIUMTEXT 16MB 最多 1 千 6 百万
LONGTEXT 4GB
TINYTEXT 255 bytes
TEXT 64KB
English 1 byte 中东:2bytes 中日:3byte
-- 整数 INTEGERS
TINYINT 1b [-128,127]
UNSIGNED TINYINT [0,255]
SMALLINT 2b [-32l,32k]
MEDIUMINT 3b[-8M,8M]
INT 4b [-2B,2B]
BIGINT 8b [-9Z,9Z]
-- 小数类型 RATIONALS
DECIMAL(p,s) DECIMAL(9,2)=>1234567.89
DEC
NUMERIC
FIXED
-------------
FLOAT 4b
DOUBLE 8b
28
-- BOOLEANS 类型
BOOL
BOOLEAN
UPDATE posts
SET is_published=TRUE # or FALSE
TRUE<-->1 FALSE<-->0
-- 枚举类型
例:ENUM(1,2,3)
ENUM('a','bb','ccc')
SET(...)
-- DATE/TIME
DATA
TIME
DATETIME 8b
TIMESTAMP 4b --通常使用于 TIMESTAMP 事件戳来记录数据插入和最后修改的时间 最大 2038
YEAR
-- BLOBS 二进制数据
TINYBLOB 255b
BLOB 65KB
MEDIUMBLOB 16MB
LONGBLOB 4GB
-- JSON
JSON-- 具体使用可以进行查询
-- 建立索引成本
COST OF INDEXES
- 增加数据库的大小（它需要和表一起保存）
- 每次增删改数据时，MySQL 会自动更新索引，会影响当前操作的效率
-- 我们只需要给特别重要的查询添加索引(数据量大，但频繁检索)
-- 不要在设计表的时候就创建好索引，不要以表来创建索引、要以查询内容来创建
29
-- 在内部：索引通常是以二叉树的方式保存的
-- 创建索引
#检索时对比了多大的数据量
EXPLAIN SELECT customer_id FROM customers WHERE state='US';
CREATE INDEX idx_state ON customers(state);
EXPLAIN SELECT customer_id FROM customers WHERE state='US';
--查看索引
SHOW INDEXES IN customers;
只要给表添加了主键，引擎自动会对主键加索引、聚合索引(主键索引) clustered INDEX
ANALYZE TABLE customers;
每张表只能有一个聚合索引
其他的索引是，从属索引 secondary indexes
外码也会自动加索引
如果索引的列为 CHAR VARCHAR TEXT BLOB,索引就会占用大量的磁盘空间、并且性能表现也不好
-- 解决方案，对数据部分内容建立索引
CREATE INDEX idx_lastname ON customers (last_name(20))
--怎样挑选一个不错的值
SELECT
COUNT(DISTINCT LEFT(last_name,1)),
COUNT(DISTINCT LEFT(last_name,5)),
COUNT(DISTINCT LEFT(last_name,10))
FROM customers;
-- 全文索引 索引类型
问题
USE sql_blog;
SELECT *
FROM posts
WHERE title LIKE '%rect%' OR body LIKE '%rect%';
数据量大的时候将会非常慢
-- 创建全文索引
CREATE FULLTEXT INDEX idx_title_body ON posts(title,body);
-- 使用全文索引
SELECT *
FROM posts
30
WHERE MATCH(title,body) AGAINST('rect');
-- MATCH(title,body) AGAINST('rect -redux +form' IN BOOLEAN MODE) 含 rect 但不含
redux 含 form
-- 组合索引
EXPLAIN SELECT customer_id FROM customers
WHERE state='CA' AND points>1000;
CREATE INDEX idx_state_points ON customers(state,points);
-- 删除索引
DROP INDEX idx_state ON customers;
--组合索引的顺序问题
-- 将最常用的列放到最前面
--ＵＳＥ ＩＮＤＥＸ
ＳＥＬＥＣＴ ｃｕｓｔｏｍｅｒ＿ｉｄ
ＦＲＯＭ ｃｕｓｔｏｍｅｒｓ
ＵＳＥ ＩＮＤＥＸ（ｉｄｘ＿ｓｔａｔｅ＿ｌａｓｔｎａｍｅ）
ＷＨＥＲＥ ｓｔａｔｅ ＬＩＫＥ ＇Ａ％＇ ＡＮＤ ｌａｓｔ＿ｎａｍｅ ＬＩＫＥ ＇Ａ％＇；
--有时候这样写效率会更好
ＳＥＬＥＣＴ *
ＦＲＯＭ ｃｕｓｔｏｍｅｒｓ
ＷＨＥＲＥ ｓｔａｔｅ ＬＩＫＥ ＇Ａ％＇
UNION
ＳＥＬＥＣＴ *
ＦＲＯＭ ｃｕｓｔｏｍｅｒｓ
WHERE ｌａｓｔ＿ｎａｍｅ ＬＩＫＥ ＇Ａ％＇；
--防止建立相同的索引
(A,B,C) == (A,B,C)
(A,C,B) != (A,B,C)
--防止建立无用索引
建立了(A,B)
再建立(A) (A)为无用索引-- 创建用户
CREATE USER `john@允许访问的 ip` IDENTIFIED BY '1232nfdb1dvfd3nhng13fdv';
-- 查看用户
SELECT * FROM mysql.user;
--删除用户
DROP USER `john@允许访问的 ip`;
--修改密码
SET PASSWORD FOR `john@允许访问的 ip`='1234frfe';
--为当前登录的用户更改密码
SET PASSWORD ='2324234';
--授予权限
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE
ON sql_store.*
TO `john@允许访问的 ip`；
--授予能够创建表\创建触发器\修改现有表
PRIVILEGES provided by mysql,summary of available PRIVILEGES
GRANT ALL
ON sql_store.* -- *.*
TO `john@允许访问的 ip`;
-- width grant option(不仅允许用户拥有这个权限，还可以将授予的权限再授予给他人)
--查看权限
SHOW GRANTS FOR `john@允许访问的 ip`;
--查看当前用户权限
SHOW GRANTS;
32
--撤销权限
REVOKE CREATE VIEW
ON sql_store.*
FROM `john@允许访问的 ip`;
1、with admin option 用于系统权限授权，with grant option 用于对象授权。
2、给一个用户授予系统权限带上 with admin option 时，此用户可把此系统权限授予其他用户或角
色，
但收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效，
如授予 A 系统权限 create session with admin option,然后 A 又把 create session 权限授予 B,
但管理员收回 A 的 create session 权限时，B 依然拥有 create session 的权限，
但管理员可以显式收回 B create session 的权限，即直接 revoke create session from B.
而 with grant option 用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，
不同的是但管理员收回用 with grant option 授权的用户对象权限时，权限会因传播而失效，
如：grant select on 表名 to A with grant option;，A 用户把此权限授予 B，但管理员收回 A 的权
限时，
B 的权限也会失效，但管理员不可以直接收回 B 的 SELECT ON TABLE 权限。 执行授权语句报错
（ora-01031，ora-01929）时，可参考一下。
相同点：
- 两个都可以既可以赋予 user 权限时使用，也可以在赋予 role 时用 GRANT CREATE SESSION TO
emi WITH ADMIN OPTION;
GRANT CREATE SESSION TO role WITH ADMIN OPTION; GRANT role1 to role2 WITH
ADMIN OPTION;
GRANT select ON customers1 TO bob WITH GRANT OPTION; GRANT select ON
customers1 TO hr_manager(role) WITH GRANT OPTION;
- 两个受赋予者，都可以把权限或者 role 再赋予 other users - 两个 option 都可以对 DBA 和
APP ADMIN 管理带来方便性，但同时，
都带来不安全的因素
不同点： - with admin option 只能在赋予 system privilege 的时使用 - with grant option 只
能在赋予
object privilege 的时使用
- 撤消带有 admin option 的 system privileges 时，连带的权限将保留
--加 with grant option
A->B
则 B 可->C
但 C！——》A
--REVOKE
33
REVOKE<权限>[,<权限>]...
ON <对象类型><对象名>[,<对象类型><对象名>]...
FROM <用户>[,<用户>]...[CASCADE|RESTRICT]
例:
REVOKE INSERT
ON TABLE SC
FROM U5 CASCADE;
U5->U6->U7
撤销了 U5 同时撤销了 U6 U7
--创建数据库模式的权限
CREATE USER <username> [WITH] [DBA|RESOURCE|CONNECT]
只有超级用户才可以创建一个新的数据库用户
--使用角色
CREATE ROLE <角色名>;
--给角色授予权限
GRANT <权限>,[....]
ON <对象类型>对象名
TO 角色、角色...;
-- 将角色授予其他的角色或用户
GRANT 角色、角色...
TO 角色、用户...
[WITH ADMIN OPTION]
--添加 WITH ADMIN OPTION，则获得某种权限的角色和用户还可以把这种权限授予给其他的角色;
--角色权限的收回
REVOKE 权限、权限
ON <对象类型><对象名>
FROM 角色、角色;1. 游标(cursor)：是一个存储在 MySQL 服务器上的数据库查询，它不是一条 select 语句，而是被该语
句检索出来的结果集。
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。
游标只能用于存储过程和函数。
2. 使用游标的步骤：
1) 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的
select 语句。
2) 一旦声明后，必须打开游标以供使用。这个过程用前面定义的 select 语句把数据实际检索出来。
3) 对于填有数据的游标，根据需要去除（检索）各行。
4) 在结束游标使用时，必须关闭游标。
3. 在一个游标被打开后，可以使用 fetch 语句分别访问它的每一行。fetch 指定检索什么数据（所需的
列），检索出来的数据存储在什么地方。它还向前移动游标中的内部指针，使下一条 fetch 语句检索下一
行（不重复读取同一行）。
*/
DROP procedure if exists fun;#永久删除游标
DROP TABLE if exists copy;
create table copy
(
order_num INTEGER NOT NULL,
cust_id CHAR(10) NOT NULL
);
DELIMITER //
CREATE procedure fun()
BEGIN
DECLARE num INTEGER;
DECLARE nam_e CHAR(10);
#创建游标
declare cur cursor for SELECT * FROM person;
open cur;#打开游标
FETCH cur INTO num,nam_e;
INSERT INTO copy VALUE(num,nam_e);
close cur;#关闭游标
END;//
DELIMITER ;
call fun();
SELECT *
FROM copy;
#每 fetch 一回就会向下自动迭代一行，类似于 C 语言中的文件读取。USE gaowanlu;
#创建表时添加主键
35
#CREATE TABLE data
#(
# table_id INTEGER NOT NULL PRIMARY KEY,
# user_name CHAR(50) NOT NULL,
# user_password CHAR(50) NOT NULL
#);
#修改表时定义主键
#ALTER TABLE data
#ADD CONSTRAINT PRIMARY KEY(table_id);
#翻译-constaint(约束)：管理如何插入或处理数据库数据的原则
#外键:
#是表中的一列，其值必须在另一表的主键中
#也就是 templae 表中的 data_table_id 任意都是 data 中的 table_id
#外键是保证引用完整性的及其重要部分。
#CREATE TABLE template
#(
# table_id INTEGER NOT NULL PRIMARY KEY,
# data_table_id INTEGER NOT NULL REFERENCES data(table_id)
#);
#修改表时定义外键
#ALTER TABLE template
#ADD CONSTRAINT
#FOREIGN KEY (data_table_id) REFERENCES data (table_id);
#唯一约束
#类似于表的主键，但与主键不同的是一个表可以有多个唯一约束
#CREATE TABLE template_1
#(
# id INTEGER NOT NULL PRIMARY KEY,
# name CHAR(20) NOT NULL UNIQUE
#);
#修改表时添加唯一约束
#ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
#ALTER TABLE template_1
#ADD CONSTRAINT
#unique_name UNIQUE(name);
#删除唯一约束
#ALTER TABLE <表名> DROP INDEX <唯一约束名>;
36
#检查约束
#检查约束能用来保证一列(或一组列)中的数据满足一组指定的条件
# 值大小 范围(如日期) 只允许特定的值
/*
CREATE TABLE template_2
(
id INTEGER NOT NULL PRIMARY KEY,
num INTEGER NOT NULL CHECK (num>0 AND num<100),
str CHAR(10) NOT NULL CHECK (str LIKE '[MF]')
);
*/
#str 只包含 M 或 F
#索引
#主键总是有序的，索引就像 hash 表一样快速找到目标
#CREATE INDEX num_ind
#ON template_2(num);
#CREATE INDEX <索引名称> ON <表名>(<列名>)
#触发器
/*
*触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行
*触发器可以与特定表上的 INSERT、UPDATE、DELETE 操作(或组合)相关联
*
* CREATE <触发器名> < BEFORE | AFTER >
* <INSERT | UPDATE | DELETE >
* ON <表名> FOR EACH Row<触发器主体>
*/
#BEFORE 触发器
#mysql> CREATE TRIGGER SumOfSalary
# -> BEFORE INSERT ON tb_emp8
# -> FOR EACH ROW
# -> SET @sum=@sum+NEW.salary;
#AFTER 触发器
#mysql> CREATE TRIGGER double_salary
# -> AFTER INSERT ON tb_emp6
# -> FOR EACH ROW
# -> INSERT INTO tb_emp7
# -> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);
#序列
#MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键
37
#如果你想实现其他字段也实现自动增加
#使用 AUTO_INCREMENT
#创建了数据表 insect
#insect 表中的 id 无需指定值可实现自动增长
#CREATE TABLE template_3
#(
# id INT unsigned NOT NULL auto_increment,
# primary key(id),
# user_name CHAR(10) NOT NULL
#);
INSERT INTO template_3
VALUES
(NULL,"gao"),
(NULL,"zhang"),
(NULL,"li");
SELECT *
FROM template_3;
-- conceptual-》 logical-》 physical
-- 概念 逻辑 实体
ER 关系 一张张表
-- ER 图绘制
www.draw.io
-- 1NF
没有重复的行，每条数据可以唯一确定
-- 2NF
满足 1NF
没有依赖任何关系的其他子集的非主键字段
-- 2NF 告诉我们，每张表都应该是单一功能的，换句话说，它仅能表示一个实体类型
-- 这张表的所有字段都是用来描述这个特定的实体的
ORDERS 1NF
order_id date name
1 ... hi
2 ... hi
2NF
ORDERS 1NF
order_id date customer_id
1 ... 1
1 ... 1
CUSTOMERS
customer_id name
1 hi
-- 3NF
满足 2NF
所有表中的字段都只依赖于主键与其他的字段值无关
INVOICES
... invoice_total payment_total balance
100 40 60(100-40)
blance 依赖于 invoice_total payment_total
-- 不要设想数据库模型将来要应对的情况，一般想出来的情况都不会出现
-- 只会让我们的解决方案变得复杂
-- 只关注眼前的需求找出最佳解法
-- 而不是考虑还没发生的问题🚪 什么是 INNER JOIN
INNER JOIN（内连接）表示：取两个表中，满足连接条件的那部分数据。 如果在 X 表和 Y 表里，某一行 KEY 值相同，那么这两行会“拼接”成一行出现在结果里。
📖 示例
假设有两个表：
表 X
| KEY | X_VAL |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
表 Y
| KEY | Y_VAL |
|---|---|
| 2 | BB |
| 3 | CC |
| 4 | DD |
执行：
SELECT *
FROM X
INNER JOIN Y ON X.KEY = Y.KEY;结果：
| KEY | X_VAL | KEY | Y_VAL |
|---|---|---|---|
| 2 | B | 2 | BB |
| 3 | C | 3 | CC |
✅ 总结口诀
INNER JOIN = “交集 + 拼接”。
🚪 什么是 FULL OUTER JOIN
FULL JOIN（有时写作 FULL OUTER JOIN）表示：
把 X 表和 Y 表的所有数据都保留下来。
如果某一边找不到匹配，就用 NULL 补齐。
可以理解为：LEFT JOIN + RIGHT JOIN 的并集。
📖 示例
还用刚才的两个表：
表 X
| KEY | X_VAL |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
表 Y
| KEY | Y_VAL |
|---|---|
| 2 | BB |
| 3 | CC |
| 4 | DD |
执行：
SELECT *
FROM X
FULL JOIN Y ON X.KEY = Y.KEY;结果：
| KEY | X_VAL | KEY | Y_VAL |
|---|---|---|---|
| 1 | A | NULL | NULL |
| 2 | B | 2 | BB |
| 3 | C | 3 | CC |
| 4 | NULL | 4 | DD |
虽然 SQL 里没有这个正式关键字，但很多人会用这个写法：
SELECT *
FROM X
FULL JOIN Y ON X.KEY = Y.KEY
WHERE X.KEY IS NULL OR Y.KEY IS NULL它的含义是：
所以可以理解为：
FULL OUTER JOIN - INNER JOIN = 对称差集（exclusive部分）
📖 示例
还是用之前的两个表：
表 X
| KEY | X_VAL |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
表 Y
| KEY | Y_VAL |
|---|---|
| 2 | BB |
| 3 | CC |
| 4 | DD |
执行上面查询，结果是：
| KEY (X) | X_VAL | KEY (Y) | Y_VAL |
|---|---|---|---|
| 1 | A | NULL | NULL |
| NULL | NULL | 4 | DD |
🚪 什么是 LEFT JOIN
LEFT JOIN（也叫 LEFT OUTER JOIN）表示：
可以理解为：“左边全要，右边能对上就对上”。
📖 示例
继续用之前的两个表：
表 X
| KEY | X_VAL |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
表 Y
| KEY | Y_VAL |
|---|---|
| 2 | BB |
| 3 | CC |
| 4 | DD |
执行：
SELECT *
FROM X
LEFT JOIN Y ON X.KEY = Y.KEY;结果：
| KEY (X) | X_VAL | KEY (Y) | Y_VAL |
|---|---|---|---|
| 1 | A | NULL | NULL |
| 2 | B | 2 | BB |
| 3 | C | 3 | CC |
虽然 SQL 没有官方关键字 LEFT EXCLUSIVE，但通常大家会用下面的写法：
SELECT *
FROM X
LEFT JOIN Y ON X.KEY=Y.KEY
WHERE Y.KEY IS NULL;它的含义是：
表X
| KEY | X_VAL |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
表Y
| KEY | Y_VAL |
|---|---|
| 2 | BB |
| 3 | CC |
| 4 | DD |
执行查询结果：
| KEY (X) | X_VAL | KEY (Y) | Y_VAL |
|---|---|---|---|
| 1 | A | NULL | NULL |
RIGHT JOIN（也叫 RIGHT OUTER JOIN）就是 LEFT JOIN 的镜像版：
“右边全要，左边能对上就对上”。
表 X
| KEY | X_VAL |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
表 Y
| KEY | Y_VAL |
|---|---|
| 2 | BB |
| 3 | CC |
| 4 | DD |
执行：
SELECT *
FROM X
RIGHT JOIN Y ON X.KEY = Y.KEY;结果：
| KEY (X) | X_VAL | KEY (Y) | Y_VAL |
|---|---|---|---|
| 2 | B | 2 | BB |
| 3 | C | 3 | CC |
| NULL | NULL | 4 | DD |
🚪 什么是 “RIGHT EXCLUSIVE”
虽然 SQL 里没有关键字 RIGHT EXCLUSIVE，但常见写法是：
SELECT *
FROM X
RIGHT JOIN Y ON X.KEY = Y.KEY
WHERE X.KEY IS NULL;它的含义是：
表 X
| KEY | X_VAL |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
表 Y
| KEY | Y_VAL |
|---|---|
| 2 | BB |
| 3 | CC |
| 4 | DD |
执行上面的查询，结果是：
| KEY (X) | X_VAL | KEY (Y) | Y_VAL |
|---|---|---|---|
| NULL | NULL | 4 | DD |
下面是一个 mysqld_safe 的样例。
root 1198 1 0 11月23 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --user=mysql --port=12028 --server-id=12028 --datadir=/data/Database/mysql_12028 --socket=/data/Database/mysql_12028/mysql.sock --pid-file=/var/run/mysqld/mysqld_12028.pid --back_log=210 --skip-name-resolve --wait_timeout=600 --max_connections=2048 --max_connect_errors=2048 --open_files_limit=102400 --interactive_timeout=600 --character_set_server=latin1 --innodb_file_per_table=1 --innodb_file_format=Barracuda --innodb_file_format_max=Barracuda --slow_query_log=ON --long_query_time=3 --log-error=/var/log/mysqld_12028.log --slow_query_log_file=/var/log/mysqld_slow_12028.log --log-bin=/data/DB_BinLog/mysql_12028/mysql-bin --relay-log=/data/DB_BinLog/mysql_12028/mysql-relay-bin --replicate-wild-ignore-table=mysql.% --replicate-wild-ignore-table=performance_schema.% --replicate-wild-ignore-table=information_schema.% --max_binlog_size=300m --expire_logs_days=7mysqld_safe本身的作用
/usr/bin/mysqld_safe是MySQL的守护进程启动器，它会：
真正的数据库进程是随后启动的mysqld.
主要启动参数逐项说明
基础参数
| 参数 | 说明 |
|---|---|
--user=mysql |
指定 mysqld 用 mysql 用户运行，提高安全性。 |
--port=12028 |
MySQL 服务监听的端口。 |
--server-id=12028 |
唯一的服务器 ID，用于主从复制。 |
--datadir=/data/Database/mysql_12028 |
MySQL 数据文件存放目录。 |
--socket=/data/Database/mysql_12028/mysql.sock |
Unix Socket 文件路径（本地连接用）。 |
--pid-file=/var/run/mysqld/mysqld_12028.pid |
PID 文件路径，用于管理进程。 |
连接与性能参数
| 参数 | 说明 |
|---|---|
--back_log=210 |
TCP 半连接队列长度，连接高峰时减轻拒绝连接。 |
--skip-name-resolve |
禁止反向 DNS 查询，加速连接（常用优化）。 |
--wait_timeout=600 |
非交互连接最大空闲时间（秒）。 |
--interactive_timeout=600 |
交互连接（如命令行）的空闲超时。 |
--max_connections=2048 |
最大连接数。 |
--max_connect_errors=2048 |
最大连续连接失败次数，超过将阻止 IP（防攻击）。 |
--open_files_limit=102400 |
最大文件句柄数，影响并发能力。 |
字符集与存储设置
| 参数 | 说明 |
|---|---|
--character_set_server=latin1 |
默认字符集（这里用的是 latin1，不是 utf8）。 |
--innodb_file_per_table=1 |
每张表独立 tablespace，便于管理。 |
--innodb_file_format=Barracuda |
使用 Barracuda 文件格式（支持更大行压缩）。 |
--innodb_file_format_max=Barracuda |
最大支持的文件格式也是 Barracuda。 |
慢查询日志
| 参数 | 说明 |
|---|---|
--slow_query_log=ON |
开启慢查询日志。 |
--long_query_time=3 |
执行超过 3 秒的 SQL 记录下来。 |
--log-error=/var/log/mysqld_12028.log |
错误日志路径。 |
--slow_query_log_file=/var/log/mysqld_slow_12028.log |
慢查询日志路径。 |
主从复制与binlog
| 参数 | 说明 |
|---|---|
--log-bin=/data/DB_BinLog/mysql_12028/mysql-bin |
开启 binlog（用于复制 / point-in-time 恢复）。 |
--relay-log=/data/DB_BinLog/mysql_12028/mysql-relay-bin |
relay log（从库使用）。 |
--replicate-wild-ignore-table=mysql.% |
复制时忽略 mysql.* 表。 |
--replicate-wild-ignore-table=performance_schema.% |
忽略 performance_schema.*。 |
--replicate-wild-ignore-table=information_schema.% |
忽略 information_schema.*。 |
--max_binlog_size=300m |
单个 binlog 文件最大 300MB。 |
--expire_logs_days=7 |
binlog 保留 7 天。 |
下面这个进程是真正运行的mysqld 主进程，它的参数和前面的mysqld_safe类似。
mysql 2842 1198 0 11月23 ? 00:10:01 /usr/sbin/mysqld --basedir=/usr --datadir=/data/Database/mysql_12028 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --server-id=12028 --back-log=210 --skip-name-resolve --wait-timeout=600 --max-connections=2048 --max-connect-errors=2048 --interactive-timeout=600 --character-set-server=latin1 --innodb-file-per-table=1 --innodb-file-format=Barracuda --innodb-file-format-max=Barracuda --slow-query-log=ON --long-query-time=3 --slow-query-log-file=/var/log/mysqld_slow_12028.log --log-bin=/data/DB_BinLog/mysql_12028/mysql-bin --relay-log=/data/DB_BinLog/mysql_12028/mysql-relay-bin --replicate-wild-ignore-table=mysql.% --replicate-wild-ignore-table=performance_schema.% --replicate-wild-ignore-table=information_schema.% --max-binlog-size=300m --expire-logs-days=7 --log-error=/var/log/mysqld_12028.log --open-files-limit=102400 --pid-file=/var/run/mysqld/mysqld_12028.pid --socket=/data/Database/mysql_12028/mysql.sock --port=12028基础路径与进程信息
| 参数 | 说明 |
|---|---|
--basedir=/usr |
MySQL 安装基础目录。 |
--datadir=/data/Database/mysql_12028 |
数据文件目录。 |
--plugin-dir=/usr/lib64/mysql/plugin |
插件目录（如 auth_socket、semisync 插件等）。 |
--user=mysql |
以 mysql 用户运行。 |
--pid-file=/var/run/mysqld/mysqld_12028.pid |
PID 文件存放位置。 |
--socket=/data/Database/mysql_12028/mysql.sock |
UNIX Socket 文件路径。 |
--port=12028 |
监听端口号。 |
网络连接并发
| 参数 | 说明 |
|---|---|
--back-log=210 |
TCP 半连接队列，防止短时间大量连接导致拒绝连接。 |
--skip-name-resolve |
禁止 DNS 反向解析，提升性能。 |
--wait-timeout=600 |
连接最大空闲 600 秒（非交互）。 |
--interactive-timeout=600 |
交互连接（如 mysql 命令行）超时。 |
--max-connections=2048 |
最大客户端连接数。 |
--max-connect-errors=2048 |
某 IP 连续连接错误超过 2048 次将被封锁。 |
--open-files-limit=102400 |
打开的文件描述符上限，关系到并发能力。 |
字符集
| 参数 | 说明 |
|---|---|
--character-set-server=latin1 |
全局默认字符集是 latin1（不是 utf8）。 |
InnoDB存储引擎设置
| 参数 | 说明 |
|---|---|
--innodb-file-per-table=1 |
每表独立文件，提高管理与碎片回收能力。 |
--innodb-file-format=Barracuda |
使用 Barracuda 表空间格式。 |
--innodb-file-format-max=Barracuda |
最大文件格式同样为 Barracuda。 |
Barracuda支持：行压缩、大索引、更灵活的存储结构，通常用于现代MySQL。
慢查询设置
| 参数 | 说明 |
|---|---|
--slow-query-log=ON |
打开慢查询日志。 |
--long-query-time=3 |
超过 3 秒算慢查询。 |
--slow-query-log-file=/var/log/mysqld_slow_12028.log |
慢查询日志文件路径。 |
主从复制binlog设置
| 参数 | 说明 |
|---|---|
--server-id=12028 |
唯一服务器 ID，主从复制必须。 |
--log-bin=/data/DB_BinLog/mysql_12028/mysql-bin |
开启二进制日志。 |
--relay-log=/data/DB_BinLog/mysql_12028/mysql-relay-bin |
relay-log（从库用）。 |
--max-binlog-size=300m |
每个 binlog 最大 300 MB。 |
--expire-logs-days=7 |
日志保存 7 天自动清理。 |
--replicate-wild-ignore-table=mysql.% |
复制时忽略系统库 mysql.*。 |
--replicate-wild-ignore-table=performance_schema.% |
忽略 performance_schema.*。 |
--replicate-wild-ignore-table=information_schema.% |
忽略 information_schema.*。 |
日志与错误记录
| 参数 | 说明 |
|---|---|
--log-error=/var/log/mysqld_12028.log |
错误日志文件。 |
常用的函数
mysql_affected_rows()：获取上一次 INSERT、UPDATE 或 DELETE 操作影响的行数。
mysql_autocommit()：开启或关闭 MySQL 连接的自动提交模式。
mysql_change_user()：修改 MySQL 连接的用户和密码。
mysql_character_set_name()：获取 MySQL 连接当前字符集的名称。
mysql_close()：关闭 MySQL 连接。
mysql_commit()：提交当前 MySQL 连接上的事务。
mysql_connect()：建立一个到 MySQL 数据库的连接。
mysql_create_db()：创建 MySQL 数据库。
mysql_data_seek()：将结果集的指针移动到指定的行号。
mysql_debug()：启用或禁用 MySQL 调试模式。
mysql_drop_db()：删除 MySQL 数据库。
mysql_dump_debug_info()：生成 MySQL 服务器的调试信息。
mysql_errno()：获取最近一次 MySQL 操作的错误码。
mysql_error()：获取最近一次 MySQL 操作的错误信息。
mysql_escape_string()：转义字符串以在 MySQL 查询中使用。
mysql_fetch_field()：获取结果集中的字段信息。
mysql_fetch_field_direct()：获取结果集中的指定字段信息。
mysql_fetch_fields()：获取结果集中的所有字段信息。
mysql_fetch_lengths()：获取结果集中的所有行的字段长度。
mysql_fetch_row()：获取结果集中的下一行数据。
mysql_field_count()：获取结果集中的字段数目。
mysql_field_seek()：将结果集的字段指针移动到指定位置。
mysql_field_tell()：获取结果集当前字段的位置。
mysql_free_result()：释放 MySQL 查询结果集。
mysql_get_character_set_info()：获取 MySQL 服务器支持的所有字符集。
mysql_get_client_info()：获取 MySQL 客户端的版本信息。
mysql_get_client_version()：获取 MySQL 客户端的版本号。
mysql_get_host_info()：获取 MySQL 服务器的主机名和连接信息。
mysql_get_proto_info()：获取 MySQL 服务器支持的协议版本。
mysql_get_server_info()：获取 MySQL 服务器的版本信息。
mysql_get_server_version()：获取 MySQL 服务器的版本号。
mysql_hex_string()：将二进制数据转换为十六进制字符串。
mysql_info()：获取上一次操作的额外信息。
mysql_init()：初始化 MYSQL 结构体。
mysql_insert_id()：获取上一次插入操作生成的 AUTO_INCREMENT 值。
mysql_kill()：关闭 MySQL 服务器上指定连接的进程。
mysql_library_end()：释放 MySQL 库资源。
mysql_library_init()：初始化 MySQL 库。
mysql_list_dbs()：获取 MySQL 服务器上所有的数据库。
mysql_list_fields()：获取表中所有的字段信息。
mysql_list_processes()：获取 MySQL 服务器上的所有进程信息。
mysql_list_tables()：获取 MySQL 数据库中所有表的名称。
mysql_load_plugin()：动态加载 MySQL 插件。
mysql_local_infile_end()：停止使用本地文件作为数据源。
mysql_load_plugin()：动态加载 MySQL 插件。
mysql_local_infile_end()：停止使用本地文件作为数据源。
mysql_local_infile_init()：开启使用本地文件作为数据源。
mysql_local_infile_read()：从本地文件中读取数据。
mysql_more_results()：检查是否还有多个结果集。
mysql_next_result()：获取下一个结果集。
mysql_num_fields()：获取结果集中的字段数。
mysql_num_rows()：获取结果集中的行数。
mysql_options()：设置 MySQL 连接选项。
mysql_ping()：检查 MySQL 连接是否仍然活动。
mysql_query()：执行 MySQL 查询。
mysql_real_connect()：建立一个到 MySQL 数据库的连接。
mysql_real_escape_string()：转义字符串以在 MySQL 查询中使用。
mysql_real_query()：执行 MySQL 查询。
mysql_refresh()：刷新 MySQL 缓存。
mysql_reload()：重新加载 MySQL 配置文件。
mysql_rollback()：撤销当前 MySQL 连接上的事务。
mysql_row_seek()：将结果集的行指针移动到指定位置。
mysql_row_tell()：获取结果集当前行的位置。
mysql_select_db()：选择 MySQL 数据库。
mysql_set_character_set()：设置 MySQL 连接的字符集。
mysql_set_local_infile_default()：设置本地文件作为默认数据源。
mysql_set_local_infile_handler()：设置本地文件数据源的处理程序。
mysql_shutdown()：关闭 MySQL 服务器。
mysql_sqlstate()：获取最近一次 MySQL 操作的 SQLSTATE。
mysql_ssl_set()：设置 MySQL 连接的 SSL 配置。
mysql_stat()：获取 MySQL 服务器的状态信息。
mysql_store_result()：获取查询结果集。
mysql_thread_id()：获取当前 MySQL 连接的线程 ID。
mysql_use_result()：获取结果集。
mysql_warning_count()：获取最近一次操作的警告数目。