数据库之多表联合查询、符合条件连接查询、子查询及pymysql模块

1、多表联合查询

1.1、链表

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

# dep表
create table dep(
    id int,
    name varchar(20) 
);

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

# emp表
create table emp(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);

# 插入数据
insert into emp(name,sex,age,dep_id) values
('allen','male',18,200),
('lucy','female',48,201),
('lily','male',38,201),
('jack','female',28,202),
('tom','male',18,200),
('andy','female',18,204);

# 查看表结构
mysql> desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int                   | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | YES  |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int                   | YES  |     | NULL    |                |
| dep_id | int                   | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> desc dep;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 查看数据
mysql> select * from emp;
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | allen | male   |   18 |    200 |
|  2 | lucy  | female |   48 |    201 |
|  3 | lily  | male   |   38 |    201 |
|  4 | jack  | female |   28 |    202 |
|  5 | tom   | male   |   18 |    200 |
|  6 | andy  | female |   18 |    204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)

mysql> select * from dep;
+------+----------+
| id   | name     |
+------+----------+
|  200 | 技术     |
|  201 | 人力资源 |
|  202 | 销售     |
|  203 | 运营     |
+------+----------+
4 rows in set (0.00 sec)

1.1.1、交叉链接

不适用任何匹配条件, 生成笛卡尔积

mysql> select * from emp,dep;
+----+-------+--------+------+--------+------+----------+
| id | name  | sex    | age  | dep_id | id   | name     |
+----+-------+--------+------+--------+------+----------+
|  1 | allen | male   |   18 |    200 |  200 | 技术     |
|  1 | allen | male   |   18 |    200 |  201 | 人力资源 |
|  1 | allen | male   |   18 |    200 |  202 | 销售     |
|  1 | allen | male   |   18 |    200 |  203 | 运营     |
|  2 | lucy  | female |   48 |    201 |  200 | 技术     |
|  2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
|  2 | lucy  | female |   48 |    201 |  202 | 销售     |
|  2 | lucy  | female |   48 |    201 |  203 | 运营     |
|  3 | lily  | male   |   38 |    201 |  200 | 技术     |
|  3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
|  3 | lily  | male   |   38 |    201 |  202 | 销售     |
|  3 | lily  | male   |   38 |    201 |  203 | 运营     |
|  4 | jack  | female |   28 |    202 |  200 | 技术     |
|  4 | jack  | female |   28 |    202 |  201 | 人力资源 |
|  4 | jack  | female |   28 |    202 |  202 | 销售     |
|  4 | jack  | female |   28 |    202 |  203 | 运营     |
|  5 | tom   | male   |   18 |    200 |  200 | 技术     |
|  5 | tom   | male   |   18 |    200 |  201 | 人力资源 |
|  5 | tom   | male   |   18 |    200 |  202 | 销售     |
|  5 | tom   | male   |   18 |    200 |  203 | 运营     |
|  6 | andy  | female |   18 |    204 |  200 | 技术     |
|  6 | andy  | female |   18 |    204 |  201 | 人力资源 |
|  6 | andy  | female |   18 |    204 |  202 | 销售     |
|  6 | andy  | female |   18 |    204 |  203 | 运营     |
+----+-------+--------+------+--------+------+----------+
24 rows in set (0.00 sec)

1.1.2、内链接

找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果,保留两张表有对应关系的记录

mysql> select * from emp,dep where emp.dep_id=dep.id;
+----+-------+--------+------+--------+------+----------+
| id | name  | sex    | age  | dep_id | id   | name     |
+----+-------+--------+------+--------+------+----------+
|  1 | allen | male   |   18 |    200 |  200 | 技术     |
|  2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
|  3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
|  4 | jack  | female |   28 |    202 |  202 | 销售     |
|  5 | tom   | male   |   18 |    200 |  200 | 技术     |
+----+-------+--------+------+--------+------+----------+
5 rows in set (0.00 sec)

