数据库应用 第3章SQL语言基础3学习资料

第3章SQL语言基础（3）3.1SQL简介3.2SQL语言的基本元素3.3SQL语言中的运算符3.4常用的SQL*Plus命令3.5SQL语言中的函数3.6关系表的DDL语句3.7关系表的DML语句3.8SQL的数据查询3.9视图3.8SQL的数据查询SELECT语句是数据库应用中最常用的语句，也是SQL标准中最灵活和应用最广泛的语句之一。SELECT语句是一个查询表达式，它以关键字SELECT开头，并且包括大量构成该表达式的元素。SELECT语句可以从数据库中按用户的要求查询行，而且允许从一个表或多个表中选择满足给定条件的一个或多个行或列，并将数据以用户规定的格式进行整理后返回给客户端。SELECT语句的完整格式比较复杂，其主要子句格式如下： SELECT[ALL|DISTINCT]{*|目标列表达式列表} FROM<表名或视图名>[,<表名或视图名>…] [WHERE<条件表达式>] [GROUPBY<列名>] [HAVING<条件表达式>] [ORDERBY<列名>[ASC|DESC]]SELECT子句：用来指定由查询返回的列。FROM子句：用来指定从中查询行的源表。WHERE子句：用来指定限定返回的行的搜索条件。GROUP子句：用来指定查询结果的分组条件。HAVING子句：用来指定组成聚合的搜索条件。ORDER子句：用来指定结果集的排序方式。在上述SELECT语句中，FROM、WHERE、GROUPBY和HAVING子句被称为表表达式。当处理SELECT语句时，这一部分总是首先被评估。表表达式中的每个子句按照次序被依次评估，评估的结果是一个虚拟表，用于以后的评估。也就是说，前一个子句的评估结果将用于下一个子句，直到表表达式的每一个子句都被评估完为止。例如，在SELECT语句中首先要评估的子句是FROM子句，如果指定了WHERE子句，那么FROM子句的评估结果将用于WHERE子句。各子句的评估次序如下：①FROM②WHERE（可选）③GROUPBY（可选）④HAVING（可选）⑤SELECT⑥ORDERBY（可选）当创建比较复杂的SELECT语句时，尤其是在处理连接和子查询时，对评估的次序有一个基本的了解显得尤为重要。3.8.1基本查询最基本的查询就是从指定的表中找出符合条件的记录。

SELECT* FROMstudent WHEREsex='男'ANDdno='d01';3.8.2SELECT子句在SELECT子句中，主要是通过指定列名确定表的哪些列值出现在结果集中，这相当于关系代数的投影操作。 ①SELECT子句后是以逗号分隔的各查询输出项。

SELECTsno,sname,dno FROMstudent WHEREsex='男';②用户可以另外指定列标题（别名）。

SELECT输出项1[AS]列标题,… SELECTsno学号,snameAS"name" FROMstudent WHEREsex='男';当别名没有被双引号括起来时，别名的显示结果为大写。如果别名中包含了特殊字符，或想让别名以原样显示，就要使用双引号把别名括起来。③SELECT的输出项为表达式，可以是列名、常量、函数、运算符等。

SELECTename||'年薪为：'||sal*12员工的年薪,'元'单位

FROMemp;3.8.3WHERE子句WHERE后的条件表达式中参与运算的值可以是常量、函数及FROM子句所声明的表中的列名。字符串及日期用单引号括起来，字符串是区分大小写的。⑴日期型数据的比较，越早的日期越小。查1980年以前出生的学生。

SELECT* FROMstudent WHEREbirtday<'1-1月-1980';⑵集合运算符 SELECTdname,tel FROMdep WHEREdnameIN('计算机系','数学系');

或

WHEREdname=ANY('计算机系','数学系');⑶空值的处理 SELECTcno FROMsc WHEREsno=9801ANDgradeISNULL;(34)查询有选课记录,但没有考试成绩的学生的学号和课程号,正确的SQL语句是：

