Oracle语句

//替换字段中的指定字符
//把A表中name这个字段中的值的大X换成小x，条件是nf=2019
update A set name=replace(name,'X','x')  where nf=2019

--===========解锁scott用户并重新设置密码
alter user scott account unlock;
alter user scott identified by tiger;

1、查询

1.1 基本查询

--1.查询出所有emp中的信息，并用中文进行字段重命名
select empno as "员工编号",ename "员工姓名",job 职位,mgr "领导编号",hiredate "入职日期",sal "工资",comm "奖金",deptno "部门编号" from emp;


--2.查询emp表中员工的job信息，并去除重复信息
select distinct(job) from emp;


--3.查询emp表中员工的全年的工资总和(sal总和)
select ename,12*sal from emp;

--4.查询emp表中员工的全年收入总和(sal+comm的总和)
select ename,12*sal+nvl(comm,0) from emp;

--5.查询emp表中员工编号，姓名
--输出格式如下：编号:xxx，姓名：xxx
----Concat拼接方式
select concat(concat('编号:',empno),concat('，姓名：',ename)) from emp;

----Oracle的||方式
select '编号'||empno||'，姓名：'||ename from emp;

1.2 条件查询

--1.查询工资大于1500的员工
select * from emp where sal >= 1500;

--2.查询工资大于1500并且有奖金的雇员
select * from emp where sal >= 1500 and comm  is not null;

--3.查询工资大于1500或者有奖金的雇员
select * from emp where sal >= 1500 or comm  is not null;

--4.查询工资大于1500并且没有奖金的雇员
select * from emp where sal >= 1500 and comm  is null;

--5.查询员工姓名为smith的员工
select * from emp where ename = 'SMITH';

1.3 范围查询

--1.查询工资大于1500但小于3000的全部雇员
---->=,<=方式
select * from emp where sal >= 1500 and sal <= 3000;

----between and方式
select * from emp where sal between 1500 and 3000;

--2.查询1981-1-1到1981-12-31号入职的雇员（between and）
select * from emp where hiredate between to_date('1981-1-1','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd')

--3.查询员工编号是7369,7654,7566的员工
----OR方式
select * from emp where empno = 7369 or empno = 7654 or empno = 7566
----IN方式
select * from emp where empno in(7369,7654,7566)

--4.查询雇员姓名是'SMITH','ALLEN','WARD'的雇员信息
----IN方式
select * from emp where ename in('SMITH','ALLEN','WARD')

1.4 模糊查询like

--1.查询所有雇员姓名中第二个字符有‘M’的雇员
select * from emp where ename like '_M%'

--2.查询名字中带有‘M’的雇员
select * from emp where ename like '%M%'

--3.查询雇员编号不是7369的雇员信息
----<>方式
select * from emp where empno <> 7369;
----!=方式
select * from emp where empno != 7369;

java的模糊查询语句

colName like concat('%',#{paramName},'%')

colName like '%${paramName}%'

1.5 排序 order by

--1.查询雇员的工资进行降序排序
select ename,sal from emp order by sal desc;

--2.查询雇员的奖金并做降序排序（关于nulls first/nulls last）
select ename,comm from emp order by comm desc nulls last;

--3.查询雇员的工资做降序排列并且其中奖金部分是升序排序
select ename,sal,comm from emp order by sal desc,comm asc;

2、函数

2.1 单行函数

/*
伪表，虚表：dual 没有任何的实际意义，只是为了补全Oracle查询语法
*/

2.1.1 字符函数

--1.将'smith'转换成大写--关键字:upper
select upper('smith') from dual;

--2.将'SMITH'转换成小写--关键字:lower
select lower(ename) from emp;

--3.将'smith'首字母大写--关键字:initcap
select initcap(ename) from emp;

--4.将'helloworld'截取字符串成'hello'--关键字substr
select substr('helloworld',0,5) from dual;

--5.获取'hello'的字符串长度--关键字length
select length('hello') from dual;

--6.将'hello'中的l用x进行替换--关键字replace
select replace('hello','l','x') from dual;

2.1.2 数值函数

--1.将15.66进行四舍五入（从-2到2）--关键字round
select round(15.66,-2) from dual;  --0
select round(15.66,-1) from dual; --20
select round(15.66,0) from dual; --16
select round(15.66,1) from dual; --15.7
select round(15.66,2) from dual; --15.66

--2.将15.66进行截断（从-2到2）--关键字trunc
select trunc(15.66,-2) from dual;  --0
select trunc(15.66,-1) from dual; --10
select trunc(15.66,0) from dual; --15
select trunc(15.66,1) from dual; --15.6
select trunc(15.66,2) from dual; --15.66


--3.对15/3进行求余数--关键字mod
select mod(15,3) from dual;

2.1.3 日期函数

--1.查询系统时间--关键字sysdate
select sysdate from dual;

--2.查询雇员进入公司的周数
select ename,(sysdate-hiredate)/7 from emp

--3.查询雇员进入公司的月数--关键字months_between
select ename,months_between(sysdate,hiredate) from emp;

--4.求出三个月后的日期--关键字add_months
select ename,hiredate,add_months(hiredate,3) from emp;

--5.查询2018年以后的人员
select name from emp where time>to_date('2017-12-31','yyyy-MM-dd')

2.1.4 转换函数

-- 日期转为char
--1.将系统日期显示为yyyy-mm-dd hh:mi:ss(去掉补零和24小时显示时间)--关键字to_char
select to_char(sysdate,'yyyyfm-mm-dd hh24:mi:ss') from dual;

----显示成年月日
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'MM')||'月'||to_char(sysdate,'dd')||'日' from dual;
--2.将字符串'1981-1-1'转换成日期类型--关键字to_date
select to_date('1981-1-1','yyyy-MM-dd') from dual;

