Oracle12cR2学习记录1

四张表

联系的4张表分别为dept,emp,bonus,salgrade

表1 DEPT部门表

Oracle12cR2学习记录1


表2 EMP员工表

Oracle12cR2学习记录1


表3 BONUS奖金表

Oracle12cR2学习记录1


表4 SALGRADE工资表

Oracle12cR2学习记录1


基本操作

查看数据库状态,创建新用户+授权,运行脚本,使用新用户登录

#查看当前数据库的名称和状态
select instance_name,status from v$instance;

Oracle12cR2学习记录1


#查看当前的open状态
select open_mode from v$database;

Oracle12cR2学习记录1


#创建用户vos
create user c##vos identified by oracle;

Oracle12cR2学习记录1


#授予vos权限
grant connect,resource to c##vos;
alter user c##vos quota unlimited on users;

Oracle12cR2学习记录1


#使用vos用户连接
conn c##vos/oracle

Oracle12cR2学习记录1


#执行脚本
@/home/oracle/vos.sql

Oracle12cR2学习记录1 vos.zip

Oracle12cR2学习记录1


#查看当前用户
show user;

Oracle12cR2学习记录1


#调整显示结果,使输出更整齐
#查看并设置输出的最大行宽
show linesize   
set linesize 300
#查看并设置显示总行数
show pagesize
set pagesize 100
#修改显示字段的长度或名称
column col_name format a20
column col_num format 999999
column col_num heading col_num2
#将所有的tab转换为space
set tab off
#允许空格
set sqlblanklines on

#或者一劳永逸修改glogin.sql
vim /u01/app/oracle/product/12.2.0/db_home/sqlplus/admin/glogin.sql 
添加
define_editor=vi
set linesize 300
set pagesize 100
set tab off
set sqlblanklines on
set sqlprompt "_user'@'_connect_identifier> "
column col_name format a20
column col_num format 999999
column col_num heading col_num2
column plan_plus_exp format a80
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';


基本查询

语法:

SELECT [DISTINCT]*| 字段 [别名][,字段 [别名]]

FROM 表名称 [表别名]

#查看当前用户有哪些表
select table_name from user_tables;

Oracle12cR2学习记录1


#查看用户的表
select * from tab;

Oracle12cR2学习记录1


#查看emp表的表结构
desc emp

Oracle12cR2学习记录1


#列出dept表的所有列
select * from dept;

Oracle12cR2学习记录1


#查看指定列
select deptno,loc from dept;

Oracle12cR2学习记录1


#查询每个雇员的编号,姓名和基本工资
select empno,ename,sal from emp;

Oracle12cR2学习记录1


#查询每个雇员的职位
select job from emp;

Oracle12cR2学习记录1


#查询出来的job内容有重复数据,使用distinct删除重复

Oracle12cR2学习记录1


#查询每个雇员的姓名,职位

Oracle12cR2学习记录1


#查询每一个雇员的姓名,职位和基本年薪(使用四则运算符)
select ename,job,sal*12 from emp;

Oracle12cR2学习记录1


#使用列的名称显示不方便浏览,可以使用列别名,将SAL*12改为income
select ename,job,sal*12 income from emp;
或者
select ename,job,sal*12 as income from emp;

Oracle12cR2学习记录1


#每个月给每个人增加200的饭补和100的车补,再计算年薪
select ename,job,(sal+300)*12 income from emp;

Oracle12cR2学习记录1


#年底多发一个月的基本工资当年终奖
select ename,job,(sal+300)*12+sal income from emp;

Oracle12cR2学习记录1


#使用||连接符
select empno||','||ename from emp;

Oracle12cR2学习记录1


限定查询

语法: 

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]]

FROM 表名称 [表别名]

[WHERE 条件(s)]

条件:

>,>=,<,<=,!=(<>),

BETWEEN...AND...,LIKE,IN,IS NULL,AND,OR,NOT


1.关系运算

#查询出基本工资高于1500的所有员工信息
select * from emp where sal>1500;

Oracle12cR2学习记录1


#查询所有职位是普通职员信息(在oracle数据库,数据需要区分大小写)
select * from emp where job='CLERK';

