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
