oracle

sqlplus sys/itcast@localhost:1521/xe as sysdba

对于简化版本：
　　数据库实例服务和监听服务必须启动，其他都可以禁用。(如果安装到本机，那么建议全手动。)

新手如何记忆这两个服务的名字呢？
　　数据库实例服务：Oracle+Service+SID，简化版的Oracle默认的sid是xe(邪恶)，完整版的Oracle默认的SID是orcl。
　　监听服务：Oracle+…+TNSListener。
手动启动或重启服务的顺序问题：
　　建议先启动实例服务：OracleServiceXE，再启动监听服务

jdbc连接oracle

// 1.注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.获取连接对象
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "sys as sysdba";// 普通用户不需要小尾巴,只有超管用户需要添加 as sysdba
String password = "itcast";

三种登录身份方式 Normal sysdba sysoper

　　默认情况下，完整版的Oracle安装完成后，只解锁有效两个用户：sys,system， scott用户需要解锁。而简化版的Oracle安装完成之后，无scott用户

数据类型
　　整型 Number(n)
　　小数 Number(m,n)
　　字符串（固定长度）char(n)
　　字符串（可变长度）varchar2(n)
　　日期 date

　　引号的问题。Oracle中如何选择单引号和双引号呢？基本上，只要是别名或不需要Oracle解析（运算）的字符串，用双引号，剩下的都用单引号（比如字符串）。

--需求：查询所有员工的月薪（月薪=基本工资+奖金）
　　SELECT ename, sal+comm 月薪  FROM emp;--原因：与null运算的结果都是null

SELECT * FROM emp WHERE hiredate ='17-12月-80';--数据库默认的日期格式，
SELECT * FROM emp WHERE ename LIKE '%\_%' ESCAPE '\';--用ESCAPE来声明一个转义字符，语句中，该转义字符之后的字符，都作为普通字符来处理。

--需求2：查询10号和20号以及没有部门的员工部门的员工；
SELECT * FROM emp WHERE deptno  IN(10,20) OR deptno IS null;

--需求3：查询不是10号和20号以及没有部门的员工部门的员工；
--deptno!=10 AND deptno!= 20 AND deptno IS NOT NULL --与的关系 ,只要有一个不满足 返回就是false
SELECT * FROM emp WHERE deptno  NOT IN(10,20) AND deptno IS NOT null;

where condition1 and condition2
　　答案：先执行2，再执行1。
原因是：
　　where条件的解析顺序：从右到左
　　SQL优化：（where条件特别多的情况下，就有效果了）
　　对于and，应该尽量把假的放到右边。
　　对于or，应该尽量把真的放到右边。

SELECT ename 姓名,sal*12 年薪 FROM emp ORDER BY 2 ASC;--根据列号：第二列

--需求：根据基本薪资的年薪倒序序排列
SELECT ename,sal*12 FROM emp ORDER BY sal *12 DESC NULLS LAST;--排序列的空值放在最后

单行函数
　　字符函数 LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD RPAD TRIM replace
数字函数 ROUND TRUNC MOD
日期函数 MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND TRUNC
转换函数
【注意】和java不同，Oracle的日期格式对大小写不敏感。
三个转换函数的语法：
　　将日期或数字转换成字符 TO_CHAR
　　将字符转换成日期 TO_DATE
　　将字符转换成数字 TO_NUMBER

　　滤空函数 NVL NVL2 NULLIF COALESCE nullif(a,b),当a=b的时候，返回null，否则返回a
条件表达式 CASE表达式 DECODE函数

--需求1：显示今天的完整日期，结果参考：“2015-07-06 11:07:25”。
SELECT to_char(SYSDATE,'yyYy-MM-dd HH24:mi:ss') FROM dual;--oracle的日期格式和java的日期格式不一样,
--oracle的日期格式不区分大小写
--需求2：显示今天是几号，不包含年月和时间，结果参考：“8日”。
SELECT to_char(SYSDATE,'dd')||'日' FROM dual;
--需求3：显示当月最后一天是几号，结果参考：”30“。
SELECT to_char(last_day(SYSDATE),'dd') FROM dual;
--需求4：xiaoming的入职日期是2015-03-15，由于其入职日期当时忘记录入，现在请将其插入到emp表中。
UPDATE emp SET hiredate=to_date('2015-03-15','yyyy-mm-dd') WHERE ename = 'xiao_ming';
SELECT * FROM emp;

--需求5：查看今天是星期几
SELECT to_char(SYSDATE,'day') FROM dual;

--需求：查询员工的薪水，格式要求：两位小数，千位数分割，本地货币代码。
SELECT ename,sal,to_char(sal,'L99,999.00') FROM emp;
SELECT ename,sal,to_char(sal,'L00,000.00') FROM emp;

--需求：查询员工的月收入（基本薪资+奖金）
SELECT ename,sal+nvl(comm,0) 月收入 FROM emp;
SELECT ename ,NVL2(sal,sal,0)+nvl(comm,0) FROM emp;--为了小明
SELECT coalesce(NULL,NULL,1,2) FROM dual;--返回第一个不为空的值


--需求:要将工种job的英文转换为中文
SELECT ename,job,
DECODE(job,'CLERK','职员','SALESMAN','销售人员','MANAGER','经理','其他工种') FROM emp;