A）SELECT学号,课程号FROMscWHERE成绩="" B）SELECT学号,课程号FROMscWHERE成绩=NULL C）SELECT学号,课程号FROMscWHERE成绩ISNULL D）SELECT学号,课程号FROMscWHERE成绩例emp表 SELECTname姓名,s1+NVL(s2,0)收入

FROMemp;namesexs1s2a男500b女60015c女40015d男700⑷匹配符匹配符有两个：

"-"代表一个且只能是一个字符。

"%"代表零个或多个字符。①当字符串比较中使用匹配符时，必须使用LIKE运算符。 SELECT* FROMstudent WHEREsnameLIKE'王%';②在进行字符串比较时，要注意字符串格式的影响。 aCHAR(10) bVARCHAR2(10) a=b='sunday'

SELECT* FROMtemp WHEREaLIKE'%day';× WHEREbLIKE'%day';√ WHEREaLIKE'%day%';√③转义符 SELECT* FROM课程表

WHEREcnameLIKE'a\_%'escape'\';课程表cnocname1a_C++2b_C++3a_数据库4b_数据库5access3.8.4ORDERBY子句ORDERBY子句实现查询结果的排序输出。例：查询d01部门的职工姓名及年龄，并按年龄由小到大排序输出。

SELECTename姓名,age年龄

FROMemp WHEREdeptno='d01' ORDERBYage;排序项可以是出现在查询列表中的列名、表达式、列标题，也可以指定输出项的序号，或用别名，还可以指定排序是升序（ASC）还是降序（DESC）。在排序子句中可以指定多个排序项。

SELECTdeptno,ename,age,sal FROMemp ORDERBYdeptno,ageDESC,sal;注意：①排序子句必须作为最后一个子句出现。②不使用ORDERBY子句，查询结果的次序是不确定的。③排序子句不能出现在子查询中。④排序的列名可以不出现在输出项中，但是当查询子句包含有DISTINCT选项时，就不能指定没有出现在查询列表中的列作为排序列。3.8.5多表查询与连接操作为了从多个表中得到所需的数据，Oracle提供了连接来完成多表查询。Oracle提供了4种类型的连接：相等连接、自连接、不等连接和外连接。⑴相等连接SELECTsno,sname,sexFROMstudent,depWHEREdname='计算机系'ANDstudent.dno=dep.dno"连接"实际上将不同的表中的相关记录连在一起，形成一个跨表的大记录。相等连接也叫简单连接或内连接，这种连接查询通常涉及到主码和外码。连接指南：①要连接的表都要放在FROM子句中，表名用逗号分开。②连接的条件放在WHERE子句中。③要进行n个表的连接需要至少n-1个连接条件。④如果多个表中有相同的列名时，在这些列的前面要冠以表名来区别它们，表名和列名之间用圆点隔开。⑤在列前冠以表名可以改善系统的效率，因为表名告诉Oracle服务器到哪一个表中找哪些列。⑥如果表名很长，可以使用表的别名。SELECTs.sno,s.sname,s.sexFROMstudents,depdWHEREd.dname='计算机系'ANDs.dno=d.dno;在以下条件下，得到的是笛卡尔积：①查询语句中漏掉了连接条件。②查询语句中两个表中的所有行都满足连接条件。③查询语句中的连接条件无效。⑵自连接职员表

emp(empno,ename,job,mgr)CREATETABLEemp( empnoCHAR(3)PRIMARYKEY, enameVARCHAR2(10), jobVARCHAR2(10), mgrCHAR(3)REFERENCESemp(empno));职员的主管查分析员的经理SELECT

w.empno,w.ename,w.job,w.mgr,FROM

empw,empmWHERE

w.mgr=m.empnoANDw.job='analyst';⑶不等连接相等连接中连接运算符为等号，我们也可以使用其它运算符，其它运算符产生的连接叫不等连接。选课表

sc(sno,cno,grade)

成绩等级划分表ggradeLOHI1044245593607047185586100查成绩为３－５级之间的学生的成绩及等级。SELECTsc.sno,o,sc.grade,g.gradeFROMsc,gWHERE

