postgres数据库基础操作
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：默认LOGINCREATE 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)
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
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 ;
