Day 41多表查询以及pymysql相关操作完善

最新推荐文章于 2022-05-31 15:48:01 发布

A1L__

最新推荐文章于 2022-05-31 15:48:01 发布

阅读量173

点赞数

文章标签： mysql 数据库 python

本文链接：https://blog.csdn.net/A1L__/article/details/108472715

版权

Day 01

多表连接查询

方案一：链表把多张物理表合并成一张虚拟表，再进行后续查询

准备表 employee 和 department

create table emp(id int primary key auto_increment,
name varchar(15),
gender enum("male", "female")  default "male" not null,
age int,
dep_id int); 

create table dep(id int,
name varcahr(15));

插入数据

# 职位数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

# 职员数据
insert into emp(name,gender, age, dep_id) values("小猪佩奇", "male", 7, 200),
("天尊杨戬", "male", 88, 201),
("森下下士", "male", 38, 201),
("孙燕姿", "female", 18, 202),
("地爆天星", "female", 19, 200),
("天山新泰罗", "male", 18, 204);

查看数据结构

select * from dep;
+------+----------+
| id   | name     |
+------+----------+
|  200 | 技术     |
|  201 | 人力资源 |
|  203 | 销售     |
|  204 | 运营     |
+------+----------+


select * from emp;
+----+------------+--------+------+--------+
| id | name       | gender | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | 小猪佩奇   | male   |    7 |    200 |
|  2 | 天尊杨戬   | male   |   88 |    201 |
|  3 | 森下下士   | male   |   38 |    201 |
|  4 | 孙燕姿     | female |   18 |    202 |
|  5 | 地爆天星   | female |   19 |    200 |
|  6 | 天山新泰罗 | male   |   18 |    204 |
+----+------------+--------+------+--------+

多表连接查询

外链语法

select 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;

交叉连接：不适用任何匹配条件。生成笛卡尔积

概念：不带where条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积（例如：dep和emp，返回4*4=16条记录），如果带where，返回或显示的是匹配的行数。

# #上述sql等同于
mysql> select emp.id,emp.name,emp.age,emp.gender,dep.name from emp,dep #where emp.dep_id=dep.id;

select * from emp, dep;
+----+------------+--------+------+--------+------+----------+
| id | name       | gender | age  | dep_id | id   | name     |
+----+------------+--------+------+--------+------+----------+
|  1 | 小猪佩奇   | male   |    7 |    200 |  200 | 技术     |
|  1 | 小猪佩奇   | male   |    7 |    200 |  201 | 人力资源 |
|  1 | 小猪佩奇   | male   |    7 |    200 |  203 | 销售     |
|  1 | 小猪佩奇   | male   |    7 |    200 |  204 | 运营     |
|  2 | 天尊杨戬   | male   |   88 |    201 |  200 | 技术     |
|  2 | 天尊杨戬   | male   |   88 |    201 |  201 | 人力资源 |
|  2 | 天尊杨戬   | male   |   88 |    201 |  203 | 销售     |
|  2 | 天尊杨戬   | male   |   88 |    201 |  204 | 运营     |
|  3 | 森下下士   | male   |   38 |    201 |  200 | 技术     |
|  3 | 森下下士   | male   |   38 |    201 |  201 | 人力资源 |
|  3 | 森下下士   | male   |   38 |    201 |  203 | 销售     |
|  3 | 森下下士   | male   |   38 |    201 |  204 | 运营     |
|  4 | 孙燕姿     | female |   18 |    202 |  200 | 技术     |
|  4 | 孙燕姿     | female |   18 |    202 |  201 | 人力资源 |
|  4 | 孙燕姿     | female |   18 |    202 |  203 | 销售     |
|  4 | 孙燕姿     | female |   18 |    202 |  204 | 运营     |
|  5 | 地爆天星   | female |   19 |    200 |  200 | 技术     |
|  5 | 地爆天星   | female |   19 |    200 |  201 | 人力资源 |
|  5 | 地爆天星   | female |   19 |    200 |  203 | 销售     |
|  5 | 地爆天星   | female |   19 |    200 |  204 | 运营     |
|  6 | 天山新泰罗 | male   |   18 |    204 |  200 | 技术     |
|  6 | 天山新泰罗 | male   |   18 |    204 |  201 | 人力资源 |
|  6 | 天山新泰罗 | male   |   18 |    204 |  203 | 销售     |
|  6 | 天山新泰罗 | male   |   18 |    204 |  204 | 运营     |
+----+------------+--------+------+--------+------+----------+

