Mysql表类型和引擎

事务安全型：支持事务
非事务安全型：不支持事务

Memory整个数据是存放在内存中的
只有Innodb支持事务
只有Innodb支持外键

哈希：为了提交检索速度而设计的一种数据结构

3种常用表类型的详细说明

MEMORY:对需要经常操作的不需要持久化的数据比较合理。这种需求很多


-- 1.查看所有的存储引擎
SHOW ENGINES

存储引擎2

#表类型和存储引擎

-- 1.查看所有的存储引擎
SHOW ENGINES

-- innodb前面使用过
-- 1.支持事务2.支持外键3.支持行级锁
-- myisam
-- 特点：1.添加速度快2.不支持外键3.支持行级锁
-- 1.创建一个实验表
CREATE TABLE t30(
	id INT ,
	`name` VARCHAR(32))ENGINE MYISAM

-- 进行事务测试
-- 开始一个事务
START TRANSACTION
-- 设置一个保存点
SAVEPOINT t1;

INSERT INTO t30 VALUES(1,'jack');

-- 进行回滚到t1时

ROLLBACK TO t1-- 此时将会出现警告

SELECT * FROM  t30 -- 数据还在没有回滚成功
COMMIT
-- memory 存储引擎
-- 1.数据存储在内存中【关闭了mysql服务，数据丢失，但是表的结构还在】2.执行速度很快（没有I/O读写）3.默认支持索引（基于哈希表）


-- 验证数据存储在内存中，关闭了Mysql服务，数据丢失
CREATE TABLE t31(
	id INT ,
	`name` VARCHAR(32))ENGINE MEMORY


INSERT INTO t31 VALUES(1,'tom'),(2,'jack')

SELECT * FROM t31-- 此时表中有2条记录

接下来我们关闭Mysql服务接着开启mysql服务

-- 查询表结构

DESC t31-- 表结构还在

SELECT * FROM t31-- 表的数据已经消失

三种存储引擎的选择

memory的使用

用户的状态频繁变化，我们可以将用户状态专门使用一个memory表进行存储，当mysql重启后状态信息丢失没有关系，重新写入即可
指令修改存储引擎

ALTER TABLE t31 ENGINE=INNODB

视图原理

视图存在的意义
视图的概念

将对应的真实的表称为基表

我们不将真实的表暴露给用户，而是将这个表对应的视图交给用户。这个视图里面有我们允许用户可以进程查询的列（用户权限控制），当用户查询到该列的信息时，我们将到真实的表中进行寻找并展示

视图的列和真是表的列是一一映射的。也可以通过这种关系进行修改

视图和基表的关系

视图的使用细节

基本使用

-- 视图的使用
-- 创建一个视图emp_view01,只能查询emp表的(empno,ename,job 和deptno)信息
-- 1.创建一个emp表的视图emp_view01(并且指明了它可以访问的列)
CREATE VIEW emp_view01
AS SELECT empno,ename,job,deptno FROM emp
-- 2.查看视图结构(视图是一个虚假的表，展现的是一个表的结构)
DESC emp_view01

-- 3.查看视图内容(可以和表一样指定要查看的列)

SELECT * FROM emp_view01;-- 此时可以查看所有的列的信息
-- 4.修改视图（将指定的视图修改成一个新的视图）

ALTER VIEW emp_view01 AS SELECT 
ename FROM emp-- 改成了只有ename进行映射的视图 

SELECT * FROM emp_view01;-- 此时只能看到一个列的信息

-- 5.查看创建的视图
SHOW CREATE VIEW emp_view01
-- 6.删除视图
DROP VIEW emp_view01;

视图的细节讨论

视图emp_view01结构

-- 视图的使用细节
-- 1.创建视图后，到数据库看，对应的视图只有一个结构文件
-- 而不存在数据（形式：视图名.frm）
-- 2.视图的数据会影响基表，基表的数据变化也会影响视图【insert update delete】
-- 1.创建一个emp表的视图emp_view01(并且指明了它可以访问的列)
CREATE VIEW emp_view01
AS SELECT empno,ename,job,deptno FROM emp

-- 2.修改视图影响基表
UPDATE emp_view01
SET job='MANAGER'
WHERE empno=7369
SELECT * FROM emp-- 此时的基表的数据被改变
-- 3.修改基表影响视图
UPDATE emp
SET job='CLARK'
WHERE empno=7369

SELECT* FROM emp_view01-- 此时视图的数据被改变

-- 视图中可以再使用视图（数据还是来自基表）
-- 从emp_view01中选出empno和ename做出新的视图

CREATE VIEW emp_view02
AS SELECT empno,ename FROM emp_view01

SELECT * FROM emp_view02-- 此时只会显示2列
-- 只能中间多映射了依次而已

视图应用实例

练习

-- 视图课堂练习
-- 针对emp,dept,salgrade 张表。创建一个视图emp_view03
-- 可以显示雇员编号,雇员名，雇员部门名称和薪水级别【即使用
-- 三张表联合查询，得到结果】

-- 方式一：使用三表查询实现

-- 过滤条件：emp表的部门编号和dept的部门编号一致
-- emp表的sal在salgrade表相应的级别之间
SELECT empno,ename,dname,grade
FROM emp,dept,salgrade
WHERE emp.`deptno`=dept.`deptno`AND
(sal BETWEEN losal AND hisal)

-- 方式二：将上述结果设计成视图
CREATE VIEW emp_view03
AS  (SELECT empno,ename,dname,grade
FROM emp,dept,salgrade
WHERE emp.`deptno`=dept.`deptno`AND
(sal BETWEEN losal AND hisal)
)

