講義文稿說明

學習 復 caseexprwhenexpr1thenwhenexpr2then…else集行數(shù)表1的行數(shù)*表2的行數(shù)列數(shù)N的連接條件至少是N-selectfromempe,deptdgroupbyselectfromempe,deptgroupbyd.deptno,d.dname;selecte.ename||'''sbossisfromempe,empbwheree.mgr=b.empno;selecte.ename||'''sbossis'||nvl(b.ename,'hiswife!')fromempe,empbwheree.mgr=groupby…errorderby…書寫規(guī)則，縮進，換行，加 表的管創(chuàng)建前提條件：表空間+權(quán)注意事長度過創(chuàng)建一增加一altertablet1 varchar2(30);修改列altertablet1 varchar2(40);重命名altertablet1renamecolumnto刪除altertablet1dropcolumnSQL>renamet1tot2;SQL>droptable表已刪oracle給提供了回收purge的作用刪回收站的表可以閃回（10g開始支持SQL>flashbacktablet2tobefore閃回完droptablet2purgerecyclebin;flashbacktableemp10tobeforecreatetablenewtableasselect*fromsrctabwhere列可以指定default值，如果該列不顯示插入，使表的約束（5種檢非唯主鍵（非空+唯一外外鍵關系圖：子表主表的主createtableidnumberconstraintpk_studentprimarykey,namevarchar2(30)notnull,varchar2(30)unique,sexvarchar2(10check(sexin男','女')),salnumbercheck(sal>10000),deptnonumber(2)referencesdept(deptno)ondeletesetaltertablestudentaddhiredatedatedefault檢查約SQL>insertintostudent(id,name,,sex,sal,deptno) *第1行出現(xiàn)錯誤ORA-02290:檢查約束條件 SQL>insertintostudent(id,name,,sex,sal,deptno) ','男*第1行出現(xiàn)錯誤ORA-02290:檢查約束條件 insertintostudent(id,name,,sex,sal,deptno)values(1,'yekai',' SQL>insertintostudent(id,name,,sex,sal,deptno) ','男 ','男*第1行出現(xiàn)錯誤ORA-00001:唯一約束條件--成insertintostudent(id,name,,sex,sal,deptno) ','男SQL>insertintostudent(id,name,,sex,sal,deptno) ','男*第1行出現(xiàn)錯誤ORA-01400:無法將NULL插入SQL>insertintostudent(id,name,,sex,sal,deptno) insertintostudent(id,name,,sex,sal,deptno) 外鍵刪除方casecade級聯(lián)刪除，主表記錄刪除的時候，子表數(shù)據(jù)跟著刪setnull設置為null，主表刪除的時候，子表該字段設為默認方式，主表刪除的時候，如果子表了該字段的數(shù)據(jù)，不能刪除，先刪子表，后刪默認外鍵delete設置模數(shù)據(jù)庫對象：表、視圖、索引、序列、過程、函數(shù)、觸發(fā)器、包、包體、數(shù)據(jù)庫鏈路(datalink)、快其他 SQL>createsequence序列的使用：給主表的主鍵字段使用，防止主鍵insertintodeptvalues(50+myseq.nextval,myseq.nextval||'-createorreplaceviewselectd.deptno,d.dname,count(e.empno)countfromempe,deptdgroupbyd.deptno,d.dnameorderby1;sqlplussys/sys@orcl100assysdbaSQL>grantcreateviewtoscott;SQL>grantcreatesynonymtoscott;SQL>select*fromv_count;DEPTNODNAME 10 20 30 40 dropviewSQL>grantselectonemployeestoselect*fromcreateindexmyindexoncreateindexmyindex2onWHERE 而且數(shù)據(jù)量很大 刪除索引:dropindexmysql基礎 瑞典MySQLAB公司開發(fā)，由SUN收購，而后SUN被甲骨文并購，目前屬于Oracle公司rpm-aq|greprpm-aq|grep#rm- #rm-rf/var/lib/mysql#rm-rf/var/share/mysql#rm-rf/usr/bin/mysql*rpm-ivhMySQL-client-advanced-5.6.20-1.el6.x86_64.rpmrpm-ivhMySQL-devel-advanced-5.6.20-1.el6.x86_64.rpmrpm-ivhMySQL-server-advanced-5.6.20-1.el6.x86_64.rpmmysql-uroot-setsudoapt-getinstallmysql-sudoapt-getinstallmysql-裝的時候初始化，在home找不到，ps-umysql可以查mysql庫的 mysql>show | | createdatabasemysql>showcreatedatabase |Database|Create |mydb1 |CREATEDATABASE`mydb1`/*!40100DEFAULTCHARACTERSETlatin1*/| createdatabasemydb2charactersetutf8;alterdatabasemydb1charactersetcreatedatabasemydb3charactersetutf8collatemysql>dropdatabaseQueryOK,5rowsaffected(0.31mysql>dropdatabaseQueryOK,5rowsaffected(0.22mysql>dropdatabaseQueryOK,2rowsaffected(0.05mysql>dropdatabasemydb3;QueryOK,0rowsaffected(0.00sec)mysql>dropdatabasemydb2;QueryOK,0rowsaffected(0.00sec) 創(chuàng)建Database需要查看mysql數(shù)據(jù)類createtablet1(idint,name增加一個字altertablet1addsalaltertablet1addcolumncomm修改字段屬altertablet1modifycolumnname改名同時改類altertablet1changecommcomm2刪除altertablet1dropcolumn查看表的創(chuàng)建方表名大小寫敏字段不區(qū)分大小庫名大小寫敏重命名表，需要加table關鍵renametablet1tomysql不支持回收站，直接刪除，不能加刪除droptablet2查看當前庫下有哪些mysql>show |Tables_in_mydb1 | mysql數(shù)據(jù)的 createtableemployee(idsexint,birthdaydate,salarydouble,resumetextinsert分區(qū)database21 mysql>select3+5*20from |3+5*20 103 mysql>selectnow()from | |2017-06-1916:47:57 mysql>selectnow()-1,now(),now()+1from |now()- | | |20170619164827|2017-06-1916:48:28|20170619164829 mysql>selectdate_add(now(),interval-1day),now(),date_add(now(),interval1day)from |date_add(now(),interval-1day)| |date_add(now(),interval1day) |2017-06-18 |2017-06-1916:51:13|2017-06-20 mysql>selectdate_add(now(),interval1year)from |date_add(now(),interval1year)++|2018-06-19|++加mysql>selectaddtime(now(),11),now()from |addtime(now(),11)| |2017-06-1916:52:56|2017-06-1916:52:45 mysql>selectaddtime(now(),'0:1:0'),now()from |addtime(now(),'0:1:0')| |2017-06-19 |2017-06-1916:53:38 selectconcat('o','mysql')frommysql>select o','mysql','haha','hehe')from |concat('o','mysql','haha','hehe') |omysql mysqlselect o中國from | o中國') 11 mysql>selectconv(10,10,2),conv(10,10,16)from |conv(10,10,2)|conv(10,10,16) | | mysqlselectdate_format('1982-11-17','%Y-%m-%d')fromdual;selectdate_format('1982-01-17','%Y-%c-%d')fromdual;selectstr_to_date('2013-6-0405:14:15','%Y-%c-%d%h:%i:%s')fromselectaddtime(str_to_date('2013-6-0405:14:15','%Y-%c-%d%h:%i:%s'),'0:1:0')fromftp文件 fromdeptd,empwheresql99的標準，內(nèi)連接寫法，口訣：1innerjoin2.where-->onfromdeptdinnerjoinempeonselecte.empno,e.ename,e.sal,s.gradefromempe,salgradeswheree.sal>=s.losalande.sal<=s.hisalselectd.deptno,d.dname,count(e.empno)fromempe,deptdwheree.deptno=d.deptnogroupbyd.deptno,d.dname；右外連接寫法口訣：1.>rightouterjoin2.whereonselectd.deptno,d.dname,count(e.empno)fromemperightouterjoindeptdone.deptno=d.deptno--左外連接寫法，口訣：1.>leftouterjoin2.whereonselectd.deptno,d.dname,count(e.empno)fromdeptdleftouterjoinempeone.deptno=d.deptnogroupbyxx'bossisselectconcat(e.ename,'''sbossis',b.ename)fromempe,empbwheree.mgr=selectconcat(e.enam