Oracle学习笔记

一:基本查询

​ 1.查询1981-1-1到1981-12-31号入职的雇员

select * from emp where to_date('1981-1-1','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');

​ 2.查询员工编号是9527,1997,1314的员工 查询雇员姓名是'JAMES','SMITH','ALLEN'的雇员的信息

​ 2.1 or方式

select * from emp where empno = 9527 or empno = 1997 or empno = 1314;

select * from emp where ename = 'JAMES' or ename = 'SMITH' or ename = 'ALLEN';

​ 2.2 in方式

select * from emp where empno in (9527,1997,1314);  

select * from emp where ename in('JAMES','SMITH','ALLEN');        

​ 3.查询所有雇员姓名中第二个字符有'M'的雇员

select * from emp where ename like '_M%';

​ 查询名字中带有'M'的雇员

select * from emp where ename like '%M%';

4.查询雇员编号不是9527的雇员信息

-- !=方式
select * from emp where empno != 9527;
-- <>方式
select * from emp where empno <> 9527;

5.查询雇员的工资进行降序排列(默认的是升序)

select ename,sal from emp oder by sal desc;

6.查询雇员的奖金并做降序排列(关于 nulls first/nulls last)

SELECT  ename, comm FROM emp ORDER BY comm DESC nulls last;

二:字符函数

-- 1.把'smith'转成大写   关键字:upper
    SELECT UPPER('smith') from daul;
-- 2.把ename转成小写  关键字lower
    SELECT LOWER(ename) from emp;
-- 3.将'smith'首字母大写   关键字:initcap
    SELECT initcap(ename) FROM emp;
-- 4.将'helloworld'截取字符串成'hello'  关键字:substr
    SELECT SUBSTR('helloword',0,5) from daul;     
-- 5.获取'hello'字符串的长度  关键字:length
    SELECT LENGTH('hello') from daul;
-- 6.将'hello'中的'l'用'x'替换  关键字 :repalce
    SELECT REPLACE('hello','l','x') from daul;

三:数值函数

-- 1.将15.66进行四舍五入(从-2到2)   关键字:round
        SELECT ROUND(15.66,-2) FROM daul;  -- 0
        SELECT ROUND(15.66,-1) FROM daul;  -- 20
        SELECT ROUND(15.66,0) FROM daul;  -- 16
        SELECT ROUND(15.66,1) FROM daul;  -- 15.7
        SELECT ROUND(15.66,2) FROM daul;  -- 15.66 

 -- 2.将15.66进行截断(从-2到2)   关键字:tranc
        SELECT trunc(15.66,-2) FROM daul; -- 0   在mysql中相当于truncate
        SELECT trunc(15.66,-1) FROM daul; -- 10
        SELECT trunc(15.66,0) FROM daul; -- 15
        SELECT trunc(15.66,1) FROM daul; -- 15.6
        SELECT trunc(15.66,2) FROM daul; -- 15.66
 -- 3.对15/4取余数  关键字 mod
        SELECT MOD(15,4) FROM daul;

四:日期函数

-- 1.查询系统时间  sysdate
    SELECT SYSDATE from daul;
-- 2.查询雇员进入公司的周数
    SELECT ename , (SYSDATE-hiredate)/7 FROM emp;
-- 3.查询员工进入公司的月数 month_between
    SELECT ename,month_between(SYSDATE,hiredate) from emp;

五:转换函数

-- 将系统日期显示为yyyy-mm-dd hh:mi:ss(去掉补零和24小时显示时间) 关键字  to_char
    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from daul;
-- 显示成年月日
    select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'MM')||'月'||to_char(sysdate,'dd')||'日';

-- 将字符串'1981-1-1'转换成日期类型  关键字:to_date
    select to_date('1981-1-1','yyyy-MM-dd') from daul;

六:通用函数

-- 1.空值的处理函数
     SELECT nvl(comm,0) from emp;
-- 2.nvl2(判断值,空返回值,非空返回值)
     SELECT mvl('xxxx','1','0') from daul;

