PostgresSQL (二) 基础语法 CREATE, INSERT INTO, SELECT

语法命令

1. 基础语法

创建数据库

create database testdb;

image-20200713142640394

删除数据库

postgres=# drop database testdb;

DROP DATABASE
postgres=#

创建表

创建表之前要连接指定的数据库 \c test;

image-20200713143355689

CREATE TABLE table_name(  
  column1 datatype,  
  column2 datatype,  
  column3 datatype,  
  .....  
  columnN datatype,  
  PRIMARY KEY( one or more columns )  
);

image-20200713143734166

写法1：
test=# create table company(id int primary key not null , name text not null , age int not null ,address char(50) , salary real);

写法2：
test=# CREATE TABLE COMPANY(
test(#   ID                  INT PRIMARY KEY     NOT NULL,
test(#   NAME           TEXT                           NOT NULL,
test(#   AGE              INT                              NOT NULL,
test(#   ADDRESS    CHAR(50),
test(#   SALARY        REAL
test(# );


# create tabke company(); 表示创建名为company的表
# 其中包括几个字段
# 每个字段新建的顺序按照 1.字段名 2.字段数据类型 3.是否可以为空 来定义
primary key主键不能为空     id            int primary key not null
                                             name      text                  not null
                                             age         int                    not null

查看架构(schema)模式

\d 查看架构模式

image-20200713145317344

\d tablename 查看表格信息

image-20200713160011595

删除表

  1. 连接到对应的数据库 \c test;
test-# \c test;
您现在已经连接到数据库 "test",用户 "postgres".
test-#
  1. drop table company;
test=# drop table company;

DROP TABLE
test=#
  1. \d 查看删除后的目录结构

由于我一开始只有一张company; 删除后, 显示没有其他的表

image-20200713145840919

更改表数据

比如我这里有一张pg_equipment的表

2. 插入记录

2.1 语法

PostgreSQL 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);

2.2 实例

在test数据库中创建COMPANY表

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
);

\d company 查看创建表

image-20200713163936559

插入数据

test=# INSERT INTO COMPANY (id,name,age,address,salary,join_date) VALUES (1, 'Paul', 32, 'California', 2000.00,'2001-07-13');

INSERT 0 1
test=#

查看新增的表中的数据

SELECT * FROM company;

image-20200713164554888

如果要忽略某一字段 如salary 就把对应某个字段删去， 对应的value值也删去

test=# INSERT INTO company (id,name,age,address,join_date) VALUES (2, 'yang', 18, 'suzhou', '2012-7-13');

INSERT 0 1

image-20200713165119061

同理:

以下插入语句 join_data 字段使用 default 子句来设置默认值，而不是指定值：

runoobdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );

INSERT 0 1

插入多行

runoobdb=# 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

使用 SELECT 语句查询表格数据：

runoobdb=# SELECT * FROM company;

ID       NAME       AGE       ADDRESS     SALARY   JOIN_DATE
----     ---------- -----     ---------- -------     --------
1         Paul        32         California  20000.0      2001-07-13
2         Allen       25         Texas                    2007-12-13
3         Teddy       23         Norway      20000.0
4         Mark        25         Rich-Mond   65000.0      2007-12-13
5         David       27         Texas       85000.0      2007-12-13

3. 导入、导出/备份数据库

3.1 导入

比如我有这么一个文件

image-20200713195455329

路径: E:\database\company.sql

  1. 找到postgres的bin目录

    image-20200713195822639

  2. 打开cmd 定位到对应目录

    C:\Windows\system32>cd /d D:\Program data\PostgreSQL\12\bin

    image-20200713195847278

  3. 输入sql命令

    D:\Program data\PostgreSQL\12\bin>psql -h 127.0.0.1 -p 5433 -d runoobdb -U postgres -f E:\database\company.sql
    • -h ip地址
    • -p 端口号
    • -d 要导入的数据库名称
    • -U 要导入的数据库的用户名
    • -f 导入文件的路径

    image-daoru

  4. 查询插入的表

    \c runoobdb;
runoobdb=# select * from company;

    image-20200713202631660

3.2 导出/备份

同理

D:\Program data\PostgreSQL\12\bin>pg_dump -s -h 127.0.0.1 -p 5433 -d runoobdb -U postgres -f E:/database/company.sql
  • -s 代表仅导出表结构(不包含数据)
  • 不加-s 代表 导出所有表和所有数据

image-20200713201757085

4. SELECT语法

4.1 语法

SELECT 语句语法格式如下：

SELECT column1, column2,...columnN FROM table_name;

  • column1, column2,...columnN 为表中字段名。

  • table_name 为表名

4.1.1 读取表中所有数据

SELECT * FROM table_name;

image-20200713203032159

4.1.2 指定的字段

读取指定的字段和name:

SELECT ID,NAME FROM company;

image-20200713203048158

4.1.3 各类比较运算符(>, =, !=, >=, AND, OR)

读取SALARY 字段大于 50000的数据(Where过滤)

runoobdb=# SELECT * FROM COMPANY WHERE SALARY > 50000;

image-20200713203132854

等于

读取 SALARY 字段等于20000的数据

runoobdb=#  SELECT * FROM COMPANY WHERE SALARY = 20000;

image-20200713204603200

不等于
两种写法：
runoobdb=#  SELECT * FROM COMPANY WHERE SALARY != 20000;
runoobdb=#  SELECT * FROM COMPANY WHERE SALARY <> 20000;
大于等于
runoobdb=#  SELECT * FROM COMPANY WHERE SALARY >= 20000;
AND 同时满足

读取AGE字段大于等于25 且 SALARY字段大于6500的数据

runoobdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;

image-20200713205641331

OR

读取 AGE 字段大于等于 25 或 SALARY 字段大于 6500 的数据：

SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY > 6500;

image-20200713210139535

4.1.4 不为NULL

读取 SALARY 字段不为 NULL 的数据：

SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;

4.1.5 字段中以 ... 开头: LIKE

在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据：

SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';

image-20200714093409110

4.1.6给定集合中的数据 IN / NOT IN

以下 SELECT 语句列出了 AGE(年龄) 字段为 25 或 27 的数据：

也就是数据只能是25 或者27 ， 区间内的数据请用BETWEEN

SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

image-20200714093632786

SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

4.1.7 列出首位区间内的数据 BETWEEN... AND...

以下 SELECT 语句列出了 AGE(年龄) 字段在 25 到 27 的数据

SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

image-20200714094303383

4.1.8 子查询

以下的 SELECT 语句使用了 SQL 的子查询，子查询语句中读取 SALARY(薪资) 字段大于 65000 的数据，然后通过 EXISTS 运算符判断它是否返回行，如果有返回行则读取所有的 AGE(年龄) 字段。

SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

image-20200714095204984

以下的 SELECT 语句同样使用了 SQL 的子查询，子查询语句中读取 SALARY(薪资) 字段大于 65000 的 AGE(年龄) 字段数据，然后用 > 运算符查询大于该 AGE(年龄) 字段数据：

SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

image-20200714095539558

SELECT 语句中的子查询使用

现在，让我们在 SELECT 语句中使用子查询：

image-20200715114026714

SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY  WHERE SALARY > 45000) ;

image-20200715114153064

INSERT 语句中的子查询使用

子查询也可以与 INSERT 语句一起使用。INSERT 语句使用子查询返回的数据插入到另一个表中。

在子查询中所选择的数据可以用任何字符、日期或数字函数修改。

基本语法如下：

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

实例

假设 COMPANY1 的结构与 COMPANY 表相似，且可使用相同的 CREATE TABLE 进行创建，只是表名改为 COMPANY1。现在把整个 COMPANY 表复制到 COMPANY1

首先给出对应两张表：

image-20200715115758862

语法如下：

runoobdb=# INSERT INTO COMPANY1 SELECT * FROM COMPANY  WHERE ID IN (SELECT ID FROM COMPANY) ;
INSERT 0 7
runoobdb=#

image-20200715115824132

UPDATE 语句中的子查询使用

子查询可以与 UPDATE 语句结合使用。当通过 UPDATE 语句使用子查询时，表中单个或多个列被更新。

基本语法如下：

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

实例

假设，我们有 COMPANY_DKP 表，是 COMPANY 表的备份。

image-20200715140902400

下面的实例把 COMPANY 表中所有 AGE 大于 27 的客户的 SALARY 更新为原来的 0.50 倍：

UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_DKP WHERE AGE >= 27 );

image-20200715141313506

DELETE 语句中的子查询使用

子查询可以与 DELETE 语句结合使用，就像上面提到的其他语句一样。

基本语法如下：

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

实例

假设，我们有 COMPANY_DKP 表，是 COMPANY 表的备份。

下面的实例删除 COMPANY 表中所有 AGE 大于或等于 27 的客户记录：

DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_DKP WHERE AGE > 27 );

