Oracle

Oracle基础

1，简单查询

1.1 查询所有信息

select   *   from  table_name;
查询	 所有列 来自	 表名

1.2 展示指定列

select employee_id,first_name from employees;
查询		列名1	，列名2	   来自	employees表;

1.3列支持数据运算（±*/）

select first_name,salary+1000 from employees;
查询		列名1，列名2+1000	来自 employees表;

1.4列别名 as

		原列名 as 新列名
select 	first_name as name,	salary 	as 	sal from employees;
查询	 first_name表示为name,salary表示为sal

1.5多列合并

--将first_name和last_name合并展示
select first_name||last_name from employees;

select employee_id,first_name||last_name as name from employees;

1.6 结果去重

select distinct manager_id from employees;
查询	 去重		manager_id 来自  employees表;

2，条件查询 关键字where

2.1比较查询 >,<,>=,<=,=

select	*	from employees where salary>10000;
查询  所有	 来自 employees  条件  salary>10000;

2.2 逻辑运算 and,or,not

--查询工资为11000和24000的员工所有信息
select * from employees where salary=11000 or salary=24000;

--查询last_name为King并且工资为24000的所有信息（sql语言不区分大小写，但是数据区分）
select * from employees where last_name='King' and salary=24000;

--查询工资不是11000的员工
select * from employees where salary!=11000;
select * from employees where not salary=11000;
--不建议使用 !=

2.3 特殊谓词

2.3.1 in / not in

--查询工资为11000和24000的员工
select * from employees where salary in (11000,24000);
--查询工资不是11000和24000的员工
select * from employees where salary not in (11000,24000);

2.3.2 between…and… 相当于>=和<= 闭区间

--查询工资在11000和24000之间所有的员工（闭区间）
select * from employees where salary>=11000 and salary<=24000;
select * from employees where salary between 11000 and 24000;

2.3.3 like 模糊查询

--'%' 表示不定长度通配，'_'表示一个字符的通配
--查询first_name包含L的员工
select * from employees where first_name like '%L%';
--查询last_name的长度大于4的员工
select * from emloyees where last_name like '____%'
--上面的语句中'____'是4个'_'加上'%'所以会找到所以大于4的last_name

2.3.4 is null / is not null

--查询commission_pct为空值的所有信息
select * from employees where commission_pct is null;
--查询manager_id不为空值的所有信息
select * from employees where manager_id is not null;

2.3.5 条件控制

--将工资大于20000的分级为A,15000以上的分级为B，其余为C
select employee_id,first_name,salary,
  case
    when salary>=20000 then 'A'
    when salary>=15000 then 'B'
    else 'C'
  end as lvl
from employees;

3，排序 order by

--用于对结果排序
-- asc升序，desc降序
--将所有员工按照salary降序排列
select * from employees order by salary desc;
--asc可以省略不写

--支持多列排序（时间由过去到现在）
--将工资大于11000的降序排列，相同的按照日期排列
select * from employees where salary>=11000 order by salary desc,hire_date;

4，函数

4.1 内置函数

--1，时间函数sysdate,sysdatestamp
-- dual是内置的一张虚表
--查询当前系统时间，自己的电脑
select sysdate from dual;		2018/12/6 19:12:43
--查询格式化的系统时间
select sysdatestamp from dual;	06-12月-18 07.13.38.448000 下午 +08:00

--2，to_char(时间，'格式')
--java		yyyy-MM-dd HH:mm:ss
--oracle	yyyy-mm-dd hh:mi:ss
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

--3，to_date('字符串'，'格式')
select to_date('2000-01-01 01:01:01','yyyy-mm-dd hh:mi:ss') from dual;

4.2 组函数

min(x)		x列的最小值
max(x)		x列的最大值
avg(x)		x列的平均值
sum(x)		x列的求和
nvl(x,0)	x列的值如果为 null 则用 0 替换，也可以替换为其他数字。
count(x)	根据x列，统计条数/计数   不会去统计null值 
select count(commission_pct) from employees;  35条 
select count(*) from employees;

5，分组 group by

--查询每个部门的员工个数，(按照部门分组)
select count(*) from employees group by department_id;

--查询每个部门的平均工资
select avg(*) from employees group by department_id;

--查询每个部门中的每个岗位的最大工资数（先按部门分组再按岗位分组）
select max(salary),department_id,job_id from employees group by department_id,job_id;

--查询1997年每个月员工入职的总数
select count(*) from employees 
where to_char(hire_date,'yyyy')='1997' 
group by to_char(hire_date,'mm');

如果应用 group by 分组表达式 
那么 select 关键字之后所能书写的列内容为：

