3. Oracle学习笔记-SQL语言基础

SQL语言基础

  graph LR
SQL语言基础-->SQL概述
SQL语言基础-->用户模式
SQL语言基础-->数据定义
SQL语言基础-->数据查询
SQL语言基础-->数据更新
SQL语言基础-->空值的处理

1 `SQL` 概述

SQL（Structured Query Language）: 结构化查询语言, 是关系数据库的标准语言

SQL是一个通用的、功能极强的关系数据库语言

1.1 SQL 的特点

1.1.1 综合统一

集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。
可以独立完成数据库生命周期中的全部活动：
- 定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库;
- 对数据库中的数据进行查询和更新;
- 数据库重构和维护
- 数据库安全性、完整性控制，以及事务控制
- 嵌入式SQL和动态SQL定义
用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据库的运行。
数据操作符统一

1.1.2 高度非过程化

非关系数据模型的数据操纵语言“面向过程”，必须指定存取路径。
SQL只要提出“做什么”，无须了解存取路径。
存取路径的选择以及SQL的操作过程由系统自动完成。

1.1.3 面向集合的操作方式

非关系数据模型采用面向记录的操作方式，操作对象是一条记录；
SQL采用集合操作方式操作对象、查找结果可以是元组的集合，一次插入、删除、更新操作的对象可以是元组的集合

1.1.4 以同一种语法结构提供多种使用方式

SQL是独立的语言，能够独立地用于联机交互的使用方式；
SQL又是嵌入式语言SQL，能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用

1.1.5 语言简介，易学易用

SQL功能极强，完成核心功能只用了9个动词。

SQL功能	动词
数据查询	SELECT
数据定义	CREATE, DROP, ALTER
数据操作	INSERT, UPDATE, DELETE
数据控制	GRANT, REVOKE

1.2 `SQL` 的基本概念

SQL 支持关系数据库三级模式结构

1.2.1 基本表

本身独立存在的表
SQL中一个关系就对应一个基本表
一个（或多个）基本表对应一个存储文件
一个表可以带若干索引

1.2.2 存储文件

逻辑结构组成了关系数据库的内模式
物理结构对用户是隐蔽的

1.2.3 视图

从一个或几个基本表导出的表
数据库中只存放视图的定义而不存放视图对应的数据
视图是一个虚表
用户可以在视图上再定义视图

2 用户模式

在oracle数据库中，数据对象是以模式为单位进行组织和管理的。模式是指一系列的逻辑数据结构或对象的集合。
模式与用户名相对应，一个模式只能对应一个用户，并且该模式名称与用户的名称相同，在一般的情况下，用户所创建的数据库对象都存储在同名的模式下。

在同一模式中数据对象的名称必须唯一。不同模式中的数据对象可以相同。

例如：用户user1在数据库中创建test表对象，则test表放在user1模式中。用户user2创建test表对象，则放在user2模式中。
在默认的情况下，用户引用对象是引用同名模式中的对象，如想要引用其他模式中的对象，则必须在引用对象的前面加上对象所属模式

例如：用户 user1下有test表，用户user2下有test表，默认情况下用户user1访问test表，直接select * from test，但是如要访问user2下的test 则 select * from user2.test;

3 数据定义

3.1 定义基本表

CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
[,<列名> <数据类型>[ <列级完整性约束条件>]] …
[,<表级完整性约束条件>] );

<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件
如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可
以定义在表级。

-- 建立“学生”表Student。学号是主码，姓名取值唯一。
CREATE TABLE student(
 sno char(9) PRIMARY KEY,
 sname char(20) UNIQUE,
 ssex char(2),
 sage SMALLINT,
 sdept char(20)
);
-- 建立一个“课程”表Course
CREATE TABLE course(
 cno char(4) PRIMARY KEY,
 cname char(40),
 cpno char(4),
 ccredit SMALLINT,
 FOREIGN key(cpno) REFERENCES course(cno)
);
-- 建立一个学生选课表SC
CREATE TABLE sc(
 sno char(9),
 cno char(4),
 grade SMALLINT,
 PRIMARY KEY(sno,cno),
 FOREIGN KEY (sno) REFERENCES student(sno),
 FOREIGN KEY (cno) REFERENCES course(cno)
);

3.2 Oracle中的数据类型

