Oracle常用語練習

首先，以超級管理員的身份登入Oracle  
    sqlplus sys/bjsxt as sysdba  
 
然後，解除對scott使用者的鎖  
    alter user scott account unlock;  
那麼這個使用者名稱就能使用了。  
(預設全域資料庫名orcl)  
 
1、select ename, sal * 12 from emp; //計算年薪  
2、select 2*3 from dual;  //計算一個比較純的資料用dual表  
3、select sysdate from dual;  //查看當前的系統時間  
4、select ename, sal*12 anuual_sal from emp; //給搜尋欄位更改名稱（雙引號 keepFormat 別名有特殊字元，要加雙引號）。  
5、任何含有空值的數學運算式，最後的計算結果都是空值。  
6、select ename||sal from emp;  //(將sal的查詢結果轉化為字串，與ename串連到一起，相當於Java中的字串串連)  
7、select ename||'afasjkj' from emp;   //字串的串連  
8、select distinct deptno from emp;   //消除deptno欄位重複的值  
9、select distinct deptno , job from emp; //將與這兩個欄位都重複的值去掉  
10、select * from emp where deptno=10;   //(條件過濾查詢)  
11、select * from emp where empno > 10;  //大於 過濾判斷  
12、select * from emp where empno <> 10  //不等於  過濾判斷  
13、select * from emp where ename > 'cba';  //字串比較，實際上比較的是每個字元的AscII值，與在Java中字串的比較是一樣的  
14、select ename, sal from emp where sal between 800 and 1500;  //(between and過濾，包含800 1500)  
15、select ename, sal, comm from emp where comm is null;  //(選擇comm欄位為null的資料)  
16、select ename, sal, comm from emp where comm is not null;  //(選擇comm欄位不為null的資料)  
17、select ename, sal, comm from emp where sal in (800, 1500,2000);  //(in 表範圍)  
18、select ename, sal, hiredate from emp where hiredate > '02-2月-1981'; //(只能按照規定的格式寫)  
19、select ename, sal from emp where deptno =10 or sal >1000;  
20、select ename, sal from emp where deptno =10 and sal >1000;  
21、select ename, sal, comm from emp where sal not in (800, 1500,2000);  //(可以對in指定的條件進行取反)  
22、select ename from emp where ename like '%ALL%';   //(模糊查詢)  
23、select ename from emp where ename like '_A%';    //(取第二個字母是A的所有欄位)  
24、select ename from emp where ename like '%/%%';   //(用逸出字元/查詢欄位中本身就帶%欄位的)  
25、select ename from emp where ename like '%$%%' escape '$';   //(用逸出字元/查詢欄位中本身就帶%欄位的)  
26、select * from dept order by deptno desc; (使用order by  desc欄位 對資料進行降序排列 預設為升序asc);  
27、select * from dept where deptno <>10 order by deptno asc;   //(我們可以將過濾以後的資料再進行排序)    
28、select ename, sal, deptno from emp order by deptno asc, ename desc;   //(按照多個欄位排序 首先按照deptno升序排列,當detpno相同時，內部再按照ename的降序排列)  
29、select lower(ename) from emp;  //(函數lower() 將ename搜尋出來後全部轉化為小寫)；  
30、select ename from emp where lower(ename) like '_a%';  //(首先將所搜尋欄位轉化為小寫，然後判斷第二個字母是不是a)  
31、select substr(ename, 2, 3) from emp;    //(使用函數substr() 將搜素出來的ename欄位從第二個字母開始截，一共截3個字元)  
32、select chr(65) from dual;  //(函數chr() 將數字轉化為AscII中相對應的字元)   
33、select ascii('A') from dual;  //(函數ascii()與32中的chr()函數是相反的 將相應的字元轉化為相應的Ascii編碼）                                                                                                                                                                                                                                                                                                                                             )  
34、select round(23.232) from dual;  //(函數round() 進行四捨五入操作)  
35、select round(23.232, 2) from dual;  //(四捨五入後保留的小數位元 0 個位 -1 十位)  
36、select to_char(sal, '$99,999.9999')from emp;  //(加$符號加入千位分隔字元，保留四位小數，沒有的補零)  
37、select to_char(sal, 'L99,999.9999')from emp;  //(L 將貨幣轉化為本地幣種此處將顯示￥人民幣)  
38、select to_char(sal, 'L00,000.0000')from emp;  //(補零位元不一樣，可到資料庫執行查看)  
39、select to_char(hiredate, 'yyyy-MM-DD HH:MI:SS') from emp;  //(改變日期預設的顯示格式)  
40、select to_char(sysdate, 'yyyy-MM-DD HH:MI:SS') from dual;  //(用12小時制顯示當前的系統時間)  
41、select to_char(sysdate, 'yyyy-MM-DD HH24:MI:SS') from dual;  //(用24小時制顯示當前的系統時間)  
42、select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:24:45','YYYY-MM-DD HH24:MI:SS');   //(函數to-date 查詢公司在所給時間以後入職的人員)  
43、select sal from emp where sal > to_number('$1,250.00', '$9,999.99');   //(函數to_number()求出這種薪水裡帶有特殊符號的)  
44、select ename, sal*12 +  nvl(comm,0) from emp;   //(函數nvl() 求出員工的"年薪 + 提成(或獎金)問題")  
45、select max(sal) from emp;  // (函數max() 求出emp表中sal欄位的最大值)  
46、select min(sal) from emp;  // (函數max() 求出emp表中sal欄位的最小值)  
47、select avg(sal) from emp;  //(avg()求平均薪水);  
48、select to_char(avg(sal), '999999.99') from emp;   //(將求出來的平均薪水只保留2位小數)  
49、select round(avg(sal), 2) from emp;  //(將平均薪水四捨五入到小數點後2位)  
50、select sum(sal) from emp;  //(求出每個月要支付的總薪水)  
 
