MySQL培训材料.docx

1、MySQL培训材料常用MySQL知识点一、知识点目录 (1)MySQL体系结构(2)常用表引擎(3)数据类型(4)sql语句(和Oracle有区别的)(5)常用字符、数值、日期函数等(6)字符集问题(7)SQL_MODE(8)并发控制、事务，MVCC，锁定机制(9)用户、权限、安全(10)索引(11)SQL执行计划及优化(12)查询缓存(13)MySQL高可用集群：主从replication，NDB cluster(14)预编译、全文索引、merge tables和分区支持。二、细节知识点1、mysql体系结构MySQL体系结构大概分为三层：顶层：处理connection请求，安全认证等第二层

2、：这一层是MySQL的核心，包括代码解析，优化，缓存，以及内置函数，过程视图、触发器都在这一层工作。第三层：存储引擎层，这一层用来存储和获取数据，有点像linux的文件系统，存储引擎通过API仅和MySQL服务器进行通信？，存储引擎并不解析SQL语句，也不和其他进行通信。每种存储引擎都有自己的优点和缺点。2、常用表引擎MySQL AB引入了新的插件式存储引擎体系结构，允许将存储引擎加载到正在运新的MySQL服务器中。Mysql支持引擎：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB cluster、ARCHIVE、CSV、BLACKHOLE、FEDERAT

3、ED等，其中InnoDB、BDB支持事务安全，其他都是非事务表（ACID），其中MyISAM是MySQL的默认存储引擎，即不指定其他引擎时默认就是MyISAM引擎，当然也可以通过storage engine更改默认引擎mysql show variables like stor%;查看当前数据库引擎：mysql show engines G在创建表或alter table的时候可以通过指定engine参数指定存储引擎。MyISAM：默认的MySQL插件式存储引擎，因为采用表级锁，所以适合以读操作和插入操作为主，而只有很少的删除和更新操作的环境，不支持事务，即无法回滚，它是在Web、数据仓储和其

4、他应用环境下最常使用的存储引擎之一。InnoDB：用于事务处理应用程序，包括ACID事务支持，支持外键，支持MVCC。采用行级锁，可以大大降低而删除和更新导致的锁定，可以支持commit和rollback，对于计费、金融对数据准确定要求较高的系统。BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问，对表的大小有限制，通常用于更新不频繁的小表。Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们，这样

5、可以突破单表MyISAM的大小限制，对于诸如数据仓储等VLDB环境十分适合Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。支持MVCC。3、数据类型3.1 数值型 整数类型存储字节属性最小、最大值tinyint1Auto_incrementunsignedzerofill有符号: -1无符号: 0-1Smallint2Auto_incrementUnsignedzerofill有符号: -1无符号: 0-1Mediumint3Auto_incrementUnsignedzerofill有符号: -1无

6、符号: 0-1Int、integer4Auto_incrementUnsignedzerofill有符号: -1无符号: 0-1Bigint8Auto_incrementUnsignedzerofill有符号: -1无符号: 0-1 这些数据类型也可以指定宽度，例如int(4)，意味着如果插入数值宽度小于指定的4，则自动从左侧用空格填满宽度，并不限制保存的值的最大和最小值。当指定zerofill属性时，则默认使用unsigned即无符号，此时填充有空格改为0。属性auto_increment，用于产生唯一标识符或顺序值，仅用于整数类型，一个表中只能有一个auto_increment的列，对于任

7、何想使用auto_increment的列，应该定义为not null，并并定义为primary key或unique。（以上最大值，当插入数据时，MySQL工作在非严格模式下，对于无意义的则转换为允许的值，如int unsigned，插入负值，则会转化为0,超过4292967295，则转化为4292967295插入，如果工作在严格模式下，则无法插入。）浮点数类型存储字节属性最小、最大值float4Unsigned1.1754494351E-383.402823466E+38double8Unsigned2.2250738585072014E-3081.7976931348623157E+38定

8、点数类型存储字节属性最小、最大值Decimal(M,D) NUMERIC(M,D)M+2Unsigned取值范围由M和D来定位类型存储字节属性最小、最大值BIT(M)18Bit(1) bit(64)浮点数、定点数都用(M,D)，M表示共M位数字（整数+小数），D是小数点后面的长度。浮点数不指定精度，会按照硬件和操作系统默认精度。定点数不指定精度时，默认整数位10，小数位是0.两者不同的是定点数是采用字符串形式存储，存储更精确，某个列指定了浮点数或定点数的精度后，插入的精度超过实际精度，在普通模式下浮点数不会警告，而定点数会警告，都是四舍五入插入。3.2日期和时间类型日期和时间类型存储字节零值表

9、示最小、最大值DATE40000-00-001000-01-019999-12-31DATETIME80000-00-00 00:00:001000-01-01 00:00:009999-12-31 23:59:59TIMESTAMP400000000000000197001010800012038年某个时刻TIME300:00:00-838:59:59838:59:59YEAR1000019012155TIMESTAMP:也表示年月日时分秒，在列值后面+0，可获得数字值。固定宽度为19个字符，支持时间范围较小，相对于DATETIME。表中第一个TIMESTAMP列会自动设置为系统时间，tim

