2023年Orcale职业培训笔记

DayOl.s_emp、sdept表的字段含义first_name^>名last_name。姓tit1e。。职位dept_id。。部门号commission_pct。提成(有空值).列出所有人的年薪selectfirstname,salary*12froms_emp;.给列起别名selectfirst_name,sa1ary*12Ann_Sa1fromsemp;selectfirst_name,salary*12"AnnSalnfroms_emp;selectfirst_name,salary*12as"AnnSalnfroms_emp；.解决空值的函数nvl(p1,p2)nullOracle当做无穷大来解决。空值不等于0。空值不等于空格算数表达式中为空值，返回空值##亚洲地区有哪些员工？selecte.first_name,froms_empejoins_deptdone.dept_id=d.idjoins_regionrond.region_id=廿Asia';——一-一一一一-为什么where能代替and4.非等值连接##列出员工的工资以及相应的工资级别？se1ecte.ename,e.sal,s.gradefromempejoinsalgradesone.sa1betweensJosalands.hisal;##SMITH的工资级别？selecte.ename，e.sa1,s.gradefromempejoinsaIgradesone.salbetweens.losa1ands.hisalande.ename='SMITH';##3,5级有哪些员工（哪些员工属于3,5级）？selecte.ename,e.sal,s.gradefromempejoinsa1gradesone.sa1betweens.1osalands.hisalands.gradein(3,5)；5.自连接#列出员工名和领导名的相应关系#结果为24个，少一个manager_id为空的人(BOSS丢了)selecte.id,e.firstnameemp1ayee,m.id,m.firstnamemanagerfroms_empejoins_empmone.manager_id=m.id;#列出哪些人是领导？selectdistinetm.firstnamefroms_empejoins_empmonm.id=e.managerid；6.outerjoin外连接#内连接fromtljoint2ontl.id=t2.id#fromtlleftouterjoint2ontl.id=t2.id。左边的表做驱动表#from11rightouterjoint2ontl.id=t2.icb右边的表做驱动表##外连接解决的问题:驱动表中的记录在结果集中“一个都不少”##列出员工名和领导名的相应关系?selecte.first_nameemployee,nv1(m.first__name/Boss')managerfroms_empe1eftouterjoins_empmone.manager_id=m.id;#如何写外连接：#先写出内连接,再拟定哪张表当驱动表就可以#哪个部门没有员工？14条记录，少1条se1ecte.ename,e.deptnofromempejoindeptdone.deptno=d.deptno；#哪个部门没有员工？15条记录selecte.ename,e.deptno,d.deptno,d.dnamefromemperightjoindeptdone.deptno=d.deptno;#哪个部门没有员工？15条记录selectd.deptno,d.dname,e.ename,e.deptnofromemperightjoindeptdd.deptnoone.deptno二wheree.empnoisnul1;d.deptno#使用外连接解决了两类问题：L把所有结果列出到结果集2.解决否认问题（不是，没有，不包含）##那些人是员工？（即：那些人不是领导?）##思绪：##先解决那些人是领导##能匹配的是领导##把匹配不上的挑出来##se1ecte.firstname,m.firstnamefroms_emperightjoins_empmone.manager_id=m.id;##加条件seiecte.first_name,m.first_namefroms_emperightjoins_empmone.manager_id=m.idwheree.idisnu11;——一----——为什么用and会出现结果错误????、/##最后列出m.firstname即可se1ectm.first_namefroms_emperightjoins_empmone.manager_id=m.idwheree.idisnu11；用and和where会得到不同的结果什么时候用and什么时候用whereAnd在外连接之前做过滤，where在外连接之后做过滤Where在得出结果以后过滤很重要别把##selecte.ename,d.dnamefromemperightjoindeptdone.deptno二d.deptnoande.ename='SMITHf;##驱动表的过滤所有写在where之后selecte.enameeename,d.dnamedenamefromemperightjoindeptdone.deptno=d.deptnoande.ename='SMITH';wheree.empnoisnull；##选择leftjon或者rightjoin不重要，重要的是选择哪张表做驱动表ful1outjoin用的比较少组函数#组函数：一堆数据返回的结果#max()#avg()#min()#avg()#求所有人的平均工资？##求所有人的平均提成？seiectavg(nv1(commission_pct,0))froms_emp;##count()解决的结果假如全为空值，结果返回0祥#计算有多少条记录selectcount(id)froms_emp；##求按提成分组，计算人数？selectcommission_pct,count(id)froms_empgroupbycommission_pct;##counto函数中可以加入关键字seleetcount(tit1e)froms_emp;##等同于selectcount(alltitle)froms_emp;##把反复值去掉，再做记录selectcount(distincttitle)froms_emp；#列出42号部门的平均工资#若有groupby子句,se1ect后面可跟groupby后面跟的表达式以及组函数,其他会报错。关于groupby选择having还是whereWhere在分组之前过滤写在group之前效率高Having写在groupby之后在分组之后做过滤；se1ectdept_id,avg(salary)froms_empwheredept_id=42groupbydept_id;#若没有groupby子句，select后面有一个组函数，其他都必须是组函数selectmax(dept_id),avg(salary)froms_empwheredept_id=42;作业：insertintosa1gradeva1ues(6,10000,15000);#列出每个工资级别有多少员工？#列出3,5级有多少员工#列出每个工资级别有多少员工(若该级别没有员工，也要列出)Day0311.子查询#先执行子查询;子查询只执行一遍#若子查询返回值为多个，Oracle会去掉反复值之后，将结果返回主查询#谁是受老板剥削工资最低的人？selectfirst_name,salaryfroms_empwheresalary=(se1ectmin(salary)froms_emp);#谁跟SMITH的职位是同样的？select1ast_name，tit1efroms_empwheretitle=(selecttitiefromsempwherelast_name=fSmith1)and1astname!=’Smith';##假如表中有反复值，如两个'Smith',会报错：#single-rowsubqueryreturnsmorethanonerow单行子查询返回多行#修改为：in可以不？？??????????可以se1ect1ast_name,titlefroms_empwheretitle=any(selecttit1efroms_empwherelast_nameSmith')andlast_name!=’Smith'；#哪些部门的平均工资比32部门的工资高？selectdept_id,avg(salary)froms_empgroupbydept—idhavingavg(salary)>(se1ectavg(salary)froms_empwheredept_id=32);#那些人是领导？子查询selectfirst_namefroms_empwhereidin(selectmanager_idfroms_emp)；##那些人是领导？表连接se1ectdistinctm.firstnamefroms_empmjoins_empeone.manager_id=m.id;##Ben的领导是谁?子查询selectfirst_namefromsempwhereid二(selectmanager_idfromsempwherefirstname='Ben')；##Ben领导谁?子查询selectfirst_namefroms_empwheremanager_id=(selectidfroms_empwherefirst_name=7Ben*);##Ben的领导是谁？表连接se1ectm.first_namefroms_empmJoins_empeone.first_name='Ben'ande.manager_id=m.id;##Ben领导谁？表连接selecte.first_namefroms_empejoins_empmonm.firstname=,Benzande.manager_id=m.id;##selectfirstname,salary*12*(1+nvl(commission_pct,O)/100)fromsemp;.SQLPLUS命令：L列出上一次敲入的命令c1earscr或者!clear清屏.字段(列名)拼接。II字符串拼接"Oracle中字符和字符串用单引号表达””双引号用于表达别名selectfirst_name|TfI|1astnameemployeefroms_emp;selectfirst_name|risintdepartmentfIIdept_id|IVfroms_emp；.去除反复值distinct##该公司有哪些职位？selectdistincttitiefroms_emp;##各个部门有哪些不同的职位?。##distinet的功能:部门号单独反复，职位单独反复，部门号和职位联合不反复se1ectdistinctdept_id,tit1efromsemp;会报错，由于distinct只能出现在select后面，否则会导致逻辑不通selectfirstnamefroms_empwhereidin(se1ectmanageridfroms_emp);##演示代码##对n。tin来说，结果集中假如有nu£则整个结果集为n##结论:对notin来说，子查询结果集中是不能有null的selectfirst_namefroms_empwhereidnotin(se1ectmanager_idfroms_emp);##查询那些人是员工?se1ectfirstnamefroms_empwhereidnotin(selectmanager_idfroms_empwheremanager_idisnotnul1)；##notin尽量不用.子查询与空值##哪些部门的员工工资等于本部门员工平均工资？##多列selectfirstname,dept_id,sa1aryfroms_empwhere(dept—id,salary)in(selectdept_id,avg(sa1ary)fromsempgroupbydept_id);.关联子查询.同表中一列相等一列比大小用关联子查询##哪些员工的工资比本部门的平均工资高？selectfirst_name,dept_id,sa1aryfromsempouterwheresa1ary>(selectavg(sa1ary)froms_empinnerwhereouter.dept_id=inner.dept_id)；.常用的关联子查询:EXISTSNOTEXISTS##找到即返回##哪些部门有员工？selectdnamefromdeptowhereexists(se1ect1fromempiwhereo.deptno=i.deptno)；##那些人是员工？seiectfirst_namefroms_empawherenotexists(se1ect1froms_empbwherea.id=b.manager_id)；总结:子查询：非关联。in/notin（不建议）关联。exists（比innerjoin优势）/notexist（即outerjoin+isnu11）IN和EXISTS的比较（非关联和关联子查询的比较）标量子查询##列出员工名和领导名？se1ectfirst_nameemp1oyee,（se1ectfirst_namefromsempiwhereo.manager_id=i.id）Managerfroms_empo；CASEWHEN表达式##实现31部门,32部门工资分别涨1.1倍和1.2倍?##假如没有else返回空值selectfirst_name,salary,casewhendeptid=31thensalary*1.1whendept_id=32thensalary*1.2eIsesa1aryend。a1a_sa1froms_emp;##工资<1000涨300块，1000〈工资V1500涨500,其别人不动selectfirst_name,salary,caseewhensalary<1000thensa1ary+300whensa1ary>1000andsalary<1500thensalary+500elsesa1aryendoa1a_salfroms_emp;DECODE函数（等同于Casewhen）##decode（参数1,参数2,参数3渗数11渗数12,参数13・・・）##表达假如参数1的值为参数2则参数3;参数11的值为参数12则参数13...se1ectfirst_name,salary,decode（dept_id,319salary*1.1,32,sa1ary*1.2,33,salary*1.3,salary)aftsa1froms_emp;se1ectfirstnamefroms_empouterwherenotexists(select1froms_empinnerwhereinner.id=outer.manager_id)；##列出所有员工selectfirstnamefroms_empouterwherenotexists(se1ect1fromsempinnerwhereouter.id=inner.manager_id);DayO4约束notnu11(非空约束)这是一个列级约束。在建表时，在数据类型的后面加上notnull，也就是在插入时不允许插入空值。createtablestudent(idnumberprimarykey,namevarchar2(32)notnull,addressvarchar2(32));primarykey(主键约束PK)保证记录的主键唯一且非空，并且每一个表中只能有一个主键。foreignkey(外建约束FK)被引用的表，叫做parenttab1e(父表)，引用方的表叫做childtable(子表)，要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，假如要修改父表的记录要保证没有被子表引用。要删表时,要先删子表，后删除父表。uniquekey(唯一键)，值为唯一的，假如创建一个uk,系统自动建一个唯一索引唯一约束，是会忽略空值的，唯一约束，规定插入的记录中的值是为一的。ereatetablestudent(idnumber,namevarchar2(32),addressvarchar2(32),primarykey(id),unique(address));check约束检查约束,可以按照指定条件，检查记录的插入。check中不能使用尾列，不能使用函数，不能引用其他字段。createtablesal(alnumber,check(al>1000));primarykey约束(主键约束)第一种形式：createtabletest(cnumberprimarykey);第二种形式：createtabletest(cnumber,primarykey(c));表级约束createtabletest(c1numberconstraintspkc1primarykey);此约束有名字：pkclforeginkey(fk)外键约束:(先定义父表，再定义子表)caretetab1eparent(clnumberprimarykey);createtablechild(cnumberprimarykey,c2numberreferencesparent(cl));或表级约束定义：createtablechild(cnumberprimarykey,c2number,foreignkey(c2)referencesparent(c1));createtabletest(clnumberprimarykey)；设立主键createtabletest(c1numberconstraintstest_clprimarykey);定义约束名,默认约束名为SYS_在列后面定义约束称为列级约束createtabietest(clnumberprimarykey(c1));所有列定义完后再定义约束称为表级约束(能定义联合主键)cretaetabletest(clnumber,c2number,priarykey(c1,c2))；定义联合主键createtablechild(clnumberprimarykey)；先要定义父表createtablechild(c1numberprimarykey,c2numberreferencesparent(cl))；然后定义子表referencesparent定义外键createtablechild(clnumberprimarykey,c2numberreferencesparent(cl)ondeletecascate);onde1etecascate为级联删除createtabiechild(clnumberprimarykey,c2numberreferencesparent(c1)ondeletesetnu11);onde1etesetnul1删除后将外键置空createtablechild(c1numberprimarykey,c2number,foreignkey(c2)referencesparent(cl));两表没有任何关联时会产生迪卡尔乘积：selectfirst_name,namefroms_emp,s_dept;insert操作，插入记录(DML操作)insertintostudentvalue(l/xxx\fxxx');insertintostudent(id,name,address)value(1/xxx?xxx');注意:有空值的话：隐式插入INSERTINTOs_dept(id,name)VALUES(12,'MIS7);不往想为空的字段中插数据，系统默认为NULL显示插入INSERTINTOs_deptVALUES。（13,'Administration',NULL）;select*froms_empwhere1=2;这样选不出纪录，方便察看表结构update修改操作updatetab1e表名set字段名1二数据1或表达式1,字段名2=数据2或表达式2[whereupdateshenfenzhensetnum=99wheresid=2；delete删除操作deletefrom表名[where..;用delete操作删除的记录可以通过rollback命令回滚操作，会恢复delete操作删除的数据。delete操作不会释放表所占用的空间,de1ete不适合删除记录多的大表。delete操作会占用大量的系统资源。altertab1e命令altertable命令用于修改表的结构（这些命令不会经常用）：增长字段：altertable表名add（字段字，字段类型）删除字段：a1tertbale表名dropcolumn字段；（8i以后才支持）给列改名：才支持aItertable表名renameco1umn旧字段名to新字段名；修改字段altertable表名modify（字段，类型）（此时应注意的问题，更改时要看具体值情况之间的转达换,改为字符类型时，必须要为空）notnu11约束是使用aItertable..modify（..,notnu11），来加上的。增长约束：a1tertab1e表名addconstraint［约束名］约束（字段）；只可以增长表级约束。解除约束：（删除约束）aItertabie表名drop约束;（对于主键约束可以直接用此方法，由于一张表中只有一个主键约束名，注意假如主键此时尚有其它表引用时删除主键时会犯错）altertablefatherdropprimarykeycascade;（假如有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）aItertable表名dropconstraint约束名;selectdept_id,distinettitlefroms_emp;(X)列出表中所有字段##注意:写*会减少效率，公司中一般会严禁写*;select*fromsemp;Orac1e中写SQL大小写区别在功能上无影响性能上有影响##注意：写SQL,一般公司都有规范where控制子句##年薪大于L2w的员工的年薪？##假如salary字段上建了索引，第一种写法，索引用不上，所以慢selectfirst_name,sa1ary*12a_salfromsempwheresalary*12>12023;。##假如salary字段上建了索引,第二种写法，效率高些selectfirstname,salary*12a—salfroms_empwheresa1ary>1000;##会报错，where子句后面不可以跟“列别名"，where子句执行在select语句之前selectfirst_name,salary*12asa1froms_empwhereasa1>12023;##EX.不会报错,orderby子句可以使用“别名”（如何取一个约束名：a、人为的违反约束规定根据错误信息获取！b、查询视图获取约束名!）使约束失效或者生效a1tertabie表名disablefromprimarykey;（相称于把一个表的主键禁用）altertable表名enableprimarykey;（enable时会自动去检查表的记录是不是符合规定，假如有脏数据时必须要先删除脏数据才可以enable）更改表名rename旧表名to新表名；删除表：trucatetable表名；（表结构还在，数据所有删除，释放表所占的空间，不支持回退，常用删除大表）Day05今天的note不全，请自己补充.哪些列更适合做索引##表大，结果集小经常出现在where子句的列经常用于表连接的列主键列PK、唯一键列UKcreatetabletest(createuniqueindextest_cl_idxontest(c1);.新的建表语句createtab1etest01asselect*fromtest;.一些概念建索引的目的就是为了加快查询速度。索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相相应的索引也会删除。索引是会进行排序。.查看表的rowid##每条记录都有自己的rowidselectrowid,firstnamefroms_emp;.创建视图creatingviews视图名；视图就相称于一条se1ect语句，定义了一个视图就是定义了一个sql语句,视图不占空间，使用view不会提高性能，但是能简朴化sql语句（扩展知识:oracle8i以后的新视图）MV物化视图（占存储空间，把select结果存在一个空间，会提高查询视图，增强实时性，但是存在刷新问题，物化视图中的数据存在延迟问题，重要应用在数据仓库中用要用于聚合表）使用视图的好处:控制数据访问权限。如何创建一个视图的例子：createorreplaceviewstestviasse1ect*fromtest1wherecl=l;orrep1ace的意义，假如view存在就覆盖，不存在才创建。force|noforce,基表存在是使用，不存在是则创建该表。此时往表testl（basetable基表）中插入数据时：表中没能变化，视图中的数据发生改变从视图中插数据时相相应的表会发生改变：往视图中插数据时，会直接插进基表中，查看视图中的数据时，相称于就是执行创建时的select语句。限制对数据库的访问，简化查询。简朴视图：来自于单表，且Select语句中不能涉及函数，能进行DML操作。复杂视图：来源于多张表，不能执行DML操作。.删除视图dropviews视图名.行列转置问题.子查询还可以跟在from后面se1ecte.first_name,e.salary,a.asa1froms_empejoin(selectdept_id,avg(salary)asalfroms_empgroupbydept_id)aone.dept_id二a.dept_idande.sa1ary>a.asal;.Rownum##出现两条记录selectfirst_name,rownumfroms_empwhererownum<=2;#无记录，由于rownum的特点是必须从1条记录开始找selectfirstname,rownumfroms_empwhererownumbetween4and9;##排名问题:找出前N条记录#找出工资最高的前10个人？se1ectrownum,firstname,sa1aryfrom(selectfirstname,salaryfroms_emporderbysalarydesc)whererownum<=10;#分页问题#找出第n条到第20条的记录(先找出前20条记录，再过滤掉前10条)#关键点：给rownum起别名#越往后走越慢，这种做法效率较低selectrn,first_name,salaryfrom(selectrownumrn,first_name,salaryfromsempwhererownum<=20)wherernbetween11and20;10.(集合运算)Union和UnionALL拼结果集##去重selectdeptnofromdeptunionselectdeptnofromemp;##不去重selectdeptnofromdeptuniona11selectdeptnofromemp;IL（集合运算）intersect去重后的交集selectdeptnofromdeptintersectselectdeptnofromemp;.（集合运算）minus。找出不包含的selectdeptnofromdeptminusse1ectdeptnofromemp;.Rename改名##改表名renameabctotestabc;##给列改名a1tertab1etestabcrenamecolumncltoc10；外延与复习.查询系统表##查询本用户下所拥有的所有表的表名selecttablenamefromusertables;.SQLPLUSSQLPLUS的buffer中会缓存最后一条sql语句.可以使用"/“来执行这最后一条命令.可以使用edit命令来编辑最后一条sq1语句.L命令（1ist）（sqlp1us命令）可以显示buffer中最后一条命令desc［表名］##sqlplus命令，注意他不是sq1语句，用于查看表的结构。descript的缩写descs_emp;.将SQL语句写入指定的文献spoola.1st.sq1脚本a）也就是在文献中写有sql语句的文献,可以在sqlplus中运营b）引入sql脚本.sqlplus用户名/密码@sq1脚本.注意：在用户名密码输入结束后一定要加空格然后再写@sql脚本.在脚本中最后一行写上“exit”，则运营完脚本以后，回到shell上.Oracle数据库中的空表dual测试表selectsysdatefromdual;.单行函数1）字符函数字符是大小写敏感的转小写lower（字段名）--其中的参数可以是一个字符串常量或是一个字段名转大写upper（字段名）首字母大写initcap（字段名）字符串拼接concat（字段1,字段2）截取子串substr（字段名，起始位置,取字符个数）dual表，是专门用于函数测试和运算的，他只有一条记录字符串拼接concat（......）求指定子串substr（…，起始位置，取字符个数）可以使用“一”表达从右向左取,取的时候可以从左往友取。

