搜索
第4章数据库的查询和视图4.1连接、选择和投影Oracle是一个关系数据库管理系统，关系数据库建立在关系模型基础之上，具有严格的数学理论基础。关系数据库对数据的操作除了包括集合代数的并、差等运算之外，还定义了一组专门的关系运算：连接、选择和投影，关系运算的特点是运算的对象和结果都是表。4.1.1选择选择（Selection），简单的说就是通过一定的条件把自己所需要的数据检索出来。选择是单目运算，其运算对象是一个表。该运算按给定的条件，从表中选出满足条件的行形成一个新表，作为运算结果。4.1.1选择【例4.1】学生情况表如表4.1所示。若要在学生情况表中找出学生表中性别为女且平均成绩在80分以上的行形成一个新表，该选择运算的结果如表4.2所示。表4.1学生表表4.2查询后的结果4.1.2投影投影（Projection）也是单目运算。投影就是选择表中指定的列，这样在查询结果中只显示指定数据列，减少了显示的数据量也提高查询的性能。【例4.2】若在表4.1中对“姓名”和“平均成绩”投影，该查询得到如表4.3所示的新表。表4.3投影后的新表4.1.3连接连接（JOIN）是把两个表中的行按照给定的条件进行拼接而形成新表。【例4.3】若表A和B分别如表4.4和表4.5所示，则连接后结果如表4.6所示。表4.4A表表4.5B表表4.6连接后的表4.1.3连接【例4.4】若表A和表B分别如表4.7和表4.8所示，自然连接后的新表C如表4.9所示。表4.7A表表4.8B表表4.9C表4.2数据库的查询使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过PL/SQL的查询可以从表或视图中迅速方便地检索数据。PL/SQL的SELECT语句可以实现对表的选择、投影及连接操作，其功能十分强大。下面介绍SELECT语句，它是PL/SQL的核心。SELECT语句很复杂，主要的子句如下：语法格式：SELECTselect_list/*指定要选择的列或行及其限定*/FROMtable_source/*FROM子句，指定表或视图*/[WHEREsearch_condition]/*WHERE子句，指定查询条件*/[GROUPBYgroup_by_expression]/*GROUPBY子句，指定分组表达式*/[HAVINGsearch_condition]/*HAVING子句，指定分组统计条件*/[ORDERBYorder_expression[ASC|DESC]]/*ORDER子句，指定排序表达式和顺序*/4.2.1选择列选择表中的列组成结果表，通过SELECT语句的SELECT子句来表示。语法格式：SELECT[ALL|DISTINCT]select_list其中select_list指出了结果的形式，select_list的主要格式为：{*/*选择当前表或视图的所有列*/|{table_name|view_name|table_alias}.*/*选择指定的表或视图的所有列*/|{colume_name|expression}[[AS]column_alias]/*选择指定的列*/|column_alias=expression/*选择指定列并更改列标题*/}[,…n]1.选择一个表中指定的列使用SELECT语句选择一个表中的某些列，各列名之间要以逗号分隔。语法格式：SELECTcolumn_name[,column_name…]FROMtable_nameWHEREsearch_condition其功能是在FROM子句指定的表中检索符合search_condition条件的列。4.2.1选择列【例4.5】查询XSCJ数据库的XS表中各个同学的XM、XH和ZXF。SELECTXH,XM,ZXFFROMXS;执行结果如图4.1所示。图4.1在XS表中选择列4.2.1选择列【例4.6】查询XS表中ZXF大于45同学的XH、XM和ZXF。SELECTXH,XM,ZXFFROMXSWHEREZXF45;当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列。【例4.7】查询XS表中的所有列。SELECT*FROMXS;该语句等价于语句：SELECTXH,XM,ZYM,XB,CSSJ,ZXF,BZFROMXS;其执行后将列出XS表中的所有数据。4.2.1选择列2.修改查询结果中的列标题当希望查询结果中的某些列或所有列显示时使用自己选择的列标题时，可以在列名之后使用AS子句来更改查询结果的列标题名，其中column_alias是指定的列标题。【例4.8】查询XS表中计算机同学的XH、XM和ZXF，结果中各列的标题分别指定为学号、姓名和总学分。SELECTXHAS学号,XMAS姓名,ZXFAS总学分FROMXSWHEREZYM=’计算机’;该语句的执行结果如图4.2所示。更改查询结果中的列标题可以省略AS关键字。例如：SELECTXH学号,XM姓名,ZXF总学分FROMXSWHEREZYM=’计算机’;4.2.1选择列图4.2更改查询结果中的列标题4.2.1选择列3.计算列值使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：SELECTexpression[,expression]【例4.9】创建产品销售数据库XSH，并在其中创建产品表CP，其表结构如表4.10所表4.10CP表结构4.2.1选择列设CP表中已有如表4.11所示的数据。表4.11CP表4.2.1选择列下列语句将列出产品名称和产品总值：SELECTCPMCAS产品名称,JG*KCLAS产品总值FROMCP;该语句的执行结果如图4.3所示。图4.3计算列值4.2.1选择列4.消除结果集中的重复行【例4.10】对XSCJ数据库的XS表只选择ZYM和ZXF，消除结果集中的重复行。SELECTDISTINCTZYMAS专业名,ZXFAS总学分FROMXS;该语句执行的结果为：专业名总学分计算机48计算机50计算机52计算机54通信工程40通信工程42通信工程44通信工程50与DISTINCT相反，当使用关键字ALL时，将保留结果集的所有行。4.2.1选择列【例4.11】以下的SELECT语句对XSCJ数据库的XS表选择ZYM和ZXF，不消除结果集中的重复行。SELECTALLZYMAS专业名,ZXFAS总学分FROMXS;该语句执行后结果为：专业名总学分计算机50计算机50计算机50计算机50计算机54计算机52计算机50计算机50计算机50计算机48计算机50通信工程42通信工程40通信工程42通信工程42通信工程44通信工程42通信工程42通信工程42通信工程42通信工程42通信工程504.2.2选择行1.表达式比较比较运算符用于比较两个表达式值，共有7个，分别是：=（等于）、（小于）、=（小于等于）、（大于）、=（大于等于）、（不等于）、!=（不等于）。比较运算的格式为：expression{=||=||=||!=}expression当两个表达式值均不为空值（NULL）时，比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，比较运算将返回UNKNOWN。【例4.12】(1)查询XSH数据库CP表中库存量在500以上的产品情况。SELECT*FROMCPWHEREKCL500;(2)查询XSCJ数据库XS表中通信工程专业总学分大于等于42的同学的情况。SELECT*FROMXSWHEREZYM=’通信工程’andZXF=42;4.2.2选择行2.模式匹配LIKE谓词用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar2和date类型的数据，返回逻辑值TRUE或FALSE。LIKE谓词表达式的格式为：string_expression[NOT]LIKEstring_expression【例4.13】查询XSH数据库CP表中产品名含有“冰箱”的产品情况。SELECT*FROMCPWHERECPMCLIKE‘%冰箱%’;执行结果为：CPBHCPMCJGKCL10001100冰箱A_1001500.050010002120冰箱A_2001850.020010001200冰箱B_2001600.0120010001102冰箱C_2101890.06004.2.2选择行【例4.14】查询XSCJ数据库XS表中姓“王”且单名的学生情况。SELECT*FROMXSWHEREXMLIKE‘王_’;执行结果为：XHXMZYMXBCSSJZXFBZ061101王林计算机男10-二月-8650061103王燕计算机女06-十月-8550061201王敏通信工程男10-六月-8442061202王林通信工程男29-一月-8540有一门课不及格，待补考3.范围比较用于范围比较的关键字有两个：BETWEEN和IN。当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，格式为：expression[NOT]BETWEENexpression1ANDexpression2当不使用NOT时，若表达式expression的值在表达式expression1与expression2之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。注意：expression1的值不能大于expression2的值。4.2.2选择行【例4.15】(1)查询XSH数据库CP表中价格在2000元与4000元之间的产品情况。SELECT*FROMCPWHEREJGBETWEEN2000AND4000;(2)查询XSCJ数据库XS表中不在1985年出生的学生情况。SELECT*FROMXSWHERECSSJNOTBETWEENTO_DATE(‘19850101’,’YYYYMMDD’)andTO_DATE(‘19851231’,’YYYYMMDD’);使用IN关键字可以指定一个值表，值表中列出所有可能的值，当表达式与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：expressionIN(expression[,…n])【例4.16】查询XSH数据库CP表中库存量为“200”、“300”和“500”的情况。SELECT*FROMCPWHEREKCLIN(200,300,500);该语句与下列语句等价：SELECT*FROMCPWHEREKCL=200ORKCL=300ORKCL=500;4.2.2选择行4.空值比较当需要判定一个表达式的值是否为空值时，使用ISNULL关键字，格式为：expressionIS[NOT]NULL当不使用NOT时，若表达式expression的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例4.17】查询XSCJ数据库中总学分尚不定的学生情况。SELECT*FROMXSWHEREZXFISNULL;4.2.2选择行5.子查询在查询条件中，可以使用另一个查询的结果作为条件的一部分，例如判定列值是否与某个查询的结果集中的值相等，作为查询条件一部分的查询称为子查询。PL/SQL允许SELECT多层嵌套使用，用来表示复杂的查询。子查询除了可以用在SELECT语句中，还可以用在INSERT、UPDATE及DELETE语句中。子查询通常与IN、EXIST谓词及比较运算符结合使用。(1)IN子查询IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：expression[NOT]IN(subquery)其中subquery是子查询。当表达式expression与子查询subquery的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。4.2.2选择行【例4.18】在XSCJ数据库中有描述课程情况的表KC和描述学生成绩表的表XS_KC