MySQL整体总结

Mysql总结 

通过之前的学习，对SQL编写有了一定基础，我们不局限于增删改查，我们需要对它进行全面的总结，才能够更好的使用它。自己敲得，有错误的欢迎指出。

MySQL 数据类型

1.表删除操作须谨慎。

表删除操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有 任何的确认信息提示，因此执行删除操时，
应当慎重。
在删除表前，最好对表中的数据进行备份，这样当操作失误时，可以对数据进行恢复，以免造成无法挽回的后果。
同样的，在使用ALTER TABLE进行表的基本修改操作时，在执行操作过程之前，也应该 确保对数据进行完整的备份，
因为数据库的改变是无法撤销的，如果添加了一个不需要的字段， 可以将其删除；相同的，如果删除了一个需要的列，该列下面的所
有数据都将会丢失。

2.每个表中都要有一个主键吗？

并不是每一个表中都需要主键，一般的，如果多个表之间进行连接操作时，需要用到主键。 
因此并不需要为每个表建立主键，而且有些情况最好不使用主键。

3.每个表都可以任意选择存储引擎吗？

外键约束（FOREIGN KEY)不能跨引擎使用。MySQL支持多种存储引擎，每一个表都可 以指定一个不同的存储引擎，
但是要注意：外键约束是用来保证数据的参照完整性，如果表之间 需要关联外键，却指定了不同的存储引擎，这些表之间是不能创建
外键约束的。
所以说，存储引擎的选择也不完全是随意的。

4.带AUTO_INCREMENT约束的字段值是从1开始的吗？

默认的，在MySQL中，AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。
设置自增属性（AUTO_INCREMENT)的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始
递增，
如在tb_emp中插入第一条记录，同时 指定id值为5,则以后插入的记录的id值就会从6开始往上增加。
添加唯一性的主键约束时， 往往需要设置字段自动增加属性。

5.日期数据类型的选择

TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别就是： 
DATETIME在存储日期时间数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关； 
TIMESTAMP值的存储是以UTC(世界标准时间）格式保存的，存储时对当前时区进行转换， 检索时再转换回当前时区。即查询时，根据
当前时区的不同，显示的时间值是不同的。

6.选择数据类型的方法和技巧是什么？

MySQL提供了大量的数据类型，为了优化存储，提高数据库性能，在任何情况下均应使用 最精确的类型。即在所有可以表示该列值的
类型中，该类型使用的存储最少。

1.整数和浮点数

如果不需要小数部分，则使用整数来保存数据；如果需要表示小数部分，则使用浮点数类型。
对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。
例如如果列的值的范 围为1〜99999，若使用整数，则MEDIUMINT UNSIGNED是最好的类型；
若需要存储小数，则 使用FLOAT类型。浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高，因此，如要求存储精度较
高时，应选择DOUBLE类型。

2.浮点数和定点数decimal

浮点数FLOAT，DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。
但是由于浮点数容易产生误差，因此对精确度要求比较高时，建议使用DECIMAL来存储。

DECIMAL在MySQL中是以字符串存储的，用于定义货币等对精确度要求较高的数据。
在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题，最好不要这样使用。
另外两个浮点数进行减法和比较运算时也容易出问题，因此在进行计算的时候， 一定要小心。
如果进行数值比较，最好使用DECIMAL类型。

3.日期与时间类型

MySQL对于不同种类的日期和时间有很多的数据类型，比如YEAR和TIME。如果只需要记录年份，则使用YEAR类型即可；
如果只记录时间，只须使用TIME类型。
如果同时需要记录日期和时间，则可以使用TIMESTAMP或者DATETIME类型。
由于TIMESTAMP列的取值范围小于DATETIME的取值范围，因此存储范围较大的日期最好使用DATETIME。
默认的情况下，当插入一条记录但并没 有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。
因此当需要插入记录同时插入当前时间时，使用TIMESTAMP是方便的，另外TIMESTAMP在空间上比 DATETIME更有效。

4.CHAR与VARCHAR之间的特点与选择

4.1CHAR和VARCHAR的区别：
CHAR是固定长度字符，VARCHAR是可变长度字符；CHAR会将占用字符位自动补空格。

CHAR是固定长度，所以它的处理速度比VARCHAR的速度要快，但是它的缺点就是浪费 存储空间。
所以对存储不大，但在速度上有要求的可以使用CHAR类型，反之可以使用 VARCHAR类型来实现。

4.2存储引擎对于选择CHAR和VARCHAR的影响：

对于MyISAM存储引擎：最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间
换时间。

对于InnoDB存储引擎：使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，
因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际的长度存储，比较节省空间，所以对磁盘I/O和数据存储总量比较
好。

5.ENUM 和 SET

ENUM只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。因此，在需要从多个值中选取一个时，
可以使用ENUM。
比如：性别字段适合定义为 ENUM类型，每次只能从’男’或’女’中取一个值。

SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。在需要取多个值的时候，适合使用SET类型，
比如：要存储一个人兴趣爱好，最好使用SET类型。ENUM和SET的值是以字符串形式出现的，但在内部，MySQL以数值的形式存储它们。

6.BLOB 和 TEXT

BLOB是二进制字符串，TEXT是非二进制字符串，两者均可存放大容量的信息。
BLOB主要存储图片、音频信息等，而TEXT只能存储纯文本文件。应分清两者的用途。

7.MySQL中如何使用特殊字符？

诸如单引号（’），双引号（”），反斜线（\）等符号，这些符号在MySQL中不能直接输入使用，否则会产生意料之外的结果。
在MySQL中，这些特殊字符称为转义字符，在输入时需要以反斜线符号（’\’）开头，所以在使用单引号和双引号时应分别输入（\’
）或者（\”），
输入反斜线时应该输入（\\）其他特殊字符还有回车符（\r)，换行符（\n)，制表符（\tab)，退格 符（\b)等。在向数据库中插入这
些特殊字符时，一定要进行转义处理。

