欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle常用SQL查询

程序员文章站 2022-04-12 21:40:46
建表信息 1.表结构 Emp----员工信息表 Ename varchar2(30), --姓名 Empno number(5), --编号 Deptno numb...

建表信息

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