数据类型	含义
CHAR(n), CHARACTER(N)	长度为n的定长字符串
VARCHAR(n), CHARACTERVARYING(n)	最大长度为n的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT, INTEGER	长整数(4字节)
SMALLINT	短整数(2字节)
BIGINT	大整数（8字节)
NUMERIC(p, d)	定点数，由p位数字(不包括符号、小数点)组成,小数后面有d位数字
DECIMAL(p, d), DEC(p, d)	同NUMERIC
REAL	取决于机器精度的单精度浮点数
DOUBLE PRECISION	取决于机器精度的双精度浮点数
FLOAT(n)	可选精度的浮点数,精度至少为n位数字
BOOLEAN	逻辑布尔量
DATE	日期，包含年、月、日，格式为YYYY-MM-DD
TIME	时间，包含一日的时、分、秒，格式为HH:MM:SS
TIMESTAMP	时间戳类型
INTERVAL	时间间隔类型

3.3 修改基本表

ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ;

-- 向Student表增加“入学时间”列，其数据类型为日期型
ALTER TABLE student ADD s_entrance DATE;

-- 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。
ALTER TABLE student modify sage int;

-- 增加课程名称必须取唯一值的约束条件。
ALTER TABLE course ADD unique(cname)

4 数据查询

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句) [AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];

SELECT子句：指定要显示的属性列.
FROM子句：指定查询对象（基本表或视图）.
WHERE子句：指定查询条件.
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数.
HAVING短语：只有满足指定条件的组才予以输出.
ORDER BY子句：对查询结果表按指定列值的升序或降序排序.

4.1 单表查询

4.1.1 选择表中的若干列

查询指定列

-- 查询全体学生的学号与姓名。
SELECT sno,sname FROM student

-- 查询全体学生的姓名、学号、所在系。
SELECT sname,sno,sdept FROM STUDENT

查询全部列

选出所有属性列：在SELECT关键字后面列出所有列名或将<目标列表达式>指定为 *
1
2
-- 查询全体学生的详细记录
SELECT sno,sname,ssex,sage,sdept FROM STUDENT

查询经过计算的值, 同时可以使用列别名更改输出列名

-- 查全体学生的姓名及其出生年份。
SELECT sname,2023-sage FROM student
-- 查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。
SELECT sname 姓名,2023-sage as 出生年份,LOWER(sdept) as 所在院系 FROM STUDENT

4.1.2 选择表中的若干元组

消除取值重复的行，如果没有指定DISTINCT关键词，则缺省为ALL
1
2
-- 查询选修了课程的学生学号。
SELECT DISTINCT sno FROM sc
查询满足条件的元组

常用的查询条件如下表

查询条件	谓词
比较	=, >, <, >=, <=, !=, <>, !>, !<; NOT+上述运算符
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE
空值	IS NULL, IS NOT NULL
多重条件(逻辑运算)	AND, OR, NOT

比较大小

-- 查询计算机科学系全体学生的名单。
SELECT * FROM student WHERE sdept='CS'
-- 查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT sname,sage FROM  student WHERE sage<20
-- 查询考试成绩有不及格的学生的学号。
SELECT DISTINCT  sno FROM sc WHERE grade>=60

确定范围

谓词: BETWEEN … AND … 和 NOT BETWEEN … AND …

-- 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
SELECT sname,sdept,sage FROM student WHERE sage>=20 AND sage<=23
SELECT sname,sdept,sage FROM student WHERE sage BETWEEN 20 AND 23
-- 查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT sname,sdept,sage FROM student WHERE sage not BETWEEN 20 AND 23
SELECT sname,sdept,sage FROM student WHERE sage<20 OR sage>23

确定集合

谓词 : IN <值表>, NOT IN <值表>

-- 查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。
SELECT * FROM student WHERE sdept = 'CS' OR sdept='MA' OR sdept = 'IS'
SELECT * FROM student WHERE sdept IN ('CS','MA','IS')
-- 查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。
SELECT * FROM student WHERE sdept != 'CS' AND  sdept!='MA' AND sdept != 'IS'
SELECT * FROM student WHERE sdept NOT IN ('CS','MA','IS')

字符匹配

谓词 : [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]