Oracle12cR2学习记录1


#查询工资在1500~3000之间的全部雇员信息
select * from emp where sal>=1500 and sal<=3000;
或者
select * from emp where sal between 1500 and 3000;

Oracle12cR2学习记录1

select * from emp where sal>1500 and sal<3000;
#排除了1500和3000

Oracle12cR2学习记录1


#查询职位是普通职员,或者是销售员的全部信息
select * from emp where job='CLERK' or job='SALESMAN';

Oracle12cR2学习记录1


#查询职位是普通职员,或者销售员的全部信息,并要求这些雇员的工资是大于1200的
select * from emp
where (job='CLERK' or job='SALESMAN') 
and sal>1200;

Oracle12cR2学习记录1


#查询所有不是普通职员的雇员信息
select * from emp where job<>'CLERK';
或者
select * from emp where job!='CLERK';
或者
select * from emp where NOT job='CLERK';

Oracle12cR2学习记录1


2.范围判断

语法:

BETWEEN 最小值 AND 最大值

#查询基本工资在1500-3000的雇员信息

Oracle12cR2学习记录1

#求反,查询了基本工资不在1500-3000的雇员信息
select * from emp where not sal between 1500 and 3000;

Oracle12cR2学习记录1


3.判断是否为空

语法:

IS(NOT) NULL,空值不是数字0或者空字符串

#查询出所有领取奖金的雇员信息
select * from emp where comm is not null;
或者
select * from emp where not comm is null;

Oracle12cR2学习记录1


#查询所有不领取奖金的雇员
select * from emp where comm is null;

Oracle12cR2学习记录1


4.指定范围的判断

语法:

IN操作符表示指定一个范围

#查询雇员编号是7369,7566,7799的雇员信息
select * from emp
where empno=7369 
or empno=7566 
or empno=7799;
#使用IN
select * from emp where empno in (7369,7566,7799);
#如果使用IN操作符,查询的范围之中存在null,不影响查询

Oracle12cR2学习记录1

使用NOT IN表示不在指定范围内

select * from emp where empno not in (7369,7566,7799,null);
#如果使用NOT IN操作符,如果查询范围中有null,则不会有任何查询结果,因为由于NULL值不能参与比较运算符,导致条件不成立,查询不出来数据

Oracle12cR2学习记录1


5.模糊查询

语法:

LIKE子句

_:匹配单个字符

%:匹配任意多个字符

#查询雇员姓名中以字母A开头的全部雇员信息
select * from emp where ename like 'A%';

Oracle12cR2学习记录1


#查询雇员姓名中第二个字母是A的全部雇员信息
select * from emp where ename like '_A%';

Oracle12cR2学习记录1

#求反,查询除了雇员姓名中第二个字母是A的其他全部雇员信息
#%%表示查询全部信息
select * from emp where ename not like '%A%';

Oracle12cR2学习记录1


数据排序

语法:

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]]

FROM 表名称 [别名]

[WHERE 条件(S)]

[ORDER BY 字段 [ASC|DESC][字段 [ASC|DESC],...]]


说明:该子句在所有的SQL语句最后,可以指定多个排序的字段,默认升序,降序需要手工指定,有需要的时候才进行排序

#查询所有雇员的信息,按工资排序
select * from emp order by sal asc;
或者
select * from emp order by sal;

Oracle12cR2学习记录1


#降序排列
select * from emp order by sal desc;

Oracle12cR2学习记录1


#查询所有雇员信息,按照工资降序排列,工资相同,则按雇佣日期从早到晚排列
select * from emp order by sal desc,hiredate asc;

Oracle12cR2学习记录1


单行函数

单数分类为:字符函数,数字函数,日期函数,转换函数,通用函数

1.字符函数

主要是进行字符串数据的操作,分为

UPPER(字符串|列)将输入的字符串变为大写返回

LOWER(字符串|列)将输入的字符串变为小写返回

INITCAP(字符串|列)开头首字母大写

LENGTH(字符串|列)求出字符串长度

REPLACE(字符串|列)进行替换

SUBSTR(字符串|列)开始点[结束点],字符串截取

