2023年Orcale职业培训笔记.docx

《2023年Orcale职业培训笔记.docx》由会员分享，可在线阅读，更多相关《2023年Orcale职业培训笔记.docx（29页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、DayOl. s _ e m p、s d ept表的字段含义fi r st_name 名last_ n ame。姓t i t 1 e。职位dep t _ i d。部门号com miss i on_ pct。提成(有空值).列出所有人的年薪selec t firs t n a me, s al a r y*12 from s_em p ;.给列起别名s e lect f i r st_name, s a 1 ary* 1 2 Ann_S a 1 from s emp;s e lec t first_n a me,salary * 1 2 Ann S al n from s_emp;selec t

2、 first_name,salary * 12 as Ann Sal n f r o m s_emp；1 .解决空值的函数 n v l(p 1 ,p2)n ull Oracl e当做无穷大来解决。空值不等于0。空值不等于空格算数表达式中为空值，返回空值#亚洲地区有哪些员工？s e lect e .first_name , r. n a mef r om s _emp ejoin s_de p t don e.dept_id = d . i djo i n s_r e g ion ron d.r e g ion_i d = r. idan d r .name 廿Asia;一-一一一一-为什么 w

3、here 能代替and4.非等值连接#列出员工的工资以及相应的工资级别？s e 1 ec te.ename , e.sal , s .g r adefrom em p ejoin salgrad e son e .sa 1 b etween s Jo s al and s .hi s al;#SMITH的工资级别？s ele c t e .e n ame， e .sa 1 , s.g r adefrom emp ej oi n s a Igrad e son e.sal b e twe e n s.los a 1 a nd s .hi s ala nd e .ename = SMITH;#3,

4、 5级有哪些员工（哪些员工属于3, 5级）？sele c t e.en a me , e. sal, s.gradefrom em p e joi n s a 1 g r a d e so n e .sa 1 b e tween s. 1 osal and s . h isaland s. g rad e in( 3 ,5 )；5.自连接#列出员工名和领导名的相应关系# #结果为2 4个，少一个ma n ager_id为空的人(BOSS丢了)s e le c t e. i d ,e. fir s t n a me emp 1 ay e e ,m.id, m. first n ame m ana

5、 g er f rom s _ e mp ej o in s_em p mo n e.mana g er_id = m. id;#列出哪些人是领导？select dist i net m.fir s t n a mefrom s_emp e join s_emp mo n m. i d = e. ma n ager i d；6. o u ter join 外连接# 内连接 from tl j o in t2 o n tl.id = t 2. i d# #from tl left o u t e r join t 2 on tl.id = t 2 . id。左边的表做驱动表# #fro m 11

6、 righ t o u ter join t 2 o n tl.id = t 2. icb 右边的 表做驱动表#外连接解决的问题:驱动表中的记录在结果集中“一个都不少”#列出员工名和领导名的相应关系?s ele c t e. f irst_ n ame em p loyee , n v 1 (m.f i rst_n a me/ Bos s ) manager f rom s_emp e1 ef t oute r join s _em p mon e.m a nag e r_ i d = m. i d;# #如何写外连接：# #先写出内连接,再拟定哪张表当驱动表就可以#哪个部门没有员工？ 14条

7、记录，少1条s e 1 e c t e.e n a me , e.d e p t no from emp ejoi n dept don e.d e pt n o = d . deptno ；#哪个部门没有员工？ 15条记录s e le c t e.ename , e.de p t no , d. d eptn o ,d. d n a m e from e mp eri g ht j o in de p t don e. d e p t no = d.de p tno;#哪个部门没有员工？ 1 5条记录s elec t d. de p tno ,d.d n ame , e . e name ,

8、 e. d eptnof rom emp e ri g ht join dept dd . deptnoo n e .deptno 二where e. em p n o is nul 1 ;# #使用外连接解决了两类问题：L把所有结果列出到结果集2.解决否认问题（不是，没有，不包含）#那些人是员工？（即：那些人不是领导?）#思绪：#先解决那些人是领导#能匹配的是领导#把匹配不上的挑出来#se 1 ec t e .fir s t name , m. f ir s t n ame from s _emp er i ght join s_ e mp mon e.mana ger _id = m.id