最后 COMPANY 表中的记录如下：

image-20200715142502509

5 更新字段 UPDATE

语法

以下是 UPDATE 语句修改数据的通用 SQL 语法：

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
  • 我们可以同时更新一个或者多个字段。
  • 我们可以在 WHERE 子句中指定任何条件。

实例

更新一个字段

以下实例将更新 COMPANY 表中 id 为 3 的 salary 字段值：

runoobdb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
UPDATE 1
runoobdb=#

image-20200714100208231

更新多个字段

UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000 ;

image-20200714101543437

6 删除字段 DELETE

语法

以下是 DELETE 语句删除数据的通用语法：

DELETE FROM table_name WHERE [condition];

删除整张表的字段

如果DELETE 末尾不加WHERE 过滤条件 默认删除整张表

DELETE FROM COMPANY;

image-20200714102158718

表名还存在 不过字段都删除了

7.有条件地查询语句

语法

带有 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]

实例

找出限定的数量的数据，即读取 4 条数据：LIMIT

SELECT * FROM COMPANY LIMIT 4;

image-20200714105430485

可能需要从一个特定的偏移开始提取记录 OFFSET

SELECT * FROM COMPANY LIMIT 4 OFFSET 2;

image-20200714105711528

8. 排列 ORDER BY

语法

