Oracle数据库 基本查询

1子查询
1.1子查询语法
select select_list from table
where expr operator(select select_list from table)

1.2子查询注意的问题：

1. 子查询需要添加括号
2. 合理的书写风格
3. 可以在where select having from后面 都可以使用子查询
4. 不可以在group by后面使用子查询
5. 强调from后面的子查询
6. 主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可
7. 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
8. 一般先执行子查询，再执行主查询；但相关子查询例外
9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
   10.子查询中的null
   1.3 举例子
   了查询需要括号 查询工资比SCOTT高的员工信息
   SQL>select * from emp where sal > (select sal from emp where ename=‘SCOTT’)
   select后可以添加子查询 查询员工记录后，每一行后面显示总裁
   SQL> select empno,ename,sal,(select ename from emp where empno=7839) 总裁 from emp;
   from后面的子查询 查询员工信息：员工号 姓名 月薪
   SQL> select * from (select empno,ename,sal from emp);
   查询员工信息：员工号 姓名 月薪 年薪
   SQL> select * from (select empno,ename,sal,sal12 annsal from emp);
   上面的语句跟select empno,ename,sal,sal12 annsal from emp性能一样的，oracle内部会重构
   主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可 查询部门名称是SALES的员工，实现方法有两种
   SQL>select * from emp where deptno=(select deptno from dept where dname=‘SALES’);
   SQL>select e.* from emp e,dept d where e.deptno=d.deptno and d.dname=‘SALES’;
   in 在集合中 查询部门名称是SALES和ACCOUNTING的员工
   SQL> select * from emp where deptno in (select deptno from dept where dname=‘SALES’ or dname=‘ACCOUNTING’);
   any: 和集合中的任意一个值比较 查询工资比30号部门到少一个员工高的员工信息【下面两行是等价】
   SQL> select * from emp where sal > any (select sal from emp where deptno = 30);
   SQL> select * from emp where sal > (select min(sal) from emp where deptno = 30);
   all 和集合中的所有值比较 查询工资比30号部门所有员工高的员工信息【下面两行是等价】
   SQL> select * from emp where sal > all (select sal from emp where deptno = 30);
   SQL> select * from emp where sal > (select max(sal) from emp where deptno = 30);
   not in(a,b,null)
   not in中不能有null
   in 中可以有null
   多行子查询中的null 查询是老板（管理层）的员工
   SQL> select * from emp where empno in(select mgr from emp);
   查询不是老板(管理层)的员工
   SQL> select * from emp where empno not in(select mgr from emp);【not in中不能有null】
   SQL> select * from emp where empno not in(select mgr from emp where mgr is not null);【这个写法才正确】

2 集合运算

举例子
这里了解union就可以,intersect和minus原理一样
Union
注意的问题：
1.参与运算的各个集合必须列数相同且类型一致
2. 采用第一个集合作为最后的表头
3. order by 永远在最后
排序是不会影响总结果集， 只会影响结构。
4. 括号 查询10和20号部门的员工
SQL> select * from emp where deptno in (10,20);
SQL> select * from emp where deptno=10 or deptno=20;
上面的代码等价于下面的代码
select * from emp where deptno=10
加上
select * from emp where deptno=20
不推荐
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
等价
select deptno,job,sum(sal) from emp group by deptno,job
+
select deptno,sum(sal) from emp group by deptno
+
select sum(sal) from emp
等价
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,性能比较差
Set timing on/off 可以查时间

3 课堂练习
3.1 rownum行号 （ 数据初始化的时候就已经存在 ）
SQL> select rownum,e.* from emp e;
rownum永远按照默认的顺序生成
SQL> select rownum,e.ename,e.sal from emp e order by sal desc;
rownum只能使用 < <=; 不能使用> >=

3.2 rowid行地址
SQL> select rowid,empno,ename,sal from emp;

SQL> select * from emp where rowid=‘AAAMfPAAEAAAAAgAAA’;

3.3 临时表

1. create global temporary table *****
2. 自动创建: order by
   特点：当事务或者会话结束的时候，表中的数据自动删除
   SQL> create global temporary table tmp_table1 (tid number,tname varchar2(20)) on commit delete rows;
   SQL> insert into tmp_table1 values (1,‘Tom’);
   SQL> select * from tmp_table1;
   SQL> commit;【commit后，数据会消失】
   SQL> desc tmp_table1;

3.4行转列
wm_concat(varchar2) 组函数

