数据库基础Database1-初级SQL

作者: sunblog | 来源:发表于2018-04-16 23:36 被阅读0次

数据库基础Database1

一基础概念

1. 概念

数据库管理系统（Database Management System, RDMS)由一个互相关联的数据的集合和一组用以访问这些数据的程序组成。这个数据集合通常称作数据库（database）。

将数据存储在文件中有以下弊端：

数据的冗余和不一致
数据访问困难
数据孤立
完整性问题
原子性问题（比如：发生异常后恢复到以前的一致状态）
并发访问异常
安全性问题

特定时刻存储在数据库中的信息的集合称作数据库的一个实例（instance）。数据库的总体设计称作数据库模式（schema）。

数据抽象

view_level

数据库可分为物理层（physical level），逻辑层（logical level），视图层（view level)

物理层：最低层次的抽象，描述数据实际上是怎样存储的。物理层详细描述复杂的底层数据结构。(红黑树等数据结构）
逻辑层：比物理层层次稍高的抽象。描述数据库中存储什么数据以及这些数据间存在什么关系。(Table)
视图层：最高层次的抽象。只描述数据库的某个部分。（View）

2. 数据模型

数据模型可划分为四类：

关系模型（relational model）。关系模型用表(table)的集合来表示数据和数据间的联系。每个表有多个列（column），每个列有唯一的列名。(MySQL)
实体-联系模型（entity-relationship model）。实体-联系（E-R)数据模型基于对现实世界的这样一种认识：现实世界由一组称作实体的基本对象以及这些对象间的联系构成。(ER图）
基于对象的数据模型（object-based data model）。（面向对象）
半结构化数据模型（semistructured data model）。（xml）

3. 数据库语言

数据库操纵语言（Data-Manipulation Language, DML）是这样一种语言：它使得用户可以访问和操作按照适当的数据模型组织起来的数据。有以下访问类型：(CRUD, 增删查改）

对存储在数据库中的信息进行检索。

向数据库中插入新的信息。
从数据库中删除信息。
修改数据库中存储的信息。

通常有两类基本的数据操作语言（DML)：

过程化DML(procedural DML): 要求用户指定需要什么数据以及如何获得这些数据。

声明式DML(declarative DML，也成非过程化DML): 只要求用户指定需要什么数据，而不指明如何获得这些数据。

查询（Query）是要求对信息进行检索的语句。DML中设计信息检索的部分称作查询语言。

数据库定义语言（Data-Definition Language，DDL) : 定义数据库模式的一系列语言。

二关系数据库

1.基本属术语

在关系模型的术语中，关系（relation）用来指代表（table），而元组（tuple）用来指代行（row），属性（attribute）指代的是表中的列（column）。

对于关系的每个属性，都存在一个允许取值的集合，成为该属性的域（domain)。

数据库模式（database schema）是数据库的逻辑设计。数据库实例（database instance）是给定时刻数据库中数据的一个快照。

模式图（schema diagram）：数据库模式的图形化表示。

关系查询语言（relational query language）：定义了一组运算集合，这些运算可以运作在表上，并输出表作为关系结果。这些运算也可以组合成表达式，表达所需的查询。

空值（Null）：表达未知或不存在。

键（key）

超键（superkey）：一个或多个属性的集合，这些属性的组合可以使我们在一个关系中唯一地标识元组。
候选键（candidate key）：是一个超键，但缺少了任意一个属性就不能成为超键。
主键（primary key）：被数据库设计者选中的，用来区分元组的候选键。
外键（foreign key）：一个关系模式(r1)在它的属性中包括一个关系模式(r2)的主键(primary key)。这个属性在r1上称作参照r2的外键（foreign key）。r1称作外键依赖的参照关系（referencing relation)。r2叫做外键的被参照关系（referenced relation)。

参照性完整约束(referentianl integrity constraint)：参照性完整约束要求在参照关系中任意元组在特定属性上的取值必然等于被参照关系中某个元组在特定属性上的取值。（r1任意元组在外键上任意取值，必然能在r2上对应的属性找到相同的取值）

关系运算：选择，投影，自然连接，笛卡尔积，并。

relation_operations

