OracleDBA之表管理

標籤：dba   10個   復原   between   eve   search   作業系統   values   distinct   

　下面是Oracle表管理的部分，用到的測試表是oracle資料庫中scott使用者下的表做的測試，有的實驗也用到了hr使用者的資料，以下這些東西是我的麥庫上存的當時學Oracle的學習筆記今天拿出來和大家分享一下，轉載請註明出處，下面用的Oracle的版本是10g,用的時WinServer2003的作業系統，可能有些命令和Oracle11g的有所不同，但大部分是一樣的，接下來還會陸續的分享一下Oracle中對資料庫的管理，對錶的管理，還有Oracle中的預存程序和PL/SQL編程。用到的Oracle的管理工具是PL/SQL Developerl和SQL PLUS，歡迎大家批評指正。 

1.表名和列名的命名規則：
　　1.必須以字母開頭
　　2.長度不能超過30個字元
　　3.不能使用oracle的保留字命名
　　4.只能使用字母數字底線，$或#；

2.oracle的資料類型
　　1.字元型：
　　　　char 定長 最長2000字元(因為是定長的，在做查詢時是多位同時比較，其好處是查詢速度特快)
　　　　　　demo: char(10) 存放‘ab’占倆個字元後面空著的8個字元用空格補全，所以存了2個字元也佔10個字元的空間；

　　　　varchar2 變長 最大是4000字元（查詢速度較慢，因為是變長，查詢比較是是一位一位的比較）
　　　　　　demo：varchar2(10) 存放‘ab’,就佔2個字元；

　　　　clob(character large object) 字元型大對象，最大是4G

　　2.數字類型：
　　　　number 的範圍 10的-38次方---10的38次方可以表示小數也可以表示整數
　　　　number（5，2）表示有5位有效數字，兩位小數；範圍 -999.99 -- 999.99
　　　　number(5) 表示有5位整數，範圍：-99999-99999；

　　3.日期類型：
　　　　date 包括年月日和時分秒
　　　　timestamp 時間戳記（毫秒級）
　　　　在oracle中預設的日期格式是“DD-MON-YY” 如“01-5月-1992”，如果沒有月則添加不成功；
　　　　修改date的格式：

alter session set nls_date_fomat = "yyyy-mm-dd";

 

　　4.大資料（存放媒體）
　　　　blob 位元據 可以存放圖片/聲音/視頻 最大是4G普通的存放媒體資料一般在資料庫中存放的是所放的檔案夾路徑當為了安全性時才會把媒體檔案放在資料庫中；

3.oracle中建立表

1 sql>create table student( --建立名為student的資料庫表2 　　name varchar2(20),    --名字10個變長3 　　idcar char(18),     --身份證18個定長字元4 　　sex char(2),     --性別2個定長字元5 　　grade number(5,2)    --成績為浮點數，有效5位小數位為2位；6 )

 

4.oracle中往已有的表中新增列；

sql>alter table student add(classid number(2));

5.修改已有欄位的長度

sql>alter table student modify(name varchar2(10));

6.刪除表中的已有欄位

sql>alter table student modify(name varchar2(10));

7.表的重新命名；

sql>rename student to std;

9.往表中插入資料：
　　1.省略欄位名

sql>insert into student values(‘name‘,‘231‘,‘男‘,234.89);

　　2.給部分欄位賦值

sql>insert into student(name,idcar) values(‘TOM‘,‘123‘);

　　3.查詢idcard欄位為空白的學生

sql>select * from student where idcard is null;

 

10.修改表中的資料：

sql>update student set name=‘cat‘ where id=1;

 

11.oracle中的復原：（要養成建立儲存點的習慣）--commit後所有的儲存點都沒有了

　　1.復原之前先建立儲存點　　　　sql>savepoint pointName;　　2.刪除表中的記錄　　　　sql>delete from student;;　　3.復原　　　　sql>rollback to pointName;　　　　truncate table student; --刪除表中的所有的資料，不寫日誌，無法復原，刪除速度極快；

 

 

 

Oracle中的select語句的練習，這也是痛點

　　1.emp表中的內關聯查詢：給出每個僱員的名字以及他們經理的名字, 使用表的別名；

sql>select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

　　2.去除重複的行，重複的行的意思是行的每個欄位都相同； distinct

sql>select distinct emp.job,emp.mgr from emp;

　　3.查詢SMITH的薪水，職位和部門：

SQL> select emp.ename as 姓名,emp.sal as 薪水, emp.job as 工作,dept.dname as 部門2 from emp,dept where emp.ename=‘SMITH‘ and emp.deptno=dept.deptno;姓名 薪水 工作 部門---------- --------- --------- --------------SMITH 800.00 CLERK RESEARCH

 

　　4.開啟顯示sql語句已耗用時間　　

sql>set timing on;

 

　　5.查詢SMITH的年工資；--nvl 處理為null的欄位，在運算式裡如果有一個值為null則結果就為null用nvl()函數處理為空白的欄位，例如nvl(comm,0)：如果為null則用0替換；

