1.建表的語法
create table 表名(
字段名(列名) 字段類型 [約束 默認(rèn)值],
.....
字段名(列名) 字段類型 [約束 默認(rèn)值]
);
注意:1.表名,字段名,關(guān)鍵字大小寫忽略
2.表名,字段名不能使用關(guān)鍵字
3.表名,字段名不可以使用數(shù)字開頭,中間不能出現(xiàn)特殊符號
4.表名,字段名長度不能超過30個字符
5.表名,字段名定義需要有含義
2.創(chuàng)建一個用戶表 t_user
|-姓名name,字符串
|-性別gender,字符串
|-年齡age,數(shù)字型
create table t_user(
name varchar2(30),
gender varchar2(3),
age number
);
--注意: desc命令必須在命令窗口中執(zhí)行.在sql window中不能執(zhí)行
desc t_user;
3.Oracle中常見的數(shù)據(jù)類型
字符串
|-varchar2(length):可變長的字符串,length定義最長字符串的字節(jié)數(shù).
length最大值位4000,節(jié)省空間,查詢效率低
|-char(length):定長的字符串,length定義最長的字符串的字節(jié)數(shù),最大值2000
浪費空間,查詢效率高
|-varchar(length):等價于varchar2(length),varchar2是Oracle獨有的.
數(shù)字類型
|-number(p,s):p表示定義數(shù)字的長度(不包含小數(shù)點),s表示小數(shù)點后面的尾數(shù)
|-定義商品的價格 number(5,2),表示小數(shù)點前面有3個數(shù)字,后面有兩個數(shù)字
123.23 12.23(合法,在精度里面) 1234.10(不合法)
|-number:表示描述整數(shù)
日期類型
|-date:七個字節(jié),如果是英文環(huán)境 DD-MOR-RR "11-JUN-15" 如果是中文環(huán)境 15-7月-15
4.刪除表 drop table 表名
drop table test2;
create table test2(
str varchar(200)
);
5.創(chuàng)建表員工表 t_emp
|-員工id 長度最長為11的整數(shù)
|-員工姓名 name
|-員工工資 salary 12345.12
|-生日 birth 日期
|-職位 job 字符串
|-員工性別 gender
create table t_emp(
id number(11),
name varchar2(30),
salary number(7,2),
birth date,
job varchar2(30),
gender char(3)
);
--使用default設(shè)置字段的默認(rèn)值
drop table t_emp;--將性別默認(rèn)值為M
create table t_emp(
gender char(3) default 'm' /*單引號表示字符串*/
);
--使用not null設(shè)置字段值不能為空
create table t_emp(
name varchar2(30) not null
);
6.使用ALTER修改表結(jié)構(gòu)
--添加新的字段
語法:alter table 表名 add (新的字段 字段類型,...);
--想t_emp表中追加deptno的字段
alter table t_emp add(deptno number(11));
--刪除字段
語法:alter table 表名 drop column 字段名;
--刪除t_emp表中的gender字段
alter table t_emp drop column gender;
--修改列名
語法:alter table 表名 rename column 舊列名 to 新列名;
--將t_emp表中的deptno修改為dept_no
alter table t_emp rename column deptno to dept_no;
--修改字段類型
語法:alter table 表名 modify (列名 新類型,...);
--將t_emp表的salary的類型修改為number(5,2)
alter table t_emp modify (salary number(5,2));
7.truncate:刪除表中的數(shù)據(jù)
語法:truncate table 表名;
truncate只是清空表中的數(shù)據(jù),但是保留表的結(jié)構(gòu)
drop:將表結(jié)構(gòu)刪除
8.DML語句:操作表數(shù)據(jù)
|-8.1 插入數(shù)據(jù) insert into
|-語法 INSERT INTO 表名 [(字段名,...)] VALUES(值,...)
--注意:如果是向表中的所有字段添加數(shù)據(jù)時,可以省略字段名
--向t_emp表中插入數(shù)據(jù)
--開發(fā)中推薦使用明確字段名
insert into t_emp(id,name,salary,birth,job,dept_no)
values(1001,'yves',123.23,sysdate,'開發(fā)',10);
--不便于維護
insert into t_emp values(1003,'yvesHe',123.45,sysdate,'測試',20);
commit;--提交數(shù)據(jù)
查詢表中的所有數(shù)據(jù):select * from 表名;-- *通配符,表示所有的字段
|-8.2 刪除數(shù)據(jù) delete
|-語法:delete from 表名 [where 過濾條件];
delete from t_emp; --注意：將表中的數(shù)據(jù)全部刪除
/*刪除數(shù)據(jù)通常使用where條件
*/
//刪除id=1001的用戶
delete from t_emp where id=1001;/*where是過濾條件*/
delete和truncate的區(qū)別频祝？
|-1.delete屬于DML語句胚宦，需要事務(wù)的支持
|-2.truncate屬于DDL語句，無需事務(wù)的支持
|-3.delete需要回滾內(nèi)存空間，truncate無需回滾內(nèi)存空間
|-4.delete性能要低于truncate
|-5.delete保留高水位線烘挫，truncate刪除高水位線
/*
DML語句需要事務(wù)的管理：
commit提交數(shù)據(jù)
rollback回滾數(shù)據(jù)
*/
|-8.3 修改記錄 update
|-語法：update 表名 set 字段名=值[,....] [where 過濾條件];
update t_emp set name='jerry';
//將id=1001的用戶名改為 jerry,工資改為 888
update t_emp set name='jerry',salary=888 where id=1001;
9.事務(wù)控制語句(配合DML語句一起使用)
commit:提交事務(wù)
rollback:事務(wù)回滾
savepoint 事務(wù)保存點
create table temp( id number);
insert into temp values(1);
insert into temp values(2);
savepoint A;--設(shè)置事務(wù)的保存點
insert into temp values(3);
insert into temp values(4);
insert into temp values(5);
savepoint B;
insert into temp values(6);
insert into temp values(7);
savepoint C;
insert into temp values(8);
rollback to B;--回滾到保存點B,數(shù)據(jù)到5
rollback to A;--可以成功,數(shù)據(jù)到2
/*rollback to C 報錯,事務(wù)已經(jīng)到了B,C不存在了*/
select * from temp;
?????????????????????????????????
1.補充的函數(shù)
coalesce(參數(shù)列表):返回參數(shù)列表中第一個非空參數(shù),最后一個參數(shù)通常為常量
--案例:
年終提成:
|-1.如果員工的comm不為空,發(fā)comm
|-2.如果員工的comm為空,發(fā)工資的一半
|-3.如果sal和comm為空,發(fā)100安慰
select ename,sal,comm,coalesce(comm,sal*0.5,100) comms
from emp;
case語句:類似于java中的switch語句
case 表達式
when 值 then 執(zhí)行的語句 /*沒有逗號*/
...
when 值 then 執(zhí)行的語句
else 執(zhí)行語句 /*類似于switch語句中的default*/
end;
--加薪
|-ANALYST職位,提高10%
|-SALESMAN,提高5%
|-CLERK,提供2%
select ename,sal,job,
case job
when 'ANALYST' then sal*1.1
when 'SALESMAN' then sal*1.05
when 'CLERK' then sal*1.02
else sal
end new_sal
from emp;
decode(判斷條件,匹配1,值1,匹配2,值2,...,默認(rèn)值):函數(shù),是case語句的簡寫
--加薪
|-ANALYST職位,提高10%
|-SALESMAN,提高5%
|-CLERK,提供2%
select ename,sal,job,
decode(job,'ANALYST',sal*1.1,'SALESMAN',sal*1.05,'CLERK',sal*1.02,sal) new_sal
from emp;
2.單行函數(shù):一條數(shù)據(jù)經(jīng)過函數(shù)處理獲得一條結(jié)果.
組函數(shù):多條記錄經(jīng)過組函數(shù)的處理只會得到一條結(jié)果
常見的組函數(shù)
|-count(表達式):計數(shù)
--統(tǒng)計員工表中有多少個員工
select count(*) from emp;
select count(ename) from emp;
--員工表中有多少個員工有提成
select count(comm) from emp;--可以進行空值處理
--員工表中有多少個職位
select count(distinct job) from emp;
|-max():最大值,min()最小值,avg()平均值,sum()求和
--員工表中最高的工資
select max(sal),min(sal),sum(sal),count(ename),avg(sal) from emp;
--求平均值是需要注意空值處理
select sum(comm),avg(nvl(comm,0)),count(ename) from emp;
--獲得每個職位的平均工資,最大值,最小值
group by:分組的關(guān)鍵子
select job,max(sal),min(sal),avg(sal)
from emp
group by job;--表示根據(jù)職位進行分組
--獲得每個部門的平均工資,最大值,最小值
select deptno ,max(sal),min(sal),round(avg(sal)) avg_sal
from emp
group by deptno
order by avg_sal;
注意:如果select后面出現(xiàn)了組函數(shù),那么其他內(nèi)容必須出現(xiàn)在 group by 子句中.
--獲得平均工資大于2000的職位
select job,round(avg(sal)) avg_sal
from emp
group by job
having round(avg(sal))>2000;/having是對分組后的結(jié)果進行過濾,不能使用別名/
having和where的區(qū)別
|-where是對表中的數(shù)據(jù)進行過濾,先執(zhí)行
|-having是對分組后的結(jié)果進行過濾,如果有where先執(zhí)行where然后分組
--獲得管理者下面的員工的平均工資, 最低工資不能少于800,沒有管理者的不參與統(tǒng)計
select mgr,round(avg(sal)),min(sal)
from emp
where mgr is not null /1.where先執(zhí)行/
group by mgr /2.分組/
having min(sal)>800; /3.對分組的結(jié)果進行過濾/
--獲得各個部門中工資大于1500的員工人數(shù)(先過濾再分組)
select deptno,count(*)
from emp
where sal>1500 /*先過濾*/
group by deptno;
--平均工資最高的部門
select max(avg(sal))
from emp
group by deptno;
--那些職位的人數(shù)超過2個人,對人數(shù)進行排序
select job,count(ename) nums
from emp
group by job
having count(ename)>2
order by nums ; /注意order by需要保存到最后面/
--薪水最高的員工
select max(sal) from emp;--獲得最高薪水
select * from emp where sal=5000;
子查詢:在主查詢執(zhí)行之前執(zhí)行,將執(zhí)行的結(jié)果作為主查詢的一個判斷依據(jù)
select ename,sal,deptno
from emp
where sal=(select max(sal) from emp);
注意:1.子查詢需要定義在括號當(dāng)中
2.子查詢通常定義在條件判斷的右邊
3.在子查詢中不建議使用 order by
--查詢誰的薪水比 BLAKE 高
select ename,sal
from emp
where sal>(select sal from emp where ename='BLAKE') ;/*避免值過多*/
/*如果子查詢的結(jié)果為null 那么結(jié)果為null*/
select ename,sal
from emp
where sal>(select sal from emp where ename='yves') ;
--子查詢中多行比較符
in :等于列表中的任何一個
any:和子查詢結(jié)果中的任意一個值進行比較
all:和子查詢結(jié)果中的所有值進行比較
--獲得各個部門中工資最高的員工
select ename,sal,deptno
from emp
where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
--誰的工資比20號部門員工的工資都高
select ename,sal,deptno
from emp
where sal>all(select sal from emp where deptno=20) ;/all大于最大值/
select ename,sal,deptno
from emp
where sal>any(select sal from emp where deptno=20) ;/any 大于最小值/
--獲得SIMTH 的同部門的員工,除了SIMTN以外
select ename,deptno
from emp
where deptno in (select deptno from emp where ename='SMITH') /in 可能其他部門有同名的人/
and ename!='SMITH';/ename <> 'SMITH'/
關(guān)聯(lián)子查詢:子查詢不再是一個獨立的SQL語句,依賴外部的主查詢傳入的參數(shù).
--那些員工的薪水比公司的平均薪水低(非關(guān)聯(lián)子查詢實現(xiàn))
select ename,sal
from emp
where sal<(select avg(sal) from emp);
--那些員工的薪水比本部分的平均薪水要低
select ename,sal,deptno
from emp e /為當(dāng)前的emp表取別名/
where sal<(select avg(sal) from emp where deptno=e.deptno);
--有下屬的員工
select empno,ename,deptno
from emp e
where exists (select 1 from emp where mgr=e.empno);
注意: exists:如果子查詢的有結(jié)果返回,true,如果沒有結(jié)果返回為false,所以exists不關(guān)心
子查詢的結(jié)果,通常返回一個常量 1,獲得定義一個任何字段
select * from emp;
select distinct mgr from emp where mgr is not null;
哪些人不是下屬
select empno,ename,deptno
from emp e
where not exists (select 1 from emp where mgr=e.empno);
select empno,ename
from emp
where empno not in (select distinct mgr from emp where mgr is not null);
注意:not in 當(dāng)中如果有null那么結(jié)果為null
集合的操作
union 去掉重復(fù)記錄
create table ta( str number);
insert into ta values(4);
insert into ta values(3);
insert into ta values(1);
insert into ta values(2);
create table tb(stb number);
insert into tb values(5);
insert into tb values(3);
ta={4,3,1,2};
tb={5,3};
select * from ta
union /合集/
select * from tb;
ta union tb={1,2,3,4,5};
select * from ta
union all
select * from tb;
ta union all tb={4,3,1,2,5,3};
union和union all的區(qū)別
|-union:去重復(fù),排序
|-union all:不重復(fù)也不排序.(推薦)
select * from ta
intersect /交集/
select * from tb;
ta intersect tb={3};
select * from ta
minus /差集/
select * from tb;
ta minus tb ={1,2,4};
tb minus ta ={5};
連接查詢:查詢多個視圖或者數(shù)據(jù)表時成為連接查詢
|-內(nèi)連接:返回所有符合連接條件的記錄
|-外連接:返回符合連接條件的記錄,同時返回不符合連接條件的記錄(左外連接,右外連接)
|-全外連接:左外連接和右外連接的結(jié)果總和
|-自連接:數(shù)據(jù)的來源是一個表表,關(guān)聯(lián)條件來自同一個數(shù)據(jù)表或者視圖中
--查詢員工的姓名和所在部門的名稱和地區(qū)
--采用內(nèi)連接: 表1 [inner] join 表2 on 關(guān)聯(lián)的條件
select e.ename,d.dname,d.loc,d.deptno
from emp e join dept d on e.deptno=d.deptno;
--inner通常被省略,建議在寫關(guān)聯(lián)查詢時提供表的別名
select e.ename,d.dname,d.loc,d.deptno
from emp e inner join dept d on e.deptno=d.deptno;
select e.ename,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;
--使用外外連接:
|-左外連接: 表1 left outer join 表2 on 關(guān)聯(lián)條件
|-結(jié)果集的組成:匹配的數(shù)據(jù)+表1中沒有匹配上的數(shù)據(jù)(結(jié)果集中保留表1的完整性)
|-右外連接: 表1 right outer join 表2 on 關(guān)聯(lián)條件
|-結(jié)果集的組成:內(nèi)連接的結(jié)果集+表2中沒有匹配上的數(shù)據(jù)(保留表2的完整性)
|-左左左全,右右右全
--獲得沒有員工的部門
select e.ename,d.dname,d.loc,d.deptno
from dept d left outer join emp e on e.deptno=d.deptno
where e.ename is null;
select e.ename,d.dname,d.loc,d.deptno
from dept d ,emp e
where e.deptno(+)=d.deptno;/保留 d 表的完整性 早期的外連接/
查詢并顯示SALES部門的職位
select distinct e.job
from emp e join dept d
on e.deptno=d.deptno
where d.dname='SALES';
查詢部門的名稱,所在地,員工數(shù)量,平均工資
select d.dname,d.loc,e.avg_sal,e.count_e
from dept d
join
( select deptno,avg(sal) avg_sal,count(ename) count_e
from emp
group by deptno
) e
on d.deptno=e.deptno;
--自連接
查詢員工以及上司的名稱
select e1.ename employee,e2.ename lidao
from emp e1,emp e2
where e1.mgr=e2.empno;
--全外連接 表1 full outer join 表2
|-結(jié)果集:內(nèi)連接結(jié)果集+左外連接+右外連接
select e.id,e.name,d.name
from temp_emp e full outer join temp_dept d
on e.d_id=d.id;
create table temp_emp(id number ,name varchar2(30),d_id number);
insert into temp_emp values (1001,'yves',1);
insert into temp_emp values(1002,'yvesHe',2);
insert into temp_emp values(1003,'lucy',3);
insert into temp_emp values(1004,'outMan',null);
create table temp_dept(id number,name varchar2(30));
insert into temp_dept values(1,'開發(fā)');
insert into temp_dept values (2,'財務(wù)');
insert into temp_dept values (3,'測試');
insert into temp_dept values (4,'前臺');
3.表的復(fù)制:如果需要對表中的數(shù)據(jù)進行刪除和修改,建議通過復(fù)制表中的數(shù)據(jù)來對數(shù)據(jù)進行操作
create table 表名 as 查詢語句;
--將emp表中的數(shù)據(jù)復(fù)制到t_emp表中
create table t_emp
as
select * from emp;
--只需要表的結(jié)構(gòu)
--將emp表的結(jié)構(gòu)復(fù)制到t_emp表中
create table t_emp
as
select * from emp
where 1=0;/提供一個否定條件/
--只復(fù)制一部分?jǐn)?shù)據(jù)
--將emp表中部門10的員工的數(shù)據(jù)復(fù)制到t_emp表中
create table t_emp
as
select * from emp
where deptno=10;
--將emp表中的員工姓名,工資,年薪保存到t_emp表中
create table t_emp
as
select ename,sal,sal12 year_sal /如果字段中出現(xiàn)函數(shù)或者計算需要提供別名*/
from emp;
--統(tǒng)計emp表中部門的人數(shù),將部門編碼和人數(shù)保存到t_emp表中
create table t_emp(did,ecount)
as
select deptno,count(ename)
from emp
group by deptno;
注意:表的復(fù)制只會復(fù)制表中的數(shù)據(jù),不會復(fù)制表中的約束
4.偽列
select rowid ,e.* from emp e;
rowid:是一個偽列,Oracle獨有的.每一條記錄的rowid的記錄是唯一的
--刪除表中的重復(fù)記錄
select e.,d.
from emp e,dept d
where e.deptno=e.deptno;/連接條件不合理:產(chǎn)生笛卡爾積/
笛卡爾積產(chǎn)生的原因
|-1.沒有連接條件
|-2.連接條件無效
避免笛卡爾積的方法:提供合理的連接條件
迪卡集的作用:臨時獲得大量的測試數(shù)據(jù).
create table t_emp
as
select e.*
from emp e,dept d
where e.deptno=e.deptno;
select rowid,e.* from t_emp e;
--刪除重復(fù)的記錄
delete from t_emp where rowid not in(
select max(rowid) from t_emp group by empno,ename,job,mgr );
select * from t_emp;
rownum:偽列,表示行號
select rownum ,e.* from t_emp e;
--顯示t_emp表中前五條記錄
select rownum ,e.* from t_emp e where rownum<=5; /rownum最小值從1開始/
--顯示t_emp表中第3條到第9條記錄
select rownum ,e.* from t_emp e
where rownum>=3 and rownum<=9; /查不出任何結(jié)果/
--在Oracle中,使用rownum進行分頁查詢
select ee.*
from
(select rownum r,e.* from t_emp e ) ee
where ee.r>=3 and ee.r<=9;
--將t_emp表中按照工資進行排序,顯示出第 5條到第10條
select ee.*
from
(select rownum r,e.* from (select * from t_emp order by sal) e ) ee
where ee.r>=5 and ee.r<=10;
5.約束條件
|-主鍵約束(PK):primary key
|-一張表中只能有一個主鍵約束
|-主鍵約束的特點:不能為空,具有唯一性
|-關(guān)系型數(shù)據(jù)庫中使用主鍵約束唯一標(biāo)識一行數(shù)據(jù)
|-主鍵約束分為兩類
|-行級約束
|-表級約束
--建表時定義主鍵約束
create table 表名(
字段名 類型 primary key , --列級約束,只能作用一個字段
...
);
--建表時定義表級約束
create table 表名(
字段1 類型,
字段2 類型,
...,
constraint 表名_字段名_PK primary key(字段1,字段2) /*字段 1和字段 2 聯(lián)合作為主鍵 */
);
非空約束(NN):not null
--語法
create table 表名(
字段名 類型 not null
....
);
--定義非空約束名稱
create table 表名(
字段名 類型 constraint 表名_字段名_NN not null,
...
);
注意:非空約束只有列級約束,沒有表級約束
唯一性約束(uk):unique:值必須唯一,可以為null
--列級約束
create table 表名(
字段名 類型 unique,
....
);
--表級約束
create table 表名(
字段1 類型,
字段2 類型,
...,
constraint 表名_字段名_uk unique(字段1,字段2)
);
檢測約束(CK):check
--列級約束
create table 表名(
字段名 類型 check(字段名 in(值1,...)),
....
);
--表級約束
create table 表名(
字段1 類型,
字段2 類型,
...,
constraint 表名_字段名_ck check(字段1 in(值1,...))
);
--提供一個用戶表 t_user
create table t_user(
id number constraint USER_ID_PK primary key,/*表示用戶編號,主鍵*/
name varchar2(30) constraint USER_NAME_NN not null,
gender char(3) default 'F',
constraint USER_NAME_UK unique(name),
constraint USER_GENDER_CK check(gender in('F','M'))
);
user_constraints:數(shù)據(jù)庫提供的查看表中約束條件的
--查詢t_user表中的約束條件
select CONSTRAINT_TYPE,CONSTRAINT_NAME from user_constraints where table_name ='T_USER';
insert into t_user values(1001,'yves','F');
insert into t_user values(1001,'yvesHe','M');--報錯,違法主鍵約束
預(yù)習(xí):外鍵約束
?????????????????????????????????
1.關(guān)于tab3表內(nèi)容：
2005-05-09 勝
2005-05-09 勝
2005-05-09 負
2005-05-09 負
2005-05-10 勝
2005-05-10 負
2005-05-10 負
如果要生成下列結(jié)果, 該如何寫sql語句?
/建表/
create table tab3(
playday date,
result varchar2(3)
);
insert into tab3 values(to_date('2005-05-09','yyyy-MM-dd'),'勝');
insert into tab3 values(to_date('2005-05-09','yyyy-MM-dd'),'勝');
insert into tab3 values(to_date('2005-05-09','yyyy-MM-dd'),'負');
insert into tab3 values(to_date('2005-05-09','yyyy-MM-dd'),'負');
insert into tab3 values(to_date('2005-05-10','yyyy-MM-dd'),'勝');
insert into tab3 values(to_date('2005-05-10','yyyy-MM-dd'),'負');
insert into tab3 values(to_date('2005-05-10','yyyy-MM-dd'),'負');
要求:
勝 負
2005-05-09 2 2
2005-05-10 1 2
方法1:
select playday,
sum(
case
when result='勝' then 1
else 0
end
) 勝,
sum(
case
when result='負' then 1
else 0
end
) 負
from tab3
group by playday;
方法2:
select playday,
sum(
decode(result,'勝',1,'負',0)
) 勝,
sum(
decode(result,'負',1,'勝',0)
) 負
from tab3
group by playday;
2.表中有A B C三列,用SQL語句實現(xiàn)：
當(dāng)A列大于B列時選擇A列否則選擇B列炊豪，
當(dāng)B列大于C列時選擇B列否則選擇C列湃缎。
/*建表*/
建立一個表:tab4里面有三個字段:A,B,C
方法1:
select
case
when A>B then A
else B
end resultAB,
case
when B>C then B
else C
end resultBC
from tab4;
sign比較大小的函數(shù)
select sign(-22),sign(100),sign(0) from dual;
/結(jié)果為:-1,1,0/
方法2:
select decode(sign(A-B),1,A,B) resultAB,decode(sign(B-C),1,B,C) from tab4;
3.一個日期判斷的sql語句
請取出tab5表中日期(SendTime字段)為當(dāng)天的所有記錄?
(SendTime字段為datetime型岁忘，包含日期與時間)
/建表:tab5/
select * from tab5 t
where to_char(t.datetime,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd');
4.有一張表傍衡，里面有3個字段：語文，數(shù)學(xué)数苫，英語聪舒。其中有3條記錄分別表示
語文70分，數(shù)學(xué)80分虐急，英語58分，
請用一條sql語句查詢出這三條記錄并按以下條件顯示出來（并寫出您的思路）：
大于或等于80表示優(yōu)秀滔迈，大于或等于60表示及格止吁，小于60分表示不及格。
顯示格式：
語文 數(shù)學(xué) 英語
及格 優(yōu)秀 不及格
/建表:tab6/
create table tab6(
語文 number,
數(shù)學(xué) number,
英語 number
);
insert into tab6 values(70,80,58);
方法1:case語句
select
(case when 語文>=80 then '優(yōu)秀'
when 語文>=60 then '及格'
else '不及格' end) 語文,
(case when 數(shù)學(xué)>=80 then '優(yōu)秀'
when 數(shù)學(xué)>=60 then '及格'
else '不及格' end) 數(shù)學(xué),
(case when 英語>=80 then '優(yōu)秀'
when 英語>=60 then '及格'
else '不及格' end) 英語
from tab6;
方法2:decode
select
decode(sign(語文-80),1,'優(yōu)秀',0,'優(yōu)秀',-1,
decode(sign(語文-60),1,'及格',0,'及格',-1,'不及格')) 語文,
decode(sign(數(shù)學(xué)-80),1,'優(yōu)秀',0,'優(yōu)秀',-1,
decode(sign(數(shù)學(xué)-60),1,'及格',0,'及格',-1,'不及格')) 數(shù)學(xué),
decode(sign(英語-80),1,'優(yōu)秀',0,'優(yōu)秀',-1,
decode(sign(英語-60),1,'及格',0,'及格',-1,'不及格')) 英語
from tab6;
5用一條SQL語句 查詢出每門課都大于80分的學(xué)生姓名
name kecheng fenshu
張三 語文 81
張三 數(shù)學(xué) 75
李四 語文 76
李四 數(shù)學(xué) 90
王五 語文 81
王五 數(shù)學(xué) 100
王五 英語 90
/建表:/
create table tab7(
name varchar2(30),
course varchar2(39),
score number
);
insert into tab7(name,course,score) values('張三','語文',81);
insert into tab7(name,course,score) values('張三','數(shù)學(xué)',75);
insert into tab7(name,course,score) values('李四','語文',76);
insert into tab7(name,course,score) values('李四','數(shù)學(xué)',90);
insert into tab7(name,course,score) values('王五','語文',81);
insert into tab7(name,course,score) values('王五','數(shù)學(xué)',100);
insert into tab7(name,course,score) values('王五','英語',90);
select distinct name from tab7 where name not in(
select distinct name from tab7 where score<80);
1.標(biāo)間約束
|-主鍵約束(PK):primary key
|-作用:唯一標(biāo)識一行數(shù)據(jù)
|-特點:不能為空,唯一
|-唯一性約束(UK):unique
|-作用:字段值不能重復(fù)
|-特點:可以為空
|-非空約束(NN):not null
|-作用:字段值不能為空
|-注意:非空約束不能定義表級約束
|-檢測約束(CK):check
|-作用:字段值只能在指定的內(nèi)容中選擇
2.外鍵(foreign key):在數(shù)據(jù)庫內(nèi)部,使用外鍵來描述表與表之間的關(guān)系
有外鍵列的表稱為從表(子表),提供參考值的表稱為主表(父表)
外鍵字段可以參考主表的主鍵約束或者唯一性約束的字段
用戶表和訂單表
|-一個用戶肯能有多個訂單,為了在數(shù)據(jù)庫中描述關(guān)系,在訂單表中提供外鍵
--用戶表
create table t_user(
id number primary key,
name varchar2(30) not null
);
--訂單表
create table t_order(
oid number primary key,/*訂單編號*/
account number(7,2) not null,/*總金額*/
pronums number not null,/*貨品總數(shù)*/
orderday date,/*下單日期*/
u_id number references t_user(id) /*定義表級的外鍵約束,參考用戶表的中id值*/
);
/注意/
|-1.建表時:先建主表,在建立從表
|-2.插入數(shù)據(jù)時,先操作主表,再操作從表
|-3.刪除數(shù)據(jù)時,如沒有級聯(lián)操作,必須先刪除子表中的數(shù)據(jù),在刪除主表中的數(shù)據(jù)
|-4.刪除表結(jié)構(gòu),要先刪除子表
/*插入數(shù)據(jù)*/
insert into t_user values(1001,'yves');
insert into t_user values(1002,'yvesHe');
insert into t_order
values(231001,250.25,4,to_date('2015-05-29','yyyy-MM-dd'),1001);
insert into t_order
values(231002,1250.25,4,to_date('2015-05-30','yyyy-MM-dd'),1001);
--插入下列數(shù)據(jù)出
--ORA-02291: 違反完整約束條件 (SCOTT.SYS_C004141) - 未找到父項關(guān)鍵字
--原因:子表中的外鍵列必須參考父表中的主鍵值,必須存在的值
insert into t_order
values(231002,1250.25,4,to_date('2015-05-30','yyyy-MM-dd'),1003);
--刪除數(shù)據(jù)
--執(zhí)行下條SQL語句出現(xiàn)
--ORA-02292: 違反完整約束條件 (SCOTT.SYS_C004141) - 已找到子記錄
--原因:主表的當(dāng)前數(shù)據(jù)被從表參考.要先刪除從表中的數(shù)據(jù)
delete from t_user where id=1001;
delete from t_order where u_id=1001;
--刪除表結(jié)構(gòu)
--執(zhí)行下條SQL語句出現(xiàn)
--ORA-02449: 表中的唯一/主鍵被外鍵引用
--原因:當(dāng)前表的主鍵被作為其他表的外鍵
drop table t_user;
--表級約束定義外鍵
--用戶表
create table t_user(
id number primary key,
name varchar2(30) not null
);
--訂單表
create table t_order(
oid number primary key,/*訂單編號*/
account number(7,2) not null,/*總金額*/
pronums number not null,/*貨品總數(shù)*/
orderday date,/*下單日期*/
u_id number, /*定義表級的外鍵約束,參考用戶表的中id值*/
constraint order_user_id_fk foreign key(u_id) references t_user(id)
);
級聯(lián)刪除:
1.默認(rèn)情況下,必須先刪除從表的數(shù)據(jù)在刪除主表的數(shù)據(jù)
--如何刪除表中的約束
alter table t_order drop constraint order_user_id_fk;
on delete set null:當(dāng)主表的數(shù)據(jù)被刪除時,子表參考的值設(shè)為null
--重新為表t_order添加外鍵約束
alter table t_order add constraint order_user_id_fk
foreign key(u_id) references t_user(id) on delete set null;
先刪除父表
delete from t_user where id=1001;
子表t_order中對應(yīng)的u_id字段的值被置空
on delete cascade :當(dāng)主表的數(shù)據(jù)被刪除時,子表中對應(yīng)的值也被刪除
--重新為表t_order添加外鍵約束
alter table t_order add constraint order_user_id_fk
foreign key(u_id) references t_user(id) on delete cascade;
先刪除父表
delete from t_user where id=1001;
子表t_order中對應(yīng)的u_id的整條記錄被刪除
drop table t_order;
drop table t_user;
--用戶表
create table t_user(
id number primary key,
name varchar2(30) unique /*唯一性約束*/
);
--訂單表
create table t_order(
oid number primary key,/*訂單編號*/
account number(7,2) not null,/*總金額*/
pronums number not null,/*貨品總數(shù)*/
orderday date,/*下單日期*/
u_name varchar2(30), /*定義表級的外鍵約束,參考的主表的唯一性約束的字段*/
constraint order_user_id_fk foreign key(u_name) references t_user(name)
);
用戶表和身份證
用戶表 t_person
身份證 t_idcard
一個用戶只能有一張身份證,一張身份證對應(yīng)一個用戶
1對1的描述的策略:唯一外鍵
create table t_person(
id number primary key,
name varchar2(30)
);
create table t_idcard(
id number primary key,
serial number, /*有效期 */
p_id number ,
constraint idcard_person_id_fk foreign key(p_id) references t_person(id),/*外鍵*/
constraint idcard_p_id_uk unique(p_id)/*唯一性約束*/
);
drop table t_idcard;
drop table t_person;
1對1的描述的策略:共享主鍵
create table t_person(
id number primary key,
name varchar2(30)
);
create table t_idcard(
id number primary key references t_person(id),/*id即為外鍵又為主鍵*/
serial number /*有效期 */
);
多對多的關(guān)系:必須通過第三張表進行描述
--學(xué)生表
create table t_stu(
id number primary key,
name varchar2(30)
);
--課程表
create table t_course(
id number primary key,
name varchar2(30),
score number /*總的學(xué)分*/
);
學(xué)生表和課程表示多對多,提供第三張關(guān)系表
create table t_s_c(
id number primary key,
s_id number references t_stu(id),
c_id number references t_course(id),
score number /*某個學(xué)生實際的成績*/
);
約束的建立時機:為了更好的對約束條件進行管理,建議對約束進行命名.在建表之后在提供約束
--添加約束
alter table 表名 add constraint 自定義約束名 約束條件;
注意:非空約束,不能定義表級約束
alter table 表名 modify (字段名 字段列席 not null );
--刪除約束
alter table 表名 drop constraint 指定的約束名;
3.數(shù)據(jù)庫設(shè)計的三范式
|-在數(shù)據(jù)設(shè)計時需要遵守的規(guī)則,關(guān)系型數(shù)據(jù)庫中對這類規(guī)則稱為范式.
第一范式:每個字段要確保原子性.每個字段不能再進行分解
第二范式:在第一范式的基礎(chǔ)上,確保表中的每一列和主鍵相關(guān).
第三范式:在第二范式的基礎(chǔ)上,卻表表中的字段和主鍵是直接相關(guān)而不是間接相關(guān)
4.作業(yè)
購物系統(tǒng)中的關(guān)系
用戶表 t_user
商品表 t_product
訂單表 t_order
地址表 t_address
訂單詳細表 t_orderitem
?????????????????????????????????
1.數(shù)據(jù)庫腳步文件的定義
1.刪除約束條件
2.刪除表結(jié)構(gòu)(先刪除主表再刪除父表)
3.建表
4.定義約束條件
5.插入測試數(shù)據(jù)
2.數(shù)據(jù)字典
2.1 數(shù)據(jù)字典的格式
user_xxx:當(dāng)前用戶自己的對象
all_xxx:當(dāng)前用戶能夠訪問的對象(自己的對象+其他用戶授權(quán)訪問的對象)
dba_xxx:當(dāng)前數(shù)據(jù)庫所有的對象
user_tables:當(dāng)前用戶中可以操作的表,查詢當(dāng)前用戶下的所有的表名
select table_name from user_tables;
user_constraints:當(dāng)前用戶所有的約束條件
user_objects:當(dāng)前用戶包含的對象
3.數(shù)據(jù)庫的對象
3.1 table 表 :是關(guān)系型數(shù)據(jù)庫中基本的存儲單元,是一個二維結(jié)構(gòu)由行和列組成.
3.2 view 視圖:是一個虛表,視圖會對應(yīng)一個查詢語句.將查詢語句的結(jié)果賦予一個名字,
就是試圖的名稱,可以像操作表一樣操作視圖.視圖的目的可以保證表的
安全和簡化查詢操作.
3.3 index 索引:在數(shù)據(jù)庫中用來加速查詢的數(shù)據(jù)庫對象.減少磁盤的IO操作,提高訪問性能
3.4 sequence 序列:用來唯一生成數(shù)值的數(shù)據(jù)庫對象.通常使用序列來控制主鍵值.由數(shù)據(jù)庫內(nèi)
部提供具有安全性和高效性
3.5 procedure 存儲過程:在數(shù)據(jù)庫中完成特定的任何和操作.實現(xiàn)小部分的業(yè)務(wù)邏輯
3.6 function 函數(shù):用來進行復(fù)雜的計算,返回結(jié)果.
3.7 package 包:將函數(shù)和存儲過程組織起來,形成程序組.由包頭和包體組成
3.8 trigger 觸發(fā)器:在事件發(fā)生時隱式的執(zhí)行,類似于java中的事件監(jiān)聽器
4.序列 sequence:連續(xù)產(chǎn)生不同的數(shù)值作為表的主鍵值
|-oracle,db2中有序列,mysql,sqlserver沒有序列
|-序列是數(shù)據(jù)庫中獨立的對象
--創(chuàng)建序列 :默認(rèn)從1開始,步進為 1
create sequence mytest_seq;
序列的屬性
|-nextval:獲得序列進行步進操作的值,會引起序列值得變化
|-currval:獲得當(dāng)前序列最大的值,不會引起序列值得變化
訪問序列屬性:自定義的序列名.nextval / 自定義的序列名.currval
--創(chuàng)建指定的起始值和步進
create sequence 自定義名稱 start with 指定的值 increment by 步進;
create sequence test_seq start with 10000 increment by 2;
create table t_test(
id number primary key ,
name varchar2(30)
);
insert into t_test(id,name) values(test_seq.nextval,'yves');
insert into t_test(id,name) values(test_seq.nextval,'yves');
insert into t_test(id,name) values(test_seq.nextval,'yves');
insert into t_test(id,name) values(test_seq.nextval,'yves');
insert into t_test(id,name) values(test_seq.nextval,'yves');
insert into t_test(id,name) values(test_seq.nextval,'yves');
--刪除序列
drop sequence 序列名;
drop sequence test_seq;--表中的數(shù)據(jù)不會受影響
5.視圖 view
視圖的特點
|-1.簡化復(fù)制查詢的操作
|-2.隱藏表中字段
|-3.視圖是虛表,基表數(shù)據(jù)的影射
簡單視圖:視圖中的數(shù)據(jù)就是基表中的子集.
drop table t_emp;
--復(fù)制emp表
create table t_emp
as
select * from emp where deptno in (10,20);
--使用t_emp表作為基表創(chuàng)建視圖
create view v_emp
as
select ename,empno,deptno from t_emp where deptno=20;
--查詢視圖
select * from v_emp;
--查詢視圖的結(jié)構(gòu)
desc v_emp;
--更新基表中的數(shù)據(jù),那么視圖中對應(yīng)的記錄也會變化
update t_emp set ename='yves' where empno=7369;
--更新視圖中的數(shù)據(jù):簡單視圖在默認(rèn)情況下可以通過修改視圖影響基表中的數(shù)據(jù)
update v_emp set ename='yves' where empno=7369;
簡單視圖防止修改視圖影響基表在創(chuàng)建視圖時使用 with read only
--刪除視圖
drop view v_emp;
--創(chuàng)建只讀的簡單視圖
create view v_emp
as
select ename,empno,deptno from t_emp where deptno=20 with read only;
復(fù)雜視圖:數(shù)據(jù)是通過基表中的數(shù)據(jù)計算獲得.特點只讀.
|-關(guān)聯(lián)視圖:視圖中的數(shù)據(jù)通過多張表聯(lián)合查詢獲得
--創(chuàng)建一個試圖,顯示每個部門有多少員工
create view v_emp_count
as
select deptno,count(ename) emp_num from t_emp group by deptno;
--向基表中插入數(shù)據(jù):視圖中的數(shù)據(jù)會變化
insert into t_emp(ename,empno,deptno) values('yves',1001,10);
--修改視圖
delete from v_emp_count where deptno=10;
--創(chuàng)建視圖可以使用create or replace 進行創(chuàng)建:如果沒有創(chuàng)建,有就覆蓋
create or replace view v_emp_count
as
select max(sal) max_sal,avg(nvl(sal,0)) avg_sal from t_emp;
--通過數(shù)據(jù)字典查尋視圖
select text from user_views where view_name='V_EMP_COUNT';--視圖對應(yīng)的sql語句
--為scott授予創(chuàng)建視圖的權(quán)限
使用sys以管理員身份登錄
grant create view to scott;
6.索引 index:目的提高查詢效率
|-結(jié)構(gòu):內(nèi)容+地址
|-注意:如果字段有頻繁的DML操作那么不適合創(chuàng)建索引(可能造成頻繁的創(chuàng)建索引)
建表時對于主鍵約束和唯一性約束自動創(chuàng)建索引(user_indexes)
create table t_test(
id number constraint t_test_id_pk primary key,
name varchar2(20) constraint t_test_name_uk unique,
age number constraint t_test_age_nn not null,
gender varchar2(3) constraint t_test_gender_ck check(gender in('F','M'))
);
select index_name,index_type from user_indexes where table_name='T_TEST';
--對于主鍵字段和唯一性字段如果有where條件會使用到索引
select * from t_test where id=1000;
--如果對索引字段進行的計算,那么不會使用索引
select * from t_test where id/100=10; --不會使用索引
優(yōu)化=> select * from t_test where id=1000; --可以使用索引
--條件查詢中沒有使用索引字段
select * from t_test where age=10;--全表掃描
手動創(chuàng)建索引
create index 自定義索引名稱 on 表名(字段名);
create index t_test_age on t_test(age);
刪除索引
drop index 自定義名稱;
6.如何進行SQL語句的優(yōu)化
|-** 在select語句中避免使用 *
|-** 減少數(shù)據(jù)庫的訪問次數(shù)
|-** 刪除重復(fù)記錄
|-盡量多使用commit
|-** 使用where替換having
|-多使用內(nèi)部函數(shù)提高sql語句效率
|-多使用表的別名
|-使用exists替換in,使用 not exists替換not in
|-盡量使用索引類進行查詢
|-sql語句盡量大寫.oracle會自動轉(zhuǎn)換成大寫
|-** 避免在索引列上進行計算
|-** 避免在索引類上使用not,oracle遇到not就使用全表掃描
|-可以使用>=替換>
|-使用in替換or
|-盡量使用where替換group by
|-** 避免使用消耗資源的操作.如 union
7.數(shù)據(jù)庫的物理組件(物理結(jié)構(gòu))
Oracle主要的物理文件有三類
|- 數(shù)據(jù)文件:存儲數(shù)據(jù),如數(shù)據(jù)表中的數(shù)據(jù),索引數(shù)據(jù)
|- 控制文件:記錄數(shù)據(jù)庫結(jié)構(gòu)的二進制文件
|-在線日志文件:記錄數(shù)據(jù)庫的日常操作. 用于故障恢復(fù).
費主要的物理文件
|-密碼文件, 參數(shù)文件,備份文件,警告和跟蹤文件
8.邏輯結(jié)構(gòu)
Oracle的邏輯組件:數(shù)據(jù)庫->表空間-->段-->區(qū)->數(shù)據(jù)塊->模式
表空間是oracle中最大的邏輯組件.一個Oracle至少包含一個表空間,系統(tǒng)默認(rèn)的就是名為
SYSTEM的系統(tǒng)表空間
表空間是由一個或者多個數(shù)據(jù)文件組成,一個數(shù)據(jù)文件只能和一個表空間進行關(guān)聯(lián).
段:是構(gòu)成表空間的邏輯結(jié)構(gòu),段由一組區(qū)組成燎悍。
區(qū)：為段分配空間敬惦，區(qū)是由數(shù)據(jù)塊組成
數(shù)據(jù)塊：是數(shù)據(jù)庫能夠讀取，操作谈山，分配的最小邏輯單元
模式：等同于用戶俄删，用戶所創(chuàng)建數(shù)據(jù)庫對象的總稱
