oracle数据库常见SQL语句

第一天

–最简单的查询
select 查询的列
from 查询的表

–查询员工表中所有的数据
select *
from emp t

–查询所有员工的编号，姓名，职位，工资
select empno，ename，job，sal
from emp

–查询dept表中的所有数据(*或者指定的列名)
select *
from dept

select deptno,dname,loc
form dept

–查询表中的数据条数
select count(*)
from emp

**distinct表示的是去掉显示重复
**不关键字区分大小写，关键字最好大写

select empno
from emp

select empno,ename,job,sal,deptno
from emp

–算数运算符（只是显示计算之后的数据，不会改变数据库表的数据）
select sal,sal+500，sal*12
from emp

**算数运算符的优先级（乘除优先于加减）

–查询月薪转正后上涨20%的工资
select sal，sal*（1+0.2）
from emp

–员工试用6个月之后上涨20%，查询所有员工工作一年的年薪所得
delect sal*6+sal*6*（1+0.2）
from emp

**空值null不等于空格或者0。null代表空值，参与运算的时候，结果为空
**通用函数nvl
**nvl（参数1，参数2）
**功能：若参数1不为空的时候返回参数1，否则返回参数2.
**前提：两个参数数据类型必须一致
–comm表示的是奖金（可能是空值），计算一年的工资
select sal,comm,sal*6+sal*6*(1+0.2)+12*NVL(comm,0)
from emp

select comm,nvl(comm,0)
from emp

**列别名，只是在显示的时候用到，不会影响数据库表中的数据
select sal 工资或者select as 工资

**三种列别名的时候需要加上双引号
**区分大小写
select sal “Sal”
from emp

**包含空格
select sal “工 资”
from emp

**包含特殊的字符
select sal “工%资”
from emp

**连接操作符 ||
select ename,job,,ename || job
from emp

select ename ||’的职位是’||job
from emp

**去重distinct（所有信息相同的条）
select distinct deptno
from emp

–查询10部门员工的编号，姓名，部分编号
select empno，ename，deptno
from emp
where deptno=10

–查询job是CLERK的员工姓名，职位
select * from emp

**具体的数据是要区分大小写的（字符类型必须加上单引号）
select ename，job
form emp
where job=‘CLERK’and deptno=10

–查询入职日期在81年以后的员工信息(dd-mon-rr)
select *
from emp
where hiredate>=’01-1月-1981’

–工资大于2000的员工信息
select *
from emp
where sal>2000

–部门号不等于10
select *
from emp
where deptno<>10

select *
from emp
where deptno！=10

–between and(是闭区间，包含2000和3000)
select ename,sal
form emp
where sal between 2000 and 3000

select enamel,sal,hiredate
from emp
where hiredate between ‘01-1月-1982’ and ‘31-12月-1982’

–in(参数列表)
select ename,deptno
from emp
where deptno =10 or deptno=20

select ename,deptno
from emp
where deptno in (10,20)

select ename,job
from emp
where job in (‘CLERK’,’SALESMAY’)

–like模糊查询
–%代表任意多个字符
–_代表一位字符
select ename
from emp
where ename like ‘S%’

select ename
form emp
where ename like ‘_C%’

select ename
form emp
where ename like ‘%C_’

select ename
from emp
where ename like ‘%C%’

–_表示下划线的时候escape
select ename
from emp
where ename like ‘S@_%’ ESCAPE ‘@’

–(此处使用两个单引号表示S后边是一个单引号，表示转义)
select ename
from emp
where ename like ‘S”%’

–不要用like去通配时间，造成时间查询不准确

–not in,between,like

–is null
select ename,sal,comm
from emp
where comm is null

select ename,sal,comm
from emp
where comm is not null

– and的优先级高于or

–order by 排序，书写和执行都是在最后
select *
from emp
order by sal asc

–默认是升序排序

select *
from emp
order by sal desc

–能够按照列名排序，也能按照列别名排序
–where字句中不能有别名
select ename,sal 工资
from emp
order by 工资 desc

–能够按照列序号排序
select ename，sal
from emp
order by 2 desc

–按照多列进行排序
select ename，sal，deptno
from emp
order by deptno asc,sal desc

第二天

