10.1 存储引擎
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)。
-
存储引擎(Storage Engines)主要包括MyISAM、innoDB、Memory等。
-
MySQL的表类型由存储引擎决定。
-
MySQL 数据表主要支持六种类型:CSV、Memory、ARCHIVE、MRG MYISAM、MYISAM、InnoBDB。根据是否支持事务,将六种类型分为两大类:”事务安全型”(transaction-safe),比如:InnoDB;其余都属于第二类,称为”非事务安全型”(non-transaction-safe)[mysiam 和memory]。
- MyISAM:mysql最早提供。不支持事务和外键,但其访问速度快,对事务完整性没有要求,支持表级锁。
- MyISAM Merge:将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。
- InnoDB:提供了具有提交、回滚和崩溃恢复能力的事务安全。支持事务、外键和行级锁。但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。也是目前MySQL默认的存储引擎。
- Memory(heap):使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常快(没有IO读写),因为它的数据是放在内存中的,并且默认使用HASH索引。但是一旦MySQL服务关闭,表中的数据就会丢失掉,表的结构还在,对临时表有用。
- archive:这种类型只支持select 和 insert语句,而且不支持索引。常应用于日志记录和聚合分析方面。
# 查看所有存储引擎
SHOW ENGINES;
# 修改存储引擎
ALTER TABLE 表名 ENGINE=存储引擎;
-- innodb 存储引擎,是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁
-- myisam 存储引擎
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
CREATE TABLE t28 (
id INT,
`name` VARCHAR(32)) ENGINE MYISAM;
-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了 Mysql 服务,数据丢失, 但是表结构还在]
-- 2. 执行速度很快(没有 IO 读写) 3. 默认支持索引(hash 表)
CREATE TABLE t29 (
id INT,
`name` VARCHAR(32)) ENGINE MEMORY;
存储引擎的选择:
如果你的应用不需要事务,处理的只是基本的CRUD操作,那么MyISAM是不二选择,速度快。
如果需要支持事务,选择InnoDB
Memory 存储引擎就是将数据存储在内存中,由于没有磁盘I/O的等待,速度极快。但由于是内存存储引擎,所做的任何修改在服务器重启后都将消失。(经典用法:用户的在线状态)
10.2 视图
1. 需求:
表的列信息很多,有些是重要信息,如果我们希望某个用户只能査询部分列信息(不重要),有什么办法?=>视图。即让用户查的其实是包含部分列信息的视图,而不是原始的表。
2. 视图概念:
-
视图是一个虚拟表,其内容由查询定义,有基表创建(基表可以有多个)。
-
同真实的表一样,视图包含列,其数据来自对应的真实表(基表)。
-
通过视图可以修改基表数据,修改基表数据也会改变视图。
-
创建视图后,到数据库去看,对应视图只有一个视图结构文件(形式: 视图名.frm),而没有数据文件。
-
视图中可以再使用视图,数据仍然来自于基表。
3. 视图的使用:
# 创建视图
create view 视图名 as select 语句
CREATE VIEW emp_view01
AS
SELECT empno, ename, job, deptno FROM emp;
# 更新成新的视图
alter view 视图名 as select 语句
# 查看创建视图的指令
SHOW CREATE VIEW 视图名
# 删除视图
drop view 视图名1,视图名2;
# 对emp、dept、salgrade三张表.创建一个视图
# 显示雇员编号,雇员名,雇员部门名称和薪水级别
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)
10.3 mysql用户管理
1. Mysql用户
mysql中的用户,都存储在系统数据库mysql中 user 表中。
不同的数据库用户,登录到DBMS,根据相应的权限,可以操作的数据库和数据对象不相同。
可以通过授权和回收使不同用户操作其他用户的数据库。
其中user表的重要字段说明:
host: 允许登录的“位置”,localhost表示该用户只允许本机登录,也可以指定ip地址,比如:192.168.1.100
user:用户名
authentication_string:密码,是通过mysql的password()函数加密之后的密码。
# 创建用户,同时指定密码
create user '用户名’@’允许登录位置’ identified by '密码';
-- 创建用户 shunping 密码 123 , 从本地登录
CREATE USER 'shunping'@'localhost' IDENTIFIED BY '123'
# 删除用户
drop user '用户名’@'允许登录位置';
# 用户修改密码
# 修改自己的密码:
set password = password('密码');
# 修改他人的密码(需要有修改用户密码权限)
set password for'用户名'@'登录位置’ = password('密码');
-- 修改 shunping 的密码为 abc
SET PASSWORD FOR 'shunping'@'localhost' = PASSWORD('abc');
在创建用户的时候,如果不指定@’允许登录位置’, 则为% , %表示所有 IP 都有连接权限
-- create user xxx;
CREATE USER jack
也可以这样指定IP段
-- create user 'xxx'@'192.168.1.%' 表示 xxx 用户在 192.168.1.*的 ip 可以登录 mysql
CREATE USER 'smith'@'192.168.1.%;
在删除用户的时候,如果 host 不是 %, 需要明确指定 '用户'@'host 值'
DROP USER jack -- 默认就是 DROP USER 'jack'@'%'
DROP USER 'smith'@'192.168.1.%
2. mysql中的权限管理:授权和回收
权限列表:
2.1 授权
# 给用户授权
grant 权限列表 on 库.对象名 to '用户名’@’登录位置';
grant 权限列表 on 库.对象名 to '用户名’@’登录位置' identified by '密码';
-- 给 shunping 分配查看 news 表和 添加 news 的权限
GRANT SELECT , INSERT
ON testdb.news
TO 'shunping'@'localhost'
-
权限列表,多个权限用逗号分开
-
库.对象名如果写成:
(1) *.*:代表本系统中的所有数据库的所有对象(表,视图,存储过程)
(2) 库.*:表示某个数据库中的所有数据对象(表,视图,存储过程等)
-
identified by可以省略,也可以写出:
(1) 如果用户存在,授权的同时修改该用户的密码
(2) 如果该用户不存在,创建该用户同时授权
2.2 回收用户授权
revoke 权限列表 on 库.对象名 from'用户名"@"登录位置';
2.3 权限生效指令
如果权限没有生效执行:
FLUSH PRIVILEGES;