1、第三章 关系DB的标准语言SQL,SQL的数据定义：基本表,视图,索引 SQL的数据查询（重点） SQL的数据更新,内容：,第一节SQL（Structured Query Language）基本简介,SQL起源于1974年，作为关系DB的标准语言，用于：ORACLE，SQL/DB，DB2等中。 标准版本：SQL89SQL2（92年）SQL3（正在修改）。,一、SQL的特点,集DB定义，操纵，控制功能于一体。 高度非过程化。 面向集合的操作方式。 提供两种用方式：交互式和嵌入式。 语言简洁，易学易用。,二、SQL结构,基本表（Base Table） 关系模式 支持DB的三级模式的结构： 视图（V
2、iew） 子模式存储文件（Stored File）存储模式,其中：元组称为行（row），属性称为列（column）。,（1）SQL中的基本表对应DB中的一个关系，一行对应一个元组，一列对应一个属性值域。（2）SQL中的视图由基本表或视图导出；基本表是实际存储在DB中的表,视图是虚表，它的数据仍在导出的基本表中，它的定义存在数据字典中。（3）一个基本表可以跨一个或多个存储文件，一个存储文件也可以存放一个或多个基本表；每一个存储文件对应外存上一个物理文件。（4）用户可用SQL语句对视图和基本表作查询等操作。在用户来看视图与基本表都是关系。（5）SQL的语句既可交互使用，也可嵌套到宿主语言的程序中使
3、用。,结构要点：,三、SQL的构成,数据定义（SQL DDL）：用于定义基本表、视图、索引等。,数据操纵（SQL DML）：用于对DB的查询和更新等操作。,数据控制：包括对基本表和视图的授权，完整性规则的 描述、事务控制等。,嵌入式SQL的使用规定。,4个部分 构成：,第二节 SQL的数据定义,包括：定义和撤消,基本表（table）,视图（view）,索引（index）,完整性约束,一、基本表的创建、修改和撤消,1SQL提供的主要数据类型,（1）数值型 （2）字符串型INTEGER（或INT）长整型 CHAR（n）长度为n的定长字符串SMALLINT 短整型 VARCHAR（n）具有最大长度n
4、REAL 浮点型（取决于机器） 可变字符串FLOAT（n） 浮点型精度为n位NCMERIC（P，d） 定点数（p位数字，小数点后d位）,（3）位串型 （4）时间型BIT（N）长度为N的二进制的往事。DATE 日期：YYYYMMDDTIME 时间：时：分：秒HH：MM：SS 算术操作仅限于数值型的数据。,2基本表的创建，修改和撤消 （1）创建基本表句型：Create Table基本表名（列名 类型 列完整性约束，）,主关键字子（PRIMARY KEY）其中：完整性约束定义有三种子句： 检查子句 （CHECK）外关键字子句（FOREIGN KEY）,例：教学DB中有三个关系模式：S（sno，sna
5、me，sex，age，dept） Sc（sno，cno，grade）C（cno，cname，credit）,定义其基本表。,Create table s（sno CHAR（5）NOT NULL UNIQUE，sname CHAR（8），sex CHAR（2），age INT，dept CHAR（6）primary KEY（sno）)；Create table sc (sno CHAR (5),cno CHAR(4)，grade SMALLINT，PRIMARY KEY（sno，cno），FOREIGN KEY（cno）REFERENCES C（cno），FOREIGN KEY（sno）REFE
6、RENCES S（sno），CHECK (（grade IS NULL）OR（grade BETWEEN 0 AND 100）) )；,其基本表定义如下：,表C定义略,（2）基本表结构的修改 增加新的属性：ALTER TABLE 基本表名 ADD 列名 类型例：ALTER TABLE S ADD PHON CHAR（8） 删除原有的属性: ALTER TABLE 基本表名 DROP CASCADE|RESTRICT其中：CASCADE： 所有引用该列的视图和约束也要一起被删除。RESTRICT：只有视图和约束没有引用该属性时，才能删除，否则拒绝。例：ALTER TABLE S DROP age
7、 CASCADE；,（3）基本表的撤消句型：DROP TABLE 基本表名；例：DROP TABLE S；,三、视图的创建与撤消视图：外模式（子模式）：由基本表或其它已建视图构造出的表。（1）视图的创建句型：Create View 视图名（列名表）AS SELECT 查询子句例：若用户经常用到sno，sname，cname和grade信息，则建立视图供查询Create View Sg（sno，sname，cname，grade）As select ssno，sname，cname，gradefrom s，sc，cwhere ssno=scsno And sc .cno=c. cno；（2）视图
8、撤消 句型：DROP View 视图名,四、索引的创建及撤消建立索引的目的，加快对关系的查找。（浪费外存空间）在基本表上可建立一个或多个索引，索引存在存储文件中称索引文件。 句型：Create UNIQUE INDEX 索引名 ON 基本表名（列名）例：Create index SI ON S（sno）按sno升序排列Create Unique index SI ON S（sno）；sno中值对应唯一的记录值Create Unique index SCI ON SC（ sno ASC，cno DESC）索引定义的结构存在数据字典库中，学号按升序排列，CNO值按降序排列。 索引的撤消：DROP
9、index 索引名例：DROP index SI；,第三节 SQL的数据查询,数据查询是DB中最重要，最基本的操作。查询操作是更新操作的基础。 查询功能的强弱直接影响DB使用性能。 一、select查询语句的句型select目标表的属性名或目标列表达式from表名或视图名 from子句where行条件表达式 行条件子句group by列名1having条件表达式 列条件子句order by列名2asc|desc 排序子句 执行过程 根据where子句的条件表达式，从from子句指定的基本表或视图中找出满足条件的元组，再按select子句中的目标列表达式，选出元组中的属性值形成结果表。如果有gr
10、oup子句，将结果按列名1的值进行分组，列值相等的元组分为一组，每一组会产生结果表中的一条记录。having子句将满足条件的组给出输出。order子句对输出的目标表按asc升序或desc降序排列。,select基本句型select A1，A2，An （属性名Ai）from R1，R2，Rn（关系名或视图名Ri）A1,A2,An (F (R1R2Rn)where F （逻辑表达式F）其中：F可使用以下运算符 算术比较符：，=，=，或！= 逻辑符：AND，OR，NOT 集合运算符：union（并），intersect（交），except（差），IN（属于） 谓词：exists（存在量词），all，
11、any(some)，unique（唯一），LIKE F，中还可以是select子句（嵌套）使用的函数：avg（列名）：求平均值min（列名）：求最小值max（列名）：求最大值sum（列名）：求和count（列名）：求列中值的个数count（*）：求元组个数,二、SELSCT语句的使用单表查询多表关联查询（嵌套查询）集合查询 1、单表查询 例：教学DB三个基本表：s（sno，sname，sex，age，dept）：学号，姓名，性别，年龄，系名c（cno，cname，pno，credit） ：课号，课名，先修课名，学分sc（sno，cno，grade） ：学号，课号，成绩,列上查询 例1：查询学生
12、的学号，姓名和系名select sno，sname，deptfrom s； 例2：查询全部学生的详细情况select *from s；,例3：查询全部学生的姓名及出生年份select sname，2003-agefrom s； 结果：sname 2003-age李一 1981刘二 1982王三 1983 例4：查询全部学生的姓名，出生年份和系名 要求：指定别名改变查询结果的列标题 select sname NAME，2003-age BIRTHDAY，ISLOWER（dept） DEPARTMENT from s； 结果： NAME BIRTHDAY DEPARTMENT 李一 1981 cs
13、 刘二 1982 is 王三 1983 ma,行上查询 例1：查询所有选修课程的学生的学号。 Select distinct sno from SC; 例2：查找成绩不及格的学生的学号及课号。 select distinct sno ，cno from sc where grade=90 AND grade=100 （或：where grade between 90 and 100） 例4查找信息系（IS），数学系（MA）和计算机系（CS）的学生的姓名和性别 select sname，sex from s where dept IN（IS，MA，CS）；（where dept NOT IN C
14、S：排除CS系）,SC表原来值： Sno Cno grade Sno 20001 1 80 操作后： 200012 85 200023 90 20002 2 80,例5：查找所有姓“刘”的学生的姓名、学号和性别。 谓词LIKE可实现字符串的匹配，满足模糊查询的要求 格式：NOT LIKE 匹配串 ESCAPE 换码字符 含义：查找指定的属性列值与匹配串相匹配的元组。一个完整字符串通配符 % 和 %表示任意长度（长度可为0）的字符串：如：a%b表示以a开头，b结尾 任意长度的字符串（下划线）表示任意单个字符。如：a b表示a开头，b结尾的长度为3的任意字符串select sname，sno，se
15、xfrom Swhere sname LIKE 刘%；,其中：匹配串：,例6：查找名字中第二个字为“涛”字的学生的学号及姓名 Select sno，sname from s Where sname LIKE 涛%；“-”前一个字为汉字占二个字符位置。,例7：查找课程名为“DB ”开头，且倒数第3个字符为i的课号，课名和学分Select From cwhere cname LIKE DB % i ESCAPE ； 结果： Cno Cname credit8 DB Desing 410 DB Programing 3 例8：查找选课后，没有参加考试的学生的学号和课号Select sno，cnofr
16、om scWhere grade IS NULL（注：IS不能用“=”代替） 例9：查找选修3号课程的学生的学号及成绩（按降序排列）。Select sno，gradefrom scWhere cno=3order by grade DESC；,例10：统计查询的例子select count () select count（distinct sno）from s； 统计学生总人数 from sc； 统计选了课程的学生的人数Select max（grade）；查找1号课程考试的最高分数from scwhere cno=1； 例11：查找信息系“IS”选修了3门以上的课程的学生的学号：（假定SC中有
17、一列dept记录了系名）select snoFrom scWhere dept=ISgroup by snohaving count（）3；,（结果分组查询）Sno Cno dept1 1 IS1 2 IS1 3 IS1 4 IS2 1 IS2 2 IS2 3 IS2 4 IS,2、关联查询 多个表联接查询 等值与非等值的连接查询 a等值连接查询： b笛卡尔积的连接 c自然连接 Select s,sc select s,sc select sno,sname,sex,dept,cno,grade from s,sc from s,sc from s,sc where ssno=sc sno w
18、here s sno=sc sno,设：,S,SC,a、等值连接结果：,b、笛卡尔积连接结果产生：35=15个元组，产生无意义的元组，因此， 该连接很少使用。c、自然连接的结果，只是去掉重复的sno.sno sname sex dept cno grade, 自身连接 例：查找至少选择选修课程号为“1”号和为“2”号的学生的学号。 select xsno from SC x, SC y where xsno=ysno AND xcno=1 AND ycno=2;,结果：xsno20001,外连接为外连接符（或+）：它可将一些为空值列连接到结果表中。 将中C改写为外连接 select sno,
19、sname, sex, dept, cno,grade from s, sc where ssno=scsno(); 注：*出现连接符的右边称左外连接*出现连接符的左边称右外连接,结果：,嵌套结构的查询 例：查询选修课程号为“2”的学生姓名与学号 a. Select ssno, ssname （涉及表s和sc连接查找）：from s,sc s(sno, sname, sex, age, dept)where ssno=scsno AND cno=2; sc(sno, cno, grade) b. 写法：Select sno, sname （嵌套查询结构）from swhere sno IN(s
20、elect snofrom scwhere cno=2）; 注：嵌套结构层次分明，具有结构化程序设计特点嵌套结构比不嵌套结构查询效率高（因先作选择）子查询中不能出现order by子句，即不能针对子查询排序。,c.写法（使用存在量词的嵌套查询）select sno, snamefrom swhere EXISTS (selectfrom SCwhere SCSno=SSno AND Cno=2);,例：查找选修了课程名为“信息系统”的学生学号与姓名select sno, sname from swhere sno IN(select snofrom scwhere cno IN(select
21、cno from cwhere(cname=信息系统);,涉及：三个表：s(sno, sname, sex, age, dept)sc(sno, cno, grade)c(cno, cname, pno, credit),例：查找其他系中比IS系任何学生年龄小的学生名单select sname, agefrom swhere ageALL (select agefrom swhere dept=IS) AND deptISOrder by age DESC;,例：查询选修了全部课程的学生姓名 Select sname from s where NOT EXISTS(select from cw
22、here NOT EXISTS(select from scwhere sc.sno=ssno AND sc. cno= ccno);,(1)涉及s, sc, c三个表 (2)SQL无全程量词运算符，因此将题意转化为等价的存在量词的形式，查询这样的学生姓名，没有一门课是他不选的。(x)p(x(p),例：查找至少有一门课的成绩超过学号为20002的一门课成绩的学生学号select distinct snofrom scwhere gradesome (select gradefrom scwhere sno=20002),3.集合查询例：查找计算机系的学生及年龄不大于19岁的学生实际求计算机系的
23、所有学生与年龄不大于19岁其它系的学生的并集select from swhere dept=csUNIONSelect from swhere age=19;,第四节 SQL的数据更新,数据插入 数据删除 数据修改,更新包括：,一、数据插入,元组值的插入 查询结果的插入,包括：,1、元组值的插入一般句型：INSERT INTO 基本表名（列名表) VALUES (元组值) 一次 仅插入一个元组或 INSERT INTO 基本表名（列名表）（TABLE（元组值），（元组值），） 一次插入多个元组如：INSERT INTO SC （TABLE（20005，1，85），（20005，2，90），（2
24、0005，3，95）,2、查询结果的插入一般句型：INSERT INTO 基本表名（列名表）SELECT 查询语句把当前表中满足条件的元组送到已存在的表中。如：从基本表SC中，把平均成绩80的男学生的学号和平均成绩存入另一个已知的基本表SG（sno, sg1）中：INSERT INTO SG (sno, sg1)SELECT sno, AVG(grade) FROM SCWHERE sno IN (SELECT snoFROM SWHERE SEX=男)GROUP BY snoHAVING AVG (grade) 80;,二、数据的删除一般句型：DELETE FROM 基本表名 WHERE 条
25、件表达式从一个基本表中删除满足条件的元组 例：删除SC中1号课程中小于该课程平均成绩的成绩元组DELETE FROM SCWHERE cno=1AND grade(SELECT AVG (grade)FROM SCWHERE cno=1);,三、数据的修改修改基本表中元组的某些列值一般句型：UPDATE 基本表名SET 列名=值表达式，列名=值表达式，WHERE 条件表达式 例：将女同学的成绩提高10%UPDATE SCSET grade=grade11WHERE sno IN (select snofrom s where sex=女）,四、视图的更新操作 1、视图的概念 视图从一个或多个基
26、本表（或已定义的视图）导出的表，提供给用户查询DB中数据使用。视图是一个虚表，它的框架定义的属性存于数据字典库中，其数据仍在原来基本表中。视图的查询与基本表的查询使用select语句。视图的作用： （1）视图能简化用户的操作。通过视图可以从一个基本表或多个基本表中抽取用户感兴趣的数据，方便用户处理。 （2）视图对重构DB提供了一定程度的逻辑独立性。,例：将表s(sno, sname, sex, age, dept)垂直划分为两个表：sx(sno, sname, dept)sy(sno, sex, age) 若建立一个视图s:Create view s(sno, sname, sex, age,
27、 dept)asselect sxsno, sxsname, sysex, syage, sxdeptfrom sx, sywhere sxsno=sysno;这样尽管DB逻辑结构改变了，但应用程序不必修改，因为新建立的视图定义了用户原来的关系，使用户的外模式保持不变。,(3) 视图能对保密数据提供保护。,2、视图的更新操作 视图是不实际存储数据的虚表，因此： 对视图的更新，最终要转换为对基本表的更新。 必须对视图的更新操作作一些限制（查询不限制）。视图更新的三条规则： （1）不允许对从多个基本表作连接操作导出的视图作更新操作。（2）不允许对使用了分组（group by）和函数（如：MIN等）
28、操作导出的视图作更新。（3）如果视图从单个基本表，使用选择、投影操作导出的，并包含了基本表的主关键字或某个候选关键字，那么这样的视图称为“行列子集视图”，可作更新操作。,例：从s表中定义一个有关男同学的视图Create view S1( sno,snam,age)as select sno,sname,agefrom Swhere sex=男; 该视图从单基本表导出，且包含关键字sno,可以更新：执行插入操作：INSERT INTO S1VALUES（20006，王五，20）； 例：定义视图Create view SSG(sno, cnum, Avgg)as select sno,count(
29、sno),Avg(grade)from scwhere grade IS NOT NULLGroup by sno; SSG由分组和函数操作导出，据（2）规则不能更新。,第五节 嵌入式SQL的使用,交互式SQL：用户在终端上以命令形式使用 嵌入式SQL：在高级语言源程序中嵌入SQL语句,SQL语言使用形式：,一、嵌入式SQL的使用规定,扩充宿主语言的编译程序功能，使之能处理SQL语句 采用预处理方式处理SQL语句,嵌入式SQL 两种处理 方式：,宿主语言语句+SQL语句,预处理方式SQL语句的过程：,预备处理程序,宿主语言语句+函数调用,宿主语言编译程序,宿主语言目标程序,SQL函数定义库,S
30、QL与宿主语言的接口 SQL与宿主语言的接口是共享变量。 共享变量由宿主语言程序定义，再用SQL的DECLARE语句说明，随后SQL语句可引用这些变量。 SQL2规定，SQLSTATE是一个特殊的共享变量，起着解释SQL语句执行状况的作用（如报错）。,嵌入式SQL的使用规定 （1）在SQL语句前使用前缀标识“EXEC SQL”“END-EXEC”（后缀标识）括起来，以示区别宿主语言语句。（注：END-EXEC后缀标识在C和PASCAL语言中用“；”号表示）。 （2）允许嵌入的SQL语句引用宿主语言的程序变量（称为共享变量）， 但有两条规定： a、引用时，变量前必须加冒号“：”作前缀标识，以示与
31、数据库中变量有区别。 b、这些变量由宿主语言的程序定义，并用SQL的DECLARE语句说明。 如：C语言程序中使用如下形式定义共享变量：EXEC SQL BEGIN DECLARE SECTION；Char Sno5,name9；Char SQLSTATE6；EXEC SQL END DECLARE SECTION；,（3）SQL的集合处理方式与宿主语言单记录处理方式的协调由于SQL语句处理的是记录集合，而宿主语言语句一次只能处理一个记录，因此需要用游标（cursor）机制，把集合操作转换成单记录处理方式。,与游标有关的SQL语句有下列四个： a、游标定义语句：游标是与某一查询结果相联系的符号
32、名。定义形式：EXEC SQL DECLARE 游标名CURSOR FORSELECT语句END-EXECb、游标打开语句（OPEN）：执行游标定义中的SELECT语句，使游标（实际是指针）指向查询结果的第一行之前。语句型：EXEC SQL OPEN 游标名END-EXEC,c、游标推进语句（FETCH）：游标推进一行，将游标所指的当前行的值送共享变量。句型：EXEC SQL FETCH FROM游标名INTO变量表END-EXEC注：FETCH常置于宿主语言程序的循环语句中。d、游标关闭语句（CLOSE）句型：EXEC SQL CLOSE游标名END-EXEC注：在游标打开后，可修改和删除游
33、标所指的元组。,二、嵌入式SQL的使用技术,涉及游标机制的使用技术 不涉及游标机制的使用技术,方式：,1、不涉及游标的SQL DML语句 INSERT，DELETE和UPDATE语句加上前缀标识“EXEC SQL”可嵌入使用 SELECT语句查找一个元组时，直接将该元组转送共享变量中。 如：EXEC SQL SELECT sname,age,sex/在s中寻找学生姓名、年龄和性别/INTO :sn，:sa，:ssFROM SWHERE S#=:give；,2、涉及游标的SQL DML语句 a、SELECT语句使用当查找多个元组时，则用游标机制将多个元组一次一个从DB中读出并处理。 具体过程：
34、用游标定义语句定义一个游标与某一个SELECT语句对应。 用游标打开语句（OPEN）打开，使游标指向满足条件的元组之前。 每执行一次FECTH，游标指向下一个满足条件的元组，并将其值送共享变量。继续循环。 关闭语句（CLOSE）关闭游标。,例：在SC基本表中查找某学生（学号由give给出）的学习成绩信息（S,C,GRADE），下面是该查询的C语言函数定义：,# define NOM ! (strcmp (SQLSTATE, “02000”) void sel( ) EXEC SQL BEGIN DECLARE SECTION;Char sno5,cno6,give5;Int g;Char SQ
35、LSTATE6;EXEC SQL END DECLARE SECTION;EXEC SQL DECLARE SCX CURSOR FORSELECT S, C, gradeFROM SC WHERE S=:give;EXEC SQL OPEN SCX;,While(1) EXEC SQL FETCH FROM SCXINTO :sno, :cno, :g;If (NOM) break;Printf (“%s, %s,%d”,sno,cno,g); EXEC SQL CLOSE SCX; ,b、游标指向元组的修改或删除操作涉及多个元组的修改或删除用游标机制。如：上例对查到的成绩删除不及格的成绩及
36、将成绩6069改为70分：则上例中将while（1）中增加如下语句：if (g60) EXEC SQL DELETE FROM SCWHERE CURRENT OF SCX;else if (g70)EXEC SQL UPDATE SC SET grade=70WHERE CURRENT OF SCX;g=70;,c、卷游标的定义与推进上述游标法只能使游标从头到尾顺序指向要查的元组，且不能返回查找。SQL2提供卷游标（scroll cursor）技术，使游标进退自如定义句型：EXEC SQL DECLARE 游标名SCROLL CURSOR FORSELECT语句END-EXEC卷游标打开，关
37、闭同游标一样。卷游标推进句型：,NEXT PRIOR FIRST LAST RELATIVE 整数 ABSOLUTE整数,EXEC SQL FETCH,FROM游标名INTO变量名表END-EXEC,第三章练习题,一.填空题 1.传统关系模型中的术语与SQL中术语存在如下对应关系,关系模式在SQL中称为_;存储模式称为_;子模式称为_。2.视图是一个虚表，它是一个从_中导出的表。3.SELECT语句中，_子句用于选择满足给定条件的元组,使用_子句可按指定的列的值分组,同时使用_子句提取满足条件的元组。4.SQL语言的数据定义功能包括_，_，_和_。5.SQL语言有两种使用方式,分别是_方式和_
38、方式。,第三章练习题,二.已知DBS中包含了三个基本表GOODS(G#,GNAME,PRICE,TYPE,FACT)其中商品表中，G#：商品号,GNAME:商品名，PRICE：单价，TYPE：型号，FACT：制造商；商场基本表：SHOPS（S#，SNAME，ADDR，MANAG）。其中分别为：商场号、商场名、地址和经理名；销售基本表SALES（S#，G#，QTY）其中QTY为数量。 1.试用SQL语句完成下列查询 (1)查询所有电视机的生产厂商、型号、单价； (2)查询同时生产电视机和电冰箱的制造商; (3)查询“吉利”商场所销售的各种商品的商品号和数量； (4)查询销售量最高的商场号和所销售的商品号。 2.试用SQL对基本表做创建和更新操作 (1)创建基本表GOODS(类型、长度自定）； (2)将“南华”厂的所有产品的名称、型号和单价插到一个已存在的基本表 A（GN，GTYPE，PRICE）中； (3)将总销售量低于1000的所有商品的价格降低10%。,
