Oracle入门--day2--单行函数（下）、多表关联查询、分组计算函数

最新推荐文章于 2021-08-27 16:25:23 发布

Cheirmin

最新推荐文章于 2021-08-27 16:25:23 发布

阅读量374

点赞数

分类专栏： Oracle入门文章标签： oracle

本文链接：https://blog.csdn.net/Cheirmin/article/details/105656796

版权

Oracle入门专栏收录该内容

6 篇文章 0 订阅

订阅专栏

大纲：

单行函数（下）、多表关联查询、分组计算函数

知识点：

单行函数（下）

函数嵌套
单行函数可以被无限层的嵌套，计算时先计算里层，再计算外层

其他常用单行函数

函数	用途
NVL (expr1, expr2)	如果expr1为空，这返回expr2
NVL2 (expr1, expr2, expr3)	如果expr1为空，这返回expr3（第2个结果）否则返回expr2
NULLIF (expr1, expr2)	如果expr1和expr2相等，则返回空
COALESCE (expr1, expr2, ..., exprn)	如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到一个不为NULL的值或者如果全部为NULL，也只能返回NULL

条件表达式：
实现方法：CASE 语句者或者DECODE 函数，两者均可实现IF-THEN-ELSE 的逻辑，相比较，而言，DECODE 更加简洁。

CASE 语句：

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

DECODE函数：

DECODE(col|expression, search1, result1 [, search2, result2,...,]
[, default])

多表关联查询

为了避免冗余信息，表结构设计遵循第三范式，所以在大多数情况下，我们需要从多张表中获取数据。
下面的例子是从两张表中分别获取部分信息，形成一个查询结果呈现给用户的

在执行多表查询时，若未指定链接条件，则结果返回是个笛卡尔乘积：
比如：

select employee_id,department_id,location_id from employees,
departments

显然大多数情况下，笛卡尔乘积不是我们想要的结果，为了避免笛卡尔乘积，我们一般要在Where 子句中提供
链接条件，对于链接，通常又包括多种类型：
（不同的数据库厂商对链接类型有不同的定义，但国际上有个凌驾于各厂商的工业标准定义（SQL 1999), 我们先
看来看Oracle 定义的链接类型：
1 、等于链接
2 、不等链接
3 、外连接（可细分为左外连接、右外连接）
4、自链接

“ 等于链接” 语法：

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

“ 不等链接” 语法：括使用不等链接符，包括> , < , !=, between

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 > table2.column2;

“ 外链接” 语法：包括左外连接，右外连接

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column (+);

“ 自链接” ：：个其实是一种概念，某个table 和自己本身链接：，比如：table1 给另一个“自己”起别名为table2

SELECT table1.column, table2.column
FROM table1, table1 table2
WHERE table1.column1 = table2.column2;

工业标准定义（SQL 1999) 的链接类型，Oracle 从9i 版本开始提供对SQL 1999 的兼容支持：
1 、交叉连接
2 、自然链接
3 、 Using 子句
4 、内连接
5、外连接（全外连接、左外连接、右外连接）

SQL 1999：

SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];

交叉连接：相当于没有连接条件的多表关联查询，结果是个笛卡尔乘积，实际工作中很少应用到。

自然链接：相当于Oracle 的“等于连接”，只不过是让系统自己去找两张表中字段名相同的字段作为
“等于连接”条件；（注意如果两个表中有相同的列名，但字段类型不一样，这会引发一个错误）

例子：

SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;

相当于：

SELECT department_id, department_name,
location_id, city
FROM departments， ， locations
Where departments.location_id = locations.location_id;

Using 子句：Using 子句可开着是自然连接的一种补充功能，我们知道自然连接会让系统自动查找两张表中的
所有列名相同的字段，并试图建立“等于连接”；但有的时候我们不期望这么做，而只是期望某个特定的字段
作为“等于连接”的条件，这种情况下可以使用Using 子句来做限制。

内连接：相当于Oracle 的“等于链接”，，关键字：INNER JOIN。

外连接：可细分为左外连接、右外连接、全外连接。
举例：左外连接

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

分组计算函数

