oracle的sql語句
從查詢結果可以看出，所有的職位都被相應的中文替換了。
1.7、SQL語法練習
現(xiàn)在我們再次通過相應的練習對scott賬戶下的四張表做進一步的熟悉。
選擇部門30中的所有員工。
select * from emp where deptno=30;
此查詢包含一個限定條件
列出所有業(yè)務員的姓名，編號和部門編號。
select empno 編號,ename 姓名,deptno 部門編號 from emp where job='CLERK';
此查詢應用了別名，單個限定條件，需要注意的是Oracle是區(qū)分大小寫的，所以我們要將業(yè)務員大寫為“CLERK”才能查詢出數(shù)據(jù)，或者使用upper()函數(shù)。
select empno 編號,ename 姓名,deptno 部門編號 from emp where job=upper('clerk');
找出傭金高于工資的雇員信息
select * from emp where comm>sal;
此查詢?yōu)閱蝹�限定條件的查詢
找出傭金高于工資的60%的雇員信息
select * from emp where comm>sal*0.6;
此查詢使用了比較運算符和四則運算符
找出部門10中所有經(jīng)理和部門20中所有業(yè)務員的詳細信息。
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
此查詢使用了多限定查詢，邏輯運算符and 和 or ，并且使用()將多個限定條件包含作為一個整體看待。
看一個比較復雜的需求。找出部門10中所有經(jīng)理，部門20中所有業(yè)務員，既不是經(jīng)理又不是業(yè)務員但是工資大于或等于2000的所有雇員信息。
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK') or (sal>=2000 and job not in('MANAGER','CLERK'));
找出領取傭金的雇員的不用工作。這個需求包含的信息有，工作會重復復，所以我們需要使用關鍵字distinct消除重復的記錄，能夠領取傭金說明comm is not null
select distinct job from emp where comm is not null;
找出不能領取傭金或者領取的傭金低于100的雇員信息
select * from emp where comm is null or comm<100;
找出每個月中倒數(shù)第三天雇傭的雇員信息，我們分析下，Oracle為我們提供了一個叫做last_day()的函數(shù)，它的功能是查詢出指定日期的最后一天對應的日期，但是我們這里是要查詢倒數(shù)第三天，如何處理?我們按照倒序的思想，將最后一天減去2不就得到了倒數(shù)第三天嗎?
select * from emp where last_day(hiredate)-2=hiredate;
來一個很有趣的需求，找出早于12年前雇傭的雇員信息，我們將這個需求轉變下，我們知道Oracle中有一個函數(shù)叫做months_between()，它的作用是查詢出給定的日期的差值，這個差值是月份數(shù)，所以，我們這么考慮
select * from emp where months_between(sysdate,hiredate)/12>12;
看一個字符函數(shù)的應用。以首字母大寫的方式顯示所有雇員的姓名
select initcap(ename) from emp;
顯示雇員姓名長度正好是5的雇員姓名
select ename from emp where length(ename)=5;
顯示雇員姓名中不含“R”的雇員姓名
select ename from emp where ename not like '%R%';
此查詢使用了like語句做模糊查詢，當like后指定的關鍵字為“%%”時，表示查詢出所有數(shù)據(jù)
顯示所有雇員姓名的前三個字符
select substr(ename,0,3) from emp;
select substr(ename,1,3) from emp;
此處應該強調的是，截取點的指定中，0和1的效果是一樣的。
顯示所有雇員的姓名，并且用“a”替換所有的“A”
select ename,replace(ename,'A','a') from emp;
此處要強調的是replace()函數(shù)的參數(shù)中，第一個表示需要做替換操作的列名稱，第二個參數(shù)是新的字符串，第三個參數(shù)表示的是原始的字符串，也就是需要替換的字符串。
顯示工齡為10或以上的雇員姓名和雇傭日期
select ename,hiredate from emp where months_between(sysdate,hiredate)/12>10;
顯示雇員的詳細信息，并按照姓名排序
select * from emp order by ename;
顯示雇員的姓名和雇傭時間，根據(jù)其工齡，將最老的雇員排在最前面
select ename,hiredate from emp order by hiredate;
顯示所有雇員的姓名、工作和工資，按照工作降序排列，若工作相同則按照工資排序
select ename,job,sal from emp order by job desc,sal;
顯示所有雇員的姓名、雇傭的年份，月份，按照雇傭日期所在月排序，如果月份相同則將最早的年份的雇員排在最前面。此需求首先要求出所有雇員的雇傭月份，使用to_char()函數(shù)求出月份
select ename,to_char(hiredate,'mm') month,to_char(hiredate,'yyyy') year from emp order by month,year;
顯示在一個月為30天的情況下所有雇員的日薪，忽略余數(shù)。忽略余數(shù)使用round()函數(shù)完成
select ename,round(sal/30) from emp;
找出在每年2月份雇員的所有雇員信息
select * from emp where to_char(hiredate,'mm')=2;
此處還是使用了to_char()函數(shù)求出月份
對于每個雇員，顯示其到今天為止的總天數(shù)
select ename,round(sysdate-hiredate) from emp;
顯示雇員姓名中包含“A”的所有雇員姓名
select ename from emp where ename like '%A%';
以年月日的方式顯示所有雇員的工齡。年，求出總月數(shù)/12，此時會產(chǎn)生小數(shù)，但是我們不能再這里進行四舍五入，而是采用trunc()函數(shù)得到整數(shù)部分
select ename,trunc(months_between(sysdate,hiredate)/12) year from emp;
現(xiàn)在工齡的年得到了，下面求出月份，我們知道年除完之后的余數(shù)就是月，使用取余函數(shù)進行處理
select ename ,trunc(months_between(sysdate,hiredate)/12) year,trunc(mod(months_between(sysdate,hiredate),12)) month from emp;
得到月份后，如何求出天數(shù)呢?我們知道日期-日期得到的是天數(shù)(需要做取整處理)，將總天數(shù)/30(假設每月為30天)得到的就是剩余的天數(shù)值
select ename ,trunc(months_between(sysdate,hiredate)/12) year,trunc(mod(months_between(sysdate,hiredate),12)) month,trunc(mod(sysdate-hiredate,30)) day from emp;
這樣就完成了上面的查詢操作。
第三次
1、Oracle
1.1、多表查詢
1) 多表查詢的基本語法
前面的查詢都是針對一張表的操作，如果在查詢的時候涉及到多張表，那么就稱為多表查詢，奪標查詢的語法如下：
select *|具體的列名 from 表名稱1,表名稱2 where 條件表達式 order by 排序字段 asc|desc;
下面看一個例子，對emp表和dept表進行查詢
select * from emp,dept;
如此多的數(shù)據(jù)，我們要向知道當前的記錄總數(shù)，如何操作呢?
select count(*) from emp,dept;--56
select count(*) from emp;--14
select count(*) from emp;--4
此處查詢使用count(*|具體的列名)查詢總記錄數(shù)
上面的三條查詢語句分別得到了多表查詢，單表查詢的總記錄數(shù)，很明顯的看到，多表查詢的記錄數(shù)56并不等于單表查詢的總記錄數(shù)之和18，怎么回事呢?因為，在進行多表查詢時，會產(chǎn)生笛卡爾積，如果表的數(shù)據(jù)越多，那么笛卡爾積就會越大。如果現(xiàn)在有5張表，每張表有10000條數(shù)據(jù)，查詢5張表會產(chǎn)生10000的5次方條數(shù)據(jù)，所以在實際開發(fā)中多表查詢不建議過多的使用。
要向去掉笛卡爾積，可以使用關聯(lián)來實現(xiàn)�，F(xiàn)在我們知道emp表中有字段deptno，dept表中有字段deptno，emp.deptno=dept.deptno就是灌籃字段。在多表查詢中加入where語句就可以消除笛卡爾積
select * from emp,dept where emp.deptno=dept.deptno;
此時查詢的記錄數(shù)為14條，但是如果表名過長的話，不方便我們使用，所以一般我們會為表名設置別名，
select * from emp e,dept d where e.deptno=d.deptno;
如果在進行多表查詢時，最好為表名設置別名
要求查詢出雇員的編號、雇員姓名、部門編號、部門名稱及部門位置
select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
要求查詢出每個雇員的姓名，工作，雇員的直接上級領導的姓名
select e.ename,e.job,m.ename from emp e,emp m where e.mgr=m.empno;
此處查詢將emp表做自身的關聯(lián)
繼續(xù)擴展之前的程序，要求將雇員素在部門名稱同時列出
select e.ename,e.job,m.ename,d.dname from emp e,emp m,dept d where e.mgr=m.empno and e.deptno=d.deptno;
查詢出每個雇員的姓名，工資，部門名稱，工資等級，以及領導的姓名及工資所在公司的等級
先確定工資等級表
select * from salgrade;
在查詢出每個雇員的姓名，工資，部門名稱和工資等級
select e.ename,e.sal,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal;
查詢其領導的姓名及工資等級
select e.ename,e.sal,m.ename,decode(ms.grade,1,'第五等工資',2,'第四等工資',3,'第三等工資',4,'第二等工資',5,'第一等工資'),decode(s.grade,1,'第五等工資',2,'第四等工資',3,'第三等工資',4,'第二等工資',5,'第一等工資'),d.dname from emp e,emp m,dept d,salgrade s,salgrade ms where e.mgr=m.empno and m.sal between ms.losal and ms.hisal and e.deptno=d.deptno and e.sal between s.losal and s.hisal;
2) 左、右連接
現(xiàn)在我們先查詢下dept表中的數(shù)據(jù)
select * from dept;
可以看到，dept表中包含了4條記錄，現(xiàn)在我們將emp表和dept表關聯(lián)查詢，查詢一下指定的字段
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
有查詢結果可以看到，部門表中的部門號只出現(xiàn)了3個，因為在雇員表中沒有指定40部門的雇員，但是我們現(xiàn)在需要查看兩張表關聯(lián)后的完整信息，該如何進行呢?
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno;
現(xiàn)在的結果中沒有指定雇員的部門信息也顯示出來了。這里我們使用到了右連接。有如下規(guī)律：
(+)在=左邊表示右連接,查詢時以=右邊的表作為標準
(+)在=右邊表示左連接，查詢時以=左邊的表作為標準
在前面我們有過如下操作：查詢雇員的編號，姓名及其領導的編號、姓名
select e.empno,e.ename,m.ename from emp e,emp m where e.mgr=m.empno;
但是我們仔細觀察會發(fā)現(xiàn)，雇員名稱為KING的雇員信息沒有顯示出來，我們知道KING為president，沒有領導，所以按照上面的方式是不能查詢出來的，修改如下：
select e.empno,e.ename,m.ename from emp e,emp m where e.mgr=m.empno(+);
發(fā)現(xiàn)在加入做鏈接后KING出現(xiàn)了。
3) SQL:1999語法對SQL的支持
SQL:1999語法格式如下：
select table1.column,table2.column from table1
[cross join table2]|
[natural join table2]|
[join table2 using(column_name)]|
[join table2 on(table1.column_name=table2.column_name)]|
[left|right|full outer join table2 on(table1.column_name=table2.column_name)];
交叉連接(cross join)：產(chǎn)生笛卡爾積
select * from emp cross join dept;--56
自然連接(natural join)：自動進行關聯(lián)字段的匹配
select * from emp natural join dept;
相當于
select * from emp,dept where emp.deptno=dept.deptno;
using子句：直接關聯(lián)的操作列
select * from emp e join dept d using(deptno) where deptno=30;
on子句，用戶自己編寫連接的條件
select * from emp e join dept d on(e.deptno=d.deptno) where d.deptno=30;
左連接(左外連接)、右連接(右外連接)：left join,right join
select e.ename,d.deptno,d.dname,d.loc from emp e right outer join dept d on(e.deptno=d.deptno);
1.2、組函數(shù)及分組統(tǒng)計
什么是分組?
舉例吧，把所有男生分為一組，把所有女生分為一組。
如果想要求出每一組的平均身高，評價呢年齡等，就需要使用分組函數(shù)。
1) 組函數(shù)
在SQL中常用的組函數(shù)有以下幾個：
count()-->求出全部的記錄數(shù)
max()-->求出一組數(shù)據(jù)中的最大值
min()-->求出一組數(shù)據(jù)中的最小值
avg()-->求出平均值
sum()-->求和
count()函數(shù)：
現(xiàn)在我們需要查詢出emp中有多少個雇員
select count(*) from emp;--14
max()、min()，求最大最小值，一般是針對數(shù)字的應用
求出所有雇員的最低工資
select min(sal) from emp;
求出所有雇員的最高工資
select max(sal) from emp;
sum()、avg()，求和，求平均值
求出部門20中的總工資
select sum(sal) from emp where deptno=20;
求出所有雇員的平均工資
select avg(sal) from emp;
2) 分組統(tǒng)計
要想使用分組統(tǒng)計，則首先應該固定其語法，使用group by進行分組，此時SQL語法格式如下：
select *|具體的列
from 表名稱1
where 條件表達式
group by 分組條件
order by 排序字段 asc|desc
求出每個部門的雇員數(shù)量，這里需要按照部門編號劃分，及按照deptno分組
select deptno,count(empno) from emp group by deptno;
求出每個部門的平均工資
select deptno,avg(sal)
from emp
group by deptno;
現(xiàn)在，我們觀察下面的一行代碼：
select deptno,count(empno) from emp;
以上代碼不能正確執(zhí)行，報錯為：不是單組分組函數(shù)，為什么呢?
如果程序中使用了分組函數(shù)，則有兩種可以使用的情況：
1-程序中存在了group by，并指定了分組條件，這樣可以將分組條件一起查詢出來。
2-如果不使用分組的話，則只能單獨的使用分組函數(shù)
在使用分組函數(shù)的時候，不能出現(xiàn)分組函數(shù)和分組條件之外的字段。
看下面的代碼：
select deptno,empno,count(empno) from emp group by deptno;
程序會報錯，提示empno不是group by表達式，因為在這里，我們使用了組函數(shù)count(empno),group by deptno,根據(jù)先前的規(guī)則，empno的出現(xiàn)是不合法的。
按照部門分組，并顯示部門的名稱，及每個部門的員工數(shù)
select d.dname,count(e.ename) from dept d,emp e where d.deptno=e.deptno group by d.dname;
要求顯示出平均工資大于2000的部門編號和平均工資
select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;
程序報錯，提示avg(sal)>2000處不允許使用分組函數(shù)。因為分組函數(shù)只能在分組中使用，不允許出現(xiàn)在where語句之中，那么如果現(xiàn)在假設要指定分組的條件，則只能通過第二種條件的指令，having，此時SQL語法格式為：
select * | 具體的列名
from 表名稱
where 條件表達式
group by 分組條件
having 分組條件
order by 排序字段 asc|desc
所以，我們使用having完成上面的操作
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
下面我們看一個這樣的需求：顯示非銷售人員工作名稱以及從事同一工作的雇員的月工資的總和，并且要滿足從事同一工作的雇員的月工資合計大于5000，輸出結果按照月工資的合計升序排列
·-首先我們查詢出全部的非銷售人員，限定條件job<>'SALESMAN'
select * from emp where job<>'SALESMAN';
·-按照工作名稱分組，并且求出每組的工資總和
select job,sum(sal) from emp
where job<>'SALESMAN'
group by job;
·-對分組的調價進行限制，工資總和大于5000
select job,sum(sal) from emp
where job<>'SALESMAN'
group by job
having sum(sal)>5000;
·-對查詢結果按照月工資的合計升序排列
select job,sum(sal) from emp
where job<>'SALESMAN'
group by job
having sum(sal)>5000
order by sum(sal) asc;
下面我們總結下分組的簡單原則：
--只要一列上存在重復的內容才有可能考慮到分組
使用分組時的注意點：
--分組函數(shù)可以嵌套使用，但是在組函數(shù)嵌套使用的時候不能再出現(xiàn)分組條件的查詢語句
例如，我們要求求出平均工資最高的部門工資
select deptno,max(avg(sal)) from emp
group by deptno;
程序會報錯，提示deptno不是單組分組函數(shù)
修改代碼如下：
select max(avg(sal)) from emp
group by deptno;
1.3、子查詢
子查詢：在一個查詢的內部還包括另外一個查詢，則此查詢稱為子查詢，子查詢的格式如下：
select * | 具體的列名稱
from 表名稱
where 條件表達式(
select * | 具體的列名稱
from 表名稱
where 條件表達式(
...
)
group by 分組條件
having 分組條件
order by 排序字段 asc|desc
)
group by 分組條件
having 分組條件
order by 排序字段 asc|desc
要求查詢出比7654工資要高的全部雇員信息
·-首先要知道7654雇員的工資是多少
select sal from emp where empno=7654;
·-上面查詢的結果作為最后查詢的子查詢結果，只要是其他的工資大于上面的查詢結果，則表示符合條件。
select * from emp where sal>(select sal from emp where empno=7654);
應該要強調的是，所有的子查詢語句必須在“()”中編寫。
子查詢在操作上分為三類：
單列子查詢：返回的結果是某列的一個內容，出現(xiàn)的幾率最高
單行子查詢：返回多個列，有可能是一條完整的記錄
多行子查詢：返回多條記錄
要求查詢出工資比7654高，同時與7788從事相同工作的全部雇員信息
·-查詢出7654的工資
select sal from emp where empno=7654;
·-查詢出7788的工作名稱
select job from emp where empno=7788;
·-總和查找
select * from emp where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788);
要求查詢出工資最低的雇員姓名，工作，工資
·-求出最低工資
select min(sal) from emp;
·-以最低工資為條件進一步查詢
select ename,job,sal from emp
where sal=(select min(sal) from emp);
要求查詢出：部門名稱，部門的員工數(shù)，部門的平均工資，部門的最低收入的雇員姓名，此時，程序需要兩張表關聯(lián)：dept、emp
·-如果要想求出每個部門的員工數(shù)，平均工資，要使用分組統(tǒng)計，這里我們按照deptno進行分組
select deptno,count(empno),avg(sal) from emp
group by deptno;
·-但是我們要查詢的是部門的名稱，所以這里需要與dept表進行關聯(lián)
select d.dname,ed.c,ed.a
from dept d,
(select deptno,count(empno) c,avg(sal) a from emp
group by deptno) ed
where d.deptno=ed.deptno;
·-求出最低收入的雇員姓名
select d.dname,ed.c,ed.a,e.ename
from dept d,(select deptno,count(empno) c,avg(sal) a,min(sal) min from emp
group by deptno) ed,emp e
where d.deptno=ed.deptno and e.sal=ed.min;
但是此程序中有一個問題，如果一個部門中同時存在兩個給你工資最低的雇員，則程序會出現(xiàn)錯誤。
在子查詢中，存在以下三種查詢的操作符號：
in、any、all
in操作符的作用是指定一個查詢的范圍
求出每個部門的最低工資的雇員信息。
分析：每個部門的最低工資，返回值肯定是多個，所以此時可以使用in指定一個操作范圍。
select * from emp
where sal in(select min(sal) from emp group by deptno);
any操作符的一般用法：=any(與in操作符的功能完全一樣)、>any(比里面最小的值要大)、<any(比里面最大的值要小)
select * from emp
where sal=any(select min(sal) from emp group by deptno);
all操作符的一般用法：>all(比最大值要大)、<all(比最小值要小)
select * from emp
where sal>all(select min(sal) from emp group by deptno);
對于子查詢來講，還可以進行多列子查詢，一個子查詢中同時返回多個查詢的列。
select * from emp
where (sal,nvl(comm,-1)) in(select sal,nvl(comm,-1) from emp where deptno=20);
1.4、數(shù)據(jù)庫更新操作
數(shù)據(jù)庫的主要操作分為兩種：
--數(shù)據(jù)庫的查詢操作：select
--數(shù)據(jù)庫的更新操作：insert、update、delete
此時，為了保存原始的emp表的信息，在進行增加、修改、刪除操作之前，先將此表復制一份
create table myemp as select * from emp;
1) 添加數(shù)據(jù)
添加數(shù)據(jù)的語法是：
insert into 表名稱[(字段名1,字段名2,......)] values(值1,值2,......);
為myemp表添加一條新的記錄，按照標準的做法完成
insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7899,'張三','清潔工',7369,'14-2月 -1995',9000,300,40);
在添加數(shù)據(jù)時，需要強調的是：對于數(shù)字，不用加單引號，但是字符串必須加上單引號�？梢允褂煤喡詫懛�，當我們想全部的阻斷插入數(shù)據(jù)時，可以不用指定需要插入數(shù)據(jù)的字段名，但是，我們并不推薦這種寫法，因為這樣寫的壞處在于，當我們向部分字段插入數(shù)據(jù)時，需要和表的字段順序一一對一個才能插入成功，而使用標準寫法時，只需要指定的字段名和value中的值一一對應就可以完成插入操作。
插入部分數(shù)據(jù)，現(xiàn)在要求插入一個新的雇員，但是此雇員暫時沒有領導，也沒有獎金，也就是說，在插入數(shù)據(jù)的時候，mgr和comm字段的值要置空。
第一種做法：不明確寫出要插入的字段名 ，沒有數(shù)據(jù)的字段寫成null
insert into myemp values(8889,'王五','清潔工',null,'14-2月 -1982',9000,null,40);
第二種做法：明確寫出要插入的字段名
insert into myemp(empno,ename,job,hiredate,sal,deptno) values(8889,'王五','清潔工','14-2月 -1982',9000,40);
在上面的插入操作中，我們應該發(fā)現(xiàn)了這么一個問題，插入的日期都是按照Oracle默認的日期格式進行書寫的，但是，現(xiàn)在有一個“2009-01-19”這樣格式的日期，該如何插入到數(shù)據(jù)庫表中呢?我們英愛還記得在前面學習單行函數(shù)的時候，介紹了一個叫做to_date()的函數(shù)，該函數(shù)的功能是將一個字符串類型的數(shù)據(jù)變?yōu)閐ate類型的數(shù)據(jù)。
insert into myemp(empno,ename,job,hiredate,sal,deptno) values(8888,'趙六','保潔工',to_date('2009-01-19','yyyy-mm-dd'),9000,40);
2) 修改數(shù)據(jù)
在SQL語法中使用update語句就可以完成數(shù)據(jù)的修改功能，此語句的語法格式如下：
修改全部：update 表名稱 set 要修改的字段=新值,要修改的字段=新值,......;
修改局部：update 表名稱 set 要修改的字段=新值,要修改的字段=新值,...... where 修改條件;
但是，從一般的開發(fā)角度上講，我們都在修改數(shù)據(jù)時加上修改條件
現(xiàn)在將myemp表中的所有雇員的獎金修改為1000-->修改全部
update myemp set comm=1000;
將編號為7899的雇員的工資修改為5000-->指定了更新條件
update myemp set sal=5000 where empno=7899;
將編號為7899的雇員的領導取消
update myemp set mgr=null where empno=7899;
注意點：在進行數(shù)據(jù)庫更新的操作時，一定要寫上更新的條件，拒絕批量更新。
將7369、8899、7788的領導及獎金取消
update myemp set mgr=null,comm=null where empno in(7369,7788,8899);
3) 刪除數(shù)據(jù)
在SQL語法中可以使用delete命令刪除記錄，語法格式如下：
刪除全部：delete from 表名稱;
刪除局部：delete from 表名稱 where 刪除條件;
刪除編號是7899的雇員信息
delete from myemp where empno=7899;
刪除編號是8889,7889,8888的雇員信息
delete from myemp where empno in(8889,7889,8888);
刪除掉全部領取獎金的雇員
delete from myemp where comm is not null;
刪除表的全部內容，此時不需要指定刪除條件
delete from myemp;
在實際開發(fā)中不建議使用全部刪除，在執(zhí)行刪除命令的時候都要指定刪除條件。
1.5、事務處理
創(chuàng)建一個只包含10部門雇員信息的臨時表
create table emp10 as select * from emp where deptno=10;
刪除emp10中7782的雇員信息
delete from emp10 where empno=7782;
當我們再次查詢emp10表的數(shù)據(jù)時，該數(shù)據(jù)確實刪除了，接下來，我們做如下操作，再次開啟一個sqlplus窗口，再次查詢emp10的數(shù)據(jù)，我們發(fā)現(xiàn)雇員編號為7782的雇員信息仍然存在，這是為什么?
這就是Oracle中事務處理的概念了。
事務處理：所謂事務處理就是保證數(shù)據(jù)的完整性，所有的操作要么同時成功，要么同時失敗。
在Oracle中對于每一個連接到數(shù)據(jù)庫的窗口(sqlplus、sqlplusw)連接之后實際上都會與數(shù)據(jù)庫建立一個session，即：每一個連接到數(shù)據(jù)庫上的用戶表示創(chuàng)建了一個session。
一個session對數(shù)據(jù)庫所做的修改，不會立刻反映到數(shù)據(jù)庫的真實數(shù)據(jù)之上，是允許回滾的，當一個session提交所有的操作之后，數(shù)據(jù)庫才真正的做出了修改。
在數(shù)據(jù)庫的操作中提供了以下兩個主要命令完成事物的處理：
--提交事物：commit
--回滾事物：rollback
如果數(shù)據(jù)已經(jīng)被提交了則肯定無法回滾，所以，回滾只有在事物未被提交時才有效。
在Oracle中關于事物的處理上夜壺存在一種死鎖的概念。
一個session如果更新了數(shù)據(jù)庫中的記錄，其他session是無法立刻更新的，要等待對方提交之后才允許更新。
下面我們來測試下Oracle的事務處理是如何進行的。
首先，我們在窗口1中查詢出emp10的數(shù)據(jù)
select * from emp10;
現(xiàn)在做一個更新的操作，將編號為7934的雇員的工資更改為3000
update emp10 set sal=3000 where empno=7934;
現(xiàn)在我們再次查詢emp10的數(shù)據(jù)，發(fā)現(xiàn)，編號為7934的雇員工資確實更改為3000來 ，但是我們應該知道，對于這個更新操作，我們是沒有提交的，我們現(xiàn)在再打開一個窗口，查詢emp10的數(shù)據(jù)，發(fā)現(xiàn)編號為7934的雇員工資還是1300，這就驗證了我們所說的“事物未提交”，接下來，我們在窗口2中進行一個更新操作，將7839的獎金設置為10000
update emp10 set comm=10000 where empno=7839;
下面我們在窗口1中提交事物
在窗口2中再次查詢emp10的數(shù)據(jù)，更新成功。
在窗口2中提交事物
同樣在窗口1中查詢emp10的數(shù)據(jù)，更新成功。
1.6、查詢練習
列出至少有一個員工的所有部門
select deptno ,count(empno) from emp
group by deptno;
此查詢使用了組函數(shù)，分組，注意，如果不加分組，該程序會報錯
列出部門人數(shù)大于1的所有部門編號
select deptno,count(empno) from emp
group by deptno having count(empno)>1;
這里沒使用where設置查詢限定條件，因為where子句中時不能出現(xiàn)函數(shù)的。
通過部門表，查詢出部門的信息
select d.*,ed.cou from dept d,(select deptno,count(empno) cou from emp
group by deptno) ed
where d.deptno=ed.deptno;
列出工資比“SMITH”多的所有雇員。
--求出SMITH的工資
select sal from emp where ename='SMITH';
--將上面的結果作為查詢條件，只要工資比上面的結果大的就是符合條件的
select * from emp where sal>(select sal from emp where ename='SMITH');
列出所有員工的姓名和其直接上司的姓名
--此程序屬于自身關聯(lián)查詢，為了得到KING，我們使用左連接查詢，以等號左邊的表為標準
select e.ename,m.ename from emp e,emp m
where e.mgr=m.empno(+);
列出雇傭日期早于其直接上級的所有雇員的編號、姓名和部門名稱
--自身關聯(lián)，查找mgr=empno的同時還要比較hiredate，我們先查詢編號和姓名
select e.empno,e.ename
from emp e,emp m
where e.mgr=m.empno and e.hiredate<m.hiredate;
--如果要加入部門名稱，我們需要加入dept表，做表關聯(lián)查詢
select e.empno,e.ename,d.dname
from emp e,emp m,dept d
where e.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;
列出部門名稱和這些部門的雇員信息，同時列出那些沒有雇員的部門
--左右關聯(lián)問題，以=右邊的表為標準
select d.deptno,d.dname,e.empno,e.ename
from dept d,emp e
where d.deptno=e.deptno(+);
列出所有“CLERK”的姓名和其部門名稱，部門的人數(shù)
--找出所有CLERK的姓名和部門編號
select ename,deptno from emp
where job='CLERK';
--要想查詢部門名稱，則必須使用dept表
select e.ename,d.dname from emp e,dept d
where job='CLERK' and e.deptno=d.deptno;
--部門人數(shù)要用到分組完成，一旦使用分組，肯定是group by
select e.ename,d.dname,ed.cou
from emp e,dept d,(select deptno, count(empno) cou from emp group by deptno) ed
where job='CLERK' and e.deptno=d.deptno and ed.deptno=e.deptno;
列出最低工資大于1500的各種工作以及從事此工作的全部雇員人數(shù)
--按工作分組，分組條件是最低工資大于1500
select job,min(sal) from emp
group by job having min(sal)>1500;
--工作就出來了，之后再求全部的雇傭人數(shù)
select e.job,count(e.empno)
from emp e
where e.job in(
select job from emp
group by job having min(sal)>1500
)
group by e.job;
列出在部門“SALES”工作的員工姓名，假定不知道銷售部的部門編號
--通過dept表查詢出銷售部的部門編號
select deptno from dept where dname='SALES';
--將上面的查詢結果作為下一步查詢的條件
select ename from emp
where deptno=(
select deptno from dept where dname='SALES'
);
列出工資高于公司平均工資的所愿雇員，所在部門，上級領導，公司的工資等級
--求出公司的平均工資
select avg(sal) from emp;
--列出工資高于平均工資的雇員信息
select * from emp where sal>(select avg(sal) from emp);
--與部門表關聯(lián)，查出所在部門的信息
select e.*,d.dname,d.loc from emp e,dept d
where sal>(select avg(sal) from emp) and e.deptno=d.deptno;
--要想查出上級領導，需要和自身進行關聯(lián)查詢
select e.empno,e.ename,m.ename,m.empno,d.deptno,d.dname,d.loc
from emp e,dept d,emp m
where e.sal>(select avg(sal) from emp)
and e.deptno=d.deptno
and e.mgr=m.empno(+);
--與工資等級表關聯(lián)，查出工資等級
select e.empno,e.ename,s.grade,m.ename,m.empno,d.deptno,d.dname,d.loc
from emp e,dept d,emp m,salgrade s
where e.sal>(select avg(sal) from emp)
and e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.sal between s.losal and s.hisal;
列出與scott從事相同工作的所有雇員及部門名稱
--找到scott的工作
select job from emp where ename='SCOTT';
--找到和上面查詢工作相同的雇員
select * from emp where job=(select job from emp where ename='SCOTT');
--使用dept表查詢出所在部門名稱
select e.*,d.dname from emp e,dept d
where job=(select job from emp where ename='SCOTT')
and ename!='SCOTT'
and d.deptno=e.deptno;
列出工資等于部門30中雇員的工資的所有雇員姓名和工資
--查出部門30中雇員的工資
select sal from emp where deptno=30;
--找出工資等于上面結果的雇員姓名
select ename,sal from emp
where sal in(select sal from emp where deptno=30)
and deptno!=30;
列出工資高于在30部門工作的所有雇員的工資的雇員姓名和工資，部門名稱
--在之前的程序上進行修改，使用>all，比最大的還要大
select ename,sal
from emp where sal>all(
select sal from emp where deptno=30
)
and deptno!=30;
--使用dept表，查詢出部門名稱
select e.ename,e.sal,d.dname from emp e,dept d
where sal>all(
select sal from emp where deptno=30
)
and e.deptno!=30
and e.deptno=d.deptno;
列出在每個部門工作的雇員數(shù)量，平均工資和平均工齡
--求出每個部門的雇員數(shù)量，按照部門名稱分組
select d.dname,count(e.empno)
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
--求出每個部門的平均工資和工齡
select d.dname,count(e.empno),avg(e.sal),avg(months_between(sysdate,hiredate)/12) 年
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
列出所有雇員的姓名、部門名稱和工資
--直接兩張表關聯(lián)
select e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno;
列出所有部門的詳細信息和部門的人數(shù)
--列出每個部門的雇員人數(shù)
select deptno,count(empno) cou
from emp
group by deptno;
--把以上的查詢作為一張臨時表
select d.*,ed.cou from dept d,
(select deptno dto,count(empno) cou from emp group by deptno ) ed
where d.deptno=ed.dto;
查詢結果中沒包含40部門，修改如下
select d.*,nvl(ed.cou,0) from dept d,
(select deptno dto,count(empno) cou from emp group by deptno ) ed
where d.deptno=ed.dto(+);
列出各種工作的最低工資及從事此工作的雇員姓名
--按照工作分組，使用min()求出最低工資
select job,min(sal) from emp group by job;
--按照工資查詢出雇員的信息
select * from emp
where sal in(select min(sal) from emp group by job);
列出各個部門的MANAGER的最低工資
select deptno,min(sal)
from emp
where job='MANAGER'
group by deptno;
列出所有雇員的年薪，按照年薪從低到高排序
select ename,(sal+nvl(comm,0))*12 yearsal from emp order by yearsal asc;
查詢某個雇員的上級主管，并求出這些主管中的薪水超過3000的
select distinct m.* from emp e,emp m
where e.mgr=m.empno and m.sal>3000;
求出部門名稱中帶“S”的部門雇員的工資合計和部門人數(shù)
--查詢部門表的部門名稱，使用模糊查詢，來確定部門的編號
select deptno from dept where dname like '%S%';
--查詢出符合上述條件的雇員工資合計和部門人數(shù)
select deptno,sum(sal),count(empno) from emp
where deptno in(select deptno from dept where dname like '%S%')
group by deptno;
第四次
1、Oracle
1.1、 創(chuàng)建和管理表
1) 常用的數(shù)據(jù)類型
varchar\varchar2-->表示的是一個字符串，有長度限制，255,
number-->number(n):表示一個整數(shù)，數(shù)字的長度是n，可以使用int
number(m,n):表示一個小數(shù)，數(shù)字小數(shù)長度為n，整數(shù)長度為m-n，可以使用float
date-->表示日期的類型，日期要按照標準的日期格式進行存放
clob-->大對象，表示大文本數(shù)據(jù)，一般可以存放4G的文本
blob-->大對象，表示二進制數(shù)據(jù)，最大可以存放4G，如：電影，歌曲，圖片
2) 表的建立
表的建立還是按照標準的語法進行，但是在表的建立時有時候會指定約束，那么此處先給出一個建立表的簡單語法。
create table 表名稱(
字段名稱1 字段類型 [default 默認值],
字段名稱2 字段類型 [default 默認值],
....
字段名稱n 字段類型 [default 默認值]
)
在前面我們使用了一種這樣的語法來創(chuàng)建表：
create table 表名稱 as (子查詢)--將子查詢的結果作為一張表
如果現(xiàn)在子查詢寫的是：select * from emp;表示將表結構和表的內容一起復制
如果現(xiàn)在子查詢寫的是：select * from emp where 1=2;加入了一個永遠都不會成立的條件，則此時表示我們復制的只是表的結構，不復制表的內容
復制表結構：
create table temp as(select * from emp where 1=2);
3) 表的刪除
表的刪除語法如下：
drop table 表名稱;
4) 表的修改
在SQL語法操作中，提供了alter指令，通過alter指令就可以增加新的列
為emp表添加一個address列
alter table emp add(address varchar2(200) default'暫無地址');
修改emp表中的ename，將長度改為50
alter table emp modify(ename varchar2(50));
5) 為表重命名
在Oracle中提供了rename命令，可以為表重新進行命名，但是此語句只能在Oracle中使用。語法格式如下：
rename 舊的表名稱 to 新的表名稱;
6) 截斷表
如果現(xiàn)在我們需要清空一張表的數(shù)據(jù)，但是同時不需要回滾，可以立刻釋放資源就需要使用截斷表的語法：
truncate table 表名稱;
思考下面的問題：現(xiàn)在有一張國家表，里面只有一個國家名稱的字段，內容如下：“中國、美國、巴西、荷蘭“，現(xiàn)在要求通過查詢實現(xiàn)對戰(zhàn)功能：
中國->美國
中國->巴西
中國->荷蘭
美國->中國
美國->巴西
美國->荷蘭
......
分析：本程序只能使用笛卡爾積完成
首先，建立一張表
create table national(
name varchar2(30)
)
向表中增加測試數(shù)據(jù)
insert into national(name) values('中國');
insert into national(name) values('美國');
insert into national(name) values('巴西');
insert into national(name) values('荷蘭');
查詢的時候表自己和自己關聯(lián)
select n1.name,n2.name from national n1,national n2 where n1.name<>n2.name;
1.2、約束
在數(shù)據(jù)庫表的開發(fā)中，余數(shù)是必不可少的支持。使用約束可以更好的保證數(shù)據(jù)庫中的數(shù)據(jù)完整性。
數(shù)據(jù)庫中的約束分類：
--在實際中，約束主要分為以下五種：
···主鍵約束primary key：主鍵表示是一個唯一的標識，本身是不能為空的
|-例如：身份證號是唯一的，不可重復，不可為空
···唯一約束unique：在一個表中只允許建立一個主鍵約束，而其他列如果不希望出現(xiàn)重復值的話，則可以使用唯一約束。
···檢查約束：檢查一個列的內容是否合法
|-例如：年齡。只能在0~150之間
|-例如：性別，只能是男、女、中性
···非空約束：姓名這樣的字段里面的內容就不能為空
···外鍵約束：在兩張表中進行約束操作。
1) 主鍵約束(primary key)
主鍵約束一般都是使用在id上，而且本身已經(jīng)默認了內容不能為空，主鍵約束可以再建表的時候指定
現(xiàn)在我們建立一張person表，在pid上增加主鍵約束
drop table person;
create table person(
pid varchar2(18) primary key,
name varchar2(200),
age number(3),
birthday date,
sex varchar2(3) default '男'
)
現(xiàn)在我們向表中插入數(shù)據(jù)
insert into person(pid,name,age,birthday,sex) values('1111111111111111','張三',30,to_date('1976-08-09','yyyy-mm-dd'),'女');
insert into person(pid,name,age,birthday,sex) values('1111111111111111','李四',30,to_date('1976-08-04','yyyy-mm-dd'),'男');
當插入第二條語句時，會提示：違反唯一約束，那么我們將pid的值設置為null
insert into person(pid,name,age,birthday,sex) values(null,'李四',30,to_date('1976-08-04','yyyy-mm-dd'),'男');
同樣會提示錯誤：無法將 NULL 插入 ("SCOTT"."PERSON"."PID")，以上的約束是系統(tǒng)自動分配好的約束名稱，也可以通過constraint指定一個約束的名字，
將person表的pid指定名稱
drop table person;
create table person(
pid varchar2(18),
name varchar2(200),
age number(3),
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid)
)
2) 非空約束(not null)
使用非空約束，表示一個字段的內容不允許為空，即：插入數(shù)據(jù)的時候必須插入內容
drop table person;
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null,
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid)
)
3) 唯一約束(unique)
表示一個字段中的內容是唯一的，其他列不允許重復。
假設：現(xiàn)在姓名不允許出現(xiàn)重名的情況
drop table person;
create table person(
pid varchar2(18),
name varchar2(200) unique not null,
age number(3) not null,
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid)
)
4) 檢查約束(check)
使用檢查約束來判斷一個列中插入的內容是否合法，例如，年齡的取值范圍，性別的取值范圍
drop table person;
create table person(
pid varchar2(18),
name varchar2(200) unique not null,
age number(3) not null check(age between 0 and 150),
birthday date,
sex varchar2(3) default '男' check(sex in('男','女','中')),
constraint person_pid_pk primary key(pid)
)
5) 主-外鍵約束(foreign key)
之前的約束都是針對一張表的，那么主-外鍵約束是針對兩張表的約束。為什么需要主-外鍵約束呢?
要求完成一個程序，一本書只屬于一個人
書本身應該是一張表，一本書中必然有一個字段表示屬于某個人的
drop table person;
drop table book;
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null,
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid),
constraint person_name_uk unique(name),
constraint person_age_ck check(age between 0 and 150),
constraint person_sex_ck check(sex in('男','女','中'))
);
create table book(
bid number primary key not null,
bname varchar2(20),
bprice number(5,2),
pid varchar2(18)
);
insert into person(pid,name,age,birthday,sex) values('1111111111111111','張三',30,to_date('1976-08-09','yyyy-mm-dd'),'女');
insert into book(bid,bname,bprice,pid) values(1,'JAVA SE',89.9,'0000000000000');
在插入第二條數(shù)據(jù)前，我們看看pid字段的值，很明顯，在我們的person表中不存在這樣的person，那么， 這樣的數(shù)據(jù)時不應該插入到數(shù)據(jù)庫中的，為了解決這樣的問題，我們使用主-外鍵關聯(lián)，關聯(lián)之后字表的數(shù)據(jù)要跟隨父表的數(shù)據(jù)內內容。
drop table person;
drop table book;
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null,
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid),
constraint person_name_uk unique(name),
constraint person_age_ck check(age between 0 and 150),
constraint person_sex_ck check(sex in('男','女','中'))
);
create table book(
bid number primary key not null,
bname varchar2(20),
bprice number(5,2),
pid varchar2(18),
constraint person_book_pid_fk foreign key(pid) references person(pid)
);
現(xiàn)在我們再次執(zhí)行數(shù)據(jù)的插入操作，此時系統(tǒng)提示：違反完整約束條件 (SCOTT.PERSON_BOOK_PID_FK) - 未找到父項關鍵字，根據(jù)上面的分析沒我們修改如下：
insert into book(bid,bname,bprice,pid) values(1,'JAVA SE',89.9,'1111111111111111');
此時插入數(shù)據(jù)成功。
在使用主-外鍵關聯(lián)的時候有幾點需要注意：
|-在子表中設置的外鍵在父表中必須是主鍵
|-刪除時應該先刪除子表，再刪除父表
在主-外鍵關聯(lián)中也可以使用級聯(lián)刪除
以現(xiàn)有數(shù)據(jù)庫中的數(shù)據(jù)為例
delete from person where pid='1111111111111111';
要刪除person表中編號為1111111111111111的人員，但是這個人在book表中存在一本書的記錄。提示錯誤：違反完整約束條件 (SCOTT.PERSON_BOOK_PID_FK) - 已找到子記錄，那么，如果想刪除成功必須先刪除book表中pid對應的記錄，再刪除此人的信息
如果我們希望一個表中的數(shù)據(jù)在刪除時，可以自動刪除掉其對應的子表記錄，則可以使用級聯(lián)刪除來實現(xiàn)。
drop table person;
drop table book;
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null,
birthday date,
sex varchar2(3) default '男',
constraint person_pid_pk primary key(pid),
constraint person_name_uk unique(name),
constraint person_age_ck check(age between 0 and 150),
constraint person_sex_ck check(sex in('男','女','中'))
);
create table book(
bid number primary key not null,
bname varchar2(20),
bprice number(5,2),
pid varchar2(18),
constraint person_book_pid_fk foreign key(pid) references person(pid) on delete cascade
);
6) 修改約束
如果一張表已經(jīng)建立完成，則可以為其添加約束
關于約束類型的命名，一定要統(tǒng)一：
--primary key-->主鍵字段_pk
--unique-->字段_uk
--check-->字段_ck
--foreign key-->父字段_子字段_fk
為person添加一個約束
alter table person add constraint person_pid_pk primary key(pid);
將person的主鍵約束刪除掉該怎么操作呢?
alter table person drop constraint person_pid_pk;
1.3、rownum
rownum：表示行號，實際上這是一個列的列名，但是這個列我們稱為偽列，此列尅在每張表中出現(xiàn)。
例如，在我們查詢雇員表的時候，加上rownum這個列名稱
select rownum,empno,ename,job,sal,hiredate from emp;
從執(zhí)行的效果來看，rownum本身采用自動編號的形式出現(xiàn)。
我們擴展下rownum的應用，現(xiàn)在我們只想顯示前5條雇員信息，該如何實現(xiàn)呢?
select rownum,empno,ename,job,sal,hiredate from emp where rownum<=5;
既然可以查詢前5條數(shù)據(jù)，那么，我們現(xiàn)在要求提高了，查詢中間的5條數(shù)據(jù)
select rownum,empno,ename,job,sal,hiredate from emp where rownum between 5 and 10;
看似沒有問題的語句卻查不出數(shù)據(jù)來，到底哪里出錯了呢?
如果現(xiàn)在要想進行中間的截取操作，則只能采用子查詢，例如現(xiàn)在假設每頁顯示5條，第二頁應該顯示6~10條，那么對于數(shù)據(jù)庫操作來講，它在查詢的時候應該首先查詢出1~10條，之后再在查詢的結果中截取出后5條。
select * from (select rownum m,empno,ename,job,sal,hiredate from emp where rownum<=10) temp where temp.m>5;
如果現(xiàn)在要求輸出最后的4條呢?
select * from (select rownum m,empno,ename,job,sal,hiredate from emp where rownum<=15) temp where temp.m>10;
1.4、集合操作
在Oracle中提供了三種類型集合的操作，并(union)、交(intersect)、差(minus)
|-union：將多個查詢的結果組合到一個查詢結果中，沒有重復的內容
|-union all：將多個查詢結果組合到一個查詢之中，但是包含了重復值
|-intersect：返回多個查詢結果中相同的部分
|-minus：返回兩個查詢結果的差集
為了更好的觀察查詢結果，我們將復制emp表，將20部門的雇員信息取出來
create table emp20 as select * from emp where deptno=20;
1) 驗證union
返回兩個集合的所有內容，不包含重負的內容
select * from emp
union
select * from emp20;
2) 驗證union all
返回兩個集合的所有內容，包含重復內容
select * from emp
union all
select * from emp20;
3) 驗證intersect
返回多個查詢結果中相同的部分
select * from emp
intersect
select * from emp20;
因為兩張表中只有20部門的雇員信息是重復的，所有實際上返回的相同內容就是表emp20的內容
4) 驗證minus
返回兩個查詢結果的差集
select * from emp
minus
select * from emp20;
1.5、交表、約束、查詢綜合練習
題目背景：
有某個學生運動會比賽信息的數(shù)據(jù)庫，保存了如下的表：
|-運動員sporter(運動員編號sporterid，運動員姓名name，運動員性別sex，所屬系號department)
|-項目item(項目編號itemid，項目名稱itemname，項目比賽地點location)
|-成績grade(運動員編號sporterid，項目編號itemid，積分mark)
功能要求
1) 建表
--定義各個表的主碼外碼約束
--運動員的姓名和所屬系別不能為空值
--積分要么為空值，要么為6、4、2、0，分別代表第一、第二、第三和其他名次的積分
create table sporter(
sporterid number(4) primary key not null,
name varchar2(50) not null,
sex varchar2(3) not null,
department varchar2(30) not null,
constraint sporter_sex_ck check(sex in('男','女'))
);
create table item(
itemid varchar2(4) primary key not null,
itemname varchar2(50) not null,
location varchar2(50) not null
);
create table grade(
sporterid number(4),
itemid varchar2(4),
mark number(2),
constraint sporter_grade_sporterid_fk foreign key(sporterid) references sporter(sporterid) on delete cascade,
constraint sporter_item_itemid_fk foreign key(itemid) references item(itemid) on delete cascade,
constraint grade_mark_ck check(mark in(6,4,2,0))
);
2) 數(shù)據(jù)
運動員sporter
insert into sporter(sporterid,name,sex,department) values(1001,'李明','男','計算機系');
insert into sporter(sporterid,name,sex,department) values(1002,'張三','男','數(shù)學系');
insert into sporter(sporterid,name,sex,department) values(1003,'李四','男','計算機系');
insert into sporter(sporterid,name,sex,department) values(1004,'王二','男','物理系');
insert into sporter(sporterid,name,sex,department) values(1005,'李娜','女','心理系');
insert into sporter(sporterid,name,sex,department) values(1006,'孫儷','女','數(shù)學系');
項目item
insert into item(itemid,itemname,location) values('x001','男子五千米','一操場');
insert into item(itemid,itemname,location) values('x002','男子標槍','一操場');
insert into item(itemid,itemname,location) values('x003','男子跳遠','二操場');
insert into item(itemid,itemname,location) values('x004','女子跳高','二操場');
insert into item(itemid,itemname,location) values('x005','女子三千米','三操場');
積分grade
insert into grade(sporterid,itemid,mark) values(1001,'x001',6);
insert into grade(sporterid,itemid,mark) values(1002,'x001',4);
insert into grade(sporterid,itemid,mark) values(1003,'x001',2);
insert into grade(sporterid,itemid,mark) values(1004,'x001',0);
insert into grade(sporterid,itemid,mark) values(1001,'x003',4);
insert into grade(sporterid,itemid,mark) values(1002,'x003',6);
insert into grade(sporterid,itemid,mark) values(1004,'x003',2);
insert into grade(sporterid,itemid,mark) values(1005,'x004',6);
insert into grade(sporterid,itemid,mark) values(1006,'x004',4);
3) 要求
求出目前總積分最高的系名，及其積分
--所有的系名都在sporter表中，而積分在grade表中，所以sporter和grade進行關聯(lián)查詢
select s.department,sum(g.mark) sum
from sporter s,grade g
where s.sporterid=g.sporterid
group by s.department
order by sum desc;
--使用rownum最方便
select * from(
select s.department,sum(g.mark) sum
from sporter s,grade g
where s.sporterid=g.sporterid
group by s.department
order by sum desc)
where rownum=1;
第五次
1、Oracle數(shù)據(jù)庫
1.1、視圖
視圖的功能：一個視圖實際上就是封裝了一條復雜的查詢語句
創(chuàng)建視圖的語法如下：
create view 視圖名稱 as 子查詢
|-實際上此時的子查詢就表示一條非常復雜的查詢語句
建立一個視圖：此視圖包含了全部的20部門的雇員信息(雇員編號，姓名，工作，雇傭日期)
create view empv20 as select empno,ename,job,hiredate from emp where deptno=20;
視圖創(chuàng)建完成之后，就可以像查找表那樣直接對視圖進行查詢的操作了。
select * from empv20;
此時，我們通過視圖查詢出20部門的雇員信息，也就是，可以使用視圖包裝的查詢語句完成我們的操作。但是，我們思考下，現(xiàn)在這個視圖中同樣只包含了4個字段的信息，如果，現(xiàn)在希望多包含一個字段呢?
create view empv20 as select empno,ename,job,sal,hiredate from emp where deptno=20;
此時，系統(tǒng)會報錯，名稱已有現(xiàn)有對象使用。也就是說，該名稱的視圖已經(jīng)存在了，所以，在創(chuàng)建視圖的時候是不允許重名的，那么，我們只能先刪除掉這個視圖然后進行新視圖的創(chuàng)建。該如何刪除視圖呢?
drop view 視圖名稱;
所以，類似于刪除表的操作，我們將上面創(chuàng)建的視圖empv20刪除
drop view empv20;
刪除成功后，重新執(zhí)行創(chuàng)建視圖的語句
create view empv20 as select empno,ename,job,sal,hiredate from emp where deptno=20;
但是，我們應該發(fā)現(xiàn)，如果所有的代碼都這樣去寫肯定很麻煩，因為如果要想對視圖進行修改操作，則肯定先要刪除掉視圖，再進行新視圖的創(chuàng)建才能達到目的，所以在Oracle中為了方便用戶修改視圖，提供了一種替換的命令，此時完整的視圖創(chuàng)建語法如下：
create or replace 視圖名稱 as 子查詢;
按照上面的語法格式，我們在更改視圖的時候就不需要先執(zhí)行刪除操作了，系統(tǒng)會為用戶自動進行刪除及重建的功能。
create or replace view empv20 as select empno,ename,job,sal,hiredate from emp where deptno=20;
此時，系統(tǒng)不會提示任何錯誤，表示該視圖刪除及創(chuàng)建成功。
我們說視圖實際上是封裝了一個非常復雜的查詢語句。下面我們使用視圖來封裝一個非常復雜的查詢。此查詢返回部門名稱、部門人數(shù)、平均工資以及最低工資的雇員姓名。首先看看以前的寫法
select d.dname,ed.c,ed.a,e.ename
from dept d,
(
select count(empno) c,deptno,avg(sal) a,min(sal) min from emp e
group by deptno
) ed,emp e
where d.deptno=ed.deptno and e.sal=ed.min;
如果在開發(fā)中每次都寫如此之長的SQL語句，則肯定很不方便，所以此時就可以通過建立師視圖簡化操作，方便用戶做查詢。
create or replace view myempv as
select d.dname,ed.c,ed.a,e.ename
from dept d,
(
select count(empno) c,deptno,avg(sal) a,min(sal) min from emp e
group by deptno
) ed,emp e
where d.deptno=ed.deptno and e.sal=ed.min;
在以后的操作中只需要查詢該視圖就可以得到結果，而不需要再次執(zhí)行那么長的SQL語句。
創(chuàng)建一個只包含20部門的雇員視圖
create or replace view mepv20
as
select * from emp where deptno=20;
視圖創(chuàng)建成功。下面進行視圖的更新操作，我們應該記住，在視圖中是不應該包含真實數(shù)據(jù)的，而且在此程序中，創(chuàng)建的視圖實際上是存在創(chuàng)建條件的，此條件是deptno=20.如果現(xiàn)在將視圖中的7369的部門編號修改為30呢?
update empv20 set deptno=30 where empno=7369;
更新成功，現(xiàn)在我們查詢該視圖，
select * from mepv20;
發(fā)現(xiàn)在視圖中已經(jīng)沒有7369這個雇員了。那么，在我們的原始表emp中呢?
select * from emp;
發(fā)現(xiàn)在emp表中的編號為7369的雇員的部門編號已經(jīng)修改為30，我們思考下，這樣的更新操作合適嗎?很明顯，是不合適的，我們在創(chuàng)建視圖的時候是有條件的，一旦修改之后，該條件被破壞。所以在創(chuàng)建視圖的時候SQL中提供了兩個很重要的參數(shù)：
|-with check option：不能更新視圖的創(chuàng)建條件
下面我們在視圖創(chuàng)建中使用此參數(shù)
create or replace view empv20
as
select * from emp where deptno=20
with check option;
我們再對創(chuàng)建的視圖進行更新操作
update mepv20 set deptno=30 where empno=7566;
此時，系統(tǒng)報錯，提示：視圖 with check option where 子句違規(guī)
很明顯，創(chuàng)建條件不能更新，那么其他字段呢，例如：現(xiàn)在將7566的雇員姓名修改為“約翰”
update empv20 set ename='約翰' where empno=7566;
更新成功，也就是說在使用了上述的with約束后，在更新視圖時，除了創(chuàng)建條件不能更新其他字段均可以更新。
但是，我們說視圖本身的作用還是用來查詢的，所以不應該允許修改，所以此時可以使用第二個參數(shù)：
|-with read only：創(chuàng)建的視圖只讀，即只能讀取操作
創(chuàng)建只讀視圖
create or replace view empv20
as
select * from emp where deptno=20
with read only;
再次執(zhí)行更新操作，更新雇員的姓名
update empv20 set ename='約翰' where empno=7566;
提示錯誤：無法對只讀視圖進行DML操作。
1.2、序列
在很多數(shù)據(jù)庫系統(tǒng)中都存在一個自動增長的列，如果現(xiàn)在要想在Oracle中完成自動增長的功能，則只能依靠序列完成，所有的自動增長操作，需要用戶手工完成處理。
序列的創(chuàng)建格式：
create sequence sequence
[increment by n][start with n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[cycle | nocycle]
[{cache n | nocache}];
創(chuàng)建一個myseq的序列，驗證自動增長的操作
create sequence myseq;
序列創(chuàng)建完成之后，所有的自動增長應該由用戶自己處理，在序列中提供了以下兩種操作：
|-nextVal：取得序列的下一個內容
|-currVal：取得序列的當前內容
現(xiàn)在我們先建立一張用于驗證序列的表
create table testseq(
next number,
curr number
);
下面向表中添加數(shù)據(jù)，添加數(shù)據(jù)的時候需要手工使用序列
使用序列插入數(shù)據(jù)
insert into testseq(next,curr) values(myseq.nextval,myseq.currval);
將以上的插入語句執(zhí)行5次
我們查詢下testseq表，看看序列的變化
select * from testseq;
從查詢結果中我們發(fā)現(xiàn)，nextval的內容始終在進行自動增長的操作，而curr使用取出當前操作的序列結果，也就是說，現(xiàn)在的這種序列，每次增長的幅度是1，那么也可以修改序列的增長幅度。
可以使用以下的一個參數(shù)：
|-每次增長長度：increment by 長度
重新建立序列
drop sequence myseq;
create sequence myseq increment by 2;
此時，序列已經(jīng)正常的創(chuàng)建，創(chuàng)建之后來測試下，序列的操作，重新創(chuàng)建testseq表
drop table testseq;
create table testseq(
next number,
curr number
);
重新進行數(shù)據(jù)的插入操作，插入5次
insert into testseq(next,curr) values(myseq.nextval,myseq.currval);
再次查詢testseq表，觀察序列的變化
select * from testseq;
從序列的結果來看，每次取得都是奇數(shù)。
默認情況下，序列從1開始的，那么可以使用start with來指定其開始的位置
drop sequence myseq;
create sequence myseq increment by 2 start with 10;
這里指定序列開始點為10，以后直接從10開始進行序列的計算。
下面我們重新創(chuàng)建下該序列，讓其取值固定在1、3、5、7、9，并循環(huán)序列
drop sequence myseq;
create sequence myseq
maxvalue 10
increment by 2 start with 1
cache 2 cycle;
重新建立testseq表，插入數(shù)據(jù)，測試最終的結果，可以發(fā)現(xiàn)序列的內容是循環(huán)出現(xiàn)的，但是我們說在實踐中，序列使用最多的語法是：create sequence 序列名稱。其他選項使用默認值。
1.3、同義詞
在前面我們使用過這樣的查詢語句：
select sysdate from dual;
我們知道dual是一張?zhí)摂M表，那么雖然是虛擬表，可是此表到底是在哪里定義的呢?
通過測試，我們發(fā)現(xiàn)在sys賬戶下存在dual表�，F(xiàn)在問題出現(xiàn)了，既然dual表是sys賬戶下的，那么根據(jù)我們前面的知識，如果想要在scott用戶中使用dual表時，正確的做法是：
select sysdate from sys.dual;
但是我們在scott賬戶下是這樣寫的
select sysdate from dual;
這是為什么呢?此時，實際上就是同義詞的作用。什么是同義詞?同義詞可以讓其他用戶通過一個名稱方便的訪問”用戶名.表名稱“。
創(chuàng)建同義詞的語法：
create synonym 同義詞名稱 for 用戶名.表名稱;
下面我們將scott表的emp定義emp的同義詞：
create sysnonym emp for scott.emp;
如果要刪除一個同義詞，可以使用如下的語法格式：
drop sysnonym 同義詞名稱;
所以，刪除上面創(chuàng)建的同義詞：
drop sysnonym emp;
但是，此種特性只適用于Oracle數(shù)據(jù)庫。
1.4、用戶管理
在Oracle中可以對用戶進行建立和授權的操作。
創(chuàng)建用戶的語法是：
create user 用戶名 identified by 密碼;
假設現(xiàn)在要創(chuàng)建一個test用戶，密碼為123
create user test identified by 123;
創(chuàng)建用戶成功過后，是否可以登錄呢?顯然是不行的，在登錄請安必須為新創(chuàng)建的用戶授予相關的權限才能執(zhí)行相應的操作。
為用戶授權的格式如下：
grant 權限1,權限2,...... to 用戶名;
所以，為了新創(chuàng)建的用戶test能夠連接數(shù)據(jù)庫，我們需要為它授權
grant create session to test;
之后我們就能使用test正常登陸了。
那么，我們開始常見一張表吧。
可是，我們發(fā)現(xiàn)，系統(tǒng)又提示權限不足。很明顯的知道，當前用戶test沒有創(chuàng)建表的權限，既然如此，我們就為它授予創(chuàng)建表的權限。在此之前，我們給出一個這樣的結論：對于一個新創(chuàng)建的用戶，所有的權限均要分別賦予，該用戶才能進行相應的操作。如果現(xiàn)在假設要想把多個權限一次性賦予一個用戶，則可以講這些權限先定義成一組角色的集合。
在Oracle中提供了兩個主要角色：connect、resource，可以直接把這啷個角色賦予test用戶。
grant connect,resource to test;
突然，test用戶密碼忘記了，那么如何修改一個用戶的密碼呢?當然該操作只有超級管理員才有權限
alter user 用戶名 identified by 密碼;
將test的用戶密碼修改為hello
alter user test identified by hello;
在一般的系統(tǒng)中存在這樣的情況，在用戶第一次登陸的時候可以修改密碼，所以要想完成此功能，可以手工讓一個密碼失效，格式如下：
alter user 用戶名 password expire;
現(xiàn)在我們可以設置test的當前密碼失效
alter user test password expire;
如果系統(tǒng)中某個用戶需要被鎖住，該如何操作呢?
alter user 用戶名 account lock;
現(xiàn)在由于某些原因，我們需要將test用戶鎖住
alter user test account lock;
那么，test用戶就不能連接數(shù)據(jù)庫了。
如果要解鎖test呢?
alter user 用戶名 account unlock;
好了，現(xiàn)在我們解鎖test
alter user test account unlock;
那么，test用戶就能正常連接數(shù)據(jù)庫了。
現(xiàn)在我們需要使用test用戶來訪問scott用戶下的emp表
select * from scott.emp;
按照前面的知識，該查詢時沒有問題的，但是系統(tǒng)提示錯誤說：scott.emp表不存在。怎么回事?
再想想前面的一句話，”我們需要為新創(chuàng)建的用戶分別授予相應的權限來執(zhí)行相應的操作“，很明顯，test沒有訪問其他用戶下表的權限，所以這么操作
grant select,delete on scott.emp to test;
我們將查詢和刪除emp表的權限授予給test用戶
既然可以授予權限，那么也可以回收權限，回收權限使用revoke語法。，語法格式如下：
revoke 權限名 on 用戶表名稱 from 用戶;
如果我們不希望test用戶查詢和刪除scott的emp表，
revoke select,delete on scott.emp from test;
1.5、數(shù)據(jù)庫的備份和恢復
數(shù)據(jù)庫在運行的期間都是需要不斷的進行備份的，萬一假設系統(tǒng)崩潰了，可以從備份中恢復數(shù)據(jù)。
Oracle在安裝完成之后可以使用兩個命名進行數(shù)據(jù)庫的備份和恢復：
|-數(shù)據(jù)庫備份：exp
|-數(shù)據(jù)庫恢復：imp
1.6、嵌套表
嵌套表：在一個表中還包含另外一個子表
例如：現(xiàn)在有一種情況，一個部門可能承接多個項目，如果此時，按照最原始的方法設計，那么定義兩張表，department表，project表
create table department(
deptno number(2) primary key not null,
dname varchar2(50) not null
);
create table project(
proid number(4) primary key not null,
proname varchar2(50) not null,
deptno number(2),
constraint department_project_deptno foreign key(deptno) references department(deptno) on delete cascade
);
這是我們最常見的思路，而且本身也屬于一個正確的做法，但是在Oracle中引入了嵌套表的概念，可以直接將項目表的類型作為一個department表的字段類型，達到嵌套的目的。
但是，要想完成一個嵌套表的制作，則首先要保證一點：因為數(shù)據(jù)庫在創(chuàng)建數(shù)據(jù)表的時候都要指定字段的類型，所以嵌套表本身也需要同樣指定類型，那么這種類型就需要單獨的定義：
create type project_ty as object(
proid number(4),
proname varchar2(50),
prodate date
);
/
類型創(chuàng)建成功之后，并不意味著此類型可以直接使用，因為此類型是一個完整的類型，所以要為此類型指定一個名稱
create type project_nt as table of project_ty;
/
以上的操作表示以后直接使用project_nt表示project_ty類型，就類似于varchar2表示字符串是一樣的，此時可以使用此類型創(chuàng)建department表
create table department(
deptno number(2) primary key not null,
dname varchar2(50) not null,
projects project_nt
)nested table projects store as project_nt_tab_temp;
對于插入語句來講，需要指定每個project_ty的類型
insert into department(deptno,dname,projects)
values(
1,'技術部',
project_nt(
project_ty(1001,'ERP',sysdate),
project_ty(1002,'CRM',sysdate),
project_ty(1003,'OA',sysdate)
)
);
此時，查詢嵌套表，可以返回多個項目
select * from department;
如果這個時候，我們需要查看一個部門的全部項目的話，則需要查詢嵌套表
select * from table
(select projects from department where deptno=1);
如果現(xiàn)在我們需要更新項目編號為1001的項目名稱，將此項目名稱更新為“測試項目”
update table (select projects from department where deptno=1) pro
set value(pro)=project_ty(1001,'測試項目',to_date('1998-09-21','yyyy-mm-dd')) where pro.proid=1001;
1.7、可變數(shù)組
可變數(shù)組屬于嵌套表的升級版，在可變數(shù)組中，實際上就是將內部的嵌套表的內容的長度進行了限制。
例如，一個部門有多個工人，如果按照可變數(shù)組的做法，肯定首先要做出一個工人的類型。
create type worker_info as object(
id number(4),
name varchar2(50),
sex varchar2(6)
);
/
下面再定義數(shù)組類型
create type worker_info_list as varray(10) of worker_info;
/
定義部門表，一個部門中可能存在多個工人
drop table department;
create table department(
deptno number(2) primary key not null,
dname varchar2(50) not null,
workers worker_info_list
);
插入測試數(shù)據(jù)
insert into department(deptno,dname,workers)
values(20,'后勤部',
worker_info_list(
worker_info(1,'張三','男'),
worker_info(2,'李四','男'),
worker_info(3,'王五','男')
)
);
查詢全部
select * from department;
除了以上的所有內容之外，對于數(shù)據(jù)庫的開發(fā)中，像過程之類的基本不用了，因為現(xiàn)在的很多地方都使用程序完成功能。
而且，對于高級開發(fā)部分：游標、觸發(fā)器、包、函數(shù)�；旧虾苌偃ブ苯诱{用。
1.8、數(shù)據(jù)庫設計范式
數(shù)據(jù)庫設計范式實際上非常的重要，但是從實際的開發(fā)來看，如果真的全部按照范式去做，則這個程序沒法寫，包括查詢語句也會變得復雜。
在Oracle中的scott用戶的全部表，實際上就已經(jīng)很好的體現(xiàn)了一張設計思路，雇員-部門的關系。
1) 第一范式
例如，現(xiàn)在假設有如下的數(shù)據(jù)庫創(chuàng)建腳本
create table person(
pid number(4) primary key not null,
name varchar2(50),
info varchar(200)
);
插入以下測試數(shù)據(jù)
insert into person(pid,name,info) values(1111,'張三','1983年11月23日出生，現(xiàn)在的住址是：北京市西城區(qū)。。。。。');
實際上對于人員來看，由以下幾部分組成：
|-生日：1983年1月23日
|-省市：北京
|-地區(qū)：西城區(qū)
|-詳細的信息：。。。。。
每個字段不可再分，所以，以上的數(shù)據(jù)庫創(chuàng)建腳本修改如下：
create table person(
pid number(4) primary key not null,
name varchar2(50),
birthday date,
area varchar2(200),
subarea varchar2(200),
address varchar2(200)
);
這種設計看上去每個字段是不可再分的，但是我們應該會注意到，在一些網(wǎng)站的注冊中，會要求用戶分別輸入“姓”和“名”，所以，可將上面的設計修改如下：
create table person(
pid number(4) primary key not null,
姓 varchar2(50),
名 varchar2(50),
birthday date,
area varchar2(200),
subarea varchar2(200),
address varchar2(200)
);
所以，在設計表字段的時候，最好保證每個字段均不能再分。
2) 第二范式
第一范式的要求非常簡單，保證每個字段有意義。但是如果所有的操作都使用第一范式，那么會存在問題：
現(xiàn)在建立一張學生選課表：學號、姓名、年齡、課程名稱、成績、學分
create table selectcourse(
stuno varchar2(50),
stuname varchar2(50),
stuage number,
cname varchar2(50),
grade number,
credit number
);
以上的腳本符合第一范式的要求，但是如果按照第一范式設計的話，會存在問題：
insert into selectcourse values('s001','張三',21,'JAVA',89,0.3);
insert into selectcourse values('s001','李四',20,'JAVA',78,0.3);
insert into selectcourse values('s001','王五',23,'JAVA',80,0.3);
insert into selectcourse values('s001',趙六',22,'JAVA',90,0.3);
從以上的數(shù)據(jù)庫腳本上可以發(fā)現(xiàn)，所有的課程信息冗余了，而且還存在以下問題：
|-如果一門課程沒有一個學生選擇，則此而成就從學校徹底消失了
|-課程中本身也應該包含一個課程的編號，但是如果按照以上的設計，則課程編號肯定重復
|-如果要更改課程信息，則要更改許多條記錄
我們使用第二范式修改數(shù)據(jù)庫腳本：
|-學生是一個實體--學生表
create table student(
stuno varchar2(50) primary key not null,
stuname varchar2(50),
stuage number
);
|-課程也應該是一個實體--課程表
create table course(
cid number(5) primary key not null,
cname varchar2(50),
credit number
);
|-學生選課信息也是一個實體--學生選課表
create table selectcourse(
stuno varchar2(50),
cid number(5),
grade number,
加入外鍵關聯(lián)，因為學生沒了，成績就沒了，因為課程沒了，成績就沒了
);
以上設計解決了以下問題：
|-學生不選課的時候，課程信息不會消失
|-更新課程的時候直接更新課程表即可
|-所有的關聯(lián)關系在關系表中體現(xiàn)。
3) 第三范式
在實際開發(fā)中，第三范式的使用是最多的。
例如，現(xiàn)在要求設計一張學生表，包含學號、姓名、年齡、所在院校、學院地址、學院電話，此時肯定不能使用第一范式，但是現(xiàn)在如果使用的是第二范式呢?
create table student(
stuno varchar2(50) primary key not null,
stuname varchar2(50),
stuage number
);
create table collage(
cid number(4) primary key not null,
cname varchar2(50) not not null,
caddress varchar2(200) not nul,
ctel varchar2(200) not null
);
create table studentcollage(
stuno varchar2(50),
cid number(4),
設置主-外鍵關系
);
按照上面的設計，一個學生可以同時在多個學院同時上課，多個學院會同時有同一個學生，此時，最好的做法是：一個學院包含多個學生，一個學生屬于一個學院，實際上，此設計就完全類似于部門和雇員表的設計結構。
create table collage(
cid number(4) primary key not null,
cname varchar2(50) not not null,
caddress varchar2(200) not nul,
ctel varchar2(200) not null
);
create table student(
stuno varchar2(50) primary key not null,
stuname varchar2(50),
stuage number,
cid number(4),
建立主-外鍵關系
);
該設計是一個很明確的一對多的關系設計。
數(shù)據(jù)庫的唯一原則：
|-數(shù)據(jù)庫表的關聯(lián)查詢越少越好，SQL語句的復雜度越低越好。
1.9、數(shù)據(jù)庫設計工具
在實際中數(shù)據(jù)庫也有自己的設計工具，比較常用的就是Sybase的PowerDesigner開發(fā)工具，此工具可以方便的做各種設計，啟動之后，可以使用此工具，進行數(shù)據(jù)庫的建模設計。
啟動PowerDesigner后，選擇新建，Physical Data Model，選擇Oracle數(shù)據(jù)庫
下面使用PowerDesigner工具將Oracle中的dept和emp表進行還原
創(chuàng)建表--在工具中進行主-外鍵的操作--得到關系之后，就可以通過Powerdesigner工具進行數(shù)據(jù)庫腳本的創(chuàng)建了。
1.10、數(shù)據(jù)庫設計分析
1) 要求
設計要求，要求設計一個網(wǎng)上購物程序(使用Powerdesigner建立模型并編寫測試數(shù)據(jù))，有以下的需求
|-管理員可以再后臺添加商品，每個商品屬于一個商品組
|-可以對管理員進行分組，對每一組進行分別授權，即一個管理員組可以有多個管理員，一個管理員組有多個權限，一個管理員可以再多個組
|-用戶可以自己購買商品，購買商品時要在訂單表中添加信息，一個用戶可以同時購買多個商品，用戶可以選擇自己所在的地區(qū)進行商品的派送
|-用戶可以根據(jù)自己的購買積分，對商品進行折扣
2) 實現(xiàn)
根據(jù)第一個要求，一個商品屬于一個商品組，則此時應該建立一個“一對多”的關系
根據(jù)第二個要求，可以對管理員進行分組，需要管理員表、管理員組表、權限表、管理員-管理員組表、管理員組-權限表
管理員和商品表也要存在關系
需要一個用戶表，與其產(chǎn)生關系的有地區(qū)表、子地區(qū)表、訂單表、訂單詳情表、積分表
正常情況下，一份訂單肯定會按照以上的格式顯示，那么請問，這樣一來要查詢多少張表?
|-用戶表(用戶姓名、用戶電話、用戶地址)
|-地區(qū)表-子地區(qū)表(用戶地區(qū))
|-訂單表、訂單詳情表(商品總價、訂單日期、郵政編碼)
本查詢需要同時查詢6張表。本程序中的所有代碼都是按照標準范式完成的，所以此時出現(xiàn)了以上的問題。
在開發(fā)中減少多表查詢的方法可以通過冗余數(shù)據(jù)完成。
Oracle 筆記
1
韓順平老師 oracle教程筆記
1.Oracle認證，與其它數(shù)據(jù)庫比較，安裝 Oracle安裝會自動的生成sys用戶和system用戶: (1)sys用戶是超級用戶，具有最高權限，具有sysdba角色，有create database的權限，該用戶默認的密碼是change_on_install (2)system用戶是管理操作員，權限也很大。具有sysoper角色，沒有create database的權限，默認的密碼是manager (3)一般講，對數(shù)據(jù)庫維護，使用system用戶登錄就可以拉 也就是說sys和system這兩個用戶最大的區(qū)別是在于有沒有create database的權限。
2.Oracle的基本使用--基本命令
sql*plus的常用命令
連接命令 1.conn[ect] 用法：conn 用戶名/密碼@網(wǎng)絡服務名[as sysdba/sysoper]當用特權用戶身份連接時，必須帶上as sysdba或是as sysoper 2.disc[onnect] 說明: 該命令用來斷開與當前數(shù)據(jù)庫的連接 3.psssw[ord] 說明: 該命令用于修改用戶的密碼，如果要想修改其它用戶的密碼，需要用sys/system登錄。 4.show user 說明: 顯示當前用戶名 5.exit 說明: 該命令會斷開與數(shù)據(jù)庫的連接，同時會退出sql*plus 文件操作命令 1.start和@ 說明: 運行sql腳本 案例: sql>@ d:\a.sql或是sql>start d:\a.sql 2.edit 說明: 該命令可以編輯指定的sql腳本 案例: sql>edit d:\a.sql,這樣會把d:\a.sql這個文件打開 3.spool 說明: 該命令可以將sql*plus屏幕上的內容輸出到指定文件中去。 案例: sql>spool d:\b.sql 并輸入 sql>spool off 交互式命令 1.& 說明：可以替代變量，而該變量在執(zhí)行時，需要用戶輸入。 select * from emp where job='&job'; 2.edit 說明：該命令可以編輯指定的sql腳本 案例：SQL>edit d:\a.sql 3.spool 說明：該命令可以將sql*plus屏幕上的內容輸出到指定文件中去。 spool d:\b.sql 并輸入 spool off 顯示和設置環(huán)境變量 概述：可以用來控制輸出的各種格式，set show如果希望永久的保存相關的設置，可以去修改glogin.sql腳本
Oracle 筆記
2
1.linesize 說明：設置顯示行的寬度，默認是80個字符 show linesize set linesize 90 2.pagesize說明：設置每頁顯示的行數(shù)目，默認是14 用法和linesize一樣 至于其它環(huán)境參數(shù)的使用也是大同小異
3.oracle用戶管理
oracle用戶的管理 創(chuàng)建用戶 概述：在oracle中要創(chuàng)建一個新的用戶使用create user語句，一般是具有dba(數(shù)據(jù)庫管理員)的權限才能使用。 create user 用戶名 identified by 密碼; (oracle有個毛病，密碼必須以字母開頭，如果以字母開頭，它不會創(chuàng)建用戶) 給用戶修改密碼 概述：如果給自己修改密碼可以直接使用 password 用戶名 如果給別人修改密碼則需要具有dba的權限，或是擁有alter user的系統(tǒng)權限 SQL> alter user 用戶名 identified by 新密碼 刪除用戶 概述：一般以dba的身份去刪除某個用戶，如果用其它用戶去刪除用戶則需要具有drop user的權限。 比如 drop user 用戶名 【cascade】 在刪除用戶時，注意： 如果要刪除的用戶，已經(jīng)創(chuàng)建了表，那么就需要在刪除的時候帶一個參數(shù)cascade; 用戶管理綜合案例 概述：創(chuàng)建的新用戶是沒有任何權限的，甚至連登陸的數(shù)據(jù)庫的權限都沒有，需要為其指定相應的權限。給一個用戶賦權限使用命令grant，回收權限使用命令revoke。 為了給講清楚用戶的管理，這里我給大家舉一個案例。 SQL> conn xiaoming/m12; ERROR: ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon denied 警告: 您不再連接到 ORACLE。 SQL> show user; USER 為 "" SQL> conn system/p; 已連接。 SQL> grant connect to xiaoming; 授權成功。 SQL> conn xiaoming/m12; 已連接。 SQL> 注意：grant connect to xiaoming;在這里，準確的講，connect不是權限，而是角色。
現(xiàn)在說下對象權限，現(xiàn)在要做這么件事情： * 希望xiaoming用戶可以去查詢emp表 * 希望xiaoming用戶可以去查詢scott的emp表
Oracle 筆記
3
grant select on emp to xiaoming * 希望xiaoming用戶可以去修改scott的emp表 grant update on emp to xiaoming * 希望xiaoming用戶可以去修改/刪除，查詢，添加scott的emp表 grant all on emp to xiaoming * scott希望收回xiaoming對emp表的查詢權限 revoke select on emp from xiaoming //對權限的維護。 * 希望xiaoming用戶可以去查詢scott的emp表/還希望xiaoming可以把這個權限繼續(xù)給別人。 --如果是對象權限，就加入 with grant option grant select on emp to xiaoming with grant option 我的操作過程： SQL> conn scott/tiger; 已連接。 SQL> grant select on scott.emp to xiaoming with grant option; 授權成功。 SQL> conn system/p; 已連接。 SQL> create user xiaohong identified by m123; 用戶已創(chuàng)建。 SQL> grant connect to xiaohong; 授權成功。 SQL> conn xiaoming/m12; 已連接。 SQL> grant select on scott.emp to xiaohong; 授權成功。 --如果是系統(tǒng)權限。 system給xiaoming權限時： grant connect to xiaoming with admin option 問題：如果scott把xiaoming對emp表的查詢權限回收，那么xiaohong會怎樣? 答案：被回收。 下面是我的操作過程： SQL> conn scott/tiger; 已連接。 SQL> revoke select on emp from xiaoming; 撤銷成功。 SQL> conn xiaohong/m123; 已連接。 SQL> select * from scott.emp; select * from scott.emp 第 1 行出現(xiàn)錯誤: ORA-00942: 表或視圖不存在 結果顯示：小紅受到誅連了。。 使用profile管理用戶口令 概述：profile是口令限制，資源限制的命令集合，當建立數(shù)據(jù)庫的，oracle會自動建立名稱為default的profile。當建立用戶沒
Oracle 筆記
4
有指定profile選項，那么oracle就會將default分配給用戶。 1.賬戶鎖定 概述：指定該賬戶(用戶)登陸時最多可以輸入密碼的次數(shù)，也可以指定用戶鎖定的時間(天)一般用dba的身份去執(zhí)行該命令。 例子：指定scott這個用戶最多只能嘗試3次登陸，鎖定時間為2天，讓我們看看怎么實現(xiàn)。 創(chuàng)建profile文件 SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2; SQL> alter user scott profile lock_account; 2.給賬戶(用戶)解鎖 SQL> alter user tea account unlock; 3.終止口令 為了讓用戶定期修改密碼可以使用終止口令的指令來完成，同樣這個命令也需要dba的身份來操作。 例子：給前面創(chuàng)建的用戶tea創(chuàng)建一個profile文件，要求該用戶每隔10天要修改自己的登陸密碼，寬限期為2天�？纯丛趺醋觥� SQL> create profile myprofile limit password_life_time 10 password_grace_time 2; SQL> alter user tea profile myprofile; 口令歷史 概述：如果希望用戶在修改密碼時，不能使用以前使用過的密碼，可使用口令歷史，這樣oracle就會將口令修改的信息存放到數(shù)據(jù)字典中，這樣當用戶修改密碼時，oracle就會對新舊密碼進行比較，當發(fā)現(xiàn)新舊密碼一樣時，就提示用戶重新輸入密碼。 例子： 1)建立profile SQL>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10 password_reuse_time //指定口令可重用時間即10天后就可以重用 2)分配給某個用戶
刪除profile 概述：當不需要某個profile文件時，可以刪除該文件。 SQL> drop profile password_history 【casade】 注意：文件刪除后，用這個文件去約束的那些用戶通通也都被釋放了。。 加了casade，就會把級聯(lián)的相關東西也給刪除掉
4.oracle表的管理(數(shù)據(jù)類型，表創(chuàng)建刪除，數(shù)據(jù)CRUD操作)
期望目標
? 1.掌握oracle表的管理(創(chuàng)建/維護)
? 2.掌握對oracle表的各種查詢技巧
? 3.學會創(chuàng)建新的oracle數(shù)據(jù)庫 oracle的表的管理 表名和列的命名規(guī)則
? 必須以字母開頭
? 長度不能超過30個字符
? 不能使用oracle的保留字
? 只能使用如下字符 A-Z，a-z，0-9，$,#等
oracle支持的數(shù)據(jù)類型? 字符類 char 定長 最大2000個字符。
Oracle 筆記
5
例子：char(10) ‘小韓’前四個字符放‘小韓’，后添6個空格補全 如‘小韓 ’ varchar2(20) 變長 最大4000個字符。 例子：varchar2(10) ‘小韓’ oracle分配四個字符。這樣可以節(jié)省空間。 clob(character large object) 字符型大對象 最大4G char 查詢的速度極快浪費空間，查詢比較多的數(shù)據(jù)用。 varchar 節(jié)省空間 數(shù)字型 number范圍 -10的38次方 到 10的38次方 可以表示整數(shù)，也可以表示小數(shù) number(5,2) 表示一位小數(shù)有5位有效數(shù)，2位小數(shù) 范圍：-999.99到999.99 number(5) 表示一個5位整數(shù) 范圍99999到-99999 日期類型 date 包含年月日和時分秒 oracle默認格式 1-1月-1999 timestamp 這是oracle9i對date數(shù)據(jù)類型的擴展�？梢跃_到毫秒。 ? 圖片 blob 二進制數(shù)據(jù) 可以存放圖片/聲音 4G 一般來講，在真實項目中是不會把圖片和聲音真的往數(shù)據(jù)庫里存放，一般存放圖片、視頻的路徑，如果安全需要比較高的話，則放入數(shù)據(jù)庫。 怎樣創(chuàng)建表 建表 --學生表 create table student ( ---表名 xh number(4), --學號 xm varchar2(20), --姓名 sex char(2), --性別 birthday date, --出生日期 sal number(7,2) --獎學金 ); --班級表 CREATE TABLE class( classId NUMBER(2), cName VARCHAR2(40) ); 修改表 ? 添加一個字段 SQL>ALTER TABLE student add (classId NUMBER(2)); ? 修改一個字段的長度 SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30)); ? 修改字段的類型/或是名字(不能有數(shù)據(jù)) 不建議做 SQL>ALTER TABLE student modify (xm CHAR(30)); ? 刪除一個字段 不建議做(刪了之后，順序就變了。加就沒問題，應為是加在后面) SQL>ALTER TABLE student DROP COLUMN sal;
Oracle 筆記
6
? 修改表的名字 很少有這種需求 SQL>RENAME student TO stu; ? 刪除表 SQL>DROP TABLE student; 添加數(shù)據(jù) 所有字段都插入數(shù)據(jù) INSERT INTO student VALUES ('A001', '張三', '男', '01-5月-05', 10); oracle中默認的日期格式‘dd-mon-yy’ dd日子(天) mon 月份 yy 2位的年 ‘09-6月-99’ 1999年6月9日 修改日期的默認格式(臨時修改，數(shù)據(jù)庫重啟后仍為默認;如要修改需要修改注冊表) ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd'; 修改后，可以用我們熟悉的格式添加日期類型： INSERT INTO student VALUES ('A002', 'MIKE', '男', '1905-05-06', 10); 插入部分字段 INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女'); 插入空值 INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null); 問題來了，如果你要查詢student表里birthday為null的記錄，怎么寫sql呢? 錯誤寫法：select * from student where birthday = null; 正確寫法：select * from student where birthday is null; 如果要查詢birthday不為null,則應該這樣寫： select * from student where birthday is not null; 修改數(shù)據(jù) ? 修改一個字段 UPDATE student SET sex = '女' WHERE xh = 'A001'; ? 修改多個字段 UPDATE student SET sex = '男', birthday = '1984-04-01' WHERE xh = 'A001'; 修改含有null值的數(shù)據(jù) 不要用 = null 而是用 is null; SELECT * FROM student WHERE birthday IS null; ? 刪除數(shù)據(jù) DELETE FROM student; 刪除所有記錄，表結構還在，寫日志，可以恢復的，速度慢。 Delete 的數(shù)據(jù)可以恢復。 savepoint a; --創(chuàng)建保存點 DELETE FROM student; rollback to a; --恢復到保存點 一個有經(jīng)驗的DBA，在確保完成無誤的情況下要定期創(chuàng)建還原點。 DROP TABLE student; --刪除表的結構和數(shù)據(jù); delete from student WHERE xh = 'A001'; --刪除一條記錄; truncate TABLE student; --刪除表中的所有記錄，表結構還在，不寫日志，無法找回刪除的記錄，速度快。
5.oracle表查詢(1)
在我們講解的過程中我們利用scott用戶存在的幾張表(emp，dept)為大家演示如何使用select語句，select語句在軟件編程中非常有用，希望大家好好的掌握。
Oracle 筆記
7
emp 雇員表 clerk 普員工 salesman 銷售 manager 經(jīng)理 analyst 分析師 president 總裁 mgr 上級的編號 hiredate 入職時間 sal 月工資 comm 獎金 deptno 部門 dept部門表 deptno 部門編號 accounting 財務部 research 研發(fā)部 operations 業(yè)務部 loc 部門所在地點 salgrade 工資級別 grade 級別 losal 最低工資 hisal 最高工資 簡單的查詢語句 ? 查看表結構 DESC emp; ? 查詢所有列 SELECT * FROM dept; 切忌動不動就用select * SET TIMING ON; 打開顯示操作時間的開關，在下面顯示查詢時間。 CREATE TABLE users(userId VARCHAR2(10), uName VARCHAR2 (20), uPassw VARCHAR2(30)); INSERT INTO users VALUES('a0001', '啊啊啊啊', 'aaaaaaaaaaaaaaaaaaaaaaa'); --從自己復制，加大數(shù)據(jù)量 大概幾萬行就可以了 可以用來測試sql語句執(zhí)行效率 INSERT INTO users (userId,UNAME,UPASSW) SELECT * FROM users; SELECT COUNT (*) FROM users;統(tǒng)計行數(shù) ? 查詢指定列 SELECT ename, sal, job, deptno FROM emp; ? 如何取消重復行DISTINCT SELECT DISTINCT deptno, job FROM emp; ?查詢SMITH所在部門，工作，薪水 SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH'; 注意：oracle對內容的大小寫是區(qū)分的，所以ename='SMITH'和ename='smith'是不同的
Oracle 筆記
8
? 使用算術表達式 nvl null 問題：如何顯示每個雇員的年工資? SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp; ? 使用列的別名 SELECT ename "姓名", sal*12 AS "年收入" FROM emp; ? 如何處理null值 使用nvl函數(shù)來處理 ? 如何連接字符串(||) SELECT ename || ' is a ' || job FROM emp; ? 使用where子句 問題：如何顯示工資高于3000的 員工? SELECT * FROM emp WHERE sal > 3000; 問題：如何查找1982.1.1后入職的員工? SELECT ename,hiredate FROM emp WHERE hiredate >'1-1月-1982'; 問題：如何顯示工資在2000到3000的員工? SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000; ? 如何使用like操作符 %：表示0到多個字符 _：表示任意單個字符 問題：如何顯示首字符為S的員工姓名和工資? SELECT ename,sal FROM emp WHERE ename like 'S%'; 如何顯示第三個字符為大寫O的所有員工的姓名和工資? SELECT ename,sal FROM emp WHERE ename like '__O%'; ? 在where條件中使用in 問題：如何顯示empno為7844, 7839,123,456 的雇員情況? SELECT * FROM emp WHERE empno in (7844, 7839,123,456); ? 使用is null的操作符 問題：如何顯示沒有上級的雇員的情況? 錯誤寫法：select * from emp where mgr = ''; 正確寫法：SELECT * FROM emp WHERE mgr is null;
6.oracle表查詢(2)
? 使用邏輯操作符號 問題：查詢工資高于500或者是崗位為MANAGER的雇員，同時還要滿足他們的姓名首字母為大寫的J? SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%'; ? 使用order by 字句 默認asc 問題：如何按照工資的從低到高的順序顯示雇員的信息? SELECT * FROM emp ORDER by sal; 問題：按照部門號升序而雇員的工資降序排列 SELECT * FROM emp ORDER by deptno, sal DESC; ? 使用列的`別名排序 問題：按年薪排序 select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc; 別名需要使用“”號圈中,英文不需要“”號
Oracle 筆記
9
? 分頁查詢 等學了子查詢再說吧。。。。。。。。 Clear 清屏命令 oracle表復雜查詢 ? 說明 在實際應用中經(jīng)常需要執(zhí)行復雜的數(shù)據(jù)統(tǒng)計，經(jīng)常需要顯示多張表的數(shù)據(jù)，現(xiàn)在我們給大家介紹較為復雜的select語句 數(shù)據(jù)分組 ——max，min， avg， sum， count 問題：如何顯示所有員工中最高工資和最低工資? SELECT MAX(sal),min(sal) FROM emp e; 最高工資那個人是誰? 錯誤寫法：select ename, sal from emp where sal=max(sal); 正確寫法：select ename, sal from emp where sal=(select max(sal) from emp); 注意：select ename, max(sal) from emp;這語句執(zhí)行的時候會報錯，說ORA-00937：非單組分組函數(shù)。因為max是分組函數(shù)，而ename不是分組函數(shù)....... 但是select min(sal), max(sal) from emp;這句是可以執(zhí)行的。因為min和max都是分組函數(shù)，就是說：如果列里面有一個分組函數(shù)，其它的都必須是分組函數(shù)，否則就出錯。這是語法規(guī)定的 問題：如何顯示所有員工的平均工資和工資總和? 問題：如何計算總共有多少員工問題：如何 擴展要求： 查詢最高工資員工的名字，工作崗位 SELECT ename, job, sal FROM emp e where sal = (SELECT MAX(sal) FROM emp); 顯示工資高于平均工資的員工信息 SELECT * FROM emp e where sal > (SELECT AVG(sal) FROM emp); ? group by 和 having子句 group by用于對查詢的結果分組統(tǒng)計， having子句用于限制分組顯示結果。 問題：如何顯示每個部門的平均工資和最高工資? SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno; (注意：這里暗藏了一點，如果你要分組查詢的話，分組的字段deptno一定要出現(xiàn)在查詢的列表里面，否則會報錯。因為分組的字段都不出現(xiàn)的話，就沒辦法分組了) 問題：顯示每個部門的每種崗位的平均工資和最低工資? SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job; 問題：顯示平均工資低于2000的部門號和它的平均工資? SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000; ? 對數(shù)據(jù)分組的總結 1 分組函數(shù)只能出現(xiàn)在選擇列表、having、order by子句中(不能出現(xiàn)在where中) 2 如果在select語句中同時包含有group by, having, order by 那么它們的順序是group by, having, order by 3 在選擇列中如果有列、表達式和分組函數(shù)，那么這些列和表達式必須有一個出現(xiàn)在group by 子句中，否則就會出錯。 如SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000; 這里deptno就一定要出現(xiàn)在group by 中 多表查詢 ? 說明 多表查詢是指基于兩個和兩個以上的表或是視圖的查詢。在實際應用中，查詢單個表可能不能滿足你的需求，(如顯示sales部門位置和其員工的姓名)，這種情況下需要使用到(dept表和emp表)
Oracle 筆記
10
問題：顯示雇員名，雇員工資及所在部門的名字【笛卡爾集】? 規(guī)定：多表查詢的條件是 至少不能少于 表的個數(shù)-1 才能排除笛卡爾集 (如果有N張表聯(lián)合查詢，必須得有N-1個條件，才能避免笛卡爾集合) SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; 問題：顯示部門號為10的部門名、員工名和工資? SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10; 問題：顯示各個員工的姓名，工資及工資的級別? 先看salgrade的表結構和記錄 SQL>select * from salgrade; GRADE LOSAL HISAL ------------- ------------- ------------ 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; 擴展要求： 問題：顯示雇員名，雇員工資及所在部門的名字，并按部門排序? SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno; (注意：如果用group by，一定要把e.deptno放到查詢列里面) ? 自連接 自連接是指在同一張表的連接查詢 問題：顯示某個員工的上級領導的姓名? 比如顯示員工‘FORD’的上級 SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD'; 子查詢 ? 什么是子查詢 子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢。 ? 單行子查詢 單行子查詢是指只返回一行數(shù)據(jù)的子查詢語句 請思考：顯示與SMITH同部門的所有員工? 思路： 1 查詢出SMITH的部門號 select deptno from emp WHERE ename = 'SMITH'; 2 顯示 SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH'); 數(shù)據(jù)庫在執(zhí)行sql 是從左到右掃描的， 如果有括號的話，括號里面的先被優(yōu)先執(zhí)行。 ? 多行子查詢 多行子查詢指返回多行數(shù)據(jù)的子查詢 請思考：如何查詢和部門10的工作相同的雇員的名字、崗位、工資、部門號 SELECT DISTINCT job FROM emp WHERE deptno = 10; SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10); (注意：不能用job=..，因為等號=是一對一的) ? 在多行子查詢中使用all操作符
Oracle 筆記
11
問題：如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號? SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30); 擴展要求： 大家想想還有沒有別的查詢方法。 SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 執(zhí)行效率上， 函數(shù)高得多 ? 在多行子查詢中使用any操作符 問題：如何顯示工資比部門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); ? 多列子查詢 單行子查詢是指子查詢只返回單列、單行數(shù)據(jù)，多行子查詢是指返回單列多行數(shù)據(jù)，都是針對單列而言的，而多列子查詢是指查詢返回多個列數(shù)據(jù)的子查詢語句。 請思考如何查詢與SMITH的部門和崗位完全相同的所有雇員。 SELECT deptno, job FROM emp WHERE ename = 'SMITH'; SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); ? 在from子句中使用子查詢 請思考：如何顯示高于自己部門平均工資的員工的信息 思路： 1. 查出各個部門的平均工資和部門號 SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno; 2. 把上面的查詢結果看做是一張子表 SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 如何衡量一個程序員的水平? 網(wǎng)絡處理能力， 數(shù)據(jù)庫， 程序代碼的優(yōu)化程序的效率要很高 小總結： 在這里需要說明的當在from子句中使用子查詢時，該子查詢會被作為一個視圖來對待，因此叫做內嵌視圖，當在from子句中使用子查詢時，必須給子查詢指定別名。 注意：別名不能用as，如：SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 在ds前不能加as，否則會報錯 (給表取別名的時候，不能加as;但是給列取別名，是可以加as的) ? 分頁查詢 按雇員的id號升序取出 oracle的分頁一共有三種方式 1.根據(jù)rowid來分 select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc; 執(zhí)行時間0.03秒 2.按分析函數(shù)來分 select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980; 執(zhí)行時間1.01秒 3.按rownum來分
Oracle 筆記
12
select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980; 執(zhí)行時間0.1秒 其中t_xiaoxi為表名稱，cid為表的關鍵字段，取按cid降序排序后的第9981-9999條記錄，t_xiaoxi表有70000多條記錄。 個人感覺1的效率最好，3次之，2最差。 //測試通過的分頁查詢okokok select * from (select a1.*, rownum rn from(select ename,job from emp) a1 where rownum<=10)where rn>=5; 下面最主要介紹第三種：按rownum來分 1. rownum 分頁 SELECT * FROM emp; 2. 顯示rownum[oracle分配的] SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e; rn相當于Oracle分配的行的ID號 3.挑選出6—10條記錄 先查出1-10條記錄 SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10; 如果后面加上rownum>=6是不行的， 4. 然后查出6-10條記錄 SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 5. 幾個查詢變化 a. 指定查詢列，只需要修改最里層的子查詢 只查詢雇員的編號和工資 SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; b. 排序查詢，只需要修改最里層的子查詢 工資排序后查詢6-10條數(shù)據(jù) SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10) WHERE rn >= 6; ? 用查詢結果創(chuàng)建新表 這個命令是一種快捷的建表方式 CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp; 創(chuàng)建好之后，desc mytable;和select * from mytable;看看結果如何? 合并查詢 ? 合并查詢 有時在實際應用中，為了合并多個select語句的結果，可以使用集合操作符號union，union all，intersect，minus 多用于數(shù)據(jù)量比較大的數(shù)據(jù)局庫，運行速度快。 1). union 該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中重復行。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 2).union all 該操作符與union相似，但是它不會取消重復行，而且不會排序。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION ALL SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
Oracle 筆記
13
該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中重復行。 3). intersect 使用該操作符用于取得兩個結果集的交集。 SELECT ename, sal, job FROM emp WHERE sal >2500 INTERSECT SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 4). minus 使用改操作符用于取得兩個結果集的差集，他只會顯示存在第一個集合中，而不存在第二個集合中的數(shù)據(jù)。 SELECT ename, sal, job FROM emp WHERE sal >2500 MINUS SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; (MINUS就是減法的意思) 創(chuàng)建數(shù)據(jù)庫有兩種方法： 1). 通過oracle提供的向導工具。√ database Configuration Assistant 【數(shù)據(jù)庫配置助手】 2).我們可以用手工步驟直接創(chuàng)建。
7.java操作oracle 內容介紹 1.上節(jié)回顧 2.java程序如何操作oracle √ 3.如何在oracle中操作數(shù)據(jù) 4.oracle事務處理 5.sql函數(shù)的使用 √ 期望目標 1.掌握oracle表對數(shù)據(jù)操作技巧 2.掌握在java程序中操作oracle 3.理解oracle事物概念 4.掌握oracle各種sql函數(shù) java連接oracle ? 介紹 前面我們一直在plsql中操作oracle，那么如何在java 程序中操作數(shù)據(jù)庫呢? 下面我們舉例說明，寫一個java，分頁顯示emp表的用戶信息。
package com.sp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//演示 如何使用 jdbc_odbc橋連接方式
public class TestOracle {
public static void main(String[] args) {
try {
// 1.加載驅動
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Oracle 筆記
14
// 2.得到連接
Connection ct = DriverManager.getConnection(
"jdbc.odbc:testConnectOracle", "scott",
"tiger");
// 從下面開始，和SQL Server一模一樣
Statement sm = ct.createStatement();
ResultSet rs = sm.executeQuery("select * from emp");
while (rs.next()) {
//用戶名
System.out.println("用戶名： "+rs.getString(2));
//默認是從1開始編號的
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
在得到連接那里，要去配置數(shù)據(jù)源，點擊控制面板-->系統(tǒng)和安全-->管理工具-->數(shù)據(jù)源(ODBC), 打開后點添加,如圖： 可以看到，有個Oracle in OraDb10g_home1的驅動，它是Oracle安裝完后自動加上去的。 選中 后，點完成，再填如下信息,如圖： 這樣配好后基本就可以了，但為了安全起見，建議大家測試一下，點擊 Test Connection按鈕， 測試通過后點ok,然后數(shù)據(jù)源就生成了，如圖： 然后把數(shù)據(jù)源名稱寫進jdbc.odbc:???里。 這里要注意：jdbcodbc能不能遠程連接呢?不能遠程連接，也就是你這樣寫的話就意味著java程 序和oracle數(shù)據(jù)庫應該是在同一臺機器上，因為這里沒有指定IP地址，肯定默認就是本地。 如 果要遠程連，就用jdbc,jdbc是可以遠程連的。 運行TestOracle.java,控制臺輸出....................... 可惜我沒運行成功，說 java.sql.SQLException: No suitable driver found for jdbc.odbc:testConnectOracle at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at com.sp.TestOracle.main(TestOracle.java:18) 不知道為什么。。。 接下來講解用JDBC的方式連接Oracle
package com.sp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//使用 jdbc連接oracle
public class TestOracle2 {
public static void main(String[] args) {
try {
// 1.加載驅動
Oracle 筆記
15
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到連接
Connection ct = DriverManager.getConnection
("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
// 從下面開始，和SQL Server一模一樣
Statement sm = ct.createStatement();
ResultSet rs = sm.executeQuery("select * from emp");
while (rs.next()) {
//用戶名
System.out.println("用戶名： "+rs.getString(2));
//默認是從1開始編號的
}
} catch (Exception e) {
e.printStackTrace();
}
}
} 記得要把驅動包引入，classes12.jar 運行，。。。。 再次可惜，我還是沒運行成功，錯誤是： java.sql.SQLException: Io 異常: The Network Adapter could not establish the connection at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334) at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:418) at oracle.jdbc.driver.OracleDriver.getConnectionInstance (OracleDriver.java:521) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:325) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at com.sp.TestOracle2.main(TestOracle2.java:18) 我也不知道為什么。。。 幽怨了。。 接下來建個web project，來測試oracle的分頁，挺麻煩，不記錄了。。 在oracle中操作數(shù)據(jù) - 使用特定格式插入日期值 ? 使用 to_date函數(shù) 請大家思考： 如何插入列帶有日期的表，并按照年-月-日的格式插入? insert into emp values (9998, 'xiaohong', 'MANAGER', 7782, to_date('1988-12- 12', 'yyyy-mm-dd'), 78.9, 55.33, 10); 注意： insert into emp values (9998, 'xiaohong', 'MANAGER', 7782, '12-12月-1988', 78.9, 55.33, 10); 這句語句是可以成功運行的 使用子查詢插入數(shù)據(jù) ? 介紹 當使用valus子句時，一次只能插入一行數(shù)據(jù)，當使用子查詢插入數(shù)據(jù)時，一條inset語句可以插
Oracle 筆記
16
入大量的數(shù)據(jù)。當處理行遷移或者裝載外部表的數(shù)據(jù)到數(shù)據(jù)庫時，可以使用子查詢來插入數(shù)據(jù)。 把emp表中10號部門的數(shù)據(jù)導入到新表中 create table kkk(myId number(4), myName varchar2(50), myDept number(5)); insert into kkk (myId, myName, myDept) select empno, ename, deptno from emp where deptno = 10; ? 介紹 使用update語句更新數(shù)據(jù)時，既可以使用表達式或者數(shù)值直接修改數(shù)據(jù)，也可以使用子查詢修改 數(shù)據(jù)。 問題：希望員工SCOTT的崗位、工資、補助與SMITH員工一樣。 update emp set(job, sal, comm)=(select job, sal, comm from emp where ename='SMITH') where ename='SCOTT';
8.oracle中事務處理
? 什么是事務 事務用于保證數(shù)據(jù)的一致性，它由一組相關的dml語句組成，該組的dml(數(shù)據(jù)操作語言，增刪改，沒有查詢)語句要么全部成功，要么全部失敗。 如：網(wǎng)上轉賬就是典型的要用事務來處理，用于保證數(shù)據(jù)的一致性。 dml 數(shù)據(jù)操作語言 銀行轉賬、QQ申請、車票購買 ? 事務和鎖 當執(zhí)行事務操作時(dml語句)，oracle會在被作用的表上加鎖，防止其它用戶修改表的結構。這里對我們的用戶來來講是非常重要的。 .....其它進程排序，知道1號進程完成，鎖打開，2號進程進入。依次進行，如果有進程級別較高的，可以插隊。 ? 提交事務 當執(zhí)行用commit語句可以提交事務。當執(zhí)行了commit語句之后，會確認事務的變化、結束事務。刪除保存點、釋放鎖，當使用commit語句結束事務之后，其它會話將可以查看到事務變化后的新數(shù)據(jù)。 保存點就是為回退做的。保存點的個數(shù)沒有限制 ? 回退事務 在介紹回退事務前，我們先介紹一下保存點(savepoint)的概念和作用。保存點是事務中的一點。用于取消部分事務，當結束事務時，會自動的刪除該事務所定義的所有保存點。當執(zhí)行rollback時，通過指定保存點可以回退到指定的點，這里我們作圖說明。 ? 事務的幾個重要操作 1.設置保存點 savepoint a 2.取消部分事務 rollback to a 3.取消全部事務 rollback 注意：這個回退事務，必須是沒有commit前使用的;如果事務提交了，那么無論你剛才做了多少個保存點，都統(tǒng)統(tǒng)沒有。 如果沒有手動執(zhí)行commit,而是exit了，那么會自動提交 ? java程序中如何使用事務 在java操作數(shù)據(jù)庫時，為了保證數(shù)據(jù)的一致性，比如賬戶操作(1)從一個賬戶中減掉10$(2)在另一個賬戶上加入10$,我們看看如何使用事務?
package com.sp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
Oracle 筆記
17
import java.sql.Statement;
public class TestTrans {
public static void main(String[] args) {
try {
// 1.加載驅動
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到連接
Connection ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
Statement sm = ct.createStatement();
// 從scott的sal中減去100
sm.executeUpdate("update emp set sal=sal-100 where ename='SCOTT'");
int i = 7 / 0;
// 給smith的sal加上100
sm.executeUpdate("update emp set sal=sal+100 where ename='SMITH'");
// 關閉打開的資源
sm.close();
ct.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
運行，會出現(xiàn)異常，查看數(shù)據(jù)庫，SCOTT的sal減了100，但是SMITH的sal卻不變，很可怕。。。 我們怎樣才能保證，這兩個操作要么同時成功，要么同時失敗呢?
package com.sp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestTrans {
public static void main(String[] args) {
Connection ct = null;
try {
// 1.加載驅動
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.得到連接
ct = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
// 加入事務處理
ct.setAutoCommit(false);// 設置不能默認提交
Statement sm = ct.createStatement();
// 從scott的sal中減去100
sm.executeUpdate("update emp set sal=sal-100 where ename='SCOTT'");
int i = 7 / 0;
Oracle 筆記
18
// 給smith的sal加上100
sm.executeUpdate("update emp set sal=sal+100 where ename='SMITH'");