Python-数据库连表查询、子查询

连表查询

【实例】通过例子来熟悉连表查询的概念

# 第一步：建表
# 建立英雄职业分类表格
create table classification(
id int,
name varchar(20) 
);

# 英雄属性表格
create table hero(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
cd int,        # 技能时间
cha_id int         # 英雄职业
);

# 插入数据
insert into classification values
(200,'法师'),
(201,'战士'),
(202,'射手'),
(203,'辅助');

insert into hero(name,sex,cd,cha_id) values
('莫甘娜','female',40,200),
('盖伦','male',50,201),
('赵信','male',48,201),
('女警','female',35,202),
('梦魇','male',30,200),
('阿卡丽','female',28,204)
;

笛卡尔积

​ 交叉连接实例：在没有任何条件连接的情况下联合两张表进行查询，结果会以笛卡尔积的形式显示

mysql> select * from hero,classification;
+----+-----------+--------+------+--------+------+--------+
| id | name      | sex    | cd   | cha_id | id   | name   |
+----+-----------+--------+------+--------+------+--------+
|  1 | 莫甘娜    | female |   40 |    200 |  200 | 法师   |
|  1 | 莫甘娜    | female |   40 |    200 |  201 | 战士   |
|  1 | 莫甘娜    | female |   40 |    200 |  202 | 射手   |
|  1 | 莫甘娜    | female |   40 |    200 |  203 | 辅助   |
|  2 | 盖伦      | male   |   50 |    201 |  200 | 法师   |
|  2 | 盖伦      | male   |   50 |    201 |  201 | 战士   |
|  2 | 盖伦      | male   |   50 |    201 |  202 | 射手   |
|  2 | 盖伦      | male   |   50 |    201 |  203 | 辅助   |
|  3 | 赵信      | male   |   48 |    201 |  200 | 法师   |
|  3 | 赵信      | male   |   48 |    201 |  201 | 战士   |
|  3 | 赵信      | male   |   48 |    201 |  202 | 射手   |
|  3 | 赵信      | male   |   48 |    201 |  203 | 辅助   |
|  4 | 女警      | female |   35 |    202 |  200 | 法师   |
|  4 | 女警      | female |   35 |    202 |  201 | 战士   |
|  4 | 女警      | female |   35 |    202 |  202 | 射手   |
|  4 | 女警      | female |   35 |    202 |  203 | 辅助   |
|  5 | 梦魇      | male   |   30 |    200 |  200 | 法师   |
|  5 | 梦魇      | male   |   30 |    200 |  201 | 战士   |
|  5 | 梦魇      | male   |   30 |    200 |  202 | 射手   |
|  5 | 梦魇      | male   |   30 |    200 |  203 | 辅助   |
|  6 | 阿卡丽    | female |   28 |    204 |  200 | 法师   |
|  6 | 阿卡丽    | female |   28 |    204 |  201 | 战士   |
|  6 | 阿卡丽    | female |   28 |    204 |  202 | 射手   |
|  6 | 阿卡丽    | female |   28 |    204 |  203 | 辅助   |
+----+-----------+--------+------+--------+------+--------+
24 rows in set (0.00 sec)

​ 【结论】实际使用时应该是不会希望在笛卡尔积表中查找信息，毕竟有大量的信息冗余。我们希望的是能够通过一种连接关系，将两张有关系的表组合成一张表。

内连接

​ 内连接，实现两张表中相互匹配的行进行连接，就像hero表中的cha_id与classification里的id是相匹配，匹配格式如下：

select  *  from   表1  inner join  表2  on   条件
select * from 表1，表2，表3 where 表1.字段1=表2.字段1 and 表2.字段2 = 表3.字段1

mysql> select * from hero inner join classification on hero.cha_id = classification.id;
# 如果表格的长度比较长不方便操作的话，给他重命名以下
mysql> select * from hero inner join classification as cla on hero.cha_id = cla.id;
+----+-----------+--------+------+--------+------+--------+
| id | name      | sex    | cd   | cha_id | id   | name   |
+----+-----------+--------+------+--------+------+--------+
|  1 | 莫甘娜    | female |   40 |    200 |  200 | 法师   |
|  2 | 盖伦      | male   |   50 |    201 |  201 | 战士   |
|  3 | 赵信      | male   |   48 |    201 |  201 | 战士   |
|  4 | 女警      | female |   35 |    202 |  202 | 射手   |
|  5 | 梦魇      | male   |   30 |    200 |  200 | 法师   |
+----+-----------+--------+------+--------+------+--------+