分组计算函数：相对于单行函数，也可称之为多行函数，它的输入是多个行构成得一个行集（这个行集可以是
一张表的所有行，也可以是按照某个维度进行分组后的某一组行），而输出都是一个值；

比如我们常见的一些分组计算需求：求某个部门的薪资总和，薪资平均值，薪资最大值等等。

分组计算函数( 常用) ：包括
1 、求和(SUM)
2 、求平均值(AVG)
3 、计数（COUNT））
4 、求标准差(STDDEV)
5 、求方差(VARIANCE)
6 、求最大值(MAX)
7 、求最小值(MIN)

SQL组中使用分组计算函数

SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];

COUNT 函数说明:
函数用法意义
COUNT(*) 返回满足选择条件的所有行的行数，包括值为空的行和重复的行
COUNT(expr) 返回满足选择条件的且表达式不为空行数。
COUNT(DISTINCT expr) 返回满足选择条件的且表达式不为空，且不重复的行数。

问题：
Select count(1) 和和 Select count(*), select count(column1) 返回的结果？一样吗？ (Y)
Select count(distinct Column1) from test1 如果不用distinct 关键字可以怎么改写？
有要使得结果与上一局相同，下面两句都可以吗？还是只有1 句可以？
select count(column1) from (select column1 from test1 group by column1)
select count(*) from (select column1 from test1 group by column1)

使用GROUP BY 子句进行分组:

1 、可以按照某一个字段分组，也可以按照多个字段的组合进行分组

SELECT AVG(salary) FROM employees
GROUP BY department_id ;

2 、SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时，其他字段必须出现在Group By子子
句中，否则不合法。

SELECT department_id, COUNT(last_name)
FROM employees;

错误信息：

SELECT department_id, COUNT(last_name)
*
ERROR at line 1:
ORA-00937: not a single-group group function

正确的写法应该是：

SELECT department_id, count(last_name) FROM employees
GROUP BY department_id;

3 、不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。

4 、分组计算函数也可嵌套使用。

练习题：

select * from t_employee;
select * from t_salary;

--修改表结构，昨天粗心没看到
--新增家庭地址字段
alter table t_employee add(home_address varchar2(100));

--修改地址字段为工作住址字段
alter table t_employee rename column address to work_address;

--update t_salary set deptname='交付二' where deptname='支付二';


/**            修改调整新资表       ***/
drop table t_salary;

create table t_salary
(empno varchar(10) NOT NULL,
workday number(2),
overtime_workday number(2),
lateday number(2),
salary number(10,2),
yyear varchar2(4) NOT NULL,
mmonth varchar2(2) NOT NULL
);

select * from t_salary;

insert into t_salary values('yc90059426',12,2,0,1200.00,'2020','5');

insert into t_salary values('yc90059426',20,4,2,1500.00,'2020','4');

insert into t_salary values('yc90059426',12,18,0,1500.00,'2020','3');

insert into t_salary values('yc90059418',12,2,3,1200.00,'2020','4');

insert into t_salary values('yc90059418',12,8,2,1600.00,'2020','3');

insert into t_salary values('yc90059425',12,8,1,1200.00,'2020','5');

insert into t_salary values('yc90059425',12,6,3,1200.00,'2020','4');

insert into t_salary values('yc90059425',12,0,0,1200.00,'2020','3');

insert into t_salary values('yc90059001',12,8,0,3200.00,'2020','4');

insert into t_salary values('yc90059000',12,8,0,4200.00,'2020','4');

insert into t_salary values('yc90058000',12,8,0,5200.00,'2020','4');


commit;


/*
练习题
员工信息表：姓名、工号、性别、年龄、省、市、县（区）、籍贯、
出生日期、入职日期、联系电话、部门、部门编码、科段、科段编码、
岗位、岗位编码，工作地点，家庭地址
薪酬表字段：工号、工作天数、加班天数，迟到次数、基本工资、年份、月份
*/


--1、查询员工姓名，员工的联系方式，优先返回联系电话，没有则返回邮箱

update t_employee set tel=NULL where empno = 'yc90058000';--修改一个空数据
update t_employee set tel='  ' where empno = 'yc90059000';--修改一个空格数据

