MySQL之多表查询

查询语句 

mysql 多表关系 查询语句 索引

 

添加数据补充:

将一个查询结果插入到另一张表中

create table student(name char(10),gender int);
insert into student values("jack",1);
insert into student values("rose",0);
​
create table student_man(name char(10),gender int);
insert into student_man select * from student where gender = 1;

 

所有的select 关键字

select distinct *  from table_name
    where 
    group by 
    having 
    order by
    limit a,b
​
必须存在的有: 
select 
    * 可以换成任意的一个或多个字段名称  
    from
    table_name
#注意: 关键字的顺序是固定的不能随意变化

where 条件

select * from  table_name
where 
​
​
where 后面可以是 
​
1.比较运算符 
    >  <  >=  <=  =  != 
    
2.成员运算符
    in  not in    后面是一个set
​
3.逻辑运算符 
    and or not  
    not 要放在表达式的前面   and 和 or 放到两个表达式中间 
4.模糊查询 
    like 
    % 表示 任意个数的任意字符
    _ 表示一个任意字符
​
 #
 请查询 姓小的  数学小于 80 分  并且  英语 > 20分   的人的 数学成绩
 select math,name  from stu where math < 80 and english > 20 and name like "小%"; 
​

distinct 去除重复记录

select distinct * from stu; 
# 注意仅当查询结果中所有字段全都相同时 才算重复的记录

指定字段

1.星号表示所有字段
2.手动指定需要查询的字段
3.还可也是四则运算   
4.聚合函数 
​
​
#请查询  英语及格的人的 平均分 
select name,(math+english) / 2 平均分 from stu where english >= 60;

取别名

select name,math+english as 总分 from stu where name = "赵云";
​
as 可以省略 

 

统计函数

也称之为聚合函数

将一堆数据经过计算得出一个结果

求和   sum(字段名)
平均数  avg(字段名)
最大值  max(字段名)
最小值  min(字段名)
个数    count(字段名)    # 字段名称可以使用* 代替   另外如果字段为空会被忽略
​
可以用在  字段的位置  或是分组的后面   
例如: 查询所有人的平均工资  
select avg(salary) from emp
​
错误案例: 查询工资最高的人的姓名 
select name,max(salary) from emp; 
    #默认显示的第一个name  因为name有很多行  而max(salary) 只有一行    两列的行数不匹配
    # 不应该这么写 逻辑错误
select name from emp where salary = max(salary);
    # 报错  
    # 原因: 伪代码
 for line in file:
       if salary = max(salary)  # 
    #分析  where 读取满足条件的一行  ,max()先要拿到所有数据 才能求最大值,
    #这里由于读取没有完成所有无法 求出最大值
#结论  where 后面不能使用聚合函数 

group by

group 是分组的意思 即将一个整体按照某个特征或依据来分为不同的部分

为什么要分组 分组是为了统计,例如统计男性有几个 女性有几个

语法:
select xxx from table_name group by 字段名称;
​
需求:统计每个性别有几个人 
select sex,count(*) from emp group by sex;
​
需求: 查询每个性别有几个 并且显示名字
select name,sex,count(*) from emp group by sex;
​
# mysql 5.6下  查询的结果是name仅显示该分组下的第一个  
# 5.7以上则直接报错 ,5.6也可以手动开启这个功能  
​
# 我们可以用group_concat 将分组之外的字段 做一个拼接 ,但是这是没有意义
# 如果要查询某个性别下的所有信息 直接使用where 即可  
​
#结论: 只有出现在了group by 后面得字段才能出现在select的后面

 

having

用于过滤,但是与where不同的是,having使用在分组之后

案例:

# 求出平均工资大于500的部门信息 
select dept,avg(salary) from emp  group by dept having avg(salary) > 5000;
​
​
#查询 部门人数少于3的 部门名称 人员名称 人员个数
​
select dept,group_concat(name),count(*) from emp group by dept having count(*) < 3;

order

根据某个字段排序

语法:
select * from table_name order by 字段名称;
# 默认是升序
​
# 改为降序 
select * from table_name order by 字段名称 desc;
​
# 多个字段  第一个相同在按照第二个    asc 表示升序
select * from table_name order by 字段名称1 desc,字段名称2 asc;
​
​
案例:
select * from  emp order by salary desc,id desc;

 

limit

用于限制要显示的记录数量

语法1:
select * from table_name limit 个数;
语法2:
select * from table_name limit 起始位置,个数;
​
​
# 查询前三条 
select * from  emp limit 3;
​
# 从第三条开始 查询3条   3-5
select * from  emp limit 2,3;
​
​
#  注意:起始位置 从0开始
​
# 经典的使用场景:分页显示  
1.每一页显示的条数   a  = 3
2.明确当前页数   b = 2
3.计算起始位置   c = (b-1) * a      
​
​
select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;
​
​
# django 提供了现成的分页组件  但是它是先查询所有数据 丢到列表中 再取出数据   这样如果数据量太大可能会有问题 
​
​
​
​

