Day04- -e Oracle

標籤：ini   取值   esc   欄位   null   單個字元   img   誤刪   let   

Day04- -e Oracle 查詢基礎
學習目標：
? 修改資料
? 刪除資料
? 簡單查詢
? 條件查詢
? 分組查詢
e oracle 修改資料
使用 update 語句修改表中資料。
U Ue pdate 語 句基本 語 法：
update 表名 set 列名= = 運算式 [, 列名 2= 運算式 2,...][where 條件 ];
注意事項：
1、update 文法可以用新值更新原有表行中的各列；
2、set 子句指示要修改哪些列和要給予哪些值；
3、where 子句指定應更新哪些行。如沒有 where 子句，則更新所有的行。( ( 特別小心) )
對 students 中的資料進行修改
將張三的性別改成女
SQL>update students set sex=‘女‘ where name=‘張三‘;
把張三的獎學金改為 10
SQL>update students set fellowship=10 where name=‘張三‘;
把所有人的獎金都指高 10%
SQL>update students set fellowship=fellowship*1.1;
將沒有獎學金同學的獎學金改成 10 元
SQL>update students set fellowship=10 where fellowship is null;
特別注意：當修改空記錄時應用 is null 而不能使用=null 或=‘‘
e oracle 表的管理- -- 刪除資料
基本文法：
delete from TABLENAME [where where_definition];
delete from 表名 [where 條件運算式 ];
注意事項：
1、如果不使用 where 子句，將刪除表中所有的資料。(特別注意)
2、delete 語句不能刪除某一列的值(可使用 update)。
3、使用 delete 語句僅刪除記錄，不刪除表本身。如要刪除表，使用 drop table 語句。
4、同 insert 和 update 一樣，從一個表中刪除記錄將引起其它表的參照完整性問題，在修改資料庫資料
時，頭腦中應始終不要忘記這個潛在的問題。
刪除的幾種方法比較：
delete from 表名;
刪除所有記錄，表結構還在，寫日誌，可以恢複的，速度慢
drop table 表名;
刪除表的結構和資料
delete from student where xh=‘A001‘;
刪除一條記錄
truncate table 表名;
刪除表中的所有記錄，表結構還在，不寫日誌，無法找回刪除的記錄，速度快。
設定儲存點
savepoint 儲存點名稱;
復原
rollback to 儲存點名稱;
合 特別注意：設定儲存點及復原操作是配合 delete 語句使用，用來找回使用 delete 刪除的資料。而通過
truncate 刪除的表資料是無法通過此方法找回的。
建議： ：
在使用 delete 刪除表資料前使用 savepoint 設定儲存點，防止資料誤刪除。
e oracle 表基本查詢基礎
scott 使用者存在的幾張表(emp,dept,salgrade)為大家示範如何使用 select 語句，select 語
句在軟體編程中非常的有用，希望大家好好的掌握。
1 ）.EMP 員工表

 

2 2 ）T DEPT 部門表

 

3 3 ）E SALGRADE 工資層級表

 

