oracle 中查詢（select）文法大全

Oracle查詢文法大全<轉>

1.select* from emp;

2. select empno, ename, job from emp;

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

4. select job from emp;

5. 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. 求出工資大於 1500 的所有僱員資訊
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. 查詢出基本工資大於 1500 或者可以領取獎金的僱員資訊
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 < 3000;

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

17. 查詢出在 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 的僱員的具體資訊
select * from emp where empno = 7369 or empno = 7499 or empno = 7521;
select * from emp where empno in(7369, 7499, 7521);

20. 要求查詢出僱員不是 7369, 7499, 7521 的僱員的具體資訊
select * from emp 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. 查詢出僱員姓名中包含字母 M 的僱員資訊
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 emp where empno <> 7369;

27. 要求按照工資由低到高排序
select * frm emp order by sal;
select * from emp order by sal asc;

28. 要求按照工資由高到低排序
select * from emp 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 lower('HELLO 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) from emp;

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

39. 要求將 789.536 數值保留兩位小數
select round(789.536, 2) from dual;

40. 要求將 789.536 數值中的整數的十位進行四捨五入進位
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.536, -2) from dual;

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(): 求出給定日期範圍的月數
add_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 sysdate, add_months(sysdate, 4) from dual;

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,
to_char(hiredate, 'mm') months,
to_char(hiredate, 'dd') day
from emp;

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

select empno, ename, to_char(hiredate, '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', '分析員',
'PRESIDENT', '總裁'
) 職位
from emp;

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

60. 內串連
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. 要求查詢出僱員的編號, 姓名, 部門的編號, 名稱, 地址
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.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,
decode(s.grade, 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 empno, ename, d.deptno, dname, loc from emp e inner join dept d on e.deptno = d.deptno;

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

68. 右外串連
select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno(+) = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e right outer join dept d on e.deptno = d.deptno;
select empno, ename, d.deptno, dname, loc from 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 join dept d 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 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;

76. select sum(sal) from emp;

77. select avg(sal) from emp;

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

79. select avg(sal) from emp where deptno = 20;

80. 求出每個部門的僱員數量
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 emp e group by e.deptno) temp, dept d
where temp.deptno = d.deptno;

83. 要求顯示出平均工資大於 2000 的部門編號和平均工資
select deptno, avg(sal) from emp group by deptno having avg(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 job, sal from emp e where job <> 'SALESMAN') temp
group by temp.job
having sum(temp.sal) > 5000
order by s;

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 = (select job from emp where 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.deptno, 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.dname = d.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;

91. 範例 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 <any(select min(sal) from emp group by deptno);
select * from emp where sal < (select max(min(sal)) from emp group by deptno);

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 <all(select min(sal) from emp group by deptno);
select * from emp where sal < (select min(min(sal)) from emp group by deptno);

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;