多表查询

-- 技术部花名册
-- select name as "技术部花名册" from employee where depart_id in (select id from department where name = "技术");
-- 平均年龄大于25的部门名称
-- select id,name from department where id in (select depart_id from employee group by depart_id having AVG(age)>25);
-- 部门人数为0的部门名称
-- select id,name from department where id not in (select depart_id from employee group by depart_id having count(id)>0);
-- select * from department;
-- insert into department (id,name) values (1,"技术"),(2,"人力资源"),(3,"销售"),(4,"运营");
-- insert into department values
-- (200,'技术'),
-- (201,'人力资源'),
-- (202,'销售'),
-- (203,'运营');
-- delete from department;
-- select * from department;
-- select id,name from department where id not in (select depart_id from employee group by depart_id having count(id)>0);
-- select name from department where id not in (select distinct depart_id from employee);
-- 部门内工资最高的员工姓名
-- SELECT depart_id,salary,name from employee where salary = any (select max(salary) from employee GROUP BY depart_id);
-- SELECT depart_id,salary,name from employee where salary in (select max(salary) from employee GROUP BY depart_id);
-- 找出大于任意一个部门平均工资的所有员工信息
-- SELECT depart_id,salary,name from employee where salary > any (select AVG(salary) from employee GROUP BY depart_id);
-- 找出大于所有部门平均工资的员工姓名
-- select * from employee where salary > all (select avg(salary) from employee group by depart_id);
-- 找出小于所有部门平均工资的员工姓名
-- select * from employee where salary < all (select avg(salary) from employee group by depart_id);
-- 找出年龄大于25岁的员工以及员工所在的部门
-- SELECT employee.name,department.name as depart_name from employee inner join department on department.id = employee.depart_id WHERE age > 25;
-- 以内连接的方式查询employee和department表，并且以age>25且年龄字段的升序方式显示
-- select employee.*,department.* from employee inner join department on department.id = employee.depart_id where age > 25 ORDER BY age asc;
-- 查询大于部门内平均年龄的员工名、年龄
-- SELECT employee.name,employee.age from employee inner join (select depart_id,avg(age) as avg_age from employee GROUP BY depart_id) as tt on employee.depart_id = tt.depart_id where employee.age > tt.avg_age;
-- department表中存在dept_id=2，Ture
-- SELECT * from employee where EXISTS (select id from department where id = 2);
-- department表中存在dept_id=5，False
-- SELECT * from employee where EXISTS (select id from department where id = 5);
-- select * from stu where sex = "男" and age in (select age from stu where sex="女");

习题

一、外链接语法

select 字段 from 表1 inner/left/right 表2 on 表1.字段 =表2.字段;

1.1 交叉链接，不适用任何匹配条件。生成笛卡尔积

select * from department,employee;

1.2内链接，只链接匹配的行

#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果

1.3外链接之左连接：优先显示左表全部记录

#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果

1	`mysql>` `select` `employee.id,employee.name,department.name` `as` `depart_name` `from` `employee` `left` `join` `department` `on` `employee.dep_id=department.id;`

1.4外链接之右连接：优先显示右表全部记录

#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果

1	`mysql>` `select` `employee.id,employee.name,department.name` `as` `depart_name` `from` `employee` `right` `join` `department` `on` `employee.dep_id=department.id;`

1.5 全外连接：显示左右两个表全部记录

#强调：mysql可以使用此种方式间接实现全外连接

select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;

#注意 union与union all的区别：union会去掉相同的纪录

二、符合条件的连接查询

#示例1：以内连接的方式查询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：以内连接的方式查询employee和department表，并且以age>25且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;

三、子查询

#1）子查询是将一个查询语句嵌套在另一个查询语句中。
#2）内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3）子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4）还可以包含比较运算符：= 、 !=、> 、<

3.1 带IN关键字的子查询

#查询平均年龄在25岁以上的部门名
select id,name from department
    where id in
        (select dep_id from employee group by dep_id having avg(age) > 25);
 