/*空值函数是与空值null相关的一些函数，完成对空值null的一些操作，从而对空值进行处理。主要包括：nvl，nvl2，nullif，coalesce函数。
a).nvl(expr1,expr2)：如果expr1是空值，则返回expr2。
b).nvl2(expr1,expr2,expr3)：如果expr1不是空值，返回expr2，否则返回expr3。
c).nullif(expr1,expr2)：比较两个表达式，如果相等，返回null，否则，返回第一个表达式。
d).coalesce (表达式1, 表达式2, ... 表达式n)函数是对NVL函数的扩展。COALESCE 函数的功能是返回第一个不为空的参数，参数个数不受限制。*/
例1：select ename,sal+nvl(comm,0) totalsal from emp;
例2：select nvl2(null,'8','fd') from dual;
例3：select nullif(28,28),nullif(3,2) from dual;
例4：select coalesce(null,'5','f') from dual;

七:条件表达式

-- 查询员工的job内容并转成中文显示
   -- decode 方式
   select ename,decode(job,'CLERK','柜员','SALESMAN','销售','MANAGER','管理','其他') from emp;
   -- case when the end方式
   select ename,case job when 'CLERK' then '柜员'
                         when 'SALESMAN' then '销售'
                         when 'MANAGER' then '管理'
                         else '其他'
                         end from emp;

八:多行函数(聚合函数)

-- 1.查询所有员工的记录数  关键字:conut()
SELECT COUNT(*) from emp;
-- 2.查询佣金的总数 -- (如何查询某个字段的总数量)
SELECT SUM(comm) from emp;
-- 3.查询最低工资  关键字  min
SELECT min(sal) FROM emp;
-- 4.查询最高工资  关键字 max
SELECT max(sal) FROM emp;
-- 5.查询平均工资 关键字  max
select AVG(sal) from emp;
-- 6.查询20号部门的员工工资总和
SELECT SUM(sal) FROM emp WHERE deptno = 20;

​

九:分组函数

-- 1.查询部分编号及人数  分组查询关键字 GROUP BY
SELECT deptno,COUNT(*) from emp GROUP BY deptno;
-- 2.查询每个部门的编号及平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
-- 3.查询部门名称,部门编号,平均工资
SELECT dname,emp.deptno,avg(sal) FROM emp,dept WHERE emp.deptno = dept.deptno GROUP BY emp.deptno,dname;
-- 4.查询部门人数超过5的部门
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;

Day02

十:连接查询

-- 1.查询员工编号,姓名,领导编号,姓名,包括没有领导的
SELECT e1.empno,e1.ename,e2.empno,e2.ename FROM emp e1 LEFT JOIN    emp e2 ON e1.mgr = e2.empno;
-- oracle的(+)方式
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;

十一:子查询

-- 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 e1.deptno,dept.dname,e1.minsal,emp.ename FROM (SELECT deptno,MIN(sal) minsal FROM emp GROUP BY deptno) e1 ,emp , dept WHERE e1.minsal=emp.sal and e1.deptno = emp.deptno and e1.deptno = dept.deptno;
-- 练习一.找到员工表中工资最高的前三名
SELECT rownum,e.* FROM (SELECT emp.* from emp ORDER BY sal DESC) e WHERE rownum <= 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.查询员工表,将员工工资进行降序查询,并进行分页取出第一页,一页三条记录
SELECT * FROM (SELECT rownum r,e.* from (SELECT * FROM emp ORDER BY sal DESC)e) e1 WHERE r>0 and r<3;

十二:集合运算交并差(了解)

-- 1.查询工资大于1300并且job是SALESMAN(intersect)
SELECT * from emp WHERE sal > 1300
intersect
SELECT * FROM emp WHERE job ='SALESMAN';
-- 2.查询工资大于1300或者job是'SALESMAN'(union)
SELECT * from emp WHERE sal > 1300
UNION
SELECT * FROM emp WHERE job ='SALESMAN';
-- 3.查询工资大于1200和job是'SALESMAN'的差集(minus)
SELECT * from emp WHERE sal > 1300
minus
SELECT * FROM emp WHERE job ='SALESMAN';

十三:exists / not exists关键字

/*
SELECT...WHERE exists(查询语句)
exist:当查询结果不为null的时候,返回true
当查询结果为null的时候,返回false
*/
-- 1.查询出有员工的部门
SELECT * from dept WHERE EXISTS(SELECT * FROM emp WHERE dept.deptno = emp.deptno);
-- 2.查询出没有员工的部门
SELECT * from dept WHERE not EXISTS(SELECT * FROM emp WHERE dept.deptno = emp.deptno);