Oracle

Oracle

1. 学习目标

2. OracleXE 安装

3.数据库

随着互联网时代的到来，需要持久化数据呈现井喷式发展，常规的 io 操作虽然可以满足持久化的需求，
但是，对于持久化的目的，对数据的操纵，显然力不从心，且操作的复杂度很大，不利于大规模的发
展，审时度势，数据库应运而生。

数据库现在已经成为数据管理的重要技术，也是计算机的重要分支。由于数据库具有数据结构化，最低
冗余度、较高的程序与数据独立性，易于扩展、易于编制应用程序等优点，较大的信息系统都是建立在
数据库设计之上的。数据库的运用从一般管理扩大到计算机辅助技术、人工智能以及科技计算等领域。
随着数据库技术的发展，计算机技术也随着得到了很大的发展，数据库为我们提供了可以快速存储以及
检索的便利，它也为近几年软件可以如此普及贡献不小的力量。
常用的数据库有：

3.1. DBMS

数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使
用和维护数据库，简称 DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。
用户通过 DBMS 访问数据库中的数据，数据库管理员也通过 dbms 进行数据库的维护工作。它可使多个
应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。大部分 DBMS 提供数据定
义语言 DDL （Data Definition Language）和数据操作语言 DML ，供用户定义数据库的模式结构与权限
约束，实现对数据的追加、删除等操作。
根据存储模型可将数据库划分为关系型数据库和非关系型数据库。关系型数据库，是建立在关系模型基
础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单来说，关系模型指的就
是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。标准数据
查询语言 SQL 就是一种基于关系数据库的语言，这种语言执行对关系数据库中数据的检索和操作。
当前主流的关系型数据库有 Oracle、DB2、Microsoft SQL Server、Microsoft
Access、MySQL等。
Oracle Database，又名 Oracle RDBMS，或简称 Oracle。是甲骨文公司的一款关系数据库管理系
统。它是在数据库领域一直处于领先地位的产品。可以说 Oracle 数据库系统是目前世界上流行的关系数
据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高
效率、可靠性好的适应高吞吐量的数据库解决方案。

3.2. SQL 语言介绍

SQL(Structured Query Language)为数据库的语言，在1974 年由Boyce【博伊斯】和Chamberlin【钱伯林】提出的一种介于关系代数与关系演算之间的结构化查询语言，是一个通用的、功能极强的关系型
数据库语言。它包含三部分:

4. SELECT

解析步骤: from -> where -> select -> order by

4.1查询列(字段)

*select distinct |字段|表达式 as 别名 from 表 表别名

SELECT * FROM 表名; ->查询某个表中所有的记录的所有字段信息
SELECT 列名 FROM 表名; ->查询某个表中所有的记录的指定字段信息
SELECT 列名1,列名2 FROM 表名; -> 查询某个表中所有的记录的字段1 字段2
SELECT distinct 列名 FROM 表名; ->去除重复记录
SELECT 表达式 FROM 表名; ->查询表达式
SELECT xxx as 别名 FROM 表名 表别名 ->使用别名

4.1.1. 部分列

查询部分字段，指定的字段名:

--1)、检索单个列
select ename from emp; --查询雇员姓名
--2)、检索多个列
select deptno,dname,loc from dept; --查询部门表的deptno,dname, loc 字段的数据。
--以下查询的数据顺序不同(查询的字段顺序代表数据顺序)
select loc,dname,deptno from dept;
select deptno,dname,loc from dept;

4.1.2. 所有列

查询所有的字段 通配符 * ( 书写方便、可以检索未知列;但是降低检索的性能) ,数据的顺序跟定义表结构
的顺序一致

--1)、检索所有列1
select * from dept; --查询部门的所有信息
--2)、检索所有列2
select deptno,dname,loc from dept; --查询部门的所有信息

4.1.3. 去除重复

使用distinct去重，确保查询结果的唯一性

select distinct deptno from emp; --去重

4.1.4. 别名

使用别名便于操作识别 、隐藏底层信息。存在字段别名和表别名

select ename as "雇员 姓名" from emp;
select ename "雇员姓名" from emp;
select ename 雇员姓名 from emp;
select ename as 雇员姓名 from emp;
select ename as " Ename" from emp;

4.1.5. 字符串

使用’’表示字符串(注意区分””) ，拼接使用 ||

select 'my' from emp;sql
select ename||'a'||'-->' info from emp;

4.1.6. 伪列

不存在的列，构建虚拟的列

select empno, 1*2 as count,'cmj' as name,deptno from emp;

4.1.7. 虚表

用于计算表达式，显示单条记录的值

select 1+1 from dual;

4.1.8. null

null 遇到数字参与运算的结果为 null,遇到字符串为空串

select 1+null from dual;
select '1'||null from dual;
select 1||'2'||to_char(null) from dual;
select ename,sal*12+comm from emp;
--nvl内置函数，判断是否为null,如果为空，取默认值0，否则取字段实际值 select
ename,sal*12+nvl(comm,0) from emp;

4.2. 查询行(记录)

where 过滤行记录条件 ,条件有

a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
b)、and 、or、 not、 union、 union all、 intersect 、minus
c)、null :is null、 is not null、 --not is null
d)、like :模糊查询 % _ escape('单个字符')
f)、in 、 exists(难点) 及子查询

