oracle学习及练习语句

Oracle练习 (2008-07-26 22:46:11) 标签：杂谈 分类：数据库 6-1 --创建数据库 CREATE table salary( sid number(8) not null, sname varchar2(50), s_salary number(8), dep varchar2(50), constraint salary_pk primary key(sid) ) insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'刘德华',15000,'曲艺部'); insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'张学友',11000,'曲艺部'); insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'赵志强',80,'学术部'); insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'孔子',88088,'学术部'); insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'庄子',78088,'学术部'); insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'二傻子',-100,'学术部'); insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'姚明',30000,'健身部'); insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'施瓦辛格',31000,'健身部'); insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'史泰龙',11000,'健身部'); insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'芙蓉姐姐',-500,'恶搞部'); insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'周星驰',8500,'恶搞部'); select * from salary; 6-2 --一共有多少人啊? select count(*) from salary --工资总和是多少啊? select sum(s_salary) from salary --平均工资是多少啊? select avg(s_salary) from salary --最高工资是多少啊?(遗留问题:只知道最高工资,不知道名字) --select max(s_salary) from salary --最低工资是多少啊?(遗留问题:只知道最低工资,不知道名字) select min(s_salary) from salary --每个部门的工资总和是多少啊? 6-3 --看看都有哪些部门啊? select dep from salary group by dep --select dep,sum(s_salary) from salary group by dep select dep,sum(s_salary) from salary group by dep --每个部门的平均工资是多少啊? select dep,avg(s_salary) from salary group by dep --每个部门的最高工资是多少啊? select dep,max(s_salary) from salary group by dep --每个部门的最低工资是多少啊? select dep,min(s_salary) from salary group by dep 6-4 --看看部门中最高工资,不过30000以下的我就不看了 select max(s_salary),dep from salary group by dep having max(s_salary) >= 30000 在一个表中查询工资表中的最高工资的人的相关情况 select * from salary where s_salary in (select max(s_salary) from salary) 为子查询创建第二个表 create table dep( depid number(8) not null, depName varchar2(50), constraint dep_pk primary key(depid) ) insert into dep (depid,depname) values (dep_seq.nextval,'曲艺部'); insert into dep (depid,depname) values (dep_seq.nextval,'健身部'); insert into dep (depid,depname) values (dep_seq.nextval,'学术部'); insert into dep (depid,depname) values (dep_seq.nextval,'恶搞部'); --卸载字段 alter table salary drop column dep; --增加字段 alter table salary add depid number(8); --设置外键 alter table salary add constraint salary_fk foreign key (depid) references dep(depid) insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'刘德华',15000,1); insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'张学友',11000,1); insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'赵志强',80,3); insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'孔子',88088,3); insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'庄子',78088,3); insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'二傻子',-100,3); insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'姚明',30000,2); insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'施瓦辛格',31000,2); insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'史泰龙',11000,2); insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'芙蓉姐姐',-500,4); insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'周星驰',8500,4); 6-5 --通过内链接查询员工姓名和部门 select s.sname,d.depName from salary as s,dep as d where s.depid = d.depid --查找重复姓名 --先插入一条数据 insert into salary (sname,s_salary,depid) values ('二傻子',-100,3); --查询 select * from salary where sid in (select s1.sid from salary s1,salary s2 where s1.sid <> s2.sid and s1.sname = s2.sname group by s1.sid) 6-6 --先卸载外键 alter table salary drop constraint salary_fk 在插入值,有个新部门5. insert into salary (sname,s_salary,depid) values ('吴宗宪',8000,5); --左连接:左边表中左右数据不管条件成立不成立都出现 select s.sname,s.s_salary,d.depName from salary s,dep d where s.depid = d.depid(+) --右连接等同左连接. 子查询 查询最高的工资人的部门和名字 select s.sname,d.depName from salary as s,dep d where s.depid = d.depid and s.s_salary = (select max(s_salary) from salary) 查询最高的各个部门工资最高人和部门名字 select s.sname,d.depName from salary as s,dep d where s.depid = d.depid and s.s_salary in (select max(s_salary) from salary group by depid) 6-7 select * into salary1 from salary 子查询 select (select max(s_salary) from salary where depid=1) ,(select max(s_salary) from salary where depid=2) ,(select max(s_salary) from salary where depid=3) ,(select max(s_salary) from salary where depid=4) from dual select * from (select sname,s_salary from salary order by s_salary desc) where rownum < 4