左外连接

连接的两张表中以左侧的表为准，右侧的表匹配左侧的表，优先显示左表全部记录，显示顺序以左侧为主

用法

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

mysql> select * from hero left join classification as cla on  hero.cha_id = cla.id;
+----+-----------+--------+------+--------+------+--------+
| id | name      | sex    | cd   | cha_id | id   | name   |
+----+-----------+--------+------+--------+------+--------+
|  1 | 莫甘娜    | female |   40 |    200 |  200 | 法师   |
|  5 | 梦魇      | male   |   30 |    200 |  200 | 法师   |
|  2 | 盖伦      | male   |   50 |    201 |  201 | 战士   |
|  3 | 赵信      | male   |   48 |    201 |  201 | 战士   |
|  4 | 女警      | female |   35 |    202 |  202 | 射手   |
|  6 | 阿卡丽    | female |   28 |    204 | NULL | NULL   |
+----+-----------+--------+------+--------+------+--------+

【结论】左侧的表全部显示，右侧匹配左侧，缺失项为NULL

右外连接

连接的两张表中以右侧的表为准，左侧的表匹配右侧的表，优先显示左表全部记录，显示顺序以左侧为主

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

mysql> select * from hero right join classification as cla on  hero.cha_id = cla.id;
+------+-----------+--------+------+--------+------+--------+
| id   | name      | sex    | cd   | cha_id | id   | name   |
+------+-----------+--------+------+--------+------+--------+
|    1 | 莫甘娜    | female |   40 |    200 |  200 | 法师   |
|    2 | 盖伦      | male   |   50 |    201 |  201 | 战士   |
|    3 | 赵信      | male   |   48 |    201 |  201 | 战士   |
|    4 | 女警      | female |   35 |    202 |  202 | 射手   |
|    5 | 梦魇      | male   |   30 |    200 |  200 | 法师   |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 辅助   |
+------+-----------+--------+------+--------+------+--------+

全外连接

根据匹配项连接两张表，显示所有内容，缺失项以NULL填充

select  *  from   表1  full  join  表2  on   条件

【注意】目前各个DBMS对外连接的支持星泪不尽相同，MySQL仅仅支持左右两个外连接，并不支持全外连接

在MySQL中实现全外连接

select  *  from   表1 right join  表2  on   条件
union
select  *  from   表1  left  join  表2  on   条件
+------+-----------+--------+------+--------+------+--------+
| id   | name      | sex    | cd   | cha_id | id   | name   |
+------+-----------+--------+------+--------+------+--------+
|    1 | 莫甘娜    | female |   40 |    200 |  200 | 法师   |
|    5 | 梦魇      | male   |   30 |    200 |  200 | 法师   |
|    2 | 盖伦      | male   |   50 |    201 |  201 | 战士   |
|    3 | 赵信      | male   |   48 |    201 |  201 | 战士   |
|    4 | 女警      | female |   35 |    202 |  202 | 射手   |
|    6 | 阿卡丽    | female |   28 |    204 | NULL | NULL   |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 辅助   |
+------+-----------+--------+------+--------+------+--------+

符合条件的连接查询

# 找出cd时间大于40的英雄名称以及该英雄的职业属性
mysql> select hero.name,cla.name,hero.cd from hero inner join classification as cla on hero.cha_id = cla.id where cd> 40;
+--------+--------+------+
| name   | name   | cd   |
+--------+--------+------+
| 盖伦   | 战士   |   50 |
| 赵信   | 战士   |   48 |
+--------+--------+------+

# 以内连接的方式查询hero和classification表，并且以age字段的升序方式显示
mysql> select * from hero inner join classification as cla on hero.cha_id = cla.id order by cd asc;
+----+-----------+--------+------+--------+------+--------+
| id | name      | sex    | cd   | cha_id | id   | name   |
+----+-----------+--------+------+--------+------+--------+
|  5 | 梦魇      | male   |   30 |    200 |  200 | 法师   |
|  4 | 女警      | female |   35 |    202 |  202 | 射手   |
|  1 | 莫甘娜    | female |   40 |    200 |  200 | 法师   |
|  3 | 赵信      | male   |   48 |    201 |  201 | 战士   |
|  2 | 盖伦      | male   |   50 |    201 |  201 | 战士   |
+----+-----------+--------+------+--------+------+--------+

9.10 子查询

