Oracle开发实用教程.ppt

1、第3章 SQL语言与查询,学习要点 基本查询 多表联接 集合运算 聚合函数,本章主要内容 SQL 语言概述 SQL语言的功能种类 数据类型、运算符与表达式 基本查询 多表联接 子查询 集合运算 聚合函数,3.1 SQL语言概述 SQL（Structured Query Language）是一种结构化查询语言，简称为SQL。它是1972年在SQUARE（Specifying Queries As Relational Expression）语言的基础上由Boyce和Chamberlin提出的一个标准的、通用的结构化查询语言，后来将SEQUEL改为SQL。 1989年，美国ANSI采纳了ANSI

2、X3.1341989报告中定义的关系数据库管理系统的SQL标准语言，称为ANSI SQL 89。该标准被国际标准化组织（ISO）和美国联邦政府组织所采纳。,3.2 SQL语言的功能种类,SQL语言按照功能可以分为四大类： （1）数据查询语言，主要用于查询数据，以SELECT 语句为主要关键词。 （2）数据定义语言DDL，用于定义SQL模式、基本表、视图和索引。 （3）数据操纵语言DML，主要用于完成数据修改操作。 （4）数据控制语言DCL，用于控制对数据库的访问，服务器的关闭、启动等,3.3 数据类型、运算符与表达式 3.3.1 简单数据类型 常用的简单数据类型分为9个：Number(p,s)

