PostgreSQL - 数据库与表格操作

转x33g5p2x  于2021-10-08 转载在 其他  
字(20.7k)|赞(0)|评价(0)|浏览(961)

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 表示"未知"。
abNOT aa AND ba OR b
TRUETRUEFALSETRUETRUE
TRUEFALSEFALSETRUE
TRUENULLNULLTRUE
FALSEFALSETRUEFALSEFALSE
FALSENULLFALSENULL
NULLNULLNULLNULLNULL

9.4 位运算符

  • 位运算符作用于位，并逐位执行操作。
  • &、 | 和 ^ 的真值表如下所示：
pqp&qp | q
0000
0101
1111
1001

  • 假设如果 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 行记录)

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://blog.csdn.net/S_numb/article/details/120645812

内容来源于网络，如有侵权，请联系作者删除！

sqlpostgresqlbig data数据库

相关文章

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com