day039mysql多表查询（三种方法）及备份，Navicat工具，pymysql的使用

本节内容：

1、MySQL之多表查询
2、Navicat工具
3、mysql数据库备份
4、pymysql模块

参考文章1
参考文章2

一、MySQL之多表查询

不建议使用外键的连接方式，这样连接方式太死板了，关联性太强，不利于表之间的修改等操作
推荐使用多表查询，使用表之间的逻辑来将表关联起来

1、三种查询方法：

1、多表连接查询（使用笛卡尔积）
2、复合条件连接查询（inner join)
3、子查询（将一个查询结果作为条件）

2、多表连接查询

#重点：外链接语法

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

建表，后面的操作全部依照这个创建的表来演示

#建表
#部门表
create table department(
id int,
name varchar(20) 
);

#员工表，之前我们学过foreign key，强行加上约束关联，但是我下面这个表并没有直接加foreign key，这两个表我只是让它们在逻辑意义上有关系，并没有加foreign key来强制两表建立关系，为什么要这样搞，是有些效果要给大家演示一下
#所以，这两个表是不是先建立哪个表都行啊，如果有foreign key的话，是不是就需要注意表建立的顺序了。那我们来建表。
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#给两个表插入一些数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'); #注意这一条数据，在下面的员工表里面没有对应这个部门的数据

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204) #注意这条数据的dep_id字段的值，这个204，在上面的部门表里面也没有对应的部门id。所以两者都含有一条双方没有涉及到的数据，这都是为了演示一下效果设计的昂
;


#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+

mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+

mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+

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

补充一点：select 查询表的时候，后面可以跟多张表一起查询：
select * from t1,t2;
此时获得是两个t1,t2表的笛卡尔积（就是全排列的组合结果）
写命令时谁在前面，表的显示就是在前面

这时的表虽然有我们要的数据，但是还需要我们进一步的加工，获取我们想要的数据

2、内连接：只连接匹配的行（hang）

内连接匹配获取数据

#我们要找的数据就是员工表里面dep_id字段的值和部门表里面id字段的值能对应上的那些数据啊，所以你看下面的写法：
mysql> select * from employee,department where employee.dep_id=department.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | egon      | male   |   18 |    200 |  200 | 技术         |
|  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.14 sec)
拿到了我们想要的结果。

但是你看，我们左表employee表中的dep_id为204的那个数据没有了，右表department表的id为203的数据没有了，因为我们现在要的就是两表能对应上的数据一起查出来，那个204和203双方对应不上。

#再看一个需求，我要查出技术部的员工的名字
mysql> select name from employee,department where employee.dep_id=department.id and department.name='技术';
ERROR 1052 (23000): Column 'name' in field list is ambiguous
#上面直接就报错了，因为select后面直接写的name，在两个表合并起来的表中，是有两个name字段的，直接写name是不行的，要加上表名，再看：
mysql> select employee.name from employee,department where employee.dep_id=department.id and department.name='技术';
+-----------+
| name      |
+-----------+
| egon      |
| liwenzhou |
+-----------+
2 rows in set (0.09 sec)
结果就没问题了

但是你看上面的代码有没有什么不太好的地方，
虽然我们能够完成我们的事情，但是代码可读性不好，所以以后不要这么写，但是看图：

以mysql为我们提供了一些专门做连表操作的方法，这些方法语义更加的明确，你一看就知道那些代码是连表的，那些代码是查询的，其实上面的连表也是个查询操作，但是我们为了区分明确，连表专门用连表的方法，查询就专门用查询的方法。那这些专门的方法都是什么呢，看后面的内容：

3、外链接之左连接：优先显示左表全部记录

#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果  #注意语法：
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+------------+--------------+
| id | name       | depart_name  |
+----+------------+--------------+
|  1 | egon       | 技术         |
|  5 | liwenzhou  | 技术         |
|  2 | alex       | 人力资源     |
|  3 | wupeiqi    | 人力资源     |
|  4 | yuanhao    | 销售         |
|  6 | jingliyang | NULL         |
+----+------------+--------------+

4、外链接之右连接：优先显示右表全部记录

