数据库学习_限制每个部门只招聘5名职工超过计划则报出错误信息

电商行业技术特点
发布式
高并发 集群 负载均衡 高可用(redis)
海量数据
业务复杂
系统安全
搜索(lucence)

基于soa的架构
soa service oriented architecture 面向服务的架构,把工程拆为服务层,表现层两个工程。服务层包含业务逻辑,只需要对外提供服务即可,表现层只需要处理和页面的交互,
业务逻辑都是调用服务层的服务来实现

不要相信任何的”神话”，学会自己思考
不要墨守成规，大部分人都知道的事情可能是错误的
不要相信网上的传言，去测试，根据自己的实践做出决定
花时间充分的思考，敢于提出质疑
MySQL5.6中的InNoDB存储引擎添加了许多功能，如多线程清理线程，全文索引，在线索引添加，独立回滚段，非递归死锁检查，新的刷新算法，新的元数据表

DML data manipulation language ->select update insert delete 对数据库里面的数据进行操作的语言
DDL data definition language -> create alter drop 用于定义或改变表的结构，数据结构，表之间的链接和约束等初始化工作，在建立表时使用
create alter drop truncate comment注释
DCL data Control lanuage -> 数据库控制功能 用来设置或更改数据库用户或角色权限的语句 grant,deny,revoke，revoke收回已经授予的权限，grant授权

数据库:物理操作系统文件或其他形式文件类型的集合。在MySQL数据库中，数据库文件可以是frm,MYD,MYI,ibd结尾的文件。
当使用NDB引擎时，数据库的文件可能不是操作系统上的文件，而是存放于内存之中的文件，但是定义仍然不变
实例:mysql数据库由后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。
需要牢记的是，数据库实例才是真正用于操作数据库文件的
实例与数据库的关系通常是一一对应的，即一个实例对应一个数据库，一个数据库对应一个实例。
但是，在集群情况下可能存在一个数据库被多个数据实例使用的情况

MySQL体现结构
数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合
数据库实例是程序，是位于用户与操作系统之间的一层数据管理软件，用户对数据库数据的任何操作，
包括数据库定义，数据查询，数据维护，数据库运行控制等都是在数据库实例下运行

mysql由下面组成
连接池组件
管理服务和工具组件
SQL接口组件
查询分析器组件
优化器组件
缓冲(Cache)组件
插件式存储引擎(基于表的)
物理文件

原子性
一致性
隔离性
持久性

select s2.dept_id,s2.last_name,max(s1.salary)
from s_emp s1,s_emp s2
group by s2.dept_id,s2.last_name,s2.salary,s1.dept_id
having s2.salary=max(s1.salary) and s2.dept_id=s1.dept_id
order by s2.dept_id;

describe(desc) 表名 查看表结构

select last_name,salary,commission_pct from s_emp;
sqlplus中命令
col last_name for a20

SQLplus中/表示刚刚执行的SQL命令(SQLplus中buffer中命令，buffer仅仅会保存上一条SQL语句)

select last_name,start_date,salary from s_emp;

select * from s_dept;

select 10+20 from s_emp;表有多少行，就显示多少个
select 10+20 from dual;专门用于测试的表，

所有员工，工资
select last_name salary*12 from s_emp;

select last_name,salary*12+1000 from s_emp;
select last_name,(salary+1000)*12 from s_emp;

取别名(如果别名有小写字符或者空格，那么需要用双引号括起来) as可以省略 起了别名，当用Java取数据时，是通过别名来取数据
select last_name,(salary+1000)*12 as “TOTAL salary” from s_emp;

连接操作 ||用于连接两个列在一起
select first_name||last_name,salary,start_date from s_emp;
select first_name||’ ‘||last_name,salary,start_date from s_emp;
col first_name||last_name a30将该列长度设为a30

查询员工信息，员工每个月的工资(考虑提成比例) 由于有的人没有提成，提成为NULL，下面会出现问题
NVL()内置函数 如果查询NULL，使用后面的值代替
select last_name,salary*(1+commission_pct/100) from s_emp; 不正确

select last_name,salary*(1+NVL(commission_pct/100,0)) from s_emp;

查询公司所有部门s_dept
desc s_dept;

select id,name from s_dept;
select distinct name from s_dept;用于去重

distinct关键字来过滤多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重复记录的所有值
其原因是distinct只有用二重循环查询来解决。

对于部分字段重复数据的删除
查询那些数据是重复的
select 字段1,字段2,count(*)
from 表名
group by 字段1,字段2
having count(*)>1

将那些数据删除
delete from 表名 where 字段1,字段2 in
(
select 字段1,字段2,count(*)
from 表名
group by 字段1,字段2
having count(*)>1
)

对于大数据量来说，可能会将数据库吊死。所以我建议先将查询到的重复的数据插入到一个临时表中，然后对进行删除，
这样，执行删除的时候就不用再进行一次查询了。如下：
CREATE TABLE 临时表 AS
(select 字段1,字段2,count() from 表名 group by 字段1,字段2 having count() > 1)

下面就可以进行这样的删除操作了：
delete from 表名 a where 字段1,字段2 in (select 字段1，字段2 from 临时表);
这种先建临时表再进行删除的操作要比直接用一条语句进行删除要高效得多。

在oracle中，有个隐藏了自动rowid，里面给每条记录一个唯一的rowid，我们如果想保留最新的一条记录，
我们就可以利用这个字段，保留重复数据中rowid最大的一条记录就可以了。
下面是查询重复数据的一个例子：
select a.rowid,a.* from 表名 a
where a.rowid !=
(
select max(b.rowid) from 表名 b
where a.字段1 = b.字段1 and
a.字段2 = b.字段2
)
下面我就来讲解一下，上面括号中的语句是查询出重复数据中rowid最大的一条记录。
而外面就是查询出除了rowid最大之外的其他重复的数据了。
由此，我们要删除重复数据，只保留最新的一条数据，就可以这样写了：
delete from 表名 a
where a.rowid !=
(
select max(b.rowid) from 表名 b
where a.字段1 = b.字段1 and
a.字段2 = b.字段2
)

随便说一下，上面语句的执行效率是很低的，可以考虑建立临时表，讲需要判断重复的字段、rowid插入临时表中，然后删除的时候在进行比较。
create table 临时表 as
select a.字段1,a.字段2,MAX(a.ROWID) dataid from 正式表 a GROUP BY a.字段1,a.字段2;
delete from 表名 a
where a.rowid !=
(
select b.dataid from 临时表 b
where a.字段1 = b.字段1 and
a.字段2 = b.字段2
);
commit;

Oracle distinct用法:创建视图时过滤数据 distinct只显示一次重复出现的值

select distinct name,region_id from s_dept;
distinct不仅仅是针对第一个去重，而是针对所有的去重
distinct仅仅能位于select之后，由于关系型数据库
distinct针对的是后面所添加的全部的字段
select title,dept_id from s_empt;

select last_name,title from s_emp;
L 显示buffer中命令
1 选中buffer中的第几行命令
A 添加语句 a where dept_id=2

SqlPlus中的命令
save D:\aa.sql 将SQLplus中命令保存为文件
clear buffer将缓冲区中语句清空
get D:\aa.sql从D:\aa.sql导入语句
spool D:\aa.sql开始录制 将后面的命令全面录入D:\aa.sql中
当spool off时将停止录制命令

col设置查询后列的显示效果
col[umn] [{column|alias} [option]]
cle[ar] -清除列的显示效果
format -设置列的表现形式
heading 设置该列的列名
justify 设置列名左中心 右对齐

col last_name heading ‘Employee|Name’ format a15
col salary justify left format $999,999.99
col start_date format a8 null ‘not hired’

oracle进行查询排序时，如果排序字段里面有空值的情况下
Oracle认为null最大
升序排序，默认情况下，null值排在后面
降序排序，默认情况下，null值排在前面

select * from tableTest order by VISITS desc;
可能达不到自己想要的,改为
select * from tableTest order by VISITS desc nulls last;

改变方法
1 用nvl函数或decode函数将null转换为一特定值
2 用case语法将null转换为一特定值 order by (case mycol when null then ‘北京漂客’ else mycol end)
3 使用nulls last或nulls first

select last_name,salary,title from s_emp order by salary asc(默认为升序),dept_id desc(降序);
(按salary排序，然后当salary相同时按照dept_id降序排列)
order by 可以写的子项
1 列名(不论在select子句中是否出现过)
2 别名
3 表达式
4 select 子项的编号(从1开始) select last_name name,salary*12,title from s_emp order by 2 desc;

select last_name name,salary*12,title from s_emp order by name asc,salary*12 desc;

select last_name,commission_pct from s_emp order by 2;
oracle认为null最大
升序，默认排在后面

where是先查询出来，然后在进行选择

select from where order by中
from先进行，将整个表查询出来，然后再运行where，对行进行选择，最后再进行select选择对应的列
最后在进行排序(order by不仅仅可以对选择出来的列进行排序，而且可以对没有选择出来的列排序)

select last_name,title,dept_id from s_emp where dept_id=42;

select last_name,title,dept_id from s_emp where last_name=’Magee’;对字符串进行精准匹配

between…and…
in(list)
like
is null

between…and开闭情况 左右均闭
查询工资为1500-2000之间的员工信息
select last_name,salary from s_emp where salary between 1500 and 2000;
查询出来的表也是由有序的，序列为insert的顺序

查询41 43 39号部门的员工信息
select last_name,title,dept_id from s_emp where dept_id in (39,41,43);
where 后面的子不能为列名或别名

like 模糊查询
% 代表0-多个字符
_ 代表1个字符

查询名字以_开头的员工信息 此时需要转义
select last_name,salary from s_emp where last_name like ‘_%’ escape(临时指定转义字符) ‘\’

