仓酷云

标题: MSSQL教程之CREATE TABLE C SQL Command [打印本页]

作者: 深爱那片海 时间: 2015-1-16 22:27
标题: MSSQL教程之CREATE TABLE C SQL Command
先说DDL的分类。有一类DDL，是不需要重建表的，比如加非聚簇索引。这类操作其实不会丢数据，也是在原表上直接操作，对于我们“以恢复数据为目的”的闪回，是可以先忽略的。另外一类，则是会影响到表数据的操作。commandCREATETABLECSQLCommand
Createsatablehavingthespecifiedfields.

CREATETABLE|DBFTableName1[NAMELongTableName][FREE](FieldName1FieldType[(nFieldWidth[,nPrecision])][NULL|NOTNULL][CHECKlExpression1[ERRORcMessageText1]][DEFAULTeExpression1][PRIMARYKEY|UNIQUE][REFERENCESTableName2[TAGTagName1]][NOCPTRANS][,FieldName2...][,PRIMARYKEYeExpression2TAGTagName2|,UNIQUEeExpression3TAGTagName3][,FOREIGNKEYeExpression4TAGTagName4[NODUP]REFERENCESTableName3[TAGTagName5]][,CHECKlExpression2[ERRORcMessageText2]])|FROMARRAYArrayName
ParametersTableName1Specifiesthenameofthetabletocreate.TheTABLEandDBFoptionsareidentical.NAMELongTableNameSpecifiesalongnameforthetable.Alongtablenamecanbespecifiedonlywhenadatabaseisopenbecauselongtablenamesarestoredindatabases.
Longnamescancontainupto128charactersandcanbeusedinplaceofshortfilenamesinthedatabase.
FREESpecifiesthatthetablewillnotbeaddedtoanopendatabase.FREEisntrequiredifadatabaseisntopen.(FieldName1FieldType[(nFieldWidth[,nPrecision])]Specifiesthefieldname,fieldtype,fieldwidth,andfieldprecision(numberofdecimalplaces),respectively.
Asingletablecancontainupto255fields.Ifoneormorefieldsallownullvalues,thelimitisreducedbyoneto254fields.
FieldTypeisasingleletterindicatingthefieldsdatatype.SomefielddatatypesrequirethatyouspecifynFieldWidthornPrecision,orboth.
ThefollowingtableliststhevaluesforFieldTypeandwhethernFieldWidthandnPrecisionarerequired.
FieldTypenFieldWidthnPrecisionDescriptionCnCCharacterfieldofwidthnDCCDateTCCDateTimeNndNumericfieldofwidthnwithddecimalplacesFndFloatingnumericfieldofwidthnwithddecimalplacesICCIntegerBCdDoubleYCCCurrencyLCCLogicalMCCMemoGCCGeneral
nFieldWidthandnPrecisionareignoredforD,T,I,Y,L,M,G,andPtypes.nPrecisiondefaultstozero(nodecimalplaces)ifnPrecisionisntincludedfortheNorFtypes.nPrecisiondefaultstothenumberofdecimalplacesspecifiedbytheSETDECIMALsettingifnPrecisionisntincludedfortheBtype.
NULLAllowsnullvaluesinthefield.Ifoneormorefieldscancontainnullvalues,themaximumnumberoffieldsthetablecancontainisreducedbyone,from255to254.NOTNULLPreventsnullvaluesinthefield.
IfyouomitNULLandNOTNULL,thecurrentsettingofSETNULLdeterminesifnullvaluesareallowedinthefield.However,ifyouomitNULLandNOTNULLandincludethePRIMARYKEYorUNIQUEclause,thecurrentsettingofSETNULLisignoredandthefielddefaultstoNOTNULL.
CHECKlExpression1Specifiesavalidationruleforthefield.lExpression1canbeauser-definedfunction.Notethatwhenablankrecordisappended,thevalidationruleischecked.Anerrorisgeneratedifthevalidationruledoesntallowforablankfieldvalueinanappendedrecord.ERRORcMessageText1SpecifiestheerrormessageVisualFoxProdisplayswhenthevalidationrulespecifiedwithCHECKgeneratesanerror.ThemessageisdisplayedonlywhendataischangedwithinaBrowsewindoworEditwindow.DEFAULTeExpression1Specifiesadefaultvalueforthefield.ThedatatypeofeExpression1mustbethesameasthefieldsdatatype.PRIMARYKEYCreatesaprimaryindexforthefield.Theprimaryindextaghasthesamenameasthefield.UNIQUECreatesacandidateindexforthefield.Thecandidateindextaghasthesamenameasthefield.Formoreinformationaboutcandidateindexes,seeSettingaPrimaryorCandidateIndex.NoteCandidateindexes(createdbyincludingtheUNIQUEoptioninCREATETABLEorALTERTABLECSQL)arenotthesameasindexescreatedwiththeUNIQUEoptionintheINDEXcommand.AnindexcreatedwiththeUNIQUEoptionintheINDEXcommandallowsduplicateindexkeys;candidateindexesdonotallowduplicateindexkeys.SeeINDEXforadditionalinformationonitsUNIQUEoption.
Nullvaluesandduplicaterecordsarenotpermittedinafieldusedforaprimaryorcandidateindex.However,VisualFoxProwillnotgenerateanerrorifyoucreateaprimaryorcandidateindexforafieldthatsupportsnullvalues.VisualFoxProwillgenerateanerrorifyouattempttoenteranullorduplicatevalueintoafieldusedforaprimaryorcandidateindex.
REFERENCESTableName2[TAGTagName1]Specifiestheparenttabletowhichapersistentrelationshipisestablished.IfyouomitTAGTagName1,therelationshipisestablishedusingtheprimaryindexkeyoftheparenttable.Iftheparenttabledoesnothaveaprimaryindex,VisualFoxProgeneratesanerror.
IncludeTAGTagName1toestablisharelationbasedonanexistingindextagfortheparenttable.Indextagnamescancontainupto10characters.
Theparenttablecannotbeafreetable.
NOCPTRANSPreventstranslationtoadifferentcodepageforcharacterandmemofields.Ifthetableisconvertedtoanothercodepage,thefieldsforwhichNOCPTRANShasbeenspecifiedarenottranslated.NOCPTRANScanonlybespecifiedforcharacterandmemofields.ThiswillcreatewhatappearsintheTableDesignerasCharacter(binary)andMemo(binary)datatypes.
ThefollowingexamplecreatesatablenamedMYTABLEcontainingtwocharacterfieldsandtwomemofields.ThesecondcharacterfieldCHAR2andthesecondmemofieldMEMO2includeNOCPTRANStopreventtranslation.
CREATETABLEmytable(char1C(10),char2C(10)NOCPTRANS,;memo1M,memo2MNOCPTRANS)
PRIMARYKEYeExpression2TAGTagName2Specifiesaprimaryindextocreate.eExpression2specifiesanyfieldorcombinationoffieldsinthetable.TAGTagName2specifiesthenamefortheprimaryindextagthatiscreated.Indextagnamescancontainupto10characters.
Becauseatablecanhaveonlyoneprimaryindex,youcannotincludethisclauseifyouhavealreadycreatedaprimaryindexforafield.VisualFoxProgeneratesanerrorifyouincludemorethanonePRIMARYKEYclauseinCREATETABLE.
UNIQUEeExpression3TAGTagName3Createsacandidateindex.eExpression3specifiesanyfieldorcombinationoffieldsinthetable.However,ifyouhavecreatedaprimaryindexwithoneofthePRIMARYKEYoptions,youcannotincludethefieldthatwasspecifiedfortheprimaryindex.TAGTagName3specifiesatagnameforthecandidateindextagthatiscreated.Indextagnamescancontainupto10characters.
Atablecanhavemultiplecandidateindexes.
FOREIGNKEYeExpression4TAGTagName4[NODUP]Createsaforeign(non-primary)index,andestablishesarelationshiptoaparenttable.eExpression4specifiestheforeignindexkeyexpressionandTagName4specifiesthenameoftheforeignindexkeytagthatiscreated.Indextagnamescancontainupto10characters.IncludeNODUPtocreateacandidateforeignindex.
Youcancreatemultipleforeignindexesforthetable,buttheforeignindexexpressionsmustspecifydifferentfieldsinthetable.
REFERENCESTableName3[TAGTagName5]Specifiestheparenttabletowhichapersistentrelationshipisestablished.IncludeTAGTagName5toestablisharelationbasedonanindextagfortheparenttable.Indextagnamescancontainupto10characters.IfyouomitTAGTagName5,therelationshipisestablishedusingtheparenttablesprimaryindexkeybydefault.CHECKeExpression2[ERRORcMessageText2]Specifiesthetablevalidationrule.ERRORcMessageText2specifiestheerrormessageVisualFoxProdisplayswhenthetablevalidationruleisexecuted.ThemessageisdisplayedonlywhendataischangedwithinaBrowsewindoworEditwindow.FROMARRAYArrayNameSpecifiesthenameofanexistingarraywhosecontentsarethename,type,precision,andscaleforeachfieldinthetable.ThecontentsofthearraycanbedefinedwiththeAFIELDS()function.Remarks
Thenewtableisopenedinthelowestavailableworkarea,andcanbeaccessedbyitsalias.Thenewtableisopenedexclusively,regardlessofthecurrentsettingofSETEXCLUSIVE.

IfadatabaseisopenandyoudontincludetheFREEclause,thenewtableisaddedtothedatabase.Youcannotcreateanewtablewiththesamenameasatableinthedatabase.

Ifadatabaseisntopenwhenyoucreatethenewtable,includingtheNAME,CHECK,DEFAULT,FOREIGNKEY,PRIMARYKEY,orREFERENCESclausesgeneratesanerror.

NotethattheCREATETABLEsyntaxusescommastoseparatecertainCREATETABLEoptions.Also,theNULL,NOTNULL,CHECK,DEFAULT,PRIMARYKEYandUNIQUEclausemustbeplacedwithintheparenthesescontainingthecolumndefinitions.
Example
ThefollowingexamplecreatesanewdatabasenamedMydata1.CREATETABLEisusedtocreatethreetables(Salesman,Customer,andOrders).TheFOREIGNKEYandREFERENCESclausesinthesecondCREATETABLEcommandcreateapersistentone-to-manyrelationshipbetweentheSalesmanandCustomertables.TheDEFAULTclausesinthethirdCREATETABLEcommandestablishdefaultvalues,andtheCHECKandERRORclausesestablishbusinessrulesforenteringdataintospecificfields.TheMODIFYDATABASEisusedtodisplaytherelationshipbetweenthethreetables.

CLOSEDATABASESCLEAR*CreatemydatadatabaseinthecurrentdirectoryorfolderCREATEDATABASEmydata1*CreateasalesmantablewithaprimarykeyCREATETABLEsalesman;(SalesIDc(6)PRIMARYKEY,;SaleNameC(20))*Createacustomertableandrelateittothesalesmantable.CREATETABLEcustomer;(SalesIDc(6),;CustIdiPRIMARYKEY,;CustNamec(20)UNIQUE,;SalesBranchc(3),;FOREIGNKEYSalesIdTAGSalesIdREFERENCESsalesman)*Createanorderstablerelatedtocustomerwithitsownprimary*keyandsomebusinessrulessuchasdefaults&checks.CREATETABLEorders;(OrderIdiPRIMARYKEY,;CustIdiREFERENCEScustomerTAGCustId,;OrderAmty(4),;OrderQtyi;DEFAULT10;CHECK(OrderQty>9);ERROR"OrderQuantitymustbeatleast10",;DiscPercentn(6,2)NULL;DEFAULT.NULL.,;CHECK(OrderAmt>0)ERROR"OrderAmountMustbe>0")*Displaynewdatabase,tables,andrelationshipsMODIFYDATABASE*DeleteexamplefilesSETSAFETYOFF&&TosuppressverificationmessageCLOSEDATABASES&&ClosedatabasebeforedeletingDELETEDATABASEmydata1DELETETABLES
Memory所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失

作者: admin 时间: 2015-1-17 13:18
XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

作者: 若相依 时间: 2015-1-20 19:10
从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。

作者: 海妖 时间: 2015-1-29 15:40
是否碎片会引发效率问题？这都是需要进一步探讨的东西。varbinary(max)代替image也让SQLServer的字段类型更加简洁统一。

作者: 爱飞 时间: 2015-2-6 02:30
对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。

作者: 谁可相欹 时间: 2015-2-15 03:51
相信各位对数据库和怎么样学习数据库都有一些经验和看法，也会有人走了一些弯路总结出自己的经验来，希望大家能把各自的看法和经验拿出来分享，给别人一份帮助，给自己一份快乐

作者: 简单生活 时间: 2015-3-4 11:14
但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

作者: 乐观 时间: 2015-3-11 18:44
然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情

作者: 精灵巫婆 时间: 2015-3-19 07:56
如果，某一版本可以提供强大的并发响应，但是没有Oracle的相应版本稳定，或者价格较贵，那么，它就是不适合的。

作者: 山那边是海 时间: 2015-3-27 16:10
习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。

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