内连接：只连接匹配的行

找到两张表共有部分，相当于利用条件从笛卡尔积结果中筛选出正确的结果

select * from emp inner join dep on dep.id=emp.dep_id;
+----+------------+--------+------+--------+------+----------+
| id | name       | gender | age  | dep_id | id   | name     |
+----+------------+--------+------+--------+------+----------+
|  1 | 小猪佩奇   | male   |    7 |    200 |  200 | 技术     |
|  2 | 天尊杨戬   | male   |   88 |    201 |  201 | 人力资源 |
|  3 | 森下下士   | male   |   38 |    201 |  201 | 人力资源 |
|  5 | 地爆天星   | female |   19 |    200 |  200 | 技术     |
|  6 | 天山新泰罗 | male   |   18 |    204 |  204 | 运营     |
+----+------------+--------+------+--------+------+----------+
# 相当于 select * from emp,dep where dep.id=emp.dep_id;

但是我们发现他并没有完整匹配出来 比如 emp中没有203职位，但是他就没有匹配出来 也并没有NULL值

外链接

左连接–优先显示左表全部记录

select * from emp left join dep on emp.dep_id = dep.id;

select * from emp left join dep on emp.dep_id = dep.id ;
+----+------------+--------+------+--------+------+----------+
| id | name       | gender | age  | dep_id | id   | name     |
+----+------------+--------+------+--------+------+----------+
|  1 | 小猪佩奇   | male   |    7 |    200 |  200 | 技术     |
|  5 | 地爆天星   | female |   19 |    200 |  200 | 技术     |
|  2 | 天尊杨戬   | male   |   88 |    201 |  201 | 人力资源 |
|  3 | 森下下士   | male   |   38 |    201 |  201 | 人力资源 |
|  6 | 天山新泰罗 | male   |   18 |    204 |  204 | 运营     |
|  4 | 孙燕姿     | female |   18 |    202 | NULL | NULL     |
+----+------------+--------+------+--------+------+----------+

哈哈  这次找到了 没有匹配到的 202职位项，但是右边的 203 好像没有

右链接–优先显示右表全部记录

select * from emp right join dep on emp.dep_id=dep.id;

+------+------------+--------+------+--------+------+----------+
| id   | name       | gender | age  | dep_id | id   | name     |
+------+------------+--------+------+--------+------+----------+
|    1 | 小猪佩奇   | male   |    7 |    200 |  200 | 技术     |
|    2 | 天尊杨戬   | male   |   88 |    201 |  201 | 人力资源 |
|    3 | 森下下士   | male   |   38 |    201 |  201 | 人力资源 |
|    5 | 地爆天星   | female |   19 |    200 |  200 | 技术     |
|    6 | 天山新泰罗 | male   |   18 |    204 |  204 | 运营     |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 销售     |
+------+------------+--------+------+--------+------+----------+

这次找到了 右边的203.....

全外连接：显示左右两个表全部记录

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
select * from emp full join dep on emp.dep_id = dep.id;  # 不支持
#强调：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       | gender | age  | dep_id | id   | name     |
+------+------------+--------+------+--------+------+----------+
|    1 | 小猪佩奇   | male   |    7 |    200 |  200 | 技术     |
|    5 | 地爆天星   | female |   19 |    200 |  200 | 技术     |
|    2 | 天尊杨戬   | male   |   88 |    201 |  201 | 人力资源 |
|    3 | 森下下士   | male   |   38 |    201 |  201 | 人力资源 |
|    6 | 天山新泰罗 | male   |   18 |    204 |  204 | 运营     |
|    4 | 孙燕姿     | female |   18 |    202 | NULL | NULL     |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 销售     |
+------+------------+--------+------+--------+------+----------+
# 这样达到了全外连接的效果 所有不匹配项都能够提取出来

符合条件连接查询

Question 1

以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门。

select emp.id, emp.name, emp.age, emp.gender, dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id 
where emp.age > 25;

