数据库学习（三）-------用户，数据管理，存储引擎，锁，调优，Pyhton连接

最新推荐文章于 2022-06-02 10:19:33 发布

Imilhy

最新推荐文章于 2022-06-02 10:19:33 发布

阅读量230

点赞数

本文链接：https://blog.csdn.net/Imilhy/article/details/104862548

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一.MySQL用户账户管理

1.开启MySQL远程连接

启动Mysql
切换到 Mysql库

mysql> use mysql;
Database changed

查看已有用户

mysql> select user,host,plugin from user;

授权：

CREATE USER 'root'@'%' IDENTIFIED BY '你的密码';
GRANT ALL ON *.* TO 'root'@'%';

2.添加授权用户

CREATE USER 'new_user'@'IP地址' IDENTIFIED BY 'passwd';

IP地址表示方式：

%意味着用户可以从任何地址连接到服务器，
指定的IP地址
localhost表示用户只能本地连接

再次查看用户

mysql> select user,host,plugin from user;

这时我们发现用户列表中有了我们刚刚添加的用户。

3.给用户授权

语法：

GRANT 权限列表 on 库.表 TO 'new_user'@'IP地址' with grant option;#表示在某库某表上使用权限列表中的权限

权限列表示例：

select,update,delete,insert,alter,drop,create...

这里我们用下面的语句表示授权所有权限

GRANT ALL ON *.* TO 'new_user'@'%';

4.登录测试

C:\Users\Ivy\mysql-8.0.19-winx64\bin>mysql -unew_user -p#用刚刚添加的用户进行本地登录
Enter password: ********

在其他电脑上通过访问本机IP地址连接

C:\Users\Ivy\mysql-8.0.19-winx64\bin>mysql -hIP地址 -unew_user -p 
Enter password: ********

5.删除授权用户

drop user '用户名'@'IP地址';

二.数据管理

1.数据备份

在终端输入命令

mysqldump -uroot -p 库名 > 备份路径/filename.sql #为了显示是数据库的备份文件，接着按照提示输入数据库登录的密码

库的选择：

--all-databases   #备份所有库
库名               #备份单个库
-B 库1 库2...      #备份多个库，注意不用逗号用空格隔开
库名 表1 表2...     #备份指定库的指定表

2.数据恢复

由于是恢复数据，所以要先在数据库中创建一个新库，或在新库中创建一个新表，用于存放要恢复的数据
在终端输入命令

mysql -uroot -p 目标库名 < 路径/filename.sql #为了显示是数据库的备份文件，接着按照提示输入数据库登录的密码

示例：先备份，然后删除，然后创建空库，最后再进行恢复

3.备份分类

完全备份：mysqldump
增量备份:binlog日志，xbackup工具

应用：从所有库的备份文件中恢复某一个库

mysql -uroot -p --one-database 目标库名 < 路径/filename.sql

注意：创建一个空库，然后再在其中创建一个表，再进行信息恢复时，这个表仍然存在，不会被覆盖掉。

三.事务和事务回滚

1.定义：一件事从发生到结束的整个过程

2.作用：确保数据的一致性

3.应用：

1.mysql中默认sql语句会自动commit到数据库，可以通过show varianles like 'autocommit’查看
2.开启事务：

start transaction  #不会对数据库造成影响

3.终止事务

commit  #输入该命令时才会对数据库操作
rollback #事务中命令没有成功时，那么就会仍保持原状态

4.说明：事务回滚只针对表记录的增删改，对创建库，表无效。

四.存储引擎

1.定义：用来处理表的处理器

2.存储引擎基本操作

查看已有表的存储引擎

show create table 表名;#engine=...

查看所有存储引擎

show engine

创建表时指定存储引擎

create table 表名(...)engine=myisam

3.常用的存储引擎

1.innodb

1.特点：
（1）共享表空间，每个表有两个文件：表名.frm(表结构），表名.ibd(记录和索引信息）
（2）支持行级锁

2.myisam

1.特点
（1）独享表空间，每个表有三个文件，表名.frm(表结构），表名.myd(表记录），表名.myi(索引信息）
（2）支持表级锁

3.memory存储引擎

（1）存储在内存空间中，查询速度快。只有一个文件，表名.frm文件
（2）缺点：一旦服务重启，创建的表结构还在，但是内容会全部丢失

4.存储引擎的选择

（1）执行查询操作多的表使用myisam存储引擎
（2）执行写操作比较多的表使用innodb存储引擎

5.更改默认存储引擎

在ini文件中设置

default-storage-engine = myisam

然后再重启mysql服务

五.锁

1.作用：解决客户端并发访问的冲突问题

2.锁类型：读锁和写锁

（1）读锁：也称共享锁

用于select 操作，
加锁之后不能更改表中的内容，但可以进行查询

（2）写锁：也称互斥锁，排他锁

用于（insert,update,delete）操作，

3.锁粒度

表级锁，行级锁

4.关系：

锁的范围取决于存储引擎，操作取决于读锁和写锁

六.MySQL调优

选择合适的存储引擎：经常用来读的表使用myisam存储引擎，其余都用innodb
SQL语句调优（尽量避免食用全表扫描）
（1）在select where order by 常涉及的字段上建立索引
（2）where子句中不使用！=，否则将放弃使用索引进行全表扫描
（3）尽量避免使用NULL值判断，否则会全表扫描。
（4）尽量避免使用or来连接条件，导致全表扫描
（5）模糊查询尽量避免使用前置%导致全表扫描
（6）尽量避免in和not in，导致全表扫描
（7）尽量不使用select * … ,要用具体的字段列表代替 *，不要返回用不到的字段

七. Python连接数据库

1.创建数据库连接

db = pymysql.connect('localhost','用户名','密码',\
                   '库名',charset='utf8')

2.connect对象支持的方法

db.cursor()     #创建游标对象
#提交命令
db.commit()
#回滚
db.rollback()
#断开连接
db.close()

游标对象支持的方法

#执行SQL命令
cursor.execute('SQL命令')
#取得结果集的第一条记录
cursor.fetchone()
#取得结果集的n条记录
cursor.fetchmany(n)
#取得结果集的所有记录
curosr.fetchall()
#关闭游标对象
cursor.close()