MySQL多表查询理论

concat 用法

查询岗位名以及岗位包含的所有员工名字

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语法

在这里可以用作对字段进行重命名,但仅仅是用于展示,不会更改实际表数据

select concat_ws('|',name,age,salary,gender) as '综合数据' from emp;

 

 

select id as '序号',name as '姓名' from emp; 

 

 

也可以用于给表起别名

select * from emp as t1 where t1.id>5; 

 

 

 

 总结: 给表起别名 就是 原表名 as 新表名 给字段起别名就是 原字段名 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);

 

 

 查询各员工姓名及对应的部门名称

数据在两个中 演示推导过程:

第一步:select * from dep,emp; 当一次性查询多张表时,数据会以表1 的每个数据对应表2的所有数据 

这个现象称之为笛卡尔集

 

 

通过SQL语句查询出来的结果也可以看做是一张表

但是 所需要的数据 需要dep_id = id才是有效数据

第二步:也就是需要筛选出dep_id = id 的数据

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

 

 

 其实翻译过来的意思就是 选取emp表中dep_id 等于dep中的id 相同的字段

第三步: 获取员工名和部门名即可

select emp.name,dep.name from emp,dep where emp.dep_id=dep.id; (获取emp表中的name 和dep表中的name)

 

 

 多表查询之联表

inner join 内链接

只连接两张表中都有的数据 

select * from emp inner join dep on emp.dep_id=dep.id;

 

 

 

 

 

 jerry 这个204没有所以会自动去除

 left join   左链接

select * from emp left join dep on emp.dep_id=dep.id;

 

 

 左连接就是展示左边 emp 表中所有数据,右边没有对应的数据字段就用null 填充

right join 右链接

同理 右连接就是展示右边 dep 表中所有数据,左边没有对应的数据字段就用null填充

select * from emp right join dep on emp.dep_id =dep.id;

 

 

 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 * from emp inner join dep on emp.dep_id=dep.id 

然后在写 select * from emp inner join dep on emp.dep_id=dep.id where dep.name in('技术','人力资源')

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

 

 

 2.通过子查询方法

先获取技术和人力资源的id号

select id from dep where name in('技术','人力资源')

 

 

 再筛选出emp表中根据部门id号筛选出员工数据 就是把上一条复制到括号内

select * from emp where dep_id in(select id from dep where name in('技术','人力资源'));

涉及到多表查询只有两种方法

1.联表操作
2.子查询
并且很多复杂的查询甚至需要两者的结合

可视化软件之Navicat

将mysql 封装成一个效率更高的软件 内部还是基于myqsl中相应的sql语句,并能操作兼容多种数据库软件

老版本破解:http://www.ddooo.com/softdown/59996.htm

 根据教程破解即可进入页面

 

 

 

练习:

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

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

 

2.子查询 select dep_id from emp group by dep_id having avg(age)>25

 

 

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