4.2.1. 比较条件

= 、>、 <、 >=、 <=、 !=、 <>

select * from emp where deptno !=20;
select * from emp where deptno <>20;
select * from emp where sal between 800 and 950; --between and是成对出现的
--查询 员工的年薪大于20000的 员工名称、岗位 年薪
--1)、nvl
select ename,job,12*(nvl(comm,0)+sal) income from emp;
--2)、年薪大于20000
--错误不能使用别名: select ename,job,12*(nvl(comm,0)+sal) income from emp where
income>2000;
--a)、嵌套一个: 查询在前 过滤在后
select ename,job,income from
(select ename,job,12*(nvl(comm,0)+sal) income from emp) where income>2000;
--b)、不使用别名 (推荐) :过滤在前，查询在后
select ename,job,12*(nvl(comm,0)+sal) income from emp where 12*(nvl(comm,0)+sal)
>2000 ;
--了解 any some all
-- >=any(值列表) 大于最小值<=any(值列表)小于最大值
select * from emp where sal >=any(900,2000);
select * from emp where sal <=any(900,2000);
-- some与any 一样的效果
-- all 大于最大值 小于最小值
select * from emp where sal >=all(900,2000); select * from emp where sal
<=all(900,2000);
--查询 工种为’SALESMAN’的员工信息 （注意 内容区分大小写）
--检索 工资 大于 2000员工名称 岗位 工资
--检索 工资 小于 3000员工名称 岗位 工资
--检索 工资 2000, 3000员工名称 岗位 工资
--查询部门编号为20的员工名称

4.2.2. 且 或 非

and、 or、 not

select * from emp where sal>=900 and sal<=950;
--查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资
--查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资
--查询 岗位 不是 CLERK 员工名称 部门编号，工资

4.2.3. null

null不能使用条件判断，只能使用is

--存在佣金的员工名称
select * from emp where comm is null;
--不存在佣金的员工名称
select * from emp where comm is not null;
select * from emp where not comm is null;

4.2.5. like :模糊查询

模糊查询，使用通配符:

%:零个及以上(任意个数的)的字符_:一个字符
遇到内容中包含 % _ 使用escape(‘单个字符’)指定转义符

--查询员工姓名中包含字符A的员工信息
select * from emp where ename like '%A%';
--查询员工姓名中包含第二个A的员工名称信息
select * from emp where ename like '_A%';
--数据中 员工姓名中 存在 _ % ,如何查找：
--1)、编写测试数据
insert into emp(empno,ename,sal) values(1000,'t_%test',8989); insert into
emp(empno,ename,sal) values(1200,'t_tes%t',8000);
--2)、查找
--查询员工姓名中包含字符%的员工名称 岗位 工资 部门编号
select ename,job,sal,deptno from emp where ename like '%a%%' escape('a');
--查询员工姓名中包含第二个_的员工名称 岗位 工资 部门编号

4.2.6. in 与 exists

in相当于使用or的多个等值，定值集合 ,如果存在 子查询，确保 类型相同、字段数为1，如果记录多，效率不高，用于 一些 少量定值判断上

select * from emp where sal in(900,800);
--子查询(查询中再有查询) in 只能存在一个字段
select * from emp where sal in (select sal from emp e where deptno=10);
--10或30部门的雇员信息
select * from emp where deptno in(10,30);
--部门名称为 SALES 或 ACCOUNTING 的雇员信息
select deptno from dept where dname in('SALES','ACCOUNTING'); SELECT *
FROM emp
WHERE deptno IN
(SELECT deptno FROM dept WHERE dname IN ('SALES', 'ACCOUNTING'));
/*
便于理解 使用java思维
while(外层结果集){
while(内层结果集){
if(emp.deptno==10){
syso("....");
}
if(emp.deptno==30){
syso("....");
}
}
}
==>in :如果记录多，效率不高，用于 一些 少量定值判断上
*/

--exists :条件为true,存在记录，则返回结果，后续不再继续 ,与字段无关，与记录有关
--exists 难点: 外层结果集 内层结果集 关系列(没有关系列 true)
/*
while(外层结果集){
while(内层结果集){
if(emp.deptno==dept.deptno||true){
syso("....");
break; //跳出本层
}
}
}
*/
--无关系列
/**
while(emp 的14条记录){
while(dept的2条记录){
if(true){
syso();
break;
}
}
}
*/
select *
from emp
where exists
(select deptno,dname from dept where dname in ('SALES', 'ACCOUNTING'));
--加入关系列
/**
while(emp 的14条记录){
while(dept的2条记录){
if(e.deptno=d.deptno){
syso();
break;
}
}
}
*/
select *
from emp e
where exists (select deptno, dname
from dept d
where dname in ('SALES', 'ACCOUNTING')
and e.deptno = d.deptno);
/**
while(emp 的14条记录){
while(dept的2条记录){
if(e.deptno!=d.deptno){
syso();
break;
}
}
}
*/
select *
from emp e
where exists (select deptno, dname
from dept d
where dname in ('SALES', 'ACCOUNTING')
and e.deptno != d.deptno);
--分析以下结果
select *
7.2.7. 获取所有行的记录
7.2.8. 排序
使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的顺序。
顺序 :asc（默认） desc
多字段: 在前面字段相等时，使用后面的字段排序
空排序: 降序为 desc，注意 null 为最后
from emp e
where not exists
(select deptno, dname
from dept d
where dname in ('SALES', 'ACCOUNTING')
and e.deptno = d.deptno);
select ename, sal
from emp
where sal in (select sal from emp e2 where e2.sal >= 2000);
select ename, sal
from emp
where exists (select ename, sal from emp e2 where e2.sal >=
2000); --exists
select empno, ename, sal
from emp e1
where exists (select empno, ename, sal, comm
from emp e2
where comm is not null
and e1.empno = e2.empno);
select empno, ename, sal
from emp e1
where exists (select empno, ename, sal, comm
from emp e2
where comm is not null
and e1.deptno = e2.deptno);

