Oracle数据库学习笔记1

什么是Oracle实例

位于物理内存里的数据结构，由操作系统的多个后台进程共享的内存池所组成，共享的内存池可以被所有进程访问。

用户如果要存取数据库中的数据，必须通过Oracle实例才能实现。
Oracle实例就是数据库服务（service）

区别：
实例可以操作数据库
任何时刻一个实例只能与一个数据库关联
同一个数据库可由多个实例访问（RAC）

关于安装Oracle服务端和客户端所存在的问题（个人问题）

1.如果在测试的时候报错无法连接：ORA-12560的错误，可以重新设定监听器和服务器
（打开Net Manager，设置监听器、服务器；再打开Net Configuration Assistant删除
原来的监听器和服务器，然后新建即可）；
如果重设后还是不行，可以尝试将Path环境变量中的服务器和客户端的路径位置调换
（这两种问题本人有遇到过，通过此方法已解决）

2.如果在登陆的时候存在用户被锁定或者用户无法登录的状况

第一步可以在cmd中输入：sqlplus /nolog
第二步：conn / as sysdba
第三步（解锁）：alter user 用户名 account unlock
第四步（设定新密码）：alter user 用户名 identified by 新密码

3.Oracle在服务器启动中比较消耗内存，而且Listener和Service两个服务是必须是开启状态，所以在使用Oracle数据库的时候建议设定Listener和Service手动启动。

用户

用户可以拥有一定的权限来对当前数据库进行操作，分为系统权限和对象权限。

系统权限:：允许用户执行特定的数据库动作，如创建表、创建索引、连接实例等。

对象权限:：允许用户操纵一些特定的对象，如读取视图，可更新某些列、执行存储过程等。

角色：超管可以将多个权限封装到一块，这个封装的结果称为角色。

一些基础语法练习

表结构：

五个表：
employees
departments
jobs
job_grades
locations

以下练习都是基于以上五个表

对Oracle的一些基本语法总结

1.查看表结构：desc 表名
2.连接符：||
例1：用逗号连接这三列，逗号用单引号

select employee_id ||','|| last_name ||','||first_name from employees;

3.别名：as XXX或者"XXX"

select employee_id "NEW NAME" from employees;

4.where后面如果是字符或者日期，需要用单引号；单引号中区分大小写

5.转义字符的使用：'\'
例1：查询名字中带有下划线的人

select name from employee where name like '%\_%' escape '\';

函数

字母转换函数

`lower()`：大写转小写
`upper()`：小写转大写
`initcap()`：每个单词首字母大写

字符控制函数

字符串合并：concat('hello','world')—helloworld
字符串拆分：substr('helloworld',1,5)—hello
字符串长度：length('helloworld')—10
字符定位：instr('helloworld','w')—6
不足以星号补齐（10位，星号，补左）：Lpad：(name,10,'*')—name
不足以星号补齐（10位，星号，补右）：Rpad：(name,10,'*')—name
去掉首尾字符：trim('h' from 'hhelloworldh')—elloworld
字符替换：replace('abcd','b','x')—axcd

数字函数

四舍五入（保留两位小数）：`round(45.926,2)`---45.93
截断（保留两位小数）：`trunc(45.926,2)`---45.92
求余：`mod(1600,300)`---100

转换函数（重点）

隐式转换：Oracle会自动转换数据类型
例1：

select '12' + 2 from dual;---14

显式转换：to_date()、to_number()、to_char()
例1：

select hire_date from employees where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07';

例2：

select hire_date from employees where to_date('1994-06-07','yyyy-mm-dd') = hire_date;

例3：

select to_char(1234567.89,'999,999,999.99') from dual;---1,234,456,89

例4：

select to_number('$001,234,567.89','$000,000,999,99')+1;---123456790

5.通用函数
nvl(expr1,expr2)：expr1如果为空就用expr2代替
例1：查询员工年薪（含奖金commission_pct）

select name,salary*12*(1+nvl(commission_pct,0)) from emp;

例2：输出last_name,department_id,当department_id为null时，显示‘没有部门’

select last_name,nvl(to_char(department_id,'9999'),'没有部门') 
from employees;

nvl2(expr1,expr2,expr3)：expr1不为空，返回expr2；expr1为空，返回expr3
例1：查询员工的奖金率，为空，返回0.01；不为空，返回实际奖金率+0.015

select last_name,commission_pct,nvl2(commission_pct,commission_pct+0.015,0.01) 
from employees;

nullif(expr1,expr2)：相等返回null，不等返回expr1

case表达式（if-then-else逻辑）（重点）
例：

select department_id,
		case department_id
		when 50 then '一号'
		when 80 then '二号'
		when 100 then '三号'
		else '其他'
		end
from employees;

或者使用：decode函数

decode(department,50.'一号',80,'二号',100,'三号')

多表联查

大部分在MySQL笔记中有，这里做补充

左外连接：
在右表中不存在的数据以NULL表示出来
例：

select e.last_name,e.department_id,d.department_name
from employees e,departments d
where e.department_id = d.department_id(+);

右外连接：
在左表中不存在的数据以NULL表示出来
例：

select e.last_name,e.department_id,d.department_name
from employees e,departments d
where e.department_id(+) = d.department_id;

或者使用JOIN ON语法（常用）
left左外连接、right右外连接、full满外连接
例：

select e.last_name,e.department_id,d.department_name
from employees e
left join departments d
on e.department_id = d.department_id;

分组函数

大部分在MySQL笔记中有，这里做补充

关于分组函数：
凡是在select后面的字段，不是组函数的，必须包含在group by后面

练习题1：
例1：查询employees表中有多少部门

select count(distinct department_id) 
from employees;

例2：查询公司奖金基数的平均值（没有奖金的按0计算）

