學習 復 caseexprwhenexpr1thenwhenexpr2then…else集行數表1的行數*表2的行數列數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…書寫規則，縮進，換行，加 表的管創建前提條件：表空間+權注意事長度過創建一增加一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行出現錯誤ORA-02290:檢查約束條件 SQL>insertintostudent(id,name,,sex,sal,deptno) ','男*第1行出現錯誤ORA-02290:檢查約束條件 insertintostudent(id,name,,sex,sal,deptno)values(1,'yekai',' SQL>insertintostudent(id,name,,sex,sal,deptno) ','男 ','男*第1行出現錯誤ORA-00001:唯一約束條件--成insertintostudent(id,name,,sex,sal,deptno) ','男SQL>insertintostudent(id,name,,sex,sal,deptno) ','男*第1行出現錯誤ORA-01400:無法將NULL插入SQL>insertintostudent(id,name,,sex,sal,deptno) insertintostudent(id,name,,sex,sal,deptno) 外鍵刪除方casecade級聯刪除，主表記錄刪除的時候，子表數據跟著刪setnull設置為null，主表刪除的時候，子表該字段設為默認方式，主表刪除的時候，如果子表了該字段的數據，不能刪除，先刪子表，后刪默認外鍵delete設置模數據庫對象：表、視圖、索引、序列、過程、函數、觸發器、包、包體、數據庫鏈路(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 而且數據量很大 刪除索引:dropindexmysql基礎 瑞典MySQLAB公司開發，由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) 創建Database需要查看mysql數據類createtablet1(idint,name增加一個字altertablet1addsalaltertablet1addcolumncomm修改字段屬altertablet1modifycolumnname改名同時改類altertablet1changecommcomm2刪除altertablet1dropcolumn查看表的創建方表名大小寫敏字段不區分大小庫名大小寫敏重命名表，需要加table關鍵renametablet1tomysql不支持回收站，直接刪除，不能加刪除droptablet2查看當前庫下有哪些mysql>show |Tables_in_mydb1 | mysql數據的 createtableemployee(idsexint,birthdaydate,salarydouble,resumetextinsert分區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的標準，內連接寫法，口訣：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