SQL 语言

注意大小写

PGSQL帮助
\h
退出
\q

1.数据的创建和删除
新建一个数据表test,使用者为test,设置UTF8
CREATE DATABASE test WITH OWNER test ENCODING = 'UTF8'；

删除一个数据库test
DROP DATABASE test;

2.创建数据表和数据
创建一个数据表weather, -- 符号为注释
Postgres SQL 支持标准的 SQL 类型：int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, interval ;
real 是一种用于存储单精度浮点数的类型;
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- 最低气温
temp_hi int, -- 最高气温
prcp real, -- 降水量
date date
);

CREATE TABLE cities ( --保存城市和它们相关的地理位置
name varchar(80),
location point
);
point 类型就是一种 PostgreSQL 特有的数据类型的例子。

3.向表中添加行
INSERT 语句用于向表中添加行：

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

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');

4.查询一个表
查表： SELECT * FROM weather;
运算： SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
WHERE: SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
排序： SELECT * FROM weather ORDER BY city;
除重复： SELECT DISTINCT city FROM weather;

5.在表之间连接

SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;
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 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;

6.聚集函数
MAX: SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
GROUP BY : SELECT city, max(temp_lo) FROM weather GROUP BY city;
HAVING : SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;

7.更新，删除
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';

DELETE FROM weather WHERE city = 'Hayward';