笔记

select * from emp;
--concat(param1,param2) 拼接两个字符串 ||
select concat('0371-','8678565') as telephone from dual;
select concat(ename,empno) from emp;
select ename||empno from emp;
--initcap 首字母大写
select initcap(ename),ename from emp;
-- upper(全大写) lower(全小写) 
select upper(ename),lower(ename),ename from emp;
select instr('410106199012132018','19901213',1,1) instring from dual;
--instr(param1,param2,param3,param4) 查找字符串 1：被查找的字符串 2,要查找的字符 3，起始位置 4第几次出现
select empno,ename,hiredate, instr(to_char(hiredate,'yyyy-mm-dd'),'1981',1,1) from emp
--lpad(param1,param2,param3)字符串左侧粘贴 1，原字符串 2,补充后达到的位数 3,补充字符
--rpad(param1,param2,param3) 字符串右侧粘贴
select lpad(rpad(empno,8,'#'),10,'*') from emp;


--ltrim(param1,param2)从字符串param1左侧删除param2 rtrim(param1,param2) 字符串param1右侧删除param2
select ltrim('##100#024##','#') from dual;
select ltrim('##100024##','#'),rtrim('**200001****','*') from dual;
--param2省略时，去除空格
select ltrim('   admin   '),rtrim('   admin   ') from dual;
select ltrim('   admin   ',' '),rtrim('   admin   ',' ') from dual;
--去除左右两侧的空格
select trim('   admin   ') from dual;


--trim(leading param1 from param2) leading (头) param2从头部去除param1
--trim(trailing param2 from param2) trailing (尾) param2 从尾部去除param1
--trim(both param2 from param2) both (头,尾)   param2从头,尾(两侧)去除param1
select trim(leading '#' from '###admin##') from dual;
select trim(trailing '#' from '###admin##') from dual;
select trim(both '#' from '###adm#in##') from dual;


--substr(param1,number1,number2)截取字符串 param1,目标字符串 number1起始位，number2长度
select substr('13012345678',3,8) from  dual;


--replace(param1,param2,param3)替换字符串 param1目标字符串,param2被替换的字符串，param3替换字符串
select replace('云和培训java培训','yunhe','教育') from dual;
select ename,replace(ename,'A','a') from emp 


select mod(7,3) from dual;


select * from emp;
-- mod(number1，number2) number1对number2 取余数
select comm,mod(comm,1000) from emp;
-- round(number1,number2) nubmer2为正数，小数点向后四舍五入number2，number2为负数，小数点向前四舍五入number2
select sal,round(sal,-2) from emp;
-- trunc(number1,number2) 截取，nubmer2为正数，小数点向后截取number2，number2为负数，小数点向前截取number2
select sal,trunc(sal,-2) from emp;


--sign(number) 判断number符号,大于0返回1，等于0返回0，小于0返回-1
select sign(10) from dual;
select sign(-10) from dual;
select sign(0) from dual;


--mysql分页 limit index,length oracle分页 rownum
--select 语句顺序
select * from table where condition1 and condition 
group by column having  order by 
-- having 字句对条件限制关键字 和where 区别: 
where 用于group by 之前 对普通表的列条件限制
having 用于group by 之后，对聚合函数(max,min,count,avg,sum)的条件限制


select * from emp;


--列出至少有一个员工的所有部门。
--having字句的聚合函数的限制条件，不能使用select聚合函数的别名
select emp.deptno,dept.dname,count(empno) from emp,dept where emp.deptno=dept.deptno and emp.deptno is not null group by emp.deptno,dept.dname having count(empno)>=1 
select dept.deptno,dept.dname,count(empno) from emp,dept where emp.deptno=dept.deptno and dept.deptno is not null group by dept.deptno,dept.dname 
--emp表中出每个部门的部门代码、薪水之和、平均薪水
select * from dept,(select deptno,sum(sal),avg(sal) from emp group by deptno) e where dept.deptno=e.deptno 
select emp.deptno,dept.dname,sum(sal),avg(sal) from emp,dept where emp.deptno=dept.deptno group by emp.deptno,dept.dname


--查询emp表中出部门平均薪水小于等于2000的部门的部门代码、平均薪水，并按平均薪水从大到小排序。
select deptno,avg(sal) from emp where deptno is not null group by deptno having avg(sal)<=2000 order by avg(sal) desc;


--询emp表中薪水最少，和薪水最大员工的姓名和薪水，并按薪水从大到小排序。union(连接)
select ename,sal from emp where sal=(select max(sal) from emp)
union
select ename,sal from emp where sal=(select min(sal) from emp)
select ename,sal from emp where sal=(select max(sal) from emp) or sal=(select min(sal) from emp)
select ename,sal from emp where sal in((select max(sal) from emp),(select min(sal) from emp))


--列出所有员工的姓名及其直接上级领导的姓名e1(员工表) e2(经理表)
select e1.empno,e1.ename,e1.mgr,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;


--列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称。
select e1.hiredate,e2.hiredate,e1.empno,e1.ename,dept.dname,e1.mgr,e2.ename from emp e1,emp e2,dept where e1.mgr=e2.empno and e1.deptno=dept.deptno and e1.hiredate<e2.hiredate;


--列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门. 
select * from emp,dept where emp.deptno(+)=dept.deptno;
select * from emp right join dept on emp.deptno=dept.deptno;


--列出所有CLERK(办事员)的姓名，及其部门名称，部门人数。
select * from emp where job='CLERK';
select * from (select ename,deptno,job from emp where job='CLERK') e1,(select deptno,count(*) from emp group by deptno) e2 where e1.deptno=e2.deptno


--列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数。


select job,count(job) from emp group by job having min(sal)>1500;
select job,count(empno) from emp group by job having min(sal)>1500;


--列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的等级工资。
select avg(sal) from emp;
select * from salgrade;
select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,emp.mgr,e2.ename,salgrade.grade from emp,dept,emp e2,salgrade where emp.deptno=dept.deptno and emp.mgr=e2.empno and emp.sal between salgrade.losal and salgrade.hisal and emp.sal>(select avg(sal) from emp);


--列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno,count(empno),avg(sal),round(avg(months_between(sysdate,hiredate))/12,1) from emp group by deptno 


--列出各种工作的最低工资及从事此工作的雇员姓名。


select * from emp where (job,sal) in(select job,min(sal) from emp group by job)


select * from emp;


--列出各个部门的MANAGER(经理)的最低薪金。
select * from emp where job='MANAGER' 


-- 列出员工的年工资，按年薪从低到高排序


select ename,(nvl(comm,0)+nvl(sal,0))*12 as year_sal from emp order by year_sal 


--给任职日期超过10年的人加薪10%。
create table emp2 as select * from emp;
update emp2 set sal=sal*1.1 where months_between(sysdate,hiredate)>120


--找出各月倒数第三天受雇的所有员工  
select * from emp where hiredate=last_day(hiredate)-2


--显示员工姓名正好为5个字符的员工
select * from emp where ename like '_____'
select * from emp where length(ename)=5
--显示所有员工姓名的前三个字符。
select ename,substr(ename,1,3) from emp


--显示所有员工的姓名，用 a 替换A 
select ename,replace(ename,'A','a') from emp;


--显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同，按年份排序
select ename,hiredate,to_char(hiredate,'yyyy') as yea,to_char(hiredate,'mm') mth from emp order by mth,yea 


--显示所有员工的日薪金，忽略余数。每个月的天数都以30天计
select ename,sal,trunc(sal/30,0) from emp;