oracle資料庫常用的99個查詢語句

1. select * from emp;

2. select empno, ename, job from emp;

3. select empno 編號, ename 姓名, job 工作from emp;

4. select job4. select distinct job from emp;

6. select distinct empno, job from emp;

說明:因為僱員編號不重複, 所以此時證明所有的列沒有重複,所以不能消除掉重複的列.

7.查詢出僱員的編號, 姓名, 工作, 但是顯示的格式:編號是: 7369 的僱員, 姓名是: smith, 工作是: clear

select '編號是: ' || empno || '的僱員, 姓名是: ' || ename || ', 工作是: ' || job from emp;

8. 求出每位員工的姓名及年薪

select ename, sal * 12 income from emp;

9. 求出工資大於15000的所有僱員資訊

select * from emp where sal > 1500;

10. 查詢每月可以得到獎金的僱員信息

select * from emp where comm is not null;

11. 查詢沒有獎金的僱員信息

select * from emp where comm is null;

12. 查詢出基本工資大於1500 同時可以領取獎金的僱員信息

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

13. 查詢基本工資大於150出基本工資或可領取獎金的員工資訊

select * from emp where sal > 1500 or comm is not null;

14. 查詢出基本工資不大於1500 或不可以領取獎金的僱員信息

select * from emp where not(sal > 1500 and comm is not null);

15. 查詢基本工資大於1500, 但是小於3000 的全部僱員信息

select * from emp where sal > 1500 and sal
16. , 但是小於等於3000 的全部僱員資訊select * from emp where sal >= 1500 and sal select * from emp where sal between 1500 and 3000;

17. 查詢出在1981 年出在1981 年的全部僱員資訊(1981 年1 月1 日至1981 年12 月31 日之間的僱用的僱員)select * from emp where hiredate between '1-1月-81' and '31-12月-81';

18. 要求查詢出姓名是smith 的僱員資訊

select * from emp where ename = 'SMITH';

19. 要求查詢出僱員是7369, 7499, 7521 的僱員的具體信息

lect * from emp where empno = from emp where empno = from emp whselect * from emp where empno = 7369 or empno = 7499 or empno = 7521;

select * from emp where empno in(7369, 7499, 7521);

20. 要求查詢僱員不是7369, 749, 7519, 7519, 75 年的員工where empno not in(7369, 7499, 7521);

21. 要求查詢出姓名是smith, allen, king 的僱員資訊
select * from emp where ename in('SMITH', 'ALLEN', 'KING') ;

22. 查詢第二個員工姓名中第二個字母包含"M" 的員工資料
        select * from emp where ename like '_M%';

23. 查詢出員工姓名中包含字母員工的姓名中包含字母員工的姓名中包含字母僱員的姓名中包含字母員工。
select * from emp where ename like '%M%';

24. 要求查詢出在1981 年僱用的僱員信息
select * from emp where hiredate like '%81%';

25. 查詢工資中包含5 的僱員資訊
select * from emp where sal like '%5%';

26. 查詢僱員編號不是7369 的僱員資訊
select * from emp where empno != 7369;

select * from empere empno
27. 要求依照薪資由低到高排序
select * frm emp order by sal;select * from emp order by sal asc;

28. 要求依照薪資由高到低排序
lect * order by sal desc;

29. 要求查詢出20 部門的所有僱員信息, 查詢的信息按照工資由高到低排序,如果工資相等,則按照僱傭日期由早到晚排序.
select * from emp where deptno = 20 order by sal desc, hiredate asc;

30. 將小寫字母變為大寫字母
select upper('hello') from dual;

31. 將大寫字母變為小寫字母select 寫字母
se lower(HEL'HELy 寫字母select lower(HEL'HELlower' WORLD') from dual;

32. 要求查詢出姓名是smith 的僱員資訊
select * from emp where ename = upper('smith');

33. 使用initcap() 函數將單字的第一個字母大寫
select initcap('hello world') from dual;

34. 將僱員表中的僱員姓名變為開頭字母大寫
select initcap(ename) from emp;

35. 將字串"hello" 和" world" 進行串聯
select concat('hello ', 'world') from dual;

36. 對字串進行操作的常用字元處理函數
select substr('hello', 1, 3) 截取字串, length ('hello') 字串的長度, replace('hello', 'l', 'x') 字串替換from dual;