(sc.gradeBETWEENg.LOANDg.HI)ANDg.grade>2;⑷外连接查询女生所在系的信息，同时也返回那些没有女生的系的信息。

SELECTsname女生,sex性别,dname系名

FROMstudents,depd WHEREs.sex='女'ANDs.dno(+)=d.dno;对于一般连接，那些没有女生的系不会输出。外连接返回两种记录： ①满足连接条件的那些记录。 ②来自其中一个表的记录，这些记录不满足连接条件而不能与另一个表的记录连接。外连接的连接运算符为(+)。该连接运算符既可以放在等号的左边也可以放在等号的右边，但一定要放在缺少相应信息的那一边。⑸SQL：1999语法的连接Oracle9i对多表连接的语法进行了扩充，它开始支持美国国家标准化组织（ANSI）的SQL标准"SQL：1999"的语法。

SELECTsno,sname,sex FROMstudents,depd WHEREd.dname='计算机系'ANDs.dno=d.dno; SELECTsno,sname,sex FROMstudents JOINdepd USING(dno)WHEREd.dname='计算机系';

SELECTsno,sname,sex FROM

students JOINdepd ONs.dno=d.dnoWHEREd.dname='计算机系';此处不能写成d.dno3.8.6查询语句中的集合操作

⑴UNION（并运算）将属于一个查询结果的行和属于另一个查询结果的行结合起来构成并运算的结果。查询选修了c01或c02课程的学生。

(SELECTsnoFROMscWHEREcno='c01') UNION (SELECTsnoFROMscWHEREcno='c02');⑵INTERSECT（交运算）将既属于一个查询结果又属于另一个查询结果的行组合起来构成交运算的结果。查询同时选修了c01和c02两门课程的学生。

(SELECTsnoFROMscWHEREcno='c01') INTERSECT (SELECTsnoFROMscWHEREcno='c02');⑶MINUS（差运算）查询选修了c01，但没有选修c02的学生。

(SELECTsnoFROMscWHEREcno='c01') MINUS (SELECTsnoFROMscWHEREcno='c02');3.8.7查询中的重复值处理①DISTINCT去掉重复值。②ALL包括重复值（默认选项）查询开设的课程。

SELECTDISTINCTcname FROMcourse;（主码是cno）(31)查询学生选修课程小于60分的学号，正确的SQL语句是：A）SELECTDISTINCT学号FROMSCWHERE"成绩"<60B）SELECTDISTINCT学号FROMSCWHERE成绩<"60"C）SELECTDISTINCT学号FROMSCWHERE成绩<60D）SELECTDISTINCT"学号"FROMSCWHERE"成绩"<603.8.8组函数（统计函数）与聚合操作常用的组函数有5个：⑴一般统计①查d01部门职工的工资总额、最高工资、最低工资和平均工资。

SELECTSUM(sal),MAX(sal),MIN(sal),AVG(sal) FROMemp WHEREdeptno='d01';②统计职工总数。

SELECTCOUNT(*) FROMemp;如统计职工总数，不含没有工作部门的职工。

SELECTCOUNT(deptno) FROMemp;③查询本学期被选修的课程数。（选课表中，成绩为空表示为本学期选的课）

SELECTCOUNT(DISTINCTcno) FROMsc WHEREgradeISNULL;(34)查询有选课记录,但没有考试成绩的学生的学号和课程号,正确的SQL语句是：A）SELECT学号,课程号FROMscWHERE成绩=""B）SELECT学号,课程号FROMscWHERE成绩=NULLC）SELECT学号,课程号FROMscWHERE成绩ISNULLD）SELECT学号,课程号FROMscWHERE成绩(20)以下关于空值(NULL值)叙述正确的是A）空值等于空字符串B）空值等同于数值0C）空值表示字段或变量还没有确定的值D）VisualFoxPro不支持空值(15)设有SC(学号，课程号，成绩)表,下面SQL的SELECT语句检索成绩高于或等于平均成绩的学生的学号。SELECT学号FROMsc WHERE成绩>=(SELECT____FROMsc)⑵分组统计职员表

emp(eno,ename,job,sal,deptno)统计各工种的平均工资。

