数据库管理系统(Database Management System, RDMS)由一个互相关联的数据的集合和一组用以访问这些数据的程序组成。这个数据集合通常称作数据库(database)。
将数据存储在文件中有以下弊端:
特定时刻存储在数据库中的信息的集合称作数据库的一个实例(instance)。数据库的总体设计称作数据库模式(schema)。
数据抽象
view_level
数据库可分为物理层(physical level),逻辑层(logical level),视图层(view level)
数据模型可划分为四类:
数据库操纵语言(Data-Manipulation Language, DML)是这样一种语言:它使得用户可以访问和操作按照适当的数据模型组织起来的数据。有以下访问类型:(CRUD, 增删查改)
通常有两类基本的数据操作语言(DML):
查询(Query)是要求对信息进行检索的语句。DML中设计信息检索的部分称作查询语言。
数据库定义语言(Data-Definition Language,DDL) : 定义数据库模式的一系列语言。
在关系模型的术语中,关系(relation)用来指代表(table),而元组(tuple)用来指代行(row),属性(attribute)指代的是表中的列(column)。
对于关系的每个属性,都存在一个允许取值的集合,成为该属性的域(domain)。
数据库模式(database schema)是数据库的逻辑设计。数据库实例(database instance)是给定时刻数据库中数据的一个快照。
模式图(schema diagram):数据库模式的图形化表示。
关系查询语言(relational query language):定义了一组运算集合,这些运算可以运作在表上,并输出表作为关系结果。这些运算也可以组合成表达式,表达所需的查询。
空值(Null):表达未知或不存在。
参照性完整约束(referentianl integrity constraint):参照性完整约束要求在参照关系中任意元组在特定属性上的取值必然等于被参照关系中某个元组在特定属性上的取值。(r1任意元组在外键上任意取值,必然能在r2上对应的属性找到相同的取值)
relation_operations
选择(selection):返回输入关系中满足谓词的行。(图中是返回sarary >= 85000的行)
投影(projection):对输入关系中所有的行指定需要输出哪些属性。
自然连接(natural join):从两个输入关系中输出这样的元组对:它们在具有相同属性名字的属性上取值相等。
笛卡尔积(cartesian product):从两个输入关系中输出所有元组对。不管它们是否具有相同的属性,也不管相同属性的取值是否相等。
SQL查询语言有以下几个部分:
基本类型:
日期类型:
大对象类型(BIG OBJECT)
当数据库需要存储很大(KB级)的属性,比如类文件,图片,我们就要用到SQL的大对象LOB(Large OBject)。
把大对象放入数据库中是非常低效和不现实地。解决方案是存储一个指向大对象的位置(文件路径)。
自定义类型:
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));
通用形式是:
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
);
主键和外键可以是一个属性,也可以是多个属性。
创建表的时候,我们可以给属性指定默认值。
CREATE TABLE student (
ID INT AUTO INCREMENT,
name VARCHAR(20) NOT NULL,
score INT DEFAULT 0,
PRIMARY KEY (ID)
)
AUTO INCREMENT
的意思是,如果插入的时候没有指定值,则自动加1。每个数据库可能不一样。
我们有可能创建和另外一个一模一样的表(除表名外):
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。请查阅具体的数据库。
通用形式如下:
SELECT A1, A2,...,An
FROM r1, r2,...,rk
WHERE P;
其中:Ai是需要查询的属性。如果rm和rn中含有相同的属性:Ai。为了区分是哪个r中的Ai,可以用rm.Ai或rm.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);
自然连接:运算作用于两个关系,并产生一个关系作为结果。自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。
通用形式如下:
SELECT A1, A2,...,An
FROM r1 NATURAL JOIN r2 ... NATURAL JOIN rm
WHERE P;
上节的一个例子用自然连接还可以这么表示:
SELECT name, instructor.dept_name
FROM instructor NATURAL JOIN department;
JOING&#8230;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&#8230;USING不是NATURAL JOIN&#8230;USING. 实际上并没有NATURAL JOIN&#8230;USING。不要搞混了。
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使用一对单引号来表示字符串,例如:&#8217;Computer&#8217;。如果单引号也是字符串的组成部分,那么再用一个单引号来转义。比如it&#8217;s good,转以后应该这样: &#8216;it&#8221;s good&#8217;(最外围一对单引号表示字符串).
在SQL标准中,字符串上的相等运算是大小写敏感的。比如&#8217;Computer&#8217; = &#8216;computer&#8217;是false。然而,一些数据库系统可能不区分大小写。比如MySQL和SQL Server。此时&#8217;Computer&#8217; = &#8216;computer&#8217;是true。这种匹配规则可以在数据库的配置中修改。
SQL还有一些其他函数,比如提取子串,计算字符串长度,大小写转换(upper, lower)。具体的函数请参考具体的数据库系统。
LIKE: 可以使用LIKE来实现模式匹配。我们使用两个特殊的字符来描述模式:
匹配模式都是大小写敏感的,比如:&#8217;%Intro&#8217; 匹配&#8217;Intro&#8217;开头的字符串。&#8217;%Intro%&#8217;匹配包含&#8217;Intro&#8217;的字符串。&#8217;__&#8217; 匹配长度为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用错的话,速度会很慢。要怎么用,请仔细搜一下。本文仅是介绍基本的语法,不考虑性能等问题。
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。
空值给关系运算带来了一系列的问题,包括算术运算、比较运算和集合运算。
比如 1
比如:0 = NULL, NULL = NULL的结果都是UKNOWN。
UNKNOWN引入了true和false之外的第三个逻辑值。WHERE子句(谓词)也应该能够处理UNKNOWN。
可以在谓词中测试是否是NULL, IS NULL, IS NOT NULL:
SELECT * FROM students
WHERE hometown IS NOT NULL;
例外:
使用SELECT DISTINCT的时候,为了达到去除重复元素的目的。当比较两个元组对应的属性值时,如果这两个值都是非空并且值相等,或者都是空,则它们是相同的。也就是,(&#8216;A&#8217;, NULL) 和 (&#8217;A&#8217;, NULL)是被认为相同的,而不是unknown。可以这么理解:&#8217;A&#8217; = &#8216;A&#8217; AND (NULL IS NULL = NULL IS NULL)。即,用IS NULL来判断值是否都为空。
聚集函数是以值的一个集合(或者多个)为输入,返回单个函数值的函数。SQL提供了五个聚集函数:
直接上例子:
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
有时候我们不仅希望将函数作用在单个元组集(单个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;
有时候,对分组限定条件比对元组限定条件更有用。例如,我们对平均工资超过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子句的查询的含义可以通过下面的操作来定义:
考虑如下一个查询:
统计各省姓氏平均身高(人数>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子句中。
聚集函数根据以下原则处理空值:除了COUNT(*)以外所有的聚集函数都忽略输入集合中的空值。由于空值被忽略,有可能造成参加函数运算的输入值的集合为空集。规定空集的COUNT运算值为0。其他所有聚集函数在输入为空集的情况下返回一个空值。
布尔值的聚集:SQL 1999引入了布尔数据类型,有三种取值:TRUE, FALSE, UNKNOWN。对应的聚集函数:SOME, EVERY。具体用法请参考手册。
SQL提供嵌套子查询机制。子查询是嵌套在另一个查询中的SELECT-FROM-WHERE表达式。如果子查询嵌套在WHERE子句中,通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。嵌套子查询也可以出现在FROM当中,还可以出现在任何地方(只要该查询只返回一个元组且该元组只有一个属性)。
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;
SQL允许对SOME和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
找出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就行。
注意: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中。
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
标量子查询:子查询出现在返回单个值的表达式能够出现的任何地方,只要该子查询只返回单个元组且该元组只包含一个属性。
找出各学院老师的人数:
SELECT dept_name, (SELECT COUNT(*) FROM instructor
WHERE department.dept_name = instructor.dept_name)
AS num_instructors
FROM department;
对数据库的修改,必须满足完整性约束。
只能删除某些元组,而不能删除某些元组的某些属性。
通用形式
DELETE FROM r
WHERE P;
例子: 删除该退休的教师:
DELETE FROM instructor
WHERE age > 60;
当没有WHERE子句的时候,即是删除关系中的所有元组(并不是删除该表。表还存在,只是没有元组):
DELETE FROM r;
两种方式:不指定插入的元组;指定需要插入的元组。
不指定插入的元组
#插入单个元组
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');
改变元组的部分或全部属性。
通用形式:
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
通用形式:
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,也不为&#8217;F&#8217;那么这个学生便学完了这门课程。
不用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');
注意:如果学生成绩为空,或者为&#8217;F&#8217;的话,上面的语句会把该学生的成绩设置成NULL。
使用case可以这么查询:
UPDATE student
SET tot_cred = (SELECT CASE
WHEN SUM(credits) IS NOT NULL THEN SUM(creidts)
ELSE 0
END
for);
注意:上面的语句会把成绩为空或者&#8217;F&#8217;的学生的成绩设置为0.