1.2 外连接

在某些情况下，需要以A表为参照物 B表与之匹配，如果A表的记录在B比表中有匹配结果，那么就显示，如果没有匹配记录，那么在查询结果中B表中字段所对应的值用null填充

     A B

id bid name  age ...         id   hehe  .....

1   1   tom   11              1   南京

2   1   lili  22              2   北京

3   2   jim   23

4   3   lucy  22

1   1   tom   11   1   南京

2   1   lili  22   1   南京

3   2   jim   23   2   北京

4   3   lucy  22   null null 


1.2.1 左连接

     以左边表为参照物 到右边表进行匹配


      A as a  left join B as b on 条件

            

mysql> select
    -> *
    -> from
    -> stu_info s left join marks_info m on s.id=m.s_id ;
+----+-------+-----+---------+------+------+------+-------+
| id | sname | age | addr    | id   | s_id | c_id | marks |
+----+-------+-----+---------+------+------+------+-------+
|  1 | Tom   |  20 | beijing |    1 |    1 |    1 |    65 |
|  1 | Tom   |  20 | beijing |    2 |    1 |    2 |    60 |
|  2 | Lucy  |  23 | nanjing | NULL | NULL | NULL |  NULL |
|  3 | Lily  |  18 | beijing |    3 |    3 |    2 |    70 |
|  4 | Damat |  21 | beijing |    4 |    4 |    2 |    90 |
|  5 | jim   |  29 | nanjing |    5 |    5 |    1 |   100 |
+----+-------+-----+---------+------+------+------+-------+
6 rows in set (0.00 sec)


意思：表左连接时，以左边表为参照物,到右边表匹配，

如果左边的表在右边的表中有匹配的记录，那么就显示记录，否则在右边表的相应的字段中填充null

查询出记录至少：  左


1.2.2 右连接   right join

 以右边表为参照物 到左边表匹配

A as a  right join B as b on 条件

mysql> select
    -> *
    -> from
    -> marks_info m right join stu_info s on s.id=m.s_id ;
+------+------+------+-------+----+-------+-----+---------+
| id   | s_id | c_id | marks | id | sname | age | addr    |
+------+------+------+-------+----+-------+-----+---------+
|    1 |    1 |    1 |    65 |  1 | Tom   |  20 | beijing |
|    2 |    1 |    2 |    60 |  1 | Tom   |  20 | beijing |
| NULL | NULL | NULL |  NULL |  2 | Lucy  |  23 | nanjing |
|    3 |    3 |    2 |    70 |  3 | Lily  |  18 | beijing |
|    4 |    4 |    2 |    90 |  4 | Damat |  21 | beijing |
|    5 |    5 |    1 |   100 |  5 | jim   |  29 | nanjing |
+------+------+------+-------+----+-------+-----+---------+
6 rows in set (0.00 sec)


1.2.3 全链接   full join

全连接：mysql不支持全连接


3. 交叉连接 笛卡尔积

就是拿一个表中的记录和另一个表中的记录进行全匹配。

A  a cross join B  b  on

结果中记录：

A 记录数  *  B 记录数

                

                每个学生可能考的科目

mysql> select
    -> *
    -> from
    -> stu_info s cross join class_info c;
+----+-------+-----+---------+----+---------+
| id | sname | age | addr    | id | c_name  |
+----+-------+-----+---------+----+---------+
|  1 | Tom   |  20 | beijing |  1 | chinese |
|  1 | Tom   |  20 | beijing |  2 | english |
|  2 | Lucy  |  23 | nanjing |  1 | chinese |
|  2 | Lucy  |  23 | nanjing |  2 | english |
|  3 | Lily  |  18 | beijing |  1 | chinese |
|  3 | Lily  |  18 | beijing |  2 | english |
|  4 | Damat |  21 | beijing |  1 | chinese |
|  4 | Damat |  21 | beijing |  2 | english |
|  5 | jim   |  29 | nanjing |  1 | chinese |
|  5 | jim   |  29 | nanjing |  2 | english |
+----+-------+-----+---------+----+---------+
10 rows in set (0.00 sec)

自查询

数据都是存在一张表里,只是在查询的时候表的别名不一样

公司结构   员工 和 领导

mysql> select * from worker;
+----+------+-----+
| id | name | lid |
+----+------+-----+
|  1 | tom  |   2 |
|  2 | jim  |   3 |
|  3 | lili |   5 |
|  4 | lucy |   5 |
|  5 | jack |   6 |
|  6 | liu  |   0 |
+----+------+-----+
6 rows in set (0.00 sec)


写个sql语句，查询出所有的员工的领导

mysql> select
    -> w.id,w.name as "worker",l.name as "leader"
    -> from
    -> worker w inner join worker l on w.lid=l.id;
+----+--------+--------+
| id | worker | leader |
+----+--------+--------+
|  1 | tom    | jim    |
|  2 | jim    | lili   |
|  3 | lili   | jack   |
|  4 | lucy   | jack   |
|  5 | jack   | liu    |
+----+--------+--------+
5 rows in set (0.00 sec)

5.多表查询

是对表中的数据进行全表扫描,全表匹配

select 

*|字段列表|all|distinct|字段 as 别名,...

from 

表1 别名，表2 as 别名……where 条件

inner join   on  条件

left join    on  条件

right join   on  条件

cross join   on  条件


where 表1.字段=表2.字段 and 表2.字段……

and|or|in()|<>[!=](不等于)|between and |like|=|not in|not between|not

having

group by 

order by  字段 DESC|ASC

limit start,length

like "%abc%"

聚合函数: avg(字段) max(字段) min(字段) sum(字段) count(字段)

除了count(字段)函数外所有的聚合函数使用都必须在数值型字段上


一般和分组一起使用时，牢记先分组后计算的原则，计算的数据是分组后的针对每个小分组的计算，而不是整个表数据计算


总计一下：4月的销量总额

时间：  time() ---->int

select sum(price) from order where ispay=1 and add_time between  strtotime("2015-4-1 00:00:00") and   strtotime("2015-4-30 00:00:00")


注意：如果检索的表中都存在同一个名称字段，那么使用查询这个字段时，必须使用 表名[表的别名].字段名 来去识别字段来源于哪个表

字段别名：方便用户查看信息  select name as "姓名";

表的别名：方便用户调用表中的字段

如果一次性不能多表查询出所需要的结果，可以使用*临时表*建立一个中间表然后进行多表查询 临时表必须用 别名

查询：科目id 科目名称 平均成绩，最高成绩，最低成绩

mysql> select
    -> c.id,c.c_name,avg(m.marks) avgm,max(m.marks) maxm,min(m.marks) minm
    -> from
    -> class_info c inner join marks_info m on c.id=m.c_id
    -> group by m.c_id
    -> ;
+----+---------+---------+------+------+
| id | c_name  | avgm    | maxm | minm |
+----+---------+---------+------+------+
|  1 | chinese | 82.5000 |  100 |   65 |
|  2 | english | 73.3333 |   90 |   60 |
+----+---------+---------+------+------+
2 rows in set (0.00 sec)

这其中涉及到临时表，就是上一次查询的结果的表

查询出每个学生名字，所考课目名，科目的成绩 科目平均成绩，最高成绩，最低成绩

select

s.id,s.sname,ll.c_name,m.marks,ll.avgm,ll.maxm,ll.minm

from

stu_info s inner join marks_info m on s.id=m.s_id inner join                     (select

c.id,c.c_name,avg(m.marks) avgm,max(m.marks) maxm,min(m.marks) minm

from

class_info c inner join marks_info m on c.id=m.c_id

group by m.c_id) ll on m.c_id=ll.id

where s.id=1