#使用oracle12c提供的一个虚拟表dual进行操作
#转大写
select upper('hello') from dual;

Oracle12cR2学习记录1

select * from emp where ename=upper('&str');
Enter value for str: king

Oracle12cR2学习记录1


#转小写
#将所有雇员姓名按照小写字母输出
select lower(ename) from emp;

Oracle12cR2学习记录1


#将每个雇员姓名的开头首字母大写
select initcap(ename) from emp;

Oracle12cR2学习记录1


#查询每个雇员姓名的长度
select ename,length(ename) from emp;

Oracle12cR2学习记录1


#查询雇员姓名长度刚好是5的雇员信息
select ename,length(ename) from emp where length(ename)=5;

Oracle12cR2学习记录1


#使用字符"_",替换雇员姓名中的所有字母"A"
select replace(ename,'A','_') from emp;

Oracle12cR2学习记录1


#字符串截取有两种语法:
#SUBSTR(字符串|列,开始点),表示从开始点一直截取到结尾
select ename,substr(ename,3) from emp;

Oracle12cR2学习记录1

#SUBSTR(字符串|列,开始点,截取多少位),表示从开始点截取多少位
select ename,substr(ename,0,3) from emp;
或者
select ename,substr(ename,1,3) from emp;

Oracle12cR2学习记录1


#截取每个雇员名字的后三个字母,通过长度-2确定开始点
select ename,substr(ename,length(ename)-2) from emp;

Oracle12cR2学习记录1


设置负数,表示从后指定截取位置
select ename,substr(ename,-3) from emp;

Oracle12cR2学习记录1


2.数字函数

#ROUND,(数字|列[,保留小数的范围]):四舍五入的操作
select round(903.5) from dual;
select round(903.53567) from dual;
select round(903.53567),round(-903.53567) from dual;
select round(903.53567),round(-903.53567),round(903.53567,-1) from dual;
select round(903.53567),round(-903.53567),round(903.53567,-1),round(903.53567,2) from dual;

Oracle12cR2学习记录1


#TRUNC,(数字|列[,保留小数的范围]): 舍弃指定位置的内容
select trunc(903.53567),trunc(-903.53567),trunc(903.53567,-1),trunc(903.53567,2) from dual;

Oracle12cR2学习记录1


#MOD,(数字1,数字2):取模,取余数
select mod(10,3) from dual;
select mod(3,10) from dual;

Oracle12cR2学习记录1


3.日期函数

日期的计算方式:

日期+数字=日期,表示若干天之后的日期

日期-数字=日期,表示若干天之前的日期

日期-日期=数字,表示两个日期之间的天数,但是必须为大日期去减小日期

#数据库查询今天的日期,使用“SYSDATE”
select sysdate from dual;

Oracle12cR2学习记录1


#查询每个雇员到今天为止的雇佣天数
select ename,hiredate,sysdate-hiredate from emp;

Oracle12cR2学习记录1


#日期的操作函数
#在所有开发之中,如果是日期的操作,建议使用以下的函数可以避免闰年的问题。
#LAST_DAY(日期):查询出哪个日期为当月的最后一天
#求出本月的最后一天日期
select last_day(sysdate) from dual;

Oracle12cR2学习记录1

#NEXT_DAY(日期,星期数):查询出下一个指定星期几的日期
#下一个周六是几号
select next_day(sysdate,'sat') from dual;
#下一个周日是几号
select next_day(sysdate,'sun') from dual;

Oracle12cR2学习记录1

#ADD_MONTHS(日期,数字):查询出若干月之后的日期
#五个月后是几号
select add_months(sysdate,5) from dual;

Oracle12cR2学习记录1

#MOUNTHS_BETWEEN(日期1,日期2):查询出两个日期之间所经历的月份
#求出每个雇员到今天为止的雇佣了多少个月
select ename,hiredate,trunc(months_between(sysdate,hiredate)) from emp;

Oracle12cR2学习记录1


4.转换函数

转换函数的主要功能是完成这几种数据之间的互相转换的操作,一共有三种转换函数

TO_CHAR(字符串|列,格式字符串):将日期或者数字变为字符串显示

