讲义文稿说明

学习 复 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