商务谈判课件

关系数据库标准语言SQL4.1SQL概述StructuredQueryLanguage结构化查询语言介于关系代数与关系演算之间，功能包括：

数据查询、数据操纵、数据定义、数据控制

已成为关系数据库领域中一个主流语言发展过程

1974年由Boyce和Chamberlin提出

1975-1979年在IBM研制的System-R上实现

SQL-86——

第一个SQL标准

SQL-89——

第二次公布SQL标准

SQL-92——

第三次公布SQL标准

SQL-99——

第四次公布SQL标准（SQL3）SQL的特点

一种一体化的语言SQL具有集数据查询、数据操纵、数据定义、数据控制为一体的特点。一种高度非过程化的语言面向集合的操作方式同一种语法结构提供两种使用方法联机交互使用方式——

自含式语言嵌入主语言的使用方式——

嵌入式语言语言简洁、易学易用SQL的命令SQL功能命令动词数据定义CREATE，DROP，ALTER数据操纵SELECTINSERT，UPDATE，DELETE数据控制GRANT，REVOKESQL语言的基本概念

基本表（basetable)

——

本身独立存在的表。在SQL中一个关系对应一个表。每个基本表逻辑上对应一个存储文件，可以带若干索引。视图（View）

——

从一个基本表或几个基本表或其他视图导出的表。视图本身不独立存储在数据库中，即数据库中只存放视图的定义，不存放它对应的数据。注意：在用户眼中，基本表和视图一样都是关系，都可以用SQL语言进行查询。是一个虚表SQL对数据库三级模式结构的支持SQL视图1存储文件1存储模式外模式模式存储文件2存储文件3存储文件4基本表1基本表2基本表3基本表4视图1用户4.2SQL的数据定义

SQL的数据定义功能包括：

定义基本表、定义视图、定义索引（还有：定义数据库、定义规则、定义存储过程）SQL的数据定义语句一、基本表的定义一般格式CREATETABLE表名（列定义[，列定义]...[其它参数]）；

其中，列定义

格式为：列名类型[列级完整性约束]

其它参数

定义表级完整性约束条件列级完整性约束如：不允许空值语句含义执行CREATE语句后，在数据库中建立一个表框架（空表），表的描述存于数据字典中。例：CREATETABLE订购单（职工号CHAR(2)，