TO_DATE(字符串,格式字符串):将字符串转变为DATE数据显示

TO_NUMBER(字符串):将字符串变为数字显示

#TO_CHAR()函数
#格式:年(yyyy),月(mm),日(dd)
#拆分年月日
select to_char(sysdate,'yyyy-mm-dd'),to_char(sysdate,'yyyy') year,to_char(sysdate,'mm') month,to_char(sysdate,'dd') day from dual;

Oracle12cR2学习记录1

#删除日期中的前导零
select to_char(sysdate,'fmyyyy-mm-dd') day from dual;

Oracle12cR2学习记录1

#在oracle之中,sysdate函数中是包含了时间的,要显示时间需要增加标记
#使用sysdate显示时间
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') haha from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
#使用24小时制
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

Oracle12cR2学习记录1

#使用TO_CHAR函数之后,所有内容为字符串,不再是date型数据,TO_CHAR同样可以用在数字上用来格式化数字
#格式化数字22222222222;此时格式化字符串的数字9表示格式,而不是数字9
select to_char(22222222222,'999,999,999,999,999,999,999') shuzi from dual;

Oracle12cR2学习记录1

#格式化数字为货币记录格式,使用“L”标记表示转换为当前语言环境下的货币符号
select to_char(22222222222,'L999,999,999,999,999,999,999') huobi from dual;

Oracle12cR2学习记录1


#TO_DATE
select to_date('1989-09-12','yyyy-mm-dd') from dual;

Oracle12cR2学习记录1


#TO_NUMBER   (不推荐使用)
select to_number('2')+to_number('3') from dual;
#在oracle中,不使用该函数也可以完成该功能
select '2'+'3' from dual;

Oracle12cR2学习记录1


5.通用函数

通用函数主要有NVL()和DECODE()这两个

#NVL 处理null
#要求查询所有雇员的全部年薪
select ename,sal,comm,(sal+comm)*12 from emp;

Oracle12cR2学习记录1

#查询的结果出现了问题,解决办法是将comm中的null值变成0
#查看转换过程
select ename,sal,comm,nvl(comm,0) from emp;

Oracle12cR2学习记录1

#解决问题方法
select ename,sal,comm,(sal+nvl(comm,0))*12 from emp;

Oracle12cR2学习记录1


#DECODE():多数值判断
#判断的是数值而不是条件,类似编程中的if...else...
#这种判断肯定是进行逐行判断,所以这个时候就必须采用DECODE(),而此函数的用法为DECODE(数值|列,判断值1,显示值1,判断值2,显示值2,...)
#例如:要求显示全部雇员的职位,但是这些职位要求显示成pinyin。
CLERK:		普通职员
SALESMAN:	销售员
MANAGER:	经理
ANALYST:	        分析员
PRESIDENT:	总裁
#实现语句:
select empno,ename,job,decode(job,
'CLERK','putongzhiyuan',
'SALESMAN','xiaoshouyuan',
'MANAGER','jingli',	
'ANALYST','fenxiyuan',
'PRESIDENT','zongcai') 
from emp;

Oracle12cR2学习记录1


多表查询

语法:

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]]

FROM 表名称 [别名],[表名称 [别名],表名称 [别名]...]

[WHERE 条件(S)]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]]

#多表查询之前,首先必须查询各个表中的数据量,查询操作可以通过COUNT()函数来完成。
#查询emp表中的数据量
select count(*) from emp;
#查询dept表中的数据量
select count(*) from dept;
确认数据量后,再执行多表查询语句:
select * from emp,dept;

Oracle12cR2学习记录1

#语句的语法是没有问题的,但是因为数据库的机制导致产生了笛卡儿积,需要采用关联字段的形式,emp表和dept表之间存在了deptno的关联字段,从关联字段下手,解法如下:
select * from emp,dept where deptno=deptno;   #执行错误
使用
select * from emp,dept where emp.deptno=dept.deptno;	  #显示上笛卡儿积已经消除,实际上笛卡儿积还在,并没有消除。
#如果表的名称比较长,那么这样的方式很不方便使用,解决办法就是使用表别名,如下:
select * from emp e,dept d where e.deptno=d.deptno;