/**
nvl函数基本语法为nvl(E1,E2)，意思是E1为null就返回E2，不为null就返回E1。
nvl2函数的是nvl函数的拓展，基本语法为nvl2(E1,E2,E3)，意思是E1为null，就返回E3，不为null就返回E2。
*/

select empno,ename,nvl(tel,work_address) from t_employee;
select empno,ename,nvl(replace(tel,' ',''),work_address) from t_employee;-- 去空格


--2、查询员工的实发工资，核算规则：加班费300/天，迟到50/一次，数据要求，起码有一个员工加班，
-- 一个员工全勤，一个员工迟到，别查询数据工资要求形成对比（基本工资、实发工资）
select s.empno，ename,salary as "基本薪资",workday*300 as "加班薪资"，overtime_workday*50 "迟到扣薪",
(salary+workday*300-overtime_workday*50) as "实际薪资",(yyear||'-'||mmonth) as "年月" 
from t_salary s,t_employee e where s.empno = e.empno;


--3、关联查询，查询出各部门员工4月份的实发工资
--查询结果：姓名、工号、部门、岗位、工作天数、加班天数，迟到次数、基本工资、实发工资、年份、月份
select s.empno，e.ename,e.deptname,e.secname,e.jobname,s.workday,s.overtime_workday,
s.lateday,salary as "基本薪资",(salary+workday*300-overtime_workday*50) as "实际薪资",
(yyear||'-'||mmonth) as "年月" 
from t_salary s,t_employee e 
where  s.empno = e.empno and s.mmonth = 4
order by e.deptno;

--4、查询4月份有加班的员工信息薪资，姓名、工号、部门、岗位、工作天数、加班天数，基本工资、实发工资、年份、月份；左连接
insert into t_salary values('yc90058999',12,8,0,5200.00,'2020','4');
update t_salary set overtime_workday = 0 where empno = 'yc90059426' and mmonth = 4;

select s.empno，e.ename,e.deptname,e.secname,e.jobname,s.workday,s.overtime_workday,
s.lateday,salary as "基本薪资",(salary+workday*300-overtime_workday*50) as "实际薪资",
(yyear||'-'||mmonth) as "年月" 
from t_salary s
left outer join t_employee e
on  s.empno = e.empno 
where s.mmonth = 4 and s.overtime_workday >0;


--查询4月份有迟到的员工信息薪资，姓名、工号、部门、岗位、工作天数、迟到次数、基本工资、实发工资、年份、月份；右连接

delete from t_salary where empno = 'yc90059000';
select * from t_employee;

select s.empno，e.ename,e.deptname,e.secname,e.jobname,s.workday,s.overtime_workday,
s.lateday,salary as "基本薪资",(salary+workday*300-overtime_workday*50) as "实际薪资",
(yyear||'-'||mmonth) as "年月" 
from t_salary s
right outer join t_employee e
on  s.empno = e.empno
where s.mmonth = 4 and s.lateday > 0;--这里因为加了4月这个条件限制，所以没薪水的右连接不显示


--5、计算出“def001”员工的工资信息：总工资、平均工资、最高工资、最低工资；
select sum(salary+workday*300-overtime_workday*50) as "总工资",
avg(salary+workday*300-overtime_workday*50) as "平均工资"，
max(salary+workday*300-overtime_workday*50) as "最高工资"，
min(salary+workday*300-overtime_workday*50) as "最低工资"
from t_salary s,t_employee e
where s.empno = e.empno and deptno = 'def0002';

-- 计算各部门的平均工资、总工资
select e.deptno,sum(salary+workday*300-overtime_workday*50) as "总工资",
avg(salary+workday*300-overtime_workday*50) as "平均工资"
from t_salary s,t_employee e
where s.empno = e.empno
group by e.deptno;

--6、查询平均工资大于6000的部门工资信息

select deptno,deptname,avg(salary+workday*300-overtime_workday*50) as "平均工资"
from t_salary s,t_employee e
where  s.empno = e.empno and 
deptno in 
( select deptno from t_salary s1,t_employee e1 
  where s1.empno = e1.empno
  having avg(s1.salary+s1.workday*300-s1.overtime_workday*50) > 6000
  group by e1.deptno )
group by deptno,deptname;