45-多表查詢

一枫攀、建表

#建表
create table department(
id int,
name varchar(20) 
);

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

#插入數(shù)據(jù)
insert into department values
(200,'技術(shù)'),
(201,'人力資源'),
(202,'銷售'),
(203,'運營');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

二禽笑、多表聯(lián)合查詢

把多張物理表合并成一張?zhí)摂M表再進行查詢。

1洽胶、內(nèi)連接：保留兩張表有對應(yīng)關(guān)系的記錄

select * from emp,dep where emp.dep_id=dep.id;
select dep.name,emp.name from emp inner join dep on emp.dep_id=dep.id
    where dep.name = "技術(shù)";

2晒夹、左連接：在內(nèi)連接的基礎(chǔ)上保留左表的記錄

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

3、右連接：在內(nèi)連接的基礎(chǔ)上保留右表的記錄

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

4姊氓、全外連接：在內(nèi)鏈接的基礎(chǔ)上保留左右表的記錄

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;

查詢部門內(nèi)最新入職的員工

select * from emp
inner join
(select depart_id,max(hire_date) as maxd from emp group by depart_id) as t1
on emp.depart_id = t1.depart_id
where emp.hire_date = t1.maxd
;

三丐怯、子查詢

從一張表中查詢出結(jié)果，用該結(jié)果作為查詢下一張表的過濾條件翔横。

帶in關(guān)鍵字的子查詢

not  in 不支持查詢null值

#查詢平均年齡在25歲以上的部門名
select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技術(shù)部員工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技術(shù)');

#查看不足1人的部門名(子查詢得到的是有人的部門id)
select name from department where id not in (select distinct dep_id from employee);

帶any關(guān)鍵字的子查詢

#在 SQL 中 ANY 和 SOME 是同義詞读跷，SOME 的用法和功能和 ANY 一模一樣。

# ANY 和 IN 運算符不同之處1
ANY 必須和其他的比較運算符共同使用禾唁，而且ANY必須將比較運算符放在 ANY 關(guān)鍵字之前效览，所比較的值需要匹配子查詢中的任意一個值，這也就是 ANY 在英文中所表示的意義

例如：使用 IN 和使用 ANY運算符得到的結(jié)果是一致的
select * from employee where salary = any (
select max(salary) from employee group by depart_id);

select * from employee where salary in (
select max(salary) from employee group by depart_id);

結(jié)論：也就是說“=ANY”等價于 IN 運算符荡短，而“<>ANY”則等價于 NOT IN 運算符

# ANY和 IN 運算符不同之處2
ANY 運算符不能與固定的集合相匹配丐枉，比如下面的 SQL 語句是錯誤的

SELECT
*
FROM
T_Book
WHERE
FYearPublished < ANY (2001, 2003, 2005)

帶all關(guān)鍵字的子查詢

# all同any類似，只不過all表示的是所有掘托，any表示任一
查詢出那些薪資比所有部門的平均薪資都高的員工=》薪資在所有部門平均線以上的狗幣資本家
select * from employee where salary > all (
select avg(salary) from employee group by depart_id);

查詢出那些薪資比所有部門的平均薪資都低的員工=》薪資在所有部門平均線以下的無產(chǎn)階級勞苦大眾
select * from employee where salary < all (
select avg(salary) from employee group by depart_id);

查詢出那些薪資比任意一個部門的平均薪資低的員工=》薪資在任一部門平均線以下的員工
select * from employee where salary < any ( select avg(salary) from employee group by depart_id); 

查詢出那些薪資比任意一個部門的平均薪資高的員工=》薪資在任一部門平均線以上的員工
select * from employee where salary > any (
select avg(salary) from employee group by depart_id);

帶比較運算符的子查詢

#比較運算符：=瘦锹、!=、>闪盔、>=弯院、<、<=泪掀、<>
#查詢大于所有人平均年齡的員工名與年齡
mysql> select name,age from emp where age > (select avg(age) from emp);

#查詢大于部門內(nèi)平均年齡的員工名抽兆、年齡
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;

帶exists關(guān)鍵字的子查詢

EXISTS關(guān)字鍵字表示存在。在使用EXISTS關(guān)鍵字時族淮，內(nèi)層查詢語句不返回查詢的記錄辫红。
而是返回一個真假值。True或False
當返回True時祝辣，外層查詢語句將進行查詢贴妻；當返回值為False時，外層查詢語句不進行查詢

#department表中存在dept_id=203蝙斜，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205名惩，F(xiàn)alse
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

in和exists的區(qū)別

！Ｔ熊Ｃ漯摹９ニ！弯予！當in和exists在查詢效率上比較時戚宦，in查詢的效率快于exists的查詢效率！Ｐ饽邸Ｊ苈ァ！：舸纭艳汽！

==============================exists==============================
# exists
exists后面一般都是子查詢，后面的子查詢被稱做相關(guān)子查詢（即與主語句相關(guān)）对雪，當子查詢返回行數(shù)時河狐，exists條件返回true，
否則返回false瑟捣，exists是不返回列表的值的甚牲，exists只在乎括號里的數(shù)據(jù)能不能查找出來，是否存在這樣的記錄蝶柿。

# 例
查詢出那些班級里有學生的班級
select * from class where exists (select * from stu where stu.cid=class.id)

# exists的執(zhí)行原理為：
1、依次執(zhí)行外部查詢：即select * from class 
2非驮、然后為外部查詢返回的每一行分別執(zhí)行一次子查詢：即(select * from stu where stu.cid=class.cid)
3交汤、子查詢?nèi)绻祷匦校瑒texists條件成立劫笙，條件成立則輸出外部查詢?nèi)〕龅哪菞l記錄

==============================in==============================
# in
in后跟的都是子查詢芙扎，in()后面的子查詢 是返回結(jié)果集的

# 例
查詢和所有女生年齡相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')

# in的執(zhí)行原理為：
in()的執(zhí)行次序和exists()不一樣,in()的子查詢會先產(chǎn)生結(jié)果集,
然后主查詢再去結(jié)果集里去找符合要求的字段列表去.符合要求的輸出,反之則不輸出.

四、符合條件連接查詢

示例1：以內(nèi)連接的方式查詢employee和department表填大，并且employee表中的age字段值必須大于25,即找出年齡大于25歲的員工以及員工所在的部門

select employee.name,department.name from employee inner join department
    on employee.dep_id = department.id
    where age > 25;

示例2：以內(nèi)連接的方式查詢employee和department表戒洼，并且以age字段的升序方式顯示

select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.id
    and age > 25
    order by age asc;  # 降序是desc