我的MYSQL学习心得.docx
- 文档编号:26689047
- 上传时间:2023-06-21
- 格式:DOCX
- 页数:55
- 大小:365.74KB
我的MYSQL学习心得.docx
《我的MYSQL学习心得.docx》由会员分享,可在线阅读,更多相关《我的MYSQL学习心得.docx(55页珍藏版)》请在冰豆网上搜索。
我的MYSQL学习心得
我的MYSQL学习心得
语法的差异
我这里主要说语法的不同
1、默认约束
区别:
mysql里面DEFAULT关键字后面是不用加括号的
--sqlserver
CREATETABLEemp
(
idINTDEFAULT(12)
)
--mysql
CREATETABLEemp
(
idINTDEFAULT12)
2、设置自增列
MYSQL的自增列一定要是有索引的列,设置种子值要在表的后面设置
--设置自增列--sqlserver
CREATETABLEemp
(
idINTIDENTITY(1,1)
)
--mysql--设置自增ID从N开始
CREATETABLEemp(
IDINTPRIMARYKEYAUTO_INCREMENT
)AUTO_INCREMENT=100;--(设置自增ID从100开始)
设置自增列的步长,可以分为全局级别和会话级别
如果是会话级别,那么当用户新建一个会话的时候,那么步长又回到了全局级别,所以mysql的步长跟sqlserver的步长有很大的不同
mysql不能设置为表级别的步长!
!
mysql服务器维护着2种mysql的系统参数(系统变量):
全局变量(globalvariables)和会话变量(sessionvariables)。
它们的含义与区别如其各占的名称所示,sessionvariables是在session级别的,对其的变更只会影响到本session;globalvariables是系统级别的,
对其的变更会影响所有新session(变更时已经存在session不受影响)至下次mysqlserver重启动。
注意它的变更影响不能跨重启,要想再mysqlserver重启时也使用新的值,那么就只有通过在命令行指定变量选项或者更改选项文件来指定,
而通过SET变更是达不到跨重启的。
每一个系统变量都有一个默认值,这个默认值是在编译mysql系统的时候确定的。
对系统变量的指定,一般可以在server启动的时候在命令行指定选项或者通过选项文件来指定
当然,大部分的系统变量,可以在系统的运行时,通过set命令指定其值。
查看系统当前默认的自增列种子值和步长值
SHOWGLOBALVARIABLESLIKE'auto_incre%';--全局变量
问:
如果有一张表,里面有个字段为id的自增主键,当已经向表里面插入了10条数据之后,删除了id为8,9,10的数据,再把mysql重启,
之后再插入一条数据,那么这条数据的id值应该是多少,是8,还是11?
答:
如果表的类型为MyISAM,那么是11。
如果表的类型为InnoDB,则id为8。
这是因为两种类型的存储引擎所存储的最大ID记录的方式不同,MyISAM表将最大的ID记录到了数据文件里,重启mysql自增主键的最大ID值也不会丢失;
而InnoDB则是把最大的ID值记录到了内存中,所以重启mysql或者对表进行了OPTIMIZE操作后,最大ID值将会丢失。
顺便说一下MYSQL获取当前表的自增值的四种方法
1、SELECTMAX(id)FROMperson针对特定表
2、SELECTLAST_INSERT_ID()函数针对任何表
3、SELECT@@identity针对任何表
@@identity是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值,是系统定义的全局变量。
一般系统定义的全局变量都是以@@开头,用户自定义变量以@开头。
使用@@identity的前提是在进行insert操作后,执行select@@identity的时候连接没有关闭,否则得到的将是NULL值。
4.SHOWTABLESTATUSLIKE'person'
如果针对特定表,建议使用这一种方法
得出的结果里边对应表名记录中有个Auto_increment字段,里边有下一个自增ID的数值就是当前该表的最大自增ID.
3、查看表定义
SQLSERVER
EXECsp_help'emp'
MYSQL
DESCemp
4、修改表名
修改表名也有差异,将表emp改为emp2
--sqlserver
EXECsys.[sp_rename]@objname=N'emp',--nvarchar(1035)
@newname='emp2'--sysname
--mysql
ALTERTABLEempRENAMEemp2
5、修改字段的数据类型
将id字段的int类型改为bigint
--sqlserver
ALTERTABLE[dbo].[emp2]ALTERCOLUMN[ID]BIGINT
--mysql
ALTERTABLEemp2MODIFYidBIGINT
6、修改字段名
MYSQL里修改字段名的时候需要加上字段的数据类型否则会报错,而CHANGE也可以只修改数据类型,实现和MODIFY同样的效果
方法是将SQL语句中的“新字段名”和“旧字段名”设置为相同的名称,只改变“数据类型”
改变数据类型,例如刚才那个例子,将id列改为bigint数据类型
ALTERTABLEemp2CHANGEididBIGINT
修改字段名
--sqlserver
EXECsys.[sp_rename]@objname=N'emp2.id',--nvarchar(1035)
@newname='iid',--sysname
@objtype='column'--varchar(13)
--mysql
ALTERTABLEemp2CHANGEidiidBIGINT
7、添加字段
添加字段的语法差不多,但是MYSQL里可以使用FIRST和AFTER关键字指定添加的字段的位置
--sqlserver
ALTERTABLE[dbo].[emp2]ADDNAMENVARCHAR(200)NULL
--mysql
ALTERTABLEemp2ADDNAMENVARCHAR(200)NULL
8、删除字段
MYSQL删除字段不需要添加COLUMN关键字的
--sqlserver
ALTERTABLE[dbo].[emp2]DROPCOLUMNNAME
--mysql
ALTERTABLEemp2DROPNAME
9、删除外键约束
MYSQL跟SQLSERVER删除约束的方法也有很大的区别
在SQLSERVER里面,无论是唯一约束,check约束还是外键约束都可以使用下面的SQL语句来删除掉
ALTERTABLE表名DROPCONSTRAINT约束名
但是MYSQL里面,如果是外键约束,需要使用DROPFOREIGNKEY,如果是主键约束需要使用DROPPRIMARYKEY,有点麻烦
--sqlserver
ALTERTABLEdbo.emp2DROPCONSTRAINTfk_emp_dept
--mysql--删除外键约束
ALTERTABLEemp2DROPFOREIGNKEYfk_emp_dept
--删除主键约束
ALTERTABLEemp2DROPPRIMARYKEYpk_emp_dept
10、删除表
删除表的语法两个都是一样的
--sqlserver
DROPTABLE[dbo].[emp2]
--mysql
DROPTABLEemp2
但是如果要同时删除多个表或者删除之前要先判断一下,MYSQL就方便多了
--sqlserver
IF(OBJECT_ID('dbo.emp2')ISNOTNULL)
DROPTABLE[dbo].[emp2]
--mysql
DROPTABLEIFEXISTSemp1,emp2
SQLSERVER需要一张一张表判断,然后一张一张表drop
MYSQL就不一样,语法非常简洁:
DROPTABLEIFEXISTSemp1,emp2
总结
这篇文章只是简单介绍了一下MYSQL跟SQLSERVER的语法方面的差异
以后会写更多关于MYSQL跟SQLERVER差异的文章,和我这段时间使用MYSQL期间的一些心得,大家敬请期待o(∩_∩)o
如有不对的地方,欢迎大家拍砖o(∩_∩)o
2014-7-16补充
USEtest;
--myisam引擎
CREATETABLETEST(
IDintunsignednotnullauto_increment,
namevarchar(10)notnull,
key(name,id))engine=MYISAMauto_increment=100;
--innodb引擎
CREATETABLETESTIdentity(
IDintunsignednotnullauto_increment,
NIDINTUNSIGNED,
namevarchar(10)notnull,
key(id))engine=INNODBauto_increment=100;
--或者主键
CREATETABLETESTIdentity(
IDintunsignednotnullauto_increment,
NIDINTUNSIGNED,
namevarchar(10)notnull,
key(id))engine=INNODBauto_increment=100;
[Database4]ErrorCode:
-2147467259,Number:
1075ErrorMessage:
Incorrecttabledefinition;therecanbeonlyoneautocolumnanditmustbedefinedasakey
altertableTESTIdentitymodifycolumnnidintauto_increment;
无论innodb引擎还是MYISAM引擎的表中,只能有一个自增列,并且自增列一定是索引列,无论是二级索引还是主键索引
这里跟SQLSERVER是不一样,SQLSERVER允许一张表有多个自增列,并且不需要在自增列上创建索引
显示宽度
MYSQL中的整数型数据类型都可以指定显示宽度,而SQLSERVER不行
创建一个表
CREATETABLEtb_emp(idBIGINT
(1))
id字段的数据类型为BIGINT
(1),注意到后面的数字1,这表示的是该数据类型指定的显示宽度,指定能够显示的数值中数字的个数。
例如,假设声明一个INT类型的字段YEARINT(4)
该声明指明,在year字段中的数据一般只显示4位数字的宽度。
在SQLSERVER里是会报错的
CREATETABLEabc
(idINT(11))
消息2716,级别16,状态1,第1行
第1个列、参数或变量:
不能对数据类型int指定列宽。
显示宽度和数据类型的取值范围是无关的。
显示宽度只是指明MYSQL最大可能显示的数字个数,数值的位数小于指定的宽度时会有空格填充
如果插入了大于显示宽度的值,只要该值不超过该类型整数的取值范围,数值依然可以插入,而且能显示出来。
例如,向year字段插入一个数值19999,当使用select查询的时候,MYSQL显示的将是完整带有5位数字的19999,而不是4位数字的值
如果不指定显示宽度,则MYSQL为每一种类型指定默认的宽度值
tips:
显示宽度只用于显示,并不能限制取值范围和占用空间,例如:
INT(3)会占用4个字节的存储空间,并且允许的最大值也不会是999,而是INT整型
所允许的最大值。
查看字段的长度
SQLSERVER
USE[sss]
GO
SELECTLEN([NAME])FROM[dbo].[aa]
MYSQL
CREATETABLEtmp13(vbVARBINARY(10))
INSERTINTOtmp13(vb)VALUES(12)
SELECTLENGTH(vb)FROMtmp13
INSERTINTOtmp13(vb)VALUES(1212)
MYSQL里的BLOB数据类型
BLOB是一个二进制大对象,用来存储可变数量的数据。
BLOB类型分为4种:
TinyBlob、Blob、MediumBlob、LongBlob,
这几个类型之间的唯一区别是在存储文件的最大大小上不同。
MySQL的四种BLOB类型类型大小(单位:
字节)
TinyBlob最大255
Blob最大65K
MediumBlob最大16M
LongBlob最大4G
BLOB列存储的是二进制字符串(字节字符串);TEXT列存储的是非二进制字符串(字符字符串)。
BLOB列没有字符集,并且排序和比较基于列值字节的数值;TEXT列有一个字符集,并且根据字符集对值进行排序和比较
BLOB是二进制字符串,TEXT是非二进制字符串,两者均可存放大容量的信息。
BLOB主要存储图片、音频信息等,
而TEXT只能存储文本文件。
SQLSERVER
SQLSERVER并没有BLOB数据类型,只有大型对象数据类型(BLOB):
text,ntext,image,nvarchar(max),varchar(max),varbinary(max)和xml数据类型
这些数据类型的数据都存放在LOB类型的数据页面里
如有不对的地方,欢迎大家拍砖o(∩_∩)o
其他类型
第11章:
列类型
目录
11.1.列类型概述
11.1.1.数值类型概述
11.1.2.日期和时间类型概述
11.1.3.字符串类型概述
11.2.数值类型
11.3.日期和时间类型
11.3.1.DATETIME、DATE和TIMESTAMP类型
11.3.2.TIME类型
11.3.3.YEAR类型
11.3.4.Y2K事宜和日期类型
11.4.String类型
11.4.1.CHAR和VARCHAR类型
11.4.2.BINARY和VARBINARY类型
11.4.3.BLOB和TEXT类型
11.4.4.ENUM类型
11.4.5.SET类型
11.5.列类型存储需求
11.6.选择正确的列类型
11.7.使用来自其他数据库引擎的列类型
MySQL支持多种列类型:
数值类型、日期/时间类型和字符串(字符)类型。
本章首先对这些列类型进行了概述,然后更加详细地描述了各种列的类型,以及列类型存储需求的总结。
概述很简单。
关于具体列类型的详细信息应查阅详细的描述,例如指定值时允许使用的格式。
MySQL支持处理空间数据的扩展名。
关于空间类型的信息参见第19章:
MySQL中的空间扩展。
几种列类型描述使用了下述惯例:
·M
表示最大显示宽度。
最大有效显示宽度是255。
·D
适用于浮点和定点类型,并表示小数点后面的位数。
最大可能的值是30,但不应大于M-2。
·方括号(‘[’和‘]’)表示可选部分。
11.1.列类型概述
11.1.1.数值类型概述
11.1.2.日期和时间类型概述
11.1.3.字符串类型概述
11.1.1.数值类型概述
下面为数值列类型的概述。
详细信息参见11.2节,“数值类型”。
列存储需求参见11.5节,“列类型存储需求”。
M指示最大显示宽度。
最大有效显示宽度是255。
显示宽度与存储大小或类型包含的值的范围无关,相关描述见11.2节,“数值类型”。
如果为一个数值列指定ZEROFILL,MySQL自动为该列添加UNSIGNED属性。
SERIAL是BIGINTUNSIGNEDNOTNULLAUTO_INCREMENTUNIQUE的一个别名。
在整数列定义中,SERIALDEFAULTVALUE是NOTNULLAUTO_INCREMENTUNIQUE的一个别名。
警告:
应当清楚,当使用在整数值(其中一个是UNSIGNED类型)之间使用减号时,结果是无符号。
参见12.8节,“Cast函数和操作符”。
·BIT[(M)]
位字段类型。
M表示每个值的位数,范围为从1到64。
如果M被省略,默认为1。
·TINYINT[(M)][UNSIGNED][ZEROFILL]
很小的整数。
带符号的范围是-128到127。
无符号的范围是0到255。
·BOOL,BOOLEAN
是TINYINT
(1)的同义词。
zero值被视为假。
非zero值视为真。
在将来,将根据标准SQL引入完全布尔类型的处理。
·SMALLINT[(M)][UNSIGNED][ZEROFILL]
小的整数。
带符号的范围是-32768到32767。
无符号的范围是0到65535。
·MEDIUMINT[(M)][UNSIGNED][ZEROFILL]
中等大小的整数。
带符号的范围是-8388608到8388607。
无符号的范围是0到16777215。
·INT[(M)][UNSIGNED][ZEROFILL]
普通大小的整数。
带符号的范围是-2147483648到2147483647。
无符号的范围是0到4294967295。
·INTEGER[(M)][UNSIGNED][ZEROFILL]
这是INT的同义词。
·BIGINT[(M)][UNSIGNED][ZEROFILL]
大整数。
带符号的范围是-9223372036854775808到9223372036854775807。
无符号的范围是0到184********709551615。
应清楚BIGINT列的下述内容:
o使用带符号的BIGINT或DOUBLE值进行所有算法,因此除了位函数,不应使用大于9223372036854775807(63位)的无符号的大整数!
如果这样做,结果中的最后几位可能出错,这是由于将BIGINT值转换为DOUBLE进行四舍五入时造成的错误。
MySQL可以在以下情况下处理BIGINT:
§当使用整数在一个BIGINT列保存大的无符号的值时。
§在MIN(col_name)或MAX(col_name)中,其中col_name指BIGINT列。
§使用操作符(+,-,*等等)并且两个操作数均为整数时。
o总是可以使用一个字符串在BIGINT列中保存严格整数值。
在这种情况下,MySQL执行字符串-数字转换,其间不存在双精度表示。
o当两个操作数均为整数值时,-、+和*操作符使用BIGINT算法。
这说明如果乘两个大整数(或来自返回整数的函数),当结果大于9223372036854775807时,会得到意想不到的结果。
·FLOAT[(M,D)][UNSIGNED][ZEROFILL]
小(单精度)浮点数。
允许的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38。
这些是理论限制,基于IEEE标准。
实际的范围根据硬件或操作系统的不同可能稍微小些。
M是小数纵位数,D是小数点后面的位数。
如果M和D被省略,根据硬件允许的限制来保存值。
单精度浮点数精确到大约7位小数位。
如果指定UNSIGNED,不允许负值。
使用浮点数可能会遇到意想不到的问题,因为在MySQL中的所有计算用双精度完成。
参见A.5.7节,“解决与不匹配行有关的问题”。
·DOUBLE[(M,D)][UNSIGNED][ZEROFILL]
普通大小(双精度)浮点数。
允许的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到1.7976931348623157E+308。
这些是理论限制,基于IEEE标准。
实际的范围根据硬件或操作系统的不同可能稍微小些。
M是小数总位数,D是小数点后面的位数。
如果M和D被省略,根据硬件允许的限制来保存值。
双精度浮点数精确到大约15位小数位。
如果指定UNSIGNED,不允许负值。
·DOUBLEPRECISION[(M,D)][UNSIGNED][ZEROFILL],REAL[(M,D)][UNSIGNED][ZEROFILL]
为DOUBLE的同义词。
除了:
如果SQL服务器模式包括REAL_AS_FLOAT选项,REAL是FLOAT的同义词而不是DOUBLE的同义词。
·FLOAT(p)[UNSIGNED][ZEROFILL]
浮点数。
p表示精度(以位数表示),但MySQL只使用该值来确定是否结果列的数据类型为FLOAT或DOUBLE。
如果p为从0到24,数据类型变为没有M或D值的FLOAT。
如果p为从25到53,数据类型变为没有M或D值的DOUBLE。
结果列范围与本节前面描述的单精度FLOAT或双精度DOUBLE数据类型相同。
FLOAT(p)语法与ODBC兼容。
·DECIMAL[(M[,D])][UNSIGNED][ZEROFILL]
压缩的“严格”定点数。
M是小数位数(精度)的总数,D是小数点(标度)后面的位数。
小数点和(负数)的‘-’符号不包括在M中。
如果D是0,则值没有小数点或分数部分。
DECIMAL整数最大位数(M)为65。
支持的十进制数的最大位数(D)是30。
如果D被省略,默认是0。
如果M被省略,默认是10。
如果指定UNSIGNED,不允许负值。
所有DECIMAL列的基本计算(+,-,*,/)用65位精度完成。
·DEC[(M[,D])][UNSIGNED][ZEROFILL],NUMERIC[(M[,D])][UNSIGNED][ZEROFILL],FIXED[(M[,D])][UNSIGNED][ZEROFILL]
是DECIMAL的同义词。
FIXED同义词适用于与其它服务器的兼容性。
11.1.2.日期和时间类型概述
本节综合讨论了临时列类型。
详细信息,参见11.3节,“日期和时间类型”。
列存储需求参见11.5节,“列类型存储需求”。
·DATE
日期。
支持的范围为'1000-01-01'到'9999-12-31'。
MySQL以'YYYY-MM-DD'格式显示DATE值,但允许使用字符串或数字为DATE列分配值。
·DATETIME
日期和时间的组合。
支持的范围是'1000-01-0100:
00:
00'到'9999-12-3123:
59:
59'。
MySQL以'YYYY-MM-DDHH:
MM:
SS'格式显示DATETIME值,但允许使用字符串或数字为DATETIME列分配值。
·TIMESTAM
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MYSQL 学习心得
![提示](https://static.bdocx.com/images/bang_tan.gif)