<匹配串>可以是一个完整的字符串，也可以含有通配符%和_ ;
% （百分号）代表任意长度（长度可以为0）的字符串
例如a%b表示以a开头，以b结尾的任意长度的字符串
_ （下横线）代表任意单个字符。
例如a_b表示以a开头，以b结尾的长度为3的任意字符串
- 匹配串为固定字符串
  1
  2
  -- 查询学号为201215121的学生的详细情况。
  SELECT * FROM student WHERE sno LIKE '201215121'
- 匹配串为含通配符的字符串
  1
  2
  -- 查询所有姓刘学生的姓名、学号和性别。
  SELECT * FROM student WHERE sname LIKE '刘_'
- 使用换码字符可以将通配符转义为普通字符
  1
  2
  3
  -- 查询DB_Design课程的课程号和学分。
  SELECT Cno，Ccredit FROM Course
  WHERE Cname LIKE 'DB\_Design' ESCAPE '\ ' ;

涉及空值的查询

谓词 : IS NULL 或 IS NOT NULL

“IS” 不能用 “=” 代替

-- 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。
-- 查询缺少成绩的学生的学号和相应的课程号。
SELECT sno,cno FROM sc
WHERE grade IS NULL
-- 查所有有成绩的学生学号和课程号。
SELECT sno,cno FROM sc WHERE grade IS NOT NULL

多重条件查询