选择（selection）：返回输入关系中满足谓词的行。（图中是返回sarary >= 85000的行）

投影（projection）：对输入关系中所有的行指定需要输出哪些属性。

自然连接（natural join）：从两个输入关系中输出这样的元组对：它们在具有相同属性名字的属性上取值相等。

笛卡尔积（cartesian product)：从两个输入关系中输出所有元组对。不管它们是否具有相同的属性，也不管相同属性的取值是否相等。

2.SQL

SQL查询语言有以下几个部分：

数据定义语言（Data-Definition Language, DDL)：SQL DDL提供定义关系模式、删除关系以及修改关系模式的命令。（针对的是关系）
数据操纵语言（Data-Manipulation Language, DML): SQL DML提供从数据库中查询信息，以及在数据库中插入元组、删除元组以及修改元组的能力。（针对的是元组）
完整性（integrity）：SQL DDL包含了定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束。破坏完整性约束的更新是不被允许的。
视图定义（view definition): SQL DDL包含了视图定义的命令。
事务控制（transaction control）：SQL 包括定义事务的开始和结束的命令。
嵌入式SQL和动态式SQL（embeded SQL and dynamic SQL)：嵌入式和动态SQL定义SQL语句如何嵌入到通用编程语言，如c++,java。
授权（authorization）：SQL DDL包括定义对关系和视图访问权限的命令。

2.1基本类型

基本类型：

CHAR(n): 固定长度的字符串，用户指定长度n。也可以使用全程character。
VARCHAR(n): 可变长度的字符串，用户指定最大长度。
INT: 整数类型（和机器相关的的整数的有限子集）。全程integer。
SMALLINT：小整数类型（和机器相关的的整数的子集）。
NUMERIC(p,d): 定点数，精度由用户指定。这个数有p位数字（小数点不算），其中d位数字在小数点右边。如何，numeric(3,1)可以存储44.1，但不能存储44.11， 444.1。
REAL, DOUBLE: 浮点数与双精度浮点数。精度与机器相关。注意有的数据库没有DOUBLE。有的数据库REAL 和 DOUBLE是同一个意思。

日期类型：

DATE: 日历日期，包括4位年份，月和日。'2001-04-25'
TIME: 一天中的时间。包括小时、分和秒。'09:30:00'
TIMESTAMP: DATE和TIME的组合。可以用TIMESTAMP(p)来表示秒的小数点后的数字位数（默认是0）。'2001-04-25 09:30:00'

大对象类型(BIG OBJECT)

当数据库需要存储很大(KB级)的属性，比如类文件，图片，我们就要用到SQL的大对象LOB（Large OBject）。

CLOB: 字符数据的大对象
BLOB: 二进制数据的大对象

把大对象放入数据库中是非常低效和不现实地。解决方案是存储一个指向大对象的位置（文件路径）。

自定义类型：

CREATE TYPE Dollars AS NUMERIC(12, 2) FINAL;
CREATE TYPE Pounds AS NUMERIC(12, 2) FINAL;

此处的FINAL没有实际意义。

将Pounds类型的变量赋值给Dollars类型会引起错误。

可以对类型进行强制转化。把Pounds类型强制转为NUMERIC：

CREATE TABLE r (
    money Pounds
);

CAST (r.money TO NUMERIC(12, 2));

2.2创建表

通用形式是：

CREATE TABLE r (
    A1 D1,
    A2 D2,
    ...
    An Dn,
    <完整性约束1>,
    ...
    <完整性约束k>
);

其中Ai代表属性i的名字，Di代表属性i的类型。

下面都是一些可用的例子：

CREATE TABLE department (
    dept_name VARCHAR(20),
    building VARCHAR(15),
    budget numeric(12, 2),
    PRIMARY_KEY (dept_name)
);

CREATE TABLE course (
    course_id VARCHAR(7),
    title VARCHAR(50) NOT NULL,
    dept_name VARCHAR(20),
    credits numeric(2, 0)
    PRIMARY_KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department
);

CREATE TABLE instructor (
    ID VARCHAR(5),
    name VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    salary nuemric(8, 2),
    PRIMARY_KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department
);