# 等同于如下操作
mysql> select * from emp inner join dep on emp.dep_id=dep.id;
+----+-------+--------+------+--------+------+----------+
| id | name  | sex    | age  | dep_id | id   | name     |
+----+-------+--------+------+--------+------+----------+
|  1 | allen | male   |   18 |    200 |  200 | 技术     |
|  2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
|  3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
|  4 | jack  | female |   28 |    202 |  202 | 销售     |
|  5 | tom   | male   |   18 |    200 |  200 | 技术     |
+----+-------+--------+------+--------+------+----------+
5 rows in set (0.00 sec)

1.1.3、左链接

在内连接的基础上增加左边有右边没有的结果

mysql> select * from emp left join dep on emp.dep_id=dep.id;
+----+-------+--------+------+--------+------+----------+
| id | name  | sex    | age  | dep_id | id   | name     |
+----+-------+--------+------+--------+------+----------+
|  1 | allen | male   |   18 |    200 |  200 | 技术     |
|  5 | tom   | male   |   18 |    200 |  200 | 技术     |
|  2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
|  3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
|  4 | jack  | female |   28 |    202 |  202 | 销售     |
|  6 | andy  | female |   18 |    204 | NULL | NULL     |
+----+-------+--------+------+--------+------+----------+
6 rows in set (0.00 sec)

1.1.4、右链接

在内连接的基础上增加右边有左边没有的结果

mysql> select * from emp right join dep on emp.dep_id=dep.id;
+------+-------+--------+------+--------+------+----------+
| id   | name  | sex    | age  | dep_id | id   | name     |
+------+-------+--------+------+--------+------+----------+
|    1 | allen | male   |   18 |    200 |  200 | 技术     |
|    2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
|    3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
|    4 | jack  | female |   28 |    202 |  202 | 销售     |
|    5 | tom   | male   |   18 |    200 |  200 | 技术     |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营     |
+------+-------+--------+------+--------+------+----------+
6 rows in set (0.00 sec)

1.1.5、全外链接

在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
注意: mysql不支持全外连接 full JOIN
强调: mysql可以使用此种方式间接实现全外连接

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 | allen | male   |   18 |    200 |  200 | 技术     |
|    5 | tom   | male   |   18 |    200 |  200 | 技术     |
|    2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
|    3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
|    4 | jack  | female |   28 |    202 |  202 | 销售     |
|    6 | andy  | female |   18 |    204 | NULL | NULL     |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营     |
+------+-------+--------+------+--------+------+----------+
7 rows in set (0.00 sec)

union与union all的区别: union会去掉相同的纪录
mysql> select * from emp left join dep on emp.dep_id=dep.id
    -> union all
    -> select * from emp right join dep on emp.dep_id=dep.id;
+------+-------+--------+------+--------+------+----------+
| id   | name  | sex    | age  | dep_id | id   | name     |
+------+-------+--------+------+--------+------+----------+
|    1 | allen | male   |   18 |    200 |  200 | 技术     |
|    5 | tom   | male   |   18 |    200 |  200 | 技术     |
|    2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
|    3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
|    4 | jack  | female |   28 |    202 |  202 | 销售     |
|    6 | andy  | female |   18 |    204 | NULL | NULL     |
|    1 | allen | male   |   18 |    200 |  200 | 技术     |
|    2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
|    3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
|    4 | jack  | female |   28 |    202 |  202 | 销售     |
|    5 | tom   | male   |   18 |    200 |  200 | 技术     |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营     |
+------+-------+--------+------+--------+------+----------+
12 rows in set (0.00 sec)

2、符合条件连接查询

以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
mysql> select emp.name,dep.name from emp inner join dep on emp.dep_id = dep.id where age > 25;
+------+----------+
| name | name     |
+------+----------+
| lucy | 人力资源 |
| lily | 人力资源 |
| jack | 销售     |
+------+----------+
3 rows in set (0.00 sec)

