《政务数据库系统》第4章：政务数据库管理语言-SQL

第4章政务数据库管理语言—SQL4.1SQL概述4.1.1.SQL历史简介4.1.2SQL的作用4.1.3SQL的特点4.1.4SQL语言的实现4.1.1.SQL历史简介IBM的非过程关系语言SEQUELStructuredEnglishQueryLanguage使用其缩名SQLSQL-86由ISO和ANSI于1986年发布SQL-89SQL-99标准SQL与扩充SQL4.1.2SQL的作用数据定义语言（DDL）用于定义、撤销和修改数据模式。查询语言用于查询数据。数据操纵语言（DML）用于增加、删除、修改数据。数据控制语言（DCL）用于数据访问权限的控制。4.1.3SQL的特点综合统一非过程化面向集合的处理方式语法简洁9个核心动词——CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE4.1.4SQL语言的实现联机交互使用方式。嵌入某种高级程序设计语言4.2SQL数据定义语句4.2.1基表与视图4.2.2基表模式定义4.2.3索引建立与撤销4.2.4约束的定义4.2.5基表模式修改4.2.1基表与视图基表显式存储在数据库中视图一种虚表，保留逻辑定义。基表1基表2基表3基表4视图1视图2视图3SQL4.2.2基表模式定义CREATETABLE关键字：NOTNULLPRIMARYKEY等CREATETABLEpersonnel(PnoCHAR(4)NOTNULL,PnameCHAR（8）NOTNULL,PsexCHAR(2)NOTNULL,PbirthDATENOTNULL,PdnoCHAR(4),PtitleCHAR(10),PRIMARYKEY(Pno));SQL的数据类型类别数据类型描述字符类型Char(n)定长字符串Varchar(n)变长字符串整数类型Int

整数，字长32位Smallint短整数，字长16位浮点数double浮点数，双精度64位浮点数Real实数Dec(m,n)m为总十进制位数，n为小数点后十进制位数位串类型Bit(n)包含0或1的定长位串Bitvarying(n)变长位串日期和时间类Date日期Time时间Timestamp日期时间型4.2.3索引建立与撤销单索引（默认升序）复合索引UNIQUE关键字CLUSTER关键字删除索引CREATEINDEXPname_indexONpersonnel(Pname

DESC);

CREATEINDEXPtitle_sex_indexONpersonnel(Ptitle,Psex);

CREATE

UNIQUEINDEXPtitle_indexONpersonnel(Ptitel);

CREATECLUSTERINDEXPno_indexONpersonnel(Pno);

DROPINDEXPtitle_index;

4.2.4约束的定义PRIMARYKEY：主关键字UNIQUE：候选关键字CREATETABLEdepartment(DnoCHAR(4),DnameCHAR(16)UNIQUE,DphoneNUMBER(8)UNIQUE,DheaderCHAR(8)UNIQUE,PRIMARYKEY(Dno));REFERENCES：外关键字CREATETABLEpayment(PnoCHAR(7)NOTNULL,PsalaryNUMBER(7,2),PrateNUMBER(4,2),FOREIGNKEY(Pno)REFERENCESpersonnel(Pno));personnelPnoPnamePsexPbirthPdnoPtitlepaymentPnoPsalaryPrateCHECK约束CREATETABLEpersonnel(PnoCHAR(7)NOTNULL,PnameVARCHAR（10）NOTNULL,PsexCHAR(2)CHECK(Psex=’男’ORPsex=’女’),PbirthDATE,PdnoCHAR(4),PtitleCHAR(10),PRIMARYKEY(Pno));4.2.5基表模式修改修改表：ALTERTABLE删除表：DROPTABLE补充定义主键删除主键定义ALTERTABLEpersonnelADDPcommentCHAR(80);

DROPTABEL<表名>ALTERTABLE<表名>ADDPRIMARYKEY(<列名表>);ALTERTABLE<表名>DROPPRIMARYKEY;补充定义外键ONDELETERESTRICTSETNULL删除外键定义ALTERTABLEpersonnelDROPPdno_FK;

ALTERTABLE<表名-1>ADDFOREIGNKEY[<外键名](<列名表>)REFERENCES<表名-2>[ONDELETE{RESTRICT|CASCADE|SETNULL}];ALTERTABLEpersonnelADDFOREIGNKEYPdno_FK(Pdno)REFERENCESdepartmentONDELETESETNULL;

