收藏
數(shù)據(jù)庫(kù)的設(shè)計(jì)與表的管理 oracle筆記
1.1oracle的表的管理
1.1.1表名和列的命名規(guī)則
1)表名必須以字母開頭
2)表名的長(zhǎng)度不能超過30
3)不能oracle關(guān)鍵字
1.1.2oracle支持的數(shù)據(jù)類型
1)字符型
char 最大是2000
例子 char(10) '小寒' 在數(shù)據(jù)庫(kù)中 '小寒 '
varchar2 最大時(shí)4000
例子 varchar2(10) '小寒' 在數(shù)據(jù)庫(kù)中 '小寒'
clob 字符型大對(duì)象 最大4g
2)數(shù)字型
number 范圍 -(10的38次方) 到 10的38次方
可以表示整數(shù)，也可以表示小數(shù)
number(5,2) 表示的 -999.99 到 999.99
numer(5) 表示一個(gè)五位數(shù)的一個(gè)整數(shù) -99999 ~ 999999
3)日期類型
date
timestamp
4)圖片
blob 二進(jìn)制數(shù)據(jù) (放入圖片或者是聲音文件 4g)
1.1.3管理表
1)建立表
create table student(
xh number(4), --學(xué)號(hào)
xm varchar2(50),--姓名
sex char(2),--性別
birthday date,--生日
sal number(7,2) --獎(jiǎng)金
);
2)如何察看表的結(jié)構(gòu)
desc 表名
3)如何給一個(gè)表添加字段
alter table student add (classId number(2));
4)修改字段的長(zhǎng)度
alter table student modify(xm varchar2(1000));
5)刪除字段
alter table student drop column sal;
6)刪除表
drop table student;
1.1.4如何操作數(shù)據(jù)表
(查詢!!!/添加/修改/刪除)
//加入一個(gè)數(shù)據(jù)
insert into student values(1,'MIKE','男','12-8月-67',12);
二 基本查詢
2.1簡(jiǎn)單的查詢語句
1)顯示某張表的結(jié)構(gòu)
desc 表名
2)查詢所有列
select * from emp;
3)查詢指定列
4)如何取消重復(fù)行
select distinct deptno,job from emp;
5)使用算術(shù)表達(dá)式
//如何計(jì)算年工資,每個(gè)雇員
nvl(comm,0):是oracle自己的一個(gè)函數(shù)
如果沒有獎(jiǎng)金 給0
如果有獎(jiǎng)金 ,就給comm
select ename, sal*12+nvl(comm,0)*12 年工資 from emp;
6)使用列的別名
//顯示年工資大于20000的所有員工
select ename, sal*12+nvl(comm,0)*12 from emp where sal*12+nvl(comm,0)*12>20000
7)如何處理null值
nvl(comm,0):是oracle自己的一個(gè)函數(shù)
如果沒有獎(jiǎng)金 給0
如果有獎(jiǎng)金 ,就給comm
2.2使用where子句
1)在where條件中使用數(shù)字
如何顯示工資高于3000的員工
select ename, sal from emp where sal>3000;
2)在where條件中使用字符值
注意: 1.要用單引號(hào)引住 2.區(qū)分大小寫
3)在where條件中使用日期值
注意: 1.要有單引號(hào)引住2.要符合日期的形式 否則要出錯(cuò)
請(qǐng)問如何查找1982.1.1后入職的員工
select * from emp where hiredate>'1-1月-82';
4)在where條件中使用between ... and
請(qǐng)問：如何顯示工資在2000到2500的員工情況
select * from emp where sal between 2000 and 2500;
5)在where條件中如何使用like操作符(模糊查詢)
%: 表示0到多個(gè)字符
_: 表示任意一個(gè)字符
請(qǐng)思考:1如何顯示首字符為S的員工姓名和工資
2如何顯示第三個(gè)字符為大寫O的所有員工的姓名和工資
select ename,sal from emp where ename like '__O%'
7)在where條件中使用in
如何顯示empno為 123,345,800,1250...的雇員情況
select * from emp where empno in(123,345,800,1250)
8)在where條件中使用is null的操作符
請(qǐng)思考:如何顯示沒有上級(jí)的雇員的情況
select * from emp where mgr is null;
9)在where條件中使用邏輯操作符號(hào)
and,or
請(qǐng)思考如何查詢工資高于2500或是崗位為MANAGER的雇員，同時(shí)還要滿足他們的姓名首寫字母為大寫的J
select * from emp where ename like 'J%' and (sal>2500 or job='MANAGER')
2.3使用order by字句
當(dāng)執(zhí)行查詢操作時(shí)，默認(rèn)情況下會(huì)按照行數(shù)據(jù)插入的先后順序來顯示數(shù)據(jù).但是在實(shí)際的應(yīng)用中常常需要對(duì)
數(shù)據(jù)進(jìn)行排序,可以使用order by
注意: 在select語句中可以包含多個(gè)子句(where , group by , having ,order by) 這是order by 必須是
最后一個(gè)子句
1)升序排列
請(qǐng)思考：如何按照工資的從低到高的順序顯示雇員的信息
select * from emp order by sal;
//從高到低(desc 從高到低 asc 低到高)
select * from emp order by sal desc;
3)使用多列排序
請(qǐng)思考如何按照部門號(hào)升序而雇員的工資降序排列
select * from emp order by deptno,sal desc;
4)使用列的別名排序
如果在where子句中為列或是表達(dá)式定義了別名，那么當(dāng)執(zhí)行排序操作時(shí)，即可以使用列或是表達(dá)式排序，
也可使用列別名排序
請(qǐng)思考如何按照年薪從低到高排序
5)使用列的編號(hào)進(jìn)行排序
6)查詢表中限定位置記錄
請(qǐng)大家思考一個(gè)問題,分頁(yè)..hehe
按雇員的id號(hào)升序取出
1)返回前5行數(shù)據(jù)
2)返回6到10行數(shù)據(jù)
3)返回最后5行數(shù)據(jù)
總結(jié) ;
1.
2.
3.
4.
三 復(fù)雜查詢
前面介紹了使用select語句執(zhí)行簡(jiǎn)單的select語句、查詢、排序、等。但是這些功能遠(yuǎn)遠(yuǎn)不能滿足實(shí)際應(yīng)用
的需要，在實(shí)際應(yīng)用中經(jīng)常需要執(zhí)行復(fù)雜的數(shù)據(jù)統(tǒng)計(jì)，經(jīng)常需要顯示多張表的數(shù)據(jù)，現(xiàn)在我們給大家介紹較為復(fù)雜的
select語句
3.1數(shù)據(jù)分組
1)分組函數(shù)
3.1.1max和min
請(qǐng)思考如何顯示所有員工中最高工資和最低工資
select max(sal),min(sal) from emp;
3.1.2avg和sum
請(qǐng)思考如何顯示所有員工的平均工資和工資總和
select avg(sal),sum(sal) from emp;
3.1.3count
請(qǐng)思考，如何得到共有多少員工
select count(*) from emp;
2)group by 和 having子句
group by用于對(duì)查詢的結(jié)果分組統(tǒng)計(jì),having子句用于限制分組顯示結(jié)果.
3.2.1 使用group by 進(jìn)行單列分組
請(qǐng)思考如何顯示每個(gè)部門的平均工資和最高工資
select deptno,avg(sal),max(sal) from emp group by deptno;
3.2.2 使用group by 進(jìn)行多列分組
多列分組是指基于兩個(gè)或是兩個(gè)以上的列生成分組統(tǒng)計(jì)結(jié)果.
請(qǐng)思考: 如何顯示每個(gè)部門的每種崗位的平均工資和最低工資
select deptno,job ,avg(sal),max(sal),min(sal) from emp group by deptno,job;
3.2.3 使用having 子句限制分組顯示結(jié)果
having 子句用于限制分組統(tǒng)計(jì)的結(jié)果,同時(shí)要求having必須要跟在group by子句后面
請(qǐng)思考: 如何顯示平均工資低于2000的部門號(hào)、平均工資、最高工資
select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;
我們這里對(duì)數(shù)據(jù)分組作一個(gè)小小的總結(jié):
1。分組函數(shù)只能出現(xiàn)在選擇列表、having、order by子句種
2. 如果在select 語句種同時(shí)包含有g(shù)roup by ,having ,order by 那么他們的順序是
group by , having , order by .
3。在選擇列種如果有列、表達(dá)式、和分組函數(shù),那么這些列和表達(dá)式必須有一個(gè)出現(xiàn)在group by 子句中，
否則就會(huì)出錯(cuò)
如select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;
這里deptno就一定要出現(xiàn)在 group by 中 呵呵
3)rollup 和 cube
在直接使用group by 執(zhí)行數(shù)據(jù)統(tǒng)計(jì)時(shí)，只會(huì)生成列的統(tǒng)計(jì)數(shù)據(jù),但是可能這樣還是不能滿足你的需求，你可
能希望產(chǎn)生橫向或是縱向的統(tǒng)計(jì)結(jié)果,這是可以使用rollup cube
思考:
3.2連接查詢
連接查詢是指基于兩個(gè)和兩個(gè)以上的表或是視圖的查詢.在實(shí)際應(yīng)用中,查詢單個(gè)表可能不能滿足你的需求,
（如顯示sales部門位置和其員工的姓名),這種情況下需要使用到連接查詢(dept表和emp表)
3.2.1相等連接
1)相等連接
請(qǐng)思考:顯示雇員名,雇員工資及所在部門的名字
select ename ,sal ,dname from emp ,dept where emp.deptno=dept.deptno
2）使用and指定其它條件
請(qǐng)思考:如何顯示部門號(hào)為10的部門名、員工名和工資
select ename ,sal ,dname from emp ,dept where emp.deptno=dept.deptno and dept.deptno=10
3.2.2不等連接
在salgrade這張表中存放了工資級(jí)別的信息
請(qǐng)思考如何顯示各個(gè)員工的姓名，工資，及其工資的級(jí)別
select ename,sal,grade from emp , salgrade where sal between losal and hisal
3.2.3自連接
自連接是指在同一張表的連接查詢。
請(qǐng)思考:如何顯示某個(gè)員工的上級(jí)領(lǐng)導(dǎo)的姓名
select manager.ename from emp manager,emp worker where manager.empno=worker.mgr and
worker.ename='BLAKE'
3.2.4內(nèi)連接和外連接//不作要求
3.3子查詢
子查詢是指嵌入在其它sql語句中的select語句,也叫嵌套查詢
3.3.1單行子查詢
單行子查詢是指只返回一行數(shù)據(jù)的子查詢語句
請(qǐng)思考：如何顯示與scott同一部門的所有員工
select ename,sal,deptno from emp where deptno=(select deptno from emp where ename='soctt');
3.3.2多行子查詢
多行子查詢指返回多行數(shù)據(jù)的子查詢
1)在多行子查詢中使用in操作符號(hào)
請(qǐng)思考:如何查詢匹配于部門10員工的崗位的雇員名、崗位、工資、部門號(hào)
select ename,job,sal,deptno from emp where job in (select distinct job from emp where
deptno=10);
2）在多行子查詢中使用all操作符
請(qǐng)思考:如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號(hào)
select ename,sal,dept from emp where sal>all (select sal from emp where deptno=30);
3）在多行子查詢中使用any操作符
請(qǐng)思考:如何顯示工資比部門30的任意一個(gè)員工的工資高的員工的姓名、工資和部門號(hào)
select ename,sal,dept from emp where sal>any (select sal from emp where deptno=30);
4)多列子查詢
單行子查詢是指子查詢只返回單列、單行數(shù)據(jù),多行子查詢是指返回單列多行數(shù)據(jù),都是針對(duì)單列而言的，而
多列子查序則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語句
請(qǐng)思考如何查詢于smith部門和崗位完全相同的所有雇員
select ename,job,sal,deptno from emp where (deptno,job)=(select deptno,job form emp where
ename='smith');
3.3.3其它子查訊
1)相關(guān)子查詢
在子查詢中使用exists謂詞來實(shí)現(xiàn)
請(qǐng)思考如何顯示工作在new york的員工
select ename,job,sal,deptno from emp where exists (select 1 from dept where
dept.deptno=emp.deptno and dept.loc='new york');
2)在from子句中使用子查詢
請(qǐng)思考：如何顯示高于部門平均工資的員工的信息
這里需要說明的當(dāng)在from子句中使用子查詢時(shí),該子查詢會(huì)被作為一個(gè)視圖來對(duì)待,因此頁(yè)叫作內(nèi)嵌視圖，當(dāng)
在from子句中使用子查訊時(shí)，必須給子查詢指定別名.
select ename,job,sal from emp ,(select deptno,avg(sal) avgsal from emp group by deptno) dept
where emp.deptno=dept.deptno and emp.sal>dept.avgsal;
3)在dml語句中使用子查詢(如insert ,update語句,delete)
請(qǐng)大家思考什么是dml語句(......) data manipulate language
請(qǐng)問如何將一張表的內(nèi)容（數(shù)據(jù)）裝載到另一張表中，這個(gè)是在dba工作中經(jīng)常遇到的事情
insert int employee (id,name,titel,salary) select empno,ename,job,sal from emp
請(qǐng)思考:如何刪除emp表中所有sales（銷售部門）的員工//比如該公司要撤銷銷售部門
delete from emp where deptno=(select deptno from dept where dname='sales');
4)在ddl語句中使用子查詢
出了可以在select,update,insert,delete語句中使用子查詢外，還可以在ddl（data definiton language)
中使用子查詢
1)如何創(chuàng)建一張新表
create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;
3.3.4合并查詢
有時(shí)在實(shí)際應(yīng)用中，為了合并多個(gè)select語句的結(jié)果，可以使用集合操作符號(hào) union , union
all,intersect , minus
1)union
該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中重復(fù)行,并且會(huì)以第一列進(jìn)
行排序后顯示結(jié)果集.
請(qǐng)思考: 如何顯示工資高于2500的員工和崗位是manager的員工(or)
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where
job='manager';
2)union all
該操作賦于union相似，但是它不會(huì)取消重復(fù)行,而且不會(huì)排序.
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where
job='manager';
3)intersect
使用該操作符用于取得兩個(gè)結(jié)果集的交集，并以第一列排序
請(qǐng)思考: 如何顯示工資高于2500的員工并且崗位是manager的員工(and)
select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where
job='manager';
4)minus
使用該操作符用于取得兩個(gè)結(jié)果集的查集，它只會(huì)顯示存在第一個(gè)集合中，而不存在第二個(gè)集合中的數(shù)據(jù).
并以第一列排序
請(qǐng)思考: 如何顯示工資高于2500的員工并且崗位不是manager的員工(and)
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='manager';
3.3.4其它復(fù)雜查詢
請(qǐng)思考; 如何查詢部門工資總和高于員工工資總和1/3的部門名及工資總和(兩次使用相同子查詢)
select dname,sum(sal) as dept_total from emp,dept where emp.deptno=dept.deptno group by dname
having sum(sal)>(select sum(sal)*1/3 from emp,dept where emp.deptno=dept.deptno)
請(qǐng)思考:在 sql語句中是否可以使用if...then...else的語法（case);
使用case 表達(dá)式時(shí),使用when指定條件語句.下面我給大家舉例說明
select ename,sal,case when sal>3000 then 3 when sal>2000 then 2 else 1 end grade from emp
where deptno=10;
========================================================================================
考慮給大家用eclipse+myeclipse--->struts工程
考慮用一個(gè)jdbc的案例去連接數(shù)據(jù)庫(kù)scott/ebook
二 oracle數(shù)據(jù)庫(kù)的創(chuàng)建
創(chuàng)建數(shù)據(jù)庫(kù)有兩種方法,一是通過oracle提供的向?qū)Чぞ?database configuration assistant)創(chuàng)建、修改
、刪除數(shù)據(jù)庫(kù);另外我們可以用手工步驟直接創(chuàng)建.
1)啟動(dòng) dbca
這里我們需要說明在運(yùn)行dbca時(shí)關(guān)閉其它的數(shù)據(jù)庫(kù).如演示的情況
2)選擇創(chuàng)建數(shù)據(jù)庫(kù)的類型:
2.1數(shù)據(jù)倉(cāng)庫(kù)
2.2通用數(shù)據(jù)庫(kù)
2.3新數(shù)據(jù)庫(kù)
2.4事務(wù)處理
3)確定數(shù)據(jù)庫(kù)庫(kù)的名字、數(shù)據(jù)庫(kù)實(shí)例、核心字符集.
這里我們需要給各位說明一下:數(shù)據(jù)庫(kù)是"database_name.database_domain"形式,比如mytest.us.acme.com
這里mytest是數(shù)據(jù)庫(kù)名，us.acme.com是數(shù)據(jù)庫(kù)所在的數(shù)據(jù)庫(kù)域,要求在同一個(gè)數(shù)據(jù)庫(kù)域中不能有相同的數(shù)據(jù)庫(kù)中.
這里需要說明的是數(shù)據(jù)庫(kù)名是一個(gè)長(zhǎng)度不超過8個(gè)字符的字符串,可以包含字母、數(shù)字、及其它字符。域部分
是一個(gè)長(zhǎng)度不超過128個(gè)字符的字符串，同樣也可以包含字母、數(shù)字、及其它字符..
4）確定數(shù)據(jù)庫(kù)網(wǎng)絡(luò)連接模式
4.1專用服務(wù)器模式
要求每個(gè)用戶進(jìn)程都有專用的服務(wù)器進(jìn)程。每增加一個(gè)客戶端，oracle服務(wù)器就自動(dòng)的增加一個(gè)服
務(wù)器進(jìn)程。
4.2共享服務(wù)器模式
共享服務(wù)器模式也叫多線程服務(wù)器體系結(jié)構(gòu)(multi_threaded server architecture mts),這種配
置允許大量用戶進(jìn)程共享少量服務(wù)器進(jìn)程的服務(wù)器，因此可以支持更多的用戶數(shù)量。在這種配置中，允許少量服務(wù)器
進(jìn)程對(duì)大量的客戶機(jī)提供服務(wù)。共享服務(wù)器模式與專用服務(wù)器相比，當(dāng)用戶數(shù)量增加是，mts減少了大量?jī)?nèi)存的使用
。調(diào)整和優(yōu)化全部系統(tǒng)性能，充分使用了網(wǎng)絡(luò)連接共享。
