【Oracle教学】Introduction to Oracle9i: SQL

1. 书写基本SQL语句 ★★★★★
提纲:
行,列的概念,别名和运算符的使用方法
案例:
跨国公司令人崩溃的字段名
实例:

create table t1_1 ("j*ssf" number, "fsd%6^8f" varchar2(10));

2. 限制和排序数据 ★★★★★
提纲:
where和order by的作用
案例:
永远错误的排序:字符和数字排序导致的排行榜问题
实例:

create table t2_1 (id varchar2(10), name varchar2(10));
insert into t2_1 values ('1','a');
insert into t2_1 values ('5','b');
insert into t2_1 values ('9','c');
insert into t2_1 values ('10','d');
insert into t2_1 values ('21','e');
commit;
create table t2_2 (id number, name varchar2(10));
insert into t2_2 values (1,'a');
insert into t2_2 values (5,'b');
insert into t2_2 values (9,'c');
insert into t2_2 values (10,'d');
insert into t2_2 values (21,'e');
commit;
select * from t2_1 order by id;
select * from t2_2 order by id;

3. 单行函数 ★★★★
提纲:
主要数据类型的转换和相关函数
案例:
隐式转换导致的性能问题
实例:

create table t3_1 (id varchar2(10));
insert into t3_1 select rownum from dual connect by rownum < 100;
commit;
create index ind_t3_1 on t3_1 (id);
explain plan for select id from t3_1 where id='12';
select * from table(dbms_xplan.display);
explain plan for select id from t3_1 where id=12;
select * from table(dbms_xplan.display);

4. 多表连接查询	★★★★
提纲:
各种join的使用,什么是标准SQL	
案例:
如何找到丢失的数据	
实例:
create table t4_1 (id number, name varchar2(10));
insert into t4_1 values (1,'a');
insert into t4_1 values (2,null);
insert into t4_1 values (3,'c');
insert into t4_1 values (4,'d');
insert into t4_1 values (5,'e');
commit;
create table t4_2 (id number, name varchar2(10));
insert into t4_2 values (1,'a');
insert into t4_2 values (2,'b');
insert into t4_2 values (3,null);
insert into t4_2 values (4,'d');
insert into t4_2 values (5,'e');
commit;
select a.id, a.name, b.id, b.name from t4_1 a, t4_2 b where a.id=b.id;
select a.id, a.name, b.id, b.name from t4_1 a, t4_2 b where a.name=b.name;
select a.id, a.name, b.id, b.name from t4_1 a, t4_2 b where a.name=b.name(+);
select a.id, a.name, b.id, b.name from t4_1 a, t4_2 b where a.name(+)=b.name;
select a.id, a.name, b.id, b.name from t4_1 a full outer join t4_2 b on (a.name=b.name);
select a.id, a.name, b.id, b.name from t4_1 a, t4_2 b where nvl(a.name,' ')=nvl(b.name,' ');

5. 分组聚合 ★★★★★
提纲:
给力的分组函数
案例:
快速选show girl
实例:

SELECT city, age, info
  FROM show_girl_table
 WHERE city in ('SH', 'BJ', 'HZ')
 GROUP BY city
HAVING age BETWEEN 18 AND 22
 ORDER BY city, age;

6. 子查询 ★★★★
提纲:
子查询和多表连接查询的区别是啥?
案例:
那个穿hello kitty衣服的医生的病人是谁?
实例:

select *
  from 病人表
 where 病人ID in
       (select 病人ID
          from 医生病人对照表
         where 医生ID in
               (select 医生ID from 医生表 where 医生制服 = 'hello kitty'));
select b.医生ID, a.*
  from 病人表 a, 医生病人对照表c, 医生表 b
 where a.病人ID = c.病人ID
   and b.医生ID = c.医生ID
   and b.医生制服 = 'hello kitty';

7. 使用iSQL*Plus ★
提纲:
网页版SQL*Plus
案例:
比iSQL*Plus更优秀的工具
实例:
SQL*Plus / PLSQL Developer / Toad

8. 操纵数据 ★★★★
提纲:
DML,DDL,事务的概念
案例:
使用merge整合数据
实例:

create table t8_1 (id number, name varchar2(10));
create table t8_2 (id number, name varchar2(10));
insert into t8_1 values (1,'a');
insert into t8_1 values (2,'b');
insert into t8_1 values (3,'b');
insert into t8_2 values (2,'g');
insert into t8_2 values (4,'d');
insert into t8_2 values (5,'e');
commit;
merge into t8_1 a
using t8_2 b
on (a.id = b.id)
when matched then
  update set a.name = b.name
when not matched then
  insert values (b.id, b.name);

9. 创建表 ★★★
提纲:
创建,修改,清空表
案例:
强拆和阳光政策比对(truncate delete)
实例:

create table t9_1 as select * from dba_objects;
select bytes from dba_segments where segment_name='T9_1';
delete from t9_1;
commit;
select count(*) from t9_1;
select bytes from dba_segments where segment_name='T9_1';
insert into t9_1 select * from dba_objects;
commit;
select count(*) from t9_1;
select bytes from dba_segments where segment_name='T9_1';
truncate table t9_1;
select count(*) from t9_1;
select bytes from dba_segments where segment_name='T9_1';

10. 约束 ★★★
提纲:
约束的分类和使用
案例:
主键和唯一键的区别
实例:

create table t10_1 (id number, name varchar2(10));
insert into t10_1 values (1,'a');
insert into t10_1 values (2,'b');
insert into t10_1 values (3,'c');
insert into t10_1 values (4,'d');
insert into t10_1 values (5,'e');
insert into t10_1 values (6,'f');
insert into t10_1 values (5,'g');
commit;
alter table t10_1 add constraint pk_id primary key (id);
alter table t10_1 add constraint uk_id unique (id);

11. 创建视图 ★★ (ref: 9-会创建和使用视图)
提纲:
视图的概念和使用,TOP-N查询
案例:
30多行的查询,程序员的噩梦
实例:

CREATE OR REPLACE VIEW V_APS_USERS_AGT AS
SELECT agt.agt_code                                                      user_id
      ,agt.agt_code                                                      agt_code
      ,agt.agt_nm                                                        agt_nm
      ,CASE WHEN agt.aps_test_user_ind = 'Y' THEN 'A'
        ELSE DECODE(agt.stat_cd,'01','A','I')  END                       stat_cd
      ,SYSDATE                                                           last_upd_dt
      ,'AMS'                                                             last_upd_by
      ,CASE WHEN agt.aps_test_user_ind = 'Y' THEN 'Y'
       ELSE SUBSTR(cas_agt_utl.par_cert_elig(agt.agt_code),1,1) END      par_cert_ind
      ,CASE WHEN agt.aps_test_user_ind = 'Y' THEN 'Y'
       ELSE SUBSTR(cas_agt_utl.ul_cert_elig(agt.agt_code),1,1) END       ul_cert_ind
      ,CASE WHEN agt.aps_test_user_ind = 'Y' THEN 'Y'
       ELSE SUBSTR(cas_agt_utl.uvl_cert_elig(agt.agt_code) ,1,1) END     uvl_cert_ind
      ,CASE WHEN agt.aps_test_user_ind = 'Y' THEN 'N'
       ELSE SUBSTR(cas_agt_utl.black_list_agt(agt.agt_code) ,1,1) END    black_list_ind
      ,CASE WHEN agt.aps_test_user_ind = 'Y' THEN 'Y'
       ELSE SUBSTR(cas_agt_utl.is_vip_agt(agt.agt_code) ,1,1) END        vip_agt_ind
      ,agt.terr_cd                                                       terr_cd
      ,'U'                                                               user_mode
      ,agt.aps_reg_code                                                  reg_code
      ,agt.aps_db_key                                                    db_key
      ,can.cert_num                                                      cert_num
      ,can.lic_num                                                       lic_num
      ,can.cert_num_exp_dt                                               cert_num_exp_dt
      ,can.lic_num_exp_dt                                                lic_num_exp_dt
      ,can.id_num                                                        id_num
      ,agt.bus_phone                                                     bus_phone
      ,agt.BR_CODE                                                       br_code
      ,agt.UNIT_CODE                                                     unit_code
      ,agt.LOC_CODE                                                      loc_code
      ,agt.MAILBOX_NUM                                                   mailbox_num
      ,CASE WHEN agt.aps_test_user_ind = 'Y' THEN 'N'
      ELSE SUBSTR(cas_agt_utl.new_agt_ind(agt.agt_code)  ,1,1)  END      new_agt_ind
      ,SUBSTR(cas_agt_utl.dist_chnl_cat(agt.agt_code) ,1,2)							 dist_chnl_cat
      ,SUBSTR(ams_agt.sso_or_terr_cd(agt.agt_code),1,5)									 so_sso_code
      ,SUBSTR(cas_agt_utl.rc_black_list_agt(agt.agt_code),1,1)           rc_black_agt_ind
      ,SUBSTR(cas_terr.get_prov_cd(agt.terr_cd),1,3)                     prov_cd
      ,agt.bus_grp_cd                                                    bus_grp_cd --CNV-201104
  FROM ams.tams_agents           agt
      ,ams.tams_candidates       can
      ,ams.tams_business_groups  bus
 WHERE agt.can_num = can.can_num
   AND agt.bus_grp_cd = bus.bus_grp_cd
   AND agt.terr_cd = bus.terr_cd
   /* -- 20100427         -- Open to national wide, no need to consider cmp_sys
   AND (bus.cmp_sys IN ('APS','ALL')
        --APS_MSLOR agt.cat_cd IN ('BS','CM','BM','BT','OT')
        OR cas_agt_utl.dist_chnl_cat(agt.agt_code) = 'BA'       -- APS_MSL
        OR agt.agt_code = 'SH88777'        -- IT internal account
        )
  */ -- 20100427;

