Oracle常用SQL查询

建表信息

1.表结构
Emp----员工信息表
Ename varchar2(30), --姓名
Empno number(5),  --编号
Deptno number(5), --所在部门
Job varchar2(20), --工种(人员类别)，如：manager 经理，clerk 办事员Hiredate  Date  --雇用日期
Hiredate Date, --雇佣日期
Comm number(6,2),  --佣金
Sal  number(6,2)  --薪金

Dept-----部门表
Dname varchar2(30),  --部门名
Deptno number(5), --部门号
Loc varchar2(50)  --位置

2.准备数据：

create table emp –创建员工信息表
(
Ename varchar2(30), --姓名
Empno number(5),  --编号
Deptno number(5), --所在部门
Job varchar2(20), --工种(人员类别)，如：manager 经理，clerk 办事员Hiredate  Date  --雇用日期
Hiredate Date --雇佣日期
Comm number(6,2),  --佣金
Sal  number(6,2)  --薪金
)
insert into emp(Ename,Empno,Deptno,Job,Comm,Sal) 
values('刘涛',10001,10,'办事员',500,2000)
insert into emp(Ename,Empno,Deptno,Job,Comm,Sal) 
values('吴昊',10002,10,'办事员',650,2200)
insert into emp(Ename,Empno,Deptno,Job,Comm,Sal) 
values('唐丹丹',10002,20,'办事员',650,2200)
insert into emp(Ename,Empno,Deptno,Job,Comm,Sal) 
values('李阳杨',20001,20,'经理',980,3200)

create table dept  --部门表
(
Dname varchar2(30),  --部门名
Deptno number(5), --部门号
Loc varchar2(50)  --位置
)
insert into dept(Dname,Deptno,Loc) values('市场部',10,'辽宁大连')
insert into dept(Dname,Deptno,Loc) values('公关部',20,'辽宁沈阳')
select * from dept;

一、简单查询操作

　1、选择部门30中的雇员 
    select * from emp e where e.deptno='30'
　2、列出所有办事员的姓名、编号和部门
    select e.ename, e.empno, d.dname from emp e, dept d where e.deptno=d.deptno
　3、找出佣金高于薪金的雇员
    select * from emp e where e.comm>e.sal
4、找出佣金高于薪金60%的雇员
select * from emp e where e.comm>e.sal * 0.6
5、找出部门10中所有经理和部门20中的所有办事员的详细资料
select * from emp e where (e.job='?-àí' and e.deptno='10') or (e.deptno='20' and e.job='°ìê??±')
6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select * from emp e where (e.job='?-àí' and e.deptno='10') or (e.deptno='20' and e.job='°ìê??±') or (e.job!='?-àí' and e.job!='°ìê??±' and e.sal>=2000)
　7、找出收取佣金的雇员的不同工作
    select distinct job from emp where nvl(comm, 0)>0
　8、找出不收取佣金或收取的佣金低于100的雇员
    select  * from emp where nvl(comm, 0)>100
　9、找出各月最后一天受雇的所有雇员
select * from emp where hiredate=last_day(hiredate)
　10、显示只有首字母大写的所有雇员的姓名
select ename from emp where initcap(ename)=ename
　11、显示正好为6个字符的雇员姓名
select ename from emp where length(ename)=6
　12、显示不带有'R'的雇员姓名
select ename from emp where ename not like '%r%'
　13、显示所有雇员的姓名的前三个字符
    select substr(ename, 1, 3) from emp
14、显示所有雇员的姓名，用a替换所有'A'
select replace(ename, 'a', 'A') from emp
　15、显示所有雇员的姓名以及满10年服务年限的日期
    select ename, add_months(hiredate, 120) from emp
　16、显示雇员的详细资料，按姓名排序
    select * from emp e, dept d where e.deptno=d.deptno  order by e.ename
　17、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
    select ename, hiredate from emp order by hiredate
　18、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
select ename, hiredate from emp order by hiredate
　19、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面
select ename, to_char(hiredate, 'yyyy'), to_char(hiredate, 'mm') from emp order by hiredate
　20、显示在一个月为30天的情况下所有雇员的日薪金
    select ename, round(sal/30,2) from emp
　21、找出在（任何年份的）2月受聘的所有雇员
    select * from emp where to_char(hiredate, 'mm')='02'
　22、对于每个雇员，显示其加入公司的天数
    select ename, trunc(sysdate-hiredate) from emp
　23、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名
    select * from emp where ename like '%a%'
　24、以年、月和日显示所有雇员的服务年限
    select ename, trunc(months_between(sysdate, hiredate)/12), trunc(months_between(sysdate, hiredate)), trunc(sysdate-hiredate) from emp

1学生表student
S#学号，sname姓名，difdate出生日期，   年级grade
2课程表 course
c#课程号 ，名字cname
3成绩单score
s#学号 c#课程号 成绩score
create table student(
       s#学号 varchar2(30),
       sname姓名 varchar2(30),
       difdate出生日期 date,
       grade年级 varchar2(30)


)

create table course(
       c#课程号 varchar2(30),
       cname名字 varchar2(30)


)

create table score(
       s#学号 varchar2(30),
       c#课程号 varchar2(30),
       score成绩 varchar2(30)
)

二、条件查询

1.查询学生表中姓李并且是2007级的学生
select * from student where sname姓名 like '李%' and grade年级=’2007’
2.查询 姓王的学生的物理课的成绩
select s.sname姓名,c.cname名字,t.score成绩 from student s,course c,score t where s.s#学号=t.s#学号 and c.c#课程号=t.c#课程号 and s.sname姓名 like '王%' and t.c#课程号='4'
3.统计每个年级有多少人
select grade年级，count(grade年级) from student  group by grade年级

4.2007级的各学生的平均成绩 没有成绩的为0；
select t.s#学号,s.sname姓名,avg(t.score成绩) from student s,score t where s.s#学号=t.s#学号 and s.grade年级='2007' group by (s.sname姓名,t.s#学号)

5. 2007级学生每科的   平均成绩和最高成绩 最低成绩 保留2位小数点 四舍五入
select c.cname名字,t.c#课程号,round(avg(t.score成绩),2),max(t.score成绩¨),min(t.score成绩)from course c,score t where c.c#课程号=t.c#课程号 and group by (t.c#课程号,c.cname名字

6.给2007级学生的 数学成绩加5分
select s.sname姓名,c.cname名字,t.score成绩+5 from student s,course c,score t where s.s#学号=t.s#学号 and c.c#课程号=t.c#课程号 and s.grade年级='2007' and t.c#课程号='2'

7.求同月出生的 人数
select count(to_char(difdate出生日期,'mm')) from student group by to_char(difdate出生日期,'mm')

8.删除 姓名是张三 的大学语文 成绩
delete from score where s#学号='2008020177' and c#课程号='3'
9.将数学替换成高等数学

update course set cname名字='高等数学' where c#课程号='2'

10.格式化 ，显示 将学号修改成S开头 不足12位左边补0；
elect 'S'||lpad(s#学号, 11, '0') from student s