PostgreSQL – Database SOS

玩多了Oracle,习惯了使用Oracle的体系架构去对比别的数据库,今天看PostgreSQL发现两者明显不一样:
1. 在数据库/表空间/schema三者关系上的区别

表空间是物理结构，同一表空间下可以有多个数据库
数据库是逻辑结构，是表／索引／视图／存储过程的集合，一个数据库下可以有多个schema
模式是逻辑结构，是对数据库的逻辑划分
2. 在oracle中用户和schema基本上可以画上等同关系,但是pg中两者没有这样严格的对应关系

相关测试实验
创建用户
在pg中role比user少login,其他基本上相同(也就是说如果给role授权login,等同user)

postgres=# CREATE USER u_xifenfei WITH
postgres-# LOGIN
postgres-# SUPERUSER
postgres-# CREATEDB
postgres-# CREATEROLE
postgres-# INHERIT
postgres-# REPLICATION
postgres-# CONNECTION LIMIT -1
postgres-# PASSWORD 'xifenfei';
CREATE ROLE

创建表空间

postgres=# CREATE TABLESPACE tbs_xifenfei
postgres-#   OWNER u_xifenfei
postgres-#   LOCATION 'D:\Program Files\PostgreSQL\tbs_xifenfei';
CREATE TABLESPACE

创建数据库

postgres=# CREATE DATABASE db_xifenfei
postgres-#     WITH
postgres-#     OWNER = u_xifenfei
postgres-#     ENCODING = 'UTF8'
postgres-#     TABLESPACE = tbs_xifenfei
postgres-#     CONNECTION LIMIT = -1;
CREATE DATABASE

查询数据库和表空间信息

postgres=# select oid, datname, datlastsysoid, dattablespace
postgres-#  from pg_catalog.pg_database order by 1,2;
  oid  |   datname   | datlastsysoid | dattablespace
-------+-------------+---------------+---------------
     1 | template1   |         12937 |          1663
 12937 | template0   |         12937 |          1663
 12938 | postgres    |         12937 |          1663
 16407 | db_xifenfei |         12937 |         16406
(4 行记录)
postgres=# select oid,* from pg_catalog.pg_tablespace;
  oid  |   spcname    | spcowner | spcacl | spcoptions
-------+--------------+----------+--------+------------
  1663 | pg_default   |       10 |        |
  1664 | pg_global    |       10 |        |
 16406 | tbs_xifenfei |    16405 |        |
(3 行记录)

使用u_xifenfei用户登录

C:\Users\Administrator>psql -U u_xifenfei -d db_xifenfei
用户 u_xifenfei 的口令：
psql (10.4)
输入 "help" 来获取帮助信息.
db_xifenfei=# \c
您现在已经连接到数据库 "db_xifenfei",用户 "u_xifenfei".

创建测试表

db_xifenfei=# create table t_xifenfei as select * from pg_database;
SELECT 5
db_xifenfei=# select pg_relation_filepath('t_xifenfei');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/16406/PG_10_201707211/16407/16408
(1 行记录)
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 5
…………
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 327680
db_xifenfei=# select count(*) from t_xifenfei;
  count
---------
 1310720
(1 行记录)

这里可以发现,创建表空间其实对应的是一个文件夹路径,创建数据库是在对应的表空间中创建相关目录和文件,创建表是对应的相关文件.
参考:PostgreSQL tablespace database schema

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL简单操作之—创建库,登录,ddl,dml,help,登出,删除库

PostgreSQL创建数据库
使用shell级别的createdb命令创建xifenfei库

-bash-3.2$ createdb xifenfei

系统认证登录PostgreSQL数据库
使用psql登录PostgreSQL中的xifenfei数据库

-bash-3.2$ psql xifenfei
psql (9.4.4)
Type "help" for help.

PostgreSQL简单查询测试
通过查询版本,当前日期,简单加法等sql语句,测试PostgreSQL中的sql操作

xifenfei=#
xifenfei=# SELECT version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
(1 row)
xifenfei=# SELECT current_date;
    date
------------
 2015-06-21
(1 row)
xifenfei=# SELECT 2 + 2;
 ?column?
----------
        4
(1 row)

PostgreSQL数据库简单ddl和dml测试
通过创建表,插入/更新/删除记录,删除表等操作

xifenfei=# create table t_xifenfei(id int,name varchar(100));
CREATE TABLE
xifenfei=# insert into t_xifenfei values(1,'www.xifenfei.com');
INSERT 0 1
xifenfei=# select * from t_xifenfei;
 id |       name
----+------------------
  1 | www.xifenfei.com
(1 row)
xifenfei=# insert into t_xifenfei values(2,'www.orasos.com');
INSERT 0 1
xifenfei=# select * from t_xifenfei;
 id |       name
----+------------------
  1 | www.xifenfei.com
  2 | www.orasos.com
(2 rows)
xifenfei=# update t_xifenfei set name='WWW.XIFENFEI.COM' WHERE ID=2;
UPDATE 1
xifenfei=#  select * from t_xifenfei;
 id |       name
----+------------------
  1 | www.xifenfei.com
  2 | WWW.XIFENFEI.COM
(2 rows)
xifenfei=# delete from t_xifenfei where id=2;
DELETE 1
xifenfei=# select * from t_xifenfei;
 id |       name
----+------------------
  1 | www.xifenfei.com