+----+----------+------+--------+----------+
| id | name     | age  | gender | dep_name |
+----+----------+------+--------+----------+
|  2 | 天尊杨戬 |   88 | male   | 人力资源 |
|  3 | 森下下士 |   38 | male   | 人力资源 |
+----+----------+------+--------+----------+

Question 2

以内连接的方式查询emp和dep表，并且以age字段的升序方式显示

select emp.id, emp.name, emp.age, dep.name from emp, dep
where emp.dep_id = dep.id
order by age desc;

Question 3

查看不足1人的部门名(子查询得到的是有人的部门id)

select dep.name, count(emp.dep_id) from emp, dep 
where emp.dep_id = dep.id
group by dep.name
having count(emp.dep_id)<=1;
----------------------------------
+------+-------------------+
| name | count(emp.dep_id) |
+------+-------------------+
| 运营 |                 1 |
+------+-------------------+

子查询

方案二：从一张表中查询出结果，用该结果作为查询下一张表的过滤条件

1：子查询是将一个查询语句嵌套在另一个查询语句中:
2：内层查询语句查询结果，可以为外层查询语句提供查询条件
3：子查询中可以包含：in、not int、any、all、exist和not exist
4：还可以包含比较运算符：=、！=、>、<等

带比较运算符的子查询

带in关键字的子查询

# 查询平均年龄在25岁以上的部门
select id, name from dep
where id not in
(select dep_id from emp group by dep_id
having avg(age)>25);
--------------------------
+------+----------+
| id   | name     |
+------+----------+
|  201 | 人力资源 |
+------+----------+

# 查看技术部员工姓名
select name, gender, dep_id  from emp
where dep_id in 
(select id from dep where name = "技术");
----------------------------------------
+----------+--------+--------+
| name     | gender | dep_id |
+----------+--------+--------+
| 小猪佩奇 | male   |    200 |
| 地爆天星 | female |    200 |
+----------+--------+--------+

# 查看不足一人的部门名字（子查询得到的是有人的部门id）
select id,name from dep 
where id not in 
(select distinct dep_id from emp);
---------------------------------------
+------+------+
| id   | name |
+------+------+
|  203 | 销售 |
+------+------+

not in 无法处理null的值，即子查询中如果存在null的值， not in 将无法处理

insert into emp(name, gender, age) values("挺甜一郎", "female", 20);
select * from emp;
-------------------------------------
+----+------------+--------+------+--------+
| id | name       | gender | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | 小猪佩奇   | male   |    7 |    200 |
|  2 | 天尊杨戬   | male   |   88 |    201 |
|  3 | 森下下士   | male   |   38 |    201 |
|  4 | 孙燕姿     | female |   18 |    202 |
|  5 | 地爆天星   | female |   19 |    200 |
|  6 | 天山新泰罗 | male   |   18 |    204 |
|  7 | 挺甜一郎   | female |   20 |   NULL |
+----+------------+--------+------+--------+

select *  from dep where id not in 
(select distinct dep_id from emp);  # 句意是找出dep中没有在emp中出现的部门（203）
# 这里会出现一个NULL值 就是挺甜一郎
-------------------------------------------
Empty set (0.00 sec)

解决方案
select * from dep where id not in
(select distinct dep_id from emp
where dep_id is not null);  # 由于加上了限制条件所以并不会出现null值 并且有null值的条件本来就不在筛选范围内
------------------------------------------
+------+------+
| id   | name |
+------+------+
|  203 | 销售 |
+------+------+

# in 还可以与固定的集合匹配
select * from emp where dep_id in (201,204);
-------------------------------------------
+----+------------+--------+------+--------+
| id | name       | gender | age  | dep_id |
+----+------------+--------+------+--------+
|  2 | 天尊杨戬   | male   |   88 |    201 |
|  3 | 森下下士   | male   |   38 |    201 |
|  6 | 天山新泰罗 | male   |   18 |    204 |
+----+------------+--------+------+--------+

也就是说“=any”等价于 IN 运算符
而“<>all”则等价于 NOT IN 运算符 5.6中 != 也是可以使用的

带any关键字的子查询

Any：只要有一条数据满足条件，整个条件成立