9、;#加条件sei e c t e.fir s t_n ame , m. firs t_nam efrom s_em p er i g ht j o in s_emp mon e.man a ger_id = m.id wh e r e e . i d is nu 1 1;一-为什么用and会出现结果错误?、/#最后列出m. f i r st n a me即可s e 1 e ct m. f irst_namefrom s_em p eri g ht joi n s_ e mp mon e.mana g er_ i d = m. i dw h er e e.i d is n u 11；用 and和

10、where会得到不同的结果什么时候用and什么时候用w h ereAnd在外连接之前做过滤，where在外连接之后做过滤7. Where在得出结果以后过滤 很重要别把#select e. e n am e , d. d namef r om e mp er i gh t joi n dept do n e.d e p tno 二 d.deptnoand e . e nam e =SM I THf;#驱动表的过滤所有写在wh e r e之后sele c t e.e n am e e e nam e , d.dname d en a m efrom e mp e ri g h t j oi n d

11、e p t done .deptno = d. d eptnoand e.ename =SMI TH;where e.emp n o i s n ull；#选择le f t jon或者righ t j oin不重要，重要的是选择哪张表做驱动表ful 1 ou t jo in用的比较少8. 组函数#组函数：一堆数据返回的结果# #ma x ()#avg ()# #mi n ()#avg()# #求所有人的平均工资？#求所有人的平均提成？sei e c t a v g (nv 1 (commi s s i on_p c t,0) fr o m s_emp;#count()解决的结果假如全为空值，结

12、果返回0祥#计算有多少条记录s e le c t c o unt(id) f rom s_ e mp；#求按提成分组，计算人数？s ele c t c o mmi s s ion_ pct , c o un t ( i d)from s _empgrou p by c omm i ssion_pct;#c o unto函数中可以加入关键字sele etc ount(ti t 1 e) from s_emp;#等同于s elect c ount(all t itle) f r om s_emp;#把反复值去掉，再做记录s e lect c o unt( d is t inct t itl e )

13、 f r om s_ e mp；#列出42号部门的平均工资# #若有g roupb y子句,se 1 e ct后面可跟grou p by后面跟的表 达式以及组函数,其他会报错。关于 group b y 选择 h av i n g 还是 w hereWhere在分组之前过滤写在group之前效率高Having写在gro u pby之后在分组之后做过滤；se 1 ect d ept_i d , avg ( s al a ry)f rom s_empw h ere dept_id=42g roup by dep t _id;# #若没有group b y子句，select后面有一个组函数，其他都必

14、须是组函数s elec t max(dept_id), a v g (sala r y)from s_em p where de p t_i d =42;作业：ins e rt into s a 1 g r a de va 1 ues (6,1000 0 ,15000);#列出每个工资级别有多少员工？# #列出3,5级有多少员工#列出每个工资级别有多少员工(若该级别没有员工，也要列出)D a y 0 311.子查询# #先执行子查询;子查询只执行一遍# #若子查询返回值为多个，Oracle会去掉反复值之后，将结果返回 主查询#谁是受老板剥削工资最低的人？select f i rs t _ n

15、am e , sal a ryfrom s_empwhe r e s a la r y = (se 1 ect mi n (salary) f r om s_ e m p );#谁跟S M I TH的职位是同样的？sel e c t 1 as t _name ， ti t 1 efrom s_em p w h e retitl e = (sele c t t i tie fr o m s e m p where last_ n ame=f S mith1) and 1 a st name != S mi t h;#假如表中有反复值，如两个Smith,会报错：# # single- row su

16、bquery re t u r ns more t h an one row 单行子查询返回多行# #修改为：in可以不？ ? ? ? ?可以se 1 e c t 1 a st_n a me , ti t lefrom s_emp wh e r etitle = any (select t i t 1 e f r om s_emp w h er e las t _name Smi t h)an d last_ n am e != Smith；# #哪些部门的平均工资比3 2部门的工资高？select dept_id , a v g (salary)fro m s_em pgroup b y d

17、eptidhavi n g av g (sala r y ) (s e 1 e ct a v g(sal a ry) from s _em p w h ere dept_ i d = 32);#那些人是领导？子查询select fi r st_n a mef r om s_empwhere id in (sele c t manager_id from s_emp)；#那些人是领导？表连接s e 1 e ct di s t i n c t m. fir st namef r om s_ e m p mjo i n s_em p eon e.man ager_id = m. id;# Ben的领