查询没有提成的员工
select last_name,commission_pct from s_emp where commission_pct is null;

and or优先级问题
=，(between and)优先级高 and 优先级高于or
优先级
1 between..and ,in,like,is null
2 and
3 or
查询41 43部门 工资范围在1000-1200员工信息
select last_name,dept_id,salary from s_emp where (dept_id =41 or dept_id =43 )and salary between 1000 and 1200;
select last_name,dept_id,salary from s_emp where dept_id in (41,43) and salary between 1000 and 1200;

单行函数 可以嵌套使用 可以使用在select中 where中，order by中
字符函数
lower
upper
initcap(单词首字母大写)
concat(多列连接)
substr(要截取的字符串，开始位置，长度)
length查询长度
nvl

select lower(last_name) ll,upper(first_name) uf,initcap(‘hello world’) ih from s_emp;
select concat(concat(first_name,’ ‘),last_name) from s_emp;等于first_name||’ ‘||last_name
substr(要截取的字符串，开始位置，长度)
查询所有员工last_name后两个字母 在SQL下标是从1开始
select substr(last_name,length(last_name)-1,2) from s_emp;

查询名字中包含tom(不区分三个字母的大小写)的员工信息
where upper(last_name) like ‘%TOM%’;不管大小写都整成大写

数值函数
round 做四舍五入 从小数点开始四舍五入
round(45.923,0) –>46
round(45.923) —->46
round(45.923,2) —>45.92
round(45.923,-1) —>50
trunc 截取
trunc(45.923) —>45
trunc(45.923,2) —>45.92
trunc(45.923,-1) –>40
mod取余

dual哑表 主要用于做测试
select * from dual;
DU
X(为占位符)

select round(45.923,-2),round(45.923,-1),round(55.932,-2),trunc(45.932,0),trunc(45.932,-1) from dual;

日期函数
默认日期为DD-MON-YY
sysdate简易版日期
systimestamp完整版日期
rownum 显示行号 rownum 当使用小于 小于等于时 是有效的
而当使用 大于时，仅能使用
rownum是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列(强调:先要有结果集)。
简单的说rownum是对符合条件结果的序列号。它总是从1开始排起。所以你选出的结果不能能没有1，而有大于1的值
所以无法得到select rownum,last_name from s_emp where rownum>11
rownum>10没有记录，因为第一条不满足去掉的话，第2条的rownum又变成了1，所以永远没有满足条件的记录
ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。
它取得第一条记录则rownum值为1，第二条为2，依次类 推。如果你用>,>=,=,between…and这些条件，
因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除， 接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。

1. select rownum,c1 from t1 where rownum != 10 为何是返回前9条数据呢？
   它与 select rownum,c1 from tablename where rownum < 10 返回的结果集是一样的呢？
   因为是在查询到结果集后，显示完第 9 条记录后，之后的记录也都是 != 10,或者 >=10,所以只显示前面9条记录。
   也可以这样理解，rownum 为9后的记录的 rownum为10，因条件为 !=10，所以去掉，其后记录补上，rownum又是10，
   也去掉，如果下去也就只会显示前面9条记录了

rowid与rownum虽然都被称为伪列，但他们的存在方式是不一样的，rowid可以说是物理存在的，表示记录在表空间中唯一位置编号ID。
在DB中唯一。只要记录没有被搬动过，rowid是不变的。rowid相对表来说是表的一般列。所以以rowid为条件就不会有rownum那些情况发生

rowid 默认起的ID(每张表都可以查rowid,数据存放到物理位置的ID值)

select sysdate from dual;
select systimestamp from dual;
select rownum,rowid,last_name from s_emp;

分页显示
查询s_emp 第11-20行数据
select last_name,salary from s_emp where rownum between 11 and 20;没有效果
使用方案
select rownum,last_name,salary
from(
select rownum,last_name,salary
from s_emp
order by salary
)
where r=2;

日期可以使用加减运算
向未来推10天
select sysdate+10 from dual;
向后推1小时
select sysdate+1/24 from dual;

select systimestamp+1/24 from dual;

months_between()两个月份之间的差距
add_months()添加月
next_day()下一个特定的天数
last_day()当前月最后一天
round 四舍五入 15舍 16入

months_between(‘01-sep-95’,’11-jan-94’)
add_months(‘11-jan-94’,6);
next_day(‘01-sep-95’,’friday’);
last_day(‘01-sep-95’)
select months_between(‘01-9月-95’,’11-1月-94’) from dual;
to_date(代表日期字符串,’dd-mon-yy’);进行字符串与日期转换
select round(to_date(‘16-6月-95’,’dd-mon-yy’),’month’) from dual;

to_char() 数字和日期转换为字符
to_number()字符转换为数子
to_date()字符串与日期转换
YYYY 年
MM 月
D 当前周第几天
DD 当前月第几天
DDD 当年的第几天
YEAR 当前年的英文写法
ddsp 第几天
DAY DY都是星期几
HH24 按照24格式写出来的小时
MI 分钟
SS 秒
RR表示年份
上个世纪的产物，现在不用了
YY表示年份
select to_char(sysdate,’YYYY-MM-D-DD-DDD-YEAR-MONTH-ddsp-ddspth-DAY-DY-HH24:MI:SS-AM’) from dual;

select to_char(start_date,’YYYY’) from s_emp;

select to_char(salary,’L999,000.00’) from s_emp;
select to_char(salary,’L000,000.00’) from s_emp;

select to_number(‘abc’) from s_emp;错误
select to_number(‘100’) from s_emp;

select to_date(‘05-9月-17’) from dual;

多表查询(连接查询)
笛卡尔积 前面的行和后面的行两两相乘 未处理完的信息
1 等值连接和不等值连接(内连接)–>自然连接
内连接仅限于查询外键 没有null值列的数据

2外连接(左外连接，右外连接，全外连接)

3自连接

查询一下:员工信息(员工所在部门的名称) 当两张表没有相同的列名时可以使用dept_id=id;
select e.last_name,e.title,d.name
from s_emp e,s_dept d
where e.dept_id=d.id;
当前面使用别名时最后面的where字句不能使用s_emp.dept_id=s_dept.id;

查询 员工信息 员工所在部门名称 所在区域
select e.last_name,d.name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id;

把左表中的所有数据都查询出来，不论是否建立过联系 join关键字左边的表就是左表
查询所有员工信息和别名名称(即便某些员工没有部门)
插入没部门员工
insert into s_emp(id,last_name) values(99,’yiyu’);
select last_name from s_emp;

select e.last_name,d.name
from s_emp e left outer join s_dept d
on e.dept_id=d.id;

查询所有部门员工信息，即使某些部门没有员工
右外连接
把右表中的所有数据都查询出来，不论是否建立过联系 join关键字右边的表就是右表

select e.last_name,d.name
from s_emp e right outer join s_dept d
on e.dept_id=d.id;

全外连接
select e.last_name,d.name
from s_emp e full outer join s_dept d
on e.dept_id=d.id;

简化写法
左外
select e.last_name,e.title,d.name
from s_emp e,s_dept d
where e.dept_id=d.id(+);

右外连接
select e.last_name,e.title,d.name
from s_emp e,s_dept d
where e.dept_id(+)=d.id;

自连接
查询员工信息以及每个员工经理的名字
select e.id,e.last_name name,m.last_name manager
from s_emp e,s_emp m
where e.manager_id=m.id;

SQL中exists 强调的是是否返回结果集，不要求知道返回什么
select name from student where sex=’m’ and mark exists(select 1 from grade where….)只要exists引导的子句有结果集返回，
那么exists这个条件就算成立，返回的字段始终为1
exists和in的区别在于in引导的子句只能返回一个字段

集合操作符
交 并 补
union
union all
minus
intersect

交集取一次，然后各自剩余的取一次 相当于并
select e.last_name,e.title,d.name
from s_emp e,s_dept d
where e.dept_id=d.id(+)
union
select e.last_name,e.title,d.name
from s_emp e,s_dept d
where e.dept_id(+)=d.id;

交集取两次，然后各自剩余的取一次
select e.last_name,e.title,d.name
from s_emp e,s_dept d
where e.dept_id=d.id(+)
union all
select e.last_name,e.title,d.name
from s_emp e,s_dept d
where e.dept_id(+)=d.id;

minus求补集 两个集合的减
select rownum,last_name
rom s_emp
where rownum<=20
minus
select rownum,last_name
from s_emp
where rownum<=10;

intersect交集 等值连接
select e.last_name,e.title,d.name
from s_emp e,s_dept d
where e.dept_id=d.id(+)
intersect
select e.last_name,e.title,d.name
from s_emp e,s_dept d
where e.dept_id(+)=d.id;

group by用完仅仅显示一行的数据
而当select中出现聚合函数与属性出现时，要求此时必须使用group by，而且必须group by 前面的属性

组函数(多行函数)
AVG平均值
COUNT行数 count(*)
MAX 不仅仅可以比较数值类型，而且可以比较字符类型(根据ASCII码来比较)
MIN
STDDEV
SUM
VARIANCE

组函数使用 要考虑二维表格的问题
select avg(salary),min(salary),stddev(salary) from s_emp;

select max(last_name),min(last_name) from s_emp;

select count(*) from s_emp;

where中不允许使用分组函数，而且where子句位于group by之前

select last_name,avg(salary) from s_emp;(由于avg(salary)呈现为1行，而last_name呈现为多行)
解决方案
每个部门的平均工资
select dept_id,avg(salary) from s_emp group by dept_id;
每个部门的每个工作的工资
select dept_id,title,avg(salary) from s_emp group by dept_id,title;

查询平均工资>1100部门信息
select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>1100;

查询平均工资>1100部门名称

子查询
出现在where和from后面，而且还可以出现在select中
当做where后面的值时，一个值用=,多个值用in
查询和Smith在同一部门的员工信息 先查内层信息，然后再查外层信息
select last_name
from s_emp
where dept_id = (
select dept_id
from s_emp
where last_name=’Smith’);

