SQL编程及高级查询
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第20页/共47页
讲解：分析函数
分析函数根据一组行来计算聚合值 用于计算完成聚集的累计排名等 分析函数为每组记录返回多个行
分析函数
ROW_NUMBER
RANK
DENSE_RANK
第21页/共47页
讲解：分析函数
以下三个分析函数用于计算一个行在一组有序行 中的排位，序号从1开始
ROW_NUMBER 返回连续的排位，不论值是否相等 RANK 具有相等值的行排位相同，序数随后跳跃 DENSE_RANK 具有相等值的行排位相同，序号是连续的
( 分析函数 )。
请列举聚合函数，有（ SUM ）、（ COUNT ）、
（ AVG ）、（ MAX ）等。
第24页/共47页
讲解：查询
SELECT语句的作用是什么？ 多表查询的类型有哪些？
子查询 - 将一个查询包含到另一个查询中 连 接 - 合并多个数据表中的列 联 合 - 合并多个数据表中的行
第25页/共47页
SELECT ename FROM Emp;
发送命令输出到用户端 ename BLAKE SMITH ALLEN DAVID MARTIN
第6页/共47页
Oracle 服务器
Βιβλιοθήκη Baidu用户
串讲：SQL 简介
SQL 支持下列类别的命令： 数据定义语言（DDL） 数据操纵语言（DML） 事务控制语言（TCL） 数据控制语言（DCL） 数据控制语言 事务控制语言 数据操纵语言 数据定义语言
插入数据 的记录,其中 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;
子查询返回查询值给父查询
父查询传送下一行的列值给子查询 (重复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;
第3页/共47页
本章任务
任务1：使用SQL操作学生表 任务2：使用SQL函数对表进行操作 任务3：查询雇员相关信息
第4页/共47页
本章目标
会使用DML语言对数据库进行操作 能够熟练运用Oracle常用函数 能够熟练运用子查询
重点 难点 重点 难点
能够熟练运用连接查询
能够熟练运用查询实现分页显示功能
第二单元
第七章
SQL编程及高级查询
本章相关学习资源
学习平台“Oracle数据库”课程
《SQL语言和常用函数》专题 《高级查询》专题
学生用书
《Web前端基础及数据库开发》
第7章 《SQL编程及高级查询》
第2页/共47页
预习检查
例举数据操纵语言中常用的SQL命令 例举Oracle中常用的函数 简述查询的分类
年"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; 演示示例： 函数-转换函数
如何使用列别名？别名中有空格如何解决？
其中两个字符串拼接用„||‟符号 SELECT „S‟||stuNo 学号,stuName “姓 名” FROM stuInfo; 演示示例： DML语句操作
第13页/共47页
上机练习
练习
需求说明
针对供应商表S，产品表P，供应情况表SP插入数据。 参照提供的素材《供应商练习相关表》。 用SQL语句完成以下查询
第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';
如何查询学号为035的记录？
SELECT * FROM stuInfo WHERE stuNo = '035';
如何选择指定的学号、姓名列？
SELECT stuNo,stuName FROM stuInfo;
如何筛除重复的行？
SELECT DISTINCT stuNo,stuName FROM stuInfo;
演示示例： 函数-分析函数
第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;
求供应产品编号为P1的供应商编号 求供应绿茶等级为1的供应商编号 求没有使用北京公司供应商的茶叶，但产品级别是1级茶叶 的产品编号
完成时间：10分钟 共性问题集中讲解
第14页/共47页
串讲： SQL 函数
Oracle 提供一系列用于执行特定操作的函数 SQL 函数带有一个或多个参数并返回一个值 以下是SQL函数的分类：
SQL 函数
单行函数
聚合函数
分析函数
第15页/共47页
串讲：单行函数分类
单行函数对于从表中查询的每一行只返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 单行函数可以大致划分为： 日期函数 数字函数 字符函数 转换函数 其他函数
第16页/共47页
串讲：转换函数
转换函数将值从一种数据类型转换为另一种数据 类型 常用的转换函数有： TO_CHAR TO_DATE TO_NUMBER
第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：其他函数
COMMIT CREATE INSERT GRANT
SELECT
ALTER SAVEPOINT DELETE
DROP REVOKE ROLLBACK UPDATE
第7页/共47页
串讲：数据操纵语言
数据操纵语言用于检索、插入和修改数据 数据操纵语言是最常见的SQL命令 数据操纵语言命令包括： INSERT UPDATE DELETE SELECT
第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; 演示示例： 函数-聚合函数
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;
讲解：子查询
为什么使用子查询？
是表达查询最自然的方式 使编写SELECT语句变得更加简单 有些查询如果不使用子查询就无法用SQL表达出来
第26页/共47页
讲解：子查询实例一
EMP表中，查询与“SCOTT”在同一个部门的雇员 信息
SELECT empno,ename,deptno
FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE ename='SCOTT'); 执行子查询获得结果值 实例 执行父查询
返回子查询值给父查询
如果将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 ); 子查询获取父查询传送的列值 实例
第8页/共47页
串讲：INSERT 命令
已知学生信息表stuInfo,如何向表中存入学生信息？
CREATE TABLE stuInfo ( stuNo CHAR(6) NOT NULL, --学号，非空 stuName VARCHAR2(20) NOT NULL , --学员姓名，非空 stuAge NUMBER(3,0) NOT NULL, --年龄，非空 beginDate DATE --开始学习时间，日期类型 ); 插入来自其他表
难点
第5页/共47页
串讲：SQL 简介
通过 可以实现与Oracle 服务器的通信 SQL SQL 是 Structured Query Language （结构化 查询语言）的首字母缩写词 SQL 是数据库语言，Oracle 使用该语言存储和检 索信息 发送 SQL 查询 表是主要的数据库对象，用于存储数据
SELECT d.dname, e.ename, DENSE_RANK() ename, deptno, sal, ROW_NUMBER() deptno, job, ename, sal,e.sal, comm, RANK() OVER OVER OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) (ORDER BY sal DESC) AS SAL_RANK (PARTITION BY deptno ORDER BY sal DESC, comm) RANK AS DENRANK FROM SCOTT.EMP; emp; FROM emp e, dept d WHERE e.deptno = d.deptno;
第10页/共47页
串讲： DELETE 命令
如何删除学号为035的学生记录？
删除数据
--删除学号为035的学生记录 DELETE FROM stuInfo WHERE stuNo = '035';
第11页/共47页
串讲：SELECT 命令
如何查询所有学生记录？
SELECT * FROM stuInfo;
讲解：分析函数
分析函数根据一组行来计算聚合值 用于计算完成聚集的累计排名等 分析函数为每组记录返回多个行
分析函数
ROW_NUMBER
RANK
DENSE_RANK
第21页/共47页
讲解：分析函数
以下三个分析函数用于计算一个行在一组有序行 中的排位，序号从1开始
ROW_NUMBER 返回连续的排位，不论值是否相等 RANK 具有相等值的行排位相同，序数随后跳跃 DENSE_RANK 具有相等值的行排位相同，序号是连续的
( 分析函数 )。
请列举聚合函数，有（ SUM ）、（ COUNT ）、
（ AVG ）、（ MAX ）等。
第24页/共47页
讲解：查询
SELECT语句的作用是什么？ 多表查询的类型有哪些？
子查询 - 将一个查询包含到另一个查询中 连 接 - 合并多个数据表中的列 联 合 - 合并多个数据表中的行
第25页/共47页
SELECT ename FROM Emp;
发送命令输出到用户端 ename BLAKE SMITH ALLEN DAVID MARTIN
第6页/共47页
Oracle 服务器
Βιβλιοθήκη Baidu用户
串讲：SQL 简介
SQL 支持下列类别的命令： 数据定义语言（DDL） 数据操纵语言（DML） 事务控制语言（TCL） 数据控制语言（DCL） 数据控制语言 事务控制语言 数据操纵语言 数据定义语言
插入数据 的记录,其中 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;
子查询返回查询值给父查询
父查询传送下一行的列值给子查询 (重复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;
第3页/共47页
本章任务
任务1：使用SQL操作学生表 任务2：使用SQL函数对表进行操作 任务3：查询雇员相关信息
第4页/共47页
本章目标
会使用DML语言对数据库进行操作 能够熟练运用Oracle常用函数 能够熟练运用子查询
重点 难点 重点 难点
能够熟练运用连接查询
能够熟练运用查询实现分页显示功能
第二单元
第七章
SQL编程及高级查询
本章相关学习资源
学习平台“Oracle数据库”课程
《SQL语言和常用函数》专题 《高级查询》专题
学生用书
《Web前端基础及数据库开发》
第7章 《SQL编程及高级查询》
第2页/共47页
预习检查
例举数据操纵语言中常用的SQL命令 例举Oracle中常用的函数 简述查询的分类
年"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; 演示示例： 函数-转换函数
如何使用列别名？别名中有空格如何解决？
其中两个字符串拼接用„||‟符号 SELECT „S‟||stuNo 学号,stuName “姓 名” FROM stuInfo; 演示示例： DML语句操作
第13页/共47页
上机练习
练习
需求说明
针对供应商表S，产品表P，供应情况表SP插入数据。 参照提供的素材《供应商练习相关表》。 用SQL语句完成以下查询
第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';
如何查询学号为035的记录？
SELECT * FROM stuInfo WHERE stuNo = '035';
如何选择指定的学号、姓名列？
SELECT stuNo,stuName FROM stuInfo;
如何筛除重复的行？
SELECT DISTINCT stuNo,stuName FROM stuInfo;
演示示例： 函数-分析函数
第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;
求供应产品编号为P1的供应商编号 求供应绿茶等级为1的供应商编号 求没有使用北京公司供应商的茶叶，但产品级别是1级茶叶 的产品编号
完成时间：10分钟 共性问题集中讲解
第14页/共47页
串讲： SQL 函数
Oracle 提供一系列用于执行特定操作的函数 SQL 函数带有一个或多个参数并返回一个值 以下是SQL函数的分类：
SQL 函数
单行函数
聚合函数
分析函数
第15页/共47页
串讲：单行函数分类
单行函数对于从表中查询的每一行只返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 单行函数可以大致划分为： 日期函数 数字函数 字符函数 转换函数 其他函数
第16页/共47页
串讲：转换函数
转换函数将值从一种数据类型转换为另一种数据 类型 常用的转换函数有： TO_CHAR TO_DATE TO_NUMBER
第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：其他函数
COMMIT CREATE INSERT GRANT
SELECT
ALTER SAVEPOINT DELETE
DROP REVOKE ROLLBACK UPDATE
第7页/共47页
串讲：数据操纵语言
数据操纵语言用于检索、插入和修改数据 数据操纵语言是最常见的SQL命令 数据操纵语言命令包括： INSERT UPDATE DELETE SELECT
第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; 演示示例： 函数-聚合函数
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;
讲解：子查询
为什么使用子查询？
是表达查询最自然的方式 使编写SELECT语句变得更加简单 有些查询如果不使用子查询就无法用SQL表达出来
第26页/共47页
讲解：子查询实例一
EMP表中，查询与“SCOTT”在同一个部门的雇员 信息
SELECT empno,ename,deptno
FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE ename='SCOTT'); 执行子查询获得结果值 实例 执行父查询
返回子查询值给父查询
如果将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 ); 子查询获取父查询传送的列值 实例
第8页/共47页
串讲：INSERT 命令
已知学生信息表stuInfo,如何向表中存入学生信息？
CREATE TABLE stuInfo ( stuNo CHAR(6) NOT NULL, --学号，非空 stuName VARCHAR2(20) NOT NULL , --学员姓名，非空 stuAge NUMBER(3,0) NOT NULL, --年龄，非空 beginDate DATE --开始学习时间，日期类型 ); 插入来自其他表
难点
第5页/共47页
串讲：SQL 简介
通过 可以实现与Oracle 服务器的通信 SQL SQL 是 Structured Query Language （结构化 查询语言）的首字母缩写词 SQL 是数据库语言，Oracle 使用该语言存储和检 索信息 发送 SQL 查询 表是主要的数据库对象，用于存储数据
SELECT d.dname, e.ename, DENSE_RANK() ename, deptno, sal, ROW_NUMBER() deptno, job, ename, sal,e.sal, comm, RANK() OVER OVER OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) (ORDER BY sal DESC) AS SAL_RANK (PARTITION BY deptno ORDER BY sal DESC, comm) RANK AS DENRANK FROM SCOTT.EMP; emp; FROM emp e, dept d WHERE e.deptno = d.deptno;
第10页/共47页
串讲： DELETE 命令
如何删除学号为035的学生记录？
删除数据
--删除学号为035的学生记录 DELETE FROM stuInfo WHERE stuNo = '035';
第11页/共47页
串讲：SELECT 命令
如何查询所有学生记录？
SELECT * FROM stuInfo;