CREATE TABLE teaches (
    ID VARCHAR(5),
    course_id VARCHAR(7),
    sec_id VARCHAR(8),
    semester VARCHAR(6),
    year INT,
    PRIMARY KEY (ID, course_id, sec_id, semester, year),
    FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section,
    FORERIGN KEY (ID) REFERENCES instructor
);

主键和外键可以是一个属性，也可以是多个属性。

默认值 DEFAULT

创建表的时候，我们可以给属性指定默认值。

CREATE TABLE student (
    ID INT AUTO INCREMENT,
    name VARCHAR(20) NOT NULL,
    score INT DEFAULT 0,
    PRIMARY KEY (ID)
)

AUTO INCREMENT的意思是，如果插入的时候没有指定值，则自动加1。每个数据库可能不一样。

复用表的模式

CREATE TABLE LIKE
CREATE TABLE AS WITH DATA;

我们有可能创建和另外一个一模一样的表（除表名外）：

CREATE TABLE student2 LIKE student;

上面例子中：表student2除表名外，和student一模一样，等价于使用了下面的语句：

CREATE TABLE student2 (
    ID INT ,
    name VARCHAR(20) NOT NULL,
    score INT DEFAULT 0,
    PRIMARY KEY (ID)
)

我们还可能从查询结果中创建表：

CREATE TABLE name_list AS (
    SELECT ID, name FROM student
    WHERE age > 10
) WITH DATA;

不同的数据库可能使用不通的方式：比如，CREATE TABLE AS而不是CREATE TABLE LIKE。请查阅具体的数据库。

3.1 查询

通用形式如下：


SELECT A1, A2,...,An
FROM r1, r2,...,rk
WHERE P;

其中：Ai是需要查询的属性。如果r_m和r_n中含有相同的属性：Ai。为了区分是哪个r中的Ai，可以用r_m.Ai或r_m.Ai来表示。意思是关系j中的Ai属性。ri是关系i。P是一个谓词或多个谓词的复合。比如（a==1 AND b >= 2)

一个例子：


SELECT name, instructor.dept_name
FROM instructor, department
WHERE instructor.dept_name = department.dept_name;

上面FROM子句中，instructor，department组合出的结果是instructor和department的笛卡尔积。

**SELECT * FROM **: 表示输出关系中所有的属性。

SELECT * from instructor;   # 输出instructor的所有属性

SELECT DISTINCT: 表示去除重复。作用在一个或多个属性上。

找出学生的年龄取值:

SELECT DISTINCT age FROM students;

相关子查询（correlated query)：使用了来自外层查询相关名称的子查询被称作相关子查询。一个子查询只能使用子查询本身定义的名称（属性，或者重命名的名称），或者包含此子查询的查询所定义的名称。

例子：找出在2008年秋和2009年春都开设的课程。具体意思请参考后面EXISTS。

SELECT course_id
FROM section as S
WHERE semester = 'Fall' AND year = 2008 
AND EXISTS (SELECT * 
                FROM section AS T
                WHERE semester = 'Spring' AND year = 2009
                AND S.course_id = T.course_id);

3.2 自然连接（natural join）

自然连接：运算作用于两个关系，并产生一个关系作为结果。自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。

通用形式如下：

SELECT A1, A2,...,An
FROM r1 NATURAL JOIN r2 ... NATURAL JOIN rm
WHERE P;

上节的一个例子用自然连接还可以这么表示:

SELECT name, instructor.dept_name
FROM instructor NATURAL JOIN department;

JOIN...USING

JOING...USING是NATURAL JOIN的一种放宽条件的JOIN: 允许部分具有相同属性名的值相同。
加入：A1,A2,A3是r1, r2都有的属性。NATURAL JOIN要求r1.A1 = r2.A1, r1.A2 = r2.A2, r1.A3 = r2.A3。而 r1 NATURAL JOIN r2 USING (A1) 表示只要r1.A1 = r2.A1即可。

注意：JOIN...USING不是NATURAL JOIN...USING. 实际上并没有NATURAL JOIN...USING。不要搞混了。

3.3 其他基本运算

AS: 重命名属性或者关系。

通用形式：old_name AS new_name。可以重命名属性，也可以重命名关系，还可以重命名临时查询的结果。

例子：重命名属性、重命名关系