4.3SQL数据查询语句4.3.1简单查询语句4.3.2条件查询语句4.3.3分组查询4.3.4排序查询4.3.5连接查询4.3.6嵌套查询4.3.1简单查询语句SELECT语句格式FROMWHEREGROUPORDERSELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[,<表名或视图名>][WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]];PnoPnamePsexPbirthPdnoPtitle001王辉男1967-7-5A01科长002李丽女1970-12-10B02科员003马苗女1976-2-23A01科员004宋贤齐男1956-10-12B02副科长005王刚男1973-9-8C03科员关系实例PnoPsalaryPrate00130000.100225000.0800325000.0800428000.0800524000.06DnoDnameDphoneDheaderA01人事科4655王辉B02办公室4621熊雷C03信息中心4633吴潜personnelsalarydepartment示例选择全部记录（*）选择部分列改变列的顺序改变列标题唯一查询SELECT*FROMpersonnel;

SELECTDno,DnameFROMdepartment;

SELECTPno,Pname,Ptitle,PbirthFROMpersonnel;

SELECTPsalary*PrateAS

个人所得税FROMpayment;

SELECTDISTINCT

PdnoFROMpersonnel;

DnoDnameA01人事科B02办公室C03信息中心个人所得税300200200224144PdnoA01B02C03personneldepartmentpayment4.3.2条件查询语句算术运算符比较运算符模糊比较日期比较SELECTPno,Psalary*0.1奖金FROMpayment;

SELECTPnameFROMpersonnelWHEREPnameLIKE‘王%’

SELECTPname,PbirthFROMpersonnelWHEREPbiith<‘1970-1-1’

personneldepartmentpayment逻辑操作符其他操作符inbetweenSELECTpnameFROMpersonnelwhereyear(date())-year(pbirth)<35and

psex='女';

SELECTpnameFROMpersonnelWHEREPdno

in('A01','BO2');SELECTPno,PsalaryFROMpaymentWHEREPsalary

BETWEEN2500AND3000;personneldepartmentpayment4.3.3分组查询集函数分组查询集函数描述COUNT(*)统计元组个数COUNT(<列名>)统计某列中值的个数SUM(<列名>)计算某列中值的总和（该列必须是数值型）AVG(<列名>)计算某列中值的平均值（该列必须是数值型）MAX(<列名>)求某列的最大值MIN(<列名>)求某列的最小值集函数示例COUNT()SUM()AVG()SELECTCOUNT(*)FROMpersonnel;

SELECTSUM(Psalary)TotalFROMpayment

SELECTAVG(Psalary*Prate)AS平均税款FROMpayment;personneldepartmentpayment分组查询示例SELECTPdno,COUNT(Pdno)AS部门人数FROMpersonnelGROUPBY

Pdno;SELECTPdno,COUNT(Pdno)AS部门人数

FROMpersonnel

GROUPBY

Pdno;

HAVINGCOUNT(*)>=2；

Pdno部门人数A012B022C031Pdno部门人数A012B022personneldepartmentpayment4.3.4排序查询ORDERBYSELECT*FROMpersonnelORDERBY

Pname;

PnoPnamePsexPbirthPdnoPtitle002李丽女1970-12-10B02科员003马苗女1976-2-23A01科员004宋贤齐男1956-10-12B02副科长005王刚男1973-9-8C03科员001王辉男1967-7-5A01科长LMSW.GW.Hpersonneldepartmentpayment4.3.5连接查询连接的类型有无条件无条件：笛卡尔积CROSSJOIN条件：（投影/选择）自然连接：参与连接的两个关系具有公共属性非自然连接：按指定条件连接内连接：结果中的元组严格符合连接条件外连接：结果中的元组部分符合连接条件连接查询示例无条件查询得到的结果中含有m*n个元组，有可能大部分是无意义的。SELECT*FROMpersonnelCROSSJOINdepartment;或者：SELECT*FROMpersonnel,department;personneldepartmentpayment自然连接SELECT*FROMpersonnelINNERJOINpaymentON

personnel.Pno=payment.Pno;

personneldepartmentpaymentSELECT*FROMpersonnel,paymentWHEREpersonnel.Pno=payment.Pno;或者PnoPnamePsexPbirthPdnoPtitleDnameDphoneDheader001王辉男1967-7-5A01科长人事科4655王辉002李丽女1970-12-10B02科员办公室4621熊雷003马苗女1976-2-23A01科员人事科4655王辉004宋贤齐男1956-10-12B02副科长办公室4621熊雷005王刚男1973-9-8C03科员信息中心4633吴潜左连接DnoDnameDphoneDheaderA01人事科4655王辉B02办公室4621熊雷C03信息中心4633吴潜D04信访办公室4666李国庆DnoDnamePnoPnameA01人事科001王辉A01人事科003马苗B02办公室002李丽B02办公室004宋贤齐C03信息中心005王刚D04信访办公室NULLNULLSELECTdepartment.Dno,department.Dname,personnel.Pno,personnel.PnameFROMdepartmentLEFTJOINpersonnelON

