MySQL学习--第三天

  • MySQL账户管理概述
  • 用户与权限管理
  • 创建用户并授予权限
  • 查看用户权限
  • 回收权限
  • 修改密码
  • 删除用户
  • 事务概述
  • 事务处理
  • 手动提交事务
  • 自动提交模式
  • 视图
  • 索引
  • 创建索引
  • 查询速度测试
  • 存储过程概述
  • 创建存储过程
  • 调用存储过程
  • 删除存储过程
  • 存储过程中的变量
  • 条件语句
  • MySQL函数
  • 内置函数--字符串函数
  • 自定义函数
  • 存储过程与函数的区别
  • python 操作数据库


MySQL账户管理概述

  • MySQL的账户管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。通过账户管理，可以保证MySQL数据库的安全性
  • MySQL中的root账号拥有最高权限，包括删库，删表。在生产环境下一班不会使用root账号登录数据库，MySQL中的用户信息保存在mysql库下的user表中

如：

mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

用户与权限管理

创建用户并授予权限

创建用户：create user'用户名'@'ip地址';
授权操作：grant 权限 on 数据库 to '用户名'@'ip地址';
刷新权限：flush privileges;

权限有如：create，alter等等，如果要分配所有权限，直接使用：all privileges
创建一个’www’用户并授予insert，alter，create权限：

mysql> create user'www'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant insert,alter,create on test to 'www'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看用户权限

show grants for 用户名;

显示’www’的权限：

mysql> show grants for www;
+------------------------------------------------------------+
| Grants for www@%                                           |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `www`@`%`                            |
| GRANT INSERT, CREATE, ALTER ON `mysql`.`test` TO `www`@`%` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

回收权限

revoke 权限 on 数据库 from 用户信息;

回收’www’用户的 insert，create权限：

mysql> revoke insert,create on test from 'www'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for www;
+--------------------------------------------+
| Grants for www@%                           |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `www`@`%`            |
| GRANT ALTER ON `mysql`.`test` TO `www`@`%` |
+--------------------------------------------+
2 rows in set (0.00 sec)

修改密码

第一种方式：普通用户修改自己的密码
在终端上修改不需要进入数据库

mysql admin -u用户名 -p password 新密码

第二种方式：root账号修改普通用户密码：

update mysql.user set authentication_string=新密码 where user='用户名';

删除用户

第一种方式:

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

第二种方式：

进入mysql库中：delete from user where user='用户名';

事务概述

  • 事务：也称为工作单元，是由一个或多个SQL语句所组成的操作序列，这些SQL语句所谓一个完整的工作单元，要么全部执行成功，要么全部执行失败。在数据库中，通过事务来保证数据的一致性。
  • 事务处理语言（Transaction Process Language，TPL）:主要用来对组成事务的DML语句的操作结果进行确认或取消。确认也就是是DML操作生效，使用提交（COMMIT）命令实现，取消也就是使DML操作失效，使用回滚（ROLLBACK）命令实现。
  • 通过事务的使用，能防止数据库中出现数据不一致现象
  • MySQL是支持事务的，跟使用的引擎相关。MySQL中支持多种引擎，默认使用Innodb引擎支持事务
    事务的特性（ACID）：
  • 原子性（Atomicity）：事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况
  • 一致性（Consistency）：一旦事务完成，不管是成功还是失败的，整个系统处于数据一致的状态
  • 隔离性（Isolation）：一个事务的执行不会被另外一个事务所干扰
  • 持久性(Durability):也称永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚

事务处理

手动提交事务

  • 使用begin，rollback，commit来实现，用begin开启事务后在没有commit提交之前执行修改命令，变更会维护到本地缓存中，而不维护到务理表中，只有在commit提交之后才会更新到务理表中。如果中间执行错误，那么用rollback回滚事务，恢复到执行事务前的状态

自动提交模式

  • MySQL默认是自动提交的，也就是提交一个sql，就直接执行。可以通过过set autocommit=0 禁止自动提交，set autocommit=1开启自动提交，来实现事务的处理
  • 注意：当set autocommit=0时，以后所有的sql都将作为事务处理，知道用commit或rollback结束，且结束这个事务的同时，也开启了新的事务

视图

命令：

create view 视图名称 as select语句;
  • 对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来十分麻烦。这种情况可以定义视图解决，视图本质上就是对查询语句的封装。视图实际上就是查询。
    -视图是一张虚拟的表，这个表的数据结构和数据是由select语句来指定的，不会生成真实的文件。

需要用到视图的场景：

  • 场景一：如果某个查询结果出现的非常频繁，也就是说，经常用这个查询结果来做子查询，此时可以使用视图，用户可以将注意力集中在所关心的数据上可以定义结构简单、清晰地查询操作**[视图]**。
  • 场景二：保密的诉求，可以过滤敏感数据，比如有一个工资表，希望只有财务能看到员工工资，其他人看不到，那么就可以使用视图来包装这样的数据，过滤掉敏感的字段

