MySQL培训材料Word文件下载.docx
《MySQL培训材料Word文件下载.docx》由会员分享,可在线阅读,更多相关《MySQL培训材料Word文件下载.docx(25页珍藏版)》请在冰豆网上搜索。
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~
Smallint
2
Unsigned
Mediumint
3
Int、integer
4
Bigint
8
这些数据类型也可以指定宽度,例如int(4),意味着如果插入数值宽度小于指定的4,则自动从左侧用空格填满宽度,并不限制保存的值的最大和最小值。
当指定zerofill属性时,则默认使用unsigned即无符号,此时填充有空格改为0。
属性auto_increment,用于产生唯一标识符或顺序值,仅用于整数类型,一个表中只能有一个auto_increment的列,对于任何想使用auto_increment的列,应该定义为notnull,并并定义为primarykey或unique。
(以上最大值,当插入数据时,MySQL工作在非严格模式下,对于无意义的则转换为允许的值,如intunsigned,插入负值,则会转化为0,超过4292967295,则转化为4292967295插入,如果工作在严格模式下,则无法插入。
)
浮点数类型
float
1.1754494351E-38
3.402823466E+38
double
2.2250738585072014E-308
1.7976931348623157E+38
定点数类型
Decimal(M,D)NUMERIC(M,D)
M+2
取值范围由M和D来定
位类型
BIT(M)
1~8
Bit
(1)bit(64)
浮点数、定点数都用(M,D),M表示共M位数字(整数+小数),D是小数点后面的长度。
浮点数不指定精度,会按照硬件和操作系统默认精度。
定点数不指定精度时,默认整数位10,小数位是0.两者不同的是定点数是采用字符串形式存储,存储更精确,某个列指定了浮点数或定点数的精度后,插入的精度超过实际精度,在普通模式下浮点数不会警告,而定点数会警告,都是四舍五入插入。
3.2日期和时间类型
日期和时间类型
零值表示
DATE
0000-00-00
1000-01-01
9999-12-31
DATETIME
0000-00-0000:
00:
00
1000-01-0100:
9999-12-3123:
59:
59
TIMESTAMP
00000000000000
19700101080001
2038年某个时刻
TIME
-838:
838:
YEAR
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字段,否则会出现问题,要么在程序中控制,对于空值统一插入一个有意义的值。
descv2;
+-------+------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
|v1|char(4)|YES||NULL||
|v2|varchar(4)|YES||NULL||
2rowsinset(0.00sec)
insertintov2(v1,v2)values('
aaa'
NULL),('
bbb'
'
'
);
QueryOK,2rowsaffected(0.00sec)
Records:
2Duplicates:
0Warnings:
0
insertintov2(v1)values('
ccc'
QueryOK,1rowaffected(0.00sec)
select*fromv2;
+------+------+
|v1|v2|
|aaa|NULL|
|bbb||
|ccc|NULL|
3rowsinset(0.00sec)
select*fromv2wherev2isnull;
CHAR是固定长度,所以处理速度比varchar更快,缺点是浪费存储空间。
不同的存储引擎对两者有不同的使用原则:
MyISAM引擎:
建议使用固定长度的数据列。
MEMORY引擎:
无论定义char或varchar都按char进行处理
建议使用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
TEXT
MEDIUMTEXT
LONGTEXT
二者区别:
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个字符长度
selectconcat('
aa'
bb'
+--------------------+
|concat('
)|
|aabb|
1rowinset(0.00sec)
在SQL_MODE为pipes_as_concat,||也可以用于两个字符串连接
setsessionsql_mode=pipes_as_concat;
QueryOK,0rowsaffected(0.00sec)
select@@sql_mode;
+-----------------+
|@@sql_mode|
|PIPES_AS_CONCAT|
select'
aa'
||'
+-------------+
|'
|
|aabb|
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
YEARS
SECOND_MICROSECOND
SECONDS.MICROSECONDS'
MINUTE_MICROSECOND
MINUTES.MICROSECONDS'
MINUTE_SECOND
MINUTES:
SECONDS'
HOUR_MICROSECOND
HOURS.MICROSECONDS'
HOUR_SECOND
HOURS:
HOUR_MINUTE
MINUTES'
DAY_MICROSECOND
DAYS.MICROSECONDS'
DAY_SECOND
DAYSHOURS:
DAY_MINUTE
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
%T
时间,24小时制(小时hh:
秒数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位数)
%%
‘%’文字字符
selectdate_format('
19830112'
%Y,%m,%d'
+------------------------------------+
|date_format('
|1983,01,12|
selectdayname(now());
+----------------+
|dayname(now())|
|Friday|
selectdatediff(now(),19830224);
+--------------------------+
|datediff(now(),19830224)|
|9549|
selectdate_add(now(),interval-2hour);
+----------------------------------+
|date_add(now(),interval-2hour)|
|2009-04-1708:
57:
47|
selectdate_add(now(),in