4.2.7. 获取所有行的记录

select * from emp;
select * from emp where 1=1 ;
select * from emp where ename like '%';

4.2.8. 排序

使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的顺序。
顺序 :asc（默认） desc
多字段: 在前面字段相等时，使用后面的字段排序
空排序: 降序为 desc，注意 null 为最后

--按工资降序
select * from emp order by sal desc;
--null问题
select * from emp order by nvl(comm,0),comm desc;
select * from emp order by comm nulls first;
--查询雇员姓名，年薪 按佣金排序 默认为升序(asc)，降序为desc，注意null为最后
select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm desc;
--查询雇员姓名，年薪 按佣金排序 默认为升序(asc)，降序为desc，注意null为最后
select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm desc;

5. 函数

函数分为系统内置函数 自定义函数（后期学习的 plsql 中定义）；了解系统内置函数（方法），重点掌握 to_date，to_char （字符和日期的转换）根据函数的返回结果，我们将函数分为单行函数和多行函数

1. 单行函数:一条记录返回一个结果
2. 多行函数 组函数 聚合函数 (重点) :多条记录 返回一个结果 (重点)

5.1. 单行函数

5.1.1. 日期函数

日期函数: 注意区分 db数据库时间 ，java应用服务器的时间。以一方为准
oracle以内部数字格式存储日期年月日小时分钟秒

sysdate/current_date 以date类型返回当前的日期
add_months(d,x) 返回加上x月后的日期d的值
LAST_DAY(d) 返回的所在月份的最后一天
months_between(date1,date2) 返回date1和date2之间月的数目
next_day(sysdate,星期一) 下一个星期一

5.1.1.1. 当前时间

select current_date from dual where 1=1; select sysdate from dual where 1=1;

5.1.1.2. 修改日期（天数±）

--两天后的时刻
select sysdate+2 from dual;

5.1.1.3. 修改月份

--当前5个月后的时间
select add_months(sysdate,5) from dual; --雇佣日期 2个月的时间
select ename,hiredate, add_months(hiredate,2) after from emp;

5.1.1.4. 月份之差

--雇佣日期 距离现在的 月份数
select ename, months_between(sysdate , hiredate) from emp;

5.1.1.5. 最后一天

-返回雇佣日期 当月最后一天的时间
select ename, last_day(hiredate) d from emp;

5.1.1.6. 下一个星期的时间

--下一个星期二
select next_day(sysdate, '星期二') from dual;

5.1.2. 转换函数（重点）

to_date(c,m) -> 字符串以指定格式转换为日期
to_char(d,m) -> 日期以指定格式转换为字符串

select to_date('2017-3-21 18:12:12', 'yyyy-mm-dd hh24:mi:ss') time from dual;
select to_char(sysdate, 'yyyy-mm-dd') from dual;
select to_char(sysdate, 'yyyy/mm/dd') from dual;
select to_char(sysdate, 'yyyy\mm\dd') from dual;
--注意中文的问题
--select to_char(sysdate,'yyyy年mm月dd日') from dual;
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual;

5.1.3. 查询82的员工信息

select * from emp where hiredate like '%82%' ;
或
select * from emp where to_char(hiredate,'yyyy')='1982';
或
select *
from emp
where hiredate between to_date('1982-01-01', 'yyyy-mm-dd') and to_date('1982-12-
31', 'yyyy-mm-dd');

5.2. 多行|聚合|组函数

组函数|多行函数|聚合函数 即多条记录 返回一个结果。我们需要掌握如下几个组函数：

avg 、sum、 min、max、count
count :统计记录数 count() -->* 或一个列名
max min: 最大值 最小值
sum：求和
avg:平均值

注意:

1. 组函数仅在选择列表和Having子句中有效
2. 出现组函数，select 只能有组函数或分组字段

说明：

组信息 与单条记录不能同时查询
组函数 不能用在 where中，能使用的地方 select having
null 不参与运算

5.2.1. count： 求和

--1、count统计所有的员工数
--1)、*
--2)、主键
--3)、推荐
select ename,1 from emp;
select count(1) from emp where 1=1;
--2、null不参与运算
--存在佣金的员工数
--不推荐/不需要
select count(comm) from emp where comm is not null; --推荐
select count(comm) from emp;
--统计 部门编号30的员工数
select count(1) from emp where deptno=30;
--统计数量过程中 ，可能处理重复
--统计 存在员工的 部门数量
select count(distinct(deptno)) 有人的部门 from emp;
--统计10和20部门一共有多少人
select distinct(count(1)) from emp where deptno in(10,20);

