OCP学习笔记(1)

细节条件子句（where，having）不能使用别名。如：Selectcount(1)cntfromtmp1groupbysubsidhavingcnt2;错！Having子句可以使用别名Selectsubsid,servnumbernofromtmp1orderbyno;不等于的几种写法：,!=,^=Escape(转义)字符：\查询%或_（任意单个字符）匹配时用。select*fromtmp1whereservnumberlike'%\%'escape'\';优先级顺序，由高到低排序默认asc（升序）。记住，一般使用desc，所以反过来默认就是asc。排序时空值NULL视为最大值。即asc最后，desc最先函数分类：当行函数与多行函数（分组函数）。区别：当行函数处理单个行，返回一个结果多行函数处理成组的行，为每组行返回一个结果。Length()返回字符按照字符数的长度，lengthb()返回字符按照字节数的长度Oracle中的正则表达式ORACLE中的支持正则表达式的函数主要有下面四个：1，REGEXP_LIKE：与LIKE的功能相似2，REGEXP_INSTR：与INSTR的功能相似3，REGEXP_SUBSTR：与SUBSTR的功能相似4，REGEXP_REPLACE：与REPLACE的功能相似它们在用法上与OracleSQL函数LIKE、INSTR、SUBSTR和REPLACE用法相同，但是它们使用POSIX正则表达式代替了老的百分号（%）和通配符（_）字符。POSIX正则表达式由标准的元字符（metacharacters）所构成：'^'匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。'$'匹配输入字符串的结尾位置。如果设置了RegExp对象的Multiline属性，则$也匹配'\n'或'\r'。'.'匹配除换行符之外的任何单字符。'?'匹配前面的子表达式零次或一次。'+'匹配前面的子表达式一次或多次。'*'匹配前面的子表达式零次或多次。'|'指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。'()'标记一个子表达式的开始和结束位置。'[]'标记一个中括号表达式。'{m,n}'一个精确地出现次数范围，m=出现次数=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。\num匹配num，其中num是一个正整数。对所获取的匹配的引用。字符簇：[[:alpha:]]任何字母。[[:digit:]]任何数字。[[:alnum:]]任何字母和数字。[[:space:]]任何白字符。[[:upper:]]任何大写字母。[[:lower:]]任何小写字母。[[:punct:]]任何标点符号。[[:xdigit:]]任何16进制的数字，相当于[0-9a-fA-F]。各种操作符的运算优先级\转义符(),(?:),(?=),[]圆括号和方括号*,+,?,{n},{n,},{n,m}限定符^,$,anymetacharacter位置和顺序|*/--创建表createtablefzq(idvarchar(4),valuevarchar(10));--查询value中以1开头60结束的记录并且长度是7位select*fromfzqwherevaluelike'1____60';select*fromfzqwhereregexp_like(value,'1....60');--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。--使用like就不是很好实现了。select*fromfzqwhereregexp_like(value,'1[0-9]{4}60');--也可以这样实现，使用字符集。select*fromfzqwhereregexp_like(value,'1[[:digit:]]{4}60');--查询value中不是纯数字的记录select*fromfzqwherenotregexp_like(value,'^[[:digit:]]+$');--查询value中不包含任何数字的记录。select*fromfzqwhereregexp_like(value,'^[^[:digit:]]+$');--查询以12或者1b开头的记录.不区分大小写。select*fromfzqwhereregexp_like(value,'^1[2b]','i');--查询以12或者1b开头的记录.区分大小写。select*fromfzqwhereregexp_like(value,'^1[2B]');--查询数据中包含空白的记录。select*fromfzqwhereregexp_like(value,'[[:space:]]');--查询所有包含小写字母或者数字的记录。select*fromfzqwhereregexp_like(value,'^([a-z]+|[0-9]+)$');--查询任何包含标点符号的记录。select*fromfzqwhereregexp_like(value,'[[:punct:]]');/*理解它的语法就可以了。其它的函数用法类似。约束Oracle有五种类型的约束：NotnullUniquekey-----UPrimarykey-----PForeignkey-----RCheck----C（notnull属于check）V-----withcheckoptiononaviewCeratetabletmp1(deptnonumber,enamevarchar2(20));Ceratetabletmp2(deptnonumber,deptnamevarchar2(20));Altertabletmp2addconstraintfk1primarykey(deptno)enablevalidate;Altertabletmp1addconstraintpk1foreignkey(deptno)referencestmp2(deptno)enablenovalidate;Altertabletmp1disableconstraintfk1;Altertabletmp1addconstraintchkcheck(deptno0);altertabletaddconstraintpk_iprimarykey(i)altertabletmodifyinotnull;altertabletaddconstraintt_iunique(i)[(createindexind_nameont(i))];altertabletaddconstraintt_icheck(iin(1,2,3,4,5));altertabletdisablenovalidateconstraintialtertabletenablenovalidateconstraintcheck_ialtertabletdropconstrainti;altertabletdropprimarykeyi;约束状态：enablevalidate:是默认,新旧数据同时满足约束规则enablenovalidate:旧数据可以不满足,检新插入的数据要满足约束disablevalidate:不允许在表上执行任何DML操作,主要用在分区表,对于主键和唯一约事,会删除相应的唯一索引,但约束状态任可用disablenovalidate数据可不满足约束规则,对于主键和唯一约事,会删除相应的唯一索引,查询约束：Select*fromuser_constraints;建立约束时约束间不能加逗号：Createtabletmp1(idnumberuniquenotnullcheck(id10));约束与索引的关系Primarykey和uniquekey会创建同名索引。如果在相应字段上已经存在索引，则不会重新创建而是使用已有索引，但是不会修改索引的uniqueness类型。Disable|dropconstraint[keep|dropindex]如果索引先于约束创建，默认keepindex;如果索引时创建约束时创建，默认dropindex。Enable|addconstraint时重建。Uniqueindex的创建：createuniqueindexindex_nameontable_name(column);序列sequenceCreatesequenceseqstartwith10incrementby5maxvalue100cyclenocache;Selectseq.nextvalfromdual;达到最大值后下一值为1.关联子查询和嵌套子查询关联子查询与非关联子查询关联子查询需要在内部引用外部表，而非关联子查询不要引用外部表。对于父查询中处理的记录来说，一个关联子查询是每行计算一次，然而一个非关联子查询只会执行一次，而且结果集被保存在内存中（如果结果集比较小），或者放在一张oracle临时数据段中（如果结果集比较大）。一个“标量”子查询十一哥非关联子查询，返回唯一记录。如果子查询仅仅返回一个记录，那么oracle优化器会将结果缩减为一个常量，而且这个子查询只会执行一次。2.如何选择？根据外部查询，以及子查询本身所返回的记录的数目。如果两种查询返回的结果是相同的，哪一个效率更好？关联子查询的系统开销：对于返回到外层查询的记录来说，子查询回每次执行一次。因此，必须保证任何可能的时候子查询都要使用索引。非关联子查询的系统开销：子查询只会执行一次，而且结果集通常是排好序的，并保存在临时数据段中，其中每一个记录在返回时都会被父级查询引用，在子查询返回大量记录的情况下，将这些结果集排序回增大系统的开销。所以：如果父查询只返回较少的记录，那么再次执行子查询的开销不会非常大，如果返回很多数据行，那么直查询就会执行很多次。如果子查询返回较少的记录，那么为内存中保存父查询的结果集的系统开销不会非常大，如果子查询返回多行，那么需要将结果放在临时段上，然后对数据段排序，以便为负查询中的每个记录服务。查询权限的视图Select*fromuser_sys_privs;Select*fromuser_role_privs;Select*fromuser_tab_privs;Select*fromrole_tab_privs;Select*fromrole_sys_privs;多表插入--UnconditionalinsertintoALLtablesINSERTALLINTOsal_historyVALUES(empid,hiredate,sal)INTOmgr_historyVALUES(empid,mgr,sysdate)SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployeesWHEREemployee_id200;--Pivotinginserttosplitnon-relationaldataINSERTALLINTOSales_infoVALUES(employee_id,week_id,sales_MON)INTOSales_infoVALUES(employee_id,week_id,sales_TUE)INTOSales_infoVALUES(employee_id,week_id,sales_WED)INTOSales_infoVALUES(employee_id,week_id,sales_THUR)INTOSales_infoVALUES(employee_id,week_id,sales_FRI)SELECTEMPLOYEE_ID,week_id,sales_MON,sales_TUE,sales_WED,sales_THUR,sales_FRIFROMSales_source_data;--Conditionallyinserti