MySQL学习--第三天
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;
结果：
调用存储过程
命令：
call 存储过程;
如：
call text_3();
删除存储过程
命令：
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()
2412
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
