绪论

四个基本概念

1. 数据：描述事物的符号记录。
2. 数据库：概括地讲，数据库数据具有永久存储、有组织和可共享的三个基本特点。严格地讲：数据库是长期储存在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。
3. 数据库管理系统：是位于用户和操作系统之间的一层数据管理软件。和操作系统一样是计算机的基础软件。主要功能：
   1. 数据定义功能
   2. 数据组织、存储和管理
   3. 数据操纵功能
   4. 数据库的事务管理和运行管理
   5. 数据库的建立和维护功能
   6. 其他功能（通信功能，数据转换功能，互访和互操作功能等）
4. 数据库系统：是由数据库、数据库管理系统（及其应用开发工具）、应用程序和数据库管理员（DBA）组成的存储、管理、处理和维护数据的系统。

数据库的发展阶段

1. 人工管理阶段：数据不保存、不共享、不具有独立性。

2. 文件系统阶段：可保存，但共享性差，冗余度大，独立性差。

3. 数据库管理系统：数据库系统的出现使信息系统从以加工数据的程序为中心转向围绕共享的数据库为中心的新阶段。

   数据的共享性高、冗余度低且易扩充：数据共享可以大大减少数据冗余，节约存储空间。

   数据独立性高：

   • 物理独立性：是指用户的应用程序与数据库中数据的物理存储是相互独立的。
   • 逻辑独立性：是指用户的应用程序与数据库的逻辑结构是相互独立的。

   数据由数据库管理系统统一管理和控制。

数据模型

两类数据模型：

1. 概念模型：也称信息模型，它是按用户观点来对数据和信息建模，主要用于数据库设计。 基本概念：

   • 实体：客观存在并可相互区别的事物。e.g. 人
   • 属性：实体所具有的某一特性。e.g. 人的鼻子 嘴巴
   • 码：唯一标识实体的属性。e.g. 学号
   • 实体型：用实体名及其属性集合来抽象和刻画同类实体。e.g. 含有鼻子和嘴巴的人 （实体+属性）
   • 实体集：同一类型实体的集合。e.g. 学校 （很多实体型）

   概念模型的一种表示方法：实体-联系方法。用E-R图来措述现实世界的概念模型，E-R方法也称为E-R模型。

2. 逻辑模型和物理模型

   • 逻辑模型：它是按计算机系统的观点对数据建模，主要用于数据库管理系统的实现。包括：
     1. 层次模型
     2. 网状模型
     3. 关系模型
   • 物理模型：是对数据最底层的抽象，它描述数据在系统内部的表示方法和存取方法，或在磁盘或磁带上的存储方式和存取方法，是面向计算机系统的。

ER图

ER图分为实体、属性和关系三个部分。实体用长方形，属性是椭圆形，关系是菱形。唯一的标识：主键。

联系：

• 实体之间的联系通常是指不同实体集之间的联系。实体之间的联系有一对一、一对多和多对多等多种联系。
• 实体内部的联系通常是指实体各属性之间的联系。

常用的数据模型

常用的数据模型：层次模型，网状模型，关系模型，面向对象数据模型，对象关系数据模型，半结构化数据模型。

• 层次模型：

  1. 有且只有一个结点没有双亲结点，这个结点称为根结点。
  2. 根以外的其他结点有且只有一个双亲结点。
  

• 网状模型：

  1. 允许一个以上的结点无双亲
  2. 一个结点可以有多于一个双亲
  

• 关系模型：对应一张表

  1. 元组：表中的一行。
  2. 属性：表中的一列。
  3. 码：也称码键，表中的某个属性组。
  4. 域：是一组具有相同数据类型的值的集合。
  5. 分量：元组中的一个属性。
  

数据库系统的结构

1. 模式数据库中全体数据的逻辑结构和特征的描述，它仅仅涉及型的描述，不涉及具体的值。其一个具体值称为模式的一个实例。模式是相对稳定的，实例是相对变动的。

   三级模式结构：外模式+模式+内模式

   • 外模式：也称子模式或用户模式，它是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。

   • 模式：也称为逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。

   • 内模式：也称存储模式，一个数据库只有一个内模式。它是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式。

   

2. 二级映像： 外模式/模式映像：当模式改变时，由数据库管理员对各个外模式/模式映像作相应改变，可以使外模式保持不变。应用程序不必修改。保证了数据与程序的逻辑独立性。

模式/内模式映像：当数据库的存储结构改变时，有数据库管理员对模式/内模式映像作相应改变，可以使模式保持不变，从而应用程序也不用改变。保证了数据与程序的物理独立性。

数据与程序之间的独立性使得数据的定义和描述可以从应用程序中分离出去。另外，由于数据的存取由数据库管理系统管理，从而简化了应用程序的编制，大大减少了应用程序的维护和修改。