Oracle12cR2学习记录1


#查询每一位雇员的编号,姓名,职位,部门名称,部门位置
思路:
1、首先确认需要的表;emp表可以查询雇员的编号,姓名,职位;dept表可以查询部门名称和位置;
2、确定表的关联字段;emp.deptno=dept.deptno
做法:
1、查询出每一位雇员的编号,姓名和职位;
select e.empno,e.ename,e.job from emp e;
2、为查询中引入部门表,同时增加消除笛卡儿积的条件;
select e.empno,e.ename,e.job,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;

Oracle12cR2学习记录1


#查询出每一位雇员的姓名,职位和领导姓名
思路:
1、确认需要的表;emp表可以查询雇员的姓名,职位和领导编号和领导的姓名
2、确定关联字段;emp.mgr=emp.empno,雇员的领导编号=领导的雇员编号
做法:
1、查询每一位雇员的姓名和职位
select e.ename,e.job from emp e;
2、查询领导信息,加入自身关联
select e.ename,e.job,m.ename 
from emp e,emp m
where e.mgr=m.empno;
注意:查询结果变为13行少了一行,缺少了KING的领导姓名,因为他没有领导,这需要左右链接才可解决。

Oracle12cR2学习记录1


#查询出每个雇员的编号,姓名,基本工资,职位,领导的姓名,部门名称及位置
思路:
1、确认需要的表;
emp表查询每个雇员的编号,姓名,基本工资,职位
emp表查询领导的姓名
dept表查询部门的名称及位置
2、确定已知的关联字段
雇员和部门: e.deptno=d.deptno
雇员和领导: e.mgr=m.empno
做法:
1、查询出每个雇员的编号,姓名,基本工资,职位
select empno,ename,sal,job from emp;
2、加入领导的信息,引入自身关联,同时增加消除笛卡儿积的条件
select e.empno,e.ename,e.sal,e.job,m.ename
from emp e,emp m
where e.mgr=m.empno;
3、加入部门的信息,引入dept表,有新表,则要继续加入消除笛卡儿积的条件
select e.empno,e.ename,e.sal,e.job,m.ename,d.dname,d.loc
from emp e,emp m,dept d
where e.mgr=m.empno 
and e.deptno=d.deptno;

Oracle12cR2学习记录1


#查询出每一个雇员的编号,姓名,工资,领导的姓名,部门名称及位置,工资所在公司的工资等级
select e.empno,e.ename,e.sal,d.dname,s.grade 
from emp e,dept d,salgrade s
where e.deptno=d.deptno 
and e.sal between s.losal 
and s.hisal;

Oracle12cR2学习记录1


左右链接

#左右连接可以改变查询判断条件的参考方向
例如以下查询
select * from emp e,dept d where e.deptno=d.deptno;
注意:一共有四个部门,但是这里只有三个,缺少编号40部门的部门信息,原因就是现在的查询以emp表为参考进行查询,要想显示40部门,就必须改变参考的方向,这时就需要左右连接
用法:
select * from emp e,dept d where e.deptno(+)=d.deptno;

(+)用于左右连接的更改,这种符号有以下两种使用情况:
(+)=:表示右连接
=(+):表示左连接

不用刻意区分左还是右,根据查询结果来定,如果发现有些需要的数据没有显示出来,就使用此符号来改变连接方向,该符号为oracle独有。
#如之前的查询领导姓名的范例:
select e.ename,e.job,m.ename from emp e,emp m where e.mgr=m.empno(+);

Oracle12cR2学习记录1


查询字段时需要多表关联,需要用到表别名,内连接,外连接,自然连接,自连接这些关键连接方法

1.表别名

#查询员工编号  员工姓名  部门名称 
select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

Oracle12cR2学习记录1


2.内连接

#A(inner)join B on 连接条件
#内连接必须满足连接条件,不满足的信息无法显示
select e.empno,e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

Oracle12cR2学习记录1


3.外连接

