mysql数据库-高级. pymysql_视图_事务_索引_账户管理_mysql主从

pymysql

Pycharm中：引入模块在py文件中引入pymysql模块：

from pymysql import *

1. 创建对象：调用connect()方法
   conn=connect(参数列表)
   参数host：连接的mysql主机，如果本机是’localhost’
   参数port：连接的mysql主机的端口，默认是3306
   参数database：数据库的名称
   参数user：连接的用户名
   参数password：连接的密码
   参数charset：通信采用的编码方式，推荐使用utf8
2. Cursor 对象
   cs1=conn.cursor() # 对象
   对象的方法:
   close()关闭
   execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
   fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
   fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
3. 例子

from pymysql import *
def main():
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
    # 获得Cursor对象
    cs1 = conn.cursor()
    # 执行insert语句，并返回受影响的行数：添加一条数据
    # 增加
    count = cs1.execute('insert into goods_cates(name) values("硬盘")')
    #打印受影响的行数
    print(count)

    count = cs1.execute('insert into goods_cates(name) values("光盘")')
    print(count)

    # # 更新
    # count = cs1.execute('update goods_cates set name="机械硬盘" where name="硬盘"')
    # # 删除
    # count = cs1.execute('delete from goods_cates where id=6')

    # 提交之前的操作，如果之前已经之执行过多次的execute，那么就都进行提交
    conn.commit()

    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()

if __name__ == '__main__':
    main()

4. 参数化
   sql语句的参数化，可以有效防止sql注入
   注意：此处不同于python的字符串格式化，全部使用%s占位

from pymysql import *

def main():
    find_name = input("请输入物品名称：")
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
    # 获得Cursor对象
    cs1 = conn.cursor()

    # # 非安全的方式
    # # 输入 " or 1=1 or "   (双引号也要输入)
    # sql = 'select * from goods where name="%s"' % find_name
    # print("""sql===>%s<====""" % sql)
    # # 执行select语句，并返回受影响的行数：查询所有数据
    # count = cs1.execute(sql)

    # 安全的方式
    # 构造参数列表
    params = [find_name]
    # 执行select语句，并返回受影响的行数：查询所有数据
    count = cs1.execute('select * from goods where name=%s', params)
    # 注意：
    # 如果要是有多个参数，需要进行参数化
    # 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可 

    # 打印受影响的行数
    print(count)
    # 获取查询的结果
    # result = cs1.fetchone()
    result = cs1.fetchall()
    # 打印查询的结果
    print(result)
    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()

if __name__ == '__main__':
    main()	

MySQL中高级用法

• 视图
  通俗的讲，视图就是一条SELECT语句执行后返回的结果集
  视图是对若干张基本表的引用，一张虚表，查询语句执行的结果
  视图的作用：提高了重用性，就像一个函数对数据库重构，却不影响程序的运行提高
  了安全性能，可以对不同的用户让数据更加清晰
  建议以v_开头： create view 视图名称 as select语句;
  查看视图，查看表会将所有的视图也列出来 ：show tables;
  使用视图，视图的用途就是查询：select * from v_stu_score;
  删除视图，drop view 视图名称：drop view v_stu_sco;

• 事务
  所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
  事务四大特性(简称ACID)

  • 原子性(Atomicity)
  • 一致性(Consistency)
  • 隔离性(Isolation)
  • 持久性(Durability)

  表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

  注意

修改数据的命令会自动的触发事务，包括insert、update、delete
而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

开启事务 :begin 或者 start transaction;
提交事务 :commit
回滚事务 :rollback

• 索引
  索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分，它们包含着对数据表里所有记录的引用指针。
  更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度
  索引目的：索引的目的在于提高查询效率
  创建索引：如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致。字段类型如果不是字符串，可以不填写长度部分

create index 索引名字 on 表名（列名（长度））
eg: create index title_index on test_index(title(10))
删除索引： drop index 索引名字 on 表名；

开启运行时间检测： set profiling = 1; 然后运行：show profiles; 可以查看运行时间
要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。

账户管理*

1. 看所有用户；所有用户及权限信息存储在mysql数据库的user表中查看user表的结构

    desc user; 查看user表的结构
    select host,user,authentication_string from user; 
    Host表示允许访问的主机
    User表示用户名
    authentication_string表示密码，为加密后的值

2. 创建账户，授权

    grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';  
    访问主机通常使用 百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
    eg: grant select on jing_dong.* to "laowang"@"localhost" identified by "123456"

常用权限有：creat, alter, drop, insert, update, delete, select
如果分配所有权限，可以使用 all privileges

3. 查看用户有哪些权限
   show grants for laowang@localhost

4. 退出登录
   quit

5. 修改权限
   grant 权限名字 on 数据库 to 账户@主机 with grant option;

6. 修改密码

	update user set authentication_string=password("新密码") where user= "用户名"
	eg: update user set authentication_string=password('123') where user='laowang';
	注意修改完后，刷新权限： flush privileges

mysql主从*

主从同步的定义

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。
因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。
通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

使用主从同步的好处：

• 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。

• 提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据

• 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

mysql 主机 192.168.113.138
mysql 的从机 192.168.113.xx

1. 备份主服务器的数据
   mysqldump -uroot -p --all-databases --lock-all-tables > ~/Desktop/master_db.sql

2. 将mater_db.sql文件复制到从机 在从机上完成数据的恢复操作
   scp /home/python/Desktop/master_db.sql python@192.168.113.155:/home/python/Desktop

3. 恢复数据
   mysql -uroot -p < ~/Desktop/master_db.sql

4. 修改主机master上的配置文件
   – 取消设置server-id的注释
   – 取消设置二进制日志文件的路径的注释

5. 重启master的mysql服务
   sudo service mysql restart

6. 登录mysql 主服务 创建从机登录主机的账号

mysql -uroot -pmysql
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
flush privileges
show master status;

7. 修改从机的的配置文件设置server-id

修改server-id
重启mysql 服务: sudo service mysql restart

8. 设置从机隶属于哪个主机

	-- 在从机中重新连接到mysql: mysql -uroot -pmysql
	-- change master to master_host='192.168.113.138', master_user='slave', master_password='slave',master_log_file='mysql-bin.000002', master_log_pos=582;

9. 开始主从复制

在从机上执行 start slave;
show slave status\G 查看主从同步是否成功

10. 出错了 需要找错误日志
    – 错误日志路径: /var/log/mysql 分析错误
    – baidu输入: mysql 1593 uuid
    – 修改uuid 不一样
    – datadir指的是 通过超级权限访问 /var/lib/mysql
    – 获取唯一的uuid: select uuid(); 设置uuid完毕之后需要重启服务