課程
java語(yǔ)言 java基本編程 -> j2EE編程
數(shù)據(jù)庫(kù)的能力 sqlserver -> ORACLE
數(shù)據(jù)結(jié)構(gòu) 訓(xùn)練編程思維方式 實(shí)際問(wèn)題到計(jì)算機(jī)問(wèn)題的轉(zhuǎn)化
linux 熟悉一種新的操作系統(tǒng)
軟件工廠(chǎng) 學(xué)到的知識(shí) 去做一個(gè)項(xiàng)目
課程安排
linux操作系統(tǒng) 4天
數(shù)據(jù)結(jié)構(gòu) 5天
DOTNET介紹 4天
ORACLE
ORACLE數(shù)據(jù)庫(kù) 10天
XML語(yǔ)言 5天
J2EE
JSP/servlet 12天
EJB 3天
struts 4天
軟件工廠(chǎng) 2周
找工作的技能
j2EE + XML + ORACLE/SQLSERVER
基礎(chǔ) java html javascript sql
ORACLE數(shù)據(jù)庫(kù)
甲骨文 英文中 意思是神寓 神說(shuō)的話(huà)
復(fù)雜性
公司野心
1973年
i internet
g grid (網(wǎng)格)
ERP 企業(yè)資源計(jì)劃 Enterprise Resource Plan
MIS 管理信息系統(tǒng) Management Information System
德國(guó) SAP
ORACLE application 11i
別的數(shù)據(jù)庫(kù)
DB2 IBM公司 IBM產(chǎn)品綁定緊密 (windows unix/linux)
sybase sybase公司 電信和銀行 (windows unix/linux)
sqlserver windows平臺(tái)
小型
access office套件
mysql PHP(Personal HomePage)(linux) +
mysql(不支持存儲(chǔ)過(guò)程,觸發(fā)器等)
學(xué)習(xí)方法
動(dòng)手能力
做會(huì)的 不是書(shū)本學(xué)會(huì)的
找問(wèn)題的答案：
問(wèn)同學(xué) 問(wèn)老師
從網(wǎng)上 找答案
搜索引擎 www.google.com
百度
錯(cuò)誤號(hào) 錯(cuò)誤文字放入關(guān)鍵字中
提取關(guān)鍵字 多實(shí)驗(yàn)
ORACLE安裝
ORACLE +“安裝”
ORACLE安裝和刪除
ORACLE9i 9.0.1 空間1.76G
ORACLE9i 9.0.2 空間2.86G
事務(wù)處理 -- 增 刪除 修改頻繁
數(shù)據(jù)倉(cāng)庫(kù) -- 查詢(xún) 頻繁
GBK --多200個(gè)字左右(偏僻字)
GB2312
SETUP.exe 雙擊
啟動(dòng)ORACLE數(shù)據(jù)庫(kù) OracleServiceACCP 服務(wù) 多啟動(dòng)幾次
OracleOraHome90TNSListener 服務(wù)
如果啟動(dòng)不了Listener(監(jiān)聽(tīng)器)
<1>改注冊(cè)表
ImagePath D:\oracle\ora90\BIN\TNSLSNR
<2>用命令行啟動(dòng)
cmd -> tnslsnr 最小化
tNSlsnter 監(jiān)聽(tīng)器 如果無(wú)法啟動(dòng)
運(yùn)行cmd 切換到DOS界面 敲tnslsnr 窗口不要關(guān)閉
<3>D:\oracle\ora90\network\ADMIN
listener.ora 文本文件
HOST=localhost或機(jī)器名
存盤(pán)
用工具 ORACLE SQL*PLUS驗(yàn)證數(shù)據(jù)庫(kù)是否啟動(dòng)
用戶(hù)名system 密碼manager
出現(xiàn) 連接到 和 SQL>符號(hào) 說(shuō)明數(shù)據(jù)庫(kù)已經(jīng)可用了
默認(rèn)用戶(hù):
超級(jí)用戶(hù): 用戶(hù)名system/密碼manager
用戶(hù)名sys/密碼change_on_install
普通的用戶(hù): 用戶(hù)名scott/密碼tiger
注意的問(wèn)題:安裝的源程序放在什么目錄下??
<1>目錄中不要出現(xiàn)中文名字的目錄
<2>數(shù)據(jù)庫(kù)的名字不要是中文,8個(gè)字符以?xún)?nèi)
<3>字符集用默認(rèn)的ZHS16GBK
刪除:
在硬盤(pán)上的一個(gè)隱含安裝的目錄Programm Files下的ORACLE
操作ORACLE數(shù)據(jù)庫(kù)的工具
ORACLE SQL*PLUS 字符界面 ORACLE自帶
sqlplusw.exe windows窗口下的sqlplus
sqlplus.exe DOS下的sqlplus
用戶(hù)名scott 密碼tiger 普通用戶(hù)
system manager 超級(jí)用戶(hù)
sys change_on_install 超級(jí)用戶(hù) 比system權(quán)限還要大
SQLPLUS下的常用命令
show user 察看用戶(hù)
connect 用戶(hù)名/密碼 連接到指定用戶(hù)
connect scott/tiger
select * from tab where tabtype='TABLE'; 察看當(dāng)前用戶(hù)下的表
select count(*) from dept; 查詢(xún)表dept中記錄的行數(shù)
desc dept 察看表dept的結(jié)構(gòu)
quit/exit 退出
clear screen 清除屏幕
set linesize 200 設(shè)置一行顯示200個(gè)字符
set pagesize 20 設(shè)置每頁(yè)顯示20行
spool 文件名 (spool c:\abc.txt) 作日志文件命令
spool off
修改D:\oracle\ora90\sqlplus\admin\glogin.sql文件
可以設(shè)置SQLPLUS的環(huán)境
set linesize 200
set pagesize 20
ORACLE 企業(yè)管理器 (OEM) 圖形界面
PL/SQL Developer 第三方軟件 ORACLE不帶的
SQLPLUS /nolog nolog是不登陸
瀏覽器使用oracle (isqlplus)
D:\oracle\ora90\Apache\Apache\conf\httpd.conf
修改80端口 -> 8001
http://localhost:8001/isqlplus
ORACLE中字段的數(shù)據(jù)類(lèi)型
字符型 char 范圍 最大2000個(gè)字節(jié) 定長(zhǎng)
char(10) '張三' 后添空格6個(gè)把10個(gè)字節(jié)補(bǔ)滿(mǎn) '張三 '
性別 char(2) '男'
varchar2 范圍 最大4000個(gè)字節(jié) 變長(zhǎng)
varchar2(10) '張三' 在數(shù)據(jù)庫(kù)中'張三'
大對(duì)象 字符型大對(duì)象 >4000字節(jié) 最大4G
CLOB (Character Large OBject)
數(shù)字 number 范圍 10的-38次方 到10的38次方
可以表示小數(shù) 也可以表示整數(shù)
number(4) 最大表示4位整數(shù) -9999 到 9999
number(5,2) 表示5位有效數(shù)字 2位小數(shù)的 一個(gè)小數(shù) -999.99 到 999.99
日期 date 包含年月日和時(shí)分秒 7個(gè)字節(jié)
圖片 blob 二進(jìn)制大對(duì)象 圖像/聲音 4G
如何建表
學(xué)生表student
create table student( --學(xué)生表
xh number(4), --學(xué)號(hào)
xm varchar2(10), --姓名
sex char(2), --性別
birthday date, --日期
sal number(7,2) --獎(jiǎng)學(xué)金
);
班級(jí)class
create table class( --班級(jí)表
classid number(2), --班級(jí)編號(hào)
cname varchar2(20) --班級(jí)名字
);
添加字段(學(xué)生所在班級(jí)classid)
alter table student add (classid number(2));
修改字段的長(zhǎng)度
alter table student modify (xm varchar2(12)) ;
修改字段的類(lèi)型(不能有記錄的)
alter table student modify (xh varchar2(5));
刪除一個(gè)字段
alter table student drop column sal;
刪除表
drop table student;
表的名字修改
rename student to stu;
字段如何改名字
--先刪除
a)alter table student drop column sal;
--再添加
b)alter table student add (salary number(7,2));
如何插入數(shù)據(jù)
插入數(shù)據(jù) insert語(yǔ)句
所有字段都插入
insert into student values ('A001','張三','男','01-5月-05',10);
ORACLE中默認(rèn)的日期格式'DD-MON-YY' dd 日子(天) mon 月份 yy 2位的年
'09-6月-99' 1999年6月9號(hào)
改日期的默認(rèn)格式
alter session set nls_date_format = 'yyyy-mm-dd';
insert into student values ('A002','MIKE','男','1905-05-06',10);
恢復(fù)ORACLE默認(rèn)格式
alter session set nls_date_format = 'dd-mon-yy';
察看日期的格式
set linesize 1000
select * from nls_session_parameters
where parameter='NLS_DATE_FORMAT';
永久設(shè)置日期格式
改注冊(cè)表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd
部分字段插入
insert into student(xh,xm,sex) values ('A003','JOHN','女');
插入空值
insert into student(xh,xm,sex,birthday) values ('A004','MARTIN','男',null);
修改 update
改一個(gè)字段
update student set sex='女' where xh='A001';
改多個(gè)字段
update student set sex='男',
birthday='1980-04-01'
where xh='A001';
改為空值 (修改為空時(shí)=null)
update student set birthday=null where xh='A001';
把生日為空的人的班級(jí)編號(hào)改為20(條件中的空是is null / is not null)
update student set classid=20 where birthday is null;
錯(cuò)誤的沒(méi)有達(dá)到要求
update student set classid=20
where birthday=null;
不表示空值 表示xm是null的字符串
update student set classid=20 where xm='null';
刪除 delete
delete from student; 刪除所有記錄,表結(jié)構(gòu)還在,寫(xiě)日志,可以恢復(fù)的,速度慢
drop table student; 刪除表的結(jié)構(gòu)和數(shù)據(jù)
delete from student where xh='A001'; 刪除一條記錄
truncate table student; 刪除表中的所有記錄,表結(jié)構(gòu)還在,不寫(xiě)日志,無(wú)法找回刪除的記錄,速度快
查詢(xún) select
select * from student;
select xh,xm,sex from student;
select * from student where xh like 'A%1'; %任意多個(gè)字符
select * from student where xh like 'A__1'; _1個(gè)字符
select * from student where xh like '%A%'; select * from student where xh like 'A%';
select * from student where xh like '%A';
select * from student where xh = 'A%';
select * from student
order by birthday ; 升序 (order by birthday asc;)
select * from student
order by birthday desc; --降序
select * from student
order by birthday desc,xh asc; --按birthday 降序 按xh升序(asc/默認(rèn))
select * from student
where sex='女' or birthday='1999-02-01';
select * from student
where sex='女' and birthday='1999-02-01';
select * from student
where salary > 20 and xh <> 'B002'; (!=)
ORALCE的函數(shù)
單行函數(shù) 返回值只有一個(gè)
分組函數(shù) 返回值是多條記錄
group by
sum
avg
單行函數(shù)
字符函數(shù)
concat 連接 ||
<1>顯示dname和loc中間用-分隔
select deptno,dname||'----'||loc from dept;
dual啞元表 沒(méi)有表需要查詢(xún)的時(shí)候 可以用它
select 'Hello World' from dual;
select 1+1 from dual;
查詢(xún)系統(tǒng)時(shí)間
select sysdate from dual;
<2> initcap 首字母大寫(xiě)
select ename,initcap(ename) from emp;
<3> lower 轉(zhuǎn)換為小寫(xiě)字符
select ename,lower(ename) from emp;
<4> upper 轉(zhuǎn)換為大寫(xiě)
update dept set loc=lower(loc);
update dept set loc=upper(loc);
<5> LPAD 左填充
select deptno,lpad(dname,10,' '),loc from dept;
<6> RPAD 右填充
<7> LTRIM 去除左邊的空格
RTRIM 去除右邊的空格
ALLTRIM 去除兩邊的空格
<8>replace 替換
translate 轉(zhuǎn)換
select ename,replace(ename,'S','s') from emp;
用's'去替換ename中的'S'
select ename,translate(ename,'S','a') from emp;
<9> ASCII 求ASC碼
chr asc碼變字符
select ascii('A') from dual;
select chr(97) from dual;
select 'Hello'||chr(9)||'World' from dual;
'\t' ascii碼是 9
'\n' ascii碼是 10
select 'Hello'||'\t'||'World' from dual;
<10> substr 字符截取函數(shù)
select ename,substr(ename,1,3) from emp;
從第1個(gè)位置開(kāi)始 顯示3個(gè)字符
select ename,substr(ename,4) from emp;
從第4個(gè)位置開(kāi)始顯示后面所有的字符
<11> instr 測(cè)試字符串出現(xiàn)的位置
select ename,instr(ename,'S') from emp;
'S'第1次出現(xiàn)的位置
select ename,instr(ename,'T',1,2) from emp;
從第1個(gè)位置開(kāi)始 測(cè)試'T'第2次出現(xiàn)的位置
<12> length 字符串的長(zhǎng)度
select ename,length(ename) from emp;
日期和 時(shí)間函數(shù)
<1> sysdate 系統(tǒng)時(shí)間
select sysdate from dual;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'DDD') from dual
select to_char(sysdate,'D') from dual
select to_char(sysdate,'DAY') from dual
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
select '''' from dual;
select to_char(sysdate,'SSSSS') from dual;
--從今天零點(diǎn)以后的秒數(shù)
<2> ADD_MONTHS 添加月份 得到一個(gè)新的日期
select add_months(sysdate,1) from dual;
select add_months(sysdate,-1) from dual;
select trunc(sysdate)-to_date('20050101','yyyymmdd') from dual;
select add_months(sysdate,12) from dual;
一年以后的今天
select add_months(sysdate,-12) from dual;
一年以前的今天
trunc(sysdate) 截取年月日
select sysdate+2 from dual;
數(shù)字代表的是天數(shù)
兩個(gè)日期之間的差值代表天數(shù)
<3> last_day 某月的最后一天
select last_day(sysdate) from dual;
select add_months(last_day(sysdate)+3,-1) from dual;
本月第3天的日期
<4> months_between 兩個(gè)日期之間的月數(shù)
select months_between(sysdate,'2005-02-01') from dual;
方向 sysdate - '2005-02-01'
select months_between('2005-02-01',sysdate) from dual;
轉(zhuǎn)換函數(shù)
to_char 把日期或數(shù)字類(lèi)型變?yōu)樽址?br />
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
select sal,to_char(sal,'L9,999') from emp;
L本地貨幣
to_number 把字符串變成數(shù)字
select to_number('19990801') from dual;
to_date 把字符串變成日期
select to_date('19800101','yyyymmdd') from dual;
select to_char(to_date('19800101','yyyymmdd'),
'yyyy"年"mm"月"dd"日"') from dual;
數(shù)學(xué)函數(shù)
ceil(x) 不小于x的最小整數(shù)
ceil（12.4) 13
ceil(-12.4) -12
floor(x) 不大于x的最大整數(shù)
floor(12.5) 12
floor(-12.4) -13
round(x) 四舍五入
round(12.5) 13
round(12.456,2) 12.46
trunc(x) 舍去尾數(shù)
trunc(12.5) 12
trunc(12.456,2) 12.45
舍去日期的小時(shí)部分
select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual;
mod(x,n) x除以n以后的余數(shù)
mod(5,2) 1
mod(4,2) 0
power(x,y) x的y次方
select power(3,3) from dual;
混合函數(shù)
求最大值
select greatest(100,90,80,101,01,19) from dual;
求最小值
select least(100,0,-9,10) from dual;
空值轉(zhuǎn)換函數(shù) nvl(comm,0) 字段為空值 那么就返回0 否則返回本身
select comm,nvl(comm,0) from emp;
comm 類(lèi)型和 值的類(lèi)型是 一致的
復(fù)雜的函數(shù)
decode 選擇結(jié)構(gòu) (if ... elseif .... elesif ... else結(jié)構(gòu))
要求:
sal=800 顯示低工資
sal=3000 正常工資
sal=5000 高工資
只能做等值比較
select sal,decode(sal,800,'低工資',3000,'正常工資',5000,'高工資','沒(méi)判斷')
from emp;
表示如下的if else 結(jié)構(gòu)
if sal=800 then
'低工資'
else if sal =3000 then
'正常工資'
else if sal = 5000 then
'高工資'
else
'沒(méi)判斷'
end if
sal > 800 sal -800 > 0
判斷正負(fù)
sign(x) x是正 1
x是負(fù) -1
x是0 0
select sign(-5) from dual;
如何做大于小于的比較？？？？
sal<1000 顯示低工資 sal-1000<0 sign(sal-1000) = -1
1000<=sal<=3000 正常工資
3000<sal<=5000 高工資
select sal,decode(
sign(sal-1000),-1,'低工資',
decode(sign(sal-3000),-1,'正常工資',
0,'正常工資',1,
decode(sign(sal-5000),-1,'高工資','高工資')
)) as 工資狀態(tài) from emp;
一般的情況 decode(x,y1,z1,y2,z2,z3)
if x= y1 then
z1
else if x = y2 then
z2
else
z3
end if
分組函數(shù) 返回值是多條記錄 或計(jì)算后的結(jié)果
group by
sum
avg
<1> 計(jì)算記錄的條數(shù) count
select count(*) from emp;
select count(1) from emp;
select count(comm) from emp; 字段上count 會(huì)忽略空值
comm不為空值的記錄的條數(shù)
統(tǒng)計(jì)emp表中不同工作的個(gè)數(shù) ????
select count(distinct job) from emp;
select distinct job from emp;
select distinct job,empno from emp;
select job,empno from emp;
得到的效果是一樣的,distinct 是消去重復(fù)行
不是消去重復(fù)的列
<2>group by 分組統(tǒng)計(jì)
--在沒(méi)有分組函數(shù)的時(shí)候
--相當(dāng)于distinct 的功能
select job from emp group by job;
select distinct job from emp;
--有分組函數(shù)的時(shí)候
--分組統(tǒng)計(jì)的功能
統(tǒng)計(jì)每種工作的工資總額是多少??
select job,sum(sal) from emp
group by job; --行之間的數(shù)據(jù)相加
select sum(sal) from emp; --公司的工資總額
統(tǒng)計(jì)每種工作的平均工資是多少??
select job,avg(sal) from emp
group by job;
select avg(saL) from emp; --整個(gè)公司的平均工資
顯示平均工資>2000的工作?？？
<1>統(tǒng)計(jì)每種工作的平均工資是多少
<2>塞選出平均工資>2000的工作
從分組的結(jié)果中篩選 having
select job,avg(sal) from emp
group by job
having avg(sal) > 2000;
group by 經(jīng)常和having搭配來(lái)篩選
計(jì)算工資在2000以上的各種工作的平均工資？？？？
select job,avg(sal) from emp
where sal > 2000
group by job
having avg(sal) > 3000;
一般group by 和 having搭配
表示對(duì)分組后的結(jié)果的篩選
where子句 --- 用于對(duì)表中數(shù)據(jù)的篩選
<3> max min
select max(sal) from emp;
公司的最高工資
select min(sal) from emp ;
公司的最低工資
找每個(gè)部門(mén)的最高和最低的工資??
select deptno,max(sal),min(sal) from emp
group by deptno;
找每個(gè)工作的最高和最低的工資??
select job,max(sal),min(sal) from emp
group by job;
找每個(gè)部門(mén)中每種工作的最高和最低的工資??
select deptno,job,max(sal),min(sal)
from emp
group by deptno,job;
select max(sal),min(sal)
from emp
group by deptno,job;
單個(gè)字段如果沒(méi)有被分組函數(shù)所包含，
而其他字段又是分組函數(shù)的話(huà)
一定要把這個(gè)字段放到group by中去
<4>關(guān)聯(lián)查詢(xún)
多張表,而表與表之間是有聯(lián)系的
是通過(guò)字段中的數(shù)據(jù)的內(nèi)在聯(lián)系來(lái)發(fā)生
而不是靠相同的字段名來(lái)聯(lián)系的或者是否有主外鍵的聯(lián)系是沒(méi)有關(guān)系的
select dname,ename from emp,dept;
笛卡爾積 (無(wú)意義的)
--當(dāng)2個(gè)表作關(guān)聯(lián)查詢(xún)的時(shí)候一定要寫(xiě)關(guān)聯(lián)的條件
--N個(gè)表 關(guān)聯(lián)條件一定有N-1個(gè)
select dname,ename from mydept,myemp
where mydept.no = myemp.deptno;
多表查詢(xún)的時(shí)候一定要有關(guān)聯(lián)的條件
--使用的表的全名
select dname,ename from emp,dept
where emp.deptno = dept.deptno ;
--使用表的別名
select dname,ename,a.deptno from emp a,dept b
where a.deptno = b.deptno and a.deptno = 10;
--等值連接(內(nèi)連接-兩個(gè)表的數(shù)據(jù)作匹配a.deptno = b.deptno )
select dname,ename,a.deptno from
emp a inner join dept b
on a.deptno = b.deptno;
where a.deptno = 10;
--on寫(xiě)連接條件的
--where中寫(xiě)別的條件
--使用where/on
select dname,ename,a.deptno from emp a,dept b
where a.deptno = b.deptno and a.deptno=10;
--on中寫(xiě)連接條件
--where中寫(xiě)其他的條件
select dname,ename,a.deptno from
emp a inner join dept b
on a.deptno = b.deptno
where a.deptno = 10 ;
--外連接
左外連接 右外連接 全外連接
(+)寫(xiě)法只有在ORACLE中有效
select dname,ename,b.deptno
from emp a,dept b
where a.deptno(+) = b.deptno;
--標(biāo)準(zhǔn)寫(xiě)法
select dname,ename,b.deptno
from emp a right outer join dept b
on a.deptno = b.deptno;
select dname,ename,b.deptno
from emp a,dept b
where a.deptno = b.deptno(+);
--標(biāo)準(zhǔn)寫(xiě)法
select dname,ename,b.deptno
from emp a left outer join dept b
on a.deptno = b.deptno;
--標(biāo)準(zhǔn)寫(xiě)法(全外聯(lián))
select dname,ename,b.deptno
from emp a full outer join dept b
on a.deptno = b.deptno;
--自連接
select a.ename as 員工姓名,b.ename as 經(jīng)理名字 from emp a,emp b
where a.mgr = b.empno(+);
a.empno = b.mgr ???????
<5>子查詢(xún)
在select語(yǔ)句中嵌套了另一個(gè)select語(yǔ)句
1)where 子句中嵌套子查詢(xún)
2)用子查詢(xún)的結(jié)果 作為字段來(lái)出現(xiàn)
--1)where 子句中嵌套子查詢(xún),執(zhí)行順序是
先執(zhí)行子查詢(xún) 再執(zhí)行主查詢(xún)
找出工資高于公司平均工資的所有員工??
select * from emp where sal+nvl(comm,0) > (select avg(sal+nvl(comm,0)) from emp);
高于部門(mén)30中員工最高工資的其他員工???
select * from emp where sal+nvl(comm,0) > all (select sal+nvl(comm,0) from emp
where deptno = 30);
低于部門(mén)30中員工工資的其他員工???
select * from emp where sal+nvl(comm,0) < all (select sal+nvl(comm,0) from emp
where deptno = 30);
select * from emp where sal+nvl(comm,0) < any (select sal+nvl(comm,0) from emp
where deptno = 30);
--2）用子查詢(xún)的結(jié)果 作為字段來(lái)出現(xiàn)
先執(zhí)行主查詢(xún) 再執(zhí)行子查詢(xún)
<1>找員工姓名和直接上級(jí)的名字
select ename as 員工姓名,(select ename from emp where empno = a.mgr) as 經(jīng)理姓名
from emp a;
<2>顯示部門(mén)名稱(chēng)和人數(shù)
select dname,(select count(*) from emp where deptno=a.deptno) as rs from dept a;
<3>顯示每個(gè)部門(mén)的最高工資的員工
select * from emp a where (deptno, sal) in ( select deptno,max(sal) from emp group by deptno);
select a.* from emp a,( select deptno,max(sal) as msal from emp group by deptno) c where a.deptno = c.deptno and
a.sal = c.msal;
--最大值和最小值的比較 轉(zhuǎn)化為人數(shù)的比較
select * from emp a where (select count(*) from
emp where deptno = a.deptno and
sal > a.sal) = 0 and a.deptno is not null;
<4>顯示每個(gè)部門(mén)的工資前2名的員工
select * from emp a where (select count(*) from
emp where deptno = a.deptno and
sal > a.sal) <=1 and a.deptno is not null;
<6> 層次查詢(xún)
--level 偽列 層次查詢(xún)的時(shí)候可以使用的 層的編號(hào)
select lpad('+',level,' ')||ename from emp
connect by prior empno = mgr --父子關(guān)系 父結(jié)點(diǎn)中的empno = 子節(jié)點(diǎn)中的mgr
start with mgr is null;--從 mgr is null的節(jié)點(diǎn) 開(kāi)始遍歷
select lpad('+',level,' ')||ename from emp
connect by prior empno = mgr
start with ename = 'BLAKE';
<7> TOP 前幾行 (sqlserver)
rownum (oracle偽列)
--emp表的前2行
--rownum必須使用<=的關(guān)系比較運(yùn)算符
select * from emp where rownum <= 2;
select top 2 * from emp; --sqlserver的寫(xiě)法
select * from emp where rownum = 1;
--公司工資最高的2個(gè)人
/*select * from emp
where rownum <= 2
order by sal desc;*/ 錯(cuò)誤的
select * from (select * from emp order by sal desc)
where rownum <= 2;
--分頁(yè)查詢(xún)
--每頁(yè)顯示4行 一共有14行記錄
第1頁(yè) 第1-4行
第2頁(yè) 第5-8行
第3頁(yè) 第9-12行
第4頁(yè) 第13-14行
--希望每次頁(yè)面顯示的時(shí)候 都只查詢(xún)回需要的記錄
select * from (select rownum as num,emp.* from emp)
where num >= 9 and num <= 12;
select * from (select rownum as num,emp.* from emp)
where num >= 13 and num <= 14;
<例子>
建立表如下:
學(xué)生基本信息表
CREATE Student(
[Studentid][Int]IDENTITY(1,1)NOT NULL primary key,--主鍵
[StudentName][char]NOT NULL
)
課程信息表
CREATE Subject(
[SubjectID][char]NOT NULL primary key, --主鍵
[SubjectName][char]NOT NULL
)
成績(jī)表
CREATE Grade(
[Studentid][Int]NOT NULL, --聯(lián)合主鍵
[SubjectID][char]NOT NULL, --聯(lián)合主鍵
[Grade] [INT]NOT NULL,
primary key (studentid,subjectid)
)
1.將建表命令改為ORACLE語(yǔ)句在ORACLE中建表
create table student( --學(xué)生表
studentid number(3) primary key, --學(xué)生編號(hào)
studentname varchar2(20) --學(xué)生的姓名
);
create table subject( --課程表
subjectid char(3) primary key, --課程編號(hào)
subjectname varchar2(20) --課程的名字
);
create table grade( --分?jǐn)?shù)表
studentid number(3) references student(studentid), --學(xué)生id
subjectid char(3) references subject(subjectid), --課程id
mark number(3), --分?jǐn)?shù)
primary key (studentid,subjectid) --聯(lián)合主鍵
);
insert into student values (101,'張三');
insert into student values (102,'李云');
insert into student values (103,'未');
insert into subject values ('A01','C++');
insert into subject values ('A02','ASP');
insert into subject values ('A03','JAVA');
insert into grade values (101,'A01',59);
insert into grade values (101,'A02',72);
insert into grade values (101,'A03',90);
insert into grade values (102,'A01',75);
insert into grade values (102,'A02',91);
insert into grade values (103,'A01',71);
2.作如下4題
第一問(wèn)：查詢(xún)出以下信息
學(xué)號(hào) 學(xué)生姓名 課程名稱(chēng) 成績(jī) （要全部學(xué)生信息）
關(guān)聯(lián)查詢(xún) (多張表的)
別名
select a.studentid as "學(xué) 號(hào)",studentname "學(xué)生姓名",
subjectname "課程名稱(chēng)",mark "成 績(jī)"
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid;
[select a.studentid "學(xué) 號(hào)",studentname "學(xué)生姓名",
subjectname "課程名稱(chēng)",mark "成 績(jī)"
from student a , subject b , grade c] 笛卡爾積
3 * 3 * 6 = 54;
第二問(wèn)：查詢(xún)出以下信息
學(xué)號(hào) 學(xué)生姓名 課程名稱(chēng) 成績(jī)（只顯示每科最高分）
select a.studentid "學(xué) 號(hào)",studentname "學(xué)生姓名",
subjectname "課程名稱(chēng)",mark "成 績(jī)"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (subjectname,mark)
in (select subjectname "課程名稱(chēng)",max(mark) "成 績(jī)"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by subjectname)
(最高分---分?jǐn)?shù)比我高的學(xué)生的人數(shù)=0)
select a.studentid "學(xué) 號(hào)",studentname "學(xué)生姓名",
subjectname "課程名稱(chēng)",mark "成 績(jī)"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
and (select count(*) from grade
where subjectid = b.subjectid and
mark > c.mark) = 0
第三問(wèn)：查詢(xún)出以下信息
學(xué)號(hào) 學(xué)生姓名 課程名稱(chēng) 成績(jī) （成績(jī)大于60時(shí)的顯示及格，小于60時(shí)的顯示不及格）
select a.studentid "學(xué) 號(hào)",studentname "學(xué)生姓名",
subjectname "課程名稱(chēng)",
decode(sign(mark-60),-1,'不及格','及格') "成 績(jī)"
from student a,subject b,grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
第四問(wèn)：查詢(xún)出以下信息
學(xué)號(hào) 學(xué)生姓名 （查詢(xún)出課程超過(guò)1門(mén)以上學(xué)生的信息）
select a.studentid "學(xué) 號(hào)",studentname "學(xué)生姓名",
count(subjectname)
from student a , subject b , grade c
where a.studentid = c.studentid
and b.subjectid = c.subjectid
group by a.studentid,studentname
having count(subjectname) >= 2
-- select * from emp where rownum > 2; 錯(cuò)誤的