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

第3章关系型数据库标准语言SQL●SQL简介●

SQL的数据定义：基本表,视图,索引,约束●

SQL的数据查询●

SQL的数据更新●嵌入式SQL的使用主要内容：重点：●

SQL的数据查询3.1SQL概述SQL（StructuredQueryLanguage）起源于1974年，作为关系DB的标准语言，用于：ORACLE，SQL/DB，DB2等关系数据库系统中。标准版本：SQL89→SQL2（92年）→SQL3（99年）。一、SQL的特点集DB定义，操纵，控制功能于一体。高度非过程化。面向集合的操作方式。提供两种用方式：交互式和嵌入式。语言简洁，易学易用。二、SQL结构基本表（BaseTable）——关系模式支持DB的三级模式的结构：视图（View）——子模式存储文件（StoredFile）——存储模式其中：元组称为行（row），属性称为列（column）。（1）SQL中的基本表对应DB中的一个关系，一行对应一个元组，一列对应一个属性值域。（2）SQL中的视图由基本表或视图导出；基本表是实际存储在DB中的表,视图是虚表，它的数据仍在导出的基本表中，它的定义存在数据字典中。（3）一个基本表可以跨一个或多个存储文件，一个存储文件也可以存放一个或多个基本表；每一个存储文件对应外存上一个物理文件。（4）用户可用SQL语句对视图和基本表作查询等操作。在用户来看视图与基本表都是关系。（5）SQL的语句既可交互使用，也可嵌套到宿主语言的程序中使用。结构要点：三、SQL的构成数据定义（SQLDDL）：用于定义基本表、视图、索引等。数据操纵（SQLDML）：用于对DB的查询和更新等操作。数据控制：包括对基本表和视图的授权，完整性规则的描述、事务控制等。嵌入式SQL的使用规定。4个部分构成：3.2SQL的数据定义包括：定义和撤消：基本表（table）：表名，属性名，类型，长度。视图（view）索引（index）完整性约束条件一、基本表的创建、修改和撤消1．SQL提供的主要数据类型（1）数值型

INTEGER（或INT）长整型SMALLINT短整型

REAL浮点型（取决于机器）FLOAT（n）浮点型精度为n位NCMERIC（P，d）定点数（p位数字，小数点后d位）（2）字符串型

CHAR（n）最大长度为n的定长字符串

VARCHAR（n）长度可变字符串，具有最大长度n（3）位串型BIT（N）长度为N的二进制的往事。（4）时间型

DATE日期：YYYY—MM—DD（年-月-号数）TIME时间：HH：MM：SS（时：分：秒）算术操作仅限于数值型的数据。2．基本表的创建，修改和撤消（1）创建基本表句型：CreateTable基本表名（列名类型[列完整性约束]，……）；主关键字子句（PRIMARYKEY）其中：完整性约束定义有三种子句：检查子句（CHECK）外关键字子句（FOREIGNKEY）例：教学DB中有三个关系模式：S（sno，sname，sex，age，dept）

Sc（sno，cno，grade）C（cno，cname，credit）定义其基本表。Createtables（snoCHAR（5）[NOTNULLUNIQUE]，snameCHAR（8），sexCHAR（2），ageINT，deptCHAR（6）,primaryKEY（sno）)；Createtablesc(snoCHAR(5),cnoCHAR(4)，gradeSMALLINT，PRIMARYKEY（sno，cno），FOREIGNKEY（cno）REFERENCESC（cno），FOREIGNKEY（sno）REFERENCESS（sno），CHECK(（gradeISNULL）OR（gradeBETWEEN0AND100）))；其基本表定义如下：表C定义略（2）基本表结构的修改增加新的属性：ALTERTABLE基本表名ADD列名类型例：ALTERTABLESADDPHONCHAR（8）;删除原有的属性:ALTERTABLE基本表名DROP列名

