python基础教程之MySQL之单表查询、多表查询(2)

当前位置:

首页 > Python基础教程 >

python基础教程之MySQL之单表查询、多表查询(2)

mysql> insert into dep values -> (200,'技术'), -> (201,'人力资源'), -> (202,'销售'), -> (203,'运营'); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into emp(name,sex,age,dep_id) values -> ('jason','male',18,200), -> ('egon','female',48,201), -> ('kevin','male',38,201), -> ('nick','female',28,202), -> ('owen','male',18,200), -> ('jerry','female',18,204) -> ; Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from dep; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec) mysql> select * from emp; +----+-------+--------+------+--------+ | id | name | sex | age | dep_id | +----+-------+--------+------+--------+ | 1 | jason | male | 18 | 200 | | 2 | egon | female | 48 | 201 | | 3 | kevin | male | 38 | 201 | | 4 | nick | female | 28 | 202 | | 5 | owen | male | 18 | 200 | | 6 | jerry | female | 18 | 204 | +----+-------+--------+------+--------+ 6 rows in set (0.00 sec)

多表查询示例：

# 笛卡尔积  多表查询
mysql> select * from emp,dep;                                
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |  
|  1 | jason | male   |   18 |    200 |  201 | 人力资源     |    
|  1 | jason | male   |   18 |    200 |  202 | 销售         |  
|  1 | jason | male   |   18 |    200 |  203 | 运营         |  
|  2 | egon  | female |   48 |    201 |  200 | 技术         |  
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |    
|  2 | egon  | female |   48 |    201 |  202 | 销售         |  
|  2 | egon  | female |   48 |    201 |  203 | 运营         |  
|  3 | kevin | male   |   38 |    201 |  200 | 技术         |  
|  3 | kevin | male   |   38 |    201 |  201 | 人力资源     |    
|  3 | kevin | male   |   38 |    201 |  202 | 销售         |  
|  3 | kevin | male   |   38 |    201 |  203 | 运营         |  
|  4 | nick  | female |   28 |    202 |  200 | 技术         |  
|  4 | nick  | female |   28 |    202 |  201 | 人力资源     |    
|  4 | nick  | female |   28 |    202 |  202 | 销售         |  
|  4 | nick  | female |   28 |    202 |  203 | 运营         |  
|  5 | owen  | male   |   18 |    200 |  200 | 技术         |  
|  5 | owen  | male   |   18 |    200 |  201 | 人力资源     |    
|  5 | owen  | male   |   18 |    200 |  202 | 销售         |  
|  5 | owen  | male   |   18 |    200 |  203 | 运营         |  
|  6 | jerry | female |   18 |    204 |  200 | 技术         |  
|  6 | jerry | female |   18 |    204 |  201 | 人力资源     |    
|  6 | jerry | female |   18 |    204 |  202 | 销售         |  
|  6 | jerry | female |   18 |    204 |  203 | 运营         |  
+----+-------+--------+------+--------+------+--------------+
# 将所有的数据都对应了一遍进行合并，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据
# 查询员工及所在部门的信息
mysql> select dep.name,emp.name from emp,dep where emp.dep_id=dep.id;
+--------------+-------+
| name         | name  |
+--------------+-------+
| 技术         | jason |
| 人力资源     | egon  |
| 人力资源     | kevin |
| 销售         | nick  |
| 技术         | owen  |
+--------------+-------+
# 查询部门为技术部的员工及部门信息
mysql> select * from emp,dep where emp.dep_id=dep.id and dep.name='技术';
+----+-------+------+------+--------+------+--------+
| id | name  | sex  | age  | dep_id | id   | name   |
+----+-------+------+------+--------+------+--------+
|  1 | jason | male |   18 |    200 |  200 | 技术   |
|  5 | owen  | male |   18 |    200 |  200 | 技术   |
+----+-------+------+------+--------+------+--------+

多表查询分为内连接、左连接、右连接、全连接，在产生连接后 如需跟条件此时就不能用到where，只能使用on作为替代。
# 将2张表关联到一起的操作，有专门的方法
# 1、内连接（inner join）：只取两张表有对应关系的记录
mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|  4 | nick  | female |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    200 |  200 | 技术         |
+----+-------+--------+------+--------+------+--------------+

# 2、左连接（left join）：在内连接的基础上保留左表满足条件的全部内容，右表没有对应上的内容用null表示
mysql> select * from emp left join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  5 | owen  | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|  4 | nick  | female |   28 |    202 |  202 | 销售         |
|  6 | jerry | female |   18 |    204 | NULL | NULL         |
+----+-------+--------+------+--------+------+--------------+
# 3 右连接（right join）：在内连接的基础上，保留右表满足条件的全部内容，左表没有对应上的内容用null表示
mysql> select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|    4 | nick  | female |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+
# 4 全连接（用union将左连接和右连接联合起来）：在内连接的基础上保留左右表没有对应
mysql> 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;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    5 | owen  | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|    4 | nick  | female |   28 |    202 |  202 | 销售         |
|    6 | jerry | female |   18 |    204 | NULL | NULL         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+

三、子查询：

# 就是将一个查询语句的结果用括号括起来当做另一个查询语句的条件去用

