仓酷云

标题: MSSQL网页编程之MYSQL创立、删除、索引和变动表 [打印本页]

作者: 活着的死人 时间: 2015-1-16 22:20
标题: MSSQL网页编程之MYSQL创立、删除、索引和变动表
操作被同步到从库上后，则主从都“回天无力”。3.4创立、删除、索引和变动表
可使用CREATETABLE、DROPTABLE和ALTERTABLE语句创立表，然后，对它们举行删除，变动它们的布局。关于它们中的每条语句，存在MySQL专有的扩大，这些扩大使各语句更加有效。CREATEINDEX和DROPINDEX语句使您可以增添或删除现有表上
的索引。
3.4.1CREATETABLE语句
用CREATETABLE语句创立表。此语句的完全语法是相称庞大的，由于存在那末多的可选子句，但在实践中此语句的使用相称复杂。如我们在第1章中利用的一切CREATETABLE语句都不那末庞大。成心思的是，年夜多半庞大器材都是一些子句，这些子句MySQL在剖析后抛弃。参阅附录D可看到这些庞大的器材。看看CREATETABLE语句的各项条目，注重该语句有几语法是用于REFERENCESCONSTRAINT和CHECK子句的。这些子句触及内部键、援用完全性及输出值束缚。MySQL不撑持这些功效，但它剖析其语法使其更简单使用在其他数据库体系中创建的表界说。（能够用较少的编纂事情更简单天时用该代码。）假如您重新入手下手编写本人的表形貌，能够完整不论这些子句。本节中我们对它们也未几做先容。CREATETABLE最少应当指出表名和表中列的清单。比方：

除组成表的列之外，在创立表时还能够申明它应当如何索引。另外一个选择是创立表时不举行索引，今后再增添索引。假如企图在入手下手将表用于查询前，用大批的数据添补此表，今后再创立索引是一个好举措。在拔出每行时更新索引较装载数据到一个未索引的表中然后
再创立索引要慢很多。我们已在第1章中先容了CREATETABLE语句的基础语法，并在第2章会商了如何形貌列范例。这里假定您已读过了这两章，因而我们就不反复这些内容了。在本节上面，我们将先容一些MySQL3.23中对CREATETABLE语句的主要扩大，这些扩大在机关表方面供应了很年夜的天真性，这些扩大为：
■表存储范例申明符。
■仅当表不存在时才举行创立。
■在客户时机话停止时主动删除一时表。
■经由过程选择但愿表存储的数据来创立一个表。
1.表存储范例申明符
在MySQL3.23之前，一切用户创立的表都使用的是ISAM存储办法。在MySQL3.23中，可在CREATETABLE语句的列的列表以后指定TYPE=type，以三品种型明白地创立表。个中type能够为MYISAM、ISAM或HEAP。比方：