10、estamp受MySQL版本和SQLMODE影响较大，具体参考MySQL文档。3.3字符串类型 3.3.1 CHAR(m)和VARCHAR(m) Char(m)：定长存储，m范围0-255.char列长度固定为创建表时声明的长度，当保存char值时，不足部分在尾部以空格填充保存。检索时会自动删除尾部的空格，无论检索或者存储均不进行大小写转换。 Varchar(m)：变长存储，m范围0-65535.。 两者都能存储m个字符（包括数字、字母、汉字、字符等） （注：关于NULL和,尽量不要定义NULL字段，否则会出现问题，要么在程序中控制，对于空值统一插入一个有意义的值。）mysql desc v2

11、;+-+-+-+-+-+-+| Field | Type | Null | Key | Default | Extra |+-+-+-+-+-+-+| v1 | char(4) | YES | | NULL | | v2 | varchar(4) | YES | | NULL | |+-+-+-+-+-+-+2 rows in set (0.00 sec)mysql insert into v2(v1,v2) values(aaa,NULL),(bbb,);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings

12、: 0mysql insert into v2(v1) values(ccc);Query OK, 1 row affected (0.00 sec)mysql select * from v2;+-+-+| v1 | v2 |+-+-+| aaa | NULL | bbb | | ccc | NULL |+-+-+3 rows in set (0.00 sec)mysql select * from v2 where v2 is null;+-+-+| v1 | v2 |+-+-+| aaa | NULL | ccc | NULL |+-+-+2 rows in set (0.00 sec)

13、CHAR是固定长度，所以处理速度比varchar更快，缺点是浪费存储空间。不同的存储引擎对两者有不同的使用原则：MyISAM引擎：建议使用固定长度的数据列。MEMORY引擎：无论定义char或varchar都按char进行处理InnoDB：建议使用varchar列。3.3.2 BINARY(m)和VARBINARY(m) 类似于char和varchar，不同的是，它们存储的是二进制字符而不包含非二进制字符。3.3.3 BLOB和TEXTBLOB：字节字符串TINYBLOB0255字节BLOB065535字节MEDIUMBLOB0167772150字节LONGBLOB04294967295字节T

14、EXT：字符字符串TINYTEXT0255字节TEXT065535字节MEDIUMTEXT0167772150字节LONGTEXT04294967295字节二者区别：BLOB可以保存二进制数据：如照片，TEXT只能保存字符数据：比如文章。对于BLOB和TEXT使用过程中，会引起一些性能问题，特别是执行了大量了删除操作。需要定期optimize table进行碎片整理。在BLOB和TEXT数据列建索引，必须使用前缀索引，某些情况下，需要单独把这些列分离到单独的表中，减少主表的碎片。2.3.4 ENUM类型、SET类型略？4、SQL语句 插入多值 ：insert into tab (filed1,

15、filed2,filed3) values (record1_value1,record1_value2,record1_value3), (record2_value1,record2_value2,record2_value3), . (record3_valuen,recordn_value2,recordn_value3)REPLACE into tab_name (col_name1,.) values (filed1,.)REPLACE的运行与INSERT很相像。只有一点除外，如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录

16、被插入之前，旧记录被删除. REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和, 受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）。为了能够使用REPLACE，您必须同时拥有表的INSERT和DELETE权限。 限制查询行数: limit start_row,row_count Start_row表示开始行数、row_count表示显示的行数，limit 2,3表示显示第3行到第5；Start_row可以为空，linit n表示显示前N行记录。 表连接：左右连接. Righ

17、t /left join Select * from t1 left/right join t2 on t1.id=t2.id 匹配：like/not like regexp/not regexp()正则表达式 Like 和Oracle类似: _表示单字符 %代表任意0-n个字符 Regexp正则表达式匹配，代表包含的意思。 匹配字符串的开始处 $ 匹配字符串的结束处 . 匹配任意单字符，包括回车换行符 * 匹配0-n个前面的字符 匹配括号内的任意字符 -代表一个范围 0-9代表任意数字 匹配不在括号内的任意字符 a* 匹配0或多个a a+ 匹配1个或多个a a? 匹配0个或1个a a1| a

18、2 匹配a1或a2 am 匹配m个a am, 匹配m个或更多个a am,n 匹配m-n个a a(,n) 匹配0-n个a 匹配特殊字符时使用 屏蔽 select a+2 regexp a+2; 1truncate table tab_name属于对ORACLE的SQL扩展，会重置AUTO_INCREMENT计数器。5、常用字符、数值、日期函数5.1字符串函数函数功能Concat(S1,S2,Sn)连接S1、S2为一个字符串INSERT(str,x,y,instr)把字符串str从x位置到y个字符长的子串替换为字符串instrLOWER(str) UPPER(str)把字符串str变为大小写LEF