–单行函数
–字符型
–upper（参数）转换成大写
select upper（ename）from emp

–lower（参数）小写
select lower（ename）from emp

select ename form emp where upper(ename)=’KING’

–initcap首字母大写
select initcap（ename） from emp

–concat(参数1，参数2)
select concat（’hellow’,’world’） from dual
select ‘hellow’||’world’ from dual

–substr(s1,n1,n2)截取
–s1操作的字符数据或者字段
–n1从第几位开始截取，n1为负数，表示从倒数第几位开始截取
–n2截取长度,省略n2表示有多长截取多长，不能为负数。
select ename,substr(ename,3,1)
from emp

–查询第三个字母是A的员工
select * form emp where substr(ename,3,1=’A’)

–length（参数）长度
select ename，length（ename） from emp

–instr（s1，s2，n1，n2）返回s2在s1中，第n1位开始，第n2次出现的位置
–n1 n2 省略表示默认值1.
–查询A在ename中从第二位开始，出现第一次的位置
select ename,instr(ename,’A’,2,1) from emp

–查询第三个字母是A的员工
select ename from emp where instr（ename，’A’,3,1）=3

–查询姓名中有A的
select ename from emp where instr（ename，’A’,1,1）<>0

–trim（s1 from s2）从s2中开头和结尾去掉s1
–去掉开头结尾的空格，掐头去尾(有连续的ss在开头结尾也去掉)
select trim（’ ’ from ’ hello w orld ‘） from dual
select trim （’s’ from ‘ssgdgddd sdsfsdgg ss’） from dual

–replace（s1，s2，s3）s1中的s2用s3替换掉
–将abc中的b用d替换
select replace（’abc’,’b’,’d’） from dual

–数值型
–round四舍五入
–四舍五入到小数点两位
select round(99.234,2) from dual

–四舍五入到整数
select round（55.234） from dual
select round（55.234，0） from dual

–个位数的四舍五入
select round（99.22，-1） from dual：最终结果是100

–trunc截断，不进行四舍五入
–截取到小数点后两位，0表示截取到整数位
select trunc(32.333,2) from dual

–截取到-1位表示清零
select trunc(32.333,2) from dual：结果是30

–mod（n1，n2）取余，模运算
select mod（1300，300）from dual：结果是100
select mod（1300，0）from dual：结果是0
select mod（-300，1600）from dual：结果是-300（结果的正负只和第一个数字的正负有关）

–日期型
–sysdate系统的但钱日期
select sysdate from dual

–months_between返回间隔的月数（大的写前边）
select months_between（sysdate，”01-1月-2000） from dual

–add_months给一个日期加几个月
select hiredate,add_months(hiredate,6) from emp

–next_day下一个星期几
select next_day(sysdate,’星期五’) from dual

–last_day返回当月的最后一天
select last_day(sysdate) from dual

–round 四舍五入到月份，日期是1号
select hiredate,round(hiredate,’MONTH’) from emp

–trunc 截取之后到1号
select hiredate,trunc(hiredate,’MONTH’) from emp

–extract提取（月份，年份等：MONTH,YEAR,DAY）
select hiredate，extract（MONTH from hiredate） from emp

–查询12月份入职的
select hiredate，extract（MONTH from hiredate）=12

–数据类型之间的转换，转换函数
–日期之间可以相减不能加，减去表示相隔的多少天
–to_char
–to_number
–to_date (number和date之间没有转换)
–日期型转换
select hiredate,
to_char(hiredate,’YYYY’) ‘1’,
to_char(hiredate,’YEAR’) ‘2’,
to_char(hiredate,’MM’) ‘3’,
to_char(hiredate,’MONTH’) ‘4’,
to_char(hiredate,’DAY’) ‘5’, 星期几
to_char(hiredate,’DY’) ‘6’, 星期几
to_char(hiredate,’HH24:MI:SS AM’) ‘7’， AM是上下午
to_char(hiredate,’DD “of” MONTH’) ‘8’
from emp

–数值型转换
select sal,to_char(sal,’$999,999.999’) from emp

–to_number
select to_number(‘34343423457.99’,99999.99) from dual

–to_date
–标准的日期格式不需要进行解释
select sysdate-to_date(‘01-1月-1998’) from dual

