oracle表查询的具体操作

–创建表空间
create tablespace ithe
datafile ‘C:\ithe.dbf’
size 100m
autoextend on
next 10m;
–创建用户
create user ithe
identified by ithe
default tablespace ithe;
–connect–连接角色，基本角色
–resource–开发者角色
–dba–超级管理员角色
–给ithe用户授予dba角色
grant dba to ithe;

select * from person;
–删除表和索引
truncate table person;
insert into person (pid,pname) values (s_person.nextval,‘小明’);
commit;
–序列不真的属于任何一张表，但是可以逻辑和表做绑定。
–序列：默认从1开始，依次递增，只是用来给主键赋值
–dual:虚表，只是为了补全语法，没有任何意义。
create sequence s_person;
select s_person.currval from dual;
select s_person.nextval from dual;
–scott用户，密码tiger。
–解锁scott用户
alter user scott account unlock;
–解锁scott用户的密码
alter user scott identified by tiger;
–切换到scott用户下
–查询出emp表中所有员工入职距离现在几天。
select sysdate-e.hiredate from emp e;
select sysdate+1 from dual;
–查询出emp表中所有员工入职距离现在几个月。
select months_between(sysdate,e.hiredate) from emp e;
–查询出emp表中所有员工入职距离现在几年。
select months_between(sysdate,e.hiredate)/12 from emp e;
–查询出emp表中所有员工入职距离现在个星期
select round((sysdate-e.hiredate)/7) from emp e;

–转换函数
–日期转字符串 加上fm可以实现：2020-09-02 17:08:19-> 2020-9-2 17:8:40
select to_char(sysdate,‘fm yyyy-mm-dd hh24:mi:ss’)from dual;
–字符串转日期
select to_date(‘2020-9-2 17:4:15’,‘fm yyyy-mm-dd hh24:mi:ss’)from dual;
–通用函数
–算出emp表中所有员工的年薪
–nvl(e.comm,0)当前面的参数是null时，用后面的零，不是则用第一个参数
select e.sal*12+nvl(e.comm,0) from emp e;

–条件表达式(通用表达式)
–给emp表中员工起中文名（在这里的case必须声明e.ename）
select e.ename,
case e.ename
when ‘SMITH’ then ‘李辉’
when ‘ALLEN’ then ‘张龙’
else ‘无名’
end
from emp e;
–判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示为中等收入，其余为低收入 (在这道题当中case后面不用加e.sal是因为后面已经声明了e.sal)
select e.sal,
case
when e.sal>3000 then ‘高收入’
when e.sal>1500 then ‘中等收入’
else ‘低收入’
end
from emp e;

–条件表达式（专用条件表达式）
select e.ename,
decode(e.ename,
‘SMITH’, ‘李辉’,
‘ALLEN’, ‘张龙’,
‘无名’) 中文名
from emp e;

–多行函数【聚合函数】：作用与多行，返回一个值。
select count(1) from emp; --查询总数量
select sum(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;

–分组查询
–查询出每个部门的平均工资
select e.deptno, avg(e.sal)
from emp e
group by e.deptno;

–查询平均工资高于2000的部门信息
select e.deptno, avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal)>2000;

–注意，where必须在group by之前，having是在group by之后
–查询出每个部门工资高于800的员工的平均工资
–然后再查询出平均工资高于2000的部门
select e.deptno, avg(e.sal) asal
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;

–等值连接
select *
from emp e, dept d
where e.deptno=d.deptno;
–内连接
select *
from emp e inner join dept d
on e.deptno=d.deptno;
–oracle中专用外连接
–此方法先当于右连接
select *
from emp e,dept d
where e.deptno(+)=d.deptno;
–查询出员工姓名，员工部门名称，员工领导名称，员工领导部门名称
select e1.ename,d1.dname,e2.ename,d2.dname
from emp e1, emp e2, dept d1, dept d2
where e1.mgr=e2.empno
and e1.deptno=d1.deptno
and e2.deptno=d2.deptno;
–子查询
–子查询返回一个值
–查询出工资和SCOTT一样的员工信息
select * from emp where sal in
(select sal from emp where ename=‘SCOTT’)
–子查询返回一个集合
–查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in
(select sal from emp where deptno=10);
–子查询返回一张表
–查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
–1.先查询出每个部门最低工资
select deptno, min(sal) msal
from emp
group by deptno;
–2,三表联查，得到最终结果
select t.deptno,t.msal,e.ename,d.dname
from (select deptno, min(sal)msal
from emp
group by deptno)t,emp e,dept d
where t.deptno=e.deptno
and t.msal=e.sal
and e.deptno=d.deptno;