逻辑运算符：使用`AND 和 OR 来连接多个查询条件
AND 的优先级高于 OR, 还可以用括号改变优先级
1
2
-- 查询计算机系年龄在20岁以下的学生姓名。
SELECT * FROM STUDENT WHERE sdept='CS' AND sage<20

4.1.3 `ORDER BY` 子句

可以按一个或多个属性列排序;
升序：ASC; 降序：DESC ; 缺省值为升序;
对于空值，排序时显示的次序由具体系统实现来决定

-- [例3.39]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
SELECT sno,grade FROM SC WHERE cno='3' ORDER BY grade DESC
-- [例3.40]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
SELECT * FROM STUDENT ORDER BY sdept,sage DESC

4.1.4 聚集函数

函数	作用
COUNT(*)	统计元组个数
COUNT([DISTINCT\|ALL] <列名>)	统计一列中值的个数
SUM([DISTINCT\|ALL] <列名>)	计算一列值的总和（此列必须为数值型）
AVG([DISTINCT\|ALL] <列名>)	计算一列值的平均值（此列必须为数值型）
MAX([DISTINCT\|ALL] <列名>) MIN([DISTINCT\|ALL] <列名>)	求一列中的最大值和最小值

-- 查询学生总人数。
SELECT count(*) FROM student
-- 查询选修了课程的学生人数。
SELECT count(distinct sno) FROM sc
-- 计算3号课程的学生平均成绩。
SELECT AVG(grade) FROM sc WHERE cno='3'
-- 查询选修3号课程的学生最高分数。
SELECT max(grade) FROM sc WHERE cno='3'
-- 查询学生201215125选修课程的总学分数。
SELECT sum(course.CCREDIT)  FROM sc,COURSE 
WHERE sc.cno=course.cno AND sc.sno='201215125'

4.1.5 `GROUP BY` 子句

细化聚集函数的作用对象

如果未对查询结果分组，聚集函数将作用于整个查询结果
对查询结果分组后，聚集函数将分别作用于每个组
按指定的一列或多列值分组，值相等的为一组

-- 求各个课程号及相应的选课人数。
SELECT cno,count(sno) FROM sc group BY cno
-- 查询选修了2门以上课程的学生学号。
SELECT sno FROM sc  group BY sno HAVING count(*)>=2
-- 查询平均成绩大于等于90分的学生学号和平均成绩
SELECT sno,avg(grade) FROM sc GROUP BY sno HAVING avg(grade)>=90

HAVING短语与WHERE子句的区别：作用对象不同
WHERE子句作用于基表或视图，从中选择满足条件的元组
HAVING短语作用于组，从中选择满足条件的组。

4.2 连接查询

连接查询：同时涉及两个以上的表的查询，
连接条件或连接谓词：用来连接两个表的条件，一般格式: [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3> ;
连接字段：连接谓词中的列名称;

连接条件中的各连接字段类型必须是可比的，但名字不必相同;

4.2.1 等值连接查询

等值连接：连接运算符为=

1 2	-- 查询每个学生及其选修课程的情况 SELECT stu.sno,stu.sname,stu.ssex,sc.cno,sc.grade FROM student stu, sc WHERE stu.sno = sc.sno;

连接操作的执行过程

嵌套循环法（NESTED-LOOP）
首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1
中的第一个元组与该元组拼接起来，形成结果表中一个元组。
表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，
找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。
重复上述操作，直到表1中的全部元组都处理完毕

索引连接（INDEX-JOIN）
对表2按连接字段建立索引
对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就
将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组

4.2.2 自然连接

一条SQL语句可以同时完成选择和连接查询，这时WHERE子句是由连接谓词和选择谓词组成的复合条件。

1 2	-- 查询选修3号课程且成绩在90分以上的所有学生的学号和姓名。 SELECT stu.sno, stu.sname FROM student stu, sc WHERE stu.sno = sc.sno AND cno = '2' AND sc.grade > 90;

执行过程:

先从SC中挑选出Cno=’2’并且Grade>90的元组形成一个中间关系
再和Student中满足连接条件的元组进行连接得到最终的结果关系

4.2.3 自身连接

一个表与其自己进行连接, 需要给表起别名以示区别;
由于所有属性名都是同名属性，因此必须使用别名前缀.

1 2	-- 查询每一门课的间接先修课（即先修课的先修课） SELECT DISTINCT c2.* FROM course c1, course c2 WHERE c1.cpno = c2.cno;

4.2.4 外连接

外连接与普通连接的区别

普通连接操作只输出满足连接条件的元组
外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
左外连接
列出左边关系中所有的元组
右外连接
列出右边关系中所有的元组

-- 查询每个学生及其选修课程的情况
SELECT * FROM student LEFT OUTER JOIN sc ON student.sno = sc.sno;
SELECT * FROM student RIGHT OUTER JOIN sc ON student.sno = sc.sno;
-- 改用内连接
SELECT s.sno, s.sname, s.ssex,sc.cno, sc.grade FROM student s INNER JOIN sc ON s.sno = sc.sno;

4.2.5 多表连接

两个以上的表进行连接

-- 查询每个学生的学号、姓名、选修的课程名及成绩
SELECT s.sno, sname, cname, grade FROM student s, sc, course c WHERE s.sno=sc.sno AND sc.cno=c.cno;
-- 使用join改造
SELECT s.sno, sname, cname, grade FROM student s JOIN sc ON s.sno=sc.sno JOIN course c ON sc.cno=c.cno;

4.3 嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块,
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询.

上层的查询块称为外层查询或父查询
下层查询块称为内层查询或子查询
SQL语言允许多层嵌套查询, 即一个子查询中还可以嵌套其他子查询
子查询的限制
不能使用ORDER BY子句

嵌套查询求解方法

不相关子查询：

子查询的查询条件不依赖于父查询：
由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的
查找条件。

相关子查询：子查询的查询条件依赖于父查询

首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE
子句返回值为真，则取此元组放入结果表

然后再取外层表的下一个元组

重复这一过程，直至外层表全部检查完为止

4.3.1 带有`IN`谓词的子查询

-- 查询与“刘晨灿”在同一个系学习的学生。
SELECT * FROM student WHERE sdept IN (SELECT sdept FROM student WHERE sname='刘晨灿');
-- JOIN 可以替代子查询
SELECT * FROM student s1 JOIN student s2 ON s1.sdept = s2.sdept AND s2.sname='刘晨灿';
-- 用自身连接查询代替
SELECT s1.* FROM student s1, student s2 WHERE s1.sdept=s2.sdept AND s2.sname='刘晨灿';

-- 查询选修了课程名为“信息系统” 的学生学号和姓名
SELECT sno, sname FROM student WHERE sno IN (
 SELECT sno FROM sc WHERE cno IN (
  SELECT cno FROM course WHERE cname='信息系统'
 )
);
-- 用连接查询改造
SELECT s.sno, s.sname FROM student s, sc, course c WHERE s.sno = sc.sno AND sc.cno=c.cno AND c.cname='信息系统';
-- 用join
SELECT s.sno, s.sname FROM student s JOIN sc ON s.sno = sc.sno JOIN course c ON sc.cno=c.cno AND c.cname='信息系统';

4.3.2 带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。

-- 由于一个学生只可能在一个系学习，则可以用 = 代替IN， 尽量不使用
SELECT * FROM student WHERE sdept = (SELECT sdept FROM student WHERE sname='刘晨灿');

-- 找出每个学生超过他选修课程平均成绩的课程号。
SELECT * FROM sc x WHERE grade >= (
 SELECT AVG(grade) FROM sc y WHERE y.sno=x.sno
);

4.3.3 带有`ANY（SOME）`或`ALL`谓词的子查询

使用ANY或ALL谓词时必须同时使用比较运算, 语义为：

ANY 大于子查询结果中的某个值
ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
= ANY 大于等于子查询结果中的某个值
= ALL 大于等于子查询结果中的所有值

<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值（通常没有实际意义）
= ALL 等于子查询结果中的所有值（通常没有实际意义）
!=（或<>） ANY 不等于子查询结果中的某个值
!=（或<>） ALL 不等于子查询结果中的任何一个值

-- 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
SELECT sname, sage
FROM student WHERE sdept <> 'CS' AND sage < ANY (
 SELECT sage FROM student WHERE sdept = 'CS'
);
-- 用聚集函数实现
SELECT sname, sage
FROM student WHERE sdept <> 'CS' AND sage < (
 SELECT MAX(sage) FROM student WHERE sdept = 'CS'
);

-- 查询非计算机科学系中比计算机科学系所有学生年龄小的学生姓名和年龄
SELECT sname, sage
FROM student WHERE sdept <> 'CS' AND sage < ALL (
 SELECT sage FROM student WHERE sdept = 'CS'
);
-- 用聚集函数来实现
SELECT sname, sage
FROM student WHERE sdept <> 'CS' AND sage < (
 SELECT MIN(sage) FROM student WHERE sdept = 'CS'
);

4.3.4 带有`EXISTS`谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则外层的WHERE子句返回真值
若内层查询结果为空，则外层的WHERE子句返回假值
由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假
值，给出列名无实际意义。
NOT EXISTS谓词
若内层查询结果非空，则外层的WHERE子句返回假值
若内层查询结果为空，则外层的WHERE子句返回真值

-- 查询所有选修了1号课程的学生姓名
SELECT sname FROM student WHERE EXISTS (
 SELECT * FROM sc WHERE student.sno=sc.sno AND cno='1'
);

-- 查询与“刘晨灿”在同一个系学习的学生。
SELECT * FROM student s1 WHERE EXISTS (
 SELECT sdept FROM student s2 WHERE s1.sdept=s2.sdept AND sname='刘晨灿'
)

-- 查询选修了全部课程的学生姓名
SELECT sname FROM student 
 WHERE NOT EXISTS (
  SELECT * FROM course WHERE NOT EXISTS (
   SELECT * FROM sc WHERE sno=student.sno AND cno=course.cno
  )
 );

4.4 集合查询

集合操作的种类

并操作 UNION
交操作 INTERSECT
差操作 EXCEPT

参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同

-- UNION：将多个查询结果合并起来时，系统自动去掉重复元组
-- UNION ALL：将多个查询结果合并起来时，保留重复元组

-- 查询计算机科学系的学生及年龄不大于19岁的学生, 多个查询查同一张表union
SELECT * FROM student WHERE sdept = 'CS'
UNION
SELECT * FROM student WHERE sage <= 19

-- 查询选修了课程1或者选修了课程2的学生。
SELECT * FROM sc WHERE cno='1'
UNION
SELECT * FROM sc WHERE cno='2'

-- 查询计算机科学系的学生与年龄不大于19岁的学生的交集。
SELECT * FROM student WHERE cdept='CS'
INTERSECT
SELECT * FROM student WHERE sage <= 19;

-- 查询既选修了课程1又选修了课程2的学生。
SELECT * FROM sc WHERE cno='1'
INTERSECT
SELECT * FROM sc WHERE cno='2';

-- 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
SELECT * FROM student WHERE sdept='CS'
MINUS
SELECT * FROM student WHERE sage<=19;

4.5 基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（Derived Table）成为主查询的查询对象

-- 找出每个学生超过他选修课程平均成绩的课程号。
SELECT sc.sno, sc.cno FROM sc,
 (SELECT sno, AVG(grade) AS avggrade FROM sc GROUP BY sno) savg
 WHERE sc.sno = savg.sno AND sc.grade >= savg.avggrade;
 
-- 查询所有选修了1号课程的学生姓名
SELECT DISTINCT student.sname FROM student, (SELECT sno FROM sc WHERE cno = '1') sc1
WHERE student.sno = sc1.sno;

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属
性。

5 数据更新

5.1 插入数据

5.1.1 插入元组

语句格式:

1
2
3

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

功能：将新元组插入指定表中

INTO子句

指定要插入数据的表名及属性列
属性列的顺序可与表定义中的顺序不一致
没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
指定部分属性列：插入的元组在其余属性列上取空值

VALUES子句
提供的值必须与INTO子句匹配

值的个数
值的类型

-- 将一个新学生元组（学号：201215128;姓名：陈冬;性别：男;所在系：IS;年龄：18岁）插入到Student表中。
INSERT INTO student(sno, sname, ssex, sage, sdept) VALUES('201215128','陈东','m'，18, 'IS');
INSERT INTO student VALUES('201215129','陈东梅','m'，18, 'IS'，null);

-- 插入一条选课记录('201215128', '1')
INSERT INTO sc(cno, sno) VALUES('1', '201215128');
INSERT INTO sc VALUES('201215128', '1', null);

5.1.2 插入子查询结果

语句格式:

1
2
3

INSERT
INTO <表名> [(<属性列1> [,<属性列2>… )]
子查询;

INTO子句
插入子查询结果;
SELECT子句目标列值的个数以及值的类型必须与INTO子句匹配

-- 建表
CREATE TABLE dept_age(sdept CHAR(25), ave_age SMALLINT);
-- 插入子查询结果
INSERT INTO dept_age(sdept, ave_age)
SELECT sdept, AVG(sage) FROM student GROUP BY sdept;

5.2 修改数据

语句格式

1
2
3

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

功能:

修改指定表中满足WHERE子句条件的元组
SET子句给出<表达式>的值用于取代相应的属性列
如果省略WHERE子句，表示要修改表中的所有元组

5.2.1 修改某一个元组的值

1 2	-- 将学生201215121的年龄改为22岁 UPDATE student SET sage = 22 WHERE sno = '201215121';

5.2.2 修改多个元组的值

1 2	-- 将所有学生的年龄增加一岁 UPDATE student SET sage = sage + 1;

5.2.3 带子查询的修改语句

1
2
3

-- 将计算机科学系全体学生的成绩置零
UPDATE sc SET grade=0 WHERE sno IN
(SELECT sno FROM student WHERE sdept='CS')

5.3 删除数据

语句格式:

1
2
3

DELETE
FROM <表名>
[WHERE <条件>];

功能：删除指定表中满足WHERE子句条件的元组
WHERE子句

指定要删除的元组
缺省表示要删除表中的全部元组，表的定义仍在字典中

5.3.1 删除某一个元组的值

1 2	-- 删除学号为201215128的学生记录。 DELETE FROM student WHERE sno='201215128';

5.3.2 删除多个元组的值

1 2	-- 删除所有的学生选课记录。 DELETE FROM sc;

5.3.3 带子查询的删除语句

1 2	-- 删除计算机科学系所有学生的选课记录。 DELETE FROM sc WHERE sno IN (SELECT sno FROM student WHERE sdept='CS');

6 空值的处理

空值就是“不知道”或“不存在”或“无意义”的值。
一般有以下几种情况：

该属性应该有一个值，但目前不知道它的具体值
该属性不应该有值
由于某种原因不便于填写

6.1 空值的产生

空值是一个很特殊的值，含有不确定性。对关系运算带来特殊的问题，需要做特殊的处理。

-- 向SC表中插入一个元组，学生号是”201215125”，课程号是”1”，成绩为空。
INSERT INTO sc(sno, cno, grade) VALUES('201215125','1',null);

-- 将Student表中学生号为”201215138”的学生所属的系改为空值
UPDATE student SET sdept=null WHERE sno='201215138'

6.2 空值的判断

判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示。

1 2	-- 从Student表中找出漏填了数据的学生信息 SELECT * FROM student WHERE sname IS NULL OR ssex IS NULL OR sage IS NULL OR sdept IS NULL;

6.3 空值的约束条件

属性定义（或者域定义）中

有NOT NULL约束条件的不能取空值
加了UNIQUE限制的属性不能取空值
码属性不能取空值

6.4 空值的算术运算、比较运算和逻辑运算

空值与另一个值（包括另一个空值）的算术运算的结果为空值
空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN。

有UNKNOWN后，传统二值（TRUE，FALSE）逻辑就扩展成了三值逻辑

x	y	x AND y	x OR y	NOT x
T	T	T	T	F
T	U	U	T	F
T	F	F	T	F
U	T	U	T	U
U	U	U	U	U
T	F	F	U	U
F	T	F	T	T
F	U	F	U	T
F	F	F	F	T

T表示TRUE, F表示FALSE, U表示UNKOWN

-- 找出选修1号课程的不及格的学生。
SELECT sno FROM sc WHERE grade < 60 AND cno='1';
-- 查询结果不包括缺考的Grade值为null的学生

-- 选出选修1号课程的不及格的学生以及缺考的学生。
SELECT sno FROM sc WHERE grade < 60 AND cno='1'
UNION
SELECT sno FROM sc WHERE grade IS NULL AND cno='1';

7 SQL小结

SQL可以分为数据定义、数据查询、数据更新、数据控制四大部分;
SQL是关系数据库语言的工业标准。大部分数据库管理系统产品都能支持SQL92, 但是许多数据库系统只支持SQL99、SQL2008和SQL2011的部分特征，至今尚没有一个数据库系统能够完全支持SQL99以上的标准。

8 Oracle 常用函数

字符类函数CHR()实现整型转字符。dual是Oracle系统内部提供的用于实现临时数据计算的特殊
表。
1
SELECT CHR(90), CHR(72) FROM dual;

字符串连接函数CONCAT(s1,s2)

1	SELECT CONCAT('hello ', 'world') information FROM dual;

数字类函数cell()返回指定小数的整数
1
SELECT ceil(5.6) FROM dual;

日期和时间类函数

1 2	SELECT sysdate AS 系统日期 FROM dual; SELECT ADD_MONTHS(SYSDATE, 2) FROM dual;

SQL语言基础

1 SQL 概述

1.1 SQL 的特点

1.1.1 综合统一

1.1.2 高度非过程化

1.1.3 面向集合的操作方式

1.1.4 以同一种语法结构提供多种使用方式

1.1.5 语言简介，易学易用

1.2 SQL 的基本概念

1.2.1 基本表

1.2.2 存储文件

1.2.3 视图

2 用户模式

3 数据定义

3.1 定义基本表

3.2 Oracle中的数据类型

3.3 修改基本表

4 数据查询

4.1 单表查询

4.1.1 选择表中的若干列

4.1.2 选择表中的若干元组

4.1.3 ORDER BY 子句

4.1.4 聚集函数

4.1.5 GROUP BY 子句

4.2 连接查询

4.2.1 等值连接查询

4.2.2 自然连接

4.2.3 自身连接

4.2.4 外连接

4.2.5 多表连接

4.3 嵌套查询

4.3.1 带有IN谓词的子查询

4.3.2 带有比较运算符的子查询

4.3.3 带有ANY（SOME）或ALL谓词的子查询

4.3.4 带有EXISTS谓词的子查询

4.4 集合查询

4.5 基于派生表的查询

5 数据更新

5.1 插入数据

5.1.1 插入元组

5.1.2 插入子查询结果

5.2 修改数据

5.2.1 修改某一个元组的值

5.2.2 修改多个元组的值

5.2.3 带子查询的修改语句

5.3 删除数据

5.3.1 删除某一个元组的值

5.3.2 删除多个元组的值

5.3.3 带子查询的删除语句

6 空值的处理

6.1 空值的产生

6.2 空值的判断

6.3 空值的约束条件

6.4 空值的算术运算、比较运算和逻辑运算

7 SQL小结

8 Oracle 常用函数

1 `SQL` 概述

1.2 `SQL` 的基本概念

4.1.3 `ORDER BY` 子句

4.1.5 `GROUP BY` 子句

4.3.1 带有`IN`谓词的子查询

4.3.3 带有`ANY（SOME）`或`ALL`谓词的子查询

4.3.4 带有`EXISTS`谓词的子查询