oracle開發學習小結(待補充)

1.Oracle  PRELUDE(前奏)

    windows+Ràcmd：START LISTENER：lsnrctl  start  [listener  name]

                        START SERVICE：oradim  –starup  –sid  orcl

                        SYS LOGIN： sqlplus  /  as  sysdba;

         控制台à管理工具à服務àOracleOraDb11g_home2TNListenerà啟動

                                 OracleServiceORCLà啟動    

2.sys  LOGIN

window+Ràsqlplus: 

          FIRST  LOGIN : sqlplus(with  out  ‘conn’  or  ‘;’)

     Super  user：sys/ysjian  as  sysdba (  / as  sysdba )

                Nomal  user：scott/tigger

                Switch user  to  login：conn  sys/ysjian  as  sysdba;

3.The  Operations  following  of  super  user

       CRARTE USER：create  user  ysjian  indetified  by  ysjian //建立使用者ysjian/ysjian

       ALTER PASSWORD：alter  user  ysjian  identified  by  000 //更改秘密 

       ALTER STATE OF USER：alter  user  scott  account  unlock //給scott使用者解鎖

                            alter  user  scott  account  lock //給scott使用者加鎖

                            show  user; //顯示目前使用者，任何使用者都可以用

                            select * from  tab; //查詢目前使用者的所有的表

                            desc  tableName;//查詢表結構

                            select * from  v$nls_parameters; //查詢系統參數

                            drop user ysjian cascade; //強制删除，有資料也可以删除

                            disconn //斷開連接配接

         GRANT  RIGHT(授權):

         System right(系統權限)：

         EXPRESSION:  grant  權限名稱  to  使用者名

                      revoke  權限名稱  from  使用者名

grant create session,create table,create sequence  to ysjian

           grant  connect  to  ysjian  //使用者ysjian可以登入

           grant  resource  to  ysjian//使用者ysjian可以使用資料庫相關資源

           grant  unlimited  tablespace  to  ysjian //使用者ysjian具有不受限表空間                   grant  create  any  table  to public  // 所有使用者有建立表的權限

            //使用者ysjian  //可以傳遞此權限

           grant  alter  any  table  to  ysjian  with  admin  optio

            //使用者ysjian可以查詢scott使用者的emp表   

            grant  select  on  scott.emp  to  ysjian

          Object  right(對象權限)

           conn  scott/tigger; //使用者scott登入

           grant select on emp to ysjian; //将對emp的查詢權限給了ysjian

grant insert on emp to ysjian;

grant update on emp to ysjian;

grant delete on emp to ysjian;

grant all on emp to ysjian; //将對emp的所有權限給了ysjian

grant update(ename) on emp to ysjian; //将對emp中name字段更新

的權限授予ysjian

grant insert(id) on emp to ysjian;

revoke all on mytable from wangwu; //将ysjian對emp的所有權限撤銷

//使用者ysjian可以講對scotte  mp表的查詢功能傳遞下去

grant select on emp to ysjian with grant option;

4.DICTIONARY OF DATA

    Select * from  user_sys_privs; //查詢目前使用者擁有的系統權限

    Select * from  user_tab_privs;// 查詢目前使用者擁有的對象權限

    Select * from  user_col_privs;//查詢目前使用者控制到列的權限

5.BASIC  QUERY (基本查詢)

    conn  scott/tigger;  the  following  operations  is  done  by  scott

    //1.查詢scott所有的表

Select * from tab;

//2.查詢emp表所有的記錄

Select * from emp;

    //3.查詢emp表的empno，enamel，job，sal

Select empno,ename,job,sal from emp;

//4. 查詢emp表的empno，enamel，job，sal,并起别名

Select empno as 編号,ename as 姓名,job as 工作,sal as 薪水 from emp;

//5.查詢emp中的job，并去除重複項，用關鍵字distinct

Select distinct job from emp;

//6.查詢enpmop，ename，job，顯示效果：編号：7369,姓名：Smith

Select ‘編号:’||empno,’姓名:’||ename,’工作:’||job from emp;

//7.查詢員工的編号，姓名，工作和年薪，空值判斷nvl(comm,0)

Select empno,ename,job,(sal+nvl(comm,0))*12 from emp;;

//8.查詢工資大于1500的雇員

Select * from emp where sal>1500;

//9.查詢能得到獎金的所有雇員

Select * from emp where comm is not null and comm>0;

//10.查詢工資大于1500或可以得到獎金的所有雇員

Select * from emp where sal>1500 or (comm is not null and comm>0);

//11.查詢工資大于1500并且可以得到獎金的雇員

Select * from emp where sal>1500 and comm is not null and comm>0;

//12.查詢工資大于1500或者不可以領取獎金的雇員

Select * from emp where sal >1500 or comm is null or comm=0;

//13.查詢工資在1500到3000隻見所有的雇員

Select * from emp where sal between 1500 and 3000;

//14.查詢在1981年雇傭的員工

Select * from emp where hiredate lile ‘%81’;

Select * from emp where hiredate between ’01-1月-81 and ’01-1月-82’;