–日期转换过程中解释
select sysdate-to_date(‘1998 1月 1日’，’YYYY MONTH DD”日”’) from dual

–81年入职的
select *
from emp
where to_char(hiredate,’YYYY’)=’1981’

–通用函数
–nvl的参数的类型必须是一样的
select sal，comm,nvl(comm,0) from emp

select ename,mgr,nvl(to_char(mgr),’没有经理’) from emp

–nvl2（n1,n2,n3）n1不为空返回n2，否则返回n3
select sal，comm，nvl2（comm,comm,0） from emp

–nullif(n1,n2)n1和n2相等则返回空值，否则返回n1
select nullif（5*1，5*2） from dual

–coalesce(….)返回第一个不为空的值
select sal,comm,coalesce(comm,0) from emp

–case,decode两个表达式
select ename,deptno,(case deptno
when 10 then ‘销售部’
when 20 then ‘技术部’
else ‘小卖部’ end) as 部门名称
from emp

select ename,deptno,decode（deptno，
10，’销售部’ ,20,’技术部’ ,’小卖部’ ） as 部门名称
from emp

–多表连接
–查询员工姓名，工资，部门编号，部门名称，工作地点
–笛卡尔积现象：一张表中所有行与另一张表中所有行都发生连接现象，由于没指定有效的连接条件产生的。
–等值连接
select ename，sal，emp.deptno，dname，loc
from emp，dept
where emp.deptno=dept.deptno

–查询工资的等级（非等值连接）
select ename,sal,grade
from emp,salgrade
where sal between losal and hisal

select ename,sal,grade,dname,loc
form emp,dept,salgrade
where emp.deptno=deptno and
sal between losal and hisal
and grade >3

–外部连接
–查询员工姓名，部门编号，部门名称，要求将没有员工的部门也显示出来
–左连接和右连接在oracle中有一个（+）表示，那边缺数据就添加在哪边（加号只能写在一边，两边都写的时候就会出现错误）
select ename，d.deptno，dname
from emp e，dept d
where e.deptno(+)=d.deptno

第三天

–查询员工的姓名，以及他们直接上级的姓名
select e.ename,m.ename
from emp e,emp m
where e.mgr=m.empno(+) (注意：king没有直接上级所以加上加号)

–查询所有工作在new york和chicago的员工姓名，员工编号，以及他们的经理姓名和经理编号
select　e.ename,e.empno,m.ename,m.empno
from emp e,emp m,dept d
where e.mgr=m.empno
and e.deptno=d.deptno
and loc in (‘new york’,’chicago’)

–在上一题的基础上，加上没有经理的员工king，按照员工的编号进行排序
select　e.ename,e.empno,m.ename,m.empno
from emp e,emp m,dept d
where e.mgr=m.empno（+）
and e.deptno=d.deptno
and loc in (‘new york’,’chicago’)
order by e.empno

–查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来
select empno,ename,dname
from emp e,dept d
where e.deptno=d.deptno(+)

–cross join(交叉连接，笛卡儿积)
select ename ,dname
from emp
cross join dept

–natural join(自然链接,两张表中相同的列做等值连接,名称数值类型必须是一样的)
select ename,dname
from emp
natural join dept

–using子句,用哪一列做等值连接，数值类型不一定相同
select ename,dname
from emp
join dept
using (deptno)

–on子句
select ename,dname,grade,m.ename
from emp e
join dept d
on e.deptno=d.deptno（on只写链接条件，其余条件使用where e.deptno=10 ）
join salgrade
on sal between losal and hisal
join emp m
on e.mgr=m.empno
where loc =’chicago’

–left outer join 左外连接（左边的数据全部显示出来，右边出现空行），join可以省略
–显示员工的姓名和部门名称
select ename,dname
from emp e
left outer join dept
on e.deptno=d.deptno

–right outer join右外连接
select ename，dname
from emp e right outer join dept d
on e.deptno=d.deptno

–full outer join 全外连接
select ename，dname
from emp e full outer join dept d
on e.deptno=d.deptno

–显示员工king和ford管理的员工姓名，经理姓名，经理的经理姓名
select e.ename,m.ename,mm.ename
from emp e,emp m,emp mm
where e.mgr=m.empno
and m.mgr=mm.empno(+)
and m.ename in (‘king’,’ford’)

