Oracle 数据库总结

Oracle 总结

1，简单SQL语句

1）distinct 关键字

在查询语句中的某个字段前面使用 distinct 关键字对重复的记录只显示一条记录，distinct 关键字只能放在 select 语句的第一个字段前

2）运算符

执行顺序： 比较运算符 --> NOT 条件取反 --> AND --> OR
BETWEEN AND 表示在 xxx 和 xxx 之间（包含头尾）

3）like 模糊查询

% 表示一个或多个字符
_ 表示一个字符
ESCAPE ‘’ 当需要使用特殊字符时，需要使用 ESCAPE 关键字进行转义， eg：select * from EMP where name LIKE ‘%%%’ ESCAPE ‘’
3）IS NULL
查询为空的字段

2，函数（不区分大小写）

1）字符函数

length（name）  返回字符的长度
max（money）	 返回最大值
min（money）   返回最小值
lower（name）  把字符转换成小写
upper（name） 把字符转成大写
initcap（name） 把首字符变成大写
concat（name1，name2）  连接 name1，和 name2 两个字符
count（name）  返回总记录数
substr（str，n1，n2）  从 str 中的第 n1 个字符开始截取，n2 个字符，如果不指定 n2，会默认截取到最后一个
nvl( a，b) 如果 a 为NULL，则NVL函数返回 b 的值，否则返回 a 的值，如果两个参数都为NULL ，则返回NULL。
instr（name，‘A’）  判断 name 字段中存在几个 A ，如果没有返回 0
LPAD（name，10，’ * ‘）   name字段显示10位数，不够时在 左边 以 * 号补齐
RPAD（name，10，‘ # ’）  name字段显示 10 位数，不够时在 右边 以 # 号补齐
TRIM（name）  去除字符串左右两边的空格
replace（name，‘ A '，’ a ' ）    把name字段中的 A ，替换成 a 返回

2）数字函数

ROUND（45.926，2）     四舍五入到指定的小数位数
TRUNC（45.926，2）   将值截断到指定的小数位数
MOD（1600，300）    返回相除后的余数

3）日期函数

DD-MON-YY      缺省使用的格式
世纪，年，月，日，小时，分钟，秒
日期类型的数据可以使用算术运算符进行计算
从日期中加或减一个数值，以得当一个新的日期结果值
两个日期相减返回相差的天数
注：日期不能 加 另一个日期，日期也不支持乘除运算

4）转换函数

隐式转换
字符串可以自动转换成数值型
手动转换（数值，字符，日期）

to_char（）  	数值型或日期型的数据转成字符型
to_char（date，‘ YYYY-MM-DD HH24：MI:SS: AM DAY '）    把时间转换成字符
to_char（name，）    
to_number（）    字符型转成数值型，字符的格式和模板的格式必须一致
to_date（）    字符型转成日期型

5）组函数（都是忽略空值的）

max（字段名）
min（字段名）
avg（字段名）		只能是数值型
sum（字段名）		只能是数值型
count（*）		返回查询数据的总条数
count（字段名）	这种情况忽略空值
GROUP BY  字段名
ORDER BY  字段名

注：对数据进行分组后，使用组函数

• 1，出现在查询列表中的字段，要么出现在组函数中，要么出现在 group by子句中
• 2，也可以只出现在 group by 中
• 3，对分组后的数据进行过滤，使用 HAVING 关键字
  示例：

	select  
		max（sal） 
	from emp  
	group by sal  
	having  max（sal） > 3000;

6）其他函数

NVL（comm，0）	   如果comm的值为null，则以 0 来参与计算
NVL2（comm，expr1，expr2）    如果comm的值不为 null 则显示表达式1，如果未 null 则显示表达式2
nullif（expr1，expr2）    比较两个表达式，如果相等返回空值，如果不等则返回第一个表达式
CASE  job  					使用 case 实现 if 。。。 else if 。。。 else 的功能
WHEN  ' clerk ' THEN
1.10 * SAL
WHEN  ' manager ’ THEN
1.3 * SAL
ELSE
SAL   
END  AS  " 修订工资 "

3，多表联查

3.1 SQL1992老标准

1）联表查询时，不使用 where 条件会出现 笛卡尔乘积，查询出来的记录数是两个表的条数的乘积
2）使用等值条件进行联表查询
3）使用非等值查询，两个表之间没有父子关系，用 != 连接两个表
eg：

select 
	e.empNo，e.eName，E.sal，S.grade，S.losal，s.hisal
from  EMP E，salgrad S
where E.sal BETWEEN S.losal AND S.hisal 

4）自连接
通过别名，将一个表虚拟成两个表，然后再这两个表上做等值查询
eg：

select  
	E.empNo，E.eName，M.empNo，MeName
from  EMP E，EMP M
where  E.empNo = M.empNo;

5）左外连接
把右边表中不满足等值条件的数据查询出来
eg：

select  
	E.empNo，E.eName，D.deptNo，D.loc_Id
from  EMP E，DEPT D
where E.deptNo （+） = D.deptNo;

6）右外连接
把左边表中不满足等值条件的数据查询出来
eg：