[CASCADE|RESTRICT];其中：CASCADE：所有引用该列的视图和约束也要一起被删除。RESTRICT：只有视图和约束没有引用该属性时，才能删除，否则拒绝。例：ALTERTABLESDROPageCASCADE；（3）基本表的撤消句型：DROPTABLE基本表名；例：DROPTABLES；三、视图的创建与撤消视图：外模式（子模式）：由基本表或其它已建视图构造出的表。（1）视图的创建句型：CreateView视图名（列名表）ASSELECT查询子句例：若用户经常用到sno，sname，cname和grade信息，则建立视图供查询CreateViewSg（sno，sname，cname，grade）Asselects•sno，sname，cname，gradefroms，sc，cwheres•sno=sc•snoAndsc.cno=c.cno；（2）视图撤消句型：DROPView视图名四、索引的创建及撤消

建立索引的目的，加快对关系的查找。（浪费外存空间）在基本表上可建立一个或多个索引，索引存在存储文件中称索引文件。句型：Create[UNIQUE]INDEX索引名ON基本表名（列名）例：CreateindexSIONS（sno）按sno升序排列CreateUniqueindexSIONS（sno）；sno中值对应唯一的记录值

CreateUniqueindexSCIONSC（snoASC，cnoDESC）索引定义的结构存在数据字典库中，学号按升序排列，CNO值按降序排列。索引的撤消：DROP

index索引名；例：DROPindexSI；

3.3SQL的数据查询一、select查询语句的句型

select〈目标表的属性名或目标列表达式〉……select子句from〈表名或视图名〉……from子句[where〈行条件表达式〉]

……行条件子句[groupby〈列名1〉[having〈条件表达式〉]]……列条件子句[orderby〈列名2〉[asc|desc]]……排序子句执行过程根据where子句的条件表达式，从from子句指定的基本表或视图中找出满足条件的元组，再按select子句中的目标列表达式，选出元组中的属性值形成结果表。group子句，将结果按〈列名1〉的值进行分组，列值相等的元组分为一组，每一组会产生结果表中的一条记录。having子句将满足条件的组给出输出。order子句对输出的目标表按ASC升序或DESC降序排列。

select基本句型

selectA1，A2，…，An（属性名Ai）fromR1，R2…，Rn（关系名或视图名Ri）whereF；（逻辑表达式F）其中：F可使用以下运算符

等价于关系代数式：ΠA1,A2,…,An(σF(R1

R2…Rn))算术比较符：<，<=，>，>=，=，<>或！=。逻辑符：AND，OR，NOT。集合运算符：union（并），intersect（交）except（差），IN（属于）。谓词：exists（存在量词），all，any(some)，unique（唯一），LIKE。F中还可以是select子句（嵌套）。使用的函数：avg（列名）：求列中的平均值。min（列名）：求列中的最小值。max（列名）：求列中的最大值。sum（列名）：求列中值之和。count（列名）：求列中值的个数。count（*）：求元组个数。注意：select语句与关系代数表达式的关系：

selectA，B，CfromR1，R2ΠA,B,C(σF(R1

R2))whereF；例：已知对R运算的关系代数表达式：∏A,B(σ

A>3∧C=1(R)):

将关系代数表达式用等价的SELECT语句写出。selectA，BselectA，S.BfromRfromR，S

whereA>3andC=1;whereR.B=S.C;例：已知S是由R通过select语句执行的结果关系，请写出此select语句。已知S是由R通过关系代数运算的结果关系，请写出此关系代数表达式。

selectB，CfromR写法不唯一。

whereB=5ORC=5;ABC631547154RBC5415SAs.B64∏A,S.B(σR.B=S.C(R×S))∏B,C(σB=5∨C=5(R))二、SELSCT语句的使用

单表查询，多表关联查询（嵌套查询），集合查询。1、单表查询例：教学DB三个基本表：

s（sno，sname，sex，age，dept）：学号，姓名，性别，年龄，系名

c（cno，cname，credit）：课号，课名，学分

sc（sno，cno，grade）：学号，课号，成绩列上查询例1：查询学生的学号，姓名和系名selectsno，sname，deptfroms；例2：查询全部学生的详细情况select*(*表示s中的全部属性)froms；∏sno，sname，dept

(S)例3：查询全部学生的姓名及出生年份selectsname，2018-agefroms；结果：sname无列名李一1999刘二1996王三1995例4：查询全部学生的姓名，出生年份和系名要求：指定别名改变查询结果的列标题selectsnameNAME，2015-ageBIRTHDAY,deptDEPARTMENTfroms；结果：

