数据库的学习

第一章 绪论

基本概念

数据---描述事物的符号

数据库DB---长期存在在计算机内、有组织的、可共享的 大量数据的集合

数据库管理系统DBMS---位于用户和操作系统之间的一层数据管理软件

数据库系统DBS---由数据库、数据库管理系统（及其应用开发工具）、应用程序和数据库管理员（DBA）组成的存储、管理、处理和维护数据的系统

单任务、单用户SQLite---微信

数据管理技术的产生和发展

人工管理阶段

数据不保存、应用程序管理数据、数据不共享、数据不具有独立性

文件系统阶段

数据可以长期保存、由文件系统管理数据

数据共享性差，冗余度大、数据独立性差

数据库系统阶段

数据库的特点

• 数据结构化

数据路系统实现整体数据的结构化，是数据库的主要特征之一，也是数据库与文件系统的本质区别

• 数据的共享性高、冗余度低且易扩充

数据共享可以大大减少冗余，节约存储空间，避免数据之间的不相容性与不一致性

• 数据独立性高

物理独立性：用户的应用程序与数据库中的物理存储是相互独立的

逻辑独立性：用户的应用程序与数据库的逻辑结构是相互独立的

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

数据的安全性保护

数据的完整性检查

数据模型

数据模型的组成要素

• 数据结构
• 数据操作
• 数据的完整性约束条件

• 概念模型

主要用于数据库的设计

实体entity：客观存在并可相互区别的事物

属性attribute：实体所具有的某一特性

码key：唯一标识属性集

实体型：用实体名及其属性名集合来抽象和刻画同类实体

ER模型：实体-联系方法（第七章）

• 逻辑模型

主要用于数据库管理系统（DBMS）的实现

常用的数据模型

（逻辑模型包括）层次模型、网状模型、关系模型、面向对象数据模型、对象关系数据模型、半结构化数据模型

• 层次模型（树形）

层次模型像一个倒立的树，结点的双亲是唯一的

优缺点：

优点：数据结构比较清晰、查询效率高、提供了良好的完整性支持

缺点：多对多非层次不适合、若一个结点有多个双亲结点、查询子女结点必须通过双亲结点、结构严密，层次命令趋于程序化

• 网状模型（图）

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

优缺点：

优点：更直观，一个结点可以有多个双亲，结点之间可以有多种联系、具有良好的性能，存取效率高

缺点：结构较复杂，DDL、DML语言复杂、应用程序在访问数据时必须选择适当的存取路径

• 关系模型

关系数据库采用关系模型作为数据的组织方式

关系模型的数据结构

关系（表）、元组（行）、属性（列）

码（表中的某个属性组，可以唯一确定一个元组）

域（属性的取值来自某个域）

分量（元组的一个属性值，二维坐标系所确定的值）

优缺点：

优点：建立在严格的数学概念上、概念单一，结构清晰，存取路径对用户透明，具有更高的数据独立性、更好的安全保密性

缺点：存储路径对用户是隐蔽的，查询效率往往不如格式化数据模型

• 物理模型

对数据最底层的抽象

数据库系统的结构

模式是相对稳定的，实例是变动的

数据库的三级模式结构

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

一个数据库只有一个模式

外模式：数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述

一个数据库可以有多个外模式

同一外模式可以为某一用户的多个应用系统所使用，但一个应用程序只能使用一个外模式

保证数据库安全性

内模式：数据物理结构和存储方式的描述，是数据在数据库内部的组织方式

一个数据库只有一个内模式

数据库的二级映像

保证了数据库外模式的稳定性，从而从底层保证了应用程序的稳定性，除非应用需求本身发生变化，否则应用程序一般不需要修改

• 外模式/模式映像

模式改变时， *可以使外模式保持不变，应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性

• 模式/内模式映像

存储结构改变时， *可以使模式保持不变，从而应用程序不必修改，保证了数据与程序的物理独立性

数据库系统的组成

数据库、数据库管理系统（及其应用开发工具）、应用程序和数据库管理员构成

1. 硬件平台
   足够大的内存、足够大的磁盘或磁盘阵列灯设备存放数据库、较高的通道能力，以提高数据传送率
2. 软件
   数据库管理系统
   操作系统
   与数据库接口的高级语言及其编译系统
   应用开发工具
3. 人员
   数据库管理员DBA
   系统分析员和数据库设计人员
   应用程序员
   用户

第二章 关系数据库

关系数据结构及形式化定义