19、T(str,x) RIGHT(str,x)返回字符串str最左边或最右边的x个字符串LTRIM(str) RTRIM(str) TRIM(str)删除字符串str左边或右边或两边的空格CHAR_LENGTH(str)返回值为字符串str 的长度，多字节字符算单字符，例如汉字算一个字符长度。LENGTH(str)返回值为字符串str 的长度，多字节字符算多字符，一个汉字算2个字符长度mysql select concat(aa ,bb);+-+| concat(aa ,bb) |+-+| aa bb | +-+1 row in set (0.00 sec)在SQL_MODE为pipes_as_c

20、oncat，|也可以用于两个字符串连接mysql set session sql_mode=pipes_as_concat;Query OK, 0 rows affected (0.00 sec)mysql select sql_mode;+-+| sql_mode |+-+| PIPES_AS_CONCAT | +-+1 row in set (0.00 sec)mysql select aa| bb;+-+| aa| bb |+-+| aabb | +-+1 row in set (0.00 sec)5.2数值函数函数功能CEIL(x)返回大于x的最小整数值FLOOR(x)返回小于x的最大

21、整数值RAND()返回0-1随机值ROUND(x) ROUND(x,y)四舍五入，y0,则在小数点后y位四舍五入，y0，在小数点左侧y位四舍五入。Truncate(x,y)截断数字x，y=0直接返回整数部分，y0截断小数点右侧y位5.3 日期和时间函数函数功能CURDATE() +0返回当期日期 +0格式变为YYYYMMDDCURTIME() +0返回当前时间 +0格式变为HHMMSSNOW()返回当前日期和时间DATEDIFF(expr,expr2)返回起始时间 expr和结束时间expr2之间的天数, Expr和expr2 为日期或 date-and-time 表达式DATE_ADD(da

22、te,INTERVAL expr type)返回一个日期或时间加上一个时间间隔的时间值DATE_FORMAT(date,format)根据format格式化日期date值DAYOFMONTH(date) 或 DAY(date)返回date 对应的该月日期，范围是从 1到31DAYNAME(date)返回date对应工作日，范围周一到周日DAYOFWEEK(date) DAYOFYEAR(date)返回一周、一年的第n天Type和expr格式type 值预期的 expr 格式MICROSECONDMICROSECONDSSECONDSECONDSMINUTEMINUTESHOURHOURSDAY

23、DAYSWEEKWEEKSMONTHMONTHSQUARTERQUARTERSYEARYEARSSECOND_MICROSECONDSECONDS.MICROSECONDSMINUTE_MICROSECONDMINUTES.MICROSECONDSMINUTE_SECONDMINUTES:SECONDSHOUR_MICROSECONDHOURS.MICROSECONDSHOUR_SECONDHOURS:MINUTES:SECONDSHOUR_MINUTEHOURS:MINUTESDAY_MICROSECONDDAYS.MICROSECONDSDAY_SECONDDAYS HOURS:MINUT

24、ES:SECONDSDAY_MINUTEDAYS HOURS:MINUTESDAY_HOURDAYS HOURSYEAR_MONTHYEARS-MONTHSFormat格式说明符说明%a工作日的缩写名称 (Sun.Sat)%b月份的缩写名称 (Jan.Dec)%c月份，数字形式(0.12)%D带有英语后缀的该月日期 (0th, 1st, 2nd, 3rd, .)%d该月日期, 数字形式 (00.31)%e该月日期, 数字形式(0.31)%f微秒 (000000.999999)%H小时(00.23)%h小时(01.12)%I小时 (01.12)%i分钟,数字形式 (00.59)%j一年中的天数

25、(001.366)%k小时 (0.23)%l小时 (1.12)%M月份名称 (January.December)%m月份, 数字形式 (00.12)%p上午（AM）或下午（ PM）%r时间 , 12小时制 (小时hh:分钟mm:秒数ss 后加 AM或PM)%S秒 (00.59)%s秒 (00.59)%T时间 , 24小时制 (小时hh:分钟mm:秒数ss)%U周 (00.53), 其中周日为每周的第一天%u周 (00.53), 其中周一为每周的第一天%V周 (01.53), 其中周日为每周的第一天 ; 和 %X同时使用%v周 (01.53), 其中周一为每周的第一天 ; 和 %x同时使用%W工

26、作日名称 (周日.周六)%w一周中的每日 (0=周日.6=周六)%X该周的年份，其中周日为每周的第一天, 数字形式,4位数;和%V同时使用%x该周的年份，其中周一为每周的第一天, 数字形式,4位数;和%v同时使用%Y年份, 数字形式,4位数%y年份, 数字形式 (2位数)%文字字符mysql select date_format(19830112,%Y,%m,%d);+-+| date_format(19830112,%Y,%m,%d) |+-+| 1983,01,12 | +-+1 row in set (0.00 sec)mysql select dayname(now();+-+| da

27、yname(now() |+-+| Friday | +-+1 row in set (0.00 sec)mysql select datediff(now(),19830224);+-+| datediff(now(),19830224) |+-+| 9549 | +-+1 row in set (0.00 sec)mysql select date_add(now(),interval -2 hour);+-+| date_add(now(),interval -2 hour) |+-+| 2009-04-17 08:57:47 | +-+1 row in set (0.00 sec)mysql select date_add(now(),in