select avg(nvl(commission_pct,0))
from employees;

例3：求出部门中的子部门的平均工资

select department_id,job_id,avg(salary) as avgsal 
from employees 
group by department_id,job_id
order by avgsal desc;

例4：求出各部门工资大于6000的部门，以及其平均工资

select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>6000;

例5：求出各部门的平均工资的最大值是哪个部门

select max(avg(salary))
from employees
group by department_id;

例6：查询Seattle这个城市的员工的平均工资

select avg(salary) 
from employees e,departments d,locations l
where e.department_id=d.department_id 
and d.location_id = l.location_id
and l.city = 'Seattle';

例7：各个城市的平均工资（有员工的城市）

select city,avg(salary)
from employees e
join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
group by city;

例8：查询平均工资高于6000的job_titile有哪些

select job_title,avg(salary)
from employees e
join jobs j
on e.job_id=j.job_id
group by job_title
having avg(salary)>6000;

练习题2：

1.组函数处理多行返回一行吗？
是

2.组函数不计算空值吗
是

3.where子句是否可使用组函数进行过滤？
不可以，用having替代

4.查询公司员工工资的最大值，最小值，平均值，总和

select max(salary),min(salary),avg(salary),sum(salary)
from employee;

5.查询各job_id的员工工资的最大值，最小值，平均值，总和

select job_id,max(salary),min(salary),avg(salary),sum(salary)
from employees
group by job_id;

6.选择具有各个job_id的员工人数

select job_id,count(*)
from employees
group by job_id;

7.查询员工最高工资和最低工资的差距

select min(salary),max(salary),max(salary)-min(salary)
from employees

8.查询各个管理者手下的最低工资，其中最低工资不低于6000，没有管理者的员工不计算在内

select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) >= 6000;

9.查询所有部门的名字，location_id，员工数量和工资平均值

select department_name,location_id,count(employee_id),avg(salary)
from employees e
right join departments d
on e.department_id = d.department_id
group by department_name,location_id;

10.查询公司在1995-1998年之间，每年雇用的人数
格式：
total 1995 1996 1997 1998
20 3 4 6 7

方法1：（case when then）

select count(*) "total",
count(
	   case to_char(hire_date,'yyyy')
	   when '1995' then 1
		 else null
		   end
) "1995",
count(
	   case to_char(hire_date,'yyyy')
	   when '1996' then 1
		 else null
		   end
) "1996",
count(
	   case to_char(hire_date,'yyyy')
	   when '1997' then 1
		 else null
		   end
) "1997",
count(
	   case to_char(hire_date,'yyyy')
	   when '1998' then 1
		 else null
		   end
) "1998"
from employees
where to_char(hire_date,'yyyy')in('1995','1996','1997','1998');

方法2：（decode函数）

select count(*) "total",
count(decode(to_char(hire_date,'yyyy'),1995,1,null)) "1995",
count(decode(to_char(hire_date,'yyyy'),1996,1,null)) "1996",
count(decode(to_char(hire_date,'yyyy'),1997,1,null)) "1997",
count(decode(to_char(hire_date,'yyyy'),1998,1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in('1995','1996','1997','1998');

子查询

大部分在MySQL笔记中有，这里做补充

单行子查询
例1：返回job_id与141号员工相同，salary比143号员工工资高的员工姓名，job_id，工资

select last_name,job_id,salary
from employees
where salary>(select salary from employees where employee_id=143)
and job_id=(select job_id from employees where employee_id=141);

例2：返回公司工资最少的员工的last_name,job_id和salary

select last_name,job_id,salary
from employees
where salary=(select min(salary) from employees);

例3：查询最低工资大于50号部门最低工资部门id和其最低工资

select department_id,min(salary)
from employees
group by department_id
having min(salary)>(select min(salary) from employees where department_id=50);

多行子查询
例1：（any）返回其他部门中比job_id为’IT_PROG’部门任一工资低的员工号、姓名、job_id、salary

select employee_id,last_name,job_id,salary
from employees
where job_id <='IT_PROG'
and salary < any(select salary from employees where job_id='IT_PROG');

例2：（all）返回其他部门中比job_id为’IT_PROG’部门任意工资低的员工号、姓名、job_id、salary

select employee_id,last_name,job_id,salary
from employees
where job_id <='IT_PROG'
and salary < all(select salary from employees where job_id='IT_PROG');

练习题：

1.查询工资最低的员工信息：last_name,salary

select last_name,salary
from employees
where salary=(select min(salary) from employees);

2.查询平均工资最低的部门信息

select *
from departments
where department_id=(
					  select department_id
					  from employees
					  group by department_id
					  having avg(salary)=(
									select min(avg(salary))
									from employees
									group by department_id
					  )
);

3.查询平均工资最低的部门信息和该部门的平均工资（难点）

select d.department_id,d.department_name,d.manager_id,d.location_id,avg(salary)
from departments d
join employees e
on d.department_id=(
					  select department_id 
					  from employees
					  group by department_id
					  having avg(salary)=(
									select min(avg(salary))
									from employees
									group by department_id
											)
					)
group by d.department_id,d.department_name,d.manager_id,d.location_id;

4.查询平均工资最高的job信息

select * 
from jobs
where job_id=(
			  select job_id
			  from employees
			  group by job_id
			  having avg(salary)=(
								select max(avg(salary))
								from employees
								group by job_id
									)
			  );

5.查询平均工资高于公司平均工资的部门有哪些

select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>(
					select avg(salary)
					from employees
					);

6.查询各个部门中最高工资中 最低的那个部门的 最低工资是多少

select min(salary)
from employees
where department_id=(
					  select department_id
					  from employees
					  group by department_id
					  having max(salary)=(
										  select min(max(salary))
										  from employees
										  group by department_id
										  )
					 );