select to_number('99') from dual;
select to_char(99) from dual;

2.1.5 通用函数

--1.空值的处理函数,如果comm是空值，就返回0，如果不是控制，还返回comm
select nvl(comm,0) from emp;
--2.nvl2(判断值,空返回值,非空返回值) ,如果参数1是空，返回参数2，如果参数1不为空，返回1
select nvl2('xxxxxx','1','2') from dual;

2.1.6 条件表达式

--1.查询员工的job内容并转成中文显示
----decode方式，查询Job字段，如果结果是CLERK，就显示为柜员，如果字段是SALESMAN，就显示为销售，以此类推
select ename,decode(job,'CLERK','柜员','SALESMAN','销售','MANAGER','管理','其他') from emp;
----case when then end方式,case是字段名，when后面是查询的结果，then后面是要重命名的值，else是除了已经设置了的when的值，其他都显示为“其他”
select ename, case job when 'CLERK'  then  '柜员' 
                       when 'SALESMAN'  then  '销售'
                       when 'MANAGER'  then  '管理'
     else
       '其他'
       end from emp;

2.1.7 取整

　   1.取整(大)  
　　select ceil(92.4) value from dual

　　2.取整（小） 
　　select floor(-1.001) value from dual

　　3.取整（截取）
　　select trunc(-1.002) value from dual 

	4.取整(舍入)
	select round(99.5) value from dual

2.2 多行函数

--1.查询所有员工记录数--关键字count，查有多少条记录
select count(*) from emp;

--2.查询佣金的总数--sum，查某个字段的总和（如何查询某个字段的总数量）
select sum(comm) from emp;

--3.查询最低工资--关键字min，查某字段最小值
select min(sal) from emp;

--4.查询最高工资--关键字max，查某字段最大值
select max(sal) from emp;

--5.查询平均工资--关键字avg，查某字段平均值
select avg(sal) from emp;

--6.查询20号部门的员工工资总和
select sum(sal) from emp where deptno = 20;

2.3 分组函数

--1.查询部门编号及人数--分组查询关键字group by，按照部门编号分组显示
select deptno,count(*) from emp group by deptno;

--2.查询每个部门编号及平均工资--按照部门编号分组显示
select deptno,avg(sal) from emp group by deptno;

--3.查询部门名称，部门编号，平均工资，如果查询多个字段，想要使用group by,就得在group by 中写上所有要查询的字段，否则报错，聚合函数可以除外
select dname,emp.deptno,avg(sal) from dept,emp where dept.deptno = emp.deptno group by emp.deptno,dname

--4.查询出部门人数大于5人的部门，有分组函数的要筛选条件就用Having
select deptno,count(*) from emp group by deptno having count(*) > 5

--5.查询部门编号，部门名称，平均工资且平均工资大于2000
select emp.deptno,dname,avg(sal) from emp,dept where emp.deptno = dept.deptno
 group by emp.deptno,dname having avg(sal) > 2000;

2.4 多表关联查询

--查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,及领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级
select * from salgrade;
select * from emp;
select * from dept;

select e1.empno,e1.ename,e1.deptno,d1.dname,d1.loc,decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') "salLevel",e1.mgr,e2.ename,e2.deptno,d2.dname,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') "salLevel"
from emp e1,dept d1,salgrade s1,emp e2,dept d2,salgrade s2 
 where e1.deptno = d1.deptno and e1.sal between s1.losal and s1.hisal
and e1.mgr = e2.empno and e2.deptno = d2.deptno and e2.sal between s2.losal and s2.hisal 
--1.查询员工编号,员工姓名,领导编号,领导姓名

--2.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,领导编号,领导姓名,领导部门编号,领导部门名称

--3.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,领导工资等级

--4.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级

select e1.empno,e1.ename,e1.deptno,d1.dname,d1.loc,decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级'),e2.empno,e2.ename,e2.deptno,d2.dname,d2.loc,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') from emp e1,emp e2,dept d1,dept d2,salgrade s1,salgrade s2 where 
e1.mgr = e2.empno and e1.sal between s1.losal and s1.hisal
and e1.deptno = d1.deptno and e2.sal between s2.losal and s2.hisal
and e2.deptno = d2.deptno

2.4.1 外连接

--1.查询员工编号，姓名，领导编号，领导姓名，包括没领导的，以left join左边的表为准
----left join on方式
select e1.empno,e1.ename,e2.empno,e2.ename  from emp e1 left join emp e2 on e1.mgr = e2.empno

