Oracle学习（二）

Oracle学习（二）

主要学习了子查询、集合运算、处理数据、创建和管理表和其他数据库对象

子查询

查询工资比SCOTT高的员工信息

select *from emp where sal > (select sal from emp where ename='SCOTT');

在select后面只用子查询示例

select empno,ename,sal,(select job from emp where empno=7839) 第四列 from emp;

查询员工信息：员工号 姓名 月薪

select * from (select empno,ename,sal from emp);

查询员工信息：员工号 姓名 月薪 年薪

select * from (select empno,ename,sal,sal*12 annsal 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.dname='SALES';

查询部门名称是SALES和ACCOUNTING的员工

--使用子查询
select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');
--使用表连接查询
select e.* from emp e,dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');

查询工资比30号部门任意一个员工高的员工信息

--any: 和集合中的任意一个值(即：最小值)比较
select * from emp where sal > any (select sal from emp where deptno=30);
--使用min函数
select * from emp where sal > (select min(sal) from emp where deptno=30);

查询工资比30号部门所有员工高的员工信息

--all:和集合中的所有值(即：最大值)比较
select * from emp where sal > all (select sal from emp where deptno=30);
--使用max函数
select * from emp where sal > (select max(sal) from emp where deptno=30);

查询不是老板的员工信息

--需要注意多行子查询中的null，即不能出现empno not in (1,2,null)
--错误写法
select * from emp where empno not in (select mgr from emp);
--正确写法
select * from emp where empno not in (select mgr from emp where mgr is not null);

集合运算

查询10和20号部门的员工

--法一：or
select * from emp where deptno=10 or deptno=20;
--法二：in
select * from emp where deptno in (10,20);
--法三：union
select * from emp where deptno=10 union select * from emp where deptno=20;

每个部门和职位的薪资总和

--rollup方法
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
--union方法
/*
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;

集合运算：

1. UNION/UNION ALL 并集，UNION ALL会包含重复的
2. INTERSECT 交集
3. MINUS 差集

处理数据

往emp表中动态插入一批数据

--使用地址符 &
insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
--输入 empno 的值:  1002
--输入 ename 的值:  'Mary'
--输入 sal 的值:  2000
--输入 deptno 的值:  20

复制一张表结构（不含数据）

create table emp10 as select * from emp where 1=2;

一次性将emp中，所有10号部门的员工插入到emp10中

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

清空表testdelete

/*
delete和truncate的区别
1、delete逐条删除；truncate先摧毁表 再重建2
2、delete是DML  truncate是DDL
   (可以回滚)   （不可以回滚）
3、delete不会释放空间 truncate会
4、delete会产生碎片 truncate不会
5、delete可以闪回(flashback，其实是一种恢复)  truncate不可以
6、oracle中delete效率高一些，mysql中truncate效率高一些
*/
delete from testdelete;
truncate table testdelete;

更新某员工的部门

update emp set department_id = 70 where employee_id = 113;

创建保存点a

savepoint a;

回退至保存点

rollback to savepoint a;

Oracle中的事务：

1、起始标志: 事务中的第一条DML语句
2、结束标志：提交： 显式 commit 隐式： 正常退出、DDL、DCL
回滚： 显式 rollback 隐式： 非正常退出、掉电、宕机

数据库的隔离级别：

1. READ UNCOMMITTED 读未提交的数据
2. READ COMMITTED 读已提交的数据
3. REPEATABLE READ 可重复读
4. SERIALIZABLE 串行化

Oracle默认隔离级别为READ COMMITTED，且只支持READ COMMITTED和SERIALIZABLE

MySql默认隔离级别为REPEATABLE READ，且4种都支持

oracle分页与相关子查询

查询工资排名前三的员工，且序号顺序为1、2、3

select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<=3;

查询工资排名为5~8的员工

select * from 
(select rownum r,e1.* from (select * from emp order by sal) e1 where rownum <=8)
where r >=5;

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

查询工资大于本部门平均工资的员工，使用连接查询

select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal>d.avgsal;

查询工资大于本部门平均工资的员工，使用相关子查询

----相关子查询： 将主查询中的值 作为参数传递给子查询
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);

统计每年入职的员工个数

select count(*) as "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;

查询所有人信息，并显示前一个人的money

--表结构
create table test1
(id int primary key,
 name varchar(20),
 money int);