SELECT name, age AS stud_age
FROM students as stud
WHERE stud.gender = 1 AND stud_age >= 20;

例子：重命名临时查询的结果

找出平均工资>42000的那些学院的平均工资。

SELECT department_name, avg_salary
FROM (SELECT dept_name, AVG(salary) 
    FROM instructor 
    GROUP BY dept_name) 
AS T(department_name, avg_salary)
WHERE avg_salary > 42000;

AS可以在一个查询中用来区分相同的集合。

比如：

SELECT name
FROM students, students AS T
WHERE age > T.age;

找出年龄不是最小的人。（有其他更好的表达方式，这里只是为了举例）

也可以对两个instructor关系都重命名，比如：

SELECT S.name
FROM students AS S, students AS T
WHERE S.age > T.age;

字符串运算

SQL使用一对单引号来表示字符串，例如：'Computer'。如果单引号也是字符串的组成部分，那么再用一个单引号来转义。比如it's good，转以后应该这样: 'it''s good'（最外围一对单引号表示字符串）.

在SQL标准中，字符串上的相等运算是大小写敏感的。比如'Computer' = 'computer'是false。然而，一些数据库系统可能不区分大小写。比如MySQL和SQL Server。此时'Computer' = 'computer'是true。这种匹配规则可以在数据库的配置中修改。

SQL还有一些其他函数，比如提取子串，计算字符串长度，大小写转换(upper, lower)。具体的函数请参考具体的数据库系统。

LIKE: 可以使用LIKE来实现模式匹配。我们使用两个特殊的字符来描述模式：

%（百分号）：匹配任意子串
_（下划线）：匹配任意字符

匹配模式都是大小写敏感的，比如：'%Intro' 匹配'Intro'开头的字符串。'%Intro%'匹配包含'Intro'的字符串。'__' 匹配长度为2的字符串。

例子：

SELECT name 
FROM students
WHERE name LIKE 'Lee %';    # 找出姓李的人

ORDER BY: 按属性排序。

SELECT * FROM students ORDER age;       # order by age. low -> old

ORDER BY默认是升序，实际的形式是ORDER BY attr1 ASC

我们也可以降序排列：ORDER BY attr1 DESC

例子：

SELECT * FROM students
ORDER BY age, name DESC;

WHERE: 子句谓词。

WHERE子句中，可以对属性进行比较: <, >, <=, >=, =, <>(不等于)。还可以在WHERE中添加布尔运算：AND(且）, OR（或）, NOT。还有一个特别运算符： BETWEEN AND(区间值，只能用于数值运算）。

SELECT * FROM students
WHERE age = 20 AND hometown = 'bj';

SELECT * FROM students
WHERE age BETWEEN 18 AND 20;    # age >= 18 AND age <= 20

SELECT * FROM students
WHERE age = 20 OR hometwon = 'bj';

分页: TOP/LIMIT

TOP子句是用来做分页的。但不是所有数据库都支持。MySQL对应的是LIMIT。下面简要说一下:

SELECT *
FROM r
WHERE P
#ORDER BY id #if any
LIMIT [offset], nrows

offset是相对于0的偏移。省略的话，就表示从0开始.

下面是几个例子:

#选取从**第0条**开始，总共5条数据:
SELECT * FROM students
LIMIT 0, 5;

# 和下面的等价
SELECT * FROM students
LIMIT 5;

#选取从第10条开始，总共20条数据
SELECT * FROM students
LIMIT 10, 20;

注意：由于本地数据有限。但据网上说，这个LIMIT用错的话，速度会很慢。要怎么用，请仔细搜一下。本文仅是介绍基本的语法，不考虑性能等问题。

3.4集合运算

SQL作用在关系上的union, intersect和except运算对应于数学集合论中的∪，∩，-（并集、交集、差集）。

Union

(SELECT course_id FROM section
WHERE semester = 'Fall' AND year = 2008)
UNION
(SELECT course_id FROM section
WHERE semester = 'Spring' AND year = 2009);

以上例子的意思是，找出section中，08年秋季和09年春节的课程id。

Union会自动去除重复的元组。如果我们想保留重复的元组，那么可以使用UNION ALL：

(SELECT course_id FROM section
WHERE semester = 'Fall' AND year = 2008)
UNION ALL
(SELECT course_id FROM section
WHERE semester = 'Spring' AND year = 2009);

INTERSECT

(SELECT course_id FROM section
WHERE semester = 'Fall' AND year = 2008)
INTERSECT
(SELECT course_id FROM section
WHERE semester = 'Spring' AND year = 2009);

以上例子的意思是，找出section中，同时出现在08年秋季和09年春节的课程id。

同样INTERSECT自动去除重复。如果要保留重复的元组，可以使用INTERSECT ALL 。

EXCEPT

EXCEPT：在第一个输出中，去除包含在第二个输出的元组。

(SELECT course_id FROM section
WHERE semester = 'Fall' AND year = 2008)
EXCEPT
(SELECT course_id FROM section
WHERE semester = 'Spring' AND year = 2009);

以上例子的意思是，找出section中，出现在08年秋季且09年春没出现的课程id。

EXCEPT也自动去除重复。为了保留重复，可以使用EXCEPT ALL。

3.5空值（NULL)

空值给关系运算带来了一系列的问题，包括算术运算、比较运算和集合运算。

比如 1 < NULL是true还是false？由于不知道NULL代表的是什么，所以上述比较是没有意义的，有可能是真，也有可能是假。所以SQL将涉及空值的任何比较运算的结果视为UNKNOWN（既不是IS NULL也不是IS NOT NULL)。