将表转换为HEAP范例大概不是一个好主张，可是，假如但愿表一向保持到服务器封闭，能够举行这个转换。HEAP表在服务器加入之前，一向保存在内存中。这三种表范例的一样平常特性以下：
■MyISAM表。MyISAM存储格局自版本3.23以来是MySQL中的缺省范例，它有以下特性：
■假如操纵体系本身同意更年夜的文件，那末文件比ISAM存储办法的年夜。
■数据以低字节优先的呆板自力格局存储。这暗示可将表从一种呆板拷贝到另外一种呆板，即便它们的系统布局分歧也能够拷贝。
■数值索引值占的存储空间较少，由于它们是按高字节优先存储的。索引值在低位字节中变更很快，因而高位字节更简单对照。
■AUTO_INCREMENT处置比ISAM的表更好。具体内容在第2章会商。
■削减了几个索引限定。比方，可对含NULL值的列举行索引，还能够对BLOB和TEXT范例的列举行索引。
■为了改良表的完全性反省，每一个表都具有一个标记，在myisamchk对表举行过反省后，设置该标记。可使用myisamchk-fast跳过对自上次反省以来还没有被修正过表的反省，如许使此办理义务更快。表中另有一个唆使表是不是一般封闭的标记。假如服务器封闭不一般，或呆板溃散，此标记可用来检测出服务器起动时必要反省的表。
■ISAM表。ISAM存储格局是MySQL3.23所用的最旧的格局，但以后仍旧可用。一般，相对ISAM表来讲，宁肯利用MyISAM表，由于它们的限定较少。对ISAM表的撑持跟着此存储格局被MyISAM表格局所撑持很有大概会渐渐消散。
■HEAP表。HEAP存储格局创建使用定长行的内存中的表，这使表运转得十分快。在服务器中断时，它们将会消散。在这类意义上，这些表是一时的。可是，与用CREATETEMPORARYTABLE所创立的一时表比拟，HEAP表是其他客户机可见的。HEAP表有几个限定，这些限定对MyISAM或ISAM表没有，以下所示：
■索引仅用于“=”和“<=>”对照。
■索引列中不克不及有NULL值。
■不克不及利用BLOB和TEXT列。
■不克不及利用AUTO_INCREMENT列。
2.创立不存在的表
要创立一个不存在的表，利用CREATETABLEIFNOTEXISTS便可。在某种使用程序中，没法断定要用的表是不是已存在，因而，要创立这类表。IFNOTEXISTS润色符关于作为用mysql运转的批量功课的剧本极其有效。在这里，一般的CREATETABLE语句事情得
不是很好。由于功课第一次运转时，创建这些表，假如这些表已存在，则第二次运转时将堕落。假如用IFNOTEXISTS语句，就不会有成绩。每次运转功课时，像后面一样创立表。假如这些表已存在，在第二次运转时，创立表失利，但不堕落。这使得功课能够持续运转，就像创立表的妄图已乐成了一样。
3.一时表
可用CREATETEMPORARYTABLE来创立一时表，这些表在会话停止时会主动消散。利用一时表很便利，由于不用操心公布DROPTABLE语句明白地删除这些表，并且假如您的会话不一般停止，这些表不会滞留。比方，假如某个文件中有一个用mysql运转的查询，您决意不比及其停止，那末能够在其实行的半途中断这个查询，并且毫无成绩，服务器将删除所创立的恣意一时表。在旧版的MySQL中，没有真实的一时表，除您在本人的思想中以为它们是一时的除外。关于必要如许的表的使用程序，必需本人记着删除这些表。假如忘了删除，或在后面使其存在的客户机中呈现毛病时，这些表在有人注重到并删除它们之前会一向存在。一时表仅对创立该表的客户机可见。其称号可与一个现有的永世表不异。这不是毛病，也不会使已有的永世表出成绩。假设在samp_db数据库中创立了一个名为member的一时表。本来的member表酿成埋没的（不成会见），对member的援用将援用一时表。假如公布一条DROPTABLEmember语句，这个一时表将被删除，而本来的member表“从头呈现”。假如您复杂地中止与服务器的毗连而没有删除一时表，服务器会主动地删除它。下一次毗连时，
本来的member表再次可见。称号埋没机制仅在一个级别上起感化。即，不克不及创立两个具有统一个称号的一时表。
4.使用SELECT的了局创立表
干系数据库的一个主要观点是，任何数据都暗示为行和列构成的表，而每条SELECT语句的了局也都是一个行和列构成的表。在很多情形下，来自SELECT的“表”仅是一个跟着您的事情在显现屏上转动的行和列的图象。在MySQL3.23之前，假如想将SELECT的了局保留在一个表中以便今后的查询利用，必需举行特别的布置：
1)运转DESCRIBE或SHOWCOLUMNS查询以断定想从中猎取信息的表中的列范例。
2)创立一个表，明白地指定方才检察到的列的称号和范例。
3)在创立了该表后，公布一条INSERT...SELECT查询，检索出了局并将它们拔出所创立的表中。
在MySQL3.23中，全都作了修改。CREATETABLE...SELECT语句打消了这些华侈工夫的器材，使得能使用SELECT查询的了局间接得出一个新表。只需一步就能够完成义务，不用晓得或指定所检索的列的数据范例。这使得很简单创立一个完整用所喜好的数据添补的表，而且为进一步查询作了筹办。能够经由过程选择一个表的全体内容（无WHERE子句）来拷贝一个表，或使用一个老是掉
败的WHERE子句来创立一个空表，如：

