PostgreSql初探(2)-创建数据库

1.创建数据库
createdb
[postgres@gc1 ~] createdb mydb

createdb 是一个 SQL 命令 CREATE DATABASE的封装,和在psql里通过create database mydb效果是一样的
具体可以参见文档http://www.postgres.cn/docs/9.3/app-createdb.html

2.访问数据库
psql,就像sqlplus一样
[postgres@gc1 ~] plsq mydb
psql (9.4.4)
Type “help” for help.

mydb=#
最后一行也可能是mydb=>
‘#’意味着你是数据库超级用户

mydb=# 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-48), 64-bit
(1 row)

mydb=# select version()

不加‘；’的话不会像oracle与mysql一样等待你输入‘；’

psql程序有一些不属于SQL命令的内部命令，他们以‘\’开头

mydb=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

mydb-# \h
Available help:
  ABORT                            CREATE FOREIGN DATA WRAPPER      DROP SEQUENCE
  ALTER AGGREGATE                  CREATE FOREIGN TABLE             DROP SERVER
  ALTER COLLATION                  CREATE FUNCTION                  DROP TABLE
  ALTER CONVERSION                 CREATE GROUP                     DROP TABLESPACE
  ALTER DATABASE                   CREATE INDEX                     DROP TEXT SEARCH CONFIGURATION
  ALTER DEFAULT PRIVILEGES         CREATE LANGUAGE                  DROP TEXT SEARCH DICTIONARY
  ALTER DOMAIN                     CREATE MATERIALIZED VIEW         DROP TEXT SEARCH PARSER
  ALTER EVENT TRIGGER              CREATE OPERATOR                  DROP TEXT SEARCH TEMPLATE
  ALTER EXTENSION                  CREATE OPERATOR CLASS            DROP TRIGGER
  ALTER FOREIGN DATA WRAPPER       CREATE OPERATOR FAMILY           DROP TYPE
  ALTER FOREIGN TABLE              CREATE ROLE                      DROP USER
  ALTER FUNCTION                   CREATE RULE                      DROP USER MAPPING
  ALTER GROUP                      CREATE SCHEMA                    DROP VIEW
  ALTER INDEX                      CREATE SEQUENCE                  END
  ALTER LANGUAGE                   CREATE SERVER                    EXECUTE
  ALTER LARGE OBJECT               CREATE TABLE                     EXPLAIN
  ALTER MATERIALIZED VIEW          CREATE TABLE AS                  FETCH
  ALTER OPERATOR                   CREATE TABLESPACE                GRANT
--More--

sql命令的帮助信息

mydb=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gset [PREFIX]         execute query and store results in psql variables
  \h [NAME]              help on syntax of SQL commands, * for all commands
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
--More--

psql命令的帮助信息

mydb-# \q
[postgres@gc1 ~]$
\q就是退出了，也不会向mysql一样说个bye啊:-(

关于plsql的详细内容可以看文档http://www.postgres.cn/docs/9.3/app-psql.html
挑几个有用的说说

testdb=>\set PROMPT1 ‘%n@%m %~%R%# ‘

peter@localhost testdb=>
不用说也能懂了吧

You can display tables in different ways by using the \pset command:

peter@localhost testdb=>\pset border 2Border style is 2.
peter@localhost testdb=>SELECT * FROM my_table;+-------+--------+
| first | second |
+-------+--------+
|     1 | one    |
|     2 | two    |
|     3 | three  |
|     4 | four   |
+-------+--------+
(4 rows)

peter@localhost testdb=>\pset border 0Border style is 0.
peter@localhost testdb=>SELECT * FROM my_table;first second
----- ------
    1 one
    2 two
    3 three
    4 four
(4 rows)

peter@localhost testdb=>\pset border 1Border style is 1.
peter@localhost testdb=>\pset format unalignedOutput format is unaligned.
peter@localhost testdb=>\pset fieldsep ","Field separator is ",".
peter@localhost testdb=>\pset tuples_onlyShowing only tuples.
peter@localhost testdb=>SELECT second, first FROM my_table;one,1
two,2
three,3
four,4

通过\pset改变输出格式

Alternatively, use the short commands:

peter@localhost testdb=>\a \t \xOutput format is aligned.
Tuples only is off.
Expanded display is on.
peter@localhost testdb=>SELECT * FROM my_table;-[ RECORD 1 ]-
first  | 1
second | one
-[ RECORD 2 ]-
first  | 2
second | two
-[ RECORD 3 ]-
first  | 3
second | three
-[ RECORD 4 ]-
first  | 4
second | four
\x  Expanded display