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
转载于:https://blog.51cto.com/it5808/1710044