mysql多表查询求和_MySQL 多表查询实战来袭

点击上方 蓝字关注我们！

  1. 准 备数据

createtable department (        idintunsigned auto_increment primary key,        namechar(12) notnullunique# 部门名称唯一);createtable teacher(        idintunsigned auto_increment primary key,        namechar(12) notnull,        gender enum("male","famale") notnulldefault"male",        age tinyintunsignednotnull,        coaching_age tinyintunsignednotnull, # 教龄        salary intunsignednotnull,        dep_id intunsignednotnull);insertinto department(id,name) values        (100,"管理部"),        (200,"教学部"),        (300,"财务部"),        (500,"教务部");insertinto teacher(name,gender,age,coaching_age,salary,dep_id) values        ("TeacherZhang","male",32,8,9000,200),        ("TeacherLi","male",34,10,12000,200),        ("TeacherYun","male",26,4,21000,100),        ("TeacherZhou","famale",24,2,4000,300),        ("TeacherZhao","famale",32,12,23000,100),        ("TeacherYang","male",28,6,3000,300),        ("TeacherWang","famale",22,1,3200,400);

　　 数据说明：老师表中有个部门编号为400的TeacherWang老师，没有对应的部门。部门表中有个编号为500的教务部，其中没有包含老师。

2.查询语法

SELECT DISTINCT(字段名1,字段名2) FROM 左表名 连接类型 JOIN 右表名ON 连表条件WHERE 筛选条件GROUP BY 分组字段HAVING 过滤条件ORDER BY 排序字段 asc/descLIMIT 限制条数;