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路径;
仅供参考,欢迎指正