5.2.2. max min: 最大值 最小值

--查询所有员工的 最高薪水 ，最低薪水，员工总数 -->组信息
select max(sal) maxSal , min(sal) minSal , count(1) from emp;
--查询 最高薪水的员工名称 及薪水
--组信息 与单条记录不能同时查询
select max(sal), ename, sal from emp; 

5.2.3. sum：求和

-- 查询10部门的所有员工的工资总和
select sum(sal) from emp where deptno=10;

5.2.4. avg: 平均

-- 查询工资低于平均工资的员工编号，姓名及工资
select empno, ename,sal from emp where sal<(select avg(sal) from emp);
--查看 高于本部门平均薪水员工姓名
select * from emp e1 where sal>(select avg(sal) from emp e2 where
e1.deptno=e2.deptno);

5.3. 分组

分组: group by , 将符合条件的记录 进一步的分组
过滤组:having, 过滤组信息 ，表达式同where 一致
语法: select 数据 from 数据源 where 行过滤条件 group by 分组 having组过滤信息 order by 排序字段;
执行流程 : from -> where -> group by -> having -> select -> order by

group by : 分组

1)、select出现分组函数,就不能使用 非分组信息，可以使用group by 字段
2)、group by字段 可以不出现 select 中 ，反之select 除组函数外的，其他字段必须出在group by中

having : 过滤组

1. where : 过滤行记录，不能使用组函数
2. having : 过滤组 可以使用组函数

代码：

-- 求出每个部门的平均工资
select deptno ,avg(sal) from emp group by deptno;

-- 找出20部门和30部门的最高工资 
select deptno, max(sal) from emp group by deptno;
--先过滤后分组
select deptno, max(sal) from emp where deptno in(20,30) group by deptno;
--先分组后过滤
select deptno, max(sal) from emp group by deptno having deptno in(20,30);

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal)from emp where sal>1000 and deptno in(10,20) group by deptno;
select avg(sal) from emp where sal>1000 group by deptno having deptno in(10,20);

-- 找出每个部门的最高工资
select deptno, max(sal) from emp group by deptno;

-- 求出平均工资高于2000的部门编号和平均工资
select deptno, avg(sal) from emp group by deptno having avg(sal)>2000;

-- 查询最低平均工资的部门编号

select deptno from emp group by deptno having avg(sal)=(select min(avg(sal))from emp group by deptno);

-- 统计每个部门的员工数，和部门编号,按照员工个数升序排序
select deptno,count(1) n from emp group by deptno order by n;

-- 查询每个工种的最高工资以及工种
select job,max(sal) from emp group by job;

-- 查询平均工资在1500到2000之间的部门平均工资和部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal) between 1500 and 2000;

5.4行转列

select name,
       decode(course, '语文', score) 语文,
       decode(course, '数学', score) 数学,
       decode(course, '英语', score) 英语
  from tb_student;
  
select name,
       max(decode(course, '语文', score)) 语文,
       min(decode(course, '数学', score)) 数学,
       max(decode(course, '英语', score)) 英语
  from tb_student group by name;

6. 连表查询

当我们获取的数据不是来自于同一张表而是来自于多张表时就需要使用到连表连接

6.1. 连表连接（92）

select * from emp;
select * from dept;
注意:同名列 非 必须区分
数据源 、关系列、 过滤条件、字段*

6.1.1. 笛卡尔积

--查询所有员工的信息以及员工所在部门信息
--数据 : 员工信息*  部门信息*
--来源 : 员工表emp ,部门表 dept
select * from emp,dept;
select emp.*,dept.deptno,dept.dname from emp,dept;
--别名
select e.*,d.deptno,d.dname from emp e,dept d;

6.1.2. 等值连接(在笛卡尔积基础上 取条件列相同的值)

--表连接条件 : 过滤通过连表产生的不满足要求的表连接数据
--等值连接 :   判断两个数据源中的某个字段值相等或者不相等 
--非等值连接 : 判断区间,判断范围的条件
--92语法中表 连接条件定义 在where后面
--如果要使用的字段来自于多个数据源中都存在,需要指明限定词|出处
select empno,ename,sal,e.deptno,dname from emp e,dept d where e.deptno=d.deptno;

--查询20部门员工的信息以及员工所在部门信息
select empno,ename,sal,e.deptno,dname from emp e,dept d where e.deptno=d.deptno and e.deptno=20;

6.1.3. 非等值连接 > < != <>between and

----非等值连接 : 判断区间,判断范围的条件
--查询每个员工的员工信息以及薪资等级信息
select * from emp e,salgrade s where sal between losal and hisal;

--查询员工信息以及所在部门信息以及薪资等级信息
select *
  from emp e, dept d, salgrade s
 where e.deptno = d.deptno
   and e.sal between s.losal and s.hisal;

--自连接 : 一张表作为两个数据源使用
--查询有上级的员工信息以及上级经理人信息
--数据 : 员工信息  经理人信息
--来源 : 员工表 emp e1 ,经理人表 emp e2
select * from emp e1,emp e2 where e1.mgr=e2.empno;