#左外连接
#关键字 A left join B on 连接条件
#首先添加一条没有部门的员工信息
insert into myemp(empno,ename,job) values(1100,'rose','artist');
#emp表里面rose没有员工编号,dept表不能找到rose的部门信息
select e.empno,e.ename,d.dname from myemp e left join dept d on e.deptno=d.deptno;
#条件查询=(+) 左连接
select e.empno,e.ename,d.dname from myemp e,dept d where e.deptno=d.deptno(+);

#右外连接
#关键字A right join B on 连接条件
#dept表的40号部门没有员工信息,使用内连接和条件查询都无法显示,使用右连接可让40号部门显示出来
select e.empno,e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;

Oracle12cR2学习记录1

#条件查询左右连接   +)=右连接     =(+)左连接
select e.empno,e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno(+); 

#完全外连接 full join
select e.empno,e.ename,d.dname
from myemp e full join dept d
on e.deptno=d.deptno;


4.自然连接

#不用区分关联条件,oracle会自动区分,缺点为如果表名不确定,不能查询
#查询工资大于2000的员工编号,员工姓名和部门名称
select empno,ename,dname from emp  natural join dept where sal>2000;

Oracle12cR2学习记录1


5.自连接

#查询员工姓名,员工职称以及员工所属领导
select e.ename,e.job,m.ename 
from emp e left join emp m
on e.mgr=m.empno;
#条件查询
select e.ename,e.job,m.ename
from emp e,emp m
where e.mgr=m.empno;

Oracle12cR2学习记录1


统计函数

分类:COUNT():查询表中的数据记录   AVG():求出平均值   SUM():求和    

MAX():求出最大值   MIN():求出最小值

#统计出公司的所有雇员每个月支付的平均工资及总工资
select count(empno),sum(sal),avg(sal) from emp;
select sum(sal),avg(sal) from emp;
#select empno,sum(sal),avg(sal) from emp;会执行错误,改为
select empno,sum(sal),avg(sal) from emp group by empno;

Oracle12cR2学习记录1


#统计雇员中的最高和最低工资
select max(sal),min(sal) from emp;
#注意:COUNT()函数的主要功能是进行数据的统计,但是在进行数据统计的时候,如果一张表中没有记录,那么COUNT()也会返回数据,只是这个数据是“0”.
#如:select count(ename) from bonus;       #返回数字0
      select sum(sal) from bonus;		#返回NULL
#如果使用其他函数,则有可能返回NULL值,但是COUNT()永远都会返回一个具体的数字

Oracle12cR2学习记录1


分组统计

什么情况下需要分组统计?

1、男的分一组,女的分一组

2、年龄分组,成年和未成年

3、地区分组,上海和北京,

这些信息如果都保存在数据库中,肯定在数据库的某一列上存在重复数据,例如按照性别分组的时候,有男和女,按照年龄分组,有一个范围的重复,按照地区的话有一个地区的信息重复

注意:当数据重复的时候分组才有意义,一个人也可以单独分一组,但是没有实际意义

如果需要分组可以使用GROUP BY子句

语法:

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],...]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,...]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]]

#按照部门编号分组,求出每个部门的人数和平均工资
select deptno,count(empno),avg(sal) from emp group by deptno;

Oracle12cR2学习记录1


#按照职位分组,求出每个职位的最高和最低工资
select job,max(sal),min(sal) from emp group by job;

Oracle12cR2学习记录1


#分组函数有以下要求:
#1、分组函数可以在没有分组的时候单独使用,可是不能出现其他的查询字段
#错误使用,出现其他字段:select empno,count(empno) from emp;
#正确使用:select count(empno) from emp;

#2、如果要进行分组,则select子句之后,只能出现分组的字段和统计函数,其他字段不能出现
#错误用法:select empno,job,count(empno),avg(sal) from emp group by job;
#正确使用:select job,count(empno),avg(sal) from emp group by job;

#3、分组函数允许嵌套,但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段
#按照职位分组,统计平均工资最高的工资
#先统计出各个职位的平均工资
select job,avg(sal) from emp group by job;
#查询平均工资最高的工资
#错误使用:select job,max(avg(sal)) from emp group by job;
#正确使用:select max(avg(sal)) from emp group by job;

