Oracle 学习笔记（二）
知识概要：
1.子查询
2.集合操作
3.DML语句操作
4.其他数据库对象
1.子查询
|
查询工资比SCOTT高的员工信息 |
|
1 select * 2 from emp 3 where sal > (select sal 4 from emp 5 where ename='SCOTT'); |
|
SQL> 注意的问题 SQL> 1. 括号 SQL> 2. 合理的书写风格 SQL> 3. 可以在主查询的where select having from后面放置子查询 SQL> 4. 不可以在group by后面放置子查询 SQL> 5. 强调from后面的子查询 SQL> 6. 主查询和子查询可以不是同一张表；只要子查询返回的结果，主查询可以使用即可 SQL> 7. 一般不在子查询中使用order by；但在Top-N分析问题中，必须对子查询排序 SQL> 8. 一般先执行子查询 再执行主查询；但相关子查询例外 SQL> 9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符 SQL> 10. 子查询中的null值 |
|
3可以在主查询的where select having from后面放置子查询 |
|
select empno,ename,sal,(select job from emp where empno=7839) 值 from emp; |
|
5. 强调from后面的子查询 |
|
SQL> --查询员工的姓名和薪水 SQL> select * from (select ename,sal from emp); |
|
select * from (select ename,sal,sal*12 annlsal from emp) |
|
6. 主查询和子查询可以不是同一张表；只要子查询返回的结果，主查询可以使用即可 查询部门名称是SALES的员工 |
|
select * 2 from emp 3 where deptno=(select deptno 4 from dept 5* where dname='SALES') |
|
select * 2 from emp 3 where deptno=(select deptno 4 from dept 5* where dname='SALES') |
|
In 查询部门名称是SALES和ACCOUNTING的员工 |
|
select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING'); |
|
-any 和集合中的任意一个值比较 查询工资比30号部门任意一个员工高的员工信息 |
|
select * 2 from emp 3 where sal > any (select sal from emp where deptno=30); |
|
all: 和集合中的所有值比较 SQL> --查询工资比30号部门所有员工高的员工信息 |
|
select * 2 from emp 3 where sal > all (select sal from emp where deptno=30) |
|
-多行子查询中的null -查询不是老板的员工 |
|
select * 2 from emp 3 where empno not in (select mgr from emp where mgr is not null); |
|
2集合运算 UNION 返回两个集合去掉重复部分后的所有的数据 UNION ALL 返回两个集合的所有数据包括重复的 INSTERSECT MINUS |
|
SQL> 查询10和20号部门的员工 |
|
SQL> select * from emp where deptno=10 2 union 3 select * from emp where deptno=20; |
|
SQL> /* SQL> 注意的问题： SQL> 1。 参与运算的各个集合必须列数相同 且类型一致 SQL> 2. 采用第一个集合的表头作为最后的表头 SQL> 3. 如果排序，order by必须在最后一个集合后 SQL> 4. 括号 SQL> */ |
|
INSTERSECT select ename, sal from emp where sal between 700 and 1300 INSTERSECT select ename ,sal from emp where sal between 1201 and 1400 minus select ename, sal from emp where sal between 700 and 1300 minus select ename ,sal from emp where sal between 1201 and 1400 |
3. DML 数据操作语言
SQL语言的类型
DML(Data Manipulation Language 数据操作语言): insert update delete select
DDL(Data Definition Language 数据定义语言): create/alter/drop/truncate table
DCL(Data Control Language 数据控制语言）：grant revoke(参考：26-管理用户安全.avi)
INSERT
insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
输入 empno 的值: 1002
输入 ename 的值: 'Mike'
输入 sal 的值: 3000
输入 deptno 的值: 10
原值 1: insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno)
新值 1: insert into emp(empno,ename,sal,deptno) values(1002,'Mike',3000,10)
SELECT &t
SQL> select empno,ename,&t
2 from emp;
输入 t 的值: sal
原值 1: select empno,ename,&t
新值 1: select empno,ename,sal
Delete 和 truncate的区别
SQL> delete和truncate区别
SQL> 1. delete是逐条删除 truncate先摧毁表 再重建
SQL> 2. ****delete是DML（可以回滚） truncate是DDL（不可以回滚）
SQL> 3. delete不会释放空间 truncate会
SQL> 4. delete会产生碎片 truncate不会
SQL> 5. delete可以闪回 truncate不可以闪回(flashback)
SQL> */
|
SQL> --问题：数据在delete后，rollback前，数据在哪里？ SQL> --undo表空间 (还原表空间) SQL> host cls SQL> 事务的标志 SQL> 1. 起始标志： 事务中，第一条DML语句 SQL> 2. 结束标志： 提交 显式commit SQL> 隐式 正常退出exit，DDL,DCL SQL> 回滚 显式rollback SQL> create table testsavepoint 2 (tid number, 3 tname varchar2(20)); 表已创建。 SQL> insert into testsavepoint values(1,'Tom'); 已创建 1 行。 SQL> insert into testsavepoint values(2,'Mike'); 已创建 1 行。 SQL> select * from testsavepoint; TID TNAME ---------- -------------------- 1 Tom 2 Mike 已选择 2 行。 SQL> insert into testsavepoint values(3,'Mara'); 已创建 1 行。 SQL> select * from testsavepoint; TID TNAME ---------- -------------------- 1 Tom 2 Mike 3 Mara 已选择 3 行。 SQL> --定义保存点 SQL> savepiont a; SP2-0734: 未知的命令开头 "savepiont ..." - 忽略了剩余的行。 SQL> savepoint a; 保存点已创建。 SQL> insert into testsavepoint values(4,'Jona'); 已创建 1 行。 SQL> rollback to savepoint a; 回退已完成。 SQL> select * from testsavepoint; TID TNAME ---------- -------------------- 1 Tom 2 Mike 3 Mara 已选择 3 行。 SQL> commit; 提交完成。 SQL> set transaction read only; 事务处理集。 SQL> select * from testsavepoint; TID TNAME ---------- -------------------- 1 Tom 2 Mike 3 Mara 已选择 3 行。 SQL> insert into testsavepoint values(4,'Jona'); insert into testsavepoint values(4,'Jona') * 第 1 行出现错误: ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作 SQL> rollback; 回退已完成。 |
4. 创建和管理表
|
典型例子 SQL> --创建表，保存20号部门的员工 SQL> create table emp20 2 as 3 select * from emp where deptno=20; 表已创建。 SQL> select * from emp20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ------------------------------------------------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7902 FORD ANALYST 7566 03-12月-81 3000 20 |
|
--修改表：追加新列，修改列，删除列 重命名列 |
|
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; |
|
SQL> --Oracle回收站 SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- ------------------------------------------------------ TESTDELETE BIN$xACLjXLhTxSlyUGR0JNdWA==$0 TABLE 2014-09-18:16:02:15 清空回收站 SQL> purge recyclebin; 回收站已清空。 在回收站中查找曾经删除过的表 |
|
select * from "BIN$nLj3MpCTTuaZoq2RJ4P2Pw==$0"; |
|
SQL> create table student 2 ( 3 sid number constraint student_PK primary key, 4 sname varchar2(20) constraint student_name_notnull not null, 5 gender varchar2(4) constraint student_gender check (gender in ('男','女')), 6 email varchar2(40) constraint student_email_unique unique 7 constraint student_email_notnull not null, 8 deptno number constraint student_FK references dept(deptno) on delete set null 9 ); |
|
5 其他数据对象 |
|
View |
|
SQL> create view empinfoview 2 as 3 select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname 4 from emp e,dept d 5 where e.deptno=d.deptno; |
|
-序列 sequence |
|
create sequence myseq; |
|
insert into testseq values(myseq.nextval,'aaa'); 已创建 1 行。 |
|
索引 index SQL> create index myindex 2 on emp(deptno); |
|
同义词 |
|
create synonym hremp for hr.employees; create synonym hremp for hr.employees * 第 1 行出现错误: ORA-01031: 权限不足 SQL> / 同义词已创建。 SQL> select count(*) from hremp; COUNT(*) ----------------------- 107 已选择 1 行。 SQL> spool off |