以内连接的方式查询emp和dep表，并且以age字段的升序方式显示
mysql> select * from emp inner join dep on emp.dep_id=dep.id order by age asc;
+----+-------+--------+------+--------+------+----------+
| id | name  | sex    | age  | dep_id | id   | name     |
+----+-------+--------+------+--------+------+----------+
|  1 | allen | male   |   18 |    200 |  200 | 技术     |
|  5 | tom   | male   |   18 |    200 |  200 | 技术     |
|  4 | jack  | female |   28 |    202 |  202 | 销售     |
|  3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
|  2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
+----+-------+--------+------+--------+------+----------+
5 rows in set (0.00 sec)

3、子查询

1: 子查询是将一个查询语句嵌套在另一个查询语句中。
2: 内层查询语句的查询结果，可以为外层查询语句提供查询条件
3: 子查询中可以包含: IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4: 还可以包含比较运算符: = 、 !=、> 、<等

3.1、带in关键字的子查询

查询平均年龄在25岁以上的部门名
mysql> select * from dep where id in(select dep_id from emp group by dep_id having avg(age) > 25);
+------+----------+
| id   | name     |
+------+----------+
|  201 | 人力资源 |
|  202 | 销售     |
+------+----------+
2 rows in set (0.00 sec)

查看技术部员工姓名
mysql> select * from emp where dep_id in(select id from dep where name="技术");
+----+-------+------+------+--------+
| id | name  | sex  | age  | dep_id |
+----+-------+------+------+--------+
|  1 | allen | male |   18 |    200 |
|  5 | tom   | male |   18 |    200 |
+----+-------+------+------+--------+
2 rows in set (0.05 sec)

查看不足1人的部门名(子查询得到的是有人的部门id)
mysql> select * from dep where id not in (select distinct dep_id from emp);
+------+------+
| id   | name |
+------+------+
|  203 | 运营 |
+------+------+
1 row in set (0.00 sec)

3.2、not in不支持null

mysql> insert into emp values(8,'zhangsan','female',19,null);
Query OK, 1 row affected (0.23 sec)

mysql> select * from emp;
+----+----------+--------+------+--------+
| id | name     | sex    | age  | dep_id |
+----+----------+--------+------+--------+
|  1 | allen    | male   |   18 |    200 |
|  2 | lucy     | female |   48 |    201 |
|  3 | lily     | male   |   38 |    201 |
|  4 | jack     | female |   28 |    202 |
|  5 | tom      | male   |   18 |    200 |
|  6 | andy     | female |   18 |    204 |
|  7 | zhangsan | female |   19 |   NULL |
+----+----------+--------+------+--------+
7 rows in set (0.06 sec)

查询出没有员工的部门
mysql> select * from dep where id not in (select distinct dep_id from emp);  # 该方法查不到结果，因为not in 不支持null
Empty set (0.00 sec)

解决:
mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
+------+------+
| id   | name |
+------+------+
|  203 | 运营 |
+------+------+
1 row in set (0.00 sec)

3.3、带any关键字的子查询

# 在sql中any和some是同义词,some的用法和功能和any一模一样

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

# 查询部门是技术和人力资源的人员信息
mysql> select * from emp where dep_id in (select id from dep where name in ("技术","人力资源"));
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | allen | male   |   18 |    200 |
|  2 | lucy  | female |   48 |    201 |
|  3 | lily  | male   |   38 |    201 |
|  5 | tom   | male   |   18 |    200 |
+----+-------+--------+------+--------+
4 rows in set (0.02 sec)

mysql> select * from emp where dep_id = any (select id from dep where name in ("技术","人力资源"));
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | allen | male   |   18 |    200 |
|  2 | lucy  | female |   48 |    201 |
|  3 | lily  | male   |   38 |    201 |
|  5 | tom   | male   |   18 |    200 |
+----+-------+--------+------+--------+
4 rows in set (0.00 sec)