SELECT ename,job,
 CASE job WHEN 'CLERK' THEN '职员'
    WHEN 'SALESMAN' THEN '卖男'
   ELSE '其他工种'
 end
 FROM emp;

case子句增强
SELECT ename,job,sal,
  CASE WHEN sal<1000 THEN '工资过低' 
       WHEN sal BETWEEN 1000 AND 5000 THEN '工资适中'
         when sal IS NULL THEN '没工资酱油瓶'
         ELSE '工资太高'
  END
FROM emp;

Decode和Case的使用选择：
　　在Oracle中，翻译值的这种条件判断，优先使用decode，因为简单明了，且Oracle有一定的优化；更复杂的条件判断或者其他的关系型数据库，只能使用Case子句。

多行函数
多行函数会自动滤空

--需求：统计计算员工的平均奖金。（不同需求不同结果）
SELECT AVG(comm) FROM emp;--统计的是有奖金的人的平均奖金
--相当于
SELECT SUM(comm)/COUNT(comm) FROM emp;--多行函数会自动滤空
--统计所有人的平均奖金
SELECT AVG(nvl(comm,0)) FROM emp;

count的使用注意点

--需求：统计员工的数量，要求使用count的多种统计方式，并分析原因。
SELECT COUNT(*) FROM emp;--效率最低，全表全字段扫描
SELECT COUNT(empno) FROM emp; --按照主键列来统计--效率也挺高，语法角度来说，不通用
SELECT COUNT(1) FROM emp;--统计的是字符是1的这一列，效率高（原因，这一列只有一个字符，运算的时候，数据流很小，而且是固定列）
SELECT 1,ename FROM emp;
SELECT COUNT(11111111111) FROM emp;--统计的参数不是列号

distinct嵌套函数
　　distinct可用来过滤掉多余的重复记录只保留一条，但往往只用 它来返回不重复记录的条数，而不是用它来返回显示不重记录的所有值。因此，一般和count配合使用，作为统计非空且不重复的记录数
　　DISTINCT关键字效率会比较低，如果仅仅是为了显示不重复的记录，建议使用group by；
　　关于聚合函数的思考
　　select deptno,max(sal) from emp;

　　因为聚合函数处理的是数据组，在本例中，MAX函数将整个EMP表看成一组，而deptno的数据没有进行任何分组，因此SELECT语句没有逻辑意义。
要想解决这个问题，需要对deptno进行分组。

分组子句的要求
　　在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。
　　反之，包含在 GROUP BY 子句中的列不一定包含在SELECT 列表中

where和having在性能上的选择：
分析：
　　使用having子句过滤，是先分组，再过滤，注意：分组的时候是全表扫描的，效率较低。
　　使用where子句过滤，是先过滤再分组，注意：分组的时候仅需要扫描部分数据，效率较高。

　　从语法上看，两者选择简单归纳为，就是group by分组之后需要的条件中有组函数的，就必须得用having，其他都可以直接用where。
　　从性能上看，实际开发中，使用分组的时候尽量先加一个where的过滤条件。没有组函数的情况下，尽量选择where。

select deptno,avg(sal) from emp t group by deptno having avg(sal)>20 and deptno!=10;
select deptno,avg(sal) from emp t where t.deptno!=10 group by deptno having avg(sal)>20;

多表连接

根据连接方式的不同，Oracle的多表关联的类型分为：
　　内连接、外连接、自连接。
　　内连接分为：等值内连接、不等值内连接
　　外连接分为：左外连接、右外连接、全外连接

自连接是一种特殊的关联，可以包含内连接和外连接的连接方式。

--需求：查询一下员工信息，并且显示其部门名称
SELECT * FROM emp t1,dept t2 WHERE t1.deptno=t2.deptno;--等值内连接，数据库的私有扩展语法：隐式内连接（mysql，oracle都支持）
SELECT * FROM emp t1 INNER JOIN dept t2 ON t1.deptno=t2.deptno;--sql99语法，显式内连接(所有符合sql99规范的都支持)

--需求:查询员工信息,要求显示员工的编号、姓名、月薪、工资级别。
SELECT * FROM emp t1,salgrade t2 WHERE t1.sal >=t2.losal AND t1.sal<=t2.hisal;--隐式语法
SELECT * FROM emp INNER JOIN salgrade ON emp.sal >=salgrade.losal AND emp.sal <=salgrade.hisal --sql99

--查询"所有"员工信息，要求显示员工号，姓名 ,和部门名称--要求使用左外连接
SELECT * FROM emp t1 LEFT OUTER JOIN dept t2 ON t1.deptno=t2.deptno;--sql99标准语法
SELECT * FROM emp t1,dept t2 WHERE t1.deptno=t2.deptno(+);--oracle私有语法（mysql不支持），+放到右边是左外，你可以认为(+)是附加补充的意思。--要求查询所有的信息的表，我们可以称之为主表，而补充信息的表，称之为从表

SELECT * FROM emp t1 RIGHT OUTER JOIN dept t2 ON t1.deptno=t2.deptno;--sql99--右外连接--右边表（dept）数据全部显示。
SELECT  * FROM emp t1,dept t2 WHERE t1.deptno(+)=t2.deptno;--oracle语法，右外连接
+到底是放在条件哪边?左外连接的+放在右边,右外连接的+放在左边.----记忆的方法:(+)放在从表的一方,起到数据附加的作用.