本 基本 select 語句
基本文法：
select [是否剔除重複資料] *|{欄位名(列名),欄位名 2(列名 2),欄位名 3(列名 3)..} from 表名 [where {條
件}];
注意事項：
1、select 指定查詢哪些列的資料；
2、column 指定列名；
3、*代表查詢所有列；
4、from 指定查詢哪張表；
5、distinct 可選，指顯示結果時，是否剔除重複資料；
6、where 條件。
簡單的查詢語句
1） ） 查詢所有列
SQL>select * from 表名;
2） ） 查詢指定列
SQL>select 列 1,列 2,列 3,.. from 表名;
3） ） 如何取消重複行
SQL>select distinct deptno,job from emp;
4）查詢 SMITH 的薪水，工作，所在部門
SQL>select sal,job,deptno from emp where ename=‘SMITH‘;
特別注意：
oracle 對 對 sql 語句不區分大小寫，但對查詢內容區分大小寫。這與 sqlserver 是有區別的，sqlserver
對查詢內容不區分大小寫。
使用算數運算式
1） ） 顯示每個僱員的年工資
SQL>select ename,sal*13+nvl(comm,0)*13 from emp;
2） ） 使用列的別名
SQL>select ename "姓名",sal*13+nvl(comm,0)*13 "年度營收" from emp;
SQL>select ename 姓名,sal*13+nvl(comm,0)*13 年度營收 from emp;
SQL>select ename as "姓名",sal*13+nvl(comm,0)*13 as "年度營收" from emp;
特別注意：
oracle 在使用別名時，可以用雙引號或不使用或使用 as 來表明別名。但不能使用單引號。sqlserver
是可以使用雙引號、單引號。
理 如何處理 null 值
nvl 函數： ：oracle 提供的函數，是用於處理 null 值使用的。
例子：查詢年薪
SQL>select ename,sal*13+nvl(comm,0)*13 from emp;
nvl(值 1,值 2) 解釋：nvl 值 1 為 null 時則取值 2，值 1 不為 null 時則取值 1 原值。
如何連接字串(||)
在查詢的時候，希望把多列內容做為一列內容返回可以使用|| 串連符。
例子：查詢年薪
SQL>select ename ||‘年度營收‘||(sal*13+nvl(comm,0)*13) "僱員的年度營收" from emp;
用 使用 where 子句
1） ）於 如何顯示工資高於 3000 的員工
SQL>select ename,sal from emp where sal>3000;
2） ）找 如何尋找 1982.1.1 後入職的員工
SQL>select ename,hiredate from emp where hiredate>‘1-1 月-82‘;
也可以使用 to_char 函數轉換日期類型後再進行日期比較，如下：
SQL>select ename,hiredate from emp where to_char(hiredate,‘yyyy-mm-dd‘)>‘1982-1-1‘;
字元對比還是有一定出入的。不推薦使用。
3） ）在 如何顯示工資在 2000 到 到 2500 的員工情況
SQL>select * from emp where sal>=2000 and sal<=2500;
SQL>select * from emp where sal between 2000 and 2500;
說明： between 是指定區間內取值，如：between 2000 and 2500，取 2000 至 2500 內的值，同時包含
2000 和 2500
用 如何使用 like 操作符
%:表示任意 0 到多個字元
_:表示任意單個字元
1 ）如何顯示首字元為 S 的員工姓名和工資
SQL>select ename,sal from emp where ename like ‘S%‘;
2 ）如何顯示第三個字元為大寫 O 的所有員工的姓名和工資
SQL>select ename,sal from emp where ename link ‘__O%‘;
在 在 where 條件中使用 in
示 如何顯示 empno 為 為 123,345,800... 的僱員情況
SQL>select * from emp where empno=123 or empno=345 or emp=800;
SQL>select * from emp where empno in(123,345,800);
用 使用 is null 的操作符
如何顯示沒有上級的僱員的情況
SQL>select * from emp where mgr is null;
使用邏輯操作符號
於 查詢工資高於 500 或是崗位為 manager 的僱員，同時還要滿足他們的姓名首寫字母為大寫的 J
SQL>select * from emp where (sal>500 or job=‘MANAGER‘) and (ename like ‘J%‘);
用 使用 order by 子句
1） ） 如何按照工資的從低到高的順序顯示僱員的資訊
SQL>select * from emp order by sal asc;
注意：asc 寫或不寫都是升序排序即從小到大排序，desc 則是降序排序從大到小排序。
2） ） 按照部門號升序而僱員的入職時間降序排列
SQL>select * from emp order by deptno,hiredate desc;
3） ） 使用列的別名排序
SQL>select ename,sal*12 "年薪" from emp order by "年薪" asc;
別名需要使用“”號圈中。
O O racle 分組查詢
在實際應用中經常需要執行複雜的資料統計，經常需要顯示多張表的資料；要用到分
組函數 max,min,avg,sum,count。
Max(),min() 最大最小
如何顯示所有員工中最高工資和最低工資
SQL>select max(sal) "最高工資",min(sal) "最低工資" from emp;
請查詢最高年工資
SQL>select max(sal*13+nvl(comm,0)*13) "最高年工資",min(sal*13+nvl(comm,0)*13) "最低年工資" from
emp;
Avg() 求平均
顯示所有員工的平均工資和工資總和
SQL>select avg(sal) "平均工資",sum(sal) "工資總和" from emp;
特別注意：
avg(sal)不會把 sal 為 null 的行進行統計，因此我們要注意，如果，你希望為空白值也考慮，則我們可以
這樣做
SQL>selec sum(sal)/count(*) from emp;
count(*) 求總數
計算共有多少員工
SQL>select count(*) "共有員工" from emp;
練習題： ：
請顯示工資最高的員工的名字，工作崗位
SQL>select ename,job from emp where sal=(select max(sal) from emp);
特別注意：select 語句執行的順序是從右向左執行，正好和書寫的方式相反。
SQL>select ename,job from emp where sal=(select max(sal) from emp);
oracle 會先執行 select max(sal) from emp 這個語句，得出最大工資後。再執行 where 條件前的語句。
請顯示工資高於平均工資的員工資訊
SQL>select * from emp where sal>(select avg(sal) from emp);
SQL>select * from emp where sal>(select sum(sal)/count(*) from emp);
group by 和 和 having 子句
group by ：用於對查詢的結果分組統計；
g having 子句：用於限制( ( 過濾) ) 分組顯示結果。
1）如何顯示每個部門的平均工資和最高工資
SQL>select avg(sal) "平均工資",max(sal) "最高工資",deptno "部門編號" from emp group by deptno;
2）顯示每個部門的每種崗位的平均工資和最低工資
SQL>select avg(sal) "平均工資",min(sal) "最低工資",job "職位",deptno "部門編號" from emp group by
deptno,job order by deptno;
3）顯示部門平均工資低於 2000 的部門號和它的平均工資
SQL>select avg(sal) "平均工資",deptno "部門編號" from emp group by deptno having avg(sal)<2000;
對資料分組的總結：
1、分組函數(avg...)只能出現在挑選清單、having、order by 子句中；
2、如果在 select 語句中同時包含有 group by/having/order by 那麼他們的順序是 group by/having/order
by；
3、在選擇列中如果有列、運算式和分組函數，那麼這些列和運算式必需有一個出現在 group by 子句
中，否則會出錯。
如 select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;
這裡 deptno 就一定要出現在 group by 中。

Day04- -e Oracle