MySQL day3

目录

MySQL

insert 插入多条记录

• values后面用逗号间隔多个记录；

快速创建表

• create table emp2 as select * from emp；
• 将一个查询结果当做一张表新建。这个可以完成表的快速复制。表创建出来，同时表中的数据也存在了。

将查询结果插入一张表

快速删除表中数据

• delete语句删除数据的原理？
  表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放。
  这种删除缺点是：删除效率比较低。
  这种删除优点是：支持回滚，后悔了可以再恢复数据。
• truncate语句删除数据的原理?
  这种删除效率比较高，表被一次截断，物理删除。
  这种删除缺点：不支持回滚。
  这种删除优点：快速。
  用法：truncate table dept_bak；（ 这种操作属于DDL操作）

对表结构的增删改

• 什么是对表结构的修改?
  添加一个字段，删除一个字段，修改一个字段。
  对表结构的修改需要使用：alter

约束

什么是约束

• 约束对应的英语单词：constraint
  在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性。
  约束的作用就是为了保证：表中的数据有效。

约束包括哪些?

• 非空约束：not null
  唯一性约束：unique
  主键约束：primary key（简称PK）
  外键约束：foreign key（简称FK）

非空约束

• 非空约束not null约束的字段不能为NULL。

唯一性约束

• 唯一性约束unique约束的字段不能重复，但是可以为NULL。

列级与表级约束

• 约束直接添加到列后面的，叫做列级约束。
  约束没有添加在列的后面，这种约束被称为表级约束。
• 什么时候使用表级约束呢?
  需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。
• not null只有列级约束，没有表级约束。

主键约束

• 在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。
• 什么是主键？有啥用？
  主键值是每一行记录的唯一标识。
  主键值是每一行记录的“身份证号”。
• 记住：任何一张表都应该有主键，没有主键，表无效。
• 主键的特征：not null+unique（主键值不能是NULL，同时也不能重复！）
• 一张表，主键约束只能添加1个。
• 在mysql当中，有一种机制，可以帮助我们自动维护一个主键值：auto_increment表示自增，从1开始，以1递增。

外键约束

• 子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗?
  不一定是主键，但至少具有unique约束。
• 外键值可以为NULL。

储存引擎

• 什么是存储引擎，有什么用呢?
  存储引擎是MySQL中特有的一个术语，其它数据库中没有。实际上存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。
• 在建表的时候可以在最后小括号的"）"的右边使用：ENGINE来指定存储引擎。CHARSET来指定这张表的字符编码方式。

MyISAM

• 它管理的表具有以下特征：
  使用三个文件表示每个表：
  格式文件—存储表结构的定义（mytable.frm）
  数据文件—存储表行的内容（mytable .MYD）
  索引文件—存储表上索引（mytable.MYl）
  特点：可被转换为压缩、只读表来节省空间。

InnoDB

• 这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
  InnoDB支持事务，支持数据库崩溃后自动恢复机制。
  InnoDB存储引擎最主要的特点是：非常安全。
• 它管理的表具有下列主要特征：
  —每个InnoDB 表在数据库目录中以.frm格式文件表示。
  —InnoDB表空间tablespace被用于存储表的内容。
  —提供一组用来记录事务性活动的日志文件。
  —用COMMIT （提交）、SAVEPOINT 及ROLLBACK （回滚）支持事务处理。
  —提供全ACID兼容。
  —在MySQL服务器崩溃后提供自动恢复。
  —多版本（MVCC）和行级锁定。
  —支持外键及引用的完整性，包括级联删除和更新。
  特点：支持事务，保证数据的安全，效率不是很高。

MEMORY

• 使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。
• MEMORY存储引擎管理的表具有下列特征：
  —在数据库目录内，每个表均以.frm格式的文件表示。
  —表数据及索引被存储在内存中。
  —表级锁机制。
  —不能包含TEXT或BLOB 字段。
  特点：查询效率是最高的；不安全，关机之后数据消失，因为数据和索引都是在内存当中。

事务

什么是事务？

• 一个事务其实就是一个完整的业务逻辑。是一个最小的工作单元。不可再分。

只有DML语句才会与事务有关

• insert、delete 、update。只有以上的三个语句和事务有关系，其它都没有关系。因为只有以上的三个语句是对数据库表中数据进行增、删、改。

事务是怎么做到多条DML语句同时成功和同时失败的呢?

• InnoDB存储引擎：提供一组用来记录事务性活动的日志文件。
  在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
  在事务的执行过程中，我们可以提交事务，也可以回滚事务。
• 提交事务？（commit）
  清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。提交事务标志着，事务的结束。并且是一种全部成功的结束。
• 回滚事务？（rollback）
  将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件。回滚事务标志着，事务的结束。并且是一-种全部失败的结束。