select e.ename,m.ename,mm.ename
from emp e
join emp m
on e.mgr=m.empno
left outer join emp mm
on e.mgr=mm.empno
where m.ename in (‘king’,’ford’)

select from where order by

–分组函数
–max min(可以针对任何类型)

select max(sal),min(sal)
from emp

select max(hiredate)
from emp

select max(ename)
from emp

–sum avg（只能针对数值类型）
select sum(sal),avg(sal)
from emp
where deptno=20

select avg(nvl(comm,0))
from emp

–count(返回条件的记录数)，分组函数空值不参与运算
select count(*)
from emp
where deptno=10

–写empno主要是因为empno不为空，如果选择其他的，可能有空值不会参与运算，不准确
select count(empno)
from emp

–查询有多少种工作，注意去重是对job
select count(distinct job)
from emp

–查询工作在chicago的员工人数，最高工资及最低工资
select count(empno),max(sal),min(sal)
from emp e,dept d
where e.deptno=d.deptno
and loc=’chicago’

–查询每个部门的平均工资,
–select 后边有什么就必须在group by中，除了分组函数，group by后边写了的select 后边可以不写
select d.deptno,d.dname,avg(sal)
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,d.dname
order by avg（sal） desc

–查询每个部门中每个 岗位的工资总和
select deptno，job，sum(sal)
from emp
group by deptno,job

–查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的员工
select count(e.empno),e.mgr.m.ename
from emp e,emp m
where e.mgr=m.empno(+)
group by e.mgr,m.ename

–where 不可能出现分组函数，除非是子查询中,指定分组前条件
–having限定分组后的条件

–把不等于20的部门进行分组
select deptno,avg(sal)
from emp
where deptno<>20
group by deptno
having avg（sal）>2300
order by avg(sal)

–查询部门平均工资大于2000.且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序
select deptno,dname,count(empno),avg(sal)
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,dname
having avg(sal)>2000 and count(empno)>2
order by count(empno) asc

第四天

–单行函数的嵌套,可以多层嵌套
select concat（concat(ename,job),sal）
from emp

–分组函数嵌套，只能嵌套两层,在select 中别写上分组的列名
–显示部门的平均工资的最大值
select max(avg(sal))
from emp
group by deptno

–常用的分组函数：max min avg sum count
–显示经理号码，经理名称，这个经理所有管理员工的最低工资，没有经理的king也要显示，不包括最低工资小于3000的，按照工资由高到低排序
select e.mgr,m.ename,min(sal)
from emp e,emp m
where e.mgr=m.empno(+)
group by e.mgr,m.ename
having min(e.sal)>=3000
order by min(e.sal) desc

–子查询
–查询工资比jones工资高的员工信息
select * from emp
where sal>(select sal from emp where ename=’jones’)

select e.ename,e.sal
from emp e,emp j
where j.ename=’jones’ and e.sal>j.sal

–查询工资最低的员工姓名
select ename
from where sal=(select min(sal) from emp)

–单行比较符:> <> < =
–多行比较符：any all in

–查询部门平均工资高于20部门平均工资的部门信息
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>(select avg(sal) from emp where deptno=20)

–查询哪个部门的员工人数高于各部门平均人数
select deptno,count(empno)
from emp
group by deptno
having count(empno)>(select avg(count(empno)) from emp group by deptno)

–查询工资在这几个工资中的员工编号和姓名
select empno,ename
from emp
where sal in (select min(sal) from emp group by deptno)

–查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数
select d.deptno,d.dname,count(empno)
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,d.dname
having count(empno)>(select avg(count(empno)) from emp group by deptno)

–查询是经理的员工编号
select * from emp
where empno in (select distinct mgr from emp)

–有空值的时候不能使用not in
–any 表示其中任意一个
–=any和in一样
–> any 大于子查询中最小值
–< any 小于子查询中最大值

–all
–> all 大于最大值
–< all 小于最小值

–多列子查询
–查询出和1981年入职的任意一个员工的部门和职位完全相同员工姓名，部门，职位入职日期，不包括1981年入职员工(不能将deptno和job分开写，分开写就不对应一个人了)
select ename,deptno,job,hiredate
from emp where (deptno,job) in (select deptno,job from emp where to_char(hiredate,’YYYY’)=’1981’)
and to_char(hiredate,’YYYY’)<>’1981’