select  
	E.empNo，E.eName，D.deptNo，D.loc_Id
from  EMP E，DEPT.D
where E.deptNo = D.deptNo （+）;

3.2 SQL1999新标准

1）交叉连接
相当于SQL1992老标准中的等值查询的时候没有给出正确的等值条件，会产生笛卡尔乘积
eg：

select 
	E.*，D.*
from EMP E
CROSS  JOIN DEPT D;

2）自然连接
在父子表关系上，自动的匹配两个表中列名完全相同的字段（参照列），在这些相同的字段上做等值查询
在 select 的查询字段中，参照列上不能使用前缀。
自然连接的缺陷：1，会把所有的参照列作为等值条件。2，如果参照列的类型不同，查询会报错
当两个表中没有参照列的时候，自然查询会产生笛卡尔乘积

3）join 。。。using
在自然连接的基础上，加以改进，使用指定的参照列来作为等值条件
eg：

select  
	E.empNo，E.eName，deptNo，D.dname，D.loc_Id
from  emp E
join  DEPT D USING（deptNo）
where  E.empNo = 7369;

4）使用 join 。。。on
1，做等值查询，需要 n-1 个 join 。。。on
eg：

select  
	E.*，D.*，L.*
from  EMP E
JOIN DEPT D  ON （E.DEPTNO = D.DEPTNO）
WHERE  E.EMPNO = 7311

2，做非等值查询
eg：

select  
	E.EMPTNO，E.eName，S.geade，S.losal，S.hisal
 from  EMP E
 JOIN  salgrad  s  ON  （E.sal  BETWEEN S.losal  AND S.hisal）
 WHERE  E.empNo = 7369

5）外连接
1，LEFT OUTER JOIN 。。ON（）
可以把左边表中不满足等值条件的数据查询出来

select  
	E.empNo，E.ename，D.deptNo，D.dname
from emp E
LEFT  OUTER  JOIN dept  d ON（E.deptNo = D.deptNo）

2，RIGHT OUTER JOIN 。。ON（）
可以把右边表中不满足等值条件的数据查询出来
3，FULL OUTER JOIN 。。ON（）
可以把左右两边表的满足于不满足等值条件的数据都查询出来
eg：

select 
	E.empNo，E.eName，E.deptNo，D.deptNo，D.dName
 from EMP E
full  outer join DEPT D on （E.deptNo = D.deptNo）

6） UNION 把两个结果集合并成一个结果集
注：两个查询语句的结果集必须要保持一致才能合并，即查询字段的个数，字段的类型，字段的顺序，必须要一致。
UNION 去除重复的数据
eg：

select  
	* 
from dept_bak
union
select
	*
from dept

UNION ALL 不去除重复的数据
eg：

select  * from dept_bak
union  all
select * from dept

7）使用查询语句来创建表（复制表及其数据，但是并没有复制主外键）
eg：create table dept_bak
as

select * from dept

4，子查询

1）分类：根据子查询的返回结果来区分的
单行列子查询，要使用单行比较运算符（即： =  >  >=  <  <=  !=）
多行列子查询，不能使用单行比较运算符，要使用多行比较运算符可以使用 in，all，any
2）使用 in 运算符
select * from EMP E
where  E.job  IN  （select job from EMP where sal  > 3000）
3）使用 ALL 运算符
> ALL  大于子查询中的最大值
select * from EMP E
where  E.sal  > ALL （select sal from EMP where deptNo = 10）
< ALL  小于子查询中最小的值
select * from EMP E
where  E.sal  < ALL （select sal from EMP where deptNo = 10）
4）使用 ANY 运算符
>  ANY  大于子查询中最小的值
select * from EMP E
where  E.sal  > ALL （select sal from EMP where deptNo = 10）
<  ANY  小于子查询中最大的值
select * from EMP E
where  E.sal  > ALL （select sal from EMP where deptNo = 10）

5，DML语句

1）insert 语句
1，第一个执行的DML语句，会引发事物
在事物没有结束之前，只有当前用户可以看到数据库的修改操作，其他用户是看不到的
事物可以以回滚的方式结束，所有的操作被放弃，回滚到事物开始之前的状态
事物也可以以提交的方式结束，对数据库的修改被永久的保存，其他用户可以看到被修改的数据
2，插入语句
insert  into  DEPT（DEPTNO，DNAME） values （50，' 销售部  '）
3，不指定插入的字段，则表示要插入全部的字段，如果插入的字段不全则会报错
insert  into  DEPT values（40，' 开发部  '）
4，插入日期类型
1）insert into EMP VALUES（8000，'张1 '，'工程师'，'9-12月-1987'）
2）insert into EMP VALUES （8001，'张2'，'工程师'，TO_DATE（'1987-12-5'，'YYYY-MM-DD'））
5，一次性插入多条记录
insert  into DEPT_BAK   SELECT * from DEPT

2）update 语句
1，使用update 语句的时候，在事物没有结束之前，该条数据会被锁住，其他的用户无法修改这条数据，事物结束之后，该条数据的锁被放开，其他的用户才可以操作这条数据


3）delect 语句
delete from  dept  where  deptNo = ‘1000'；