供应商号CHAR(2)，订购单号CHAR(4)NOTNULL，订购日期DATE）；二、修改基本表的结构一般格式ALTERTABLE表名[ADD(列定义[，列定义]...)

[MODIFY(列名类型[，列名类型]...)]

[DROP完整性约束名]；增加新列修改原有的列定义删除指定的约束注意：无论基本表中原来是否有数据，新加的列一律为空值；不能修改属性列的名称；没有提供删除属性列的语句。例：ALTERTABLE订购单ADD（完成日期DATE）；

ALTERTABLE订购单MODIFY(职工号CHAR(4))；

三、删除基本表一般格式

DROPTABLE表名；语句含义执行DROP语句后，将基本表的定义(表框架)连同它的所有元组、索引以及由它导出的所有视图全部删除，并释放相应的存储空间。例DROPTABLE订购单；四、索引的定义和维护索引的作用加快数据查询速度保证数据的唯一性加快连接速度建立索引的原则由DBA(DBO)和DBMS完成索引的建立、删除和维护大表应该建立索引一个基本表的索引不要太多根据查询要求建立索引索引的建立和删除建立索引

CREATE[UNIQUE][CLUSTER]INDEX索引名

ON基本表名（列名[次序][，列名[次序]]...）；次序:索引值的排列次序，ASC|

DESC，缺省值为ASC。删除索引

DROPINDEX索引名；

索引的建立和删除例

CREATEUNIQUEINDEXpidxON订购单（订购单号）；

CREATEINDEXsupp_idxON订购单（供应商号）；

DROPINDEXsupp_idx；4.3SQL的数据查询

SQL数据查询语句的基本格式：

SELECT目标列——

要查询的内容

FROM基本表或视图——

涉及的关系名

[WHERE条件表达式]——

查询条件

[GROUPBY列名1[HAVING条件表达式]]——

分组

[ORDERBY列名2[ASC|DESC]]——

排序

[COMPUTE短语]；——

带明细的分组汇总

两个基本表仓库职工SQL数据查询——简单查询(1)检索工资多于1230元的职工号。

SELECT职工号

FROM职工

WHERE工资>1230；

SELECT*FROM职工

WHERE工资>1230；SQL数据查询——简单查询(2)检索选修课程的学生号。

SELECTDISTINCTSnoFROMSC；检索计算机科学系的学生姓名和出生年份。

SELECTSN,2005-SAFROMSWHERESD=‘CS’；SQL数据查询——连接查询找出工资多于1230元的职工号和他们所在的城市。

SELECT职工号，城市

FROM职工，仓库

WHERE(职工.仓库号=仓库.仓库号)

AND(工资>1230)；SQL数据查询——嵌套查询(1)检索选修DB课程的学生学号和名字。

SELECTSno，SNFROMSWHERESnoIN

SELECTSnoFROMSCWHERECnoIN

SELECTCnoFROMCWHERE

CN=‘DB’；

从内到外逐层处理C4S2,S3,S6SELECTSno，SNFROMS,C,SCWHERES.Sno=SC.Sno

ANDC.Cno=SC.CnoANDCN=‘DB’;SQL数据查询——嵌套查询(2)查询所有职工工资都多于1210元的仓库信息。

SELECT*FROM仓库WHERE仓库号NOTINSELECT仓库号

FROM职工

WHERE工资<=1210；WH1SQL数据查询——几种特殊算符WHERE子句的条件表达式中可以使用的几种形式：

列名BETWEEN低值AND高值列名NOTBETWEEN低值AND高值列名IN（值1，值2，……

）列名NOTIN（值1，值2，……

）列名LIKE字符串常量—可以用通配符‘%’和‘_’

列名NOTLIKE字符串常量列名ISNULL

列名ISNOTNULLSQL数据查询——相关子查询(1)与子查询相关的另外两种嵌套查询

形如：

[NOT]EXISTS（子查询）

即：检查在子查询中是否存在或不存在元组。表达式比较算符[ANY|ALL|SOME]（子查询）

即：对ANY、SOME，在比较运算中只要子查询中有一元组使结果为真，则结果为真；对ALL，要求子查询中的所有元组都使结果为真，结果才为真。SQL数据查询——相关子查询(2)检索选修C2课程的学生名。

SELECTSNFROMSWHEREEXISTS

（SELECT*FROMSCWHERESno=S.SnoANDCno=‘C2’）；

按外层元组的值逐个处理SQL数据查询——相关子查询(3)检索有职工工资大于WH1仓库中任意一职工工资的仓库号。

SELECTDISTINCT仓库号

FROM职工

WHERE工资>ANY

（SELECT工资

FROM职工

WHERE仓库号=‘WH1’）；1210，1250SQL数据查询——排序按职工工资值的降序列出全部职工的信息。

SELECT*FROM职工

ORDERBY工资DESC；SQL数据查询——库函数SQL为了增强检索功能，方便计算提供若干库函数：

COUNTSUMAVGMAXMIN给出WH1仓库的职工人数。

SELECTCOUNT（*）

FROM职工

WHERE仓库号=’WH1‘；给出WH1仓库的职工工资总和。

SELECTSUM（工资）

FROM职工

WHERE仓库号=‘WH1’；

SQL数据查询——分组(1)求每个仓库的职工的平均工资。

SELECT仓库号，AVG(工资)FROM职工

GROUPBY仓库号；

求有一个以上职工的仓库的平均工资。SELECT仓库号，COUNT(*)，AVG(工资)FROM职工GROUPBY仓库号

HAVINGCOUNT(*)>=2；SQL数据查询——分组(2)列出全部职工信息并计算各仓库的平均工资和工资小计，最后给出全体职工的平均工资和工资总和。

SELECT仓库号，职工号，工资

FROM职工

ORDERBY仓库号

COMPUTEAVG(工资)，SUM(工资)BY仓库号

COMPUTEAVG(工资)，SUM(工资)；带明细的分组汇总仓库号职工号工资WH1E31210WH1E7125012302460WH2E11220WH2E4125012352470WH3E61230123012301231.56160SQL数据查询——自连接查询查询每门课的间接先修课。

在同一个关系C中进行连接，为加以区分引入别名。

SELECTFIRST.Cno，SECOND.PCnoFROMCFIRST，CSECONDWHEREFIRST.Pcno=SECOND.Cno；FIRSTSECONDSQL数据查询——外部连接查询内连接：结果集中只保留符合连接条件的元组（一般、等值、自然）外部连接：查询结果集中保留非匹配元组左外部连接（*=）：结果集中保留连接表达式左表中非匹配元组右外部连接（=*）：结果集中保留连接表达式右表中非匹配元组例：外部连接查询工号姓名性别年龄部门1010李勇男20111011刘晨女191012王明女22121014张立男2113部门号部门名称电话11生产科56612计划科57813一车间46714科研所职工表部门表内连接SELECT职工.*，部门名称，电话FROM职工，部门WHERE职工.部门=部门.部门号；工号姓名性别年龄部门部门名称电话1010李勇男2011生产科5661012王明女2212计划科5781014张立男2113一车间467左外连接SELECT职工.*，部门名称，电话FROM职工，部门WHERE职工.部门*=部门.部门号；工号姓名性别年龄部门部门名称电话1010李勇男2011生产科5661011刘晨女191012王明女2212计划科5781014张立男2113一车间467右外连接SELECT职工.*，部门名称，电话FROM职工，部门WHERE职工.部门=*部门.部门号；工号姓名性别年龄部门部门名称电话1010李勇男2011生产科5661012王明女2212计划科5781014张立男2113一车间467科研所SQL数据查询—组合查询（并）求选修了C1或C2课程的学生学号SELECTSNOFROMSCOWHERECNO=‘C1’UNIONSELECTSNOFROMSCOWHERECNO=‘C2’；SQL数据查询—组合查询（交）求选修了C1和C2课程的学生学号SELECTSNOFROMSCOWHERECNO=‘C1’INTERSECTSELECTSNOFROMSCOWHERECNO=‘C2’；SQL数据查询—组合查询（差）求选修了C1但未选C2课程的学生学号SELECTSNOFROMSCOWHERECNO=‘C1’MINUSSELECTSNOFROMSCOWHERECNO=‘C2’；求选修了C2课程的学生姓名SELECTSNFROMSWHEREEXISTS(SELECT*FROMSCWHERES.Sno=SnoANDCno=‘C2’);SELECTSNFROMS,SCWHERES.Sno=SC.SnoANDCno=‘C2’;求没有选修C2课程的学生姓名SELECTSNFROMSWHERENOTEXISTS(SELECT*FROMSCWHERES.Sno=SnoANDCno=‘C2’);SELECTSNFROMS,SCWHERES.Sno=SC.SnoANDCno<>‘C2’;×查询选修了全部课程的学生姓名SELECTSNFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERES.Sno=SnoANDC.Cno=Cno));求至少选修了‘S2’所选修的全部课程的学生学号和姓名SELECTSno,SNFROMSWHERENOTEXISTS(SELECT*FROMSC,SC1WHERESC1.Sno=‘S2’ANDNOTEXISTS(SELECT*FROMSC,SC2WHERES.Sno=SC2.SnoANDSC2.Cno=SC1.Cno));别名4.4SQL的数据更新SQL的数据更新功能包括：

