目 录
什么是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
)
);