8.MySQL中可以存储文件吗？

MySQL中的BLOB和TEXT字段类型可以存储数据量较大的文件，可以使用这些数据类型 存储图像、声音或者是大容量的文本内容，例如
网页或者文档。
虽然使用BLOB或者TEXT可以存储大容量的数据，但是对这些字段的处理会降低数据库的性能。如果并非必要，可以选择只储存文件的
路径。

9.MySQL中如何执行区分大小写的字符串比较？

在Windows平台下，MySQL是不区分大小的，因此字符串比较函数也不区分大小写。如果 想执行区分大小写的比较，可以在字符串前面
添加BINARY关键字。
例如默认情况下，’a’=‘A’ 返回结果为1，如果使用BINARY关键字，SELECT BINARY 'a'='A';结果为0,在区分大小写的情况下，’
a’ 与’A’并不相同。

10.如何从日期时间值中获取年、月、日等部分日期或时间值？

-- 获取当前系统日期时间：
SELECT SYSDATE() AS 系统日期时间;

-- 获取当前系统年月日：
SELECT CURRENT_DATE AS 年月日;

-- 分别获取
SELECT
    SYSDATE(),
    YEAR (CURRENT_DATE) AS 年,
    MONTH (CURRENT_DATE) AS 月,
    DAY (CURRENT_DATE) AS 日,
    TIME(SYSDATE()) AS 系统时间,
    HOUR(SYSDATE()) AS 系统小时,
    MINUTE(SYSDATE()) AS 系统分钟,
    SECOND(SYSDATE()) AS 系统秒,
    MICROSECOND(SYSDATE()) AS 系统毫秒;

11.如何改变默认的字符集？

MySQL字符集设置
系统变量：
SET character_set_server='utf8'; #默认的内部操作字符集
SET character_set_client='utf8'; #客户端来源数据使用的字符集
SET character_set_connection='utf8'; #连接层字符集
SET character_set_results='utf8'; #查询结果字符集
SET character_set_database='utf8'; #当前选中数据库的默认字符集
SET character_set_system='utf8'; #系统元数据(字段名等)字符集
#还有以collation_开头的同上面对应的变量，用来描述字符序。

对于字符集的支持细化到四个层次:
服务器(server)，数据库(database)，数据表(table)和连接(connection)。

查看系统的字符集和排序方式的设定
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation_%';

2通过MySQL文件修改编码方式:
在mysql的安装目录下找到my.ini文件(如果没有的话就把my-medium.ini复制，然后重命名为my.ini即可)
在my.ini文件中找到[client]和[mysqld]字段，在下面均加上default-character-set=utf8，保存并关闭

12.DISTINCT可以应用于所有的列吗？

查询结果中，如果需要对列进行降序排序，可以使用DESC，这个关键字只能对其前面的列 进行降序排列。
例如，要对多列都进行降序排序，必须要在每一列的列名后面加DESC关键字。 

orderby多个字段时，用逗号分隔每一个字段，排序的方法是先按第一个字段排序，如果有相同的再按后续的字段依次排序。
SELECT *FROM tb_emp ORDER BY sal DESC,comm DESC;