子查询

将一个查询语句的结果作为另一个查询语句的条件或是数据来源

当我们一次性查不到想要数据时就需要使用子查询

in 关键字子查询

当内层查询 (括号内的) 结果会有多个结果时, 不能使用 = 必须是in ,另外子查询必须只能包含一列数据

需求: 指定一个部门名称,获取改部门下的所有员工信息

1.查询出 平均年龄 大于25的部门编号
​
select  dept_id from emp  group by  dept_id  having avg(age) > 25;
​
​
​
2.再根据编号查询部门的名称  
​
select name from  dept where id in (select  dept_id from emp  group by  dept_id  having avg(age) > 25);
​
子查询的思路:
1.要分析 查到最终的数据 到底有哪些步骤 
2.根据步骤写出对应的sql语句
3.把上一个步骤的sql语句丢到下一个sql语句中作为条件 
​

exists 关键字子查询

当内层查询 有结果时 外层才会执行

 案例:
 select* from dept where exists (select * from dept where id = 1);
 # 由于内层查询产生了结果 所以 执行了外层查询dept的所有数据 
​

 

多表查询

笛卡尔积查询

 

select * from   table1,table2,......
​
# 笛卡尔积查询的结果会出现大量的错误数据即,数据关联关系错误!
添加过滤条件 从表外键值 等于 主表的主键值
​
# 并且会产生重复的字段信息  例如员工里的 部门编号  和 部门表的id字段 
在select 后指定需要查询的字段名称 
​
案例:
select  dept.name 部门 ,dept.id 部门编号,emp.name 姓名,emp.id 员工编号,sex from emp ,dept where dept.id = dept_id;

内连接查询:

本质上就是笛卡尔积查询

语法:
select * from  table1 inner join table2;
案例:
select * from  emp inner join dept where dept_id = dept.id;
​
inner可以省略
select * from  emp join dept where dept_id = dept.id;

左外连接查询

左边的表无论是否能够匹配都要完整显示

右边的仅展示匹配上的记录

需求: 要查询所有员工以及其所属的部门信息 
select * from emp left join dept on dept_id = dept.id;
注意: 在外连接查询中不能使用where 关键字 必须使用on专门来做表的对应关系   

右外连接查询

右边的表无论是否能够匹配都要完整显示

左边的仅展示匹配上的记录

需求: 要查询所有部门以及其对应的员工信息 
select * from emp right join dept on dept_id = dept.id;

全外连接查询

无论是否匹配成功 两边表的数据都要全部显示

需求:查询所有员工与所有部门的对应关系  
select * from emp full join dept on dept_id = dept.id;
​
注意:mysql不支持全外连接 
​
​
我们可以将 左外连接查询的结果  和 右外连接查询的结果 做一个合并  
select * from emp left join dept on dept_id = dept.id
union
select * from emp right join dept on dept_id = dept.id;
​
 
union的用法:
select * from emp
union 
select * from emp;
​
# union将自动去除重复的记录  
# union all 不去重复 
​
​
select sex,name from emp 
union 
select * from dept;
# 注意  union 必须保证两个查询结果 列数相同  一般用在多个结果结构完全一致时
​

 

总结: 外连接查询 查到的是没有对应关系的记录,但是这样的数据原本就是有问题的,所以最常用的是内连接查询

内连接表示 只显示匹配成功的记录

外连接 没有匹配成功的也要实现

多表查询案例:

create table stu(id int primary key auto_increment,name char(10));
​
create table tea(id int primary key auto_increment,name char(10));
​
create table tsr(id int primary key auto_increment,t_id int,s_id int,
foreign key(s_id) references stu(id),
foreign key(t_id) references tea(id));
​
insert into stu values(null,"张三"),(null,"李四");
insert into tea values(null,"egon"),(null,"wer");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);
​
​
#egon老师教过哪些人? 
select tea.name,stu.name from tea join tsr join stu
on 
tea.id = t_id and stu.id = s_id
where tea.name = "egon";
​
​
# 子查询实现 
select * from stu where id in (select s_id from tsr where t_id = (select id from tea where name = "egon"));
​

 

小结：

    select [distinct] *|字段名|四则运算|函数  from table_name
    where    比较运算符   逻辑运算符    成员运算符   区间  between and  模糊匹配 like   exists                                                                       regexp 正则匹配
    group by    
    having   通常根聚合函数  count sum max  min avg 
    order by 
    limit a,b
​

子查询

多表查询

笛卡尔积 内连接 外连接

内连接最常用

通常一个需求 可以用连表 也可以 用子查询