oracle学习.docx

oracle 检索表t1的所有列：select * from t1;创建表t2create table t2(xuehao varchar2(10),name varchar(10),job varchar(10),salary number);插入值insert into t1 values(111,liucong,clear,5000);insert into t1 values(112,wangbenlia,sales,4000);insert into t1 values(113,zhaobing,office,1500);insert into t1 values(114,chenkui,manager,9000);更新数据：update t2 set salary = 1500where name=zhaobing;alter table stu add(addr varchar(20);添加表的结构alter table stu drop(addr); 删除表结构alter table stu modify(addr varchar2(150);修改精度alter table stu drop constraint stu_class_fk; 删除约束条件alter table stu add constraint stu_class_fk forengn key(class) references class(id),添加约束条件索引：创建索引create index idx_stu_email on stu(email);drop index idx_stu_email;查找索引select index_name from user_indexes;索引读的速度快了，插入速度变慢序列的创建sequence产生独一无二的序列，而且是oracle独有的create sequence seq;select seq.nextval from dual; 查找序列号insert into arcticle values(seq.nextval,a,b);往表中插入序列drop seq;删除一行：delete from t2where salary=500;删除全部数据：delete from t2;删除表：truncate table t2;drop table t2;查询：分组查询、select xuehao,job,avg(salary) from t1group by name,job; select xuehao,avg(salary)from t1where salary300group by xuehaohaving avg(salary)400order by xuehao;select xuehao,avg(salary) FROM t1 where salary2000 group by xuehao having avg(salary)1500 order by avg(salary) desc;（where子句是检查每条记录是否满足条件，而having子句是检查分组之后的各组是否满足条件。没有group by就不能使用having）select salary from t1where name=d;子查询建表create table t3 asselect name,salaryfrom t1where salary(select salary from t1where name =d);返回多行：select name,salary,job,hiredatefrom t1where name not in(select name from t1where salary5000);消除重复行：(只改变显示结果，不改变原表结构）select distinct namefrom t1;外连接：t1右外连接t2：select ,a.salary,a.xuehao,,b.salary,b.xuehao from t1 a,t2 b where (+)=;（t2全列，如果t2中有a是重复的，而且t1中只有一个a，那么也显示多次，显示的行数还是t2的所有行） (t2全列，若果t2中只有一个a，而且t1中a是重复的，那么a显示多次，所以显示的行数大于t2的所有行）t1左外连接t2：select ,a.salary,a.xuehao,,b.salary,b.xuehao from t1 a,t2 b where =(+);非等值连接：select ,a.salary,,b.salary from t1 a,t2 b where != and a.salary=500 表结构：describe t2;分组查询：select sum(salary) from t1group by decode(&groupby,1,job,2,xuehao);条件控制语句：为名字为zhaobing的一个员工增加工资：declarename %type:=renyafei;increment t2.salary%type;job2 t2.job%type;beginselect job into job2 from t2where name=liucong;if job2=clear then increment:=200;elsif job2=office then increment:=300;else increment:=400;end if;update t2 set salary=salary+incrementwhere name=liucong;commit;end;select * from t2;/declare sid number:=1;sscore number:=100;beginloopinsert into tb_stu(id,score) values(sid,sscore);sid:=sid+1;sscore:=sscore-2;exit when sid=12;end loop;end;FOR循环语句：(sql window) declarecha varchar2(200);num number:=40;begincha:= *;for i in 1.60 loopdbms_output.put_line(cha);cha:=replace(cha, *, * *);end loop;end; 直到型循环&when(command window) variable sum numberdeclarei number(3):=100;begin:sum:=0;loop:sum:=:sum+i;i:=i-1;exit when i=0;end loop;end;/(sql window)declarea number(10);i number(3);begini:=100;a:=0;loopa:=a+i;i:=i-1;exit when i=0;end loop;dbms_output.put_line(a);end;&if(command window)variable sum number declarei number(3):=100;begin:sum:=0;loop:sum:=:sum+i;i:=i-1;if i1 then exit;end if;end loop;end;/(sql window)declarea number(10);i number(3);begina:=0;i:=100;loopa:=a+i;i:=i-1;if i0 loop:sum:=:sum+i;i:=i-1;end loop;end;/SELECT NAME, MAX(salary) FROM t1GROUP BY name,xuehao;SELECT NAME, AVG(salary) FROM t1WHERE salary2000GROUP BY name,xuehao;SELECT ,a.xuehao,b.xuehao, FROM t1 a,t1 bWHERE a.xuehao=b.salary;select name, salary from (select name, salary, rownum r from (select name, salary from t1 order by salary desc )where r = 6 and r= 10;上面这段用来查询salary在6-10名之间的人！oracle用三层嵌套来解决这个问题，重点掌握。select from s,scwhere s.sno=sc.sno and o=oand c.cteacher李白;select from s,scwhere s.sno=sc.snoand sc.grade60;select count(*) sc.sno from s,scwhere s.sno=sc.snoand sc.grade=2 select ,avg(sc.grade) from s,scwhere s.sno=sc.snoand s.sno in (select count(*),sno from sc where sc.grade=2)group by s.sno;select from swhere s.sno=sc.snoand o=1and o in (select sc.sno from sc where o=2); 3个表S, C, SCS( SNO, SNAME ) 代表（学号，姓名）C( CNO, CNAME, CTEACHER ) 代表（课号，课名，教师）SC( SNO, CNO, SCGRADE ) 代表（学号，课号，成绩）问题：1. 找出没选过“李白”老师的所有学生姓名2. 列出2门以上（含2门）不及格学生姓名及平均成绩。3. 既学过1号课程又学过2号课程所有学生的姓名answers：1. select distinct s.sname from s, c, sc where s.sno=sc.sno and o=o and c.cteacher李白;-easy 2. select s.sname from s, scwhere s.sno=sc.snoand sc.scgrade60;-有成绩小于60； select count(*), sc.sno from s, sc where s.sno=sc.sno and sc.scgrade=2- =2门不及格人的sno select s.sname, avg(sc.grade) from s, sc where s.sno=sc.sno and s.sno in ( select count(*), sno from sc where scgrade=2) group by s.sno; 3. select s.sname from s, sc where s.sno=sc.sno and o=1 and s.sno in ( select sno frome sc where cno=2 ); select ,avg(sc.grade) from s,scwhere s.sno=sc.snoand s.sno in (select count(*),sno from sc where sc.grade=2)group by snocreate table stu(id number(6),name varchar2(20) constraint stu_name_nn not null,sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varchar2(50) unique (唯一约束);非空 唯一 主键 外键 checkcreate table stu(id number(6) primary key,(主键约束)name varchar2(20) constraint stu_name_nn not null,(非空约束)sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varchar2(50),constraint stu_name_uui unique(email,name) 组合性约束);主键约束方法二create table stu(id number(6),name varchar2(20) constraint stu_name_nn not null,-(非空约束)sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4) references class(id),-(参考class 这张表，参考字段) 外键约束email varchar2(50),constraint stu_id_pk primary key(id),constraint stu_name_uui unique(email,name) -组合性约束);-外键约束create table class(id number(4) primary key,-(id为被参考字段,被参考的字段必须是主键)name varchar2(20) not null);create table stu(id number(6),name varchar2(20) constraint stu_name_nn not null,-(非空约束)sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varchar2(50),constraint stu_class_fk foreign key(class) references class(id),constraint stu_id_pk primary key(id),constraint stu_name_uui unique(email,name) -组合性约束);select max(avg(sal) from emp group by deptno); 组函数可以嵌套，最多可以嵌套两层select distinct device_number from dw_his_v_bill_month t join dim_user_dinner d on(t.user_dinner=d.user_dinner)join tb_area_no_temp tb on(tb.area_no=d.area_no)where t.acct_month=201202and d.dinner_name likeWCDMA(3G)% and d.dinner_name like%Aand t.area_no=381;select nvl(area_desc,合计) area,count(distinct device_number) ,avg(tot_fee) ,sum(tot_fee) ,sum(tot_fee)/16797258.86 from dw_his_v_bill_month b join (select USER_DINNER from dim_user_dinner d where d.dinner_name likeWCDMA(3G)% and d.dinner_name like%A) t on(t.user_dinner=b.user_dinner) join tb_area_no_temp a on (a.area_no=b.area_no) where b.acct_month=201202 group by rollup(area_desc);select nvl(area_desc,合计) area,count(distinct device_number) 用户数 ,avg(tot_fee) arpu值 ,sum(tot_fee) 总费用 ,sum(tot_fee)/16797258.86 占比 from dw_his_v_bill_month b join (select USER_DINNER from dim_user_dinner d where d.dinner_name likeWCDMA(3G)% and d.dinner_name like%A) t on(t.user_dinner=b.user_dinner) join tb_area_no_temp a on (a.area_no=b.area_no) where b.acct_month=201202 group by rollup(area_desc);有表如下：sqlkokooaselect * from test026;IDNAMESUBJECTSCORE1jim语文881jim数学841jim英语902kate语文862kate数学762kate英语96想得到如下效果：学生编号 学生姓名 语文 数学 英语方法：create table chengji (id number , name varchar2(20) ,subject varchar2(20) ,score number );-学生编号 学生姓名 语文 数学 英语select id 学生编号,name 学生姓名, sum(case when kecheng=语文