而DISTINCT不同，DISTINCT不能部分使用。换句话说，DISTINCT关键字应用于所有列而不仅是它后面的第一个指定列。
例如，查询3个字段s_id，f_name，f_price，如果不同记录的这3个字段的组合值都不同，则所有记录都会被查询出来。
SELECT DISTINCT dept_id,job FROM tb_emp;

13.ORDER BY可以和LIMIT混合使用吗？

在使用ORDER BY子句时，应保证其位于FROM子句之后，如果使用LIMIT，则必须位 于ORDER BY之后，
如果子句顺序不正确，MySQL将产生错误消息。

14.什么时候使用引号？

在查询的时候，会看到在WHERE子句中使用条件，有的值加上了单引号，而有的值未加。 单引号用来限定字符串，
如果将值与字符串类型列进行比较，则需要限定引号；而用来与数值进行比较则不需要用引号。

15.在WHERE子句中必须使用圆括号吗？

任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确操作顺序。 如果条件较多，即使能确定计算次序，
默认的计算次序也可能会使SQL语句不易理解，因此使 用括号明确操作符的次序，是一个好的习惯。

16.更新或者删除表时必须指定WHERE子句吗？

如果省略WHERE子句，则UPDATE或DELETE将被应用到表中所有的行。
因打算更新或者删除所有记录，否则要注意使用不带WHERE子句的UPDATE或DELETE 语句。

17.索引对数据库性能如此重要，应该如何使用它？

如果索引列较多，则需要的磁盘空间和维护开销都较多。如果在一个大表上创建了多种组合索引，索引文件也会膨胀很快。
而另一方面，索引较多 可覆盖更多的查询。
可能需要试验若干不同的设计，才能找到最有效的索引。
可以添加、修改和删 除索引而不影响数据库架构或应用程序设计。因此，应尝试多个不同的索引从而建立最优的索引。

18.尽量使用短索引。

如果有一个 CHAR(255)的列如果在前10个或30个字符内，多数值是惟一的，则不需要对整个列进行索引。
 短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作。

19. MySQL存储过程和函数有什么区别？

函数与储存过程区别：
1、在本质上它们都是存储程序。
2、存储过程实现的过程要复杂一些,而函数的针对性较强;
3、函数只能通过return语句返回单个值或者表对象；而存储过程不允许执行return，但是可以通过out参数返回多个值。
4、函数限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等等；而存储过程的限制相对就比较少。
5、函数可以嵌入在SQL 语句中使用，可以在SELECT语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执
行。

20.存储过程中的代码可以改变吗？

目前，MySQL还不提供对已存在的存储过程代码的修改，如果必须要修改存储过程，必须使用DROP语句删除之后，再重新编写代码，
或者创建一个新的存储过程。

21.存储过程中可以调用其他存储过程吗？

存储过程包含用户定义的SQL语句集合，可以使用CALL语句调用存储过程，当然在存储过程中也可以使用CALL语句调用其他存储过程，
但是不能使用DROP语句删除其他存储过程。

22.存储过程的参数不要与数据表中的字段名相同。

在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开来，否则将出现无法预期的结果。

23.存储过程的参数可以使用中文吗？

一般情况下，可能会出现存储过程中传入中文参数的情况，例如某个存储过程根据用户的名字查找该用户的信息，传入的参数值可能
是中文。
这时需要在定义存储过程的时候，在后面加上character set gbk,不然调用存储过程使用中文参数会出错，
比如定义userInfo存储过程，代码 如下：
CREATE PROCEDURE useInfo (
    IN u_name VARCHAR (50) CHARACTER SET gbk,
    OUT u_age INT
)

24.MySQL中视图和表的区别以及联系是什么？

两者的区别：
(1)视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化的表，而表不是。
(2)视图没有实际的物理记录，而基本表有。
(3)表是内容，视图是窗口。
(4)表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时进行修改，但视图只能用创建的语句来修改。
(5)视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL 语句的集合。
从安全的角度来说，视图可以防止用户接触数据表，因而用户不知道表结构。
(6)表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
(7)视图的建立和删除只影响视图本身，不影响对应的基本表。

两者的联系：
视图（view)是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有记录） 都来自基本表，
它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关
系。

25.使用触发器时须特别注意。

在使用触发器的时候需要注意，对于相同的表，相同的事件只能创建一个触发器，比如对表account创建了一个BEFORE INSERT触发器
，
那么如果对表account再次创建一个BEFORE INSERT触发器，MySQL将会报错，
此时，只可以在表account上创建AFTER INSERT或者 BEFORE UPDATE类型的触发器。灵活的运用触发器将为操作省去很多麻烦。

26.及时删除不再需要的触发器。