比如：0 = NULL, NULL = NULL的结果都是UKNOWN。

UNKNOWN引入了true和false之外的第三个逻辑值。WHERE子句（谓词）也应该能够处理UNKNOWN。

AND: TRUE AND UNKNOWN结果是UNKNOWN，FALSE AND UNKNOWN结果是FALSE, UNKNOWN AND UNKNOWN的结果是UNKNOWN。
OR: TRUE OR UNKNOWN的结果是UNKNOWN，FALSE OR UNKNOWN的结果是UNKNOWN，UNKNOWN OR UNKNOWN的结果是UNKNOWN。
NOT: NOT UNKNOWN的结果是UNKNOWN。

可以在谓词中测试是否是NULL, IS NULL, IS NOT NULL:

SELECT * FROM students
WHERE hometown IS NOT NULL;

例外：

使用SELECT DISTINCT的时候，为了达到去除重复元素的目的。当比较两个元组对应的属性值时，如果这两个值都是非空并且值相等，或者都是空，则它们是相同的。也就是，('A', NULL) 和（'A', NULL)是被认为相同的，而不是unknown。可以这么理解：'A' = 'A' AND (NULL IS NULL = NULL IS NULL)。即，用IS NULL来判断值是否都为空。

3.6 聚集函数（aggregate function）

3.6.1 聚集函数

聚集函数是以值的一个集合（或者多个）为输入，返回单个函数值的函数。SQL提供了五个聚集函数：

avg: 平均值
min：最小值
max：最大值
sum：总和
count：计数

直接上例子：

SELECT AVG(age) FROM students;  # 返回平均年龄

SELECT MIN(age) FROM students;  # 返回最小年龄

SELECT MAX(age) FROM students;  # 返回最大年龄

SELECT SUM(age) FROM students;  # 所有年龄加起来。此处无现实意义

SELECT COUNT(*) FROM students;  # 返回学生人数

SELECT COUNT(DISTINCT first_name) FROM students; # 找出学生姓氏的个数。学生可能同姓不同名

聚集函数不能出现在WHERE中。只能出现在SELECT, HAVING或者子查询中。

下面这种是错误的：给工资低于平均值且属于Biology学院的教师，加薪500:

#下面这种是错误的。聚集函数不能出现在WHERE中。只能出现在SELECT, HAVING或者子查询中。
UPDATE instructor
SET salary = salary + 500
WHERE dept_name = 'Biology' AND salary < AVG(salary);

3.6.2 分组聚集 GROUP BY

