数据库系统原理学习（三）--PG数据定义与操作

1. SQL概述

关系数据库的标准语言是Structed Query Language,即结构化查询语言，简称SQL。其功能包括数据定义、查询、修改和保护等。
以PostgreSQL为例，经常简写为PG。

SQL语言的核心是Select语句，基本Select语句的一般形式：

// 基本Select语句的一般形式
SELECT[ALL|DISTINCT]
      <目标列表达式>[别名][,<目标列表达式>[别名]]...
[FROM <表名>[别名][<连接表达式>]...
[WHERE <条件表达式>]
[GROUP <列名>[<列名>]
[HAVING <条件表达式>]]
[ORDER BY <列名> [ASC|DESC][<列名>[ASC|DESC]]
[LIMT <行数> [<OFFSET 偏移量>]]];

一共七个子句，但后面六个子句都是可选的，在实际查询中，至少有一个SELECT子句。

整个语句的执行过程：

1. 如果仅有SELECT子句，按SELECT子句中给出的列名或列表达式求值，否则继续执行2；
2. FROM：从FROM子句获得表；
3. 选取满足WHERE子句所给出条件表达式的行；
4. 按GROUP BY子句中指定列的值分组；
5. 提取满足HAVING子句中组条件表达式的那些组；
6. 按SELECT子句中给出的列名或列表达式求值；
7. ORDER BY子句对输出的目标表进行排序，ASC：升序，DEXC：降序；
8. 按照LIMIT子句的偏移量和行数确定输出元组；
9. 输出。

没有FROM子句就不能有WHERE子句
没有GROUP BY子句就不能有HAVING子句

注意：在实际执行时数据库管理系统会为了尽可能快地获得等价查询结果而采取完全不同的执行步骤。
SQL语句大小写不敏感
用单引号做字符串常量的标识

2. 数据定义与修改

表的定义：表模式的创建、修改和删除。
数据修改：给表里添加数据、更新数据以及删除数据。
创建表：

CREATE TABLE <表名>
(<列名> <数据类型>,
 <列名> <数据类型>,
 ...
);

修改表名：

// 把表名从a修改为b
ALTER TABLE a RENAME TO b;

删除表：

DROP TABLE <表名>;

修改表的结构：

ALTER TABLE <表名>
   [ADD COLUMN <新列名> <数据类型>] //给表中添加一个新列
   [RENAME COULUMN <旧列名> TO <新列名>] //把表中对应列的名称进行修改
   [ALTER COULUMN <列名> TYPE <数据类型>] //把表中给定的列的数据类型进行修改
   [DROP COULUMN <列名>]; //删除表中给定的列

插入数据的三种方式
①插入元组：

INSERT INTO <表名> [(属性1>[,<属性2>...)]
VALUES(<常量1>[,<常量2>]...)

②插入查询结果：

INSERT INTO <表名> [(列名序列)]
<SELECT查询语句>

③插入表：

INSERT INTO <表名1> [(列名序列)]
TABLE<表名2>

插入常量元组时，
插入元组的属性值必须在属性域中；
插入元组的分量个数也必须是正确的；
元组值对应的属性排列顺序必须与定义时的顺序一致；
否则必须在INSERT语句中明确指明属性顺序。
在插入常量元组中，非主键属性可以使用空值null。

删除元组：

DELETE FROM <表名> [WHERE <条件表达式>]

如果没有任何条件限制，将删除所有元组。
更新表中的某些列值：

UPDATE <表名>
SET <列名> = <表达式> [,<列名>=<表达式>]...
[WHERE <条件>]

3. 查询操作

3.1 简单查询

单表查询：投影、选择、聚集。
投影：包括基本投影和广义投影。基本投影是指选取表中的某些列的列值；广义投影是指在选取属性列时，允许进行适当运算。
例：

SELECT eid,ename
FROM exampaper;

这个语句的执行过程就是对exampaper表，输出其每一行的试卷号eid，试卷名ename值。

SELECT *
FROM exampaper;

这个语句的执行就是对exampaper表，输出其每一行的每一列值。

SELECT后面可以是表达式，即为广义投影。该情况下，为FROM后面给出表的每一行计算一个表达式值，作为对应行中相应表达式的值。

//查询全体考生成绩与满分（100）之差
SELECT eeid,100-achieve
FROM eexam;

选择：用WHERE子句实现，从表中选择满足给定条件的行。

//查询年龄大于20的所有考生的信息，条件为eeage>20
SELECT *
FROM examinee
WHERE eeage>20;

如果没有WHERE子句，就是无条件选择所有行。
WHERE子句中的条件表达式可以用各种运算符组合而成。

聚集：从多个输入行中计算出一个结果。
经常使用的聚集函数：
COUNT（计数）、SUM（求和）、AVG（平均值）、MAX（最大值）、MIN（最小值）、协方差、标准差等等。
SUM和AVG的输入必须是数值型的，其他聚集函数还可以用在非数值数据类型的列上，聚集函数不能进行复合运算。

3.2 联接查询

如果查询的数据涉及两个或多个表，可是使用联接操作，称为联接查询。
联接条件：决定了两个表中哪些行是匹配的，以及联接结果中出现哪些列。
联接类型：按照对悬浮行的不同处理方式来分的。
（内联接、左外联接、右外联接、全外联接）
查询涉及多个表，FROM后面表名以逗号或CROSS JOIN分隔。
FROM子句的结果表就是这些表的笛卡尔积，结果表包含所有这些表的所有列，如果表中有同名列，在列名前加上表名做前缀，表明列的来源。

自然联接：即自然内联接，是在笛卡尔积的基础上选取所有同名列上取值相等的行，结果表中同名列只出现一次。
属性联接：即属性内联接，是在笛卡尔积的基础上选取指定同名属性上取值相等的行，结果表中这些指定同名属性只出现一次。
区别：自然联接所有同名属性全部取值相等；属性联接指定其中若干同名属性取值相等。
如果属性联接指定全部同名列来匹配，则等价于自然联接。
条件联接：即条件内联接，是在笛卡尔积运算的基础上选取满足给定条件的行。

内联接可能会出现
左表当中的一些行在右表中没有相匹配的行；
右表当中的一些行在左表中没有相匹配的行。
这些没有找到匹配的行称为悬浮行。

内联接和外联接的区别：
内联接抛弃所有悬浮行；
外联接：左外联接、右外联接、全外联接。

左外联接的计算：
内联接：计算内联接的结果；
外联接：把左侧表中的悬浮行加入结果表，这些行中来自右侧表的属性赋值为空值null。
右外联接的计算：
内联接：计算内联接的结果；
外联接：把右侧表中的悬浮行加入结果表，这些行中来自左侧表的属性赋值为空值null。
全外联接：
是左外联接和右外联接的结合，左侧表中的悬浮行补上空值后加到结果表中，同时，右侧表中的悬浮行补上空值后也加到结果表中。

PGSQL的联接计算：
内联接用INNER而外联接用OUTER
默认为INNER
LEFT、RIGHT、FULL均隐含外联接

3.3 嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块。
表式嵌套：查询块可以出现在另外一个查询中表名可以出现的任何地方。

// 查询平均成绩良好（>=80）的考生人数
WITH avgach(eeid,avgachieve) AS
           (SELECT eeid,AVG(achieve)
            FROM eeexam
            GROUP BY eeid
           )
SELECT COUNT(*)
FROM avgach
WHERE avgachieve>=80;

也可以使用FROM子句来实现：

// 查询平均成绩良好（>=80）的考生人数
SELECT COUNT(*)
FROM (SELECT eeid,AVG(achieve)
       FROM eeexam
       GROUP BY eeid
      )AS avgach (eeid,avgachieve)
WHERE avgachieve>=80;

集合式嵌套：查询块也可以出现在集合能够出现的任何地方。