Oracle开发实用教程

第3章SQL语言与查询学习要点基本查询多表联接子查询集合运算聚合函数

本章主要内容SQL语言概述SQL语言的功能种类数据类型、运算符与表达式基本查询多表联接子查询集合运算聚合函数 3.1SQL语言概述SQL（StructuredQueryLanguage）是一种结构化查询语言，简称为SQL。它是1972年在SQUARE（SpecifyingQueriesAsRelationalExpression）语言的基础上由Boyce和Chamberlin提出的一个标准的、通用的结构化查询语言，后来将SEQUEL改为SQL。1989年，美国ANSI采纳了ANSIX3.134—1989报告中定义的关系数据库管理系统的SQL标准语言，称为ANSISQL89。该标准被国际标准化组织（ISO）和美国联邦政府组织所采纳。

3.2SQL语言的功能种类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）关系表达式。用比较运算符连接的表达式称为关系表达式。如：sal>2000。（3）逻辑表达式。用逻辑运算符连接的表达式称为逻辑表达式。如：name='HERY'ANDSAL=2000。3.4基本查询SELECT语句的功能是从指定的数据库的表中提取数据。其语法格式为：SELECT[ALL|DISTINCT]{字段名列表}FROM表名[WHERE{条件表达式}][GROUPBY{group_by_list}][ORDERBY{order_by_list}[ASC/DESC]];说明：这条语句分为三大部分：（1）SELECT：SELECT子句用于指定检索数据库中的哪些列。（2）FROM：FROM子句用于指定从哪一个表或视图中检索数据。（3）WHERE：WHERE字句用于在查询过程中的限制条件。关于DISTINCT子句、GROUPBY子句、ORDERBY子句的使用在本节后面介绍。3.4.1简单查询简单查询格式如下：SELECT{字段名列表}FROM表名;【例】假设关于一个企业信息的数据库中有三个表：职工表EMP、部门表DEPT、岗位（或工种）表JOBLEVEL。其中：表EMP有如下字段：序号字段名称字段类型字段说明1name字符型职工姓名2sex字符型性别3birth日期型出生日期4phone字符型员工个人联系方式5deptname字符型所在部门6salary实型数据工资7job字符型岗位（或工种）8title字符型职务（无职务，可以为空）这个表表主要要描述述企业业的职职工信信息，，数据据如下下namesexbirthdeptnametitlephonesalaryjob欧阳军女1986-09-01办公室867988212500文员底雪洋男1998-10-09软件开发部867988233800软件开发李一岚女1984-10-01软件开发部139123454500软件开发白海燕女1978-02-09市场部867988251700产品销售刘紫羽女1989-01-09办公室主任867988213000文员刘徐贝尔女1989-01-09软件开发部经理867988234500软件开发周楚远男1989-01-09市场部经理867988252000产品销售表DEPT有如下下字段段序号字段名称字段类型字段说明1deptname字符型部门名2manager字符型部门经理姓名3addr字符型部门地址4phone字符型电话号码表JOBLEVEL有如下下字段段：序号字段名称字段类型字段说明1job字符型岗位名字2level1数字型工资等级13level2数字型工资等级24level3数字型工资等级33.4.2带条件件的简简单查查询SELECT{字段名名列表表}FROM表名WHERE{条件表表达式式}]这里讲讲的带带条件件的简简单查查询指指单条条件的的查询询和组组合条条件的的查询询。下下面分分别介介绍。。1．单条条件的的查询询【例】查询企企业中中工资资超过过2100元（包包括2100元）的的所有有员工工的姓姓名及及其岗岗位。。SQL语句如如下：：SQL>SELECTname,salary,jobFROMEMPWHEREsalary>=2100;2．组合合条件件的查查询组合条条件指指WHERE子句中中的条条件由由多个个条件件组成成，这这些组组合主主要是是由逻逻辑运运算符符（AND、OR、NOT）组成成。看看下面面的例例子。。【例】找出企企业员员工中中做软软件开开发工工作并并且工工资在在4000元以上上的员员工姓姓名和和所在在部门门。SQL语句如如下：：SQL>SELECTname,deptname,salary,jobFROMEMPWHEREjob='软件开开发'andsalary>4000;3.4.3单表查查询应应用1．查询询某些些字段段不同同的记记录在查询询工程程中，，可能能发现现有某某些相相同的的数据据，为为了只只显示示不同同的数数据，，可以以使用用DISTINCT关键字字。【例】查询企企业中中有多多少种种不同同的岗岗位。。SQL语句如如下：：SQL>SELECTDISTINCTjobFROMEMP;2．BETWEEN…AND关键字字的使使用BETWEEN…AND关键字字给出出了查查询范范围内内的字字段，，而NOTBETWEEN…AND则给出出了查查询选选择范范围之之外的的某些些列。。【例】找出工工资在在2000元到3500元的所所有员员工。。SQL语句如如下：：SQL>SELECTname,salaryFROMEMPWHEREsalaryBETWEEN2000AND3500;3．模糊糊查询询前面的的查询询都是是精确确查询询，但但是我我们平平时查查询时时经常常会使使用模模糊查查询，，比如如“找找姓陈陈的先先生””，““找员员工名名字中中最后后一个个字是是琳的的小姐姐”等等，这这些只只给出出某个个字段段得到到部分分关键键词而而不是是字段段的整整个数数据时时，就就可以以使用用LIKE关键字字了。。而NOTLIKE的意思思正好好相反反。这里介介绍几几个通通配符符，如如表3-3所示。。序号通配符说明1%代表0到多个字符2_代表单个字符4．NULL和NOTNULL关键字字的使使用NULL和NOTNULL主要用用于找找出下下落不不明值值的记记录。。例如如，在在公司司人员员招聘聘过程程中，，查询询出尚尚未安安排面面试的的候选选人名名单。。【例】查询办办公室室中一一般员员工的的基本本信息息（不不带职职务的的员工工）。。SQL语句如如下：：SQL>SELECT*FROMEMPWHEREdeptname="办公室室"ANDtitle=null;5．查询询结果果排序序ORDERBY子句使使得SQL在显示示查询询结果果时将将各返返回行行按顺顺序排排列，，返回回行的的排列列顺序序由ORDERBY子句指指定的的表达达式的的值ASC或DESC确定。。SELECT[ALL|DISTINCT]{字段名名列表表}FROM表名[WHERE{条件表表达式式}][GROUPBY{group_by_list}][ORDERBY{order_by_list}[ASC/DESC]];其中：：ASC：升序序。表表示按按照递递增顺顺序从从低到到高对对指定定的列列值排排序。。DESC：降序序。表表示按按照递递减顺顺序从从高到到低对对指定定的列列值排排序。。6．字段段运算算查询询在数据据库查查询时时，我我们希希望把把表中中的数数据进进行基基本的的算术术运算算，然然后显显示出出来。。常见的的算术术运算算有：：+（加））、-（减））、*（乘乘）、、/（除））四种种。【例】如果将将软件件开发发部的的所有有员工工工资资增加加1000元，请请显示示出此此部门门的员员工姓姓名和和工资资。SQL语句如如下：：SQL>SELECTname,salary+1000FROMEMPWHEREdeptname='软件开开发部部';7．rowid和rownumrowid和rownum是很有有用的的两个个数据据库表表中隐隐含的的字段段。rowid是数据据库内内部的的十六六进制制的数数据地地址，，表明明信息息放在在第几几块第第几行行。rownum是行号号，并并且顺顺序排排列，，根据据你的的select语句的的执行行动态态生成成。它它表示示是表表的逻逻辑上上的第第几行行【例】区别rowid和rownum。SQL>SELECTrownum,rowid,enameFROMEMP;执行结结果：：rownumrowidename-----------------------------------------------------------------1D9B3333C.0003.0002白海燕燕2DAAAA66D.0002.0002周楚远远3BAA33331.0003.0002底雪洋洋4D555DFD.0005.0002欧阳军军5DB133344.0006.0002刘徐贝贝尔………………………………………….8．别名名的使使用在前面面的查查询中中，显显示的的结果果都是是用字字段名名来显显示的的，这这样不不是很很直观观，我我们可可以给给显示示的字字段名名起一一个直直观的的名字字（叫叫“别别名””），，以这这个别别名来来显示示。格格式是是：字段名名[as]别名在“字字段名名”和和“别别名””之间间加空空格或或者““as””即可，，可以以将默默认的的字段段名以以设定定的名名称显显示。。【例】显示出出软件件开发发部的的每个个员工工工资资增加加1000元后的的信息息。SQL>SELECTname员工姓姓名,salary+1000增加工工资FROMEMPWHEREdeptname='软件开开发部部';3.4.4多表查查询多表查查询是是指查查询结结果要要从两两个以以上的的表中中检索索结果果。【例】查询员员工““刘紫紫羽””所在在的部部门地地址。。分析：：首先先确定定要求求中的的关键键字：：刘紫紫羽，，部门门地址址。然然后确确定查查询的的表有有哪些些。““部门门名称称”在在部门门表DEPT中，但但是““刘紫紫羽””是员员工名名称，，“员员工名名称””在表表EMP中。所所以我我们确确定查查询的的表涉涉及两两个表表：EMP和DEPT。SQL语句如如下：：SQL>SELECTd.addrFROMEMPe,DEPTdWHERE='刘紫羽羽';3.5子查询询如果某某一个个SELECT命令（（查询询1）出现现在另另一个个SQL命令（（查询询2）的一一个子子句中中，则则称查查询1是查询询2的子查查询，，又称称为““嵌套套查询询”。。在SELECT查询语语句里里可以以嵌入入SELECT查询语语句，，称为为嵌套套查询询。有有些书书中将将内嵌嵌的SELECT语句称称为““子查查询””，子子查询询形成成的结结果又又称为为父查查询的的条件件。下面是是嵌套套查询询的格格式：：SELECT字段列列表FROM表名WHERE操作符符(SELECTselect_listFROMtable);3.5.1简单子子查询询【例】查询员员工中中所有有高于于员工工底雪雪洋薪薪水的的员工工信息息。SQL语句如如下：：SQL>SELECTname,sex,salaryFROMEMPWHEREsalary>=(SELECTsalaryFROMEMPWHEREname='底雪雪洋洋');3.5.2单行行子子查查询询单行行子子查查询询的的结结果果仅仅返返回回一一行行记记录录。。一一般般使使用用比比较较运运算算符符（（<、>、<=、>=、<>）。。【例】阅读读下下面面的的SQL语句句，，并并解解释释其其含含义义。。SQL>SELECTname,sex,salaryFROMEMPWHEREjob=(SELECTjobFROMEMPWHEREname='底雪雪洋洋')ANDsalary>(SELECTsalaryFROMEMPWHEREname='李一一岚岚');3.6集合合运运算算3.6.1UNIONUNION是集集合合的的并并运运算算，，并并运运算算的的结结果果是是参参加加运运算算的的集集合合元元素素的的总总和和，，相相当当于于数数学学的的加加法法。。如如图图3-1所示示。。其其中中集集合合A和集集合合B的交交叉叉部部分分为为集集合合C。集合合A集合合B3.6.2INTERSECTINTERSECT是集集合合的的交交运运算算。。交交运运算算的的结结果果是是参参加加运运算算的的集集合合元元素素的的公公共共元元素素。。假设设：：集集合合A={1,2,3}，集集合合B={2,3,5}，那那么么执执行行INTERSECT运算算的的结结果果是是{2,3}。数学学符符号号记记为为：：A∩∩B。在SQL语言言中中它它返返回回查查询询结结果果中中的的相相同同部部分分。。【例】找出出account、research、sales中都都存存在在的的工工种种。。SQL>SELECTjobFROMaccountINTERSECTSELECTjobFROMresearchINTERSECTSELECTjobFROMsales;3.6.3MINUSMINUS是集合的差运运算。差运算算的结果是参参加运算的集集合元素中从从第一个集合合中去掉二者者相同部分元元素后剩余的的元素，相当当于数学的减减法。假设：集合A={1,2,3}，集合B={2,3,5}，那么执行MINUS运算的结果是是{1}。数学符号记为为：A-B。在SQL语言中它返回回在第一次查查询结果中而而不在第二次次查询结果中中的记录。【例】找出在account中存在而在research中不存在的工工种。SQL>SELECTjobFROMaccountMINUSSELECTjobFROMresearch;3.7聚合函数常用的聚合函函数包括如下下几种函数：：AVG()、MIN()、MAX()、SUM()、COUNT()。1．MAX格式：MAX(x)返回一个字段段的所有值中中的最大值。。如果x是VARCHAR2数据类型，返返回一个字符符串；如果x是一个DATE数据类型，返返回一个日期期；如果x是number数据类型，返返回一个数字字。【例】查询EMP表中的最高工工资。SQL>SELECTmax(salary)AS最高薪水FROMEMP;2．MIN格式：MIN(x)返回一个字段段的所有值中中的最小值。。如果字段是是空的，函数数MIN()返回空值。【例】查询EMP表中的最低工工资。SQL>SELECTMIN(salary)AS最少薪水FROMEMP;【例】查询公司中工工资最高和工工资最低的员员工。SQL>SELECTename,salFROMEMPHAVINGsal=(SELECTMAX(SAL)FROMEMP)ANDsal=(SELECTMIN(SAL)FROMEMP);3．AVG格式：AVG({x}返回指定字段段中的平均值值。注意，函函数AVG()只能对数值型型字段使用，，即x的数据类型只只能是数值型型，如果空值值即AVG('')，忽略不计。。缺省设置为为ALL。【例】查询软件开发发部人员的平平均工资。SQL>SELECTavg(salary)AS平均工资FROMEMPWHEREdeptname='软件开发部';4．COUNT格式：COUNT(*)返回查询中行行的数目，缺缺省值是ALL，*表示返回回所有的行。。用来统计一一个表