/////////////////////////組函數(共5個)：將多個條件組合到一起最後只產生一個資料//////min() max() avg() sum() count()/////////////////////////////  
51、select count(*) from emp;  //求出表中一共有多少條記錄  
52、select count(*) from emp where deptno=10;  //再要求一共有多少條記錄的時候，還可以在後面跟上限定條件  
53、select count(distinct deptno) from emp;   //統計部門編號前提是去掉重複的值  
////////////////////////聚組函數group by() //////////////////////////////////////  
54、select deptno, avg(sal) from emp group by deptno;  //按照deptno分組，查看每個部門的平均工資  
55、select max(sal) from emp group by deptno, job; //分組的時候，還可以按照多個欄位進行分組，兩個欄位不相同的為一組  
56、select ename from emp where sal = (select max(sal) from emp); //求出  
57、select deptno, max(sal) from emp group by deptno; //搜素這個部門中薪水最高的的值  
//////////////////////////////////////////////////having函數對於group by函數的過濾 不能用where//////////////////////////////////////  
58、select deptno, avg(sal) from emp group by deptno having avg(sal) >2000; （order by ）//求出每個部門的平均值，並且要 > 2000  
59、select avg(sal) from emp where sal >1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;//求出sal>1200的平均值按照deptno分組，平均值要>1500最後按照sal的倒序排列  
60、select ename,sal from emp where sal > (select avg(sal) from emp);  //求那些人的薪水是在平均薪水之上的。  
61、select ename, sal from emp join (select max(sal) max_sal ,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno=t.deptno);  //查詢每個部門中工資最高的那個人  
///////////////////////////////等值串連//////////////////////////////////////  
62、select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;  //自串連，把一張表當成兩張表來用  
63、select ename, dname from emp, dept;  //92年文法 兩張表的串連 笛卡爾積。  
64、select ename, dname from emp cross join dept; //99年文法 兩張表的串連用cross join  
65、select ename, dname from emp, dept where emp.deptno = dept.deptno; // 92年文法 表串連 + 條件串連  
66、select ename, dname from emp join dept on(emp.deptno = dept.deptno); // 新文法  
67、select ename,dname from emp join dept using(deptno); //與66題的寫法是一樣的，但是不推薦使用using ： 假設條件太多  
///////////////////////////////////////非等值串連///////////////////////////////////////////  
68、select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal); //兩張表的串連 此種寫法比用where更清晰  
69、select ename, dname, grade from emp e  
    join dept d on(e.deptno = d.deptno)  
    join salgrade s on (e.sal between s.losal and s.hisal)  
    where ename not like '_A%';  //三張表的串連  
70、select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno); //自串連第二種寫法，同62  
71、select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno); //左外串連 把左邊沒有滿足條件的資料也取出來  
72、select ename, dname from emp e right join dept d on(e.deptno = d.deptno); //右外串連  
73、select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s  on    (t.avg_sal between s.losal and s.hisal);//求每個部門平均薪水的等級  
74、select ename from emp where empno in (select mgr from emp); // 在表中搜尋那些人是經理  
75、select sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal)); // 面試題 不用組函數max()求薪水的最大值  
76、select deptno, max_sal from  
    (select avg(sal) max_sal,deptno from emp group by deptno)  
        where max_sal =  
        (select max(max_sal) from  
         (select avg(sal) max_sal,deptno from emp group by deptno)  
    );//求平均薪水最高的部門名稱和編號。  
77、select t1.deptno, grade, avg_sal from  
      (select deptno, grade, avg_sal from  
    (select deptno, avg(sal) avg_sal from emp group by deptno) t  
        join salgrade s on(t.avg_sal between s.losal and s.hisal)  
      ) t1  
    join dept on (t1.deptno = dept.deptno)  
    where t1.grade =   
      (  
        select min(grade) from  
          (select deptno, grade, avg_sal from  
    (select deptno, avg(sal) avg_sal from emp group by deptno) t  
    join salgrade s on(t.avg_sal between s.losal and s.hisal)  
     )  
   )//求平均薪水等級最低的部門的名稱 哈哈 確實比較麻煩  
78、create view v$_dept_avg_sal_info as  
    select deptno, grade, avg_sal from  
       (select deptno, avg(sal) avg_sal from emp group by deptno) t  
    join salgrade s on(t.avg_sal between s.losal and s.hisal);  
    //視圖的建立，一般以v$開頭，但不是固定的   

1 2 3 4 下一頁