--全外连接
SELECT * FROM emp t1 LEFT OUTER JOIN dept t2 on t1.deptno=t2.deptno
UNION
SELECT * FROM emp t1 RIGHT JOIN dept t2 ON t1.deptno=t2.deptno;
SELECT * FROM emp t1 FULL OUTER JOIN dept t2 ON t1.deptno=t2.deptno;
--sql99语法，Oracle没有私有扩展的语法。而且，mysql没有全外

--查询员工信息，要求同时显示员工和员工的领导的姓名  自连接
SELECT *  FROM emp t1,emp t2 WHERE t1.mgr=t2.empno;
--查询“所有”员工信息，要求同时显示员工和员工的领导的姓名 自连接 以下相当于左连接
SELECT *  FROM emp t1,emp t2 WHERE t1.mgr=t2.empno(+);
自连接是一种特殊的多表连接方式,其实含有内连接和外连接的操作.
注意问题:你也要注意笛卡尔集的产生.

子查询

--【需求】谁的工资比scott高？
--多表关联查询：自连接的不等值连接
SELECT * FROM emp t1,emp t2 WHERE t2.ename='SCOTT' AND t1.sal>t2.sal
--子查询
SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='SCOTT');

单行操作符（> = <）对应单行子查询，多行操作符(in,not in)对应多行子查询。

注意：使用子查询的时候，一定要保证子查询不能为空，否则数据就会出现异常。

--需求:查询工资比30号部门任意一个员工的工资高的员工信息。--面试题
--任意一个：比最低的那个高就ok。
SELECT * FROM emp WHERE sal >(SELECT MIN(sal) FROM emp WHERE deptno=30);
--any(多行函数)
SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);
--【示例】需求:查询工资比30号部门所有员工的工资高的员工信息。
SELECT * FROM emp WHERE sal>(SELECT MAX (sal) FROM emp WHERE deptno=30);
--all(多个返回记录)--max(sal)
SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

子查询的位置：可以放在主查询的where、select、having、from的后面。不可以放在主查询的group by后面。

子查询和主查询可以是同一张表，也可以不是是不同一张表，只要子查询返回的结果在主查询中能使用即可。

关于使用操作符：单行操作符对应单行子查询，多行操作符对应多行子查询。
执行顺序：一般子查询先执行，再执行主查询；

关于排序：一般不在子查询中使用order by；但在top-N分析问题中，必须在子查询中使用order by。

多行子查询一般用于from后面，作为一张新的虚拟临时表来使用

虚拟临时表是临时表的一种，是运行过程中，内存中虚拟出来的一张临时表，用于sql的操作。

--虚拟表
SELECT * FROM
(
SELECT * FROM emp WHERE deptno=30 --虚表:将查询结果再作为一张表来使用。
) t
WHERE sal>2000

子查询和多表关联查询的选择

　　理论上，在都可以实现需求的情况下尽量选择多表查询。
　　原因：子查询会操作两次，多表查询只操作一次。多表的效率高。
　　但要注意的是，多表查询如果产生了笛卡尔集（语句上要注意条件的使用），则会出现严重的效率问题。
　　一般不在子查询中使用排序（order by），但在top-N分析问题中必须在子查询中使用排序。

伪列
　　伪列是在ORACLE中的一个虚拟的列。
　　列的数据是由ORACLE进行维护和管理的，用户不能对这个列修改，只能查看。
　　所有的伪列要得到值必须要显式的指定。

最常用的两个伪列：rownum和rowid。
　　ROWNUM（行号）：是在查询操作时由ORACLE为每一行记录自动生成的一个编号。
　　每一次查询ROWNUM都会重新生成。（查询的结果中Oracle给你增加的一个编号，根据结果来重新生成）
　　rownum永远按照默认的顺序生成。（不受orderby的影响）
　　rownum只能使用< <=，不能使用> >=符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。

–需求：查询出所有员工信息，并且显示默认的行号列信息。
　　SELECT ROWNUM,t.* FROM emp t;–* 和指定的列一起显示的时候，必须加别名
　　ROWNUM是由数据库自己产生的。
　　ROWNUM查询的时候自动产生的。

行号的排序
　　–需求：查询出所有员工信息，按部门号正序排列，并且显示默认的行号列信息。
SELECT ROWNUM,t.* FROM emp t ORDER BY deptno;–order by 的原理：将查询结果（此时行号已经有了，已经和每一行数据绑定了）进行排序。
　　- –order by是查询语句出来的结果之后再排序的，，rownum是在查询出来结果的时候产生。order by不会影响到行号
　　–先排序，再查询
　　
SELECT ROWNUM,t.* FROM
(
SELECT * FROM emp ORDER BY deptno
) t

结论：
　　order by排序，不会影响到rownum的顺序。rownum永远按照默认的顺序生成。
　　所谓的“默认的顺序”，是指系统按照记录插入时的顺序（其实是rowid）。

--根据工资的高低排序后查询第二页:
--先排序:
SELECT * FROM emp ORDER BY sal DESC NULLS LAST;
--查询出顺序的rownum,过滤出小于的那部分数据:(尽量保证子查询数据量足够小);
SELECT ROWNUM,t.* FROM (SELECT * FROM emp ORDER BY sal DESC NULLS LAST) t WHERE ROWNUM<=6;
SELECT * FROM(SELECT ROWNUM r,t.* FROM (SELECT * FROM emp ORDER BY sal DESC NULLS LAST) t WHERE ROWNUM<=6) WHERE r>=4;

