JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询-阿里云开发者社区

day02_Oracle

一、课程目标

常用函数：了解会使用
高级查询：掌握（mysql,复习）
分页查询：伪列
关联查询：掌握（mysql,复习）

二、常用函数

2.1 Oracle字符函数

函数	说明
ASCII	返回对应字符的十进制值
CHR	给出十进制返回字符
`CONCAT`	拼接两个字符串，与`
INITCAP	将字符串的第一个字母变为大写
INSTR	找出某个字符串的位置
INSTRB	找出某个字符串的位置和字节数
`LENGTH`	以字符给出字符串的长度
LENGTHB	以字节给出字符串的长度
`LOWER`	将字符串转换成小写
LPAD	使用指定的字符在字符的左边填充
LTRIM	在左边裁剪掉指定的字符
RPAD	使用指定的字符在字符的右边填充
RTRIM	在右边裁剪掉指定的字符
`REPLACE`	执行字符串搜索和替换
`SUBSTR`	取字符串的子串
SUBSTRB	取字符串的子串（以字节）
SOUNDEX	返回一个同音字符串
TRANSLATE	执行字符串搜索和替换
TRIM	裁剪掉前面或后面的字符串
`UPPER`	将字符串变为大写

-- 字符函数
-- 求字符的长度
-- dual 是一个伪表，没有实际应用，就是配合着sql语法而来的
select length('ALDKF') from dual;
-- 截取字符
-- 参数解释:HelloWord: 原字符，6：从第几位开始 , 5: 截取多少位
-- 注意：索引是从1开始，不是0
select substr('HelloWorld',6,5) from dual;
-- 字符拼接
-- concat('字符1','字符2')
select concat('Hello','World') from dual;
-- 注意：concat一次只能拼接两个字符，如果有多个字符，需嵌套使用concat
select concat(concat('Hello','World'),'MM') from dual;
-- concat嵌套方式比较麻烦，可以使用|| 进行嵌套
select 'Hello' || 'World' || 'haha' from dual;
-- 将字符转换为小写
select lower('ALDKF') from dual;
-- 字符串替换
select replace('冬天就是好','冬天','切糕') from dual;

2.2 Oracle数学函数

函数	说明
ROUND	四舍五入
TRUNC	截取数值
MOD(n1,n2)	返回一个n1除以n2的余数
CEIL	向上取整
FLOOR	向下取整
ABS	指定值的绝对值
POWER(n1,n2)	返回n1的n2次方

-- 数学函数
-- round四舍五入，默认小数点最近的一位，是否四佶五入
select round(123.456) from dual;
-- 保留两位小数，进行四舍五入
select round(123.456,2) from dual;
-- trunc截取数值，默认将小数点后全部舍弃掉
select trunc(123.456) from dual;
-- 保留两位小数，进行截取数值
select trunc(123.456,2) from dual;
-- mod 取模
select mod(10,3) from dual;
-- ceil 向上取整
select ceil(123.456) from dual;
-- floor 向下取整
select floor(123.456) from dual;
-- abs 取绝对值
select abs(-123.456) from dual;
-- power 求几次幂
select power(2,3) from dual;

2.3 Oracle日期函数

函数	说明
systimestamp	获取当前日期和时间、小数点后面精确6位、时区、上下午
sysdate	获取当前日期和时间
ADD_MONTHS	在当前日期基础上加指定的月
LAST_DAY	获取当前日期所在月的最后一天
TRUNC	日期截取

-- 日期函数
-- 获取当前系统时间
select sysdate from dual;
-- 加月份
select add_months(sysdate,4) from dual;
-- 减月份，我们只需要数值为负值
select add_months(sysdate,-4) from dual;
-- 获取当前日期的所在的月份的最后一天
select last_day(sysdate) from dual;
-- sysdate-33：到了上一个月，也就是获取的是上一个月的最后一天
select last_day(sysdate-33) from dual;
-- 截取日期
-- 默认按日截取，将时间截掉
select trunc(sysdate) from dual;
-- 按月截取(把日载掉)
select trunc(sysdate,'mm') from dual; 
-- 按年截取(把月载掉)
select trunc(sysdate,'yyyy') from dual; 
-- 按小时截取
select trunc(sysdate,'hh') from dual; 
-- 按分钟截取
select trunc(sysdate,'mi') from dual;

2.4 Oracle转换函数

函数	说明
CHARTOROWID	将字符转换到 rowid 类型
CONVERT	转换一个字符节到另外一个字符节
HEXTORAW	转换十六进制到 raw 类型
RAWTOHEX	转换 raw 到十六进制
ROWIDTOCHAR	转换 ROWID 到字符
`TO_CHAR`	转换日期格式到字符串
`TO_DATE`	按照指定的格式将字符串转换到日期型
`TO_MULTIBYTE`	把单字节字符转换到多字节
TO_NUMBER	将数字字串转换到数字
TO_SINGLE_BYTE	转换多字节到单字节

-- 转换函数
-- 数字转字符串
select to_char(100) from dual;
-- 还可以与其他字符串进行拼接
select to_char(100)||'分' from dual;
-- 日期转字符串
-- 将当前的系统时间，按照yyyy-MM-dd模式转换成字符串
select to_char(sysdate,'yyyy-MM-dd') from dual;
select to_char(sysdate,'yyyy-MM-dd HH:mi:ss') from dual;
-- 注意：年月日中间的连接符，不能是中文，比如:yyyy年MM月dd日
-- select to_char(sysdate,'yyyy年MM月dd日') from dual; -- 错误的写法
-- 如果要yyyy年MM月dd日这种模式的日期时间，我们可以单独把年月日截取出来进行拼接
select 
   to_char(sysdate,'yyyy')||'年' ||
   to_char(sysdate,'MM')||'月' ||
   to_char(sysdate,'dd')||'日'    
from dual;
-- 字符串转日期
-- 将2020-03-11日期字符串按照yyyy-mm-dd模式转换成Date对象
select to_date('2020-03-11','yyyy-mm-dd') from dual;
-- 将字符串转数值
select to_number('100') from dual;
-- 验证100是否是数值
select to_number('100')+100 from dual;
-- 可以直接相加，会自动转换为数值进行参与运算
select '100' + 100 from dual;

2.5 Oracle其他函数

2.5.1 nvl函数

语法

NVL(检测的值，需要是字符型，如果为 null 的值);

示例

select NVL(NULL,0) from dual;
select NVL('','哈哈') from dual;   -- 返回哈哈
select NVL(null,'哈哈') from dual;  -- 返回哈哈
select NVL(' ','哈哈') from dual;  -- 返回空格

2.5.2 nvl2函数

需求

使用nvl函数，判断值是否为空，如果为空，将值替换为’补考’

以学生表（student）英语成绩为例
sql演示

select NVL(english,'补考') from student

英语成绩是number类型的，我们替换的值是字符类型的，所以报错
nvl2语法

NVL2(检测的值，如果不为 null 的值，如果为 null 的值);

示例

select NVL2(english,to_char(english),'补考') from student;

2.6 Oracle聚合函数

函数	说明
count(*) \| count(主键)	计算表中的总记录数
max	计算最大值
min	计算最小值
sum	计算和
avg	计算平均值

注意：聚合函数的计算，排除null值。

解决方案：

选择不包含非空的列进行计算
nvl2或nvl函数

以student表为例，进行演示

查询学生总数（null值处理）

SELECT COUNT(id) FROM student;
select count(NVL(english,0)) from student;
SELECT COUNT(*) FROM student;

查询数学成绩总分

SELECT SUM(math) FROM student;

查询数学成绩平均分

SELECT AVG(math) FROM student;

查询数学成绩最高分

SELECT MAX(math) FROM student;

查询数学成绩最低分

SELECT MIN(math) FROM student;

三、DQL高级查询

3.1 数据准备

-- 创建表
CREATE TABLE person (
  id number,
  name varchar2(20),
  age number,
  sex varchar2(5),
  address varchar2(100),
  math number,
  english number
);
-- 插入记录
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(1,'马云',55,'男','杭州',66,78);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(2,'马化腾',45,'女','深圳',98,87);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(3,'马景涛',55,'男','香港',56,77);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(4,'柳岩',20,'女','湖南',76,65);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(5,'柳青',20,'男','湖南',86,NULL);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(6,'刘德华',57,'男','香港',99,99);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(7,'马德',22,'女','香港',99,99);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(8,'德玛西亚',18,'男','南京',56,65);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(9,'唐僧',25,'男','长安',87,78);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(10,'孙悟空',18,'男','花果山',100,66);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(11,'猪八戒',22,'男','高老庄',58,78);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(12,'沙僧',50,'男','流沙河',77,88);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(13,'白骨精',22,'女','白虎岭',66,66);
INSERT INTO person(id,NAME,age,sex,address,math,english) VALUES 
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
commit;

3.2 排序查询

语法

SELECT 字段名 FROM 表名 [WHERE条件] ORDER BY 字段名 [ASC|DESC];

示例

-- 按照年龄的降序排序
select * from person order by age desc;

3.3 分组查询

对一列数据进行分组，相同的内容分为一组，通常与聚合函数一起使用，完成统计工作

3.3.1 语法

SELECT 字段 1,字段 2... FROM 表名 [where条件] GROUP BY 分组字段 [HAVING 条件] [order by];

注意事项

分组之后查询的字段：分组字段、聚合函数
where和having 的区别？

where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来 where 对基本的条件筛选
where 后不可以跟聚合函数，having可以进行聚合函数的判断。

where: 操作的数据源: 原始表
having: 操作的数据源: 结果集

3.3.2 案例演示

查询男女各多少人

SELECT sex,COUNT(*) FROM person GROUP BY sex;

查询年龄大于25岁的人,按性别分组,统计每组的人数

SELECT sex,COUNT(*) FROM person WHERE age >25 GROUP BY sex;

查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据

SELECT sex,COUNT(*) FROM person WHERE age >25 GROUP BY sex HAVING COUNT(*)>2;

3.4 分页查询 *

伪列是Oracle提供的一个系统列值，在最左侧，可以直接使用。

我们在 ORACLE 进行分页查询，需要用到ROWNUM 和嵌套查询

3.4.1 需求

分页查询person表每页 3条记录

3.4.2 简单分页查询

首先显示第一页的3条数据

select rownum, p.* from person p where rownum <= 3

显示第二页的3条数据

select rownum,p.* from person p where rownum>3 and rownum<=6

发现查询出来的没有结果伪列的特性决定；

这是因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于” 符号，只能使用“小于”或“小于等于” ，只用“等于”也不行

那怎么办呢？我们可以使用子查询来实现

select * from (select rownum r,p.* from person p) where r > 3 and r<= 6

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PAKZljuY-1665832782011)(assets/image-

.png)]

3.4.3 排序分页查询

按照person表中的年龄降序进行查询第二页的数据

select * from (select rownum r,p.* from (select * from person order by age desc) p) where r > 3 and r<= 6 ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lPNVeCDx-1665832782012)(assets/

)]

四、多表查询

4.1 数据准备

-- 部门表
CREATE TABLE dept (
  id NUMBER PRIMARY KEY, -- 部门id
  dname VARCHAR2(50),    -- 部门名称
  loc VARCHAR2(50)       -- 部门位置
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京');
INSERT INTO dept(id,dname,loc) VALUES (20,'学工部','上海');
INSERT INTO dept(id,dname,loc) VALUES (30,'销售部','广州');
INSERT INTO dept(id,dname,loc) VALUES (40,'财务部','深圳');
commit;
-- 职务表
CREATE TABLE job (
  id NUMBER PRIMARY KEY,
  jname VARCHAR2(20), -- 职务名称
  description VARCHAR2(50) -- 职务描述
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES(1, '董事长', '管理整个公司，接单');
INSERT INTO job (id, jname, description) VALUES(2, '经理', '管理部门员工');
INSERT INTO job (id, jname, description) VALUES(3, '销售员', '向客人推销产品');
INSERT INTO job (id, jname, description) VALUES(4, '文员', '使用办公软件');
commit;
-- 员工表
CREATE TABLE emp (
  id NUMBER PRIMARY KEY, -- 员工id
  ename VARCHAR2(50), -- 员工姓名
  job_id NUMBER, -- 职务id  外键
  mgr NUMBER , -- 上级领导编号 *
  joindate DATE, -- 入职日期
  salary NUMBER(7,2), -- 工资 99999.99
  bonus NUMBER(7,2), -- 奖金 99999.99
  dept_id NUMBER, -- 所在部门编号  外键
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,to_date('2000-12-17','yyyy-MM-dd'),'8000.00',NULL,20);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1002,'卢俊义',3,1006,to_date('2001-02-20','yyyy-MM-dd'),'16000.00','3000.00',30);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1003,'林冲',3,1006,to_date('2001-02-22','yyyy-MM-dd'),'12500.00','5000.00',30);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1004,'唐僧',2,1009,to_date('2001-04-02','yyyy-MM-dd'),'29750.00',NULL,20);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1005,'李逵',4,1006,to_date('2001-09-28','yyyy-MM-dd'),'12500.00','14000.00',30);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1006,'宋江',2,1009,to_date('2001-05-01','yyyy-MM-dd'),'28500.00',NULL,30);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1007,'刘备',2,1009,to_date('2001-09-01','yyyy-MM-dd'),'24500.00',NULL,10);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1008,'猪八戒',4,1004,to_date('2007-04-19','yyyy-MM-dd'),'30000.00',NULL,20);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1009,'罗贯中',1,NULL,to_date('2001-11-17','yyyy-MM-dd'),'50000.00',NULL,10);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1010,'吴用',3,1006,to_date('2001-09-08','yyyy-MM-dd'),'15000.00','0.00',30);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1011,'沙僧',4,1004,to_date('2007-05-23','yyyy-MM-dd'),'11000.00',NULL,20);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1012,'李逵',4,1006,to_date('2001-12-03','yyyy-MM-dd'),'9500.00',NULL,30);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1013,'小白龙',4,1004,to_date('2001-12-03','yyyy-MM-dd'),'30000.00',NULL,20);
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1014,'关羽',4,1007,to_date('2002-01-23','yyyy-MM-dd'),'13000.00',NULL,NULL);
commit;
-- 工资等级表
CREATE TABLE salarygrade(
  grade NUMBER PRIMARY KEY,  -- 等级
  losalary NUMBER, -- 最低工资
  hisalary NUMBER -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000);
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(2,12010,14000);
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(3,14010,20000);
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(4,20010,30000);
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(5,30010,99990);
commit;

4.2 内连接

拿左表的记录去匹配右表的记录，若符合条件显示（二张表的交集）

需求

1.查询所有员工的姓名，工资,入职日期和所在部门名称

sql语句

select 
   e.ename,
   e.salary,
   e.joindate,
   d.dname
from emp e, dept d
where e.dept_id = d.id;

4.3 外连接

左外连接

展示左表全部，再去匹配右表记录，若条件符合显示，若条件不符合显示NULL

语法

select ... from 左表 left [outer] join 右表 on 连接条件;

右外连接

展示右表全部，再去匹配左表记录，若条件符合显示，若条件不符合显示NULL

语法

select ...from 左表 right [outer] join 右表 on 连接条件;

sql演示

# 左外连接（推荐）
-- 查询所有员工信息及对应的部门名称
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
-- 查询所有部门及对应的员工信息
SELECT * FROM dept d LEFT JOIN emp e ON e.dept_id = d.id;
# 右外连接（了解）
-- 查询所有部门及对应的员工信息
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

4.4 子查询（重点复习)

一条select语句执行结果，作为另一条select语法的一部分。其实就是select语句的嵌套！

语法

-- 【1】查询结果单值
SELECT MAX(salary) FROM emp;
-- 【2】查询结果单列多行
SELECT salary FROM emp;
-- 【3】查询结果多行多列
SELECT * FROM emp;

规律

-- 【1】子查询结果为单列，肯定作为条件在where后面使用
    select ... from 表名 where 字段 in (子查询);
-- 【2】子查询结果为多列，一般作为虚拟表在from后面使用
    select ... from (子查询) as 表别名;

sql演示

子查询结果为单行单列

-- 【1】查询工资最高的员工是谁？ 
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

-- 【2】查询工资小于平均工资的员工有哪些？
-- 2.1 先求出平均工资
SELECT AVG(salary) FROM emp;
-- 2.2 查询低于平均工资的员工
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

子查询结果为单列多行

-- 【1】查询工资大于5000的员工，来自于哪些部门的名字  
-- 1.1 查询工资大于5000的员工
SELECT dept_id FROM emp WHERE salary >5000;
-- 1.2 来自于哪些部门的名字  
SELECT * FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000);

-- 【2】查询开发部与财务部所有的员工信息
-- 2.1 根据部门名称，查询部门主键
SELECT id FROM dept WHERE `name` IN('开发部','财务部');
-- 2.2 根据部门id查询员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name` IN('开发部','财务部'));