6.1.4. 自连接: 特殊的等值连接 (来自于同一张表)

--内连接 : 满足连接条件查询到不满足过滤掉
--找出 存在上级的员工姓名 及上级名称
-- 数据来源： emp e, emp m
-- 字段： e.ename, m.ename
-- 条件： e.mgr=m.empno
select e.ename, m.ename from emp e, emp m where e.mgr = m.empno;

6.1.5. 外连接

--外连接 : 某一个数据源中的数据不满足连接条件的时候也能显示
--左外连接 | 左连接 : from后面主表在左边
--右外连接 | 右连接 : from后面主表在右边
--主表 : 主表中的数据无论是否满足连接条件都能显示
--92语法 : 在连接条件位置,主表的对面添加(+)
--找出 所有的员工姓名 及上级名称
--找出 所有部门的员工数 及部门名称
select dname, nu
from dept d, (select count(1) nu, deptno from emp group by deptno) e
where d.deptno(+) = e.deptno;

看逗号, 主表在,的左边就叫左外连接主表在,的右边叫右连接

6.2. 连表连接（99）

交叉连接 cross join —>笛卡尔积
自然连接(主外键、同名列) natural join -->等值连接
join using连接(同名列) -->等值连接
[inner]join on 内连接 -->等值连接 非等值 自连接 (解决一切) 关系列必须区分
left|right [outer] join on|using -->外连接
full join on|using -->全连接 满足直接匹配，不满足 相互补充null ，确保 所有表的记录 都至少出现一次

6.2.1. 交叉连接

--笛卡尔积 :
--92
select * from emp,dept;
--99
select * from emp cross join dept;

6.2.2. 自然连接

自动做等值连接

--注意: 在自然连接中同名字段的不能使用限定词
select ename,deptno,dname from emp natural join dept;

6.2.3. using 连接

指定同名字段做等值连接

--注意: 在join..using中同名字段的不能使用限定词
select ename,deptno,dname from emp join dept using(deptno);

6.2.4. on 连接

--既能够做等值连接,也能做非等值连接
--数据源1 join 数据源2 on 连接条件;
--注意: 在join..on中同名字段的必须使用限定词
select ename,e.deptno,dname from emp e join dept d on e.deptno = d.deptno;


-- 查询30部门的员工信息和部门信息
select ename, e.deptno, dname
  from emp e
  join dept d
    on e.deptno = d.deptno
 where e.deptno = 30;

--非等值连接
-- 查询每个员工的工资，姓名，工种，工资等级
select ename,sal,job,grade from emp e join salgrade s on sal between losal and hisal;

-- 查询20部门工资大于1500员工的工资，姓名，工种， 工资等级
select ename,sal,job,grade from emp e join salgrade s on sal between losal and hisal where deptno=20 and sal>1500;

--有员工存在的员工信息 与上级信息
select * from emp e1 inner join emp e2 on e1.mgr=e2.empno;

--部门编号为30的员工 员工名称 部门名称 工资等级 上级名称
select *
  from emp e1
  join dept d
    on e1.deptno = d.deptno
  join salgrade sa
    on sal between losal and hisal
  join emp e2
    on e1.mgr = e2.empno
 where e1.deptno = 30
 order by e1.sal desc;

6.2.5. 外连接

-所有部门的 部门名称，员工数
--左外
select dname, n
from dept d
left outer join (select deptno, count(1) n from emp group by deptno) i
on d.deptno = i.deptno;
select dname, n
from dept d
left outer join (select deptno, count(1) n from emp group by deptno) i
using (deptno);
--右外
select dname, n
from (select deptno, count(1) n from emp group by deptno) i
right outer join dept d
on d.deptno = i.deptno;

6.2.6. 全连接

select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) a
full join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) b
on a.no = b.no;
select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) a
full join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) b
using (no);

6.3.rowid与rownum

6.3.1rowid

实现重复记录的删除

伪列
rowid行记录的地址,行记录的唯一标识
实现没有主键,唯一字段的表中完全相同数据的去重
select empno,ename,rowid from emp;

SELECT * FROM TB_STUDENT;
--去重
--1)查询到要保留的数据
SELECT DISTINCT ID,NAME,COURSE,SCORE FROM TB_STUDENT; --显示去重
SELECT MAX(ROWID) FROM TB_STUDENT GROUP BY ID,NAME,COURSE,SCORE; 
--2)查到要删除的数据
SELECT * FROM TB_STUDENT WHERE NOT ROWID IN (SELECT MAX(ROWID) FROM TB_STUDENT GROUP BY ID,NAME,COURSE,SCORE);
--3)删除这些数据
DELETE FROM TB_STUDENT WHERE NOT ROWID IN (SELECT MAX(ROWID) FROM TB_STUDENT GROUP BY ID,NAME,COURSE,SCORE);

6.3.2 rownum : 结果集中行记录的序号

rownum :1、必须排序 2、不能直接取大于 1 的数

一个结果集就存在一份rownum
rownum从1开始,每次+1,有规律,有顺序

