Oracle9i第4章 数据库的查询和视图免费下载7859.pptx

《Oracle9i第4章 数据库的查询和视图免费下载7859.pptx》由会员分享，可在线阅读，更多相关《Oracle9i第4章 数据库的查询和视图免费下载7859.pptx（63页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、4.1连接、选择和投影 4.1.1选择【例例4.1】学生情况表如表4.1所示。学生表中性别为女且平均成绩在80分以上的行形成一个新表，该选择运算的结果如表4.2所示。1第4章数据库的查询和视图4.1.2投影【例例4.2】若在表中对“姓名”和“平均成绩”投影，该查询得到如表所示的新表。2第4章数据库的查询和视图4.1.3连接【例例4.3】若表A和B分别如表4.4和表4.5所示，则连接后结果如表4.6所示。3第4章数据库的查询和视图4.1.3连接【例例4.4】若表A和表B分别如表4.7和表4.8所示，自然连接后的新表C如表4.9所示。4第4章数据库的查询和视图4.2 数据库的查询 语法格式：语法格

2、式：SELECT select_list FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC|DESC 5第4章数据库的查询和视图4.2.1选择列 语法格式：语法格式：SELECT ALL|DISTINCT 其中select_list指出了结果的形式，select_list的主要格式为：*/*选择当前表或视图的所有列*/|table_name|view_name|table_alias .*/*选择指

3、定的表或视图的所有列*/|colume_name|expression AS column_alias /*选择指定的列*/|column_alias=expression /*选择指定列并更改列标题*/,n 6第4章数据库的查询和视图4.2.1选择列选择一个表中指定的列选择一个表中指定的列 语法格式：语法格式：SELECT column_name ,column_nameFROM table_name WHERE search_condition【例例4.5】查询XSCJ数据库的XS表中各个同学的XM、XH和ZXF。SELECT XH,XM,ZXF FROM XS;【例例4.6】查询XS表中

4、ZXF大于45同学的XH、XM和ZXF。SELECT XH,XM,ZXFFROM XSWHERE ZXF45;7第4章数据库的查询和视图4.2.1选择列【例例4.7】查询XS表中的所有列。SELECT*FROM XS;该语句等价于语句：SELECT XH,XM,ZYM,XBCSSJ,ZXF,BZFROM XS;8第4章数据库的查询和视图4.2.1选择列修改查询结果中的列标题修改查询结果中的列标题【例例4.8】查询XS表中计算机同学的XH、XM和ZXF，结果中各列的标题分别指定为学号、姓名和总学分。SELECT XH AS学号，XM AS 姓名，ZXF AS总学分/*也可以省略AS*/FROM

5、XSWHERE ZYM=计算机;9第4章数据库的查询和视图4.2.1选择列计算列值计算列值 格式为：SELECT expression ,expression【例例4.9】创建产品销售数据库XSH，并在其中创建产品表CP，其表结构如表所示。10第4章数据库的查询和视图4.2.1选择列下列语句将列出产品名称和产品总值：SELECT CPMC AS 产品名称，JG*KCL AS产品总值FROM CP;11第4章数据库的查询和视图4.2.1选择列消除结果集中的重复行消除结果集中的重复行格式是：SELECT DISTINCT column_name ,column_name【例例4.10】对XSCJ数

6、据库的XS表只选择ZYM和ZXF，消除结果集中的重复行。SELECT DISTINCT ZYM AS 专业名，ZXF AS 总学分;该语句执行的结果为：专业名 总学分计算机 48计算机 50计算机 52计算机 54通信工程 40通信工程 42通信工程 44通信工程 50 12第4章数据库的查询和视图4.2.1选择列【例例4.11】以下的SELECT语句对XSCJ数据库的XS表选择ZYM和ZXF，不消除结果集中的重复行。SELECT ALL ZYM AS 专业名，ZXF AS 总学分;说明说明：当SELECT语句中不写ALL与DISTINCT时，默认值为ALL。13第4章数据库的查询和视图4.2