----Orcl的(+)方式,写了+的字段以另一个没写+的表为准，如下，e1没有加号，e2有加号，就以e1为准，相反，如果e1有加号，e2没有，就以e2为准
select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+)

--2.查询出所有部门信息(包括没员工的部门)及部门下的员工信息
select * from emp,dept where emp.deptno(+) = dept.deptno;

2.4.2 子查询

--1.查询比雇员7654工资高，同时从事和7788的工作一样的员工
select * from emp where sal > (select sal from emp where empno = 7654)
and job = (select job from emp where empno = 7788);


--2.查询每个部门最低工资及最低工资的部门名称和雇员名称
select emp.empno,emp.ename,e1.minsal,e1.deptno from (select min(sal) minsal,deptno from emp group by deptno) e1,emp,dept 
where e1.deptno = dept.deptno and emp.deptno = e1.deptno and e1.minsal = emp.sal;


select * from dept;
select * from emp;
--===========课堂练习
--1.找到员工表中工资最高的前三名
select rownum,empno,ename,sal from emp  order by sal desc;

select rownum,e.* from (select emp.* from emp order by sal desc)e where rownum <=3

--2.找到员工表中薪水大于本部门平均工资的所有员工
select emp.empno,emp.ename,e1.avgsal,e1.deptno,emp.sal from (select avg(sal) avgsal,deptno from emp group by deptno) e1,emp 
where e1.deptno = emp.deptno and e1.avgsal < emp.sal


select avg(sal),deptno from emp group by deptno
select * from emp;
--3.统计每年入职的员工个数
select count(*),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');

select sum(hcount) "Total",sum(decode(hdate,'1980',hcount)) "1980",min(decode(hdate,'1981',hcount)) "1981",max(decode(hdate,'1982',hcount)) "1982",avg(decode(hdate,'1987',hcount)) "1987" from (select count(*) hcount,to_char(hiredate,'yyyy') hdate from emp group by to_char(hiredate,'yyyy')) e;

2.4.3 分页查询

--1.查询员工表，将员工工资进行降序查询，并进行分页取出第一页，一页三条记录
select * from (select rownum r,e.* from (select * from emp order by sal desc)e) e1 
where r > 0 and r <= 3
/*
分页公式
pageNo = 1
pageSize = 3
select * from (select rownum r,e.* from (select * from 表名 order by 列名 desc)e) e1 
where r > (pageNo - 1)*pageSize and r <= pageNo*pageSize
*/



--===========集合运算（了解）
--1.查询工资大于1200并且job是SALESMAN(intersect)
select * from emp where sal > 1200
intersect
select * from emp where job = 'SALESMAN'

--2.查询工资大于1200或者job是SALESMAN(union)
select * from emp where sal > 1200 
union
select * from emp where job = 'SALESMAN' 


--3.求工资大约1200和job是SALESMAN的差集(minus)
select * from emp where sal > 1200 
minus
select * from emp where job = 'SALESMAN'


--==========================exists / not exists
/*
select ... where exists(查询语句)
exists：当查询结果不为null，返回true
  当查询结果为null，返回false

*/
--1.查询出有员工的部门

select * from dept where exists(select * from emp where dept.deptno = emp.deptno)

select * from dept where not exists(select * from emp where dept.deptno = emp.deptno)

select * from emp where 1=1;
select * from emp where exists(select * from dept where deptno = 199);

查询到一条数据插入另一个表中，前提是两张表字段要一样

select count(*) from user_tab_columns where table_name=upper('表名')  

报错

ORA-00979:不是GROUP BY表达式

group by分组的条件没有包含所有的查询字段（不包括函数），例如

//错误语句
select dname,emp.deptno,avg(sal) from dept,emp where dept.deptno = emp.deptno group by emp.deptno
//这里查询的是dname和deptno以及avg(sal)，group后的分组条件就得包含dname和deptno，函数可以除外，以下为正确语句
select dname,emp.deptno,avg(sal) from dept,emp where dept.deptno = emp.deptno group by emp.deptno,dname

ORA-00947: 没有足够的值

表A有三列，但是只插入了两列,如果只想插入两列，需要指定字段名

insert into A values('zhangsan',20)
//正确如下
insert into A(name,age) values('zhangsan',20)

ORA-00911:无效字符

数据库直接执行正常，java执行报错，查看sql语句，发现语句最后有一个封号;,去掉就可以了

自定义函数

定义

CREATE OR REPLACE FUNCTION ascii_replace(str in VARCHAR2) return VARCHAR2
is 
str1 VARCHAR2(100); 
str2 VARCHAR2(100); 
BEGIN
	select   ascii( substr(str,0,1)  ) into str2 from dual;
  select   replace(str,chr(str2),'') into str1 from dual;
	return str1;
END ascii_replace;

调用

和普通函数一样，上面的函数定义了几个参数，就传几个就行，

ascii_replace：刚定义好的函数名
'	a52bfb738d2ca2532a62c1e20c440629	'：要传的参数
SELECT ascii_replace('	a52bfb738d2ca2532a62c1e20c440629	') from dual