NAMEBIRTHDAYDEPARTMENT李一1981cs刘二1982is王三1983ma行上查询例1：查询所有选修课程的学生的学号。SelectdistinctsnofromSC;例2：查找成绩不及格学生的学号及课号。selectdistinctsno，cnofromscwheregrade<60；例3：查找成绩在100分~90分之间的学号及课号。SelectSno，CnoFromSCwheregrade>=90ANDgrade<=100

;

或：wheregradebetween90and100;SC表原来值：SnoCnogradeSno20001180操作后：200012852000239020002280∏sno，cno

(σ

grade<60

(SC))例4:查找信息系（IS），数学系（MA）和计算机系（CS）的学生的姓名和性别。selectsname，sexfromswheredeptIN（‘IS’，‘MA’，‘CS’）；例5：查找所有姓“刘”的学生的姓名、学号和性别。谓词LIKE可实现字符串的匹配，满足模糊查询的要求格式：[NOT]LIKE‘〈匹配串〉’[ESCAPE‘〈换码字符〉’]含义：查找指定的属性列值与〈匹配串〉相匹配的元组。一个完整字符串通配符

%

和

＿

%表示任意长度（长度可为0）的字符串：如：a%b表示以a开头，b结尾任意长度的字符串。

＿（下划线）表示任意单个字符。如：a

b表示a开头，b结尾的长度为3的任意字符串。其中：〈匹配串〉：selectsname，sno，sexfromSwheresnameLIKE‘刘%’；例6：查找名字中第二个字为“涛”字的学生的学号及姓名

Selectsno，snamefromsWheresnameLIKE‘＿＿涛%’；“--”前一个字为汉字占二个字符位置。例7：查找选课后，没有参加考试的学生的学号和课号Selectsno，cnofromscWheregradeISNULL；（注：IS不能用“=”代替）例8：查找选修3号课程的学生的学号及成绩（按降序排列）。Selectsno，gradefromscWherecno=‘3’orderbygradeDESC；例9：统计查询的例子

1）统计学生总人数selectcount(﹡)froms；

2）统计选了课程的学生的人数selectcount（distinctsno）fromsc；

3）查找1号课程考试的最高分数Selectmax（grade）fromscwherecno=‘1’；例10：查找信息系“IS”选修了3门以上的课程的学生的学号：（假定SC中有一列dept记录了系名）selectsnofromscwheredept=‘IS’

groupbysnohavingcount（﹡）>3；（分组查询）SnoCnodept11IS12IS13IS14IS21IS22IS23IS24IS2、关联查询

①多个表连接查询

例1：查询每个学生及选修课程的情况。Selects·﹡,sc·﹡froms,scwheres·sno=sc·sno；

∏s.sno,sname,sex,dept,sc.sno,cno,grade(σ

s·sno=sc·sno(S×SC))②自身连接查询例2：查找至少选择选修课程号为“1”号和为“2”号的学生的学号。selectx·snofromSCx,SCyx，y是别名wherex·sno=y·snoANDx·cno=‘1’ANDy·cno=‘2’;另一嵌套结构写法：