关系

二维表

域

笛卡尔积

属性---列

n目关系必有n个属性

若关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，则称该属性组为候选码

若一个关系有多个候选码，则选定其中一个为主码

主属性：所有候选码并集中的属性

非主属性/非码属性：全集-主属性

关系类型：基本关系（基本表）、查询表、视图表

基本表性质：

（1）列是同质的，来自同一个域

（2）不同的列可出自同一个域，称其中每一列为一个属性

（3）列的顺序无所谓

（4）任意两个元组的候选码不能取相同的值

（5）行的顺序无所谓

（6）分量必须取原子值

关系模式

关系模式是型，关系是值

元组集合的结构（由哪些属性构成、这些属性来自哪些域、以及属性与域之间的影响关系）

完整性约束条件

R(U,D,DOM,F)

关系操作

查询、插入、删除、修改

查询操作又可分为选择、投影、连接、除、并、差、交、笛卡尔积等

其中，选择、投影、并、差、笛卡尔积是5种基本操作

关系代数和关系演算，介于之间的结构化查询语言SQL

关系的完整性

实体完整性和参照完整性---是必须满足的，关系的两个不变性

• 实体完整性

主属性不能为空

外码：设F是基本关系R的一个或一组属性，但不是关系R的码，K是基本关系S的主码。如果F与K相对应，则称F是R的外码

基本关系R为参照关系，基本关系S为被参照关系。关系R和S不一定是不同的关系

• 参照完整性

取空值

等于S中某个元组的主码值

• 用户完整性

应用领域所需要遵循的条件

关系代数

• 传统的集合运算

二目运算，包括交、并、交、笛卡尔积4种运算

• 专门的关系运算

包括选择、投影、连接、除运算等

关系演算

第三章 关系数据库标准语言SQL

SQL的基本观念

学生-课程数据库

数据定义

模式的定义与删除

create schema <模式名> authorization <用户名>;

例：为用户WANG定义一个学生-课程模式S-T

create schema "S-T" authorization WANG;

create schema <模式名> authorization <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];

例：为用户ZHANG创建一个模式TEST，并且在其中定义一个表TAB1

create schema test authorization ZHANG

create table tab1(col1 smallint,

                            col2 int,

                            col3 char(20),

                            col4 numeric(10,3),

                            col5 decimal(5,2)

);

drop schema <模式名> <cascade|restrict>;

cascade(级联)---删除模式的同时把该模式中所有的数据库对象全部删除

restrict(限制)---该模式中已经定义了下属的数据库对象，则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行

 基本表的定义、删除与修改

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) not null,

 Cpno char(4),

 Ccredit smallint,

 foreign key(Cpno) references Course(Cno)

 /*表级完整性约束条件，Cpno是外码，被参照表式Course，被参照列是Cno*/

);

建立学生选课表SC

create table sc

(Sno char(9),

 Cno char(4),

 Grade smallint,

 primary key(Sno,Cno),

 foreign key (Sno) references Student(Sno),

 /*表级完整性约束条件，Sno是外码，被参照表式Student*/

foreign key (Cno) references Course(Cno)

);

模式与表

方法一，在表名中明显地给出模式名

create table "S-T".Student(…);

create table "S-T".Course(…);

create table "S-T".SC(…);

方法二，在创建模式语句中同时创建表

方法三，设置所属的模式，当用户创建基本表时若没有指定模式，系统根据搜索路径（search path）来确定该对象所属的模式

show search_path;

修改基本表

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 alter column Sage int;

增加课程名称必须取唯一值的约束条件

alter table Course add unique(Cname);

删除基本表

drop table <表名> [restrict|cascade];

索引的建立与删除

建立索引

create [unique] [cluster] index <索引名> on <表名>(<列名> [<次序>] [,<列名> [<次序>]] …);

例：为学生-课程数据库中的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);

修改索引 

alter index <旧索引名> rename to <新索引名>;

例：将SC表的SCno索引名改为SCSno

alter index SCno rename to SCSno;

删除索引

drop index <索引名>;

数据查询

select [all|distinct] <目标表达式>  [,<目标表达式>]…

from <表名或视图名> [,<表名或视图名>] | (<select语句>) [as] <别名>

[where <条件表达式>]

[group by <列名1> [having<条件表达式>]]

[order by <列名2> [asc|desc]]; 

单表查询

选择表中的若干列

（1）查询指定列

例：查询全体学生的学号与姓名