例:selectsubstr(first_name,-2,2)subfroms_emp；(取后两个)selectsubstr(first_name,2,2)subfroms_emp;(取前两个)2）数值函数四舍五入round（数据，保存小数点后几位）可以用负数表达小数点前,0,表达小数点后第一位，也就是保存个位，表达个位（保存到十位）。例：seiectround（15.36,1）fromdual;截取数字函数trimc（数据，保存的位数（小数点后位数））截取个位之后补0例：selecttrune（123.456,1）fromdua1;3）日期函数日期格式,全日期格式世纪信息,全日期格式世纪信息,全日期格式世纪信息,年月日，时分秒。缺省日期格式，日•月•年dd-mon—rr修改当前会话的日期格式，会按照指定的格式输出日期全日期格式世纪信息,年月日，时分秒。缺省日期格式，日•月•年dd-mon—rraltersessionsetnls_date_format='yyyymmddhh24：返回当前日期sysdate例：se1ectsysdatefromdua1;se1ectsysdate+1fromdual;获得明天的日期，加1,单位是天日期是格式敏感的求两个日期间相隔了多少个月months_between(datel,date2)加减指定数量的月份add_months(date,月数)，月数可认为负,负值就是减去相应的月数。从date日期开始的第一个星期五next_day(date,FriDay)返回月末的日期1ast_day(date)截取日期trunc(date,，年或月或日或时分秒，)例:selectnext_day(sysdate,2)fromdua1；例：se1ecttrunc(add_months(sysdate,1)，'month*)fromdual；ROUND('25-MAY-95'MONTH')0^1-JUN-95ROUND(125-MAY-95YEAR)001-JAN~95TRUNC('25-MAY-95'「MONTH')01-MAY-95TRUNCC25-MAY-957/YEAR)01-JAN-95练习：返回下个月的第一天的日期selectround(last_day(sysdate),'MONTH1)fromdual;selectadd_months(trunc(sysdate,'MONTH'),1);4）不同数据类型间转换函数将日期转成字符toehar（datej日期格式，）selectfirst_name,sa1ary*12asa1froms_emporderbyasa1;注意:单引号中大小写敏感##歹1」出Carmen的年薪是多少？Selectfirst_name,salary*12a—salFroms_empWherefirstname='Carmen';大小写转换函数“ower()upper()##列出Carmen的年薪是多少？selectfirstname,salary*12a_sa1froms_empwhere1ower(first_name)=fcarmen’;wheresalarybetween1000and1500;AND和betweenand连接符##找出员工工资在1000与1500之间selectfirst_name,sa1aryfroms_empwheresa1ary>=1000andsa1ary<=1500;##betweenand就表达了如上含义selectfirstname,salaryfroms_emp日期格式要用有效格式，格式大小写敏感'yyyymmddhh24:mi:ss\,year，（全拼的年）JmnT（数字表达的月）'month'（全拼的月）,，day'（星期的全拼）；ddspth'（日期的全拼），yymmdd'例：selectto_char（sysdate/yyyymmddhh24：mi：ssz）fromdual;将字符转换成数字to_number（f...,）将数字转字符to_char（numberjfmt，）fmt是数字格式将字符串转成日期to_date（，・・.':日期格式，）例:selectto_char（to_date（'20231103'Jyyyymmddf）,'dd-month・yy'）fromdua1；OR连接符IN()表述形式=ANY()#找出31、41、43部门员工的姓名和部门号？selectfirst_name,dept_idfroms_empwheredept_id=31ordept_id=41ordept_id=43；#简朴的表述形式in()selectfirst_name,dept—idfroms_empwheredept_idin(31,41,43)；#另一种表述形式in()相称于=any()se1ectfirstname,dept—idfromsempwheredept_id=any(31,41,43);##从连续区间中取值使用Between—And,从离散数值中取值用IN()LIKE运算符SUBSTR()函数Length()函数(通配符:%表达0或多个字符;_表达任意单个字符)##效率高些where1astnamelike'M%'##结果等同如上wheresubstr(last_name,1,1)=fMf;##列出名字的最后两个字母selectfirst_name,substr(first_name,