OracleDBA表管理

    

創(chuàng)新互聯(lián)公司堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的北鎮(zhèn)網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

下面是Oracle表管理的部分，用到的測(cè)試表是oracle數(shù)據(jù)庫(kù)中scott用戶下的表做的測(cè)試，有的實(shí)驗(yàn)也用到了hr用戶的數(shù)據(jù)，以下這些東西是我的麥庫(kù)上存的當(dāng)時(shí)學(xué)Oracle的學(xué)習(xí)筆記今天拿出來(lái)和大家分享一下，轉(zhuǎn)載請(qǐng)注明出處，下面用的Oracle的版本是10g,用的時(shí)WinServer2003的操作系統(tǒng)，可能有些命令和Oracle11g的有所不同，但大部分是一樣的，接下來(lái)還會(huì)陸續(xù)的分享一下Oracle中對(duì)數(shù)據(jù)庫(kù)的管理，對(duì)表的管理，還有Oracle中的存儲(chǔ)過(guò)程和PL/SQL編程。用到的Oracle的管理工具是PL/SQL Developerl和SQL PLUS，歡迎大家批評(píng)指正。 

1.表名和列名的命名規(guī)則：
　　1.必須以字母開(kāi)頭
　　2.長(zhǎng)度不能超過(guò)30個(gè)字符
　　3.不能使用oracle的保留字命名
　　4.只能使用字母數(shù)字下劃線，$或#；

2.oracle的數(shù)據(jù)類型
　　1.字符型：
　　　　char 定長(zhǎng) 最長(zhǎng)2000字符(因?yàn)槭嵌ㄩL(zhǎng)的，在做查詢時(shí)是多位同時(shí)比較，其好處是查詢速度特快)
　　　　　　demo: char(10) 存放‘a(chǎn)b’占倆個(gè)字符后面空著的8個(gè)字符用空格補(bǔ)全，所以存了2個(gè)字符也占10個(gè)字符的空間；

　　　　varchar2 變長(zhǎng) 最大是4000字符（查詢速度較慢，因?yàn)槭亲冮L(zhǎng)，查詢比較是是一位一位的比較）
　　　　　　demo：varchar2(10) 存放‘a(chǎn)b’,就占2個(gè)字符；

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

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

　　3.日期類型：
　　　　date 包括年月日和時(shí)分秒
　　　　timestamp 時(shí)間戳（毫秒級(jí)）
　　　　在oracle中默認(rèn)的日期格式是“DD-MON-YY” 如“01-5月-1992”，如果沒(méi)有月則添加不成功；
　　　　修改date的格式：

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

 

4.大數(shù)據(jù)（存放媒體）
　　　　blob 二進(jìn)制數(shù)據(jù) 可以存放圖片/聲音/視頻 最大是4G普通的存放媒體數(shù)據(jù)一般在數(shù)據(jù)庫(kù)中存放的是所放的文件夾路徑當(dāng)為了安全性時(shí)才會(huì)把媒體文件放在數(shù)據(jù)庫(kù)中；

3.oracle中創(chuàng)建表

1 sql>create table student( --創(chuàng)建名為student的數(shù)據(jù)庫(kù)表2 　　name varchar2(20),    --名字10個(gè)變長(zhǎng)3 　　idcar char(18),     --×××18個(gè)定長(zhǎng)字符4 　　sex char(2),     --性別2個(gè)定長(zhǎng)字符5 　　grade number(5,2)    --成績(jī)?yōu)楦↑c(diǎn)數(shù)，有效5位小數(shù)位為2位；6 )

 

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

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

5.修改已有字段的長(zhǎng)度

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

6.刪除表中的已有字段

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

7.表的重命名；

sql>rename student to std;

9.往表中插入數(shù)據(jù)：
　　1.省略字段名

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

　　2.給部分字段賦值

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

　　3.查詢idcard字段為空的學(xué)生

sql>select * from student where idcard is null;

 

10.修改表中的數(shù)據(jù)：

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

 

11.oracle中的回滾：（要養(yǎng)成創(chuàng)建保存點(diǎn)的習(xí)慣）--commit后所有的保存點(diǎn)都沒(méi)有了

　　1.回滾之前先創(chuàng)建保存點(diǎn)
　　　　sql>savepoint pointName;
　　2.刪除表中的記錄
　　　　sql>delete from student;;
　　3.回滾
　　　　sql>rollback to pointName;
　　　　truncate table student; --刪除表中的所有的數(shù)據(jù)，不寫(xiě)日志，無(wú)法回滾，刪除速度極快；

 

 

 

