老杜 mySql自学笔记day3

目录

一.存储引擎（了解）

1.什么是存储引擎？存储引擎有什么用？

2.怎么给表添加常用的引擎

3.查看Mysql支持哪些引擎（讲的较粗）

4.常见的存储引擎

二.事务（Transaction）很重要的章节******

1.什么是事务  

2、和事务相关的语句只有：DML语句。（insert delete update）

3.事务是如何完成多条语句同时成功或失败的

4.怎么提交事务，怎么回滚事务

5.事务的特性

三.索引（index）

1、什么是索引？有什么用？

2.怎么创建索引对象？怎么删除索引对象？

3.什么时候考虑给字段添加索引？（满足什么条件）

5、查看语句是否使用了索引

6.索引失效

7.索引的分类

四.视图

1.什么是视图？

2.怎么创建视图？怎么删除视图？

3.视图的用途

4.面向视图操作

五.DBA命令（数据的导入和导出）

六.数据库设计的三范式

1.什么是设计范式？

2、三范式都是哪些？

3.总结

4.嘱咐

---

一.存储引擎（了解）

1.什么是存储引擎？存储引擎有什么用？

存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字， 就是“表的存储方式”）

mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。 每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

2.怎么给表添加常用的引擎

查询创建表时使用的引擎

show create table t_student;

显示表创建时的信息，外加使用到得引擎信息

| t_student | CREATE TABLE `t_student` (
  `sno` int NOT NULL,
  `sname` varchar(255) DEFAULT NULL,
  `classno` int DEFAULT NULL,
  PRIMARY KEY (`sno`),
  KEY `classno` (`classno`),
  CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

 可以在创建表的最后边“）”的右边指定所用到得字符集和引擎

ENGINE：指定引擎       CHARSET：指定字符集

默认的引擎是：InnoDB  默认的字符集是：utf8mb3

将字符集更换为自己指定的

 create table t_product(
    id int primary key,
    name varchar(255)
 )engine = InnoDB default charset =gbk;

 查询

 show create table t_product;


| t_product | CREATE TABLE `t_product` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |

3.查看Mysql支持哪些引擎（讲的较粗）

命令

show engines \G

支持哪些引擎看数据库的版本

 Support: YES代表着支持

查看数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+

4.常见的存储引擎

MyISAM引擎

Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO

MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
MyISAM采用三个文件组织一张表：
            xxx.frm（存储格式的文件）
            xxx.MYD（存储表中数据的文件）
            xxx.MYI（存储表中索引的文件）  
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务。

小知识点：

对于一张表来说，有主键或者有unique约束的字段会自动创建索引

mysql当中自带的数据库mysql下的 columns_priv表格使用的就是这个引擎

mysql> show create table  columns_priv;

InnoDB引擎

Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES

   
优点：支持事务、行级锁、外键，mvvc(多版本)等。这种存储引擎数据的安全得到保障。
        
表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
InnoDB支持级联删除和级联更新。

最大特点：

支持事务，以保证数据的安全。（下面会将什么是事务）
    

 MEMORY引擎

Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO

特点：每个表以  .frm 为结尾   表级锁机制
缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快。
以前叫做HEPA引擎。
 

二.事务（Transaction）很重要的章节******

1.什么是事务  

 一个事务是一个完整的业务逻辑单元，要不同时成功要不同时失败，不可再分。

 比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：

从A账户上减去10000元，从b账户上加入10000元

update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';

  
 以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

 要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

2、和事务相关的语句只有：DML语句。（insert delete update）

为什么？

因为它们这三个语句都是和数据库表当中的“数据”相关的。
事务的存在是为了保证数据的完整性，安全性。

假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

不需要事务。

但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。本质上：事务就是保证多个DML同时成功或着同时失败

3.事务是如何完成多条语句同时成功或失败的

InnoDB引擎提供了一组用来记录事务性活动的日志

事务开始了：

insert

update

insert

事务结束了

在事务执行的过程当中每一条DML语句都会记录到日志当中（保存历史记录）

在事务执行的过程当中，我们可以提交事务，也可以回滚事务

提交事务？

清空日志当中的记录，将所有的数据放到表当中，永久存储。

提交事务表示事务结束，并且是一种成功的结束。

回滚事务？

清空日志当中的记录，将事务当中执行的DML命令全部撤回。

表示事务结束，并且是一种失败的结束。

4.怎么提交事务，怎么回滚事务

提交：commit

回滚：rollback

mysql事务默认情况下是自动提交的。
什么是自动提交？

只要执行任意一条DML语句则提交一次。

怎么关闭自动提交？

start transaction;
    
准备表：

drop table if exists t_user;

create table t_user(
id int primary key auto_increment,
username varchar(255)
);

    
演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次。

 mysql> insert into t_user(username) values('zs');

 Query OK, 1 row affected (0.03 sec)

 mysql> select * from t_user;
 +----+----------+
 | id | username |
 +----+----------+
 |  1 | zs       |
 +----+----------+
 1 row in set (0.00 sec)

 mysql> rollback;

 Query OK, 0 rows affected (0.00 sec)

 mysql> select * from t_user;
 +----+----------+
 | id | username |
 +----+----------+
 |  1 | zs       |
 +----+----------+
 1 row in set (0.00 sec)

 演示：使用start transaction;关闭自动提交机制。

 mysql> start transaction;

 Query OK, 0 rows affected (0.00 sec)

 mysql> insert into t_user(username) values('lisi');

 Query OK, 1 row affected (0.00 sec)

 mysql> select * from t_user;
 +----+----------+
 | id | username |
 +----+----------+
 |  1 | zs       |
 |  2 | lisi     |
 +----+----------+
 2 rows in set (0.00 sec)

 mysql> insert into t_user(username) values('wangwu');

 Query OK, 1 row affected (0.00 sec)

 mysql> select * from t_user;
 +----+----------+
 | id | username |
 +----+----------+
 |  1 | zs       |
 |  2 | lisi     |
 |  3 | wangwu   |
 +----+----------+
 3 rows in set (0.00 sec)

 mysql> rollback;
 Query OK, 0 rows affected (0.02 sec)

 mysql> select * from t_user;
 +----+----------+
 | id | username |
 +----+----------+
 |  1 | zs       |
 +----+----------+
 1 row in set (0.00 sec)

从关闭自动提交开始，都是被回滚的DML命令。 

案例：使用start transaction;关闭自动提交机制。在使用commit进行提交

  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)

  mysql> insert into t_user(username) values('wangwu');
  Query OK, 1 row affected (0.0