SQL基础--常见SQL查询

SQL基础–常见SQL查询

---

1 . TOP-N查询

Oracle没有limit语句，自由对排序好的表取ROWNUM在前N个的记录

--查询薪资前3的员工
SELECT s.*
FROM
(
  SELECT e.LAST_NAME, e.SALARY
   FROM EMPLOYEES e
  ORDER BY SALARY DESC
) s
WHERE ROWNUM <= 3;

同样的分页查询可以通过下面的语句来完成

-- 分页查询
SELECT s.*
FROM
(
  SELECT e.LAST_NAME, e.SALARY,ROWNUM CNT
   FROM EMPLOYEES e
  ORDER BY SALARY DESC
) s
WHERE CNT >= 2 AND CNT <= 3;

2 . EXIST 和 IN 查询

查询有历史工作记录的员工

--EXIST查询
SELECT d.DEPARTMENT_NAME
FROM DEPARTMENTS d
WHERE EXISTS (
      SELECT 'X' FROM EMPLOYEES e WHERE d.DEPARTMENT_ID = e.DEPARTMENT_ID

);

SELECT d.DEPARTMENT_NAME
FROM DEPARTMENTS d
WHERE EXISTS (
      SELECT 'X' FROM EMPLOYEES e WHERE d.DEPARTMENT_ID = e.DEPARTMENT_ID AND ROWNUM <= 1

);


SELECT d.DEPARTMENT_NAME
FROM DEPARTMENTS d
WHERE d.DEPARTMENT_ID IN  (
      SELECT e.DEPARTMENT_ID FROM EMPLOYEES e
);

3 . NOT IN（A,B，NULL）

NOT IN 中包含NULL时，条件衡为FALSE

(原因可能是NULL和任何值的运算结果都为NULL，而NULL可看成是FALSE)

所以大致有这样的使用优先级(从高到低)：EXIST—>IN—->OR

SELECT d.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM DEPARTMENTS d
WHERE d.DEPARTMENT_ID NOT IN  (80,90,NULL);

---->

SELECT d.DEPARTMENT_ID,d.DEPARTMENT_NAME
FROM DEPARTMENTS d
WHERE NOT( d.DEPARTMENT_ID = 80 OR d.DEPARTMENT_ID = 90 OR d.DEPARTMENT_ID = NULL);

--> d.DEPARTMENT_ID = 80    FALSE
--> d.DEPARTMENT_ID = 90    FALSE
--> d.DEPARTMENT_ID = NULL  NULL
-->FALSE  or  FALSE  or NULL = NULL
-->not NULL = NULL

4 . 取某个字段最小的记录

查询平均工资最高的部门中的最低薪水

--1
SELECT DEPARTMENT_ID, MIN(SALARY)
  FROM EMPLOYEES
 GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) >= ALL (SELECT AVG(SALARY)
                             FROM EMPLOYEES
                            GROUP BY DEPARTMENT_ID);

--2
SELECT DEPARTMENT_ID, MIN(SALARY)
  FROM EMPLOYEES
 GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) = (SELECT MAX(AVG(SALARY))
                             FROM EMPLOYEES
                            GROUP BY DEPARTMENT_ID);

5 . HAVING子句

查询总人数大于3的部门

--HAVING子句
SELECT DEPARTMENTS.DEPARTMENT_ID, DEPARTMENT_NAME, COUNT(*)
FROM DEPARTMENTS, EMPLOYEES
WHERE DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
GROUP BY DEPARTMENTS.DEPARTMENT_ID,DEPARTMENT_NAME
HAVING COUNT(*) > 3;

6 . 数字与字符串比较

Oracle在比较时，会将字符串自动转换成数字，建议明确指出类型转换，否则在有的情况下会报错（原始数据）

SELECT * FROM EMPLOYEES e WHERE e.FIRST_NAME = 111;

--->>

SELECT * FROM EMPLOYEES e WHERE to_number(e.FIRST_NAME) = 111;

7 .

8 .