SELECT * FROM emp_view03

在方式二中我们将多表查询后的过滤后的结果映射成一个视图。在以后向查询多表中的数据时就只需要查看视图了。视乎想要进行多表映射这样做也是很好的选择，因为已经提前将多表笛卡尔中不合法的数据给过滤了

Mysql管理

mysql用户管理

localhost表示只允许本机登录，也可以指定ip，则说明只允许指定的ip登录

-- mysql用户的管理

-- 原因：当我们做项目开发时，可以根据不同的开发人员，付给它相应的mysql操作权限
-- 所以，mysql数据库管理人员(root)根据需要创建不同的用户 ，赋予相应的权限
-- 共人员使用
-- 1.创建新的用户
-- 解读：1.‘hsp_edu’@'localhost'表示用户的完整信息 'hsp_edu'用户名 localhost登录的ip
-- 2.1234546 登录的密码 但是注意存储到mysql.user时，是password('123456')加密后的密码



CREATE USER 'hsp_edu'@'localhost'IDENTIFIED BY '123456'

SELECT * FROM mysql.`user`-- 这样查询可以不用切换数据库

添加用户后查看user表

我们发现user表中增加了hsp_edu这个用户。authentication_string表示的是用户的密码（加密后的）这个表中还有一个权限

-- 2.删除用户(要写上用户的完整信息)
DROP USER 'hsp_edu'@'localhost'

-- 3.登录

接下来我们登录刚刚添加的用户

但是我们可以看到2个用户的权限完全不同。我们也可以授权给其他用户让他可以扩大权限也可以将指定用户的权限收回
修改密码

-- 修改密码


-- hsp_edu角度
-- 1.可以修改自己的密码

SET PASSWORD = PASSWORD('abcdef')-- 此时可以成功
-- 修改别人的密码
SET PASSWORD FOR 'root'@ 'localhost' = PASSWORD('123456')-- 权限不够，将会失败


-- root角度

-- 修改别人密码
SET PASSWORD FOR 'root'@ 'localhost' = PASSWORD('123456')-- 可以修改

mysql权限管理

通过root用户对其他用户进行权限管理

mysql权限表如下
1.给用户授权
给用户授权，你自己必须有这个授权的权限，一般来讲是通过root用户来执行

权限一定是针对某个用户在某个库里面的某个数据对象的

grant all on . to '用户名'@‘登录位置’这样写相对于给该用户赋予了相当于root的权限了，慎用

一般在mysql5.7赋予了权限就立即生效了

想要从本地登录写localhost如果想要从远程登录讲localhost改成远程的ip或者是主机名

-- 演示用户权限的管理

-- 1.创建用户shunping 密码123，从本地登录
CREATE USER 'shunping'@localhost IDENTIFIED BY '123'
-- 此时我们的shunping用户只能看到一个默认的系统数据库
-- 2.yon root用户创建testdb数据库和它下面的表news
CREATE DATABASE testdb

CREATE TABLE news
(
	id INT ,
	content VARCHAR(32));
-- 插入一条记录

INSERT INTO news VALUES(100,'北京新闻');
-- 此时我们发现我们创建的shunping用户还是看不到这个信息
-- 3.给shunping分配查看news表和添加news的权限
-- 此时我们的权限只是对该表有效
GRANT SELECT,INSERT
ON testdb.news
TO 'shunping'@'localhost'

此时我们可以看到在shunping这个用户中出现了一个testdb数据库和一个news表

-- 接下来我门使用shunping操作数据库验证权

上图中我对news表进行了select和Insert操作，都可以执行，但是当我们想进行update操作时，却被拒绝了。因为shunping用户没有这个去权限

我们给给它再次增加权限，然后验证
此时我们发现，shunping用户可以使用update功能了
通过root修改shunping的密码为abc

-- 修改shunping的密码为abc
SET PASSWORD  FOR 'shunping '@'localhost'=PASSWORD('abc')

此时我们发现我们再次登录shunping用户时只能使用abc进行登录

演示用户权限回收

-- 回收shunping在testdb.news表中的所有权限
REVOKE SELECT,UPDATE,INSERT ON testdb.news
 FROM 'shunping'@'localhost'


-- 下面的一种写法也可以表示所有权限


REVOKE ALL ON testdb.news
 FROM 'shunping'@'localhost'

此时在我们的shunping这个用户里面就看不到我们的testdb.news表了

如果mysql版本比较就，权限收回可能需要执行以下权限生效指令，

使用root用户删除shunping这个用户

-- 删除 shunping
DROP  USER 'shunping'@'localhost'

mysql管理细节

细节一查询的user表详情

-- 说明用户管理的细节
-- 1.在创建用户的时候，如果不指定host,则默认为%
-- %表示所有IP都有连接权限(而且不需要密码)
CREATE USER jack
-- 详情见图片
SELECT `user`,`user`,authentication_string FROM USER

-- 2.可以指定一个用户在一个Ip段登录

-- create user 'xxx'@'192.168.1.%'
-- 表示xx用户在192.168.1.（以这个开头）*的ip可以登录

-- 3.在删除用户的时候，如果host不是%，则需要明确指定
-- ‘用户’@‘host’值

DROP USER jack-- 默认是 DROP USER jack @'%'

posted @ 2023-04-19 18:44 一往而深，阅读(38) 评论(0) 编辑收藏举报

刷新页面返回顶部