#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-----------+--------------+
| id   | name      | depart_name  |
+------+-----------+--------------+
|    1 | egon      | 技术         |
|    2 | alex      | 人力资源     |
|    3 | wupeiqi   | 人力资源     |
|    4 | yuanhao   | 销售         |
|    5 | liwenzhou | 技术         |
| NULL | NULL      | 运营         |
+------+-----------+--------------+

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

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

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用此种方式间接实现全外连接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
#查看结果
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | egon       | male   |   18 |    200 |  200 | 技术         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|    2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
+------+------------+--------+------+--------+------+--------------+

#注意 union与union all的区别：union会去掉相同的纪录，
因为union all是left join 和right join合并，所以有重复的记录，通过union就将重复的记录去重了。

3、符合条件连接查询

#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
    on employee.dep_id = department.id
    where age > 25;

#示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.id
    and age > 25
    order by age asc;

4、子查询

子查询其实就是将你的一个查询结果用括号括起来，这个结果也是一张表，
就可以将它交给另外一个sql语句，作为它的一个查询依据来进行操作。

　　我们简单来个需求：技术部都有哪些员工的姓名，都显示出来：　
1、看一下和哪个表有关，然后from找到两个表
2、进行一个连表操作
3、基于连表的结果来一个过滤就可以了

我们之前的做法是：先连表

#我们之前的做法是：先连表
mysql> select * from employee inner join department on employee.dep_id = department.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | egon      | male   |   18 |    200 |  200 | 技术         |
|  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.10 sec)

#然后根据连表的结果进行where过滤，将select*改为select employee.name

mysql> select employee.name from employee inner join department on employee.dep_id = department.id where department.name='技术';
  +-----------+
  | name |
  +-----------+
  | egon |
  | liwenzhou |
  +-----------+
  2 rows in set (0.09 sec)

然后看一下子查询这种方式的写法：它的做法就是解决完一个问题，再解决下一个问题，
针对我们上面的需求，你想，我们的需求是不是说找技术部门下面有哪些员工对不对，
如果你直接找员工表，你能确定哪个dep_id的数值表示的是技术部门吗，不能，
所以咱们是不是应该先确定一个技术部门对应的id号是多少，然后根据部门的id号，
再去员工表里面查询一下dep_id为技术部门对应的部门表的那个id号的所有的员工表里面的记录：
好，那我们看一下下面的操作

子查询

#首先从部门表里面找到技术部门对应的id
mysql> select id from department where name='技术';
+------+
| id   |
+------+
|  200 |
+------+
1 row in set (0.00 sec)

#那我们把上面的查询结果用括号括起来，它就表示一条id=200的数据，然后我们通过员工表来查询dep_id=这条数据作为条件来查询员工的name
mysql> select name from employee where dep_id = (select id from department where name='技术');
+-----------+
| name      |
+-----------+
| egon      |
| liwenzhou |
+-----------+
2 rows in set (0.00 sec)
上面这些就是子查询的一个思路，解决一个问题，再解决另外一个问题，
你子查询里面可不可以是多个表的查询结果，当然可以，然后再通过这个结果作为依据来进行过滤，
然后我们学一下子查询里面其他的内容，往下学。

1、子查询：

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

2、带IN关键字的子查询

#查询员工平均年龄在25岁以上的部门名，可以用连表，也可以用子查询，我们用子查询来搞一下
select id,name from department
    where id in
        (select dep_id from employee group by dep_id having avg(age) > 25);
#连表来搞一下上面这个需求
select department.name from department inner join employee on department.id=employee.dep_id
    group by department.name
    having avg(age)>25;
总结：子查询的思路和解决问题一样，先解决一个然后拿着这个的结果再去解决另外一个问题，
连表的思路是先将两个表关联在一起，然后在进行group by啊过滤啊等等操作，两者的思路是不一样的

#查看技术部员工姓名
select name from employee
    where dep_id in
        (select id from department where name='技术');

#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

3、带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)


#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;

4、带EXISTS关键字的子查询

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

　当返回True时，外层查询语句将进行查询；
当返回值为False时，外层查询语句不进行查询。
还可以写not exists，和exists的效果就是反的

#department表中存在dept_id=203，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

5、练习：通过连表的方式来查询每个部门最新入职的那位员工

就按一开始创建的那张员工表，来完成练习