ORDER BY 子句的基础语法如下：

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

实例

升序

SELECT * FROM COMPANY ORDER BY AGE ASC;

image-20200714110632782

下面实例对结果根据 NAME 字段值和 SALARY 字段值进行升序排序：

(主序：NAME; 次序： SALARY)

SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

image-20200714110958624

降序

下面实例将对结果根据NAME字段值进行降序排列：

runoobdb=# SELECT * FROM COMPANY ORDER BY NAME DESC;

image-20200714111810288

9. 分组 GROUP BY

语法

下面给出了 GROUP BY 子句的基本语法:

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

GROUP BY 子句必须放在WHERE 子句中的条件之后， 必须放在ORDER BY子句之前

实例

下面实例将根据 NAME 字段值进行分组，找出每个人的工资总额：

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

image-20200714112910464

现在我们添加使用下面语句在 COMPANY 表中添加三条记录：

# 可以使用插入多行语句
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00),
(9, 'James', 44, 'Norway', 5000.00),
(10, 'James', 45, 'Texas', 5000.00);

image-20200714113908879

现在再根据NAME字段值进行分组和升序排序, 找出每个客户的工资总额

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME

image-20200714143745779

10. 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]

# 可直接复制粘贴
With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

image-20200714145044952

接下来让我们使用 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;

image-20200714145601083

下面我们建立一张和 COMPANY 表相似的 COMPANY1 表，使用 DELETE 语句和 WITH 子句删除 COMPANY 表中 SALARY(工资) 字段大于等于 30000 的数据，并将删除的数据插入 COMPANY1 表，实现将 COMPANY 表数据转移到 COMPANY1 表中：

CREATE TABLE COMPANY1(
	ID INT PRIMARY KEY 	  NOT NULL,
	NAME 		  TEXT    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

11. HAVING 筛选分组后的各组数据

下面是 HAVING 子句在 SELECT 查询中的位置：

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 语句中基础语法：

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

实例

下面实例将找出根据 NAME 字段值进行分组，并且 name(名称) 字段的计数少于 2 数据：

SELECT NAME FROM COMPANY GROUP BY NAME HAVING COUNT(NAME) < 2;

image-20200714153239890

12. DISTINCT 关键字

在 PostgreSQL 中，DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。

我们平时在操作数据时，有可能出现一种情况，在一个表中有多个重复的记录，当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

语法

用于去除重复记录的 DISTINCT 关键字的基本语法如下：

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

实例

对表company插入两条数据

image-20200714155101157

得到结果： 可以看到其中有重复的数据

image-20200714155629851

这里我们使用

SELECT DISTINCT NAME FROM COMPANY;

image-20200714155820110

从结果可以看到，重复数据已经被删除。

13. 约束

常用约束：

  • NOT NULL：指示某列不能存储 NULL 值。
  • UNIQUE：确保某列的值都是唯一的。
  • PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。。
  • FOREIGN Key： 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK： 保证列中的值符合指定的条件。
  • EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。

14. 操作数据

NULL

把几个可设置为空的字段设置为NULL;

UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

image-20200714173059954

IS NOT NULL

我们用 IS NOT NULL 操作符把所有 SALARY（薪资） 值不为空的记录列出来：

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;

image-20200714173450760

IS NULL

 SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL;

image-20200714173618959

15. 索引

使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。

索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。

创建索引 CREATE INDEX

CREATE INDEX （创建索引）的语法如下：

CREATE INDEX index_name ON table_name;

索引类型:

单列索引

CREATE INDEX index_name
ON table_name (column_name);

组合索引

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

不管是单列索引还是组合索引，该索引必须是在 WHEHE 子句的过滤条件中使用非常频繁的列。

如果只有一列被使用到，就选择单列索引，如果有多列就使用组合索引。

唯一索引（UNIQUE)

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：

