数据库基础讲义word版

数据库数据库(Database，DB)是长期存储在计算机内的、有组织的、可共享的、统一管理的相关数据的集合。按照字面的理解，数据库就是存放数据的仓库。数据库管理系统(DBMS)DBMS的功能：数据库定义功能；数据存取功能；数据库运行管理功能；数据库的建立和维护功能；数据通信功能。DBMS的组成：语言编译处理程序；系统运行控制程序；系统建立、维护程序；数据字典。数据库系统的组成数据：数据是数据库系统的工作对象，它们是某特定应用环境中进行管理和决策所必需的信息。用户：用户是指存储、维护和检索数据库中数据的人员。数据库系统中主要有3类用户：终端用户、应用程序员和数据库管理员。硬件：硬件是指存储数据库和运行数据库管理系统DBMS的硬件资源。软件：软件是指负责数据库存取、维护和管理的软件系统，通常叫做数据库管理系统(DBMS)。数据库系统的特点数据低冗余、共享性高。数据独立性提高。有统一的数据控制功能。数据库应用系统数据库应用系统(DatabaseApplicationSystem，DBAS)，是指在BMS的基础上，针对一个实际问题开发出来的面向用户的系统。数据库系统的体系结构内部体系结构：数据库系统得内部体系结构是三级模式结构，分别为模式、外模式和内模式。外部体系结构：外部体系结构主要有集中式结构、文件服务器结构和客户/服务器结构。概念模型实体与实体集：实体是现实世界中可区别于其他对象的“事件”或物体；实体集是具有相同类型及共享相同性质(属性)的实体集合。属性：实体通过一组属性来表示；属性是实体集中每个成员具有的描述性性质。关键字和域：实体的某一属性或属性组合，其值能惟一标识出某一实体，称为关键字，也称码；每个属性都有一个可取值的集合，称为该属性的域，或者该属性的值集；联系：两个实体之间的联系又可分为一对一联系(1:1)、一对多联系(1:n)和多对多的联系(m:n)。常用的数据模型层次模型：层次数据模型是数据库系统最早使用的一种模型，它的数据结构是一颗有向树。层次结构模型具有如下特征：有且仅有一个结点没有双亲，该结点是根结点。其他结点有且仅有一个双亲。网状模型：如果取消层次模型的两个限制，即两个或两个以上的结点都可以有多个双亲，则“有向树”就变成了“有向图”。“有向图”结构描述了网状模型。网状模型具有如下特征：可有一个以上的结点没有双亲。至少有一个结点可以有多于一个双亲。关系模型：关系模型(RelationalModel)是用二维表格结构来表示实体及实体之间联系的数据模型。关系模型的数据结构是一个“二维表框架”组成的集合，每个二维表又可称为关系，因此可以说，关系模型是“关系框架”组成的集合。关系模型是使用最广泛的数据模型，目前大多数数据库管理系统都是关系型的，如Access、sqlserver、oracle都是关系数据库管理系统。1.关系数据库关系模型中的基本术语关系的性质关系数据库中的表之间的关系关系模型的完整性约束关系模型中的基本术语关系：一个关系就是一张二维表。元组：二维表中的每一条记录就是一个元组。属性：二维表中的一列就是一个属性，又称为字段。域：属性的取值范围。分量：元组中的一个属性值。关系模式：对关系的描述。候选关键字：关系中的一个或几个属性的集合，该属性集惟一标识一个元组。关系数据库：对应于一个关系模型的所有关系的集合称为关系数据库。主关键字：一个关系中有多个候选关键字，可以选择其中一个作为主关键字。外部关键字：如果一个属性组不是所在关系的关键字，但它是其他关系的关键字，则该属性组称为外部关键字。主属性：包含在任一候选关键字中的属性称为主属性。关系的性质关系是一个二维表，但并不是所有的二维表都是关系。关系应具有以下性质：每一列中的分量是同一类型的数据。不同的列要给予不同的属性名。列的次序可以任意交换。一个关系中的任意两个元组不能完全相同。行的次序可以任意交换。每一个分量必须是不可分的数据项。关系数据库中的表之间的关系在关系数据库中，可以通过外部关键字实现表与表之间的联系，公共字段是一个表的主键和另一个表的外键。关系模型的完整性约束实体完整性(EntityIntegrity)：若属性（指一个或一组属性）A是基本关系R的主属性，则A不能取空值。所谓空值就是“不知道”或“不存在”的值。参照完整性(ReferentialIntegrity)：若属性（或属性组）F是基本关系R的外键，它与基本关系S的主键Ks相对应（基本关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须为空或是等于S中某个元组的主键值。用户定义的完整性(User-definedIntegrity)：用于定义的完整性就是针对某一具体关系数据库的约束条件。2、关系代数传统的集合运算传统的集合运算是二目运算，包括并、差、交、笛卡尔积4种运算。关系R和关系S的并运算关系R和关系S的差关系R和关系S的交两个分别具有n和m个属性的关系R和S的笛卡尔积是一个（n+m）列的元组的集合。元组的前n列是关系R的一个元组，后m列是关系S的一个元组。若R有k1个元组，S有k2个元组，则关系R和关系S的笛卡尔积有k1k2元组。专门的关系运算:包括选择、投影、连接、除运算等。选择：从一个关系中选出满足给定条件的记录的操作称为选择或筛选。选择是从行的角度进行的运算;投影：从一个关系中选出若干指定字段的值。投影是从列的角度进行的运算，所得到的字段个数通常比原关系少，或者字段的排列顺序不同;连接：把两个关系中的记录按一定条件横向结合，生成一个新的关系;除：元组在X上分量值x的象集Yx包含S在Y上投影的集合。3、数据类型与运算上例中的二维表格列叫做“字段”，行叫做“记录”，对应某行某列对应了指定的一个“元素”。这样就组成了一张张的关系表格。每个字段都需起个名字，叫字段名，字段名称最长可达到64个字符（包括空格）。每个字段存储的数据有不同的数据类型，同一字段数据类型必须一致。常见数据类型主要有：1、文本型(c):最大长度255个字符；2、数字型(n)，常用的有： (1)整型：整数字段。 (2)长整型：整数字段，当该字段设为自动编号字段时必须是长整型。 (3)单精度： (4)双精度： (5)小数：小数字段存储的小数长度18位（含小数点及前后所有位数） 一般情况下，数字型字段我们都选双精度，小数位数2位。3、日期时间型：一般选择常规日期即可。会计帐表中的日期为短日期。4、逻辑型：长度为1，存储（true）“真”或（false）“假”。5、备注型：长度6，直存储指向备注文件的一个指针，字段的实际内容存储在备注文件中。 程序中变量数据类型基本相同。在asl语言中对变量内容进行了扩充，可以是“通用型”，变量除了有以上数据类型意外，变量的内容可以是一个数据集（一个查询的结果）。如：VaraBigena:=createQ(‘select*from学生档案数据where专业like‘计算机应用’’)……End.上述程序中的CREATEQ()创建的查询结果赋给了变量a，就可以象操作一个查询一样操作a。SQL语言运算符运算符运算符：是一种符号，用来指定在一个或多个表达式中执行的操作运算符分类：1）算术运算符2）赋值运算符3）比较运算符4）逻辑运算符5）字符运算符*6）按位运算符*7）一元运算符一、算术运算符连接两个式子进行数学运算的符号运算符包括：加，减，乘，除，取模(求余数）例：select3+5结果：8select16%3结果：1selectCAST('2010-1-1'ASDATETIME)+100AS'原日期加100后的日期'结果：原日期加100后的日期====================2010-04-1100:00:00.000说明：CAST。。AS。。：用来将一种数据类型转换为另一种类型二、赋值运算符（=号）将表达式的值赋给另一个变量格式：SET<变量名>=表达式例：useschoolselectsno='学生',sname,classfromSTUDENTSNOSNAMECLASS=====================学生王二计算机系学生张三化生系学生李四经管系。。。例：useschoolselectsno,sname,class='计算机系'fromSTUDENTWHERECLASS='C01'在asl语言中，常量赋值用“=”，变量赋值用“：=”三、比较运算符比较两个表达式，其结果可能是真：TRUE(T)或假：FALSE(F)比较符包括：>大于，<小于，=等于，>=大于等于，<=小于等于，<>不等于，=!不等于,!>不大于，!<不小于例：useschoolselect*fromSTUDENTwhereclass="计算机系"gouseschoolselect*fromscorewheredegree>80四、逻辑运算符判断多个关系比较的连接运算符号,其运算结果:可能是真TRUE或是假FLASE逻辑运算符包括：1）AND:与运算2）OR:或运算3）NOT:非运算4）ALL:所有关系式全为真时,返回TRUE5）ANY:只要比较关系中有一个值为TRUE,就返回TRUE6）BETWEEN:对操作数在指定范围内,返回TRUE7）IN:对操作数在所给定的具体值时,返回TRUE8）LIKE:如果操作数与模式匹配时,返回TRUE例：查询学生表中所有计算机系的男生记录CLASS="计算机系"ANDSSEX="男"查询成绩表中课号为C01成绩大于80分的学生记录DEGREE>90ADNCNO="C01"查询成绩表中成绩在80到100之间的学生记录BETWEEN80AND100查询计算机系与物理系的学生记录IN("计算机系","物理系")五、字符串运算符将两个字符串连接起来的符号：“+”例：SELECT('AAA'+'BBB')AS'两个字符串相加'结果：两个字符串相加================AAABBB六、运算优先级乘*、除/、取模%加+、连接+、减-大于、小于、等于(=,>,<,>=,<=,<>,!=,!>,!<)NOTANDALL、NAY、BETWEEN、IN、LIKE、OR、SOME说明：可用括号来提高优先级，一个表达式中它首先对括号内的内容进行运算。对于多个括号嵌套情况，最内括号中的式子首先运算。例：DECLARE@XXXINTSET@XXX=3*(5+(7-3))SELECT@XXXAS'计算结果'计算结果========27Sql查询单表查询使用合计函数连接查询子查询单表查询选择表中的若干列选择表中的若干元组ORDERBY子句GROUPBY子句选择表中的若干列查询指定列：查询表中指定的若干列，例如，以下SQL语句查询全体学生的姓名和年龄SELECTSno,SageFROMStudent;查询全部列，例如SELECT*FROMStudent;查询经过计算的值，例如，以下SQL语句查询全体学生的姓名及其出生年份：SELECTSname,2008-SageFROMStudent;选择表中的若干元组消除取值重复的行：两个本来并不完全相同的元组，投影到指定的某些列后，可能变成相同的行了，这时可以用DISTINCT取消他们。查询满足指定条件的行，可以通过WHERE子句来实现。ORDERBY子句用户可以用ORDERBY子句对查询结果按照一个或多个字段的升序或降序排列，缺省值为升序。以下SQL语句查询选修了2号课程的学生的学号及其成绩，查询结果按分数的降序排列：SELECTSno,GradeFROMSCWHERECno=’2ORDERBYGradeDESC;对于空值，若按升序排列，含空值的元组将最后显示。若按降序排列，空值的元组将最先显示。GROUPBY子句GROUPBY子句将查询结果按某一列或多列的值分组，值相等的为一组。如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，可以使用HAVING关键字指定筛选条件。使用合计函数合计函数功能COUNT([DISTINCT|ALL]*)统计元组个数COUNT([LDISTINCT|ALL]<列名>)统计一列中值的个数SUM([DISTINCT|ALL]<列名>)计算一列值的总和(此列必须是数值型)AVG([DISTINCT|ALL]<列名>)计算一列值的平均值(此列必须是数值型)MAX([DISTINCT|ALL]<列名>)求一列值中的最大值MIN([DISTINCT|ALL]<列名>)求一列值中的最小值连接查询连接查询也叫多表查询，在实际应用过程中经常需要同时从两个表或者两个以上的表中检索数据。连接查询允许通过指定表中某个或者某些列作为连接条件，同时从两个表或者多个表中检索数据。连接查询可以使用两种连接语法形式，一种是ANSI连接语法形式，它的连接条件写在FROM子句中，另外一种是SQLServer连接语法形式，它的连接条件写在on子句中。1、where子句的多表查询。在多表中查询所需要的字段。Selecta.字段1,a.字段2,b.字段1,b.字段2…From表1asa,表2asb,….Wherea.字段n=b.字段mand….Jion子句1、leftjion左连接：包括左表中所有记录和右表中连接字段相等的记录；2、rightjion右连接：包括右表中所有记录和左表中连接字段相等的记录；3、innerjion内连接：只包含两个表中连接字段相等的行。Jion连接语法格式：Selecta.字段1,a.字段2,….b.字段1,b.字段2,….,c.字段1,…From表1asa[left、right、inner]Jion表2asbona.字段n=b.字段m[left、right、inner]Jion表3ascona.字段n=C.字段m……注意：确定连接条件对应关系的唯一性。也就是说on子句确定的条件必须有唯一对应分量（元素）。否则会出现大量重复数据。地税连接的一个例子（可以对一个表做多次连接selectdistincta.纳税人,a.行业,a.行业大类,a.征收项目,b.税额合计as一月,c.税额合计as二月,d.税额合计as三月,e.税额合计as四月,f.税额合计as五月,g.税额合计as六月,h.税额合计as七月,i.税额合计as八月,j.税额合计as九月,k.税额合计as十月,l.税额合计as十一月,m.税额合计as十二月into单位汇总表from((((((((((((dwxxasaleftjoinhzsjasbon(a.纳税人=b.纳税人anda.征收项目=b.征收项目andb.入库年=2010andb.入库月=1))leftjoinhzsjascon(a.纳税人=c.纳税人anda.征收项目=c.征收项目andc.入库年=2010andc.入库月=2))leftjoinhzsjasdon(a.纳税人=d.纳税人anda.征收项目=d.征收项目andd.入库年=2010andd.入库月=3))leftjoinhzsjaseon(a.纳税人=e.纳税人anda.征收项目=e.征收项目ande.入库年=2010ande.入库月=4))leftjoinhzsjasfon(a.纳税人=f.纳税人anda.征收项目=f.征收项目andf.入库年=2010andf.入库月=5))leftjoinhzsjasgon(a.纳税人=g.纳税人anda.征收项目=g.征收项目andg.入库年=2010andg.入库月=6))leftjoinhzsjashon(a.纳税人=h.纳税人anda.征收项目=h.征收项目andh.入库年=2010andh.入库月=7))leftjoinhzsjasion(a.纳税人=i.纳税人anda.征收项目=i.征收项目andi.入库年=2010andi.入库月=8))leftjoinhzsjasjon(a.纳税人=j.纳税人anda.征收项目=j.征收项目andj.入库年=2010andj.入库月=9))leftjoinhzsjaskon(a.纳税人=k.纳税人anda.征收项目=k.征收项目andk.入库年=2010andk.入库月=10))leftjoinhzsjaslon(a.纳税人=l.纳税人anda.征收项目=l.征收项目andl.入库年=2010andl.入库月=11))leftjoinhzsjasmon(a.纳税人=m.纳税人anda.征收项目=m.征收项目andm.入库年=2010andm.入库月=12))说明：通过上述连接生成易于审计人员查看的横排的纳税人年度纳税台账。Union合并查询（纵向）Union实现了多个查询结果的纵向合并Select字段1,字段2,字段3,…..From表1UnionSelect字段1,字段2,字段3,…..From表2UnionSelect字段1,字段2,字段3,…..From表3注意事项：1、Union合并的查询结果纵向对应字段类型要一致，即字段的数据类型要一致，取值范围一致，否则出现类型不匹配等错误。2、注意查询结果字段的先后顺序和前后查询结果字段的对应顺序。子查询带有IN和BETWEEN的子查询带有比较运算符的子查询带有ANY(SOME)或ALL谓词的子查询IN和BETWEENIN查询1、指定字段与条件列表比较:Select字段1,字段2,字段3From表1WHERE字段1IN(‘张三’,‘李四’,‘王五’,‘赵六');2、指定字段与查询的集合比较：Select字段1,字段2,字段3,字段4From表1Where字段1in（select*from表2）3、notin用法同in。BetweenSELECT字段1,SUM(字段2)

FROM表1

WHERE字段3BETWEEN值1AND值2;等同于：SELECT字段1,SUM(字段2)

FROM表1

WHERE字段3>=值1AND字段3<=值2;带有比较符的子查询在SELECT查询语句中再包括一个SELECT子查询语句使用比较符。例：查所购物品比平均价格高出100的顾客。

SELECT顾客id

FROM购物信息表

WHERE价格>

(SELECTAVG(价格)+100

FROM购物信息表);exists和all（Any）EXISTS使用了一个子查询作为条件，只有当子查询返回行的时候这个条件才为真，如果子查询不返回任何的行条件就为假。如果商店在处理Chair的时候，有个顾客想看看所有拥有者的列表，就可以使用EXSIST，语句如下：

SELECT第一客户名,最后客户名

FROM拥有客户表

WHEREEXISTS(SELECT*FROM拥有商品表

WHERE品名=‘Chair’);

如果在“拥有商品表”列中有Chair，那么子查询就会返回一行或者多行，就使得EXISTS子句为真，然后让SQL列出拥有者来。如果没有搜索到Chair，则没有行被返回，条件就为假。ALL是另外一个不寻常的关键字，因为ALL查询通常可以用不同的方法来进行，并且可能是一种更为简单的方法。SELECTBUYERID,ITEM

FROMANTIQUES

WHEREPRICE>=ALL

(SELECTPRICE

FROMANTIQUES);

上面这条语句将返回最高价格的Item以及它的买方。子查询返回了Antiques表中的所有的Price列，而外层的查询逐行查询Antiques表，并且如果它的Price大于等于（或者ALL）列中的Prices，它就会被列出，它就是最好价格的Item。这里必须使用">="的原因是最高价格的Item要等于列表中的最高价格，因为这个Item在Price列中。上述语句可以写成：SELECTBUYERID,ITEM,MAX(PRICE) FROMANTIQUES groupbybuyerid,item取最大值的聚合函数，与groupby配合使用，计算并显示分组字段的最大值；否则语法错误。Min()、count(*)等函数同。SQL数据操纵功能INSERT命令UPDATE命令DELETE命令INSERT命令完全添加，语法格式如下：INSERTINTO表名VALUES(第一个字段值,...,最后一个字段值)部分添加，语法格式如下：INSERTINTO表名(字段1,…,字段N,…)VALUES(第一个字段值,...,第N个字段值,…)UPDATE命令UPDATE