答案

SELECT * FROM emp AS t1
INNER JOIN (   #和虚拟表进行连表
    SELECT
        post,
        max(hire_date) as max_date  #给这个最大的日期取个别名叫做max_date，先将每个部门最近入职的最大的日期的信息筛选出来，通过这个表来和我们上面的总表进行关联
    FROM
        emp
    GROUP BY  # 根据部门分组
        post
) AS t2 ON t1.post = t2.post  #给虚拟表取个别名叫做t2
WHERE
    t1.hire_date = t2.max_date;  #然后再通过where来过滤出，入职日期和最大日期相等的记录，就是我们要的内容

二、Navicat工具（IDE工具，集成器开发环境）

1、IDE工具介绍(Navicat)

生产环境还是推荐使用mysql命令行，但为了方便我们测试，可以使用IDE工具，
我们使用Navicat工具，这个工具本质上就是一个socket客户端，
可视化的连接mysql服务端的一个工具，并且他是图形界面版的。
我们使用它和直接使用命令行的区别就类似linux和windows系统操作起来的一个区别。

掌握：
#1. 测试+链接数据库
#2. 新建库
#3. 新建表，新增字段+类型+约束
#4. 设计表：外键
#5. 新建查询
#6. 备份库/表

#注意：
批量加注释：ctrl+ / 键
批量去注释：ctrl+shift + / 键

三、MySQL数据备份

#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。
#2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
#3. 导出表： 将表导入到文本文件中。

1、使用mysqldump实现逻辑备份

使用mysqldump实现逻辑备份

#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

4、恢复逻辑备份

恢复逻辑备份

#方法一：
[root@localhost backup]# mysql -uroot -p123 < /backup/all.sql

#方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;
mysql> source /root/db1.sql

#注：如果备份/恢复单个库时，可以修改sql文件
DROP database if exists school;
create database school;
use school;

3、备份/恢复案例

备份/恢复案例

#数据库备份/恢复实验一：数据库损坏
备份：
1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. # mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog
3. 插入数据 //模拟服务器正常运行
4. mysql> set sql_log_bin=0; //模拟服务器损坏
mysql> drop database db;

恢复：
1. # mysqlbinlog 最后一个binlog > /backup/last_bin.log
2. mysql> set sql_log_bin=0;
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份
mysql> source /backup/last_bin.log //恢复最后个binlog文件


#数据库备份/恢复实验二：如果有误删除
备份：
1. mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog
3. 插入数据 //模拟服务器正常运行
4. drop table db1.t1 //模拟误删除
5. 插入数据 //模拟服务器正常运行

恢复：
1. # mysqlbinlog 最后一个binlog --stop-position=260 > /tmp/1.sql
# mysqlbinlog 最后一个binlog --start-position=900 > /tmp/2.sql
2. mysql> set sql_log_bin=0;
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份
mysql> source /tmp/1.log //恢复最后个binlog文件
mysql> source /tmp/2.log //恢复最后个binlog文件

注意事项：
1. 完全恢复到一个干净的环境（例如新的数据库或删除原有的数据库）
2. 恢复期间所有SQL语句不应该记录到binlog中

4、实现自动化备份

实现自动化备份

备份计划：
1. 什么时间 2:00
2. 对哪些数据库备份
3. 备份文件放的位置

备份脚本：
[root@localhost~]# vim /mysql_back.sql
#!/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123

if [ ! -d /backup ];then
mkdir -p /backup
fi

# 备份并截断日志
mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file}
mysql -u${user} -p${pass} -e 'flush logs'

# 只保留最近一周的备份
cd $back_dir
find . -mtime +7 -exec rm -rf {} ;

手动测试：
[root@localhost ~]# chmod a+x /mysql_back.sql
[root@localhost ~]# chattr +i /mysql_back.sql
[root@localhost ~]# /mysql_back.sql

配置cron：
[root@localhost ~]# crontab -l
2 * * * /mysql_back.sql

5、表的导出和导入

表的导出和导入

SELECT... INTO OUTFILE 导出文本文件
示例：
mysql> SELECT * FROM school.student1
INTO OUTFILE 'student1.txt'
FIELDS TERMINATED BY ',' //定义字段分隔符
OPTIONALLY ENCLOSED BY '”' //定义字符串使用什么符号括起来
LINES TERMINATED BY '
' ; //定义换行符


