演示示例： 函数-分析函数
第22页/共47页
上机练习
练习
需求说明
针对供应情况表进行如下操作
查询供货商总人数 查询一次供应茶叶的最大斤数 查询各个供应商编号及供应茶叶的总斤数 查询提供了2种以上茶叶的供货商编号
完成时间：15分钟 共性问题集中讲解
第23页/共47页
小结
SQL函数分为（ 单行函数)、( 聚合函数)和
第12页/共47页
串讲：SELECT 命令
如何对结果集排序,先按照年龄降序，如果年 龄相同的按照学号升序排列?
SELECT * FROM stuInfo ORDER BY stuAge DESC,stuNo ASC;
如何利用现有的表(stuInfo)创建新表 (stuInfoBAK)？
CREATE TABLE stuInfoBAK as SELECT * FROM stuInfo;
18/45
串讲：聚合函数
聚合函数基于一组行来返回结果 为每一组行返回一个值
聚合函数
AVG
MIN
MAX
SUM
COUNT
SELECT COUNT(*) FROMemp emp; SUM(sal) FROM ; SELECT COUNT(comm) FROM emp; SELECT AVG(sal) FROM emp WHERE job='CLERK'; SELECT COUNT(DISTINCT job) FROM emp; SELECT MAX(sal) FROM emp;
如何查询学号为035的记录？
SELECT * FROM stuInfo WHERE stuNo = '035';
如何选择指定的学号、姓名列？
SELECT stuNo,stuName FROM stuInfo;
如何筛除重复的行？
SELECT DISTINCT stuNo,stuName FROM stuInfo;
子查询返回查询值给父查询
父查询传送下一行的列值给子查询 (重复1-3步)
返回第一步
第28页/共47页
讲解：子查询实例三
查询其他部门中比30号部门某一雇员薪水少的雇 员信息
SELECT empno,ename,sal,deptno FROM emp e1 WHERE sal<ANY (SELECT sal FROM emp WHERE deptno=30 ) AND deptno<>30;
插入数据 的记录,其中 stuInfoBAK为 stuInfo备份表
INSERT INTO stuInfo(stuNo,stuName,stuAge) VALUES('034','Tom',23); INSERT INTO stuInfo VALUES('032','Ford',22,SYSDATE); INSERT INTO stuInfo SELECT stuNo,stuName,stuAge,beginDate FROM stuInfoBAK;
第9页/共47页
串讲： UPDATE 命令
如何修改年龄和开始学习时间？
修改数据 -- UPDATE单列 UPDATE stuInfo SET stuAge = 26 WHERE stuNo = '035'; -- UPDATE多列 UPDATE stuInfo SET stuage = 26, beginDate = '2012-09-18' WHERE stuNo= '035';
SELECT ename FROM Emp;
发送命令输出到用户端 ename BLAKE SMITH ALLEN DAVID MARTIN
第6页/共47页
Oracle 服务器
用户
串讲：SQL 简介
SQL 支持下列类别的命令： 数据定义语言（DDL） 数据操纵语言（DML） 事务控制语言（TCL） 数据控制语言（DCL） 数据控制语言 事务控制语言 数据操纵语言 数据定义语言
年"MM"月"DD"日" HH24:MI:SS') SELECT TO_CHAR(sysdate,'YYYY" TO_DATE('2005-12-06' , 'yyyy-mm-dd') FROM dual; SELECT TO_NUMBER('100') FROM dual; TO_CHAR (123456.03,'099,999.99') FROM dual; 演示示例： 函数-转换函数
返回子查询值给父查询
如果将IN换成NOT IN又如何？
第27页/共47页
讲解：子查询实例二
EMP表中，找出每个雇员超过他所在部门平均工 资的雇员编号、雇员名称、薪水、部门编号
父查询传送列值给子查询 SELECT empno,ename,sal,deptno FROM emp e1 WHERE sal > (SELECT AVG(sal) FROM emp e2 WHERE e1.deptno=e2.deptno ); 子查询获取父查询传送的列值 实例
难点
第5页/共47页
串讲：SQL 简介
通过 可以实现与Oracle 服务器的通信 SQL SQL 是 Structured Query Language （结构化 查询语言）的首字母缩写词 SQL 是数据库语言，Oracle 使用该语言存储和检 索信息 发送 SQL 查询 表是主要的数据库对象，用于存储数据
第3页/共47页
本章任务
任务1：使用SQL操作学生表 任务2：使用SQL函数对表进行操作 任务3：查询雇员相关信息
第4页/共47页
本章目标
会使用DML语言对数据库进行操作 能够熟练运用Oracle常用函数 能够熟练运用子查询
重点 难点 重点 难点
能够熟练运用连接查询
能够熟练运用查询实现分页显示功能
求供应产品编号为P1的供应商编号 求供应绿茶等级为1的供应商编号 求没有使用北京公司供应商的茶叶，但产品级别是1级茶叶 的产品编号
完成时间：10分钟 共性问题集中讲解
第14页/共47页
串讲： SQL 函数
Oracle 提供一系列用于执行特定操作的函数 SQL 函数带有一个或多个参数并返回一个值 以下是SQL函数的分类：
第17页/共47页
其它函数
转换空值的函数
NVL NVL DECODE
示例
SELECT ename, sal+NVL(comm,0) sal1, NVL2(comm,sal+comm,sal) sal2, DECODE(to_char(hiredate,‘MM’),‘ 01’,‘一月’, ‘02’,‘二月’, ‘03’,‘三月‘,'04','四月', ‘05','五月','06','六月', '下半年') mon FROM employee; 演示示例8：其他函数
第19页/共47页
讲解： GROUP BY和HAVING子句
GROUP BY子句 用于将信息划分为更小的组 每一组行返回针对该组的单个结果 HAVING子句 用于指定 GROUP BY 子句检索行的条件
SELECT deptno,MAX(sal) maxSal,AVG(sal) avgSal,MIN(sal) minSal FROM emp GROUP BY deptno; SELECT deptno,MAX(sal) maxSal,AVG(sal) avgSal,MIN(sal) minSal FROM emp GROUP BY deptno HAVING AVG(sal)>2000; 演示示例： 函数-聚合函数
第20页/共47页
讲解：分析函数
分析函数根据一组行来计算聚合值 用于计算完成聚集的累计排名等 分析函数为每组记录返回多个行
分析函数
ROW_NUMBER
RANK
DENSE_RANK
第21页/共47页
讲解：分析函数
以下三个分析函数用于计算一个行在一组有序行 中的排位，序号从1开始
ROW_NUMBER 返回连续的排位，不论值是否相等 RANK 具有相等值的行排位相同，序数随后跳跃 DENSE_RANK 具有相等值的行排位相同，序号是连续的
第10页/共47页
串讲： DELETE 命令
如何删除学号为035的学生记录？
删除数据
--删除学号为035的学生记录 DELETE FROM stuInfo WHERE stuNo = '035';
第11页/共47页
串讲：SELECT 命令
如何查询所有学生记录？
SELECT * FROM stuInfo;
COMMIT CREATE INSERT GRANT
SELECT
ALTER SAVEPOINT DELETE
DROP REVOKE ROLLBACK UPDATE
第7页/共47页
串讲：数据操纵语言
数据操纵语言用于检索、插入和修改数据 数据操纵语言是最常见的SQL命令 数据操纵语言命令包括： INSERT UPDATE DELETE SELECT
( 分析函数 )。
请列举聚合函数，有（ SUM ）、（ COUNT ）、
（ AVG ）、（ MAX ）等。
第24页/共47页
讲解：查询
SELECT语句的作用是什么？ 多表查询的类型有哪些？
子查询 -Leabharlann 将一个查询包含到另一个查询中 连 接 - 合并多个数据表中的列 联 合 - 合并多个数据表中的行