有时候我们不仅希望将函数作用在单个元组集(单个relation）上，而且也希望将其作用到一组元素集（单个relation的不同部分）上。可以使用GROUP BY 来达到。

比如：找出各学院平均薪资。

SELECT dept_name, AVG(salary) AS av_salary
FROM instructor
GROUP BY dept_name;

GROUP BY分组如下：

group_by

最后结果如下：

group_by_result

使用GROUP BY的时候，十分容易出错的是，没有使用聚集函数的那些属性，一定要出现在GROUP BY子句中。

比如，下面的语句就是错的，ID没有使用聚集函数，也没有出现在GROUP BY子句中。

SELECT ID, dept_name, AVG(salary) AS av_salary
FROM instructor
GROUP BY dept_name;

3.6.3 HAVING子句

有时候，对分组限定条件比对元组限定条件更有用。例如，我们对平均工资超过42000的学院更感兴趣。该条件不针对单个元组，而是针对GROUP BY子句构成的分组。此时用HAVING就能达到目的。

HAVING修饰的是GROUP BY起作用后形成的分组。（对分组进行筛选）

一个例子：找出平均工资总额>42000的学院。

SELECT dept_name, AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name;
HAVING AVG(salary) > 42000;

结果如下：

having

使用HAVING十分容易出错的是，没有使用聚集函数的那些属性，如果属性出现在HAVING子句中，也必须出现在GROUP BY子句中。

包含聚集、GROUP BY或HAVING子句的查询的含义可以通过下面的操作来定义：

与不带聚集的查询情况类似，最先根据FROM子句来计算出一个关系
如果出现了WHERE子句，WHERE子句中的谓词将应用到FROM子句的结果关系上
如果出现了GROUP BY子句，满足WHERE谓词的元组将通过GROUP BY子句形成分组。如果没有GROUP BY子句，满足WHERE谓词的整个元组集被当作一个分组
如果出现HAVING子句，它将应用到每个分组上；不满足HAVING子句谓词的分组将被丢弃
SELECT子句利用剩下的分组产生出查询结果中的元组，即在每个分组上应用聚集函数来得到单个结果元组。

考虑如下一个查询：

统计各省姓氏平均身高（人数>1)：对于整个中国国籍，如果来自同一个省份且姓氏姓氏相同的人数大于1，则计算出该姓氏的人的身高。（考虑有人居住在中国，但国籍是其他国家）。

SELECT first_name, province, AVG(height)
FROM people_info
WHERE citizenship='CHINA'
GROUP BY first_name, province
HAVING COUNT(id) > 1;   # 假设id是每个有中国国籍和居住在中国的外籍人的唯一标识。类似身份证号码。但外籍人没有身份证。

上面的例子中，first_name, province没有被聚集函数使用，所以必须出现在GROUP BY中。对于HAVING子句中的元素，id被聚集函数count修饰了，所以可以不用出现在GROUP BY子句中。如果id没有被count修饰，那么必须出现在GROUP BY子句中。

3.6.4 对NULL和布尔值的聚集

聚集函数根据以下原则处理空值：除了COUNT(*)以外所有的聚集函数都忽略输入集合中的空值。由于空值被忽略，有可能造成参加函数运算的输入值的集合为空集。规定空集的COUNT运算值为0。其他所有聚集函数在输入为空集的情况下返回一个空值。

布尔值的聚集：SQL 1999引入了布尔数据类型，有三种取值：TRUE, FALSE, UNKNOWN。对应的聚集函数：SOME, EVERY。具体用法请参考手册。

3.7 嵌套子查询

SQL提供嵌套子查询机制。子查询是嵌套在另一个查询中的SELECT-FROM-WHERE表达式。如果子查询嵌套在WHERE子句中，通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。嵌套子查询也可以出现在FROM当中，还可以出现在任何地方（只要该查询只返回一个元组且该元组只有一个属性）。

3.7.1 集合成员资格 IN, NOT IN

SELECT course_id FROM section
WHERE semester = 'Spring' AND year = 2009
AND course_id IN        # IN 可以替换成NOT IN，表示不在后面这个集合里面
(SELECT course_id FROM section WHERE semester = 'Fall' AND year = 2008);

先找出2008求开学的课程，再从里面找出2009春也会开的课程。

上面个查询也可以不用子查询：（注意对AS的用法）

