ch11 SQL概述

SQL（Structured Query Language）结构化查询语言，是关系数据库的标准语言
SQL是一个通用的、功能极强的关系数据库语言

  • 综合统一
  • 高度非过程化：不关心过程是如何实现的
  • 面向集合的操作方式
  • 以同一种语法结构提供两种使用方法：嵌入式、单独使用
  • 语言简洁，易学易用

SQL的特点

image.png

image.png

image.png

三级模式、两层映射

image.png

ch12 SQL数据定义

层次化的数据库对象命名机制

  • 一个关系数据库管理系统的实例（Instance）中可以建立多个数据库
  • 一个数据库中可以建立多个模式
  • 一个模式下通常包括多个表、视图和索引等数据库对象

实例：对数据库进行管理，提供权限等功能
模式：相互隔离的用于区分的应用域

  • 单模式
  • 多模式

image.png

数据定义

image.png

定义模式

定义模式实际上定义了一个命名空间。
在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。
在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句。

  • CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>| <视图定义子句>|<授权定义子句>]

image.png

删除模式

DROP SCHEMA <模式名> <CASCADE|RESTRICT>

  • CASCADE（级联）
    • 删除模式的同时把该模式中所有的数据库对象全部删
  • RESTRICT（限制）
    • 如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。
    • 仅当该模式中没有任何下属的对象时才能执行。

DROP SCHEMA ZHANG CASCADE;

  • 删除模式ZHANG
  • 同时该模式中定义的表TAB1也被删除

定义基本表

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

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

通俗理解：schema是文件夹，表是文件
数据类型：用来描述域
image.png

数据类型

SQL中域的概念用数据类型来实现
定义表的属性时需要指明其数据类型及长度
选用哪种数据类型

  • 取值范围
  • 要做哪些运算：要结合具体的应用场景选择合适的数据类型
    数据类型 含义
    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 时间间隔类型

修改基本表

image.png

image.png

  • [例3.8] 向Student表增加“入学时间”列，其数据类型为日期型
    • ALTER TABLE Student ADD S_entrance DATE;
    • 不管基本表中原来是否已有数据，新增加的列一律为空值
  • [例3.9] 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。
    • ALTER TABLE Student ALTER COLUMN Sage INT;
  • [例3.10] 增加课程名称必须取唯一值的约束条件。
    • ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表

DROP TABLE <表名>［RESTRICT| CASCADE］;

  • RESTRICT：删除表是有限制的。
    • 欲删除的基本表不能被其他表的约束所引用
    • 如果存在依赖该表的对象，则此表不能被删除
  • CASCADE：删除该表没有限制。
    • 在删除基本表的同时，相关的依赖对象一起删除

image.png

索引

order by是实在的排序
查询次数和更新次数不一致：例如姓名。
建立索引的目的：加快查询速度

  • 由数据库管理员或表的拥有者建立：数据库管理员可以看到各种表的增删改查的记录，从而建立更方便查询的索引
  • 由关系数据库管理系统自动完成维护
  • 关系数据库管理系统自动使用合适的索引作为存取路径，用户不必也不能显式地选择索引：非过程性（没有精确的步骤）的解释语言

如果查询次数很低，就没有必要建立索引
数据库索引有多种类型，常见索引包括顺序文件上的索引、B+树索引、散列（hash)索引、位图索引等。

  • 顺序文件上的索引：是针对按指定属性值升序或降序存储的关系，在该属性上建立一个顺序索引文件，索引文件由属性值和相应的元组指针组成。
  • B+树索引：是将索引属性组织成B+树形式，B+树的叶结点为属性值和相应的元组指针。B+树索引具有动态平衡的优点。
  • 散列索引：是建立若干个桶，将索引属性按照其散列函数值映射到相应桶中，桶中存放索引属性值和相应的元组指针。散列索引具有查找速度快的特点。
  • 位图索引：是用位向量记录索引属性中可能出现的值，每个位向量对应一个可能值。

image.png
[例3.13] 为学生-课程数据库中的Student，Course，SC三个表建立索引。Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引

  • CREATE UNIQUE INDEX Stusno ON Student(Sno);
  • CREATE UNIQUE INDEX Coucno ON Course(Cno);
  • CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

修改/删除索引

image.png

数据字典

image.png

ch13 SQL数据查询（单表）

Mysql中having和where的区别
SQL中Where与Having的区别
having与where的区别:

  • having是在分组后对数据进行过滤
  • where是在分组前对数据进行过滤
  • having后面可以使用聚合函数
  • where后面不可以使用聚合
  • 在查询过程中执行顺序：from>where>group（含聚合）>having>order>select

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

