数据库基础sql语句总结(以Oracle为主介绍,并与mysql，sql server做对比)二

前一篇总结了大部分对表的增删改查操作，这一篇主要总结了比较难，但是学习工作中会经常性用到的多表连接查询。

在没有where条件时，多表连接即相当于对多张表做笛卡尔积。

—–等值连接(内连接)：只能查询出满足连接条件的数据。
—-查询员工的last_name,department_name
下面是通用的sql多表连接语句：

SELECT LAST_NAME,DEPARTMENT_NAME
FROM EMPLOYEES,DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID;

—–多表连接时，建议在每个列的前面都添加表名的前缀，可以提高查询效率。
—-查询员工的last_name,department_id,department_name

SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;

—通常情况下，连接条件的个数是表的个数减一。
–查询员工的last_name,department_name,city

SELECT E.LAST_NAME,D.DEPARTMENT_NAME,L.CITY
FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
AND D.LOCATION_ID=L.LOCATION_ID;

—–非等值连接
–查询员工的last_name,salary,级别

SELECT E.LAST_NAME,E.SALARY,J.GRADE_LEVEL
FROM EMPLOYEES E,JOB_GRADES J
WHERE E.SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL;

—–SQL:99标准
–查询员工last_name,department_name,job_title,city

SELECT E.LAST_NAME,D.DEPARTMENT_NAME,J.JOB_TITLE,L.CITY
FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
JOIN LOCATIONS L ON L.LOCATION_ID=D.LOCATION_ID
JOIN JOBS J ON J.JOB_ID=E.JOB_ID;

—–外连接：查询出满足连接条件与不满足连接条件的数据
—-左外连接
–查询出所有员工的last_name,department_id,department_name

SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;

—-右外连接

SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;

—-全外连接

SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;

—-Oracle中特有外连接的语法:(+)
–左外连接

SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID(+);

–右外连接

SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID;

—–SQL Server中特有的外连接语法：
–左外连接

SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID*=D.DEPARTMENT_ID;

–右外连接

SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID=*D.DEPARTMENT_ID;

—–组函数(聚合函数)
—-AVG():求平均值

SELECT AVG(SALARY)
FROM EMPLOYEES;

—-SUM():求总和

SELECT SUM(SALARY)
FROM EMPLOYEES;

—-MAX()/MIN():求最大/最小值

SELECT MAX(SALARY),MIN(SALARY)
FROM EMPLOYEES;

—-COUNT()：获得满足条件的条数

SELECT COUNT(*)--获得查询结果的行数。
FROM EMPLOYEES;

SELECT COUNT(COMMISSION_PCT)--获得指定列中不为NULL的值的个数
FROM EMPLOYEES;

—-组函数与DISTINCT

SELECT COUNT(JOB_ID),COUNT(DISTINCT JOB_ID),COUNT(DISTINCT DEPARTMENT_ID)
FROM EMPLOYEES;

—–组函数与NULL:所有的组函数在计算时都会自动忽略NULL。

SELECT AVG(NVL(COMMISSION_PCT,0))
FROM EMPLOYEES;

—-GROUP BY子句：分组，统计，报表
格式：

SELECT      4
FROM        1
[WHERE]     2
[GROUP BY]  3
[ORDER BY]  5

—-当使用GROUP BY子句时，SELECT子句中非组函数的列必须出现在GROUP BY子句中参加分组。
–查询每个职位的人数与最高工资，显示：job_id,人数,最高工资

SELECT JOB_ID,COUNT(*),MAX(SALARY)
FROM EMPLOYEES
GROUP BY JOB_ID;

–GROUP BY子句中出现的列，可能不在SELECT子句中出现。

SELECT AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

—–HAVING子句：过滤分组的结果，使用HAVING子句时必须使用GROUP BY子句。
格式：

SELECT            5
FROM              1
[WHERE]           2
[GROUP BY]        3
[HAVING]          4
[ORDER BY]        6

注意：
1.WHERE子句中不能使用组函数作为过滤条件。 而HAVING子句中可以使用组函数作为过滤条件。
2.当使用DISTINCT或GROUP BY子句时，ORDER BY子句中排序的列必须在SELECT子句中出现。
3.当条件即可以出现在WHERE子句中，也可以出现在HAVING子句中时，建议放在WHERE子句中，可以提高查询效率。

–查询最低工资在6000以上，并且job_id不包含’REP’的job_id，最低工资。

SELECT JOB_ID,MIN(SALARY)
FROM EMPLOYEES
WHERE JOB_ID NOT LIKE '%REP%'
GROUP BY JOB_ID
HAVING MIN(SALARY)>6000;

—–组函数嵌套
–平均工资最高的部门的平均工资。

SELECT MAX(AVG(SALARY)),MIN(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

注意：
1.组函数嵌套时，必须使用GROUP BY子句。
2.组函数嵌套时，SELECT子句中只能出现组函数嵌套的列(COUNT()除外)。
3.组函数嵌套只能直接出现在SELECT子句中。

—–子查询(嵌套查询)
–查询employees表中哪些员工的工资大于176号员工，显示：last_name,salary

SELECT LAST_NAME,SALARY
FROM EMPLOYEES
WHERE SALARY>(SELECT SALARY
          FROM EMPLOYEES
          WHERE EMPLOYEE_ID=176);

注意
1.从Oracle8开始，除了GROUP BY子句外，其它的子句都可以使用子查询。
2.无论子查询出现在哪个子句中，子查询必须放在一对小括号内。
3.如果子查询作为条件时，尽量将子查询放在运算符的右面，可以提高查询效率。
4.如果子查询作为条件时，子查询中列的个数与类型必须与主查询中条件列的个数与类型保持一致。
5.除非执行TOP N的操作，否则不能在子查询中使用ORDER BY子句。
6.普通子查询执行的顺序：先执行子查询，再执行主查询

—–子查询与组函数
–查询employees表中收入最高的员工，显示：last_name,salary

SELECT LAST_NAME,SALARY
FROM EMPLOYEES
WHERE SALARY=(SELECT MAX(SALARY)
          FROM EMPLOYEES);

—–HAVING子句与子查询
–查询哪些职位平均工资大于公司的平均工资，显示：job_title,平均工资

SELECT J.JOB_TITLE,AVG(E.SALARY)
FROM EMPLOYEES E JOIN JOBS J ON E.JOB_ID=J.JOB_ID
GROUP BY J.JOB_TITLE
HAVING AVG(SALARY)>(SELECT AVG(SALARY)
            FROM EMPLOYEES);

—–多行子查询(指返回多行查询结果的子查询):ALL ANY IN
–查询employees表中哪些员工的工资大于60号部分所有员工的工资，显示：last_name,department_id,salary

SELECT last_name,department_id,salary
FROM EMPLOYEES
WHERE SALARY>(SELECT MAX(SALARY)
          FROM EMPLOYEES
          WHERE DEPARTMENT_ID=60);

SELECT last_name,department_id,salary
FROM EMPLOYEES
WHERE SALARY>ALL(SELECT SALARY
             FROM EMPLOYEES
             WHERE DEPARTMENT_ID=60);

—–SELECT子句与子查询:子查询只能返回一行一列的值
–查询每个部门的department_name,job_title,人数,以及此部门中指定职位人数占公司总人数的百分比

SELECT D.DEPARTMENT_NAME,J.JOB_TITLE,COUNT(*),(COUNT(*)/(SELECT COUNT(*) FROM EMPLOYEES))*100||'%' AS 百分比
FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
JOIN JOBS J ON E.JOB_ID=J.JOB_ID
GROUP BY D.DEPARTMENT_NAME,J.JOB_TITLE;

——-伪列：Oracle自动为表添加的列。
–ROWID:数据在硬盘或内存中的地址。
–ROWNUM:行号,只能小于或小于等于一个值，或等于1.

—-查询employees收入最低的前5名员工
–Oracle中

SELECT LAST_NAME,SALARY
FROM (SELECT LAST_NAME,SALARY
            FROM EMPLOYEES
            ORDER BY SALARY ASC)
WHERE ROWNUM<=5;

–SQL SERVER中

SELECT TOP 5 LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY ASC;

–MYSQL中

SELECT LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY ASC
LIMIT 5;

–ORCLE中设置排名

SELECT 
    ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS 排名1, // 1,2,3,4,5,6,7,8
    RANK() OVER(ORDER BY SALARY DESC) AS 排名2,   //1,2,2,4,5,6,6,8
    DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 排名3,   //1,2,2,3,4,5,6,6
    LAST_NAME,SALARY
FROM EMPLOYEES;

—–查询employees表工资最高的6-10名员工的信息。
–ORACLE中。

SELECT R,LAST_NAME,SALARY
FROM (SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS R,LAST_NAME,SALARY
      FROM EMPLOYEES)
WHERE R BETWEEN 11 AND 15;

–MySQL中

SELECT LAST_NAME,SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC
LIMIT 6,10;

—SQL SERVER中

SELECT LAST_NAME,SALARY
FROM (SELECT TOP (10-6+1) LAST_NAME,SALARY
            FROM  (SELECT TOP(10) FROM EMPLOYEES ORDER BY SALARY DESC))
ORDER BY SALARY DESC;

—–高级子查询
—-成对子查询
–查询每个部门中收入最高的员工，显示：last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE (NVL(DEPARTMENT_ID,0),SALARY) IN (SELECT NVL(DEPARTMENT_ID,0),MAX(SALARY)
                    FROM EMPLOYEES
                    GROUP BY DEPARTMENT_ID);

—–相关子查询（关联子查询）:先执行主查询，再执行子查询。
–查询employees表中哪些员工的工资大于其所在部门的平均工资，last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES E
WHERE SALARY>(SELECT AVG(SALARY)
          FROM EMPLOYEES
          WHERE DEPARTMENT_ID=E.DEPARTMENT_ID);

—–集合运算：并集，交集，补集
—并集：UNION/UNION ALL
–查询收入最高与最低的员工，显示：last_name,salary

SELECT LAST_NAME AS EMP_NAME,SALARY
FROM EMPLOYEES
WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEES)
UNION
SELECT LAST_NAME AS NAME,SALARY
FROM EMPLOYEES
WHERE SALARY=(SELECT MIN(SALARY) FROM EMPLOYEES)
ORDER BY SALARY DESC;

注意：
1.集合运算时，运算的结果列的个数与类型必须保持一致。
2.集合运算时，第一个集合可以决定结果列的标题。
3.集合运算时，最后一个集合决定排序的规则。
–UNION:自动去重复值
–UNION ALL:不去重复，在不考虑重复值的情况下，建议使用UNION ALL,效率高。、

—–交集：INTERSECT

SELECT L_R_ID
FROM LIBRARY
WHERE L_B_ID='449901'
INTERSECT
SELECT L_R_ID
FROM LIBRARY
WHERE L_B_ID='112266';

—–补集：MINUS

--查询employees表中收入最高的6-10名员工的信息
SELECT LAST_NAME,SALARY
FROM (SELECT LAST_NAME,SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC)
WHERE ROWNUM<=10
MINUS
SELECT LAST_NAME,SALARY
FROM (SELECT LAST_NAME,SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC)
WHERE ROWNUM<=5
ORDER BY SALARY DESC;

到这里基本的SQL语句就总结完了，接下来就是结合实际的编程语言运用数据库知识，当然也可以再深入考虑数据库的优化问题，如对数据库的设计，安全性，稳定性，异常处理等。