sql查询操作

--查询编号、姓名、职位、工资
SELECT EMPNO,ENAME,JOB,SAL FROM EMP;
--查询职位信息，去重
SELECT DISTINCT JOB FROM EMP;
SELECT DISTINCT JOB,SAL FROM EMP;
--查询编号，姓名，年薪（计算）
SELECT EMPNO,ENAME,SAL * 12 FROM EMP;
--
SELECT EMPNO,ENAME,SAL * 0.6 FROM EMP;
--字段重命名
SELECT EMPNO 雇员编号, ENAME AS 雇员姓名, SAL * 0.6 薪水 FROM EMP;
--字符串拼接                                                                      重命名
SELECT
       ENAME || '(编号：' || EMPNO || '，基本工资：' || SAL || '，职位：' || JOB 员工信息
FROM EMP;

–筛选查询语句：

SELECT *FROM EMP WHERE SAL < 1000;
--工资1000-1500
SELECT *FROM EMP WHERE SAL >= 1000 AND SAL <=1500;
SELECT *FROM EMP WHERE SAL BETWEEN 1000 AND 1500; 
SELECT *FROM EMP WHERE HIRDATE BETWEEN '1-1月 -81' AND '31-12月 -81';--日期也可以用
--查询XXX的完整信息(XXX是值，区分大小写)
SELECT *FROM EMP WHERE ENAME = 'XXX';
SELECT *FROM EMP WHERE HIRDATE = '17-4月 -80'; --默认格式，后期借助函数解决
-- 工资在1500-3000，且职位为 MANAGER
SELECT *FROM WHERE SAL BETWEEN 1500 AND 3000 AND JOB = 'MANAGER';
-- 工资在1500-3000，且职位不为 MANAGER
SELECT *FROM WHERE SAL BETWEEN 1500 AND 3000 AND JOB != 'MANAGER';
SELECT *FROM WHERE SAL BETWEEN 1500 AND 3000 AND JOB <> 'MANAGER';
--工资在1500-3000，或者职位是 CLERK
SELECT *FROM WHERE SAL BETWEEN 1500 AND 3000 OR JOB = 'CLERK';
SELECT *FROM WHERE (SAL BETWEEN 1500 AND 3000) OR JOB = 'CLERK'; --()开源改变优先级
--工资不在1500-3000，且职位不是是 CLERK
SELECT *FROM WHERE (SAL < 1500 AND SAL > 3000) AND JOB != 'CLERK';
SELECT *FROM WHERE NOT (SAL BETWEEN 1500 AND 3000 AND JOB = 'CLERK');
--查询1981年雇佣的全部雇员信息
SELECT *FROM EMP WHERE HIRDATE BETWEEN '1-1月 -81' AND '31-12月 -81';
--查询编号为7369,7566,7788的雇员信息 (IN)
SELECT *FROM EMP WHERE EMPNO IN(7369,7566,7788);
SELECT *FROM EMP WHERE EMPNO NOT IN(7369,7566,7788); --不在
--哪些雇员有奖金
SELECT *FROM EMP WHERE COMM IS NOT NULL;
SELECT *FROM EMP WHERE COMM IS NULL; --没有
--没有奖金，但工资大于2000
SELECT *FROM EMP WHERE (COMM IS NULL) AND SAL >2000;

–模糊查询（LIKE）：

--查询姓名是 S 开头的雇员
SELECT *FROM EMP WHERE ENAME LIKE 'S%';
--查询姓名是第二个字母是L的雇员
SELECT *FROM EMP WHERE ENAME LIKE '_L%';
--查询姓名包含 S 的雇员
SELECT *FROM EMP WHERE ENAME LIKE '%S%';
--查询姓名长度为 5 的雇员
SELECT *FROM EMP WHERE ENAME LIKE '_____';--5个 _
--日期
SELECT *FROM EMP WHERE HIRDATE LIKE '%81%';

–排序（ORDER BY：）

--工资从低到高 （默认） 
SELECT *FROM EMP ORDER BY SAL;
--工资从高到低
SELECT *FROM EMP ORDER BY SAL DESC;
SELECT *FROM EMP ORDER BY SAL DESC,HIRDATE;--第二条件

*内连接 INNER JOIN(默认的连接方式，INNER可以省略)====>必须指定连接条件
如果连接不上，记录就会丢失：*

SELECT E.EMPNO,E.ENAME,JOB,HIREDATE,E.SAL,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

外连接：
左外连接：如果多表发生连接不上的情况，左表记录保留，右表置为 null(OUTER) LEFT JOIN
右外连接：如果多表发生连接不上的情况，左表记录保留，右表置为null(OUTER) RIGHT JOIN
全外连接：(OUTER) FULL JOIN

--查询所有员工所属的部门信息
SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
--查询所有部门的员工信息
SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
--查询所有部门和员工的信息
SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E FULL JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

自然连接：不需要指定连接条件，Oracle内部根据多表中同名字段作为连接条件
不推荐使用
NATURAL JOIN
NATURAL LEFT JOIN
NATURAL RIGHT JOIN

SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DNAME,D.LOC
FROM EMP E NATURAL JOIN DEPT D;

–函数

--大小写转换
SELECT UPPER('hello'),LOWER('WORLD') FROM DUAL;
SELECT *FROM EMP WHERE ENAME = UPPER('smith');
--字符串连接
SELECT CONCAT('HELLO:',ENAME) FROM EMP;
--显示姓名长度正好是5的雇员信息
SELECT *FROM EMP WHERE LENGTH(ENAME) = 5;
--字符串替换
SELECT REPLACE(ENAME,'A','*') FROM EMP;
--显示每个雇员姓名前三个字母
SELECT SUBSTR(ENAME,0,3) FROM EMP;
--四舍五入
SELECT ROUND(789.5000) FROM DUAL;

SELECT TRUNC(345.23423),TRUNC(123.34532,2),TRUNC(234.1234,-2) FROM DUAL;
--现在的时间
SELECT SYSDATE FROM DUAL;
--20天后
SELECT SYSDATE +20 FROM DUAL;
--20天前
SELECT SYSDATE -20 FROM DUAL;

SELECT SYSDATE -TO_DATE('2018-05-12','yyyy-mm-dd') FROM DUAL;

SELECT EMPNO,ENAME,SYSDATE-HIRDATE,MOUNTHS_BETWEEN(SYSDATE,HIRDATE) FROM EMP;
SELECT ADD_MOUNTHHS(SYSDATE,2),NEXT_DAY(SYSDATE,'星期二'),LAST_DAY(SYSDATE),LAST_DAY(ADD_MOUNTH) FROM DUAL;
--字符串转换
SELECT SYSDATE,TO_CAHR(SYSDATE,'yyyy-mm-dd') FROM DUAL;
--1981年的雇员
SELECT *FROM EMP WHERE TO_CHAR(HIREDATE,'yyyy') = '1981';

–聚合函数

SELECT COUNT(*),COUNT(1),COUNT(EMPNO),COUNT(COMM) FROM EMP;
--求和
SELECT SUM(SAL) FROM EMP;
--求平均
SELECT SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL),SUM(SAL)/COUNT(*) FROM EMP;

–分组

--求出每个部门的人数及平均工资
SELECT DEPTNO, COUNT(*),AVG(SAL) FROM EMP GROUP BY DEPTNO;
--每种工作的平均工资
SELECT JOB,AVG(SAL) FROM EMP GROUP BY JOB;
--统计每个部门的人数、平均工资，部门要求显示部门名称
SELECT AVG(SAL),COUNT(EMPNO),DNAME 
FROM EMP E RIGHT JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME;

--查询工资大于平均工资的雇员信息
SELECT *FROM DROM EMP WHERE AVG(SAL)<SAL; --错误，WHERE里面不能用聚会函数

SELECT DNAME,COUNT(EMPNO),AVG(SAL) 
FROM EMP E RIGHT JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
GROUP BY DNAME
HAVING AVG(SAL) > 2000;

SELECT JOB,SUM(SAL) FROM EMP 
WHERE JOB = !'SALESMAN';
GROUP BY JOB
HAVING SUM(SAL) > 5000
ORDER BY SUM(SAL) ASC;

SELECT NVL(NULL,'HELLO'),NVL(20,0) FROM DUAL;

–子查询

--查询工资比7566高的员工信息
SELECT * FROM EMP 
WHERE SAL > (SELECT SAL FROM EMP WHERE EMPNO = 7566);

--统计每个部门的完整信息，部门编号、名称、位置、人数、平均工资
SELECT D.DEPTNO,D.DNAME,D.LOC,T.COUNT,T.SAL FROM DEPT D
JOIN (SELECT DEPTNO,COUNT(EMPNO) COUNT,AVG(SAL) SAL FROM EMP--临时表
GROUP BY DEPTNO) T
ON D.DEPTNO = T.DEPTNO;
--查询每个工作的最低工资的员工信息 (IN)
SELECT * FROM EMP WHERE SAL IN
(SELECT MIN(SAL) FROM EMP GROUP BY JOB);
--ANY
SELECT * FROM EMP WHERE SAL = ANY
(SELECT MIN(SAL) FROM EMP GROUP BY JOB);
--
SELECT * FROM EMP WHERE SAL > ANY
(SELECT MIN(SAL) FROM EMP GROUP BY JOB);
--
SELECT * FROM EMP WHERE SAL < ANY
(SELECT MIN(SAL) FROM EMP GROUP BY JOB);
--查询工作和工资与7654相同的员工信息
SELECT * FROM EMP 
WHERE (JOB,SAL) = 
(SELECT JOB,SAL FROM EMP
WHERE EMPNO = 7654);

–分页

--ORACLE 伪例
SELECT ROWID,ROWNUM,E.* FROM EMP E;
--查询第一页数据(1-5)
SELECT ROWNUM,EMP.* FROM EMP WHERE ROWNUM <= 5;
--查询第二页数据(6-10)
SELECT ROWNUM,EMP.* FROM EMP 
WHERE ROWNUM > 5 AND ROWNUM <= 10;--错误不能写 ROWNUM>5
--
SELECT ROWNUM,T.* FROM
(SELECT ROWNUM NUM,EMP.* FROM EMP) T
WHERE T.NUM >5 AND t.NUM <=10;

--分页+排序
SELECT * FROM EMP ORDER BY SAL DESC;
--先分页再查询，不然结果不正确
SELECT T.* FROM
(SELECT ROWNUM NUM,T1.* FROM 
        (SELECT EMP.* FROM EMP ORDER BY SAL DESC) T1
)T
WHERE T.NUM >5 AND T.NUM <=10;