seo外链高质量网站,自己做的网站如如统计访问量,助企建站,WordPress站内链接设置转载自 37 个 MySQL 数据库小技巧#xff0c;不看别后悔#xff01;
无论是运维、开发、测试#xff0c;还是架构师#xff0c;数据库技术是一个必备加薪神器#xff0c;那么#xff0c;一直说学习数据库、学MySQL#xff0c;到底是要学习它的哪些东西呢#xff1f;…转载自 37 个 MySQL 数据库小技巧不看别后悔
无论是运维、开发、测试还是架构师数据库技术是一个必备加薪神器那么一直说学习数据库、学MySQL到底是要学习它的哪些东西呢
1、如何快速掌握MySQL?
培养兴趣
兴趣是最好的老师不论学习什么知识兴趣都可以极大地提高学习效率。当然学习MySQL 5.6也不例外。
夯实基础
计算机领域的技术非常强调基础刚开始学习可能还认识不到这一点随着技术应用的深 入只有有着扎实的基础功底才能在技术的道路上走得更快、更远。对于MySQL的学习来说 SQL语句是其中最为基础的部分很多操作都是通过SQL语句来实现的。所以在学习的过程中 读者要多编写SQL语句对于同一个功能使用不同的实现语句来完成从而深刻理解其不同之处。
及时学习新知识
正确、有效地利用搜索引擎可以搜索到很多关于MySQL 5.6的相关知识。同时参考别 人解决问题的思路也可以吸取别人的经验及时获取最新的技术资料。
多实践操作
数据库系统具有极强的操作性需要多动手上机操作。在实际操作的过程中才能发现问题 并思考解决问题的方法和思路只有这样才能提高实战的操作能力。
2、如何选择服务器的类型
MySQL服务器配置窗口中各个参数的含义如下。 【Server Configuration Type】该选项用于设置服务器的类型。单击该选项右侧的向下按钮 即可看到包括3个选项。
3个选项的具体含义如下 Development Machine(开发机器)该选项代表典型个人用桌面工作站。假定机器上运行 着多个桌面应用程序。将MySQL服务器配置成使用最少的系统资源。 Server Machine (服务器)该选项代表服务器MySQL服务器可以同其它应用程序一起 运行例如FTP、email和web服务器。MySQL服务器配置成使用适当比例的系统资源。 DedicatedMySQL Server Machine (专用 MySQL 服务器该选项代表只运行MySQL服务的服务器。假定运行没有运行其它应用程序。MySQL服务器配置成使用所有可用系统资源。作为初学者建议选择【DevelopmentMachine】开发者机器选项这样占用系统的资源 比较少。
3、如何选择存储引擎
不同存储引擎都有各自的特点以适应不同的需求如下表所示。为了做出选择 首先需 要考虑每一个存储引擎提供了哪些不同的功能。如果要提供提交回滚和崩溃恢复能力的事务安全ACID兼容能力并要求实现并发控 制InnoDB是个很好的选择。如果数据表主要用来插入和查询记录则MyISAM引擎能提供较 高的处理效率如果只是临时存放数据数据量不大并且不需要较高的数据安全性可以选择将数据保存在内存中的Memory引擎MySQL中使用该引擎作为临时表存放查询的中间结果。如果只有INSERT和SELECT操作可以选择Archive引擎Archive存储引擎支持高并发的插 入操作但是本身并不是事务安全的。Archive存储引擎非常适合存储归档数据如记录日志信 息可以使用Archive引擎。 使用哪一种引擎要根据需要灵活选择一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。 使用合适的存储引擎将会提高整个数据库的性能。
4、如何查看默认存储引擎
使用SHOW ENGINES语句查看系统中所有的存储引擎其中包括默认的存储引擎。可以看出来当前数据库系统中有五种存储引擎默认是MyISAM。还可以使用一种直接的方法查看默认存储引擎。执行结果直接显示了当前默认的存储引擎为MyISAM。
5、表删除操作须谨慎
表删除操作将把表的定义和表中的数据一起删除并且MySQL在执行删除操作时不会有 任何的确认信息提示因此执行删除操时应当慎重。在删除表前最好对表中的数据进行备份 这样当操作失误时可以对数据进行恢复以免造成无法挽回的后果。
同样的在使用ALTER TABLE进行表的基本修改操作时在执行操作过程之前也应该 确保对数据进行完整的备份因为数据库的改变是无法撤销的如果添加了一个不需要的字段 可以将其删除相同的如果删除了一个需要的列该列下面的所有数据都将会丢失。
6、每个表中都要有一个主键吗
并不是每一个表中都需要主键一般的如果多个表之间进行连接操作时需要用到主键。 因此并不需要为每个表建立主键而且有些情况最好不使用主键。
7、每个表都可以任意选择存储引擎吗
外键约束FOREIGN KEY)不能跨引擎使用。MySQL支持多种存储引擎每一个表都可 以指定一个不同的存储引擎但是要注意外键约束是用来保证数据的参照完整性如果表之间 需要关联外键却指定了不同的存储引擎这些表之间是不能创建外键约束的。所以说存储引 擎的选择也不完全是随意的。
8、带AUTO_INCREMENT约束的字段值是从1开始的吗
默认的在MySQL中AUTO_INCREMENT的初始值是1每新增一条记录字段值自动加1。设置自增属性AUTO_INCREMENT)的时候还可以指定第一条插入记录的自增字段的 值这样新插入的记录的自增字段值从初始值开始递增如在tb_emp8中插入第一条记录同时 指定id值为5,则以后插入的记录的id值就会从6开始往上增加。添加唯一性的主键约束时 往往需要设置字段自动增加属性。
9、TIMESTAMP与DATATIME两者的区别
TIMESTAMP与DATETIME除了存储字节和支持的范围不同外还有一个最大的区别就是 DATETIME在存储日期数据时按实际输入的格式存储即输入什么就存储什么与时区无关 而TIMESTAMP值的存储是以UTC(世界标准时间格式保存的存储时对当前时区进行转换 检索时再转换回当前时区。即查询时根据当前时区的不同显示的时间值是不同的。
10、选择数据类型的方法和技巧是什么
MySQL提供了大量的数据类型为了优化存储提高数据库性能在任何情况下均应使用 最精确的类型。即在所有可以表示该列值的类型中该类型使用的存储最少。
整数和浮点数
如果不需要小数部分则使用整数来保存数据如果需要表示小数部分则使用浮点数类 型。对于浮点数据列存入的数值会对该列定义的小数位进行四舍五入。例如如果列的值的范 围为1〜99999若使用整数则MEDIUMINT UNSIGNED是最好的类型若需要存储小数则 使用FLOAT类型。浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高因此如要求存储精度较高时应选择DOUBLE类型。
浮点数和定点数
浮点数FLOATDOUBLE相对于定点数DECIMAL的优势是在长度一定的情况下浮点 数能表示更大的数据范围。但是由于浮点数容易产生误差因此对精确度要求比较高时建议使 用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的用于定义货币等对精确度要 求较高的数据。在数据迁移中float(M,D)是非标准SQL定义数据库迁移可能会出现问题最 好不要这样使用。另外两个浮点数进行减法和比较运算时也容易出问题因此在进行计算的时候 一定要小心。如果进行数值比较最好使用DECIMAL类型。
日期与时间类型
MySQL对于不同种类的日期和时间有很多的数据类型比如YEAR和TIME。如果只需要 记录年份则使用YEAR类型即可如果只记录时间只须使用TIME类型。如果同时需要记录日期和时间则可以使用TIMESTAMP或者DATETIME类型。由于 TIMESTAMP列的取值范围小于DATETIME的取值范围因此存储范围较大的日期最好使用 DATETIME。TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下当插入一条记录但并没 有指定TIMESTAMP这个列值时MySQL会把TIMESTAMP列设为当前的时间。因此当需要 插入记录同时插入当前时间时使用TIMESTAMP是方便的另外TIMESTAMP在空间上比 DATETIME更有效。
CHAR与VARCHAR之间的特点与选择
CHAR和VARCHAR的区别 CHAR是固定长度字符VARCHAR是可变长度字符CHAR会自动删除插入数据的尾部 空格VARCHAR不会删除尾部空格。 CHAR是固定长度所以它的处理速度比VARCHAR的速度要快但是它的缺点就是浪费 存储空间。所以对存储不大但在速度上有要求的可以使用CHAR类型反之可以使用 VARCHAR类型来实现。
存储引擎对于选择CHAR和VARCHAR的影响 对于MyISAM存储引擎最好使用固定长度的数据列代替可变长度的数据列。这样可以使 整个表静态化从而使数据检索更快用空间换时间。 对于InnoDB存储引擎使用可变长度的数据列因为InnoDB数据表的存储格式不分固定 长度和可变长度因此使用CHAR不一定比使用VARCHAR更好但由于VARCHAR是按照 实际的长度存储比较节省空间所以对磁盘I/O和数据存储总量比较好。
ENUM 和 SET
ENUM只能取单值它的数据列表是一个枚举集合。它的合法取值列表最多允许有65 535 个成员。因此在需要从多个值中选取一个时可以使用ENUM。比如性别字段适合定义为 ENUM类型每次只能从’男’或’女’中取一个值。SET可取多值。它的合法取值列表最多允许有64个成员。
空字符串也是一个合法的SET值。 在需要取多个值的时候适合使用SET类型比如要存储一个人兴趣爱好最好使用SET类型。ENUM和SET的值是以字符串形式出现的但在内部MySQL以数值的形式存储它们。
BLOB 和 TEXT
BLOB是二进制字符串TEXT是非二进制字符串两者均可存放大容量的信息。BLOB主 要存储图片、音频信息等而TEXT只能存储纯文本文件。应分清两者的用途。点击这里总结了55道去BAT面试的MYSQL面试题。
11、MySQL中如何使用特殊字符
诸如单引号’双引号反斜线)等符号这些符号在MySQL中不能直接输入 使用否则会产生意料之外的结果。在MySQL中这些特殊字符称为转义字符在输入时需要 以反斜线符号’\’开头所以在使用单引号和双引号时应分别输入\’或者\)输入反 斜线时应该输入\)其他特殊字符还有回车符\r)换行符\n)制表符\tab)退格 符\b)等。在向数据库中插入这些特殊字符时一定要进行转义处理。
12、MySQL中可以存储文件吗
MySQL中的BLOB和TEXT字段类型可以存储数据量较大的文件可以使用这些数据类型 存储图像、声音或者是大容量的文本内容例如网页或者文档。虽然使用BLOB或者TEXT可 以存储大容量的数据但是对这些字段的处理会降低数据库的性能。如果并非必要可以选择只 储存文件的路径。
13、MySQL中如何执行区分大小写的字符串比较
在Windows平台下MySQL是不区分大小的因此字符串比较函数也不区分大小写。如果 想执行区分大小写的比较可以在字符串前面添加BINARY关键字。例如默认情况下’a’‘A’ 返回结果为1如果使用BINARY关键字BINARY’a’‘A’结果为0,在区分大小写的情况下’a’ 与’A’并不相同。
14、如何从日期时间值中获取年、月、日等部分日期或时间值
MySQL中日期时间值以字符串形式存储在数据表中因此可以使用字符串函数分别截取日期时间值的不同部分例如某个名称为dt的字段有值“2010-10-01 12:00:30”如果只需要获 得年值可以输入LEFT(dt, 4)这样就获得了字符串左边开始长度为4的子字符串即YEAR 部分的值如果要获取月份值可以输入MID(dt,6,2)字符串第6个字符开始长度为2的子 字符串正好为dt中的月份值。同理读者可以根据其他日期和时间的位置计算并获取相应的值。
15、如何改变默认的字符集
CONVERT()函数改变指定字符串的默认字符集在开始的章节中向读者介绍使用GUI图形化安装配置工具进行MySQL的安装和配置其中的一个步骤是可以选择MySQL的默认字符集。但是如果只改变字符集没有必要把配置过程重新执行一遍在这里一个简单的方式是 修改配置文件。在Windows中MySQL配置文件名称为my.ini该文件在MySQL的安装目录下面。修改配置文件中的default-character-set和character-set-server参数值将其改为想要的字 符集名称如gbk、gb2312、latinl等修改完之后重新启动MySQL服务即可生效。读者可以在修改字符集时使用SHOW VARIABLES LIKE ’character_set_°%’;命令查看当前字符集以进行对比。
16、DISTINCT可以应用于所有的列吗
查询结果中如果需要对列进行降序排序可以使用DESC这个关键字只能对其前面的列 进行降序排列。例如要对多列都进行降序排序必须要在每一列的列名后面加DESC关键字。
而DISTINCT不同DISTINCT不能部分使用。换句话说DISTINCT关键字应用于所有列而不 仅是它后面的第一个指定列。例如查询3个字段s_idf_namef_price如果不同记录的这3个字段的组合值都不同则所有记录都会被查询出来。
17、ORDER BY可以和LIMIT混合使用吗
在使用ORDER BY子句时应保证其位于FROM子句之后如果使用LIMIT则必须位 于ORDER BY之后如果子句顺序不正确MySQL将产生错误消息。
18、什么时候使用引号
在查询的时候会看到在WHERE子句中使用条件有的值加上了单引号而有的值未加。 单引号用来限定字符串如果将值与字符串类型列进行比较则需要限定引号而用来与数值进 行比较则不需要用引号。
19、在WHERE子句中必须使用圆括号吗
任何时候使用具有AND和OR操作符的WHERE子句都应该使用圆括号明确操作顺序。 如果条件较多即使能确定计算次序默认的计算次序也可能会使SQL语句不易理解因此使 用括号明确操作符的次序是一个好的习惯。
20、更新或者删除表时必须指定WHERE子句吗
在前面章节中可以看到所有的UPDATE和DELETE语句全都在WHERE子句中指定了条 件。如果省略WHERE子句则UPDATE或DELETE将被应用到表中所有的行。
因此除非 确实打算更新或者删除所有记录否则要注意使用不带WHERE子句的UPDATE或DELETE 语句。建议在对表进行更新和删除操作之前使用SELECT语句确认需要删除的记录以免造 成无法挽回的结果。点击这里总结了55道去BAT面试的MYSQL面试题。
21、索引对数据库性能如此重要应该如何使用它
为数据库选择正确的索引是一项复杂的任务。如果索引列较少则需要的磁盘空间和维护开销 都较少。如果在一个大表上创建了多种组合索引索引文件也会膨胀很快。
而另一方面索引较多 可覆盖更多的查询。可能需要试验若干不同的设计才能找到最有效的索引。可以添加、修改和删 除索引而不影响数据库架构或应用程序设计。因此应尝试多个不同的索引从而建立最优的索引。
22、尽量使用短索引。
对字符串类型的字段进行索引如果可能应该指定一个前缀长度。例如如果有一个 CHAR(255)的列如果在前10个或30个字符内多数值是惟一的则不需要对整个列进行索引。 短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作。
23、MySQL存储过程和函数有什么区别
在本质上它们都是存储程序。函数只能通过return语句返回单个值或者表对象而存储过程 不允许执行return但是可以通过out参数返回多个值。函数限制比较多不能用临时表只能用表变量还有一些函数都不可用等等而存储过程的限制相对就比较少。函数可以嵌入在SQL 语句中使用可以在SELECT语句中作为查询语句的一个部分调用而存储过程一般是作为一个独立的部分来执行。
24、存储过程中的代码可以改变吗
目前MySQL还不提供对已存在的存储过程代码的修改如果必须要修改存储过程必须使用DROP语句删除之后再重新编写代码或者创建一个新的存储过程。
25、存储过程中可以调用其他存储过程吗
存储过程包含用户定义的SQL语句集合可以使用CALL语句调用存储过程当然在存储 过程中也可以使用CALL语句调用其他存储过程但是不能使用DROP语句删除其他存储过程。
26、存储过程的参数不要与数据表中的字段名相同。
在定义存储过程参数列表时应注意把参数名与数据库表中的字段名区别开来否则将出 现无法预期的结果。
27、存储过程的参数可以使用中文吗
一般情况下可能会出现存储过程中传入中文参数的情况例如某个存储过程根据用户的 名字查找该用户的信息传入的参数值可能是中文。这时需要在定义存储过程的时候在后面加 上character set gbk,不然调用存储过程使用中文参数会出错比如定义userInfo存储过程代码 如下 CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT) 28、MySQL中视图和表的区别以及联系是什么
两者的区别 (1)视图是已经编译好的SQL语句是基于SQL语句的结果集的可视化的表而表不是。 (2)视图没有实际的物理记录而基本表有。 (3)表是内容视图是窗口。 (4)表占用物理空间而视图不占用物理空间视图只是逻辑概念的存在表可以及时对它 进行修改但视图只能用创建的语句来修改。 (5)视图是查看数据表的一种方法可以查询数据表中某些字段构成的数据只是一些SQL 语句的集合。从安全的角度来说视图可以防止用户接触数据表因而用户不知道表结构。 (6)表属于全局模式中的表是实表视图属于局部模式的表是虚表。 (7)视图的建立和删除只影响视图本身不影响对应的基本表。
两者的联系 视图view)是在基本表之上建立的表它的结构即所定义的列和内容即所有记录 都来自基本表它依据基本表存在而存在。一个视图可以对应一个基本表也 可以对应多个基本 表。视图是基本表的抽象和在逻辑意义上建立的新关系。
29、使用触发器时须特别注意。
在使用触发器的时候需要注意对于相同的表相同的事件只能创建一个触发器比如对 表account创建了一个BEFORE INSERT触发器那么如果对表account再次创建一个BEFORE INSERT触发器MySQL将会报错此时只可以在表account上创建AFTER INSERT或者 BEFORE UPDATE类型的触发器。灵活的运用触发器将为操作省去很多麻烦。点击这里总结了55道去BAT面试的MYSQL面试题。
30、及时删除不再需要的触发器。
触发器定义之后每次执行触发事件都会激活触发器并执行触发器中的语句。如果需求 发生变化而触发器没有进行相应的改变或者删除则触发器仍然会执行旧的语句从而会影响 新的数据的完整性。因此要将不再使用的触发器及时删除。
31、应该使用哪种方法创建用户
创建用户有几种方法GRANT语句、CREATE USER语句和直接操作user表。一般情况 最好使用GRANT或者CREATE USER语句而不要直接将用户信息插入user表因为user表中存储了全局级别的权限以及其他的账户信息如果意外破坏了 user表中的记录则可能会对 MySQL服务器造成很大影响。
32、mysqldump备份的文件只能在MySQL中使用吗
mysqldump备份的文本文件实际是数据库的一个副本使用该文件不仅可以在MySQL中恢 复数据库而且通过对该文件的简单修改可以使用该文件在SQL Server或者Sybase等其他数 据库中恢复数据库。这在某种程度上实现了数据库之间的迁移。
33、如何选择备份工具
直接复制数据文件是最为直接、快速的备份方法但缺点是基本上不能实现增量备份。备 份时必须确保没有使用这些表。如果在复制一个表的同时服务器正在修改它则复制无效。备份 文件时最好关闭服务器然后重新启动服务器。为了保证数据的一致性需要在备份文件前 执行以下SQL语句 CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT) 目录下即可。mysqlhotcopy 是一个 PERL 程序它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp来快速备份数据库。它是备份数据库或单个表的最快的途径但它只能运行在数据库文件所在的 机器上并且mysqlhotcopy只能用于备份MyISAM表。
mysqlhotcopy适合于小型数据库的备份 数据量不大可以使用mysqlhotcopy程序每天进行一次完全备份。mysqldump将数据表导成SQL脚本文件在不同的MySQL版本之间升级时相对比较合适 这也是最常用的备份方法。mysqldump比直接复制要慢些。也就是把内存中的数据都刷新到磁盘中同时锁定数据表以保证复制过程中不会有新的 数据写入。这种方法备份出来的数据恢复也很简单直接复制回原来的数据库
34、平时应该打开哪些日志
日志既会影响MySQL的性能又会占用大量磁盘空间。因此如果不必要应尽可能少地 开启日志。根据不同的使用环境可以考虑开启不同的日志。例如在开发环境中优化查询效率 低的语句可以开启慢查询日志如果需要记录用户的所有查询操作可以开启通用查询日志 如果需要记录数据的变更可以开启二进制日志错误日志是默认开启的。
35、如何使用二进制日志
二进制日志主要用来记录数据变更。如果需要记录数据库的变化可以开启二进制日志。 基于二进制日志的特性不仅可以用来进行数据恢复还可用于数据复制。
在数据库定期备份的情况下如果出现数据丢失可以先用备份恢复大部分数据然后使用二进制日志恢复最近备份 后变更的数据。在双机热备情况下可以使用MySQL的二进制日志记录数据的变更然后将变 更部分复制到备份服务器上。
36、如何使用慢查询日志
慢查询日志主要用来记录查询时间较长的日志。在开发环境下可以开启慢查询日志来记 录查询时间较长的查询语句然后对这些语句进行优化。通过配long_query_time的值可以灵活地掌握不同程度的慢查询语句。
37、是不是索引建立得越多越好
合理的索引可以提高查询的速度但不是索引越多越好。在执行插入语句的时候MySQL 要为新插入的记录建立索引。所以过多的索引会导致插入操作变慢。原则上是只有查询用的字段 才建立索引。