在mysql当中默认的事务行为是怎样的?

• mysql默认情况下是支持自动提交事务的。( 自动提交)
  什么是自动提交?
  每执行一条DM语句，则提交一次。

怎么将mysql的自动提交机制关闭掉呢?

• 先执行命令：start transaction；

事务包括4个特性

• A：原子性
• C：一致性
• I：隔离性
• D：持久性

事物的隔离性

• A教室和B教室中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的隔离级别。这道墙越厚，表示隔离级别就越高。

事务和事务之间的隔离级别有哪些呢？4个级别

• 读未提交：readuncommitted（最低的隔离级别）
  什么是读未提交?
  事务A可以读取到事务B未提交的数据。
  这种隔离级别存在的问题就是：脏读现象（Dirty Read）—我们称读到了脏数据。
• 读已提交：read committed
  什么是读已提交?
  事务A只能读取到事务B提交之后的数据。
  这种隔离级别解决了什么问题？—解决了脏读的现象。
  这种隔离级别存在什么问题?
  不可重复读取数据。
  什么是不可重复读取数据呢?
  事务1读取某一数据后，事务2执行更新操作，使1无法再现前一次读取的结果。
• 可重复读：repeatable read
  什么是可重复读取?
  事务A开启之后，不管是多久，每一次在事务A中读取到的数据
  都是一致的。即使事务B将数据已经修改，并且提交了，事务A
  读取到的数据还是没有发生改变，这就是可重复读。
• 序列化/串行化：serializable（最高的隔离级别）
  这是最高隔离级别，效率最低。解决了所有的问题。
  这种隔离级别表示事务排队，不能并发。

索引

什么是索引？

• 索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。 索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
• 在mysql数据库当中索引也是需要排序的，在mysql当中索引是一个B-Tree数据结构。
  遵循左小右大原则存放。采用中序遍历方式遍历取数据。

索引的实现原理

• 提醒1：
  在任何数据库当中主键上都会自动添加索引对象。另外在mysql当中，一个字段如果有unique约束的话，也会自动创建索引对象。
• 提醒2：
  在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
• 提醒3：
  在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。在MyISAM存储引擎中，索引存储在一个.MYI文件中；在InnoDB存储引擎中，索引存储在一个逻辑名称叫做tablespace的当中；在MEMORY存储引擎当中，索引存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。

什么条件下，会考虑给字段添加索引？

• 条件1：数据量庞大
• 条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
• 条件3：该字段很少的DML（insert、delete、update）操作。（因为DM之后，索引需要重新排序）。

索引的创建和删除语法

• 创建索引：
  create index XXX on 表名（字段名）；
• 删除索引：
  drop index XXX on 表名；

索引有失效的时候，什么时候索引失效呢？

• 第一种情况：
  select * from emp where ename 1 ike ’ %T ’ ；
  ename上即使添加了索引，也不会走索引，为什么?
  因为模糊匹配当中以“%”开头了，尽量避免模糊查询的时候以“%”开始。
  这是一种优化手段/策略。
• 第二种情况：
  使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。这就是不建议使用or的原因。
• 第三种情况：
  使用复合索引的时候，没有使用左侧的列查找，索引失效。
  什么是复合索引？两个字段，或者更多的字段联合起来添加一个索引，叫做复合 索引。
• 第四种情况：
  在where当中索引列参加了运算。
• 第五种情况：
  在where当中索引列使用了函数。

视图（view）

什么是视图？

• view：站在不同的角度去看待同一份数据。

怎么创建视图对象？怎么删除视图对象？

• 创建：
  create view emp_ view as select * from emp ；
• 删除：
  drop view emp_view；
• **注意：**只有DQL语句才能以view的形式创建。
  create view view_ name as 这里的语句必须是DQL语句；

用视图做什么?

• 我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作。（**视图的特点：**通过对视图的操作，会影响到原表数据。）

BDA常用命令

• 重点掌握：数据的导入和导出（数据的备份）

导出？

• 注意：在windows 的dos命令窗口中：
  mysqldump bjpowernode>D : \bjpowernode. sql -uroot -p123456
  可以导出指定的表吗?
  mysq1dump bjpowernode emp>D : \bjpowernode.sql -uroot- p123456

导入？

• 注意：需要先登录到mysql数据库服务器上。
  然后创建数据库：create database bjpowernode ;
  使用数据库：use bjpowernode
  然后初始化数据库：source D: \bjpowernode . sql

数据库设计三范式

什么是数据库设计范式?

• 数据库表的设计依据。教你怎么进行数据库表的设计。

数据库设计范式共有？

• 第一范式：要求任何一张表必须有主键，一个字段原子性不可再分。
• 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
• 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。