假如但愿使用LOADDATA将一个数据文件装进本来的文件中，而不敢一定是不是具有指定的准确数据格局时，创立空拷贝很有效。您其实不但愿在第一次未失掉准确的选项时以本来表中畸形的纪录而了结。使用原表的空拷贝同意对特定的列和行分开符用LOADDATA的选项举行实验，直到对输出数据的注释中意时为止。在中意以后，就能够将数据装进原表了。可分离利用CREATETEMPORARYTABLE与SELECT来创立一个一时表作为它本身的拷贝，如：

这同意修正my_tbl的内容而不影响本来的内容。在但愿实验对某些修正表内容的查询，而又不想变动原表内容时，如许做很有效。为了利用使用原表名的事后编写的剧本，不必要为援用分歧的表而编纂这些剧本；只需在剧本的肇端处增添CREATETEMPORARYTABLE语句便可。响应的剧本将创立一个一时拷贝，并对此拷贝举行操纵，当剧本停止时服务器会主动删除这个拷贝。
要创立一个作为本身的空拷贝的表，能够与CREATETEMPORARY...SELECT一同利用WHERE0子句，比方：

但创立空表时有几点要注重。在创立一个经由过程选择数据添补的表时，其列名来自所选择的列名。假如某个列作为表达式的了局盘算，则该列的“称号”为表达式的文本。表达式不是正当的列名，可在mysql中运转以下查询懂得这一点：

假如选择了来自分歧表的具有不异称号的列，将会呈现必定的坚苦。假定表t1和t2二者都具有列c，而您但愿创立一个来自两个表中行的一切组合的表。那末能够供应别号指定新表中唯一性的列名，如：

经由过程选择数据举行添补来创立一个表并会主动拷贝原表的索引。
3.4.2DROPTABLE语句
删除表比创立表要简单很多，由于不必要指定有关其内容的任何器材；只需指定其称号便可，如：
DROPTABLEtb1_name
MySQL对DROPTABLE语句在某些有效的方面做了扩大。起首，可在统一语句中指定几个表对它们举行删除，如：
DROPTABLEtb1_name1,tb1_name2,......
其次，假如不克不及一定一个表是不是存在，但但愿假如它存在就删除它。那末可在此语句中增添IFEXISTS。如许，假如DROPTABLE语句中给出的表不存在，MySQL不会收回毛病信息。如：
DROPTABLEIFEXISTStb1_name
IFEXISTS在mysql所用的剧本中很有效，由于缺省情形下，mysql将在堕落时加入。比方，有一个安装剧本可以创立表，这些表将在其他剧本中持续利用。在此情况下，但愿包管此创立表的剧本在入手下手运转时无后顾之忧。假如在该剧本入手下手处利用一般的DROPTABLE，那末它在第一次运转时将会失利，由于这些表从未创立过。假如利用IFEXISTS，就不会发生成绩了。当表已存在时，将它们删除；假如不存在，剧本持续运转。
3.4.3创立和删除索引
索引是减速表内容会见的次要手腕，出格对触及多个表的毗连的查询更是云云。这是第4章“查询优化”中的一个主要内容，第4章会商了为何必要索引，索引怎样事情和如何使用它们来优化查询。本节中，我们将先容索引的特性，和创立和删除索引的语法。
1.索引的特性
MySQL对机关索引供应了很年夜的天真性。可对单列或多列的组合举行索引。假如但愿可以从一个表的分歧列中找出一个值，还能够在一个表上机关不止一个索引。假如某列为串范例而非ENUM或SET范例，能够选择只对该列最右边的n个字符举行索引。假如该列的前n个字符最具有独一性，如许做一样平常不会就义功能，并且还会对功能有年夜的改良：用索引列的前缀而非全部列可以使索引更小且会见更快。固然跟着MySQL的进一步开辟创立索引的束缚将会愈来愈少，但如今仍是存在一些束缚的。上面的表依据索引的特征，给出了ISAM表和MyISAM表之间的不同：

