Oracle数据库开发实用教程第3章

D,第3章SQL语言与查询,学习要点基本查询多表联接子查询集合运算聚合函数,本章主要内容SQL语言概述SQL语言的功能种类数据类型、运算符与表达式基本查询多表联接子查询集合运算聚合函数,B,3.1SQL语言概述SQL（StructuredQueryLanguage）是一种结构化查询语言，简称为SQL。它是1972年在SQUARE（SpecifyingQueriesAsRelationalExpression）语言的基础上由Boyce和Chamberlin提出的一个标准的、通用的结构化查询语言，后来将SEQUEL改为SQL。1989年，美国ANSI采纳了ANSIX3.1341989报告中定义的关系数据库管理系统的SQL标准语言，称为ANSISQL89。该标准被国际标准化组织（ISO）和美国联邦政府组织所采纳。,3.2SQL语言的功能种类,B,SQL语言按照功能可以分为四大类：（1）数据查询语言，主要用于查询数据，以SELECT语句为主要关键词。（2）数据定义语言DDL，用于定义SQL模式、基本表、视图和索引。（3）数据操纵语言DML，主要用于完成数据修改操作。（4）数据控制语言DCL，用于控制对数据库的访问，服务器的关闭、启动等,3.3数据类型、运算符与表达式3.3.1简单数据类型常用的简单数据类型分为9个：Number(p,s)、smallint、integer、float、double、char(n)、varchar(n)、date、time，下面一一进行介绍。（1）Number(p,s)。p是数字的整个位数，s是小数点后的位数。如：number(8,4)是一个8位数字，其中小数点后有4位。如果没有特别指定，例如：number，则系统会设为p=5；s=0。（2）smallint。表示16位的整数。（3）integer。表示32位的整数。（4）float。表示32位的实数。（5）double。表示64位的实数。（6）char(n)。表示固定长度为n的字符串，n不能超过254。（7）varchar(n)。表示可变长度的字符串且其最大长度为n，n不能超过4000。（8）date。表示日期，包含年份、月份。（9）time。表示时间，包含小时、分钟、秒。,3.3.2运算符SQL的运算符分为如下几种：（1）算术运算符。加（+），减（-），乘（*），除（/）。（2）比较运算符。（3）逻辑运算符（4）范围运算符（5）列表运算符。,3.3.3表达式SQL语言的表达式分为三类：算术表达式；关系表达式；逻辑表达式。（1）算术表达式。用算术运算符连接的表达式称为算术表达式。如：sal+2000。（2）关系表达式。用比较运算符连接的表达式称为关系表达式。如：sal2000。（3）逻辑表达式。用逻辑运算符连接的表达式称为逻辑表达式。如：name=HERYANDSAL=2000。,3.4基本查询SELECT语句的功能是从指定的数据库的表中提取数据。其语法格式为：SELECTALL|DISTINCT字段名列表FROM表名WHERE条件表达式GROUPBYgroup_by_listORDERBYorder_by_listASC/DESC;说明：这条语句分为三大部分：（1）SELECT：SELECT子句用于指定检索数据库中的哪些列。（2）FROM：FROM子句用于指定从哪一个表或视图中检索数据。（3）WHERE：WHERE字句用于在查询过程中的限制条件。关于DISTINCT子句、GROUPBY子句、ORDERBY子句的使用在本节后面介绍。,C,3.4.1简单查询简单查询格式如下：SELECT字段名列表FROM表名;,【例】假设关于一个企业信息的数据库中有三个表：职工表EMP、部门表DEPT、岗位（或工种）表JOBLEVEL。其中：表EMP有如下字段：,D,这个表主要描述企业的职工信息，数据如下,B,表DEPT有如下字段,D,表JOBLEVEL有如下字段：,D,3.4.2带条件的简单查询SELECT字段名列表FROM表名WHERE条件表达式这里讲的带条件的简单查询指单条件的查询和组合条件的查询。下面分别介绍。1单条件的查询【例】查询企业中工资超过2100元（包括2100元）的所有员工的姓名及其岗位。SQL语句如下：SQLSELECTname,salary,jobFROMEMPWHEREsalary=2100;,C,2组合条件的查询组合条件指WHERE子句中的条件由多个条件组成，这些组合主要是由逻辑运算符（AND、OR、NOT）组成。看下面的例子。【例】找出企业员工中做软件开发工作并且工资在4000元以上的员工姓名和所在部门。SQL语句如下：SQLSELECTname,deptname,salary,jobFROMEMPWHEREjob=软件开发andsalary4000;,A,3.4.3单表查询应用1查询某些字段不同的记录在查询工程中，可能发现有某些相同的数据，为了只显示不同的数据，可以使用DISTINCT关键字。【例】查询企业中有多少种不同的岗位。SQL语句如下：SQLSELECTDISTINCTjobFROMEMP;,D,2BETWEENAND关键字的使用BETWEENAND关键字给出了查询范围内的字段，而NOTBETWEENAND则给出了查询选择范围之外的某些列。【例】找出工资在2000元到3500元的所有员工。SQL语句如下：SQLSELECTname,salaryFROMEMPWHEREsalaryBETWEEN2000AND3500;,C,3模糊查询前面的查询都是精确查询，但是我们平时查询时经常会使用模糊查询，比如“找姓陈的先生”，“找员工名字中最后一个字是琳的小姐”等，这些只给出某个字段得到部分关键词而不是字段的整个数据时，就可以使用LIKE关键字了。而NOTLIKE的意思正好相反。这里介绍几个通配符，如表3-3所示。,D,4NULL和NOTNULL关键字的使用,NULL和NOTNULL主要用于找出下落不明值的记录。例如，在公司人员招聘过程中，查询出尚未安排面试的候选人名单。【例】查询办公室中一般员工的基本信息（不带职务的员工）。SQL语句如下：SQLSELECT*FROMEMPWHEREdeptname=办公室ANDtitle=null;,5查询结果排序,ORDERBY子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDERBY子句指定的表达式的值ASC或DESC确定。SELECTALL|DISTINCT字段名列表FROM表名WHERE条件表达式GROUPBYgroup_by_listORDERBYorder_by_listASC/DESC;其中：ASC：升序。表示按照递增顺序从低到高对指定的列值排序。DESC：降序。表示按照递减顺序从高到低对指定的列值排序。,6字段运算查询,在数据库查询时，我们希望把表中的数据进行基本的算术运算，然后显示出来。常见的算术运算有：+（加）、-（减）、*（乘）、/（除）四种。【例】如果将软件开发部的所有员工工资增加1000元，请显示出此部门的员工姓名和工资。SQL语句如下：SQLSELECTname,salary+1000FROMEMPWHEREdeptname=软件开发部;,7rowid和rownum,rowid和rownum是很有用的两个数据库表中隐含的字段。rowid是数据库内部的十六进制的数据地址，表明信息放在第几块第几行。rownum是行号，并且顺序排列，根据你的select语句的执行动态生成。它表示是表的逻辑上的第几行【例】区别rowid和rownum。SQLSELECTrownum,rowid,enameFROMEMP;执行结果：rownumrowidename-1D9B3333C.0003.0002白海燕2DAAAA66D.0002.0002周楚远3BAA33331.0003.0002底雪洋4D555DFD.0005.0002欧阳军5DB133344.0006.0002刘徐贝尔.,8别名的使用,在前面的查询中，显示的结果都是用字段名来显示的，这样不是很直观，我们可以给显示的字段名起一个直观的名字（叫“别名”），以这个别名来显示。格式是：字段名as别名在“字段名”和“别名”之间加空格或者“as”即可，可以将默认的字段名以设定的名称显示。【例】显示出软件开发部的每个员工工资增加1000元后的信息。SQLSELECTname员工姓名,salary+1000增加工资FROMEMPWHEREdeptname=软件开发部;,3.4.4多表查询,多表查询是指查询结果要从两个以上的表中检索结果。【例】查询员工“刘紫羽”所在的部门地址。分析：首先确定要求中的关键字：刘紫羽，部门地址。然后确定查询的表有哪些。“部门名称”在部门表DEPT中，但是“刘紫羽”是员工名称，“员工名称”在表EMP中。所以我们确定查询的表涉及两个表：EMP和DEPT。SQL语句如下：SQLSELECTd.addrFROMEMPe,DEPTdWHERE=刘紫羽;,3.5子查询,如果某一个SELECT命令（查询1）出现在另一个SQL命令（查询2）的一个子句中，则称查询1是查询2的子查询，又称为“嵌套查询”。在SELECT查询语句里可以嵌入SELECT查询语句，称为嵌套查询。有些书中将内嵌的SELECT语句称为“子查询”，子查询形成的结果又称为父查询的条件。下面是嵌套查询的格式：SELECT字段列表FROM表名WHERE操作符(SELECTselect_listFROMtable);,3.5.1简单子查询,【例】查询员工中所有高于员工底雪洋薪水的员工信息。SQL语句如下：SQLSELECTname,sex,salaryFROMEMPWHEREsalary=(SELECTsalaryFROMEMPWHEREname=底雪洋);,3.5.2单行子查询,单行子查询的结果仅返回一行记录。一般使用比较运算符（、=、）。【例】阅读下面的SQL语句，并解释其含义。SQLSELECTname,sex,salaryFROMEMPWHEREjob=(SELECTjobFROMEMPWHEREname=底雪洋)ANDsalary(SELECTsalaryFROMEMPWHEREname=李一岚);,3.6集合运算,3.6.1UNIONUNION是集合的并运算，并运算的结果是参加运算的集合元素的总和，相当于数学的加法。如图3-1所示。其中集合A和集合B的交叉部分为集合C。,集合A,集合B,3.6.2INTERSECT,INTERSECT是集合的交运算。交运算的结果是参加运算的集合元素的公共元素。假设：集合A=1,2,3，集合B=2,3,5，那么执行INTERSECT运算的结果是2,3。数学符号记为：AB。在SQL语言中它返回查询结果中的相同部分。【例】找出account、research、sales中都存在的工种。SQLSELECTjobFROMaccountINTERSECTSELECTjobFROMresearchINTERSECTSELECTjobFROMsales;,3.6.3MINUS,MINUS是集合的差运算。差运算的结果是参加运算的集合元素中从第一个集合中去掉二者相同部分元素后剩余的元素，相当于数学的减法。假设：集合A=1,2,3，集合B=2,3,5，那么执行MINUS运算的结果是1。数学符号记为：A-B。在SQL语言中它返回在第一次查询结果中而不在第二次查询结果中的记录。【例】找出在account中存在而在research中不存在的工种。SQLSELECTjobFROMaccountMINUSSELECTjobFROMresearch;,3.7聚合函数,常用的聚合函数包括如下几种函数：AVG()、MIN()、MAX()、SUM()、COUNT()。,1MAX,格式：MAX(x)返回一个字段的所有值中的最大值。如果x是VARCHAR2数据类型，返回一个字符串；如果x是一个DATE数据类型，返回一个日期；如果x是number数据类型，返回一个数字。【例】查询EMP表中的最高工资。SQLSELECTmax(salary)AS最高薪水FROMEMP;,2MIN,格式：MIN(x)返回一个字段的所有值中的最小值。如果字段是空的，函数MIN()返回空值。【例】查询EMP表中的最低工资。SQLSELECTMIN(salary)AS最少薪水FROMEMP;【例】查询公司中工资最高和工资最低的员工。SQLSELECTename,salFROMEMPHAVINGsal=(SELECTMAX(SAL)FROMEMP)ANDsal=(SELECTMIN(SAL)FROMEMP);,3AVG,格式：AVG(x返回指定字段中的平均值。注意，函数AVG()只能对数值型字段使用，即x的数据类型只能是数值型，如果空值即AVG()，忽略不计。缺省设置为ALL。【例】查询软件开发部人员的平均工资。SQLSELECTavg(salary)AS平均工资FROMEMPWHEREdeptname=软件开发部;,4COUNT,格式：COUNT(*)返回查询中行的数目，缺省值是ALL，*表示返回所有的行。用来统计一个表中有多少条记录，包括有空值的记录。因此，不需要指定要被计算的特定字段。【例】统计EMP表目前有多少条记录。SQLSELECTcount(*)AS记录总数FROMEMP;,5SUM,格式：SUM(x)返回选择列表项目x的数值的总和。【例】求所有部门的工资总和。SQLSELECTSUM(salary)AS薪水总和FROMEMP;,3.7.2GROUPB