子查询：解决不能一步求解。
子查询(内查询)
主查询(外查询)
主查询和子查询可以是：1：n
注意10个问题：
小括号
合理的书写风格
可以在where、select、having、from后面 都可以使用子查询
select后的子查询必须是单行子查询(只返回一条记录)不可以在group by后面使用子查询
强调from后面的子查询
把子查询的结果看成一张表。主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可。
查询部门名称是sales的员工：
子查询：
多表查询：连接条件至少n-1条
优化原则：尽量使用多表查询。一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序。
top-n分析问题：rownum 行号(伪列)，只能使用< <= , 不能使用> >= 。
select rownum,ename,sal from (select * from emp order by sal desc) where rownum<=3 ;一般先执行子查询，再执行主查询；但相关子查询例外
相关子查询：(先执行主查询)
把主查询中的值 作为参数传递给子查询。
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);
单行子查询只能使用单行操作符；多行子查询只能使用多行操作符。
主查询 和 单行子查询 之间，只能使用：
单行操作符：= < >
主查询 和 多行子查询 之间，只能使用：
多行操作符：in(在集合中)、 any(和集合中的任意一个值比较)、 all(和集合中的所有值比较)
any 和 all 前必须使用一个单行操作符。多行子查询中的null值问题：
not in (a,b,null) 语法错误。等同于 < > ALL
课堂练习：
-
找到员工表中工资最高的前三名：select rownum,ename,sal from (select * from emp order by sal desc) where rownum<=3;
SQL> select rownum,empno,ename,sal
2 from (select * from emp order by sal desc)
3 where rownum<=3;ROWNUM EMPNO ENAME SAL
1 7839 KING 5000
2 7788 SCOTT 3000
3 7902 FORD 3000
分页：where rownum>=5 and rownum<=8
不成立,因为：rownum 行号(伪列)，只能使用< <= , 不能使用> >= 。Oracle行式数据库，取了第一行才能取第二行。
Oracle使用嵌套子查询来实现分页：
select *
from (select rownum r,e1.
from (select * from emp order by sal) e1
where rownum <=8)
where r >=5;
临时表：
手动创建：create global temporary table *****
自动创建：order by 后自动产生
特点：当事务或者会话结束时，表中数据自动删除。
-
找到员工表中薪水大于本部门平均薪水的员工： empno ename sal avgsal
select deptno,avg(sal) avgsal from emp group by deptno;DEPTNO AVGSAL
30 1566.66667
20 2175
10 2916.66667
select empno,ename,sal,avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) a
where e.deptno=a.deptno and e.sal>a.avgsal;
EMPNO ENAME SAL AVGSAL
7698 BLAKE 2850 1566.66667
7499 ALLEN 1600 1566.66667
7902 FORD 3000 2175
7788 SCOTT 3000 2175
7566 JONES 2975 2175
7839 KING 5000 2916.66667
相关子查询：(先执行主查询)
把主查询中的值 作为参数传递给子查询。
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);
-
统计每年入职的员工个数：(不许使用子查询)
total 1980 1981 1982 1987
14 1 10 1 2EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
select to_char(hiredate,'yyyy'),count(*) from emp group by to_char(hiredate,'yyyy');
select count(*) as "total",
sum(case to_char(hiredate,'yyyy') when '1980' then 1 else 0 end) as "1980",
sum(case to_char(hiredate,'yyyy') when '1981' then 1 else 0 end) as "1981",
sum(case to_char(hiredate,'yyyy') when '1982' then 1 else 0 end) as "1982",
sum(case to_char(hiredate,'yyyy') when '1987' then 1 else 0 end) as "1987"
from emp;
TOTAL 1980 1981 1982 1987
14 1 10 1 2
select count(*) as "total",
sum( decode(to_char(hiredate,'yyyy'), '1980', 1, 0) ) as "1980",
sum( decode(to_char(hiredate,'yyyy'), '1981', 1, 0) ) as "1981",
sum( decode(to_char(hiredate,'yyyy'), '1982', 1, 0) ) as "1982",
sum( decode(to_char(hiredate,'yyyy'), '1987', 1, 0) ) as "1987"
from emp;
行转列：
wm_concat(varchar2) 组函数
SQL> select deptno,wm_concat(ename) names from emp group by deptno;
DEPTNO NAMES
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
集合运算：
并集：union 公共部分只要一次
union all 公共部分要两次
交集：intersect
差集：minus
集合运算的注意问题：
- 参与运算的各个集合必须列数相同且类型一致,
列数不一样，补成一样的，根据列的类型使用：to_number(null)、to_char(null) - 采用第一个集合作为最后的表头
- order by 永远在最后
- 可以使用括号()
select * from emp where deptno=10
union 或者 union all
select * from emp where deotno=20;
group by 增强:
group by rollup(a,b)
sql执行时间：
set timing on/off
sql优化原则：尽量不要使用集合运算。
sql语句的类型:
- DML（增删改查）
select语句：DQL - DDL（数据定义语言）：create table、alter table、drop table、truncate table、
create/drop view、sequence(序列)、index、synonym(同义词) - DCL（数据控制语言）：grant授权、revoke撤销权限，管理用户的安全。
- insert：
隐式插入null值
显示插入null值
preparedStatement：
预编译一条带有?的sql
地址符&，相当于?
insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
输入 empno 的值：
select empno,ename,&c from emp;
select empno,ename,sal from &t;
批处理：
创建表：
create table emp10 as select * from emp where 1=2;
海量拷贝数据：数据移动
- 数据泵（datapump）--> plsql
- sql*Loader
- (数据仓库)由很多个数据库组成的比较大的数据库，外部表的方式。
- 可传输的表空间
- update：注意加where条件，否则处理的是整张表。
数据完整性：创建在这张表的约束。
- delete：注意加where条件，否则删除的是整张表。
区别：
- 逐条删除。
truncate：先摧毁表结构，在创建表结构。
2.最根本的区别：
delete语句是DML语句，truncate是DDL语句。
DML可以回滚，DDL不可以回滚。
- delete不会释放空间，truncate会。
10M的表，用delete清空后，表空间还是10M。 - delete可以闪回(flashback)，truncate不可以。
commit后想要恢复，就需要使用闪回。 - delete会产生碎片()；truncate不会。
Oracle插入数据的顺序是从后往前。
指针：HWM 高水位线，下次插入数据的位置。
delete删掉后空间不会释放，下次插入的数据还插到指针指向的高水位线。所以会产生空间间隙，形成许多碎片。
碎片产生后影响读取的速度。
去掉碎片：
- alter table emp move;
- 导出exp、expdp 和 导入imp、impdp。
exp -help 帮助文档。
测试速度：
set feedback off
@d:\xx.sql //导入sql脚本
set timing on
delete from testdelete; / drop table testdelete;
Oracle中是delete快。因为Oracle有个功能 undo数据(还原数据)。
mysql中是truncate快。
删除表：
drop table testdelete purge; 不经过回收站直接删除。
事务：
由一个或多个DML语句组成
DDL语句： 把前面的DML语句给提交了。
DCL：
本地事务(之前学的)
全局事务
发短信验证码给，给一个账户+钱，另一个账户-钱。
JTA，全局事务/分布式事务，spring支持jta。
事务的标志：
- 事务的起始标志：事务中第一条DML语句。
- 结束标志：提交（显示提交commit，隐式提交：正常退出、DDL语句、DCL语句）、
回滚（显示rollback，隐式：非正常退出、掉电、宕机）
控制事务：
定义保存点：savepoint a;
回滚到保存点：rollback to savepoint a;
隔离级别：
因为数据库支持并发访问。
Oracle中支持3种隔离级别：
sql99标准：
read commited（默认）
serializable （串行化，单线程）
orale自己的：
read only
DDL语句：
管理数据库的对象：10个对象
数据类型：
ROWID 行地址(伪列)字符串，相当于指针指向数据文件.dbf
创建表：
create table emp10 as select * from emp where 1=2;
条件为假，只拷贝表结构到新表。
创建视图：
create view emp10 as select * from emp where deptno=10;
修改表：alter table tablename
追加新列 + add
修改列，+modify
删除列，+ drop column
重命名列，+ rename column name1 to name2
重命名表
rename table1 to table2;
drop table tablename [purge];
并没有真的删除表，只是给它加一个标志recyclebin_name，删到了回收站里面。
Oracle回收站：
show recyclebin
purge recyclebin; 清空回收站
select * from "recyclebin_name";
可以查到回收站的表的内容
管理员是没有回收站的。
sqlplus sys/orcl as sysdba
管理员密码忘了，随便写一个密码就可以登录
用户名也忘了，也可以随便写一个就能登录。
从回收站恢复，第四天的闪回。
flashback table table1 to before drop;
约束：
列级约束：
not null
unique 唯一性的索引
primary key
foreign key
check约束，检查性约束。
create table tab1
(tid number,
gender varchar2 check (gender in ('男','女'))
sal number check (sal>0)
);
constraint ming check (sal>0)
表级约束：联合主键
外键约束;
foreign key
reference
on delete cascade
on delete set null
create table stu
(sid number constraint sid_primary_key primary key,
sname varchar2 constraint sname_not_null not null,
email varchar2 constraint email_unique unique,
cid number constraint foreign key cid_classes_fk references classes(cid) on delete set null
);
其他数据库对象：
- 视图：
权限不足
grant create view to scott
with check option
通过视图只能操作看得见的数据。
不建议通过视图对表进行修改。
受到限制
子查询中含有xxx，不能通过视图做insert/update/delete
with read only 只读
视图是不缓存数据的，是个逻辑上的概念，虚表。
物化视图，可以缓存数据。
- 序列：数组[20] -- 内存，提高效率。
auto_increment功能一样
create sequence sename;
increment by 1, 步长
nocycle
cache 默认20
两个伪列：nextval、curval(第一次不要使用这个)
回滚、系统异常、多个表同时使用同一序列，会造成序列裂缝，不连续。
修改序列，只影响。。。
删除序列
- 索引：（可以提高查询速度 是 必要条件，但是不是充分条件）
主键就是唯一性的索引。
一本书的目录。
create index myindex on emp(deptno);
索引表：目录
可以在多个列创建索引。
索引类型：
- B树索引（默认）平衡树，insert、update、delete(OLTP)
- 位图索引 --> 矩阵，行和列，select(OLAP 商品推荐)
create bitmap index *** on ***
- 同义词（别名）：
为表或视图起别名，就叫同义词。
create synonym myemp for emp;
grant create [public] synonym to scott
create synonym myemp2 for hr.employees;
私有同义词:默认
公有同义词：grant create public synonym to scott
grant select on hr.employees to scott;
select count(*) from myemp2;