CREATE UNIQUE INDEX index_name
on table_name (column_name);

局部索引

局部索引是在表的子集上构建的索引; 子集由一个条件表达式上定义。 索引值包含满足条件的行

CREATE INDEX index_name
on table_name (conditional_expression);

隐式索引

隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

实例

在company表的SALARY列上创建索引;

CREATE INDEX salary_index ON COMPANY (salary);

image-20200714204415394

现在，用 \d company 命令列出 COMPANY 表的所有索引：

image-20200714204500600

company_pkey 是隐式索引, 是表创建时就有的.

你可以使用 \di 命令列出数据库中所有索引：

image-20200714204610677

删除索引 ROP INDEX

一个索引可以使用 PostgreSQL 的 DROP 命令删除。

DROP INDEX index_name;

您可以使用下面的语句来删除之前创建的索引：

# DROP INDEX salary_index;

删除后可以用\di查看所有索引,确认索引被删除

什么情况下要避免使用索引？

虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。

使用索引时，需要考虑下列准则：

  • 索引不应该使用在较小的表上。
  • 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
  • 索引不应该使用在含有大量的 NULL 值的列上。
  • 索引不应该使用在频繁操作的列上。

16. ALTER TABLE

表添加列:

ALTER TABLE table_name ADD column_name datatype;

image-20200714210410018

表删除列:

ALTER TABLE table_name DROP COLUMN column_name;

image-20200714210417551

修改某列的数据类型:

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

给某列添加NOT NULL约束:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

给某列添加UNIQUE 约束:

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

给表中添加CHECK约束

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

给表添加主键 PRIMARY KEY:

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

删除约束 DROP CONSTRAINT:

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

删除主键 DROP PRIMARY KEY：

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

17. 删除表结构 TRUNCATE TABLE

PostgreSQL 中 TRUNCATE TABLE 用于删除表的数据，但不删除表结构。

也可以用 DROP TABLE 删除表，但是这个命令会连表的结构一起删除，如果想插入数据，需要重新建立这张表。

TRUNCATE TABLE 与 DELETE 具有相同的效果，但是由于它实际上并不扫描表，所以速度更快。 此外，TRUNCATE TABLE 可以立即释放表空间，而不需要后续 VACUUM 操作，这在大型表上非常有用。

PostgreSQL VACUUM 操作用于释放、再利用更新/删除行所占据的磁盘空间。

语法

TRUNCATE TABLE 基础语法如下：

TRUNCATE TABLE  table_name;

下面实例使用了 TRUNCATE TABLE 来清除 COMPANY 表：

runoobdb=# TRUNCATE TABLE COMPANY;

得到结果如下：

runoobdb=# SELECT * FROM CUSTOMERS;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

18.自增 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
);

实例

假定我们要创建一张 COMPANY 表，并创建下面几个字段：

runoobdb=# 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 );

会得到:

image-20200715172829566

19. 权限 PRIVILEGES

无论何时创建数据库对象，都会为其分配一个所有者，所有者通常是执行 create 语句的人。

对于大多数类型的对象，初始状态是只有所有者(或超级用户)才能修改或删除对象。要允许其他角色或用户使用它，必须为该用户设置权限。

在 PostgreSQL 中，权限分为以下几种：

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • REFERENCES
  • TRIGGER
  • CREATE
  • CONNECT
  • TEMPORARY
  • EXECUTE
  • USAGE

根据对象的类型(表、函数等)，将指定权限应用于该对象。

要向用户分配权限，可以使用 GRANT 命令。

GRANT 语法

GRANT 命令的基本语法如下：

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }
  • privilege − 值可以为：SELECT，INSERT，UPDATE，DELETE， RULE，ALL。
  • object − 要授予访问权限的对象名称。可能的对象有： table， view，sequence。
  • PUBLIC − 表示所有用户。
  • GROUP group − 为用户组授予权限。
  • username − 要授予权限的用户名。PUBLIC 是代表所有用户的简短形式。