插入数据、修改数据、删除数据

不同于数据定义，实现对数据库中数据的操作。SQL的三类数据更新语句：插入语句——INSERT有两种形式修改语句——UPDATE

删除语句——DELETE一、插入数据插入单个元组

INSERTINTO表名[(列名1[，列名2]…

)]VALUES(常量1[，常量2]…)；例：插入一个学生记录

INSERTINTOSVALUES(‘S7’,‘程成‘,‘CS’,19)；例：插入一个选课记录

INSERTINTOSC(Sno，Cno)VALUES(‘S7’，’C2’)；没有列出的G取空值插入数据——

批量插入

插入子查询结果——

批量插入

INSERTINTO表名[(列名1[，列名2]…

)]

子查询；例：将每个系学生的平均年龄存入数据库。

CREATETABLEDeptAge(SdeptCHAR(15)，

AvgageDEC(4,1))；

INSERTINTODeptAge(Sdept，Avgage)SELECTSD，AVG(SA)FROMSGROUPBYSD；按系分组二、修改数据修改操作又称更新操作，其语句的一般格式为：

UPDATE表名

SET列名1=表达式1[，列名2=表达式2]…[WHERE条件表达式]；语句含义对指定表中满足条件的元组，按SET子句中的表达式的值修改相应的列值。若无WHERE子句，则修改全部元组。允许带子查询修改数据（1）给WH1仓库的职工提高10%的工资。