• 将一个查询语句前台到另一个查询语句中；
• 内层查询语句的结果可以作为外层查询语句的条件；
• 子查询中可以包含IN，NOT IN，ANY，ALL，EXISTS和 NOT　EXISTS等关键字；EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False，当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
• 还可以包含比较运算符　＝　！＝　＞　＜

【举例1】

# 带in关键字的子查询
# 查询cd时间在40秒以上的英雄职业有哪些

第一步：通过hero查询cd时间在40秒的职业id
mysql> select cha_id from hero where cd>40;
+--------+
| cha_id |
+--------+
|    201 |
|    201 |
+--------+
第二步：从第一步的查询到的id在classification里找到对应的name
mysql> select name from classification where id in (201);
+--------+
| name   |
+--------+
| 战士   |
+--------+

# 将两部结合在一起
mysql> select name from classification where id in (select cha_id from hero where cd>40);
+--------+
| name   |
+--------+
| 战士   |
+--------+

【举例2】

# 查询平均cd在30以上的英雄职业,平均cd
【方法一】先查平均cd>30的职业id，平均cd，查出结果在之后再连表
mysql> select c.name,avg_cd from classification as c inner join (select avg(cd) as avg_cd ,cha_id from hero group by cha_id having avg(cd) > 30 )as tem on tem.cha_id = c.id;
+--------+---------+
| name   | avg_cd  |
+--------+---------+
| 法师   | 35.0000 |
| 战士   | 49.0000 |
| 射手   | 35.0000 |
+--------+---------+
3 rows in set (0.00 sec)

【方法二】先查各职业的平均cd，查出结果在之后再连表筛选
mysql> select c.name,avg_cd from classification as c inner join (select avg(cd) as avg_cd,cha_id from hero group by cha_id) as tem on tem.cha_id = c.id where avg_cd > 30;
+--------+---------+
| name   | avg_cd  |
+--------+---------+
| 法师   | 35.0000 |
| 战士   | 49.0000 |
| 射手   | 35.0000 |
+--------+---------+

【注意】

如果最终需要的结果只出现在一张表中,可以用子查询解决问题

如果最终需要的结果出现在两张表中,那么最后用的一定是连表查询

能用连表的时候就用连表，效率比子查询快

【举例三】

# 运算符的使用
# 查看法师职业有谁
mysql> select name from hero where cha_id = (select id from classification where name = '法师');
+-----------+
| name      |
+-----------+
| 莫甘娜    |
| 梦魇      |
+-----------+

# 查询大于所有人平均cd的英雄名与cd
mysql> select name,cd from hero where cd > (select avg(cd) from hero);
+-----------+------+
| name      | cd   |
+-----------+------+
| 莫甘娜    |   40 |
| 盖伦      |   50 |
| 赵信      |   48 |
+-----------+------+

# 扩展练习：查询大于职业平均cd的英雄名、cd
mysql> select hero.name,hero.cd from hero inner join (select cha_id,avg(cd) as avg_cd from hero group by cha_id) as tem on tem.cha_id = hero.cha_id where tem.avg_cd <hero.cd;
+-----------+------+
| name      | cd   |
+-----------+------+
| 莫甘娜    |   40 |
| 盖伦      |   50 |
+-----------+------+

【举例四】

# 带EXISTS关键字的子查询
# EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记# 录。而是返回一个真假值。True或False当返回True时，外层查询语句将进行查询 # 当返回值为False时，外层查询语句不进行查询

# classification表中存在203，True
mysql> select * from hero where exists (select id from classification where id = 203);
+----+-----------+--------+------+--------+
| id | name      | sex    | cd   | cha_id |
+----+-----------+--------+------+--------+
|  1 | 莫甘娜    | female |   40 |    200 |
|  2 | 盖伦      | male   |   50 |    201 |
|  3 | 赵信      | male   |   48 |    201 |
|  4 | 女警      | female |   35 |    202 |
|  5 | 梦魇      | male   |   30 |    200 |
|  6 | 阿卡丽    | female |   28 |    204 |
+----+-----------+--------+------+--------+

# classification表中存在205，False
mysql> select * from hero where exists (select id from classification where id = 205);
Empty set (0.00 sec)

总结

select干了什么：首先是根据条件对每行记录进行检索，再根据其他条件进行字段筛选

数据库数据导入

# 准备表、记录
mysql> create database 数据库名;
mysql> use 数据库名;
mysql> source init.sql路径；