insert into test1 values(1,'Tom',1000);
insert into test1 values(2,'Mary',2000);
insert into test1 values(3,'Mike',3000);
insert into test1 values(4,'Jeff',4000);
commit;

--答案
select id,name,money,(select money from test1 where id=t.id-1) money1
from test1 t;

查询每个课程选修的学生姓名

 --表结构
 create table pm_ci
 (ci_id varchar(20) primary key,
  stu_ids varchar(100));

insert into pm_ci values('1','1,2,3,4');
insert into pm_ci values('2','1,4');

 create table pm_stu
 (stu_id varchar(20) primary key,
  stu_name varchar(20));
insert into pm_stu values('1','张三');
insert into pm_stu values('2','李四');
insert into pm_stu values('3','王五');
insert into pm_stu values('4','赵六');
commit;

--答案
select ci_id, wm_concat(stu_name) nameslist
from	(select c.ci_id, s.stu_name
	from pm_ci c, pm_stu s
	where instr(c.stu_ids, s.stu.id) > 0)
group by c.ci_id

创建和管理表

创建表test1

create table test1 (
    tid number,tname varchar2(20)
);

行地址rowid的使用

--查询员工表，带rowid
select rowid,empno,ename,sal from emp;
--根据rowid查询某个员工
select * from emp where rowid='AAAMfPAAEAAAAAgAAJ';

创建表：保存20号部门的员工

create table emp20
as
select * from emp where deptno=20;

创建表：员工号 姓名 月薪 年薪 部门名称

create table empinfo
as
select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
from emp e,dept d
where e.deptno=d.deptno;

修改表：增加新列，修改列，删除列，重命名列，重命名表

--增加新列photo 类型为blob
alter table test1 add photo blob;
--修改列
alter table test1 modify tname varchar2(40);
--删除列
alter table test1 drop column photo;
--重命名列
alter table test1 rename column tname to username;
--重命名表
rename test1 to test2;

删除表

--删除表
drop table TESTDELETE;
--查看回收站。注意：管理员没有回收站
show recyclebin;
--清空回收站
purge recyclebin;

检查性约束（check）

create table test3 (
    tid number,
    tname varchar2(20),
    gender varchar2(2) check (gender in ('男','女')),
    sal  number check (sal > 0)
);

给约束起名字（constraint）

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
);

其他数据库对象

创建视图

--可能会没有权限。登录管理员用户，给scott用户授权 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;

创建只读视图

create or replace 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
with read only;

创建序列

--默认序列，后面省略了很多参数
create sequence myseq;
--获取下一个序列
select myseq.nextval from dual;
--获取当前序列
select myseq.currval from dual;

创建索引

--在deptno列上创建索引
create index myindex on emp(deptno);

查看sql的执行计划

explain plan for select * from emp where deptno=10;

创建同义词

--登录管理员用户，给scott用户授权 grant create synonym to scott
--当前用户是scott，给hr用户下的表employees创建同义词hremp
create synonym hremp for hr.employees;
--查询该同义词表
select count(*) from hremp;

附：一些说明

使用子查询时，需要注意的问题：

1. 括号
2. 合理的书写风格
3. 可以在主查询的where、select、having、from 后面使用子查询
4. 不可以在group by使用子查询
5. 强调from后面的子查询
6. 主查询和子查询可以不是同一张表；只有子查询返回的结果 主查询可以使用 即可
7. 一般不在子查询中排序；但在top-n分析问题中 必须对子查询排序
8. 一般先执行子查询，再执行主查询；但相关子查询例外
9. 单行子查询（只有一条记录）只能使用单行操作符；多行子查询（有多条记录）只能使用多行操作符
10. 子查询中的null

sql优化原则：

1. 尽量使用列名，不要使用*
2. where解析顺序: 右 —> 左
3. 用到分组时，能用where尽量使用where
4. 相比于子查询而言，尽量使用多表查询
5. 尽量不要使用集合运算

SQL的类型：

1. DML（Data Manipulation Language 数据操作语言）: select insert update delete
2. DDL(Data Definition Language 数据定义语言): create table,alter table,truncate table,drop table
   create/drop view,sequnece,index,synonym(同义词)
3. DCL(Data Control Language 数据控制语言): grant(授权) revoke(撤销权限)