Mysql查询，多表连接

一、基本概念（查询语句）*

①基本语句

1、“select * from 表名;” 查询全部数据
2、“select 字段名 from 表名;” 查询表中指定字段数据
3、“select distinct 字段名 from 表名;”，对特定字段去重查询
4、“select 字段名 from 表名 where 查询条件;”，加限制的字段查询

②条件查询

1）比较运算符：>, <, >=, <=, =, !=, <>

查询大于50岁的信息
select * from emp where age>50;
select id, name from emp where age>50;
查询小于30岁的信息
select * from emp where age<30;
查询年龄为30岁的所有员工的名字
select * from emp where age=30;

2）逻辑运算符：and, or, not

18到28之间的学生信息
select * from emp where age>18_and age<28:
28岁以上的女性
select * from emp where age>28 and gender=“女”;
select * from emp where age>28 and gender=2;
18以上或者身高查过180(包含)以上
select * from emp where age>18 or height>=180;

3)模糊查询：like， rlike

% 替换1个或者多个
_ 替换1个
查询姓名中 以“小”开始的名字
select name from emp where name=“小”;
select name from emp where name like"小%";
查询姓名中有“小”所有的名字
select name from emp whece name like “%小%”;
查询有2个字的名字
select name from emp where name like “";
查询有3个字的名字
select name from emp where name like "”;
查询至少有2个字的名字
select name from emp where name like “__%”;
rlike正则
查询以周开始的姓名
select name from emp where name rlike “^周.*”;
查询以张开始、伦结尾的姓名
select name from emp where name rlike “^张.*伦$”;

4)范围查询：in，not in，between…and，not between…and

查询年龄为18、34的姓名
select name, age from emp where age=18 or age=34;
select name,age from emp where age in (18，34);
not in不非连续的范围之内
年龄不是 18、34岁之间的信息
select name,age from emp where age not in (18，34);
between … and …表示在一个连续的范围内
查询年龄在18到34之间的的信息
select name,age from emp where age between 18 and 34;
not between … and …表示不在一个连续的范围内
查询年龄不在在18到34之间的的信息
select * from emp where age not between 18 and 34;

空判断

判空 is null
查询身高为空的信息
select *from emp where height is null/NULL/Null;
判非空is not null
select * from emp where height is not null;

排序：order_by
select * from emp where (age between 28 and 34) and gender=1 order by age;
分组：group_by, group_concat():查询内容， having

where :是对整个数据表信息的判断;
having:是对于分组后的数据进行判断
–group by
按照性别分组,查询所有的性别
select gender from emp group by gender;
–计算每种性别中的人数
select gender, count() from emp group by gender;
where是在group by 前面
–计算男性的人数
select count() from emp where gender=‘男’;
–group_concat(…)
查询同种性别中的姓名
select gender,group_concat(name) from emp group by gender;
having :having是在group by后面
查询平均年龄超过30岁的性别，以及姓名
select gender ,avg(age) from emp group by gender having avg(age) > 30;
查询每种性别中的人数多于2个的信息
select gender,count() from emp group by gender having count() > 2;
– 查询每组性别的平均年龄
select gender,avg(age) from emp group by gender;

分页： limit

limit start,count (start:表示从哪─个开始;count:表示数量)
即limit（第N页-1)*每个的个数，每页的个数;
limit在使用的时候，要放在最后面.
1
2
3
限制查询出来的数据个数
select from emp where gender=1 limit 2;
查询前5个数据
select from emp limit 0,5;
查询id6-10(包含)的书序
select * from emp limit 5,5;
每页显示2个，第1个页面
select * from emp limit 0,2;
每页显示2个，第2个页面
select * from emp limit 2,2;
每页显示2个，第3个页面
select * from emp limit 4,2;
每页显示2个，第4个页面
select * from emp limit 6,2;
每页显示2个，显示第6页的信息,按照年龄从小到大排序
select * from emp order by age asc limit 10,2;
– 如果重新排序了,那么会显示第一页
select * from emp where gender=2 order by height des limit 0,2;

5)聚合函数：count(), max(), min(), sum(), avg(), round()

聚合函数
-总数-- count
-查询男性有多少人，女性有多少人
select count() from emp where gender=1;
select count() as 男性人数 from emp where gender=1;
select count() as 女性人数 from emp where gender=2;
-最大值-最小值
– max --min
一查询最大的年龄
select max (age) from emp ;
–查询女性的最高身高
select max (height) from emp where gender=2;
-求和
–sum
-计算所有人的年龄总和
select sum ( age) from emp ;
–平均值
– avg
–计算平均年龄
select avg(age) from emp ;
–计算平均年龄
select sum ( age) / count( ) from emp ;
–四舍五入round ( 123.23 ,_1)保留1位小数
–计算所有人的平均年龄,保留2位小数

6)连接查询 ：inner join, left join, right join

inner join
select … from 表 A inner join表B;
select * from students inner join classes;
查询有能够对应班级的学生以及班级信息
select * from emp inner join classes on students.cls_id=classes.id;
按照要求显示姓名、班级
select emp ., classes.name from emp inner join classes on emp .cls_id=classes.id;
select emp .name，classes.name from emp inner join classes on emp .cls_id=classes.id;
给数据表起名字
select s.name,c.name from emp as s inner join classes as c on s.cls_id=c.id;
查询有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select s.,c.name from emp as s inner join classes as c on s.cls_id=c.id;
在以上的查询中,将班级姓名显示在第1列
select c.name，s.* from emp as s inner join classes as c on s.cls_id=c.id;
查询有能够对应班级的学生以及班级信息，按照班级进行排序
select c.xxx s.xxx from emp as s inner join clssses as c on … order by …;
select c.name，s.* from emp as s inner join classes as c on s.cls_id=c.id order by c.name;
当时同一个班级的时候，按照学生的id进行从小到大排序
select c.name，s.* from emp as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;
left join
查询每位学生对应的班级信息
select * from emp as s left join classes as c on s.cls_id=c.id;
查询没有对应班级信息的学生
– select … from xxx as s left join xxx as c on… where …
– select … from xxx as s left join xxx as c on… . … having …
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;

最近遇到的一个多表连接查询例子，要求取出Bom中所有值，但是所需的字段有部分在material
多表连接查询
bom，material_extend，material三张表连接查询。

通过bom中material_id关联materialextend中的barcode，用material中的material_id关联material中的id，取出所需的对应字段。bom表与material无直接关联数据，此时多表连接查询即可。

SELECT o.material_id,o.effective_date,o.disable_date,o.isEffective,o.version,o.create_on,o.remarks,q.unit,q.standard,q.color,q.name 
from jsh_bom o 
LEFT JOIN jsh_material_extend p ON o.material_id = p.bar_code 
LEFT JOIN jsh_material q ON p.material_id = q.id 
order by o.material_id asc