#查询出每个部门的名称,部门的人数,平均工资
#思路:
#先确定所需要的数据表 dept表:每个部门的名称   emp表:统计出部门的人数,平均工资
#再确定已知的关联字段emp.deptno=dept.deptno
#做法:
#1、将dept表和emp表的数据关联
select d.dname,e.ename,e.sal from dept d,emp e where d.deptno=e.deptno;
#2、查看上面语句执行的结果,有重复值,就可以进行分组查询,但是此时与之前的分组不太一样,之前的分组是针对一张实体表,但是对于以上的数据是通过查询结果显示的,所以是一张临时的虚拟表,但是不管是实体表和虚拟表,只要是有重复,那么就直接进行分组
select d.dname,count(e.empno),avg(e.sal) from dept d,emp e where d.deptno=e.deptno group by d.dname;

#通过结果可以看到,这个分组并不合适,部门一共有四个,因为已经引入了dept表,该表存在了四个部门,所以应该通过左右连接来改变查询的结果
select d.dname,count(e.empno),avg(e.sal) from dept d,emp e where d.deptno=e.deptno(+) group by d.dname;
	
#查询结果发现OPERATIONS部门的平均工资为NULL值,并不符合结果预期,所以可以通过NVL函数来进行处理,如下:
select d.dname,count(e.empno),trunc(nvl(avg(e.sal),0)) avg from dept d,emp e where d.deptno=e.deptno(+) group by d.dname;

Oracle12cR2学习记录1


多字段分组

#显示每个部门的编号,名称,位置,部门的人数和平均工资
#不管是单字段还是多字段,一定要有一个前提就是存在了重复数据
#思路:
#1、确定所需要的数据表 dept表:每个部门的编号,名称,位置  emp表:统计出部门的人数,平均工资
#2、确定已知的关联字段 emp.deptno=dept.deptno
#做法:
#1、将emp表和dept表关联
select d.deptno,d.dname,d.loc,e.empno,e.sal from dept d,emp e where d.deptno=e.deptno(+);
	
#2、查看以上语句执行的结果,发现有三个列存在重复数据(deptno,dname,loc),所以在分组上的GROUP BY子句中就可以写上三个字段:
select d.deptno,d.dname,d.loc,count(e.empno),trunc(nvl(avg(e.sal),0)) avg from dept d,emp e where d.deptno=e.deptno(+) group by d.deptno,d.dname,d.loc;

Oracle12cR2学习记录1


#统计出每个部门的详细信息,并且要求这些部门的平均工资高于2000
#使用where子句
select d.deptno,d.dname,d.loc,count(e.empno) mycount,trunc(nvl(avg(e.sal),0)) myavg
from dept d,emp e
where d.deptno=e.deptno(+) 
and avg(e.sal)>2000
group by d.deptno,d.dname,d.loc;

Oracle12cR2学习记录1

#执行该语句会报错ORA-00934: group function is not allowed here,在where子句中不能使用统计函数,这和where子句的功能有关
#如果要对分组后的数据再次进行过滤,需要使用HAVING子句
#WHERE和HAVING的区别
#WHERE:在执行GROUP BY操作之前进行的过滤,表示从全部数据中进行过滤,不能使用统计函数;
#HAVING:在GROUP BY分组之后的再次过滤,可以使用统计函数。

语法

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],...]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,...]]

[HAVING 分组后的过滤条件(可以使用分组函数)]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]]

#使用HAVING子句
select d.deptno,d.dname,d.loc,count(e.empno) mycount,trunc(nvl(avg(e.sal),0)) myavg
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname,d.loc
having avg(sal)>2000;

Oracle12cR2学习记录1


子查询

子查询=简单查询+限定查询+多表查询+统计查询的综合体,多表查询不建议使用,因为性能差,但是多表查询最有利的替代者就是子查询,在实际的开发中使用最多的就是子查询。

语法:

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数,(

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],...]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,...]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]])

FROM 表名称 [别名],[表名称 [别名],...],(

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],...]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,...]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]])

[WHERE 条件(s)](

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],...]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,...]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]])

[GROUP BY 分组字段1 [,分组字段2,...]]

