????oracle ???????е?????????????????? ?????
????????????:
?????????:?洢??????????Oracle????????????????? XE??ORCL??
????????:??Oracle?洢??????????????????????????*.dbf??
??????????????:????????????????????????????????????????????????洢?????
????ж??Oracle?????????:
????0:???????????????
????1:?ú?????(????????????ж?????ж?????360??????????)
????2:ж???????????????????????
????3:?????????????Oracle???????????????
??????????????:window+R(services.msc)
???????Oracle?????????:
????1.??????в??????????????ò???п??
????2.?ò????????????????????
????Oracle:8i/9i-->Oracle10g/11g-->Oracle12C
?????汾:
????????? (enterprise Edition)
????????? (Express Edition)
??????????:1521 ?? 8080 ??tomcat?????????????Oracle?????
????SSH:Struts+Spring+Hibernate
????SSI:SpringMVC+Spring+IBatis
???????ü?????:
????Oracle..Listener
????OracleService<SID>
??????????ü?????:????????listener???????
????Oracle?????????:
????1.?????:?洢????????????????????(?????XE???????orcl)
????2.???(?? schema):Oracle???????????????????????????
????3.??(???/?????)????:???????????????е??/???/????/????...
????4.????:??Oracle?洢????????????????????????????????????*.dbf
??????ε??Oracle?????
????1.pl/sql developer
????sqlplus:
?????????:sys(?????sysdba??sysoper???????) system(normal???)
???????/?л??????sql????:conn ???/???? [as sysdba];
????Oracle ??sql???
????1>?????????????:stuName??stuAge()
????select distinct stuName??stuAge from student;
????select stuName??stuAge from student group by stuName??stuAge having count(1)=1;
????2>?????????(??? ???? as)???????????????????
????select stuName as ??????stuAge as "?? ??" from student;
????order by ???? asc | desc;
????where
????3>??θ??????б????????±?
????create table ????
????as
????select * from student[where 1=2];
?????????±????????
????create table ????
????as
????select s.*??'T113' as cname from student s;
?????????sql????:
????1.show user:?????????????? ????????????????????
????2.conn ?????/????[as sysdba]:?л??????????????
????3.desc :???????
??????Oracle????????????????????????????????
????????:
????select * from ????;
?????????:
????desc ????;
????sql???????:4??
???????????????(DML):insert update delete select
???????????????(TCL):commit rollback savepoint
???????????????(DDL):create drop alter truncate
???????????????(DCL):grant revoke
??????editor?б??????????????:??±?????????check??????????д???tables??????????????
?????Ч??????:
????tools-->pre..-->key conf..-->reset to default-->ok
????Oracle?????????????????Щ?
????char varchar2 number date lob(blob clob)
?????????????:(1-6?? 2016) to_date(2016-6-1??'yyyy-MM-dd')date'2016-6-1' ???????? ???myobject??tables ??edit data
????????????:rank() over (partition by ???? order by ???? )
?????????????????3000?????
????/*select * from dept d
????where (select avg(sal) from employee e where e.deptno = d.deptno)>3000;*/
???????????????????:
????1.??????????????????????????????(???????棬???????)
????2.????????????????????????????
???????????С:
????1.????????????С
????alter database datafile
????'d:datad.dbf'
????resize 10M;
????2.??????????
????alter datafile
????'d:datad2.dbf'
????size 5M
????autoextend on;
????3.????????????
????alter tablespace hello read only;
????alter tablespace hello read write;
???????????
????drop tablespace hello
????including contents and datafiles;????????к??????????
???????????
????create user zhao
????identified by sa
????default tablespace hello;
????privilege ???
????expire:?????????
???????:
?????????:
????grant connect??resource to zhao;
????revoke connect??resource from zhao;
????grant create synonym to zhao;
????grant create public synonym to zhao;
????grant select on dept to public;
???????????:
????grant all on emp to zhao;
????revoke all on emp from zhao;
????????????:
????create sequence seq_dept
????start with 50
????increment by 10;
????1.???????
????insert into dept(????) values (seq_dept.nextval);
????2.??????????
????select seq_dept.currval from dual;
????3.drop sequence ??
????????????:??????????д???Scott??????;
????create or replace synonym emp for Scott.emp;
????????????????:????????????(?????????????????????????????????????)
????create public synonym emp for Scott;
????-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
?????????????????1000??????????2??????
????1. ???飺
????select * from dept
????where deptno in (select deptno from emp group by deptno having sum(sal) > 1000)
????2. ????
????select * from dept d
????where (select sum(sal) from emp e where e.deptno=d.detpno) > 1000;
????--?????????????
????/*truncate table dept;*/
????--???????
????/*select * from dept;*/
????--?????
????/*alter table dept add(looc varchar2(10));*/
????--?????
????/*alter table dept drop column looc;*/
????--???????
????/*insert into dept values('40'??'accp4'??'????4');*/
????--????????????????
????/*select distinct dname??loc from dept;*/
????--????????
????/*select * from dept order by loc ASC;*/
????--????????
????/*select * from dept order by loc DESC;*/
????--????????
????/*select depton as '???'?? dname as '?γ?'?? loc '????'?? FROM dept;*/
????--?????????????????????????????
????/*create table dept2
????as
????select * from dept;*/
????----??????????????????????????????i????
????/*create table dept3
????as
????select * from dept where 1=2;*/
????--?????е????
????/*select count(1) from dept;*/
????--?????е????(Ч???)
????/*select count(*) from dept;*/
????--????в??????????
????/*select dname??loc
????from dept
????group by dname??loc
????having(count(dname||loc)<2);*/
????--??????????
????--commit ??????
????--rollback ???????
????--savepoint ???????д??????
????-----------------------------
????/*commit;
????insert into dept2 values('01'??'accp01'??'????01');
????/*savepoint a;
????insert into dept values('4000'??'accp400'??'????400');
????rollback to savepoint a;
????select * from dept;
????;
????select * from dept;**/
????------------------------------
????--??????
????/*SELECT *
????FROM (SELECT e.*??rownum rn
????FROM (SELECT *
????FROM employee
????ORDER BY sal DESC
????) e
????)
????WHERE rn>=5 AND rn<=9;*/
????-------------------------
????--SQL??????
????--1.???????????? +-*/??????
????--2.???????? = <= >= AND IN LIKE
????--3.???????? or and not ??????????
????--4.????????? nuion(????) unionall(????????) intersect(????) minus(????)
????--union ?????????????????????????
????/*select * from dept
????union
????select * from dept2;*/
????--union??????????
????/*select loc from dept
????union
????select loc from dept2
????order by loc;*/
????--union all ??????е?????
????/*select * from dept
????union all
????select * from dept2;
????*/
????--intersect ???? ???????????е???
????/*select * from dept
????intersect
????select * from dept2;*/
????--minus ??????????????????????е???
????/*select * from dept
????miuns
????select * from dept2;*/
????--?????????
????--?????????||??????????????????????????????????????????????????????????????
????/*select dname||'_'||loc from dept;*/
????--????
????/*alter table dept
????add constraint depton PRIMARY KEY (depton);*/
????--????
????--sql????
????--to_char?????????varchar2????
????/*select to_char(sysdate??'YYYY"??"fmMM"??"fmDD"??"HH24:MI:ss')from dual;*/
????--???????????????????????????
????/*select to_char(1210.7??'$9??999.00') from dual;*/
????/*select to_char(sysdate??'YYYY"??"fmMM"??"fmDD"??"hh:mi:ss') from dual;*/
????/*select to_char(1200.2??'$9??999.00') from dual;*/
????/*select to_char(sysdate??'YYYY"??"fmMM"??"fmDD"??"HH24:MI:SS') from dual;
????select to_char(123.1??'$9??999.00') from dual;*/
????--??char varchar2???????????
????/*select to_date('2017-01-01'??'yyyy-mm-dd') from dual;*/
????--nvl ???÷????money2???null ???????????????null???nvl????????money2???null????????0
????--select stuname??money+nvl(money2??0) from stuinfo;
????--?ж?money2???money2????null????money+money2???????null????money.
????/*select stuname??nvl2(money2??money+money2??money) from stuinfo;*/
????--???date??java?е?switch????????????01????? ???02???? else??????
????/*select decode(to_char(day??'fmmm')??'01'??'???'??'02'??'????'??'?????') from stuinfo;*/
????--????????
????/*insert into emp values(1111??'?????'??'????'??2??to_date('1991-01-02'??'yyyy/mm/dd')??1000??1000??12);*/
????-----------------------------------------------------
????--????????
????--rank
????/*select empno??ename??sal??DENSE_rank() over(order by sal desc) as ???? from emp;*/
????/*select empno??ename??sal??ROW_NUMBER() over(order by sal desc) from emp;*/
????---------------------------------------------------------------------------
????--SQL??????
????--????????????
????--???round?????????????
????/*select empno??ename??hiredate?? to_char(round(hiredate??'mm')??'YYYY')||'???' from emp;*/
????--?г????3??????????having ????group by ???????
????/*select deptno??count(1)from emp
????group by deptno
????having count(1)>3;
????*/
????--?????????????
????/*select ename?? hiredate??last_day(hiredate) from emp where hiredate=last_day(hiredate);*/
????--??????????
????/*create tablespace EPET
????datafile'D:orcaleappOracleoradataXEEPET.DBF'
????size 5m
????autoextend on;*/
????--???????????????????????
????/*drop tablespace EPET;*/
????--????????????????????
????/*drop tablespace EPET including contents and datafiles;*/
????--????????С ?????????????С
????/*alter database
????datafile 'D:orcaleapporacleoradataXEEPET.DBF'
????resize 10m;*/
????--???????
????/*alter tablespace EPET read only;*/
????--????????д
????/*alter tablespace EPET read write;*/
????--???????
????/*create user li
????identified by yapeng
????default tablespace epet;--??????*/
????--??????
????/*drop user li cascade;*/
????--??????
????/*grant resource to li;*/
????--???????
????/*revoke resource from li;*/
????--??????
????/*grant select on emp to li;*/
????--????
????/*create table toys
????(
????toyid number not null??
????toyname varchar2(10)??
????toydate date
????);*/
????/*create sequence se
????start with 0
????increment by 1
????maxvalue 2000
????nocycle
????cache 30; */
????/* insert into toys (toyid??toyname??toydate)
????values(se.nextval??'????'??to_date('2012/01/01'??'YYYY??MM??dd')); */
????--????????е??
????/*select se.currval from toys;
????*/
????--????????
????/*--dba???貼?????????
????grant create synonym to T111;*/
????--????????
????/*create or replace synonym emp for system.emp;*/
????--dba???貼?????????????
????/*grant create public synonym to system;*/
????--????????????
????/*create or replace public synonym d for emp;*/
????--??????????????
????/*grant select on emp to public;*/
????--?????????Щ????
????/*select * from user_synonyms;*/
????--????
????--1??B?????? ??Oracle?????????????????????????????
????--2: ?????????????????IO????????
????--3??λ???????????????????????????
????--???????Χ????