selectsnofromSCwherecno=‘2’ANDsnoIN(selectsnofromSCwherecno=‘1’）;③嵌套结构的查询例3：查询选修课程号为“2”的学生姓名与学号a.Selects·sno,s·sname（涉及表s和sc连接查找）：

froms,scs(sno,sname,sex,age,dept)wheres·sno=sc·snoANDcno=′2′;sc(sno,cno,grade)b.写法：Selectsno,sname（嵌套查询结构）

fromswheresnoIN(selectsnofromscwherecno=′2′）;注：嵌套结构层次分明，具有结构化程序设计特点。嵌套结构比不嵌套结构查询效率高（因先作选择）。

c.写法（使用存在量词的嵌套查询）

selectsno,snamefromswhereEXISTS(select＊

fromSCwhereSC·Sno=S·SnoANDCno=′2′);例4：查找选修了课程名为“信息系统”的学生学号与姓名。

selectsno,snamefromswheresnoIN(selectsnofromscwherecnoIN(selectcnofromcwherecname=‘信息系统’);涉及：三个表：s(sno,sname,sex,age,dept)sc(sno,cno,grade)c(cno,cname,credit)关于多个表关联查询select语句设计问题：方法与步骤：①首先确定关系（确定from后面的关系名）根据已知的属性和要查找的属性是否在同一关系中？是，此关系就是要找的（单关系查找）。否，已知的属性所在的关系R，与要查找的属性所在的关系S之间，找外键的联系；则：R,S与外键通过的关系（有的话）就是要找的关系。（多关系关联查找）②确定查找条件（确定where后面的条件）根据题目给定的属性和值组织。嵌套结构中，将某一个查询条件用子查询表示，再参与主查询之中。例5：查找其他系中比IS系任何学生年龄小的学生名单。

selectsname,agefromswhereage﹤ALL(selectagefromswheredept=′IS′)ANDdept<>′IS′OrderbyageDESC;例6：查找至少有一门课的成绩超过学号为20002的一门课成绩的学生学号。

selectdistinctsnofromscwheregrade﹥some(selectgradefromscwheresno=′20002′)；3.集合查询例：查找计算机系的学生及年龄不大于19岁的学生实际求计算机系的所有学生和年龄不大于19岁其它系的学生的并集

select＊

fromswheredept=′cs′

UNIONSelect＊

fromswhereage﹤=19anddept<>′cs′;3.4SQL的数据更新数据插入数据删除数据修改更新包括：一、数据插入元组值的插入查询结果的插入包括：1、元组值的插入一般句型：INSERTINTO基本表名（列名表)

VALUES(元组值)一次仅插入一个元组。

如：INSERTINTOSC

VALUES（′20005′，′1′，85）；2、查询结果的插入一般句型：INSERTINTO基本表名（列名表）

SELECT

查询语句……把当前表中满足条件的元组送到已存在的表中。如：从基本表SC中，把平均成绩﹥80的男学生的学号和平均成绩存入另一个已知的基本表SG（sno,sg1）中：INSERTINTOSG(sno,sg1)SELECT

sno,AVG(grade)

FROMSCWHEREsnoIN(SELECTsnoFROMSWHERESEX=′男′)GROUPBYsnoHAVINGAVG(grade)﹥80;Snocnogreade117012801390219022702360二、数据的删除一般句型：DELETEFROM基本表名[WHERE条件表达式]

从一个基本表中删除满足条件的元组例：删除SC中‘1’号课程中小于该课程平均成绩的成绩元组。

DELETEFROMSCWHEREcno=‘1’ANDgrade﹤(SELECTAVG(grade)FROMSCWHEREcno=‘1’);三、数据的修改修改基本表中元组的某些列值一般句型：UPDATE基本表名

SET列名=值表达式[，列名=值表达式，…][WHERE条件表达式]例：将女同学的成绩提高10%UPDATESCSETgrade=grade＊1·1WHEREsnoIN(selectsno

fromswheresex=′女′）；

3.5视图1、视图的概念视图从一个或多个基本表（或已定义的视图）导出的表，提供给用户查询DB中数据使用。视图是一个虚表，它的框架定义的属性存于数据字典库中，其数据仍在原来基本表中。视图的查询与基本表的查询一样，使用select语句。视图的作用：（1）视图能简化用户的操作。通过视图可以从一个基本表或多个基本表中抽取用户感兴趣的数据，方便用户处理。（2）视图对重构DB提供了一定程度的逻辑独立性。例：将表s(sno,sname,sex,age,dept)垂直划分为两个表：

sx(sno,sname,dept)sy(sno,sex,age)若建立一个视图s，可以还原原来的表s:

Createviews(sno,sname,sex,age,dept)asselectsx·sno,sx·sname,sy·sex,sy·age,sx·deptfromsx,sywheresx·sno=sy·sno;这样尽管DB逻辑结构改变了，但应用程序不必修改，因为新建立的视图定义了用户原来的关系，使用户的外模式保持不变。(3)视图能对保密数据提供保护。2、视图的更新操作视图是不实际存储数据的虚表，因此：对视图的更新，最终要转换为对基本表的更新。必须对视图的更新操作作一些限制（查询不限制）。视图更新的三条规则：（1）不允许对从多个基本表作连接操作导出的视图作更新操作。（2）不允许对使用了分组（groupby）和函数（如：MIN等）操作导出的视图作更新。（3）如果视图从单个基本表，使用选择、投影操作导出的，并包含了基本表的主关键字或某个候选关键字，那么这样的视图称为“行列子集视图”，可作更新操作。例：从s表中定义一个有关男同学的视图CreateviewS1(sno,snam,age)asselectsno,sname,agefromSwheresex=′男′;该视图从单基本表导出，且包含关键字sno,可以更新：执行插入操作：INSERTINTOS1VALUES（′20006′，′王五′，20）；例：定义视图

CreateviewSSG(sno,cnum,Avgg)asselectsno,count(sno),Avg(grade)fromscwheregradeISNOTNULL

Groupbysno;SSG由分组和函数操作导出，据（2）规则不能更新。

3.6嵌入式SQL的使用(不讲，嵌入与语言有关)交互式SQL：用户在终端上以命令形式使用。嵌入式SQL：在高级语言源程序中嵌入SQL语句。SQL语言使用形式：一、嵌入式SQL的使用规定●扩充宿主语言的编译程序功能，直接处理SQL语句。●采用预处理方式处理SQL语句。1、嵌入式SQL两种处理方式宿主语言语句+SQL语句预处理方式SQL语句的过程：预备处理程序宿主语言语句+SQL函数调用宿主语言编译程序宿主语言目标程序SQL函数定义库⇒⇒⇒⇒2.嵌入式SQL的使用规定

（1）SQL与宿主语言的接口SQL与宿主语言的接口是共享变量。1）共享变量由宿主语言程序定义，再用SQL的DECLARE语句说明，随后SQL语句可引用这些变量。

2）引用时，变量前必须加冒号“：”作前缀标识，以示区别数据库中的变量。

（2）主语言语句与SQL语句的区别

在SQL语句前使用前缀标识“EXECSQL”……；以示区别宿主语言语句。如：C语言程序中使用如下形式定义共享变量：

EXECSQLBEGINDECLARESECTIONCharSno[5],name[9]；CharSQLSTATE[6]；

EXECSQLENDDECLARESECTION；3.SQL的集合处理方式与宿主语言单记录处理方式的协调由于SQL语句处理的是记录集合，而宿主语言语句一次只能处理一个记录，因此需要用游标（cursor）机制，把集合操作转换成单记录处理方式。与游标有关的SQL语句有下列四个：a、游标定义语句：游标是与某一查询结果相联系的符号名。定义形式：EXECSQLDECLARE〈游标名〉CURSORFOR〈SELECT语句〉；

b、游标打开语句（OPEN）：执行游标定义中的SELECT语句，使游标（实际是指针）指向查询结果的第一行之前。语句型：EXECSQLOPEN〈游标名〉；注：在游标打开后，可修改和删除游标所指的元组。c、游标推进语句（FETCH）：游标推进一行，将游标所指的当前行的值送共享变量。句型：EXECSQLFETCHFROM〈游标名〉INTO〈变量表〉；

注：FETCH常置于宿主语言程序的循环语句中。d、游标关闭语句（CLOSE）句型：EXECSQLCLOSE〈游标名〉；

二、嵌入式SQL的使用技术a、涉及游标机制的使用技术b、不涉及游标机制的使用技术1、不涉及游标的SQLDML语句

当涉及对DB中一个元组操作的语句：INSERT，DELETE和UPDATE，SELECT，都不必使用游标。如：EXECSQLSELECTsname,age,sex

INTO:sn，:sa，:ssFROMSWHERESno=:give；/*假设一个学生的学号已在give中*//*在s中寻找学生姓名、年龄和性别分别送到sn、sa、ss共享变量中*/2、涉及游标的SQLDML语句

当涉及对DB中多个元组操作时，都必须使用游标。（1）、SELECT语句使用当查找多个元组时，则用游标机制将多个元组一次一个从DB中读出并处理。具体过程：用游标定义语句定义一个游标与某一个SELECT语句对应。用游标打开语句（OPEN）打开，使游标