# 查询部门不是技术和人力资源的人员信息
mysql> select * from emp where dep_id not in (select id from dep where name in ("技术","人力资源"));
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  4 | jack | female |   28 |    202 |
|  6 | andy | female |   18 |    204 |
+----+------+--------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from emp where ! (dep_id = any(select id from dep where name in ("技术","人力资源")));
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  4 | jack | female |   28 |    202 |
|  6 | andy | female |   18 |    204 |
+----+------+--------+------+--------+
2 rows in set, 1 warning (0.06 sec)

结论:
    "=any"等价于in运算符，而"<>any"则等价于not in运算符

# any和in运算符不同之处2
any运算符不能与固定的集合相匹配
mysql> select * from emp where age<any(18,28,38);  # 该用法是错误的

总结:
    any后也跟子查询语句，与in不一样的地方在哪里
	in (子查询语句)
	in (值1,值2,值3)
	而any只能跟子查询语句
	any必须跟比较运算符配合使用

3.4、带all关键字的子查询

# all同any类似,只不过all表示的是所有,any表示任意

查询出那些年龄比所有部门的平均年龄都高的员工
mysql> select * from emp where age > all (select avg(age) from emp where dep_id is not null group by dep_id);
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  2 | lucy | female |   48 |    201 |
+----+------+--------+------+--------+
1 row in set (0.00 sec)

查询出那些年龄比所有部门的平均年龄都低的员工
mysql> select * from emp where age < all (select avg(age) from emp where dep_id is not null group by dep_id);
Empty set (0.05 sec)

查询出那些年龄比任意一个部门的平均年龄低的员工
mysql> select * from emp where age < any (select avg(age) from emp group by dep_id);
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | allen | male   |   18 |    200 |
|  3 | lily  | male   |   38 |    201 |
|  4 | jack  | female |   28 |    202 |
|  5 | tom   | male   |   18 |    200 |
|  6 | andy  | female |   18 |    204 |
+----+-------+--------+------+--------+
5 rows in set (0.00 sec)

查询出那些年龄比任意一个部门的平均年龄高的员工
mysql> select * from emp where age > any (select avg(age) from emp group by dep_id);
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  2 | lucy | female |   48 |    201 |
|  3 | lily | male   |   38 |    201 |
|  4 | jack | female |   28 |    202 |
+----+------+--------+------+--------+
3 rows in set (0.00 sec)

3.5、带exists关键字的子查询

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

mysql> select * from dep where exists (select * from emp where id>3);
+------+----------+
| id   | name     |
+------+----------+
|  200 | 技术     |
|  201 | 人力资源 |
|  202 | 销售     |
|  203 | 运营     |
+------+----------+
4 rows in set (0.00 sec)

注: exists后面执行的结果为True就返回前面的sql语句,not exists意思取反
    
mysql> select * from dep where not exists (select * from emp where id>3);
Empty set (0.00 sec)

3.6、in与exists

当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率

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

查询出那些班级里有学生的班级
select * from class where exists (select * from stu where stu.cid=class.id)

# exists的执行原理为:
1、依次执行外部查询: 即select * from class 
2、然后为外部查询返回的每一行分别执行一次子查询: 即(select * from stu where stu.cid=class.cid)
3、子查询如果返回行,则exists条件成立,条件成立则输出外部查询取出的那条记录

# in
in后跟的都是子查询，in()后面的子查询 是返回结果集的

查询和所有女生年龄相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')

# in的执行原理为:
in()的执行次序和exists()不一样,in()的子查询会先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出

3.7、not in与not exists

当not exists查询的效率远远高与not in查询的效率

not in()子查询的执行顺序是:
为了证明not in成立,即找不到,需要一条一条地查询表,符合要求才返回子查询的结果集,不符合的就继续查询下一条记录,直到把表中的记录查询完,只能查询全部记录才能证明,并没有用到索引

not exists:
如果主查询表中记录少，子查询表中记录多，并有索引。
例如:查询那些班级中没有学生的班级
select * from class where not exists(select * from student where student.cid = class.cid)

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

3.8、应用

3.8.1、准备数据

# 准备数据表
create database db13;

