SQL语言

一.创建数据库和表，添加数据

createdb exampledb;
create table weather(
    city varchar(40),
    temp_lo int,
    temp_hi int,
    prcp real,
    date date
);
create table cities(
    name varchar(40),
    location point
);
INSERT INTO cities 
    VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO cities 
    VALUES ('Hayward', '(-111.0, 55.0)');    
    
INSERT INTO weather 
    VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
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);

SQL语句的分类

1.单表的查询

查询一个表的几个方式；

1.查询表中的所有列，基本上用来做测试，在生产环境中很少这样些， 因为给表增加一个列就改变了结果

SELECT * FROM weather;

2.查询指定类, AS 是可选的;

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

3.可以用WHERE 子句来修饰，包含布尔表达式，可以有AND，OR, NOT;

SELECT * FROM weather

WHERR city = 'San Francisco' AND prcp > 0.0;

4.可以把查询的结果按指定的列来排序

SELECT * FROM weather

ORDER BY city, temp_lo;

5.你可以要求在查询的时候消除重复的行

SELECT DISTINCT city FROM weather;

6.结果行的顺序可能变化， 你可以组合使用DISTINCT 和 ORDER BY 来保证获取一致结果

SELECT DISTINCT city

FROM weather

ORDER BY city;

2.多表之间连接

一个同时访问同一个或者不同表的多个行的查询叫连接查询

举例来说，比如你想列出所有天气记录以及相关的城市位置。要实现这个目标，我们需要拿 weather表每行的city列和cities表所有行的name列进行比较， 并选取那些在该值上相匹配的行对。

注意: 这里只是一个概念上的模型。连接通常以比实际比较每个可能的行对更高效的方式执行， 但这些是用户看不到的
 

SELECT *

FROM weather, cities

WHERE city = name;

city | temp_lo | temp_hi | prcp | date | name | location

---------------+---------+---------+------+------------+---------------+-----------

San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)

San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)

(2 rows)

也可以这样查询：

SELECT *

FROM weather INNER JOIN cities ON ( city = name );

观察结果集的两个方面：

没有城市Hayward的结果集， 这是因为在cities表里面没有Hayward的匹配行，所以连接忽略 weather表里的不匹配行。我们稍后将看到如何修补它。下面是左外连接

SELECT *

FROM weather LEFT OUTER JOIN cities ON (city = name);

city | temp_lo | temp_hi | prcp | date | name | location

---------------+---------+---------+------+------------+---------------+-----------

Hayward | 37 | 54 | | 1994-11-29 | |

San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)

San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)

(3 rows)

​

3.聚集函数

一个聚集函数从多个输入行中计算出一个结果，聚集函数不能用在WHERE子句中

一.聚集函数有（count(计数), sum(和), avg(均值), max,min)；

SELECT max(temp_lo) FROM weather;

如果我们想知道该读数发生在那个城市

错误的方式：

SELECT city FROM weather WHERE temp_lo = max(temp_lo); 

正确的方式,(子查询方式):

select city from weather where temp_lo = ( select max(temp_lo) from weather);

聚集同样也常用于和GROUP BY子句组合。比如，我们可以获取每个城市观测到的最低温度的最高值：

select max(temp_lo),city from weather group by city;

city | max

---------------+-----

Hayward | 37

San Francisco | 46

(2 rows)

这样给我们每个城市一个输出。每个聚集结果都是在匹配该城市的表行上面计算的。我们可以用HAVING 过滤这些被分组的行：

SELECT city, max(temp_lo)

FROM weather

GROUP BY city

HAVING max(temp_lo) < 40;

city | max

---------+-----

Hayward | 37

(1 row)

-- # HAVING 和 where 的区别

-- 1.Having 在group by 分组后进行过滤

-- 2.where 是在分组前对数据进行过滤

-- 3.having 后面可以使用分组函数（统计函数）

-- 4.where 后面不可以使用分组函数

-- 5.where 是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，

-- 那么这条记录就不会参加分组，而having是对分组后的数据进行过滤

 

4.更新

你可以用UPDATE命令更新现有的行。假设你发现所有 11 月 28 日以后的温度读数都低了两度，那么你就可以用下面的方式改正数据：

UPDATE weather

SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2

WHERE date > '1994-11-28';

SELECT * FROM weather;

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------

San Francisco | 46 | 50 | 0.25 | 1994-11-27

San Francisco | 41 | 55 | 0 | 1994-11-29

Hayward | 35 | 52 | | 1994-11-29