今后表中能够看到，关于ISAM表来讲，其索引列必需界说为NOTNULL，而且不克不及对BLOB和TEXT列举行索引。MyISAM表范例往失落了这些限定，并且减缓了其他的一些限定。两种表范例的索引特征的差别标明，依据所利用的MySQL版本的分歧，有大概对某些列不克不及举行索引。比方，假如利用3.23版之前的版本，则不克不及对包括NULL值的列举行索引。
假如利用的是MySQL3.23版或更新的版本，但表是已往以ISAM表创立的，可使用ALTERTABLE很便利地将它们转换为MyISAM存储格局，如许使您能使用某些较新的索引功效，如：
ALTERTABLEtb1_nameTYPE=MYISAM
2.创立索引
在实行CREATETABLE语句时，可为新表创立索引，也能够用CREATEINDEX或ALTERTABLE来为一个已有的表增添索引。CREATEINDEX是在MySQL3.23版中引进的，但假如利用3.23版之前的版本，可使用ALTERTABLE语句创立索引（MySQL一般在外部将CREATEINDEX映照到ALTERTABLE）。能够划定索引可否包括反复的值。假如不包括，则索引应当创立为PRIMARYKEY或UNIQUE索引。关于单列唯一索引，这包管了列不包括反复的值。关于多列唯一索引，它包管值的组合不反复。
PRIMARYKEY索引和UNIQUE索引十分相似。现实上，PRIMARYKEY索引仅是一个具着名称PRIMARY的UNIQUE索引。这暗示一个表只能包括一个PRIMARYKEY，由于一个表中不成能具有两个同名的索引。统一个表中可有多个UNIQUE索引，固然如许做意义不
年夜。
为了给现有的表增添一个索引，可以使用ALTERTABLE或CREATEINDEX语句。ALTERTABLE最经常使用，由于可用它来创立一般索引、UNIQUE索引或PRIMARYKEY索引，如：

个中tbl_name是要增添索引的表名，而column_list指出对哪些列举行索引。假如索引由不止一列构成，各列名之间用逗号分开。索引名index_name是可选的，因而能够不写它，MySQL将依据第一个索引列赋给它一个称号。ALTERTABLE同意在单个语句中指定多个表的变动，因而能够在同时创立多个索引。
CREATEINDEX可对表增添一般索引或UNIQUE索引，如：

tbl_name、index_name和column_list具有与ALTERTABLE语句中不异的寄义。这里索引名不成选。不克不及用CREATEINDEX语句创立PRIMARYKEY索引。要想在公布CREATETABLE语句时为新表创立索引，所利用的语法相似于ALTERTABLE语句的语法，可是应当在您界说表列的语句部分指定索引创立子句，以下所示：

与ALTERTABLE一样，索引名关于INDEX和UNIQUE都是可选的，假如未给出，MySQL将为其选一个。有一种特别情况：可在列界说以后增添PRIMARYKEY创立一个单列的PRIMARYKEY索引，以下所示：

后面一切表创立样例都对索引列指定了NOTNULL。假如是ISAM表，这是必需的，由于不克不及对大概包括NULL值的列举行索引。假如是MyISAM表，索引列能够为NULL，只需该索引不是PRIMARYKEY索引便可。
假如对某个串列的前缀举行索引（列值的最右边n个字符），使用column_list申明符暗示该列的语法为col_name(n)而不必col_name。比方，上面第一条语句创立了一个具有两个CHAR列的表和一个由这两列构成的索引。第二条语句相似，但只对每一个列的前缀举行索引：

