oracle数据库基础入门教程笔记

商业互联网开发运维1:命令行sql>conn用户名/[as命令行:sqlplus"用户名/[as普通用户scott来做练习:1：加：Alteruserscottidentifiedby2：:alteruserscottaccountunlock;Conn查看自己的:showuser;Sqlplus的一些命令:Helpindex@@@要查具体令的用法Help命令，如helpedit注意：sqlplus中的这些命令不要和sqlDual表：dual是一张虚拟的表，可以帮助我们拼凑sql及内容Sqlplus的一个重要功能是缓冲sql语句，可以进行编辑，还可以与外部文本Sqlplus命令之list作用：显示缓冲区里的sqllist作用:Del作用：删除缓冲的sql比如del22Del24删除缓冲区的第2到4行注意listN后,第N行理解为当前行再del*5,5Input往缓冲增加内容L11Inputxxxxx1行后追回xxxxxCLappend典型用法：lAppendxxxx把xxxxEdGet路径/作用：是把外部文件的内容读到缓冲区，但是不执行@路径/文件 Start路径/Run路径/作用：外部文件内容，显示出来，并执Save路径/文件名[create/append/re作用:在sqlplus场景：有时我们需要反复执行一个系列的sql语句，这些sql语句只有参数不Select&numfrom请输入num的值我们可以反复执行该语句，并输入num语法：define变量名=变量值用法select&注销变量Undefined也可以在外部文件中用&1&2...&n当@/path/to/外部文件时,可以按顺序写数字参数,并会顺序赋&1 建表时字段的选择Varchar2(n)变长类型,4000Char(n)2000个字符Varchar2(n)char(n)2者最多能存n不同在于,char(n)1个字符，仍占据nVarchar(n)11+[1-2]开发优化实例：在一些大的上，用户名一般允许6-16字101000字,用char太浪费了，怎么办?====>把intro字段拿出来,2NumberNumber(m,n)m:是总位数(精度)，是小数点后面的位数（标度）Number(5,2),999.99Date日期类型:年月日时间戳是指建表语法建表就是一个列的过程典型语法Createtable（列名称列类型,列名名列类型查看表结构Desc修改表删除列altertable表名dropcolumn列名例:altertableemployeedropintro;增加列:altertable表名add（列名列类型altertablemodify（）改表名：altertable表名renameto新表名快速一Createtable表名asselect*/字段from？想表结构,不想要数据例：createtableempcopy2asselect*fromemployerwhere删除表：droptableInsertintovalues(字段1，字段2…)Insertintoempoyer如果是date类型，请在值前加date例：insertinto表名(12,(12Oracle语句上不支持一次插入多个行,可以通过如下语句，变通的来实现:Insertinto表名Selectxxx,yyy,zzzfromdualSelectxxx,yyy,zzzfrom…;删除数据:语法：deletefrom列名where不加where条件，全部数据被删和truncatetableTruncate是删表重做表,deleteTruncate更快些。修改数据updateUpdate1=…列N=值NWhere表达式数据的查正确的查询模型1:2:where后面是表达式，表达式为真，则该行取出createtablegoods(gidnumber,gnamevarchar2(20),pricenumber,mpricenumberinsertintogoodsvalues1,'诺基亚insertintogoodsvalues(2,'insertintogoodsvalues3,'拖鞋',14,19);insertintogoodsvalues(4,'尿布',8,12);例：SQL>select*fromGID148SQL>select*fromgoodswhereGID 1诺基 SQL>selectgid,gname,mprice-pricefromGID MPRICE-123544SQL>selectgid,gname,mprice-priceasdiscountfromGID 123544在这个例子中，price,mprice2个变量，进行运算.在数据库理论中,称为“集合的投影”做搜索时，为什么有时初始条件是whreWhereaWhereaandbWhereaandbandWhere1>0andaandband观察以上语句，1>0--sql的效率，因为sql50Select*fromgoodswhereprice<取出814SQL>selectgid,gname,pricefromgoodswhereprice=8orGID 拖 尿 用inInSQL>selectgid,gname,pricefromgoodswherepriceinGID 拖 尿 Betweenminand 10元->50SQL>selectgid,gname,pricefromgoodswherepricebetween10andGID 3拖 逻辑and[10,50]模糊查询Select*fromemployerwhereename=’王%_1selecteid,ename,introfromemployerwhereenamelike王约束Notnull非空约束,SQL>createtablestudent2sid3snamevarchar2(20)not4*NOTnull例snamevarchar2(20default‘‘notPrimarykey主键约束主键：不能重复createtablestudentsidnumberprimarysnamevarchar2(20)default''notinsertintostudentvalues(1,'zhsan');insertintostudentvalues(1,'lisi');第1行出现错误ORA-00001:唯一约束条件 Unique约束,createtablestudent(sidnumberprimarykey,snochar(10)unique,snamevarchar2(20)notinsertintostudentvalues(1,'oracle001','zhasan');insertintostudentvalues(2,'oracle001','lisi');SQL>insertintostudentvalues(2,'oracle001','lisi');insertintostudentvalues(2,'oracle001','lisi')*第1行出现错误ORA-00001:唯一约束条件 createtablestudent(sidnumberprimarykey,snochar(10)unique,snamevarchar2(20)notgenderchar(2check(genderin男’,’女Insertintostudentvalues(1,’oracle001’,’lisi’,’男’);Insertintostudentvalues2,’oracle002’,’zhsan’,’女’);Insertintostudentvalues(3,’oracle003’,’zhao’,’妖’);SQL>Insertintostudentvalues3,'oracle003','zhao','妖Insertintostudentvalues3,'oracle003','zhao','妖*第1行出现错误ORA-02290:检查约束条件 *注意：上面的sys_c createtablestudent(sidnumberconstraintpk_sidprimarykey,snochar(10)constraintuk_snounique,snamevarchar2(20)constraintnn_snamenotgenderchar(2constraintgen_chkcheck(genderin('男','女createtablestudent(sidnumber,snosnamevarchar2(20)constraintnn_snamenotnull,genderchar(2),constraintpk_sidprimarykey(sid),constraintuk_snounique(sno),constraintgen_chkcheck(genderin('男','女注意：notnull非空约束只能写在列上，他不是一个表级约束，而是一个列级1:如果有大量数据要插入,1000万条数据批量插入，2:表之间存在外键约束时，比员工表，部门表，员工的部门id--->部门表语法：altertable表名add(Constraint约束名约束类型（列)altertablestudentadd(constraintuk_snounique(sno),constraintgen_chkcheck(genderin男','女Altertable表名dropconstraintcreatetableclass(clanumberclassnamechar(10)insertintoclassvalues(1,'班');insertintoclassvalues2,'西方不红班');createtablestudent(snonumber,snameclaconstraintfk_claforeignkey(cla)referencesclass(cla*注意：现在是不行的，因为被的cla应该是主键或者唯一--如下修改altertableclassadd(constraintpkprimarykey(cla)再次建studentSelectwhere,group,having,orderby3:如果对中间结果再次筛选，需要用where-having-group |name|subject|score ||数 90||语 50||地 40||语 55||政 45 |政 30 22createtablescore(namevarchar2(10),subjectvarchar2(10),scorenumberinsertintoscorevalues('','数学',90);insertintoscorevalues('','语文',50);insertintoscorevalues('','地理',40);insertintoscorevalues('','语文',55);insertintoscorevalues('','政治',45);insertintoscorevalues('','政治selectname,count(*)fromscorewherescore<60groupbynamehavingcount(*)>=2;2:再根据名求这些同学的平均分,selectavg(score),namefromscorewherenamein(selectnamefromscorewherescore<60groupbynamehavingcount(*)>=2)groupbyname;再想办法筛选出挂科2门以上的行。Casewhen条件then结果else结果SQL>selectname,score,casewhenscore<60then1else0endfrom SCORE011111selectname,avg(score),sum(casewhenscore<60then1else0end)fromscoregroupby--selectname,avg(score),sum(casewhenscore<60then1else0fromscoregroupbynamehavingsum(casewhenscore<60then1else0unionselect1,2…列Nfromxxxxselect1,2Nfromselectempno,ename,deptnofromempwheredeptno=10selectempno,ename,deptnofromempwhereEMPNO749975217654769877827839784479007934注意：union1如果想强所有行，可以unionA | | | 5| 10| 15| 10 B | | | 5| 15| 20| 99 | |sum(num) | 5| 15| 30| 30| 99 selectid,sum(num)(select*fromaunionallselect*fromb)groupbyid;orderby语句：[groupbyxxx]orderby字段名asc/desc默认是多字段排序orderby1asc/desc],selectempno,ename,deptno,salfromemporderbydeptno,sal3高,13selectempno,ename,sal,rownum(selectempno,ename,salfromemporderbysaldesc)whererownum<=3;利用Rank()over(partitionby列orderby列Partition理解为group,partition指定的列分组并排序比较。selectempno,ename,deptno,sal,rank()over(partitionbydeptnoorderbysaldesc)rankfromemporderbydeptno,sal作用同上：只是连续，不跳跃selectempno,ename,deptno,sal,dense_rank()over(partitionbydeptnoorderbysaldesc)rankfromemporderbydeptno,salselectempno,ename,deptno,sal,row_number()over(partitionbydeptnoorderbysaldesc)rankfromemporderbydeptno,sal总结：获取前NN1:from子查询，先排序，外层sqlrownum2:利用rank(),dense_rank(),计算，3:序列sequence可以在sql，pl/sql里调用。createsequence序列名[incrementbynstartwithn–maxvaluen|nomaxvalue–最大值minvaluen|nominvalue--最小值cycle|nocycle cachen|nocache --n2个伪列：nextval1个值和当前的值createsequenceseq3incrementby2startwith5maxvalueminvalue1cache3删除序列sequenceDropsequence序列名同义词比如xxxxtable->xt同义词。Create[publicsynonymfor对象名视图意义1：简化查询。createviewview_1selectempno,ename,sal,deptnofromempwheresal>(selectavg(sal)from注意：视图本身并没有存数据，只是一个select语句。视图的意义2：在列级别上进行精细的权限控制Createorreceview视图SelectWithreadonlyWithcheckoption如果select语句中只针对单表进行列的查询，且没有对列进行表达式运算或函复杂视图不能进行DML操作Dropview事务要么全部执行，要么全部取消（就像上面的银行例子）事务的开启：当操作dml语句时，就已经开启。显式提交：1：发生ddlDCL2事务可以设置保存点Rollback[to回滚点Where型的子查询：是指内层的sql的返回值作为包层sql的条件例子：查询的员工（员工编号最大selectempno,ename,deptno,salfromempwhereempno=(selectmax(empno)fromempEMPNO 7934 From型的子查询内层sql的结果充当临时表，供外层sql再次查询。见上午的union的案例Exists型子查询：内层sql的结果作为外层sql的条件。--即如果内层成立，则10 NEW20 30 40 select*fromdeptwherenotexists(select*fromempwhere理解方式：把dept中的行代入内层来，看内层能否成立）,如果成立，则取出Existswherein来互换。但是，一般exists效率要高一些，尤其是in(值过多时)Withas1Withsas（select1）Select*froms;--语句2此处，select12SWithc99sql（mysql没有createtableboy(bidchar(2),nameinsertintoboyvalues('a','');insertintoboyvalues('b','');insertintoboyvalues('c','');createtableg