MySQL培训材料.docx

MySQL培训材料.docx

《MySQL培训材料.docx》由会员分享，可在线阅读，更多相关《MySQL培训材料.docx（25页珍藏版）》请在冰豆网上搜索。

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更改默认引擎

mysql>showvariableslike'stor%';

查看当前数据库引擎：

mysql>showengines\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数值型

整数类型

存储字节

属性

最小、最大值

tinyint

1

Auto_increment

unsigned

zerofill

有符号:

-

~

-1

无符号:

0~

-1

Smallint

2

Auto_increment

Unsigned

zerofill

有符号:

-

~

-1

无符号:

0~

-1

Mediumint

3

Auto_increment

Unsigned

zerofill

有符号:

-

~

-1

无符号:

0~

-1

Int、integer

4

Auto_increment

Unsigned

zerofill

有符号:

-

~

-1

无符号:

0~

-1

Bigint

8

Auto_increment

Unsigned

zerofill

有符号:

-

~

-1

无符号:

0~

-1

这些数据类型也可以指定宽度，例如int（4），意味着如果插入数值宽度小于指定的4，则自动从左侧用空格填满宽度，并不限制保存的值的最大和最小值。

当指定zerofill属性时，则默认使用unsigned即无符号，此时填充有空格改为0。

属性auto_increment，用于产生唯一标识符或顺序值，仅用于整数类型，一个表中只能有一个auto_increment的列，对于任何想使用auto_increment的列，应该定义为notnull，并并定义为primarykey或unique。

（以上最大值，当插入数据时，MySQL工作在非严格模式下，对于无意义的则转换为允许的值，如intunsigned，插入负值，则会转化为0,超过4292967295，则转化为4292967295插入，如果工作在严格模式下，则无法插入。

）

浮点数类型

存储字节

属性

最小、最大值

float

4

Unsigned

1.1754494351E-38

3.402823466E+38

double

8

Unsigned

2.2250738585072014E-308

1.7976931348623157E+38

定点数类型

存储字节

属性

最小、最大值

Decimal（M,D）NUMERIC（M,D）

M+2

Unsigned

取值范围由M和D来定

位类型

存储字节

属性

最小、最大值

BIT（M）

1~8

Bit

（1）bit（64）

浮点数、定点数都用（M,D），M表示共M位数字（整数+小数），D是小数点后面的长度。

浮点数不指定精度，会按照硬件和操作系统默认精度。

定点数不指定精度时，默认整数位10，小数位是0.两者不同的是定点数是采用字符串形式存储，存储更精确，某个列指定了浮点数或定点数的精度后，插入的精度超过实际精度，在普通模式下浮点数不会警告，而定点数会警告，都是四舍五入插入。

3.2日期和时间类型

日期和时间类型

存储字节

零值表示

最小、最大值

DATE

4

0000-00-00

1000-01-01

9999-12-31

DATETIME

8

0000-00-0000:

00:

00

1000-01-0100:

00:

00

9999-12-3123:

59:

59

TIMESTAMP

4

00000000000000

19700101080001

2038年某个时刻

TIME

3

00:

00:

00

-838:

59:

59

838:

59:

59

YEAR

1

0000

1901

2155

TIMESTAMP:

也表示年月日时分秒，在列值后面+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字段，否则会出现问题，要么在程序中控制，对于空值统一插入一个有意义的值。

）

mysql>descv2;

+-------+------------+------+-----+---------+-------+

|Field|Type|Null|Key|Default|Extra|

+-------+------------+------+-----+---------+-------+

|v1|char（4）|YES||NULL||

|v2|varchar（4）|YES||NULL||

+-------+------------+------+-----+---------+-------+

2rowsinset（0.00sec）

mysql>insertintov2（v1,v2）values（'aaa',NULL）,（'bbb',''）;

QueryOK,2rowsaffected（0.00sec）

Records:

2Duplicates:

0Warnings:

0

mysql>insertintov2（v1）values（'ccc'）;

QueryOK,1rowaffected（0.00sec）

mysql>select*fromv2;

+------+------+

|v1|v2|

+------+------+

|aaa|NULL|

|bbb||

|ccc|NULL|

+------+------+

3rowsinset（0.00sec）

mysql>select*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和TEXT

BLOB：

字节字符串

TINYBLOB

0~255字节

BLOB

0~65535字节

MEDIUMBLOB

0~167772150字节

LONGBLOB

0~4294967295字节

TEXT：

字符字符串

TINYTEXT

0~255字节

TEXT

0~65535字节

MEDIUMTEXT

0~167772150字节

LONGTEXT

0~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_count

Start_row表示开始行数、row_count表示显示的行数，limit2,3表示显示第3行到第5；Start_row可以为空，linitn表示显示前N行记录。

表连接：

左右连接.Right/leftjoin

Select*fromt1left/rightjoint2ont1.id=t2.id

匹配：

like/notlikeregexp/notregexp（）正则表达式

Like和Oracle类似:

_表示单字符%代表任意0-n个字符

Regexp正则表达式匹配，代表包含的意思。

^匹配字符串的开始处

$匹配字符串的结束处

.匹配任意单字符，包括回车换行符

*匹配0-n个前面的字符

[…]匹配括号内的任意字符-代表一个范围0-9代表任意数字

[^…]匹配不在括号内的任意字符

a*匹配0或多个a

a+匹配1个或多个a

a?

匹配0个或1个a

a1|a2匹配a1或a2

a{m}匹配m个a

