掌握：#1. 测试+链接数据库#2. 新建库#3. 新建表，新增字段+类型+约束#4. 设计表：外键#5. 新建查询#6. 备份库/表#注意：批量加注释：ctrl+？键
批量去注释：ctrl+shift+？键

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

#语法：# 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

#方法一：[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;

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

恢复：1. # mysqlbinlog 最后一个binlog > /backup/last_bin.log2. 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.sql2. mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog3. 插入数据 //模拟服务器正常运行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中

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

备份脚本：
[root@localhost~]# vim /mysql_back.sql#!/bin/bashback_dir=/backup
back_file=`date +%F`_all.sql
user=rootpass=123if [ ! -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* * * /mysql_back.sql

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


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.htmlLOAD 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 '\n';

#可能会报错mysql> select * from db1.emp into outfile 'C:\\db1.emp.txt' fields terminated by ',' lines terminated by '\r\n';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable#数据库最关键的是数据，一旦数据库权限泄露，那么通过上述语句就可以轻松将数据导出到文件中然后下载拿走，因而mysql对此作了限制，只能将文件导出到指定目录在配置文件中
[mysqld]
secure_file_priv='C:\\' #只能将数据导出到C:\\下重启mysql
重新执行上述语句

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

#安装pip3 install pymysql

import pymysql
user=input('用户名: ').strip()
pwd=input('密码: ').strip()#链接，指定ip地址和端口，本机上测试时ip地址可以写localhost或者自己的ip地址或者127.0.0.1，然后你操作数据库的时候的用户名，密码，要指定你操作的是哪个数据库，指定库名，还要指定字符集。不然会出现乱码conn=pymysql.connect(host='localhost',port=3306,user='root',password='123',database='student',charset='utf8') #指定编码为utf8的时候，注意没有-，别写utf-8，数据库为#得到conn这个连接对象#游标cursor=conn.cursor() #这就想到于mysql自带的那个客户端的游标mysql> 在这后面输入指令，回车执行#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #获取字典数据类型表示的结果：{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'} {'字段名':值}#然后给游标输入sql语句并执行sql语句executesql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号，执行这句sql的前提是医药有个userinfo表，里面有name和password两个字段，还有一些数据，自己添加数据昂print(sql)
res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目，是个数字，是受sql语句影响到的记录行数，其实除了受影响的记录的条数之外，这些记录的数据也都返回了给游标,这个就相当于我们subprocess模块里面的管道PIPE，乘放着返回的数据#all_data=cursor.fetchall()  #获取返回的所有数据，注意凡是取数据，取过的数据就没有了，结果都是元祖格式的#many_data=cursor.fetchmany(3) #一下取出3条数据，#one_data=cursor.fetchone()  #按照数据的顺序，一次只拿一个数据，下次再去就从第二个取了，因为第一个被取出去了，取一次就没有了，结果也都是元祖格式的
  fetchone：(1, '男', 1, '理解')
  fetchone：(2, '女', 1, '钢蛋')
  fetchall：((3, '男', 1, '张三'), (4, '男', 1, '张一')）#上面fetch的结果都是元祖格式的，没法看出哪个数据是对应的哪个字段，这样是不是不太好看，想一想，我们可以通过python的哪一种数据类型，能把字段和对应的数据表示出来最清晰，当然是字典{'字段名':值}#我们可以再创建游标的时候，在cursor里面加上一个参数：cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)获取的结果就是字典格式的，fetchall或者fetchmany取出的结果是列表套字典的数据形式上面我们说，我们的数据取一次是不是就没有了啊，实际上不是的，这个取数据的操作就像读取文件内容一样，每次read之后，光标就移动到了对应的位置，我们可以通过seek来移动光标
同样，我们可以移动游标的位置，继续取我们前面的数据,通过cursor.scroll(数字，模式)，第一个参数就是一个int类型的数字，表示往后移动的记录条数，第二个参数为移动的模式，有两个值：absolute：绝对移动，relative：相对移动#绝对移动：它是相对于所有数据的起始位置开始往后面移动的#相对移动：他是相对于游标的当前位置开始往后移动的#绝对移动的演示#print(cursor.fetchall())#cursor.scroll(3,'absolute') #从初始位置往后移动三条，那么下次取出的数据为第四条数据#print(cursor.fetchone())#相对移动的演示#print(cursor.fetchone())#cursor.scroll(1,'relative') #通过上面取了一次数据，游标的位置在第二条的开头，我现在相对移动了1个记录，那么下次再取，取出的是第三条，我相对于上一条，往下移动了一条#print(cursor.fetchone())print(res) #一个数字cursor.close() #关闭游标conn.close()   #关闭连接if res:    print('登录成功')else:    print('登录失败')

#我们来使用数据来进行一下用户名和密码的认证操作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，如果正确，这个结果为1if res:    print('登陆成功')else:    print('用户名和密码错误！')#通过上面的验证方式，比我们使用文件来保存用户名和密码信息的来进行验证操作要方便很多。

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

我们来分析一下：
此时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的记录，然后他就登陆成功了，但是其实他连密码都不知道，只知道个用户名。。。，他完美的跳过了你的认证环节。

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

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

之前我们的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 -- xxxxxpword = input('请输入密码：')

sql = "select * from userinfo where username=%s and password=%s;"print(sql)
res = cursor.execute(sql,[uname,pword]) #res我们说是得到的行数，如果这个行数不为零，说明用户输入的用户名和密码存在，如果为0说名存在，你想想对不print(res) #如果输入的用户名和密码错误，这个结果为0，如果正确，这个结果为1if res:    print('登陆成功')else:    print('用户名和密码错误！')#看结果：请输入用户名：xxx' or 1=1 -- xxxxx请输入密码：
select * from userinfo where username=%s and password=%s;
用户名和密码错误！

总结咱们刚才说的两种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的规矩来。

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#part3sql='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()

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)'''

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()