//15.查詢員工姓名中第二個字母為’M’的雇員

Select * from emp where ename like ‘_M%’;

//16.查詢工資帶8字的員工

Select * from emp where sal like ‘%8%’;

//17.查詢編号是7396,7399,7521,7799的雇員資訊

Select * from emp where empno in(7396,7399,7521,7799);

//18. 查詢編号不7396,7399,7521,7799的雇員資訊

Select * from emp where empno not in 7396,7399,7521,7799);

//19.查詢員工編号不為7396的雇員

Select * from emp where empno <> 7396;

//20.查詢員工，并按工資的升序排列(asc,預設)

Select * from emp order by sal asc;

//21.查詢員工，并按工資的降序排列(desc)

Select * from emp order by sal desc;

//union:将兩表記錄合并，去點重複項

Select distinct deptno from emp union select deptno from dept;

//union all:将兩表記錄合并，不去除重複項

Select distinct deptno from emp union all select deptno form dept;

//intersect:取兩個集合的交集

Select * from deptno from emp intersect select deptno from dept;

//minus:去掉交集

Select * from deptno from emp minus select deptno from dept;

6.ADVANCED  QUERY(進階查詢)

    //1.聚合函數 count,sum,mix,max,avg

    Select count(*) from emp;

    //2.内連接配接(inner join)(兩表資料的交集):查詢員工的姓名，工資和隸屬的部門名稱

    Select ename ,sal,dname from emp,dept where emp.deptno = dept.deptno;

    Select ename,sal,dname from emp inner join dept on emp.deptno=dept.deptno;

//3.左連接配接(left join)(左表為主，右表沒有填充null)

Select ename,sal,dname from emp left join dept on emp.deptno=dept.deptno;

Select ename,sal,dname from emp,dept where emp.deptno=dept.deptno(+);

    //4.右連接配接(right join)(右表為主，左表沒有填充null)

    Select ename,sal,dname from emp right join dept on emp.deptno=dept.deptno;

    Select ename,sal.dname form emp,dept where emp.deptno(+)=dept.deptno;

    //5.全連接配接(full join)(左右表所有資料均出現，對方沒有的用null填充)

    Select ename,sal,dname from emp full join dept on emp.deptno=dept.deptno;

    //6.交叉連接配接(cross join)(笛卡爾乘積)

    Select ename,sal,dname from emp cross join dept on emp.deptno=dept.deptno;

    //7.自然連接配接(natural join)(笛卡爾乘積)

    Select ename,sal,danme from emp natural join dept on emp.deptno=dept.deptno;

    //8.子查詢，查詢工資最高的員工的姓名，工資以及隸屬的部門名稱

Select emp.ename,emp.sal,dept.dname from emp,dept where

emp.deptno=dept.deptno and emp.sal= (select max(sal) from emp);

    //9分組函數(group by),查詢平均工資大于2000的部門編号,having 出現在group by 後面

    Select deptno from emp group by deptno having avg(sal)>2000;

    //10.分組函數,查詢平均工資大于2000的部門編号和部門名稱

    Select deptno,dename from dept where deptno in(select deptno from emp group by

                                                 deptno having avg(sal)>2000)

select current_date from dual //日期函數,dual是Oracle中一張虛拟的表

select to_date(‘2013-1-24’,’yyyy-mm-dd’) from dual;//日期轉換函數：24-1月 -13

select to_char(hiredate,’yyyy-mm-dd’) from emp;//将日期轉換成字元串

select to_number(‘10000’) from dual;//将字元串轉換為數字

PAGE DIVIDSIOIN QUERY(分頁查詢，分頁機關為3)

         第一頁：rownum(1,2,3)

           Select * from emp where rownum <4;

         第二頁：rownum(4,5,6)

            Select * from emp where rownum>3 and rownum < 7 //結果為空,沒有記錄

            Select * from (select rownum rn,emp.* from emp where rownum<7 ) where rn>3;

         第三頁：rownum(7,8,9)

            Select * from(select rownum rn,emp.* from emp where rownum<10) where rn >6;

         第n頁：

            Select * from(select rownum rn,emp.* from emp where rownum<3*n+1) where

           rn>(n-1)*3

JDBC IN ORACLE(Oracle與資料庫的連接配接)

  class JdbcInOracle{

     //實際開發中，下面發放是封裝的，結果也是單獨封裝的

public void jdbc (){

           try{

               Class.forName(“oracle.jdbc.driver.OracleDriver”);

               String url = “jdbc:oracle:thin:@localhost:1521:orcl”;

               String username = “scott”;

               String password = “ysjian”;

               Connection conn =

DriverManager.getConnection(url,username,password);

               String sql = “select * from emp where empno=?”;

               PreparedStatement pstm = conn.prepareStatement(sql);

               pstm.setObject(7396);

               ResultSet rs = pstm.executeQuery();

               if(rs.next()){

                   int deptno = rs.getInt(“deptno”);

                   String ename = rs.getString(“ename”);

}

}catch(ClassNotFoundException e){

         e.printStackTrace();

}catch(SQLException e){