查询11-20
select rownum r,last_name
from s_emp

select r,last_name
from 虚拟表
where r between 11 and 20;
查询11-20
select r,last_name
from (
select rownum r,last_name
from s_emp
)
where r between 11 and 20;

员工工资低于平均工资的员工的信息
select last_name
from s_emp
where salary<(

)

查询平均工资大于32号部门的平均工资的部门ID
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp
group by dept_id
having dept_id=32);

select last_name
from s_emp
where dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp)
);

查询语句中select from where group by having order by的执行顺序
写的顺序为select–from–where–group by–having–order by
select from是必须的，其他关键字是可选的
执行顺序
　 from 需要从哪个数据表检索数据
where过滤表中数据的条件
group by如何将上面过滤出的数据分组
having对上面已经分组的数据进行过滤的条件 此子句很耗资源，尽量少用
select 查看结果集中的哪个列，或列的计算结果
order by按照什么样的顺序来查看返回的数据
from后面的表关联，是自右向左解析的 最后面的那个表为驱动表，执行顺序为从后往前，数据量小的表尽量放后面
where条件的解析顺序是自下而上的
也就是，写SQL文时候，尽量把数据量小的表放在最右边来进行关联(小表去匹配大表)

替换变量

select last_name,dept_id
from s_emp
where dept_id=&var1;(&var1就是一个替换变量)

除关键字外其他字都可以替换
select &v1,&v2
from &v3
where &v4=&v5;

select last_name,dept_id
from s_emp
where last_name=’&var1’;字符变量需要加单引号

替换变量指定默认值
define var1=”’Smith”’
设定默认值之后怎么再次接收别的值
accept var1 prompt ‘请输入名字’

用下标来指定替换变量
select last_name,dept_id
from s_emp
where last_name=&1

数据模型E-R图和数据库设计
E-R转换为表的思维步骤
1 每一个实体要转化为一张表
2 实体与实体之间的关系，用表怎么表示
转化为表之后用外键来表示关系
1：1 外键加在任何一张表上都可以
1: n 外键加在n的一方
n: n 外键加在新表中(只有两列两个表的主键) 桥表
3 实体中的属性，转化为表中的列，要考虑属性数量够不够

范式
第一范式:行和列交叉的地方只有唯一的一个值 属性必须为唯一的
第二范式:表中的每一列 只能通过主键列取唯一标识
第三范式:表中的非主键列不能依赖于其他非主键列

创建表的语句
主键约束
primary key
即可写成列级约束，也可以写成表级约束
外键约束
foreign key.. references…
即可写成列级约束，也可以写成表级约束
非空约束
not null
只能写成列级
唯一性约束
unique
即可写成列级约束，也可以写成表级约束
检查约束
check
即可写成列级约束，也可以写成表级约束
create table student(
gender varchar2(10) check(gender in(‘男’,’女’))
);

insert into student values(‘你好’);
insert into student values(‘男’);

constraint是用于定义异常的

drop table customer;
create table customer(
id number(7) constraint cus_id_pk primary key,
name varchar2(25) constraint cus_name_nn not null,
phone varchar2(20) constraint cus_phone_uq unique,
emp_id number(7) constraint cus_emp_id_fk references s_emp(id)
);

insert into customer values(1,’yiyu’,’12345’,10);
insert into customer values(null,’yi’,’1234’,11);
insert into customer values(1,’yu’,’1234’,11);
insert into customer values(2,’yi’,’12345’,11);
insert into customer values(2,’yi’,’1234’,26);

drop table customer;
create table customer(
id number(7) ,
name varchar2(25) ,
phone varchar2(20),
emp_id number(7),
primary key(id),
foreign key(emp_id) references s_emp(id),
unique(phone)
);

schema
drop table student;
create table yiyu.student(
gender varchar2(10) check(gender in(‘男’,’女’))
);

联合主键如何写
多个列组合起来成为主键
多个列的组合唯一，每个列但是非空
每个表只允许一个主键
故使用表级主键
create table t_s_tab(
t_id number(7),
s_id number(7),
primary key(t_id,s_id)
);

默认值
create table stu(
id number(7),
name varchar2(25) default ‘yiyu’
);
insert into stu(id) values(10);

创建表时可以使用子查询

原表中只有not null约束会复制到新表中
create table jd1710_emp
as
select id,last_name,salary
from s_emp;

Oracle数据字典表
系统表就是数据字典

查询yiyu用户创建的所有表(表名)
数据字典表，规模庞大 600

dictionary视图 存放所有系统表的名字和描述信息
select * from dictionary;
user 当前用户所有的表
all 所有用户都可以访问的表
dba 数据库管理员才能操控的表
V$ 数据库性能的表

1通过模糊查询(从需求中提取关键字)从dictionary找到相关的若干张系统表
select table_name
from dictionary
where upper(table_name) like ‘USER%TABLE%’;
2使用desc命令，查看这些系统表有哪些列
desc user_tables
3选取合适的列进行查看
select table_name,num_rows
from user_tables;

查询yiyu用户创建的视图
select table_name
from dictionary
where upper(table_name) like ‘USER%VIEW%’;

select view_name
from user_views

select owner,constraint_name,constraint_type,table_name
from user_constraints;

操控数据
insert into jd1710_emp
values(30,’yiyu’,1000);

insert into jd1710_emp(id,last_name)
values(31,’呓语’);

create table jd1710_cus(
id number(7) primary key,
name varchar2(20)
);
create table jd1710_ord(
id number(7) primary key,
name varchar2(20),
cus_id number(7) references jd1710_cus(id)
);
insert into jd1710_cus
values(1,’jack’);
insert into jd1710_cus
values(2,’tom’);
insert into jd1710_ord
values(1,’食品订单’,1);

insert into jd1710_ord
values(2,’餐具订单’,2);

插入数据可以写子查询
delete from jd1710_emp;
insert into jd1710_emp(id,last_name,salary)
select id,last_name,salary
from s_emp
where salary>1200;

更新数据
update jd1710_emp
set last_name=”

删除数据
delete from jd1710_emp;
delete 相当于是添加了一个删除标记，有这个标记，

级联删除
在删除指令后面添加关键字
创建含有外键表的时候，在外键后面就指定级联删除
drop table jd1710_ord;
create table jd1710_ord(
id number(7) primary key,
name varchar2(20),
cus_id number(7) references jd1710_cus(id)
on delete cascade
);

delete from jd1710_cus
where id=1;

事务控制
事务:一组SQL命令的集合(DML,select,DDL,DCL)
代表逻辑中一个行为 这个命令
涉及到的SQL命令会有多条
一个事务可以包含多条DML()，select
一个事务只能包含一条DDL()，DCL()
怎么开启事务
只要执行DML或者执行select
ACID
A atomicity 原子性 控制事务的最小单元就是事务 要么成功要么失败
C Consistency 一致性 事务的值总和不会发生变化
I Isolation 隔离性 一个会话中改变在没有提交之前，其他事务无法查看到
Durability 持久性 事务完成之后，事务中所操作的数据会持久保存
Oracle必须自己提交
insert into jd1710_emp
values(51,’jack’,1200);

会话
一次新的连接，连接到服务端

commit提交事务 只有事务提交以后，事务中操作的数据才能持久化保存，别的会话才能看见
Oracle的SQLplus事务需要手动提交
MySQL的客户端会自动提交事务
jdbc自动提交事务

哪些行为能触发事务提交
1 DDL DCL
2 正常退出SQLplus exit
rollback
事务回滚
事务在没有提交之前都可以使用rollback让数据回滚到事务开启之前的状态
rollback to 不会让事务结束
savepoint
设置回滚点 记录某些关键SQL语句执行的结果 可以在回滚的时候，不恢复原始状态
当事务结束的时候(commit,rollback)
该事务内声明的回滚点全部删除
update jd1710_emp
set last_name=’yiyu’;

update jd1710_emp
set last_name=’tom’
where id=1;
savepoint p1;

update jd1710_emp
set last_name=’yi’
where id=16;
savepoint p2;

修改表结构DDL语句
添加新列
alter table jd1710_emp
add(
first_name varchar2(25) unique
);
删除列 考虑外键
alter table jd1710_cus
drop column id;删除不成功
alter table jd1710_emp
drop column first_name;
修改列的特性 只能修改列的数据类型，和添加删除not null约束(not null是列级的)，如果原列中有null值，则无法加上not null
insert into jd1710_ord
values(1,null,null);

alter table jd1710_ord
modify(
name number(7) not null
)
update jd1710_ord
set name=’yiyu’;

alter table jd1710_ord
modify(
name varchar2(25) not null
);

添加约束
alter table jd1710_emp
add constraint emp_salary_uq unique(salary);

删除约束
insert into jd1710_emp
values(500,null,null);

select table_name
from dictionary
where upper(table_name) like ‘USER%CONSTRAINT%’;

select constraint_name,owner,table_name
from user_constraints;

删除表
drop table stu;

rename jd1710_emp to j_emp;
修改列名
alter table j_emp
rename column salary to money;

清空表空间
truncate table j_emp;
1 DDL语句
2 是真正的删除数据
delete from j_emp
1 DML语句
2 delete删除数据 不会立刻把数据删除，而是把要删除的数据添加一个标记，select语句不会显示带有删除标记的数据，从而达到删除数据的效果

序列 用于主键生成
使用方式类似Random
1创建序列
2通过nextval关键字取到序列中下一个值
create sequence id_seq
increment by 2
start with 10;

currval当前值
nextval下一个值
select id_seq.nextval,id_seq.currval
from dual;

insert into j_emp
values(id_seq.nextval,’hello’,1000);
视图
视图
视图是表的一种映像
create view 视图名
as
子选项