在某些情形下，大概会发明必需对列的前缀举行索引。比方，索引行的长度有一个最年夜下限，因而，假如索引列的长度凌驾了这个下限，那末便可能必要使用前缀举行索引。在MyISAM表索引中，对BLOB或TEXT列也必要前缀索引。对一个列的前缀举行索引限定了今后对该列的变动；不克不及在不删除该索引并利用较短前缀的情形下，将该列延长为一个长度小于索引所用前缀的长度的列。
3.删除索引
可使用DROPINDEX或ALTERTABLE语句来删除索引。相似于CREATEINDEX语句，DROPINDEX一般在外部作为一条ALTERTABLE语句处置，而且DROPINDEX是在MySQL3.22中引进的。删除索引语句的语法以下：

前两条语句是等价的。第三条语句只在删除PRIMARYKEY索引时利用；在此情况中，不必要索引名，由于一个表只大概具有一个如许的索引。假如没有明白地创立作为PRIMARYKEY的索引，但该表具有一个或多个UNIQUE索引，则MySQL将删除这些UNIQUE索引中的第一个。
假如从表中删除列，则索引大概会遭到影响。假如所删除的列为索引的构成部分，则该列也会从索引中删除。假如构成索引的一切列都被删除，则全部索引将被删除。
3.4.4ALTERTABLE语句
ALTERTABLE语句是MySQL中一条通用的语句，可用它来做很多事变。我们已看过了它的几种功效（创立和删除索引和将表从一种存储格局转换为另外一种存储格局）。本节中，我们将先容它的一些其他功效。ALTERTABLE的完全语法在附录D中先容。
在发明某个表的布局不再反应所但愿的器材时，ALTERTABLE很有效处。大概但愿用该表纪录其他信息，大概它含有过剩的值。大概有的列太小，大概其界说较实践必要来讲太年夜，必要将它们改小以节俭存储空间。大概公布CREATETABLE语句时给出的表名不合错误。等等，诸云云类的成绩，都能够用ALTERTABLE语句来办理。上面是一些例子：
■您正利用一个基于Web的问卷，将每份提交的问卷作为表中的一个纪录。厥后决意修正此问卷，增添一些成绩。这时候必需对表增添一些列以寄存新成绩。
■您正在办理一个研讨项目。用AUTO_INCREMENT列分派案例号来研讨纪录。您不但愿经费延期太长发生多于50000个以上的纪录，因而，令该列的范例为UNSIGNEDSMALLINT，它能存储的最年夜唯一值为65535。可是，项目标经费延伸了，仿佛大概别的发生50000个纪录。这时候，必要使该列的范例更年夜一些以便存储更多的件号。
■巨细的变动也多是反偏向的。大概创立了一个CHAR(255)列，但如今发明表中没有比100个字符更长的串。这时候可延长该列以节俭存储空间。ALTERTABLE的语法以下：

每一个action暗示对表所做的一个修正。MySQL扩大了ALTERTABLE语句，同意指定多个举措，各举措间以逗号分开。这关于削减键盘输出很有效，但这个扩大的更加主要的缘故原由是，除非能同时将一切VARCHAR列变动为CHAR列，不然不成能将表从行可变长的表变动为行定长的表。
上面的例子示出了某些ALTERTABLE的功效。
■对表从头定名。这很复杂；只需给出旧表名和新表名便可：

在MySQL3.23中有一时表，重定名一个一时表为数据库中已存在的称号将埋没原始表，只需一时表存在就会埋没原始表。这相似于经由过程用不异的名字创立一个一时表来埋没一个表的办法。
■变动列范例。为了变动列的范例，可以使用CHANGE或MODIFY子句。假设表my_tbl中的列为SMALLINTUNSIGNED的，但愿将其变动为MEDIUMINTUNSIGNED的列。用上面的任何一个命令都可完成此项事情：

为何在CHANGE命令中给出列名两次？由于CHANGE能够做的而MODIFY不克不及做的一桩事是，除变动范例外还能变动列名。假如但愿在变动范例的同时从头将i定名为j，可按以下举行：

