postgres数据库基础操作

1. 链接数据库

• 命令

/# PGPASSWORD=liubei@161 psql -U liubei  -d xishu

当然你也可以交互式输入密码

psql -U liubei  -d xishu

• 输出

psql (13.8 (Debian 13.8-1.pgdg110+1))
Type "help" for help.

xishu=#

2. 库操作

2.1 创建库

xishu=# create database jing_zhou;
CREATE DATABASE

2.2 查看数据库

• 命令

xishu=# \l

• 输出

                              List of databases
   Name    | Owner  | Encoding |  Collate   |   Ctype    | Access privileges
-----------+--------+----------+------------+------------+-------------------
 jing_zhou | liubei | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | liubei | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | liubei | UTF8     | en_US.utf8 | en_US.utf8 | =c/liubei        +
           |        |          |            |            | liubei=CTc/liubei
 template1 | liubei | UTF8     | en_US.utf8 | en_US.utf8 | =c/liubei        +
           |        |          |            |            | liubei=CTc/liubei
 xishu     | liubei | UTF8     | en_US.utf8 | en_US.utf8 |
(5 rows)

如上可见我们创建的数据库jing_zhou

2.3 切换数据库

• 命令

xishu=# \c jing_zhou;

• 输出

You are now connected to database "jing_zhou" as user "liubei".

2.4 修改库名

xishu=# alter database jing_zhou rename to new_jing_zhou;

2.5 删除数据库

xishu=# drop database new_jing_zhou;

3. 表操作

3.1 创建表

• 命令

CREATE TABLE users(
   ID INT PRIMARY KEY     NOT NULL,
   name           CHAR(50)    NOT NULL,
   mail        CHAR(50),
   phone        CHAR(50)
);

3.2 查看table list

• 命令

xishu=# \d

• 输出

         List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+--------
 public | users   | table | liubei
(2 rows)

3.3 删除表

xishu=# drop table users;

4. 数据操作

4.1 插入数据

INSERT INTO users (ID,name,mail)  
VALUES (1,'guanYu','guanyu@xishu.com');

4.2 查询数据

xishu=# select * from users;

• 输出

 id |                        name                        |                        mail                        | phone
----+----------------------------------------------------+----------------------------------------------------+-------
  1 | guanYu                                             | guanyu@xishu.com                                   |

4.3 删除数据

delete from users where name='guanYu';

5. 用户&权限

• CREATE USER和REATE ROLE的区别
  • CREATE USER：默认LOGIN
  • CREATE ROLE：默认NOLOGIN

5.1 创建用户

• 创建用户并添加密码

CREATE USER guanyu PASSWORD 'guanyu@123456';

或者

CREATE ROLE guanyunchang LOGIN PASSWORD 'guanyu@123456';

• 创建管理员用户

CREATE USER kongming SUPERUSER PASSWORD 'kongming@123456';

• 创建可创建数据库的用户

CREATE USER pangtong CREATEDB;

• 创建可创建表的用户

CREATE USER pangtong CREATETABLE;

• 创建可创建用户的用户

CREATE USER fazheng CREATEROLE;

5.2 查看用户

• 命令

SELECT * FROM pg_roles;

• 输出

          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 pg_signal_backend         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4200
 pg_read_server_files      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4569
 guanyu                    | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16403
 liubei                    | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10
 pg_write_server_files     | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4570
 pg_execute_server_program | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4571
 fazheng                   | f        | t          | t             | f           | t           | f              |           -1 | ********    |               | f            |           | 16406
 pg_read_all_stats         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3375
 pg_monitor                | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3373
 guanyunchang              | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16407
 pangtong                  | f        | t          | f             | t           | t           | f              |           -1 | ********    |               | f            |           | 16405
 pg_read_all_settings      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3374
 pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3377
 kongming                  | t        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16404
(14 rows)

说明：
rolname：角色名
rolsuper：角色是否具有超级用户权限
rolinherit：如果此角色是另一个角色的成员，是否能自动继承另一个角色的权限
rolcreaterole：角色能否创建更多角色
rolcreatedb：角色能够创建数据库
rolcanlogin：角色能否登录
rolreplication：角色能否为一个复制角色
rolconnlimit：对于可登录角色，设置最大并发连接数，-1表示无限制
rolpassword：不是口令
rolvaliduntil：口令失效时间
rolbypassrls：绕过每一条行级安全策略的角色

5.3 删除用户

drop user guanyunchang ;

5.4 修改用户名

• 命令

ALTER USER guanyu RENAME TO guanyunchang;

• 输出

NOTICE:  MD5 password cleared because of role rename
ALTER ROLE

如上可知，密码被清空。

---

5.5 修改密码

• 命令

ALTER USER guanyunchang PASSWORD 'guanyunchang@123456';

5.6 权限回收

这里不展开写了，可以对应创建用户时的权限

 ALTER ROLE fazheng NOCREATEDB ;

6. 用户表权限

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

USAGE 仅使用权限，比如给scheme USAGE权限，仅能查询有哪些表，并不能查询表的内容

6.1 用户增加表权限

• 增加用户表权限

给用户关云长修改user表的权限

GRANT UPDATE ON users TO guanyunchang;

• 给所有用户查看权限

给所有用户查看users表的权限

 GRANT SELECT ON users TO PUBLIC;

• 给用户所有权限

给所用户关云长user表所有权限

GRANT ALL ON users TO guanyunchang;

6.2 回收用户表权限

REVOKE DELETE ON users FROM guanyunchang ;

---