SELECTjob,AVG(sal) FROMemp GROUPBYjob;注意：如果在一个查询中使用了分组函数，任何不在分组函数中的列或表达式必须出现在GROUPBY子句中。

SELECTjob,AVG(sal) FROMemp GROUPBYjob;

GROUPBYdeptno;×

⑶HAVING子句的使用HAVING子句用于筛选满足条件的组。查询平均工资高于1500的工种。

SELECTjob,AVG(sal) FROMemp WHEREAVG(sal)>1500 GROUPBYjob;×ORA-00934：此处不允许使用分组函数 SELECTjob,AVG(sal) FROMemp GROUPBYjob; HAVINGAVG(sal)>1500 ORDERBYAVG(sal);(24)SQL的SELECT语句中,"HAVING<条件表达式>"用来筛选满足条件的

A）列B）行C）关系D）分组(14)使用SQL语言的SELECT语句进行分组查询时,如果希望去掉不满足条件的分组,应当在GROUPBY中使用_______子句。3.8.9子查询在SQL中，一个SELECT－FROM－WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE或HAVING子句的条件中的查询称为嵌套查询。

SELECT* FROMstudent WHEREsex= (SELECTsex FROMstudent WHEREsname='刘勇');上层查询块称为外层查询或父查询，下层查询称为内层查询或子查询。系统从内层子查询做起。⑴单值子查询（返回单一的值）单值子查询的比较关系符包括：

>、>=、<、<=、=、!=查工资在平均线以下的员工。

SELECTempno,ename,sal FROMemp WHEREsal<(SELECTAVG(sal)FROMemp);

WHEREsal<AVG(sal);× ORA-00934:此处不允许使用分组函数 注：where条件中不能包含分组函数。 ①主查询和子查询的数据可以来自不同的表。 ②单行子查询除了可以放在WHERE子句中，还可以放在HAVING子句中。例：查平均工资高于最低平均工资（按工种分类）的所有工种。（即平均工资不是最低的工种）SELECTjobFROMempGROUPBYjobHAVINGAVG(sal)>(SELECTMIN(AVG(sal))FROMempGROUPBYjob);？SELECT*FROMstudentWHEREsex= (SELECTsex FROMstudent WHEREdno='d01');⑵多行子查询子查询返回多行。比较使用集合运算符。例：查工资比所有工种平均工资都低的职工。

SELECTempno,ename,sal,job FROMemp WHEREsal<ALL (SELECTAVG(sal) FROMemp GROUPBYjob);例：多列子查询。查工资为所任工种最高的职工。

SELECTempno,ename,sal,job FROMemp WHERE(sal,job)IN (SELECTMAX(sal),job FROMemp GROUPBYjob);⑶相关子查询SQL还提供了一个EXISTS运算符，EXISTS代表存在量词。带EXISTS的子查询不返回任何数据，只产生逻辑真值"true"或逻辑假值"false"，不空为真，空为假。例：查所有选修了01号课程的学生的姓名。 SELECTsname FROMstudent WHEREEXISTS (SELECT* FROMsc WHEREsno=student.snoANDcno='01');由EXISTS引出的子查询，其目标列表达式通常都用*。此类查询与前面讲的子查询有一个明显的区别，即子查询的查询条件依赖于外层父查询的某个属性值，这类查询称为相关子查询。从概念上讲，相关子查询的一般处理过程是：首先取外层查询中（student）表的第1个元组，根据它与内层查询相关的属性值（sno）处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取（student）表的下一个元组；重复这一过程，直到外层（student）表全部检查完为止。(35)查询选修C2课程号的学生姓名,下列SQL语句中错误的是A）SELECT姓名FROMSWHEREEXISTS (SELECT*FROMSCWHERE学号=S.学号AND课程号='C2')B）SELECT姓名FROMSWHERE学号IN (SELECT学号FROMSJOINSCONS.学号=SC.学号WHERE课程号='C2')C）SELECT姓名FROMSJOINSCONS.学号=SC.学号WHERE课程号='C2'D）SELECT姓名