Oracle学习记录

简单时间函数

获取系统时间,日期可以进行加减的运算

select sysdate from dual;
select systimestamp from dual;

-- +1 代表加 1 天
select sysdate+1 from dual;
-- +1 之后和sysdate + 1 的结果一样
select systimestamp+1 from dual;

获取下一年的第一天

select round(sysdate,'year') from dual;

-- 返回从当前日期开始往后数的第一个星期一的时间

select next_day(sysdate,'星期一') from dual;

-- 返回当前单元的第一天
select trunc(sysdate,'year') from dual;

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

查表练习

-- 查询出每个员工的姓名，以及到今天他共工作了几天
select ename,MONTHS_BETWEEN(sysdate,hiredate) as "工作时长" from emp

-- 查询出当月最后一天入职的员工姓名和入职日期 ?? 函数不生效
select ename,hiredate from emp where hiredate = last_day(sysdate)

-- 查询出在当月第一天入职的员工姓名和员工日期
select ename,hiredate from emp where trunc(hiredate,'MONTH') = hiredate;

-- 修改当前会话的时区
alter session set time_zone = '+09:00';
select dbtimezone from dual;
select current_date from dual;

字符串和时间的转换

-- 字符串转时间
select to_date('1999/01/08','yyyy-MM-dd') from dual;
-- to_char 数值转字符串
select to_char(153,'C000.000') from dual
-- to_number 字符串转数字
select to_number('￥153','L000') from dual
-- to_char 日期转字符串
select to_char(sysdate,'yyyy!mm!dd') from dual
-- decode : 如果是salesman就是销售员，是Manager就返回经理，否则返回其他
select ename,decode(job,'SALESMAN','销售','MANAGER','经理','其他岗位') from emp where job is not null

重点： 数字转换时间

select to_char(153,'9999.999') from dual
-- 0153.000
select to_char(153,'0000.000') from dual
select to_char(0.596,'999,999,999,999,999.999') from dual
select to_char(0.596,'FM000,000,000,000,000.000') from dual

正则，聚合函数

select regexp_like('sahdfjkhdghjkhs','jk',c) from dual
-- 只能用在where后面
select * from emp where regexp_like(ename,'A','c');
-- regexp_like
-- regexp_instr
select regexp_instr('Joe_Smith,10045,ananan','an',1,2) from dual;
-- abcdefg字符串的替换(ab -> ll)
select regexp_replace('abcdefg','ab','ll') from dual;
-- regexp_substr() : 从字符串中截取出ab(正则）
select regexp_substr('abcdefg','ab') from dual;
-- regexp_count
select regexp_count('abcdefg','cd') from 

聚合函数

select * from emp for update
-- 用主键比*效率高一些
select count(comm) from emp;
-- sum 求算数加和
select sum(sal) from emp
select sum(sal) from emp where sal is not null;
-- 求平均值 avg(空工资不计入除数个数中)
select avg(sal) from emp where empno < 14
-- min
select min(sal) from emp for update

关键字的执行顺序

select ，from， where， group by ，having 执行顺序：

1. 查询到一条数据，看看是否满足where后面的条件，如果马努就留下
2. 将剩余数据进行分组
3. 进行聚合函数的运算
4. 用having对group by的结果进行筛选

分组统计

-- select from where group by having
select * from emp;
select job,avg(sal) from emp group by job;
select job,count(empno) from emp group by job;
select job,avg(sal) from emp group by job having avg(sal) > 2000

多表查询的连接

-- 内连接
select e.*,d.* from emp e inner join dept d on e.deptno = d.deptno
-- 左外连接  //关联不上拿空补齐
select e.*,d.* from emp e left outer join dept d on e.deptno = d.deptno
-- 右外连接
select e.*,d.* from emp e right outer join dept d on e.deptno = d.deptno
-- 全外连接 把左边没有的和右边没有的都加上去（左空+右空)
select e.*,d.* from emp e full outer join dept d on e.deptno = d.deptno
-- 交叉连接，笛卡尔积
select e.*,d.* from emp e cross join dept d
-- 查询出员工姓名以及所在部门名称
select e.ename,d.dname from emp e left outer join dept d on e.deptno = d.deptno;
-- 查询出每个员工的姓名，月薪以及月薪等级
select e.ename,e.job,round(nvl(
-- 查询出每个员工的职位

25道SQL练习题

-- SQL练习题

1.选择部门 20 中的所有员工。
select e.* from emp e where deptno = 20

2.列出所有职位为业务员(CLERK)的编号、部门编号及姓名。
select empno,ename,deptno from emp where job = 'CLERK'

3.找出奖金高于月薪的员工的所有信息。
select * from emp where nvl(comm,0) > nvl(sal,0)

4.找出奖金高于一半月薪的员工的的所有信息。
select * from emp where nvl(comm,0) > nvl2(sal,sal/2,0)

5.找出部门编号10中所有经理(MANAGER)和部门编号20中所有业务员(CLERK)的详细资料。
select * from emp where deptno = 10 and job = 'MANAGER' or  deptno = 20 and job = 'CLERK'

6.找出部门编号30中，既不是经理(MANAGER)又不是业务员(CLERK),但其薪金大于或等于1500的所有员工的详细资料。
select * from emp where job <> 'MANAGER' and job <> 'CLERK' and nvl(sal,0) >= 1500

7.找出有奖金的员工的不同种职位名。
select distinct(job) from emp where comm is not null

8.找出没有奖金或奖金低于800的员工。
select * from emp where comm < 800 or comm is not null

9.找出各月倒数第3天受雇的所有员工。
--select last_day(sysdate)-3 from dual
select * from emp where last_day(hiredate)-3 = hiredate

10.找出工龄超过30年的员工。
--select sysdate-hiredate from emp
select * from emp where months_between(sysdate,hiredate) > 30*12

11.以首字母大写的方式显示所有员工的姓名。
select initcap(lower(ename)) from emp

12.显示正好为6个字符的员工的姓名和月薪。
select ename,nvl(sal,0) from emp where length(ename) = 6

13.显示不带有“M”的员工的姓名。
select ename from emp where instr(ename,'M') = 0;

14.显示所有员工姓名的前三个字符。
select substr(ename,1,3) from emp

15.显示所有员工的姓名，用 “m” 替换所有 “M”
select replace(ename,'m','M') from emp

16.显示年薪水（包括奖金）超过30000的员工的姓名和受雇日期。
select ename,hiredate from emp where 3000 > nvl(sal,0)*12

17.显示员工的详细资料，按姓名排序。
select * from emp order by ename asc

18.显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面。
select ename,hiredate from emp order by sysdate-hiredate asc

19.显示所有员工的姓名、职位和月薪，按职位升序排序，若职位相同则按月薪降序排序。
select ename,job,nvl(sal,0)*12 from emp order by job,sal;

20.显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面。
select ename,to_char(hiredate,'MM') m,to_char(hiredate,'dd') d from emp order by m,d

-- select to_char(sysdate,'MM') as nowMonth from dual;

21.显示所有员工的姓名和平均日薪(一个月以30天算)，忽略余数
select ename,nvl2(sal,sal/30,0) from emp

22.找出每年12月受聘的员工的资料。
select * from emp where to_char(hiredate,'MM') = 12

23.显示每位员工的姓名及其加入公司的天数。 
select ename,ceil(sysdate - hiredate) from emp

24.显示姓名包含"M"但不包含“S”的所有员工的资料。
select * from emp where instr(ename,'M') != 0 and instr(ename,'S') = 0;

25.以年月日的方式显示所有员工的工作年限（大概）。-- 整不出来