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