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
										  )
					 );

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值