关键字

在SQL中，DISTINCT和ALL是两个可选的关键字，用于指定SELECT语句返回的结果集是否包含重复的值。

  • DISTINCT表示只返回不同的值，即去除重复的记录。例如，SELECT DISTINCT name FROM Websites 返回Websites表中name字段的不同值
  • ALL表示返回所有的值，包括重复的记录。这是默认的行为，即不指定关键字时等同于使用ALL。例如，SELECT ALL name FROM Websites 返回Websites表中name字段的所有值

is和=的不同

  • is 用于判断是否为null值，而 = 不能判断null值。
  • Null不等于任何值，在= !=中始终都代表着false
  • is 不能用于比较数值或字符串，而 = 可以。
  • is 是一个逻辑运算符，而 = 是一个关系运算符。

如果要判断一个字段是否为null，应该用is null或is not null，而不是= null或!= null。例如：

1
2
3
4
--正确写法
select * from student where name is null;
--错误写法
select * from student where name = null;

如果要判断一个字段是否为true或false，可以用is true或is false，也可以用= true或= false。但是如果字段为null，那么is true和= true的结果不同。例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
--假设有一个表格如下：
id | flag
1  | true
2  | false
3  | null

--查询flag为true的记录：
select * from table where flag is true; --返回id为1的记录
select * from table where flag = true; --返回id为1的记录

--查询flag不为true的记录：
select * from table where flag is not true; --返回id为2和3的记录
select * from table where flag != true; --只返回id为2的记录，因为null和true不相等也不不相等。

如果要比较两个数值或字符串是否相等，应该用=而不是is。例如：

1
2
3
4
5
6
7
8
9
--假设有一个表格如下：
id | name 
1  | Alice 
2  | Bob 
3  | Charlie

--查询name为Alice的记录：
select * from table where name = 'Alice'; --返回id为1的记录
select * from table where name is 'Alice'; --报错，因为is不能用于比较字符串。

假设有一个学生表格如下：

id name age
1 Alice 18
2 Bob 19
3 Charlie null

如果你想查询年龄为null的学生，你应该用这样的语句：
select * from student where age is null;
这样就会返回id为3的记录。
如果你用这样的语句：
select * from student where age = null;
那么就不会返回任何记录，因为null不等于null，也不等于任何其他值。

通配符

image.png
DISTINCT去重
BETWEEN … AND … NOT BETWEEN … AND …
IN <值表>, NOT IN <值表>
image.png

image.png

image.png

多重条件查询

image.png

对查询结果排序

image.png

聚集函数

聚集函数只能用于select字句和group by中的having字句中
当聚集函数遇到空值时，除COUNT (*) 外，都跳过空值而只处理非空值
image.png

分组查询

GROUP BY子句分组：细化聚集函数的作用对象

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

image.png

image.png

ch14 SQL数据查询（连接）

等值连接/自然连接

两张表的笛卡尔积，然后筛选即可。
连接操作是引用关系和被引用关系之间的操作
自然连接：外码和关键字的含义相同
等值连接：外码和关键字的含义不同
image.png

连接操作的执行过程

嵌套循环法

image.png

排序合并法

归并排序
连接方法的选择由数据库自身决定，对程序员透明，并不清楚，也不关心
image.png
连接：在更大的表上进行过滤、选择

自身连接

对自身连接的表的操作是只读操作，从自身派生出新的关系
两个元组之间相互引用：先设置为null，再插入。形和值的约束
image.png

image.png

左右外连

  • null是有意义的
  • 两个关系R和S在做自然连接时，关系R中某些元组有可能在S中不存在公共属性上值相等的元组，从而造成R中这些元组在操作时被舍弃了，这些被舍弃的元组称为悬浮元组。
  • 左连接：保留连接符左侧的悬浮元组，列出左边关系中的所有元组，右表不匹配的为NULL
  • 右连接：保留连接符右侧的悬浮元组，列出右边关系中的所有元组，左表不匹配的为NULL
  • LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

image.png

多表连接

image.png

ch15 SQL数据查询（嵌套）

一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
image.png
嵌套的使用是基于一系列的假设，一旦假设的改变，就无法成功实现了，同时比较难理解
order by 只是用于结果的展示，不影响语义，所以不能使用
image.pngS
嵌套查询：

  • 习惯性控制具体的数据

  • 能思路简单的完成任务 — 要根据别人的思路走，难以用数学论证等价

  • 不相关子查询：子查询的查询条件不依赖于父查询由里向外 逐层处理。

    • 即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

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

    • 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表，并通过SELECT挑选对应的属性
    • 然后再取外层表的下一个元组
    • 重复这一过程，直至外层表全部检查完为止
    • 可以理解为外层查询是一个for循环遍历，提供特定的属性值给内层查询