# 接着上面的表：
mysql> select * from emp;
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | jason | male   |   18 |    200 |
|  2 | egon  | female |   48 |    201 |
|  3 | kevin | male   |   38 |    201 |
|  4 | nick  | female |   28 |    202 |
|  5 | owen  | male   |   18 |    200 |
|  6 | jerry | female |   18 |    204 |
+----+-------+--------+------+--------+
mysql> select * from dep;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
# 1.查询部门是技术或者人力资源的员工信息
mysql> # 先将技术和人力资源部门的id号先找出来：
mysql> select id from dep where name='技术' or name='人力资源';
+------+
| id   |
+------+
|  200 |
|  201 |
+------+

mysql> # 在将上述查询到的id号作为条件，进行再一次查询：
mysql> select * from emp where dep_id in (select id from dep where name='技术' or name='人力资源');
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | jason | male   |   18 |    200 |
|  2 | egon  | female |   48 |    201 |
|  3 | kevin | male   |   38 |    201 |
|  5 | owen  | male   |   18 |    200 |
+----+-------+--------+------+--------+
--------------------------------------------------------------------------------------------------------------------------------
# 2.每个部门最新入职的员工
有这张表emp：
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary  | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
|  1 | jason     | male   |  18 | 2017-03-01 | 外交部    | NULL         |    6600 |    401 |         0 |
|  2 | egon      | male   |  78 | 2015-03-02 | 教学部    | NULL         | 1000000 |    401 |         1 |
|  3 | kevin     | male   |  81 | 2013-03-05 | 教学部    | NULL         |    8300 |    401 |         1 |
|  4 | tank      | male   |  73 | 2014-07-01 | 教学部    | NULL         |    3500 |    401 |         1 |
|  5 | owen      | male   |  28 | 2012-11-01 | 教学部    | NULL         |    2100 |    401 |         1 |
|  6 | jerry     | female |  18 | 2011-02-11 | 教学部    | NULL         |    9000 |    401 |         1 |
|  7 | nick      | male   |  18 | 1900-03-01 | 教学部    | NULL         |   30000 |    401 |         1 |
|  8 | sean      | male   |  48 | 2010-11-11 | 教学部    | NULL         |   10000 |    401 |         1 |
|  9 | 歪歪      | female |  48 | 2015-03-11 | 销售部    | NULL         |    3000 |    402 |         2 |
| 10 | 丫丫      | female |  38 | 2010-11-01 | 销售部    | NULL         |    2000 |    402 |         2 |
| 11 | 丁丁      | female |  18 | 2011-03-12 | 销售部    | NULL         |    1000 |    402 |         2 |
| 12 | 星星      | female |  18 | 2016-05-13 | 销售部    | NULL         |    3000 |    402 |         2 |
| 13 | 格格      | female |  28 | 2017-01-27 | 销售部    | NULL         |    4000 |    402 |         2 |
| 14 | 张野      | male   |  28 | 2016-03-11 | 运营部    | NULL         |   10000 |    403 |         3 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | 运营部    | NULL         |   20000 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | 运营部    | NULL         |   19000 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | 运营部    | NULL         |   18000 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | 运营部    | NULL         |   17000 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+

# ①先将入职日期最大的员工查询处理起名为表t2
#  (select post,max(hire_date) as max_date from emp group by post) as t2
# ②为了易读性，将emp表起名为t1，将需要查询的结果字段名写在select后（此时包括t2中的字段，因为select是最后执行的语句）
# ③ 用内连接将t1和t2连接起来，通过t1.hire_date=t2.max_date
最终结果：
mysql> select t1.id,t1.name,t1.sex,t1.age,t1.salary,t1.office,t2.* from emp as t1
    ->  inner join (select post,max(hire_date) as max_date from emp group by post) as t2 where
    ->  t1.hire_date=t2.max_date;
+----+--------+--------+-----+---------+--------+-----------+------------+
| id | name   | sex    | age | salary  | office | post      | max_date   |
+----+--------+--------+-----+---------+--------+-----------+------------+
|  1 | jason  | male   |  18 |    6600 |    401 | 外交部    | 2017-03-01 |
|  2 | egon   | male   |  78 | 1000000 |    401 | 教学部    | 2015-03-02 |
| 13 | 格格   | female |  28 |    4000 |    402 | 销售部    | 2017-01-27 |
| 14 | 张野   | male   |  28 |   10000 |    403 | 运营部    | 2016-03-11 |
+----+--------+--------+-----+---------+--------+-----------+------------+
 
记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询

额外题：

部门中薪资超过部门平均薪资的员工姓名及薪资

mysql> select t1.name,t1.salary,t1.post,t2.avg_salary from emp t1
    ->  inner join (select post,avg(salary) as avg_salary from emp group by post) as t2
    ->  where t1.post=t2.post and t1.salary>t2.avg_salary;
+-----------+---------+-----------+-------------+
| name      | salary  | post      | avg_salary  |
+-----------+---------+-----------+-------------+
| egon      | 1000000 | 教学部    | 151842.8571 |
| 歪歪      |    3000 | 销售部    |   2600.0000 |
| 星星      |    3000 | 销售部    |   2600.0000 |
| 格格      |    4000 | 销售部    |   2600.0000 |
| 程咬金    |   20000 | 运营部    |  16800.0000 |
| 程咬银    |   19000 | 运营部    |  16800.0000 |
| 程咬铜    |   18000 | 运营部    |  16800.0000 |
| 程咬铁    |   17000 | 运营部    |  16800.0000 |
+-----------+---------+-----------+-------------+

栏目列表