【分析原因】：
　　rownum只能使用< <=，不能使用> >=符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。
【提示】：
　　如何记忆编写Oracle的分页？建议写的时候从里到外来写，即先写小于的条件的子查询（过滤掉rownum大于指定值的数据），再写大于的条件的查询（过滤掉rownum小于的值）。
　　Oracle的分页中如果需要排序显示，要先排序操作,再分页操作。(再嵌套一个子查询)
　　性能优化方面:建议在最里层的子查询中就直接指定字段或者其他的条件，减少数据的处理量。

ROWID
　　使用insert语句插入数据时，oracle会自动生成rowid并将其值与表数据一起存放到表行中。
　　这与rownum有很大不同，rownum不是表中原本的数据，只是在查询的时候才生成的。

作用：
这里列举两个常见的应用：
去除重复数据。–面试题—了解
在plsql Developer中，加上rowid可以更改数据。

关于主键和rowid的区别：
　　相同点：为了标识唯一一条记录的。
　　不同点：
　　　主键：针对业务数据，用来标识不同的一条业务数据。
　　　rowid：针对具体数据的，用来标识不同的唯一的一条数据，跟业务无关。
　　　提示：是否能使用工具修改，主要看语句有没有rowid。
　　　需求：删除表中的重复数据，要求保留重复记录中最早插入的那条。（DBA面试题）

INSERT INTO TEST VALUES(1,'xiaoming');
INSERT INTO TEST VALUES(2,'xiaoming');
INSERT INTO TEST VALUES(3,'xiaoming');
INSERT INTO TEST VALUES(4,'Rose');
INSERT INTO TEST VALUES(5,'Rose');
COMMIT;
--剔除重复数据
SELECT * FROM TEST WHERE ROWID NOT in(SELECT MIN(ROWID) FROM TEST GROUP BY NAME);
DELETE TEST WHERE ROWID NOT in(SELECT MIN(ROWID) FROM TEST GROUP BY NAME);

这里为什么叫rowid的作用？难道只有有rowid的情况下才能实现这样的功能？
测试如果是mysql的话同样的逻辑也是能实现的

delete from test where id not in (select id from test group by name);
[SQL]delete from test where id not in (select id from test group by name);
[Err] 1093 - You can’t specify target table ‘test’ for update in FROM clause

数据处理

--需求：将dept表中部门"名称"不为空的数据都插入到test表中
INSERT INTO TEST(ID,NAME) SELECT deptno,dname FROM dept;--select的结果必须能插入到目标表中。（字段个数要对应、字段类型要对应）
INSERT INTO TEST SELECT deptno,dname FROM dept ;--必须前后字段对应
--非法使用批量插入
INSERT INTO TEST SELECT deptno,dname,loc FROM dept ;

delete和truncate的区别

　　delete逐条删除，truncate先摧毁表，再重建 。
　　最根本的区别是：delete是DML（可以回滚,还能闪回），truncate是DDL（不可以回滚 ，后面的所事务会讲回滚）
　　Delete不会释放空间，truncate会（当确定一张表的数据不再使用，应该使用truncate）
　　delete会产生碎片，truncate不会。

高水位线英文全称为high water mark，简称HWM

　　Oracle的事务是如何开启的？
　　Oracle的事务默认是手动管理事务，事务是自动开启（不需要显式的开启，隐式开启），但一般需要手动提交关闭。

Oracle事务的开始和结束的触发条件：
　　事务的开始：以第一个DML语句(insert update delete)的执行作为开始，即是自动开启的事务。
　　事务的结束（以下条件之一）：
　　显式结束：commit， rollback（还是隐式commit）
　　隐式结束（自动提交）：DDL（create table…）和DCL（所以不能回滚 ），exit(事务正常退出)
　　隐式回滚（系统异常终止）：关闭窗口，死机，掉电。

保存点
　　SAVEPOINT的作用：
　　使用 SAVEPOINT 语句在当前事务中创建保存点，语法：SAVEPOINT 保留点名称。
　　使用 ROLLBACK 语句回滚到创建的保存点。语法：ROLLBACK TO 保留点名称。

数据库对象
　　数据库对象，是数据库的组成部分，有表（Table ）、索引（Index）、视图（View）、用户（User）、触发器（Trigger）、存储过程（Stored Procedure）、图表（Diagram）、缺省值（Default）、规则（Rule）等。

复制表

–复制一张和原来一模一样的新表，包含数据
CREATE TABLE t_dept AS SELECT * FROM dept;
SELECT * FROM t_dept

–复制一张和原来一模一样的新表，不要数据，只要结构
CREATE TABLE t_dept_empty AS SELECT * FROM dept WHERE 1<>1;
SELECT * FROM t_dept_empty;

关于删除表
注意：
　　数据和结构都被删除。
　　所有正在运行的相关事物被提交。（ddl语句）
　　所有相关索引被删除。（表附属对象会被删除）
　　DROP TABLE 语句不能回滚，但是可以闪回。
　　完整的oracle数据库的版本的情况下，普通用户删除的表，会自动放入回收站

常见的约束类型
　　NOT NULL
　　UNIQUE
　　PRIMARY KEY
　　FOREIGN KEY
　　DEFAULT
　　CHECK—用来检查一个字段的值是否符合某表达式,表达式的结果必须是布尔值。
其中：check约束是Oracle特有的约束。