use db13

create table student(
    id int primary key auto_increment,
    name varchar(16)
);

create table course(
    id int primary key auto_increment,
    name varchar(16),
    comment varchar(20)
);

create table student2course(
    id int primary key auto_increment,
    sid int,
    cid int,
    foreign key(sid) references student(id),
    foreign key(cid) references course(id)
);

# 准备数据
insert into student(name) values("lily"),("jack"),("tank"),("tom");

insert into course(name,comment) values
("数据库","数据仓库"),
("数学","高等数学"),
("英语","专业八级");

insert into student2course(sid,cid) values(1,1),(1,2),(1,3),(2,1),(2,2),(3,2);

3.8.2、应用练习

1、查询选修了所有课程的学生id、name
mysql> select * from student s where not exists
    ->     (select * from course c where not exists
    ->         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
+----+------+
| id | name |
+----+------+
|  1 | lily |
+----+------+
1 row in set (0.00 sec)

mysql> select s.id,s.name from student as s
    -> inner join student2course as sc
    -> on s.id=sc.sid
    -> group by s.name
    -> having count(sc.id) = (select count(id) from course);
+----+------+
| id | name |
+----+------+
|  1 | lily |
+----+------+
1 row in set (0.00 sec)

2、查询没有选择所有课程的学生，即没有全选的学生
mysql> select * from student s where exists
    ->     (select * from course c where not exists
    ->         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
+----+------+
| id | name |
+----+------+
|  2 | jack |
|  3 | tank |
|  4 | tom  |
+----+------+
3 rows in set (0.00 sec)

3、查询一门课也没有选的学生
mysql> select * from student s where not exists
    ->     (select * from course c where exists
    ->         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
+----+------+
| id | name |
+----+------+
|  4 | tom  |
+----+------+
1 row in set (0.00 sec)

4、查询至少选修了一门课程的学生
mysql> select * from student s where exists
    ->     (select * from course c where exists
    ->         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
+----+------+
| id | name |
+----+------+
|  1 | lily |
|  2 | jack |
|  3 | tank |
+----+------+
3 rows in set (0.00 sec)

4、pymysql模块

4.1、安装的两种方法

4.1.1、第一种

# 安装
pip3 install pymysql

4.1.2、第二种

4.2、链接，执行sql，关闭(游标)

import pymysql

user = input('请输入用户名: ').strip()
pwd = input('请输入密码: ').strip()

# 先链接，拿到游标
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='root',
    port=3307,
    db='db01',
    charset='utf8'
)

cursor = conn.cursor()  # 拿到游标，即mysql >

# 执行sql
sql = "select * from user where name='%s' and pwd='%s';" % (user, pwd)

rows = cursor.execute(sql)  # 拿到受影响的行数
cursor.close()
conn.close()

if rows:
    print('登录成功')
else:
    print('登录失败')

4.3、execute()之sql注入

注意: 符号--会注释掉它之后的sql，正确的语法: --后至少有一个任意字符

根本原理: 就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- abc,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- abc'

最后那一个空格，在一条sql语句中如果遇到select *from t1 where id > 1 -- and name='zhangsan';则--之后的条件被注释掉了

1、sql注入之: 用户存在，绕过密码
zhangsan' -- 任意字符

2、sql注入之: 用户不存在，绕过用户与密码
xxx' or 1=1 -- 任意字符

解决sql注入

# 原来是我们对sql进行字符串拼接
# sql = "select * from user where name='%s' and pwd='%s';" % (user, pwd)
# rows = cursor.execute(sql)

# 改写为(execute帮我们做字符串拼接,我们无需且一定不能再为%s加引号了)
sql = "select * from user where name=%s and pwd=%s"  # 注意%s需要去掉引号，因为pymysql会自动为我们加上
rows = cursor.execute(sql, (user, pwd))
# pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。
cursor.close()
conn.close()

4.4、增、删、改操作：conn.commit()

4.4.1、增

import pymysql
# 先链接，拿到游标
conn = pymysql.connect(user='root', password='root', host='127.0.0.1',
                       port=3307, db='db01', charset='utf8')
cursor = conn.cursor()  # 拿到游标,即mysql >
# 执行sql   增:
rows = cursor.execute("insert into user values(%s,%s,%s)", args=(3, 'wangwu', '123'))
print(rows)  # 返回受影响的记录条数

# 批量插入
rows = cursor.executemany("insert into user values(%s,%s,%s)",
                   args=((4, 'jack', '123'), (5, 'tom', '123'), (6, 'lily', '123')))
print(rows)  # 返回受影响的记录条数

conn.commit()  # 提交到数据库
cursor.close()
conn.close()

4.4.2、删

import pymysql

conn = pymysql.connect(user='root', password='root', host='127.0.0.1',
                       port=3307, db='db01', charset='utf8')
cursor = conn.cursor()
# 执行sql   删:
rows = cursor.execute("delete from user where id>%s", args=(4,))  # 删除数据
print(rows)  # 返回受影响的记录条数

conn.commit()  # 提交到数据库
cursor.close()
conn.close()

4.4.3、改

import pymysql

conn = pymysql.connect(user='root', password='root', host='127.0.0.1',
                       port=3307, db='db01', charset='utf8')
cursor = conn.cursor()
# 执行sql   改:
rows = cursor.execute("update user set name=%s where id=%s", args=('tom',1))  # 修改数据
print(rows)  # 返回受影响的记录条数

conn.commit()  # 提交到数据库
cursor.close()
conn.close()

4.5、查询操作

fetchone: 查询一条
fetchmany: 查询多条
fetchall: 查询所有
    
# 查询
import pymysql

conn = pymysql.connect(user='root', password='root', host='127.0.0.1',
                       port=3307, db='db01', charset='utf8')
cursor = conn.cursor()
# 如果想让结果变成字典形式,配置游标:
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

cursor.execute("select * from user")

# 查单条 fetchone
print(cursor.fetchone())  # (1, 'tom', '123')
print(cursor.fetchone())  # (2, 'lisi', '123')
print(cursor.fetchone())  # ((3, 'wangwu', '123'))

# 查多条 fetchmany
print(cursor.fetchmany(3))  # ((1, 'tom', '123'), (2, 'lisi', '123'), (3, 'wangwu', '123'))
print(cursor.fetchone())  # (4, 'jack', '123')

# 查所有 fetchall
print(cursor.fetchall())  # ((1, 'tom', '123'), (2, 'lisi', '123'), (3, 'wangwu', '123'), (4, 'jack', '123'))
# 等同于如下:
for line in cursor:
    print(line)

print(cursor.fetchall())  # ()   结果取完再查就是空

# 光标的移动
# 1、绝对路径: 从文件的开头位置算起
print(cursor.fetchall())  # ((1, 'tom', '123'), (2, 'lisi', '123'), (3, 'wangwu', '123'), (4, 'jack', '123'))
cursor.scroll(1, mode='absolute')
print(cursor.fetchone())  # (2, 'lisi', '123')
cursor.scroll(3, mode='absolute')
print(cursor.fetchone())  # (4, 'jack', '123')

# #2、相对路径: 根据当前位置算起
print(cursor.fetchone())  # (1, 'tom', '123')
print(cursor.fetchone())  # (2, 'lisi', '123')
cursor.scroll(1, mode='relative')  # 相对于上面的两条向后移一条
print(cursor.fetchone())  # (4, 'jack', '123')

cursor.close()
conn.close()

4.6、获取插入的最后一条数据的自增id

import pymysql

conn = pymysql.connect(user='root', password='root', host='127.0.0.1',
                       port=3307, db='db13', charset='utf8')
cursor = conn.cursor()

cursor.execute("insert into user (name,password) values(%s,%s)", args=('hhh', '123'))

conn.commit()

print(cursor.lastrowid)  # 查看表中最后一行的iD

cursor.close()
conn.close()