另外，我们可以使用 REVOKE 命令取消权限，REVOKE 语法：

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

实例

为了理解权限，创建一个用户：

CREATE USER runoob WITH PASSWORD 'password';
CREATE ROLE

信息 CREATE ROLE 表示创建了一个用户 "runoob"。

创建 COMPANY 表（下载 COMPANY SQL 文件 ），数据内容如下：

image-20200715194011951

给用户分配权限：

GRANT ALL ON COMPANY TO runoob;
GRANT

image-20200715194136945

信息 GRANT 表示所有权限已经分配给了 "runoob"。

去除用户权限

方式1 ：撤销权限

REVOKE ALL ON COMPANY FROM runoob;
REVOKE

信息 REVOKE 表示已经将用户的权限撤销

方式2： 删除用户

DROP USER runoob;
DROP ROLE

信息 DROP ROLE 表示 用户 已经从数据库中删除。

20. 常用函数

PostgreSQL 内置函数也称为聚合函数，用于对字符串或数字数据执行处理。

下面是所有通用 PostgreSQL 内置函数的列表：

  • COUNT 函数：用于计算数据库表中的行数。
  • MAX 函数：用于查询某一特定列中最大值。
  • MIN 函数：用于查询某一特定列中最小值。
  • AVG 函数：用于计算某一特定列中平均值。
  • SUM 函数：用于计算数字列所有值的总和。
  • ARRAY 函数：用于输入值(包括null)添加到数组中。
  • Numeric 函数：完整列出一个 SQL 中所需的操作数的函数。
  • String 函数：完整列出一个 SQL 中所需的操作字符的函数。

数学函数

下面是PostgreSQL中提供的数学函数列表，需要说明的是，这些函数中有许多都存在多种形式，区别只是参数类型不同。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。

函数 返回类型 描述 例子 结果
abs(x) 绝对值 abs(-17.4) 17.4
cbrt(double) 立方根 cbrt(27.0) 3
ceil(double/numeric) 不小于参数的最小的整数 ceil(-42.8) -42
degrees(double) 把弧度转为角度 degrees(0.5) 28.6478897565412
exp(double/numeric) 自然指数 exp(1.0) 2.71828182845905
floor(double/numeric) 不大于参数的最大整数 floor(-42.8) -43
ln(double/numeric) 自然对数 ln(2.0) 0.693147180559945
log(double/numeric) 10为底的对数 log(100.0) 2
log(b numeric,x numeric) numeric 指定底数的对数 log(2.0, 64.0) 6.0000000000
mod(y, x) 取余数 mod(9,4) 1
pi() double "π"常量 pi() 3.14159265358979
power(a double, b double) double 求a的b次幂 power(9.0, 3.0) 729
power(a numeric, b numeric) numeric 求a的b次幂 power(9.0, 3.0) 729
radians(double) double 把角度转为弧度 radians(45.0) 0.785398163397448
random() double 0.0到1.0之间的随机数值 random()
round(double/numeric) 圆整为最接近的整数 round(42.4) 42
round(v numeric, s int) numeric 圆整为s位小数数字 round(42.438,2) 42.44
sign(double/numeric) 参数的符号(-1,0,+1) sign(-8.4) -1
sqrt(double/numeric) 平方根 sqrt(2.0) 1.4142135623731
trunc(double/numeric) 截断(向零靠近) trunc(42.8) 42
trunc(v numeric, s int) numeric 截断为s小数位置的数字 trunc(42.438,2) 42.43

三角函数列表

函数 描述
acos(x) 反余弦
asin(x) 反正弦
atan(x) 反正切
atan2(x, y) 正切 y/x 的反函数
cos(x) 余弦
cot(x) 余切
sin(x) 正弦
tan(x) 正切

字符串函数和操作符

下面是 PostgreSQL 中提供的字符串操作符列表：

