oracle学习.docx
- 文档编号:10815434
- 上传时间:2023-02-23
- 格式:DOCX
- 页数:17
- 大小:19.23KB
oracle学习.docx
《oracle学习.docx》由会员分享,可在线阅读,更多相关《oracle学习.docx(17页珍藏版)》请在冰豆网上搜索。
oracle学习
oracle
检索表t1的所有列:
select*fromt1;
创建表t2
createtablet2(xuehaovarchar2(10),namevarchar(10),jobvarchar(10),salarynumber);
插入值
insertintot1values(111,'liucong','clear',5000);
insertintot1values(112,'wangbenlia','sales',4000);
insertintot1values(113,'zhaobing','office',1500);
insertintot1values(114,'chenkui','manager',9000);
更新数据:
updatet2setsalary=1500
wherename='zhaobing';
altertablestuadd(addrvarchar(20));添加表的结构
altertablestudrop(addr);删除表结构
altertablestumodify(addrvarchar2(150));修改精度
altertablestudropconstraintstu_class_fk;删除约束条件
altertablestuaddconstraintstu_class_fkforengnkey(class)referencesclass(id),添加约束条件
索引:
创建索引
createindexidx_stu_emailonstu(email);
dropindexidx_stu_email;
查找索引
selectindex_namefromuser_indexes;
索引读的速度快了,插入速度变慢
序列的创建sequence产生独一无二的序列,而且是oracle独有的
createsequenceseq;
selectseq.nextvalfromdual;查找序列号
insertintoarcticlevalues(seq.nextval,'a','b');往表中插入序列
dropseq;
删除一行:
deletefromt2
wheresalary=500;
删除全部数据:
deletefromt2;
删除表:
truncatetablet2;
droptablet2;
查询:
分组查询、
selectxuehao,job,avg(salary)fromt1
groupbyname,job;
selectxuehao,avg(salary)
fromt1
wheresalary>300
groupbyxuehao
havingavg(salary)>400
orderbyxuehao;
selectxuehao,avg(salary)
FROMt1
wheresalary>2000
groupbyxuehao
havingavg(salary)>1500
orderbyavg(salary)desc;
(where子句是检查每条记录是否满足条件,而having子句是检查分组之后的各组是否满足条件。
没有groupby就不能使用having)
selectsalaryfromt1
wherename='d';
子查询建表
createtablet3as
selectname,salary
fromt1
wheresalary<3000;
返回单行:
selectname,job,hiredate,sal
fromt1
wheresalary>(selectsalaryfromt1
wherename='d');
返回多行:
selectname,salary,job,hiredate
fromt1
wherenamenotin(selectnamefromt1
wheresalary<5000);
消除重复行:
(只改变显示结果,不改变原表结构)
selectdistinctname
fromt1;
外连接:
t1右外连接t2:
selecta.name,a.salary,a.xuehao,b.name,b.salary,b.xuehao
fromt1a,t2b
wherea.name(+)=b.name;
(t2全列,如果t2中有a是重复的,而且t1中只有一个a,那么也显示多次,显示的行数还是t2的所有行)
(t2全列,若果t2中只有一个a,而且t1中a是重复的,那么a显示多次,所以显示的行数大于t2的所有行)
t1左外连接t2:
selecta.name,a.salary,a.xuehao,b.name,b.salary,b.xuehao
fromt1a,t2b
wherea.name=b.name(+);
非等值连接:
selecta.name,a.salary,b.name,b.salary
fromt1a,t2b
wherea.name!
=b.nameanda.salary<=500
表结构:
describet2;
分组查询:
selectsum(salary)fromt1
groupbydecode(&groupby,1,job,2,xuehao);
条件控制语句:
为名字为'zhaobing'的一个员工增加工资:
declare
namet2.name%type:
='renyafei';
incrementt2.salary%type;
job2t2.job%type;
begin
selectjobintojob2fromt2
wherename='liucong';
ifjob2='clear'thenincrement:
=200;
elsifjob2='office'thenincrement:
=300;
elseincrement:
=400;
endif;
updatet2setsalary=salary+increment
wherename='liucong';
commit;
end;
select*fromt2;/
declare
sidnumber:
=1;
sscorenumber:
=100;
begin
loop
insertintotb_stu(id,score)values(sid,sscore);
sid:
=sid+1;
sscore:
=sscore-2;
exitwhensid=12;
endloop;
end;
FOR循环语句:
(sqlwindow)
declare
chavarchar2(200);
numnumber:
=40;
begin
cha:
='*';
foriin1..60loop
dbms_output.put_line(cha);
cha:
=replace(cha,'*','**');
endloop;
end;
直到型循环
&&&&&&&when
(commandwindow)
variablesumnumber
declare
inumber(3):
=100;
begin
:
sum:
=0;
loop
:
sum:
=:
sum+i;
i:
=i-1;
exitwheni=0;
endloop;
end;
/
(sqlwindow)
declare
anumber(10);
inumber(3);
begin
i:
=100;
a:
=0;
loop
a:
=a+i;
i:
=i-1;
exitwheni=0;
endloop;
dbms_output.put_line(a);
end;
&&&&&&&&&if
(commandwindow)
variablesumnumber
declare
inumber(3):
=100;
begin
:
sum:
=0;
loop
:
sum:
=:
sum+i;
i:
=i-1;
ifi<1thenexit;
endif;
endloop;
end;
/
(sqlwindow)
declare
anumber(10);
inumber(3);
begin
a:
=0;
i:
=100;
loop
a:
=a+i;
i:
=i-1;
ifi<1thenexit;
endif;
endloop;
dbms_output.put_line(a);
end;
variablesumnumber
declare
inumber(3):
=0;
begin
:
sum:
=0;
loop
:
sum:
=:
sum+i;
i:
=i+1;
exitwheni=100;
endloop;
end;
/
当型循环:
variablesumnumber
declare
inumber(3):
=100;
begin
:
sum:
=0;
whilei>0loop
:
sum:
=:
sum+i;
i:
=i-1;
endloop;
end;
/
SELECTNAME,MAX(salary)FROMt1
GROUPBYname,xuehao;
SELECTNAME,AVG(salary)FROMt1
WHEREsalary>2000
GROUPBYname,xuehao;
SELECTa.name,a.xuehao,b.xuehao,b.nameFROMt1a,t1b
WHEREa.xuehao=b.salary;
selectname,salaryfrom(selectname,salary,rownumrfrom(selectname,salaryfromt1orderbysalarydesc))
wherer>=6andr<=10;
上面这段用来查询salary在6-10名之间的人!
!
!
!
oracle用三层嵌套来解决这个问题,重点掌握。
selects.namefroms,sc
wheres.sno=sc.snoando=o
andc.cteacher<>'李白';
selects.namefroms,sc
wheres.sno=sc.sno
andsc.grade<60;
selectcount(*)sc.snofroms,sc
wheres.sno=sc.sno
andsc.grade<60
groupbys.sno
havingcount(*)>=2
selects.name,avg(sc.grade)froms,sc
wheres.sno=sc.sno
ands.snoin(selectcount(*),snofromscwheresc.grade<60groupbysnohavingcount(*)>=2)
groupbys.sno;
selects.namefroms
wheres.sno=sc.sno
ando=1
andoin(selectsc.snofromscwhereo=2);
3个表S,C,SC
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1.找出没选过“李白”老师的所有学生姓名
2.列出2门以上(含2门)不及格学生姓名及平均成绩。
3.既学过1号课程又学过2号课程所有学生的姓名
answers:
1.selectdistincts.snamefroms,c,sc
wheres.sno=sc.snoando=o
andc.cteacher<>'李白';-----easy
2.selects.snamefroms,sc
wheres.sno=sc.sno
andsc.scgrade<60;------有成绩小于60;
selectcount(*),sc.snofroms,sc
wheres.sno=sc.sno
andsc.scgrade<60
groupbys.sno
havingcount(*)>=2------>=2门不及格人的sno
selects.sname,avg(sc.grade)froms,sc
wheres.sno=sc.sno
ands.snoin(selectcount(*),snofromscwherescgrade<60groupbysnohavingcount(*)>=2)
groupbys.sno;
3.selects.snamefroms,scwheres.sno=sc.sno
ando='1'
ands.snoin(selectsnofromescwherecno='2');
selects.name,avg(sc.grade)froms,sc
wheres.sno=sc.sno
ands.snoin(selectcount(*),snofromscwheresc.grade<60groupbysnohavingcount(*)>=2)
groupbysno
createtablestu
(idnumber(6),
namevarchar2(20)constraintstu_name_nnnotnull,
sexnumber
(1),
agenumber(3),
sdatedate,
gradenumber
(2)default1,
classnumber(4),
emailvarchar2(50)unique(唯一约束)
);
非空唯一主键外键check
createtablestu
(idnumber(6)primarykey,(主键约束)
namevarchar2(20)constraintstu_name_nnnotnull,(非空约束)
sexnumber
(1),
agenumber(3),
sdatedate,
gradenumber
(2)default1,
classnumber(4),
emailvarchar2(50),
constraintstu_name_uuiunique(email,name)组合性约束
);
主键约束方法二
createtablestu
(idnumber(6),
namevarchar2(20)constraintstu_name_nnnotnull,--(非空约束)
sexnumber
(1),
agenumber(3),
sdatedate,
gradenumber
(2)default1,
classnumber(4)referencesclass(id),--(参考class这张表,参考字段)外键约束
emailvarchar2(50),
constraintstu_id_pkprimarykey(id),
constraintstu_name_uuiunique(email,name)--组合性约束
);
--外键约束
createtableclass
(idnumber(4)primarykey,--(id为被参考字段,被参考的字段必须是主键)
namevarchar2(20)notnull
);
createtablestu
(idnumber(6),
namevarchar2(20)constraintstu_name_nnnotnull,--(非空约束)
sexnumber
(1),
agenumber(3),
sdatedate,
gradenumber
(2)default1,
classnumber(4),
emailvarchar2(50),
constraintstu_class_fkforeignkey(class)referencesclass(id),
constraintstu_id_pkprimarykey(id),
constraintstu_name_uuiunique(email,name)--组合性约束
);
selectmax(avg(sal)fromempgroupbydeptno);
组函数可以嵌套,最多可以嵌套两层
selectdistinctdevice_numberfromdw_his_v_bill_montht
joindim_user_dinnerdon(t.user_dinner=d.user_dinner)
jointb_area_no_temptbon(tb.area_no=d.area_no)
wheret.acct_month='201202'andd.dinner_namelike'WCDMA(3G)%'andd.dinner_namelike'%A'andt.area_no=381
;
selectnvl(area_desc,'合计')area,count(distinctdevice_number),avg(tot_fee),sum(tot_fee),sum(tot_fee)/16797258.86fromdw_his_v_bill_monthb
join(selectUSER_DINNERfromdim_user_dinnerdwhered.dinner_namelike'WCDMA(3G)%'andd.dinner_namelike'%A')t
on(t.user_dinner=b.user_dinner)
jointb_area_no_tempaon(a.area_no=b.area_no)
whereb.acct_month='201202'
groupbyrollup(area_desc);
selectnvl(area_desc,'合计')area,count(distinctdevice_number)用户数,avg(tot_fee)arpu值,sum(tot_fee)总费用,sum(tot_fee)/16797258.86占比fromdw_his_v_bill_monthb
join(selectUSER_DINNERfromdim_user_dinnerdwhered.dinner_namelike'WCDMA(3G)%'andd.dinner_namelike'%A')t
on(t.user_dinner=b.user_dinner)
jointb_area_no_tempaon(a.area_no=b.area_no)
whereb.acct_month='201202'
groupbyrollup(area_desc);
有表如下:
sql@kokooa>select*fromtest026;
ID
NAME
SUBJECT
SCORE
1
jim
语文
88
1
jim
数学
84
1
jim
英语
90
2
kate
语文
86
2
kate
数学
76
2
kate
英语
96
想得到如下效果:
学生编号学生姓名语文数学英语
方法:
createtablechengji
(idnumber,namevarchar2(20),subjectvarchar2(20),scorenumber);
--学生编号学生姓名语文数学英语
selectid学生编号,name学生姓名,
sum(casewhenkecheng='语文'thenfenshuend)语文,
sum(casewhenkecheng='数学'thenfenshuend)数学,
sum(casewhenkecheng='英语'thenfenshuend)英语
fromchengji
groupbyid,name;
1.自连接:
(这是自连接很典型的用处应当熟练掌握)
selecta.id,a.name,a.scoreas语文,b.scoreas数学,c.scoreas英语
fromchengjia,chengjib,chengjic
wherea.id=b.idanda.subject='语文'andb.subject='数学'
anda.id=c.idandc.subject='英语';
IDNAME语文数学英语
------------------------------------------------------------
1jim888490
2kate867696
2使用casewhen
sql@kokooa>selectid,name,
2sum(casewhensubject='语文'thenscoreend)as"语文",
3sum(casewhensubject='数学'thenscoreend)as"数学",
4sum(casewhensubject='英语'thenscoreend)as"英语"
5fromchengji
6groupbyid,name
7/
IDNAME语文数学英语
------------------------------------------------------------
1jim888490
2kate867696
3decode
selectmax(id)asid,name,
max(decode(subject,'数学',score))as"数学",
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 学习