给予yiyu的权限为connect，resource(创建表)
而没有给予创建view权限
grant create any view to yiyu;

简单视图
创建视图使用子查询，数据只来自一张表

仅用了where和order by
create view v_emp
as
select id,last_name,salary
from s_emp
where salary>1500;
查

增删改

1 如果对视图进行操作，会影响原表
update v_emp
set last_name=’hello world’
where id=1;
2 如果对原表进行操作，会影响视图
update s_emp
set last_name=’yiyu’
where id=1;
复杂视图
创建视图使用的子查询包含group by或者连接查询
create view v_emp_dept
as
select e.id id,e.last_name name,e.salary salary,d.name dept_name
from s_emp e,s_dept d
where e.dept_id=d.id;

查

增删改
不允许

修改视图
create or replace view v_emp
as
select id,last_name,title
from s_emp;

设置视图为只读(在简单视图后面加，复杂视图不加)
create or replace view v_emp
as
select id,last_name
from s_emp
with read only;

with check option
检查不允许更改建立视图所用的

create or replace view v_emp
as
select id,last_name,title
from s_emp
where last_name=’Jack’
with check option;

update v_emp
set last_name=’tom’
where id=20;
当没有with check option时，当修改时，由于last_name不符合where last_name=’Jack’要求，此时，会从该视图中删除
当有with check option时，会不允许修改
但是允许修改其他属性

删除视图
drop view;

创建索引
索引的创建
1 当创建表时添加primary key或者unique时自动添加索引
2 create index 名字
on table

索引:添加在列上的一种数据库对象，目的是提高针对此列的查询效率

DATA数据类型
可以存储月，年，日，世纪，时，分和秒
DATA数据类型的问题在于他表示两个事件发生时间间隔的度量粒度是秒

oracle中case when then else end用法
简单case表达式：使用表达式确定返回值
语法
case search_expression
when expression1 then result1
when expression2 then result2
…..
else default_result end
搜索case表达式 使用条件确定返回值
case
when condition1 then result1
when condition2 then result2
….
else default_result
end
例子
select product_id,product_type_id,
case
when product_type_id=1 then ‘Book’
when product_type_id=2 then ‘Video’
when product_type_id=3 then ‘DVD’
when product_type_id=4 then ‘CD’
else ‘Magazine’
end
from products
简单case函数
case sex
when ‘1’ then ‘男’
when ‘2’ then ‘女’
else ‘其他’ end
case搜索函数
case
when sex=’1’ then ‘男’
when sex=’2’ then ‘女’
else ‘其他’ end

SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /sex 1为男生，2位女生/
ELSE NULL
END) 男生数,
COUNT (CASE WHEN sex = 2 THEN 1
ELSE NULL
END) 女生数
FROM students GROUP BY grade;

SELECT T2., T1.
FROM T1, T2
WHERE (CASE WHEN T2.COMPARE_TYPE = ‘A’ AND
T1.SOME_TYPE LIKE ‘NOTHING%’
THEN 1
WHEN T2.COMPARE_TYPE != ‘A’ AND
T1.SOME_TYPE NOT LIKE ‘NOTHING%’
THEN 1
ELSE 0
END) = 1

SELECT
CASE WHEN salary <= 500 THEN ‘1’
WHEN salary > 500 AND salary <= 600 THEN ‘2’
WHEN salary > 600 AND salary <= 800 THEN ‘3’
WHEN salary > 800 AND salary <= 1000 THEN ‘4’
ELSE NULL END salary_class, – 别名命名
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN ‘1’
WHEN salary > 500 AND salary <= 600 THEN ‘2’
WHEN salary > 600 AND salary <= 800 THEN ‘3’
WHEN salary > 800 AND salary <= 1000 THEN ‘4’
ELSE NULL END;

Oracle中decode函数用法
decode(条件,值1,返回值1,值2，返回值2….缺省值)
if 条件=值1 then
return(返回值1)
else if(条件=值2) then
return(返回值2)
…….
行转列 主要原理是利用decode函数，聚集函数(sum)或者max,min均可,结合group by分组实现的
select t.user_name,
sum(decode(t.course,’语文’,score,null)) as CHINESE,
sum(decode(t.course,’数学’,score,null)) as MATH,
sum(decode(t.course,’英语’,score,null)) as ENGLISH
from test_tb_grade t
group by t.user_name
order by t.user_name

select name,
min(decode(xk,’语文’,cj,null)) as CHINESE,
min(decode(xk,’数学’,cj,null)) as MATH,
min(decode(xk,’英语’,cj,null)) as ENGLISH
from stu
group by name
order by name;

如果要实现对各门功课的不同分数段进行统计
select t2.score_gp,
sum(decode(t2.course,’语文’,countnum,null)) as CHINESE,
sum(decode(t2.course,’数学’,countnum,null)) as MATH,
sum(decode(t2.course,’英语’,countnum,null)) as ENGLISH
from(
select t.course,
case when t.score<60 then ‘00-60’
when t.score>=60 and t.score<80 then ‘60-80’
when t.score>80 then ‘80-100’ end as score_gp,
count(t.score) as countnum
from test_tb_grade t
group by t.course,
case when t.score<60 then ‘00-60’
when t.score>=60 and t.score<80 then ‘60-80’
when t.score>=80 then ‘80-100’ end
order by t.course) t2
group by t2.score_gp
order by t2.score_gp

列转行
create table test_tb_grande(
id number(10) primary key,
user_name varchar(20),
CN_SCORE number(10),
MATH_SCORE number(10),
EN_SCORE number(10)
)
id user_name cn_score math_score en_score
1 lisi 87 65 75
2 michael 78 95 81
3 xiaoxiao 97 78 91
转为
user_name course score
lisi 数学 65
lisi 英语 75
lisi 语文 87
michael 数学 95
michael 英语 81
michael 语文 78
xiaoxiao 数学 78
xiaoxiao 英语 91
xiaoxiao 语文 97

主要原理是利用SQL的union
select user_name,’语文’ course,CN_SCORE as score from test_tb_grade
union select user_name,’数学’ course,math_score as score from test_tb_grade
union select user_name,’英语’ course,en_score as score from test_tb_grade
order by user_name,course;

group by分组，只是简单地依据所选列的数据进行分组，将该列具有相同值的行划为一组

JDBC
java DataBase Connection
jdbc是sun公司
ibatis

PL/SQL
是Oracle对SQL语言的过程化扩展，指在SQL命令语言中增加过程处理语言(分支，循环)，使SQL语言具有过程化处理能力
在大数据核心框架一个是Hadoop，另一个为spark
Hadoop使用Java，spark使用Scala
程序语言
declare
说明部分(变量说明，游标声明，例外说明)
begin
语句序列(DML语言)
exception
例外处理语句
end;

说明变量(char,varchar2,date,number,boolean,long)
var1 char(15); 说明变量名，数据类型和长度后用分号结束说明语句
married boolean :=true; 相当于MySQL的自定义变量
psal number(7,2);
my_name s_emp.last_name%type; 引用类型，即my_name的类型与s_emp中last_name列的类型一样
s_emp_rec s_emp%rowtype; 记录型类型
记录变量分量的引用
s_emp_rec.last_name:=’adams’;

定义常量 var const

if语句
if 条件 then 语句1;
语句2;
end if;

if 条件 then 语句序列1;
esle 语句序列2;
end if;

if 条件 then 语句;
elsif 语句 then 语句;
else 语句;
end if;
– 查询并打印7839的姓名和薪水
declare
– 定义变量保存姓名和薪水
– Local variables here
pename s_emp.last_name%type;
psal s_emp.salary%type;
begin
–得到7839的姓名和薪水
– Test statements here
select last_name,salary into pename,psal from s_emp where id=10;
dbms_output.put_line(pename||’的薪水是’||psal);
end;

– 查询并打印7839的姓名和薪水
declare
– 定义记录型变量，代表一行
emp_rec s_emp%rowtype;
begin
– Test statements here
select * into emp_rec from s_emp where id=10;
dbms_output.put_line(emp_rec.last_name||’的薪水为’||emp_rec.salary);
end;

– 查询并打印7839的姓名和薪水
declare
– 定义变量保存姓名和薪水
– Local variables here
pename s_emp.last_name%type;
psal s_emp.salary%type;
begin
–得到7839的姓名和薪水
– Test statements here
select last_name,salary into pename,psal from s_emp where id=10;
dbms_output.put_line(pename||’的薪水是’||psal);
end;

– 判断用户从键盘输入的数字
–接收键盘输入
–num 地址值，在该地址上保存了输入的值
accept num prompt ‘请输入一个数字:’;
declare
– 定义变量保存数字
pnum number：=#
begin
if pnum=0 then dbms_output.put_line(‘您输入的是0’);
elsif pnum =1 then dbms_output.put_line(‘您输入的是1’);
elsif pnum=2 then dbms_output.put_line(‘您输入的是2’);
else dbms_output.put_line(‘其他数字’);
end if;
end;

循环语句
while total<=2500 LOOP
LOOP EXIT [WHEN 条件]
….. 语句序列 ……语句序列
total:=total+salary; End loop;
end LOOP;

FOR I IN 1....3
LOOP
语句序列;
END LOOP;
1
2
3
4

– 打印1-10
declare
pnum number :=1;
begin
loop
–循环
–退出条件
exit when pnum>10;
dbms_output.put_line(pnum);
–加1
pnum:=pnum+1;

end loop;
end;

光标(Cursor)==ResultSet
Cursor初始指向第一行
说明光标语法
Cursor 光标名 [(参数名 数据类型[,参数名，数据类型])]
is select 语句;

用于存储一个查询返回的多行数据
coursor c1 is select ename from emp;
打开光标 open c1;(打开光标执行查询)
取一行光标的值 fetch c1 into pename;(取一行到变量中)
关闭光标 close c1;(关闭游标释放资源)