department.Dno=personnel.Pdno;personneldepartmentpayment4.3.6嵌套查询查在办公室工作的人员情况查department中办公室的部门编号dno根据获得的dno查personnel表中的人员情况合并SELECT*FROMpersonnelWHEREPdno=(SELECTDnoFROMdepartmentWHEREDname=’办公室’);SELECTDnoFROMdepartmentWHEREDname=’办公室’DnoB02SELECT*FROMpersonnelWHEREPdno=“B02”PnoPnamePsexPbirthPdnoPtitle002李丽女1970-12-10B02科员004宋贤齐男1956-10-12B02副科长personneldepartmentpayment查王辉的工资及纳税情况首先在personnel中查询王辉的Pno其次在payment中查询工资及纳税情况合并SELECTPsalaryAS工资,Prate*PsalaryAS纳税FROMpaymentWHEREPno=(SELECTPnoFROMpersonnelWHEREPname=’王辉’);personneldepartmentpayment带谓词的子查询IN表示包含于EXISTS表示存在ANY(略)ALL(略)SELECT*FROMpersonnelWHEREPdno

NOTIN

(SELECTDnoFROMdepartmentWHEREDname='办公室');SELECTPnameFROMpersonnelWHEREEXISTS

(SELECT*FROMpaymentWHEREpno=personnel.pnoandpsalary=2500);

personneldepartmentpayment相关子查询相关子查询与一般子查询的区别一般子查询都是单独执行，然后将结果用于外层查询，外查询只是将这些子查询的结果作为一个常量或条件来对待的相关子查询不同，它不能一次将子查询的结果求解出来，而需要外查询为它提供信息，因此需要反复求值。本例中，SQL首先提取外查询表personnel的第一个元组，根据它与内查询相关的属性值即Pno来处理内层查询，如果内层查询结果非空，则EXISTS返回真值，将该元组放入结果集合中，依次重复直至完成。SELECTPnameFROMpersonnelWHEREEXISTS

(SELECT*FROMpaymentWHEREpno=personnel.pnoandpsalary=2500);

4.4SQL数据操纵语句4.4.1插入数据4.4.2删除数据4.4.3修改数据4.4.1插入数据单元组插入批量插入INSERTINTOpersonnel(Pno,Pname,Pdno,Ptitle)VALUES（‘012’，‘吴潜’，‘CO3’，‘高级工程师’）；INSERTINTOofficeSELECTPno,Pname,PtitleFROMpersonnelWHEREPdno=’B02’;personneldepartmentpayment4.4.2删除数据单元组删除批量删除带子查询删除DELETEFROMpersonnelWHEREPname=’吴潜’；DELETEFROMoffice;DELETEFROMpersonnelWHEREPdno=(SELECTdno

FROMdepartmentWHEREdname='信息中心');personneldepartmentpayment4.4.3修改数据单元组修改批量修改带子查询修改UPDATEpersonnelSET

Ptitle=’副科长’WHEREPname=’马苗’;

UPDATEpaymentSETPsalary=Psalary*1.2;UPDATEpaymentSET

Psalary=Psalary*1.2WHEREPnoIN(SELECTPno

FROMpersonnelWHEREPdno=(SELECTDno

FROMdepartmentWHEREDname=’办公室’));personneldepartmentpayment4.5SQL数据控制语句权限类型SELECTINSERTDELETEUPDATEREFERENCESUSAGEALTERINDEXCREATETAB权限分配权限回收REVOKEGRANT<权限>[，<权限>]…[ON<对象类型><对象名称>]TO<用户>[，<用户>]…[WITHGRANTOPTION];权限分配示例GRANTSELECT(Psalary)ONpaymentTO郑华;GRANTSELECT，UPDATEONpaymentTOleader;GRANTALLPRIVILEGESONpersonnelTO李勇;GRANTSELECT,INSERTONpaymentTO王磊WITHGRANTOPTION;权限回收示例REVOKEUPDATE(Psalary)ONpaymentFROM王梅;REVOKEDELETEONpersonnelFROMPUBLIC;4.6视图4.6.1定义视图4.6.2查询视图4.6.3删除视图4.6.4更新视图4.6.1定义视图例1WithcheckoptionCREATEVIEWV_TITLE(人员编号，人员姓名，人员职务)AS

SELECTPno,Pname,PtitleFROMpersonnel；CREATEVIEWV_OFFICEAS

SELECT*FROMpersonnelWHEREPdno=’B02’WITHCHECKOPTION;4.6.2查询视图同查询表4.6.3删除视图DropviewDROPVIEWV_OFFICE4.6.4更新视图INSERTINTOV_OFFICEVALUES(‘099’,’陈登’,’科员’);4.7高级SQL4.7.1嵌入式SQL4.7.2游标4.7.3存储过程4.7.4动态SQL4.7.1嵌入式SQL接口和声明EXECSQL<SQL

语句>；特殊的全局变量SQLCAEXECSQLINCLUDESQLCA;Sqlca

代码值 0：成功