连接数据库
-bash-4.1$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
yyjs_luxuemei701 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
(4 rows)
-bash-4.1$ psql yyjs_luxuemei701 --连接到指定数据库
psql (10.3)
Type "help" for help.
远程连接:
-bash-4.1$ psql -U yyjsdata -d yyjs_luxuemei701 -h 30.12.18.104 -p 35433
Password for user yyjsdata:
psql (10.3)
Type "help" for help.
具体见
https://blog.csdn.net/xuemeilu/article/details/67632883
创建用户
yyjs_luxuemei701=# CREATE USER yyjsdata WITH PASSWORD 'passwd001';
CREATE ROLE
yyjs_luxuemei701=# CREATE USER yyjsopr WITH PASSWORD 'passwd001';
CREATE ROLE
创建表
CREATE TABLE hostmanage(
OS_NAME varchar(50),
OS_VERSION varchar(50),
OS_BRAND varchar(50),
CPU_CORE varchar(10),
OS_MEMORY varchar(10),
HOST_TYPE varchar(20),
HOST_STATUS varchar(20),
HOST_OWER varchar(50),
IP_ADDR varchar(100)
);
DML操作
--插入
insert into hostmanage values('1','Red Hat','Red Hat 4.4.7-18','Oracle Linux Server release 6.7','2','4G','Linux','start','luxuemei701','30.12.18.104');
insert into hostmanage values('红帽','win 10','microsoft','2','4G','windows','down','luxuemei701','30.12.0.1');
--更新表数据
yyjs_luxuemei701=> update hostmanage set host_name = 'SHB-L0102013';
控制台
--切换数据库:\c [database_name]
--切换用户:\c - [user_name]
--查看所有库: \l
--查看所有表: \dt
--查询当前用户表: \d
--退出:\q
--当前数据库和连接的信息: \conninfo
--修改数据库用户密码: \password user
--查看当前用户:
yyjs_luxuemei701=> select user;
user
----------
yyjsdata
(1 row)
yyjs_luxuemei701=> select * from current_user;
current_user
--------------
yyjsdata
(1 row)
--查询当前用户表\d
yyjs_luxuemei701=> \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | hostmanage | table | yyjsdata
(1 row)
--查看表结构\d table
yyjs_luxuemei701=> \d hostmanage
Table "public.hostmanage"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
os_name | character varying(50) | | |
os_version | character varying(50) | | |
os_brand | character varying(50) | | |
cpu_core | character varying(10) | | |
os_memory | character varying(10) | | |
host_type | character varying(20) | | |
host_status | character varying(20) | | |
host_ower | character varying(50) | | |
ip_addr | character varying(100) | | |
DDL
--新增字段
yyjs_luxuemei701=> alter table hostmanage add column host_name varchar(50);
ALTER TABLE
yyjs_luxuemei701=> \d hostmanage
Table "public.hostmanage"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
id | character varying(10) | | |
os_name | character varying(50) | | |
os_version | character varying(100) | | |
os_brand | character varying(50) | | |
cpu_core | character varying(10) | | |
os_memory | character varying(10) | | |
host_type | character varying(20) | | |
host_status | character varying(20) | | |
host_ower | character varying(50) | | |
ip_addr | character varying(100) | | |
host_name | character varying(50) | | |
--修改字段名(将HOST_OWER修改为HOST_NAME)
yyjs_luxuemei701=> alter table hostmanage rename column HOST_OWER to HOST_NAME;
ALTER TABLE
yyjs_luxuemei701=> \d hostmanage
Table "public.hostmanage"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
id | character varying(10) | | |
os_name | character varying(50) | | |
os_version | character varying(50) | | |
os_brand | character varying(50) | | |
cpu_core | character varying(10) | | |
os_memory | character varying(10) | | |
host_type | character varying(20) | | |
host_status | character varying(20) | | |
host_name | character varying(50) | | |
ip_addr | character varying(100) | | |
--修改字段类型
yyjs_luxuemei701=> alter table hostmanage alter column os_version type varchar(100);
ALTER TABLE
yyjs_luxuemei701=> \d hostmanage
Table "public.hostmanage"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
id | character varying(10) | | |
os_name | character varying(50) | | |
os_version | character varying(100) | | |
os_brand | character varying(50) | | |
cpu_core | character varying(10) | | |
os_memory | character varying(10) | | |
host_type | character varying(20) | | |
host_status | character varying(20) | | |
host_ower | character varying(50) | | |
ip_addr | character varying(100) | | |
修改表名:
alter table hostmanage rename to host;
删除字段:
alter table hostmanage drop column host_name;
授权:
--特殊符号:ALL代表所访问权限,PUBLIC代表所有用户
GRANT ALL ON demo TO demo_role; --赋给用户所有权限
GRANT SELECT ON demo TO PUBLIC; --将SELECT权限赋给所有用户
yyjs_luxuemei701=> grant insert,update,select,delete on hostmanage to yyjsopr;
GRANT
--查询授权
yyjs_luxuemei701=> select * from information_schema.role_table_grants;
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+----------+------------------+--------------+------------+----------------+--------------+----------------
yyjsdata | yyjsdata | yyjs_luxuemei701 | public | hostmanage | INSERT | YES | NO
yyjsdata | yyjsdata | yyjs_luxuemei701 | public | hostmanage | SELECT | YES | YES
yyjsdata | yyjsdata | yyjs_luxuemei701 | public | hostmanage | UPDATE | YES | NO
yyjsdata | yyjsdata | yyjs_luxuemei701 | public | hostmanage | DELETE | YES | NO
yyjsdata | yyjsdata | yyjs_luxuemei701 | public | hostmanage | TRUNCATE | YES | NO
yyjsdata | yyjsdata | yyjs_luxuemei701 | public | hostmanage | REFERENCES | YES | NO
yyjsdata | yyjsdata | yyjs_luxuemei701 | public | hostmanage | TRIGGER | YES | NO
yyjsdata | yyjsopr | yyjs_luxuemei701 | public | hostmanage | INSERT | NO | NO
yyjsdata | yyjsopr | yyjs_luxuemei701 | public | hostmanage | SELECT | NO | YES
yyjsdata | yyjsopr | yyjs_luxuemei701 | public | hostmanage | UPDATE | NO | NO
yyjsdata | yyjsopr | yyjs_luxuemei701 | public | hostmanage | DELETE | NO | NO
yyjsdata | PUBLIC | yyjs_luxuemei701 | public | hostmanage | SELECT | NO | YES
(12 rows)
GRANT ALL ON hostmanage TO public;
文章推荐
https://www.cnblogs.com/taosim/articles/4380383.html
点评:非常全的一篇文章,包括postgresql编译,安装,配置,常用命令…