end as new_salary from emp_tian;
----------------------分别提薪---------------------方法三 select ename,job,salary,
decode(job,'clerk',nvl(salary,0)*1.05,'programer',nvl(salary,0)*1.1,'analyst', nvl(salary,0)*1.15,nvl(salary,0)) as newsarary from emp_tian;
三.分组函数计算 -------------- 处理多个数据，得到单个结果 count(*) 查记录
select count(*) from emp_tian; select count(*) from user_tables; select count(*) from user_objects;
----------------查询员工中赚钱最多/少的工资------------------------- select max(salary) from emp_tian; select min(salary) from emp_tian;
----------------计算雅思得分总分最大/小的得分----------------------- select max((scr1+scr2+scr3+scr4)) as totalmax from ielts_tian; select min((scr1+scr2+scr3+scr4)) as totalmin from ielts_tian; ----------------计算入职时间最早/最晚的时间------------------------- select min(hiredate) from emp_tian;
----------------求和每月人力总支出--------------------------------- select sum(nvl(salary,0)) from emp_tian;
----------------求平均值每月人力平均支出--------------------------------- select avg(nvl(salary,0)) from emp_tian; select sum(salary)/count(*) from emp_tian; -------------------忽略空值查询
select sum(salary)/count(salary) from emp_tian; select avg(salary) from emp_tian;
--------------------组函数(从一组数据中查找某个元素)--------------------------- ---------------------------sum/agv/min/max--------------------------------------
四.子查询---------------------在一个主查询语句中包好另一个主查询语句 ********************************************************************************
子查询一次性给出语句，减少I/O次数，提高访问效率 ******************************************************************************** ---------------查询员工中赚钱最多/少的工资的员工---------------------------
select ename,salary from emp_tian where salary = (select max(salary) from emp_tian); select ename,salary from emp_tian where salary = (select min(salary) from emp_tian);
----------查询每个部门的人数(分组计算)按什么分组，下面group by 要与之对应------- ---------------------group by 按照什么什么分组-------------------------------
select deptno,count(*) from emp_tian where deptno is not null group by deptno order by deptno;
---------------查询每个部门的人数---得到最多的人数-----------------------
select max(count(*)) as maxNum from emp_tian where deptno is not null
group by deptno ;
--------------查询每个部门的人数---得到最多的人数的部门编号-------------------- select deptno,count(*) from emp_tian where deptno is not null group by deptno having count(*) = 4 order by deptno;
update emp_tian set deptno = 20 where deptno is null;
select deptno,count(*) from emp_tian where deptno is not null group by deptno having count(*) = (
select max(count(*)) as maxNum from emp_tian where deptno is not null group by deptno ) order by deptno;
-----------查询部门最多的人数的部门编号对应的地理位置和部门民称---------------- 分步骤理解
select*from dept_tian;
select dname,loaction from dept_tian
where deptno = (select deptno from emp_tian group by deptno
having count(*) = (select max(count(*)) as maxNum from emp_tian group by deptno) );
------------哪些部门(部门编号)的总工资共和比整部门20的总工资高--------------- 首先：算出每个部门的工资共和
select deptno,sum(salary) from emp_tian group by deptno; 然后：得到部门20 的总工资
select deptno,sum(salary) from emp_tian where deptno = 20 group by deptno; select sum(salary) from emp_tian where deptno = 20 group by deptno;
--不显示部门编号 最后:组合
select deptno,sum(salary) from emp_tian group by deptno
having sum(salary) > (select sum(salary) from emp_tian where deptno = 20 group by deptno);
----------------------------哪些部门的员工人数大于5---------------------- 首先：查出每个部门的员工人数
select deptno,count(deptno) from emp_tian group by deptno; 最后:组合
select deptno,count(deptno) from emp_tian group by deptno having count(*)>5;
-------------------------------谁的工资比刘苍松高------------------------- 首先：列出类个人的工资
select ename,salary from emp_tian where salary is not null;
然后：得到刘苍松的工资
select ename,salary from emp_tian where salary is not null and ename ='刘苍松'; 最后组合：
select ename from emp_tian where salary is not null and salary > (select salary from emp_tian where salary is not null and ename ='');
如果有同名的人，就要注意，有两种解决办法 1.加限定条件
2.将>改为 all或者any ----all所有 any任意一个
------------------------谁和老疯子同部门------------------------------------- 首先：列出所有人以及其所属部门
select empno,ename,deptno from emp_tian;
----------------查询每个部门赚最多钱的人--------------------------- 首先：得到每个部门的最多的薪水
select deptno,max(salary) from emp_tian group by deptno; 最后：查找每个部门对应的最高工资的人名 select ename,deptno,salary from emp_tian
--where (A) in (B) ; 语法,将A中的信息和B中的信息比较
where (deptno,salary) in(select deptno,max(salary) from emp_tian group by deptno); ---------------------------更新信息------------------------------- update emp_tian
set ename = '张三丰' where ename = 'zhangsanfeng'; update emp_tian
set ename = '陆无双' where ename = 'luwushuang'; update emp_tian
set ename = '张无忌' where ename = 'zhangwuji'; update emp_tian
set ename = '刘祤' where ename = 'liyu'; update emp_tian
set ename = '郭芙蓉' where ename = 'guoferong'; update emp_tian
set ename = '叶瞳' where ename = 'yanxiaoliu'; update emp_tian
set ename = '姬紫月' where ename = 'huangrong'; update emp_tian
set ename = '姬皓月' where ename = 'guojing'; update emp_tian
set ename = '摇光' where ename = 'weixiaobao'; update emp_tian
set ename = '老疯子' where ename = 'wanxiaofei'; update emp_tian
set ename = '人魔老头' where ename = 'wanxiaofei'; update emp_tian
set ename = '庞博' where ename = 'jerry'; update emp_tian
set ename = '叶凡' where ename = 'jizyue'; update emp_tian
set ename = '刘苍松' where ename = 'liucangsong'; commit;
select*from emp_tian; /
drop --------------------删除表
select table_name from user_tables where table_name like '%_TIAN%';
select table_name from user_tables where table_name like 'EMP%';
drop TEMP_TIAN;
----------------------------------------------------------------------
脚本文件(建表,插表,提交等代码存放的文件) xxx.sql 扩展名约定为.sql -------------------作用：初始化数据库--------------------------------- ------------每个班分数加起来是否等于100-------------------------- select class_id,sum(scale) from t_assess_rule_tian group by class_id;
------------每个学生的总分？按总分高低排序------------------------- select student_id ,sum(test_score) from t_performance_tian group by student_id order by 2; ###################### 一.子##############################
-----------------------------谁的总成绩比一号学生成绩高------------------- 首先：得到每个同学的总成绩
select student_id,sum(task_score) from t_performance_tian group by student_id;
然后：得到一号同学的总成绩
select sum(task_score) from t_performance_tian where student_id = 1; 最后：组合
select student_id,sum(task_score) from t_performance_tian group by student_id
having sum(task_score)>(
select sum(task_score) from t_performance_tian where student_id = 1) order by sum(task_score) desc;
-----------------------------谁的薪水比公司的平均薪水低------------------- select deptno,ename,nvl(salary,0) salary from emp_tian where nvl(salary,0) < (
查询
select avg(nvl(salary,0)) from emp_tian);
-----------------------------谁的薪水比部门的平均薪水低--------------------
select deptno,ename,nvl(salary,0) salary from emp_tian x ----- x为别名 where nvl(salary,0)<(
select avg(nvl(salary,0)) from emp_tian
where deptno = x.deptno ); ---------x.deptno关联子查询 ----------------------------每个部门的平均薪水--------------------------------
select deptno,avg(nvl(salary,0)) avg_sal from emp_tian group by deptno;
delete emp_tian where deptno is null;
------------------------谁的薪水比同一个经理的人的平均薪水低------------------- select manager,ename,nvl(salary,0) salary from emp_tian x where nvl(salary,0)<(
select avg(nvl(salary,0)) from emp_tian where manager = x.manager );
----------------------每个部门同经理的人的平均薪水-------------------------
select manager,avg(nvl(salary,0)) avg_sal from emp_tian group by manager; -------------------------哪些员工是别人的经理------------------------------ select empno,ename from emp_tian x
where exists (select empno from emp_tian where manager = x.empno); select ename,manager from emp_tian;
--------------------------哪些员工不是别人的经理--------------------------- select empno,ename from emp_tian x
where not exists (select empno from emp_tian where manager = x.empno);
---------------------------哪些部门没有员工------------------------------- select deptno,dname,loaction from dept_tian x where not exists (select 1 from emp_tian where deptno = x.deptno);
--------------------------------------------------------------------------
select deptno from dept_tian --- -------- minus --- 集合操作 差集 -------- select distinct deptno from emp_tian; --- -------- --------------------------------------------------------------------------
select ename,salary,deptno from emp_tian --- -------- where salary < 9000 --- -------- union --去掉重复元素 /union all 不去重 --- 集合操作 合集-------- select ename,salary,deptno from emp_tian --- -------- where salary >= 8000; --- -------- ----------------------------------------------------------------------------
select ename,salary,deptno from emp_tian --- -------- where salary < 9000 --- -------- intersect --- 集合操作 交集--------