7、.2选择行 WHERE子句必须紧跟FROM子句之后，其基本格式为：WHERE 其中search_condition为查询条件，格式为：NOT|()AND|OR NOT|(),n 其中predicate为判定运算，结果为TRUE、FALSE或UNKNOWN，格式为：expression =|=|!=expression/*比较运算*/|string_expression NOT LIKE string_expression ESCAPE escape_character /*字符串模式匹配*/|expression NOT BETWEEN expression AND expression/*指

8、定范围*/|expression IS NOT NULL /*是否空值判断*/|expression NOT IN(subquery|expression,n)/*IN子句*/|EXIST(subquery)/*EXIST子查询*/14第4章数据库的查询和视图4.2.2选择行 表达式比较表达式比较 比较运算的格式为：expression =|=|!=expression【例例4.12】(1)查询XSH数据库CP表中库存量在500以上的产品情况。SELECT*FROM CP WHERE KCL 500;(2)查询XSCJ数据库XS表中通信工程专业总学分大于等于42的同学的情况。SELECT*FR

9、OM XS WHERE ZYM=通信工程 and ZXF=42;15第4章数据库的查询和视图4.2.2选择行模式匹配模式匹配 LIKE谓词表达式的格式为：string_expression NOT LIKE string_expression【例例4.13】查询XSH数据库CP表中产品名含有“冰箱”的产品情况。SELECT*FROM CP WHERE CPMC LIKE%冰箱%;【例例4.14】查询XSCJ数据库XS表中姓“王”且单名的学生情况。SELECT*FROM XS WHERE 姓名 LIKE 王_;16第4章数据库的查询和视图4.2.2选择行范围比较范围比较 格式为：expressi

10、on NOT BETWEEN expression1 AND expression2【例例4.15】(1)查询XSH数据库CP表中价格在2000元与4000元之间的产品情况。SELECT*FROM CP WHERE JG BETWEEN 2000 AND 4000;(2)查询XSCJ数据库XS表中不在1979年出生的学生情况。SELECT*FROM XS WHERE CSSJ NOT BETWEEN TO_DATE(19790101,YYYYMMDD)and TO_DATE(19791231,YYYYMMDD);17第4章数据库的查询和视图4.2.2选择行IN关键字指定值表的格式为：关键字指定

11、值表的格式为：expression IN(expression,n)【例例4.16】查询XSH数据库CP表中库存量为“200”、“300”和“500”的情况。SELECT*FROM CP WHERE KCL IN（200，300，500）;该语句与下列语句等价：SELECT*FROM CP WHERE KCL=200 OR KCL=300 OR KCL=500;18第4章数据库的查询和视图4.2.2选择行空值比较空值比较 格式为：expression IS NOT NULL【例例4.17】查询XSCJ数据库中总学分尚不定的学生情况。SELECT*FROM XS WHERE ZXF IS NUL

12、L;19第4章数据库的查询和视图4.2.2选择行子查询子查询 IN子查询子查询 格式为：expression NOT IN (subquery)【例例4.18】在XSCJ数据库中有描述课程情况的表KC和描述学生成绩表的表XS_KC(表的结构和样本数据见附录A)。查找选修了课程号为101的课程的学生的情况：SELECT*FROM XSWHERE XH IN (SELECT XH FROM XS_KC WHERE KCH=101);20第4章数据库的查询和视图4.2.2选择行【例例4.19】查找未选修离散数学的学生的情况。SELECT XH,XM,ZYM,ZXF FROM XSWHERE 学号 N

