mysql进阶

• 2019 年 10 月 6 日
• 筆記

python与mysql的交互

在python中，我们要完成与mysql的交互需要用到pymysql模块。

在python2中，用到的是MySQLdb

安装该模块：pip3 install pymysql

数据准备：

还是和上次的数据库一样，链接：Mysql查询集合

python中操作mysql

基本流程：

1、创建一个connection对象

2、通过connection创建一个游标对象(cursor)

3、通过游标对象(cursor)来执行sql语句

4、关闭cursor

5、关闭connection

查询实例：

# 导入模块

from pymysql import *

# 创建一个connection链接

conn=connect(host='localhost',port=3306,user='root',password='123456',database='student',charset='utf8')

# host：localhost表示本地，127.0.0.1也表示本地。两者都一样。挑一个。

# port：端口，mysql的端口

# user：用户名

# passsword：密码

# database：数据库名

# charset：编码格式

# 利用connection创建一个游标，用来接收返回的数据

cursor = conn.cursor()

# 执行SQL语句

count = cursor.execute('select *from studentinfo')

# 输出count，count为受影响行数

print(count)

# 一下显示全部数据

#print(cursor.fetchall())

# 显示一行

#print(cursor.fetchone())

# 再调用一次取一行数据会取第二行

# 显示三行数据

print(cursor.fetchmany(3))

# 关闭游标

cursor.close()

# 关闭connection

conn.close()

注意：如果全取出来之后就没有数据了。

增删改实例：

from pymysql import *

conn=connect(host='localhost',port=3306,user='root',password='123456',database='student',charset='utf8')

cursor = conn.cursor()

# 增加sql语句

insertcount = cursor.execute("insert into studentinfo values(0,'赵六',1,'男',26,'天津',0)")

# 删除sql语句

#deletecount = cursor.execute("delete from studentinfo where studentid=5")

# 修改

#updatecount = cursor.execute("update studentinfo set studentage=23 where studentid=1")

# 执行到数据库

conn.commit()

cursor.close()

conn.close()

mysql视图

视图是什么？

视图是一条select语句执行后返回的结果集，所以创建视图最主要的是就是select语句。

视图是对1或多个表的引用，是一张虚表，来查询查询语句执行的结果。

比如我们创建一个视图：

语法：create view 名字 as 查询语句

create view v_stu_cla as select s.*,c.classname from studentinfo as s inner join classinfo as c on s.classid = c.classid;

我们show tables；一下

会看到多出来一张表：v_stu_cla

我们select * from v_stu_cla 一下就会显示我们创建视图后面的查询语句查询出来的结果

更加方便了我们查数据，视图主要用于查数据。不可用于修改，删除，增加。

删除视图：drop view 视图名；

视图的作用：

1、提高查询语句的重用性

2、对数据重构

3、提高安全性（不能修改，等其他删除）

4、数据更加清晰

事务

什么是事务？

他是一个操作序列，要么都执行，要么都不执行

为什么这样设置？

比如我们在转账的时候，一个转，另一个收，是需要同时进行的。

不能存在一个减了另一个没有增加。

流程：

1、开启事务

2、执行sql的增，删，改。

3、全部都成功commit

4、有一个不成功事务回滚rollback

事务的四大特性(简称ACID)

原子性(Atomicity)

一致性(Consistency)

隔离性(Isolation)

持久性(Durability)

原子性：不可分割

一致性：最后的结果是一样的，如果出错，将不会进行任何操作

隔离性：比如当我们修改金额减去200时，增加的sql语句还没有之行时，修改的数据放在缓存中，是不会做出修改操作的。

持久性：事务一旦提交，便会保存到数据库中。

开启一个事务：

begin；

或者

start transaction；

提交事务：

commit；

回滚事务：

rollback；

索引

什么是索引？

索引是一种特使的文件，它包含了对数据库表里所有记录的引用指针。

像一本书的目录，帮助我们更快的找到数据。

索引的目的

提高查询效率

创建一个索引

create index name_index on studentinfo(studentname(20));

create index:创建索引关键字

name_index：索引名字

on：关键字

studentinfo:表的名字

studentname：字段的名字

20：我们创建的时候给的类型是varchar(20),这里就写20，如果是int类型，就不用写。

如何看执行的时间？

开启计时：

set profiling=1;

执行sql语句；

查看执行的时间：

show profiles;

查看索引：

show index from 表名;

另：主键，外键 都是一种索引。

删除索引：

drop index 索引名称 on 表名;

权限管理

目前我们的mysql只有一个账户root，也就是所有的权限都有，有些员工，是不允许有删除的操作的。保证数据的安全性。root是最高权限的。