子查询结果为多列多行

-- 【1】 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 1.1 根据 join_date 查询 2011-11-11 之后的员工信息
SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11';
-- 1.2 根据1步骤的结果，跟部门表关联
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

t WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000);

```

```sql
-- 【2】查询开发部与财务部所有的员工信息
-- 2.1 根据部门名称，查询部门主键
SELECT id FROM dept WHERE `name` IN('开发部','财务部');
-- 2.2 根据部门id查询员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name` IN('开发部','财务部'));
```

子查询结果为多列多行

-- 【1】 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 1.1 根据 join_date 查询 2011-11-11 之后的员工信息
SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11';
-- 1.2 根据1步骤的结果，跟部门表关联
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询

day02_Oracle

一、课程目标

二、常用函数

2.1 Oracle字符函数

2.2 Oracle数学函数

2.3 Oracle日期函数

2.4 Oracle转换函数

2.5 Oracle其他函数

2.5.1 nvl函数

2.5.2 nvl2函数

2.6 Oracle聚合函数

三、DQL高级查询

3.1 数据准备

3.2 排序查询

3.3 分组查询

3.3.1 语法

3.3.2 案例演示

3.4 分页查询 *

3.4.1 需求

3.4.2 简单分页查询

3.4.3 排序分页查询

四、多表查询

4.1 数据准备

4.2 内连接

4.3 外连接

4.4 子查询（重点复习)

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询

day02_Oracle

一、课程目标

二、常用函数

2.1 Oracle字符函数

2.2 Oracle数学函数

2.3 Oracle日期函数

2.4 Oracle转换函数

2.5 Oracle其他函数

2.5.1 nvl函数

2.5.2 nvl2函数

2.6 Oracle聚合函数

三、DQL高级查询

3.1 数据准备

3.2 排序查询

3.3 分组查询

3.3.1 语法

3.3.2 案例演示

3.4 分页查询 *

3.4.1 需求

3.4.2 简单分页查询

3.4.3 排序分页查询

四、多表查询

4.1 数据准备

4.2 内连接

4.3 外连接

4.4 子查询 （重点复习)

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

4.4 子查询（重点复习)