数据库系统概论-终极版

数据库系统概论李迁副教授南京大学工程管理学院

2015.9数据和信息紧密关联，是对对象结构化的重要过程。有效的数据管理能更有效抽取信息、存储信息和安全地使用信息。数据库设计与管理存在于每个企事业单位，如ERP\CAD\CAPP\CIMS，OA，电子商务以及BIM及项目管理软件。数据库系统概论学习能够帮助我们更好地理解现实世界到计算机世界转化过程，指导我们更好地进行信息系统规划、设计与开发。大数据时代的来临所带来的挑战——复杂信息。背景

绪论

关系数据库关系数据库标准语言

数据库安全性

数据库完整性关系数据理论数据库设计与编程关系查询处理和查询优化数据库恢复技术并发控制大纲3教材及参考书10–4教材王珊，萨师煊：数据库系统概论(第四版)高等教育出版社，2006.5AFirstCourseinDatabaseSystems

Jeffrey.D.Ullman,JenniferWidomDept.OfComputerScienceStanfordUniversity参考书王珊：《数据库系统概论(第4版)学习指导与习题解析》高等教育出版社，2008.6上机软件MSSQLServer2005/2008/2012学习、开发、个人版系统可以从微软或学校机器内部网站下载考试成绩平时成绩10%（出勤、提问、书面作业）实验成绩20%（上机实验和大作业（数据库设计））期末闭卷笔试成绩70%实验报告提交格式数据库实验报告实验内容：姓名：学号：日期：实验目的和要求：具体实验题目：（一）实验指导（1）********实验方案：（2）****（二）练习题（1）出现的问题解决方案问题16第一章绪论7一、基本概念1、数据：描述事务的符号记录。可用文字、图形等多种形式表示，经数字化处理后可存入计算机。2、数据库（DB）：按一定的数据模型组织、描述和存储在计算机内的、有组织的、可共享的数据集合。3、数据库管理系统（DBMS）：位于用户和操作系统之间的一层数据管理软件。主要功能包括：

数据定义功能：DBMS提供DDL，用户通过它定义数据对象。

数据操纵功能：DBMS提供DML，用户通过它实现对数据库的查询、插入、删除和修改等操作。

数据库的运行管理：DBMS对数据库的建立、运行和维护进行统一管理、统一控制，以保证数据的安全性、完整性、并发控制及故障恢复。

数据库的建立和维护功能：数据库初始数据的输入、转换，数据库的转储、恢复、重新组织及性能监视与分析等。4、数据库系统（DBS）：计算机中引入数据库后的系统，包括数据库DB数据库管理系统DBMS应用系统数据库管理员DBA和用户

数据库

应用系统应用开发工具

操作系统

数据库管理系统

数据库管理员用户用户用户

数据库系统数据库系统二、数据管理与数据处理1、数据管理：对数据收集、整理、组织、存储、维护、检索、传送等对象操作目标：在妥当的时候以妥当的形式给妥当的人提供妥当的数据。2、数据处理：对数据进行加工、计算、提炼，从而产生新的有效数据的过程数据信息3、管理与处理的关系：管理是处理的基础处理为管理服务数据处理数据处理……源数据新数据新数据

管理和处理又可看成一个问题的两个阶段，故可以统一起来，其中心是管理数据管理数据管理三、数据管理的发展阶段

阶段

人工管理阶段（50年代中期以前）

文件系统阶段（50年代中期至60年代后期）

数据库系统阶段（60年代后期以后）数据管理技术的发展动力应用需求的推动计算机硬件的发展计算机软件的发展1、人工管理阶段（程序员管理阶段）