(3 rows)

5.删除

1.删除语句：

delete from weather where city='Hayward';

2.如果后面没有条件where,则会删除整个表的内容，但还有结构

delete from weather;

truncate 也是 清空表，但他属于DDL语句，相当于重新定义一个新的表

truncate table table_name;

或者使用drop table name 来删除表：

drop table cities,weather;

6.创建视图

create view viewTableName as + 后面查询语句

1.创建视图的格式：create view viewTableName as + 查询语句

create view myview as

select city,temp_lo,temp_hi,prcp,date

FROM weather,cities

where city = name;

创建视图的好处：

1.视图用户允许通过始终如一的接口封装表的结构细节，可以避免原始表的结构改变而不用变化；

2.视图是放在内存里，查询速度非常快；

 

7.外键

回想以前的weather和cities表。考虑以下问题：我们希望确保在cities表中有相应项之前任何人都不能在weather表中插入行，这叫做维持数据的引用完整性；重新定义如下的表：

create table cities(

    city varchar(40) primary key,

    location point

);

create table weather(

    city varchar(40) references cities(city),

    temp_lo int,

    temp_hi int,

    prcp real,

    date date

);

尝试在weather表插入一行，会提示一个非法记录：

INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');

ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"

DETAIL: Key (city)=(Berkeley) is not present in table "cities".

外键的行为可以很好的根据应用来调整，正确使用外键无疑会提高我们的数据库质量， 强烈建议用户学会如何使用它们

8.事务

事务是所有数据库系统的基础概念。事务最重要的一点是它将多个步骤捆绑成了一个单一的、要么全完成要么全不完成的操作。步骤之间的中间状态对于其他并发事务是不可见的，并且如果有某些错误发生导致事务不能完成，则其中任何一个步骤都不会对数据库造成影响

在PostgreSQL中，开启一个事务需要将SQL命令用BEGIN和COMMIT命令包围起来。因此我们的银行事务看起来会是这样：

BEGIN;

UPDATE accounts SET balance = balance - 100.00

WHERE name = 'Alice';

-- etc etc

COMMIT;

9.窗口函数

一个窗口函数在一系列与当前行有某种关联的表行上执行一种计算。这与一个聚集函数所完成的计算有可比之处。但是与通常的聚集函数不同的是，使用窗口函数并不会导致行被分组成为一个单独的输出行--行保留它们独立的标识。在这些现象背后，窗口函数可以访问的不仅仅是查询结果的当前行

下面是一个例子用于展示如何将每一个员工的薪水与他/她所在部门的平均薪水进行比较：

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

depname | empno | salary | avg

-----------+-------+--------+-----------------------

develop | 11 | 5200 | 5020.0000000000000000

develop | 7 | 4200 | 5020.0000000000000000

develop | 9 | 4500 | 5020.0000000000000000

develop | 8 | 6000 | 5020.0000000000000000

develop | 10 | 5200 | 5020.0000000000000000

personnel | 5 | 3500 | 3700.0000000000000000

personnel | 2 | 3900 | 3700.0000000000000000

sales | 3 | 4800 | 4866.6666666666666667

sales | 1 | 5000 | 4866.6666666666666667

sales | 4 | 4800 | 4866.6666666666666667

(10 rows)

10.继承

是面向对象数据库中的概念。它展示了数据库设计的新的可能性。

让我们创建两个表：表cities和表capitals。自然地，首都也是城市，所以我们需要有某种方式能够在列举所有城市的时候也隐式地包含首都。如果真的聪明，我们会设计如下的模式：

CREATE TABLE capitals (

    name text,

    population real,

    altitude int, -- (in ft)

    state char(2)

);

CREATE TABLE non_capitals (

    name text,

    population real,

    altitude int -- (in ft)

);

​

CREATE VIEW cities AS

SELECT name, population, altitude FROM capitals

UNION

SELECT name, population, altitude FROM non_capitals;

这个模式对于查询而言工作正常，但是当我们需要更新一些行时它就变得不好用了。

更好的方案是：

CREATE TABLE cities (

name text,

population real,

altitude int -- (in ft)

);

CREATE TABLE capitals (

state char(2)

) INHERITS (cities);

在这种情况下，一个capitals的行从它的父亲cities继承了所有列（name、population和altitude）。列name的类型是text，一种用于变长字符串的本地PostgreSQL类型。州首都有一个附加列state用于显示它们的州。在PostgreSQL中，一个表可以从0个或者多个表继承