????/*
????CREATE TABLE SALES1
????(
????SALES_ID NUMBER??
????PRODUCT_ID VARCHAR2(5)??
????SALES_DATE DATE NOT NULL
????)
????PARTITION BY RANGE (SALES_DATE)
????(
????PARTITION P1 VALUES LESS THAN (to_date('2013-04-1'?? 'yyyy-mm-dd'))??
????PARTITION P2 VALUES LESS THAN (to_date('2013-07-1'?? 'yyyy-mm-dd'))??
????PARTITION P3 VALUES LESS THAN (to_date('2013-10-1'?? 'yyyy-mm-dd'))??
????PARTITION P4 VALUES LESS THAN (to_date('2014-01-1'?? 'yyyy-mm-dd'))??
????PARTITION P5 VALUES LESS THAN (maxvalue)
????);
????--?????????????????
????SELECT * FROM SALES1 partition(P3);
????--?????????????????
????DELETE FROM SALES1 partition(P3);*/
????--??????
????--????????????
????CREATE TABLE SALES2
????(
????SALES_ID NUMBER??
????PRODUCT_ID VARCHAR2(5)??
????SALES_DATE DATE NOT NULL
????)
????PARTITION BY RANGE(SALES_DATE)
????INTERVAL(NUMTOYMINTERVAL(3??'MONTH'))
????(PARTITION P1 VALUES LESS THAN (to_date('2013-04-1'??'yyyy/mm/dd')));
????--????????
????INSERT INTO sales2 VALUES (1??'a'??to_date('2013-08-1')??10??'1');
????--??÷??????
????SELECT table_name??partition_name
????FROM user_tab_partitions
????WHERE table_name=UPPER('sales2');
????--????????????????????????????????????·???“SYS_P82”
????TABLE_NAME PARTITION_NAME
????----------------------------
????SALES2 P1
????SALES2 SYS_P82
????--???????????
????SELECT * FROM sales2 PARTITION(sys_P82);
????/*
????===========================================================
????| ???ü?????????????????з???????????μ????????
????============================================================
????*/
????/*???????*/
????--1.???????SALES??
????CREATE TABLE SALES
????(
????SALES_ID NUMBER??
????PRODUCT_ID VARCHAR2(5)??
????SALES_DATE DATE NOT NULL
????);
????--?????????????
????/*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??λ???????????????????????????
????--???????Χ????
????/*
????CREATE TABLE SALES1
????(
????SALES_ID NUMBER??
????PRODUCT_ID VARCHAR2(5)??
????SALES_DATE DATE NOT NULL
????)
????PARTITION BY RANGE (SALES_DATE)
????(
????PARTITION P1 VALUES LESS THAN (to_date('2013-04-1'?? 'yyyy-mm-dd'))??
????PARTITION P2 VALUES LESS THAN (to_date('2013-07-1'?? 'yyyy-mm-dd'))??
????PARTITION P3 VALUES LESS THAN (to_date('2013-10-1'?? 'yyyy-mm-dd'))??
????PARTITION P4 VALUES LESS THAN (to_date('2014-01-1'?? 'yyyy-mm-dd'))??
????PARTITION P5 VALUES LESS THAN (maxvalue)
????);
????--?????????????????
????SELECT * FROM SALES1 partition(P3);
????--?????????????????
????DELETE FROM SALES1 partition(P3);*/
????--??????
????--????????????
????CREATE TABLE SALES2
????(
????SALES_ID NUMBER??
????PRODUCT_ID VARCHAR2(5)??
????SALES_DATE DATE NOT NULL
????)
????PARTITION BY RANGE(SALES_DATE)
????INTERVAL(NUMTOYMINTERVAL(3??'MONTH'))
????(PARTITION P1 VALUES LESS THAN (to_date('2013-04-1'??'yyyy/mm/dd')));
????--????????
????INSERT INTO sales2 VALUES (1??'a'??to_date('2013-08-1')??10??'1');
????--??÷??????
????SELECT table_name??partition_name
????FROM user_tab_partitions
????WHERE table_name=UPPER('sales2');
????--????????????????????????????????????·???“SYS_P82”
????TABLE_NAME PARTITION_NAME
????----------------------------
????SALES2 P1
????SALES2 SYS_P82
????--???????????
????SELECT * FROM sales2 PARTITION(sys_P82);
????/*
????===========================================================
????| ???ü?????????????????з???????????μ????????
????============================================================
????*/
????/*???????*/
????--1.???????SALES??
????CREATE TABLE SALES
????(
????SALES_ID NUMBER??
????PRODUCT_ID VARCHAR2(5)??
????SALES_DATE DATE NOT NULL
????);