MySql（一） 基础知识、法规范、常见指令

一、基础知识

常见面试题：

• 为什么要使用数据库

数据保存在内存

优点： 存取速度快

缺点： 数据不能永久保存

数据保存在文件

优点： 数据永久保存

缺点：1）速度比内存操作慢，频繁的IO操作。2）查询数据不方便

数据保存在数据库

1）数据永久保存

2）使用SQL语句，查询方便效率高。

3）管理数据方便

• 什么是SQL？

结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。

作用：用于存取数据、查询、更新和管理关系数据库系统。

• 什么是MySQL?

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。

• 数据库三大范式是什么

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全（直接或间接）依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键（消除传递依赖），不依赖于其他非主键。

第一范式（1NF）：

1、数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

如果需求知道那个省那个市并按其分类，那么显然第一个表格是不容易满足需求的，也不符合第一范式。

2、两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

显然第一个表结构不但不能满足足够多物品的要求，还会在物品少时产生冗余。也是不符合第一范式的。

 第二范式（2NF）：

满足1NF后要求表中的所有列，每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。

一个人同时订几个房间，就会出来一个订单号多条数据，这样子联系人都是重复的，就会造成数据冗余。我们应该把他拆开来。

这样便实现啦一条数据做一件事，不掺杂复杂的关系逻辑。同时对表数据的更新维护也更易操作。

第三范式（3NF）：

满足2NF后，要求：表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能依赖于主键）。

数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a-->b-->c  属性之间含有这样的关系，是不符合第三范式的。

注意事项：

1.第二范式与第三范式的本质区别：在于有没有分出两张表。

第二范式是说一张表中包含了多种不同实体的属性，那么必须要分成多张表，第三范式是要求已经分好了多张表的话，一张表中只能有另一张标的ID，而不能有其他任何信息，（其他任何信息，一律用主键在另一张表中查询）。

2.必须先满足第一范式才能满足第二范式，必须同时满足第一第二范式才能满足第三范式。

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

• mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

• MySQL的binlog有有几种录入格式？分别有什么区别？

有三种格式，statement，row和mixed。

MySQL binlog日志有三种格式，分别为Statement,MiXED和ROW.

1.Statement：每一条会修改数据的sql都会记录在binlog中，不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能，由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，。

优点:

binlog文件较小

日志是包含用户执行的原始SQL,方便统计和审计

出现最早，兼容较好

缺点：

存在安全隐患，可能导致主从不一致

对一些系统函数不能准确复制或是不能复制

2.ROW：不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大

优点:

相比statement更加安全的复制格式

在某些情况下复制速度更快(SQL复杂，表有主键)

系统的特殊函数也可以复制

更少的锁

在复制时，对于更新和删除语句检查是否有主键，如果有则直接执行，如果没有，看是否有二级索引，如再没有，则全表扫描

缺点：

binlog比较大(myql5.6支持binlog_row_image)

单语句更新(删除)表的行数过多，会形成大量binlog

无法从binlog看见用户执行SQL(5.6中增加binlog_row_query_log_events记录用户的query)

3.Mixed: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

优点:

混合使用row和statement格式，对于DDL记录statument,对于table里的行操作记录为row格式。

如果使用innodb表，事务级别使用了READ_COMMITTED or READ_UMCOMMITTED日志级别只能使用row格式。

但是使用ROW格式中DDL语句还是会记录成statement格式。

缺点:

mixed模式中，那么在以下几种情况下自动将binlog模式由SBR模式改成RBR模式。

当DML语句更新一个NDB表

当函数中包含UUID时

2个及以上auto_increment字段的表被更新时

行任何insert delayed语句时

用UDF时

视图中必须要求使用RBR时，例如创建视图使用了UUID()函数
 

行模式和语句模式的区别

1.语句模式： 100万条记录 只需1条delete * from test；就可以删除100万条记录 2.row模式 100万条记录 记录100万条删除命令  

企业场景如何选择binlog模式

1、互联网公司，使用MySQL的功能相对少（存储过程、触发器、函数） 选择默认的语句模式，Statement Level（默认） 2、公司如果用到使用MySQL的特殊功能（存储过程、触发器、函数） 则选择Mixed模式 3、公司如果用到使用MySQL的特殊功能（存储过程、触发器、函数）又希望数据最大化一直，此时最好选择Row level模式

 如何配置binlog

在数据库中查看binlog模式

1. show global variables like "%binlog_format%";

默认是STATEMENT   现在我想修改成ROW模式 方法1： 修改 my.cnf （Mac 修改 /usr/local/mysql/my.cnf 无效，需要修改 /etc/my.cnf） 在[mysqld] 下面添加

1. binlog_format=ROW
2. log-bin=mysql-bin

然后重启 mysqld  

  方法2： 无需重新在线修改命令

1. SET global binlog_format='ROW';

重启 mysql 后失效    

二、语法规范

• 不区分大小写，建议关键字大，表名、列名小写
• 每条命令以分号结尾
• 注释  ：  单行注释 ：  # 或者 --（空格）   多行注释  ： /*      */ 

三、常用指令

查看Mysql版本

SELECT version()

查看所有数据库

show databases

选择某个数据库

use (数据库名）

查看数据库里的表

show tables

查看表结构

desc （表名)

查看当前所使用的数据库

SELECT database()