SQL入门

\encoding utf-8;
/*
beging:创建需要学习的数据库study开始postgresql的学习
*/

CREATE DATABASE study;
\c study;
\encoding utf-8;
/*
next:创建新表weather
city       temp_lo      temp_hi      preciptation
*/

CREATE TABLE weather ( city varchar(80), temp_lo int, /*low temperature*/ temp_hi int, /*high temperature*/ prcp real, /*precipitation*/ date date);


/*
next:创建新表cities
name                    location
*/

CREATE TABLE cities (
    name            varchar(80),
    location        point
);


/*
next:向表中添加数据
*/

INSERT INTO weather(city, temp_lo, temp_hi, prep,date)
VALUES ('San Francisco',46,50,0.25,'1994-11-27');

INSERT INTO cities(name, location) VALUES ('San Francisco', '(-194.0,53.0)');

INSERT INTO weather (date,city,temp_hi,temp_lo)
VALUES ('1994-11-29','Hayward',54,37);

/*
end:通过 \i yousqlfilepath  以便进行以后的学习
*/

/*
begin:单表检索
*/
\c study;
--按字段检索
SELECT city ,temp_lo,temp_hi,prcp,date FROM weather;
--整合字段信息并重命名
SELECT city, (temp_hi + temp_lo) / 2 AS temp_avg,date FROM weather;
--按条件检索 AND OR NOT....
SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
--要求返回结果是排序好的
SELECT * FROM weather ORDER BY city;
SELECT * FROM weather ORDER BY city,temp_lo;
--要求消除重复结果
SELECT DISTINCT city FROM weather;
SELECT DISTINCT city FROM weather ORDER BY city;
--自连接  利用表行间字段的关系检索
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;
/*
next:表间连接检索 -- 内连接(消除其他表不存在行)
表间无概念上的主次关系
*/
--选取表weather city字段和cities name相同的行
SELECT * FROM weather ,cities WHERE city = name;
--不需要重复信息
SELECT city/*or name*/,temp_lo,temp_hi,prcp,date,location FROM weather,cities WHERE city = name;
--如果表间有重复名称,用字段全称限定

SELECT weather.city,weather.temp_lo,weather.hi,weather.prcp,weather.date,cities.location FROM weather, cities WHERE cities.name = weather.city;
--使用inner join ....on代替上述语法
SELECT * FROM weather INNER JOIN cities ON(weather.city = cities.name);

/*
next:表间连接检索  --外连接(其他表不存在数据用“空值”代替产生新行)
*/
--右表不存在的数据字段用空值代替
SELECT * FROM weather LEFT OUTER JOIN cities ON (
weather.city = cities.name); 
--左表不存在的数据字段值用空值代替
SELECT * FROM weather RIGHT OUTER JOIN cities ON (
weather.city = cities.name);
--可以看作左外连接和右外连接的并集
SELECT * FROM weather FULL OUTER JOIN cities ON (
weather.city = cities.name);

\c study;
/*
begin:聚集函数 综合表的行得到结果
*/

SELECT max(temp_lo) FROM weather;
--注:聚集函数不能用于where子集
SELECT city FROM weather where temp_lo = (select max(temp) FROM weather);
--聚集函数用于GROUP BY 子句
SELECT city,max(temp_lo) FROM weather GROUP BY city;
--使用HAVING 过滤分组 注:HAVING子句只能出现聚集函数或者GROUP BY子句中的条件
SELECT city,max(temp_lo) FROM weather GROUP BY city
 HAVING max(temp_lo) < 40;

 --LIKE 子句 
SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%' GROUP BY city HAVING max(temp_lo) > 40;

/*
next:更新   UPDATE tablename  SET column = somevalue....
*/
UPDATE weather SET temp_hi = temp_hi - 2,temp_lo = temp_lo - 2 WHERE date > '1994-11-28';

/* 
next:删除  DELETE FROM tablename..无WHERE条件会删除所有行
*/
DELETE FROM weather WHERE city = 'Hayward';

/*
end
*/