约束的应用选择
　　在应用开发中，主键约束一般要设置，其他如非空、唯一、默认值、检查等约束，可以根据实际情况来添加。而外键约束是否要设置，是存在一点争议的。（争议在性能上）
一般建议：
　　在大型系统中（性能要求不高，安全要求高），可以使用外键；在大型系统中（性能要求高，安全自己控制），不用外键；小系统随便。
不用外键的话，可以用程序控制数据一致性和完整性，可以在代码的数据层通过代码来保证一致性和完整性。
　　用外键要适当，不能过分追求。

序列-sequence

需求:
　　Mysql中主键有自增长的特性.
　　Oracle中,主键没有自增长这个特性.那么如何解决这个问题.使用序列可以解决.

序列：可供多个用户来产生唯一数值的数据库对象
　　自动提供唯一的数值
　　共享对象
　　主要用于提供主键值
　　将序列值装入内存可以提高访问效率

这个是Oracle特色的。Mysql是没有的。
　　简单的说，他可以用来高效的生成主键值。

在ORACLE中为序列提供了两个伪列：
　1. NEXTVAL 获取序列对象的下一个值（指针向前移动一个，并且获取到当前的值。）
　2. CURRVAL 获取序列对象当前的值

序列的裂缝
　也就是说，用序列插入数据库的值不一定是连续的。
补充：
　Mysql的自增长列也可以是不连续的.
　序列出现裂缝的条件：

* 事务回滚。
* 系统异常。
* 多个表同时使用同一个序列。

　　这个序列是公用的对象。如果你很在意的话，就一个表用一个序列，但大多数情况下，这个主键值（代理主键）没有什么意义的话，可以多个表公用一个序列。

预定义用户
　　Oracle预定义有很多用户，用于不同的用途。这些用户大都默认是禁用的（如scott，hr等），但有两个最重要的用户是默认开启的，这两个用户就是SYS和SYSTEM。

SYS 帐户(数据库拥有者):
　　拥有 DBA 角色权限
　　拥有 ADMIN OPTION 的所有权限
　　拥有 startup, shutdown, 以及若干维护命令
　　拥有数据字典
　　SYSTEM 帐户
　　拥有 DBA 角色权限.

注意：这些帐户通常不用于常规操作。
　Sys和system账户的区别：
　　sys用户是数据库的拥有者，是系统内置的、权限最大的超级管理员帐号。
　　system用户只是拥有DBA角色权限的一个管理员帐号，其实它还是归属于普通用户。

删除用户及其下面所有的对象
　　drop user itcasttest cascade;

提示，每个数据库用户帐户具备：
　　一个唯一的用户名
　　一个验证方法
　　一个默认的表空间
　　一个临时表空间
　　权限和角色

如何选择预定义的角色呢？
　　普通用户就选择：connect和Resource角色即可。
　　管理员用户选择：connect和Resource、dba角色。

跨域访问也称之为跨用户访问、跨方案访问，访问的方式为：用户名.对象名，
其他用户的表不属于本用户的空间
如果要查询其他用户下的表，要使用其他用户的用户名作为前缀

Oracle用户的权限分为两种:
　　系统权限(System Privilege): 允许用户执行对于数据库的特定行为，例如：创建表、创建用户等
　　对象权限(Object Privilege): 允许用户访问和操作一个特定的对象，例如：对其他方案下的表的查询
注意：
　　赋权的时候，只能是自己拥有的权限、或者该权限是可以传递的，才可以将其赋予别人。

视图
概念：
　　视图是一种虚表.
　　视图建立在已有表的基础上, 视图赖已建立的这些表称为基表。
　　向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
视图向用户提供基表数据的另一种表现形式

视图默认是可以对基表数据进行编辑的

视图和表的区别：
　　视图是实体表的映射，视图和实体表区别就是于视图中没有真实的数据存在。
　　WITH CHECK OPTION;–数据的增加和修改，必须满足子查询的条件
　　WITH READ ONLY;

跨域访问视图
　　只放开scott下的emp表的部分数据给itcast用户查询，开放的数据要求为：10号部门的员工，字段只显示员工号和姓名，且要求这两个字段的标题显示为中文

什么时候使用视图：
　　1. 在开发中，有一些表结构是不希望过多的人去接触，就把实体表映射为一个视图。
　　2. 在项目过程中，程序人员主要关注编码的性能、业务分析这方面。对于一些复杂的SQL语句，设计人员会提前把这些语句封装到一个视图中，供程序人员去调用

注意:在企业中，你查询的对象（表）他可能不是一张的表，可能是视图；你看到的视图的字段可能也不是真实的字段。

同义词 SYNONYM
　　同义词就是（对象的）别名，可以对表、视图等对象起个别名，然后通过别名就可以访问原来的对象了。
作用：
　　方便访问
　　缩短对象名字的长度
如果工作中,你遇到一张”表”来查询数据,那么它一定是表么?不一定,可能是视图,也可能是同义词.
另外，任何对象都能起别名