上面的pename必须和emp表中ename列类型一致
定义 pename emp.ename%type;

– 查询并打印员工的姓名和薪水
/*
光标相当于ResultSet集合
光标的属性:
%isopen 是否打开
%rowcount(影响的行数)
%found 找到对应的行
%notfound 没有找到对应的行
*/
declare
– 定义光标
cursor cemp is select last_name,salary from s_emp;
pename s_emp.last_name%type;
psal s_emp.salary%type;
begin
– 打开光标
open cemp;
loop
–取当前记录
fetch cemp into pename,psal;
–exit when 没有取得记录;
exit when cemp%notfound;
dbms_output.put_line(pename||’的薪水为’||psal);
end loop;

–关闭光标
close cemp;
end;

– 涨工资，总裁1000，经理800，其他400
declare
– 定义光标
cursor cemp is select title,id from s_emp;
pid s_emp.id%type;
pjob s_emp.title%type;
begin
– 打开光标
open cemp;
loop
–取一个员工
fetch cemp into pjob,pid;
exit when cemp%notfound;
–判断职务
if pjob =’President’ then update s_emp set salary=salary+1000 where id=pid;
elsif pjob =’VP, Administration’ then update s_emp set salary=salary+800 where id=pid;
else update s_emp set salary=salary+400 where id=pid;
end if;
end loop;
–关闭光标
close cemp;
–提交,由于plsql会在一个事务中进行，所以必须进行提交—>ACID
commit;
dbms_output.put_line(‘完成了’);
end;

– 查询某个部门的员工姓名
declare
cursor cemp(dno number) is select last_name from s_emp where dept_id =dno;
pename s_emp.last_name%type;
begin
open cemp(41);
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;

异常
系统定义异常
Java输出的异常信息是Java调用栈的信息，栈是一种数据结构
Java异常处理是向上抛出，谁调用谁处理

Oracle异常处理
系统定义例外
no_data_found 没有找到数据
too_many_rows select…into语句匹配多行
zero_divide 被零除
value_error 算术或转换错误
timeout_on_resource(在线等待资源时发生超时)

raise 是抛出数据库异常

–系统变量 被0除
declare
pnum number;
begin
pnum:=1/0;

exception
when zero_divide then dbms_output.put_line(‘1:0不能做分母’);
dbms_output.put_line(‘2:0不能做分母’);
–when value_error then dbms_output.put_line(‘算术或转换异常’);
–when timeout_out_resource then dbms_output.put_line(‘在线等待资源时发生超时’);
–when no_data_found then dbms_output.put_line(‘没有找到数据’);
–when too_many_rows thendbms_output.put_line(‘select …into 语句匹配多行’);
when others then dbms_output.put_line(‘其他例外’);
end;

–查询50号部门的员工姓名
declare
–定义光标 代表50号部门的员工
cursor cemp is select last_name from s_emp where dept_id=1;
pename s_emp.last_name%type;
–自定义例外
no_emp_found exception;
begin
open cemp;
–取第一条记录
fetch cemp into pename;
if cemp%notfound then
–抛出异常
raise no_emp_found;
end if;
close cemp;
exception
when no_emp_found then dbms_output.put_line(‘没有找到员工’);
when others then dbms_output.put_line(‘其他异常’);
end;

pmon进程：进程监视进程
在用户进程失败时执行进程恢复
清除数据库缓冲区高速缓存
释放该用户进程使用的资源

/*
sql语句 select to_char(start_date,’YYYY’) from s_emp;
–>光标—>循环–>退出条件:notfound
变量 1初始值 2最终得到
count80 number:=0;
count81 number:=0;
*/
declare
–入职年份
cursor cemp is select to_char(start_date,’YYYY’) from s_emp;
pdate varchar2(4);
–每年入职的人数
count90 number:=0;
count91 number:=0;
count92 number:=0;
begin
–打开光标
open cemp;
loop
–取一个员工的入职年份
fetch cemp into pdate;
exit when cemp%notfound;
–判断年份
if pdate = ‘1990’ then count90:=count90+1;
elsif pdate = ‘1991’ then count91:=count91+1;
elsif pdate = ‘1992’ then count92:=count92+1;
end if;
end loop;
–关闭
close cemp;
–输出
dbms_output.put_line(‘total:’||(count90+count91+count92));
dbms_output.put_line(‘1990:’||count90);
dbms_output.put_line(‘1991:’||count91);
dbms_output.put_line(‘1992:’||count92);

end;

/*
select last_name,sal from s_emp order by sal;
—>光标–>循环–>退出条件:1总额>5w 2 notfound
变量 初始值 最终得到
涨工资的人数 countEmp number:=0
涨后工资的工资总额 salTotal number;
1 select sum(sal) into salTotal from emp;
2 涨后=涨前+sal*0.1
*/
declare
cursor cemp is select id,salary from s_emp order by salary;
pid s_emp.id%type;
psal s_emp.salary%type;
–涨工资的人数
countEmp number:=0;
–涨工资的工资总额
salTotal number:=0;
begin
–得到初始的工资总额
select sum(salary) into salTotal from s_emp;
open cemp;
loop
–1 总额>5w
exit when salTotal>500000;
–取一个员工涨工资
fetch cemp into pid,psal;
–notfound
exit when cemp%notfound;
–涨工资
update s_emp set salary=salary*1.1 where id=pid;
–人数+1
countEmp:=countEmp+1;
–2 涨后=涨前+sal*0.1
salTotal:=salTotal+psal*0.1;

end loop;
close cemp;
dbms_output.put_line(‘涨后的工资总额:’||salTotal);
dbms_output.put_line(‘有几个人涨了工资:’||countEmp);
end;

/*
SQL语句
部门 select id from dept;
查部门中的员工薪水 select sal from emp where dept_id=?
变量 1初始值 2最终得到
每个段的人数
count1 number;
count2 number;
count3 number;
部门的工资总额
saltotal number;
1 最终值select sum(sal) into saltotal from emp where deptno=?
2 累加
*/
declare
–部门
cursor cdept is select id from s_dept;
pdeptno s_dept.id%type;
–部门中员工薪水
cursor cemp(dno number) is select salary from s_emp where dept_id=dno;
psal s_emp.id%type;
–每个段的人数
count1 number;
count2 number;
count3 number;
–部门的工资总额
saltotal number;
begin
open cdept;
loop
–取一个部门
fetch cdept into pdeptno;
exit when cdept%notfound;
–初始化
count1:=0;
count2:=0;
count3:=0;
–部门的工资总额
select sum(salary) into saltotal from s_emp where dept_id=pdeptno;

--取部门中员工的薪水
open cemp(pdeptno);
loop
   --取一个员工的薪水
   fetch cemp into psal;
   exit when cemp%notfound;
   if psal <3000 then count1:=count1+1;
      elsif psal>3000 and psal<6000 then count2:=count2+1;
      else count3:=count3+1;
   end if;
end loop;
close cemp;
--保存结果
insert into msg values(pdeptno,count1,count2,count3,saltotal);
dbms_output.put_line('你好');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

end loop;
close cdept;
commit;
end;

