MySQL培训材料

常用MySQL知识点一、知识点目录(1)MySQL体系结构(2)常用表引擎(3)数据类型(4)sql语句(和Oracle有区别的)(5)常用字符、数值、日期函数等(6)字符集问题(7)SQL_MODE(8)并发控制、事务，MVCC，锁定机制(9)用户、权限、安全(10)索引(11)SQL执行计划及优化(12)查询缓存(13)MySQL高可用集群：主从replication，NDBcluster(14)预编译、全文索引、mergetables和分区支持。二、细节知识点1、mysql体系结构MySQL体系结构大概分为三层：顶层：处理connection请求，安全认证等第二层：这一层是MySQL的核心，包括代码解析，优化，缓存，以及内置函数，过程视图、触发器都在这一层工作。第三层：存储引擎层，这一层用来存储和获取数据，有点像linux的文件系统，存储引擎通过API仅和MySQL服务器进行通信？？？？？？？？？？？？？，存储引擎并不解析SQL语句，也不和其他进行通信。每种存储引擎都有自己的优点和缺点。2、常用表引擎MySQLAB引入了新的插件式存储引擎体系结构，允许将存储引擎加载到正在运新的MySQL服务器中。Mysql支持引擎：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDBcluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB、BDB支持事务安全，其他都是非事务表（ACID），其中MyISAM是MySQL的默认存储引擎，即不指定其他引擎时默认就是MyISAM引擎，当然也可以通过storageengine更改默认引擎mysqlshowvariableslike'stor%';查看当前数据库引擎：mysqlshowengines\G在创建表或altertable的时候可以通过指定engine参数指定存储引擎。MyISAM：默认的MySQL插件式存储引擎，因为采用表级锁，所以适合以读操作和插入操作为主，而只有很少的删除和更新操作的环境，不支持事务，即无法回滚，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。InnoDB：用于事务处理应用程序，包括ACID事务支持，支持外键，支持MVCC。采用行级锁，可以大大降低而删除和更新导致的锁定，可以支持commit和rollback，对于计费、金融对数据准确定要求较高的系统。BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问，对表的大小有限制，通常用于更新不频繁的小表。Merge：允许MySQLDBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们，这样可以突破单表MyISAM的大小限制，对于诸如数据仓储等VLDB环境十分适合Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。支持MVCC。3、数据类型3.1数值型整数类型存储字节属性最小、最大值tinyint1Auto_incrementunsignedzerofill有符号:-72~72-1无符号:0~82-1Smallint2Auto_incrementUnsignedzerofill有符号:-152~152-1无符号:0~162-1Mediumint3Auto_incrementUnsignedzerofill有符号:-232~232-1无符号:0~242-1Int、integer4Auto_incrementUnsignedzerofill有符号:-312~312-1无符号:0~322-1Bigint8Auto_incrementUnsignedzerofill有符号:-632~632-1无符号:0~642-1这些数据类型也可以指定宽度，例如int(4)，意味着如果插入数值宽度小于指定的4，则自动从左侧用空格填满宽度，并不限制保存的值的最大和最小值。当指定zerofill属性时，则默认使用unsigned即无符号，此时填充有空格改为0。属性auto_increment，用于产生唯一标识符或顺序值，仅用于整数类型，一个表中只能有一个auto_increment的列，对于任何想使用auto_increment的列，应该定义为notnull，并并定义为primarykey或unique。（以上最大值，当插入数据时，MySQL工作在非严格模式下，对于无意义的则转换为允许的值，如intunsigned，插入负值，则会转化为0,超过4292967295，则转化为4292967295插入，如果工作在严格模式下，则无法插入。）浮点数类型存储字节属性最小、最大值float4Unsigned1.1754494351E-383.402823466E+38double8Unsigned2.2250738585072014E-3081.7976931348623157E+38定点数类型存储字节属性最小、最大值Decimal(M,D)NUMERIC(M,D)M+2Unsigned取值范围由M和D来定位类型存储字节属性最小、最大值BIT(M)1~8Bit(1)bit(64)浮点数、定点数都用(M,D)，M表示共M位数字（整数+小数），D是小数点后面的长度。浮点数不指定精度，会按照硬件和操作系统默认精度。定点数不指定精度时，默认整数位10，小数位是0.两者不同的是定点数是采用字符串形式存储，存储更精确，某个列指定了浮点数或定点数的精度后，插入的精度超过实际精度，在普通模式下浮点数不会警告，而定点数会警告，都是四舍五入插入。3.2日期和时间类型日期和时间类型存储字节零值表示最小、最大值DATE40000-00-001000-01-019999-12-31DATETIME80000-00-0000:00:001000-01-0100:00:009999-12-3123:59:59TIMESTAMP400000000000000197001010800012038年某个时刻TIME300:00:00-838:59:59838:59:59YEAR1000019012155TIMESTAMP:也表示年月日时分秒，在列值后面+0，可获得数字值。固定宽度为19个字符，支持时间范围较小，相对于DATETIME。表中第一个TIMESTAMP列会自动设置为系统时间，timestamp受MySQL版本和SQLMODE影响较大，具体参考MySQL文档。3.3字符串类型3.3.1CHAR(m)和VARCHAR(m)Char(m)：定长存储，m范围0-255.char列长度固定为创建表时声明的长度，当保存char值时，不足部分在尾部以空格填充保存。检索时会自动删除尾部的空格，无论检索或者存储均不进行大小写转换。Varchar(m)：变长存储，m范围0-65535.。两者都能存储m个字符（包括数字、字母、汉字、字符等）（注：关于NULL和’’,尽量不要定义NULL字段，否则会出现问题，要么在程序中控制，对于空值统一插入一个有意义的值。）mysqldescv2;+-------+------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+------------+------+-----+---------+-------+|v1|char(4)|YES||NULL|||v2|varchar(4)|YES||NULL||+-------+------------+------+-----+---------+-------+2rowsinset(0.00sec)mysqlinsertintov2(v1,v2)values('aaa',NULL),('bbb','');QueryOK,2rowsaffected(0.00sec)Records:2Duplicates:0Warnings:0mysqlinsertintov2(v1)values('ccc');QueryOK,1rowaffected(0.00sec)mysqlselect*fromv2;+------+------+|v1|v2|+------+------+|aaa|NULL||bbb|||ccc|NULL|+------+------+3rowsinset(0.00sec)mysqlselect*fromv2wherev2isnull;+------+------+|v1|v2|+------+------+|aaa|NULL||ccc|NULL|+------+------+2rowsinset(0.00sec)CHAR是固定长度，所以处理速度比varchar更快，缺点是浪费存储空间。不同的存储引擎对两者有不同的使用原则：MyISAM引擎：建议使用固定长度的数据列。MEMORY引擎：无论定义char或varchar都按char进行处理InnoDB：建议使用varchar列。3.3.2BINARY(m)和VARBINARY(m)类似于char和varchar，不同的是，它们存储的是二进制字符而不包含非二进制字符。3.3.3BLOB和TEXTBLOB：字节字符串TINYBLOB0~255字节BLOB0~65535字节MEDIUMBLOB0~167772150字节LONGBLOB0~4294967295字节TEXT：字符字符串TINYTEXT0~255字节TEXT0~65535字节MEDIUMTEXT0~167772150字节LONGTEXT0~4294967295字节二者区别：BLOB可以保存二进制数据：如照片，TEXT只能保存字符数据：比如文章。对于BLOB和TEXT使用过程中，会引起一些性能问题，特别是执行了大量了删除操作。需要定期optimizetable进行碎片整理。在BLOB和TEXT数据列建索引，必须使用前缀索引，某些情况下，需要单独把这些列分离到单独的表中，减少主表的碎片。2.3.4ENUM类型、SET类型略？？？？？？？？？？？？4、SQL语句插入多值：insertintotab(filed1,filed2,filed3)values(record1_value1,record1_value2,record1_value3),(record2_value1,record2_value2,record2_value3),….(record3_valuen,recordn_value2,recordn_value3)REPLACEintotab_name(col_name1,…..)values(filed1,….)REPLACE的运行与INSERT很相像。只有一点除外，如果表中的一个旧记录与一个用于PRIMARYKEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除.REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和,受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）。为了能够使用REPLACE，您必须同时拥有表的INSERT和DELETE权限。限制查询行数:limitstart_row,row_countStart_row表示开始行数、row_count表示显示的行数，limit2,3表示显示第3行到第5；Start_row可以为空，linitn表示显示前N行记录。表连接：左右连接.Right/leftjoinSelect*fromt1left/rightjoint2ont1.id=t2.id匹配：like/notlikeregexp/notregexp()正则表达式Like