13、OT IN(SELECT 学号FROM XS_KCWHERE 课程号 IN (SELECT 课程号 FROM KC WHERE 课程名=离散数学);21第4章数据库的查询和视图4.2.2选择行比较子查询 格式为：expression|=|!=|ALL|SOME|ANY (subquery)【例例4.20】查找比所有计算机系学生年龄都大的学生。SELECT*FROM XSWHERE CSSJ=ANY(SELECT CJ FROM XS_KC WHERE KCH=101);23第4章数据库的查询和视图4.2.2选择行EXISTS子查询 格式为：NOT EXISTS(subquery)【例例4.22

14、】查找选修206号课程的学生姓名。SELECT XM FROM XS WHERE EXISTS (SELECT*FROM XS_KC WHERE XH=XS.XH AND KCH=206);24第4章数据库的查询和视图4.2.2选择行【例例4.23】查找选修了全部课程的同学的姓名。SELECT XM FROM XS WHERE NOT EXISTS (SELECT*FROM KC WHERE NOT EXISTS (SELECT*FROM XS_KC WHERE XH=XS.XH AND KCH=KC.KCH);25第4章数据库的查询和视图4.2.3查询对象【例例4.24】查找选修了00110

15、2号同学所选修的全部课程的同学的学号。SELECT DISTINCT XHFROM XS_KC AS CJ1WHERE NOT EXISTS(SELECT*FROM XS_KC AS CJ2WHERE CJ2.XH=001102 AND NOT EXISTS(SELECT*FROM XS_KC AS CJ3WHERE CJ3.XH=CJ1.XH AND CJ3.KCH=CJ2.KCH);26第4章数据库的查询和视图4.2.3查询对象【例例4.25】在XS表中查找1980年1月1日以前出生的学生的姓名和专业名。SELECT XM,ZYMFROM (SELECT*FROM XS WHERE CSS

16、J=80;执行结果为：姓名 成绩王燕 81李方方 80林一帆 87张蔚 8929第4章数据库的查询和视图4.2.4连接【例例4.29】查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SELECT XS.XH,XM,KCM,CJ FROM XS,KC,XS_KC WHERE XS.XH=XS_KC.XH AND KC.KCH=XS_KC.KCH AND KCM=计算机基础 AND CJ=80;30第4章数据库的查询和视图4.2.4连接 以以JOIN关键字指定的连接关键字指定的连接 连接表的格式为：ON|CROSS JOIN|内连接【例例4.30】查找XSCJ数据库每

17、个学生的情况以及选修的课程情况。SELECT*FROM XS INNER JOIN XS_KC ON XS.XH=XS_KC.XH;【例例4.31】用FROM的JOIN关键字表达下列查询：查找选修了206课程且成绩在80分以上的学生姓名及成绩。SELECT XM,CJ FROM XS JOIN XS_KC ON XS.XH=XS_KC.XH WHERE KCH=206 AND CJ=80;31第4章数据库的查询和视图4.2.4连接【例例4.32】用FROM的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SELECT XS.学号,XM,

18、CKM,CJ FROM XS JOIN XS_KC JOIN KC ON XS_KC.KCH=KC.KCH ON XS.XH=XS_KC.XH WHERE KCM=计算机基础 AND CJ=80;【例例4.33】查找不同课程成绩相同的学生的学号、课程号和成绩。SELECT a.XH,a.KCH,b.KCH,a.CJ FROM XS_KC a JOIN XS_KC b ON a.CJ=b.CJ AND a.XH=b.XH AND a.KCH!=b.KCH;32第4章数据库的查询和视图4.2.4连接外连接 左外连接（LEFT OUTER JOIN）右外连接（RIGHT OUTER JOIN）完全外

19、连接（FULL OUTER JOIN）【例例4.34】查找所有学生情况及他们选修的课程号，若学生未选修任何课，也要包括其情况。SELECT XS.*,KCH FROM XS LEFT OUTER JOIN XS_KC ON XS.XH=XS_KC.XH;【例例4.35】查找被选修了的课程的选修情况和所有开设的课程名。SELECT XS_KC.*,KCM FROM XS_KC RIGHT JOIN KC ON XS_KC.KCH=KC.KCH;33第4章数据库的查询和视图4.2.4连接交叉连接【例例4.36】列出学生所有可能的选课情况。SELECT XH,XM,KCH,KCM FROM XS C

20、ROSS JOIN KC;34第4章数据库的查询和视图4.2.5汇总 统计函数统计函数 SUM和AVG函数 语法格式为：SUM/AVG(ALL|DISTINCT expression)【例例4.37】求选修101课程的学生的平均成绩。SELECT AVG(CJ)AS 课程101平均成绩 FROM XS_KC WHERE KCH=101;MAX和MIN函数 语法格式为：MAX/MIN(ALL|DISTINCT expression)【例例4.38】求选修101课程的学生的最高分和最低分。SELECT MAX(CJ)AS 课程101的最高分,MIN(CJ)AS 课程101的最低分 FROM XS_

21、KC WHERE KCH=101;35第4章数据库的查询和视图4.2.5汇总COUNT函数格式为：COUNT(ALL|DISTINCT expression|*)【例例4.39】(1)求学生的总人数。SELECT COUNT(*)AS 学生总数 FROM XS;COUNT(*)不需要任何参数。(2)求选修了课程的学生总人数。SELECT COUNT(DISTINCT XH)FROM XS_KC;(3)统计离散数学课程成绩在85分以上的人数。SELECT COUNT(CJ)AS 离散数学85分以上的人数 FROM XS_KC WHERE CJ=85 AND KCH=(SELECT KCH FRO

22、M KCWHERE KCM=离散数学);36第4章数据库的查询和视图4.2.5汇总GROUP BY子句子句 格式为：GROUP BY ALL group_by_expression,n【例例4.40】将XSCJ数据库中各专业名输出。SELECT ZYM AS 专业名 FROM XS GROUP BY ZYM;【例例4.41】求XSCJ数据库中各专业的学生数。SELECT ZYM AS 专业名,COUNT(*)AS 学生数 FROM XS GROUP BY ZYM;【例例4.42】求被选修的各门课程的平均成绩和选修该课程的人数。SELECT KCH AS课程号,AVG(成绩)AS 平均成绩,CO

23、UNT(XH)AS 选修人数 FROM XS_KC GROUP BY KCH;37第4章数据库的查询和视图4.2.5汇总HAVING子句子句 格式为：HAVING 【例例4.43】查找XSCJ数据库中平均成绩在85分以上的学生的学号和平均成绩。SELECT XH AS 学号,AVG(CJ)AS 平均成绩 FROM XS_KC GROUP BY XH HAVING AVG(CJ)=85;【例例4.44】查找选修课程超过2门且成绩都在80分以上的学生的学号。SELECT XH AS学号 FROM XS_KC WHERE CJ=80 GROUP BY XH HAVING COUNT(*)2;38第4

24、章数据库的查询和视图4.2.5汇总【例例4.45】查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。SELECT XH AS学号,AVG(CJ)AS 平均成绩 FROM XS_KC WHERE XH IN (SELECT XH FROM XS WHERE ZYM=通信工程)GROUP BY XH HAVING AVG(CJ)=85 39第4章数据库的查询和视图4.2.6排序 ORDER BY子句的格式为：ORDER BY order_by_expression ASC|DESC ,n【例例4.46】将通信工程专业的学生按出生时间先后排序。SELECT*FROM XS WHERE ZY

25、M=通信工程ORDER BY CSSJ;【例例4.47】将计算机专业学生的“计算机基础”课程成绩按降序排列。SELECT XM AS 姓名,KCM AS 课程名,CJ AS 成绩 FROM XS,KC,XS_KC WHERE XS.XH=XS_KC.XH AND XS_KC.KCH=KC.KCHAND KCM=计算机基础 AND ZYM=计算机 ORDER BY CJ DESC;40第4章数据库的查询和视图4.2.7 SELECT语句的UNION子句 格式为：|()UNION A LL|()UNION A LL|()n【例例4.48】设在XSCJ数据库中建两个表：数学系学生、外语系学生，表结构

26、与XS表相同，两个表分别存储数学系和外语系的学生情况，下列语句将这两个表的数据合并到XS表中。SELECT*FROM XS UNION ALL SELECT*FROM 数学系学生 UNION ALL SELECT*FROM 外语系学生;41第4章数据库的查询和视图4.3数据库的视图 4.3.1视图的概念 视图可以由以下任意一项组成：一个基表的任意子集 两个或者两个以上基表的合集 两个或者两个以上基表的交集 对一个或者多个基表运算的结果集合 另一个视图的子集 视图有下列优点：为用户集中数据，简化用户的数据查询和处理。屏蔽数据库的复杂性。简化用户权限的管理。便于数据共享。可以重新组织数据以便输出到

27、其它应用程序中。42第4章数据库的查询和视图4.3.2创建视图 在在OEM中创建视图中创建视图 使用向导创建视图使用向导创建视图 图形工具创建视图图形工具创建视图 43第4章数据库的查询和视图4.3.2创建视图 使用使用CREATE VIEW语句创建视图语句创建视图 语法格式：语法格式：CREATE OR REPLACE FORCE|NOFORCE VIEW schema.view_name (column_name ,n )AS select_statementWITH CHECK OPTIONCONSTRAINT constraint_nameWITH READ ONLY 44第4章数据库

28、的查询和视图4.3.2创建视图【例例4.49】创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。CREATE OR REPLACE VIEW CS_KC AS SELECT XS.XH,KCH,CJ FROM XS,XS_KC WHERE XS.XH=XS_KC.XH AND ZYM=计算机 WITH CHECK OPTION;45第4章数据库的查询和视图4.3.2创建视图【例例4.50】创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。CR

29、EATE OR REPLACE VIEW CS_KC_AVG(num,score_avg)AS SELECT XH,AVG(CJ)FROM CS_KC GROUP BY XH;46第4章数据库的查询和视图4.3.3查询视图 通过界面查看通过界面查看 47第4章数据库的查询和视图4.3.3查询视图SELECT语句查询视图语句查询视图【例例4.51】查找计算机专业的学生学号和选修的课程号。SELECT XH,KCH FROM CS_KC;【例例4.52】查找平均成绩在80分以上的学生的学号和平均成绩。CREATE OR REPLACE VIEW XS_KC_AVG(num,score_avg)AS

30、 SELECT XH,AVG(CJ)FROM XS_KCGROUP BY XH;再对XS_KC_AVG视图进行查询。SELECT*FROM XS_KC_AVG WHERE score_avg=80;48第4章数据库的查询和视图4.3.4 更新视图 可更新视图可更新视图 没有使用连接函数、集合运算函数和组函数；创建视图的SELECT语句中没有聚合函数且没有GROUP BY、ONNECT BY、START WITH子句及DISTINCT关键字；创建视图的SELECT语句中不包含从基表列通过计算所得的列；创建视图没有包含只读属性。【例例4.53】在XSCJ数据库中使用以下语句创建可更新视图CS_XS

31、1。CREATE OR REPLACE VIEW CS_XS1 AS SELECT*FROM XS WHERE ZYM=计算机;49第4章数据库的查询和视图4.3.4 更新视图插入数据插入数据【例例4.54】向CS_XS1视图中插入一条记录：（001115,刘明仪,计算机,男,1978-3-2,50,三好学生）INSERT INTO CS_XS1VALUES(001115,刘明仪,计算机,男,TO_DATE(19780302,YYYYMMDD,50,三好学生);修改数据修改数据【例4.55】将CS_XS视图中所有学生的总学分增加8。UPDATE CS_XS SET ZXF=ZXF+8;【例例4

32、.56】将CS_KC视图中学号为001101的学生的101课程成绩改为90。UPDATE CS_KC SET 成绩=90 WHERE 学号=001101 AND 课程号=101;50第4章数据库的查询和视图4.3.4 更新视图删除数据删除数据【例例4.57】删除CS_XS中女同学的记录。DELETE FROM CS_XS WHERE XB=女;51第4章数据库的查询和视图4.3.5修改视图的定义 通过通过OEM修改视图修改视图 52第4章数据库的查询和视图4.3.5修改视图的定义使用使用SQL命令修改视图命令修改视图【例例4.58】修改视图CS_KC的定义，包括学号、姓名、选修的课程号、课程名

33、和成绩。CREATE OR REPLACE FORCE VIEW CS_KCASSELECT XS.XH,XS.XM,XS_KC.KCH,KC.KCM,CJFROM XS,XS_KC,KCWHERE XS.XH=XS_KC.XH AND XS_KC.KCH=KC.KCH AND ZYM=通信工程WITH CHECK OPTION;53第4章数据库的查询和视图4.3.6删除视图 通过通过OEM删除视图删除视图54第4章数据库的查询和视图4.3.6删除视图删除视图的删除视图的PL/SQL语句语句 格式为：DROP VIEW schema.view_name例如：DROP VIEW CS_KC;55

34、第4章数据库的查询和视图4.4 格式化输出结果 4.4.1替换变量&替换变量替换变量【例例4.59】查询XSCJ数据库XS表计算机专业的同学情况。SELECT XH AS 学号,XM AS 姓名FROM XSWHERE ZYM=&specialty_name;【例【例4.60】查找平均成绩在80分以上的学生的学号、姓名和平均成绩。SET VERIFY ONSELECT*FROM XS_KC_AVG WHERE score_avg=score_avg;56第4章数据库的查询和视图4.4.1替换变量【例例4.61】查找选修了“离散数学”课程的学生学号、姓名、课程名及成绩。SELECT XS.XH,

35、&name,KCM,&columnFROM XS,&kc,XS_KCWHERE XS.XH=XS_KC.XH AND&conditionAND KCM=&kcmORDER BY&column;57第4章数据库的查询和视图4.4.1替换变量&替换变量替换变量 【例例4.62】查找选修了“离散数学”课程的学生学号、姓名、课程名及成绩。执行过程和结果为：输入 name 的值：XM输入 column 的值：CJ原值 1：SELECT XS.XH,&name,KCM,&column新值 1：SELECT XS.XH,XM,KCM,CJ原值 2：FROM XS,&kc,XS_KC新值 2：FROM XS,

36、kc,XS_KC输入 condition 的值：KC.KCH=XS_KC.KCH输入 kcm 的值：离散数学原值 3：WHERE XS.XH=XS_KC.XH AND&condition AND KCM=&kcm新值 3：WHERE XS.XH=XS_KC.XH AND KC.KCH=XS_KC.KCH AND KCM=离散数学输入 column 的值:NAME原值 4：ORDER BY&column新值 4：ORDER BY NAME58第4章数据库的查询和视图4.4.1替换变量为了避免为同一个变量提供两个不同的值，且使系统为同一个变量值提示一次信息，可以使用“&”替换变量。【例例4.63】

37、查询选修课程超过2门且成绩在75分以上的学生的学号。SELECT&columnFROM XS_KCWHERE CJ=75GROUP BY&columnHAVING COUNT(*)2;59第4章数据库的查询和视图4.4.1替换变量DEFINE和和ACCEPT命令命令 语法格式：语法格式：DEFINE variable=value【例例4.64】定义一个变量specialty，并为它赋值“通信工程”。然后，显示该变量信息。DEFINE specialty=通信工程DEFINE specialty显示结果为：DEFINE SPECIALTY=通信工程(CHAR)【例例4.65】查询专业为通信工程的

38、学生情况，引用上例中定义的变量specialty。SELECT XH,XM,XB,CSSJ,ZXFFROM XSWHERE ZYM=&specialty;60第4章数据库的查询和视图4.4.1替换变量语法格式：语法格式：ACCEPT variable datatype FORMAT formatPROMPT text HIDE【例例4.66】使用ACCEPT定义一个变量num，且指定提示文本。根据这个变量的值查询选修该课程的学生学号、课程名和成绩情况。ACCEPT num PROMP 请输入课程号：请输入课程号：101SELECT XH,KCM,CJFROM XS_KC,KCWHERE XS_

39、KC.KCH=KC.KCH AND KC.KCH=&numORDER BY CJ;原 值 3：WHERE XS_KC.KCH=KC.KCH AND KC.KCH=&num新值 3：XS_KC.KCH=KC.KCH AND KC.KCH=101 61第4章数据库的查询和视图4.4.2定制SQL*Plus环境 show all显示格式的设置显示格式的设置 SET命令控制当前环境的设置。命令控制当前环境的设置。语法格式：语法格式：SET system_variable value 页和行的大小页和行的大小SET LINESIZE 50SET PAGESIZE 30 页头标页头标TTITLE 选修计算机基础课程学生成绩报表 页脚标页脚标BTITLE-report1.sql-RIGHT 62第4章数据库的查询和视图4.4.2定制SQL*Plus环境格式化输出表列格式化输出表列 COLUMN FIRST_NAME FORMAT a8 WRAP HEADING FirstNameCOLUMN LAST_NAME FORMAT a8 HEADING LastName 断开逻辑(Break Logic)断开和跳过(Break and Skip)使用COMPUTE命令 63第4章数据库的查询和视图