Oracle学习总结_day03_day04_条件查询_排序_函数_子查询

本文为博主辛苦总结,希望自己以后返回来看的时候理解更深刻,也希望可以起到帮助初学者的作用.

**转载请注明 出自 : luogg的博客园 ** 谢谢配合!

day03_条件查询_排序_函数

• 清空回收站: PURGE recyclebin;
• 给表名,字段加别名 : 表名 + 别名 ; 字段 [as] 别名 ;
• 去重复 : select distinct first_name from employees;
  
  删除重复数据 :
  
  方法1,先查找不重复的,再复制一份查询后不重复的
  
  方法2,用rowID方法

条件查询与排序

where 语法 中的比较运算符

= , > , < , >= , <= , <>或!= , between and(中间值) , in(匹配多个值中任意一个,代替了OR) , like(模糊查询%_) , is null

逻辑运算符

not,and, or,

优先级: not > and > or

排序 order by(asc,desc)

• 默认是asc升序排列.
  
  select * from copy_emp order by employee_id;--按员工id从小到大序
• 降序排列,desc
  
  select * from copy_emp order by employee_id desc;
• 降序时,null值会放到第一位, 需要将它方法最后,或者用0代替
  
  select * from copy_emp order by employee_id desc nulls last;
  
  select nvl(department_id,0) from copy_emp order by employee_id;
• 空值为最小值.
• 字符串排列原则 : A -> Z ,之后 a -> z;

函数

单行函数

字符函数

大小写转换函数

1. LOWER.将大写或小写混合的字符转换成小写
2. UPPER.将大写或小写混合的字符转换成大写
3. INITCAP.将每个单词的第一个字母转换成大写，其余字母都是转换成小写
   
   Eg:select initcap(te_name) from weixin_te_msg;

字符处理函数

1. CONCAT.将字符串连接在一起
   
   select concat('哈哈','呵呵') from dual; - -哈哈呵呵
2. SUBSTR.提取字符串的某一特定部分
   
   select substr('wqedsadsa',1,3) from dual; - -wqe
3. LENGTH.返回字符串的长度
   
   select length('哈哈') from dual; - -2
4. LPAD.右对齐,填充在前边
5. RPAD.左对齐,填充在后边
   
   select rpad ('150',10,'') from dual; - -150******
6. TRIM.去除字符串头部和尾部的字符
   
   select trim('S' from first_name) from copy_emp;

数值函数

• round.(数值,从第几位开始取)
  
  将列或表达式所表示的数值四舍五入到小数点后的第n位
• trunc.(数字,从第几位开始)
  
  将列或表达式所表示的数值截取到小数点后的第n位

日期函数

• ADD_MONTHS(日期,数字): 在日期上加上一定的月份
• LAST_DAY(日期): 该日期的最后一天
• MONTHS_BETWEEN(日期1，日期2) : 两个月之间的天数
• NEXT_DAY(d,char) : 返回day日期之后的日期, char为数字或字符串
• 日期-日期, 为天数
• 日期+/- 数字 , 为日期

数值格式

滤空函数(如果字符串为空则替换)

nvl(expr1,expr2);

可以使用nvl转换任何类型的数据, 但是表达式1和2的数据类型必须一致

select nvl(salary,0) from copy_emp; - -查询salary,为空用0替代

decode函数

给部门 50 的员工涨工资 200 元, 给部门 80 的涨工资 300 元, 其余的不涨 .

select,department_id,salary,decode(department_id,50,salary+200,80,salary+300,salary) as "涨后工资" from copy_emp;

case when 函数

工资0-3000之间,第三等级,3000-5000 第二等级, 5000以上 , 第一等级.

select salary,(case when salary<=3000 then '第三等级' when salary>3000 and salary<=5000 then '第二等级' when salary>5000 then '第一等级' when salary is null then '无' end) as "工资等级" from copy_emp;

decode函数和case when 函数对比 :

1. DECODE Oracle 特有;
2. CASE WHEN Oracle , SQL Server, MySQL 都可用;
3. DECODE 只能用做相等判断
   
   CASE 可用于=,>=,<,<=,<>,is null,is not null 等的判断;

多行函数

组函数

**按雇员所在部门统计各部门内雇员的平均工资: **

SELECT department_id,AVG(salary) FROM copy_emp GROUP BY department_id;

按所在部门及从事的工作分组统计平均工资:

SELECT department_id,job_id,avg(salary) FROM copy_emp GROUP BY department_id,job_id;

day04_子查询

1.单列子查询

2.多列子查询

--查询显示和 Donald 同部门且同工作岗位的员工姓名、工作岗位、工资、部门编号
select first_name||'-'||last_name as "姓名",job_id,salary,department_id
from copy_emp where (department_id,job_id)=(
select department_id,job_id from copy_emp where
first_name='Donald');
--查询大于所在部门平均工资的雇员的姓名、工资、部门编号以及所在部门的平均工资
select first_name||'-'||last_name as "姓名",salary,emp.department_id
from copy_emp emp,(select department_id,avg(salary) avgsal
from copy_emp group by department_id) d
where emp.department_id=d.department_id
and salary>avgsal;


3.单行子查询 : 子查询只返回一行.

--任务一: 查询出比编号151雇员工资高的其他雇员
select * from copy_emp where salary>(select salary from copy_emp where employee_id=111);
--任务二: 显示和雇员151从事相同工作并且工资大于雇员123的雇员的姓名和工作
select first_name||','||last_name as "姓名", job_id from copy_emp
where job_id=(select job_id from copy_emp where employee_id = 111)
AND salary>(select salary from copy_emp where employee_id = 112);
--任务三: 显示工资最低的雇员的姓名、工作、和工资
select first_name||'-'||last_name as "姓名" , job_id,salary from copy_emp
where salary = (select min(salary) from copy_emp );
--任务四: 显示部门内最低工资比100 部门最低工资要高的部门编号以及部门内最低工资
select department_id,min(salary) from copy_emp group by department_id having min(salary)>(
select min(salary) from copy_emp where department_id = 100);


4.多行子查询

• IN：等于列表中的任意一个

• ANY：
  
  小于某集合中的任意一个值，就是小于集合中的最大值。
  
  大于某集合中的任意一个值，就是大于最小值。

• ALL：
  
  小于某集合中的所有值，就是小于最小值。
  
  大于某集合中的所有值，就是大于最大值。

--查询工资与任何一个部门最低工资相等的雇员姓名,工资
select first_name||'-'||last_name as "姓名" ,salary from copy_emp where salary in(
select min(salary) from copy_emp group by department_id);
--显示只要比某个IT工资低但又不是IT 工作的雇员姓名，职位id,工资
select first_name||'-'||last_name as "姓名" ,job_id,salary from copy_emp
where salary<any(select salary from copy_emp where job_id = 'IT_PROG')
and job_id <>'IT_PROG';
--显示工资大于所有部门平均工资的雇员雇员姓名,工资
select first_name||'-'||last_name as "姓名",salary from copy_emp
where salary>any(select avg(salary) from copy_emp);


---

课外补充 :

--工资表中前三的员工
select rownum,first_name ,last_name, salary from (
select first_name,last_name,salary from copy_emp order by salary desc nulls last
) where rownum<=3;rownum是一个伪列, 表示记录在结果集中的行号,
1,rownum一旦生成, 就不会变化,
2,对于rownum,只能使用<和<=, 不能使用>和>=.查询我的经理的工资:
select empno,ename,sal my_sal,mgr,(select sal from emp where
empno=e.mgr) as mgr_sal from emp e;