select substr('hello', 0, 3) 截取字串, length('hello ') 字串的長度, replace('hello', 'l', 'x') 字串替換from dual;

37. 顯示所有僱員的姓名及姓名的後三個字元
select ename, substr(ename, length(ename) -2) from emp;
select ename, substr(ename, -3, 3) 從 emp;

38. 使用數值函數執行四捨五入操作
select round(789.536) from dual;

39. 要求將789.536 數值保留兩位小數
select round(789.536, 2) from dual;的整數的十位進行四捨五入進位

select round(789.536, -2) from dual;

41. 採用trunc() 函數不會保留任何小數,而且小數點也不會執行四捨五入的操作

select trunc(789.536) from dual;

42. 透過trunc() 也可以指定小數點的保留位數

select trunc(789.536, 2) from dual;

43. 作用負數表示位數

select trunc(789. ;

44. 使用mod() 函數可以進行取餘的操作

select mod(10, 3) from dual;

45. 顯示10 部門僱員進入公司的星期數(當前日期- 僱用日期= 天數/ 7 = 星期數)

select empno, ename, round((sysdate - hiredate) / 7) from emp where deptno = 10;

46. 日期函數

months_between(): 求出給定日期範圍的月數

_months(_) ): 在指定的日期上加上指定的月數, 求出之後的日期
next_day(): 指定日期的下一個日期
last_day(): 求出給定日期當月的最後一天日期

47. 

select empno, ename, months_between(sysdate, hiredate) from emp;

select empno, ename, round(months_between(sysdate, hiredate)) from emp;_
48. select sys, 設49. select next_day(sysdate, '星期一') from dual;

50. select last_day(sysdate) from dual;

51. 轉換函數

to_char(): 轉換成字串

to_number(): 數字轉換成數字

to_date(): 轉換成日期

52. 查詢所有僱員的僱員編號, 姓名, 僱用日期
select empno, 
ename,
to_char(hiredate, 'yyyy') year,

ename,

to_char(hiredate, 'yyyy') year,
to_char(hiredate, ' months,
to_char(hiredate, 'dd') day
from emp;

select empno, ename, to_char(hiredate, 'from emp;

select empno, ename, to_char(hiredate, 'yyyy-mm-dd') from emp;

ate

select empno, enameyyyy, '(hiredate, ename, to fmyyyy-mm-dd') from emp;

53. 查詢所有僱員的編號, 姓名和工資

select empno, ename, sal from emp;
select empno, ename, to_char(sal, '99,999') from emp;
select empno, ename, to_char(sal, 'L99,999') from emp;
select empno, ename, to_char(sal, '$99,999') from emp;

54. select to_number('123') + to_number '123') from dual;

55. 將一個字串轉換成日期類型

select to_date('2009-01-01', 'yyyy-mm-dd') from dual;

56. 求出每個僱員的年薪(要求加上獎金)

select empno, ename, sal, comm, (sal + comm) * 12 from emp;
select empno, ename, sal, comm, nvl(comm, 0), (sal + nvl (comm, 0)) * 12 income from emp;

57. decode() 函數類似if....elsif...else 語句

select decode(1, 1, '內容是1', 2, '內容是2', 3, '內容是3') from dual;

58. 查詢出僱員的編號, 姓名, 僱用日期及工作, 要求將僱員的工作替換成以下信息:

select empno 僱員編號, 
ename 員工姓名, 
hiredate 僱用日期, 
decode(job, 
'CLERK', '業務員', 
'SALESMAN', '銷售人員',
'MANAGER', '經理',
'ANALYST', '分析
'MANAGER', '經理',
'ANALYST', '分析員',
'PRESIDENT', '總裁'
) 職位

from emp;

59. 笛卡爾積(交差連接)
select * from emp, dept;

select * from emp cross join dept;內連接

select * from emp e, dept d where e.deptno = d.deptno;
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e .deptno = d.deptno;

61. 自然連接
select * from emp natural join dept;
select * from emp e join dept d using(deptno);

62. 查詢出出姓名的員工編號, 出762.部門的編號, 名稱, 地址

select e.empno, e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno = d.deptno;

63. 要求查詢出僱員的姓名, 工作, 僱員的直接上級領導姓名