索引

  • 一般的应用对数据库大部分操作都是查询，所以查询速度显得尤为重要。
  • 建立索引是一个有效的优化方案，索引就好比一本书的目录，它会让你更快的找到内容，但需要注意，索引也是要占磁盘空间的
  • 选择索引的数据类型，越小的数据类型越好
  • MySQL常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

创建索引

  • PRIMARY KEY（主键索引）：不允许有空值
alter table 表名 add primary key (列名)：
  • UNIQUE（唯一索引）：索引列的值必须唯一，允许有空值
alter table 表名 add unique (列名);
  • INDEX （普通索引）：无限制
alter table 表名 add index 索引名称 (列名);
  • FULLTEXT（全文索引）：仅可用于myisam，针对的是较大的数据
alter table 表名 fulltext (列名);
  • 组合索引：遵循最左前缀原则
alter table 表名 add index 索引名(列1,列2,列3);

查询速度测试

  • 开启profiling参数，M有SQL的Query Profiler 是一个使用非常方便的Query 诊断分析工具
set profiling=1;
show profiles;

存储过程概述

  • 存储过程，也翻译为存储程序，是一条或者多条SQL语句的集合，可以视为批处理

创建存储过程

命令：

delimiter //
create procedure 存储过程名称(参数列表)
begin
sql 语句
end
//
delimiter ;
  • delimiter 用于设置sql语句分隔符，默认为分号
  • 在"sql语句"部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程钱需要指定其他符号作为分隔符，此处使用//，也可以使用其他字符
    创建一个存储过程：
delimiter //
 create procedure text_3()
 begin 
 select * from student where id=2;
 end 
 //
 delimiter ;
 
show procedure STATUS;

结果：

mysql视图用索引 mysql视图索引登录_mysql

调用存储过程

命令：

call 存储过程;

如：

call text_3();

mysql视图用索引 mysql视图索引登录_mysql_02

删除存储过程

命令：

drop procedure 存储过程名称;

存储过程中的变量

  • 声明变量：
declare 变量名 数据类型 默认值等; 不申明默认值则默认值为null\
/也可以直接使用 @变量名，以此来声明临时变量，这样就不用正式声明了
给变量赋值
set 变量名=值;

条件语句

单分支：
IF expression THEN
    statements;
END IF;
多分支：
IF expression THEN
    statements;
ELSEIF elseif-expression THEN
    elseif-statements;
ELSE 
    else-statements;
END IF;

WHILE语句：
WHILE expression DO
      statements
END WHILE

MySQL函数

内置函数–字符串函数

  • 查看字符的ascii码值：
select ascii('a')
  • 查看ascii码值对应的字符
select char(数字)
  • 拼接字符串
select concat(str1,str2,...)
  • 包含字符个数
select length('str')
  • 截取字符串
left(str,len)返回字符串str左端的len个字符
right(str,len)返回字符串str右端的len个字符
substring(str,pos,len)返回字符串str从pos起的len个字符
  • 去除空格
ltrim(str)返回删除了左空格的字符串str
rtrim(str)返回删除了右空格的字符串str
trim(方向 remstr from str)返回从某侧删除remstr后的字符串str
方向词：both，leading，training，表示两侧，左，右
  • 返回由n个空格字符组成的一个字符串
select space(n)

自定义函数

定义函数：
delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter ;

调用函数：
select 函数名()

存储过程与函数的区别

  • 1.函数的限制较多，如不能使用临时表，只能使用表变量，存储过程限制比较少
  • 2.存储过程实现的功能逻辑相对说更复杂，而函数的实现功能针对性更强
  • 3.函数必须要有返回值，而且仅仅返回一个结果集，存储过程可以没有返回值，但是能返回结果集
  • 3.调用语法不同：
    1.函数经常会嵌入到sql中使用，通过select 函数名()
    2.存储过程通过call 存储过程名 来调用

python 操作数据库

  • 1.通过pip install pymysql库
  • 2.连接数据库，创建Connection对象：用于建立与数据库的连接
  • 3.获取cursor对象，cursor对象用于执行sql语句
    cursor对象拥有的方法：
    1.close()，关闭cursor对象
    2.execute(operation[,parameters])执行语句，返回受影响的行数，可以执行所有语句
    3.fetchone()获取查询结果集的第一个行数据，返回一个元组
    4.fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
from pymysql import *

# 创建数据库连接

conn = connect(host='id', user='root', password='123456', database='test', charset='utf8')
# 创建一个游标对象,可以利用这个对象进行数据库操作
try:
    cur = conn.cursor()
    print('success')
except Exception as  ex:
    print(ex)
finally:
    cur.close()
    conn.close()