mysql 命令导出文本文件
示例:
# mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt
# mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml
# mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html

LOAD DATA INFILE 导入文本文件
mysql> DELETE FROM student1;
mysql> LOAD DATA INFILE '/tmp/student1.txt'
INTO TABLE school.student1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '”'
LINES TERMINATED BY '
';

#可能会报错

#可能会报错
mysql> select * from db1.emp into outfile 'C:\db1.emp.txt' fields terminated by ',' lines terminated by '
';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable


#数据库最关键的是数据，一旦数据库权限泄露，
那么通过上述语句就可以轻松将数据导出到文件中然后下载拿走，因而mysql对此作了限制，只能将文件导出到指定目录

在配置文件中
[mysqld]
secure_file_priv='C:\' #只能将数据导出到C:\下

重启mysql
重新执行上述语句

6、数据库迁移

务必保证在相同版本之间迁移
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456

三、pymysql模块

我们要学的pymysql就是用来在python程序中如何操作mysql，它和mysql自带的那个客户端还有navicat是一样的，
本质上就是一个套接字客户端，只不过这个套接字客户端是在python程序中用的，
既然是客户端套接字，应该怎么用，
是不是要连接服务端，并且和服务端进行通信啊，让我们来学习一下pymysql这个模块

#安装
pip3 install pymysql

1、链接、执行sql、关闭（游标）

在pycharm下对MySQL进行操作，

名字

import pymysql

#链接，指定ip地址和端口，本机上测试时ip地址可以写localhost或者自己的ip地址或者127.0.0.1，
# 然后你操作数据库的时候的用户名，密码，
# 要指定你操作的是哪个数据库，指定库名，还要指定字符集。不然会出现乱码

conn = pymysql.connect(
    host='127.0.0.1',  # ip地址，也可以写localhost
    port=3306,    # mysql的本机运行端口号，注意不是字符串形式，是int
    user='root',   # mysql的root账户
    password='',    # mysql的登录密码
    database='day02', # 哪个数据库
    charset='utf8'   #指定编码为utf8的时候，注意没有-，别写utf-8，数据库为
)

# 这就想到于mysql自带的那个客户端的游标mysql> 在这后面输入指令，回车执行
cursor = conn.cursor()  # 游标

#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #获取字典数据类型表示的结果：
# {'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'} {'字段名':值}


# 然后给游标输入sql语句并执行sql语句execute
sql = "select * from employee;"  # sql语句
res = cursor.execute(sql)   #执行sql语句，返回sql查询成功的记录数目，是个数字，是受sql语句影响到的记录行
print(res) # 一个数字
# print(cursor.fetchone())  # # 获取1条数据，类似f.read()
# print(cursor.fetchmany(2))  # 获取2条数据, 用括号内的数据来判断行数，类似两个f.read(),
# print(cursor.fetchmany(5))
print(cursor.fetchall())  # 获取所有数据
print('xxxxxxxxxx')

#绝对移动的演示
cursor.scroll(3,'absolute') # 绝对位置，从开头往后三条

#相对移动的演示
# cursor.scroll(3,'relative') # 相对位置，从当前位置往后三条

print(cursor.fetchone())

cursor.close() #关闭游标
conn.close()   #关闭连接

2、execute()之sql注入

之前我们进行用户名密码认证是先将用户名和密码保存到一个文件中，然后通过读文件里面的内容，
来和客户端发送过来的用户名密码进行匹配，现在我们学了数据库，
我们可以将这些用户数据保存到数据库中，然后通过数据库里面的数据来对客户端进行用户名和密码的认证。

　　自行创建一个用户信息表userinfo，里面包含两个字段，username和password，然后里面写两条记录

fe1:使用数据库来验证用户登录

使用数据库来验证用户登录

#我们来使用数据来进行一下用户名和密码的认证操作
import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='666',
    database='crm',
    charset='utf8'
)

cursor = conn.cursor(pymysql.cursors.DictCursor)
uname = input('请输入用户名：')
pword = input('请输入密码：')

sql = "select * from userinfo where username='%s' and password='%s';"%(uname,pword)

