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路径；

巴特西