dos模式下启动pgsql
1.进入安装目录/bin
2.创建数据库createdb dbname, dropdb dbname
3.psql [数据库名(可选,默认为账户名得数据库)]
4.执行相应sql命令, 如:
SELECT version();
SELECT current_date;
SELECT 2 + 2;
CREATE TABLE weather (
city varchar(80),
temp_lo int, – low temperature
temp_hi int, – high temperature
prcp real, – precipitation
date date
);
CREATE TABLE cities (
name varchar(80),
location point
);
DROP TABLE tablename;
INSERT 用于向表中添加行∶
INSERT INTO weather VALUES (‘San Francisco’, 46, 50, 0.25, ’1994-11-27′); 请注意所有数据类型都使用了相当明了的输入格式． 那些不是简单数字值的常量必需用单引号(‘)包围， 就象在例子里一样． date 类型实际上对可接 收的格式相当灵活， 不过在本教程里，我们应该坚持使用这里显示的格式．
point 类型要求一个座标对作为输入，如下∶
INSERT INTO cities VALUES (‘San Francisco’, ’(-194.0, 53.0)’);
到目前为止使用的语法要求你记住字段的顺序．一个可选的 语法允许你明确地列出字段∶
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES (‘San Francisco’, 43, 57, 0.0, ’1994-11-29′); 如果你需要，你可以用另外一个顺序列出字段或者是忽略某些字段， 也就是说，以未知的顺序∶
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES (’1994-11-29′, ’Hayward’, 54, 37);许多开发人员认为明确列出字段要比依赖隐含的顺序是更好的风格．
请输入上面显示的所由命令，这样你在随后的各节中才有可用的数据．
你还可以使用 COPY 从文本文件中装载大量 数据．这么干通常更快，因为 COPY 命令就是为 这类应用优化的，同时还有比 INSERT 少一些的 灵活性．比如∶
COPY weather FROM ’/home/user/weather.txt’;
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;
SELECT *
FROM weather INNER JOIN cities ON (weather.city = cities.name);
左外连接:
SELECT *
FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
自连接:
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
W2.city, W2.temp_lo AS low, W2.temp_hi AS high
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;
General
\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
connect to new database (currently ”postgres”)
\cd [DIR] change the current working directory
\copyright show PostgreSQL usage and distribution terms
\encoding [ENCODING]
show or set client encoding
\h [NAME] help on syntax of SQL commands, * for all commands
\prompt [TEXT] NAME
prompt user to set internal variable
\password [USERNAME]
securely change the password for a user
\q quit psql
\set [NAME [VALUE]]
set internal variable, or list all if no parameters
\timing toggle timing of commands (currently off)
\unset NAME unset (delete) internal variable
\! [COMMAND] execute command in shell or start interactive shell
Query Buffer
\e [FILE] edit the query buffer (or file) with external editor
\g [FILE] send query buffer to server (and results to file or |pipe)
\p show the contents of the query buffer
\r reset (clear) the query buffer
\w FILE write query buffer to file
Input/Output
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o [FILE] send all query results to file or |pipe
\qecho [STRING]
write string to query output stream (see \o)
Informational
\d [NAME] describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add ”+” for more detail)
list tables/indexes/sequences/views/system tables
\da [PATTERN] list aggregate functions
\db [PATTERN] list tablespaces (add ”+” for more detail)
\dc [PATTERN] list conversions
\dC list casts
\dd [PATTERN] show comment for object
\dD [PATTERN] list domains
\df [PATTERN] list functions (add ”+” for more detail)
\dF [PATTERN] list text search configurations (add ”+” for more detail)
\dFd [PATTERN] list text search dictionaries (add ”+” for more detail)
\dFt [PATTERN] list text search templates
\dFp [PATTERN] list text search parsers (add ”+” for more detail)
\dg [PATTERN] list groups
\dn [PATTERN] list schemas (add ”+” for more detail)
\do [NAME] list operators
\dl list large objects, same as \lo_list
\dp [PATTERN] list table, view, and sequence access privileges
\dT [PATTERN] list data types (add ”+” for more detail)
\du [PATTERN] list users
\l list all databases (add ”+” for more detail)
\z [PATTERN] list table, view, and sequence access privileges (same as \dp)
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE]
set table output option
(NAME := {format|border|expanded|fieldsep|footer|null|
numericlocale|recordsep|tuples_only|title|tableattr|pager})
\t show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x toggle expanded output (currently off)
Copy, Large Object
\copy … perform SQL COPY with data stream to the client host
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations
