MySQL数据库中的多表数据记录查询

1.关系数据操作

（1）并（UNION）

“并”就是把具有相同字段数目和字段类型的表合并到一起。

（2）笛卡儿积（CARTESIAN PRODUCT）

笛卡儿积就是没有连接条件表关系返回的结果。

SELECT e.ename employeename, e.job

     FROM t_employee e;

（3）内连接（INNER JOIN）

在表关系的笛卡儿积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。主要有以下三种连接方式：

1）自然连接（NATURAL JOIN）：首先根据表关系中相同名称的字段自动进行记录匹配，然后去掉重复的字段。

2)等值连接：等值连接操作就是表关系的笛卡儿积中，选择所匹配字段值相等的数据记录。等值连接关系数据操作需要在执行过程中的用“符号=”指定匹配条件，在新关系中不会去掉重复字段。

3)不等连接：笛卡尔积中，选择所匹配字段值不相等（！=）的数据记录。与自然连接相比，不等连接关系数据操作需要在执行过程中用符号！=指定匹配条件，在新关系中不会去掉重复字段。

（4）外连接（OUTER JOIN）

左外连接：就是表关系的笛卡儿积中，除了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录

右外连接：就是表关系的笛卡儿积中，除了选择相匹配的数据记录，还包含关联右边表中不匹配的数据记录

全外连接：就是表关系的笛卡儿积中，除了选择相匹配的数据记录，还包含关联左右两边表中不匹配的数据记录

 

(5)内连接查询

包含三种情况：

1)自连接。指表与其自身进行连接。

SELECT e.ename employeename, e.job, l.ename loadername

     FROM t_employee AS e INNER JOIN t_employee AS l

          ON e.mgr=l.empno   #雇员表的领导编号=领导表的领导编号

或者

SELECT e.ename employeename, e.job, l.ename loadername

    FROM t_employee AS e, t_employee l

    WHERE e.mgr=l.empno;

2)等值连接

SELECT e.empno, e.ename, e.job, d.dname, d.loc

   FROM t_employee e INNER JOIN t_dept d

        ON e.deptno=d.deptno;

或

SELECT e.empno, e.ename, e.job, d.dname, d.loc

   FROM t_employee e, t_dept d

   WHERE e.deptno=d.deptno;

SELECT e.empno, e.ename employeename, e.sal, e.job, l.ename loadername, d.dname, d.loc

   FROM t_employee e INNER JOIN t_employee l ON e.mgr=l.empno

                                      INNER JOIN t_dept d ON l.deptno=d.deptno;

或者

SELECT e.empno, e.ename employeename, e.sal, e.job, l.ename loadername, d.dname, d.loc

  FROM t_employee e, t_employee l, t_dept d

  WHERE e.mgr=l.empno AND l.deptno=d.deptno;

3）不等连接

SELECT e.ename employeename, e.job, l.ename loadername

     FROM t_employee e INNER JOIN t_employee l

           ON e.mgr=l.empno AND e.empno>l.empno;

或者

SELECT e.ename employeename, e.job, l.ename loadername

   FROM t_employee e, t_employee l

   WHERE e.mgr=l.empno AND e.empno>l.empno;

（6）外连接查询

左外连接

SELECT e.ename employeename, e.job, l.ename loadername

     FROM t_employee e LEFT JOIN t_employee l

           ON e.mgr=l.empno;

右外连接

SELECT e.empno, e.ename, e.job,d.dname,d.loc

     FROM t_dept d RIGHT JOIN t_employee e

           ON e.deptno=d.deptno;

(7)合并查询数据记录

UNION会把查询结果集直接合并在一起，同时将去掉重复数据记录。

SELECT * 

       FROM t_cstudent

UNION

SELECT *

      FROM t_mstudent;

UNION ALL 会把查询结果集直接合并在一起

SELECT * 

       FROM t_cstudent

UNION ALL

SELECT *

      FROM t_mstudent;

(8)子查询

子查询可以减少电脑的工作负荷，访问速度快。

#返回结果为单行单列子查询

SELECT *

  FROM t_employee

 WHERE sal>(

      SELECT sal

              FROM t_employee

              WHERE ename='SMITH');

#单行多列子查询

SELECT ename, sal, job

    FROM t_employee

    WHERE (sal, job)=(

         SELECT sal, job

               FROM t_employee

               WHERE ename='SMITH');

#返回结果为多行单列子查询

SELECT *

     FROM t_employee

     WHERE deptno IN (

           SELECT deptno

              FROM t_dept

);

SELECT *

   FROM t_employee

   WHERE sal>ANY(

   SELECT sal

        FROM t_employee

        WHERE job='MANAGER'

);

SELECT *

   FROM t_employee

   WHERE sal>ALL(

   SELECT sal

         FROM t_employee

        WHERE job='MANAGER'

);

SELECT *

   FROM t_dept c

   WHERE NOT EXISTS(

       SELECT *

      FROM t_employee

      WHERE deptno=c.deptno);

 

#返回结果为多行多列子查询

SELECT d.deptno, d.dname, d.loc, number, average

     FROM t_dept d Inner JOIN (

           SELECT deptno dno, COUNT(empno) number, AVG(sal) average

                 FROM t_employee

                 GROUP BY deptno DESC) employee

           ON d.deptno=employee.dno;