UPDATE职工

SET工资=工资*1.10WHERE仓库号=‘WH1’；将北京职工的工资提高10%。

UPDATE职工

SET工资=工资*1.10WHERE仓库号INSELECT仓库号

FROM仓库

WHERE城市=‘北京’；修改数据（2）修改学号，将‘97036’号学生改为‘98070’号。

修改学生关系表S：

UPDATESSETSno=‘98070’WHERESno=‘97036’；

修改学生选课关系表SC：

UPDATESCSETSno=‘98070’WHERESno=‘97036’；注意参照完整性，保持修改操作与数据库的一致性。三、删除数据删除语句的一般格式

DELETEFROM表名

[WHERE条件表达式]；语句含义从指定表中删除满足WHERE子句条件的元组。若无WHERE子句，则删除表中的全部元组。

注意：此语句删除的是表中的数据，不是表的定义。允许带子查询删除数据删除WH1仓库的职工记录。

DELETEFROM职工

WHERE仓库号=‘WH1’；删除人工智能（AI）课的学生选课记录。

DELETEFROMSCWHERECnoINSELECTCnoFROMCWHERECN=‘AI’；保持数据的一致性！4.5视图视图（View）

——

从一个或几个基本表(或视图)导出的表，是虚表。

即：数据库中只存放视图的定义，不存放视图对应的数据，它的数据仍然存放在原来的基本表中。

视图象一个窗口，透过它可以看到自己感兴趣的数据及其变化。

视图一经定义，可以和基本表一样被查询、被删除，也可用来定义新的视图，但对视图的更新操作有限制。

用户的外模式由若干基本表和若干视图组成。一、视图的定义建立视图

格式：CREATEVIEW视图名[（列名[，列名]…）]AS子查询

[WITHCHECKOPTION]；

若省略，则用子查询中SELECT子句的各目标列作为视图的列名。注意：此时目标列只能是单纯的属性名。表示对视图进行更新时要保证更新的元组满足视图定义中的条件（子查询中的条件）。建立视图（1）建立计算机科学系学生的视图。

CREATEVIEWCS-SASSELECTSno，SN，SAFROMSWHERESD=‘CS’WITHCHECKOPTION；要求插入、修改时要满足SD=‘CS’的条件CS-S建立视图（2）建立职工与工作所在城市关系的视图。

CREATEVIEWemp_cityASSELECT职工号，工资，城市

FROM职工，仓库

WHERE职工.仓库号=仓库.仓库号；emp_city建立视图（3）建立职工年、月工资的视图。

CREATEVIEWE_sal(职工号，月工资，年工资)ASSELECT职工号，工资，工资*12FROM职工；E_sal视图列名不可省略删除视图删除视图

格式：DROPVIEW视图名；语句含义

将视图的定义从数据字典中删除。由该视图导出的视图，尽管其定义还在数据字典中，但已无法使用，应同时删除。

注意：如果导出视图的基本表被删除，该视图将失效，需要用DROPVIEW语句删除。二、视图的查询视图定义后，用户可以象基本表一样对视图查询。例：在计算机科学系学生视图中查小于20岁的学生姓名。

