MySQL多表查詢(xún)
2021-09-08 17:02:48

補(bǔ)充知識(shí)

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語(yǔ)法

　　1.可以給查詢(xún)出來(lái)的字段起別名（as可以省略但是不許省略）

　　　　select id as '序號(hào)',name as '姓名' from emp;

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

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

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

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

多表查詢(xún)理論

多表查詢(xún)：所需數(shù)據(jù)來(lái)源于多張表數(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,'銷(xiāo)售'),

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

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

查詢(xún)各員工姓名及對(duì)應(yīng)的部門(mén)名稱(chēng)

　　員工姓名在emp表

　　部門(mén)名稱(chēng)在dep表

　　推導(dǎo)過(guò)程1

　　　　select * from emp,dep;

　　推導(dǎo)過(guò)程2

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

　　推導(dǎo)過(guò)程3

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

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

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

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

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

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

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

　　　　（只鏈接兩個(gè)表中都有對(duì)應(yīng)的數(shù)據(jù)）

　　left join　　左鏈接

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

　　　?。ㄒ躁P(guān)鍵字左表為基礎(chǔ)展示左表所有的數(shù)據(jù)，沒(méi)有對(duì)應(yīng)的以null填充）

　　right join　　右鏈接

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

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

多表查詢(xún)之子查詢(xún)

（將一張表的查詢(xún)結(jié)果用括號(hào)括起來(lái)當(dāng)成另一條SQL語(yǔ)句的條件）

子查詢(xún)即日常生活中解決問(wèn)題的思路：分步操作

1.查詢(xún)部門(mén)是技術(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：子查詢(xún)

　　　　1.先查詢(xún)技術(shù)和人力資源id號(hào)

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

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

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

?總結(jié)

涉及到多表查詢(xún)只有兩種方法

　　1.聯(lián)表操作

　　2.子查詢(xún)

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

可視化軟件之Navicat

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

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

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

基本使用

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

　　1.鏈接

　　2.創(chuàng)建

　　3.外鍵

　　4.查詢(xún)

　　5.SQL文件

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

聯(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;

子查詢(xún)：

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