不相关子查询

image.png

相关子查询

求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关,因此必须反复求值。
image.png

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

image.png

带有EXISTS谓词的子查询

image.png

1
2
3
select * from emp e where exists (
	select * from dept d where  deptno <30 and d.deptno = e.deptno ；
);
  1. 首先执行外查询 select * from emp e，然后取出第一行数据，将数据中的部门编号传给内查询
  2. 内查询执行select * from dept d where deptno <30 and d.deptno = e.deptno ；看是否查询到结果，查询到，则返回true，否则返回false；比如传来的是30，则不满足deptno <30 and d.deptno = 30，返回false
  3. 内查询返回true，则该行数据保留，作为结果显示；反之，返回false，则不作结果显示
  4. 逐行查询，看内查询是否查到数据，是否保留作结果显示

如果上述内查询为： select * from dept d where deptno <30
因为该语句都会查询到有结果，每一行都返回true，所以会查询到的是全部员工信息
image.png

两层EXISTS的嵌套子查询

注意：SQL 查询的结果是一个集合，实际上就是一层全称谓词。如果给出的条件中再出现全称谓词，则可以转换为存在谓词进行查询。
注意：嵌套查询如果进行了相关查询，则实际上就是一个外层和内层进行笛卡尔积然后对结果集合进行筛选（嵌套内层进行筛选）的过程。

  • 查询（曾经）在所有部门待过的员工。

转换为，不存在这样的部门，员工没有待过。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT emp_no
FROM employees e
WHERE NOT EXISTS (
  SELECT *
  FROM departments d
  WHERE NOT EXISTS (
    SELECT *
    FROM dept_emp de
    WHERE d.dept_no=de.dept_no
    AND e.emp_no=de.emp_no
  )
);
  1. 要先看select的语句代表的含义
    1. SELECT * FROM departments d 选择所有的部门 – 可以理解为遍历所有的部门
    2. SELECT * FROM dept_emp de WHERE d.dept_no=de.dept_no AND e.emp_no=de.emp_no 选择某个员工在某个部门的工作信息
  2. 直观理解：从not exists的语义上理解：不存在一个部门，该员工没有待过（没有工作信息） ->要查找在所有部门都工作过的员工
  3. 从内到外理解：对于每一个员工e，检查每一个部门d，判断有无工作记录
    1. 第二层子查询：判断有无工作记录，如果没有工作记录，第六行的not exists返回为true -> 存在这样一个部门d，该员工在该部门没有工作信息 ->第一层子查询返回的结果不是为空，第三行的not exists为false，因此第一行的selcet就不会执行
    2. 第二层子查询：如果有工作记录，第六行的not exists返回为false -> 不存在这样一个部门d，该员工在该部门没有工作信息 ，第一层子查询中的select不会执行 -> 该员工在部门d中有工作过，但是无法保证在其他部门也有工作记录，所以第一层子查询会挑选一个新的部门d后，重新进入第二层子查询判断 -> 全部判断完后，如果仍然没有部门，说明第三行第三行的not exists为true，因此第一行的selcet就会执行 ->得到想要的结果

ch16 SQL数据查询（集合）

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

并操作 UNION

对于填入同一张表的数据：业务逻辑上应该相同。
image.png

交操作 INTERSECT

image.png

差操作 EXCEPT

image.png

ch17 SQL数据查询（基于派生表）

image.png

image.png

ch18 SQL数据更新-插入

插入元组

将新元组插入指定表中，语句格式
INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>]… );

  • INTO子句
    • 指定要插入数据的表名及属性列
    • 属性列的顺序可与表定义中的顺序不一致
    • 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
    • 指定部分属性列：插入的元组在其余属性列上取空值
  • VALUES子句 提供的值必须与INTO子句匹配
    • 值的个数
    • 值的类型

在关系型数据库中可以避免犯错误 - 保姆型的，所以必须要提供限制，处理错误的方式交给：核（数据库）
NOSQL：不用约束形，代价是处理错误的方式交给：壳（应用）
image.png

插入子查询

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

  • 子查询SELECT子句目标列必须与INTO子句匹配
    • 值的个数
    • 值的类型

image.png

ch19 SQL数据更新-修改

image.png

image.png

修改数据与完整性

关系数据库管理系统在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则

  • 实体完整性
  • 参照完整性
  • 用户定义的完整性
    • NOT NULL约束
    • UNIQUE约束
    • 值域约束

ch20 SQL数据更新-删除

delete和drop有区别
sql数据库中的 delete 与drop的区别
image.png