res = cursor.execute(sql) #res我们说是得到的行数，如果这个行数不为零，说明用户输入的用户名和密码存在，如果为0说名存在，你想想对不

print(res) #如果输入的用户名和密码错误，这个结果为0，如果正确，这个结果为1
if res:
    print('登陆成功')
else:
    print('用户名和密码错误！')

#通过上面的验证方式，比我们使用文件来保存用户名和密码信息的来进行验证操作要方便很多。

fe2: mql注入，知道用户名，注释掉密码，成功登陆

但是我们来看下面的操作，如果将在输入用户名的地方输入一个 chao'空格然后--空格然后加上任意的字符串，
就能够登陆成功，也就是只知道用户名的情况下，他就能登陆成功的情况：

名字

uname = input('请输入用户名：')
pword = input('请输入密码：')

sql = "select * from userinfo where username='%s' and password='%s';"%(uname,pword)
print(sql)
res = cursor.execute(sql) #res我们说是得到的行数，如果这个行数不为零，说明用户输入的用户名和密码存在，如果为0说名存在，你想想对不

print(res) #如果输入的用户名和密码错误，这个结果为0，如果正确，这个结果为1
if res:
    print('登陆成功')
else:
    print('用户名和密码错误！')
#运行看结果：居然登陆成功
请输入用户名：chao' -- xxx
请输入密码：
select * from userinfo where username='chao' -- xxx' and password='';
1
登陆成功

我们来分析一下：
此时uname这个变量等于什么，等于chao' -- xxx,然后我们来看我们的sql语句被这个字符串替换之后是个什么样子：
select * from userinfo where username='chao' -- xxx' and password='';
其中chao后面的这个'，在进行字符串替换的时候，我们输入的是chao',这个引号和前面的引号组成了一对，
然后后面--在sql语句里面是注释的意思，也就是说--后面的sql语句被注释掉了。
也就是说，拿到的sql语句是select * from userinfo where username='chao';
然后就去自己的数据库里面去执行了，发现能够找到对应的记录，
因为有用户名为chao的记录，然后他就登陆成功了，但是其实他连密码都不知道，
只知道个用户名。。。，他完美的跳过了你的认证环节。

fe3:直接连用户名和密码都不知道，但是依然能够登陆成功的情况：

直接破解登录

请输入用户名：xxx' or 1=1 -- xxxxxx
请输入密码：
select * from userinfo where username='xxx' or 1=1 -- xxxxxx' and password='';
3
登陆成功

我们只输入了一个xxx' 加or 加 1=1 加 -- 加任意字符串
看上面被执行的sql语句你就发现了，or 后面跟了一个永远为真的条件，
那么即便是username对不上，但是or后面的条件是成立的，也能够登陆成功

上面两个例子就是两个sql注入的问题，看完上面这两个例子，
有没有感觉后背发凉啊同志们，别急，我们来解决一下这个问题，怎么解决呢？

有些网站直接在你输入内容的时候，是不是就给你限定了，你不能输入一些特殊的符号，
因为有些特殊符号可以改变sql的执行逻辑，其实不光是–，还有一些其他的符号也能改变sql语句的执行逻辑，
这个方案我们是在客户端给用户输入的地方进行限制，但是别人可不可以模拟你的客户端来发送请求，
是可以的，他模拟一个客户端，不按照你的客户端的要求来，就发一些特殊字符，你的客户端是限制不了的。

所以单纯的在客户端进行这个特殊字符的过滤是不能解决根本问题的，那怎么办？
我们服务端也需要进行验证，可以通过正则来将客户端发送过来的内容进行特殊字符的匹配，
如果有这些特殊字符，我们就让它登陆失败。

2、在服务端来解决sql注入的问题：

 不要自己来进行sql字符串的拼接了，pymysql能帮我们拼接，
 他能够防止sql注入，所以以后我们再写sql语句的时候按下面的方式写：

服务端代码编写示例

之前我们的sql语句是这样写的：
sql = "select * from userinfo where username='%s' and password='%s';"%(uname,pword)