#查看技术部员工姓名
select name from employee
    where dep_id in
        (select id from department where name='技术');
 
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理。
# 解决方案如下
mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);

3.2 带ANY关键字的子查询

#在 SQL 中 ANY 和 SOME 是同义词，SOME 的用法和功能和 ANY 一模一样。
 
# ANY 和 IN 运算符不同之处1
ANY 必须和其他的比较运算符共同使用，而且ANY必须将比较运算符放在 ANY 关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是 ANY 在英文中所表示的意义
 
例如：使用 IN 和使用 ANY运算符得到的结果是一致的
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);
 
结论：也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符
 
# ANY和 IN 运算符不同之处2
ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的
 
SELECT
*
FROM
T_Book
WHERE
FYearPublished < ANY (2001, 2003, 2005)

3.3 带ALL关键字的子查询

# all同any类似，只不过all表示的是所有，any表示任一
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all (
select avg(salary) from employee group by depart_id);
查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
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);

3.4 带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)
 
 
#查询大于部门内平均年龄的员工名、年龄
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;

3.5带EXISTS关键字的子查询

EXISTS关键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。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，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

3.6 in与exists

！！！！！！当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率！！！！！！
==============================exists==============================
# exists
exists后面一般都是子查询，后面的子查询被称做相关子查询（即与主语句相关），当子查询返回行数时，exists条件返回true，
否则返回false，exists是不返回列表的值的，exists只在乎括号里的数据能不能查找出来，是否存在这样的记录。
 
# 例
查询出那些班级里有学生的班级
select * from class where exists (select * from stu where stu.cid=class.id)
 
# exists的执行原理为：
1、依次执行外部查询：即select * from class 
2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)
3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录
 
==============================in==============================
# in
in后跟的都是子查询，in()后面的子查询 是返回结果集的
 
# 例
查询和所有女生年龄相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')
 
# in的执行原理为：
in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

3.7 not in与 not exists

!!!!!!not exists查询的效率远远高与not in查询的效率。!!!!!!
 
==============================not in==============================
not in()子查询的执行顺序是：
为了证明not in成立，即找不到，需要一条一条地查询表，符合要求才返回子查询的结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。
                 
==============================not exists==============================
not exists：
如果主查询表中记录少，子查询表中记录多，并有索引。
例如:查询那些班级中没有学生的班级
select * from class
 
where not exists
 
(select * from student where student.cid = class.cid)
 
not exists的执行顺序是：
在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。

3.8应用

#数据
create database db13;
 
use db13
 
create table student(
    id int primary key auto_increment,
    name varchar(16)
);
 
create table course(
    id int primary key auto_increment,
    name varchar(16),
    comment varchar(20)
);
 
create table student2course(
    id int primary key auto_increment,
    sid int,
    cid int,
    foreign key(sid) references student(id),
    foreign key(cid) references course(id)
);
 
 
insert into student(name) values
("egon"),
("lili"),
("jack"),
("tom");
 
insert into course(name,comment) values
("数据库","数据仓库"),
("数学","根本学不会"),
("英语","鸟语花香");
 
 
insert into student2course(sid,cid) values
(1,1),
(1,2),
(1,3),
(2,1),
(2,2),
(3,2);
 
准备数据

#示例
# 1、查询选修了所有课程的学生id、name:（即该学生根本就不存在一门他没有选的课程。）
select * from student s where not exists
    (select * from course c where not exists
        (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
 
 
select s.name from student as s
inner join student2course as sc
on s.id=sc.sid
group by s.name
having count(sc.id) = (select count(id) from course);
 
# 2、查询没有选择所有课程的学生，即没有全选的学生。（存在这样的一个学生，他至少有一门课没有选）
select * from student s where exists
    (select * from course c where not exists
        (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
 
# 3、查询一门课也没有选的学生。（不存这样的一个学生，他至少选修一门课程）
select * from student s where not exists
    (select * from course c where exists
        (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
 
# 4、查询至少选修了一门课程的学生。
select * from student s where exists
    (select * from course c where exists
        (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));