/*
SQL语句
系名 select dno from dep;
pdno number
学生在上面的系名 select grade from sc,course c,student s where sc.sno=s.sno and sc.cno=c.cno and c.cname=’大学物理’ and s.dno=pdno
pgrade number
*/
declare
cursor cdept is select distinct dno,dname from dep;
pdptno dep.dno%type;
pdptname dep.dname%type;
couname course.cname%type;
cursor cstudent(dptno number,cvname course.cname%type) is
select grade,s.sname
from sc,course c,student s,dep d
where s.sno=sc.sno and sc.cno=c.cno and s.dno=d.dno and c.cname=cvname and s.dno=dptno;
pgrade sc.grade%type;
pname student.sname%type;
pavg number;
count1 number;
count2 number;
count3 number;
begin
couname:=’大学物理’;
open cdept;
loop

 fetch cdept into pdptno,pdptname;
 exit when cdept%notfound;
 dbms_output.put_line(pdptname);
 count1:=0;
 count2:=0;
 count3:=0;
  pavg:=0;
 select avg(grade) into pavg from student s,sc where dno=pdptno and sc.sno=s.sno;
 open cstudent(pdptno,couname);
 loop
    fetch cstudent into pgrade,pname;
    exit when cstudent%notfound;
    dbms_output.put_line('pgrade'||pgrade||' '||pname);
    if pgrade <60 then count1:=count1+1;
      elsif pgrade>60 and pgrade<85 then count2:=count2+1;
      else count3:=count3+1;
     dbms_output.put_line(count1||' '||count2||' '||count3);
    end if;
 end loop;
 close cstudent;
 dbms_output.put_line('最终'||count1||' '||count2||' '||count3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

insert into msg values(couname,pdptname,count1,count2,count3,pavg);
end loop;
close cdept;
commit;
end;

存储过程和存储函数 指存储在数据库中供所有用户程序调用的子程序叫存储过程，存储函数
create procedure命令创建存储过程
/*
第一个存储过程:打印helloWorld
调用存储过程
1 exec sayHelloWorld();
2 begin
sayHelloWorld();
sayHelloWorld();
end;
*/
create or replace procedure sayHelloWorld is
begin
dbms_output.put_line(‘Hello World’);
end sayHelloWorld;

/*
给指定的员工涨100，并且打印涨前和涨后的薪水
*/
create or replace procedure raiseSalary(eno in number)
as
–定义变量保存涨前的薪水
psal s_emp.salary%type;
begin
–得到涨前的薪水
select salary into psal from s_emp where id=eno;
–涨100
update s_emp set salary=salary+100 where id=eno;
–要不要commit
dbms_output.put_line(‘涨前:’||psal||’涨后:’||(psal+100));

end;

/*
为指定的员工涨指定的额度
*/
create or replace procedure raiseToSalary(empid in number,rate in number)
as
psal s_emp.salary%type;
begin
select salary into psal from s_emp where id=empid;
update s_emp set salary=salary*rate where id=empid;
dbms_output.put_line(‘员工号:’||empid||’涨工资前’||psal||’涨工资后’||psal*rate);
end;

存储函数
为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似。但必须有一个return子句，用于返回函数值。函数说明要指定函数名，结果值的类型，以及参数类型等
–查询某个员工的年收入
create or replace function queryEmpIncome(eno in number)
return number
as
–月薪和奖金
psal s_emp.salary%type;
pcomm s_emp.commission_pct%type;
begin
–得到月薪和奖金
select salary,commission_pct into psal,pcomm from s_emp where id=eno;
return psal*12+nvl(pcomm,0)*0.1*psal;
end;

一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值
但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，
在过程和函数中实现返回多个值

什么时候用存储过程/存储函数
只有一个返回值，用存储函数，否则，用存储过程

begin
– Call the function
:result := queryempincome(eno => :eno);
end;
绑定变量
:result

/*
查询某个员工的姓名 月薪 职位
思考
1 查询某个员工的所有信息 –out参数太多
2 查询某个部门中所有员工信息 –>返回一个集合

*/
create or replace procedure queryEmpInformation(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select last_name,salary,title into pename,psal,pjob from s_emp
where id=eno;
end;

• 不能通过java代码使java虚拟机运行垃圾回收机制
  
  • 运行java程序
  • Java -Xms100M -Xmx200M HelloWorld 执行helloWorld需要的堆内存最小为100M，最大为200M
    *垃圾回收，我们不能经常进行垃圾回收，垃圾回收会耗费CPU资源 会使计算机变慢，我们应该避免频繁垃圾回收

在out参数中使用游标
声明包头结构
create or replace package mypackage as
type empcursor is ref cursor;
procedure queryEmpList(dno in number,empList out empcursor)
end mypackage;
创建包体

触发器 一个与表相关联的，存储的PL/SQL程序。每当一个特定的数据操作语句(insert,update,delete)在指定的表上发出时，Oracle自动的执行触发器中定义的语句序列
触发器类型
语句级触发器 针对的是表
在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行
行级触发器 for each row 针对的是行
触发语句作用的每一条记录都被触发，在行级触发器使用:old和:new记录变量，识别值的状态

每当成功插入新员工后，自动打印”成功插入新员工”
create trigger abcd
after insert
om s_emp
declare
begin
dbms_output.put_line(‘成功插入新员工’);
end;

触发器可用于
数据确认
实施复杂的安全性检查
做审计，跟踪表上所做的数据操作等
数据的备份和同步
查询触发器，过程及函数
select * from user_triggers;
select * from user_source;

创建触发器 create pr replace trigger 触发器名
{before|after}
{delete|insert|update[ of 列名]}(update of 列名 即根据列名)
on 表名
[for each row [when(条件)]](只要有for each row 就是)
plsql块

/*
触发器应用:禁止在非工作时间插入新员工
非工作时间
1 周末 to_char(sysdate,’day’) in (‘星期六’,’星期日’)
2 上班前 下班后 to_number(to_char(sysdate,’hh24’) no between 9 and 17
*/
create or replace trigger securityemp
before insert
on s_emp

begin
if to_char(sysdate,’hh24’) in (‘星期六’,’星期日’) or
to_number(to_char(sysdate,’hh24’)) not between 9 and 17 then
–禁止insert操作
raise_application_error(-20001,’不能在非工作时间插入新员工’);
end if;
end;

触发语句与伪记录变量的值
触发语句 :old :new
insert 所有的字段都是空(null) 将要插入的数据
update 更新以前该行的值 更新后的值
delete 删除以前该行的值 所有字段都是空(null)

/*
触发器应用2 数据确认
涨后的薪水不能小于涨前的薪水
行级触发器
*/
create or replace trigger checksalary
before update
on s_emp
for each row
begin
if :new.salary<:old.salary then
raise_application_error(-20003,’涨后的薪水不能小于涨前的薪水,涨前:’||:old.salary||’涨后:’||:new.salary);
end;

限制每个部门只招聘5名职员，超过计划则报错

关系型数据库
基于关系模型提出的数据库
关系模型：用二维表行和列保存数据的模型

数据库
物理概念
是一堆文件
实例
逻辑概念
操作数据时，需要将文件读入内存中，内存中的镜像就是实例
我们必须通过操作Oracle实例来操作Oracle数据库
Oracle由一个Oracle数据库和多个Oracle实例组成
集群是一组协同工作的服务实体，用以提供单一服务实体更具扩展性与可用性的服务平台。在客户端看来，一个集群就像一个服务实体，但事实上集群由一组服务实体组成
分布式是并联工作
集群是串联工作
分布式是将不同的业务分布在不同的地方。集群将几台服务器集中在一起，实现同一业务
分布式中的每一个节点，都可以做集群。而集群并不一定是分布式

Oracle集群
RAC real application cluster
优点
1 load Balance 负载均衡 多个服务器组合在一起，当客户端请求时，服务器将请求分开
2 Fail Over 失败迁移 当一个服务器坏掉，将请求转给其他服务器

Oracle体系结构
jps 用于列出当前机器中java程序进程号
OS进程
查看Oracle进程 linux ps -ef | grep ora_
SGA system global area
PGA process global area
客户端连接到实例上，操作的是PGA，当客户端执行insert，update时，数据是保存在PGA下，是无法操作SGA，当执行commit时，PGA中数据保存到SGA，SGA再通过写进程，
保存到磁盘中的文件
两阶段提交优点
1 使Oracle写的速度更快 多个PGA中数据先保存到SGA，然后一次性保存到磁盘文件中，速度快

连接其他数据库 
sqlplus scott/1234@192.168.137.123:1521/yiyu
1
2

表空间和数据文件(.dbf)
表空间由多个数据文件组成 数据文件只能属于一个表空间
表空间为逻辑概念，数据文件为物理概念
段存在于表空间中
段是区的集合
区是数据块的集合
数据块会被映射到磁盘块

sqlplus yiyu/yiyu@118.89.234.118:1521/XE
查询语句
oracle 中所有表都属于用户，用户属于数据库，数据库下有多个用户，用户下才有表
show * from tab; 当前用户的表
所有员工的信息
–设置行宽
show linesize

set linesize 150
设置列宽
col last_name format a20(a表示字符串)
col salary for 9999(9代表一个数字)

–通过列名来查询
select last_naem,salary from s_emp;

–查询员工信息 员工号 姓名 月薪
select id,last_name,salary from s_emp

–c命令 change
select *
form s_emp
l 得到当前缓冲区中的命令
然后用数字得到第几行命令，使光标移动到那里
c /form/from
/执行上一条SQL语句
查询员工信息 员工号 姓名 月薪 年薪 年收入
select id,last_name,salary,salary*12,comm,sal*12+comm
from s_emp;

oracle滤空函数 nvl
select id,last_name,salary,salary*12,comm,sal*12+nvl(comm,0)
from s_emp;

查询奖金为空的员工
select * from s_emp where comm is null;

edit(ed) 会把上一条SQL语句放入系统默认的编辑器中
select id as “用户号”,last_name “姓名” salary 薪水,salary*12,comm,sal*12+nvl(comm,0)
from s_emp;

别名的问题
1 当有特殊符号时，需要使用”“来括起来
2 当使用数字时，也需要使用”” 别名使用”“,以便在别名中包含空格或者特殊字符，并区分大小写

distinct作用于后面的所有的列 组合起来不一样即可

连接符||和 concat()

dual表是伪表
伪列
select ‘hello’||’ world’ from dual;

字符串可以是select列表中的一个字符，数字，日期
日期和字符只能在单引号中出现
每当返回一行，字符串被输出一次
双引号表示列的别名
字符和日期要包含在单引号中
字符大小写敏感，日期格式敏感
默认日期格式为DD-MON-RR

查询名叫KING的员工
select * from s_emp where last_name =’KING’;

查询入职日期为17-11月-81的员工
select * from s_emp where start_date=’17-11月-81’;
修改日期格式
select * from v$nls_parameters;
alter session set NLS_DATE_FORMAT=’yyyy-mm-dd’;

比较运算
赋值使用:=值

between and 在…之间 含有边界 小值在前 大值在后
select * from s_emp where between 1000 and 2000;

in 在..之中
查询部门号是10和20的员工
select * from s_emp
where dept_id in(10,20);

mysql事务需要手动开启
Oracle事务自动开启

如果集合中null，不能使用not In 但是可以使用 in
查询名字中含有下划线的员工
转义字符 escape
select * from s_emp
where last_name like ‘%_%’ escape ‘\’;

逻辑运算
and
or
not
where condition1 and condition2
和where condition2 and condition1
是完全不相同的,但是结果是相同的

select concat(‘hello’,’world’);
hive hadoop数据仓库(hive支持SQL92标准)

SQL和SQLplus
SQLplus是工具
select insert update delete是SQL
c ed for col desc 都是SQLplus语句
SQL SQLplus
一种语言 一种环境
关键字不能缩写 关键字可以缩写
使用语句控制数据库中 命令不能改变数据库中的数据的值
表的定义信息和表中数据 集中运行

Oracle服务

查询10号部门员工
select *
from s_emp
where dept_id=10;
查询员工信息，按照月薪排序
order by 后面+列，表达式 别名 序号
select * from s_emp order by salary;

select * from s_emp order by salary*12 desc;

select id,last_name,salary*12 年薪 from s_emp order by 年薪 desc
select id,last_name,salary from s_emp order by 1;
函数
单行函数
字符函数
大小写控制函数
lower
upper
initcap
字符控制函数
concat
substr
length/lengthb
instr
lpad|rpad
trim
replace
substr(a,b) 从a开始，第b为开始取
select substr(‘Hello World’,3) 子串 from dual;
substr(a,b,c) 从a中，第b位开始取，取c位
select substr(‘Hello World’,3,4) 子串 from dual;
length字符数 lengthb字节数
select length(‘Hello World’) 字符,lengthb(‘Hello World’) 字节 from dual
select length(‘北京’) 字符,lengthb(‘北京’) 字节 from dual;
instr(a,b)在a字符串中查找b字符串
select instr(‘Hello World’,’ll’) 位置 from dual;
lpad 左填充 rpad右填充
select lpad(‘abcd’,10,’‘) 左,rpad(‘abcd’,10,’‘) 右 from dual;
trim 去掉前后指定的字符
select trim(‘H’ from ‘Hello WorldH’) from dual;
replace 替换
select replace(‘Hello World’,’l’,’*’) from dual;
结果为He**o Wor*d
round(45.926,2) 45.93
round(45.926,1) 45.9
round(45.926,0) 46
round(45.926,-1) 50
round(45.926,-2) 0
截断trunc(45.926,2) 45.92
trunc(45.926,1) 45.9
trunc(45.926,0) 45
trunc(45.926,-1) 40
trunc(45.926,-2) 0
MySQL 表示年月日 date datetime
oracle date=日期+时间
当前时间 select sysdate from dual;
多行函数(分组函数)
分组函数作用于一组数据，并对一组数据返回一个值
常用的组函数
avg
count
max
min
sum

工资总额
select sum(salary) from s_emp;
人数
select count(*) from s_emp;
平均工资
select sum(salary)/count(*) 一,avg(salary) from s_emp;
平均奖金
1 select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三 from s_emp;
二和三是一样的
count(计数)函数
count(expr)返回expr不为空的记录总数

分组数据 group by子句语法
group by将表中数据分为若干组
select a,b,c,组函数(x)
from table
group by a,b,c;
在select列表中所有未包含在组函数中的列都应该包含在group by子句中
包含在group by子句中的列不必包含在select列表中
所用包含于select列表中，而未包含于组函数中列都必须包含在group by子句中

多个列的分组
select dept_id,job,sum(salary)
from s_emp
group by dept_id,job;
分组先按照第一个列分组，如果第一个列相同，按照第二个列分组，依次类推
求平均工资大于2000的部门
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>2000;

where和having的区别:where之后半年使用组函数，可以在having子句中使用组函数

求10号部门平均工资
select dept_id,avg(salary)
from s_emp
where dept_id=10
group by dept_id;

group by增强
select dept_id,job,sum(salary) from s_emp group by dept_id,job
+
select dept_id,sum(salary) from s_emp group by dept_id
+

select sum(salary) from s_emp;

select dept_id,job,sum(salary) from s_emp group by rollup(dept_id,job);

抽象

group by rollup(a,b)

group by a,b
+
group by a
+
没有分组

多表查询
笛卡尔集:两个表的列数相加，行数相乘
避免使用笛卡尔全集

连接条件至少有n-1个 (n为表的个数)

等值连接 cross joins
不等值连接 natural joins
外连接 using clause
自连接 full or two sided outer joins

等值连接
查询员工信息 员工号 姓名 月薪 部门
select e.id,e.last_name,e.salary,d.id
from s_emp e,s_dept d
where e.dept_id=d.id

不等值连接
查询员工信息 员工号 姓名 月薪 工资级别
select e.id,e.last_name,e.salary,s.grade
from s_emp e,salgrade s
where e.salary between s.losal and s.hisal;

外连接
按部门统计员工信息 部门号 部门名称 人数
select d.id 部门号,d.name 部门名称,count(e.id) 人数
from s_emp e,s_dept d
where e.dept_id=d.id
group by d.id,d.name;

上面是不正确的，有的部门没有人
任然希望包含在最后的结果中

左外连接:当where e.dept_id=d.id不成立的时候，等号左边任然被包含在最后的结果中
写法 where e.dept_id=d.id(+)
select e.last_name,d.name
from s_emp e left outer join s_dept d
on e.dept_id=d.id;
右外连接:当where e.dept_id=d.id不成立的时候，等号右边的表任然被包含在最后的结果中
写法where e.dept_id(+)=d.id
select e.last_name,d.name
from s_emp e right outer join s_dept d
on e.dept_id=d.id;

自连接 通过表的别名，将同一张表视为多张表
查询员工信息 员工姓名 老板姓名
select e.last_naem,d.last_name
from s_emp e,s_emp d
where e.mga=d.id;

自连接不适合操作大表

层次查询
select **
from s_emp
connect by 上一层的员工=当前层老板
start with 起始条件

select level id,name,mgr
from s_emp
connect by prior id=mgr
start with id=7838;(start with mgr is null)
order by l;

select
select dept_id,max(salary)
from s_emp
group by dept_id;

叉集
使用cross join子句使连接的表产生叉集
叉集合笛卡尔集是相同的
select last_name,dept_id
from s_emp
cross join s_dept;

自然连接
Natual join子句，会以两个表中具有相同名字的列为条件创建等值连接
在表中查询满足等值条件的数据
如果只是列名相同而数据类型不同，则会产生错误
select dept_id,dept_name,location_id,city
from departments matual join locations;

使用using子句创建连接
在natual join子句创建等值连接时，可以使用using子句指定等值连接中需要用到的列
使用 USING 可以在有多个列满足条件时进行选择。
不要给选中的列中加上表名前缀或别名 。
NATURAL JOIN 和 USING 子句经常同时使用 。

子查询
查询工资比Scott高的员工信息
1 scott的工资
select sal from emp where ename=’scott’;
2查询工资高于3000的员工
select * from emp where sal>(select sal from emp where ename=’scott’);

子查询解决的问题:不能一步求解
注意的问题
1 合理的书写风格
2 括号
3 可以在主查询的where select having from后面使用子查询
4 不可以在group by后面使用子查询
5 强调from后面的子查询
6 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用即可
7 一般不在子查询排序，但是top-n分析问题中，必须对子查询排序
8 一般先执行子查询，再执行主查询，但是相关子查询例外
9 单行子查询只能使用单行操作符，多行子查询只能使用多行操作符
10 子查询中的null问题
在select语句后面的子查询只能是单行子查询
原因为:Oracle为关系型数据库，使用二维表来保存数据，所以每一行上列中只能有一条数据，不能出现多条
select empno,ename,sal,(select job from emp where empno=1) 第四列
from emp;

oracle很多问题都可以通过在from语句后面使用子查询来解决
select *
from (select empno,ename,sal from emp);

查询部门名称为sales的员工
select *
from emp
where deptno=(select deptno
from dept
where dname=’sales’);
或者
select e.*
from emp e,dept d
where e.deptno=d.deptno and d.name=’sales’;

top-n分析问题

执行顺序

单行子查询
只返回一行
使用单行比较操作符
= > >= < <= <>
select ename,job,sal
from emp
where job=(select job from emp where empno=7566)
and sal>(select sal from emp where empno=7782);
oracle子查询最多可以嵌套255个

查询最低工资的员工
select ename,job,sal
from emp
where sal=(select min(sal)
from emp);
子查询中having子句
首先执行子查询
向主查询中having子句返回结果
select deptno,min(sal)
from emp
group by deptno
having min(sal)>(select min(sal) from emp where deptno=10);

非法使用子查询
select empno,ename
from emp
where sal>(select min(sal) from emp group by deptno);
多行子查询使用单行比较符
group by子句得出的结果为一个集合，而前面的值为单个，二者不匹配

单行子查询中空值问题
select ename,job
from emp
where job=(select job from emp where ename=’mike’);
子查询不返回null

多行子查询
返回多行 使用多行比较操作符
in 等于列表中的任何一个
any 和子查询返回的任意一个值比较
all 和子查询返回的所有值比较

查询部门名称为sales和accountting的员工
select *
from emp
where deptno in (select deptno from dept where dname=’sales’ or dname=’accountting’);
如果查询不是sales和accounting部门的员工

any 和集合中任意一个值比较
查询工资比30号部门任意一个员工高的员工信息
select *
from emp
where sal> any (select sal from emp where deptno=30);
或
select *
from emp
where sal> any (select min(sal) from emp where deptno=30);

all 和集合中中所有值比较
查询工资比30号部门所有员工高的员工信息
select *
from emp
where sal> all (select sal from emp where deptno=30);
或
select *
from emp
where sal> (select max(sal) from emp where deptno=30);

any 比子查询返回结果中的某个值大 any 相当于min
=any 与子查询返回结果中的某个值相等

select empno,ename,sal from emp where sal in(select sal from emp where deptno=10)

select empno,ename,sal from emp where sal=any(select sal from emp where deptno=10)

=any()括号中即使出现重复的值，也不会保错

多行子查询中的null
如果集合中含有null,不可使用not in,可以使用In

查询不是老板的员工
select *
from emp
where empno not in(select mgr from emp where mgr is not null);
会出现问题

查询是老板的员工
select *
from emp
where empno in(select mgr from emp);

课堂练习
select rownum,empno,ename,sal from emp;
找到员工表中工资最高的前三名 –使用rownum
rownum 行号
select *
from (select rownum,e.*
from (select empno,ename,sal from emp order by sal desc) e
)
where rownum>=3;

mysql分页查询limit
oracle分页使用rownum
Oracle读取是读取完第一行，然后再读取第二行，rownum永远从第一行开始，取了第一行，才能取第二行，不能直接取第二行

select rownum,empno,ename,sal
from emp
order by sal desc;
此时的rownum并不是从1开始，而是之前未排序的rownum，原因在于当select执行在order by之前，并且排序时会创建临时表，产生新的rownum值，但是并未使用。而我们看到的是临时表

Oracle表有标准表，索引表 临时表
临时表创建
1 手动创建 create global temporary table *
2 自动创建 排序
特点 当事务或者会话结束时，表自动删除
create global temporary table temptest1 创建临时表，当提交删除行时，自动加入值
(tid number,tname varchar2(20))
on commit delete rows;

基于会话的临时表
create global temporary table temptest1
(tid number,tname varchar2(20))
on commit preserve rows;

相关子查询:将主查询中的值作为参数传递给子查询

找到员工表中薪水大于本部门平均薪水的员工
select e.*
from emp e,(select deptno dept_id,avg(sal) salary from emp group by deptno) d
where e.deptno=d.dept_id and e.sal>d.salary;
或者 采用相关子查询
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);

统计每年入职的员工个数
oracleif/else功能的实现3种写法
1 标准SQL规范
单个if
if a=…then ….
end if;

   if a=...then...
   else ...
   end if;

   if a=...then...
   elsif ...
   elsif ...
   end if;
1
2
3
4
5
6
7
8

2decode函数
decode(value,if1,then1,if2,then2….else)
表示如果value等于if1时，decode函数结果返回then1….如果不等于任何一个if值，则返回else
3case then
case when a=’1’ then ‘xxx’
when a=’2’ then ‘sss’
else
‘zzz’
end as
注意点：

1、以CASE开头，以END结尾
2、分支中WHEN 后跟条件，THEN为显示结果
3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加
4、END 后跟别名

case when在语句中不同位置的用法
1 select case when用法
用来统计每段的总数
select grade,count(case when sex=1 then 1
else null
end) 男生数,
count(case when sex=2 then 2
else null
end) 女生数
from students
group by grade;

2 when case when用法
select t2.,t1.
from t1,t2
where (case when t2.compare_type=’a’ and t1.some_type like ‘nothing%’
then 1
when t2.compare_type !=’a’ and t1.some_type not like ‘nothing%’
then 1
else 0
end)=1

group by case when用法
select
case when salary<=500 then ‘1’
when salary>500 and salary<=600 then ‘2’
when salary>600 and salary<=800 then ‘3’
when salary>800 and salary<=1000 then ‘4’
else null end salary_class,–别名命名
count(*)
from table_a
group by
case when salary<=500 then ‘1’
when salary>500 and salary<=600 then ‘2’
when salary>600 and salary<=800 then ‘3’
when salary>800 and salary<=1000 then ‘4’
else null end;

行转列
wm_concat(varchar2)组函数

select deptno，wm_concat(ename) nameslist
from emp
group by deptno;

关于rownum
1 rownum永远按照默认的顺序生成
2 rownum只能使用< <= 不能使用> >=

查询10和20号部门的员工
select count(*) total,sum(decode(to_char(hiredate,’yyyy’),’1980’,1,0)) ‘1980’
sum(decode(to_char(hiredate,’yyyy’),’1981’,1,0)) ‘1981’
sum(decode(to_char(hiredate,’yyyy’),’1982’,1,0)) ‘1982’
sum(decode(to_char(hiredate,’yyyy’),’1987’,1,0)) ‘1987’
from emp;

select * from emp where deptno in (10,20)
select * from emp where deptno=10 or deptno=20;
集合运算
select * from emp where deptno=10
union
select * from emp where deptno=20
关于集合运算
1 参与运算的各个集合必须列数相同，且类型一致
2 采用第一个集合作为最后的表头
3 order by永远在最后
4 括号
select deptno,job,sum(sal) from emp group by deptno,job
union
select deptno,to_char(null),sum(sal) from emp group by deptno
union

select to_number(null),to_char(null),sum(sal) from emp;

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

sql执行时间的开关
set timing on

SQL类型
1 DML(data manipulation language数据操作语言):insert update delete select
2 DDL(data definition language数据定义语言):create table,alter table,drop table,truncate table create/drop view,sequence,index,synonym(同义词)
3 DCL(data control language数据控制语言) grant 授权 revoke 撤销权限
插入insert
insert into emp(empno,ename,sal,deptno) values(1001,’tom’,2000,10);

PreparedStatement pst=”insert into emp(empno,ename,sal,deptno) values(?,?,?,?)”;
地址符
insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
select empno,ename,&t from emp;
用于自己将其替换，然后再显示输出
select * from &t;

一次插入多条数据
create table emp10 as select * from emp where 1=2;
将emp中所有10号部门的员工插入到emp10中
在insert语句中加入子查询，不必书写values子句
子查询中的值列表应该与insert子句中的列名对应

insert into emp10 select * from emp where deptno=10;

海量插入数据
1 数据泵(plsql程序:dbms_datapump 完成数据导入导出)
2 SQL*Loader工具
3 使用外部表

delete逐条删除，truncate先摧毁表，再重建
delete是DML(可以回滚) truncate是DDL(不可回滚)
delete不会释放空间，而truncate会释放空间
delete可以闪回(flashback) truncate不可以
delete会产生碎片 truncate不会

Oracle插入顺序为从后往前插入
Oracle每一张表都维护一个指针:HWM(high water mark 高水位线:下次insert的位置)
当删除一行后，再插入一行，此时会插入到高水位线后，会产生碎片
去掉碎片
1 alter table 表名 move;
2 数据的导出和导入
exp导出 imp导入
expdp增强版导出 impdp增强版导入

数据库事务
数据库事务由下面部分组成
一个或多个DML语句
一个DDL数据定义语言
一个DCL数据控制语言

Oracle事务的标志
1 起始标志:事务中第一条DML语句
2 结束标志:提交 显式commit
隐式 正常退出exit,DDL(truncate=清空表+commit),DCL
回滚 显式 rollback
隐式 关闭窗口 掉电 宕机
commit和rollback语句的优点
1 确保数据完整性
2 数据改变被提交之前预览
3 将逻辑上相关的操作分组
回滚到保留点
使用savepoint在当前事务中创建保留点
使用rollback to savepoint语句回滚到创建的保留点

数据库的隔离级别
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，会导致各种并发问题
脏读 : 对于两个事物 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段.
之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.
之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事物 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中
插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.

数据库命名规则
表名和列名
必须以字母开头
必须在1-30个字符之间
必须只能包含A-Z a-z 0-9 _ $ #
不能和用户定义的其他对象重名
必须不能是Oracle的保留字
Oracle默认存储都是大写
数据库名只能是1-8位，datalink可以是128位(分布式数据库),其他一些特殊

rowid 行地址 指向dbf文件的位置，在该位置上保存了对应的记录

Oracle回收站
查询回收站 show recyclebin;
清空回收站 purge recyclebin;
从回收站中返回数据
oracle 10g 6种不同闪回 oracle 11g 7种
闪回 flashback table testsavepoint to before drop;
Oracle中有重名的表
flashback table “BIN$fsuiVf1UT)a5qvlz6=$0” to before drop;
注意Oracle不是所有用户都有回收站
管理员是没有回收站的

