数据库多表查询之 where和INNER JOIN

https://blog.csdn.net/u013372487/article/details/52622491?locationNum=1

https://blog.csdn.net/qingtanlang/article/details/2133816

http://www.cnblogs.com/drake-guo/p/6101531.html

https://blog.csdn.net/huyr_123/article/details/61616547

在多表查询中，一些SQL开发人员更喜欢使用WHERE来做join，比如：

SELECT a.ID, b.Name, b.Date FROM Customers a, Sales b WHERE a.ID = b.ID;

• 1

缺点：在上面语句中，实际上是创建了两张表的笛卡尔积，所有可能的组合都会被创建出来。在笛卡尔连接中，在上面的例子中，如果有1000顾客和1000条销售记录，这个查询会先产生1000000个结果，然后通过正确的 ID过滤出1000条记录。 这是一种低效利用数据库资源，数据库多做100倍的工作。 在大型数据库中，笛卡尔连接是一个大问题，对两个大表的笛卡尔积会创建数10亿或万亿的记录。

为了避免创建笛卡尔积，应该使用INNER JOIN ：

SELECT a.ID, b.Name, b.Date FROM Customers a INNER JOIN Sales b ON a.ID = b.ID;

• 1

优点：如上面语句，使用inner join 这样数据库就只产生等于ID 的1000条目标结果。增加了查询效率。

有些数据库系统会识别出 WHERE连接并自动转换为 INNER JOIN。在这些数据库系统中，WHERE 连接与INNER JOIN 就没有性能差异。但是， INNER JOIN 是所有数据库都能识别的，因此DBA会建议在你的环境中使用它。

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

• 1
• 2
• 3

注意： 
1、INNER JOIN 等同于 JOIN； 
2、示意图： 

我在练习MySQL操作语句时，使用一条完全没有错误的语句：

update students set name='drake' where name='chuan';  

却报了如下错误：

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.  

简单翻译一下：
你正在使用 安全更新模式（safe upate mode）并且你在尝试 update 一个表时 没有用带有键的列 作为where条件,在首选项中切换选项。  

初学者在修改一个值时可能会用以下语句：

update t set col='new_value'   

而这正是很多人常犯的错误。因为他没有限定条件，会将表中所有的记录都修改一遍。

为了防止这种错误出现，我们可以开启安全更新模式（safe update mode）：

set [global] SQL_SAFE_UPDATES = 1;  

在update操作中：当where条件中列（column）没有索引可用且无limit限制时会拒绝更新。where条件为常量且无limit限制时会拒绝更新。

在delete操作中： 当①where条件为常量，②或where条件为空，③或where条件中 列（column）没有索引可用且无limit限制时拒绝删除。

需要注意的是：

update操作中，where可以为常量  ，where条件中列（column）可以没有索引。但是需要有limit限制。

然而delete要严格一些：where不能为常量，且where条件中列（column）不能没有索引！

1 .WHERE子句中使用的连接语句，在数据库语言中，被称为隐性连接。INNER JOIN……ON子句产生的连接称为显性连接。（其他JOIN参数也是显性连接）WHERE 和INNER JOIN产生的连接关系，没有本质区别，结果也一样。但是！隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。

2 .无论怎么连接，都可以用join子句，但是连接同一个表的时候，注意要定义别名，否则产生错误！

a> left join：理解为“有效连接”，两张表中都有的数据才会显示left join：理解为“有左显示”，比如on a.field=b.field，则显示a表中存在的全部数据及a//b中都有的数据，A中有、B没有的数据以null显示

b> right join：理解为“有右显示”，比如on a.field=b.field，则显示B表中存在的全部数据及a//b中都有的数据，B中有、A没有的数据以null显示

c> full join：理解为“全连接”，两张表中所有数据都显示，实际就是inner +(left-inner)+(right-inner)

3 .join可以分主次表   外联接有三种类型：完全外联，左联，右联．
完全外联包含两张表的所有记录．
左联是以左边的表为主，右边的为辅，右联则相反

语法格式：
    其实 INNER JOIN ……ON的语法格式可以概括为：
    FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表X ON Member.字段号=表X.字段号
    您只要套用该格式就可以了。
    
    现成格式范例：
    虽然我说得已经比较明白了，但为照顾初学者，我还是以本会员注册系统为例，提供一些现成的语法格式范例，大家只要修改其中的数据表名称和字段名称即可。
    
    连接两个数据表的用法：
    FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort
    语法格式可以概括为：
    FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
    
    连接三个数据表的用法：
    FROM (Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel 
    语法格式可以概括为：
    FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号
    
    连接四个数据表的用法：
    FROM ((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity
    语法格式可以概括为：
    FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号
    
    连接五个数据表的用法：
    FROM (((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock=Wedlock.Wedlock
    语法格式可以概括为：
    FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

连接查询是将两个或多个的表按某个条件连接起来，从中选取需要的数据，连接查询是同时查询两个或两个以上的表的使用的。当不同的表中存在相同意义的字段时，可以通过该字段来连接这几个表。

     1.内连接查询

            内连接查询是一种最常用的连接查询。内查询可以查询两个或两个以上的表。举例说明两个表的连接查询。当该字段的值相等时，就查询出该记录。

      举个栗子~

       SELECT  num,name,employee.d_id,sex,d_name,function

               FROM employee ，department

                WHERE employee.d_id=department.d_id;

      2.外连接查询

            基本语法：

                 SELECT 属性名列表

                                FROM 表名1  LEFT | RIGHT JOIN 表名2

                                ON 表名1.属性1=表名2.属性2；

• 左连接查询

                   进行左连接查询时，可以查出表1的表中所有记录，而表2所指的表中，只能查询出匹配的记录

• 右连接查询

                  进行右连接查询时，可以查出表2的表中所有记录，而表1所指的表中，只能查询出匹配的记录

     3.复合·条件查询

                  在连接查询时，也可以增加其他的限制条件，使查询结果更加准确。

子查询

       子查询是将一个查询语句嵌套在另外一个查询语句中，内层查询语句的查询结果，可以为外层查询语句提供查询条件。

          举个栗子~IN

          SELECT   *   FROM   employee

                       WHERE  d_id   IN

                         (SELECT   d_id    FROM   department);

          还 举个栗子~比较运算符

          SELECT  id ,name, score FROM computer_stu

                    WHERE  score>=

                           (SELECT score FROM scholarship

                              WHERE level=1)；

           还 举个栗子~EXISTS(表示存在，使用EXISTS关键字时，内层查询语句不反悔查询的记录，而是返回一个真假值)

          SELECT  *   FROM employee

                        WHERE EXISTS

                       ( SELECT d_name FROM department

                                    WHERE d_id =1003)；

            还 举个栗子~ANY(表示满足其中任一条件)

                如查询获得任意一个奖学金的最低分，即只要获得奖学金都满足条件

          SELECT * FROM computer_stu

                 WHERE score >=ANY

                         ( SELECT score FROM scholarship);

               最后一个栗子~ALL(表示满足所有条件)

                如查询获得最高奖学金的记录

           SELECT * FROM computer_stu

                  WHERE score >=ALL

                         ( SELECT score FROM scholarship);