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;
集合运算:
- UNION/UNION ALL 并集,UNION ALL会包含重复的
- INTERSECT 交集
- 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 隐式: 非正常退出、掉电、宕机
数据库的隔离级别:
- READ UNCOMMITTED 读未提交的数据
- READ COMMITTED 读已提交的数据
- REPEATABLE READ 可重复读
- 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;
附:一些说明
使用子查询时,需要注意的问题:
- 括号
- 合理的书写风格
- 可以在主查询的where、select、having、from 后面使用子查询
- 不可以在group by使用子查询
- 强调from后面的子查询
- 主查询和子查询可以不是同一张表;只有子查询返回的结果 主查询可以使用 即可
- 一般不在子查询中排序;但在top-n分析问题中 必须对子查询排序
- 一般先执行子查询,再执行主查询;但相关子查询例外
- 单行子查询(只有一条记录)只能使用单行操作符;多行子查询(有多条记录)只能使用多行操作符
- 子查询中的null
sql优化原则:
- 尽量使用列名,不要使用*
- where解析顺序: 右 —> 左
- 用到分组时,能用where尽量使用where
- 相比于子查询而言,尽量使用多表查询
- 尽量不要使用集合运算
SQL的类型:
- DML(Data Manipulation Language 数据操作语言): select insert update delete
- DDL(Data Definition Language 数据定义语言): create table,alter table,truncate table,drop table
create/drop view,sequnece,index,synonym(同义词) - DCL(Data Control Language 数据控制语言): grant(授权) revoke(撤销权限)