数据库系统的组成

1. 硬件平台及数据库：足够大内存、磁盘或磁盘阵列等设备，较高的通道能力以提高数据的传送率。

2. 软件：数据库管理系统，支持数据库管理系统运行的操作系统，具有与数据库接口的高级语言及其编译系统，以数据库管理系统为核心的应用开发工具，为特定应用环境开发的数据库应用系统。

3. 人员：开发、管理和使用数据库的人员主要包括数据库管理员、系统分析员和数据库设计人员、应用程序员和最终用户。

   数据库管理员职责：

   • 决定数据库中的信息内容和结构
   • 决定数据库的存储结构和存取策略
   • 定义数据的安全性要求和完整性约束条件
   • 监控数据库的使用和运行
   • 数据库的改进和重组、重构

关系

可以形象地表示为 R(U, D, DOM, F)

• R:关系名
• U:所有属性名
• D：属性来自哪些域
• DOM：属性和域的映射
• F: 属性间的依赖关系

关系语言的分类

分为关系代数语言，关系演算语言和SQL语言。

• 关系代数语言：一种抽象的查询语言，它用对关系的运算来表达查询。

  三大要素：运算对象（关系）、运算符（集合运算符和专门的关系运算符）和运算结果（关系）

  • R $\cap$ S：交 (union)：结果由既属于R又属于S的元组组成。

  • R $\cup$ S：并 (intersection)：结果由属于R和属于S的所有元组组成。

  • R $-$ S：差 (difference)：结果由属于R而不属于S的所有元组组成。

  • R $\times$ S：笛卡尔积 (product)：

    • 将 R 中的每个元组 $t_1$ 和 S 中的每个元组 $t_2$ 配对连接
    • 列数：$n+m$
    • 前 $n$ 列是关系 R 的一个元组 $t_1$
    • 后 $m$ 列是关系 S 的一个元组 $t_2$
    • 行数：$k_1 \times k_2$
    • 当 R 和 S 中有重名属性 A 时，则采用 R.A 和 S.A 分别命名对应的属性列
    

  • $\sigma_C$(R)：选择 (selection)：

    • 用于在关系R中选择满足给定条件的各个元组

    • C：选择条件，是一个逻辑表达式

    • 结果为只包含R中某些元组的新的关系

    • 一些运算符：

      1. 比较运算符

         • $>$：大于
         • $\ge$：大于等于
         • $<$：小于
         • $\le$：小于等于
         • $=$：等于
         • <>：不等于

      2. 逻辑运算符

         • $\neg$：非
         • $\wedge$：与
         • $\vee$：或
    

  • $\Pi_L$(R)：投影 (projection)

    • 用于从R中选择出若干属性列组成新的关系(默认去重)
    • L为R中的属性列表
    • 结果为只包含R中某些列的新的关系
    • 结果要去掉重复元组
    

  • R $\Join$ S：连接 (Join)

    • A $\theta$ B：
      • A 和 B：分别为 R 和 S 上度数相等且可比的属性组
      • $\theta$：比较运算符
    • 等值连接：$\theta$ 为 $=$ 的连接运算称为等值连接
      • 从关系 R 与 S 的笛卡尔积中选取A、B属性值相等的那些元组
    • 自然连接是在公共属性（组）上进行的等值连接
      • 两个关系中必须具有公共属性（组）
      • 在结果中把重复的属性列去掉
    
    • 悬浮元组(Dangling tuple)：两个关系 R 和 S 在做自然连接时，关系 R 中某些元组有可能在 S 中不存在公共属性上值相等的元组，从而造成 R 中这些元组在操作时被舍弃了，这些被舍弃的元组称为悬浮元组。
    • 外连接(Outer Join)：如果把悬浮元组也保存在结果关系中，而在其他属性上填空值(Null)，就叫做外连接。
    • 左外连接(Left Outer Join或Left Join)：只保留左边关系 R 中的悬浮元组
    • 右外连接(Right Outer Join或Right Join)：只保留右边关系 S 中的悬浮元组

  • R $\div$ S：保留 R 中满足 S 的，而且 R 中的列去掉 S 的列。

关系的完整性

1. 实体完整性(Entity Integrity)：主码唯一且非空
2. 参照完整性(Referential Integrity)：外码要么为空，要么对应另一表的主码。
   • 外码：外码是一个关系中的属性。例如，如果关系R1中的属性X是关系R2的主码，那么X就是R1的外码。
3. 用户定义完整性(User-defined Integrity)：是针对特定应用领域定义的数据约束条件。它包括字段的值域、数据类型和有效性规则等。这些约束条件反映了特定应用所涉及的数据必须满足的应用语义要求，例如，一个年龄字段可能被限制只能包含正整数。