12. 其他的数据库对象 ★★★★ (ref: 9-会创建和使用索引、序列)
提纲:
索引,序列,同义词的概念
案例:
为啥翻书比翻脸还快,发号器使数据库崩溃
实例:

Index: Refer to C3
create sequence seq_12 cache 100;
select seq_12.nextval from dual;
select seq_12.nextval from dual;
alter system flush shared_pool;
select seq_12.nextval from dual;
select seq_12.nextval from dual;
exec dbms_shared_pool.keep(name => 'SEQ_12',flag => 'Q');
select seq_12.nextval from dual;
select seq_12.nextval from dual;
alter system flush shared_pool;
select seq_12.nextval from dual;
select seq_12.nextval from dual;

13. 用户访问控制 ★★ (ref: 10-懂得角色和用户的权限赋予,收回)
提纲:
权限,角色的使用。Dblink的概念
案例:
建立数据库的安全制度
实例:

grant dba to hr;
revoke dba from hr;
grant select any dictionary to hr;
revoke select any dictionary from hr;
grant select any dictionary to hr with admin;
grant select any dictionary to hr with admin option;

*14. workshop ★
提纲:
第一阶段知识总结测试
案例:
快速完成数据库开发需求
实例:
Pass.

15. 集合操作 ★★
提纲:
并集、交集等概念
案例:
not in和minus的异同
实例:

create table t15_1 (id number, name varchar2(10));
insert into t15_1 values (1,'a');
insert into t15_1 values (2,'b');
insert into t15_1 values (3,'c');
insert into t15_1 values (4,'d');
create table t15_2 (id number, name varchar2(10));
insert into t15_2 values (3,'c');
insert into t15_2 values (4,'d');
insert into t15_2 values (5,'e');
insert into t15_2 values (6,'f');
commit;
select * from t15_1 minus select * from t15_2;
select * from t15_1 intersect select * from t15_2;
select * from t15_1 union select * from t15_2;
select * from t15_1 union all select * from t15_2;
explain plan for select * from t15_1 minus select * from t15_2;
explain plan for select * from t15_1 where (id,name) not in (select * from t15_2);
select * from table(dbms_xplan.display);

16. 日期函数 ★
提纲:
时间函数,时区的概念
案例:
跨国公司数据库的协调方法
实例:

select dbtimezone from dual;
select sessiontimezone from dual;

17. 高级分组聚合 ★
提纲:
rollup、cube,grouping set的使用
案例:
秒杀复杂的报表需求
实例:
rollup:
第一是按把所有和显示到第一行。
第二是按GROUP BY department_id 这种情况显示出来,这里是按部门进行分组。
第三是按GROUP BY department_id, job_id情况显示出来,这里是按某部门中得名字进行分组得
cube:
第一是按把所有和显示到第一行。
第二是按group by department_id 这种情况分组
第三是按group by job_id这种情况分组
第四是按group by department_id, job_id分组.
grouping set:
第一是按把所有和显示到第一行
第二是按group by department_id进行分组
第三是按group by job_id进行分组.

18. 高级子查询 ★★ (ref: 12-将每个雇员的工资改成自己部门最高工资的数额)
提纲:
使用级联查询和DML,使用exists,with简化查询逻辑
案例:
交友网站快速配对
实例:

SELECT employee_id, last_name, job_id, department_id
FROM   employees outer
WHERE  EXISTS ( SELECT 1
                 FROM   employees
                 WHERE  manager_id = 
                        outer.employee_id);

SELECT employee_id, last_name, job_id, department_id
  FROM employees
 WHERE employee_id in (select manager_id from employees);

19. 层次遍历查询 ★
提纲:
connect by使用方法
案例:
如何顺藤摸瓜,抓到所有操纵股价的敢死队?
实例:

SELECT employee_id, last_name, job_id, manager_id
  FROM employees
 START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id;

20. 高级DML和DDL ★
提纲:
ALL/FIRST INSERT,外部表使用
案例:
不用导入几百万数据也能查询到你要的东西
实例:

create or replace directory BDUMP as 'D:\oracle\product\10.2.0\admin\CAS\bdump';
CREATE EXTERNAL TABLE ALERT_LOG (
TEXT VARCHAR2(400)
) 
ORGANIZATION EXTERNAL 
(
TYPE "ORACLE_LOADER" 
DEFAULT DIRECTORY "BDUMP" 
ACCESS PARAMETERS
 ( records delimited by newline
   nobadfile
   nodiscardfile
   nologfile
 ) 
LOCATION ('alert_cas.log') 
) 
REJECT LIMIT UNLIMITED;
Trackback

no comment untill now

Add your comment now

切换到手机版