查看: 2859|回复: 20
OCP11G 的教材以及上机实验-2014，给ORACLE的入门者以及进阶者提供帮助
认证徽章论坛徽章:55
&&为了巩固自己的知识点，提高其理论水平以及动手能力，同时在上课时作为教材，本人花了约半年的时间（当然是上班的空闲时间），整理了管理I的相关教材以及上机实验，给ORACLE的入门者以及进阶者提供帮助，同时也请大家提出意见，文档中包括官方文档以及官方PPT,官方的学生指南，以及自己或者他人的文档,当然也有以前培训老师的文档。管理II，相信在近期也完成。
(2.04 MB, 下载次数: 360)
16:08 上传
点击文件名下载附件
(1.83 MB, 下载次数: 374)
16:09 上传
点击文件名下载附件
(1.36 MB, 下载次数: 385)
16:09 上传
点击文件名下载附件
_161253.png (223.17 KB, 下载次数: 4)
16:13 上传
认证徽章论坛徽章:2
认证徽章论坛徽章:55
blzys 发表于
共同进步。
论坛徽章:0
非常感谢大神的资料分享
招聘 : 论坛徽章:25
用心了，谢谢。
论坛徽章:0
谢谢分享！！!
认证徽章论坛徽章:21
谢谢分享！
认证徽章论坛徽章:55
super-patpat 发表于
谢谢分享！！!
tks,共同进步。
认证徽章论坛徽章:27
感谢LZ无私贡献
认证徽章论坛徽章:55
desert_xu 发表于
谢谢分享！
tks,共同进步。
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号Oracle 实训资料 - 博客频道 - CSDN.NET
super2007的专栏
潜心悟道，诚心待人。用心编码，专心调试。
分类：Oracle
SQLdatebase (DB)
Structured query language 结构化查询语言
DDL(数据定义语言)
Date definition language
create table 建表
alter table 修改表结构
drop table 删表
column data type width constraints(约束)
DML(数据操作语言)
Data manipulation language
insert update delete
data row record
TCL(事物控制语句)
Transcation control language
commit(提交) rollback(回滚) savepoint
DQL(数据查询语言)
Data query language
Installsoftware rdbms 安装软件
createdatabase 创建数据库
登录数据库 sqlplus username/password
ORACLE_SID(环境变量) 数据库对应的实例的名字，该名字决定了连接哪个具
体的数据库
show user 查看当前用户
desc tablename 查看表结构 desc describe的缩写
查询员工的姓名，和工资
selectfirst_name,salary
查询员工的名字和职位
selectfirst_name,title
edit 修改sql语句 l 查看& /运行
from s_ 列出部门表的所有信息
列出每个员工的年薪
selectfirst_name,salary*12
列出每个员工的总收入
selectfirst_name,salary*12*(1+commission_pct/100) &tol sal&
空值会导致算术表达式为空，Oracle认为null为无穷大
selectfirst_name,salary*12*(1+nvl(commission_pct,0)/100) tol_sal,
Commission_pct
nvl(p1,p2)
if(p1 is null) then return p2;
else return p1;
coalesce 和nvl实现的是同样的功能，nvl只能用在Oracle，coalesce可以用在多种数据库,nvl的两个参数的类型必须一致
给列起别名在列后直接跟别名，别名有空格，或者大小写敏感，要给别名加“”
将姓和名拼接起来&
''表示字符串，“”表示别名，||表示字符串的拼接
selectfirst_name||last_name employee
selectfirst_name||' '||last_name employee
selectfirst_name||' is in department '||dept_id||'.'
列出有哪些部门
selectdistinct name
distinct 去重(null值夜只保留一个)，只能放在select后
列出公司有哪些不同的职位
selectdistinct title
set feed on 显示查询返回的记录数
各个部门有哪些不同的职位
selecttitle,dept_id
from s_部门号和职位联合起来唯一
列出工资大于1000的员工
selectfirst_name,salary
from s_emp where salary&1000;
年薪大于12000的员工(where语句后的字段最好不要使用表达式，影响效率)
selectfirst_name,salary*12
from s_emp where salary&1000;
where子句不能跟列的别名
列出Carmen的年薪
selectfirst_name,salary
from s_emp where first_name='Carmen';
哪些人的职位是Stock Clerk
selectfirst_name,title
from s_emp where title='Stock Clerk';
哪些员工的工资在之间
selectfirst_name,salary
from s_emp where salary&=1550 and salary&=2000;
selectfirst_name,salary
from s_emp where salary
between1550 and 2000;
列出部门号为31,41,43的员工的工资
selectfirst_name,salary,dept_id
from s_emp where dept_id=31 or dept_id=41 or dept_id=43;
selectfirst_name,salary,dept_id
from s_emp where dept_id
in(31,41,43);
selectfirst_name,salary,dept_id
from s_emp where dept_id
=any(31,41,43);
%表示0或多个字符
_表示任意一个字符
select last_name
from s_emp wherelast_name
like 'M%';
系统表，user_tables记录数据库中有哪些表
查询当前用户下有哪些表
selecttable_name
from user_
查找用户下所有以“s_”的表名
selecttable_name
from user_tables where table_name like 'S\_%' escape '\';
escape '\' 表示定义\为转义字符
哪些员工没有提成
selectfirst_name,commission_pct
from s_emp where commission_
判断字段的值是否为空 isnull
而不是 =null
哪些员工有提成
selectfirst_name,commission_pct
from s_emp where commission_
列出除了31,41,43部门的员工的工资
selectfirst_name,dept_id,salary
from s_emp where dept_id
not in(31,41,43);
selectfirst_name,dept_id,salary
from s_emp where dept_id &&31 and
dept_id &&41 and dept_id &&43;
selectfirst_name,dept_id,salary
from s_emp where dept_id
&&all (31,41,43);
如果集合里面包含null值，not in
一定查不出结果，null与任何值比较都为false
列出部门号为32,42，工资大于1500的员工
selectfirst_name,dept_id,salary
from s_emp where salary&1500 and dept_id in(32,42);
列出员工的姓名和工资，并按工资由大到小排序
selectfirst_name,salary
from s_emp
order by 后的字段要想使用索引，必须保证不能为null
selectfirst_name,salary
from s_emp where salary is not null
列出42部门的员工信息，按年薪降序排列
selectfirst_name,dept_id,salary*12 ann_sal
from s_emp where dept_id=42
selectfirst_name,dept_id,salary*12 ann_sal
from s_emp where dept_id=42 order by ann_
selectfirst_name,dept_id,salary*12 ann_sal
from s_emp where dept_id=42 order by 3
按部门号升序排列，同一部门按工资的降序排列
selectfirst_name,dept_id,salary
from s_emp order by dept_id,salary
单行函数：每一条记录都对应有结果集
lower()& 将字符串转化为小写
select lower('SQL')
upper()& 将字符串转化为大写
initcap() 将字符的首字母大写
concat(string,string) 字符串的拼接
substr(string,1,3) 字符串的截取,第二个参数表示字符串的起始位置，第三个表示截取的长度
length(string) 字符串的长度
lpad(string,10,'*') 字符串的长度是10，不足的在左边补'*'
rpad(string,10,'*') 字符串的长度是10，不足的在右边补'*'
调文字值和表没有关系的时候使用系统表dual
查询Carmen的工资（不知道大小写）
select first_name,salary
froms_emp where lower(first_name)=
'carment';
找出每个人名字的最后两个字符
select first_name,substr(first_name,length(first_name)-1,2)from s_
select first_name,substr(first_name,-2,2)from s_
负数表示从右向左
数值类型
round(处理的数字,保留小数点后几位) 四舍五入
trunc(处理的数字,截取几位) 截取
to_number('1550') 转化为数字
to_number(字段,'xx') 按16进制处理字段值
隐式数据类型转换，默认字符转为数字，可能导致索引用不了
to_char(字段,'$99,999,99') 按格式转化成字符串
格式：9代表数字位 0代表定义宽度大于实际值宽度，有0补齐
& L 表示本地货币
& 如果显示位数不足（定义宽度小于实际值宽度），用#代替
更改本地语言
NLS_LANG='SIMPLIFIEDCHINESE_CHINA.ZHS16GBK'
export NLS_LANG
NLS_LANG=AMERICAN_AMERICA.US7ASCII
将没有领导的员工的领导位置设为boss
select first_name,nvl(to_char(manger_id),'Boss')
列出员工的名字，和部门的名称
cross join 笛卡尔积，两个表的每条记录都匹配
selectfirst_name,dept_id,name
from s_emp cross join s_300条
selecte.first_name,e.dept_id,d.id,d.name
from s_emp e cross join s_
如果两个表里有字段名相同，必须指出是哪个表的字段
inner join 内连接(将满足条件的记录匹配,精确匹配)可省略的写成join
selecte.first_name,e.dept_id,d.id,d.name
from s_emp e inner join s_dept d
one.dept_id=d. 25条
列出部门号，地区号，地区名称
selectd.id,d.name,r.id,r.name
from s_dept d join s_region r
ond.region_id=r.
等值连接：两张表有描述共同属性的列
非等值连接：可以用between and把两张表中的列写成表达式
自连接：同一张表的列之间有关系实际反映的是同一张表的行之间有关系，通过给表起别名将同一张表的列之间的关系转换成不同表的列之间的关系
所谓表之间的关系，实际指表中的行之间的关系，该关系通过将表中的列写成表达式来体现
Carmen在哪个部门上班
selecte.first_name,d.name
from s_emp e join s_dept d
one.dept_id=d.id and e.first_name='Carmen';先做过滤在做连接
亚洲地区有哪些部门
selectd.id,d.name,r.name
from s_dept d join s_region r
ond.region_id=r.id and r.name='Asia';
Carmen在哪个地区上班
select e.first_name,r.namefrom s_emp e join s_dept d
on e.dept_id=d.id and
e.first_name='Carmen'join s_region r
ond.region_id=r.
亚洲地区有哪些员工
select e.first_name,r.namefrom s_emp e join s_dept d
on e.dept_id=d.id joins_region r
on d.region_id=r.id and r.name='Asia';
列出员工的工资，名字，及工资的级别
selecte.ename,e.sal,s.grade
from emp e join salgrade s
one.sal between s.losal
3级和5级有哪些员工
select e.ename,e.sal,s.grade from emp e
joinsalgrade s on e.sal
betweens.losal and s.hisal and
s.gradein (3,5);
列出每个员工的领导
selecte.first_name,m.first_name from emp e
join emp m one.manager_id=m.
哪些人是领导
selectdistinct m.first_name from emp e
join emp m one.manager_id=m.
外连接 leftouter join&
right outer join&& outer可以省略
from t1 left join t2 on t1.c1=t2.c2&
表示t1做驱动表
from t1 right join t2 on t1.c1=t2.c2
表示t2做驱动表
外连接把驱动表不能匹配的记录也会放在结果集里，没有匹配上的字段设置
为null，驱动表里的数据一个都不能少
外连接的结果集=内连接的结果集+t1表中匹配不上的记录和一条null记录的组合
关键：谁做驱动表
列出每个员工的领导，并且打印出boss
selecte.first_name,nvl(m.first_name,'Boss') from emp eleft
join emp m on e.manager_id=m.
哪个部门没有员工
select e.ename,d.deptnofrom
emp e right join deptd
on e.deptno=d.deptno wheree.这里用where不用and，因为and是先过滤在连接
哪些人是员工(哪些人不是领导)
select e.first_name,m.first_namefrom
s_emp e right join s_empm
on e.manager_id=m.id wheree.
哪个部门没有叫SMITH的员工
select d.deptno,d.dnamefrom
emp e right join deptd
on e.deptno=d.deptno ande.ename='SMITH'
哪个部门没有叫SMITH的部门(Oracle)
selectd.dname
from emp e,dept d where e,deptno(+)=d.deptnoand e.ename(+)='SMITH'and
第一个+表示dept表为驱动表，第二个+表示在 连接之前过滤
操作在一组记录上，每组返回一个结果
处理的是所有的非空值（count(*)表示记录数）
如果所有记录都是null，count返回0，其他返回空
avg 平均值(数值)
sum 求和（数值）
count 计数（字符，数字，日期）
max 最大值（字符，数字，日期）
min 最小值（字符，数字，日期）
工资的平均值，提成的平均值，和提成的平均值
avg(salary),avg(commission_pct),max(commission_pct) froms_
所有人提成的平均值
sum(commission_pct)/count(*)from s_
avg(nvl(commission_pct,0))from s_
group by 给记录分组
根据group by子句指定的表达式，将要处理的数据（若有where子句即为通过条件过滤后的数据）分成若干组，每组有唯一标识，组内有若干条记录，根据select语句后的组函数处理每个组的记录，每个组返回一直值
各个部门的平均工资
avg(salary),dept_id froms_emp
group by dept_
各个职位的平均工资
selecttitle
avg(salary) froms_emp
每个提成有多少人
selectcommission_pct,count(*)
froms_emp group by commission_
分组时所有的空值都在同一个组
各个部门有多少种不同的职位
selectdept_id,count(distincttitle)
from s_emp groupby dept_
各个部门不同职位的平均工资
selectdept_id,title,avg(salary)
from s_emp group bydept_id,
42部门的平均工资，列出部门号
max(dept_id),round(avg(salary))
from s_empwhere dept_id=42;
selectdept_id,round(avg(salary))from s_emp
wheredept_id=42 group by
若没有groupby子句，select后面有一个是组函数，其他都必须是组函数
有group by子句，select后面可跟group by后面跟的表达式以及组函数，其他会报错
每个工资级别有多少人
selects.grade,count(e.empno)
fromemp e right
joinsalgrade s on e.sal
betweens.losal and s.hisal
groupby s.grade order by s.
各个部门的平均工资（列出部门名称：部门名称+地区名称）
selectd.name,r.name,avg(e.salary)
from s_emp e joins_dept d
on e.dept_id=d.id join s_region r
ond.region_id=r.id group by d.name,r,
selectmax(d.name),max(r.name),avg(e.salary)
from s_emp e joins_dept d
on e.dept_id=d.id join s_region r
ond.region_id=r.id group by d.
having 对分组后的结果进行过滤(后面跟组函数)
&& 过滤组，而where是过滤记录
哪些部门的平均工资比2000高
selectdept_id,avg(salary)
from s_emp group by dept_id
havingavg(salary)&2000;
32部门和42部门的平均工资
selectdept_id,avg(salary)
from s_emp where dept_id in (32,42)
groupby dept_优化
selectdept_id,avg(salary)
from s_emp group by dept_id
havingdept_id in(32,42);
where和having的区别
where子句过滤的是行（记录）
having子句过滤的是分组（组标识，每组数据的聚合结果）
where子句包含单行函数
having子句只能包含group by 后面的表达式和组函数
where子句执行在前，having子句执行在后
where子句和having子句都不允许用列别名
就是在一条SQL语句中嵌入select语句
先执行子查询，子查询的返回结果作为主查询的条件，在执行主查询
子查询只执行一遍
若子查询的返回结果为多个值，Oracle会去掉重复值后，再将结果返回给主
非关联子查询：主查询和子查询之间没有表连接
单列子查询
谁的工资最低
selectfirst_name,title
from s_emp where salary=(selectmin(salary) from s_emp);
谁和SMITH的职位一样
select last_name,titlefrom s_emp
wheretitle=(select title from s_emp where last_name='Smith')and last_name&&'Smith';
哪些人是领导
selectfirst_name
from s_emp whereid
in (select manager_id from s_emp);
哪些人是员工
selectfirst_name
from s_emp whereid
not in (select manager_id from s_emp wheremanager_id is not null);
not in 比外连接的效率低
哪个部门的平均工资比32部门的平均工资高
selectdept_id,avg(salary)
from s_emp group by dept_id
havingavg(salary)&(select avg(salary) from s_emp where dept_id = 32);
多列子查询
哪个员工的工资等于本部门的平均工资
select first_name,dept_id,salaryfrom s_emp
where(dept_id,salary) in (select dept_id ,avg(salary)from s_emp group by dept_id);
关联子查询
先做主查询，再执行子查询
1.外部查询得到一条记录（查询先从outer表中读取数据）并将其传入到内部查询
2.内部查询基于传入的值执行
3.内部查询从其结果中把值传回外部查询，外部查询使用这些值来完成其处理，若符合条件，outer表中得到的那条记录放入结果集中，否则放弃，该记录不符合条件
4.重复执行步骤1-3，直到把outer表中的所有记录判断一边
哪个员工的工资比本部门的平均工资高
selectfirst_name,dept_id,salary
from s_emp o where salary&(selectavg(salary) from s_emp i where o.dept_id=i.dept_id);
exists运算符（存在）
主查询与子查询的条件是否匹配（一旦匹配就返回，不会继续找下去）
exists采用的是循环方式，判断outer表中是否存在记录只要在inner表中找到一条匹配的记录即可
1.外部查询得到一条记录（查询先从outer表中的去数据）并将其传入到内部查询表
2.对inner表中的记录依次扫描，若根据条件存在一条记录与outer表中的记录匹配，立即停止扫描，返回true，将outer表中的记录放入结果集中，若扫描了全部的记录，没有任何一条记录符合匹配条件，返回false，outer表中的该记录被过滤掉
3.重复执行步骤1-2，直到把outer表中的所有记录判断一边
哪些人是领导
selectfirst_name,dept_id
from s_emp outer where exists (select 'x' from s_emp wheremanager_id=outer.id);
哪个部门有员工
selectdname
from dept d whereexists (select 1 from emp e where e.deptno=d.deptno);
not exists 不存在
哪些人是员工
selectfirst_name,dept_id
from s_emp outer where not exists (select 'x' from s_emp wheremanager_id=outer.id);
in和exists的比较
exists是用循环的方式，由outer表的记录数决定循环的次数，对于exists的影响最大，所以，外表的记录数要少
in先执行子查询，子查询的返回结果去重之后，在执行主查询，所以，子查询的返回结果越少，越适合用该方式
标量子查询
列出员工的名字和领导名字的对应关系
selectfirst_name,nvl((select first_name from s_emp iwhere o.manager_id=i.id),'Boss')
查出每个工资级别的人数
selectgrade,(select count(empno) from emp e where e.sal between s.losal and s.hisal)
基于数据库的开发
将业务需求转换成可操作的数据库
&E 实体，有共同属性的一类对象的集合
属性，描述实体，区分实体
&R 关系，描述实体和实体的关系
&&&&&&&&&&&&&&&&&&&&&&&&&&& 虚线表示可选的，可以
&&&&&&& 表示实体&&&& 实线表示强制的，必须
* 表示强制属性
o 表示可选属性
# 表示唯一属性
实体和实体的关系从实例之间的数量关系的角度分为一对一，一对多，多对多
实体和实体之间的关系从紧密程度上分为必须和可以
递归关系：同一实体里的实例之间有关系
完整性约束
保证数据的一致性
通过数据库的特性或应用程序完成
数据库约束
主键（primary key），唯一键（unique key），外键（foreign key）
实体完整性：主键值唯一且非空（PK）
引用完整性：外键值必须是已存在的主键值或为空（FK）
主键值要求唯一且非空
联合主键：多列联合唯一，任意一列都可以重复，每一列都不能为null
表中只能有一个主键
外键是定义在子表（child table）上一列，它的取值要引用父表（parenttable）
上的主键列或唯一列
外键的定义是基于数据值的，是纯逻辑概念
外键的取值必须匹配主键值或唯一键值还可以是空值
若外键是主键的一部分，它的取值不能为空
1.先create parent table（pk，uk），再create child table（fk）
2.先insert into parent table，再insert into childtable
3.先delete from child table，再delete fromparent table
4.先drop child table，再drop parent table
E-R图向表转换
表和列分别对应实体和属性
1.将实体映射成表
2.将属性映射成列
强制属性定义成非空
列名不能用保留字
3.将唯一标识映射成主键
一个表只能有一个主键
4.将关系映射成外键
一对多：一的那边定义成主键，或唯一键，多的那边定义成外键
一对一：合表
&在外键列上增加唯一约束
&主键即外键
多对多：通过增加中间表将一个多对多关系转换成两个一对多关系
最小化数据冗余
减少完整性问题
标识丢失的实体，关系，表
第一范式：表中不会有重复的记录，即有主属性（pk）；每个属性值不可再
第二范式：每个非主属性必须完全依赖于主属性
第三范式：每个非主属性不能依赖于另一个非主属性
DDL建表语句
首字母必须是字符
长度是1-30
只能包含A-Z,a-z,0-9,_,$,#
同一个区域不能定义相同的对象
不能定义Oracle的保留字
table tablename(
字段 数据类型 默认值 约束，
字符 varchar2 后面必须跟宽度，按照实际长度存，最大4000个字节，列
取值长度不固定用varchar2，比较时按实际字符长度比，对&& 空格是敏感的
& char 默认一个字符，按照定义长度存，最大2000个字节，取值长度&&& &&&&&& &&&&&& 固定用char，比较时，会将短字符串补齐后，在与长字符串比& &&&&&& &&&&&& 较，对空格不敏感
数字 number(p,s) 可以不定义长度，缺省38位,p表示数值中所有数字位的&&& &&&&&& &&&&&& &&&&&& &&&&&& && 个数，最大38位，s表示刻度范围，s为正数，表示小& &&&&&& &&&&&& &&&&&& &&&&&& && 数点右边的数字的个数，为负数，表示小数点开始向左& &&&&&& &&&&&& &&&&&& &&&&&& && 进行计算数字位的个数。刻度范围从-84~127
日期类型 date Oracle用7个字节来存储日期和时间，默认格式是DD-MON- &&&&&& &&&&&& &&&&&& && RR，格式敏感。
sysdate 返回当前系统时间
alter session set nls_date_format='yyyymm dd hh24:mi:ss';修改会话日期格式
to_date('01-JAN-09','dd-MON-rr')将字符串按照日期格式转化为日期值,不写&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& && 格式为默认格式
to_date(' 10:10:10','yyyy mm ddhh24:mi:ss')
to_char(date,'dd-MON-rr')将日期类型转化为字符类型
格式：fm能去掉两端的空格以及去掉前导零
查出三月份入职的员工
selectfirst_name,start_date
from s_emp where
to_char(start_date,'mm')
selectfirst_name,start_date
from s_emp where
to_char(start_date,'fmmonth')
selectfirst_name,start_date
from s_emp where
rtrim(to_char
(start_date,'month'))='march';
rtrim 压缩右边的空格
日期可以进行运算，+1，-1，加减一天
十分钟之后的时间是多少
selectsysdate,sysdate+1/144 from
列出员工的工作时间
select sysdate-start_datefrom s_
months_between(date,date) 两个日期的隔了几个月
add_months(date,6)给日期加几个月
next_day(date,'FRIDAY')从date开始第一个星期五
last_day(date)给定日期的最后一天
round(to_date(''),'MONTH')以月为单位进行四舍五入
trunc(to_date(''),'MONTH')以月为单位截取
列出下个月0点0分0秒
selectadd_months(trunc(sysdate,'MONTH'),1)
selectround(last_day(sysdate),'MONTH')
select trunc(last_day(sysdate)+1)from
into tablename values (字段值，……);给表里所有的字段插入数据
into tablename (字段名，……) values (字段值，null，……);给表里指定字&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& &&&&&& 段插入数据
delete fromtablename where ……
没有where子句删除所有记录
用delete删除记录不会释放空间
不用delete删除一张大表
truncate table tablename (DDL)清空大表，不能rollback
updatetablename
set column=value,.... where ....
没有where子句更新所有的记录
主键约束,唯一且非空
table test(
c1 numberprimary key
table test(
c1 numberconstraints test_c1_pk primary key
constraints test_c1_pk 给约束其名字
表级约束（主要适用于联合主键）
table test(
c1 number，
constraintstest_c1_pk primary key(c1)
table test(
c1 number，
c2 number，
constraintstest_c1_pk primary key(c1,c2)
外键约束,引用的值必须唯一
createtable parent(
c1 numberprimary key
create table child(
c1 numberprimary key,
c2 numberconstraints child_c2_ fk
references parent(c1));
references 表示引用父表中的某列
parent cascadeconstraints;
先解除子表的外键约束，再删除父表
create table child(
c1 numberprimary key,
c2 numberreferences parent(c1)
ondelete cascade
on delete cascade 级联删除，删父表级联删除子表
create table child(
c1 numberprimary key,
c2 numberreferences parent(c1)
ondelete set null
on delete set null 先将子表的外键置为null，然后删除父表
create table child(
c1 numberprimary key,
c2 number，
foreign key(c2)references parent(c1)
create table test(
c1 numberprimary key,
c2 numbernot null
唯一约束，
允许为null，允许多个null
create table child(
c1 numberprimary key,
c2 numberunique
表级约束，联合唯一
create table child(
c1 numberprimary key,
c2 number，
c3 number,
unique(c2,c3)
create tabletest(
c1 numbercheck (c1&100)
createtable test(
c1 number，
check (c1&100)
1.创建一个脚本文件（建议后缀名是.sql），内容是sql语句
2.保存脚本文件，执行脚本文件 sqlplus openlab/open123 @test.sql
& 已经连接上数据库后执行脚本文件@test.sql，Oracle只会在当前目录
& 下找脚本文件，如果不在脚本所在的文件，可写绝对路径
带子查询的createtable
create table emp_24 as
select *from s_emp where dept_id=24;
只有非空约束不需要定义可以直接复制过来
table emp_43 (id primary key,dept_id,salary) as
select id,dept_id,salary from s_emp wheredept_id=43;
数据类型和宽度都是从表中复制来的，不用定义
create table emp_new (id primary key,first_name,ann_sal)
as select id,first_name,salary*12 from s_
若有表达式，要么定义别名，要么在新表中定义字段名
create table emp_new (id primary key,commission_pct)
as select id,commission_pct from s_emp wherecommission_
create table emp_new as select *from s_emp where 1&2;
只复制表结构，没有复制记录
带子查询的insert语句
insert intotablename(字段名,....)
select ........
带子查询的update语句
updates_emp_new n
set ann_sal=(selectann_sal*(1+commission_pct/100) from s_emp_comm c where n.id=c.id)
where id in (select id from s_emp_comm);
定义缺省值default
create table test(
c1 number default 10,
事务的结束：commit/rollback
&&&&&&&&&&&DDL语句自动提交
事务的开始：上一个事务的结束就是下一个事务的开始
事务的特性：原子性：一个事务要么完全发生，要么完全不发生
&&&&&&&&&&& 一致性：事务把数据库从一个一致状态转变到另一个状态
&&&&&&&&&&& 隔离性：在事务提交之前，其他事物觉察不到事务的影响
&&&&&&&&&&& 持久性：一旦事务提交，他是永久的
事务的隔离级别：（read committed）一个事务只可以读取在事务开始之前提
&&&&&&&&&&&&&&&交的数据和本事务正在修改的数据
事务的提交会释放对象锁，释放回滚段的空间
排它锁（X）：如果一个对象上加了X锁，在这个锁被采用commit和
&&&&&&&&&&&& rollback释放之前，该对象上不能施加任何其他类型的锁
savepoint 设置保留点，可以设置事务的部分回滚
savepoint XXX
rollback to XXX
修改表结构
alter tableemp_42
add foreign key(dept_id) referencess_dept(id);
修改表名字
rename xxto xxx
索引使用的是b*tree结构（Oracle server）
索引上叶子节点实际上是双向连接的表，一旦找到叶子节点的开始点，就可以对叶子节点的开始值进行顺序扫描，
那样不必再进行结构导航，只用对叶子节点进行转发就可以了。
使用索引是要占用独立的空间的（空间代价）
维护代价（dml操作变慢）
update数据时，相当于先delete，后insert
whyuse an index?
&OracleServer 通过rowid能快速定位要找的行
&通过rowid定位能有效降低读取数据快的数量
&索引的使用和维护是自动的，一般情况不需要用户的干预。
操作索引的语句：(当使用索引一段时间后，感觉系统运行效率降低时)
&alterindex：快，用空间换时间& SQL:alterindex s_emp_last_name_
&dropindex和createindex ：慢，占用的空间少，用时间换空间
createindex（会进行排序的动作，使用的时间较长）
索引：物理上与表是独立的，逻辑上依赖于表
＊＊＊＊＊数据量大，结果集小适合建 索引(会进行排序)
适合建索引的列：
&经常出现where子句的列
&经常用于表连接的列
&该列是高基数数据列（高基数数据列是指有很多不同的值，重复度很低）
&该列包含很多的null值&&&&&&&&&&&&&&&&&& 索引里面不建空值
&表很大，查询的结果集小
&PK、UK列 系统会自动创建唯一性索引
&外键列（FK）
&经常需要排序（order by）和分组（group by）的列
&索引不是万能的
不适合建索引的列：
&列很少出现where子句& 既要创建额外的空间，再操作数据时，又要进行维护。不划算
&查询的结果集也大
&该列被反复更新
唯一性索引： create unique index test_c3_idx& on test(c3);&相当于唯一性约束
非唯一性索引：用于提高查询效率
单列索引：建在一列上
联合索引：建在多列上& ： 经常c4和c5联合起来进行查询
& alter table test add (c4 number,c5 number);
& create index test_c4_c5_inx
& on test(c4,c5);
那些写法会导致索引用不了
&函数&& where upper(first_name)='CARMEN';&& 索引里面记录的是索引本身的值，而非转换后的值
&部分隐式数据类型where c1=2;(c1是varchar2类型) 相当于 whereto_number(c1)=2;
&like和 substr&&&&&&& 尽量用like,不用substr
&否定形式&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 尽量不使用否定形式
&isnull&&&& 索引里面不记录空值
函数索引：
createindex& test_c4_funind
ontest(round(c1));
select* from test
whereround(c1)=1;
FROM后面跟子查询 （NILINE VIEWS）
那个员工的工资等于本部门的平均工资?
SQL&select first_name,dept_id,salary
& 2& froms_emp
& 3&where (dept_id,salary) in (select dept_id,avg(salary)
& 4&&&&&&&&&&&&from s_emp
& 5&&&&&&&&&&&&group by dept_id);
FIRST_NAME&&&&&&&&&&&&&&&&&& DEPT_ID&&&& SALARY
----------------------------------- ----------
Colin&&&&&&&&&&&&&&&&&&&&&&&&&&&& 31&&&&&& 1400
Midori&&&&&&&&&&&&&&&&&&&&&&&&&&& 31&&&&&& 1400
Yasmin&&&&&&&&&&&&&&&&&&&&&&&&&&& 33&&&&&& 1515
Andre&&&&&&&&&&&&&&&&&&&&&&&&&&&& 35&&&&&& 1450
& 1&select first_name,dept_id,salary
& 2& froms_emp e
& 3&where salary=(select avg(salary)
& 4&&&&&&&&&&&&from s_emp d
& 5*&&&&&&&&&&&where e.dept_id=d.dept_id)
FIRST_NAME&&&&&&&&&&&&&&&&&& DEPT_ID&&&& SALARY
----------------------------------- ----------
Midori&&&&&&&&&&&&&&&&&&&&&&&&&&& 31&& &&&&1400
Colin&&&&&&&&&&&&&&&&&&&&&&&&&&&& 31&&&&&& 1400
Yasmin&&&&&&&&&&&&&&&&&&&&&&&&&&& 33&&&&&& 1515
Andre&&&&&&&&&&&&&&&&&&&&&&&&&&&& 35&&&&&& 1450
&1&select e.first_name,e.dept_id,e.salary
& 2& froms_emp e join (select dept_id,avg(salary) avgsal
&&3&&&&&&&&&&&& from s_emp
& 4&&&&&&&&&&&&group by dept_id) a
& 5& one.dept_id=a.dept_id
& 6* and e.salary=a.avgsal
FIRST_NAME&&&&&&&&&&&&&&&&&& DEPT_ID&&&& SALARY
----------------------------------- ----------
Midori&&&&&&&&&&&&&&&&&&&&&&&&&&& 31&&&&&& 1400
Colin&&&&&&&&&&&&&&&&&&&&&&&&&&&& 31&&&&&& 1400
Yasmin&&&&&&&&&&&&&&&&&&&&&&&&&&& 33&&&&&& 1515
Andre&&&&&&&&&&&&&&&&&&&&&&&&&&&& 35&&&&&& 1450
****＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
ROWNUM系统里面临时生成的记录号。
SQL&select rownum,id,first_name
& 2& froms_
&&& ROWNUM&&&&&&&& ID FIRST_NAME
-------------------- -------------------------
&&&&&&&& 1&&&&&&&&& 1 Carmen
&&&&&&&& 2&&&&&&&&& 2 LaDoris
&&&&&&&& 3&&&&&&&&& 3 Midori
&&&&&&&& 4&&&&&&&&& 4 Mark
&&&&&&&& 5&&&&&&&&& 5 Audry
&&&&&&&& 6&&&&&&&&& 6 Molly
&&&&&&&& 7&&&&&&&&& 7 Roberta
&&&&&&&& 8&&&&&&&&& 8 Ben
&&&&&&&& 9&&&&&&&&& 9 Antoinette
&&&&&&& 10&&&&&&&& 10 Marta
&&&&&&& 11&&&&&&&& 11 Colin
&&& ROWNUM&&&&&&&& ID FIRST_NAME
-------------------- -------------------------
&&&&&&& 12&&&&&&&& 12 Henry
&&&&&&& 13&&&&&&&& 13 Yasmin
&&&&&&& 14&&&&&&&& 14 Mai
&&&&&&& 15&&&&&&&& 15 Andre
&&&&&&& 16&&&&&&&& 16 Elena
&&&&&&& 17&&&&&&&& 17 George
&&&&&&& 18&&&&&&&& 18 Akira
&&&&&&& 19&&&&&&&& 19 Vikram
&&&&&&& 20&&&&&&&& 20 Chad
&&&&&&& 21&&&&&&&& 21 Alexander
&&&&&&& 22&&&&&&&& 22 Eddie
&&& ROWNUM&&&&&&&& ID FIRST_NAME
-------------------- -------------------------
&&&&&&& 23&&&&&&&& 23 Radha
&&&&&&& 24&&&&&&&& 24 Bela
&&&&&&& 25&&&&&&&& 25 Sylvie
25rows selected.
第一条记录
& 1&select first_name,dept_id,salary
& 2& froms_emp
& 3* where rownum=1
FIRST_NAME&&&&&&&&&&&&&&&&&& DEPT_ID&&&& SALARY
----------------------------------- ----------
Carmen&&&&&&&&&&&&&&&&&&&&&&&&&&& 50&&&&&& 3500
第二条记录
& 1&select first_name,dept_id,salary
& 2& froms_emp
& 3* where rownum=2
norows selected
2—5条记录
1& select first_name,dept_id,salary
& 2& froms_emp
& 3* where rownum between 2 and 5
norows selected
前两条记录
& 1&select first_name,dept_id,salary
& 2& froms_emp
& 3* where rownum&=2
FIRST_NAME&&&&&&&&&&&&&&&&&& DEPT_ID&&&& SALARY
----------------------------------- ----------
Carmen&&&&&&&&&&&&&&&&&&&&&&&&&&& 50&&&&&& 3500
LaDoris&&&&&&&&&&&&&&&&&&&&&&&&&& 41&&&&&& 1450
从一开始的记录可以查询
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊8
分页问题：
工资最高的前10人：
SQL&select rownum,first_name,dept_id,salary
& 2& froms_emp
& 3&order by salary desc
& 4&where rownum&=10;
whererownum&=10
ERRORat line 4:
ORA-00933:SQL command not properly ended
orderby 和where的顺序错误了
&1&select rownum,first_name,salary
& 2& from(select first_name,salary from s_emp
& 3&&&&&&order by salary desc)
& 4* where rownum&11
&&& ROWNUM FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY
----------------------------------- ----------
&&&&&&&& 1 Carmen&&&&&&&&&&&&&&&&&&&&&&&&& 3500
&&&&&&&& 2 Audry&&&&&&&&&&&& &&&&&&&&&&&&&&1550
&&&&&&&& 3 Mai&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1525
&&&&&&&& 4 Yasmin&&&&&&&&&&&&&&&&&&&&&&&&& 1515
&&&&&&&& 5 Henry&&&&&&&&&&&&&&&&&&&&&&&&&& 1490
&&&&&&&& 6 LaDoris&&&&&&&&&&&&&&&&&&&&&&&& 1450
&&&&&&&& 7 Andre&&&&&&&&&&&&&&&&&&&&&&&&&& 1450
&&&&&&&& 8 Mark&&&&&&&&&&&&&&&&&&&&&&&&&&& 1450
&&&&&&&& 9 Midori&&&&&&&&&&&&&&&&&&&&&&&&& 1400
&&&&&&& 10 Elena&&&&&&&&&&&&&&&&&&&&&&&&&& 1400
10rows selected.
rownum是一个伪列
选择11条到20条记录（将rownum固定为真正的列）
&1&select first_name,salary
& 2& from(select rownum r,first_name,salary from s_emp
& 3&&&&where rownum&=20)
& 4* where r&10
FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY
-----------------------------------
Colin&&&&&&&&&&&&&&&&&&&&&&&&&& 1400
Henry&&&&&&&&&&&&&&&&&&&&&&&&&& 1490
Yasmin&&&&&&& &&&&&&&&&&&&&&&&&&1515
Mai&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1525
Andre&&&&&&&&&&&&&&&&&&&&&&&&&& 1450
Elena&&&&&&&&&&&&&&&&&&&&&&&&&& 1400
George&&&&&&&&&&&&&&&&&&&&&&&&&& 940
Akira&&&&&&&&&&&&&&&&&&&&&&&&&& 1200
Vikram&&&&&&&&&&&&&&&&&&&&&&&&&& 795
Chad&&&&&& &&&&&&&&&&&&&&&&&&&&&&750
10rows selected.
将工资11到20的人显示：
& 2& from(
& 3&&&&select rownum rn,first_name,salary&
& 4&&&&from (select first_name,salary from s_emp
& 5&&&&&&&&&&&&order by salary desc)
& 6&&&&where rownum&=20
&&&& 7&&&&&&&&&&&&&&&&&& &&&&&&&&&&)
& 8&where rn&10;
FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY
-----------------------------------
Elena&&&&&&&&&&&&&&&&&&&&&&&&&& 1400
Marta&&&&&&&&&&&&&&&&&&&&&&&&&& 1307
Antoinette&&&&&&&&&&&&&&&&&&&&& 1300
Roberta&&&&&&&&&&&&&&&&&&&&&&&& 1250
Molly&&&&&&&&&&&&&&&&&&&&&&&&&& 1200
Akira&&&&&&&&&&&&&&&&&&&&&&&&&& 1200
Ben&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1100
Sylvie&&&&&&&&&&&&&&&&&&&&&&&&& 1100
George&&&&&&&&&&&&&&&&&&&&&&&&&& 940
Bela&&&&&&&&&&&&&&&&&&&&&&&&&&&& 860
10rows selected.
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
CASEWHEN AND DECOODE
》在select语句中可以实现if_then_else的逻辑
＠＠＠＠＠
部门31的员工工资涨10％
部门32的员工工资涨20％
部门33的员工工资涨30％
其他部门工资不动
&1&select dept_id,first_name,salary,
& 2&&&&case when dept_id=31 then salary*1.1
& 3&&&&&&&&&when dept_id=32 then salary*1.2
& 4&& &&&&&&&when dept_id=33 then salary*1.3
& 5&&&&else
& 6&&&&&&&&&salary
& 7&&&&end aft_sal
& 8* from s_emp
&& DEPT_ID FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&&& AFT_SAL
----------------------------------- ---------- ----------
&&&&&&& 50 Carmen&&&& &&&&&&&&&&&&&&&&&&&&&3500&&&&&& 3500
&&&&&&& 41 LaDoris&&&&&&&&&&&&&&&&&&&&&&&& 1450&&&&&& 1450
&&&&&&& 31 Midori&&&&&&&&&&&&&&&&&&&&&&&&& 1400&&&&&& 1540
&&&&&&& 32 Mark&&&&&&&&&&&&&&&&&&&&&&&&&&& 1450&&&&&& 1740
&&&&&&& 50 Audry&&&&&&&&&&&&&&&&&&&&&&&&& &1550&&&&&&1550
&&&&&&& 41 Molly&&&&&&&&&&&&&&&&&&&&&&&&&& 1200&&&&&& 1200
&&&&&&& 42 Roberta&&&&&&&&&&&&&&&&&&&&&&&& 1250&&&&&& 1250
&&&&&&& 43 Ben&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1100&&&&&& 1100
&&&&&&& 44 Antoinette&&&&&&&&&&&&&&&&&&&&& 1300&&&&&& 1300
&& &&&&&45 Marta&&&&&&&&&&&&&&&&&&&&&&&&&& 1307&&&&&& 1307
&&&&&&& 31 Colin&&&&&&&&&&&&&&&&&&&&&&&&&& 1400&&&&&& 1540
&& DEPT_ID FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&&& AFT_SAL
----------------------------------- ---------- ----------
&&&&&&& 32 Henry&&&&& &&&&&&&&&&&&&&&&&&&&&1490&&&&&& 1788
&&&&&&& 33 Yasmin&&&&&&&&&&&&&&&&&&&&&&&&& 1515&&&& 1969.5
&&&&&&& 34 Mai&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1525&&&&&& 1525
&&&&&&& 35 Andre&&&&&&&&&&&&&&&&&&&&&&&&&& 1450&&&&&& 1450
&&&&&&& 41 Elena&&&&&&&&&&&&&&&&&&&&&&&&& &1400&&&&&&1400
&&&&&&& 41 George&&&&&&&&&&&&&&&&&&&&&&&&&& 940&&&&&&& 940
&&&&&&& 42 Akira&&&&&&&&&&&&&&&&&&&&&&&&&& 1200&&&&&& 1200
&&&&&&& 42 Vikram&&&&&&&&&&&&&&&&&&&&&&&&&& 795&&&&&&& 795
&&&&&&& 43 Chad&&&&&&&&&&&&&&&&&&&&&&&&&&&& 750&&&&&&& 750
&& &&&&&43 Alexander&&&&&&&&&&&&&&&&&&&&&&& 850&&&&&&& 850
&&&&&&& 44 Eddie&&&&&&&&&&&&&&&&&&&&&&&&&&& 800&&&&&&& 800
&& DEPT_ID FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&&& AFT_SAL
----------------------------------- ---------- ----------
&&&&&&& 34 Radha&&&&& &&&&&&&&&&&&&&&&&&&&&&795&&&&&&& 795
&&&&&&& 45 Bela&&&&&&&&&&&&&&&&&&&&&&&&&&&& 860&&&&&&& 860
&&&&&&& 45 Sylvie&&&&&&&&&&&&&&&&&&&&&&&&& 1100&&&&&& 1100
25rows selected.
& 1&select dept_id,first_name,salary,
& 2&&&&case when dept_id=31 then salary*1.1
& 3&&&&&&&&&when dept_id=32 then salary*1.2
& 4&&&&&&&&&when dept_id=33 then salary*1.3
& 5&&&&end aft_sal
& 6* from s_emp
&& DEPT_ID FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&&& AFT_SAL
----------------------------------- ---------- ----------
&&&&&&&50 Carmen&&&&&&&&&&&&&&&&&&&&&&&&&3500
&&&&&&& 41 LaDoris&&&&&&&&&&&&&&&&&&&&&&&& 1450
&&&&&&& 31 Midori&&&&&&&&&&&&&&&&&&&&&&&&& 1400&&&&&& 1540
&&&&&&& 32 Mark&&&&&&&&&&&&&&&&&&&&&&&&&&& 1450&&&&&& 1740
&&&&&&& 50 Audry&&&&&&&&&&&&&&&&&&&&&&&&&& 1550
&&&&&&& 41 Molly&&&&&&&&&&&&&&&&&&&&&&&&&& 1200
&&&&&&& 42 Roberta&&&&&&&&&&&&&&&&&&&&&&&& 1250
&&&&&&& 43 Ben&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1100
&&&&&&& 44 Antoinette&&&&&&&&&&&&&&&&&&&&& 1300
&&&&&&& 45 Marta&&&&&&&&&&&&&&&&&&&&&&&&&& 1307
&&&&&&& 31 Colin&&&&&&&&&&&&&&&&&&&&&&&&&& 1400&&&&&& 1540
&& DEPT_ID FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&&& AFT_SAL
----------------------------------- ---------- ----------
&&&&&&& 32 Henry&&&&&&&&&&&&&&&&&&&&&&&&&& 1490&&&&&& 1788
&&&&&&& 33 Yasmin&&&&&&&&&&&& &&&&&&&&&&&&&1515&&&& 1969.5
&&&&&&& 34 Mai&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1525
&&&&&&& 35 Andre&&&&&&&&&&&&&&&&&&&&&&&&&& 1450
&&&&&&& 41 Elena&&&&&&&&&&&&&&&&&&&&&&&&&& 1400
&&&&&&& 41 George&&&&&&&&&&&&&&&&&&&&&&&&&& 940
&&&&&&& 42 Akira&&&&&&&&&&&&&&&&&& &&&&&&&&1200
&&&&&&& 42 Vikram&&&&&&&&&&&&&&&&&&&&&&&&&& 795
&&&&&&& 43 Chad&&&&&&&&&&&&&&&&&&&&&&&&&&&& 750
&&&&&&& 43 Alexander&&&&&&&&&&&&&&&&&&&&&&& 850
&&&&&&& 44 Eddie&&&&&&&&&&&&&&&&&&&&&&&&&&& 800
&& DEPT_ID FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&& &AFT_SAL
----------------------------------- ---------- ----------
&&&&&&& 34 Radha&&&&&&&&&&&&&&&&&&&&&&&&&&& 795
&&&&&&& 45 Bela&&&&&&&&&&&&&&&&&&&&&&&&&&&& 860
&&&&&&& 45 Sylvie&&&&&&&&&&&&&&&&&&&&&&&&& 1100
25rows selected.
＠＠＠＠＠＠＠＠＠＠
工资小于1000的涨8％
1000——1500涨10％
1500以上的不动
&1&select first_name,salary,
& 2&&&&case when salary&1000 then salary*1.08
& 3&&&&&&&&&when salary between 1000 and 1500 then salary*1.1
& 4&&&&else
& 5&&&&&&&&&salary
& 6&&&&end aft_sal
& 7* from s_emp
FIRST_NAME&&& &&&&&&&&&&&&&&&&SALARY&&& AFT_SAL
----------------------------------- ----------
Carmen&&&&&&&&&&&&&&&&&&&&&&&&& 3500&&&&&& 3500
LaDoris&&&&&&&&&&&&&&&&&&&&&&&& 1450&&&&&& 1595
Midori&&&&&&&&&&&&&&&&&&&&&&&&& 1400&&&&&& 1540
Mark&&&&&&&&&&&&&&&&&&&&&&&&& &&1450&&&&&&1595
Audry&&&&&&&&&&&&&&&&&&&&&&&&&& 1550&&&&&& 1550
Molly&&&&&&&&&&&&&&&&&&&&&&&&&& 1200&&&&&& 1320
Roberta&&&&&&&&&&&&&&&&&&&&&&&& 1250&&&&&& 1375
Ben&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1100&&&&&& 1210
Antoinette&&&&&&&&&&&&&&&&&&&&& 1300&&&&&& 1430
Marta&&&&&&&&&&&&&&&&&&&&&&&&&& 1307&&&& 1437.7
Colin&&&&&&&&&&&&&&&&&&&&&&&&&& 1400&&&&&& 1540
FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&&& AFT_SAL
----------------------------------- ----------
Henry&&&&&&&&&&&&&&&&&&&&&&&&&& 1490&&&&&& 1639
Yasmin&&&&&& &&&&&&&&&&&&&&&&&&&1515&&&&&& 1515
Mai&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1525&&&&&& 1525
Andre&&&&&&&&&&&&&&&&&&&&&&&&&& 1450&&&&&& 1595
Elena&&&&&&&&&&&&&&&&&&&&&&&&&& 1400&&&&&& 1540
George&&&&&&&&&&&&&&&&&&&&&&&&&& 940&&&& 1015.2
Akira&&&&&&&&&&&&&&&&&&&&&&& &&&1200&&&&&&1320
Vikram&&&&&&&&&&&&&&&&&&&&&&&&&& 795&&&&& 858.6
Chad&&&&&&&&&&&&&&&&&&&&&&&&&&&& 750&&&&&&& 810
Alexander&&&&&&&&&&&&&&&&&&&&&&& 850&&&&&&& 918
Eddie&&&&&&&&&&&&&&&&&&&&&&&&&&& 800&&&&&&& 864
FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&&& AFT_SAL
----------------------------------- ----------
Radha&&&&&&&&&&&&&&&&&&&&&&&&&&& 795&&&&& 858.6
Bela&&&&&&&&&&&&&&&&&&&&&&&&&&&& 860&&&&& 928.8
Sylvie&&&&&&&&&&&&&&&&&&&&&&&&& 1100&&&&&& 1210
25rows selected.
转帐动作：一条update语句完成
SQL&select *
&&&&&&& C1&&&&&&&& C2
--------------------
&&&&&&&& && &&&&&& 1&&&&&&&&&& &&&&&& 1000
&&&&&&& &&&& &&&&&& &2&&&&& &&&& &&&&&& &2000
&&&&&&& &&&& &&&&&& &3&&&& &&&&&& &&&&&& &3000
&&&&&&& &&&& &&&&&& &4&&&&& &&&& &&&&&& &4000
SQL&update wolf
& 2& setc2= case when c1=1 then c2-800
& 3&&&&&&&&&&&&&& when c1=2 then c2+800
&&4&&&&&&&&& end
& 5&where c1 in (1,2);
2rows updated.
SQL&select *
&&&&&&& C1&&&&&&&& C2
--------------------
&&&&&&&& 1&&&&&&& 200
&&&&&&&& 2&&&&&&2800
&&&&&&&& 3&&&&&&3000
&&&&&&&& 4&&&&&&4000
＠＠＠＠＠
&1&select first_name,salary,
&&2&&&& decode(dept_id,31,salary*1.1
& 3&&&&&&&&&&&&&&&&&& ,32,salary*1.2
& 4&&&&&&&&&&&&&&&&&& ,33,salary*1.3
& 5&&&&&&&&&&&&&&&&&& ,salary) aft_sal
& 6* from s_emp
FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&&& AFT_SAL
----------------------------------- ----------
Carmen&&&&&&&&&&&&&&&&&&&&&&&&& 3500&&&&&& 3500
LaDoris&&&&&&&&&&&&&&&&&&&&&&&& 1450&&&&&& 1450
Midori&&&&&&&&&&&&&&&&&&&&&&&&& 1400&&&&&& 1540
Mark&&&&&&&&&&&&&&&&&&&&&&&&&&& 1450&&&&&& 1740
Audry&&&&&&&&&&&&&&&&&&&&&&&&&& 1550&&&&&& 1550
Molly&&&&&&&&&&&&&&&&&&&&&&&&&& 1200&&&&&& 1200
Roberta&&&&&&&&&&&&&&&&&&&&&&&& 1250&&&&&& 1250
Ben&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1100&&&&&& 1100
Antoinette&&&&&&&&&&&&&&&&&&&&& 1300&&&&&& 1300
Marta&&&&&&&&&&&&&&&&&&&&&&&&&& 1307&&&&&& 1307
Colin&&&&&&&&&&&&& &&&&&&&&&&&&&1400&&&&&& 1540
FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&&& AFT_SAL
----------------------------------- ----------
Henry&&&&&&&&&&&&&&&&&&&&&&&&&& 1490&&&&&& 1788
Yasmin&&&&&&&&&&&&&&&&&&&&&&&&& 1515&&&& 1969.5
Mai&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1525&&&&&& 1525
Andre&&&&&&&&&&&&&&&&&&&&&&&&&& 1450&&&&&& 1450
Elena&&&&&&&&&&&&&&&&&&&&&&&&&& 1400&&&&&& 1400
George&&&&&&&&&&&&&&&&&&&&&&&&&& 940&&&&&&& 940
Akira&&&&&&&&&&&&&&&&&&&&&&&&&& 1200&&&&&& 1200
Vikram&&&&&&&&&&&&&&&&&&&&&&&&&& 795&&&&&&& 795
Chad&&&&&&&&&&&&&&&&&&&&&&&&&&&& 750&&&&&&& 750
Alexander&&&&&&&&&&&&&&&&&&&&&&& 850&&&&&&& 850
Eddie&&&&&&&&&&&&&&&&&&&&&&&&&&& 800&&&&&&& 800
FIRST_NAME&&&&&&&&&&&&&&&&&&& SALARY&&& AFT_SAL
----------------------------------- ----------
Radha&&&&&&&&&&& &&&&&&&&&&&&&&&&795&&&&&&& 795
Bela&&&&&&&&&&&&&&&&&&&&&&&&&&&& 860&&&&&&& 860
Sylvie&&&&&&&&&&&&&&&&&&&&&&&&& 1100&&&&&& 1100
25rows selected.
行列转制问题：
1& select job,deptno,count(empno)
& 2& fromemp
& 3* group by job,deptno
JOB&&&&&&&&&& DEPTNO COUNT(EMPNO)
------------------- ------------
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 2
CLERK&&&&&&&&&&&& 10&&&&&&&&&&& 1
CLERK&&&&&&&&&&&& 20&&&&&&&&&&& 2
CLERK&&&&&&&&&&&& 30&&&&&&&&&&& 1
ANALYST&&&&&&&&&& 20&&&&&&&&&&& 2
MANAGER&&&&&&&&&& 10&&&&&&&&&&& 1
MANAGER&&&&&&&&&& 20&&&&&&&&&&& 1
MANAGER&&&&&&&&&& 30&&&&&&&&&&& 1
SALESMAN&&&&&&&&& 30&&&&&&&&&&& 4
PRESIDENT&&&&&&&& 10&&&&&&&&&&& 1
10rows selected.
& 1&&select job,count(decode(deptno,10,deptno)) d10,
& 2&&&&&&&&&&&&&count(decode(deptno,20,deptno)) d20,
& 3&&&&&&&&&&&&&count(decode(deptno,30,deptno)) d30
& 4&&&from emp
& 5*&&group by job
JOB&&&&&&&&&&&&& D10&&&&&&& D20&&&&&&& D30
------------------- ---------- ----------
ANALYST&&&&&&&&&&& 0&&&&&&&&& 2&&&&&&&&& 0
CLERK&&&&&&&&&&&&& 1&&&&&&&&& 2&&&&&&&&& 1
MANAGER&&&&&&&&&&& 1&&&&&&&&& 1&&&&&&&&& 1
PRESIDENT&&&&&&&&& 1&&&&&&&&& 0&&&&&&&&& 0
SALESMAN&&&&&&&&&& 0&&&&&&&&& 0&&&&&&&&& 4
5rows selected.
集合运算符
union/union all 并集
intersect 交集
minus 差集
集合运算要求两个select语句是同构的，即列的个数和数据类型必须一致
员工名字和领导名字的对应关系
selecte.first_name,nvl(m.first_name,'boss')
from s_empe left join s_emp m
one.manager_id=m.id
select first_name,nvl((selectfirst_name from s_emp m where m.id=e.manager_id),'Boss')
selecte.first_name,m.first_name
from s_emp e join s_emp m
one.manager_id=m.id union all selectfirst_name,'Boss' from s_emp where manager_id is null
哪个部门没有员工
selectdeptno
from dept minusselect
deptno from
什么是序列号sequence
能自动产生唯一值
是个可共享的数据库对象
主要用于产生主键值
可以减少应用程序代码量
create table test(c1 number primary key);
create sequence s1;
insert into test values (s1.nextval);
create sequences_dept_id
increment by 1
start with 51
maxvalue 99999
s1.currval 当前session中sequence取值，先做nextval在做currval
序列号的使用
如果使用了cache子句，系统将序列号（sequence）的多个值缓存到内存（共
享池 shared pool）中，将提高执行nextval的效率
如下情况会是序列号的值不连续
同一个序列号用于多张表
删除序列号
排名：第16830名
（1）（11）（44）（25）（13）（3）（1）（2）（3）（1）（5）（2）（1）（16）（1）（2）（4）（17）（0）（2）（0）（2）（2）（3）（1）（5）（0）（0）