Oracle_1
一、Oracle介绍
ORACLE 数据库系统是美国ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或 B/S体系结构的数据库之一。比如SilverStream 就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了 ORACLE知识，便能在各种类型的机器上使用它。
二、Oracle体系结构
1. 数据库
Oracle数据库是数据的物理存储。
包括数据文件ORA或者DBF、控制文件、联机日志、参数文件等。
其实Oracle数据库的概念和其他数据库不一样，这里的数据是一个操作系统，只有一个库。可以看作Oracle就只有一个大数据库。
2. 实例
一个Oracle实例（Oracle Instance）有一系列的后台进程（Background Processes）和内存结构（Memory Structures）组成。
一个数据库可以有n个实例。
3. 用户
用户实在实例下建立的。
不同的实例可以创建相同名字的用户。
4. 表空间
表空间是Oracle对物理数据库上相关数据文件（ORA后者DBF文件）的逻辑映射。
一个数据库在逻辑上被划分成若干个表空间，每个表空间包含了逻辑上相关联的一组结构。
每个数据库至少有一个表空间（称之为 system 表空间）。
每个表空间右同一磁盘上的一个或多个文件组成，这些文件叫做数据文件（datafile）。一个数据文件只能属于一个表空间。
5. 数据文件（ORA、DBF）
数据文件是数据库的物理存储单位。
数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。
一旦数据文件被加入某个空间表后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。
注：表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些数据放到一个或者多个数据文件中。
由于Oracle的数据库不是普通的概念，Oracle是由用户和表空间对数据进行管理和存放的。但是表不是由空间表去查询的，而是由用户去查的。因为不同的用户可以再同意个表空间建立同一个名字的表！这里的区分就是用户！
三、创建表空间
表空间：Oracle数据库的逻辑单元。
数据库——表空间
一个表空间可以与多个数据文件（物理结构）关联。
一个数据库下可以建立多个表空间，一个表空间可以建立多个用户，一个用户可以建立多个表。
create tablespace hua -- hua为表空间
datafile 'd:\hua.dbf' -- datafile 指定表空间对应的数据文件
size 100m -- 后定义的是表空间的初始大小
autoextend on -- 自动增长，当表空间存储都占满时，自动增长
next 10m -- 后指定的是一次自动增长的大小
四、用户
1. 创建用户
create user dahua -- 创建用户 dahua
identified by 123456 -- 密码为123456
default tablespace hua -- 默认表空间的名称
Oracle数据库与其他数据库产品的区别在于，表和其他的数据库对象都是存储在用户下的。
2. 用户赋权限
新创建的用户没有任何权限，登录后会提示
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-71iwb68Q-1580645317806)(F:\JAVA常用技术\数据库\MySQL\img\oracle提示用户没有权限.JPG)]
Oracle中已存在的三个重要角色：connect角色，resource角色，dba角色
- CONNECT角色：是授予最终用户的典型权利，最基本的
- ALTER SESSION --修改会话
- CREATE CLUSTER --建立聚簇
- CREATE DATABASE LINK --建立数据库链接
- CREATE SEQUENCE --建立序列
- CREATE SESSION --建立会话
- CREATE SYNONYM --建立同义词
- CREATE VIEW --建立视图
- RESOURCE角色：是授予开发人员的
- CREATE CLUSTER --建立聚簇
- CREATE PROCEDURE --建立过程
- CREATE SEQUENCE --建立序列
- CREATE TABLE --建表
- CREATE TRIGGER --建立触发器
- CREATE TYPE --建立类型
- DBA角色：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意基表，包括删除
进入system用户下给用户赋予权限，否则无法正常登陆
grant 角色名称 to 用户名称;
grant connect to dahua; -- 给用户dahua赋予connect角色
grant resource to dahua; -- 给用户dahua赋予resource角色
grant dba to dahua; -- 给用户dahua赋予dba角色
五、数据类型
| 序号 | 数据类型 | 描述 |
|---|---|---|
| 1 | varchar，varchar2 | 表示一个字符串 |
| 2 | number | number(n)：表示一个整数，长度是n，number(m,n)：表示一个小数，总长度是m，小数是n，整数位是m-n |
| 3 | date | 表示日期类型 |
| 4 | clob | 大对象，表示大文本数据类型，可存4G |
| 5 | blob | 大对象，表示二进制数据，可存4G |
六、表的管理
1. 建表
语法：
create table 表名(
字段1 数据类型 [default 默认值],
字段2 数据类型 [default 默认值],
......
字段n 数据类型 [default 默认值]
);
示例：
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
birthday date
);
insert into person(pid, name, gender, birthday) values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));
2. 删除表
语法：
DROP TABLE 表名;
3. 修改表
语法：
-- 添加字段
ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名2 类型 [DEFAULT 默认值]...);
-- 修改字段
ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名2 类型[DEFAULT 默认值]...);
-- 修改字段名称
ALTER TABLE 表名称 RENAME 列名 TO 新列名;
示例：
-- 在person表中添加address字段
ALTER TABLE person ADD(address varchar2(10));
-- 把 person 表的address 列的长度修改成 20 长度
ALTER TABLE person MODIFY(address varchar2(20));
-- 修改address字段名称为addr
ALTER TABLE 表名称 RENAME address TO addr;
4. 表数据的增删改
因为 oracle 的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数
据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据
库。如果事务提交后则不可以再回滚。
-
INSERT（增加）
-- 标准写法： INSERT INTO 表名[(列名1，列名2，...)] VALUES(值1，值2，...); commit; -- 简单写法（不建议） INSERT INTO 表名 VALUES(值 1，值 2，...); commit;示例：
-- 标准写法 INSERT INTO 表名(id, name, gender, birthday, addr) VALUES(2,'李四',1,null,'北京育新'); commit; -- 简单写法 INSERT INTO person VALUES(2,'李四',1,null,'北京育新'); commit;**注意：**使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用 null
-
UPDATE（修改）
-- 全部修改： UPDATE 表名 SET 列名1=值1，列名2=值2，....; commit; -- 局部修改： UPDATE 表名 SET 列名1=值1，列名2=值2，....WHERE 修改条件; commit; -
DELETE （删除）
DELETE FROM 表名 WHERE 删除条件; commit;在删除语句中如果不指定删除条件的话就会删除所有的数据
5. 序列
在很多数据库中都存在一个自动增长的列，如果现在要想在Oracle中完成自动增长的功能，则只能靠序列来完成，所有的自动增长操作，需要用户手工完成处理。
语法：
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
示例：
创建序列：
-- 创建序列
CREATE SEQUENCE seqpersonid;
-- 序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
-- nextval :取得序列的下一个内容
select seqpersonid.nextval from dual;
-- currval :取得序列的当前内容
select seqpersonid.currval from dual;
在插入数据时需要自增的主键可以这样使用：
INSERT INTO 表名(id, name, gender, birthday, addr) VALUES(seqpersonid.nextval,'李四',1,null,'北京育新');
在实际项目中，每一张表会配一个序列，但是表和序列是没有必然联系的，一个序列被那一张表使用都可以，但是我们一般都是一张表用一个序列。
序列的管理一般使用工具来管理。
七、Scott用户下的表结构
八、单行函数
1. 字符函数
接收字符输入，返回字符或者数值，dual是伪表
-
把小写的字符转换成大写字符
upper(string)函数select upper('smith') from dual; -
把大写字符变成小写字符
lower(string)函数select lower('SMITH') from dual; -
首字母大写函数
initcap(string)函数select initcap('hello world') from dual; -
字符串链接函数
concat(str1, 2str)函数或||运算符select concat('hello','world') from dual; select 'hello' || 'world' from dual; -
字符串截取函数
substr(string, position, length)：将字符串string从第二位开始，截取length个字符position：开始位置（从0开始）
length：可选项，子字符串的个数
select substr('helloworld',2,5) from dual; select substr('abcdefg', 2) from dual; -- 返回：bcdefg，截取从b及之后所有字符 -
查找字符串位置
instr（string,subString,position,ocurrence）
string：源字符串
subString：要查找的子字符串
position：查找的开始位置
ocurrence：源字符串中第几次出现的子字符串INSTR('CORPORATE FLOOR','OR', 3, 2) -- 源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置；返回结果为14 -
字符串替换函数
translate(char, from, to)select translate('abcdefga','abc','wo') from dual; -- wodefgw /* 该语句要将'abcdefga'中的'abc'转换为'wo', 由于'abc'中'a'对应'wo'中的'w', 故将'abcdefga'中的'a'全部转换成'w'; 而'abc'中'b'对应'wo'中的'o', 故将'abcdefga'中的'b'全部转换成'o'; 'abc'中的'c'在'wo'中没有与之对应的字符, 故将'abcdefga'中的'c'全部删除; 简单说来，就是将from中的字符转换为to中与之位置对应的字符， 若to中找不到与之对应的字符，返回值中的该字符将会被删除。 在实际的业务中，可以用来删除一些异常数据， 比如表a中的一个字段t_no表示电话号码， 而电话号码本身应该是一个由数字组成的字符串， 为了删除那些含有非数字的异常数据， 就用到了translate函数： */
replace(char, search_string,replacement_string)
select replace('abcdefga','abc','wo') from dual;
-
获取字符串长度函数
select length('郭世华') from dual; -- 3 select lengthb('郭世华') from dual; -- 6 -- 可以通过 length(string) = lengthb(string) 来判断字符串是否含有中文。
2. 数值函数
-
四舍五入函数
默认情况下 round() 四舍五入取整，可以自己指定保留的小数位数。
select round(13.42) from dual; -- 13 取整 select round(13.42432,2) from dual; -- 13.42 保留2位小数 select round(13.42432,-1) from dual; -- 10 负数表示从小数点左边开始截取1位 -
数值截取函数
select trunc(122.555) from dual; --122 默认取整 select trunc(122.555,2) from dual; -- 122.55 保留2位小数 select trunc(122.555,-2) from dual; -- 100 负数表示从小数点左边开始截取2位 -
取余函数
select mod(15,2) from dual; -- 1 15%2
3. 日期函数
Oracle 中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律
日期 – 数字 = 日期
日期 + 数字 = 日期
日期 – 日期 = 数字
-
查询雇员的进入公司的周数。
分析：查询雇员进入公司的天数(sysdate – 入职日期)/7就是周数select ename,round((sysdate - hiredate) / 7) from emp; -
获得两个时间段中的月数：
MONTHS_BETWEEN()
范例：查询所有雇员进入公司的月数select ename,round(months_between(sysdate, hiredate)) from emp;
4. 转换函数
-
字符串转换函数
TO_CHAR范例：查询所有的雇员将将年月日分开，此时可以使用TO_CHAR 函数来拆分,拆分时需要使用通配符
- 年：y, 年是四位使用 yyyy
- 月：m, 月是两位使用 mm
- 日：d, 日是两位使用 dd
select empno, ename, to_char(hiredate,'yyyy') 年, to_char(hiredate,'MM') 月, to_char(hiredate,'dd') 日 from emp; ----------------------------------- select empno, ename, to_char(hiredate,'yyyy-MM-dd') -- 2019-04-26 -- to_char(hiredate,'fmyyyy-MM-dd') -- 2019-4-26 去掉10月之前月份的前导0 from emp; -
日期转换函数
TO_DATE可以把字符串的数据转换成日期类型select to_date('2019-04-26','yyyy-MM-dd') -- to_date('2019-4-26','fmyyyy-MM-dd') from dual;
5. 通用函数
-
控制处理
select nvl(null,0) from dual; -- 如果为null，就换成0 -- 查询员工年薪 select ename， nvl(comm, 0), -- 奖金，如果为null，就换成0 sal*12 + nvl(comm, 0) -- 年薪（12个月薪 + 奖金） from emp; -
Decode函数
该函数类似 if…else if…esle
语法：
DECODE(col/expression, [search1,result1],[search2, result2]....[default])
/*
Col/expression:列名或表达式
Search1，search2...:用于比较的条件
Result1, result2...:返回值
如果 col/expression 和 Searchi匹配就返回 resulti,否则返回 default 的默认值
*/
示例：
select decode(2,1,'我是1',2,'我是2','我是无名') from dual; -- 我是2
-- 查询出所有雇员的职位的中文名
select
ename,
decode(job,
'CLERK','业务员',
'SALESMAN','销售',
'PRESIDENT','总裁',
'MANAGER','经理',
'无业')
from
emp;
-
case when函数
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END示例：
select t.empno, t.ename, case when t.job = 'CLERK' then '业务员' when t.job = 'MANAGER' then '经理' when t.job = 'ANALYST' then '分析员' when t.job = 'PRESIDENT' then '总裁' when t.job = 'SALESMAN' then '销售' else '无业' end from emp t;
九、多行函数（聚合函数）
1. 统计记录数 count()
范例：查询出所有员工的记录数
select count(*) from emp;
-- 不建议使用 count(*)，可以使用一个具体的列以免影响性能。
select count(ename) from emp;
2. 最小值查询 min()
范例：查询出来员工最低工资
select nvl(sal,0),min(sal) from emp;
3. 最大值查询 max()
范例：查询出来员工最高工资
select nvl(sal,0),max(sal) from emp;
4. 查询平均值 avg()
范例：查询出来员工平均工资
select nvl(sal,0),avg(sal) from emp;
5. 求和函数 sum()
范例：查询出 20 号部门的员工的工资总和
select nvl(sal,0),sum(sal) from emp where deptno = 20;
十、分组统计
分组统计需要使用
GROUP BY来分组
语法：
SELECT
字段列表
FROM
表名
{WEHRE 查询条件}
{GROUP BY 分组字段}
ORDER BY
列名1 ASC|DESC，
列名 2...ASC|DESC
范例1：查询每个部门的人数
select deptno, count(ename) from emp group by deptno;
范例2：查询出每个部门的平均工资
select deptno, avg(sql) from emp group by deptno;
注意：
- 如果使用分组函数， SQL 只可以把 GOURP BY 分组条件字段和聚合函数查询出来，不能有其他字段。
- 如果使用分组函数，不使用 GROUP BY 只可以查询出来分组函数的值
范例3：按部门分组，查询出部门名称和部门的员工数量
select
d.deptno, d.dname, count(ename)
from
emp e, dept d
group by
d.deptno, d.dname;
范例4：查询出部门人数大于 5 人的部门
需要给 count(ename)加条件，此时在本查询中不能使用 where，可以使用HAVING
select
d.deptno, d.dname, count(ename)
from
emp e, dept d
group by
d.deptno, d.dname
having
count(ename) > 5;
范例5：查询出部门平均工资大于 2000的部门
select
d.deptno, d.dname, avg(sal)
from
emp e, dept d
group by
d.deptno, d.dname
having
avg(sal) > 2000;
十一、多表查询
1. 多表连接基本查询
语法：
SELECT {DISTINCT} -- DISTINCT：去重
字段列表.
FROM
表名1 别名1，表名2 别名2
WHERE
限制条件
ORDER BY
排序字段 ASC|DESC...
范例1：查询员工表和部门表
select * from emp, dept;
我们发现产生的记录数是 56条，我们还会发现emp 表是 14 条，dept 表是 4条，56 正是emp表和 dept表的记录数的乘积，我们称其为笛卡尔积。
如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造成影响，想要去掉笛卡尔积我们需要关联查询。
在两张表中我们发现有一个共同的字段是 depno，depno 就是两张表的关联的字段，我们可以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。
select * from emp e, dept d where e.deptno = d.deptno;
关联之后我们发现数据条数是 14条，不在是 56 条。
多表查询我们可以为每一张表起一个别名
范例2：查询出雇员的编号，姓名，部门的编号和名称，地址
select
e.empno, e.ename, -- 雇员的编号，姓名
d.deptno, d.dname, d.loc -- 部门的编号和名称，地址
from
emp e, -- 员工表
dept d -- 部门表
where
e.deptno = d.deptno;
范例3：查询出每个员工的上级领导
分析：emp 表中的 mgr字段是当前雇员的上级领导的编号，所以该字段对 emp表产生了自身关联，可以使用 mgr字段和 empno来关联
select
e.empno, e.ename,
e2.empno, e2.ename
from
emp e,
emp e2
where
w.mgr = e2.empno;
范例4：在上一个例子的基础上查询该员工的部门名称
分析：只要在上一个例子基础上再加一张表的关联，使用deptno 来做关联字段即可
select
e.empno, e.ename,
e2.empno, e2.ename,
d.dname
from
emp e,
emp e2,
dept d
where
w.mgr = e2.empno
and e.deptno = d.deptno;
范例5：查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级
select
e.empno, e.ename,
decode(s.grade,
1,'一级',
2,'二级',
3,'三级',
4,'四级',
5,'五级') grade,
d.dname,
e1.empno, e1.ename,
decode(s1.grade,
1,'一级',
2,'二级',
3,'三级',
4,'四级',
5,'五级') grade
from
emp e,
emp e1,
dept d,
salgrade s,
salgrade s1
where
e.mgr = e1.empno
and e.deptno = d.deptno
and e.sal between s.losal and s.hisal
and e1.sal between s1.losal and s1.hisal;
2. 外连接（左右连接）
-
右连接
当我们在做基本连接查询的时候，查询出所有的部门下的员工，我们发现编号为 40 的部门下没有员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的
select e.empno, e.ename, d.deptno, d.dname form emp e, dept d where e.deptno(+) = d.deptno;使用(+)表示左连接或者右连接，当(+)在左边表的关联条件字段上时是左连接，如果是在右边表的关联条件字段上就是右连接。
范例：查询出所有员工的上级领导
分析：我们发现使用我们以前的做法发现 KING 的上级领导没有被展示，我们需要使用左右连接把他查询出来
select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.mgr = m.empno(+);
十二、子查询
子查询：在一个查询的内部还包括另一个查询，则此查询称为子查询。
Sql的任何位置都可以加入子查询。
范例：查询比 7654工资高的雇员
分析：查询出 7654员工的工资是多少，把它作为条件
select
*
from
emp t1
where
t1.sal > (
select
t.sal
from
emp t
where
t.empno = 7654
);
子查询在操作中有三类：
- 单列子查询：返回的结果是一列的一个内容
- 单行子查询：返回多个列，有可能是一个完整的记录
- 多行子查询：返回多条记录
范例2：查询出比雇员 7654 的工资高，同时从事和 7788的工作一样的员工
select
*
from
emp t1
where
t1.sal > (
select
t.sal
from
emp t
where
t.empno = 7654)
and
t1.job = (
select
t2.job
from
emp t2
where t2.empno = 7788);
范例：要求查询每个部门的最低工资和最低工资的雇员和部门名称
select
d.dname,
a.minsal,
e.ename
from
dept d,
(select
deptno, min(sql) minsal
from
emp
group by
deptno) a,
emp e
where
d.deptno = a.deptno,
and
e.sal = a.minsal;
十三、 Rownum 与分页查询
ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
范例：查询 emp 表带有 rownum 列
select rownum, t.* frorm emp t;
我们可以根据 rownum 来取结果集的前几行，比如前 5 行
select rownum, t.* frorm emp t where rownum < 6;
但是我们不能取到中间几行，因为rownum不支持大于号，只支持小于号，如果想 实现我们的需求怎么办呢？答案是使用子查询，也正是oracle分页的做法。
-
第一种写法：
select * from (select rownum rm, a.* from (select * from emp) a where rownum < 11) b where b.rm > 5;
-
第二种写法：
select * from (select rownum r ,emp.* from emp) b where b.r >5 and b.r <11
select *
from (select rownum r ,emp.* from emp) b
where b.r >5 and b.r <11
sal,
e.ename
from
dept d,
(select
deptno, min(sql) minsal
from
emp
group by
deptno) a,
emp e
where
d.deptno = a.deptno,
and
e.sal = a.minsal;
# 十四、 Rownum 与分页查询
> ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
**范例：查询 emp 表带有 rownum 列**
```plsql
select rownum, t.* frorm emp t;
我们可以根据 rownum 来取结果集的前几行，比如前 5 行
select rownum, t.* frorm emp t where rownum < 6;
但是我们不能取到中间几行，因为rownum不支持大于号，只支持小于号，如果想 实现我们的需求怎么办呢？答案是使用子查询，也正是oracle分页的做法。
-
第一种写法：
select * from (select rownum rm, a.* from (select * from emp) a where rownum < 11) b where b.rm > 5;
-
第二种写法：
select * from (select rownum r ,emp.* from emp) b where b.r >5 and b.r <11
扫一扫
1453
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