3、、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(

4、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。 （

5、3）逻辑表达式。用逻辑运算符连接的表达式称为逻辑表达式。如：name =HERY AND SAL=2000。,3.4 基本查询 SELECT语句的功能是从指定的数据库的表中提取数据。其语法格式为： SELECT ALL|DISTINCT字段名列表 FROM 表名 WHERE 条件表达式 GROUP BY group_by_list ORDER BY order_by_list ASC/DESC; 说明： 这条语句分为三大部分： （1）SELECT：SELECT子句用于指定检索数据库中的哪些列。 （2）FROM：FROM子句用于指定从哪一个表或视图中检索数据。 （3）WHERE：WHERE字句用

6、于在查询过程中的限制条件。 关于DISTINCT子句、GROUP BY子句、ORDER BY子句的使用在本节后面介绍。,3.4.1 简单查询 简单查询格式如下： SELECT 字段名列表 FROM 表名;,【例】假设关于一个企业信息的数据库中有三个表： 职工表EMP、部门表DEPT、岗位（或工种）表JOBLEVEL。 其中： 表EMP有如下字段：,这个表主要描述企业的职工信息，数据如下,表DEPT有如下字段,表JOBLEVEL有如下字段：,3.4.2 带条件的简单查询 SELECT 字段名列表 FROM 表名 WHERE 条件表达式 这里讲的带条件的简单查询指单条件的查询和组合条件的查询。下面

7、分别介绍。 1单条件的查询 【例】查询企业中工资超过2100元（包括2100元）的所有员工的姓名及其岗位。 SQL 语句如下： SQLSELECT name,salary,job FROM EMP WHERE salary = 2100;,2组合条件的查询 组合条件指WHERE子句中的条件由多个条件组成，这些组合主要是由逻辑运算符（AND、OR、NOT）组成。看下面的例子。 【例】找出企业员工中做软件开发工作并且工资在4000元以上的员工姓名和所在部门。 SQL 语句如下： SQLSELECT name,deptname,salary,job FROM EMP WHERE job=软件开发 a

8、nd salary 4000;,3.4.3 单表查询应用 1查询某些字段不同的记录 在查询工程中，可能发现有某些相同的数据，为了只显示不同的数据，可以使用DISTINCT关键字。 【例】查询企业中有多少种不同的岗位。 SQL 语句如下： SQLSELECT DISTINCT job FROM EMP;,2BETWEENAND关键字的使用 BETWEENAND 关键字给出了查询范围内的字段，而NOT BETWEENAND则给出了查询选择范围之外的某些列。 【例】找出工资在2000元到3500元的所有员工。 SQL 语句如下： SQLSELECT name,salary FROM EMP WHER

9、E salary BETWEEN 2000 AND 3500;,3模糊查询 前面的查询都是精确查询，但是我们平时查询时经常会使用模糊查询，比如“找姓陈的先生”，“找员工名字中最后一个字是琳的小姐”等，这些只给出某个字段得到部分关键词而不是字段的整个数据时，就可以使用LIKE关键字了。而NOT LIKE的意思正好相反。 这里介绍几个通配符，如表3-3所示。,4NULL和 NOT NULL 关键字的使用,NULL和 NOT NULL主要用于找出下落不明值的记录。例如，在公司人员招聘过程中，查询出尚未安排面试的候选人名单。 【例】查询办公室中一般员工的基本信息（不带职务的员工）。 SQL 语句如下：

10、 SQLSELECT * FROM EMP WHERE deptname=办公室 AND title =null;,5查询结果排序,ORDER BY 子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值ASC 或DESC确定。 SELECT ALL|DISTINCT字段名列表 FROM 表名 WHERE 条件表达式 GROUP BY group_by_list ORDER BY order_by_list ASC/DESC; 其中： ASC：升序。表示按照递增顺序从低到高对指定的列值排序。 DESC：降序。表示按照递减顺序从高到低对指定的

11、列值排序。,6字段运算查询,在数据库查询时，我们希望把表中的数据进行基本的算术运算，然后显示出来。 常见的算术运算有：+（加）、-（减）、*（乘）、/（除）四种。 【例】如果将软件开发部的所有员工工资增加1000元，请显示出此部门的员工姓名和工资。 SQL 语句如下： SQLSELECT name,salary+1000 FROM EMP WHERE deptname=软件开发部;,7rowid和rownum,rowid和rownum是很有用的两个数据库表中隐含的字段。 rowid 是数据库内部的十六进制的数据地址，表明信息放在第几块第几行。 rownum是行号，并且顺序排列，根据你的sele

12、ct语句的执行动态生成。它表示是表的逻辑上的第几行 【例】区别rowid和rownum。 SQLSELECT rownum,rowid,ename FROM EMP; 执行结果： rownum rowid ename - 1 D9B3333C.0003.0002 白海燕 2 DAAAA66D.0002.0002 周楚远 3 BAA33331.0003.0002 底雪洋 4 D555DFD.0005.0002 欧阳军 5 DB133344.0006.0002 刘徐贝尔 .,8别名的使用,在前面的查询中，显示的结果都是用字段名来显示的，这样不是很直观，我们可以给显示的字段名起一个直观的名字（叫“别

13、名”），以这个别名来显示。格式是： 字段名 as 别名 在“字段名”和“别名”之间加空格或者“as”即可，可以将默认的字段名以设定的名称显示。 【例】显示出软件开发部的每个员工工资增加1000元后的信息。 SQLSELECT name 员工姓名,salary+1000 增加工资 FROM EMP WHERE deptname=软件开发部;,3.4.4 多表查询,多表查询是指查询结果要从两个以上的表中检索结果。 【例】查询员工“刘紫羽”所在的部门地址。 分析：首先确定要求中的关键字：刘紫羽，部门地址。然后确定查询的表有哪些。“部门名称”在部门表DEPT中，但是“刘紫羽”是员工名称，“员工名称”在

14、表EMP中。所以我们确定查询的表涉及两个表：EMP和DEPT。 SQL语句如下： SQLSELECT d.addr FROM EMP e,DEPT d WHERE =刘紫羽;,3.5 子查询,如果某一个SELECT命令（查询1）出现在另一个SQL命令（查询2）的一个子句中，则称查询1是查询2的子查询，又称为“嵌套查询”。在SELECT查询语句里可以嵌入SELECT查询语句，称为嵌套查询。有些书中将内嵌的SELECT语句称为“子查询”，子查询形成的结果又称为父查询的条件。 下面是嵌套查询的格式： SELECT 字段列表 FROM 表名 WHERE 操作符 (SELECT select

15、_list FROM table);,3.5.1 简单子查询,【例】查询员工中所有高于员工底雪洋薪水的员工信息。 SQL 语句如下： SQLSELECT name,sex,salary FROM EMP WHERE salary= (SELECT salary FROM EMP WHERE name=底雪洋);,3.5.2 单行子查询,单行子查询的结果仅返回一行记录。一般使用比较运算符（、=、）。 【例】阅读下面的SQL 语句，并解释其含义。 SQLSELECT name,sex,salary FROM EMP WHERE job = (SELECT job FROM EMP WHERE na

16、me =底雪洋) AND salary (SELECT salary FROM EMP WHERE name=李一岚);,3.6 集合运算,3.6.1 UNION UNION 是集合的并运算，并运算的结果是参加运算的集合元素的总和，相当于数学的加法。如图3-1所示。其中集合A和集合B的交叉部分为集合C。,集合A,集合B,3.6.2 INTERSECT,INTERSECT是集合的交运算。交运算的结果是参加运算的集合元素的公共元素。 假设：集合A=1,2,3，集合B=2,3,5，那么执行INTERSECT运算的结果是2,3。 数学符号记为：AB。 在SQL语言中它返回查询结果中的相同部分。 【例】

17、找出account、research、sales中都存在的工种。 SQLSELECT job FROM account INTERSECT SELECT job FROM research INTERSECT SELECT job FROM sales;,3.6.3 MINUS,MINUS是集合的差运算。差运算的结果是参加运算的集合元素中从第一个集合中去掉二者相同部分元素后剩余的元素，相当于数学的减法。 假设：集合A=1,2,3，集合B=2,3,5，那么执行MINUS运算的结果是1。 数学符号记为：A-B。 在SQL语言中它返回在第一次查询结果中而不在第二次查询结果中的记录。 【例】找出在ac

18、count 中存在而在research中不存在的工种。 SQLSELECT job FROM account MINUS SELECT job FROM research;,3.7 聚合函数,常用的聚合函数包括如下几种函数：AVG()、MIN()、MAX()、SUM()、COUNT()。,1MAX,格式：MAX(x) 返回一个字段的所有值中的最大值。如果x是VARCHAR2数据类型，返回一个字符串；如果x是一个DATE数据类型，返回一个日期；如果x是number数据类型，返回一个数字。 【例】查询EMP表中的最高工资。 SQLSELECT max(salary) AS 最高薪水 FROM EM

19、P;,2MIN,格式：MIN(x) 返回一个字段的所有值中的最小值。如果字段是空的，函数MIN()返回空值。 【例】查询EMP表中的最低工资。 SQLSELECT MIN(salary) AS 最少薪水 FROM EMP; 【例】查询公司中工资最高和工资最低的员工。 SQLSELECT ename,sal FROM EMP HAVING sal=(SELECT MAX(SAL) FROM EMP) AND sal=(SELECT MIN(SAL) FROM EMP);,3AVG,格式：AVG(x 返回指定字段中的平均值。注意，函数AVG()只能对数值型字段使用，即x的数据类型只能是数值型，如果

20、空值即AVG()，忽略不计。缺省设置为ALL。 【例】查询软件开发部人员的平均工资。 SQLSELECT avg(salary) AS 平均工资 FROM EMP WHERE deptname=软件开发部;,4COUNT,格式：COUNT(*) 返回查询中行的数目，缺省值是ALL，*表示返回所有的行。用来统计一个表中有多少条记录，包括有空值的记录。因此，不需要指定要被计算的特定字段。 【例】统计EMP表目前有多少条记录。 SQLSELECT count(*) AS 记录总数 FROM EMP;,5SUM,格式：SUM(x) 返回选择列表项目x的数值的总和。 【例】求所有部门的工资总和。 SQLSELECT SUM(salary) AS 薪水总和 FROM EMP;,3.7.2 GROUP BY的应