(1 row)
xifenfei=# drop table t_xifenfei;
DROP TABLE
xifenfei=# select * from t_xifenfei;
ERROR:  relation "t_xifenfei" does not exist
LINE 1: select * from t_xifenfei;
                      ^

PostgreSQL数据库帮助使用方法
PostgreSQL数据库使用\h命令来查看帮助

xifenfei=# \h
Available help:
  ABORT                            CLUSTER                          DECLARE                          EXPLAIN
  ALTER AGGREGATE                  COMMENT                          DELETE                           FETCH
  ALTER COLLATION                  COMMIT                           DISCARD                          GRANT
  ALTER CONVERSION                 COMMIT PREPARED                  DO                               INSERT
  ALTER DATABASE                   COPY                             DROP AGGREGATE                   LISTEN
  ALTER DEFAULT PRIVILEGES         CREATE AGGREGATE                 DROP CAST                        LOAD
  ALTER DOMAIN                     CREATE CAST                      DROP COLLATION                   LOCK
  ALTER EVENT TRIGGER              CREATE COLLATION                 DROP CONVERSION                  MOVE
  ALTER EXTENSION                  CREATE CONVERSION                DROP DATABASE                    NOTIFY
  ALTER FOREIGN DATA WRAPPER       CREATE DATABASE                  DROP DOMAIN                      PREPARE
  ALTER FOREIGN TABLE              CREATE DOMAIN                    DROP EVENT TRIGGER               PREPARE TRANSACTION
  ALTER FUNCTION                   CREATE EVENT TRIGGER             DROP EXTENSION                   REASSIGN OWNED
  ALTER GROUP                      CREATE EXTENSION                 DROP FOREIGN DATA WRAPPER        REFRESH MATERIALIZED VIEW
  ALTER INDEX                      CREATE FOREIGN DATA WRAPPER      DROP FOREIGN TABLE               REINDEX
  ALTER LANGUAGE                   CREATE FOREIGN TABLE             DROP FUNCTION                    RELEASE SAVEPOINT
  ALTER LARGE OBJECT               CREATE FUNCTION                  DROP GROUP                       RESET
  ALTER MATERIALIZED VIEW          CREATE GROUP                     DROP INDEX                       REVOKE
  ALTER OPERATOR                   CREATE INDEX                     DROP LANGUAGE                    ROLLBACK
  ALTER OPERATOR CLASS             CREATE LANGUAGE                  DROP MATERIALIZED VIEW           ROLLBACK PREPARED
  ALTER OPERATOR FAMILY            CREATE MATERIALIZED VIEW         DROP OPERATOR                    ROLLBACK TO SAVEPOINT
  ALTER ROLE                       CREATE OPERATOR                  DROP OPERATOR CLASS              SAVEPOINT
  ALTER RULE                       CREATE OPERATOR CLASS            DROP OPERATOR FAMILY             SECURITY LABEL
  ALTER SCHEMA                     CREATE OPERATOR FAMILY           DROP OWNED                       SELECT
  ALTER SEQUENCE                   CREATE ROLE                      DROP ROLE                        SELECT INTO
  ALTER SERVER                     CREATE RULE                      DROP RULE                        SET
  ALTER SYSTEM                     CREATE SCHEMA                    DROP SCHEMA                      SET CONSTRAINTS
  ALTER TABLE                      CREATE SEQUENCE                  DROP SEQUENCE                    SET ROLE
  ALTER TABLESPACE                 CREATE SERVER                    DROP SERVER                      SET SESSION AUTHORIZATION
  ALTER TEXT SEARCH CONFIGURATION  CREATE TABLE                     DROP TABLE                       SET TRANSACTION
  ALTER TEXT SEARCH DICTIONARY     CREATE TABLE AS                  DROP TABLESPACE                  SHOW
  ALTER TEXT SEARCH PARSER         CREATE TABLESPACE                DROP TEXT SEARCH CONFIGURATION   START TRANSACTION
  ALTER TEXT SEARCH TEMPLATE       CREATE TEXT SEARCH CONFIGURATION DROP TEXT SEARCH DICTIONARY      TABLE
  ALTER TRIGGER                    CREATE TEXT SEARCH DICTIONARY    DROP TEXT SEARCH PARSER          TRUNCATE
  ALTER TYPE                       CREATE TEXT SEARCH PARSER        DROP TEXT SEARCH TEMPLATE        UNLISTEN
  ALTER USER                       CREATE TEXT SEARCH TEMPLATE      DROP TRIGGER                     UPDATE
  ALTER USER MAPPING               CREATE TRIGGER                   DROP TYPE                        VACUUM
  ALTER VIEW                       CREATE TYPE                      DROP USER                        VALUES
  ANALYZE                          CREATE USER                      DROP USER MAPPING                WITH
  BEGIN                            CREATE USER MAPPING              DROP VIEW
  CHECKPOINT                       CREATE VIEW                      END
  CLOSE                            DEALLOCATE                       EXECUTE
xifenfei-# \h CREATE TABLE AS
Command:     CREATE TABLE AS
Description: define a new table from the results of a query
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

PostgreSQL退出登录

xifenfei-# \q
-bash-3.2$

PostgreSQL删除数据库

-bash-3.2$ dropdb xifenfei
-bash-3.2$ psql xifenfei
psql: FATAL:  database "xifenfei" does not exist

标签归档：PostgreSQL