oracle数据库sql编程及高级查询专业资料
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
select * from stuInfo t where t.age>20 and t.score>70
如何查询年龄>20或者成绩大于70分的学生？
select * from stuInfo t where t.age>20 or t.score>70
如何对条件取反查询？
select * from stuInfo t where not t.score>60
-- UPDATE多列 update stuInfo t set age=20, t.sdate=to_date('2021-10-21','yyyy-mm-dd')
讲解： DELETE 命令
delete : 删除表中的数据 语法： delete from 表名 where 条件表达式
解释：where ： WHERE 子句用于规定选择的标准〔即条件〕，后面跟表达式内容 注意与drop的区别
INSERT UPDATE DELETE SELECT
串讲：INSERT 命令
学生信息表stuInfo,如何向表中存入学生信息？
CREATE TABLE stuInfo ( stuNo CHAR(6) NOT NULL, --学号，非空 stuName VARCHAR2(20) NOT NULL , --学员姓名，非空 stuAge NUMBER(3,0) NOT NULL, --年龄，非空 score NUMBER(3,2) not null -- 成绩 非空 beginDate DATE --开场学习时间，日期类型 );
all，some，any ：数量判断，all 表示记录集合中的所有记录， some表示记录集合中的一些记录，any 表示记录集合中的任意记录 其中，all 判别式等价于逻辑运算符中 的 and 运算，some和any是等 价的，他们又等价于逻辑运算符中的or运算。这三个判别式主要用 于子查询中，后面的知识点会涉及。
第10页/共26页
讲解：SELECT 命令
根本查询: 无条件查询
如何查询所有学生记录？
SELECT * FROM stuInfo as t;
如何选择指定的学号、姓名列？
SELECT stuNo,stuName FROM stuInfo;
如何筛除重复的行？
select distinct sname,t.* from stuInfo t
插入数据
//oracle中插入日期时间有以下几种格式： insert into stuInfo values(1002,‘张三’,20,90,sysdate); // sysdate: oracle获取系统日期 时间的关键字 insert into stuInfo values(1003,‘张三’,20,90,to_date(‘2021-5-4’,‘yyyy-MM-dd’));日期 的格式化[字符串转换为日期]，通过to_date将字符串日期转换为oracle所能识别的 date类型日期 insert into stu values(1005,'张三',20,90,to_date('2021-5-4 11:20:30','yyyy-mm-dd
如何利用现有的表(stuInfo)创立新表
CREATE TABLE stuInfoBAK as SELECT * FROM stuInfo;
如何使用列别名？如何将多列合并？
其中两个字符串拼接用‘||’符号 select t.sno||t.sname||t.age as 学籍 from STU t order by age desc
数数数事据据据务定操控控义纵制制语语语语言言言言
INSECROGCTRMAEMANTITTE
SELECT SAAVLETPEORINT DELETE
REDVROOKLPELUBAPDCKATE
讲解：数据操纵语言DML
数据操纵语言用于检索、插入和修改、删除数据 数据操纵语言是最常见的SQL命令 数据操纵语言命令包括：
is null ：在逻辑判断中，对于列值为空的判断，不能使用=， oracle对与空值的判断提供了专门的判别式---is null 举例：查询信息资料不全的学生？
select * from stu t where t.sname is null or age is null or score is null
如何查询日期大于2021 -02-24的数据？
select * from stu t where t.sdate>to_date('2021 -02-24','yyyy-mm-dd')
串讲：SELECT 命令
如何对结果集排序?按照学号升序/降序排列?
SELECT * FROM stuInfo ORDER BY stuAge DESC,stuNo ASC;
表是主要的数据库对象，用于存储数据
发送 SQL 查询
用户
SELECT ename FROM Emp;
发送命令输出到用户端 ename BLAKE SMITH ALLEN DAVID MARTIN
Oracle 服务器
第3页/共26页
讲解：SQL 简介
SQL (构造化查询语言)操作
SQL包括了所有对数据库的操作，主要是由4个局部组成： 数据定义语言〔DDL〕： 全称是Data Definition Language，主要用 来操纵Oracle数据库的构造。可以使用DDL语句定义、修改和删 除在Oracle中存在的每种类型的对象 数据操纵语言〔DML〕： 全称是data manipulation language，其中 包括数据查询和数据更新两大类操作，其中数据更新又包括插入 、删除和更新三种操作 事务控制语言〔TCL〕：全称是transaction Control Language，事务 控制语句 数据控制语言〔DCL〕：全称是data Control Language，对用户访 问数据的控制有根本表和视图的授权
• 以下是oracle中内置函数的分类：
SQL 函数
单行函数
聚合函数
第19页/共26页
讲解：单行函数分类
单行函数对于从表中查询的每一行只操作并返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 单行函数可以大致划分为：
日期函数 数学函数 字符串函数 转换函数 其他函数
第20页/共26页
相关子查询实例三emp表中找出每个雇员超过他所在部门平均工资的雇员编号雇员名称薪水部门编号返回第一步selectempnoenamesaldeptnofromempe1wheresalselectavgsalfromempe2wheree1deptnoe2deptno父父查询取出传送查询取出传送列值给子查询列值给子查询子查询获取父查询传送的列值子查询获取父查询传送的列值子查询返回查询值给父查询子查询返回查询值给父查询父查询传送下一行的列值给子查询父查询传送下一行的列值给子查询父查询传送下一行的列值给子查询父查询传送下一行的列值给子查询重复重复1133步步实例第37页共47页讲解
讲解：条件表达式
带条件查询之： 特殊判别式
like：模糊查询，通常使用通配符%，表示为任何长度 ，任何字符组成的字符串
举例：查询所有名字以‘张’姓开头的学生？
select * from stu t where t.sname like '张%‘
说明： like ‘张%‘表示以张开头，其后为任何长度，任
in ：集合成员测试，用于判断某个值是否是一个集合的成员 举例：
查询 学号是否在指定集合中学生 select * from stu t where t.sno in (1001,1002,1003,1004) ; 说明： t.sno in (1001,1002,1003,1004) 是查询条件
第15页/共26页
讲解：日期函数
单行函数------日期函数：oracle提供常用的日期函数 ，可以灵活的对日期进展运算 1、to_date() :函数，将字符串转换为日期型，被转换 的字符串必须符合特定的日期格式 语法：
to_date(‘2021-02-12’,’yyyy-mm-dd’) 举例： insert into stuInfo values(1003,‘张三 ’,20,90,to_date(‘2021-5-4’,‘yyyy-MM-dd’)); 2、add_months() 函数，为日期加上特定月份 举例：其中sysdate：获取系统日期 insert into stu values(1008,'张三
distinct 可以过滤查询字段中所有记录一样的 元组
讲解：算术比较运算符与逻辑 运算符
带条件查询之：算术比较运算符与逻辑运算符
<：小于运算，可用于数值型，日期型 <=：小于等于运算，可用于数值型，日期型 >：大于运算，可用于数值型，日期型 >=：小于等于运算，可用于数值型，日期型 =：等于运算，可用于数值型，日期型 <>〔或!=〕：不等于运算，可用于数值型，日期型
讲解： UPDATE 命令
update : 对表中的数据做出修改 语法：
update 表名 set 列名1=‘新值’，列名2=‘新值’，…
where 条件表达式
解释：where ： WHERE 子句用于规定选择的标准〔即条件〕，后面跟表达式内容
如何修改年龄和开场学习时间？
修改数据
-- UPDATE单列 update stuInfo set age=20 where t.sno=1003 update stuInfo set sdate=to_date('2021-10-21','yyyy-mm-dd') where t.sno=1003
第17页/共26页
带条件查询之： 特殊判别式 exists：存在性判断，in 判断用于判断表的列值是否
存在于集合中，而 exists那么可用于判断查询结果集合是 否为空，常配合子查询去使用，后面的知识点会涉及。
第18页/共26页
讲解： SQL 函数
• Oracle 提供了大量的内置函数，以处理各种形式的 运算。
第9页/共26页
讲解：条件表达式
在WHERE子句中的条件表达式中可出现以下操作符，用来实现更 加灵活的查询
算术比较运算符：<，<=，>，>=，=，<>〔或!=〕。 逻辑运算符：
AND:两个或多个条件结合起来，且条件必须都成立 OR：两个或多个条件结合起来，有一个条件为真即可 NOT：对条件取反 特殊判别式： between：取值范围 in ：集合成员测试 like：模糊查询 is null ：空值判断 all，some，any ：数量判断 exists：存在性判断
• 语法：
• SELECT 字段1，字段2 FROM 表名或视图名〔WHERE 条件表达式〕〔GROUP BY列名集合〔HAVING组条件 表达式〕〔ORDER BY列名〔集合〕…〕
• 整个语句的语义如下：从FROM子句中列出的表中，选 择满足WHERE子句中给出的条件表达式的元组，然后 按GROUPBY子句(分组子句)中指定列的值分组，再提 取满足HAVING子句中组条件表达式的那些组，按
讲解：条件表达式
带条件查询之： 特殊判别式
between：取值范围，用于判断某个值是否在另外两个值之间， 两个值之间用and连接，查询结果包含两值 举例：
查询年龄介于18-20岁学生 select * from stu t where t.age between 18 and 20 说明： t.age between 18 and 20 是查询条件
Oracle数据库 SQL编程及高级查询
第1页/共26页
本章目标
重点 难点 重点 难点
难点
第2页/共26页
讲解：SQL 简介
通过 SQL可以实现与Oracle 效劳器的通信 SQL 是 Structured Query Language〔构造化 查询语言〕的首字母缩写词
SQL 是数据库语言，Oracle 使用该语言存储和 检索信息
如何删除学号为035的学生记录？
删除数据 --删除学号为035的学生记录 DELETE FROM s39;035';
讲解：SELECT命令
• 数据查询 ：select 命令
• SQL是一种查询功能很强的语言，只要是数据库存在的 数据，总能通过适当的方法将它从数据库中查找出来。 SQL中的查询语句只有一个：SELECT，它可与其它语 句配合完成所有的查询功能
逻辑运算符：
AND:两个或多个条件结合起来，且条件必须都成立
OR：两个或多个条件结合起来，有一个条件为真即
可
第12页/共26页
根本查询: 带条件查询
如何查询学号为035的记录？
SELECT * FROM stuInfo WHERE stuNo = '035';
如何查询年龄>20并且成绩大于70分的学生？
何字符组成的字符串
查询名字中间含有‘三’的学生？
select * from stu t where t.sname like '%三%'
说明： like ‘%三%‘表示前面为任何长度，任何字符组
成的字符串，中间内容含有’三’，其后为任何长度，任
何字符组成的字符串
第16页/共26页
讲解：条件表达式
带条件查询之： 特殊判别式
如何查询年龄>20或者成绩大于70分的学生？
select * from stuInfo t where t.age>20 or t.score>70
如何对条件取反查询？
select * from stuInfo t where not t.score>60
-- UPDATE多列 update stuInfo t set age=20, t.sdate=to_date('2021-10-21','yyyy-mm-dd')
讲解： DELETE 命令
delete : 删除表中的数据 语法： delete from 表名 where 条件表达式
解释：where ： WHERE 子句用于规定选择的标准〔即条件〕，后面跟表达式内容 注意与drop的区别
INSERT UPDATE DELETE SELECT
串讲：INSERT 命令
学生信息表stuInfo,如何向表中存入学生信息？
CREATE TABLE stuInfo ( stuNo CHAR(6) NOT NULL, --学号，非空 stuName VARCHAR2(20) NOT NULL , --学员姓名，非空 stuAge NUMBER(3,0) NOT NULL, --年龄，非空 score NUMBER(3,2) not null -- 成绩 非空 beginDate DATE --开场学习时间，日期类型 );
all，some，any ：数量判断，all 表示记录集合中的所有记录， some表示记录集合中的一些记录，any 表示记录集合中的任意记录 其中，all 判别式等价于逻辑运算符中 的 and 运算，some和any是等 价的，他们又等价于逻辑运算符中的or运算。这三个判别式主要用 于子查询中，后面的知识点会涉及。
第10页/共26页
讲解：SELECT 命令
根本查询: 无条件查询
如何查询所有学生记录？
SELECT * FROM stuInfo as t;
如何选择指定的学号、姓名列？
SELECT stuNo,stuName FROM stuInfo;
如何筛除重复的行？
select distinct sname,t.* from stuInfo t
插入数据
//oracle中插入日期时间有以下几种格式： insert into stuInfo values(1002,‘张三’,20,90,sysdate); // sysdate: oracle获取系统日期 时间的关键字 insert into stuInfo values(1003,‘张三’,20,90,to_date(‘2021-5-4’,‘yyyy-MM-dd’));日期 的格式化[字符串转换为日期]，通过to_date将字符串日期转换为oracle所能识别的 date类型日期 insert into stu values(1005,'张三',20,90,to_date('2021-5-4 11:20:30','yyyy-mm-dd
如何利用现有的表(stuInfo)创立新表
CREATE TABLE stuInfoBAK as SELECT * FROM stuInfo;
如何使用列别名？如何将多列合并？
其中两个字符串拼接用‘||’符号 select t.sno||t.sname||t.age as 学籍 from STU t order by age desc
数数数事据据据务定操控控义纵制制语语语语言言言言
INSECROGCTRMAEMANTITTE
SELECT SAAVLETPEORINT DELETE
REDVROOKLPELUBAPDCKATE
讲解：数据操纵语言DML
数据操纵语言用于检索、插入和修改、删除数据 数据操纵语言是最常见的SQL命令 数据操纵语言命令包括：
is null ：在逻辑判断中，对于列值为空的判断，不能使用=， oracle对与空值的判断提供了专门的判别式---is null 举例：查询信息资料不全的学生？
select * from stu t where t.sname is null or age is null or score is null
如何查询日期大于2021 -02-24的数据？
select * from stu t where t.sdate>to_date('2021 -02-24','yyyy-mm-dd')
串讲：SELECT 命令
如何对结果集排序?按照学号升序/降序排列?
SELECT * FROM stuInfo ORDER BY stuAge DESC,stuNo ASC;
表是主要的数据库对象，用于存储数据
发送 SQL 查询
用户
SELECT ename FROM Emp;
发送命令输出到用户端 ename BLAKE SMITH ALLEN DAVID MARTIN
Oracle 服务器
第3页/共26页
讲解：SQL 简介
SQL (构造化查询语言)操作
SQL包括了所有对数据库的操作，主要是由4个局部组成： 数据定义语言〔DDL〕： 全称是Data Definition Language，主要用 来操纵Oracle数据库的构造。可以使用DDL语句定义、修改和删 除在Oracle中存在的每种类型的对象 数据操纵语言〔DML〕： 全称是data manipulation language，其中 包括数据查询和数据更新两大类操作，其中数据更新又包括插入 、删除和更新三种操作 事务控制语言〔TCL〕：全称是transaction Control Language，事务 控制语句 数据控制语言〔DCL〕：全称是data Control Language，对用户访 问数据的控制有根本表和视图的授权
• 以下是oracle中内置函数的分类：
SQL 函数
单行函数
聚合函数
第19页/共26页
讲解：单行函数分类
单行函数对于从表中查询的每一行只操作并返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 单行函数可以大致划分为：
日期函数 数学函数 字符串函数 转换函数 其他函数
第20页/共26页
相关子查询实例三emp表中找出每个雇员超过他所在部门平均工资的雇员编号雇员名称薪水部门编号返回第一步selectempnoenamesaldeptnofromempe1wheresalselectavgsalfromempe2wheree1deptnoe2deptno父父查询取出传送查询取出传送列值给子查询列值给子查询子查询获取父查询传送的列值子查询获取父查询传送的列值子查询返回查询值给父查询子查询返回查询值给父查询父查询传送下一行的列值给子查询父查询传送下一行的列值给子查询父查询传送下一行的列值给子查询父查询传送下一行的列值给子查询重复重复1133步步实例第37页共47页讲解
讲解：条件表达式
带条件查询之： 特殊判别式
like：模糊查询，通常使用通配符%，表示为任何长度 ，任何字符组成的字符串
举例：查询所有名字以‘张’姓开头的学生？
select * from stu t where t.sname like '张%‘
说明： like ‘张%‘表示以张开头，其后为任何长度，任
in ：集合成员测试，用于判断某个值是否是一个集合的成员 举例：
查询 学号是否在指定集合中学生 select * from stu t where t.sno in (1001,1002,1003,1004) ; 说明： t.sno in (1001,1002,1003,1004) 是查询条件
第15页/共26页
讲解：日期函数
单行函数------日期函数：oracle提供常用的日期函数 ，可以灵活的对日期进展运算 1、to_date() :函数，将字符串转换为日期型，被转换 的字符串必须符合特定的日期格式 语法：
to_date(‘2021-02-12’,’yyyy-mm-dd’) 举例： insert into stuInfo values(1003,‘张三 ’,20,90,to_date(‘2021-5-4’,‘yyyy-MM-dd’)); 2、add_months() 函数，为日期加上特定月份 举例：其中sysdate：获取系统日期 insert into stu values(1008,'张三
distinct 可以过滤查询字段中所有记录一样的 元组
讲解：算术比较运算符与逻辑 运算符
带条件查询之：算术比较运算符与逻辑运算符
<：小于运算，可用于数值型，日期型 <=：小于等于运算，可用于数值型，日期型 >：大于运算，可用于数值型，日期型 >=：小于等于运算，可用于数值型，日期型 =：等于运算，可用于数值型，日期型 <>〔或!=〕：不等于运算，可用于数值型，日期型
讲解： UPDATE 命令
update : 对表中的数据做出修改 语法：
update 表名 set 列名1=‘新值’，列名2=‘新值’，…
where 条件表达式
解释：where ： WHERE 子句用于规定选择的标准〔即条件〕，后面跟表达式内容
如何修改年龄和开场学习时间？
修改数据
-- UPDATE单列 update stuInfo set age=20 where t.sno=1003 update stuInfo set sdate=to_date('2021-10-21','yyyy-mm-dd') where t.sno=1003
第17页/共26页
带条件查询之： 特殊判别式 exists：存在性判断，in 判断用于判断表的列值是否
存在于集合中，而 exists那么可用于判断查询结果集合是 否为空，常配合子查询去使用，后面的知识点会涉及。
第18页/共26页
讲解： SQL 函数
• Oracle 提供了大量的内置函数，以处理各种形式的 运算。
第9页/共26页
讲解：条件表达式
在WHERE子句中的条件表达式中可出现以下操作符，用来实现更 加灵活的查询
算术比较运算符：<，<=，>，>=，=，<>〔或!=〕。 逻辑运算符：
AND:两个或多个条件结合起来，且条件必须都成立 OR：两个或多个条件结合起来，有一个条件为真即可 NOT：对条件取反 特殊判别式： between：取值范围 in ：集合成员测试 like：模糊查询 is null ：空值判断 all，some，any ：数量判断 exists：存在性判断
• 语法：
• SELECT 字段1，字段2 FROM 表名或视图名〔WHERE 条件表达式〕〔GROUP BY列名集合〔HAVING组条件 表达式〕〔ORDER BY列名〔集合〕…〕
• 整个语句的语义如下：从FROM子句中列出的表中，选 择满足WHERE子句中给出的条件表达式的元组，然后 按GROUPBY子句(分组子句)中指定列的值分组，再提 取满足HAVING子句中组条件表达式的那些组，按
讲解：条件表达式
带条件查询之： 特殊判别式
between：取值范围，用于判断某个值是否在另外两个值之间， 两个值之间用and连接，查询结果包含两值 举例：
查询年龄介于18-20岁学生 select * from stu t where t.age between 18 and 20 说明： t.age between 18 and 20 是查询条件
Oracle数据库 SQL编程及高级查询
第1页/共26页
本章目标
重点 难点 重点 难点
难点
第2页/共26页
讲解：SQL 简介
通过 SQL可以实现与Oracle 效劳器的通信 SQL 是 Structured Query Language〔构造化 查询语言〕的首字母缩写词
SQL 是数据库语言，Oracle 使用该语言存储和 检索信息
如何删除学号为035的学生记录？
删除数据 --删除学号为035的学生记录 DELETE FROM s39;035';
讲解：SELECT命令
• 数据查询 ：select 命令
• SQL是一种查询功能很强的语言，只要是数据库存在的 数据，总能通过适当的方法将它从数据库中查找出来。 SQL中的查询语句只有一个：SELECT，它可与其它语 句配合完成所有的查询功能
逻辑运算符：
AND:两个或多个条件结合起来，且条件必须都成立
OR：两个或多个条件结合起来，有一个条件为真即
可
第12页/共26页
根本查询: 带条件查询
如何查询学号为035的记录？
SELECT * FROM stuInfo WHERE stuNo = '035';
如何查询年龄>20并且成绩大于70分的学生？
何字符组成的字符串
查询名字中间含有‘三’的学生？
select * from stu t where t.sname like '%三%'
说明： like ‘%三%‘表示前面为任何长度，任何字符组
成的字符串，中间内容含有’三’，其后为任何长度，任
何字符组成的字符串
第16页/共26页
讲解：条件表达式
带条件查询之： 特殊判别式