以后再写的时候，sql语句里面的%s左右的引号去掉，并且语句后面的%(uname,pword)这些内容也不要自己写了，按照下面的方式写
sql = "select * from userinfo where username=%s and password=%s;"
难道我们不传值了吗，不是的，我们通过下面的形式，在excute里面写参数：
#cursor.execute(sql,[uname,pword]) ，其实它本质也是帮你进行了字符串的替换，只不过它会将uname和pword里面的特殊字符给过滤掉。

看下面的例子：
uname = input('请输入用户名：') #输入的内容是：chao' -- xxx或者xxx' or 1=1 -- xxxxx
pword = input('请输入密码：')

sql = "select * from userinfo where username=%s and password=%s;"
print(sql)
res = cursor.execute(sql,[uname,pword]) #res我们说是得到的行数，如果这个行数不为零，说明用户输入的用户名和密码存在，如果为0说名存在，你想想对不

print(res) #如果输入的用户名和密码错误，这个结果为0，如果正确，这个结果为1
if res:
    print('登陆成功')
else:
    print('用户名和密码错误！')
#看结果：
请输入用户名：xxx' or 1=1 -- xxxxx
请输入密码：
select * from userinfo where username=%s and password=%s;
0
用户名和密码错误！

通过pymysql提供的excute完美的解决了问题。

总结咱们刚才说的两种sql注入的语句
#1、sql注入之：用户存在，绕过密码
chao' -- 任意字符

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

解决方法总结：

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

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

3、增、删、改：conn.commit()

查操作在上面已经说完了，我们来看一下增删改，
也要注意，sql语句不要自己拼接，交给excute来拼接
执行完增删改，必须执行这个命令conn.commit()，这样才会成功保存

增、删、改：示例

import pymysql
#链接
conn=pymysql.connect(host='localhost',port='3306',user='root',password='123',database='crm',charset='utf8')
#游标
cursor=conn.cursor()

#执行sql语句
#part1
# sql='insert into userinfo(name,password) values("root","123456");'
# res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数
# print(res)
# print(cursor.lastrowid) #返回的是你插入的这条记录是到了第几条了

#part2
# sql='insert into userinfo(name,password) values(%s,%s);'
# res=cursor.execute(sql,("root","123456")) #执行sql语句，返回sql影响成功的行数
# print(res)
#还可以进行更改操作：
#res=cursor.excute("update userinfo set username='taibaisb' where id=2")
#print(res) #结果为1
#part3
sql='insert into userinfo(name,password) values(%s,%s);'
res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数，一次插多条记录
print(res)
#上面的几步，虽然都有返回结果，也就是那个受影响的函数res，但是你去数据库里面一看，并没有保存到数据库里面，
conn.commit() #必须执行conn.commit,注意是conn，不是cursor，执行这句提交后才发现表中插入记录成功，没有这句，上面的这几步操作其实都没有成功保存。
cursor.close()
conn.close()

4、查：fetchone，fetchmany，fetchall

fetchone，查询单条
fetchmany，查询你指定的条数
fetchall  查询所有

查询

import pymysql
#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
#游标
cursor=conn.cursor()

#执行sql语句
sql='select * from userinfo;'
rows=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数rows,将结果放入一个集合，等待被查询

# cursor.scroll(3,mode='absolute') # 相对绝对位置移动
# cursor.scroll(3,mode='relative') # 相对当前位置移动
res1=cursor.fetchone()
res2=cursor.fetchone()
res3=cursor.fetchone()
res4=cursor.fetchmany(2)
res5=cursor.fetchall()
print(res1)
print(res2)
print(res3)
print(res4)
print(res5)
print('%s rows in set (0.00 sec)' %rows)


conn.commit() #提交后才发现表中插入记录成功
cursor.close()
conn.close()

'''
(1, 'root', '123456')
(2, 'root', '123456')
(3, 'root', '123456')
((4, 'root', '123456'), (5, 'root', '123456'))
((6, 'root', '123456'), (7, 'lhf', '12356'), (8, 'eee', '156'))
rows in set (0.00 sec)
'''

5、获取插入的最后一条数据的自增ID

名字

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
cursor=conn.cursor()

sql='insert into userinfo(name,password) values("xxx","123");'
rows=cursor.execute(sql)
print(cursor.lastrowid) #在插入语句后查看

conn.commit()

cursor.close()
conn.close()