Mysql知识总结

1. 如何创建和删除数据库？

   craete database 数据库名

   drop database 数据库名

2. MyISAM与InnoDB的区别？

   • InnoDB 支持事务；MyISAM 不支持事务
   • InnoDB 支持行级锁；MyISAM 支持表级锁
   • InnoDB 支持 MVCC(多版本并发控制)；MyISAM 不支持
   • InnoDB 支持外键，MyISAM 不支持
   • MySQL 5.6 以前的版本，InnoDB 不支持全文索引，MyISAM 支持；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
   • InnoDB 不保存表的总行数，执行 select count(*) from table 时
     需要全表扫描；MyISAM 用一个变量保存表的总行数，查总行数速度很快
   • InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，
     通过主键索引效率很高。辅助索引需要两次查询，先查询到主键，再通过主键查询到数据。主键太大，其他索引也会很大；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的

   总结：

   • InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引
   • MyISAM 不支持事务、也不支持外键，优势是访问的速度快。对事务的完整性没有要求、以 SELECT 和 INSERT 为主的应用可以使用这个存储引擎

3. char与varchar的区别

   • char 是一种固定长度的字符串类型
   • varchar 是一种可变长度的字符串类型

4. 建表语句中varchar(50)中50的指是什么？

   字段最多存放 50 个字符

   如 varchar(50) 和 varchar(200) 存储 “ConstXiong” 字符串所占空间是一样的，后者在排序时会消耗更多内存

5. int(10)中10指什么？

   INT[(M)] [UNSIGNED] [ZEROFILL] M 默认为11

   10 就是上述语句里的 M，指最大显示宽度，最大值为 255

   最大显示宽度意思是，如果是 int(10)，字段存的值是 10，则，显示会自动在之前补 8 个 0，显示为 0000000010

   int 类型在数据库里面存储占 4 个字节的长度

   • 有符号的整型范围是 -2147483648 ~ 2147483647
   • 无符号的整型范围是 0 ~ 4294967295

6. MySQL如何获取当前日期？

   SELECT CURRENT_DATE();
   

7. 如何获取MySQL的版本？

   SELECT VERSION()

8. 什么是触发器，MySQL都有哪些触发器？

   MySQL 数据库中有六种触发器：

   • Before Insert
   • After Insert
   • Before Update
   • After Update
   • Before Delete
   • After Delete

   使用场景：

   • 可以通过数据库中的相关表实现级联更改
   • 实时监控表中字段的更改做出相应处理

   注意：滥用会造成数据库及应用程序的维护困难

9. MySQL显示表前 50 行

   SELECT * FROM tablename LIMIT 0,50;
   

10. 如何连接MySQL服务端、关闭连接？

    • 连接：使用指令 mysql -u -p -h -P (-u:指定用户名 -p:指定密码 -h:主机 -P:端口) 连接 MySQL 服务端
    • 关闭：使用指令 exit 或 quit

11. text 表示存储的数据类型是字符串类型

12. 说说你知道的MySQL存储引擎

    InnoDB

    • 默认事务型引擎，被广泛使用的存储引擎
    • 数据存储在共享表空间，即多个表和索引都存储在一个表空间中，可通过配置文件修改
    • 主键查询的性能高于其他类型的存储引擎
    • 内部做了很多优化，如：从磁盘读取数据时会自动构建hash索引，插入数据时自动构建插入缓冲区
    • 通过一些机制和工具支持真正的热备份
    • 支持崩溃后的安全恢复
    • 支持行级锁
    • 支持外键

    MyISAM

    • 拥有全文索引、压缩、空间函数
    • 不支持事务和行级锁、不支持崩溃后的安全恢复
    • 表存储在两个文件：MYD 和 MYI
    • 设计简单，某些场景下性能很好，例如获取整个表有多少条数据，性能很高

    其他表引擎：Archive、Blackhole、CSV、Memory

13. 说一说MySQL中的锁机制

    按粒度分：

    • 表级锁：粒度最大的一种锁，表示对当前操作的整张表加锁。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
    • 行级锁：粒度最小的一种锁，表示只针对当前操作的行进行加锁。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
    • 页级锁：粒度介于行级锁和表级锁中间的一种锁。开销、加锁时间和并发度界于表锁和行锁之间；会出现死锁

    按操作分：

    • 读锁(共享锁)：针对同一份数据，多个读取操作可以同时进行，不互相影响
    • 写锁(排它锁)：当前写操作没有完成前，会阻断其他写锁和读锁

    MySQL 不同的存储引擎支持不同的锁机制

    • InnoDB 存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用行级锁
    • MyISAM 和 MEMORY 存储引擎采用的是表级锁(table-level locking)
    • BDB 存储引擎采用的是页面锁(page-level locking)，也支持表级锁

14. 说一说MySQL中的事务

    事务具有四大特性（ACID）：

    • 原子性(Atomic)：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样
    • 一致性(Consistency)：在事务开始之前和事务结束以后， 数据库的完整性没有被破坏
    • 隔离性(Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
    • 持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

    事务控制：

    • BEGIN 或 START TRANSACTION 显式地开启一个事务
    • COMMIT 会提交事务，使已对数据库进行的所有修改成为永久性的
    • ROLLBACK 回滚，会结束用户的事务，并撤销正在进行的所有未提交的修改
    • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT
    • RELEASE SAVEPOINT identifier 删除一个事务的保存点，没有指定的保存点执行该语句会抛出一个异常
    • ROLLBACK TO identifier 把事务回滚到标记点
    • SET TRANSACTION 用来设置事务的隔离级别