image.png

ch21 SQL中的空值

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

  • 该属性应该有一个值，但目前不知道它的具体值：我的对象（x）
  • 该属性不应该有值：猫的平均学分绩点
  • 由于某种原因不便于填写：涉及填写个人隐私的信息

空值的产生

image.png

空值的判断

image.png

空值的约束条件

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

  • 有NOT NULL约束条件的不能取空值
  • 加了UNIQUE限制的属性不能取空值：如果插入学号是null，可能学号已经重复了，则违反了unique的限制
  • 码属性不能取空值

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

image.png

  • 空值与另一个值（包括另一个空值）的算术运算的结果为空值
  • 空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN。
  • 有UNKNOWN后，传统二值（TRUE，FALSE）逻辑就扩展成了三值逻辑
  • 处理空值，需要显式的使用is null 或者is not null

image.png

ch22 视图

create table创建
create view创建的是数据结构，有什么不同？
视图的特点：

  • 虚表，是从一个或几个基本表（或视图）导出的表
  • 只存放视图的定义，不存放视图对应的数据
  • 基表中的数据发生变化，从视图中查询出的数据也随之改变

image.png

建立视图

组成视图的属性列名：全部省略或全部指定

  • 全部省略: 由子查询中SELECT目标列中的诸字段组成
  • 明确指定视图的所有列名:
    • 某个目标列是聚集函数或列表达式
    • 多表连接时选出了几个同名列作为视图的字段
    • 需要在视图中为某个列启用新的更合适的名字

image.png

行列子集视图

[例3.85]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= ‘IS’
WITH CHECK OPTION;

  • 定义IS_Student视图时加上了WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept=’IS’的条件。
  • 若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图。
  • IS_Student视图就是一个行列子集视图。

操作视图就是操作基本表

基于多个表的视图

[例3.86] 建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）。 

1
2
3
4
5
6
7
CREATE VIEW IS_S1(Sno,Sname,Grade)        
AS         
SELECT Student.Sno,Sname,Grade        
FROM  Student,SC       
 WHERE  Sdept= 'IS' AND                       
				Student.Sno=SC.Sno AND                       
				SC.Cno= '1';

基于视图的视图

[例3.87] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。 

1
2
3
4
5
CREATE VIEW IS_S2        
AS        
SELECT Sno,Sname,Grade        
FROM  IS_S1        
WHERE  Grade>=90;

基于表达式的视图

[例3.88] 定义一个反映学生出生年份的视图。 

1
2
3
4
CREATE  VIEW BT_S(Sno,Sname,Sbirth)        
AS         
SELECT Sno,Sname,2014-Sage        
FROM  Student;

分组视图

[例3.89] 将学生的学号及平均成绩定义为一个视图 

1
2
3
4
5
CREAT  VIEW     S_G(Sno,Gavg)             
AS               
SELECT Sno,AVG(Grade)             
FROM  SC             
GROUP BY Sno;

删除视图

语句的格式： DROP VIEW <视图名>[CASCADE];

  • 该语句从数据字典中删除指定的视图定义
  • 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
  • 删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除

image.png

查询视图

  • 用户角度：查询视图与查询基本表相同
  • 关系数据库管理系统实现视图查询的方法

视图消解法（View Resolution）

  • 进行有效性检查
  • 转换成等价的对基本表的查询
  • 执行修正后的查询

image.png
where语句后面不能接聚集函数，where的执行顺序先于聚集函数的执行顺序
image.png

更新视图

image.png

image.png

不可更新视图

  • 有多对一的关系，不能更新：成绩 = 平时成绩 * a1 + 期末成绩 * a2，直接更新成绩
  • 没有判断做逆运算的函数：四舍五入的函数
  • 一个不允许更新的视图上定义的视图也不允许更新

要玩一些比较花的操作：

  • 解决方案符合业务方案吗？
  • 不同数据库对这种情况处理方式不同，如果需要进行数据库的移植，会出错

image.png
例如：修改平均成绩为90，但是基表中的成绩无法修改，因为是多对一的映射

视图的作用

视图能够简化用户的操作

当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作

  • 基于多张表连接形成的视图
  • 基于复杂嵌套查询的视图
  • 含导出属性的视图

视图使用户能以多种角度看待同一数据

  • 视图机制能使不同用户以不同方式看待同一数据
  • 适应数据库共享的需要

视图对重构数据库提供了一定程度的逻辑独立性

image.png

视图能够对机密数据提供安全保护

对不同用户定义不同视图，使每个用户只能看到他有权看到的数据

适当的利用视图可以更清晰的表达查询

image.png