多表查询理论与navicat

多表查询理论

与可视化软件navicat

补充知识

1.group_concat(字段名)  

用于分组之后
获取除了分组以外还符合条件的其他字段数据 本质是拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'|',salary) from emp group by post;
# 可以支持多个字段一起合并获取 

 

 

 

2.concat(字段名)

 select concat(name,'|',age) from emp;
# 同样支持多个字段合并获取

3.concat_ws()

select concat_ws('|',name,age,salary,gender) from emp;
# 用于分组前 适用于多个字段相同的分隔符，可以自动分隔

 

4.as语句

1.可以给查询出来的字段名起别名（建议不要省略）
        select id as '序号',name as '姓名' from emp;
2.还可以给表的名字起别名（主要用于多表查询）
        select * from emp as t1 where t1.id>5;
    
    将emp表名起别名为t1 之后用t1代替emp

总结：as左边是字段名那右边就是字段名,表名同理

给字段起别名

给表起别名

 

多表查询理论

多表查询:所需数据来源于多张表数据的组合

数据准备

create table dep(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    gender enum('male','female') not null default 'male',
    age int,
    dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,gender,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

SQL语句查询出来的结果其实也可以看成是一张表，

涉及到多张表可能会出现字段名冲突需要在字段名前面加上表名作为限制

 

查询各员工姓名及对应的部门名称（员工姓名在emp表，部门名称在dep表）

　　推导过程1

select * from emp,dep; # 笛卡尔积

　　推导过程2

select * from emp,dep where emp.dep_id=dep.id;

　　推导过程3

select emp.name,dep.name from emp,dep where emp.dep_id=dep.id;

 

多表查询之联表

"""先将多张表拼接成一张大表，然后在基于表单查询完成"""

 

在MySQL中拼接表有专门的关键字

　　inner join 内链接

select * from emp innner join dep on emp.dep_id=dep.id;
# 只链接两种表中都有对应的数据

　　

　　left join 左链接

select * from emp right join dep on emp.dep_id=dep.id;
# 以关键字左表为基础表展示左表所有的数据，没有对应的以null填充

　　right join 右链接

select * from emp right join dep on emp.dep_id=dep.id;
# 以关键字右表为基础展示右表所有的数据，没有对应的用null来填充

　　union （了解）

# 全链接
        select * from emp left join dep on emp.dep_id=dep.id
        union
        select * from emp right join dep on emp.dep_id=dep.id;        

多表查询之子查询

"""将一张表的查询结果括号括起来当成另一张SQL语句的条件"""

子查询就相当于我们日常生活中解决问题的方法>>>分步处理

1.查询部门是技术或者人力资源的员工信息

方法一:联表

select emp.name,emp.age,dep.name from emp inner join dep on emp.dep_id=dep.id where dep.name in('技术','人力资源');

方法二:子查询

1.先查询技术和人力资源id号
    select id from dep where name in('技术','人力资源');
2.再到员工表里根据部门id号筛选出员工数据
    select * from emp where dep_id (select id from dep where name in('技术','人力资源'));

 

总结

涉及到多表查询只有两种方法
1.联表查询
2.子查询
很多复杂的查询甚至需要用到两者的结合使用

可视化软件之Navicat

 

在工作中有时候需要更加快速的完成一些基本操作 可视化软件可以大大提升工作效率 Navicat是一款可以操作多种数据库的软件 内部其实就是封装了相应的SQL语句
Navicat软件也是需要收费的 但是我们可以使用破解版本

破解地址:https://defcon.cn/214.html
老版本破解:http://www.ddooo.com/softdown/59996.htm
免费试用14天

基本使用
    有些功能如果没有你也可以直接修改软件自动生成的SQL语句
  
1.链接
2.创建
3.外键
4.查询
5.SQL文件

 

 

 

作业

1.查询平均年轻在25岁以上的部门名(使用两种方式都完成一下)

第一种方法：联表操作

select dep.name from emp 
　　　　 inner join dep
        on emp.dep_id = dep.id
        group by dep.name
        having avg(age) > 25
        ;

第二种方法：子查询

select name from dep where id in(select dep_id from emp group by dep_id  having avg(age)>25);