阅读目录
一、IDE工具（Navicat）介绍
生产环境还是推荐使用mysql命令行，但为了方便我们测试，可以使用IDE工具
下载链接：
链接：https://pan.baidu.com/s/13JmtWAyO2-36SJjsm4tb6Q
提取码：qcgg
掌握： #1. 测试+链接数据库 #2. 新建库 #3. 新建表，新增字段+类型+约束 #4. 设计表：外键 #5. 新建查询 #6. 备份库/表 #注意： 批量加注释：ctrl+？键 批量去注释：ctrl+？键，ps：有的版本是ctrl+shift+？
二、MySQL数据备份
#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。 #2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。 #3. 导出表： 将表导入到文本文件中。
使用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
示例：(在windows上执行备份命令)
管理员身份进入cmd，进入到MySQL的安装目录下的bin目录，执行下图命令之后，就会在bin目录下生成备份文件alldb.sql
注意：在命令行输入命令时，别在-p后跟密码，提示输入密码后手动输入，如果在命令行-p跟密码，有可能会报错如下：
恢复逻辑备份
#方法一：这个命令在linux执行正常 [root@egon backup]# mysql -uroot -p123 < /backup/all.sql
注意：
在windows上要先创建即将要备份的数据库，然后再恢复，恢复命令也要指定导入到创建的数据库
#mysql -uroot -p123 之前创建的数据库名 < /backup/all.sql
#方法二：进入mysql命令行 mysql> source /backup/all.sql
示例：在windows执行恢复备份
先进入mysql命令行，
注意，要先创建要导入的数据库，例如本次要导入的数据库名为userinfo_db
mysql> create database userinfo_db; Query OK, 1 row affected (0.00 sec) mysql> use userinfo_db; Database changed mysql> source D:mysql5.6mysql-5.6.37-winx64inalldb.sql #再执行source命令恢复 mysql> show tables; +-----------------------+ | Tables_in_userinfo_db | +-----------------------+ | baseinfo | | class | | cmd | | emp | | errlog | | studnet | | user | +-----------------------+ 7 rows in set (0.00 sec) mysql>
备份/恢复案例
#数据库备份/恢复实验一：数据库损坏 备份： 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中
实现自动化备份
备份计划： 1. 什么时间 2:00 2. 对哪些数据库备份 3. 备份文件放的位置 备份脚本： [root@egon ~]# 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@egon ~]# chmod a+x /mysql_back.sql [root@egon ~]# chattr +i /mysql_back.sql [root@egon ~]# /mysql_back.sql 配置cron： [root@egon ~]# crontab -l 2 * * * /mysql_back.sql
表的导出和导入
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 重新执行上述语句
数据库迁移
务必保证在相同版本之间迁移 # mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456
三、pymysql模块
#安装（管理员运行cmd） pip3 install pymysql
链接、执行sql、关闭（游标）
import pymysql # 链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123456', database='atm_db', charset='utf8', # autocommit=True # 自动提交,可以不加 ) # 游标 cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示 # cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #执行完毕返回的结果集以字典显示 user=input('用户名: ').strip() pwd=input('密码: ').strip() # 执行sql语句 sql = 'select * from userinfo where username=%s and password=%s' val=(user, pwd) res = cursor.execute(sql,val) # 执行sql语句，返回sql查询成功的记录数目 #也可以写成 #res = cursor.execute(sql,(user, pwd)) print(res) cursor.close() conn.close() if res: print('登录成功') else: print('登录失败')
execute()之sql注入
注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符
根本原理：就根据程序的sql语句字符串拼接中name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha' #示例： sql = 'select * from userinfo where username="%s" and password="%s"' %(user,pwd) res = cursor.execute(sql) #ps:有个注意的地方，就是sql语句拼接中%s如果用的双引号，那么我们输入xxx加" #总之，看你的sql语句中%s用的什么引号，输的时候就跟什么 #不同注入方法 #1、sql注入之：用户存在，绕过密码 egon' -- 任意字符 #2、sql注入之：用户不存在，绕过用户与密码 xxx' or 1=1 -- 任意字符
示例（程序代码）
import pymysql # 链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123456', database='atm_db', charset='utf8' ) # 游标 cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示 # cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #执行完毕返回的结果集以字典显示 user=input('用户名: ').strip() pwd=input('密码: ').strip() # 执行sql语句 sql = 'select * from userinfo where username="%s" and password="%s"' %(user,pwd) print("sql语句",sql) res = cursor.execute(sql) # 执行sql语句，返回sql查询成功的记录数目 print(res) cursor.close() conn.close() if res: print('登录成功') else: print('登录失败')
不同输入效果（注意代码中sql语句用的是双引号）
用户存在绕过密码：
用户不存在：
解决方法：
# 原来代码中是对对sql进行字符串拼接 sql = 'select * from userinfo where username="%s" and password="%s"' %(user,pwd) print("sql语句",sql) res = cursor.execute(sql) #改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号也无需在sql后%(user,pwd)去指定，在） sql="select * from userinfo where name=%s and password=%s" #注意%s需要去掉引号，因为execute方法会自动为我们加上 res=cursor.execute(sql,[user,pwd]) # execute自动识别sql里面的%s用后面元组里面的数据替换
修改过后的代码：
import pymysql # 链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123456', database='atm_db', charset='utf8' ) # 游标 cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示 # cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #执行完毕返回的结果集以字典显示 user=input('用户名: ').strip() pwd=input('密码: ').strip() # 执行sql语句 sql = 'select * from userinfo where username=%s and password=%s' val=(user, pwd) res = cursor.execute(sql,val) # 执行sql语句，返回sql查询成功的记录数目 #也可以写成 #res = cursor.execute(sql,(user, pwd)) print(res) cursor.close() conn.close() if res: print('登录成功') else: print('登录失败')
增、删、改：conn.commit()
import pymysql # 链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123456', database='atm_db', charset='utf8' ) # 游标 cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示 # cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #执行完毕返回的结果集以字典显示 #执行sql语句 #part1（插入单条记录） # sql='insert into userinfo(username,password) values("root","123456");' # res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数 # print(res) #part2（插入单条记录） #sql='insert into userinfo(username,password) values(%s,%s);' #res=cursor.execute(sql,("root","123456")) #执行sql语句，返回sql影响成功的行数 #print(res) #ps: ''' add_name="root passwd="123456" sql='insert into userinfo(username,password) values(%s,%s);' val=(add_name,passwd) res=cursor.execute(sql,val) #可以将元组提出了在外面通过变量给元组赋值 ''' #part3（插入多条记录） sql='insert into userinfo(name,password) values(%s,%s);' res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数 print(res) conn.commit() #提交后插入记录才能成功 cursor.close() conn.close()
查：fetchone，fetchmany，fetchall
import pymysql # 链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123456', database='atm_db', charset='utf8' ) # 游标 cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示 # cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #执行完毕返回的结果集以字典显示 #执行sql语句 sql='select * from userinfo;' rows=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数rows,将结果放入一个队列，等待被查询 # cursor.scroll(3,mode='absolute') # 相对绝对位置移动，相对于返回结果队列队首移动3，括号内设置几就移动几 # cursor.scroll(3,mode='relative') # 相对当前位置移动，相对于游标在返回结果队列当前所在位置 res1=cursor.fetchone() #从命令返回的结果队列中当前位置取出一条记录 res2=cursor.fetchone() res3=cursor.fetchone() res4=cursor.fetchmany(2) #从命令返回的结果队列中当前位置取出2条记录，括号内设置几就取几条 res5=cursor.fetchall() #从命令返回的结果队列中当前位置之后所有记录 print(res1) print(res2) print(res3) print(res4) print(res5) conn.commit() #提交后才发现表中插入记录成功 cursor.close() conn.close()
获取插入的最后一条数据的自增ID
import pymysql # 链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123456', database='atm_db', charset='utf8' ) # 游标 cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示 # cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #执行完毕返回的结果集以字典显示 sql='insert into userinfo(username,password) values(%s,%s);' val=("jason","123456") rows=cursor.execute(sql,val) print(cursor.lastrowid) #在插入语句后查看 conn.commit() cursor.close() conn.close()