SQL

SQL基本概念

SQL是Structured Query Language的缩写，意思是结构化查询语言，是一种在数据库管理系统中查询或对数据库里面的数据进行更改的语言。

常见的数据库管理系统：

• 关系型： MySQL , Oracle , PostgreSQL , Mycrosoft SQL Server
• 非关系型： Redis , MongoDB

SQL语言分类

1. 数据定义语言DDL (Data Ddefinition Language): SQL数据定义语言主要用来定义逻辑结构，包括定义基表，视图和索引。删除表，定义表，修改表。
2. 数据查询语言DQL (Data Query Language): SQL的数据查询语言主要用来对数据库中的各种数据对象进行查询。
3. 数据操纵语言DML (Data Manipulation Language): SQL的数据操纵语言，用于改变数据库中的数据，包括插入，删除，修改
4. 数据控制功能DCL (Data Control Language): SQL的数据控制语言，对表和视图的授权，完整性规则的描述以及事务开始和结束等控制语句。

SQL语言特点

1.综合统一（独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据、建立数据库、查询、更新、维护、数据库重构、数据库安全） 2.高度非过程化（用户只需提出"做什么"，而不必指明"怎么做"。） 3.面向集合的操作方式（SQL采用集合操作方式） 4.以同一种语法结构提供两种使用方式（SQL既是自含式语言，又是嵌入式语言。SQL语句能够嵌入到高级语言程序中） 5.语言简洁，易学易用（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$ 位数字 （整数位有 $p - d$ 位数字）
• DECIMAL($p,d$), DEC(p, d)：同NUMERIC （MySQL）
• REAL：取决于机器精度的单精度浮点数
• DOUBLE PRECISION： 取决于机器精度的双精度浮点数
• FLOAT($n$)： 可选精度的浮点数，精度至少为n位数字
• BOOLEAN：逻辑布尔量
• DATE：日期，包含年、月、日，格式为YYYY-MM-DD
• TIME：时间，包含一日的时、分、秒，格式为HH:MM:SS
• TIMESTAMP：时间戳类型
• INTERVAL：时间间隔类型

模式定义

1

create SCHEMA <模式名> AUTHORIZATION <模式名>

e.g. 为用户 WANG 定义一个 学生-课程 模式 S-T。

1

create SCHEMA "S-T" AUTHORIZATION WANG;

模式删除

1

drop SCHEMA 模式名 CASCADE/RESTRICT

删除模式，其中CASCADE和RESTRICT必须二选一

• CASCADE（级联）：删除模式的同时也把该模式的所有数据库对象删除。
• RESTRICT（限制）：如果该模式下有下属对象，就拒绝这个删除语句的执行。

表的定义删除修改

定义

1

create table 表名 (字段名 类型 字段约束, 字段名 类型 字段约束, 字段名 类型 字段约束);

e.g.

1

create table user (name varchar(20), age int, sex char(1));

删除

1

drop table 表名 CASCADE/RESTRICT;

• CASCADE: 如果表有外键，视图，触发器的话也会强行删除。
• RESTRICT: 如果表有外键，视图，触发器的话不会删除。

修改

1
2

alter table 表名 ADD 新列名 数据类型 [完整性约束];
alter table 表名 ADD [表级完整性约束];

e.g. 向Student表增加"入学时间"列，其数据类型为日期型。

1

alter table Student ADD S_entrance DATE;

e.g. 将Student表中年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

1

alter table Student ALTER COLUMN Sage INT;

索引的定义删除修改

数据量大的时候，查询耗时长，定义索引可以有效减少消耗时间，索引可以建立在一列或多列上，索引是独一无二的。

定义

1

create UNIQUE/CLUSTER INDEX 索引名 ON 表名(列名，列名，...);

• UNIQUE: 唯一索引
• CLUSTER: 聚簇索引：物理顺序与索引顺序相同

e.g.

1

create UNIQUE INDEX Stusno ON Student(Sno);

修改

1

alter INDEX 旧索引名 RENAME TO 新索引名;

e.g.

1

alter INDEX SCno RENAME TO SCSno;

删除

1

drop INDEX 索引名;

e.g.

1

drop INDEX Stusname;

查询

查询所有

1

select * from 表名;

查询部分+起别名

1

select 列名1 (as) 别名1,列名2 (as) 别名2,... from 表名 (as) 别名; -- as 可以省略

假设起别名的时候别名有空格：select a b c from 表名;

• 不符合语法，会编译报错。
• 解决方案：select a 'b c' from 表名; 或者 select a "b c" from 表名;

查询结果去重

distinct关键词

1

select distinct 列名1 from 表名;

查询结果加条件

查询符号：

