MySQL多表查詢
2021-09-08 17:02:48

補(bǔ)充知識

1.group_concat()方法　　用于分組之后

獲取除分組以外其他字段數(shù)據(jù)，本質(zhì)可以理解為是拼接操作

　　select post,group_concat(name) from emp group by post;

　　select post,group_concat(name,':',salary) from emp group by post;

?2.concat()方法　　用于分組之前

　　select post,concat(name,':',salary) from emp;

3.concat_ws()方法　　用于分組之前，多個(gè)字段相同分隔符情況

　　select concat_ws('|',name,age,salary,gender) from emp;

4.as語法

　　1.可以給查詢出來的字段起別名（as可以省略但是不許省略）

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

　　2.還可以給表名起別名（主要用在多表查詢中）

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

　　　?。▽mp表名起別名為t1，之后使用t1代替emp）

總結(jié)：as左邊是字段則是字段別名，左邊是表名則是表別名

多表查詢理論

多表查詢：所需數(shù)據(jù)來源于多張表數(shù)據(jù)的組合

建表

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

);

插入數(shù)據(jù)

insert into dep values

(200,'技術(shù)'),

(201,'人力資源'),

(202,'銷售'),

(203,'運(yùn)營');

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);

查詢各員工姓名及對應(yīng)的部門名稱

　　員工姓名在emp表

　　部門名稱在dep表

　　推導(dǎo)過程1

　　　　select * from emp,dep;

　　推導(dǎo)過程2

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

　　推導(dǎo)過程3

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

SQL語句查詢出來的結(jié)果其實(shí)也可以看成是一張表
涉及到多表可能會出現(xiàn)字段名沖突需要在字段名前面加上表名作限制

多表查詢之聯(lián)表

（現(xiàn)將多張表拼接成一張大表，然后再基于單表查詢完成）

在MySQL中拼接表有專門的關(guān)鍵字

　　inner join　　內(nèi)鏈接（用的最多）

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

　　　?。ㄖ绘溄觾蓚€(gè)表中都有對應(yīng)的數(shù)據(jù)）

　　left join　　左鏈接

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

　　　　（以關(guān)鍵字左表為基礎(chǔ)展示左表所有的數(shù)據(jù)，沒有對應(yīng)的以null填充）

　　right join　　右鏈接

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

　　　?。ㄒ躁P(guān)鍵字右表為基礎(chǔ)展示右表所有的數(shù)據(jù)，沒有對應(yīng)的以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;

?　　　?。ㄍ瑫r(shí)以關(guān)鍵字兩邊為基礎(chǔ)，展示兩表所有的數(shù)據(jù)，沒有對應(yīng)的以null填充）

多表查詢之子查詢

（將一張表的查詢結(jié)果用括號括起來當(dāng)成另一條SQL語句的條件）

子查詢即日常生活中解決問題的思路：分步操作

1.查詢部門是技術(shù)或者人力資源的員工信息

　　方法1：聯(lián)表操作

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

　　方法2：子查詢

　　　　1.先查詢技術(shù)和人力資源id號

　　　　　　select id from dep where name in ('技術(shù)','人力資源');

　　　　2.再去員工表里面根據(jù)部門id號篩選出員工數(shù)據(jù)

　　　　　　select * from emp where dep_id in (select id from dep where name in ('技術(shù)','人力資源'));

?總結(jié)

涉及到多表查詢只有兩種方法

　　1.聯(lián)表操作

　　2.子查詢

并且很多復(fù)雜的查詢甚至需要兩者結(jié)合使用

可視化軟件之Navicat

在工作中有時(shí)候需要更加快速的完成一些基本操作，可視化軟件可以大大提升工作效率，Navicat是一款可以操作多種數(shù)據(jù)庫的軟件，內(nèi)部其實(shí)就是封裝了相應(yīng)的SQL語句

Navicat軟件也是需要收費(fèi)的，只能免費(fèi)試用14天，但是我們可以使用破解版本

破解地址：自己百度去（當(dāng)心垃圾軟件）

基本使用

　　有些功能如果沒有也可以直接在軟件內(nèi)手動(dòng)修改SQL語句

　　1.鏈接

　　2.創(chuàng)建

　　3.外鍵

　　4.查詢

　　5.SQL文件

查詢平均年齡在25歲以上的部門名(使用兩種方式都完成一下)

聯(lián)表：

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

子查詢：

select dep_id from emp group by dep_id having avg(age) > 25;

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