–查询比自己部门平均工资高的员工姓名，工资，部门编号，部门名称
–在from中使用子句(子句相当于创建了一张新的表一样)
select ename,sal,e.deptno,avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) f
where e.deptno=f.deptno
and sal >avgsal

–insert into（不写或者写全列名是一样的）
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(‘8989’,’华夏’,’书童’,7788,’01-7月-1981’，1900，3，20)

–实现多条数据的插入只能从别的表中先读取数据
create table emp1
as select * from emp

create table emp2
as select * from emp where 1=0

insert into emp2
select * from emp1 where deptno =10

insert into emp2(empno,ename)
select empno,ename from emp1 where deptno=10

–update
update emp2
set deptno=20
where deptno is null

update emp2
set sal=500
where deptno=20

update emp2
set sal=sal+500

update emp2
set sal=null
where deptno=20

update emp2
set sal=sal+(select avg(sal) from emp2)
where deptno=20

–delete
delete from emp2 where deptno=20

delete from emp2 where job=’clerk’

–commit
–rollback

–创建数据库表create
create table student(
学号 number（10）,
姓名 varchar2（20）, 变长
性别 char（2）default ‘男’， 定长
身份证号 char（18），
出生日期 date
)

–创建一张空表（1=0条件不成立所以创建的是空表）
create table emp3
as
select * from emp where1=0

–alter修改表结构
alter table emp3 add(
学号 number（10）,
姓名 varchar2（20）
)

alter table emp3 modify(
学号 number（11）
)

alter table emp3 modify(
学号 default 1234567
)

alter table emp3 add(
学号1 number（10）default 9999,
姓名 varchar2（20）
)

–drop删除表
drop table emp3

第五天

–约束
–not null
–unique
–primary key
–foreign key references
–check

–追加和删除约束

–view视图
–创建一个视图v_emp10，通过该视图只能查看10号部门员工编号，员工姓名，职位
create view empvu10
as
select empno,ename,job
from emp
where deptno=10

–显示视图的结构
desc empvu10

–用create or replace view子句修改视图 empvu10，为每个列添加别名
create or replace view empvu10(employee_number,employee_name,job_title)
as select empno,ename,job
from emp
where deptno=10

–with check option
–with read only
–drop view empvu10删除视图

–创建序列test_seq,起始值为10，每次增长2，最大值100，最小值9，循环序列，每次缓存10
create sequence test_seq
start with 10
increment by 2
maxvalue 100
minvalue 9
cycle
cache 10

–使用序列的时候
–序列名字.currval
–序列名字.nextval

–使用序列student_seq生成sid列插入值
insert into student
values(student_seq.nextval,’scott’,….)

–查询student_seq序列当前值
select student_seq.currval from dual

–序列的修改没有start with子句

–rowid伪列

–索引（自动创建的时候，当有primary key或者unique约束时，数据库会自动创建一个索引）

–在emp表的ename字段上创建索引
create index idx_ename on emp(ename)

–在emp表的deptno和job的组合上创建索引
create index idx_emp_deptnojob
on emp(deptno,job)

–删除索引
drop index idx_ename

–创建employees表的别名
create synonym s_emp for hr.employees

–删除同义词
drop synonym s_emp

–使用子查询创建的新表没有约束（没有主键之类的约束）

create table test_emp
as
select * from emp

insert into test_emp
select * from test_emp

select * from test_emp

update test_emp
set empno=rownum

alter table test_emp modify (empno number(7))

select * from test_emp where empno=150000

create index idx_test_emp_empno
on test_emp(empno)

–索引会降低增删改的速度和占用内存

–集合运算
–联合union（并）
–相交intersect（交）
–相减minus（减）
–完全联合union all（不会去掉重复）

–使用联合运算，查询10号部门及20号部门的员工姓名，部门编号
select ename ,deptno,null
from emp where deptno=20
union (intersect,minus,union all)
select ename ,deptno,sal
from emp where deptno=10

–查询比自己部门平均工资高的信息的另一种高级子查询
select ename,sal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno)