MySQL学习之连接查询

1. 笛卡尔乘积现象

在表的连接查询方面有一种现象被称为：笛卡尔积现象。

笛卡尔积现象：

当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

select ename,dname from emp,dept;

避免笛卡尔积现象：

连接时加条件，满足条件的记录筛选出来。

思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？

         不会，次数还是56次。只不过显示的是有效记录。

关于表的别名：

        select e.ename,d.dname from emp e,dept d;

        表的别名有什么好处？

                第一：执行效率高。

                第二：可读性好。

mysql> select

->  e.ename,d.dname

-> from

->  emp as e,dept as d

-> where

->  e.deptno = d.deptno;

1. 内连接：等值连接

SQL99语法结构更清晰一些：

表的连接条件和后来的where条件分离了。

SQL92语法：

mysql> select

    ->          e.ename,d.dname

    -> from

    ->          emp as e,dept as d

    -> where

->          e.deptno = d.deptno;

SQL99语法：

mysql> select

    ->          e.ename,d.dname

    -> from

    ->          emp as e

    -> join

    ->          dept as d

    -> on

    ->          e.deptno = d.deptno;

//inner 可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）

SQL99语法：

Select

........

From

A

Innner jion

B

On

a和b的连接条件

Where

筛选条件

1. 内连接：非等值连接

最大的特点是：连接条件中的关系是非等量关系。

mysql> select

    ->  e.ename,e.sal,s.grade

    -> from

    ->  emp e

    ->（inner） join

    ->  salgrade s

    -> on

->  e.sal between s.losal and hisal;

1. 内连接：自连接

最大的特点是：一张表看做两张表。自己连接自己。

mysql> select

    ->  a.ename as '员工名',b.mgr as '领导名'

    -> from

    ->  emp a

    -> join

    ->  emp b

    -> on

    -> a.mgr = b.empno;

1. 外连接

内连接：

假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。

AB两张表没有主副之分，两张表是平等的。

外连接：

假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中

的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类？

左外连接（左连接）：表示左边的这张表是主表。 left  (outer)  join

右外连接（右连接）：表示右边的这张表是主表。  right  (outer)  join

Outer 可省略

左连接有右连接的写法，右连接也会有对应的左连接的写法。

mysql> select

    ->          e.ename,d.dname

    -> from

    ->          emp as e

    -> right (outer) join

    ->          dept as d

    -> on

    ->          e.deptno = d.deptno;

mysql> select

    ->          e.ename,d.dname

    -> from

    ->          dept as d   

-> left (outer) join

    ->          emp as e

    -> on

    ->          e.deptno = d.deptno;

连接最重要的特点是：主表的数据无条件的全部查询出来。

1. 三张表连接/多张表连接

注意，解释一下：

     

一条SQL中内连接和外连接可以混合。都可以出现!

        表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。

1. 子查询

where子句中使用子查询

From子句中使用子查询

注意（技巧）: from后面的子查询，可以将子查询的查询结果当做一张临时表。

此时有个注意点：平均值avg()是特殊函数，需起别名使用！

在select后面嵌套子查询

mysql> select

    ->   e.ename,(select d.dname from dept d where d.deptno = e.deptno) result

    -> from

->  emp e;

1. union （可以将查询结果集相加）

mysql> select ename,job from emp where job = 'manager' or job  = 'salesman'；

mysql> select ename,job from emp where job in ('manager' ,'salesman');

mysql> select ename,job from emp where job = 'manager'

    -> union

    -> select ename,job from emp where job = 'salesman'；

//union的效率更高，union 把乘法变成了加法。

表连接来说，每连接一次新表。则匹配的次数满足笛卡尔积，成倍得翻

但是union可以减少匹配的次数。

在减少匹配次数的情况下，

还可以完成两个结果集的拼接。

a连接 b 连接 C

a  10条记录

B  10条记录

C  10条记录

匹配次数是: 1000

a 连接 b一个结果: 10*10 --> 100次

a 连接 C一个结果: 10 * 10 --> 100次

使用union的话是: 100次 + 100次 =200次

union在使用的时候有注意事项吗?

//错误的: union在进行结果集合并的时候，要求两个结果集的列数相同。

select ename ,job from emp where job =’ MANAGER’

union

select ename from emp where job = 'SALESMAN';

// MYSQL可以，oracle语法严格 ，不可以，报错。要求: 结果集合并时列和列的数据类型也需要一致。

select ename ,job from emp where job = ‘MANAGER’

union

select ename ,sal from emp where job = 'SALESMAN';

1. Limit(重点中的重点，以后分页查询全靠它了。)

• limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）
• limit取结果集中的部分数据，这是它的作用。
• 语法机制：

       limit startIndex, length

               startIndex表示起始位置，从0开始，0表示第一条数据。

               length表示取几个

      

• limit是sql语句最后执行的一个环节：

        select                     5

                ...

        from                       1

                ...            

        where                    2

                ...    

        group by                3

                ...

        having                    4

                ...

        order by                6

                ...

        limit                        7

                ...;

mysql> select ename from emp order by sal desc limit 0,5;

mysql> select ename from emp order by sal desc limit 5;

• 分页

每页显示pageSize条记录：

第pageNo页：(pageNo - 1) * pageSize, pageSize

pageSize是什么？是每页显示多少条记录

pageNo是什么？显示第几页

通用的标准分页sql？

每页显示3条记录：

第1页：0, 3

第2页：3, 3

第3页：6, 3

第4页：9, 3

第5页：12, 3