密码认证
sqlplus sys/password as sysdba
主机认证(外部认证)
sqlplus sys/asdffsf as sysdba
sqlplus ad/asdffs as sysdba
全局认证

约束是表一级的限制
如果存在依赖关系，约束可以防止错误的删除数据
约束类型
not null
unique
primary key
foreign key
check检查性约束
create table test
(
tid number.
tname varchar2(20),
gender varchar2(2) constraint emp_gender check (gender in (‘男’,’女’)),
sal number check constraint emp_salary_min (sal > 0)
);
constraint 为异常起名字

外键约束
foreign key 在子表中，定义了一个表级的约束
reference 指定表和父表中的列
on delete cascade 当删除父表时，级联删除子表记录
on delete set null 将子表的相关记录的外键值置为null

create table student
(
sid number constraint student_pk primary key,
sname varchar2(20) constraint student_name_notnull not null,
gender varchar2(20) constraint student_gender check (gender in (‘男’,’女’)),
email varchar2(40) constraint student_email_unique unieque
constraint student_email_notnull not null,
deptno number constraint student_fk references dept(deptno) on delete set null
);

数据库对象
表
视图
create view empinfoview
as select e.empno,e.ename,e.sal,e.sal*12
from emp
where deptno=10
with read only;只读
视图是一种虚表
视图建立在已有的表中,