主要的是定名了但愿变动的列，并申明了一个包含列名的列的完全界说。即便不变动列名，也必要在界说中包含响应的列名。
变动列范例的一个主要缘故原由是为了改良对照两个表的毗连查询的效力。在两个列的范例不异时，对照更快。假设实行以下的查询：

假如t1.name为CHAR(10)，而t2.name为CHAR(15)，此查询的运转速率没有它们二者都为CHAR(15)时的快。那末能够用上面的任一条命令变动t1.name使它们的范例不异：

关于3.23之前的MySQL版本，所毗连的列必需是一样范例的这一点很主要，不然索引不克不及用于对照。关于版本3.23或以上的版本，索引可用于分歧的范例，但假如范例不异，查询仍旧更快。
■将表从可变长行转换为定长行。假设有一个表chartbl具有VARCHAR列，想要把它转换为CHAR列，看看可以失掉甚么样的功能改良。（定长行的表一样平常比变长行的表处置更快。）这个表以下创立：

这里的成绩是必要在不异的ALTERTABLE语句中一次变动一切的列。不成能一次一列地改完，大概说这个妄图将不起感化。假如实行DESCRIBEchartbl，会发明两个列仍旧是VARCHAR的列！缘故原由是假如每次变动一列，MySQL注重到表仍旧包括有可变长的列，则会把已变动过的列从头转换为VARCHAR以节俭空间。为了处置这个成绩，应当同时变动一切VARCHAR列：

如今DESCRIBE将显现该表包括的都是CHAR列。的确，这类范例的操纵很主要，由于它使ALTERTABLE能在不异的语句中撑持多个举措。
这里要注重，在但愿转换如许的表时：假如表中存在BLOB或TEXT列将使转换表为定长行格局的妄图失利。即便表中只要一个可变长的列都将会使表有可变长的行，由于这些可变长的列范例没有定长的等价物。
■将表从定长行转换为可变长的行。固然，chartbl用定长行更快，但它要占用更多的空间，因而决意将它转换回本来的情势以节俭空间。这类转换更加简单。只需将某个CHAR列转换为VARCHAR列，MySQL就主动地转换其他的CHAR列。要想转换chartbl表，用以下任一条语句都能够：

■转换表的范例。假如从MySQL3.23版之前的版本晋级到3.23版或更高，那末大概会有一些本来创立为ISAM表的旧表。假如但愿使它们为MyISAM格局，以下操纵：

为何要如许做呢？正如在“创立和删除索引”大节中所先容的那样，一个缘故原由是MyISAM存储格局具有某些ISAM格局没有的索引特征，比方可以对NULL值、BLOB和TEXT列范例举行索引。另外一个缘故原由为，MyISAM表是自力于呆板的，因而可经由过程将它们间接拷贝来将它们移到其他呆板上，即便那些呆板具有分歧的硬件系统布局也一样。这在第11章中将要作进一步的先容。

不可，这不是我想要找的，我要再搜一搜
MySQL的海豚标志的名字叫“sakila”，它是由MySQLAB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者AmbroseTwebaze提供。

作者: 变相怪杰 时间: 2015-1-19 09:05
多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

作者: 第二个灵魂 时间: 2015-1-24 15:43
SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）

作者: 深爱那片海 时间: 2015-2-2 05:19
还不是性能有问题！否则面向对象的数据库早就实现了！建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。

作者: 灵魂腐蚀 时间: 2015-2-7 16:51
可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

作者: 只想知道 时间: 2015-2-22 16:36
不过话说回来了，绝大多数的性能优化准则与对sqlserver存储的结构理解息息相关

作者: admin 时间: 2015-3-7 01:33
还不是性能有问题！否则面向对象的数据库早就实现了！建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。

作者: 兰色精灵 时间: 2015-3-14 05:32
sqlserver的痛苦之处在于有用文档的匮乏，很多只是表明的东西

作者: 飘飘悠悠 时间: 2015-3-21 00:51
还不是性能有问题！否则面向对象的数据库早就实现了！建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。

欢迎光临仓酷云 (http://ckuyun.com/)