Mysql介绍

Mysql介绍
一、存储引擎
1.存储引擎是：MySQL特有的，其它数据库没有
2.存储引擎的本质
通过采用不同的技术将数据存储在文件或内存中；
每一种技术都有不同的存储机制，不同的存储机制提供不同的功能和能力；
通过选择不同的技术，可以获得额外的速度或功能，改善我们的应用；
3.MySQL支持很多种，查看存储引擎，命令如下：
show engines\G
4.在创建表时，可使用< ENGINES> 选项为CREATE TABLE语句显示指定存储引擎
5.如果创建表时没有指定存储引擎，则使用当前默认的存储引擎
6.默认的存储引擎可在 my.ini 配置文件中使用 default-storage-engin 选项指定
7.修改表的存储引擎使用：ALTER TABLE 表名 ENGINE = 存储引擎名称
8.查看表使用的存储引擎，命令如下：
SHOW CREATE TABLE emp\G;
SHOW TABLE STATUS LIKE ‘emp’\G;
二、常用的存储引擎
1、MyISAM存储引擎
MyISAM引擎是MySQL数据库最常用的；
它管理的表具有以下特性：
使用三个文件表示每个表：
a) 格式文件 — 存储表的结构（mytable.frm）
b) 数据文件 — 存储表的数据（mytable.MYD）
c) 索引文件 — 存储表的索引（mytable.MYI）
可转换为压缩、只读表来节省空间
2、InnoDB存储引擎
它管理的表具体有以下特征：
a) 每个InnoDB表在数据库目录中以.frm格式文件表示
b) InnoDB表空间tablespace被用于存储表的内容
c) 提供一组用来记录事务性活动的日志文件
d) 用COMMIT（提交）、SAVEPOINT及ROLLBACK（回滚）支持事务处理
e) 提供全部ACID兼容
f) 在MySQL服务器崩溃后提供自动恢复
g) 多版本（MVCC）和行级锁定
h) 支持外键及引用的完整性，包括级联更新和删除
3、MEMORY存储引擎
使用MEMORY存储引擎的表，因为数据存储在内存中，且行的长度固定，所以使得MEMORY存储引擎非常快；
MEMORY存储引擎管理的表具有下列特征：
a) 在数据库目录内，每个表均以.frm格式文件表示；
b) 表数据及索引被存储在内存中；
c) 表级锁机制；
d) 字段属性不能包含TEXT或BLOB字段；
MEMORY存储引擎以前被称为HEAP引擎；
4、DBA命令
修改密码
进入mysql库：
use mysql;
update user set password = password(‘123456’) where user = ‘root’;
或者 ALTER USER ‘root’@‘localhost’ identified by ‘123456’;
或者 set password for ‘root’@‘localhost’=password(‘123456’);
刷新权限
flush privileges;
5、索引
索引对应的英文单词：index
索引的作用：相当于一本字典目录，提高程序的检索 / 查询效率；表中每一个字段都可添加索引
MySQL数据库中表的检索方式有2种：
全表扫描（效率较低）
通过索引检索（提高查询效率）
什么情况下适合给表中字段添加索引
该字段数据量庞大
该字段很少的DML操作（由于索引也需要维护，DML操作多的话，也影响检索效率）
该字段经常出现在where条件中
注：实际开发中会根据项目需求以及客户需求等综合因素来做调整
6、索引的应用
创建索引，语法结构：
a） create index 索引名 on 表名（列名）
b） create unique index 索引名 on 表名（列名）
注：添加unique表示在该表中的该列添加一个唯一性约束
示例：create index dept_dname_index on dept(dname);
查看索引，语法结构：
show index from 表名
show index from dept;
删除索引，语法结构：
drop index 索引名 on 表名;
drop index dept_dname_index on dept;
三、事务Transaction
1.事务是什么：
一个最小的不可再分的工作单元；
通常一个事务对应一个完整的业务；（如：银行转账业务）
而一个完整的业务需要批量的DML（insert、update、delete）语句共同完成；
事务只和DML语句有关系，或者说只有DML语句才有事务；
以上所描述的批量DML语句共有多少DML语句，这个和业务逻辑有关系，业务逻辑不同DML语句个数不同 ；
2.以上两条DML语句必须同时成功或者同时失败，因为它为最小业务单元，不可拆分；
当第一条DML语句执行成功之后，并不能将底层数据库中的第一个账户的数据修改，只是将操作记录了一下，这个记录是在内存中完成的
当第二条DML语句执行成功之后，和底层数据库文件中的数据完成同步
若第二条DML语句执行失败，将清空所有的历史操作记录
要完成以上功能，必须借助事务 transaction。对于数据库来说，事务保证批量的DML要么全成功，要么全失败。
3.事务具有四个特征ACID：
原子性（Atomicity）：事务是最小单元，不可再分；
一致性（Consistency）：事务要求所有的DML语句操作的时候，必须保证同时成功或同时失败；
隔离性（Isolation）：一个事务不会影响其它事务的运行；
持久性（Durability）：在事务完成之后，该事务对数据库所作的更改将持久地保存在数据库中，并不会被回滚；
4、开启事务：start transaction，任何一条DML语句执行，标志事务的开启；
结束事务：end transaction
提交事务：commit transaction，成功的结束，将所有的DML语句操作记录和底层硬盘文件中数据进行一次同步；
回滚事务：rollback transaction，失败的结束，将所有DML语句操作记录全部清空；
注意：
在事务进行过程中，未结束之前，DML语句是不会修改底层数据库文件中的数据。
只是将历史操作记录一下，在内存中完成记录。
只有在事务结束的，而且是成功结束的时候才会修改底层硬盘文件中的数据。
5、事务开启和结束的标志
结束事务：end transaction
提交事务：commit transaction
回滚事务：rollback transaction
四、MySQL添加约束、删除约束及修改约束
1.MySQL添加约束
添加主键约束：alter table 表 add constraint 约束名称 primary key(主
键字段);
alter table t_student add constraint pk primary key(student_id);
添加外键约束：alter table 从表 add constraint 约束名称 foreign key 从
表(外键字段) references 主表(主键字段);
alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id);
添加唯一性约束：alter table 表 add constraint 约束名称 unique 表(字段);
alter table t_student add constraint uk unique(email);
2.MySQL修改约束，其实就是修改字段
alter table t_student modify student_name varchar(30) unique;
mysql对有些约束的修改不支持，所以我们可以先删除，再添加
3.MySQL删除约束
删除主键约束：alter table 表名 drop primary key ;
alter table t_student drop primary key;
删除外键约束：alter table 表名 drop foreign key 外键（区分大小写）;
alter table t_student drop foreign key fk_classes_id;
删除其他约束：alter table 表名 drop key 约束名称 ;
alter table t drop key uk;