SQL> select deptno,wm_concat(ename) namelist from emp group by deptno;

第一题：【oracle的分页】 2 每页3行
Select * from emp where limit (3,3)
找到员工表中工资最高的前三名
select e.ename,e.sal from (select * from emp order by sal desc) e where rownum <=3
分页【这样写是不对的】，rownum不能使用> >=
select e.ename,e.sal
from (select * from emp order by sal desc) e
where rownum >=3 and rownum <=6
分页的一种正确写法
select * from (select rownum r,e.* from (select * from emp order by sal) e where rownum <=6) where r>3

第二题：
找到员工表中薪水大于本部门平均薪水的员工
1.求出部门的平均工资
2.判断员工是否工资大于本部门的平均工资
第一种写法
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 e.empno,e.ename,e.sal,(select avg(sal) from emp where deptno = e.deptno) avgsal
from emp e
where e.sal > (select avg(sal) from emp where deptno = e.deptno)
第三种写法：
select * from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno=e1.deptno group by deptno);

第三题：
计算每年入职的员工数

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

4数据处理
4.1SQL 的类型
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（撤销权限）
4.2 例子

PreparedStatement
预处理 insert into emp (empno,ename,sal,deptno) values (?,?,?,?);
地址符 & SQL>insert into emp (empno,ename,sal,deptno) values (&empno,&ename,&sal,&deptno)

SQL> select empno,ename,sal,&t from emp;

SQL> select * from &t;

批处理 复制表结构
SQL> create table emp10 as select * from emp where 1=2;
一次性将emp中，所有10号部门的员工插入到emp10中
SQL> insert into emp10 select * from emp where deptno=10;
delete和truncate的区别: 1. delete逐条删除；truncate先摧毁表，再重建
2.(根本)delete是DML（可以回滚），truncate是DDL（不可以回滚）
3. delete不会释放空间 truncate会
4. delete可以闪回 (flashback), truncate不可以
5. delete会产生碎片；truncate不会
SQL> set feedback off
SQL> @C:\课程\JavaEE\oracle【3天】\资料\testdelete.sql
SQL> select count(*) from testdelete;
SQL> set timing on
SQL> delete from testdelete;
SQL> drop table testdelete purge;
SQL> @C:\课程\JavaEE\oracle【3天】\资料\testdelete.sql
SQL> truncate table testdelete;
SQL> set feedback on
SQL> set timing off

事务的标致 1. 起始标志：事务中第一条DML语句
2. 结束标志：提交： 显式 commit
隐式 正常退出（exit），DDL，DCL
回滚: 显式 rollback
隐式 非正常退出，掉电，宕机
保存点 SQL> create table testsavepoint (tid number, tname varchar2(20));
SQL> insert into testsavepoint values(1,‘Tom’);
SQL> insert into testsavepoint values(2,‘Mary’);
SQL> savepoint a;
SQL> insert into testsavepoint values(3,‘Maake’);
SQL> select * from testsavepoint;
SQL> rollback to savepoint a;
SQL> select * from testsavepoint;
SQL> commit;
SQL> set transaction read only;
SQL> select * from testsavepoint;
SQL>insert into testsavepoint values(3,‘Maake’);【不能在 READ ONLY 事务处理中执行插入/删除/更新操作
SQL> rollback;

5创建和管理表 （int char dou bigint（long））
表创建 SQL> create table test3(tid number,tname varchar2(20),hiredate date default sysdate);
SQL> insert into test3(tid,tname) values(1,‘Tom’);
SQL> select * from test3;
创建表，保存20号部门的员工
SQL> create table emp20 as select * from emp where deptno=20;
创建表，员工号 姓名 月薪 年薪 部门名称
SQL> 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
添加表字段
SQL> alter table test3 add photo blob;
修改表字段
SQL> alter table test3 modify tname varchar2(40);
删除表字段
SQL> alter table test3 drop column photo;
重命名表字段
SQL> alter table test3 rename column tname to username;
改表名
SQL> rename test3 to test5;
删除表
SQL> drop table test5

显示回收站
SQL> show recyclebin