触发器定义之后，每次执行触发事件，都会激活触发器并执行触发器中的语句。如果需求 发生变化，
而触发器没有进行相应的改变或者删除，则触发器仍然会执行旧的语句，从而会影响新的数据的完整性。

27.应该使用哪种方法创建用户？

创建用户有几种方法：GRANT语句、CREATE USER语句和直接操作user表。

一般情况， 最好使用GRANT或者CREATE USER语句，而不要直接将用户信息插入user表，
因为user表中存储了全局级别的权限以及其他的账户信息，如果意外破坏了 user表中的记录，则可能会对 MySQL服务器造成很大影响
。

1、通过CREATE USER命令进行创建用户（identified确认）
CREATE USER 'root@localhost_3306' IDENTIFIED BY 'root'; #其中密码是可选项；

2、通过GRANT命令创建用户（grant授予）

模型：GRANT privileges ON databasename.tablename TO 'username'@'host' 

说明:
privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示 

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON luo.tb_dept TO root@localhost_3309 identified by 'root'; #其中密码是
可选项；

28.mysqldump备份的文件只能在MySQL中使用吗？

mysqldump备份的文本文件实际是数据库的一个副本，使用该文件不仅可以在MySQL中恢复数据库，而且通过对该文件的简单修改，
可以使用该文件在SQL Server或者Sybase等其他数据库中恢复数据库。这在某种程度上实现了数据库之间的迁移。

29.mysql备份数据库的方法？

1、直接拷贝数据库库文件，直接，快速，但不能实现增量备份.
为确保数据一致性，需在备份前执行：
FLUSH TABLES WITH READ LOCK;   #向所有表施加读锁

2、使用mysqlhotcopy工具快速备份
mysqlhotcopy是一个perl脚本，最初由Tim Bunce编写并提供。
他使用LOCK TABLES 、FLUSH TABLES和cp或scp来快速备份数据库。
他是备份数据库或单个表的最快途径，但他只能运行在数据库目录所在机器上，并且只能备份myisam类型的表。

一般正确安装mysql以后，mysqldump所在的路径就被加入系统环境变量里边了，直接在命令行里就可以执行了。
mysqldump -help;
mysqldump -uroot -proot luo > C:\Users\Administrator\Desktop\luo.sql;

30.平时应该打开哪些日志？

MySQL中有六种日志文件，分别是：
重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、
慢查询日志（slow query log）、通用查询日志（general log），中继日志（relay log）。

日志既会影响MySQL的性能，又会占用大量磁盘空间。因此，如果不必要，应尽可能少地开启日志。
根据不同的使用环境，可以考虑开启不同的日志。

在开发环境中优化查询效率低的语句，可以开启慢查询日志；
如果需要记录用户的所有查询操作，可以开启通用查询日志； 
如果需要记录数据的变更，可以开启二进制日志；
错误日志是默认开启的。

31.如何使用二进制日志？

二进制日志主要用来记录数据变更。如果需要记录数据库的变化，可以开启二进制日志。 
基于二进制日志的特性，不仅可以用来进行数据恢复，还可用于数据复制。
在数据库定期备份的情况下，如果出现数据丢失，可以先用备份恢复大部分数据，然后使用二进制日志恢复最近备份后变更的数据。
在双机热备情况下，可以使用MySQL的二进制日志记录数据的变更，然后将变更部分复制到备份服务器上。

32.如何使用慢查询日志？

慢查询日志主要用来记录查询时间较长的日志。在开发环境下，可以开启慢查询日志来记 录查询时间较长的查询语句，然后对这些语
句进行优化。
通过配long_query_time的值，可以灵活地掌握不同程度的慢查询语句。

33.是不是索引建立得越多越好？

合理的索引可以提高查询的速度，但不是索引越多越好。在执行插入语句的时候，MySQL 要为新插入的记录建立索引。
所以过多的索引会导致插入操作变慢。原则上是只有查询用的字段 才建立索引。

34.如何使用查询缓冲区？

查询缓冲区可以提高查询的速度，但是这种方式只适合查询语句比较多、更新语句比较少的情况。
默认情况下查询缓冲区的大小为0，也就是不可用。可以修改queiy_cache_size以调整 查询缓冲区大小；
修改query_cache_type以调整查询缓冲区的类型。在my.ini中修改 query_cache_size 和 query_cache_type 的值如下所示：
[mysqld]
query_cache_size= 512M 
query_cache_type= 1

表示开启查询缓冲区。只有在查询语句中包含SQL_NO_CACHE关键字时，才不会使用查询缓冲区。
可以使用 FLUSH QUERY CACHE; 语句来刷新缓冲区，清理查询缓 冲区中的碎片。