18、导是谁?子查询s el e ct fi r st_n a m efr o m s empw here id 二(s e lect man a ger_i d from s emp where firs t name=Ben)；#Ben领导谁?子查询select firs t _ n am ef r om s_em pw her e m a n a g er_ i d =(s e lect i d f rom s_ e mp w he r e first_name=7 Ben*);#Ben的领导是谁？表连接s e 1 ec t m.f i r s t_namef r om s_emp mJ o

19、in s _emp eon e.firs t _name =B en and e.m a na g er_id = m.i d ;#B e n领导谁？表连接sel e ct e . f irst_namefrom s_ e mp ejoin s_emp mon m.f i rst name=,B e nz and e .manag e r_id = m.i d ;#select f irs t n ame , salar y *12*(1+nvl( c omm i s sio n _ p ct,O) / 1 00) fr o m s emp;5 . SQL PLUS 命令：a) L列出上一次敲

20、入的命令b) c 1 ear scr 或者!clear清屏6 .字段(列名)拼接。II字符串拼接Or a cle中字符和字符串用单引号表达” 双引号用于表达别名sele c t firs t _name|T f I |1 a st name e mployee from s_emp;s e le c t f irst_name|r is i nt d epa r tme n t f I I dept_id| I V f r o m s_emp ；.去除反复值 di s tinct#该公司有哪些职位？s e lect di s tin c t t i tie from s_ e mp;#各个部门

21、有哪些不同的职位?。#di s t i net的功能:部门号单独反复，职位单独反复，部门号和职 位联合不反复s e 1 ect d i s ti n c t dept_id, tit 1 e f r om s e mp;会报错，由于dis t i n c t只能出现在selec t后面，否则会导致逻辑不 通s e lect f i rst name from s_empw h ere id in (se 1 e c t manag e r id f roms _em p );#演示代码 #对n。t in来说，结果集中假如有n u 则整个结果集为n#结论:对not in来说，子查询结果集中是不能

22、有null的selec t f i r st_namefrom s_empwhere id n o t in ( s e 1 ect m a na g e r_ i d f r o m s_em p );#查询那些人是员工?se 1 ect f i r s t namefrom s_empwhere id n ot i n (select man a g e r _id from s_emp wh e r e manager_i d i s n ot nul 1 )；#n o t i n尽量不用12 .子查询与空值#哪些部门的员工工资等于本部门员工平均工资？#多列sele c t f irst

23、 name , dept_id , s a 1 a ryfr o m s _em pwhe r e ( d epti d , s alary) i n (s e lec t de p t_i d , a v g(sa 1 ary) from s e mp g roup b ydept_ i d);13 .关联子查询.同表中一列相等一列比大小用关联子查询#哪些员工的工资比本部门的平均工资高？select f irs t _name , d ep t _id , s a 1 a ryfrom s emp ou t e rw here sa 1 a r y (select avg( s a 1 a

24、r y) from s_ e mp inn e rwh e re o u te r . dept_id = inner. dept_ i d )；14 .常用的关联子查询:EXISTSNOT EX I STS#找到即返回#哪些部门有员工？select dname fromdeptowhere e x is t s(se 1 e ct 1 from e mp iw h ere o. d ept n o = i .dept no)；#那些人是员工？sei e ct fir s t_ n ame f r om s _emp aw h ere n ot exist s(se 1 ec t 1 from

25、 s _em p b w h er e a. i d =b.m ana ger_ id)；总结: 子查询：非关联。i n / n o t in（不建议）关联。exist s （比 inner j oin 优势）/ n ot e xi s t （即 outer j oin + is n u 1 1 ）IN和E XIS TS的比较（非关联和关联子查询的比较）17. 标量子查询#列出员工名和领导名？s e 1 ect f i rst_n a me em p 1 o ye e ,（s e 1 e ct f i r st_n a me from s e mp i wh e r e o .ma n age

26、r_id = i. i d） Man a g e r from s_emp o ；CASE WHEN 表达式#实现31部门,3 2部门工资分别涨1.1倍和1. 2倍?#假如没有else返回空值select f i rst_ name, salar y ,case wh e n dep t id = 31 th e n salary * 1 . 1wh e n dep t _id = 3 2 t h en salar y * 1 . 2e Is es a 1 a ryen d。a 1 a_s a 1 f rom s _ e m p ;#工资100 0涨30 0块，1 0 00工资V1500涨5