索引(INDEX)
概念:
　　简单的说，相当于一本书的目录。(数据库中的索引相当于字典的目录(索引)),它的作用就是提升查询效率。
　　
特性：
　　一种独立于表的模式（数据库）对象, 可以存储在与表不同的磁盘或表空间中。
　　索引被删除或损坏, 不会对表（数据）产生影响, 其影响的只是查询的速度。
　　索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引。
　　在删除一个表时, 所有基于该表的索引会自动被删除。
　　如果建立索引的时候,没有指定表空间,那么默认索引会存储在所属用户默认的表空间.
作用：
　　通过指针（地址）加速Oracle 服务器的查询速度。
　　提升服务器的i/o性能（减少了查询的次数）；

强制索引
　　强制索引对于超大的数据量的表来说是有一定的作用的，虽然它是全索引扫描，但扫描索引比扫描表速度还是快。
强制索引的使用注意：

1. 尽量少用强制索引，如何避免使用，条件上尽量不要用<>, like  ‘%aa%’
2. 如果要用强制索引，在非常大的数据量的情况下使用。

索引不是万能！
以下情况可以创建索引:
列中数据值分布范围很广
列经常在 WHERE 子句或连接条件中出现
表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%
下列情况不要创建索引:
表很小
列不经常作为连接条件或出现在WHERE子句中
表经常频繁更新（看需求，如果表经常不断的再更新，Oracle会频繁的重新改动索引，反而降低了数据库性能。但如系统日志历史表，就必须增加索引，效率超高）
面试题：
1. 索引的作用是什么？
　　主要是提高查询效率,减少磁盘的读写，从而提高数据库性能。
2. 创建索引一定能提高查询速度么？
　　未必！得看你创建的索引的合理性。
3. 索引创建的越多越好么？
　　不是！索引也是需要占用存储空间的，过多的索引不但不会加速查询速度，反而还会降低效率。

set define off
set feedback off

PL/SQL

如果想要在命令窗口运行，需要打开输出选项：set serveroutput on

--打印两个变量的值，两个变量的值分别采用两种不同的赋值方法：
DECLARE--声明变量
  --姓名
  v_name VARCHAR(20) :='Zhong';--声明的时候直接赋值
  --薪资
  v_sal NUMBER;
  --工作地点
  v_local VARCHAR(200);
BEGIN --开始程序逻辑
  --程序运行时赋值
  --方法一：--直接赋值
  v_sal :=9999;
  --方法二：语句赋值
  SELECT '上海' INTO v_local FROM dual;
  --输出打印
  dbms_output.put_line('姓名：'||v_name||',薪资：'||v_sal||'，工作地点：'||v_local); 
END;--程序结束

–查询并打印7839号（老大）员工的姓名和薪水

DECLARE
  --定义变量
  --姓名
  v_ename emp.ename%TYPE;--姓名使用的emp表中的ename的字段的数据类型
  --薪水
  v_sal emp.sal%TYPE;--你不需要关心具体什么数据类型了

BEGIN
   --赋值
  --注意：into前后字段名和变量名必须对应（不管是数据类型，还是个数，顺序）
  --必须：查询的结果必须只有一个值，不能有多行记录
   SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=7839;  
  --打印
  dbms_output.put_line('7839号员工的姓名是：'||v_ename||'，薪资'||v_sal);
END;

–查询并打印7839号（老大）员工的姓名和薪水

DECLARE
  --记录型变量
  v_emp emp%ROWTYPE;--该变量可以存储emp表中的一行记录
BEGIN
   --赋值
   --默认情况下，必须是全字段赋值
   SELECT * INTO v_emp FROM emp WHERE empno=7839;    
  --打印
  dbms_output.put_line('7839号员工的姓名是：'||v_emp.ename||'，薪资'||v_emp.sal);
END;

游标
使用引用型变量获取游标的值

–使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。

DECLARE
  --声明一个游标
  CURSOR C_EMP IS
    SELECT ENAME, SAL FROM EMP;
  --引用型变量
  V_ENAME EMP.ENAME%TYPE; --姓名
  V_SAL   EMP.SAL%TYPE; --工资

BEGIN
  --打开游标，执行查询
  OPEN C_EMP;
  --使用游标，循环取值
  LOOP  
    --获取游标的值放入变量的时候，必须要into前后要对应（数量和类型）
    FETCH C_EMP INTO V_ENAME, V_SAL;  
    EXIT WHEN C_EMP%NOTFOUND;  
    --输出打印
    DBMS_OUTPUT.PUT_LINE('员工的姓名：' || V_ENAME || '，员工的工资' || V_SAL);  
  END LOOP;
CLOSE c_emp ;--关闭游标，释放资源  
END;

使用记录型变量存值

--使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。
DECLARE
  --声明一个游标
  CURSOR C_EMP IS SELECT * FROM EMP;
  --记录型变量
v_emp emp%ROWTYPE;
BEGIN
  --打开游标，执行查询
  OPEN C_EMP;
  --使用游标，循环取值
  LOOP 
    --获取游标的值放入变量的时候，必须要into前后要对应（数量和类型）
    FETCH C_EMP INTO v_emp; 
    EXIT WHEN C_EMP%NOTFOUND; 
    --输出打印
    DBMS_OUTPUT.PUT_LINE('员工的姓名：' || v_emp.ename || '，员工的工资' || v_emp.sal); 
  END LOOP;
CLOSE c_emp ;--关闭游标，释放资源 
END;

定义带参数的游标