设置权限在mysql这个数据库中

进入mysql数据库：

use mysql；

看一下表：

show tables；

有一个user表：

看一下表结构desc user；

查看一下表中数据：

select user，host from user；

如果你是没有创建其他的用户，只有root。

host表示可以在哪个地方登陆，%表示任意地方，localhostbendi登陆。

查看密码：

select user,host,authentication_string from user;

密码都是经过加密的。

创建一个用户，给与其权限：

我们创建一个zhangsan用户，密码为123456，只能通过本地访问，只能对student数据库曹志勇，只能select（读）操作。

首先用过root登陆。

创建用户给与权限：

grant select on student.* to 'zhangsan'@'localhost' identified by '123456';

想要给其他权限在select后用,隔开，写上insert等其他。

grant ：关键字

select ：查询（读）的权限

on：关键字

student.*:student下的所有表

to：关键字

zhangsan：用户名

@：关键字

localhost：允许本地登陆

identified by：关键字

123456：密码

这样就添加上了。

如果想要给与他正删改查的权限：

grant all privileges on student.* to 'zhangsan'@'%' identified by '123456';

all privileges :所有权限

%：表示任意地方都可以登录

修改权限

grant 权限名称 on 数据库 to 账户@登陆地方 with grant option;

例如：给张三添加一个insert的权限：

grant select,insert on student.* to 'zhangsan'@'localhost' with grant option;

flush privileges;

flush privileges:刷新权限就可以了。

修改密码

需要用root用户登陆来修改mysql数据库中的user表

update user set authentication_string=password('新密码') where user = '用户名';

例如：

update user set authentication_string=password('123') where user = 'zhangsan';

修改完之后也需要刷洗：

flush privileges;

password()：加密

删除用户：

drop user '用户名'@'主机'

远程连接

mysql -u用户名 -p密码 -hip地址

例如：

mysql -uroot -p123456 -h192.168.1.1

或者 mysql -h192.168.1.1 -uroot -p

然后输入密码进入

但是root用户一般都是本地登陆，因为root的用户名大家都知道，密码可以试出来，万一试出来就完了，修改root的登陆地址修改文件：

需要把mysql中的mysqld.cnf中的 bind-address -127.0.0.1注释掉。虽然方便了，但是没有了安全性。

mysql的主从

主从的意思是一个主数据库，1个或多个从数据库，从数据库是对主数据库的备份。

主从数据库是同步的，当一个修改，另一个也会变。

主从数据库的好处：

1、读写分明，可以用主数据库来进行增删改，从数据库用来查询。

2、数据的备份

3、负载均衡

配置主从的流程

1、首先备份主数据库中的数据。让从数据库和主数据库的数据一致。

密令：mysqldump -uroot -p123456 数据库名 > student.sql;

就会导出一份该数据库的所有sql。

注意：我们一般在导出数据的时候，都是全部导出，并且只允许查询操作：

mysqldump -uroot -p123456 –all-databases –lock-all-table >~/master_db.sql;

~/master_db.sql:存放的文件路径，可以自己写。

–lock-all-table：锁住所有的表

–all-databases：所有的数据库

2、恢复

执行：mysql -u用户名 -p密码 新的数据库名字 < student.sql;

如果是所有数据库都导出：就不需要写数据库的名字。

以上达到了数据都相同。

3、配置主服务器

打开mysql中的mysqld.cnf文件，保证server – id和log_bin没有被注释掉。

4、重启主服务器的mysql服务

5、配置从服务器

在主服务器中server-id的值为1，同样打开从服务器的mysqld.cnf文件，保证server-id不与主服务器

的值相同，一般情况用ip地址的最后一位，将从服务器的log_bin注释掉。

5、重启从服务器的mysql服务

6、在主服务齐全中创建一个用户给从服务器用

grant replication slave on *.* to 'ss'@'%' identified by '111'

*.*:表示所有数据库，所有表。

flish privileges;

刷新权限。

7、链接两个服务器

在从服务器中执行：change master to master_host = '主服务器ip地址'，master_user = 'ss',master_password='111',master_log_file='',master_log_pos=;

还有两个参数没有给值：

这两个参数来自主服务器，怎么查看？

在主服务器上登陆mysql，

执行：show master status;

可以看到其中有file字段和position字段，

master_log_file的字段就填写file字段中的内容，

master_log_pos的字典填写position地段中的内容。

8、查看是否成功

在从服务器中登陆，执行：show slave status;

查看到Slave_IO_Runing的值为Yes，和

Slave_SQL_Runing的值也为Yes,说明我们配置成功了。