PostgreSQL 数据库与表格操作
1. 创建数据库
PostgreSQL 创建数据库可以用以下三种方式:
- 使用
CREATE DATABASE
SQL 语句来创建; - 使用
createdb
命令来创建; - 使用
pdAdmin
工具;
1.1 CREATE DATABASE 创建数据库
- 语法:
CREATE DATABASE dbname;
- 栗子:
postgres=# CREATE DATABASE testdb;
1.2 createdb 命令创建数据库
- createdb 是一个 SQL 命令 CREATE DATABASE 的封装。
- 语法:
createdb [option ...] [dbname [description]]
-
参数说明:
- dbname:要创建的数据库名;
- description:关于新创建的数据库相关的说明;
- options:参数可选项;
选项 描述 -D tablespace 指定数据库默认表空间。 -e 将 createdb 生成的命令发送到服务端。 -E encoding 指定数据库的编码。 -l locale 指定数据库的语言环境。 -T template 指定创建此数据库的模板。 –help 显示 createdb 命令的帮助信息。 -h host 指定服务器的主机名。 -p port 指定服务器监听的端口,或者 socket 文件。 -U username 连接数据库的用户名。 -w 忽略输入密码。 -W 连接时强制要求输入密码。 -
使用封装的命令
createdb
创建命令时,不能已经登录到 pg 数据库命令行,而需要在数据库安装的 bin 目录下,直接使用命令创建; -
栗子:
[10307440@zte.intra@LIN-874286D0CB5 bin]$ createdb -h localhost -p 5432 -U postgres sdtestbd
口令:
[10307440@zte.intra@LIN-874286D0CB5 bin]$
- 以上命令表示:使用 postgres 用户登录到主机地址为 localhost,端口号为 5432 的 PostgreSQL 数据库中并创建 sdtestdb 数据库。
2. 选择数据库
- 使用
\l
查看已经存在的数据库:
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
sdtestbd | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 行记录)
- 使用
\c + 数据库名
进入数据库:
postgres=# \c sdtestbd
您现在已经连接到数据库 "sdtestbd",用户 "postgres".
3. 删除数据库
PostgreSQL 删除数据库可以用以下三种方式:
- 使用
DROP DATABASE
SQL 语句来删除; - 使用
dropdb
命令来删除; - 使用
pgAdmin
工具;
3.1 DROP DATABASE 删除数据库
- DROP DATABASE 会删除数据库的系统目录项并且删除包含数据的文件目录。
- DROP DATABASE 只能由超级管理员或数据库拥有者执行。
- DROP DATABASE 命令需要在 PostgreSQL 命令窗口来执行,语法格式如下:
DROP DATABASE [ IF EXISTS ] name
- 参数说明:
- IF EXISTS:如果数据库不存在则发出提示信息,而不是错误信息。
- name:要删除的数据库的名称。
- 栗子:
postgres=# DROP DATABASE sdtestbd;
DROP DATABASE
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行记录)
3.2 dropdb 命令删除数据库
- dropdb 是 DROP DATABASE 的包装器。
- dropdb 用于删除 PostgreSQL 数据库。
- dropdb 命令只能由超级管理员或数据库拥有者执行。
- 语法格式:
dropdb [connection-option...] [option...] dbname
-
参数说明:
- dbname:要删除的数据库名。
- options:参数可选项,可以是以下值:
选项 描述 -e 显示 dropdb 生成的命令并发送到数据库服务器。 -i 在做删除的工作之前发出一个验证提示。 -V 打印 dropdb 版本并退出。 –if-exists 如果数据库不存在则发出提示信息,而不是错误信息。 –help 显示有关 dropdb 命令的帮助信息。 -h host 指定运行服务器的主机名。 -p port 指定服务器监听的端口,或者 socket 文件。 -U username 连接数据库的用户名。 -w 连接数据库的用户名。 -W 连接时强制要求输入密码。 –maintenance-db=dbname 删除数据库时指定连接的数据库,默认为 postgres,如果它不存在则使用 template1。 -
跟创建数据库时的命令一样,还是得进入 bin 目录下:
dropdb -h localhost -p 5432 -U postgres runoobdb
4. 创建表格
- PostgreSQL 使用
CREATE TABLE
语句来创建数据库表格。 CREATE TABLE
语法格式如下:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( 一个或多个列 )
);
CREATE TABLE
是一个关键词,用于告诉数据库系统将创建一个数据表。- 表名字必需在同一模式中的其它表、 序列、索引、视图或外部表名字中唯一。
CREATE TABLE
在当前数据库创建一个新的空白表,该表将由发出此命令的用户所拥有。- 表格中的每个字段都会定义数据类型,如下:
- 栗子:
postgres=# CREATE DATABASE pgtestdb
postgres-# ;
CREATE DATABASE
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
pgtestdb | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 行记录)
postgres=# \c pgtestdb
您现在已经连接到数据库 "pgtestdb",用户 "postgres".
pgtestdb=# CREATE TABLE COMPANY(
pgtestdb(# ID INT PRIMARY KEY NOT NULL,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# ADDRESS CHAR(50),
pgtestdb(# SALARY REAL
pgtestdb(# );
CREATE TABLE
- 然后使用
\d
查看表是否创建成功:
pgtestdb=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+---------+--------+----------
public | company | 数据表 | postgres
(1 行记录)
- 使用
\d tablename
查看表格信息:
pgtestdb=# \d COMPANY
数据表 "public.company"
栏位 | 类型 | 校对规则 | 可空的 | 预设
---------+---------------+----------+----------+------
id | integer | | not null |
name | text | | not null |
address | character(50) | | |
salary | real | | |
索引:
"company_pkey" PRIMARY KEY, btree (id)
- 即兴,在创建一个表(教程说后续会用):
pgtestdb=# CREATE TABLE DEPARTMENT(
pgtestdb(# ID INT PRIMARY KEY NOT NULL,
pgtestdb(# EDPT CHAR(50) NOT NULL,
pgtestdb(# EMP_ID INT NOT NULL
pgtestdb(# );
CREATE TABLE
pgtestdb=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+------------+--------+----------
public | company | 数据表 | postgres
public | department | 数据表 | postgres
(2 行记录)
5. 删除表格
- PostgreSQL 使用
DROP TABLE
语句来删除表格,包含表格数据、规则、触发器等,所以删除表格要慎重,删除后所有信息就消失了。 - 语法:
DROP TABLE table_name;
- 栗子:
pgtestdb=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+------------+--------+----------
public | company | 数据表 | postgres
public | department | 数据表 | postgres
(2 行记录)
pgtestdb=# DROP TABLE department;
DROP TABLE
pgtestdb=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+---------+--------+----------
public | company | 数据表 | postgres
(1 行记录)
6. 模式(SCHEMA)
- 模式(SCHEMA)可以看着是一个表的集合。
- 一个模式可以包含:
- 视图、索引、数据类型、函数和操作符等。
- 相同的对象名称可以被用于不同的模式中而不会出现冲突;
- 例如 schema1 和 myschema 都可以包含名为 mytable 的表;
- 使用模式的优势:
- 允许多个用户使用一个数据库并且不会互相干扰。
- 将数据库对象组织成逻辑组以便更容易管理。
- 第三方应用的对象可以放在独立的模式中,这样它们就不会与其他对象的名称发生冲突。
- 模式类似于操作系统层的目录,但是模式不能嵌套。
6.1 语法
- 可以使用
CREATE SCHEMA
语句来创建模式; - 语法格式如下:
CREATE SCHEMA myschema.mytable (
...
);
6.2 实例
- 在数据库
pgtestdb
下创建模式 testSchema:
pgtestdb=# CREATE SCHEMA testSchema;
CREATE SCHEMA
- 在模式 testSchema 下创建一个表格:
pgtestdb=# CREATE TABLE testSchema.COMPANY(
pgtestdb(# ID INT NOT NULL,
pgtestdb(# NAME VARCHAR(9) NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# ADDRESS CHAR(20),
pgtestdb(# SALARY DECIMAL(18, 2),
pgtestdb(# PRIMARY KEY(ID)
pgtestdb(# );
CREATE TABLE
- 查看表格:
pgtestdb=# SELECT * FROM testschema.COMPANY
pgtestdb-# ;
id | name | age | address | salary
----+------+-----+---------+--------
(0 行记录)
6.3 删除模式
- 删除一个为空的模式(其中的所有对象已经被删除):
pgtestdb=# DROP SCHEMA testschema;
错误: 无法删除 模式 testschema 因为有其它对象倚赖它
描述: 表 testschema.company 倚赖于 模式 testschema
提示: 使用 DROP .. CASCADE 把倚赖对象一并删除.
- 当我们没有删除这张表时,是无法直接删除这个模式的,提示告诉我们可以直接使用命令删除;
pgtestdb=# DROP SCHEMA testschema CASCADE;
注意: 递归删除 表 testschema.company
DROP SCHEMA
7. INSERT INTO 语句
INSERT INTO
语句用于向表中插入新记录。- 可以插入一行也可以同时插入多行。
- 语法:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
- 参数说明:
- column1, column2,…columnN 为表中字段名。
- value1, value2, value3,…valueN 为字段对应的值。
- 在使用 INSERT INTO 语句时,字段列必须和数据值数量相同,且顺序也要对应。
- 如果我们向表中的所有字段插入值,则可以不需要指定字段,只需要指定插入的值即可:
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
- 插入后返回值结果说明:
输出信息 | 描述 |
---|---|
INSERT oid 1 | 只插入一行并且目标表具有 OID的返回信息, 那么 oid 是分配给被插入行的 OID。 |
INSERT 0 # | 插入多行返回的信息, # 为插入的行数。 |
- 栗子:
- 在 pgtestdb 数据库中创建表 COMPANY ;
pgtestdb=# CREATE TABLE COMPANY(
pgtestdb(# ID INT NOT NULL,
pgtestdb(# NAME TEXT NOT NULL,
pgtestdb(# AGE INT NOT NULL,
pgtestdb(# ADDRESS CHAR(50) NOT NULL,
pgtestdb(# SALARY REAL,
pgtestdb(# JOIN_DATE DATE,
pgtestdb(# PRIMARY KEY(ID)
pgtestdb(# );
CREATE TABLE
pgtestdb=# \d company
数据表 "public.company"
栏位 | 类型 | 校对规则 | 可空的 | 预设
-----------+---------------+----------+----------+------
id | integer | | not null |
name | text | | not null |
age | integer | | not null |
address | character(50) | | not null |
salary | real | | |
join_date | date | | |
索引:
"company_pkey" PRIMARY KEY, btree (id)
- 在表 COMPANY 中插入以下数据:
pgtestdb=# INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE)
pgtestdb-# VALUES(1, 'Alice', 19, 'California', 19999.00, '2021-07-14');
INSERT 0 1
- 忽略 SALARY 字段,插入数据:
pgtestdb=# INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, JOIN_DATE)
pgtestdb-# VALUES (2, 'Mona', 18, 'Texas', '2021-07-14');
INSERT 0 1
- 使用默认值 DEFAULT 来对 JOIN_DATE 字段插入:
pgtestdb=# INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY, JOIN_DATE)
pgtestdb-# VALUES (3, 'Gorden', 45, 'WHOCARE', 100.00, DEFAULT);
INSERT 0 1
- 插入多行:
pgtestdb=# 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 2
- 查询表中的数据:
pgtestdb=# SELECT * FROM COMPANY;
id | name | age | address | salary | join_date
----+--------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
2 | Mona | 18 | Texas | | 2021-07-14
3 | Gorden | 45 | WHOCARE | 100 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
(5 行记录)
8. SELECT
- SELECT 语句用于从数据库中选取数据。
- 结果被存储在一个结果表中,称为结果集。
- 语法:
SELECT column1, column2,...columnN FROM table_name;
- 参数说明:
- column1, column2,…columnN 为表中字段名。
- table_name 为表名。
- 读取表中的所有数据:
SELECT * FROM table_name;
- 指定字段来读取表中的数据:
pgtestdb=# SELECT ID, NAME, AGE FROM COMPANY;
id | name | age
----+--------+-----
1 | Alice | 19
2 | Mona | 18
3 | Gorden | 45
4 | Mark | 25
5 | David | 27
(5 行记录)
9. 运算符
- 运算符是一种告诉编译器执行特定的数学或逻辑操作的符号。
- PostgreSQL 运算符是一个保留关键字或字符,一般用在 WHERE 语句中,作为过滤条件。
- 常见的运算符有:
- 算术运算符;
- 比较运算符;
- 逻辑运算符;
- 按位运算符;
9.1 算术运算符
- 假设变量 a 为 2,变量 b 为 3,则:
运算符 | 描述 | 实例 |
---|---|---|
+ | 加 | a + b 结果为 5 |
- | 减 | a - b 结果为 -1 |
* | 乘 | a * b 结果为 6 |
/ | 除 | b / a 结果为 1 |
% | 模(取余) | b % a 结果为 1 |
^ | 指数 | a ^ b 结果为 8 |
|/ | 平方根 | |/ 25.0 结果为 5 |
||/ | 立方根 | ||/ 27.0 结果为 3 |
! | 阶乘 | 5 ! 结果为 120 |
!! | 阶乘(前缀操作符) | !! 5 结果为 120 |
9.2 比较运算符
- 假设变量 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 |
9.3 逻辑运算符
运算符 | 描述 |
---|---|
AND | 逻辑与运算符。如果两个操作数都非零,则条件为真。 |
NOT | 逻辑非运算符。用来逆转操作数的逻辑状态。如果条件为真则逻辑非运算符将使其为假。 |
OR | 逻辑或运算符。如果两个操作数中有任意一个非零,则条件为真。 |
- SQL 使用三值的逻辑系统,包括 true、false 和 null,null 表示"未知"。
a | b | NOT a | a AND b | a OR b |
---|---|---|---|---|
TRUE | TRUE | FALSE | TRUE | TRUE |
TRUE | FALSE | FALSE | TRUE | |
TRUE | NULL | NULL | TRUE | |
FALSE | FALSE | TRUE | FALSE | FALSE |
FALSE | NULL | FALSE | NULL | |
NULL | NULL | NULL | NULL | NULL |
9.4 位运算符
- 位运算符作用于位,并逐位执行操作。
- &、 | 和 ^ 的真值表如下所示:
p | q | p&q | p | q |
---|---|---|---|
0 | 0 | 0 | 0 |
0 | 1 | 0 | 1 |
1 | 1 | 1 | 1 |
1 | 0 | 0 | 1 |
- 假设如果 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
10. 表达式
- 表达式是由一个或多个的值、运算符、PostgresSQL 函数组成的。
- PostgreSQL 表达式类似一个公式,用来查找数据库中指定条件的结果集。
10.1 语法
- SELECT 语句的语法格式如下:
SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];
10.2 布尔表达式
- 布尔表达式是根据一个指定条件来读取数据:
SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;
- 栗子:
pgtestdb=# SELECT * FROM COMPANY;
id | name | age | address | salary | join_date
----+--------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
2 | Mona | 18 | Texas | | 2021-07-14
3 | Gorden | 45 | WHOCARE | 100 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
(5 行记录)
pgtestdb=# SELECT * FROM COMPANY WHERE SALARY < 20000;
id | name | age | address | salary | join_date
----+--------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
3 | Gorden | 45 | WHOCARE | 100 |
(2 行记录)
10.3 数字表达式
- 数字表达式常用于查询语句中的数学运算:
SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;
- 栗子:
pgtestdb=# SELECT (99 + 1) AS RESULT;
result
--------
100
(1 行记录)
-
此外 PostgreSQL 还内置了一些数学函数,如:
- avg() : 返回一个表达式的平均值;
- sum() : 返回指定字段的总和;
- count() : 返回查询的记录总数;
-
以下实例查询 COMPANY 表的记录总数:
pgtestdb=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
RECORDS
---------
5
(1 行记录)
10.4 日期表达式
- 日期表达式返回当前系统的日期和时间,可用于各种数据操作,以下实例查询当前时间:
pgtestdb=# SELECT CURRENT_TIMESTAMP;
current_timestamp
-------------------------------
2021-09-30 16:37:59.760127+08
(1 行记录)
11. WHERE 子句
- 在 PostgreSQL 中,当需要根据指定条件从单张表或者多张表中查询数据时,就可以在 SELECT 语句中添加 WHERE 子句,从而过滤掉不需要数据。
- WHERE 子句不仅可以用于 SELECT 语句中,同时也可以用于 UPDATE,DELETE 等等语句中。
11.1 语法
- 以下是 SELECT 语句中使用 WHERE 子句从数据库中读取数据的通用语法:
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1]
- 可以在 WHERE 子句中使用比较运算符或逻辑运算符,例如 >, <, =, LIKE, NOT 等。
11.2 AND
- 找出 AGE(年龄) 字段大于等于 20,并且 SALARY(薪资) 字段大于等于 65000 的数据:
pgtestdb=# SELECT * FROM COMPANY WHERE AGE >= 20 AND SALARY >= 65000;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
(2 行记录)
11.3 OR
- 找出 AGE(年龄) 字段大于等于 20,或者 SALARY(薪资) 字段大于等于 65000 的数据:
pgtestdb=# SELECT * FROM COMPANY WHERE AGE >= 20 OR SALARY >= 65000;
id | name | age | address | salary | join_date
----+--------+-----+----------------------------------------------------+--------+------------
3 | Gorden | 45 | WHOCARE | 100 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
(3 行记录)
11.4 NOT NULL
- 在公司表中找出 SALARY(薪资) 字段不为空的记录:
pgtestdb=# SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;
id | name | age | address | salary | join_date
----+--------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
3 | Gorden | 45 | WHOCARE | 100 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
(4 行记录)
11.5 LIKE
- 在 COMPANY 表中找出 NAME(名字) 字段中以 Al 开头的的数据:
pgtestdb=# SELECT * FROM COMPANY WHERE NAME LIKE "Al%";
错误: 字段 "Al%" 不存在
第1行SELECT * FROM COMPANY WHERE NAME LIKE "Al%";
^
pgtestdb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Al%';
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
(1 行记录)
- 从上边看出,不可以使用 双引号;
11.6 IN
- 以下 SELECT 语句列出了 AGE(年龄) 字段为 18 或 25 的数据:
pgtestdb=# SELECT * FROM COMPANY WHERE AGE IN (18, 25);
id | name | age | address | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
2 | Mona | 18 | Texas | | 2021-07-14
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
(2 行记录)
11.7 NOT IN
- 以下 SELECT 语句列出了 AGE(年龄) 字段不为 18 或 25 的数据:
pgtestdb=# SELECT * FROM COMPANY WHERE AGE NOT IN (18, 25);
id | name | age | address | salary | join_date
----+--------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
3 | Gorden | 45 | WHOCARE | 100 |
5 | David | 27 | Texas | 85000 | 2007-12-13
(3 行记录)
11.8 BETWEEN
- 以下 SELECT 语句列出了 AGE(年龄) 字段在 18 到 25 的数据:
pgtestdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 18 AND 25;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
2 | Mona | 18 | Texas | | 2021-07-14
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
(3 行记录)
11.9 子查询
- 以下的 SELECT 语句使用了 SQL 的子查询,子查询语句中读取 SALARY(薪资) 字段大于 65000 的数据,然后通过 EXISTS 运算符判断它是否返回行,如果有返回行则读取所有的 AGE(年龄) 字段。
pgtestdb=# SELECT AGE FROM COMPANY
pgtestdb-# WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
age
-----
19
18
45
25
27
(5 行记录)
12. UPDATE 语句
- 可以用 UPDATE 来更新在 PostgreSQL 数据库中的数据;
- 语法:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
- 栗子:
pgtestdb=# SELECT * FROM COMPANY;
id | name | age | address | salary | join_date
----+--------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
2 | Mona | 18 | Texas | | 2021-07-14
3 | Gorden | 45 | WHOCARE | 100 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
(5 行记录)
pgtestdb=# UPDATE COMPANY SET SALARY = 19991 WHERE ID = 2;
UPDATE 1
pgtestdb=# SELECT * FROM COMPANY;
id | name | age | address | salary | join_date
----+--------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
3 | Gorden | 45 | WHOCARE | 100 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
2 | Mona | 18 | Texas | 19991 | 2021-07-14
(5 行记录)
13. DELETE 语句
- 使用 DELETE 语句来删除 PostgreSQL 表中的数据。
- 语法:
DELETE FROM table_name WHERE [condition];
- 如果没有指定 WHERE 子句,PostgreSQL 表中的所有记录将被删除。
- 一般我们需要在 WHERE 子句中指定条件来删除对应的记录,条件语句可以使用 AND 或 OR 运算符来指定一个或多个。
pgtestdb=# SELECT * FROM COMPANY;
id | name | age | address | salary | join_date
----+--------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
3 | Gorden | 45 | WHOCARE | 100 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
2 | Mona | 18 | Texas | 19991 | 2021-07-14
(5 行记录)
pgtestdb=# DELETE FROM COMPANY WHERE ID = 3;
DELETE 1
pgtestdb=# SELECT * FROM COMPANY;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
2 | Mona | 18 | Texas | 19991 | 2021-07-14
(4 行记录)
14. LIKE 子句
- 在 PostgreSQL 数据库中,我们如果要获取包含某些字符的数据,可以使用 LIKE 子句。
- 在 LIKE 子句中,通常与通配符结合使用,通配符表示任意字符,在 PostgreSQL 中,主要有以下两种通配符:
- 百分号
%
; - 下划线
_
;
- 百分号
- 如果没有使用以上两种通配符,LIKE 子句和等号 = 得到的结果是一样的。
- 以下是使用 LIKE 子句搭配百分号 % 和下划线 _ 从数据库中获取数据的通用语法:
SELECT FROM table_name WHERE column LIKE 'XXXX%';
或者
SELECT FROM table_name WHERE column LIKE '%XXXX%';
或者
SELECT FROM table_name WHERE column LIKE 'XXXX_';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX_';
- 栗子:
pgtestdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
(2 行记录)
- 其中:
::
作用:将一种类型的常量转化成指定类型的常量;
15. LIMIT 子句
- PostgreSQL 中的
limit
子句用于限制 SELECT 语句中查询的数据的数量。 - 带有 LIMIT 子句的 SELECT 语句的基本语法如下:
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
- 下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法:
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]
- 查看 COMPANY 表:
pgtestdb=# SELECT * FROM COMPANY;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
2 | Mona | 18 | Texas | 19991 | 2021-07-14
(4 行记录)
- 读取 2 条数据:
pgtestdb=# SELECT * FROM COMPANY LIMIT 2;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
(2 行记录)
- 从第二位开始提取 2 个记录:
pgtestdb=# SELECT * FROM COMPANY LIMIT 2 OFFSET 1;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
(2 行记录)
16. ORDER BY 语句
- ORDER BY 用于对一列或者多列数据进行升序(ASC)或者降序(DESC)排列。
- 语法:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
- 查看 COMPANY 中的内容:
pgtestdb=# SELECT * FROM COMPANY
pgtestdb-# ;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
1 | Alice | 19 | California | 19999 | 2021-07-14
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
2 | Mona | 18 | Texas | 19991 | 2021-07-14
(4 行记录)
- 通过 ORDER BY 对 AGE 字段进行升序排列:
pgtestdb=# SELECT * FROM COMPANY ORDER BY AGE ASC;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
2 | Mona | 18 | Texas | 19991 | 2021-07-14
1 | Alice | 19 | California | 19999 | 2021-07-14
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
(4 行记录)
- 通过 ORDER BY 对 NAME 字段进行降序排列:
pgtestdb=# SELECT * FROM COMPANY ORDER BY NAME DESC;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
2 | Mona | 18 | Texas | 19991 | 2021-07-14
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
1 | Alice | 19 | California | 19999 | 2021-07-14
(4 行记录)
17. GROUP BY 语句
- GROUP BY 语句和 SELECT 语句一起使用,用来对相同的数据进行分组。
- GROUP BY 在一个 SELECT 语句中,放在 WHRER 子句的后面,ORDER BY 子句的前面。
- 语法:
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
- 创建新的 COMPANY 表:
pgtestdb=# 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 | 15000
5 | David | 27 | Texas | 15000
6 | Kim | 22 | South-Hall | 45000
(6 行记录)
- 根据 NAME 字段值进行分组,找出每个人的工资总额:
pgtestdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
name | sum
-------+-------
Teddy | 20000
David | 15000
Paul | 20000
Kim | 45000
Mark | 15000
Allen | 15000
(6 行记录)
- 其实,这个就是按照人来将工资综合求和(聚合函数 SUM);
- 看不懂没关系,再来一个,通过聚合函数
COUNT
来统计一下不同工资的人数;
pgtestdb=# SELECT SALARY, COUNT(*) FROM COMPANY GROUP BY SALARY;
salary | count
--------+-------
20000 | 2
45000 | 1
15000 | 3
(3 行记录)
- 将 ORDER BY 和 GROUO BY 一起使用:
pgtestdb=# SELECT SALARY, COUNT(*) FROM COMPANY GROUP BY SALARY ORDER BY SALARY DESC;
salary | count
--------+-------
45000 | 1
20000 | 2
15000 | 3
(3 行记录)
18. WITH 语句
- WITH 子句提供了一种编写辅助语句的方法,以便在更大的查询中使用。
- WITH 子句有助于将复杂的大型查询分解为更简单的表单,便于阅读。
- 这些语句通常称为通用表表达式(Common Table Express, CTE),也可以当做一个为查询而存在的临时表。
- WITH 子句是在多次执行子查询时特别有用,允许我们在查询中通过它的名称(可能是多次)引用它。
- WITH 子句在使用前必须先定义。
- 语法:
WITH
name_for_summary_data AS (
SELECT Statement)
SELECT columns
FROM name_for_summary_data
WHERE conditions <=> (
SELECT column
FROM name_for_summary_data)
[ORDER BY columns]
-
name_for_summary_data
:是 WITH 子句的名称; -
name_for_summary_data
可以与现有的表名相同,并且具有优先级。 -
实例没看懂,感觉像是将某一部分功能提出来了一个函数,有兴趣的参考 菜鸟教程:WITH 子句
19. HAVING 子句
- HAVING 子句可以让我们筛选分组后的各组数据。
- WHERE 子句在所选列上设置条件,而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
- HAVING 子句必须放置于 GROUP BY 子句后面,ORDER BY 子句前面,下面是 HAVING 子句在 SELECT 语句中基础语法:
- 语法:
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
- 根据工资字段进行分组,并且统计工资数少于 18000 的人数:
pgtestdb=# SELECT SALARY, COUNT(*) FROM COMPANY GROUP BY SALARY HAVING SALARY < 18000;
salary | count
--------+-------
15000 | 3
(1 行记录)
20. DISTINCT 关键字
- DISTINCT 关键字与 SELECT 语句一起使用,用于去除重复记录,只获取唯一的记录。
- 语法:
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
- 查看表 COMPANY,发现并没有重复数据,顺便在里边添加两条重复数据;
pgtestdb=# 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 | 15000
5 | David | 27 | Texas | 15000
6 | Kim | 22 | South-Hall | 45000
(6 行记录)
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (8, 'Paul', 32, 'California', 20000.00 );
INSERT 0 1
pgtestdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
pgtestdb-# VALUES (9, 'Allen', 25, 'Texas', 15000.00 );
INSERT 0 1
pgtestdb=# 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 | 15000
5 | David | 27 | Texas | 15000
6 | Kim | 22 | South-Hall | 45000
8 | Paul | 32 | California | 20000
9 | Allen | 25 | Texas | 15000
(8 行记录)
- 找出 COMPANY 表中的所有 NAME:
pgtestdb=# SELECT NAME FROM COMPANY;
name
-------
Paul
Allen
Teddy
Mark
David
Kim
Paul
Allen
(8 行记录)
- 在 SELECT 语句中使用 DISTINCT 子句:
pgtestdb=# SELECT DISTINCT NAME FROM COMPANY;
name
-------
Teddy
David
Paul
Kim
Mark
Allen
(6 行记录)