select Sno,Sname from Student;

查询全体学生的姓名、学号、所在系

select Sname,Sno,Sdept from Student;

（2）查询全部列

select * from Student;

等同于查询所有列

（3）查询经过计算的值

目标列表达式不仅可以是表中的属性列，也可以是表达式

例：查询全体学生的姓名以及出生年份

select Sname,2014-Sage from Student;

目标表达式不仅可以是算术表达式，还可以是字符串常量、函数等

例：查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名

select Sname,'Year of Birth:' ,2014-Sage,lower(Sdept) from Student;

可以通过指定别名来改变查询结果的列标题

select Sname name,'Year of Birth:' bith,2014-Sage bithday,lower(Sdept) department from Student;

选择表中的若干元组

（1）消除取值重复的行

distinct

（2）查询满足条件的元组

• 比较大小

例：查询所有年龄在20岁以下的学生姓名及其年龄

select Sname,Sage from Student where age<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 between 20 and 23;

• 确定集合

谓词in可以用来查找属性值属于指定集合的元组

例：查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别

select Sname,Ssex from Student where Sdept in('CS','MA','IS');

• 字符匹配

谓词like可以用来字符串的匹配

[not] like '<匹配串>' [escape ''<换码字符>]

<匹配串>可以是一个完整的字符串，也可以含有通配符%和_

%代表任意长度（长度可以为0）的字符串

_代表任意单个字符

例：查询学号为201215121的学生的详细情况

select * from Student where Sno like '201215121';

等价于

select * from Student where Sno = '201215121';

如果like后面的匹配串中不含通配符，可以用=取代like

用！或<> （不等于）取代not like

例：查询所有姓刘的学生的姓名、学号和性别

select Sname,Sno,Ssex from Student where Sname like '刘%';

查询性“欧阳”且全名为三个汉字的学生的姓名

select Sname from Student where Sname like '欧阳_';

注：数据库字符集为ASCII时，一个汉字需要两个__

查询名字中第二个字为“阳”的学生的姓名和学号

select Sname,Sno from Student where Sname like '_阳%';

如果用户要查询的字符串本身就含有通配符%和_，这时就要使用escape ‘<换码字符>’ 短语对通配进行转义

例3.34：查询DB_Design课程的课程号和学分

select Cno,Ccredit from Course where Cname like 'DB \ _Design' escape '\';

例3.35：查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况

select * from Course where Cname like 'DB \ _%i__' escape '\';

这里的匹配串为'DB \ _%i__' 。第一个_前面有换码字符\，所以被转义为普通的_字符。而i后面的两个_的前面均没有换码字符\，所以它们仍作为通配符

• 涉及空值的查询

某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号

select Sno,Cno from SC where Grade is null;

注意这里的is不能用等号=代替

• 多重条件查询

逻辑运算符and和or可用来连接多个查询条件，and的优先级高于or，可以用括号改变优先级

例：查询计算机科学系年龄在20岁以下的学生姓名

select Sname from Student where Sdept='CS' and Sage<20;

in谓词相当于多个or运算符的缩写

例：查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别

select Sname,Ssex from Student where Sdept in('CS','MA','IS');

select Sname,Ssex from Student where Sdept='CS' or Sdept='MA' or Sdept='IS';

order by子句

对查询结果按照一个或多个属性列的升序/降序排列，默认值为升序

例：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

select Sno,Grade from SC where Cno='3' order by Grade desc;

查询全体学生情况，查询结果按所在系的系号升序排序，同一系中的学生按年龄降序排序

select * from Student order by Sdept,Sage desc;

聚集函数

count(*)

count( [distinct|all] <列名>)

sum( [distinct|all] <列名>)

avg( [distinct|all] <列名>)

max( [distinct|all] <列名>)

min( [distinct|all] <列名>)

例：查询学生总人数

select count(*) from Student;

计算选修1号课程的学生平均成绩

select avg(Grade) from SC where Cno='1';

查询选修1号课程的学生的最高分数

select max(Grade) from SC where Cno='1';

查询学生201215012选修课程的总学分

select sun(Ccredit) from SC,Course where Sno='201215012' and SC.Cno=Course.Cno;

group by子句

分组后聚集函数将作用于每一个组

例：求各个课程号及相应的选课人数

select Cno,count(Sno) from SC group by Cno;

使用having对分组结果进行选择

例：查询选修了三门以上课程的学生学号

select Sno from SC group by Sno having count(*)>3;