特点：数据不保存程序员负责数据管理的一切工作数据和程序一一对应，没有独立性和共享性数据和程序的关系：应用程序1数据1应用程序2数据2应用程序n数据n……硬件：有了大容量直接存储外存设备，如磁盘、磁鼓等软件：有了专门的数据管理软件--文件系统处理方式：有批处理、联机实时处理等2、文件系统阶段基础{特点记录内有结构。数据的结构是靠程序定义和解释的。数据只能是定长的。可以间接实现数据变长要求，但访问相应数据的应用程序复杂了。文件间是独立的，因此数据整体无结构。可以间接实现数据整体的有结构，但必须在应用程序中对描述数据间的联系。数据的最小存取单位是记录。三个主要缺点：数据高度冗余：数据基本上还是面向应用或特定用户的。数据共享困难：文件基本上是私有的，只能提供很弱的文件级共享数据和程序缺乏独立性：只有一定的物理独立性，完全没有逻辑独立性。应用程序1数据1应用程序2数据2应用程序n数据n…………数据与程序的关系：存取方法操作系统负责3、数据库系统阶段文件系统不能适应大数据量、多应用共享数据的根本原因：

数据没有集中管理数据库方法的基本出发点：

把数据统一管理、控制，共享使用应用程序1应用程序2应用程序n……数据与程序的关系：DBMS数据库（1）数据高度结构化集成，面向全组织（2）数据共享性好。可为多个不同的用户共同使用（3）数据冗余少，易扩充（4）数据和程序的独立性高物理独立性:存储结构变，逻辑结构可以不变，从而应用程序也不必改变。逻辑独立性:总体逻辑结构变，局部逻辑结构可以不变，从而应用程序也不必改变。好处：简化应用程序的编写和维护（5）数据控制统一

安全性控制：防止泄密和破坏

完整性控制：正确、有效、相容

并发控制：多用户并发操作的协调控制

故障恢复：发生故障时，将数据库恢复到正确状态主要优点4、各个阶段的比较：

人工管理文件系统数据库系统谁管理数据面向谁共享性数据独立性程序员特定应用不能没有操作系统提供存取方法系统集中管理基本上是特定用户共享很弱面向系统充分共享一定的物理独立性较高的独立性

文件系统和数据库系统的本质区别：内部：数据库的数据是结构化的，有联系的

文件系统的各记录无联系外部：数据库系统是共享的文件系统基本上是面向特定用户的§2数据模型数据处理的抽象过程（涉及三个领域）建立概念模型建立数据模型（便于用户和DB设计人员交流）（便于机器实现）一、概念模型(信息模型）把现实世界中的客观对象抽象成的某种信息结构，主要用于数据库设计。

独立于具体的计算机系统

独立于具体的DBMS支持的数据模型现实世界===信息世界抽象=====机器世界（数据世界）转换实体：客观存在并可相互区分的事物。实体集：性质相同的同类实体的集合。属性：实体具有的某一特性。实体标识符：能将一个实体与其它实体区分开来的一个或一组属性。信息世界记录

实体（抽象表示）文件

实体集字段或数据项

属性关键字

实体标识符。唯一地标识一个记录。又称码、键。数据世界1、实体与记录2、型与值在DBS中，每一个对象广义上讲都有型与值之分：

——

型是对象的结构或特性描述，

——值是一个具体的对象实例。类似于程序设计语言中数据类型与数据值的概念。（1）实体型：对实体固有特性或结构的描述。用实体名及其属性名集合来抽象和刻画。如汽车（车牌号，车型，车主）实体值：实体型的一个实例，即一个具体的实体。如（豫A00001，丰田，张三）（2）记录型：记录格式。

记录值：一个具体的记录。如：车牌号名称车主豫A00001丰田张三（3）几点说明

•

区分型与值的实质•DBS中讨论的重点是型•通常只说实体、记录，含义根据上下文自明3、实体间的联系

实体内部的联系（属性间的联系）：反映在数据上就是记录内部数据项间的联系实体之间的联系：反映在数据上就是记录之间的联系（1）1对1联系（1：1）：两个实体集中的每一个实体至多和另一个实体集中的一个实体有联系。如国家——部长学员队——学员（2）1对多联系（1：n）：若实体集A中的每个实体与实体集B中0个或多个实体有联系，而B中每个实体至多与A中的一个实体有联系，则称从A到B为1对多的联系。如国家——总统学员队——队长实体之间的联系可归结为三类：（3）多对多联系（m：n）：两个实体集中的每一个实体都和另一个实体集中0个或多个实体有联系。如学员——课程DBS的核心问题之一：

如何表示和处理实体及实体间的联系。4、概念模型的表示方法之一：

实体—联系方法（Entity-RelationshipApproach）用E—R图（Entity-RelationshipDiagram）描述：

实体型：用长方形表示联系：用菱形表示属性：用椭圆形表示框内写上相应的名称用无向边连接：实体与其属性联系与其属性联系与有关实体，并标上联系类型实体名联系名实体名属性名属性名属性名1n说明：联系也必须命名多个实体之间也可以有联系联系也可以有属性学员领导1n供应量单个实体之间也可以有联系项目供应商零件供应pmn例：某工厂物资管理E--R图（P19）供应商供应商号姓名地址帐号电话号码项目项目号预算开工日期仓库仓库号面积电话号职工职工号姓名年龄职称零件零件号名称规格单价描述库存库存量mn工作1n领导1n供应供应量mnp二、数据模型是对现实世界进行抽象的工具，它按计算机系统的观点对数据建模，用于提供数据库系统中信息表示和操作手段的形式框架，主要用于DBMS的实现，是数据库系统的核心和基础。1、常用的数据模型层次模型网状模型关系模型面向对象模型称作非关系模型，是下列基本层次联系的集合Ri，Rj是实体型（记录型）Lij是从Ri到Rj的1：1或1：n联系}RiRjLij2、数据模型的三要素形式化描述数据、数据之间的联系以及数据操作和有关的语义约束规则的方法数据结构数据操作完整性约束如何保证数据的约束条件得到满足如何实现查、增、删、改如何表示实体及联系（难点是表示联系）根据现实世界实体间联系的特征用四种不同的方法进行抽象层次模型网状模型关系模型面向对象模型(因此，是按照数据结构的类型来命名数据模型）（动态）（静态）3、层次模型根据一个单位的组织结构直观地得出学院部系处学员队教研室教员学员方框表示一个实体型（结点）

线表示联系（边）（1）定义：用树形结构来表示实体以及实体间联系的模型。其特征是：（a）有且仅有一个结点无双亲（根结点）；（b）其它结点有且仅有一个双亲。（2）说明：（a）树中实体间联系只能是从父到子的1:1或1:n联系，对m:n联系，须使用辅助手段转换成多个1:n联系，但不易掌握（b）简单直观，结构清晰，运行效率高，但编程复杂

4、网状模型（1）定义：用图结构来表示实体以及实体间联系的模型。其特征是：任一结点都可以无双亲或有一个以上的双亲。例教员学校班级学生课程（2）优：可表示m:n的联系，运行效率高缺：过于复杂，实现困难（3）说明（a）即使对网状模型，具体在计算机上实现时，m:n的联系仍需分解成若干个1:n的联系。（因此，网状模型的图结构实质上是有向图），如学生课程选课mn课程成绩单学生成绩单学号姓名年龄性别课程号名称学号课程号得分（b）网状模型中允许两结点间有多条边，层次模型则不允许5、关系模型层次、网状模型基本上是面向专业人员的，使用极不方便

问题：寻找一种能面向一般用户的数据模型？（1）定义：用二维表（关系）来描述实体及实体间联系的模型。（2）示例零件供应商供应mn

设备

工人使用保养供应商SS1张三北京S2李四郑州………S#SNAMESADDR零件PP1电机2000P2螺丝2………P#PNAMEPRICE（联系）供应SP

S1P1200

S1P322………S#P#QTY关系：对应一张表，每表起一个名称即关系名元组：表中的一行属性：表中一列，每列起一个名称即属性名主码：唯一确定一个元组的属性组域：属性的取值范围（3）关系模式：对关系的描述，一般表示为：关系名（属性1，属性2，…，属性n）（4）优点：

无论实体还是实体之间的联系都用统一的数据结构（二维表、关系）来表示，可方便地表示m:n联系，因此概念简单，用户易懂易用如：可表示为：学生（学号，姓名，性别，系和年级）课程（课程号，课程名，学分）选修（学号，课程号，成绩）学生选修课程mn表格中行、列次序无关有坚实的理论基础（关系理论）

存取路径对用户透明，用户只需指出“做什么”，不需说明“怎么做”，因此数据独立性更高缺点：由于存取路径对用户透明，查询效率不够高，必须对查询请求进行优化。说明：

关系必须规范化，关系的每个分量必须是一个不可分的数据项，不允许表中套表。规范化理论将在后续章节讲解。（5）关系模型与非关系模型的比较统一不统一均为关系实体及实体间联系采用的数据结构操作方式存取路径关系模型非关系模型对用户透明对用户不透明一次一集合一次一记录三级模式（外模式、模式、内模式）两级映象（外模式/模式，模式/内模式映象）一、DBS的三级模式结构1、模式（Schema）：又称逻辑模式。DB的全局逻辑结构。即DB中全体数据的逻辑结构和特征的描述。

说明①模式只涉及到型的描述，不涉及具体的值（实例），反映的是数据的结构及其联系②模式不涉及物理存储细节和硬件环境，也与应用程序无关③模式承上启下，是DB设计的关键④DBS提供模式DDL（DataDefinitionLanguage）来定义模式（描述DB结构）§3DBS的结构⑤模式定义的任务（概念模型模式）

定义全局逻辑结构（构成记录的属性名、类型、宽度等）定义有关的安全性、完整性要求

定义记录间的联系⑥一个数据库只有一个模式2、外模式：又称子模式或用户模式。DB的局部逻辑结构。即与某一应用有关的数据的一个逻辑表示。

说明:外模式是某个用户的数据视图，模式是所有用户的公共数据视图；一个DB只能有一个模式，但可以有多个外模式；外模式通常是模式的子集，但可以在结构、类型、长度等方面有差异；DBS提供外模式DDL。3、内模式：又称存储模式。数据的物理结构和存储方式的描述。即DB中数据的内部表示方式。

说明：一个数据库只有一个内模式DBS提供内模式DDL；内模式定义的任务记录存储格式，索引组织方式，数据是否压缩、是否加密等。4、两级映象及其作用（1）外模式/模式映象：定义外模式和模式间的对应关系。对应同一个模式可以有多个外模式，对每个外模式都有一个外模式/模式映象。作用：模式变，可修改映象使外模式保持不变，从而应用程序不必修改，保证了程序和数据的逻辑独立性。（2）模式/内模式映象：定义DB全局逻辑结构和存储结构间的对应关系。一个数据库只有一个模式，也只有一个内模式，因此模式/内模式的映象也是唯一的。

作用：存储结构变，可修改映象使逻辑结构（模式）保持不变，从而应用程序不必修改，保证了数据与程序的物理独立性。§4数据库系统的组成1、数据库：一个或多个数据库数据库的四要素：用户数据、元数据、索引和应用元数据2、软件操作系统；支持DBMS的运行

数据库管理系统DBMS（DataBaseManagementSystem）：操纵和管理数据库的大型软件系统，是数据库系统的核心

数据库应用开发工具等辅助软件

具有数据库接口的高级语言与编译系统，如PB、C++等

某个数据库应用系统一、数据库系统（DataBaseSystem，DBS）的组成广义上讲，DBS就是计算机系统中引进数据库后的构成。有下面四部分：3、人员用户应用程序员数据库管理员DBA（使用）（开发）（管理）DBA（DataBasedministrator）的职责：①决定数据库的内容和逻辑结构、存储结构②确定数据的安全性要求和完整性约束条件③监控数据库的使用和运行，维护数据库④决定数据库的存储结构和存储策略

⑤负责数据库的改进和重组重构4、硬件计算机及有关设备，要求有足够大的内、外存储容量及较高的处理速度。数据库系统图示：用户1用户2用户n应用程序1应用程序m辅助软件

DBMS

操作系统数据库数据库DBA负责应用程序员•••••••••二、数据库系统研究的对象如何高效巧妙地进行数据管理，而又花费最少如：占用空间少查询快维护方便等三个主要研究领域：DBMS及其辅助软件数据库设计数据库理论作业：7，13，15，222022/12/2144本章要求：本章内容：1、掌握关系、关系模式、关系数据库等基本概念2、掌握关系的三类完整性的含义3、掌握关系代数运算§1关系模型的基本概念§2RDBS的数据操纵语言：关系代数§3RDBS的数据操纵语言：关系演算语言第二章关系数据库2.1关系模型关系数据结构表结构码关系关系操纵查询、插入、删除、修改关系中的数据约束关系数据结构关系的集合元组的集合关系关系数据库关系模式是型,关系是值;表达方式R(U)关系操纵关系操纵小结关系中的数据约束小结2.2关系代数关系的表示关系操作的表示在讲专门的关系运算之前，为叙述上的方便先引入几个概念。

（1）设关系模式为R(A1,A2,……An)，它的一个关系为R，t∈R表示t是R的一个元组，t[Ai]则表示元组t中相应于属性Ai的一个分量。（2）若A={Ai1,Ai2,……，Aik}，其中Ai1,Ai2,……,Aik是A1,A2,……，An中的一部分，则A称为属性列或域列，Ã则表示{A1,A2,……，An}中去掉{Ai1,Ai2,……，Aik}后剩余的属性组。t[A]={t[Ai1],t[Ai2],……,t[Aik]}表示元组t在属性列A上诸分量的集合。（3）R为n目关系，S为m目关系，tr∈R,ts∈S,trts称为元组的连接(concatenation)，它是一个n+m列的元组，前n个分量为R的一个n元组，后m个分量为S中的一个m元组。（4）给定一个关系R（X，Z），X和Z为属性组，定义当t[X]=x时，x在R中的象集(imageset)，为Zx={t[Z]|t∈R,t[X]=x},它表示R中的属性组X上值为x的诸元组在Z上分量的集合。

投影运算选择运算笛卡儿乘积举例：客户—代理商—产品关系代数中的扩充运算除法运算是二目运算，设有关系R（X，Y）与关系S（Y，Z），其中X，Y，Z为属性集合，R中的Y与S中的Y可以有不同的属性名，但对应属性必须出自相同的域。关系R除以关系S所得的商是一个新关系P（X），P是R中满足下列条件的元组在X上的投影：元组在X上分量值x的象集Yx包含S在Y上投影的集合。记作：R÷S={tr[X]|tr∈R∧Πy(S)Yx}其中，Yx为x在R中的象集，x=tr[X]。例2.11已知关系R和S，如图2.11(a)，(b)所示，则R÷S如图(c)所示。与除法的定义相对应，本题中X={A,B}={(a1,b2),(a2,b4),(a3,b5)}，Y={C,D}={(c3,d5),(c4,d6)}，Z={F}={f3,f4}。其中，元组在X上各个分量值的象集分别为：(a1,b2)的象集为{(c3,d5),(c4,d6)}(a2,b4)的象集为{(c1,d3)}(a3,b5)的象集为{(c2,d8)}S在Y上的投影为{(c3,d5),(c4,d6)}显然只有(a1,b2)的象集包含S在Y上的投影，所以R÷S={(a1,b2)}RSTABCD

CDF

ABa1b2c3d5

c3d5f3

a1b2a1b2c4d6

c4d6f4

a2b4c1d3

a3b5c2d8

`关系代数小结3.4.5关系代数实例3.5关系演算3.5关系演算3.5.0一阶谓词逻辑3.5.1关系的表示五种基本关系操作的表示小结:关系代数与关系演算2022/12/21175二、未实现的元组关系演算语言——ALPHAE.F.Codd提出，但并未实现。

1、检索操作（GET)

（1）不设元组变量例：取出计算机系学生的学号：工作空间名表达式限定条件GETW（S.S#):S.SD=‘CS’2022/12/211761、检索操作（GET）（1）不设元组变量例：取出计算机系学生的学号：相当于原子公式t[i]CGETW(1)（S.S#):S.SD=‘CS’(事实上关系名起到元组变量的作用)相当于投影取出一个计算机系学生的学号GETW（S.S#):S.SD=‘CS’定额2022/12/21177(2)使用元组变量应用场合用较短的名字代替较长的关系名使用量词时查找选修全部课程的学生姓名RANGECCXRANGESCSCXGETW（S.SN):CXSCX(SCX.S#=S.S#SCX.C#=CX.C#)2022/12/211782、存储操作（1）修改：UPDATE

（2）插入：PUT

（3）删除：DELETE参阅教材P64—P65。关键字不能修改，只能先删除、再插入2022/12/21179四、域关系演算语言——QBEQBE是QueryByExample

的缩写，1978年在IBM370上实现。

1、特点用户通过表格形式提出查询，查询结果也通过表格显示出来用户容易掌握，易学易用三、域关系演算与元组关系演算类似，只不过这里的变量取值范围是属性值，其谓词变元称作欲变量，关系的属性名可视作欲变量。

关系代数、元组关系演算、域关系演算的表达能力是等价的。2022/12/211802、使用方法（1）用户提出使用要求（如键入某一命令）（2）机器显示空白表格（3）用户输入关系名如学生关系SS（4）机器自动显示属性名S#SNSDSA2022/12/211812、使用方法（1）用户提出使用要求（如键入某一命令）（2）机器显示空白表格（3）用户输入关系名如学生关系S（4）机器自动显示属性名SS#SNSDSA（5）提出查询要求如查询计算机系的学生姓名和年龄P.张三CSP.30查询条件SD=‘CS’P.是操作符示例元素(任选一个可能的值)2022/12/21182SS#SNSDSA3、其他例子:

（1）查询操作例1：查计算机系年龄大于19的学生姓名P.张三CS>19SS#SNSDSAP.张三CS>19P.张三两个条件写两行,示例元素相同,表示条件之间是“与”的关系SS#SNSDSAP.张三CS>19P.李四示例元素不同,表示条件之间是“或”的关系例2：查计算机系或年龄大于19的学生姓名2022/12/21183例3：查选修C2的学生名字（涉及两个关系，需要连接操作）SS#SNSDSAP.张三S1SCS#C#GS1C2不同关系中的两个示例元素相同，表示了连接操作。2022/12/21184(2)修改操作修改操作符为“U.”，不允许修改主码，若要修改主码，需先删除元组，再插入。SS#SNSDSAU.CSS1SS#SNSDSACSS1U.修改操作不包含表达式，可有两种表示方法。例2：将计算机系所有学生的年龄增加1岁。SS#SNSDSACSS1U.例1：把学号为S1的学生转入计算机系。19S119+12022/12/21185（3）插入操作操作符为“I.”，新元组必须包含码，其他属性值可为空。SS#SNSDSACSS8I.19美丽例：（4）删除操作操作符为“D.”。例：删除计算机系的学生。SS#SNSDSACSD.作业：习题第五题：试用关系代数、关系演算及ALPHA语言完成查询。1862022/12/21187本章要求：本章内容：1、掌握SQL定义基本表和建立索引的方法2、掌握SQL中各种查询方法和数据更新方法3、掌握SQL中视图的定义方法和用法4、掌握SQL的授权机制5、了解嵌入式SQL的基本使用方法§1SQL概述§2SQL数据定义功能§3SQL数据操纵功能§4视图§5SQL数据控制功能§6嵌入式SQL第三章关系数据库标准语言SQL2022/12/21188一、SQL的发展

SQL是StructuredQueryLanguage的缩写（ANSI解释为StandardQueryLanguage）

74年Boyce&Chambarlin提出，在IBM的SystemR上首先实现79年Oracle82年IBM的DB284年Sybase采用SQL作为数据库语言§1SQL概述2022/12/21189二、SQL的主要特点1、一体化：两方面集DDL、DML、DCL为一体实体和联系都是关系，因此每种操作只需一种操作符86年10月成为美国国家标准87年国际标准化组织（ISO）采纳为国际标准89年ISO推出SQL8992年ISO推出SQL2目前正制定SQL3标准2022/12/211902、高度非过程化语言（WHAT

HOW

）3、面向集合的操作方式（一次一集合）4、交互式和嵌入式两种使用方式，统一的语法结构5、语言简洁，易学易用完成核心功能只有9个动词：数据查询：SELECT数据定义：CREATE，DROP，ALTER数据操纵：INSERT，DELETE，UPDATE数据控制：GRANT，REVOKE6、支持三级模式结构视图外模式基本表（的集合）模式存储文件和索引内模式2022/12/21191SQL支持的三级模式结构用户SQLViewV1ViewV2BasetableB1BasetableB2BasetableB3BasetableB4StoredfileS1StoredfileS2外模式模式内模式2022/12/21192说明：

基本表是独立存在的表。一个关系对应一个表。一个（或多个）表对应一个存储文件，每个表可有若干索引，这些索引也可放在存储文件中。

对内模式，只需定义索引，其余的一切均有DBMS自动完成

视图是从一个或几个基本表中导出的表，概念上同基本表。但它并不真正存储数据，也不独立存在，它依赖于导出它的基本表，数据也存放在原来的基本表中。SQL与关系模型SQL功能§2SQL数据定义功能整数数据类型：依整数数值的范围大小，有BIT,INT,SMALLINT,TINYINT四种。精确数值类型：用来定义可带小数部分的数字，有NUMERIC和DECIMAL两种。二者相同，但建议使用DECIMAL。如：123.0、8000.56近似浮点数值数据类型：当数值的位数太多时，可用此数据类型来取其近似值，用FLOAT和REAL两种。如：1.23E+10日期时间数据类型：用来表示日期与时间，依时间范围与精确程度可分为DATETIME与SMALLDATETIME两种。如：1998-06-0815:30:00smalldatetime4byte1900年1月1日到2079年6月6日精确到分钟datetime8byte从1753年1月1日到9999年12月31日的日期和时间数据，精确度为百分之三秒字符串数据类型：用来表示字符串的字段。包括：CHAR,VARCHAR,TEXT三种，如：“数据库”二进制数据类型：用来定义二进制码的数据。有：BINARY,VARBINARY，IMAGE

三种，通常用十六进制表示：如：OX5F3C货币数据类型：用来定义与货币有关的数据，分为MONEY与SMALLMONEY两种，如：123.0000创建数据库CREATEDATABASE<数据库名>如，createdatabasejxgl;创建、修改和删除数据表在SQL语言中，使用语句CREATETABLE创建数据表，其基本语法格式为：

CREATETABLE<表名>(<列定义>[{，<列定义>|<表约束>}])<表名>是合法标识符，最多可有128个字符，如S,SC,C，不允许重名。<列定义>：<列名><数据类型>[DEFAULT][{<列约束>}]DEFAULT：若是某字段设置有默认值，当该字段未被输入数据时，则以该默认值自动填入该字段。（1）字段名（列名）：字段名可长达128个字符。字段名可包含中文、英文字母、下划线、#号、货币符号（￥）及AT符号(@)。同一表中不许有重名列；(2)字段数据类型(3)字段的长度、精度和小数位数CHAR(N)--------CHAR(20)NUMERIC(P,[S])-------NUMERIC(8,3)(4)NULL值与DEFAULT值DEFAULT值表示某一字段的默认值，当没有输入数据时，则使用此默认的值。例

建立一学生表USESTUDENTCREATETABLES(SNOCHAR(8),SNVARCHAR(20),AGEINT,SEXCHAR(2)DEFAULT'男',DEPTVARCHAR(20));执行该语句后，便产生了学生基本表的表框架，此表为一个空表。其中，SEX列的缺省值为“男”。

定义完整性约束上述为创建基本表的最简单形式，还可以对表进一步定义，如主键、空值的设定，使数据库用户能够根据应用的需要对基本表的定义做出更为精确和详尽的规定。在SQLSERVER中，对于基本表的约束分为列约束和表约束。列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用’,’分隔，定义表约束时必须指出要约束的那些列的名称。完整性约束的基本语法格式为： [CONSTRAINT<约束名>]<约束类型>约束名：约束不指定名称时，系统会给定一个名称。约束类型：在定义完整性约束时必须指定完整性约束的类型。在SQLSERVER中可以定义五种类型的完整性约束，下面分别加以介绍：（1）NULL/NOTNULL例

建立一个S表，对SNO字段进行NOTNULL约束。USESTUDENTCREATETABLES(SNOCHAR(10)（CONSTRAINTS_CONS）NOTNULL，SNVARCHAR(20),AGEINT,SEXCHAR(2)DEFAULT’男’,DEPTVARCHAR(20));（2）UNIQUE约束UNIQUE约束用于指明基本表在某一列或多个列的组合上的取值必须唯一。定义了UNIQUE约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。唯一键允许为空，但系统为保证其唯一性，最多只可以出现一个NULL值。UNIQUE既可用于列约束，也可用于表约束。UNIQUE用于定义列约束时，其语法格式如下： [CONSTRAINT<约束名>]UNIQUE例

建立一个S表，定义SN为唯一键。USESTUDENTCREATETABLES(SNOCHAR(6),SNCHAR(8)[CONSTRAINTSN_UNIQ]UNIQUE,SEXCHAR(2),AGENUMERIC(2));UNIQUE用于定义表约束例3.7建立一个S表，定义SN+SEX为唯一键。USESTUDENTCREATETABLES(SNOCHAR(5),SNCHAR(8),SEXCHAR(2),[CONSTRAINTS_UNIQ]UNIQUE(SN,SEX));（3）PRIMARYKEY约束PRIMARYKEY约束用于定义基本表的主键，起唯一标识作用，其值不能为NULL，也不能重复，以此来保证实体的完整性。PRIMARYKEY与UNIQUE约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别：①在一个基本表中只能定义一个PRIMARYKEY约束，但可定义多个UNIQUE约束；②对于指定为PRIMARYKEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。注意：不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARYKEY约束。PRIMARYKEY既可用于列约束，也可用于表约束例

建立一个S表，定义SNO为S的主键USESTUDENTCREATETABLES(SNOCHAR(5)NOTNULLCONSTRAINTS_PRIMPRIMARYKEY,SNCHAR(8),AGENUMERIC(2));例

建立一个SC表，定义SNO+CNO为SC的主键USESTUDENTCREATETABLESC(SNOCHAR(5)NOTNULL,CNOCHAR(5)NOTNULL,SCORENUMERIC(3),CONSTRAINTSC_PRIMPRIMARYKEY(SNO,CNO));（4）FOREIGNKEY约束FOREIGNKEY约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为从表，包含外部键所引用的主键或唯一键的表称主表。系统保证从表在外部键上的取值要么是主表中某一个主键值或唯一键值，要么取空值。以此保证两个表之间的连接，确保了实体的参照完整性。FOREIGNKEY既可用于列约束，也可用于表约束例

建立一个SC表，定义SNO,CNO为SC的外部键。USESTUDENTCREATETABLESC(SNOCHAR(5)NOTNULLCONSTRAINTS_FOREFOREIGNKEYREFERENCESS(SNO),CNOCHAR(5)NOTNULLCONSTRAINTC_FOREFOREIGNKEYREFERENCESC(CNO),SCORENUMERIC(3),CONSTRAINTS_C_PRIMPRIMARYKEY(SNO,CNO));（5）CHECK约束CHECK约束用来检查字段值所允许的范围，如，一个字段只能输入整数，而且限定在0-100的整数，以此来保证域的完整性。CHECK既可用于列约束，也可用于表约束例

建立一个SC表，定义SCORE的取值范围为0到100之间。USESTUDENTCREATETABLESC(SNOCHAR(5),CNOCHAR(5),SCORENUMERIC(5,1)CONSTRAINTSCORE_CHKCHECK(SCORE>=0ANDSCORE<=100));例

建立包含完整性定义的学生表USESTUDENTCREATETABLES(SNOCHAR(6)CONSTRAINTS_PRIMPRIMARYKEY,SNCHAR(8)CONSTRAINTSN_CONSNOTNULL,AGENUMERIC(2)CONSTRAINTAGE_CONSNOTNULLCONSTRAINTAGE_CHKCHECK(AGEBETWEEN15AND50),SEXCHAR(2)DEFAULT'男',DEPTCHAR(10)CONSTRAINTDEPT_CONSNOTNULL);修改基本表表结构的修改完整性约束ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][ALTERCOLUMN<列名><数据类型>]1.ADD方式例

在S表中增加一个班号列和住址列。USESTUDENTALTERTABLESADDCLASS_NOCHAR(6),ADDRESSCHAR(40)注意：使用此方式增加的新列自动填充NULL值，所以不能为增加的新列指定NOTNULL约束

。例

在SC表中增加完整性约束定义，使SCORE在0-100之间。USESTUDENTALTERTABLESCADDCONSTRAINTSCORE_CHKCHECK(SCOREBETWEEN0AND100)2.ALTER方式例

把S表中的SNO列加宽到8位字符宽度USESTUDENTALTERTABLESALTERCOLUMNSNOCHAR(8)3.DROP方式例

删除S表中的AGE_CHK约束USESTUDENTALTERTABLESDROPCONSTRAINTAGE_CHK改变基本表的名字使用RENAME命令，可以改变基本表的名字，其语法格式为： RENAME<旧表名>TO<新表名>例

将S表的名字更改为STUDENTUSESTUDENT RENAMESTOSTUDENT删除基本表删除后，该表中的数据和在此表上所建的索引都被删除，而建立在该表上的视图不会随之删除，系统将继续保留其定义，但已无法使用。如果重新恢复该表，这些视图可重新使用。DROPTABLE<表名>[RESTRICT|CASCADE];例

删除表STUDENTUSESTUDENT DROPTABLESTUDENTCASCADE;注：CASCADE不仅将表中的数据和表结构删除，而且会将其上的索引、视图、触发器等删除；RESTRICT:如果删除的表和其他表有约束，有视图，触发器等时，则无法删除。缺省情况为:RESTRICT设计、创建和维护索引索引的作用在日常生活中我们会经常遇到索引，例如图书目录、词典索引等。借助索引，人们会很快地找到需要的东西。索引是数据库随机检索的常用手段，它实际上就是记录的关键字与其相应地址的对应表。例如，当我们要在本书中查找有关“SQL查询”的内容时，应该先通过目录找到“SQL查询”所对应的页码，然后从该页码中找出所要的信息。这种方法比直接翻阅书的内容要快。如果把数据库表比作一本书，则表的索引就如书的目录一样，通过索引可大大提高查询速度。此外，在SQLSERVER中，行的唯一性也是通过建立唯一索引来维护的。

索引的作用可归纳为：1.加快查询速度；2.保证行的唯一性。索引的分类1.按照索引记录的存放位置可分为聚集索引与非聚集索引聚集索引：按照索引的字段排列记录，并且依照排好的顺序将记录存储在表中。非聚集索引：按照索引的字段排列记录，但是排列的结果并不会存储在表中，而是另外存储。2.唯一索引的概念唯一索引表示表中每一个索引值只对应唯一的数据记录，这与表的PRIMARYKEY的特性类似，因此唯一性索引常用于PRIMARYKEY的字段上，以区别每一笔记录。当表中有被设置为UNIQUE的字段时，SQLSERVER会自动建立一个非聚集的唯一性索引。而当表中有PRIMARYKEY的字段时，SQLSERVER会在PRIMARYKEY字段建立一个聚集索引。3.复合索引的概念复合索引是将两个字段或多个字段组合起来建立的索引，而单独的字段允许有重复的值。建立索引建立索引的语句是CREATEINDEX，其语法格式为： CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[次序][{,<列名>}][次序]…)UNIQUE表明建立唯一索引。CLUSTER表示建立聚集索引。

次序用来指定索引值的排列顺序，可为ASC（升序）或DESC（降序），缺省值为ASC。例

为表SC在SNO和CNO上建立唯一索引。USESTUDENTCREATEUNIQUEINDEXSCIONSC(SNO[ASC],CNO[DESC])此索引为SNO和CNO两列的复合索引，即对SC表中的行先按SNO的递增顺序索引，对于相同的SNO，又按CNO的递增顺序索引。由于有UNIQUE的限制，所以该索引在(SNO,CNO)组合列的排序上具有唯一性，不存在重复值。删除索引建立索引是为了提高查询速度，但随着索引的增多，数据更新时，系统会花费许多时间来维护索引。这时，应删除不必要的索引。删除索引的语句是DROPINDEX，其语法格式为： DROPINDEX数据表名.索引名例3.20删除表SC的索引SCI。

DROPINDEXSC.SCI§3SQL数据查询功能表或者视图查询的结果是仍是一个表。SELECT语句的执行过程是:根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。如果有GROUP子句，则将查询结果按照<列名1>相同的值进行分组。如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。如果有ORDER子句，查询结果还要按照<列名2>的值进行排序。SQL数据查询功能例

查询全体学生的学号、姓名和年龄。

SELECTSNO,SN,AGEFROMS例

查询学生的全部信息。

SELECT*FROMS用‘*’表示S表的全部列名，而不必逐一列出。

例

查询选修了课程的学生号。

SELECT

DISTINCTSNOFROMSC查询结果中的重复行被去掉上述查询均为不使用WHERE子句的无条件查询，也称作投影查询。另外，利用投影查询可控制列名的顺序，并可通过指定别名改变查询结果的列标题的名字。例

查询全体学生的姓名、学号和年龄。

SELECTSNAME[AS]NAME,SNO,AGEFROMS其中，NAME为SNAME的别名条件查询当要在表中找出满足某些条件的行时，则需使用WHERE子句指定查询条件。WHERE子句中，条件通常通过三部分来描述：1．

列名；2．

比较运算符；IN,BETWEEN,LKIEEXISTS…3．

列名、常数。运算符含义=,>,<,>=,<=,!=比较大小多重条件AND,ORBETWEENAND确定范围IN确定集合LIKE字符匹配ISNULL空值比较大小例

查询选修课程号为‘C1’的学生的学号和成绩。SELECTSNO,SCOREFROMSCWHERECNO=’C1’例

查询成绩高于85分的学生的学号、课程号和成绩。SELECTSNO,CNO,SCOREFROMSCWHERESCORE>85多重条件查询当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。其优先级由高到低为：NOT、AND、OR，用户可以使用括号改变优先级。例

查询选修C1或C2且分数大于等于85分学生的的学号、课程号和成绩。SELECTSNO，CNO，SCOREFROMSCWHERE（CNO=’C1’ORCNO=’C2’）ANDSCORE>=85确定范围例

查询工资在1000至1500之间的教师的教师号、姓名及职称。SELECTTNO,TN,PROFFROMTWHERESALBETWEEN1000AND1500等价于SELECTTNO,TN,PROFFROMTWHERESAL>=1000ANDSAL<=1500例

查询工资不在1000至1500之间的教师的教师号、姓名及职称。SELECTTNO,TN,PROFFROMTWHERESALNOTBETWEEN1000AND1500确定集合利用“IN”操作可以查询属性值属于指定集合的元组。例

查询选修C1或C2的学生的学号、课程号和成绩。SELECTSNO,CNO,SCOREFROMSCWHERECNOIN(‘C1’,‘C2’)此语句也可以使用逻辑运算符“OR”实现。SELECTSNO,CNO,SCOREFROMSCWHERECNO=‘C1’

ORCNO=‘C2’利用“NOTIN”可以查询指定集合外的元组。

例

查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。SELECTSNO,CNO,SCOREFROMSCWHERECNONOTIN(‘C1’,‘C2’)等价于：SELECTSNO,CNO,SCOREFROMSCWHERECNO!=‘C1’ANDCNO!=‘C2’部分匹配查询上例均属于完全匹配查询，当不知道完全精确的値时，用户还可以使用LIKE或NOTLIKE进行部分匹配查询（也称模糊查询）。LIKE定义的一般格式为：<属性名>LIKE<字符串常量>[ESCAPE’<换码字符>’]属性名必须为字符型，字符串常量的字符可以包含如下两个特殊符号：%：表示任意长度的字符串；_：表示任意单个字符。如果用户要查询的字符串本身就含有通配符%或_，这时就要使用ESCAPE，对通配符进行转义例

查询所有姓张的教师的教师号和姓名。SELECTTNO,TNFROMTWHERETNLIKE‘张%’例

查询姓名中第二个汉字是“力”的教师号和姓名。SELECTTNO,TNFROMTWHERETNLIKE‘__力%’注：一个汉字占两个字符。例

查询“DB_Design”课程的课程号和学分Selectcno,ccreditFromcourseWherecnamelike‘DB\_Design’escape‘\’;例

查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况Select*FromcourseWherecnamelike‘DB\_%i__’escape‘\’空值查询某个字段没有值称之为具有空值（NULL）。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。

例

查询没有考试成绩的学生的学号和相应的课程号。SELECTSNO,CNOFROMSCWHERESCOREISNULL注意：这里的空值条件为ISNULL，不能写成SCORE=NULL。查询的排序当需要对查询结果排序时，应该使用ORDERBY子句ORDERBY子句必须出现在其他子句之后排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序例

查询选修C1的学生学号和成绩，并按成绩降序排列。SELECTSNO,SCOREFROMSCWHERECNO='C1'ORDERBYSCOREDESC;例

查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。SELECTSNO,CNO,SCOREFROMSCWHERECNOIN('C2','C3','C4','C5')ORDERBYSNO,SCOREDESC;常用库函数及统计汇总查询SQL提供了许多库函数，增强了基本检索能力。常用的库函数，如表所示函数名称功能AVG(column)按列计算平均值SUM(column)按列计算值的总和MAX(column)求一列中的最大值MIN(column)求一列中的最小值COUNT（*|column）按列值或者行计个数注意：AVG([DISTINCT|ALL]<列名>)，缺省值为ALL另外，当函数为空值时，除COUNT(*)外，都跳过空值而只处理非空值例

求学号为S1学生的总分和平均分。SELECTSUM(SCORE)[AS]TotalScore,AVG(SCORE)[AS]AveScoreFROMSCWHERESNO='S1'注意：函数SUM和AVG只能对数值型字段进行计算。例

求选修C1号课程的最高分、最低分及之间相差的分数SELECTMAX(SCORE)ASMaxScore,MIN(SCORE)ASMinScore,MAX(SCORE)-MIN(SCORE)ASDiffFROMSCWHERECNO='C1'例

求计算机系学生的总数SELECTCOUNT(SNO)FROMSWHEREDEPT='计算机'例

求学校中共有多少个系SELECTCOUNT(DISTINCTDEPT)ASDeptNumFROMS注意：加入关键字DISTINCT后表示消去重复行，可计算字段“DEPT“不同值的数目。COUNT函数对空值不计算，但对零进行计算。例

统计有数据库(002)成绩同学的人数SELECTCOUNT(SCORE)FROMSCWherecno=‘002’;上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。例

利用特殊函数COUNT(*)求计算机系学生的总数SELECTCOUNT(*)FROMSWHEREDEPT=‘计算机’COUNT（*）用来统计元组的个数不消除重复行，不允许使用DISTINCT关键字。分组查询GROUPBY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。分组后的集聚函数将作用于每一个租，即每一组都有一个函数值。例

查询各位教师的教师号及其任课的门数。SELECTTNO,COUNT(*)ASC_NUMFROMTCGROUPBYTNOGROUPBY子句按TNO的值分组，所有具有相同TNO的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。

例

查询选修两门以上课程的学生学号和选课门数SELECTSNO,COUNT(*)ASSC_NUMFROMSCGROUPBYSNOHAVINGCOUNT(*)>=2GROUPBY子句按SNO的值分组，所有具有相同SNO的元组为一组，对每一组使用函数COUNT进行计算，统计出每位学生选课的门数。HAVING子句去掉不满足COUNT（*）>=2的组。当在一个SQL查询中同时使用WHERE子句，GROUPBY

子句和HAVING子句时，其顺序是WHERE－GROUPBY－HAVING。WHERE与HAVING子句的根本区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组；HAVING子句作用于组，选择满足条件的组，必须用于GROUPBY子句之后，但GROUPBY子句可没有HAVING子句。可用一些统计功能函数例

求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。SELECTSNO,SUM(SCORE)ASTotalScoreFROMSCWHERESCORE>=60GROUPBYSNOHAVINGCOUNT(*)>=3ORDERBYSUM(SCORE)DESC此语句为分组排序，执行过程如下：1.（FROM）取出整个SC2.（WHERE）筛选SCORE>=60的元组3.（GROUPBY）将选出的元组按SNO分组4.（HAVING）筛选选课三门以上的分组5.（SELECT）以剩下的组中提取学号和总成绩6.（ORDERBY）将选取结果排序ORDERBYSUM(SCORE)DESC可以改写成 ORDERBY2DESC

2代表查询结果的第二列。多表连接查询数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。前面的查询都是针对一个表进行的，当查询同时涉及两个以上的表时，称为连接查询。表的连接方法：表之间满足一定的条件的行进行连接，此时FROM子句中指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。例

查询刘伟老师所讲授的课程SELECTT.TNO,TN,CNOFROMT,TCWHERE

T.TNO=TC.TNO

AND

TN=‘刘伟’这里，TN=‘刘伟’为查询条件，而T.TNO=TC.TNO为连接条件，TNO为连接