SELECTSNFROMCS_SWHERESA<20；视图查询的处理DBMS对视图查询的处理

——

将查询操作和视图定义中的子查询结合起来，形成一个修正的对基本表的查询语句，然后执行之。SELECTSNFROMSWHERESD=‘CS’ANDSA<20；注意：这种转换有时不能直接进行三、视图的更新视图是虚表，对它的更新最终要转换为对基本表的更新。为防止非法操作，视图定义时加WITH子句，DBMS将在视图更新时检查视图定义中的条件，不满足则拒绝执行。并非所有视图都可更新。视图的更新例：向计算机科学系学生视图CS_S插入一个新记录。INSERTINTOCS_SVALUES(‘S9’,‘杨力’,18)；INSERTINTOSVALUES(‘S9’,‘杨力’,‘CS’,18)；四、视图的用途（优点）简化用户的操作

——

视图机制使用户可以只注意他所关心的数据。能使用户以多种角度看待同一数据

——

视图机制为不同用户使用同一个数据库提供灵活性。对重构数据库提供了一定程度的逻辑独立性

——

在数据库的逻辑结构改变后，用新建立的视图定义用户原来的关系，使用户的外模式保持不变。能够对机密数据提供安全保护

——

视图机制可以对不同的用户定义不同的视图，使机密数据不出现在用户视图中，从而自动提供了对机密数据的保护。4.6SQL的数据控制数据控制的两个主要方面：一是控制用户对数据的存取权限——

安全性控制

保护数据库防止非法使用造成的数据泄露和破坏二是控制数据的完整性——

提供完整性约束

数据的完整性是指数据库中数据的正确性和相容性注意：数据控制也称数据保护，除包括数据的安全性控制和完整性控制外，还包括并发控制和数据库恢复。本节仅讨论SQL的安全性控制功能存取控制数据库系统中保证数据安全性的主要措施：

存取控制——

规定用户对数据的存取权限SQL语言为DBA和表主提供定义和回收这种权限的手段授权语句的一般格式：

GRANT权限[，权限]…[ON对象类型对象名]TO用户[，用户]...[WITHGRANTOPTION]；语义：将指定对象的指定权限授予指定用户。

获得授权权限PUBLIC代表全体用户不同对象类型允许的操作权限授权与收回权限授权例：

GRANTSELECTONTABLE仓库TOPUBLIC；

GRANTSELECT，INSERTONTABLE职工

TOZhaoWITHGRANTOPTION；收回权限语句的一般格式：

REVOKE权限[，权限]…[ON对象类型对象名]FROM用户[，用户]…

；语义：收回指定用户在指定对象上的指定权限。收回权限例：

REVOKEINSERTONTABLE职工FROMZhao；授权与收回权限举例GRANTUPDATE(SN),SELECTONTABLESTOWANG；GRANTALLPRIVILEGESONTABLES,C,SCTOZHANG；GRANTCREATETABONDATABASES_CTOLI；GRANTSELECTONTABLESCTOPUBLIC；REVOKEUPDATE(SN)ONTABLESFROMWANG；REVOKESELECTONTABLESCFROMU2；4.7嵌入式SQL把SQL嵌入到宿主语言中使用必须解决的三个问题：

(1)嵌入识别问题

宿主语言的编译程序如何区分主语句和SQL语句。

(2)宿主语言与SQL语言的数据交互

SQL语句的结果提交宿主语言处理，宿主语言的数据交给SQL语句使用。

(3)宿主语言的单记录与SQL的多记录问题

宿主语言一般只能在单记录方式下工作，一次处理一条记录；而SQL常常处理的是记录集合。嵌入式SQL语句的识别

识别标记

——

为区分SQL语句和主语句，对嵌入宿主语言的SQL语句常必须加前缀EXECSQL。SQL语句的结束标志随主语言不同而不同。

以C作为主语言的嵌入式SQL语句：

EXECSQLSQL语句；