SELECT DISTINCT course_id
FROM section, section AS section2
WHERE semester = 'Spring' AND year = 2009 AND section.course_id = section2.course_id AND section2.semester = 'Fall' AND section2.year = 2008;

3.7.2 集合的比较 SOME, ALL

SQL允许对SOME和ALL进行比较：<SOME, <= SOME, >SOME, >=SOME, =SOME和<>SOME。ALL也一样，可以有，<ALL, <=ALL, >ALL, >= ALL, =ALL, <>ALL

考虑如下一个查询：找出满足下面条件的所有老师的姓名，他们的工资至少比Biology学院某一个教师的工资高。

SELECT name 
FROM instructor
WHERE salary > SOME(SELECT salary
                        FROM instructor
                        WHERE dept_name = 'Biology');

不用SOME的查询：

SELECT DISTINCT name
FROM instructor, instructor AS T
WHERE salary > T.salry;

SELECT name
FROM instructor
WHERE salary > (SELECT MIN(salary) 
                        FROM instructor 
                        WHERE dept_name = 'Biology');

稍微修改一下条件：找出所有老师的姓名，他们的工资比每一个Biology学院老师的工资高。

SELECT name
FROM instructor
WHERE salary > ALL(SELECT salary
                        FROM instructor
                        WHERE dept_name = 'Biology');

=SOME 等价于IN。<>SOME不等价于NOT IN

<>ALL等价于NOT IN. =ALL不等价于IN

3.7.3 空关系测试 EXISTS，NOT EXISTS

找出2008年秋和2009年春都开设的课程。

SELECT course_id
FROM section as S
WHERE semester = 'Fall' AND year = 2008 
AND EXISTS (SELECT * 
                FROM section AS T
                WHERE semester = 'Spring' AND year = 2009
                AND S.course_id = T.course_id);

如果要找出2008年秋开设但2009年春不开设的课程。只需要把上面的EXISTS替换成NOT EXISTS就行。

3.7.4 FROM子句中的子查询

注意：FROM中的子查询只能使用该子查询的名称。

例子：找出平均工资>42000的那些学院的平均工资。

SELECT dept_name, avg_salary
FROM (SELECT dept_name, AVG(salary) AS avg_salary
    FROM instructor 
    GROUP BY dept_name) 
WHERE avg_salary > 42000;

#不使用嵌套查询：
SELECT dept_name, AVG(salary) AS avg_salary
FROM instructory
GROUP BY dept_name
WHERE AVG(salary) > 42000;

SELECT-WHERE中的属性必须被是子查询SELECT的属性或子查询SELECT属性的一部分。

可以重名子查询的临时结果：

SELECT department_name, avg_salary
FROM (SELECT dept_name, AVG(salary) 
        FROM instructor 
        GROUP BY dept_name) 
AS T(department_name, avg_salary)
WHERE avg_salary > 42000;       #department_name，avg_salary都没有使用聚集函数，所以不用出现在GROUP BY中。

这里使用了AS重命名子查询的临时结果。外围的SELECT属性，必须是重名后的属性。

注意：并非所有SQL实现都支持FROM中嵌套子查询。某些SQL实现还要求所有子查询结果都要有一个名字（AS)。也有的SQL实现允许对子查询结果重命名，但不允许对关系中的属性重命名。

找出工资总额最大的学院：

SELECT dept_name, max(tot_salary)
FROM (SELECT dept_name, sum(salary) AS tot_salary 
    FROM instructor)
GROUP BY dept_name;     # tot_salary 使用了聚集函数，而dept_name没有使用聚集函数，所以dept_name必须出现在GROUP BY中。

3.7.5 WITH子句

WITH子句提供定义临时关系的方法。

考虑下面的查询，找出具有最大预算的系：

WITH max_budget(value) AS
(SELECT MAX(budget)
FROM department)
SELECT budget
FROM department, max_budget # 此处不要忘了max_budget
WHERE budget = max_budget.value;

WITH也可以定义多个临时关系：

找出工资总额大于平均工资总额的学院。

WITH tot_budget(dept_name, value) AS
        (SELECT dept_name, SUM(salary)
        FROM department
        GROUP BY dept_name),
    avg_budget(value) AS
        (SELECT AVG(salary)
        FROM department)
