跳到主要内容

2. SQL语言

一、创建、删除表​

1.1 创建表​

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

1.2 删除表​

DROP TABLE 表名;

二、插入数据 INSERT INTO​

备注
  1. PostgreSQL 命令不区分大小写，除非加了单引号；
  2. 插入值时，除了数字不需要加单引号，其他需要插入表的值都需要加单引号。

有两种插入数据的方式，一种是默认按照列名插入，不需要指定列名；另一种是指定列名，这种方式可以插入部分列。

2.1 不指定列名​

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

2.2 指定列名​

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

2.3 从文件中 COPY 数据​

点击查看

三、更新数据 UPDATE​

UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE city = 'San Francisco' AND date = '1994-11-29';

四、删除数据 DELETE​

DELETE FROM weather WHERE city = 'Hayward';

五、查询数据 SELECT​

5.1 基本查询​

信息

分为三部分，SELECT、FROM、WHERE。

SELECT 指定要查询的列，FROM 指定要查询的表，WHERE 指定查询条件。

5.1.1. 对列进行计算、重命名​

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

AS 用于重命名输出列。

5.1.2. WHERE 条件中添加布尔运算符（AND、OR、NOT）​

SELECT * FROM weather WHERE city = '咸阳' AND prcp > 0.2;

5.1.3. 对结果进行排序 ORDER BY​

按城市名称进行排序：

SELECT * FROM weather ORDER BY city;

先按城市名称进行排序，如果城市名称相同，则按照 temp_hi 进行排序：

SELECT * FROM weather ORDER BY city, temp_hi;

5.1.4. 删除重复的行 DISTINCT​

SELECT DISTINCT city FROM weather;

5.2 表之间的连接 JOIN 表名 ON 条件​

5.2.1 inner join​

SELECT weather.city as weather_city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.name as cities_name, cities.location 
FROM weather 
JOIN cities ON weather.city = cities.name;

5.2.2 outer join​

上面的 inner join 会将匹配不到的 weather.city = cities.name 不进行输出，如果要输出，则又分为三种情况：

5.2.2.1 left outer join​
SELECT 
weather.city as weather_city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.name as cities_name, cities.location 
FROM weather 
LEFT OUTER JOIN cities 
ON weather.city = cities.name;
5.2.2.2 right outer join​
5.2.2.3 full outer join​

5.2.3 self join​

同一张表中，找出城市最低温度小于别的城市的温度，并且最高温度大于别的城市的温度：

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 
JOIN weather w2 
ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;

5.3 聚合函数​

关键字：

  • count：计数
  • sum：求和
  • avg：平均值
  • max：最大值
  • min：最大值
  • GROUP BY：分组
  • HAVING：对聚合后的行进行过滤
  • LIKE：模式匹配
  • FILTER：选择哪些行用于聚合计算
# 按最大值聚合查询
SELECT max(temp_lo) FROM weather;
# 根据聚合结果查出对应城市
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
# 根据城市进行分组
SELECT city, count(*), max(temp_lo) FROM weather GROUP BY city;
# 对分组进行过滤
SELECT city, count(*), max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 20;
# 匹配出城市名以'咸'开头的
SELECT city, count(*), max(temp_lo) FROM weather WHERE city LIKE '咸%' GROUP BY city;
# count 聚合函数只计算 temp_lo 低于 20 的行
SELECT city, count(*) FILTER (WHERE temp_lo < 20), max(temp_lo) FROM weather GROUP BY city;