Oracle数据库常用命令

以本表为例（emp）

（dept）

 

 

一、知识点

1.基础查询select
语法：select 字段列表 from 表名;

2.#取别名（使用空格，as）

select EMPNO 员工编号,job, sal, sal*12 as  年薪 from emp;

3.#去重复(distinct)

select distinct job from emp;

4.#查询可以用表达式

select sal*12 from emp;

5.#排序order by asc |desc

#按工资降序排序显示所有的员工信息
select * from emp ORDER BY sal desc;

6.#指定范围查询 BETWEEN and 和not BETWEEN and

7.#不等于查询(> < >= <= != !>  !<  <>)

8.like模糊查询 

% 表示0个或多个字符
_表示1个字符

9.in 查询在某个集合中 not in

10.is null 为空 is not null
11.not 取反
12.多条件查询 and且 or 或

13.函数：

nvl(x,values) 处理是否为空，为空返回values值
length()长度函数
聚合函数：count() 求列表个数
Max() 求最大值
Min() 求最小值
sum() 求和
avg()求平均值
特点：不能放到where条件中使用

14.分组，在遇到每个的题目时用

select DEPTNO #分组名称，跟分组项相同
from emp
GROUP BY DEPTNO #分组项

例：#查询每个部门有多少人
select * from emp;
select DEPTNO,count(empno)
from emp
GROUP BY DEPTNO

15.having进行二次查询

16.多表连接

（1.）等值连接
语法：select * from 表1 join 表2 on 表1.列名=表2.列名

例：select a.*,b.* from a,b where a.sno=b.sno and a.score>60

       select a.*,b.* from a join b on a.sno=b.sno where a.score>60
（2.）左连接右连接（属于外连接）
左连接语法：select * from 表1 left join 表2 on 表1.列名=表2.列名

右连接语法：select * from 表1 rigth join 表2 on 表1.列名=表2.列名

例：左连接：select a.*,b.* from a left join b on a.sno=b.sno where a.score>60

      右连接：select a.*,b.* from a lright join b on a.sno=b.sno where a.score>60

（3.）不等值连接

例：select * from emp e join SALGRADE s on e.sal BETWEEN s.LOSAL and s.HISA

17.子查询

子查询是一个数据，在where子句中直接可使用=（子查询）即可

子查询是多个数据，在where子句中需要使用 列in（子查询）

18.删除部分数据语法

delete from 表 where 筛选条件

19.删除全部数据语法

delete from 表名称

20.查询时使用ifnull如果字段值为空则用0来表示，如果非空则为原来的数据

select sno if null（score，0）from 表格

 

二、例题

1.#查询所有的表格信息

select * from emp

2.#选择部门编号是30的员工

select * from emp where DEPTNO=30
3.#列出所有办事员（CLERK）的姓名，编号和部门编号
select ENAME,MGR,EMPNO from emp where JOB='CLERK'
4.#找出奖金高于薪金的员工
select * from emp where comm>sal
5.#找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料
select * from emp where DEPTNO=10 and job='MANAGER' or DEPTNO=20 and job='CLERK'
6.#找出部门10中所有经理（MANAGER），部门20中所有办事员（CLERK），以及既不是经理又不是办事员但其薪金大于或等于2000的所有员工和详细资料
select * from emp where DEPTNO=10 and job='MANAGER' or DEPTNO=20 and job='CLERK' or job!='MANAGER' and job!='CLERK' and sal>=2000
7.#找出有奖金的员工的不同工作
select job from emp where comm is not null
8.#找出没有奖金或奖金低于100的员工
select * from emp where comm is null or comm<100
9.#找出早于87年前入职的员工
select * from emp where HIREDATE<to_date('1987-01-01','yyyy-mm-dd');
10.#显示正好为5个字符的员工姓名
select ENAME from emp where length(ENAME)=5
11.#显示不带“R”的员工姓名
select ENAME from emp where ENAME not like '%R%'
12.#显示所有员工的姓名，加入公司的时间，按入职时间进行排序
select ENAME,HIREDATE from emp order by HIREDATE asc
13.#显示所有员工的姓名、入职日期、工作和工资，按工资的降序排序，若工资相同则按入职日期排序
select ENAME,HIREDATE,job,sal from emp order by sal desc

14.#显示姓名字段的任何位置包含“A”的所有员工的姓名
select ENAME from emp where ENAME like '%R%'
15.#列出所有员工的年收入，按年薪从低到高排序
select ENAME,SAL*12 from emp order by sal*12 asc
16.#显示年薪等于奖金加年薪
select ENAME,sal,sal*12+nvl(comm,0) 年薪 from emp;
17.#函数length
select *from emp where length(ENAME)=5
18.#count 函数
#查询有多少领导
select count(mgr) from emp
#查询有多少员工
select count(ENAME) from emp
#查询有多少部门（去重复）
select count(distinct DEPTNO) from emp
19.#查询30部门什么时候创建的
select min(hiredate) from emp where deptno=30
20#查询最晚的入职时间
select max(hiredate) from emp
21.#查询员工工资总和和平均值
select avg(sal) 平均 from emp;

22.#查询部门人数
select deptno,count(deptno)
from emp
group by deptno

23.查询每个工作的最高工资和最低工资

select job,max(sal),min(sal)
from emp
group by job

24..查询每个部门成立日期（最早入职）
select deptno,min(HIREDATE)
from emp
group by deptno
25..查询每个部门需要发多少工资
select deptno,sum(sal)
from emp
group by deptno
26.查询每个工作的平均工资和平均奖金
select job,avg(sal),avg(comm)
from emp
group by job
27.查询每个领导都带了几个小弟
select mgr,count(mgr)
from emp
group by mgr
28..查询每一个部门姓名带a的员工数量，只显示小于三个员工的部门
select deptno,min(deptno)
from emp where ENAME like '%A%'
group by deptno
having count(deptno)<3
29..查询1981年入职的员工数量
select count(HIREDATE) from emp where HIREDATE between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-12-30','yyyy-mm-dd')

.30.列出至少有一个员工的所有部门
select distinct(deptno) from emp where deptno>=1
31.列出薪金比“SMITH”多的所有员工
select * from emp e where e.sal>(select sal from emp where ENAME='SMITH')
32.列出所有员工的姓名及其直接上级的姓名
select e.ENAME,em.ENAME
from emp e,emp em
where e.empno=em.mgr

 

多表查询
等值连接
select * from 表1 join 表2 on 表1.列名=表2.列名
内连接
select* from emp e,dept d where e .DEPTNO=d.DEPTNO
左连接 select * from 表1 left join 表2 on 表1.列名=表2.列名
查询入职最早的那个员工全部信息
select * from emp e where e.HIREDATE=(select min(HIREDATE) from emp);

select * from emp e where e.sal>(select avg(sal) from emp)
#等值查询
select * from emp e,dept d where e.DEPTNO=d.DEPTNO
#右连接
select * from dept d rigth join emp e on d.DEPTNO=e.DEPTNO
#不等值查询
select * from emp e join SALGRADE s on e.sal BETWEEN s.LOSAL and s.HISAL

 

修改列名

增加列名：alter table 表名 add  新列名 varchar(255)

删除列名：alter table 表名 drop column 列名 varchar(255) 

修改列名：alter table 表名 change 列名 新列名 varchar(255)