MySQL学习笔记之基础操作(四)

1. 数据库安全和管理

• 表的复制

• 表能根据实际需求复制数据

• 复制表时不会KEY属性复制过来

• 语法

  create table 表名 select 查询命令；
  

• 数据库备份

• 备份命令格式

  mysqldump -u 用户名 -p 源库名 > ~/stu.sql

• 恢复命令格式

  mysql -u root -p 目标库名 < stu.sql

• MySQL远程连接

  更改配置文件，重启服务！
  1. cd etc/mysql/mysql.conf.d
  2.sudo vi mysql.cnf 找到43行左右，加#注释
      # bind-address = 127.0.0.1
  3.保存退出
  4.sudo service mysql restart
  5.进入mysql修改用户表host值
      user mysql;
      update user set host='%' where user='root';
      select host,user from user;(查看远程用户信息)
  6.刷新权限
      flush privileges;

• 添加用户和授权

  1.用root用户登录mysql
  mysql -u root -p
  
  2.添加用户 % 表示自动选择可用IP
      create user 'username'@'host' identified by 'password';
  3.权限管理
      # 增加权限
      grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;
      # 删除权限
      revoke insert,update,select on 库.表 from "user"@'%';
  4.刷新权限
      flush privileges;
  5.删除用户
      drop user "用户名"@"%" 

  权限列表

  1. all privileges,select,insert,update,delete,alter,create,drop等。
  2. 库.表：*.* 代表所有库的所有表

2.pymysql模块

Pymysql是一个第三方库，如果自己的计算上没有可以在终端使用命令进行安装。

sudo pip3 install pymysql

• pymysql使用流程

  1.建立数据连接：
      db=pymysql.connect(...)
  2.创建游标对象：
      cur = db.cursor()
  3.游标方法：
      cur.execute("insert ...")
  4.提交到数据库或获取数据：
      db.commit()/cur.fetchall()
  5.关闭游标对象：
      cur.close()
  6.断开数据库连接：
      db.close()

• 常用函数

  db = pymysql.connect(参数列表)
  功能：链接数据库
  host:主机地址，本地 localhost
  port:端口号，默认3306
  user:用户名
  password:密码
  database:库
  charset : 编码方式，推荐使用 utf8

  cur = db.cursor()
  功能：创建游标
  返回值：返回游标对象，用于执行具体SQL命令

  cur.execute(sql,args_list)
  功能：执行sql命令
  参数：sql sql语句
   args_list 列表，用于给语句传递参数
   
  cur.executemany(sql命令，args_list)
  功能：多次执行sql命令，执行次数由列表中元组数量决定
  参数：sql sql语句
   args_list 列表中包含元组 每个元组用于给sql语句传递变量，一般用于写操作。

  db.commit()提交到数据库执行，必须支持事务操作才有效
  db.rollback()回到原来的数据形态，必须支持事务操作才有效
  cur.fetchone() 获取查询结果集的第一条数据，查找到返回一个元组否则返回None
  cur.fetchmany(n)获取前n条查找到的记录，返回结果为元组嵌套元组，无结果返回空元组
  cur.fetchall()获取所有查找到的记录，返回结果形式同上。

  cur.close()关闭游标对象
  db.close()关闭数据库连接

• 文件存储

• 优点：安全可靠，数据库与文件同在

• 缺点：占用数据库空间大，文件存取效率低

• 优点：节省数据库空间，提取方便

• 缺点：文件或者数据库迁移会导致文件丢失

• 存储文件路径

• 存储文件本身

"""
存取二进制数据
"""


import pymysql


kwargs = {
   "host": "localhost",
   "port": 3306,
   "user": "root",
   "password": "123456",
   "database": "stu",
   "charset": "utf8"
}


# 连接数据库
db = pymysql.connect(**kwargs)


# 创建游标 使用sql操作数据得到结果的对象
cur = db.cursor()


# 存储图片
# with open("left.png",'rb') as f:
#     data = f.read()
# sql = "update class set image=%s where id=4;"
# cur.execute(sql,[data])
# db.commit()


# 取出图片
sql = "select image from class where id=4;"
cur.execute(sql)
data = cur.fetchone()[0]
with open("xxx.png",'wb') as f:
  f.write(data)


# 关闭
cur.close()
db.close()