索引

SQL语言大小写不敏感
SQL可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写
乘除优先级高于加减
优先级相同，按照从左到右运算
可以使用括号改变优先级

SQL语句中null值(null是无效的，未指定的，未知的或不可预知的值，null不是空格或者0)
1 包含null的表达式都为null
2 null永远!=null
3null排序问题 null最大
4组函数(多行函数)自动滤空，嵌套滤空函数来屏蔽它的滤空函数

SQL执行计划–一条查询语句在oracle中的执行过程或访问路径的描述
写完一段SQL代码后，按F5，plsql会自动打开执行计划窗口，显示该SQL的执行计划
执行第一行所对应的cost(即成本耗费)值，
sql执行计划，决定了SQL的好坏

sql优化的原则
1 尽量使用列名
2 where解析的顺序是从右往左
3尽量使用where
4当子查询和多表查询都可以使，尽量使用多表查询(在Oracle中，大多数子查询在执行时，都转换为多表查询)
5当union和union all都可以用时 尽量使用union all(union=union all+distinct)
6尽量不要使用集合运算，集合运算会随着集合数量增多而执行效率降低

当and时，尽量将假的放在右边，and特性:当第一个为假，就不会继续执行
当or时，尽量将真的放在右边，or特性：当第一个为真，就不会继续执行

oracle持久一个数据库步骤
1 创建两个数据库文件
2 创建用户与上面的文件形成映射关系
3 给用户添加权限

1 创建两个数据库文件(monitor.dbf和monitor_temp.dbf两个文件)
create tablespace monitor lo