select deptno,dname,rownum from dept order by deptno desc;```

**在oracle数据库中可以使用rownum实现分页查询**

	如果想要对rownum进行判断,需要针对一条已经确定的rownum进行判断,解决方案,外部嵌套select
```sql
select empno,ename,sal,n,rownum num from (select empno,ename,sal,rownum n from emp order by sal desc);

分页 :  每页显示数据个数 n = 3   当前显示第几页 i    起始位置rownum : rownum> (i-1)*n;    结束位置rownum : rownum<=i*n

select *
  from (select empno, ename, sal, rownum num
          from (select empno, ename, sal, rownum n from emp order by sal desc))
 where num <= 6
   and num > 3;

7.表设计

明确表的作用 表名

明确表中的字段

字段名
字段类型 : number char varchar2 date
字段约束 :

主键约束(唯一+非空)
唯一约束
非空约束
默认约束
检查约束
外键约束  Emp  Dept
涉及到两张表
         父表|主表 
         子表|从表 : 子表中添加一个外键字段,关联主表的主键字段
                     外键字段的值只能为主表中主键字段已有的值

select  * from emp;
select  * from dept;

设计表的三范式

目的：最终的目的避免数据重复冗余

        1NF-->列不可再分最小原子 （避免重复);
        2NF-->主键依赖(确定唯一)；
        3NF-->消除传递依赖(建立主外键关联 拆分表);

表与表之间的关系

一对一关系 : 用户表 身份证信息表

 主外键关联关系 

一对多|多对一 : 班级表 学生表

 主外键关联关系 : 在多的一方设置外键,关联一的一方的主键

多对多 : 订单与商品 学生与选课

中间表 : 定义两张表的关联关系

8.DDL 数据定义语言 （了解能做修改）

create表创建 drop删除表 alter修改表结构

create表创建

创建表（不加约束）

create table tb_user(
  userid number(5),
  username varchar2(30),
  userpwd varchar2(20),
  age  number(3) ,
  gender char(1 char) ,
  email  varchar2(30),
  regtime date
);
--数据类型(默认字节数) , varchar2(1 char)一个字符

--加入注释
comment on table tb_user is '用户表';
comment on column tb_user.userid is '流水号,主键';
comment on column tb_user.username is '用户名';
comment on column tb_user.userpwd is '密码';
comment on column tb_user.age is '年龄';
comment on column tb_user.gender is '性别';
comment on column tb_user.email is '邮箱';
comment on column tb_user.regtime is '注册日期';

创建表（同时创建约束+默认名称)

create table  tb_user(
  --主键约束
  userid number(5) primary key,
  --非空约束
  username varchar2(30) not null, 
  --检查约束 
  userpwd varchar2(20) check(length(userpwd) between 4 and 18), 
  --默认约束  检查约束
  age number(3) default(18) check(age>=18),
  --默认约束  检查约束
  gender char(3) default('男') check(gender in('男','女')),
  --唯一约束
  email  varchar2(30) unique,
  --默认约束
  regtime date default(sysdate)
);

创建表（同时创建约束+指定名称） 在字段的后面指定约束名直接添加

create table  tb_user(
  --主键约束
  userid number(5) constraint pk_user primary key,
  --非空约束
  username varchar2(30) constraint nameNotNull not null, 
  --检查约束 
  userpwd varchar2(20) constraint ckPwd check(length(userpwd) between 4 and 18), 
  --默认约束  检查约束
  age number(3) default(18) constraint ck_age check(age>=18),
  --默认约束  检查约束
  gender char(3) default('男') check(gender in('男','女')),
  --唯一约束
  email  varchar2(30) unique,
  --默认约束
  regtime date default(sysdate)
);

创建表（同时创建约束+指定名称） 在结构结束之前为当前表中指定字段添加约束

create table  tb_user(
  userid number(5),
  username varchar2(30),
  userpwd varchar2(20) constraint nn_user_pwd not null , 
  age number(3) default(18) ,
  gender char(3) default('男'),
  email  varchar2(30),
  regtime date default(sysdate),

  constraint pk_user_id  primary key (userid),
  constraint name_not_null check(username is not null),
  constraint ck_user_name check(length(username)between 4 and 20) , 
  constraint ck_user_pwd check(length(userpwd) between 4 and 18), 
  constraint ck_user_age check(age>=18),
  constraint ck_user_gender  check(gender in('男','女')),
  constraint uq_user_email unique(email)
);

--追加约束
alter table tb_user add constraint pk_user_id  primary key (userid);
alter table tb_user add constraint ck_user_name check(length(username)between 4 and 20) ; 
alter table tb_user add constraint ck_user_pwd check(length(userpwd) between 4 and 18); 
alter table tb_user add constraint ck_user_age check(age>=18);
alter table tb_user add constraint ck_user_gender check(gender in('男','女')); 
alter table tb_user add constraint uq_user_email unique(email);
--默认与非空
alter table tb_user modify age default 18;
alter table tb_user modify username not null;

--已有表中拷贝结构
--create table 表名 as select 字段列表 from 已有表 where 1!=1; 
--拷贝结构 emp
create table emp_his as select ename,sal from emp where 1!=1; 
--拷贝结构 emp +数据
create table emp_his2 as select ename,sal from emp where sal>2000;

drop删除表