declare
   --定义游标--带参数的游标:需要定一个形式参数
   CURSOR c_emp(v_deptno emp.deptno%TYPE) IS SELECT ename,sal FROM emp WHERE deptno=v_deptno ;

   --声明变量
   v_ename emp.ename%TYPE;
   v_sal emp.sal%TYPE;
BEGIN
  --用
  --打开游标
  OPEN c_emp(10);
  --循环fetch
  LOOP     
      --取出数据
      FETCH c_emp INTO v_ename,v_sal;
--退出条件
      EXIT WHEN c_emp%NOTFOUND;
      --打印--写任何的逻辑
      dbms_output.put_line('姓名：'||v_ename||'，薪资：'||v_sal);  
  END LOOP;
  --关闭
  CLOSE c_emp;
end;

–使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。

DECLARE
  --声明一个带参数的游标
  CURSOR C_EMP(v_deptno emp.deptno%TYPE) IS SELECT * FROM EMP WHERE deptno=v_deptno;
  --记录型变量
  v_emp emp%ROWTYPE;
BEGIN
  --打开游标，执行查询
  --打开游标的时候需要传入参数
  OPEN C_EMP(20);
  --使用游标，循环取值
  LOOP 
    --获取游标的值放入变量的时候，必须要into前后要对应（数量和类型）
    FETCH C_EMP INTO v_emp; 
    EXIT WHEN C_EMP%NOTFOUND; 
    --输出打印
    DBMS_OUTPUT.PUT_LINE('员工的姓名：' || v_emp.ename || '，员工的工资' || v_emp.sal); 
  END LOOP;

  CLOSE c_emp ;--关闭游标，释放资源 
END;

存储过程

存储过程：就是一块PLSQL语句包装起来，起个名称
语法上:相当于plsql语句戴个帽子。
相对而言:单纯plsql可以认为是匿名程序。
存储作用：
　　在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源)。这种就需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.
　　ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)

提示：
　　plsql是存储过程的基础。
　　java是不能直接调用plsql的，但可以通过存储过程这些对象来调用。

如何调用执行，两种方法：
　　一种是是用exec命令来调用—用来测试存储
　　一种是用其他的程序（plsql和java）来调用

注意：
　　第一个问题：is和as是可以互用的，用哪个都没关系
　　第二个问题：过程中没有declare关键字，declare用在语句块中
存储可以带参数可以不带参数？其实都有应用.
　　不带参数的存储一般用来处理内部数据的。不需要输入参数也不需要结果的，是可以使用。

create or replace procedure p_queryempsal_out(i_empno IN emp.empno%TYPE,o_sal OUT emp.sal%TYPE) AS

BEGIN 
  SELECT sal INTO o_sal FROM emp WHERE empno=i_empno; 
end p_queryempsal_out;


DECLARE
  --输入参数值
  v_empno emp.empno%TYPE:=7839;
  --声明一个变量来接收输出参数
  v_sal emp.sal%TYPE;
BEGIN
  p_queryempsal_out(v_empno,v_sal);--第二个参数是输出的参数，必须有变量来接收！！
  --当上面的语句执行之后，v_sal就有值了。
  dbms_output.put_line('员工编号为：'||v_empno||'的薪资为：'||v_sal);
END;

小结：
存储过程作用：主要用来执行一段程序。
　　无参参数：只要用来做数据处理的。存储内部写一些处理数据的逻辑。
　　带输入参数：数据处理时，可以针对输入参数的值来进行判断处理。
　　带输入输出参数：一般用来传入一个参数值，我想经过数据库复杂逻辑处理后，得到我想要的值然后输出给我。

java程序调用存储过程

public class PTest {
     public static void main(String[] args) throws Exception {
           //1.注册驱动
           Class.forName("oracle.jdbc.driver.OracleDriver");
           // 2.获取连接对象
           String url = "jdbc:oracle:thin:@localhost:1521:xe";
           String user = "scott";// 普通用户不需要小尾巴,只有超管用户需要添加 as sysdba
           String password="tiger";
           Connection conn = DriverManager.getConnection(url, user, password);
           // 3.设置输入参数
           String sql="{call p_queryempsal_out(?,?)}";
           CallableStatement call = conn.prepareCall(sql);
           call.setInt(1, 7839);
           // 4.OUT参数的类型必须在执行存储过程之前进行注册
           call.registerOutParameter(2, OracleTypes.DOUBLE);

           // 5.执行存储过程
           call.execute();
           // 6.获取输出参数
           double sal = call.getDouble(2);
           System.out.println("sal:" + sal);
           // 7.释放资源
           conn.close();
     }
}

触发器

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

解释：
　　首先，它也是一段plsql程序。
　　然后，它是来触发与表数据操作相关的（insert，update，delete）。
　　然后，在进行表数据操作的时候，会自动触发执行的一段程序。
　　换句话说：触发器就是在执行某个操作（增删改）的时候触发一个动作(一段程序)。
　　有点像springMVC的拦截器，可以对cud增强。

语句级触发器和行级触发器区别：
　　在语法上，行级触发器就多了一句话：for each row
　　在表现上，行级触发器，在每一行的数据进行操作的时候都会触发。但语句级触发器，对表的一个完整操作才会触发一次。

简单的说：行级触发器，是对应行操作的；语句级触发器，是对应表操作的。

