PostgreSQl
SQL语言
概念
pgsql是关系型数据库管理系统。关系本质上是表的数学表达。
表是行的集合。同一个表中每行都有相同的列。每个列都是一个特定的数据类型。每列都有固定的顺序,但是无序的。
表被分组存放到数据库,由单个pgsql服务器实例管理的数据库集合构成一个数据库集群。
创建表
先来看一个例子:
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
操作如下图:
划掉的部分不是怕泄密,而是那些地方不重要。
启动pgsql后输入上述命令,就可以创建一张表。psql能够识别分号才是命令的结束,当输入为结束之前,命令提示符也是不一样的。
SQL命令中可以自由使用空格、缩进和换行,上面的命令既可以在一行输入,也可以在多行输入。正如你所见,换行以后并没有结束命令的输入,遇到分号;
命令才结束。
--
表示单行注释。
SQL不区分大小写,但是双引号内用以保存的字符串是区分大小写的。
varchar(80)
表示长度为80个字符的可以存储任意字符的字符串。int
表示整数类型。real
表示单精度浮点数。date
表示日期。正如你所见,变量名和类型都叫date
,这是允许的,因为类型并不是SQL关键字。
SQL数据类型
-
int
-
smallint
-
real
-
double precision
-
char(N)
-
varchar(N)
-
date
-
time
-
timestamp
-
interval
-
几何类型
-
自定义类型
就像前面说的,类型名称不是SQL关键字,除了SQL标准中的特殊类型。比如下面的point
类型:
CREATE TABLE cities (
name varchar(80),
location point
);
最后,要删除一张表可以使用如下命令:
DROP TABLE tablename;
现在我们为mydb数据库创建了两张表,那么去哪里能看到这两张表呢?
还记得pgsql默认安装的pgAdmin吗?它是图形化应用程序,从这里可以清楚的看到。如果你安装的也是最新的pgAdmin 4的话,那么一定会想哭的,WTF?我的表呢?表在哪里?
不得不说相比于之前的版本,很想骂人。甚至于在很长一段时间内,面对pgAdmin 4笔者都是一脸懵逼的状态。好在天无绝人之路,小编幸得高人指点,终于找到它把数据库表藏到了模式
>>public
>>表
下面。
高人名叫殇莫忆(一听名字就知道是高人),住在https://blog.csdn.net/qq_28289405 。
向表中插入一行数据
-
对号入座。
类似于C语言对结构体的初始化。我们说过,列在行中的顺序是固定的,我们可以按照列的顺序将所有值依次列出来插入一行。如下:
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT
是插入命名,INTO
后面是数据库名字,VALUES
后面是要插入的数据。所有数据用括号括起来,数据之间用逗号隔开,非数值的数据全部要用单引号引起来。即使是
point
这样的SQL标准类型,也要用写在引号中。如下:INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
point
类型需要坐标对作为输入,即x
坐标和y
坐标。 -
韩信点兵。
第1中方法中,要求数据的顺序和列的顺序不差分毫。这里的方式也可以按列的名字插入数据,类似于golang中按名称初始化结构体。
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
如你所见,现在数据库名字后面跟了一个括号,里面列出了需要插入数据的列的名字,
VALUES
后面是与前边对应的数据值。这样你不必记得列在行中的顺序,只需要知道你需要往哪些列中插入数据即可,现在数据的顺序完全有weather
后面的字段顺序决定。这也意味着你可以省略某些列的初始化,例如不初始化降雨量(prec):
INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);
推荐使用这种方式。
-
一气呵成。
想象一下如果你有一万条数据要录入,要是靠手一条一条命令敲进去。。。喂,120吗。。。
所以SQL提供了
COPY
命令从文件中加载大量数据。因为对COPY
命令进行了优化,所以它要比INSERT
更快。然而阴阳相生,在更快的同时也失去了部分的灵活性。COPY weather FROM '/home/user/weather.txt';
而且,文件名必须在运行后端进程的机器上可用,而不是客户端,因为后端进程直接读取文件。
查询
使用SELECT
语句。SELECT
包含以下几个部分:
-
查询列表。紧跟在
SELECT
后面,用逗号隔开的列名,想查询哪些列的数据就把列的名字写在后面,*
表示所有列。 -
表。跟在
FROM
后面,想要查询的表的名字,可以有多个表。 -
查询条件(可选)。查询的约束条件。
普通查询
让我们来看第一个例子,从weather表中查询所有数据。
SELECT * FROM weather;
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
以上两条命令是等价的。结果如下:
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
查询表达式
查询列表中不仅可以写想要查询的列的名字。还可以是表达式。如下:
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
输入上面的命令,你将得到如下输出:
city | temp_avg | date
---------------+----------+------------
San Francisco | 48 | 1994-11-27
San Francisco | 50 | 1994-11-29
Hayward | 45 | 1994-11-29
(3 rows)
AS
字句的作用是给字段重命名,它不是必须的。如果没有AS
字句,对应字段会用默认命名?column?
,结果如下:
city | ?column? | date
---------------+----------+------------
San Francisco | 48 | 1994-11-27
San Francisco | 50 | 1994-11-29
Hayward | 45 | 1994-11-29
(3 rows)
WHERE
条件查询
WHERE
后面必须跟bool表达式。查询只返回表达式为true
的数据。多个bool表达式之间用AND
、OR
和NOT
连接。
例如,查询"San Francisco"降雨量大于0的数据:
SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
ORDER BY
排序查询
ORDER BY
后跟一个或多个列名称,可以使查询结果按指定的列排序。例如:
按城市名排序:
SELECT * FROM weather ORDER BY city;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
Hayward | 37 | 54 | | 1994-11-29
San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27
按城市名和最低温排序:
SELECT * FROM weather ORDER BY city, temp_lo;
自所以需要按多个列来排序,因为如果只按一个列来排序时,难保该列中会有同样的值,那么他们之间的顺序依然是任意的。
DISTINCT
去除重复的数据
在查询列表中的列名字前加上DISTINCT
可以查询该列的数据并去掉重复的数据。
SELECT DISTINCT city FROM weather;
city
---------------
Hayward
San Francisco
(2 rows)
当然,在去重的同时,也可以进行排序。去重排序两不误。
SELECT DISTINCT city FROM weather ORDER BY city;
为什么会有去重的同时排序这种操作呢?
原来在早期的pgsql中,DISTINCT
命令会自动对结果进行排序,而不需要手动ORDER BY
。但这并不是SQL的标准,于是后来pgsql就去掉了DISTINCT
命令的排序功能。所以现在才有了去重并排序这样的操作。
连表查询
同时访问一个或多个表的多个行。
例如,在weather和cities表中查询字段city和字段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)
问题1:city字段和name字段内容相同,重复输出。
可以指定查询列表,选择输出哪些列来去掉重复。
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
思考:上面的查询语句去掉WHERE
字句后,结果是什么?(排列组合)
问题2:语法分析器能够自动识别查询列表中的列属于哪个表,但如果两个表中存在同名字段,需要加上表名显示指定该字段属于哪个表。
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;
也许你正奇怪,为什么连表查询没有看到JOIN
呢?其实到目前为止,上面讲的查询都是在做INNER JOIN
查询。本节的第一条查询命令等同于下面的查询语句:
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
ON
字句表示的是查询条件。
五种JOIN
1. JOIN
SELECT * FROM weather JOIN cities ON (weather.city = cities.name);
单独使用JOIN
等同于INNER JOIN
。
2. INNER JOIN
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
只输出符合查询条件的行。
3. LEFT OUTER JOIN
SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
列出LEFT OUTER JOIN
关键字左边的表中的所有行,以及关键字右边表中符合查询条件的行。如果没有匹配的行则输出空。
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)
4. RIGHT OUTER JOIN
SELECT * FROM weather RIGHT OUTER JOIN cities ON (weather.city = cities.name);
与LEFT OUTER JOIN
相反,RIGHT OUTER JOIN
会列出关键字右边表的所有行,以及左边表中符合查询条件的行。没有匹配的行输出空。
为了更清晰的分析命令的含义,我们先向cities表中再插入一条数据:
INSERT INTO cities VALUES ('London', '(205, 88)');
然后再执行上面的查询语句。
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 10 | 20 | 3 | 2000-03-01 | San Francisco | (-194,53)
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
| | | | | London | (205,88)
因为cities表中只有一条记录,而且匹配成功,所以结果看起来跟INNER JOIN
的结果一样。
5. FULL OUTER JOIN
SELECT * FROM weather FULL OUTER JOIN cities ON (weather.city = cities.name);
两个表中的所有记录都会列出,相匹配的记录输出到同一行,没有匹配的那边输出空。
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
Hayward | 37 | 54 | | 1994-11-30 | |
San Francisco | 10 | 20 | 3 | 2000-03-01 | San Francisco | (-194,53)
| | | | | London | (205,88)
(4 行记录)
你可以清楚的看到查询结果中哪些地方是空着的。
关于JOIN
就只有这么5种组合方式,其他的组合在语法上就是错误的。切记!
一个表除了可以和其他表JOIN
,还可以自己跟自己玩,称为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, weather W2
WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi > W2.temp_hi;
先看结果再分析。
city | low | high | city | low | high
---------------+-----+------+---------------+-----+------
San Francisco | 43 | 57 | San Francisco | 46 | 50
Hayward | 37 | 54 | San Francisco | 46 | 50
(2 rows)
这里用到了很多重命名技术。查询列表中的AS
字句自不必多说。其中w1
和w2
都是weather表,类似于C语言中声明变量的做法。也就是说查询的数据来自同一张表——weather表。
w1
和w2
就是weather表中的两条记录,不要把它们看做是表,只是它们来自同一张表而已。满足w1
的气温范围包含w2
的气温范围时,将这两条记录输出。
这种重命名技术也可以用在其他查询语句中,例如:
SELECT * FROM weather w, cities c WHERE w.city = c.name;
这相当于是给表取了一个别名。
聚合(统计,拿不准哪个词合适)函数
-
count
-
sum
-
avg
-
max
-
min
以上这些函数和C语言中的函数的用方法别无二致。都是函数名后跟括号,括号中是参数,这里参数是列名称。例如,查询weather表中最低温最大的数据:
SELECT max(temp_lo) FROM weather;
max
-----
46
(1 row)
其他函数的使用同max
一样,就不一一演示了。大家可以自己试试,练习一下。
现在问题又来了。如果我想输出最低温最大的城市的名字,该如何是好呢?
这个时候是比较麻烦的,需要使用嵌套查询。如下:
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city
---------------
San Francisco
(1 row)
每个SELECT
是一个独立的计算单元,它们之间互不影响。所以我们可以将一个SELECT
的输出作为另一个SELECT
的输入。
也许你觉得这样的嵌套太麻烦了。那么可不可以在WHERE
字句直接写WHERE temp_lo = max(temp_lo)
呢?这样是万万不可滴,切记切记!
原因是:聚合函数的输入并不是表中的所有记录,而是由WHERE
字句选出来的那些记录。WHERE
都还没执行,max
又拿什么去计算呢?所以聚合函数不能用在WHERE
字句中!
聚合函数与GROUP BY
让咱们先来个例子开开胃。
SELECT city, max(temp_lo) FROM weather GROUP BY city;
city | max
---------------+-----
Hayward | 37
San Francisco | 46
(2 rows)
GROUP BY
的意思就是分组。其后跟着的列名,所有该列的值相同的行都会被分为一组。所有查询操作包括聚合函数都会在每个组上执行,最后返回查询结果 。
GROUP BY
和WHERE
是相似的。只不过WHERE
是将所有满足bool表达式的行作为一个组返回,然后在这个组上执行其他查询操作。而GROUP BY
将表中的所有行按某列分成若干组,然后其他查询操作在每个组上都会执行一遍。
####聚合函数与HAVING
通过在HAVING
字句中使用聚合函数,可以进一步限制查询出来的结果。例如在上面的查询结果中选出最低温的最大值小于40的记录。
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;
city | max
---------+-----
Hayward | 37
(1 row)
总结
最后WHERE
字句还支持LIKE
模式匹配:
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%'
GROUP BY city
HAVING max(temp_lo) < 40;
那么问题是为什么将模式匹配放在WHERE
子中,而不是放到HAVING
字句中?
要回答这个问题首先要弄清楚聚合函数、WHERE
字句、GROUP
字句以及HAVING
字句的执行顺序。
它们的执行顺序依次是:
WHERE
字句GROUP
字句- 聚合函数
HAVING
字句
WHERE
字句最先执行,选出符合条件的记录。其次是GROUP
字句,这也意味着GROUP
的分组对象可以是SELECT
选出来的部分记录。
聚合函数在GROUP BY
函数之后执行,这也是为什么在WHERE
字句和GROUP BY
字句中不能使用聚合函数的原因。
HAVING
字句最后执行,所以其中可以用聚合函数进一步过滤查询结果。HAVING
基本就是和聚合函数一起使用,鲜有HAVING
字句中不带聚合函数的。
最后我们来回答上面的问题。将模式匹配放在WHERE
子中,而不是放到HAVING
字句中显然更高效。因为这样只需要对WHERE
选出来的记录执行GROUP
操作和HAVING
过滤,而不是对所有的记录执行。
更新
更新数据用UPDATE
命令。更新只能修改已有的记录。
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';
UPDATE
后跟要修改的表名。然后是SET
以及后面的表达式,表达式之间用逗号分隔,负责修改数据。最后是可选的查询条件,意味着可以限定修改的范围,也可以全部修改。
不信你看↓↓
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)
这一集就这么短!
删除
使用DELETE
命令删除一行,也就整条记录。
DELETE FROM weather WHERE city = 'Hayward';
city = 'Hayward'
记录已被删除,不信随便查。
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
(2 rows)
最后,删除命令也可以不带查询条件。
DELETE FROM tablename;
这样的命令将偷偷删除表中的所有记录,不会有任何提示。所以如果不是脑子抽风尽量碰它。