删除 （先删除从表 再删除主表 ；同时删除约束）

drop table emp_his;

主外键关系下的两张表的删除 :

删除从表 : 直接删除 drop table student
删除主表 : 不能直接删除    
   1)先删除所有从表,再删除主表
   2)删除主表的同时级联删除主外键约束 drop table clazz cascade constraints;

drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;
drop table student cascade constraints;

外键约束

create table clazz(
    cid number(5)  primary key,
    cname varchar2(30)
)

create table student(
    sid number(5)  primary key,
    sname varchar2(30) not null,
    --所在班级编号
    --外键约束
    -- 1)cid number(5) references clazz(cid)
    -- 2)cid number(5) constraint fk_cid references clazz(cid)
    cid number(5)
    --3) constraint fk_cid foreign key(cid) references clazz(cid) 
)
--4)alter table student add constraint pk_stu_cid  foreign key(cid) references clazz(cid) ;

物理约束 : 表结构上为字段添加约束,推荐主外键约束可以定义物理约束
逻辑约束 : 在后端逻辑代码上添加判断,逻辑,检查等约束建议定义为逻辑约束

--测试数据 
insert into tb_user values(111,null,'1234',18,'女','xxxx',sysdate);
select * from student;

alter修改表结构

修改表名

rename student to haha_stu;

9.视图与索引

视图 : 建立在结果集与表之间的虚拟表

物理视图 : 真是存储数据
逻辑视图 : 不会真是存储数据,数据来资源数据源

  简化封装sql

权限问题 :

登录sys管理员账号
授权: grant dba to 账户名;
回收: revoke dba from 账户名;

--create or replace view 视图名 as select语句 [with read only];
create or replace view vw_xixi_haha as select empno,ename,sal,deptno from emp with read only;

select * from vw_xixi_haha;

--修改数据源中的数据
update emp set sal  = 1000 where empno = 7369;

update vw_xixi_haha set sal  = 800 where empno = 7369;

--删除试图
drop view vw_xixi_haha;

--查询每个部门的经理人平均薪资最小的部门名称
--1)找到所有的经理人
select distinct mgr from emp where mgr is not null;
--2）对所有经理人按照部门分组,求平均工资
select deptno, avg(sal) avg_sal
  from emp
 where empno in (select distinct mgr from emp where mgr is not null)
 group by deptno;
--3) 找到最小平均工资
select min(avg(sal))
  from emp
 where empno in (select distinct mgr from emp where mgr is not null)
 group by deptno;

--4) 哪一个部门的平局工资与最低平均工资相等,获取部门编号
select deptno
  from (select deptno, avg(sal) avg_sal
          from emp
         where empno in (select distinct mgr from emp where mgr is not null)
         group by deptno)
 where avg_sal = (最小平均薪资);
 
select deptno
  from (select deptno, avg(sal) avg_sal
          from emp
         where empno in (select distinct mgr from emp where mgr is not null)
         group by deptno)
 where avg_sal =
       (select min(avg(sal))
          from emp
         where empno in (select distinct mgr from emp where mgr is not null)
         group by deptno);
--5) 根据部门编号查询部门名称
select dname
  from dept
 where deptno =
       (select deptno
          from (select deptno, avg(sal) avg_sal
                  from emp
                 where empno in
                       (select distinct mgr from emp where mgr is not null)
                 group by deptno)
         where avg_sal =
               (select min(avg(sal))
                  from emp
                 where empno in
                       (select distinct mgr from emp where mgr is not null)
                 group by deptno));


create or replace view vw_mgr as select distinct mgr from emp where mgr is not null;

--通过视图简化
select dname
  from dept
 where deptno = (select deptno
                   from (select deptno, avg(sal) avg_sal
                           from emp
                          where empno in (select * from vw_mgr)
                          group by deptno)
                  where avg_sal = (select min(avg(sal))
                                     from emp
                                    where empno in (select * from vw_mgr)
                                    group by deptno));

索引

1. 提高查询效率
2. 相当于目录
3. 索引是透明的,一个字段上是否添加了索引对字段的使用没有任何影响
4. 大数据量前提下,做查询才会提高效率,如果频繁做增删,反而会降低效率,索引也是数据库的对象之一,需要维护
5. 唯一性较好,少量修改,大量查询适合添加索引
6. oracle数据库自动为主键添加索引

select * from emp where sal>1500;
--添加索引
create index index_sal on emp(sal);
drop index index_sal;     

10.事物

概念：事务是指作为单个逻辑工作单元执行的一组相关操作。这些操作要求全部完成或者全部不完成。使用事务是为了保证数据的安全有效。
要求了解: 什么是事务,事务作用,事务特点|特性,事务隔离级别,幻读,脏读…,事务的成功与失败

事务的特点

事务有一下四个特点：（ACID）

1. 原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。
2. 一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。
3. 隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。
4. 持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，也不应该丢失。 

事务的隔离级别

丢失更新：两个事务同时存储， 一个存储 100 ， 一个存储 200，最终可能至存储了 200 或者 100，那另一个的更新就没成功，即结果不为预想的 300

脏读：事务 T1 更新了一行数据，还没有提交所做的修改，T2 读取更新后的数据，T1回滚，T2 读取的数据无效，这种数据称为脏读数据。