4）合并语句
MERGE  INTO  dept_back D
USING dept S
ON （D.deptNo = S.deptNo）
WHERE MATCHED THEN
UPDATE SET D.Dname = S.Dname，D.loc_id = S.loc_id
WHERE NOT MATCHED THEN
INSERT VALUES （S.deptNo，S.Dname，S.loc_id）

6，修改表语句

1）建表语句
1，常规建表
create  table  student（
sId     number（4）  primary key，
sName  varchar2（10），
grade  varchar2（20），
sex   char（2） default '男'，
birthday   date
）；
2，在建表的时候使用子查询
create table dept_back
as 
select * form dept；

2）删除表
1，可以回滚的（DML语句）
drop  table dept_back；
2，不可以回滚的（DDL语句）
所有的数据，索引被删除，没有完成的事物被提交，不能回滚，要谨慎使用。
truncate  table dept_back；

3）修改表
在不影响数据的情况下，对表做出修改，对表的修改主要是对字段的修改
主要的操作：1，添加字段  2，删除字段  3，修改字段（修改字段的类型，修改字段的长度）
1，添加字段：总是可以成功，新添加的字段出现在表的最后
alter  table  student 
add  Tel  varchar2（11）；
2，修改字段：在该字段没有数据的时候，字段的类型，字段的长度都是可以修改的
alter  table student
modify  Tel  varhcar2（10）；
对于缺省值得修改，不会影响已经存在的数据，只会对以后插入的数据产生影响
alter  table  student
modify  sex  char（2） default '女'；
当该字段有数据的时候，字段的类型是不能修改的
字段的长度总是能修改的，增大总是可以的，减少要看数据的实际长度
alter  table  student 
modify  grade varchar2（20）；

3，删除字段
alter  table  student
drop  column  birthday；

7，事务

1）在事物中使用标记点
savepoint  A；
注：标记点只存在事务之中，事务结束之后所有的标记点都失效
2）事务处理的ACID特性
Atomic：原子性
Consistent：一致性
Isolated：隔离性
Durable：永久性

8，数据库对象

1）表
基本的数据存储对象，以行和列的形式存在
2）约束
约束是在表上强制执行的数据检验规则，被插入，修改或删除的数据必须符合相关字段上的设置的这些检验条件， 也就是约束条件
约束条件可以构建在一个表的单个字段上，也可以构建在一个表的多个字段上
当表中数据有相互依赖关系时，可以保护相关的数据不被删除
Oracle支持下面五类完整性约束
NOTNULL      非空约束
UNIQUE  KEY      唯一约束
PRIMARY  KEY     主键
FOREIGN  KEY     外键
CHECK 		检察
3）视图
一个或多个数据的显示
行内视图：就是出现在 from 后面的子查询，也就是一个视图，但是该视图没有命名，不会再数据库中保存
rownum只能适用于 <= 的情况，不适用于 >= 的情况
例如：查询工资最高的前三个人的信息，这种方式呗称为 TOP-N 分析法
select rownum，
from （select * from emp order by empNo）
where rownum <= n；

rowId：可以使用ROWID来获得数据的修改权限

4）索引
用于提高查询速度
1，创建索引
方式一：自动创建
Oracle会自动为主键和唯一键创建索引
自动创建的索引是无法手动删除的，但是在删除主键约束时，对应的索引会被自动删除
alter  table  emp
add constraints  ename_uni  unique（ename）；
alter  table emp
drop constrains  ename_uni；
方式二：手动创建
 	create index ename _index  on  emp（ename）；
drop  index ename_index； 
5）同义词
对象的别名

6）序列
1）作用：用来维护数据库的主键数据（和业务数据无关的流水号）
2）如果不使用序列，需要事先查询出当前最大的 ID，再加上1后再插入到数据库中
使用序列则可以让Oracle自己维护
3）创建序列
     方式一：在 Materialized views 中的 Sequences 里填写内容：最大值，起始值，步长。
     方式二：create sequence SEQ_STU
minvalue 1
maxvalue 9999
start with 1
increment by 1；
4）使用序列
insert  into student values （SEQ_STU.NEXTVALUE，'张三'）；
5）注：
删除数据后，序列的值不会受到影响

8，PL/SQL

1)pl/sql 的优点：
使用PL/SQL可以编写具有很多高级功能的程序，虽然这些功能可以通过多个SQL语句老完成同样的功能，但是PL/SQL具有如下优点：
1，使一组语句功能形成模块化程序开发
2，使用过程性语言控制程序结构
3，可以对程序中的错误进行处理
4，具有较好的可移植性
5，集成在数据库中，调用更快
6，减少了网络的交互，有助于提高程序性能

9，数据库设计的范式：

1）一个表中不能包含重复的数据列
2）所有非主键字段都必须完全依赖于表的主键
3）非主键字段不能依赖于其他的非主键字段，即非主键字段之间不能存在着传递依赖。

10，踩过的坑

1）在SQL语句中就好不使用 TAB 键作为空格，可能会出现错误，TAB 为制表符会被编译成特殊字符导致 SQL 执行失败。