行级别触发器的伪记录变量
　　：new代表操作之后的数据，只出现在INSERT/UPDATE中，：old代表操作（cud）之前的那条数据，出现在UPDATE/DELETE，
　　INSERT时:NEW表示新插入的行数据，UPDATE时:NEW表示要替换的新数据，:OLD表示要被更改的原来数据，DELETE时:OLD表示要被删除的数据。

--涨工资：涨后的工资不能少于涨前的工资
create or replace trigger tri_checkempsal
  BEFORE UPDATE ON emp--更新之前拦截触发
  for each row--行级触发器
declare
BEGIN
      --如果涨后小于涨前，则，终止更新操作
  IF :new.Sal<:old.Sal THEN
    --终止程序继续运行，也就终止了更新操作了。
    raise_application_error(-20001,'涨后的工资不能少于涨前的工资！！涨前的工资：'||:old.Sal||'，涨后的工资：'||:new.sal);
    --相当于抛出异常（throw）,(使用了oracle内置的一个函数来抛出异常)
  END IF;
end tri_checkempsal;

数据的备份：
业务的原理：在更新或者删除数据的时候，将旧的数据备份出来到另外一张表中。

--数据的备份：
create or replace trigger tri_deptbak
  before UPDATE
  on dept
  FOR EACH ROW--行级触发器
declare

begin
 INSERT INTO depttemp VALUES(:OLD.DEPTNO,:OLD.DNAME,:OLD.LOC,SYSDATE);
 --COMMIT;
END ;

但是要注意：触发器会引起锁，降低效率！使用时要慎重。如无必要，尽量不要使用触发器。
　　行级触发器会引发行级锁（锁行数据）
　　语句级触发器可能会引起表级锁（锁表）

　　在插入数据的之前，自动插入主键值（值是序列）
思考:这个该用哪种触发器？行级触发器

实现了一个类似mysql的自增长主键的功能.

create or replace trigger tri_beforeInsert_t_testseq
  before insert
  on t_testseq
  for each row
declare
  -- local variables here
begin
  SELECT seq_test.nextval INTO :NEW.ID FROM dual;
end tri_beforeInsert_t_testseq;

数据字典
什么是数据字典？
　　Oracle的数据字典是Oracle数据库安装之后，自动创建的一系列表。
数据字典表和用户创建的表没有什么区别，不过数据字典表里的数据是Oracle系统存放的系统数据，而普通表存放的是用户的数据而已
　　对于数据字典表，里面的数据是由数据库系统自身来维护的。所以这里虽然和普通表一样可以用DML语句来修改数据内容，但是大家最好还是不要自己来做了，因为这些表都是作用于数据库内部的。所以这里我们切记记住不要去修改这些表里的内容。，所以，数据字典主要用来查询的。

命名规则
　　数据字典表的用户都是sys，存在在system这个表空间里，表名都用”$”结尾，为了便于用户对数据字典表的查询（这样的名字是不利于我们记忆的），所以Oracle对这些数据字典都分别建立了用户视图，不仅有更容易接受的名字，还隐藏了数据字典表与表之间的关系，让我们直接通过视图来进行查询，简单而形象，Oracle针对这些对象的范围，分别把视图命名为DBA_XXXX, ALL_XXXX和USER_XXXX，所以我们说的数据字典一般是指数据字典视图。

数据字典视图非常多，我们无法一一记住，但是有个视图，我们必须知道，那就是dictionary视图，该视图里记录了所有的数据字典视图的名称。所以当我们需要查找某个数据字典而又不知道这个信息在哪个视图里的时候，就可以在dictionary视图里找。该视图还有个同义词dict。

--需求1：查询所有数据字典的名称和描述

SELECT * FROM DICTIONARY;--视图
SELECT * FROM dict;--同义词

--需求2：我想查看当前用户下有哪些视图对象，但我不知道查询当前用户视图的数据字典的名称
SELECT * FROM DICTIONARY WHERE table_name LIKE UPPER('user_%view%');
SELECT * FROM User_Views;--当前用户下创建的所有视图


--需求：由于业务需要，查询当前用户下有没有emp这张表（如果没有就创建，有的话就直接插入数据）
SELECT * FROM user_tables WHERE table_name =UPPER('emp');

--我想看看emp表中有几列，将列名都打印出来
SELECT * FROM USER_TAB_COLUMNS WHERE table_name =UPPER('emp');

Oracle重点：

1. 单表的查询（强化）
2. 转换函数（to_char,to_date,to_number）
3. 条件表达式(函数):decode,case when then end;
4. 分页（rownum）--能力
5. 表的连接查询（强化）
6. 高水位（delete和truncation的区别）
7. 序列的创建和使用
8. 用户的创建和权限的配置（配置角色connection,resource------dba---注意hibernate跨域访问会出现问题）
9. 视图作用概念—面试
10. 索引----面试和基于数据库的优化
11. 存储过程—写报表、复杂的业务。java调用。
12. 游标
13. 触发器—面试

自己总结 oracle与mysql的区别

连接查询oracle有自己的语法
mysql主键可以自增 oracle使用序列
mysql适用于互联网项目 oracle适用于大数据量的传统项目
mysql有数据库的概念 oracle有表空间的概念
分页查询不一样

springcloud复习
之前几个项目整理 包括不限于springboot 慕课网上的
之前的上课视频还有需要看一下的
接下来准备项目2
再复习一下java基础中的重点 找一下多线程并发资料看看
面试宝典看一下