• 比较：=, >, <, >=, <=, != (<>), not+上述比较运算符

  1 2 3

  select a from 表名 where b = 1; select a from 表名 where b <> 1; --(select a from 表名 where b != 1;) select a from 表名 where b < 1;

• 确定范围：BETWEEN a AND b, NOT BETWEEN a AND b.

  1	select a from 表名 where b between 1 and 2;

• 确定集合（不是区间，是列表/集合，相当于多个or连接）：IN, NOT IN

  1	- select a from 表名 where b in(1,2);

• 字符匹配（模糊查询，支持%（匹配任意个字符）和下划线（一个下划线只匹配一个字符）匹配）：LIKE, NOT LIKE

  1 2 3 4 5 6

  select a from 表名 where a like '%o%' -- a中有o的 select a from 表名 where a like '%M' -- a中以M结尾的 select a from 表名 where a like 'K%' -- a中以K开始的 select a from 表名 where a like '_T%' -- a中第二个字母是T的 select a from 表名 where a like '__R%' -- a中第三个字母是R的 select a from 表名 where a like '%\_%' -- a中有_的 (\是转义字符)

• 空值（判断是否为null）：IS NULL, IS NOT NULL

  1	select a from 表名 where b is null;

• 多重条件（逻辑运算）：AND, OR, NOT （优先级 not > and > or）

  1 2	select a from 表名 where b < 1 and c = 2; select a from 表名 where b < 1 or c = 2;

排序

默认升序

1

select a from 表名 order by c;

• 降序

  1	select a from 表名 order by c desc;

• 升序

  1	select a from 表名 order by c asc;

• 两个字段排序或者多个字段排序：

  1	select a from 表名 order by c asc, d asc; -- c在前起主导，只有c相同时考虑比较d

• 按照字段的位置进行排序：

  1	select a from 表名 order by 2; -- 2表示第二列，按照查询结果的第二列排序

单行处理函数

特点：一个输入对应一个输出。

• lower 转换小写：

  1	select lower(a) from 表名;

• upper 转换大写：

  1	select upper(a) from 表名;

• substr 取子字符串（起始下标从1开始）：

  1	select substr(a,1,3) from 表名; -- substr(被截取的字符串，起始下标，截取长度)

  e.g. 找a中以字母A开头的：

  1 2	select a from 表名 where a like 'A%'; select a from 表名 where substr(a,1,1) = 'A';

• concat 字符串拼接：

  1	select concat(a,b) from 表名;

• length 字符串长度：

  1	select length(a) from 表名;

• trim 去空格：

  1 2	select a from 表名 where a = trim(' K'); select a from 表名 where a = 'K'; -- same as above

多行处理函数

特点：多个输入对应一个输出。

• count 计数：

  1	select count(a) from 表名; -- 计算 a 这个字段中不为空的元素总数。

• sum 求和：

  1	select sum(a) from 表名;

• avg 平均：

  1	select avg(a) from 表名;

• max 最大值：

  1	select max(a) from 表名;

• min 最小值：

  1	select min(a) from 表名;

注意

• 分组函数在使用的时候必须先分组，然后才能用。如果没有对数据进行分组，整张表默认为一组。
• 分组函数自动忽略null，不需要提前对null进行处理
• count(*)和count(a)的区别：count(*)是统计表当中的总行数，只要有一行数据就count++，count(a)是统计a字段下不为null的元素总数。
• 分组函数不能直接使用在where子句中
• 所有分组函数可以组合起来一起用：

  1	select min(a),max(a),count(a) from 表名;

分组查询

实际应用中可能需要先进行分组，然后对每一组的数据进行操作：

1

select a from 表名 group by c

分组函数不能直接使用在where子句中：因为分组函数必须先分组再使用，where执行的时候还没有分组，所以where后面不能出现分组函数。

在一条select语句中，如果有group by语句的话，select后面只能跟参加分组的字段和分组函数。

两个字段联合分组

e.g. 找出每个部门不同工作岗位的最高薪资：

1

select department,job,max(sal) from 表名 group by department,job

技巧：把两个字段联合成一个字段看

e.g. 找出每个部门最高薪资，要求显示最高薪资大于3000：

1
2

select department,max(sal) from 表名 group by department having max(sal) > 3000 --先分组再筛选
select department,max(sal) from 表名 where sal > 3000 group by department --先筛选再分组

• 策略：where和having，优先选择where，where完成不了选择having。where完成不了的情况：显示平均薪资大于2500。

单表查询总结

• 关键字执行顺序：from → where → group by → having → select → order by
• 从某张表中查询数据，先经过where条件筛选出有价值的数据，对这些有价值的数据进行分组，分组之后可以使用having继续筛选，select查询出来。