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

一、数据库介绍

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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

五、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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175

六、关联查询

--关联查询
--查询雇员名称和部门名称
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

七、子查询

--子查询：嵌套在其他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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111