Oracle基础之一-＞Oracle 11安装与介绍

一、数据库介绍

1、关系型数据库

(1)Mysql(用的最多)
(2)Oracle(最安全)
(3)Sql server(.net)
(4)Db2(金融、银行)

2、非关系型数据库

(1)Hbase
(2)Redis
(3)mongodb

3、Oracle介绍

1、数据库版本

(1)Oracle 8及8i:表示的Internet，向网络发展，过度版本，只有一张vcd,8i是过度性产品
(2)Oracle9i:是之前使用最广泛的版本，8i的升级版，1CD
(3)Oracle 10g:700M过渡性产品，其中g表示的是网格计算，以平面网格，以中心查找
(4)Oracle 11g:完整性产品，最新版本2G
(5)Oracle 12c:the world’s first database designed for the cloud

2、用户

(1)sys 超级管理员
(2)system 普通管理员
(3)scott 普通的用户

3、示例–>数据库

(1)数据库实例名 统一使用orcl

二、Oracle安装

1、安装文档

链接：https://pan.baidu.com/s/1OXZtVkS4RjC0P8aU_IMsyg
提取码：6666

2、安装完成后会有如下服务

3.1 Oracle服务的作用

安装oracle 11g R2中的方法成功安装Orcle 11g后，共有7个服务，这七个服务的含义分别为：

(1)Oracle ORCL VSS Writer Service:Oracle卷映射拷贝写入服务，VSS(Volume Shadow Copy Service)能够让存储基础设备(比如磁盘，阵列等)创建高保真的时间点映像，即映射拷贝(shadow copy)，它可以在多个卷或者单个卷上创建映射拷贝，同时捕获影响到系统的系统性能。(非必须启动)
(2)OracleDBConsoleorcl:Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager(企业管理器OEM)的时候，需要启动这个服务。（非必须启动）
(3)OracleJobSchedulerORCL:Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）
(4)OracleMTSRecoveryService：服务端控制，该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）
(5)OracleOraDb11g_home1ClrAgent:Oracle数据库.NET扩展服务的一部分。（非必须启动）
(6)OracleOraDb11g_home1TNSListener:监听器服务，服务只有在数据库需要远程访问的时候才需要。(必须启动，下面会有详细解释)
(7)OracleServiceORCL：数据库服务（数据实例），是Oracle核心服务，该服务是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动（必须启动）

3.2开发的时候需要启动哪些服务

对新手来说，要是只有Oracle自导的sql*plus的话，只要启动OracleServiceORCL即可，要是使用PL/SQL Developer等第三方工具的话，OracleOraDb11g_home1TNSListener服务也要开启，OracleDBConsoleorcl是进入基于web的EM必须开启的，其余服务很少用
**注：**ORCL是数据库实例名，默认的数据库是ORCL，你可以创建其他的，即OrcleService+数据库名

3、卸载文档

链接：https://pan.baidu.com/s/1OXZtVkS4RjC0P8aU_IMsyg
提取码：6666

三、账号管理

四、SQL语言

1、结构化查询语言(Structured Query Language)

具有定义，查询，更新和控制等多种功能，是关系数据库的标准语言

2、sql分类

2.1 DML：数据操纵语言 Data Manipulation Language:

SELECT INSERT UPDATE DELETE

2.2 DDL：数据定义语言 Data definition language

CREATE ALTER DROP RENAME TRUNCATE

2.3 DCL：数据控制语言 Data Control Language

GRANT REVOKE

2.4 Transaction:

commit rollback savepoint

3、示例

comment on column emp.hiredate is '入职日期';
comment on column emp.hiredate is '入职日期';
/*
sql语句学习
SELECT [DISTINCT] {*,column alias,...}
FROM table alias
Where 条件表达式
*/
--查询雇员表中部门编号是10的员工
select empno,ename, job,deptno from emp where deptno=10;
--去除重复数据
select distinct deptno from emp;
--去除重复数据也可以针对多个字段，多个字段值只要有一个不匹配就算是不同的记录
select distinct deptno,sal from emp;
--在查询的过程中可以给列添加别名，同时也可以给表添加别名
select e.empno 雇员编号,e.ename 雇员名称,e.job 雇员工作 from emp e where e.deptno=10;
select e.empno as "雇员 编号",e.ename as 雇员名称,e.job as 雇员工作 from emp e where e.deptno=10;
--查询表中的所有字段，可以使用*，但是在项目中不要随便使用
select * from emp;

/*
=, !=, <>, <, > <=, >=, any, some, all
is null, is not null
between x and y
in(list), not in (list)
exists (sub-query)
like _, %, escape `\` _ \% escape `\` 
*/
-- =
select * from emp where deptno=20;
-- !=
select * from emp where deptno != 20;
-- <> 不等于
select * from emp where deptno <> 20;
-- <, > <=, >=
select sal from emp where sal > 1500;
select sal from emp where sal < 1500;
select sal from emp where sal >= 1500;
select sal from emp where sal <= 1500;
--any 只要sal大于any里面的任意一个值都符合条件
select sal from emp where sal > any(1000,1500,3000)
--some 跟any是同一个效果，只要大于其中某一个值都符合条件
select sal from emp where sal > some(1000,1500,3000)
--all 大于所有的值才会成立
select sal from emp where sal > all(1000,1500,3000)

-- is null, is not null 在sql语法中null表示一个特殊含义, null != null
-- 所以要使用is null 或者is not null 来判断
select * from emp where comm is null;
select * from emp where comm is not null;

-- between x and y 包含x 和 y的值
select sal from emp where sal between 1500 and 3000;
select sal from emp where sal >= 1500 and sal <= 3000;

--in(list) 
select * from emp where deptno in(10,20);
select * from emp where deptno =10 or deptno =20;
--not in (list)
select * from emp where deptno not in (10,20);
select * from emp where deptno !=10 and deptno !=20;

-- exists (sub-query) 当exists中的子查询语句能查到结果的时候，意味着条件满足
-- 相当于双重for循环
select *
  from emp e
 where exists (select deptno
          from dept d
         where (deptno = 10 or deptno = 20)
           and e.deptno = d.deptno);
-- like _, %, escape `\` _ \% escape `\`
-- 模糊查询 查询名字以S开头 使用like的时候要慎重，因为like效率比较低
-- 使用like可以参考索引，但是不能以%开头
select * from emp where ename like ('S%');
select * from emp where ename like ('%S%');
-- 模糊查询 查询名字以S开头 且倒数第二个字符为T的用户
select * from emp where ename like ('S%T_');
select * from emp where ename like ('S%T%');
select * from emp where ename like ('JO%');
select * from emp where ename like ('_O%'); --第二个字母为O

--查询名字中带%的用户
--escape 使用转义字符，可以自己规定转义字符
select * from emp where ename like ('%\%%') escape('\');
select * from emp where ename like ('%a%%') escape('a');

--order by
--默认按升序排序 ASC
--desc 降序
-- 排序是按照自然顺序排序的 数值从小到大，
--字符串按照字典序排序
--在进行排序的时候可以指定多个字段，而且多个字段可以使用不同的排序方式
--每次在执行order by的时候相当于是做了全排序，思考全排序的效率，
-- 比较耗费系统资源，因此一般统计的事情放在业务不太繁忙的时候进行
select * from emp order by sal;
select * from emp order by sal desc;
select * from emp order by sal desc,ename asc;

-- 创建计算字段
--字符串连接
select 'my name is '|| ename from emp;
select concat('my name is',ename) from emp;
--计算所有员工的年薪
-- null是比较特殊的存在，null做任何计算都还是为null，因此要将null进行转换
--nvl(arg1,arg2),如果arg1是空，那么返回arg2，如果不是空，则返回原来的值
select ename,(sal+comm) * 12 from emp;
select ename,(sal+nvl(comm,0))*12 from emp;
--并集 将两个集合中的所有数据都进行显示，但是不包含重复数据
select * from emp where deptno != 30;
select * from emp where sal > 1000;

select * from emp where deptno != 30 union
select * from emp where sal > 1000;
--全集 将两个集合中的所有数据都进行显示，包含重复数据
select * from emp where deptno != 30 union all
select * from emp where sal > 1000;
--交集 两个集合中交叉的数据集，只显示一次
select * from emp where deptno != 30 intersect
select * from emp where sal > 1000;
--差集 包含在A集合而不包含在B集合中的数据，跟A B集合的顺序有关
select * from emp where deptno != 30 minus
select * from emp where sal > 1000;

五、Oracle函数

--组函数：又称为聚合函数，输入多个值，最终只会返回一个值，组函数仅可用于选择列表或查询的having子句
--单行函数：输入一个值，输出一个值
--函数的测试
--查询所有员工的薪水总和
select sum(sal) from emp;
-- 查看表中有多少条记录
select deptno,count(*) from emp group by deptno having count(*) >3;

--字符函数
--concat:表示字符串连接 等同于||
select concat('my name is ',ename)from emp;
-- inicap 将字符串首字母大写
select initcap(ename) from emp;
-- 将字符串全部转换为大写
select upper(ename) from emp;
-- 将字符串全部转换为小写
select lower(ename) from emp;
-- 填充字符串
select lpad(ename,10,'*') from emp;
select rpad(ename,10,'*') from emp;
-- 去除空格
select trim(ename) from emp;
select ltrim(ename) from emp;
select rtrim(ename) from emp;
--查找指定字符串的位置
select instr('ABABCDEF','A') FROM emp;
--查看字符串的长度
select length(ename) from emp;
--截取字符串
select substr(ename,0,2) from emp;
--替换操作
select replace(ename,'S','hehe') from emp;

-- 数值函数
-- 四舍五入 可以指定小数部分的位数
select round(123.126,2) from dual;
-- 截断数据 按照位数进行截取，但是不会进行四舍五入的操作
select trunc(123.128,2) from dual;
-- 取模操作
select mod(10,4) from dual;
-- 向上取整
select ceil(12.12) from dual;
-- 向下取整
select floor(12.13) from dual;
-- 取绝对值
select abs(-100) from dual;
--获取正负值
select sign(-100) from dual;
--获取x的y次幂
select power(2,3) from dual;

--日期函数
select  sysdate from dual;
select current_date from dual;
--add_months,添加指定的月份
select add_months(hiredate,2),hiredate from emp;
-- 返回输入日期月份的最后一天
select last_day(sysdate) from dual;
-- 两个日期间隔的月份
select months_between(sysdate,hiredate) from emp;
-- round 返回四舍五入的第一天
select sysdate 当时日期,
round(sysdate) 最近0点日期,
round(sysdate,'day') 最近星期日,
round(sysdate,'month') 最近月初,
round(sysdate,'q') 最近季初日期, 
round(sysdate,'year') 最近年初日期 from dual;

-- 返回下周的星期几
select sysdate 当时日期,
next_day(sysdate,'星期一') 下周星期一,
next_day(sysdate,'星期二') 下周星期二,
next_day(sysdate,'星期三') 下周星期三,
next_day(sysdate,'星期四') 下周星期四,
next_day(sysdate,'星期五') 下周星期五,
next_day(sysdate,'星期六') 下周星期六,
next_day(sysdate,'星期日') 下周星期日 from dual;

--提取日期中的时间
select 
extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,
extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,
extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,
extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,
extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,
extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年
 from dual;

-- 返回日期的时间戳
select localtimestamp from dual;
select current_date from dual;
select current_timestamp from dual;
-- 给指定的时间单位增加数值
select
trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)
trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)
trunc(sysdate)+(interval '1' hour), --加1小时(1/24)
trunc(sysdate)+(INTERVAL '1' DAY),  --加1天(1)
trunc(sysdate)+(INTERVAL '1' MONTH), --加1月
trunc(sysdate)+(INTERVAL '1' YEAR), --加1年
trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒
trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒
trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟
trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟
from dual;

--转换函数:在oracle存在数值的隐式转换和显示转换
--隐式转换：是指字符串可以转换为数值或者日期
--显示转换：
--   to_char:当由数值或者日期转换成字符串的时候，必须要规定格式
select '999'+10 from dual;
-- date to_char
select to_char(sysdate,'YYYY-MM-dd HH24:MI:SS') from dual;
--number to_char
select to_char(123.456789,'99999999') from dual;
select to_char(123.456789,'0000.00') from dual;
select to_char(123.456789,'$0000.00') from dual;
select to_char(123.456789,'L0000.00') from dual;
select to_char(123456789,'999,999,999,999') from dual;
--to_date 转换之后都是固定格式
select to_date('2019-10-10 10:10:10','YYYY-MM-DD HH24:MI:SS') from dual;
--to_number:转成数字
select to_number('123,456,789','999,999,999')from dual;

--显示没有上级管理的公司首脑
select ename,nvl(to_char(mgr),'no manager') from emp where MGR is null;
--显示员工雇佣期满6个月后下一个星期五的日期
select hiredate,next_day(add_months(hiredate,6),'星期五') from emp;


--其他函数
-- 条件函数
-- decode, case when
-- 给不同部门的人员涨薪,10部门涨10%，20部门涨20%，30部门涨30%
select ename,sal,deptno,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*1.3) from emp;
select * from emp;

select ename,
       sal,
       deptno,
       case deptno
         when 10 then
          sal * 1.1
         when 20 then
          sal * 1.2
         when 30 then
          sal * 1.3
       end
  from emp;




-- 组函数，一般情况下，组函数都要和group by组合使用
-- 组函数一般用于选择列表（select）和having子句
-- 常用的组函数
--avg() 平均值 适用于数值类型
--min() 最小值 适用于任何类型
--max() 最大值 适用于任何类型
-- count() 记录数 一般用来获取表中的记录数，建议使用count(1),count(id)这种方式，不建议使用count(*)
--sum()求和 适用于数值类型
select avg(sal) from emp;
select min(sal) from emp;
select max(sal) from emp;
select count(sal) from emp;
select sum(sal) from emp;

--group by 按照某些相同的值进行分组操作,可以指定一个列或者多个列，
-- 但是使用了group by之后，选择列表中只能包含组函数的值或者group by的普通字段
--例如：select deptno, avg(sal),deptno,ename from emp group by deptno having avg(sal)>2000;
--这个sql语句中的ename会报错
--求每个部门的平均薪水
select deptno, avg(sal) from emp group by deptno;
--求平均薪水大于2000的部门
select deptno, avg(sal),deptno from emp group by deptno having avg(sal)>2000;

六、关联查询

--关联查询
--查询雇员名称和部门名称
select ename,dname from emp,dept where dept.deptno = emp.deptno;--等值连接
--查询雇员名称以及自己的薪水等级
select e.ename, e.sal, sg.grade
  from emp e, salgrade sg
 where e.sal between sg.losal and sg.hisal; --非等值连接
--外连接 左外连接(把左边表的数据全部显示), 右外连接(把右边表的数据全部显示)
select * from emp, dept where emp.deptno(+) = dept.deptno;--右外连接
select * from emp, dept where emp.deptno = dept.deptno(+);--左外连接
select * from emp, dept where emp.deptno = dept.deptno;
--自连接 将一张表当成不同的表来看待，自己关联自己
--将雇员和他经理的名称查出来
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;
--笛卡尔积 当关联多张表，但是不指定连接条件的时候，会进行笛卡尔积
select * from emp e,dept d;

-- cross join
select * from emp cross join dept;
--natural join 相当于等值连接，不需要写连接条件
--当两张表中不具有相同的列名的时候，会进行笛卡尔积
select * from emp e natural join dept d;
select * from emp e natural join salgrade d;
--on子句，添加连接条件
select * from emp e join dept d on e.deptno= d.deptno; --等值连接
select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal; --非等值连接
--left outer join 左外连接
select * from emp e left outer join dept d on e.deptno = d.deptno; 
select * from emp e left join dept d on e.deptno = d.deptno; 
--right outer join右外连接
select * from emp e right outer join dept d on e.deptno = d.deptno; 
--full outer join 全连接
select * from emp e full outer join dept d on e.deptno = d.deptno; 
--inner join 两张表的连接查询，只会查询出有匹配记录的数据
select * from emp e inner join dept d on e.deptno = d.deptno;
--using 除了可以使用on表示连接条件之外，也可以使用using作为连接条件,此时连接条件的列不再归属于任何一张表
select deptno,e.ename from emp e join dept d using(deptno);
select e.deptno from emp e join dept d on e.deptno = d.deptno;
--检索雇员名称，所在单位，薪水等级
select e.ename, d.loc, sg.grade
  from emp e
  join dept d
    on e.deptno = d.deptno
  join salgrade sg
    on e.sal between sg.losal and sg.hisal;

七、子查询

--子查询：嵌套在其他sql语句中的完整的sql语句，可以称之为子查询
--分类 单行子查询  多行子查询
--有哪些人的薪水是在整个雇员的平均薪水之上的
select avg(sal) from emp;
select * from emp where sal >(select avg(sal) from emp);
--查询雇员中有哪些人是经理人
select * from emp where empno in (select distinct(t.mgr) from emp t );
--找出部门编号为20的所有员工中收入最高的职员
select sal from emp where deptno=20;
select * from emp where sal >= all(select sal from emp where deptno=20) and emp.deptno = 20;
--求每个部门平均薪水的等级
select avg(sal) from emp group by deptno;
select t.deptno,t.vsal,sg.grade from salgrade sg join (select deptno,avg(sal) vsal from emp group by deptno) t
on t.vsal between sg.losal and sg.hisal;
--求平均薪水最高的部门的编号
select t.deptno,avg(sal) vsal from emp t group by t.deptno;
select max(t.vsal) from (select deptno,avg(sal) vsal from emp group by deptno)t;
select e.deptno from (select t.deptno, avg(sal) vsal from emp t group by t.deptno) e
 where e.vsal =
       (select max(t.vsal)
          from (select deptno, avg(sal) vsal from emp group by deptno) t);
--求部门平均薪水的等级
select avg(sal) from emp group by deptno;
select sg.grade from salgrade sg join (select avg(sal) vsal from emp group by deptno)t
on t.vsal between sg.losal and sg.hisal;

--求部门平均的薪水等级
select e.deptno,sg.grade from salgrade sg,emp e where e.sal between sg.losal and sg.hisal ;
select t.deptno, avg(t.grade)
  from (select e.deptno, sg.grade
          from salgrade sg, emp e
         where e.sal between sg.losal and sg.hisal) t group by t.deptno;
--求薪水最高的前5名雇员
--限制输出：在oracle中，如果需要使用限制输出和分页的功能的话，必须要使用rownum，
--但是rownum不能直接使用，必须要嵌套
select * from emp order by sal desc;
select * from (select * from emp where sal is not null order by sal desc) where rownum <=5;

--求薪水最高的第6到10名雇员
--使用rownum的时候必须要在外层添加嵌套，此时才能将rownum作为其中的一个列，然后进行限制输出
select t1.*,rownum from (
select * from emp e order by e.sal desc)t1 where rownum <= 10;

select *
  from (select t1.*, rownum rn
          from (select * from emp e order by e.sal desc) t1
         where rownum <= 10) t
 where t.rn > 5
   and t.rn <= 10
   
select * from (select t1.*,rownum rn from
 (select * from emp e order by e.sal desc) t1) t where t.rn >5 and t.rn <= 10;

/*
表内容：
2005-05-09  胜
2005-05-09  胜
2005-05-09  负
2005-05-09  负
2005-05-10  胜
2005-05-10  负
2005-05-10  负

生成以下结果
     日期  胜 负
2005-05-10	1	2
2005-05-09	2	2

*/
select t.rq,
       count(decode(shengfu, '胜', 1)) 胜,
       count(decode(shengfu, '负', 2)) 负
  from tmp t
 group by rq;

/*
student_score 表转成以下格式
姓名    语文    数学    英语
王五     89      56      89
*/
select * from student_score;
--decode
select ss.name, 
max(decode(subject,'语文',ss.score)) 语文,
max(decode(subject,'数学',ss.score)) 数学,
max(decode(subject,'英语',ss.score)) 英语
from student_score ss group by ss.name
--case when
select ss.name,
max(case ss.subject when '语文' then ss.score end) 语文,
max(case ss.subject when '数学' then ss.score end) 数学,
max(case ss.subject when '英语' then ss.score end) 英语
from student_score ss group by ss.name
--join
select ss.name, ss.score from student_score ss where ss.subject='语文';
select ss.name, ss.score from student_score ss where ss.subject='数学';
select ss.name, ss.score from student_score ss where ss.subject='英语';

select ss01.name,ss01.score 语文,ss02.score 数学,ss03.score 英语  
from (select ss.name, ss.score from student_score ss where ss.subject='语文') ss01
join (select ss.name, ss.score from student_score ss where ss.subject='数学') ss02
on ss01.name = ss02.name
join (select ss.name, ss.score from student_score ss where ss.subject='英语') ss03
on ss01.name = ss03.name;

--union all
select t.name,max(t.score01) 语文,max(t.score02) 数学,max(t.score03) 英语 from (
select ss01.name, ss01.score score01, 0 score02,0 score03 from student_score ss01 where ss01.subject='语文'  union all
select ss02.name, 0 score01,ss02.score score02, 0 score03 from student_score ss02 where ss02.subject='数学' union all
select ss03.name, 0 score01, 0 score02,ss03.score score03 from student_score ss03 where ss03.subject='英语') t
 group by t.name;