MySQL学习–第三天

2163-王同学

发表文章数:35

热门标签

, , ,
首页 » 数据结构 » 正文

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学习--第三天

调用存储过程

命令:

call 存储过程;

如:

call text_3();

MySQL学习--第三天

删除存储过程

命令:

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()
    

未经允许不得转载:作者:2163-王同学, 转载或复制请以 超链接形式 并注明出处 拜师资源博客
原文地址:《MySQL学习–第三天》 发布于2021-10-11

分享到:
赞(0) 打赏

评论 抢沙发

评论前必须登录!

  注册



长按图片转发给朋友

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

Vieu3.3主题
专业打造轻量级个人企业风格博客主题!专注于前端开发,全站响应式布局自适应模板。

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录