postgresql基本命令操作
postgresql基本命令操作: 登陆数据库: [postgres@localhost ~]$ psql -Utestwjw -h 127.0.0.1 -dpostgres -p 36985 Password for user testwjw: psql.bin (9.5.9) Type "help" for help. postgres=> 切换数据库: postgres=> c testdb1 You are now connected to database "testdb1" as user "testwjw". 查看所有的数据库: testdb1=> l testdb1=> list List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres testdb1 | testwjw | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/testwjw + | | | | | testwjw=CTc/testwjw testdb2 | testwjw | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (5 rows) 查看所有的表: testdb1=> dt List of relations Schema | Name | Type | Owner --------+-------+-------+--------- public | t | table | testwjw public | t1 | table | testwjw public | tlb01 | table | testwjw (3 rows) testdb1=> 创建数据库: [postgres@localhost ~]$ psql -p 36985 psql.bin (9.5.9) Type "help" for help. postgres=# create database testdb3 with encoding='utf8' owner=testwjw; CREATE DATABASE [postgres@localhost ~]$ createdb testdb5 -p 36985 [postgres@localhost ~]$ createdb testdb6 -p 36985 查看创建的数据库: [postgres@localhost ~]$ psql -p 36985 -c 'list'|egrep "testdb4|testdb5" testdb4 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | testdb5 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 删除创建的数据库: #以testwjw的身份连接服务器,删除testdb1数据库。 [postgres@localhost ~]$ dropdb -Utestwjw -p 36985 -e testdb1 DROP DATABASE testdb1; [postgres@localhost ~]$ psql -p 36985 -c 'list'|grep "testdb1" 通过查看系统表验证该数据库是否已经被删除: [postgres@localhost ~]$ psql -p 36985 -c "SELECT count(*) FROM pg_database WHERE datname ='testdb1'" count ------- 0 (1 row) 证明此数据库确实被删除。 查看数据库中所有的表以及单表结构: testdb2=# dt List of relations Schema | Name | Type | Owner --------+------+-------+--------- public | tlb2 | table | testwjw (1 row) testdb2=# d tlb2 Table "public.tlb2" Column | Type | Modifiers --------+-----------------------+----------- id | integer | pay | character varying(20) | name | character varying(6) | Indexes: "uniq" UNIQUE CONSTRAINT,btree (id) testdb2=# 查看索引详细信息: testdb2=# d uniq; Index "public.uniq" Column | Type | Definition --------+---------+------------ id | integer | id unique,btree,for table "public.tlb2" d+ 命令:将会显示比d命令更详细的信息,除了前面介绍的那些,它还会显示任何与表列相关的注释,以及表中出现的OID。 testdb2=# d+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+---------+---------+------------- public | tlb2 | table | testwjw | 0 bytes | (1 row) testdb2=# d List of relations Schema | Name | Type | Owner --------+------+-------+--------- public | tlb2 | table | testwjw (1 row) testdb2=# 列出所有的schemas: testdb2=# dn List of schemas Name | Owner --------+---------- public | postgres (1 row) 创建schema: testdb2=# create schema sa; CREATE SCHEMA testdb2=# dn List of schemas Name | Owner --------+---------- public | postgres sa | postgres (2 rows) testdb2=# 显示sql执行的时间,可以使用timing参数: testdb2=# timing Timing is on. testdb2=# select * from tlb2; id | pay | name ----+-----+------ (0 rows) Time: 0.177 ms testdb2=# 如果想列出数据库中所有的角色或者用户,可以使用du dg,这两个命令等价,因为postgresSQL中用户和角色不区分: testdb2=# du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser,Create role,Create DB,Replication,Bypass RLS | {} testwjw | | {} testdb2=# dg List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser,Bypass RLS | {} testwjw | | {} testdb2=# 查看表字段: testdb2=# SELECT a.attname from pg_class c,pg_attribute a,pg_type t where c.relname='tlb2' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid; attname --------- id pay name (3 rows) Time: 0.586 ms testdb2=# dnp+ List of schemas Name | Owner | Access privileges | Description --------+----------+----------------------+------------------------ public | postgres | postgres=UC/postgres+| standard public schema | | =UC/postgres | sa | postgres | | (2 rows) testdb2=# dn List of schemas Name | Owner --------+---------- public | postgres sa | postgres (2 rows) testdb2=# 创建表: testdb2=# dt List of relations Schema | Name | Type | Owner --------+------+-------+--------- public | tlb2 | table | testwjw (1 row) 建表: testdb2=# CREATE TABLE products ( product_no integer, name text, price numeric ); CREATE TABLE 查看表: testdb2=# dt List of relations Schema | Name | Type | Owner --------+----------+-------+---------- public | products | table | postgres public | tlb2 | table | testwjw (2 rows) 删除表: testdb2=# drop table products; DROP TABLE testdb2=# dt List of relations Schema | Name | Type | Owner --------+------+-------+--------- public | tlb2 | table | testwjw (1 row) testdb2=# 列出所有的表空间: postgres=# db List of tablespaces Name | Owner | Location ---------------+----------+-------------------------- my_tablespace | postgres | /data/postgresql/mydata pg_default | postgres | pg_global | postgres | tbspace01 | postgres | /data/postgresql/tbspace (4 rows) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |