mysql 多表查询

 

MYSQL：
1、多表查询
     超过一个表的综合查询；
     注意：找到表与表之间的纽带（桥梁）
     分类:
        1：内连接：
      等值连接:查询的结果带有重复记录的
      自然连接:查询结果中没有重复的记录
      （distinct|group by）删掉重复记录的查询
2：外连接：
      左连接:
      右连接
      全连接
3：交叉连接：
student_info    学生信息表
s_id s_name     age   area
1      Tom      20     北京
2      Jack     21     上海
3      Lily     19     南京
4      Damat    22     北京
5      DaiJun   22     上海

1，2，3
marks 成绩表：
s_id c_id mark
1     1    60
2     2    70
3     1    80
4     3    60
1     2    70
2     3    59
class 科目表
c_id c_name
1     语文
2     数学
3     英语
1）等值连接:
    select 字段列表 from 表列表 where 表1.字段1=表2.字段1 and 表2.字段2=表3.字段2 and。。。。。。
    eg：检索出所有的学生信息及其各科目所考试的成绩
        编号，姓名，年龄 地区 科目和成绩;

select s_id,s_name,age,area,c_name,marks
from student_info,marks,class
where student_info.s_id=marks.s_id
       and marks.c_id=class.c_id;
    注意:如果查询涉及到的字段中，在多个表中存在，那么要使用"表名.字段名"的方式来表明是那个表中的；
2)如果重复的字段较多，那么我们给使用的表起个别名:
   表名 as 别名|表名 别名

   select s.s_id,s_name,age,area,c_name,marks
   from student_info as s,marks as m,class as c
   where s.s_id=m.s_id
       and m.c_id=c.c_id;
   eg:查询出各科目的平均成绩？group by
select c_name,avg(marks) as "jjj"
from class,marks
where class.c_id=marks.c_id;
group by c_name;
   eg:统计学生所在地的学生人数？
   eg:统计学生大于70的所在地的学生人数
3)sql语句中中：
内连接：
select 字段列表 from 表1 innerjoin 表2
on 表1.字段1=表2.字段1
eg:
    检索出所有学生的姓名和成绩；
    select s_name,marks from student_info s inner join marks m on s.s_id=m.s_id
外连接：
左连接:
      格式:
       select 字段列表 from 表1 left outerjoin 表2
       on 表1.字段1=表2.字段1    
   注意:以左边的表为参照物，用右边的表进行匹配，如果右边的表中有匹配记录，那么就显示记录，如果没有匹配的记录，那么就填充null；

   eg:
      打印出学生的全部成绩？
    

右连接:
    格式:
       select 字段列表 from 表1 right outerjoin 表2
       on 表1.字段1=表2.字段1    
   注意:以右边的表为参照物，用左边的表进行匹配，如果左边的表中有匹配记录，那么就显示记录，如果没有匹配的记录，那么就填充null；
 
全连接：在mysql中部支持全连接
   格式:
       select 字段列表 from 表1 left outerjoin 表2
       on 表1.字段1=表2.字段1

       union all

       select 字段列表 from 表1 right outerjoin 表2
       on 表1.字段1=表2.字段1
    
        union all:将查询得到得两个记录集合并起来，但是不去除重复的记录；默认使用的情况下没有all---就相当于加了distinct去掉的重复的记录；现在所实现的效果才是全连接的效果；
4）交叉连接:
   cross
   格式:
     select 字段列表 from 表1 cross 表2
   
   供应商    超市
   1   1
   2            2
   3            3
5）自连接：
inner join

person

p_id   p_name   s_id
1 tom       2
2      lily      3
3      damat     4
4      mack

职工   领导   经理
tom    领导   lily
lily   领导   damat
damat 领导   mack
per 职工表
p_id   p_name   s_id
1 tom       2
2      lily      3
3      damat     4
4      mack

cap 经理表
p_id   p_name
1 tom 
2      lily 
3      damat
4      mack

select per.p_name,"领导",cap.p_name
from person per,person cap
where per.s_id=cap.p_id;

6)sql语句中的常量：
   select "字符" from 表
7)成绩大于60的学生姓名：
select s_name from student_info,marks
where student_info.s_id=marks.s_id marks>60;

 

mysql中的内连接,外连接

mysql中的内连接,外连接实例详解
内连接: 只连接匹配的行
左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
交叉连接  生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

举个例子吧。
表A
id   name
1    张
2    李
3    王

表B
id   address   A_id
1    北京      1
2    上海      3
3    南京      10

包容性:A表包容B表,左连接左表是全的.(left join 或 left outer join )
SQL语句如下：
SELECT A.name, B.address
FROM A
LEFT JOIN B ON A.id = B.A_id
查询结果为：
name     address
张     北京
李     NULL
王     上海

包容性:B表包容A表,右连接右表是全的.(right join 或 right outer join )
SQL语句如下：
SELECT A.name, B.address
FROM A
RIGHT JOIN B ON A.id = B.A_id
查询结果为：
name     address
张     北京
王     上海
NULL     南京

排他性:A,B表中至少有1个匹配时，才返回行。两表的交集
SQL语句如下：
select A.name,B.address from A
inner join B
on A.id = B.A_id

查询结果为：
name     address
张     北京
王     上海
inner join  内连接等价于下面的sql:
SELECT A.name, B.address
FROM A, B
WHERE A.id = B.A_id

注释:全外连接返回参与连接的两个数据集合中的全部数据，无论它们是否具有与之相匹配的行。在功能上，它等价于

对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集

。(full join 或 full outer join )
SQL语句如下：
select * from A
full join B
查询结果为：
id     name     id     address A_id
1     张     1     北京     1
2     李     1     北京     1
3     王     1     北京     1
1     张     2     上海     3
2     李     2     上海     3
3     王     2     上海     3
1     张     3     南京     10
2     李     3     南京     10
3     王     3     南京     10

注释：返回3*3=9条记录，即笛卡尔积
SQL语句如下：
SELECT * FROM A
CROSS JOIN B
查询结果为：
id     name     id     address A_id
1     张     1     北京     1
2     李     1     北京     1
3     王     1     北京     1
1     张     2     上海     3
2     李     2     上海     3
3     王     2     上海     3
1     张     3     南京     10
2     李     3     南京     10
3     王     3     南京     10
CROSS JOIN等价于:
select * from A,B

注意:
1. on A.id = B.id 等同于 using(id)//这里字段名要相同
2. 当 MySQL 在从一个表中检索信息时，你可以提示它选择了哪一个索引。 
如果 EXPLAIN 显示 MySQL 使用了可能的索引列表中错误的索引，这个特性将是很有用的。 
通过指定 USE INDEX (key_list)，你可以告诉 MySQL 使用可能的索引中最合适的一个索引在表中查找记录行。 
可选的二选一句法 IGNORE INDEX (key_list) 可被用于告诉 MySQL 不使用特定的索引。   

效率问题:
1.inner join比left join快
注:inner join  内连接等价于下面的sql: SELECT A.name, B.address FROM A, B WHERE A.id = B.A_id
所以一般要用一般的连接就可以了.
2.连接字段建索引