MySQL图形界面怎么多表查询_mysql多表查询

多表查询

#表和数据的准备工作

mysql> create table department(

-> id int,

-> name varchar(20)

-> );

mysql> 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

-> );

mysql> insert into department values

-> (200,'技术'),

-> (201,'人力资源'),

-> (202,'销售'),

-> (203,'运营');

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

-> ;

连表查询

所谓连表其实就是在连接的时候先创建一张大表，里面存放的是两张表的笛卡尔积，接下来就按照条件进行筛选即可

连表查询主要分为内连接和外连接两种，两种各有异同

内连接

内连接是根据两张表的相同之处进行连接处理，只会显示连表关联处相同的内容

有如下两种方式

select 列 from 表1,表2 where 条件;

select * from department,employee where department.id=employee.dep_id;

select 列 from 表1 inner join 表2 on 条件;

select * from department inner join employee on department.id=employee.dep_id;

mysql> select * from department inner join employee on department.id=

employee.dep_id;

+------+--------------+----+-----------+--------+------+--------+

| id | name | id | name | sex | age | dep_id |

+------+--------------+----+-----------+--------+------+--------+

| 200 | 技术 | 1 | egon | male | 18 | 200 |

| 201 | 人力资源 | 2 | alex | female | 48 | 201 |

| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |

| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |

| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |

+------+--------------+----+-----------+--------+------+--------+

5 rows in set (0.00 sec)

相比如上两种内连接方式，第二种更好，因为第二种使用了inner join….on….的方法，后面可以跟单表查询筛选方式一致继续使用where等条件，而第一种已经使用了where条件，后面再接where条件稍显别扭

外连接

外连接主要分为三种：左外连接，右外连接，全连接

左外连接

左外连接相比于内连接而言增加了一点：会将左表的内容全部显示，右表没有内容会默认为null

select 列 from 表1 left join 表2 on 条件

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

mysql> select * from department left join employee on department.id= employee.dep_id;

+------+--------------+------+-----------+--------+------+--------+

| id | name | id | name | sex | age | dep_id |

+------+--------------+------+-----------+--------+------+--------+

| 200 | 技术 | 1 | egon | male | 18 | 200 |

| 201 | 人力资源 | 2 | alex | female | 48 | 201 |

| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |

| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |

| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |

| 203 | 运营 | NULL | NULL | NULL | NULL | NULL |

+------+--------------+------+-----------+--------+------+--------+

6 rows in set (0.02 sec)

右外连接

右外连接相比于右连接而言增加了一点：会将右表的内容全部显示，左表表没有内容会默认为null

select 列 from 表1 right join 表2 on 条件

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

mysql> select * from department right join employee on department.id=employee.dep_id;

+------+--------------+----+------------+--------+------+--------+

| id | name | id | name | sex | age | dep_id |

+------+--------------+----+------------+--------+------+--------+

| 200 | 技术 | 1 | egon | male | 18 | 200 |

| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |

| 201 | 人力资源 | 2 | alex | female | 48 | 201 |

| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |

| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |

| NULL | NULL | 6 | jingliyang | female | 18 | 204 |

+------+--------------+----+------------+--------+------+--------+

6 rows in set (0.00 sec)

全外连接

全连接是左外连接与右外连接的集合，但mysql没有全外连接的直接语句，sqlserver的全外连接为full join，但mysql可以使用union连接左右外连接从而实现全外连接的功能1

select 列 from 表1 left join 表2 on 条件 union select 列 from 表1 right join 表2 on 条件

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

union

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

mysql> select * from department left join employee on department.id= employee.dep_id

-> union

-> select * from department right join employee on department.id= employee.dep_id;

+------+--------------+------+------------+--------+------+--------+

| id | name | id | name | sex | age | dep_id |

+------+--------------+------+------------+--------+------+--------+

| 200 | 技术 | 1 | egon | male | 18 | 200 |

| 201 | 人力资源 | 2 | alex | female | 48 | 201 |

| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |

| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |

| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |

| 203 | 运营 | NULL | NULL | NULL | NULL | NULL |

| NULL | NULL | 6 | jingliyang | female | 18 | 204 |

+------+--------------+------+------------+--------+------+--------+

7 rows in set (0.02 sec)

总结

所谓连表就是将两张表按一定规则连接起来变成一张大表，从from开始一直到on条件结束就是形成一张大表的过程，然后就可以使用单表查询的条件where、group by、having、order by 、limit等条件就行数据筛选

注意一点：两张表中重复的字段注意使用表名.字段名的方式处理

子查询

子查询是将一个查询语句嵌套在另一个查询语句中。

内层查询语句的查询结果，可以为外层查询语句提供查询条件

子查询中可以包含：in、not in、any、all、exits和 not exists等关键字

还可以包含比较运算符：= 、 !=、> 、

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

带比较运算符的子查询

#比较运算符：=、!=、>、>=、

#查询大于所有人平均年龄的员工名与年龄

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;

带exists关键字的子查询

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

总结与练习

如果一个问题既可以使用连表查询解决也可以使用子查询解决，尽量使用连表查询解决，因为连表查询速度快效率高

#连表查询的练习

mysql> select t1.name,t2.name from department as t1 left join employee as t2 on t1.id=t2.dep_id where age>25;

mysql> select * from department as t1 inner join employee as t2 on t1.id=t2.dep_id order by age;

mysql> select * from department as t1 inner join employee as t2 on t1.id=t2.dep_id order by age;

mysql> select count(t1.name) from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name;

mysql> select t1.id,count(t2.id) from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name;

mysql> select t1.id,t1.name,count(t2.id) from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name;

mysql> select t1.id,t1.name,count(t2.id) from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name order by count(t2.id);

mysql> select t1.id,t1.name,count(t2.id) as c from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name order by c;

#子查询

mysql> select name from department where id not in(select dep_id from employee group by dep_id); #查询部门中不足一人的部门名

mysql> select * from department where id in(select dep_id from employee group by dep_id having count(name)=1); #查询部门中只有一人的部门名