以COBOL作为主语言的嵌入式SQL语句：

EXECSQLSQL语句

END-EXEC

PowerBuilder

SQL语句；嵌入式SQL语句的处理方法预编译（常用）修改和扩展主语言使之能处理SQL语句直接传递给DBMS（PowerBuilder）嵌入式SQL语句与主语言之间的通信在嵌入SQL语句的高级语言程序中SQL语句负责操纵数据库高级语言语句负责控制程序流程、预处理两种计算模型之间的通信主要包括：

–

向主语言传递SQL执行状态，以便据此控制程序流程。

通过SQL通信区（SQLCA）实现

–主语言向SQL语句提供参数。

通过主变量实现

–SQL语句查询数据库的结果交主语言处理。

通过主变量和游标实现SQL通信区与主变量SQL通信区（SQLCommunicationArea）

定义：EXECSQLINCLUEDSQLCA；说明：SQLCA是SQL与主语言的通信区，类似于结构变量，数据库系统将SQL的执行状态(成功、失败、出错原因等)记录在其中(SQLCODE)，程序从该通信区中得到相关的信息。主变量

——SQL中使用的主语言程序变量简称主变量。定义：EXECSQLBEGINDECLARESECTION；主变量说明……

EXECSQLENDDECLARESECTION；

主变量在SQL语句中使用需要在变量名前加前缀‘:’。SQL的应用(1)1.根据给定学号查寻学生信息，学号在主变量GIVENSNO中。EXECSQLSELECTSno,Sn,SD,SAFromSInto:Hsno,:Hsn,:Hsd,:HsaWhereSno=:givensno;SQL的应用(2)2.根据学号和课程号查寻学生选课成绩，学号在主变量GIVENSNO和GIVENCNO中。EXECSQLSELECTSn,CN,GFromS,C,SCInto:Hsn,:Hcn,:HgradeWhereSno=:givensnoandCno=:givencnoand&S.Sno=SC.SnoandC.Cno=SC.Cno;SQL的应用(3)3.将所有学生选修数据库(某课程，课程名在主变量Cname中)的成绩加10分。EXECSQLUpdateSCSetG=G+10WhereCnoInSelectCnoFromCWhereCN=“DB”;

CN=:Cname

SQL的应用(4)4.删除退学学生的信息，学生姓名已经在主变量Sname中。EXECSQLDeleteFromSCWhereSnoInselectSnoFromSWhereSN=:Sname;EXECSQLDeleteFromSWhereSN=:Sname;SQL的应用（PB）1SQLCA：缺省的事务对象CONNECTUSINGSQLCA；DISCONNECTUSINGSQLCA；单行检索SELECT*FROMSWHERESNO=“S3”INTO:NO,:NAME,:DEPT,:AGE；插入语句INSERTINTOEmployee(emp_no,emp_name)VALUES(:NO,:NAME)；SQL的应用（PB）2修改语句UPDATESSETSD=:NEWSDWHERESNO=:SNO；删除语句DELETEFROMEmployeeWHEREemp_no=:NO；SQL的应用（PB）3检查SQL语句的执行情况SQLCode（事务对象）0：成功-1：失败100：没有返回数据IntStudent_noStudent_no=Integer(sle_Sno.text)SELECTS.SN,S.SDINTO:sle_Name.text,:sle_Dept.text FROMsWHERES.SNO=:Student_noUSINGSQLCA;IfSQLCA.SQLCode=100thenMessageBox(“查询学生”，“未找到指定学生”)ElseifSQLCA.SQLCode<0thenMessageBox(“数据库错误”,SQLCA.SQLErrText,Exclamation!)EndifIntEmp_numEmp_num=Integer(sle_Emp_Num.text)SELECTemployee.Emp_Lname,employee.Emp_LnameINTO:sle_LName.text,:sle_FName.text FROMemployeeWHEREemployee.Emp_nbr=:Emp_numUSINGEmp_tran;IfEmp_tran.SQLCode=100thenMessageBox(“查询雇员”，“未找到指定雇员”)ElseifEmp_tran.SQLCode<0then