在SQL中any和some是同义词，some的用法和功能与any一摸一样

# ANY 和 IN 运算符不同之处1
ANY 必须和其他的比较运算符共同使用，而且ANY必须将比较运算符（= < >）放在 ANY 关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是 ANY 在英文中所表示的意义

select * from emp where dep_id > any
(select id from dep where  id=200);  # 找出emp表中dep_id大于的200的记录
------------------------------------
+----+------------+--------+------+--------+
| id | name       | gender | age  | dep_id |
+----+------------+--------+------+--------+
|  2 | 天尊杨戬   | male   |   88 |    201 |
|  3 | 森下下士   | male   |   38 |    201 |
|  4 | 孙燕姿     | female |   18 |    202 |
|  6 | 天山新泰罗 | male   |   18 |    204 |
+----+------------+--------+-- 
使用 IN 和使用 =ANY运算符得到的结果是一致的  相当于修改了判断条件 =any 全选
select * from dep where id >any
(select dep_id from emp where dep_id is not null);
-----------------------------------------
+------+----------+
| id   | name     |
+------+----------+
|  200 | 技术     |
|  201 | 人力资源 |
|  204 | 运营     |
+------+----------+

带all关键字的子查询

all同any类似，只不过all表示的是所有，any表示任一

# 找出部门中id大于200的部门名
select * from dep where id > all
(select dep_id from emp where dep_id=200);
-----------------------------------------
+------+----------+
| id   | name     |
+------+----------+
|  201 | 人力资源 |
|  203 | 销售     |
|  204 | 运营     |
+------+----------+

带比较运算符的子查询

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

#查询大于所有人平均年龄的员工名与年龄
select * from emp where age >
(select avg(age) from emp);
-----------------------------------------
+----+----------+--------+------+--------+
| id | name     | gender | age  | dep_id |
+----+----------+--------+------+--------+
|  2 | 天尊杨戬 | male   |   88 |    201 |
|  3 | 森下下士 | male   |   38 |    201 |
+----+----------+--------+------+--------+

带exists关键字的子查询

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

in与exists的比较

！！！！！！当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率！！！！！！
in 是将子查询语句中所有的结果全部列出然后开始匹配 时间复杂度为 m * n  # m为第一个判断条件
exists 是先将第一个判断条件执行一次，然后将子查询再查询一片 时间复杂度的 n**m

==============================exists==============================
# exists
exists后面一般都是子查询，后面的子查询被称做相关子查询（即与主语句相关），当子查询返回行数时，exists条件返回true，
否则返回false，exists是不返回列表的值的，exists只在乎括号里的数据能不能查找出来，是否存在这样的记录。

not in 和 exists的比较

!!!!!!not exists查询的效率远远高与not in查询的效率。!!!!!!

==============================not in==============================
not in()子查询的执行顺序是：
为了证明not in成立，即找不到，需要一条一条地查询表，符合要求才返回子查询的结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。
                
==============================not exists==============================
not exists：
如果主查询表中记录少，子查询表中记录多，并有索引。
例如:查询那些班级中没有学生的班级
select * from class

where not exists

(select * from student where student.cid = class.cid)

not exists的执行顺序是：
在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。

Pymysql初识

本地数据库的链接

在 py 文件中引入 pymysql 模块

import pymysql

链接数据库对象：调用 pymysql.connect() 方法

conn=pymysql.connect(参数列表)

参数:

host - 数据库服务器所在的主机
user - 登录的用户名
password - 要使用的密码。
database - 要使用的数据库，None不使用特定的数据库。
port - 要使用的MySQL端口，默认通常都可以。（默认值：3306）
charset - 你要使用的Charset。
db - 数据库的别名。（与MySQLdb兼容）
passwd - 密码的别名。（与MySQLdb兼容）
connect_timeout - 连接时抛出异常之前的超时。（默认值：10，最小值：1，最大值：31536000）
read_timeout - 以秒为单位读取连接的超时（默认值：无 - 无超时）
write_timeout - 以秒为单位写入连接的超时（默认值：无 - 无超时）

2. 数据库操作方法

对象的方法

connect()

链接数据库
close()

发送数据关闭与数据库的链接
commit()

提交并保存数据
cursor(cursor=None)