select emp.ename "名字", emp.sal*12+nvl(emp.comm,0)*12 "年薪" from emp where name=‘SMITH‘;

 

　　6.模糊查詢like %代替多個字元，_代替一個字元；

select * from emp where emp.ename like ‘S%‘;

 

　　7.or的升級in查詢

select * from emp where emp.empno in(7369,7788);

　　8.查詢工資高於500或者是崗位是manager同時名字以J開頭的僱員

SQL> select * from emp where (emp.sal>500 or emp.job=‘MANAGER‘) and emp.ename like ‘J%‘;

 

　　9.按照工資從低到高排序 order by語句； desc是降序（從高到低），asc是升序（從低到高 預設）

SQL>select * from emp order by emp.sal asc;

　　10.按照部門號升序（asc）,員工號降序（desc）

SQL>select * from emp order by emp.deptno ,emp.empno desc;

 

　　11.使用列的別名排序：按年薪降序（desc）

SQL>select emp.sal*12 "年薪" from emp order by "年薪" desc;

 

資料的分組————min,max,avg,sum,count;

　　1.查詢員工的最高工資和最低工資； min()和max() 的使用

select max(sal) "最高工資", min(sal) "最低工資" from emp;

　　2.查詢所有員工的工資總和和平均工資 sun() 和 avg() 的使用；

SQL> select sum(sal) "工資總和", avg(sal) "平均工資" from emp;

　　3.查詢員工的總人數：

SQL> select count(*) from emp;

　　4.把最高工資的員工的資訊輸出(用到了子查詢)

SQL>select * from emp where sal=(select max(sal) from emp);//ERROR 不能使用分組函數error: select * from emp where sal = max(sal);--error;error: select ename,max(sal) from emp; -error;

　　　　select 後面若有分組函數子可以跟分組函數

　　5.顯示工資高於平均工資的員工資訊：

SQL>select * from emp where sal=(select max(sal) from emp);//ERROR 不能使用分組函數error: select * from emp where sal = max(sal);--error;error: select ename,max(sal) from emp; -error;

　　　

 

group by 和 having子句
　　group by 用於對查詢的結果進行分組統計
　　having子句用於限制分組顯示結果

　　1.顯示每個部門的平均工資和最高工資；

　select avg(sal),max(sal),deptno from emp group by deptno;

　　2.顯示每個部門的每種崗位的平均工資和最高工資

SQL> select avg(sal),max(sal),deptno,job from emp group by deptno,emp.job order by deptno;

 

　　3.顯示平均工資小於2000的部門號和他們的平均工資：

SQL> select emp.deptno,avg(sal) from emp group by emp.deptno having avg(sal)<2000;DEPTNO AVG(SAL)------ ----------30 1566.66666

分組函數只能出現在挑選清單，having，order by子句中
如果select中同時有group by ,having ,order by 則三者的順序為group by ,having, order by;

 

 

多表查詢：
　　1.顯示僱員名，僱員工資，所在部門名稱；

SQL> select emp.empno,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;

　　2.顯示部門號為10的僱員名，僱員工資，所在部門名稱

SQL> select emp.empno,emp.sal,dept.dname from emp,dept where emp.deptno=10 and emp.deptno=dept.deptno;

 

　　3.顯示僱員名，僱員工資，工資的層級；

SQL> select emp.ename,emp.sal,salgrade.grade          from emp,salgrade        where emp.sal between salgrade.losal and salgrade.hisal;    

 

子查詢： SQL中執行順序是從右至左執行
　　1.查詢與SMITH在同一部門的所有員工；

SQL> select * from emp where emp.deptno=(         select emp.deptno from emp where emp.ename=‘SMITH‘);    

 

　　2.查詢和部門10的工作相同的員工的資訊

SQL> select * from emp where emp.job in(          select emp.job from emp where emp.deptno=10);      

 

　3.顯示工資比部門號為30的所有員工的工資都高的員工資訊；（用 all() 或 max()實現）

SQL> select * from emp where sal>all(         select sal from emp where emp.deptno=30);或者（下面的效率要高的多）SQL> select * from emp where sal>(         select max(sal) from emp where emp.deptno=30);

 

　　4.顯示工資比部門號為30的一個員工的工資都高的員工資訊；

SQL> select * from emp where sal>any(         select sal from emp where emp.deptno=30);或者（下面的效率要高的多）SQL> select * from emp where sal>(         select min(sal) from emp where emp.deptno=30);

 

返回多欄位的子查詢：
 　　1.查詢與SMITH在同一部門並且職位也相同的員工資訊；

SQL> select * from emp where (deptno,job)=(         select deptno,job from emp where ename=‘SMITH‘);

 

    2.查詢員工比自己部門的平均工資高的員工資訊；（把查詢出的資訊當作一張表起一個別名）

SQL> select * from emp a,(         select deptno,avg(sal) mysal from emp group by deptno) a2         where a.deptno = a2.deptno and a.sal>a2.mysal;

 

在from中使用子查詢時查詢的結果會當作一個視圖來對待，因此也叫做內嵌視圖
必須給內嵌視圖命一個別名

OracleDBA之表管理