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

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

2、WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC 54.2.1选择列 语法格式：语法格式：SELECT ALL | DISTINCT 其中select_list指出了结果的形式，select_list的主要格式为： * /*选择当前表或视图的所有列*/ | table_name | view_name | table_alias . * /*选择指定的表或视图的所有列*/ | colume_name | expressi

3、on AS column_alias /*选择指定的列*/ | column_alias = expression /*选择指定列并更改列标题*/ , n 64.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表中ZXF大于45同学的XH、XM和ZXF。SELECT XH,XM,ZXF

4、FROM XSWHERE ZXF45;74.2.1选择列【例例4.7】查询XS表中的所有列。SELECT * FROM XS;该语句等价于语句：SELECT XH,XM,ZYM,XBCSSJ,ZXF,BZFROM XS;84.2.1选择列修改查询结果中的列标题修改查询结果中的列标题 【例例4.8】查询XS表中计算机同学的XH、XM和ZXF，结果中各列的标题分别指定为学号、姓名和总学分。SELECT XH AS学号，XM AS 姓名，ZXF AS总学分 /*也可以省略AS*/FROM XSWHERE ZYM=计算机;94.2.1选择列计算列值计算列值 格式为：SELECT expression

5、, expression 【例例4.9】创建产品销售数据库XSH，并在其中创建产品表CP，其表结构如表所示。 104.2.1选择列下列语句将列出产品名称和产品总值：SELECT CPMC AS 产品名称，JG * KCL AS产品总值FROM CP; 114.2.1选择列消除结果集中的重复行消除结果集中的重复行格式是： SELECT DISTINCT column_name , column_name【例例4.10】对XSCJ数据库的XS表只选择ZYM和ZXF，消除结果集中的重复行。SELECT DISTINCT ZYM AS 专业名，ZXF AS 总学分; 该语句执行的结果为：专业名 总学分

6、计算机 48计算机 50计算机 52计算机 54通信工程 40通信工程 42通信工程 44通信工程 50 124.2.1选择列【例例4.11】以下的SELECT语句对XSCJ数据库的XS表选择ZYM和ZXF，不消除结果集中的重复行。SELECT ALL ZYM AS 专业名，ZXF AS 总学分;说明说明：当SELECT语句中不写ALL与DISTINCT时，默认值为ALL。 134.2.2选择行 WHERE子句必须紧跟FROM子句之后，其基本格式为：WHERE 其中search_condition为查询条件，格式为： NOT | ( ) AND | OR NOT | () ,n 其中predi

7、cate为判定运算，结果为TRUE、FALSE或UNKNOWN，格式为： expression = | | | = | | != expression /*比较运算*/ | string_expression NOT LIKE string_expression ESCAPE escape_character /*字符串模式匹配*/ | expression NOT BETWEEN expression AND expression /*指定范围*/ | expression IS NOT NULL /*是否空值判断*/ | expression NOT IN ( subquery | exp

8、ression ,n ) /*IN子句*/ | EXIST ( subquery ) /*EXIST子查询*/ 144.2.2选择行 表达式比较表达式比较 比较运算的格式为：expression = | | | = | | != expression【例例4.12】(1) 查询XSH数据库CP表中库存量在500以上的产品情况。SELECT * FROM CP WHERE KCL 500; (2) 查询XSCJ数据库XS表中通信工程专业总学分大于等于42的同学的情况。SELECT * FROM XS WHERE ZYM= 通信工程 and ZXF=42;154.2.2选择行模式匹配模式匹配 LI

9、KE谓词表达式的格式为：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 王_; 164.2.2选择行范围比较范围比较 格式为：expression NOT BETWEEN expression1 AND expression2【例例4.15】(1) 查询XSH数据库CP表中价格在20

10、00元与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);174.2.2选择行IN关键字指定值表的格式为：关键字指定值表的格式为：expression IN ( expression ,n)【例例4.16】查询XSH数据库CP表中库存量为“200”、“300”和“50

11、0”的情况。SELECT * FROM CP WHERE KCL IN （200，300，500）;该语句与下列语句等价：SELECT * FROM CP WHERE KCL=200 OR KCL=300 OR KCL=500; 184.2.2选择行空值比较空值比较 格式为：expression IS NOT NULL【例例4.17】查询XSCJ数据库中总学分尚不定的学生情况。 SELECT * FROM XS WHERE ZXF IS NULL; 194.2.2选择行子查询子查询 IN子查询子查询 格式为：expression NOT IN ( subquery )【例例4.18】在XSCJ