select e.ename, e.job, m.ename from emp e, emp m where e.mgr = m.empno;

64. 要求查詢出僱員的姓名, 工作, 僱員的直接上級領導姓名以及部門名稱

select e.ename, e.job, m.ename, d.dname from emp e, emp m, dept d where e.mgr = m.empno and e.deptno = d.deptno;

65. 要求查詢出每位僱員的姓名, 工資, 部門名稱, 工資在公司的等級(salgrade), 及其領導的姓名及工資所在公司的等級
select e.ename, e.sal, d.dname , s.grade, m.ename, m.sal, ms.grade
from emp e, dept d, salgrade s, emp m, salgrade ms
where e.deptno = d.deptno 
and e.sal between s.losanno = d.deptno 
and e.sal between s.losal and s.hisal 
and e.mgr = m.empno

and m.sal between ms.losal and ms.hisal;

select e.ename, 
e.sal, 
d.dname, , 1, '第五等級', 2, '第四等級', 3, '第三等級', 4, '第二等級', 5, '第一等級'), 
m.ename, 
m. sal, 
decode(ms.grade, 1, '第五等級', 2, '第四等級', 3, '第三等級', 4, '第二等級', 5, '第一等級')
from emp e, dept d, salgrade s, emp m, salgrade ms
where e.deptno = d.deptno and e.sal between s.losal and s.hisal and e.mgr = m.empno
and m.sal between ms.losal and ms.hisal;

66. select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno = d.deptno;
    select no, ename, deptno, ename. , loc from emp e inner join dept d on e.deptno = d.deptno;

67. 左外連接
    select empno, ename, d.deptno, dname, loc from emp e, deptno deptno .deptno(+);
    select empno, ename, d.deptno, dname, loc from emp e left outer join dept d on e.deptno = d.deptno;
 emp e left join dept d on e.deptno = d.deptno(+);

68. 右外連接
    select empno, ename, d.deptno, dname, loc from emp e, dept dere e.deptno( ) = d.deptno;
    select empno, ename, d.deptno;
    select empno, ename, d.deptno;

    select empno, ename, d.deptno, dname, loc from emp e right outer join dept d on e.deptno = d.deptno;

    select on e.deptno = d.deptno;

  . emp e right join dept d on e.deptno = d.deptno;

69. select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.mgr = m.empno;

70. select e.empno, e.ename, m.empno, m.ename from emp e, emp m where e.mgr = m.empno(+);

71. 
select * from emp e, dept d where e.deptno = d.deptno and d.deptno = 30;
select * from emp e inner join dept d on e.deptno = d.deptno where d.deptno = 30;

select * from emp e joinno where d.deptno = 30;

select * from emp e joinin dept on e.deptno = d.deptno where d.deptno = 30;
select * from emp e natural join dept d where deptno = 30;
select * from emp e join dept d using(deptno) where
select * from emp e join dept d using(deptno) where deptno = 30; 72. 

select e.ename, d.deptno, d.dname, d.loc from emp e right outer join dept d on e.deptno = d.deptno;

select e.ename, d.deptno, d.dname, d.loc from emp e right join dept d on e.deptno = d.deptno;

select e.ename, d.deptno, d.dname, d.loc from emp e, dept d where e.deptno(+) = d.deptno;

73. select count(ename) from emp;

74. select min(sal) from emp;

75. select max(sal) from emp;

75. select max(sal) from emp;

75. select max(sal) from emp;