创建一个新游标，用于执行sql语句并获得结果
rollback()

回滚当前事务。
select_db(db)

设置当前数据库。

参数:db – 数据库的名称.

import pymysql

# 获取连接
connection = pymysql.connect(host='192.168.31.89',     # MySQL所在电脑的 ip
                             port=3306,                 # 端口
                             user='windows',            # 用户名
                             password='123456',         # 密码
                             db='python',               # 数据库名
                             charset='utf8')            # 字符编码
print(connection)

示例

import pymysql  # 导入pymysql模块

connect = pymysql.connect("127.0.0.1", port=3306, user='root', password='xxxxx',
                          database="python", charset="utf8mb4")  # 链接本地数据库

coursor = connect.cursor()  # 用于执行sql语句，使用频度最高的语句为select、insert、update、delete

# 创建一个数据库完善相关字段
# coursor.execute("""create table mydata(id int primary key auto_increment,
#                     name varchar(20),
#                     gender enum("男", "女"));""")

# 插入基本数据
# coursor.execute("""insert into mydata(name, gender) values("毛成", "男"),
#                     ("李二狗", "男"),("小李子", "女");""")

# 接受相关参数
name = input("New_name >>>>:").strip()
gender = input("Gender >>>>:").strip()


# 封装成函数
def write_simple_data(name, gender):
    sql = f'insert into mydata(name, gender) values("{name}", "{gender}")'
    # coursor.execute('insert into mydata(name, gender) values(%s, %s)', (name, gender))
    coursor.execute(sql)


write_simple_data(name, gender)

connect.commit()  # 提交事务 不执行事务无法保存
coursor.close()  # 关闭游标对象
connect.close()  # 发送数据关闭与数据库的链接

pymysql再识

方法

以字典形式打印
cursor = connect.cursor(cursor=pymysql.cursors.DictCursor) 创建游标对象
以元组形式打印
cursor = connect.cursor(cursor=pymysql.cursors.Cursor) 默认

import pymysql

connect = pymysql.connect(host="127.0.0.1",
                          port=3306,
                          user="root",
                          password="200830",
                          database="python",
                          charset="utf8mb4")

cursor = connect.cursor()  # 创建游标对象

# 增
# cursor.execute('insert into mydata(name, gender) values (%s, %s)', ["唐老鸭","男"])

# 删
# cursor.execute("delete from student where id >6")

# 改
cursor.execute('update mydata set gender="女" where name="唐老鸭"')

# 查
rows = cursor.execute('select * from mydata')
# print(rows)  # 这里只能看到数据库中数据总量
# 查询数据需要用专门的函数
# 读取一行
# row1 = cursor.fetchone()  # 本质是一个生成器 从头开始选取一行
# row2 = cursor.fetchone()
# print(f'读取一行{row1, row2}')
#
# # 读取多行
# row_s = cursor.fetchmany(3)  # 会接着指针之后开始读取  此时指针到了2
# print(f'读取多行{row_s}')
#
# # 读取所有
# row_all = cursor.fetchall()  # 会接着指针之后开始读取  此时指针到了5
# print(f"读取所有{row_all}")
#
# print(cursor.lastrowid)  # 获取插入的最后一条数据的自增ID lastrowid()方法 插入失败时候自增id也会改变

cursor.scroll(3,mode='absolute')  # 相对绝对位置移动
print(f'读取一行{cursor.fetchone()}')
print(f'读取所有{cursor.fetchall()}')
cursor.scroll(0,mode='absolute')  # 相对绝对位置移动
print(f"读取所有{cursor.fetchall()}")


# cursor.fetchone()
# cursor.scroll(1,mode='relative') # 相对当前位置移动
# cursor.scroll(0,mode='absolute') # 相对当前位置移动
# print(cursor.fetchone())

# conn.commit()
# cursor.close()
# conn.close()

A1L__

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Day 41多表查询以及pymysql相关操作完善

Day 01多表连接查询方案一：链表把多张物理表合并成一张虚拟表，再进行后续查询准备表 employee 和 departmentcreate table emp(id int primary key auto_increment,name varchar(15),gender enum("male", "female") default "male" not null,age int,dep_id int); create table dep(id int,name varc
复制链接

扫一扫