不可重复读：事务 T1 读取一行数据，T2 修改了 T1 刚刚读取的记录，T1 再次查询，发现与第一次读取的记录不相同，称为不可重复读。

幻读：事务 T1 读取一条带 WHERE 条件的语句，返回结果集，T2 插入一条新纪录，恰好也是 T1 的WHERE 条件，T1 再次查询，结果集中又看到 T2 的记录，新纪录就叫做幻读。

事务的开启

执行DML中的insert delete update默认开启事务

事务的结束

成功

手动提交 ：使用 commit
正常执行完成的 DDL 语句：create、alter、drop
正常执行完 DCL 语句 GRANT、REVOKE
正常退出的 SQLPlus 或者 SQL Developer 等客户端
如果人工要使用隐式事务，SET AUTOCOMMIT ON (只针对一个连接

失败

意外退出,强制断电
手动提交  ：使用 rollback

insert into dept values(50,'后勤部','北京');
create table xixi(
       id number(5)
)、
select * from dept;

11.DML

1.Insert 向数据表张插入一条记录
2.Delete 删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是，它的操作对象仍是记录
3.Update 用于修改已存在表中的记录的内容

10.1. insert 插入

insert into 表名 [(字段列表)] values(值列表); 添加记录

--insert into 表名 values(值列表);
insert into  dept values(60,'教学部','上海');
--insert into 表(指定列) values(值列表);
insert into dept(deptno,dname) values(70,'营销部');
--insert into 表名 select 查询列 from 源表 where 过滤数据; 
insert into emp_his2 select ename,sal from emp where deptno = 30;
--insert into 表(指定列) select 查询列 from 源表 where 过滤数据; 
insert into emp_his2(ename) select ename from emp where deptno = 10;

select * from dept;
select * from emp;
select * from emp_his2;
delete from emp_his2;

10.2 .update 更新修改

update 表名 set (字段列表) =(select 字段列表 from 源表 where 过滤源表记录) where 更新记录的条件手动更改字段值：

--update 表名 set 字段=值 [,....] where  过滤行记录;
update dept set loc='三亚' where deptno = 70;
update dept set dname='A'||dname,loc='青岛' where deptno in (50,60,70);

--update 表名 set (字段列表) =(select 字段列表 from 源表 where 过滤源表记录) where 更新记录的条件手动更改字段值：
update dept set (dname,loc) =(select ename,job from emp where empno=7369) where deptno in (50,60);

10.3.delete

delete [from] 表名 where 过滤行记录
说明：

1. delete 可以删除指定部分记录，删除全部记录
2. 记录上存在主外键关联时， 删除存在关联的主表的记录时,注意 参考外键约束， 约束强制不让删除
先删除从表 再删除主表

-删除一个或多个或所有数据
--delete from 表名; 删除一张表中所有数据
delete from emp_his2;
----delete from 表名 where 行过滤条件; 满足条件的数据删除
delete dept where deptno = 70;

--删除主外键约束关系下的两张表中的数据:
       --从表中的数据可以直接正常删除 emp
       delete from emp where empno = 7369;
       --删除主表中数据 : 
           --主表中没有被从表引用的数据,可以直接删除
           delete from dept where deptno = 50;
           --主表中已被从表引用的数据,不能直接删除
           delete from dept where deptno = 10;
                  --解决方案: 
                       --1.删除从表中引用了当前主表数据的那些从表删除,然后再删除当前主表数据->默认
                       delete from emp where deptno = 10;
                       delete from dept where deptno = 10;
                       --2.删除主表数据的同时,为从表所有引用了当前主表数据的那些从表数据的外键字段设置为null
                              --需要为从表中外键字段设置约束的时候 on delete set null
                                delete from clazz where cid = 55;
                       --3.删除主表数据的同时,直接删除从表所有引用了当前主表数据的那些从表数据
                              --需要为从表中外键字段设置约束的时候 on delete cascade
                                delete from clazz where cid = 55;

10.4. 截断数据

truncate: 截断所有的数据 ,如果截断的是主表，结构不能存在外键关联，截断数据同时从结构上检查

truncate table tb_student;
select * from tb_student;

截断数据与删除数据区别 truncate 与delete 区别

1、truncate -->ddl ，不涉及事务，就不能回滚
delete -->dml ,涉及事务，可以回滚
2、truncate 截断所有的数据 delete 可以删除全部 或者部分记录
3、truncate从结构上检查是否存在主外键，如果存在，不让删除
delete 从记录上检查是否存在主外键，如果存在，按参考外键约束进行删除。

序列

  工具 ,管理流水号
 管理类似主键字段的值,数值型的,有变化规律的
 序列没有与表与字段绑定，序列的 删除不影响之前的使用 
 第一次使用序列需要先获取下一个最新值

--create sequence 序列名  start with  起始值  increment by 步进; 
 create sequence seq_deptno start with 70  increment by 1; 
 --当前值 序列名.currval
 select seq_deptno.currval from dual;
 --最新值 序列名.nextval
 select seq_deptno.nextval from dual;
 
 --删除
 drop sequence seq_deptno;
 
 select * from dept;
 
 insert into dept values(seq_deptno.nextval,'','');