清空回收站
SQL> purge recyclebin;
删除的表，可以从回收站查询
SQL> drop table TESTSAVEPOINT;
SQL> select * from “BIN$ULtYrrqvTlqfr+qeSXIHNA==$0”;
闪回删除
SQL> flashback table TESTSAVEPOINT to before drop;
注意：管理员没有回收站sys
创建约束表字段
SQL>create table test5
(tid number,
tname varchar2(20),
gender varchar2(2) check (gender in (‘男’,‘女’)),
sal number check(sal > 0)
)
SQL> insert into test5 values(1,‘Jone’,‘男’,1000);
SQL> insert into test5 values(1,‘Jone’,‘A’,1000);【违反检查约束条件 (SCOTT.SYS_C005420)】
SQL> insert into test5 values(1,‘Jone’,‘男’,-10);
外键关联
SQL>create table student
(sid number constraint student_pk primary key,
sname varchar2(20) not null,
deptno number constraint student_fk references dept(deptno))
SQL> insert into student values (1,‘Jone’,10);

6视图
6.1常见的数据库对象
对象 描述
表 基本的数据存储集合，由行和列组成
视图 从表中抽出的逻辑上的相关的数据集合
序列 提供有规律的数值
索引 提搞查询的效率
同义词 给对象起别名

6.2视图
视图是一张虚表
视图建立在已有表的基础，视图赖以建立的这些表称为基表
向视图提供数据的内容的语句为select语句，可以将视图理解为存储起来的select语句
视图是向用户提供基表数据的另一种形式

6.3视图的优点
限制数据访问
简化复杂查询
提供数据的相互独立
同样的数据可以有不同的显示方式
但视图不能提高性能

ora-01034 oracle not acailable

添加权限 在虚拟机里以管理员登录，并授权scott的视图权限
SQL> sqlplus sys/manager as sysdba
SQL> grant create view to scott
创建视图 SQL>create view empinfoview
as
select e.empno,e.ename,e.sal,e.sal12 annsal,d.dname
from emp e,dept d
where e.deptno=d.deptno
查看视图结构 SQL> desc empinfoview;
查询视图 SQL> select * from empinfoview;
只读视图 SQL>create or replace view empinfoview
as
select e.empno,e.ename,e.sal,e.sal12 annsal,d.dname
from emp e,dept d
where e.deptno=d.deptno
with read only

7 序列
7.1 序列
可供多个用户用来产生唯一数值的数据库对象
自动提供惟一的数值
共享对象
主要用于提供主键
将序列值装入内存可以提高访问效率

7.2创建序列
SQL> create sequence myseq;
SQL> create table testseq(tid number,tname varchar2(20));
SQL> select myseq.nextval from dual;
SQL> select myseq.currval from dual;
SQL> insert into testseq values(myseq.nextval,‘aa’);
SQL> insert into testseq values(myseq.nextval,‘aa’);
SQL> insert into testseq values(myseq.nextval,‘aa’);
SQL> slect * from testseq;
SQL> commit;
SQL> insert into testseq values(myseq.nextval,‘aa’);
SQL>rollback

8 索引
8.1 索引
一种独立于表的模式对象，可以存储在与表不同的磁盘或者表空间
索引被删除或者损坏，不会对表产生影响，其影响只是查表的速度
索引一旦被建立，oracle管理系统会对其进行自动维护，而且由oracle管理系统决定何时使用索引，用户不用在查询查询语句中指定使用哪个索引
在删除一个表时，所有基于该表的索引会自动被删除
通过指针加速oracle服务器的查询速度
通过快速定位的方法，减少磁盘的I/O

索引可以提高查询速度，我们只需要创建索引，至于怎么使用索引，是oracle系统内部的事情

8.2 什么情况下创建索引(经常查询并且不是使用like查询，一般使用在外键。)
列中数据值分布很广
列经常在where语句（查询条件 = > >= like(不能使用索引)）或连接条件中出现 内，外，左外，右外 可以使用索引
表经常被访问，而且量很大，访问的数据大概占总量的2%~4%

8.3 什么情况下不要创建索引 (数据量小，经常更新操作的数据，经常使用like模糊查询的数据，不使用条件查询的)
表很小
经常更新
列不经常在where语句或连接条件中出现

8.4 创建索引

SQL> create index myindex on emp();

9 同义词
9.1 同义词 取别名
使用同义词访问其它对象
方便访问其它用户的对象
缩短对象名字的长度
创建同义词需要授权grant create synonym to scott

9.2 使用
SQL> create synonym myemp for emp;
SQL> select * from myemp;
SQL> create synonym hremp for hr.employees;
SQL> grant select on hr.employees to scott【需要授权才能访问其它用户的表】
SQL> select count() from hr.employees;
SQL> select count() from hremp;