75. select( fromsal) sum( emp;

77. select avg(sal) from emp;

78. select sum(sal) from emp where deptno = 20;

79. select avg(sal) from empere deptno = 20383;求出各部門的僱員數量
select deptno, count(deptno) from emp group by deptno;
select deptno, count(empno) from emp group by deptno;

81. 求出各部門的平均薪資
select deptno, avg(sal) from emp group by deptno;

82. 按部門分組, 並顯示部門的名稱, 及每個部門的員工數

select d.dname, count(e.empno) from emp e, dept d 
where e.deptno = d.deptno

group by d.dname;

select d.deptno, d.dname, temp.c

from (select deptno, count(e.empno) c from temp.c

from (select deptno, count(e.empno) c from emp e group by eemp .deptno) temp, dept d
where temp.deptno = d.deptno;

83. 要求顯示平均工資大於2000 的部門編號和平均工資
select deptno, avg(sal) from emp group by deptno ing 角色 by deptg sal) > 2000;

84. 顯示非銷售人員工作名稱以及從事同一工作僱員的月工資的總和,並且要滿足從事同一工作的僱員的月工資合計大於5000, 輸出結果按月工資的合計升序排序.
select job, sum(sal) su from emp where job 'SALESMAN' group by job having sum(sal) > 5000 order by su;

select temp.job, sum(temp.sal) s
from (select temp.job, sum(temp.sal) s

from (select temp.job, sum(temp.sal) s🎜from (select temp.job, sum(temp.sal) s🎜from (select temp.job, sum(temp.sal) s🎜from ( select job, sal from emp e where job 'SALESMAN') temp🎜group by temp.job🎜having sum(temp.sal) > 5000🎜order by s;🎜🎜85. 求出平均工資最高的部門工資🎜🎜85. 求出平均工資最高的部門工資🎜 select max(avg(sal)) from emp group by deptno;🎜🎜86. 要求查詢出比僱員編號為7654 工資高的所有僱員資訊🎜select * from emp where sal >(select sal from emp where empno = 7654) ;🎜

87. 要求查詢出工資比7654 高, 同時與7788 從事相同工作的全部僱員信息
select * from emp 
where sal >(select sal from emp where empno = 7654)
and job empno = 7788);

88. 要求查詢出工資最低的僱員姓名, 工作, 工資
select ename, job, sal from emp where sal = (select min(sal) from emp);

89. 要求查詢:　部門名稱，部門的員工數，部門的平均工資，部門的最低收入僱員的姓名
select d.dname, temp.c, temp.a, e.ename 
from dept d, 
(select deptno, count( empno) c, avg(sal) a, min(sal) m from emp group by deptno) temp, 
emp e
where d.deptno = temp.deptno and e.sal = temp.m;

select d.deptnoselect d.deptnoselect d.deptnoselect , temp.dname, temp.c, temp.a, e.ename, e.sal
from 
(select d.dname , count(e.empno) c, avg(e.sal) a, min(e.sal ) m
from emp e, dept d
where e.deptno = d.deptno
group by d.dname) temp, 
emp e,
dept d
where temp.m = e.sal
and temp.deptd .dname;

90. 求出各部門的最低工資的僱員的信息
select * from emp where sal in(select min(sal) from emp group by deptno);
select * from emp where sal =any( select min(sal) from emp group by deptno);
select * from 
(select min(sal) m from emp group by deptno) temp,
emp e
where e.sal = temp.m;

emp e
where e.sal = temp.m; 90 中, 比子查詢條件中最低(小)的工資要大的僱員資訊
select * from emp where sal >any(select min(sal) from emp group by deptno);

select * from emp where sal > ( select min(min(sal)) from emp group by deptno);

92. 範例90 中, 比子查詢條件中最高(大)的工資要小的僱員資訊
select * from emp where sal select * from emp where sal
93. 範例90 中, 比子查詢條件中最高(大)的薪水要大的員工資訊
select * from emp where sal >all(select min(sal) from emp group by deptno);

select * from emp where sal > (select max(min(sal)) from emp group by deptno);

94. 範例90 中, 比子查詢條件中最低(小)的工資要小的僱員資訊
select * from emp where sal select * from emp where sal
95. 查找出20 部門中沒有獎金的僱員資訊
select * from emp where (sal, nvl( comm, -1)) in (select sal, nvl(comm, -1) from emp where deptno = 20);

select * from emp where deptno = 20 and comm is null;

96. union 運算子回傳兩個查詢選定的所有不重複的行

select deptno from emp union select deptno from dept;

97. union all 操作符合併兩個查詢選定的所有行，包括重複的行

select deptno from emp union all select deptno from dept;

98. intersect 運算子只回傳兩個查詢都有的行

select deptno from emp intersect select deptno from dept;

99. minus 運算子只回傳由第一個查詢選定但沒有被查詢但是沒有被查詢但是沒有被查詢但是第二個查詢選定的行, 也就是在第一個查詢結果中排除在第二個查詢結果中出現的行

select deptno from dept minus select deptno from emp;

更多oracle資料庫常用的99條查詢語句相關文章請關注PHP中文網！ 🎜