SELECT dept_name, tot_budget.value
FROM tot_budget, avg_budget
WHERE tot_budget.value > avg_budget.value

3.7.6 标量子查询（scalar subquery）

标量子查询：子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回单个元组且该元组只包含一个属性。

找出各学院老师的人数：

SELECT dept_name, (SELECT COUNT(*) FROM instructor
                        WHERE department.dept_name = instructor.dept_name) 
                        AS num_instructors
FROM department;

4.数据库的修改

对数据库的修改，必须满足完整性约束。

4.1 删除

只能删除某些元组，而不能删除某些元组的某些属性。

通用形式

DELETE FROM r 
WHERE P;

例子：删除该退休的教师：

DELETE FROM instructor
WHERE age > 60;

当没有WHERE子句的时候，即是删除关系中的所有元组（并不是删除该表。表还存在，只是没有元组）：

DELETE FROM r;

4.2 插入

两种方式：不指定插入的元组；指定需要插入的元组。

不指定插入的元组

#插入单个元组
INSERT INTO r VALUES(a1, a2, ..., an);  #ai属性的顺序必须与r中属性的顺序相同。不可以省略属性

#插入多个元组
INSERT INTO r VALUES(a1, a2, ..., an), (b1, b2, ..., bn), (c1, c2, ..., cn);

例子：

INSERT INTO instructor
VALUES ('robert', 20, 'Physics', ....);

指定插入元组：

#插入单个元组
INSERT INTO r(Ai, Aj, ..., Ak) VALUES(ai, aj, ..., ak); #指定的顺序可以与r中属性的顺序不同。在满足完整性约束下，可以省略部分属性

#插入多个元组
INSERT INTO r(Ai, Aj, ..., Ak) VALUES(ai, aj, ..., ak), (bi, bj, ..., bk), (ci, cj, ..., ck);

例子：

INSERT INTO instructor(name, age, salary, department)
VALUES ('robert', 20, 4000, 'Physics');

4.3 更新

改变元组的部分或全部属性。

通用形式：

UPDATE r
SET Ai = ai, Aj = aj, ... Ak = ak
WHERE P;

如果没有谓词P，则代表修改r中的所有元组。

例子：给工资低于3000的教师，全部涨薪500：

UPDATE instructor
SET salary = salary + 500
WHERE salary < 3000;

和前面一样，谓词P也可以嵌套查询。

比如，给工资低于平均值且属于Biology学院的教师，加薪500:

UPDATE instructor
SET salary = salary + 500
WHERE dept_name = 'Biology' AND salary < (SELECT AVG(salary) FROM instructor);

#下面这种是错误的。聚集函数不能出现在WHERE中。只能出现在SELECT, HAVING或者子查询中。
UPDATE instructor
SET salary = salary + 500
WHERE dept_name = 'Biology' AND salary < AVG(salary);

CASE结构

通用形式：

CASE 
    WHEN p1 then result1
    WHEN p2 then result2
    ...
    WHEN pi then resulti
    ELSE result(i+1)
END

比如给工资低于3000的人加薪10%，低于4000的人加薪5%：

UPDATE instructor
SET salary = CASE 
                    WHEN salary < 3000 then salary * 10%
                    WHEN salary < 4000 then salary * 5%
                    ELSE salary
                END
WHERE salary < 4000;

CASE结果也可以用于标量子查询中。

比如：如果一个学生的成绩，即不为NULL，也不为'F'那么这个学生便学完了这门课程。

不用case可以这么查询：

UPDATE student AS S
SET tot_cred = (SELECT sum(credits)
                        FROM takes NATURAL JOIN course
                        WHERE S.ID = takes.ID 
                        AND takes.grade IS NOT NULL 
                        AND takes.grade <> 'F');

注意：如果学生成绩为空，或者为'F'的话，上面的语句会把该学生的成绩设置成NULL。

使用case可以这么查询：

UPDATE student
SET tot_cred = (SELECT CASE
                            WHEN SUM(credits) IS NOT NULL THEN SUM(creidts)
                            ELSE 0
                    END
                for);

注意：上面的语句会把成绩为空或者'F'的学生的成绩设置为0.

数据库基础Database1-初级SQL