Oracle學習筆記五---表查詢
下面使用scott用戶,用到的表見底部
1、查看表結構 desc [表名] desc emp
2、distinct命令，去除重復行
3、練習 查找SMITH的工作，薪水等 select deptno,job,sal from emp where ename ='SMITH';
4、函數nvl 如果值為null，即以0代替
查找員工年工資 select sal*13+comm*13 "年工資" from emp;
在comm沒有值時，結果將沒有值，所以使用函數nvl select sal*13+nvl(comm,0)*13 "年工資" from emp;
5、條件並列：and select sal, empno, ename from emp where sal>=2000 and sal<2500;
6、like操作符 % 表示多個任意字符 _ 表示單個任意字符 首字母為S的員工姓名和工資 select ename, sal where ename like 'S%'; 第三個字符為大寫O得所有員工姓名和工資 select ename,sal from emp where ename like '__O%';
7、在where條件使用in select * from emp where empno in(7844,7998,3222);
8、邏輯操作符 工資高於500或崗位為MANAGER 同事還要滿足姓名首字母為大寫J select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';
9、order by [字段] [規則:asc、desc] 工資從第低到高排序 select * from emp order by sal; //asc默認 select * from emp order by sal desc; //從高到低 按照部門號升序而雇員工資降序排列 select * from emp order by deptno , sal desc;
10、使用列的別名排序 as select ename, (sal+nvl(comm,0))*13 as "年薪" from emp order by "年薪"; select ename, (sal+nvl(comm,0))*13 as nianxin from emp order by nianxin; select ename, (sal+nvl(comm,0))*13 "年薪" from emp order by "年薪"; select ename, (sal+nvl(comm,0))*13 nianxin from emp order by nianxin; as可以省略，中文別名需要加雙引號
注意：給表起別名不能加as，見下面 19
------8<---------------8<---------------8<---------------8<---------------8<---------------8<--------- 復雜查詢 ------8<---------------8<---------------8<---------------8<---------------8<---------------8<---------
11、數據分組 max min sum count avg 顯示所有員工中最高工資和最低工資 select max(sal), min(sal) from emp; 顯示最高工資的員工姓名及其工資 select ename,sal from emp where sal= (select max(sal) from emp);
注意： 這樣寫是錯誤的：select ename,sal from emp where sal = max(sal);ORA-00934: 此處不允許使用分組函數 顯示比平均工資高的員工 select * from emp where sal > (select avg(sal) from emp); 顯示所有員工平均工資和工資總和 計算總共有多少員工
12、group by 用於對查詢的結果分組統計 select avg(sal), max(sal), deptno from emp group by deptno;
![\]()
select avg(sal), max(sal), deptno from emp group by deptno, job;
![\]()
13、篩選having 分組 select avg(sal), max(sal), deptno from emp where group by deptno
![\]()
分組後篩選 select avg(sal), max(sal), deptno from emp where group by deptno having avg(sal) > 2000;
![\]()
14、數據分組小結 14.1 分組函數只能出現在選擇列表、having 、order by 子句中 14.2 如果select 語句中同時包含group by, having, order by 那麼他們的順序是 從左至右 group by , having , order by
--------8<-----------------8<-----------------8<-----------------8<-----------------8<--------- 多表查詢 --------8<-----------------8<-----------------8<-----------------8<-----------------8<---------
15、笛卡爾集 多表查詢 多表查詢的條件是 至少不能少於表的個數-1 顯示雇員名字、工資、及其部門名字 select a.ename,a.sal, b.dname from emp a, dept b where a.deptno = b.deptno; 這裡兩個表 dept和emp，一個條件 emp.deptno = dept .deptno; 顯示雇員名稱，工資，部門名字，按部門排序 select a.deptno, a.ename,a.sal, b.dname from emp a, dept b where a.deptno = b.deptno order by a.deptno
16、自連接 select a.ename "小弟", b.ename BOSS from emp a, emp b where a.mgr = b.empno and a.ename = 'FORD'
![\]()
17、子查詢 17.1、單行子查詢 ‘=’ 等於號 題目：查找和SMITH同一部門的員工
查找出SMITH的部門號（結果僅一行數據） select deptno from emp where ename='SMITH'; 顯示 select * from emp where deptno
= (select deptno from emp where ename='SMITH');
17.2、單列多行子查詢 in 、 all、any 題目：查找和部門10的工作相同的雇員的名字 查找出10號部門所有的崗位（結果是多行數據） select distinct job from emp where deptno=10; 顯示 select * from emp where job
in (select distinct job from emp where deptno=10)
顯示工資比部門30的所有員工的工資高的員工姓名工資和部門號 select ename,sal,deptno from emp where sal >
all (select sal from emp where deptno=30);
根據oracle的執行順序，下面這個單行子查詢效率更高 select ename,sal,deptno from emp where sal >
(select max(sal) from emp where deptno=30);
顯示工資比部門30的任意員工的工資高的員工姓名工資和部門號 select ename,sal,deptno from emp where sal >
any (select sal from emp where deptno=30);
下面這個單行子查詢效率更高 select ename,sal,deptno from emp where sal >
(select min(sal) from emp where deptno=30);
18、多行多列 select * from emp where ( job, deptno) = (select job, deptno from emp where ename = 'SMITH');
![\]()
19、練習 顯示各部門高於該部門平均工資的員工姓名、工資、部門號 select a2.ename, a2.sal, a2.deptno, a1.mysal from emp a2, (select deptno, avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal > a1.mysal;
上面子查詢得到的表a1叫
內嵌視圖 from子查詢必須指定別名，這裡指定為a1，另外給表取別名時不能加as
20、分頁查詢 一共三種 20.1、根據rownum來分，效率還不錯，可讀性也行
select a1.* , rownum rn from (select * from emp) a1; 這裡a1.* a1表的所有列
select a1.* , rownum rn from (select * from emp) a1 where rownum < 8
![\]()
select * from (select a1.* , rownum rn from (select * from emp) a1 where rownum < 8) a2 where rn > 5;
![\]()
這裡，rn不能修改為rownum，子查詢的rownum也不能修改為rn 20.2、根據ROWID來分，效率最高，但可讀性差 20.3、根據分析函數來分，效率很差，
21、用查詢結果創建新表 create table mytable(id,name,sal,job,deptno) as select empno, ename,sal,job,deptno from emp; 表將創建，數據可以拷貝過來了
22、合並查詢 為了合並多個select語句的結果，可以使用集合操作符：union、union all、intersect、minus 22.1、union 【並集】會去掉重復的記錄 select ename,sal,job from emp where job='MANAGER' union select ename,sal,job from emp where sal>2500;
![\]()
22.2、union all 【並集】不會去掉重復的記錄 22.3、intersect 【交集】 22.4、minus 【差集】
23、創建新的數據庫 23.1、通過oracle提供的向導工具 dbca :數據庫配置助手 Database Configuration Assistant
emp表
![\]()
dept表
![\]()