1. 写在 group by 之后的列名一定可以书写在 select 关键字之后。
2. 列名书写在组函数中，可以书写在 select 关键字之后。
3. 普通的内置函数如果应用在 group by 表达式之后 也可以书写在 select 关键字之后。

使用 group by 进行分组 有可能会出现多值问题？
---  在一行一列的表格中 有可能会出现多个数据值均满足条件判断

6，条件筛选 having

-- 查询平均工资 大于 5000 的部门
select department_id,avg(salary) from employees
group by department_id
having avg(salary)>5000;

--如果你的条件判断中涉及组函数的应用，则一般使用having进行条件的判断

--查询 1997 年每个月入职的员工人数>=3个人的月份并按照人数进行降序排序
select to_char(hire_date,'mm'),count(*) from employees 
where to_char(hire_date,'yyyy')=1997
group by to_char(hire_date,'mm')
having count(*)>=3
order by count(*) desc;

-- 关键字顺序
select .. from .. where .. group by .. having .. order by .. asc/desc;

Oracle 练习题：

--1.查询员工表所有数据
select * from employees;

--2.打印公司里所有的manager_id
select manager_id from employees;

--3.查询所员工的email全名,公司email 统一以 "@zpark.cn" 结尾
select email||'@zpark.cn' from employees;

--4.按照入职日期由新到旧排列员工信息
select * from employees order by hire_date desc;

--5.查询80号部门的所有员工
select * from employees where department_id = 80;

--6.查询50号部门每人增长1000元工资之后的人员姓名及工资.
select first_name,salary+1000 from employees;

--7.查询80号部门工资大于7000的员工的全名与工资.
select first_name||last_name,salary from employees where department_id=80 and salary>7000;

--8.查询80号部门工资大于8000并且佣金高于0.3的员工姓名,工资以及提成
select first_name,commission_pct from employees 
where department_id=80 and commission_pct>0.3;

--9.查询职位(job_id)为'AD_PRES'的员工的工资
select salary from employees where job_id='AD_PRES';

--10.查询佣金(commission_pct)为0或为NULL的员工信息
select * from employees where commission_pct=0 or commission_pct is null;

--查询入职日期在1997-5-1到1997-12-31之间的所有员工信息
select * from employees where to_char(hire_date,'yyyy')='1997'
 and to_char(hire_date,'mm') between 5 and 12;

--12.显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
select * from employees where first_name not like '%L%' or first_name like '%SM%';

--13.查询电话号码以5开头的所有员工信息.
select * from employees where phone_number like '5%';

--14.查询80号部门中last_name以n结尾的所有员工信息
select * from employees where department_id=80 and last_name like '%n';

--15.查询所有last_name 由四个以上字母组成的员工信息
select * from employees where last_name like'____%';

--单行函数练习
--1.把hiredate列看做是员工的生日,查询本月过生日的员工(考察知识点:单行函数)
select * from employees where to_char(hire_date,'mm')=to_char(sysdate,'mm');

--2.请用三种以上的方式查询1997年入职的员工(考察知识点:单行函数)
select * from employees where to_char(hire_date,'yyyy')='1997';

--3.查询2002年下半年入职的员工(考察知识点:单行函数)
select * from employees where to_char(hire_date,'yyyy')='1997' and to_char(hire_date,'mm')>6;

--4.打印自己出生了多少天
select sysdate-to_date('1994,12,06','yyyy,mm,dd') from dual; 

--5.打印入职时间超过10年的员工信息
select * from employees where to_char(sysdate,'yyyy') - to_char(hire_date,'yyyy')>=20;


--组函数练习
--1.显示各种职位的最低工资(组函数) 
select min(salary) from employees group by job_id;

--2.求1997年各个月入职的的员工个数(考察知识点:组函数)
select to_char(hire_date,'mm'),count(*) from employees 
where to_char(hire_date,'yyyy')='1997' group by to_char(hire_date,'mm');

--3.查询每个部门,每种职位的最高工资(考察知识点:分组)
select department_id,max(salary) from employees group by department_id;

--4.查询各部门的总工资
select department_id,sum(salary) from employees group by department_id;

--5.查询50号部门,60号部门,70号部门的平均工资
select department_id,avg(salary) from employees 
 group by department_id having department_id between 50 and 70;
 
--6.查询各部门的最高工资,最低工资.
select department_id,max(salary),min(salary) from employees group by department_id;

--7.查询各岗位的员工总数. 
select job_id,count(*) from employees group by job_id;

--8.查询各部门中各个岗位的平均工资.
select avg(salary) from employees group by department_id;

--9.查询平均工资高于8000元的部门的最高工资.
select department_id,max(salary),avg(salary) from employees 
 group by department_id having avg(salary)>8000;