27、0 0,其别人不动 select f irst_nam e , sala r y,c a se ewhen sal a ry100 0 and sal a ry100 0 );p rima r y key约束(主键约束)第一种形式：cr e at e t a bl e test( c numb erpr ima r y k ey );第二种形式：c r eate table te s t(c n u mber , prima r y key(c);表级约束 create t a ble te s t( c 1 number cons train t s pk c 1 p rimary key

28、); 此约束有名字：p k clfor egin key ( f k)外键约束:(先定义父表，再定义子表) carete ta b 1 eparent(cl n um b er p rim ary key );c r ea t e table c h ild ( c n u mb e r pri m ar y k e y , c 2 num b er r e fe r e n c es pa r ent(cl);或表级约束定义：create t abl e child( c n u mber primary key , c2 num b er , f oreign key (c 2 ) ref

29、ere n ces paren t ( c 1);crea t e table tes t (cl n umbe r pri m a ry key)； 设立主键 crea t e t a ble test(c 1 n u mber c o nstraints tes t_cl primary key); 定义约束名,默认约束名为SYS_在列后面定义约束称为列级约束cr e a t e tabi e t e st(cl numb e r primary k e y( c 1 ); 所有列定义完 后再定义约束称为表级约束(能定义联合主键)cretae t able t e st(cl n u m

30、b er, c2 numbe r ,pri ary key( c 1 , c2)；定义联合主键c r e a te t able child(cl number primary key)；先要定义父表create table child(c 1 number p r imar y k e y , c2 numb e r reference spar e n t (cl)；然后定义子表r eferenc e s p a rent定义外键crea t e t a ble c h ild(cl numbe r p r imary key, c2 n u mbe r re f ere nces par

31、en t (cl) on d e lete cas c ate); on d e 1 ete c a scat e 为级 联删除c r eate tabi e child(cl num b er p r imar y k e y, c 2 number reference s par e n t ( c 1) o n d elete s e t nu 1 1); on d e 1 ete s e t nul 1 删除后 将外键置空c r e a te t a ble child ( c 1 n u mber prima r y k e y, c 2 n um b e r,for e ignk

32、e y(c2) refer e nces p a r ent (cl);20. 两表没有任何关联时会产生迪卡尔乘积：selec t fir s t_name , n a m e from s_em p , s_dep t ;i nse r t操作，插入记录(DM L操作) inse r t in t o student val u e(l/ x xx f xxx);in s ert i nto student(id,nam e ,address) v alue (1/ xxx?xxx);注意:有空值的话：隐式插入I NS ERT INTO s_dept (i d , n a me) V A L

33、UES (12, MIS7 );不往想为空的字段中插数据，系统默认为NULL显示插入INS ERT INTO s_de p t V A LUES。（13,Adminis t ration, NU L L）;s el e c t * from s_emp where 1=2;这样选不出纪录，方便察看表结构upda t e修改操作u p d a t e tab 1 e表名s et字段名1二数据1或表达式1,字段名2= 数据2或表达式2whereu pdate she n fen zhen s e t num=99 where sid=2；de lete删除操作d el e te f r om 表名

34、where. ;用dele t e操作删除的记录可以通过rollbac k命令回滚操作，会恢复d e lete操作删除的数据。d e lete操作不会释放表所占用的空间,de 1 e te不适合删除记录多的大 表。de lete操作会占用大量的系统资源。21. alter ta b 1 e 命令alter t a bl e 命令用于修改表的结构（这些命令不会经常用）：增长字段：alter table表名a d d （字段字，字段类型）删除字段：a 1 ter tbale表名dro p column 字段；（8 i以后才支持）给列改名：才支持a Iter table表名rename co 1 u

35、 m n 旧字段名to 新字段名；修改字段alter t able 表名 modify（字段，类型）（此时应注意的问题，更改时要看具体值情况之间的转达换,改为字符类型 时，必须要为空）not n u 11 约束是使用 a Iter table . .mo dify （.,not n u 11），来 加上的。增长约束：a 1 ter ta b 1 e 表名 add co n s t r ai n t 约束名约束（字段）；只可以增长表级约束。解除约束：（删除约束）a Ite r tabi e 表名 d r op 约束;（对于主键约束可以直接用此方法，由于一张表中只有一个主键约束名，注意假如主键此时

36、尚有其它表引用时删除主键时会犯错）alter table f ather drop p rimary key cascade;（假如有子表引用主键时，要用此语法来删除主键,这时子表还存在只是 子表中的外键约束被及联删除了）a Iter table 表名 drop con s trai n t 约束名;s elect dept_i d , di s t i net titl e from s_emp; (X)列出表中所有字段#注意:写*会减少效率，公司中一般会严禁写*;sel e c t * f rom s e mp;8. Ora c 1 e中写S QL大小写区别在功能上无影响性能上有影响#注意

37、：写SQL,一般公司都有规范9. w h er e控制子句#年薪大于L2w的员工的年薪？#假如salary字段上建了索引，第一种写法，索引用不上，所以慢selec t f i r s t_name,s a 1 a ry* 1 2 a_salfr o m s em pw h ere s alary* 12 12 0 2 3;。#假如sal a ry字段上建了索引,第二种写法，效率高些s ele c t f ir s t n a me, sala r y * 1 2 asalf r om s _ e mpwhere s a 1 a r y10 0 0 ;#会报错，w h er e子句后面不可以跟“

38、列别名，wh e re子句执行在select语句之前select f i rst_ n ame,sal ary* 12 a s a 1from s _empwh e re a s a 1 12 0 23;#EX.不会报错,order by子句可以使用“别名”（如何取一个约束名：a、人为的违反约束规定根据错误信息获取！b、查询视图获取约束名!）使约束失效或者生效a 1 t e r tabi e 表名 d i sabl e from primar y key;（相称于把一个表的 主键禁用）alter t a ble 表名 en a bl e p r imar y ke y ;（en a ble时会

39、自动去检查表的记录是不是符合规定，假如有脏数据时必须要先删除脏数据才可以enable）更改表名r e name旧表名to新表名；删除表：tru c ate table 表名；（表结构还在，数据所有删除，释放表所占的空间，不支持回退，常用删除大表）Day 0 5今天的note不全，请自己补充.哪些列更适合做索引#表大，结果集小经常出现在whe re子句的列经常用于表连接的列主键列PK、唯一键列UKc reat e table test(create u n iqu e ind e x test_ c l_idx on t e st( c 1);1 .新的建表语句cr e a t e ta b 1

40、 e t e st 01a ss e lec t * from test;2 . 一些概念建索引的目的就是为了加快查询速度。索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一 个表时，相相应的索引也会删除。索引是会进行排序。3 .查看表的row i d#每条记录都有自己的row i dselec t r o w i d,fi r s t n a m e fr o m s_em p ;.创建视图 c r eating v i ews 视图名；视图就相称于一条se 1 ec t 语句，定义了一个视图就是定义了一个sql 语句,视图不占空间，使用v iew不会提高性能，但是能简朴化s

41、ql语句（扩展知识:o r a c le 8i以后的新视图）MV 物化视图（占存储空间，把select结果存在一个空间，会提高查询 视图，增强实时性，但是存在刷新问题，物化视图中的数据存在延迟问题， 重要应用在数据仓库中用要用于聚合表）使用视图的好处:控制数据访问权限。如何创建一个视图的例子：cr e a t e or r eplace v ie w s tes t v i as se 1 e c t * f rom tes t 1 w h e r e cl = l;or r e p 1 a ce的意义，假如v iew存在就覆盖，不存在才创建。force| n o force ,基表存在是使用

42、，不存在是则创建该表。此时往表testl （base table基表）中插入数据时：表中没能变化，视图中 的数据发生改变从视图中插数据时相相应的表会发生改变：往视图中插数据时，会直接插进基表中，查看视图中的数据时，相称于就 是执行创建时的select语句。限制对数据库的访问，简化查询。简朴视图：来自于单表，且S elec t语句中不能涉及函数，能进行DML操作。复杂视图：来源于多张表，不能执行DML操作。4 .删除视图drop vie ws视图名.行列转置问题5 .子查询还可以跟在fro m后面se 1 e c t e. f i rs t _name ,e. s al a ry , a.a s a 1fr o m s _emp e join(s e lec t d e pt_id , avg (sal a ry) a sal fr o m s_emp g r oup by de p t_i d ) aone. d ept_id 二 a . d e p t _i dand e . sa 1 a r y a. a sal;. Ro w num#出现两条记录s ele c t first_name , rownum from s_emp where r o wnum = 2 ;#无记录，由于r o wnu m的特点是必须从1条记录开始找sel e ct fir