函数 返回类型 描述 例子 结果
string 丨丨 string text 字串连接 'Post' 丨丨 'greSQL' PostgreSQL
bit_length(string) int 字串里二进制位的个数 bit_length('jose') 32
char_length(string) int 字串中的字符个数 char_length('jose') 4
convert(string using conversion_name) text 使用指定的转换名字改变编码。 convert('PostgreSQL' using iso_8859_1_to_utf8) 'PostgreSQL'
lower(string) text 把字串转化为小写 lower('TOM') tom
octet_length(string) int 字串中的字节数 octet_length('jose') 4
overlay(string placing string from int [for int]) text 替换子字串 overlay('Txxxxas' placing 'hom' from 2 for 4) Thomas
position(substring in string) int 指定的子字串的位置 position('om' in 'Thomas') 3
substring(string [from int] [for int]) text 抽取子字串 substring('Thomas' from 2 for 3) hom
substring(string from pattern) text 抽取匹配 POSIX 正则表达式的子字串 substring('Thomas' from '…$') mas
substring(string from pattern for escape) text 抽取匹配SQL正则表达式的子字串 substring('Thomas' from '%#"o_a#"_' for '#') oma
trim([leading丨trailing 丨 both] [characters] from string) text 从字串string的开头/结尾/两边/ 删除只包含characters(默认是一个空白)的最长的字串 trim(both 'x' from 'xTomxx') Tom
upper(string) text 把字串转化为大写。 upper('tom') TOM
ascii(text) int 参数第一个字符的ASCII码 ascii('x') 120
btrim(string text [, characters text]) text 从string开头和结尾删除只包含在characters里(默认是空白)的字符的最长字串 btrim('xyxtrimyyx','xy') trim
chr(int) text 给出ASCII码的字符 chr(65) A
convert(string text, [src_encoding name,] dest_encoding name) text 把字串转换为dest_encoding convert( 'text_in_utf8', 'UTF8', 'LATIN1') 以ISO 8859-1编码表示的text_in_utf8
initcap(text) text 把每个单词的第一个子母转为大写，其它的保留小写。单词是一系列字母数字组成的字符，用非字母数字分隔。 initcap('hi thomas') Hi Thomas
length(string text) int string中字符的数目 length('jose') 4
lpad(string text, length int [, fill text]) text 通过填充字符fill(默认为空白)，把string填充为长度length。 如果string已经比length长则将其截断(在右边)。 lpad('hi', 5, 'xy') xyxhi
ltrim(string text [, characters text]) text 从字串string的开头删除只包含characters(默认是一个空白)的最长的字串。 ltrim('zzzytrim','xyz') trim
md5(string text) text 计算给出string的MD5散列，以十六进制返回结果。 md5('abc')
repeat(string text, number int) text 重复string number次。 repeat('Pg', 4) PgPgPgPg
replace(string text, from text, to text) text 把字串string里出现地所有子字串from替换成子字串to。 replace('abcdefabcdef', 'cd', 'XX') abXXefabXXef
rpad(string text, length int [, fill text]) text 通过填充字符fill(默认为空白)，把string填充为长度length。如果string已经比length长则将其截断。 rpad('hi', 5, 'xy') hixyx
rtrim(string text [, character text]) text 从字串string的结尾删除只包含character(默认是个空白)的最长的字 rtrim('trimxxxx','x') trim
split_part(string text, delimiter text, field int) text 根据delimiter分隔string返回生成的第field个子字串(1 Base)。 split_part('abc@def@ghi', '@', 2) def
strpos(string, substring) text 声明的子字串的位置。 strpos('high','ig') 2
substr(string, from [, count]) text 抽取子字串。 substr('alphabet', 3, 2) ph
to_ascii(text [, encoding]) text 把text从其它编码转换为ASCII。 to_ascii('Karel') Karel
to_hex(number int/bigint) text 把number转换成其对应地十六进制表现形式。 to_hex(9223372036854775807) 7fffffffffffffff
translate(string text, from text, to text) text 把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。 translate('12345', '14', 'ax') a23x5

类型转换相关函数

函数 返回类型 描述 实例
to_char(timestamp, text) text 将时间戳转换为字符串 to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text) text 将时间间隔转换为字符串 to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text) text 整型转换为字符串 to_char(125, '999')
to_char(double precision, text) text 双精度转换为字符串 to_char(125.8::real, '999D9')
to_char(numeric, text) text 数字转换为字符串 to_char(-125.8, '999D99S')
to_date(text, text) date 字符串转换为日期 to_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text) numeric 转换字符串为数字 to_number('12,454.8-', '99G999D9S')
to_timestamp(text, text) timestamp 转换为指定的时间格式 time zone convert string to time stamp to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double precision) timestamp 把UNIX纪元转换成时间戳 to_timestamp(1284352323)

posted on 2020-07-13 17:12  sunnywillow  阅读(1327)  评论(0)  编辑  收藏  举报