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.111cn.net');
INSERT 0 1
xifenfei=# select * from t_xifenfei;
 id |       name     
----+------------------
  1 | www.111cn.net
(1 row)
 
xifenfei=# insert into t_xifenfei values(2,'www.orasos.com');
INSERT 0 1
xifenfei=# select * from t_xifenfei;
 id |       name     
----+------------------
  1 | www.111cn.net
  2 | www.orasos.com
(2 rows)
 
xifenfei=# update t_xifenfei set name='www.111cn.net' WHERE ID=2;
UPDATE 1
xifenfei=#  select * from t_xifenfei;
 id |       name     
----+------------------
  1 | www.111cn.net
  2 | www.111cn.net
(2 rows)
 
xifenfei=# delete from t_xifenfei where id=2;
DELETE 1
xifenfei=# select * from t_xifenfei;
 id |       name     
----+------------------
  1 | www.111cn.net
(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

聯絡：手機(13429648788) QQ(107644445)

連結：http://www.xifenfei.com/5943.html