12、数据库中有描述课程情况的表KC和描述学生成绩表的表XS_KC(表的结构和样本数据见附录A)。查找选修了课程号为101的课程的学生的情况：SELECT * FROM XSWHERE XH IN ( SELECT XH FROM XS_KC WHERE KCH = 101 );204.2.2选择行【例例4.19】查找未选修离散数学的学生的情况。SELECT XH,XM,ZYM,ZXF FROM XSWHERE 学号 NOT IN ( SELECT 学号FROM XS_KCWHERE 课程号 IN ( SELECT 课程号 FROM KC WHERE 课程名 = 离散数学 );214.2.2选择行比

13、较子查询 格式为：expression | | = | != | ALL | SOME | ANY ( subquery )【例例4.20】查找比所有计算机系学生年龄都大的学生。SELECT * FROM XSWHERE CSSJ = ANY ( SELECT CJ FROM XS_KC WHERE KCH = 101);234.2.2选择行EXISTS子查询 格式为： NOT EXISTS ( subquery )【例例4.22】查找选修206号课程的学生姓名。SELECT XM FROM XS WHERE EXISTS ( SELECT * FROM XS_KC WHERE XH=XS.X

14、H AND KCH=206 );244.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) );254.2.3查询对象 【例例4.24】查找选修了001102号同学所选修的全部课程的同学的学号。 SELECT DISTINCT XHFROM XS_KC AS CJ1WHERE NOT EXISTS( SELECT *FROM XS_KC AS

15、 CJ2WHERE CJ2.XH = 001102 AND NOT EXISTS( SELECT *FROM XS_KC AS CJ3WHERE CJ3.XH= CJ1.XH AND CJ3.KCH = CJ2. KCH);264.2.3查询对象【例例4.25】在XS表中查找1980年1月1日以前出生的学生的姓名和专业名。SELECT XM,ZYMFROM (SELECT * FROM XS WHERE CSSJ= 80;执行结果为：姓名 成绩王燕 81李方方 80林一帆 87张蔚 89294.2.4连接【例例4.29】查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成

16、绩。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;304.2.4连接 以以JOIN关键字指定的连接关键字指定的连接 连接表的格式为： ON | CROSS JOIN | 内连接 【例例4.30】查找XSCJ数据库每个学生的情况以及选修的课程情况。SELECT * FROM XS INNER JOIN XS_KC ON XS.XH = XS_KC.XH;【例例4.31】用FROM的JOIN关键字表达下

17、列查询：查找选修了206课程且成绩在80分以上的学生姓名及成绩。SELECT XM , CJ FROM XS JOIN XS_KC ON XS.XH = XS_KC.XH WHERE KCH = 206 AND CJ=80; 314.2.4连接【例例4.32】用FROM的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SELECT XS.学号 , XM , CKM , CJ FROM XS JOIN XS_KC JOIN KC ON XS_KC.KCH = KC.KCH ON XS.XH = XS_KC.XH WHERE KCM = 计

18、算机基础 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;324.2.4连接外连接 左外连接（LEFT OUTER JOIN） 右外连接（RIGHT OUTER JOIN） 完全外连接（FULL OUTER JOIN） 【例例4.34】查找所有学生情况及他们选修的课程号，若学生未选修任何课，也要包括其情况。SELECT XS.* , KCH FROM XS LE

19、FT 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; 334.2.4连接交叉连接【例例4.36】列出学生所有可能的选课情况。 SELECT XH, XM, KCH, KCM FROM XS CROSS JOIN KC; 344.2.5汇总 统计函数统计函数 SUM和AVG函数 语法格式为：SUM / AVG ( ALL | DISTINCT expression )【例

20、例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_KC WHERE KCH=101;354.2.5汇总COUNT函数格式为：COUNT ( ALL | DISTINCT expression | * ) 【例例4

21、.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 FROM KCWHERE KCM= 离散数学); 364.2.5汇总GROUP BY子句子句 格式为： GROUP BY ALL group_by_expre

22、ssion ,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 平均成绩,COUNT(XH) AS 选修人数 FROM XS_KC GROUP BY KCH; 374.2.5汇总HAVING子句子句 格式为： HAVING 【例例4.

23、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; 384.2.5汇总【例例4.45】查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。SELECT XH AS学号,AVG(CJ) AS 平均成绩 FROM XS_

24、KC WHERE XH IN ( SELECT XH FROM XS WHERE ZYM= 通信工程) GROUP BY XH HAVING AVG(CJ) =85 394.2.6排序 ORDER BY子句的格式为： ORDER BY order_by_expression ASC | DESC ,n 【例例4.46】将通信工程专业的学生按出生时间先后排序。SELECT * FROM XS WHERE ZYM= 通信工程ORDER BY CSSJ; 【例例4.47】将计算机专业学生的“计算机基础”课程成绩按降序排列。SELECT XM AS 姓名, KCM AS 课程名, CJ AS 成绩 F

25、ROM XS,KC,XS_KC WHERE XS.XH=XS_KC.XH AND XS_KC.KCH= KC.KCHAND KCM= 计算机基础 AND ZYM= 计算机 ORDER BY CJ DESC; 404.2.7 SELECT语句的UNION子句 格式为： | ( ) UNION A LL | ( ) UNION A LL | ( ) n 【例例4.48】设在XSCJ数据库中建两个表：数学系学生、外语系学生，表结构与XS表相同，两个表分别存储数学系和外语系的学生情况，下列语句将这两个表的数据合并到XS表中。SELECT * FROM XS UNION ALL SELECT * FRO

26、M 数学系学生 UNION ALL SELECT * FROM 外语系学生; 414.3数据库的视图 4.3.1视图的概念 视图可以由以下任意一项组成： 一个基表的任意子集 两个或者两个以上基表的合集 两个或者两个以上基表的交集 对一个或者多个基表运算的结果集合 另一个视图的子集 视图有下列优点： 为用户集中数据，简化用户的数据查询和处理。 屏蔽数据库的复杂性。 简化用户权限的管理。 便于数据共享。 可以重新组织数据以便输出到其它应用程序中。 424.3.2创建视图 在在OEM中创建视图中创建视图 使用向导创建视图使用向导创建视图 图形工具创建视图图形工具创建视图 434.3.2创建视图 使用

27、使用CREATE VIEW语句创建视图语句创建视图 语法格式：语法格式：CREATE OR REPLACE FORCE | NOFORCE VIEW schema.view_name ( column_name ,n ) AS select_statementWITH CHECK OPTIONCONSTRAINT constraint_nameWITH READ ONLY 444.3.2创建视图【例例4.49】创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。 CREATE OR REPLACE VIEW CS_KC AS

28、 SELECT XS.XH,KCH,CJ FROM XS,XS_KC WHERE XS.XH=XS_KC.XH AND ZYM= 计算机 WITH CHECK OPTION; 454.3.2创建视图【例例4.50】创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。CREATE OR REPLACE VIEW CS_KC_AVG(num,score_avg) AS SELECT XH,AVG(CJ) FROM CS_KC GROUP BY XH; 464.3.3查询视图 通过界面查看通过界面查看 474.3.3查询

29、视图SELECT语句查询视图语句查询视图 【例例4.51】查找计算机专业的学生学号和选修的课程号。SELECT XH,KCH FROM CS_KC;【例例4.52】查找平均成绩在80分以上的学生的学号和平均成绩。 CREATE OR REPLACE VIEW XS_KC_AVG ( num,score_avg ) AS SELECT XH,AVG(CJ) FROM XS_KCGROUP BY XH;再对XS_KC_AVG视图进行查询。SELECT * FROM XS_KC_AVG WHERE score_avg=80; 484.3.4 更新视图 可更新视图可更新视图 没有使用连接函数、集合运算

30、函数和组函数； 创建视图的SELECT语句中没有聚合函数且没有GROUP BY、ONNECT BY、START WITH子句及DISTINCT关键字； 创建视图的SELECT语句中不包含从基表列通过计算所得的列； 创建视图没有包含只读属性。 【例例4.53】在XSCJ数据库中使用以下语句创建可更新视图CS_XS1。CREATE OR REPLACE VIEW CS_XS1 AS SELECT * FROM XS WHERE ZYM= 计算机; 494.3.4 更新视图插入数据插入数据【例例4.54】向CS_XS1视图中插入一条记录：（ 001115 , 刘明仪, 计算机, 男,1978-3-2

31、, 50 , 三好学生 ）INSERT INTO CS_XS1VALUES(001115, 刘明仪,计算机,男,TO_DATE(19780302,YYYYMMDD,50, 三好学生); 修改数据修改数据【例4.55】将CS_XS视图中所有学生的总学分增加8。UPDATE CS_XS SET ZXF=ZXF+ 8;【例例4.56】将CS_KC视图中学号为001101的学生的101课程成绩改为90。UPDATE CS_KC SET 成绩=90 WHERE 学号=001101 AND 课程号=101; 504.3.4 更新视图删除数据删除数据 【例例4.57】 删除CS_XS中女同学的记录。DELE

32、TE FROM CS_XS WHERE XB=女; 514.3.5修改视图的定义 通过通过OEM修改视图修改视图 524.3.5修改视图的定义使用使用SQL命令修改视图命令修改视图 【例例4.58】修改视图CS_KC的定义，包括学号、姓名、选修的课程号、课程名和成绩。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 OPTIO

33、N; 534.3.6删除视图 通过通过OEM删除视图删除视图544.3.6删除视图删除视图的删除视图的PL/SQL语句语句 格式为：DROP VIEW schema.view_name例如：DROP VIEW CS_KC;554.4 格式化输出结果 4.4.1替换变量 &替换变量替换变量 【例例4.59】查询XSCJ数据库XS表计算机专业的同学情况。SELECT XH AS 学号, XM AS 姓名FROM XSWHERE ZYM=&specialty_name;【例例4.60】查找平均成绩在80分以上的学生的学号、姓名和平均成绩。SET VERIFY ONSELECT * F

34、ROM XS_KC_AVG WHERE score_avg=score_avg; 564.4.1替换变量【例【例4.61】查找选修了“离散数学”课程的学生学号、姓名、课程名及成绩。SELECT XS.XH, &name, KCM, &columnFROM XS, &kc, XS_KCWHERE XS.XH=XS_KC.XH AND &conditionAND KCM=&kcmORDER BY & column; 574.4.1替换变量&替换变量替换变量 【例例4.62】查找选修了“离散数学”课程的学生学号、姓名、课程名及成绩。执行过程和结

35、果为：输入 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,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 NAME584.4.1替换变量为了避免为同一个变量