1.子查询
子查询:也叫内查询,在主查询之前一次执行完成,子查询的结果被主查询使用(外查询)
子查询所要解决的问题:不能一步求解
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、单行子查询只能使用单行操作符;多行子查询只能使用多行操作符
0、子查询中的null
2>子查询的类型
单行子查询:只返回一条记录
操作符:=、>、>=、<、<=、<>
select * from emp where sal <> (select sal from emp where ename='SCOTT');
多行子查询:返回多条记录
操作符:IN(在集合中)、ANY(满足集合中的任意一个)、ALL(满足集合中的所有)
select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');
注意:子查询中不能使用group by分组函数
2.集合运算
集合运算:交集(UNION / UNION ALL)、并集(INTERSECT)、差集(MINUS)
select * from emp where deptno=10 union select * from emp where deptno=20;
注意:
1、参与运算的各个集合必须列数相同 且类型一致
2、采用第一个集合作为最后的表头
3、order by永远在最后
4、括号
5、尽量不要使用集合运算
3.oracle分页
行号:rownum
select rownum,empno,ename,sal from emp;
select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<=3;
注意:rownum永远按照默认的顺序生成,rownum只能使用< <=;不能使用> >=
select *
from (select rownum r,e1.*
from (select * from emp order by sal) e1
where rownum <=8
)
where r >=5;
4.Oracle的数据处理(划重点)
DML(增、删、改、查)、DDL(表的创建、修改、删除)、DCL(用户的授权、撤销权限)
1>DML(Data Manipulation Language 数据操作语言): select insert update delete
插入数据:
--插入数据的时候建议书写指定列名
insert into emp(empno,ename,sal,deptno) values(1001,'Tom',3000,10);
--地址符 & 可以代替成值、列名、表名
insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
select empno,ename,sal,&t from emp;
select * from &t;
--一次性将emp中,所有10号部门的员工插入到emp10中
insert into emp10 select * from emp where deptno=10;
删除数据:
delete from testdelete;
truncate table testdelete;
delete和truncate的区别:
delete逐条删除 truncate先摧毁表 再重建,delete是DML(可以回滚) truncate是DDL(不可以回滚),delete不会释放空间 truncate会,delete会产生碎片 truncate不会,delete可以闪回(flashback) truncate不可以
更新数据:
update emp set sal=sal+100 where depthno in (select deptno from dept where loc='NEW YORK')
事务:Oracle自动已经开启事务,当我们更新好数据之后需要commit提交
提交事务:commit
回滚事务:rollback
保存点回滚:savepoint
insert into testsavepoint values(2,'Mary');--先插入一条数据
savepoint a;--创建一个保存点
insert into testsavepoint values(3,'Maee');--再插入一条数据
rollback to savepoint a;--回滚到保存点
Oracle支持三种事务隔离级别:READ COMMITED,SERIALIZABLE,READ ONLY
Oracle默认的事务隔离级别未:READ COMMITED
2>DDL(Data Definition Language 数据定义语言):
create table,alter table,truncate table,drop table
表空间:Oracle数据库的逻辑单元,一个表空间可以与多个数据文件关联,一个数据库下可以建立多个表空间,一个表空间可以建立多个用户,一个用户下可以建立多个表
创建表空间:create tablespace 表空间名
创建用户:create user 用户名
表:数据库中存储数据的主体
普通创建表:
create table test1 (
tid number,
tname varchar2(20)
);
采用子查询创建表:
--创建表:保存20号部门的员工
create table emp20 as select * from emp where deptno=20;
--只需要表结构,不需要表数据
create table emp20 as select * from emp where 1=2;
数据的类型:
修改表:
添加列:
alter table 表名 add 列名 数据类型;
修改列:
alter table 表名 modify 列名 数据类型;
修改列名:
alter table 表名 rename column 列名 to 新列名;
删除列:
alter table 表名 drop column 列名;
删除表:DROP TABLE 表名
查看表的回收站:show recyclebin; 注意:管理员没有回收站,只有scott等用户才有
清空回收站:purge recyclebin;
约束:主键约束(Primary Key),非空约束(Not Null),唯一约束(Unique),外键约束(Foregin Key),检查性约束(Check)
create table student
(
sid number constraint student_pk primary key,--主键约束
sname varchar2(20) constraint student_name_notnull not null,--非空约束
gender varchar2(2) constraint student_gender check (gender in ('男','女')),--检查性约束
email varchar2(40) constraint student_email_unique unique--唯一约束
constraint student_email_notnull not null,
deptno number constraint student_fk references dept(deptno) on delete set null--外键约束
);
注意:主键约束是唯一的不可重复,如果不指定名称系统会默认命名,外键约束时必须先删除字表再删除主表
数据库对象:表、视图、序列、索引、同义词(共十个,后五个在Oracle第三章)
视图
就是封装了一条复杂查询的语句,视图是一个虚表使用时月平常的表无区别,最大的优点就是简化复杂的查询
不建议通过视图对表中的数据进行修改,因为会受到很多的限制,视图只读
视图授权:sys账户下 >>> grant create view to scott;
create view empinfoview as select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname from emp e,dept d where e.deptno=d.deptno;
序列:自动增长的列,
nextval:取得序列的下一个内容
currval:取得序列的当前内容
序列产生裂缝的原因:回滚、系统异常、多个表共用一个序列
--创建序列
create sequence myseq;
--使用序列
select myseq.nextval from dual;
索引
索引是用于加速数据存取的数据对象,合理的使用索引可以大大降低i/o次数,索引也相当于书的目录
--创建目录(索引)
create index myindex on emp(deptno);
同义词
相当于别名,可以很方便的访问其他用户的数据库对象,缩短了对象名字的长度
允许scott表访问hr表:sys用户下 >>> grant select on hr.employeess to scott;
开启同义词授权:sys用户下 >>> grant create synonym to scott;
--为hr.employees起别名 ---> 同义词
create synonym hremp for hr.employees;
3>DCL(Data Control Language 数据控制语言): grant(授权) revoke(撤销权限)(了解)