Oracle中的select語(yǔ)句的練習(xí)，這也是難點(diǎn)

1.emp表中的內(nèi)關(guān)聯(lián)查詢：給出每個(gè)雇員的名字以及他們經(jīng)理的名字, 使用表的別名；

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

　2.去除重復(fù)的行，重復(fù)的行的意思是行的每個(gè)字段都相同； distinct

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

　　3.查詢SMITH的薪水，職位和部門(mén)：

SQL> select emp.ename as 姓名,emp.sal as 薪水, emp.job as 工作,dept.dname as 部門(mén)2 from emp,dept where emp.ename='SMITH' and emp.deptno=dept.deptno;

姓名 薪水 工作 部門(mén)---------- --------- --------- --------------SMITH 800.00 CLERK RESEARCH

 

　　4.打開(kāi)顯示sql語(yǔ)句運(yùn)行時(shí)間　　

sql>set timing on;

 

　　5.查詢SMITH的年工資；--nvl 處理為null的字段，在表達(dá)式里如果有一個(gè)值為null則結(jié)果就為null用nvl()函數(shù)處理為空的字段，例如nvl(comm,0)：如果為null則用0替換；

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

 

　　6.模糊查詢like %代替多個(gè)字符，_代替一個(gè)字符；

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

 

　　7.or的升級(jí)in查詢

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

　　8.查詢工資高于500或者是崗位是manager同時(shí)名字以J開(kāi)頭的雇員

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

 

　　9.按照工資從低到高排序 order by語(yǔ)句； desc是降序（從高到低），asc是升序（從低到高 默認(rèn)）

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

　　10.按照部門(mén)號(hào)升序（asc）,員工號(hào)降序（desc）

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

 

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

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

 

數(shù)據(jù)的分組————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.查詢員工的總?cè)藬?shù)：

SQL> select count(*) from emp;

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

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

　　　　select 后面若有分組函數(shù)子可以跟分組函數(shù)

　　5.顯示工資高于平均工資的員工信息：

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

　　　

 

group by 和 having子句
　　group by 用于對(duì)查詢的結(jié)果進(jìn)行分組統(tǒng)計(jì)
　　having子句用于限制分組顯示結(jié)果

　　1.顯示每個(gè)部門(mén)的平均工資和最高工資；

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

　　2.顯示每個(gè)部門(mén)的每種崗位的平均工資和最高工資

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

 

　　3.顯示平均工資小于2000的部門(mén)號(hào)和他們的平均工資：

SQL> select emp.deptno,avg(sal) from emp group by emp.deptno having avg(sal)<2000;

DEPTNO AVG(SAL)------ ----------30 1566.66666

分組函數(shù)只能出現(xiàn)在選擇列表，having，order by子句中
如果select中同時(shí)有g(shù)roup by ,having ,order by 則三者的順序?yàn)間roup by ,having, order by;

 

 

多表查詢：
　　1.顯示雇員名，雇員工資，所在部門(mén)名稱；

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

2.顯示部門(mén)號(hào)為10的雇員名，雇員工資，所在部門(mén)名稱

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

 

3.顯示雇員名，雇員工資，工資的級(jí)別；

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

 

子查詢： SQL中執(zhí)行順序是從右到左執(zhí)行
　　1.查詢與SMITH在同一部門(mén)的所有員工；

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

 

2.查詢和部門(mén)10的工作相同的員工的信息

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

 

3.顯示工資比部門(mén)號(hào)為30的所有員工的工資都高的員工信息；（用 all() 或 max()實(shí)現(xiàn)）

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.顯示工資比部門(mén)號(hào)為30的一個(gè)員工的工資都高的員工信息；

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在同一部門(mén)并且職位也相同的員工信息；

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

 

    2.查詢員工比自己部門(mén)的平均工資高的員工信息；（把查詢出的信息當(dāng)作一張表起一個(gè)別名）

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中使用子查詢時(shí)查詢的結(jié)果會(huì)當(dāng)作一個(gè)視圖來(lái)對(duì)待，因此也叫做內(nèi)嵌視圖
必須給內(nèi)嵌視圖命一個(gè)別名

網(wǎng)頁(yè)題目：OracleDBA表管理
網(wǎng)站路徑：http://muchs.cn/article30/jejcpo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、關(guān)鍵詞優(yōu)化、建站公司、Google、手機(jī)網(wǎng)站建設(shè)、靜態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)