[HAVING 分组后的过滤条件(可以使用统计函数)]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]]


WHERE:子查询一般只返回单行单列,多行单列,单行多列的数据

FROM:子查询返回的一般是多行多列的数据,当作一张临时表出现

#查出工资比SMITH还要高的全部信息
#思路:
1、首先要知道SMITH的工资是多少
select sal from emp where ename='SMITH';
2、由于此时返回的是单行单列的数据,所以这个子查询可以在WHERE中出现
select * from emp where sal>(select sal from emp where ename='SMITH');


Oracle12cR2学习记录1


#查询出高于公司平均工资的全部雇员信息
#思路:
1、公司的平均工资是多少?
select avg(sal) from emp;
2、由于此时返回的是单行单列的数据,在WHERE之中出现
select * from emp where sal>(select avg(sal) from emp);
以上返回的是单行单列,但是在子查询中,也可以返回单行多列的数据,只是这种子查询很少出现,如
select * from emp where(job,sal)=(select job,sal from emp where ename='ALLEN');

Oracle12cR2学习记录1


如果子查询返回的是多行单列数据的话,这个时候就需要使用三种判断符来进行判断:IN,ANY,ALL

#IN操作符:用于指定一个子查询的判断范围
select * from emp
where sal in(
select sal
from emp
where job='MANAGER');

Oracle12cR2学习记录1

#在使用IN的时候还要注意NOT IN的问题,如果使用NOT IN操作,在子查询中,如果有一个内容是NULL,则不会有任何查询结果,因为如果有NULL,则会查询所有数据,如果数据量太大就会导致有漏洞产生,所以加入限制。
select * from emp
where sal not in(
select sal
from emp
where job='MANAGER');

Oracle12cR2学习记录1


#ANY操作符,与每一个内容相匹配,有三种匹配形式
#=ANY:功能与IN操作符是完全一样
select * from emp
where sal=any(
select sal
from emp
where job='MANAGER');

Oracle12cR2学习记录1

#>ANY:比子查询中返回记录最小的还要大的数据
select * from emp
where sal>any(
select sal
from emp
where job='MANAGER');

Oracle12cR2学习记录1

#<any:比子查询中返回记录的最大值还要小的
select * from emp
where sal<any(
select sal
from emp
where job='MANAGER');

Oracle12cR2学习记录1


#ALL操作符:与每一个内容相匹配,有两种形式
#>ALL:比子查询结果中最大值还大
select * from emp
where sal>all(
select sal
from emp
where job='MANAGER');	

Oracle12cR2学习记录1

#<ALL:比子查询结果中最小值还小
select * from emp
where sal<all(
select sal
from emp
where job='MANAGER');

Oracle12cR2学习记录1


#FROM子句中出现的子查询,这个子查询一般都返回的是多行多列的数据,当作一张临时表的方式来处理。
#查询出每个部门的编号,名称,位置和部门人数,平均工资
#之前使用的是多字段分组统计完成的
select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;
#这时产生了笛卡儿积,共56条记录

#使用子查询来完成,所有的统计查询只能在GROUP BY中出现,所以在子查询之中负责统计数据,而在外部的查询之中,负责将统计数据和dept表数据相统一
select d.deptno,d.dname,d.loc,nvl(temp.count,0),nvl(temp.avg,0)
from dept d,(
select deptno dno,count(empno) count,avg(sal) avg
from emp
group by deptno) temp
where d.deptno=temp.dno(+);

#这种子查询和之前的两种方法的比较:
#1、子查询中统计的记录是14条,最终统计的显示结果是3条结果
#2、dept表之中一共有4条记录
#3、如果现在产生笛卡儿积的话只有12条,在加上雇员的14条,一共才26条
#在开发中使用子查询可以提高效率和节省性能,大部分情况下:如果最终查询结果中出现了select语句,但是又不能直接使用统计函数时,就在子查询中统计信息。




更新日期: 2020年02月06日
文章标签: Oracle
文章链接: https://www.vos.cn/db/395.html  
版权说明:如非注明,本站文章均为vOS原创,转载请注明出处和附带本文链接。