a{m,}匹配m个或更多个a

a{m,n}匹配m-n个a

a（,n）匹配0-n个a

匹配特殊字符时使用\\屏蔽select'a+2'regexp'a\\+2';1

truncatetabletab_name

属于对ORACLE的SQL扩展，会重置AUTO_INCREMENT计数器。

5、常用字符、数值、日期函数

5.1字符串函数

函数

功能

Concat（S1,S2,…Sn）

连接S1、S2…为一个字符串

INSERT（str,x,y,instr）

把字符串str从x位置到y个字符长的子串替换为字符串instr

LOWER（str）UPPER（str）

把字符串str变为大小写

LEFT（str,x）RIGHT（str,x）

返回字符串str最左边或最右边的x个字符串

LTRIM（str）RTRIM（str）TRIM（str）

删除字符串str左边或右边或两边的空格

CHAR_LENGTH（str）

返回值为字符串str的长度，多字节字符算单字符，例如汉字算一个字符长度。

LENGTH（str）

返回值为字符串str的长度，多字节字符算多字符，一个汉字算2个字符长度

mysql>selectconcat（'aa','bb'）;

+--------------------+

|concat（'aa','bb'）|

+--------------------+

|aabb|

+--------------------+

1rowinset（0.00sec）

在SQL_MODE为pipes_as_concat，||也可以用于两个字符串连接

mysql>setsessionsql_mode=pipes_as_concat;

QueryOK,0rowsaffected（0.00sec）

mysql>select@@sql_mode;

+-----------------+

|@@sql_mode|

+-----------------+

|PIPES_AS_CONCAT|

+-----------------+

1rowinset（0.00sec）

mysql>select'aa'||'bb';

+-------------+

|'aa'||'bb'|

+-------------+

|aabb|

+-------------+

1rowinset（0.00sec）

5.2数值函数

函数

功能

CEIL（x）

返回大于x的最小整数值

FLOOR（x）

返回小于x的最大整数值

RAND（）

返回0-1随机值

ROUND（x）ROUND（x,y）

四舍五入，y>0,则在小数点后y位四舍五入，y<0，在小数点左侧y位四舍五入。

Truncate（x,y）

截断数字x，y=0直接返回整数部分，y<0截断小数点左侧y位,y>0截断小数点右侧y位

5.3日期和时间函数

函数

功能

CURDATE（）+0

返回当期日期+0格式变为YYYYMMDD

CURTIME（）+0

返回当前时间+0格式变为HHMMSS

NOW（）

返回当前日期和时间

DATEDIFF（expr,expr2）

返回起始时间expr和结束时间expr2之间的天数,Expr和expr2为日期或date-and-time表达式

DATE_ADD（date,INTERVALexprtype）

返回一个日期或时间加上一个时间间隔的时间值

DATE_FORMAT（date,format）

根据format格式化日期date值

DAYOFMONTH（date）

或DAY（date）

返回date对应的该月日期，范围是从1到31

DAYNAME（date）

返回date对应工作日，范围周一到周日

DAYOFWEEK（date）DAYOFYEAR（date）

返回一周、一年的第n天

Type和expr格式

type值

预期的expr格式

MICROSECOND

MICROSECONDS

SECOND

SECONDS

MINUTE

MINUTES

HOUR

HOURS

DAY

DAYS

WEEK

WEEKS

MONTH

MONTHS

QUARTER

QUARTERS

YEAR

YEARS

SECOND_MICROSECOND

'SECONDS.MICROSECONDS'

MINUTE_MICROSECOND

'MINUTES.MICROSECONDS'

MINUTE_SECOND

'MINUTES:

SECONDS'

HOUR_MICROSECOND

'HOURS.MICROSECONDS'

HOUR_SECOND

'HOURS:

MINUTES:

SECONDS'

HOUR_MINUTE

'HOURS:

MINUTES'

DAY_MICROSECOND

'DAYS.MICROSECONDS'

DAY_SECOND

'DAYSHOURS:

MINUTES:

SECONDS'

DAY_MINUTE

'DAYSHOURS:

MINUTES'

DAY_HOUR

'DAYSHOURS'

YEAR_MONTH

'YEARS-MONTHS'

Format格式

说明符

说明

%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

一年中的天数（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

工作日名称（周日..周六）

%w

一周中的每日（0=周日..6=周六）

%X

该周的年份，其中周日为每周的第一天,数字形式,4位数;和%V同时使用

%x

该周的年份，其中周一为每周的第一天,数字形式,4位数;和%v同时使用

%Y

年份,数字形式,4位数

%y

年份,数字形式（2位数）

%%

‘%’文字字符

mysql>selectdate_format（'19830112','%Y,%m,%d'）;

+------------------------------------+

|date_format（'19830112','%Y,%m,%d'）|

+------------------------------------+

|1983,01,12|

+------------------------------------+

1rowinset（0.00sec）

mysql>selectdayname（now（））;

+----------------+

|dayname（now（））|

+----------------+

|Friday|

+----------------+

1rowinset（0.00sec）

mysql>selectdatediff（now（）,19830224）;

+--------------------------+

|datediff（now（）,19830224）|

+--------------------------+

|9549|

+--------------------------+

1rowinset（0.00sec）

mysql>selectdate_add（now（）,interval-2hour）;

+----------------------------------+

|date_add（now（）,interval-2hour）|

+----------------------------------+

|2009-04-1708:

57:

47|

+----------------------------------+

1rowinset（0.00sec）

mysql>selectdate_add（now（）,in