创建表

创建表►目录1.语法2.创建表命令2.1. CREATE TABLE ... AS SELECT 语句3.列定义3.1. 列数据类型3.2. 默认子句3.3. COLLATE 子句3.4. GENERATED ALWAYS AS 子句3.5. 主键3.6. 唯一约束3.7. 检查约束3.8. NOT NULL 约束4.约束执行4.1. 对违反约束的响应5. ROWID 和 INTEGER PRIMARY KEY1.语法

创建表 stmt：

CREATETEMPTEMPORARYTABLEIFNOTEXISTSschema-name.table-name(column-deftable-constraint,)table-options,ASselect-stmt

列定义：

column-nametype-namecolumn-constraint

列约束：

CONSTRAINTnamePRIMARYKEYDESCconflict-clauseAUTOINCREMENTASCNOTNULLconflict-clauseUNIQUEconflict-clauseCHECK(expr)DEFAULT(expr)literal-valuesigned-numberCOLLATEcollation-nameforeign-key-clauseGENERATEDALWAYSAS(expr)VIRTUALSTORED

冲突条款：

ONCONFLICTROLLBACKABORTFAILIGNOREREPLACE

表达式：

literal-valuebind-parameterschema-name.table-name.column-nameunary-operatorexprexprbinary-operatorexprfunction-name(DISTINCTexpr)filter-clauseover-clause,*(expr),CAST(exprAStype-name)exprCOLLATEcollation-nameexprNOTLIKEGLOBREGEXPMATCHexprexprESCAPEexprexprISNULLNOTNULLNOTNULLexprISNOTDISTINCTFROMexprexprNOTBETWEENexprANDexprexprNOTIN(select-stmt)expr,schema-name.table-function(expr)table-name,NOTEXISTS(select-stmt)CASEexprWHENexprTHENexprELSEexprENDraise-function

过滤器子句：

FILTER(WHEREexpr)

过度条款：

OVERwindow-name(base-window-namePARTITIONBYexpr,ORDERBYordering-term,frame-spec)

框架规格：

GROUPSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWINGRANGEROWSUNBOUNDEDPRECEDINGexprPRECEDINGCURRENTROWexprPRECEDINGCURRENTROWexprFOLLOWINGexprPRECEDINGCURRENTROWexprFOLLOWINGEXCLUDECURRENTROWEXCLUDEGROUPEXCLUDETIESEXCLUDENOOTHERS

订购条款：

exprCOLLATEcollation-nameDESCASCNULLSFIRSTNULLSLAST

提升功能：

RAISE(ROLLBACK,error-message)IGNOREABORTFAIL

外键子句：

REFERENCESforeign-table(column-name),ONDELETESETNULLUPDATESETDEFAULTCASCADERESTRICTNOACTIONMATCHnameNOTDEFERRABLEINITIALLYDEFERREDINITIALLYIMMEDIATE

字面值：

CURRENT_TIMESTAMPnumeric-literalstring-literalblob-literalNULLTRUEFALSECURRENT_TIMECURRENT_DATE

签名号码：

+numeric-literal-

类型名称：

name(signed-number,signed-number)(signed-number)

签名号码：

+numeric-literal-

选择stmt：

WITHRECURSIVEcommon-table-expression,SELECTDISTINCTresult-column,ALLFROMtable-or-subqueryjoin-clause,WHEREexprGROUPBYexprHAVINGexpr,WINDOWwindow-nameASwindow-defn,VALUES(expr),,compound-operatorselect-coreORDERBYLIMITexprordering-term,OFFSETexpr,expr

公用表表达式：

table-name(column-name)ASNOTMATERIALIZED(select-stmt),

复合运算符：

UNIONUNIONINTERSECTEXCEPTALL

表达式：

literal-valuebind-parameterschema-name.table-name.column-nameunary-operatorexprexprbinary-operatorexprfunction-name(DISTINCTexpr)filter-clauseover-clause,*(expr),CAST(exprAStype-name)exprCOLLATEcollation-nameexprNOTLIKEGLOBREGEXPMATCHexprexprESCAPEexprexprISNULLNOTNULLNOTNULLexprISNOTDISTINCTFROMexprexprNOTBETWEENexprANDexprexprNOTIN(select-stmt)expr,schema-name.table-function(expr)table-name,NOTEXISTS(select-stmt)CASEexprWHENexprTHENexprELSEexprENDraise-function

过滤器子句：

FILTER(WHEREexpr)

字面值：

CURRENT_TIMESTAMPnumeric-literalstring-literalblob-literalNULLTRUEFALSECURRENT_TIMECURRENT_DATE

过度条款：

OVERwindow-name(base-window-namePARTITIONBYexpr,ORDERBYordering-term,frame-spec)

框架规格：

GROUPSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWINGRANGEROWSUNBOUNDEDPRECEDINGexprPRECEDINGCURRENTROWexprPRECEDINGCURRENTROWexprFOLLOWINGexprPRECEDINGCURRENTROWexprFOLLOWINGEXCLUDECURRENTROWEXCLUDEGROUPEXCLUDETIESEXCLUDENOOTHERS

提升功能：

RAISE(ROLLBACK,error-message)IGNOREABORTFAIL

类型名称：

name(signed-number,signed-number)(signed-number)

签名号码：

+numeric-literal-

加入子句：

table-or-subqueryjoin-operatortable-or-subqueryjoin-constraint

加入约束：

USING(column-name),ONexpr

join-operator:

NATURALLEFTOUTERJOIN,RIGHTFULLINNERCROSS

ordering-term:

exprCOLLATEcollation-nameDESCASCNULLSFIRSTNULLSLAST

result-column:

exprAScolumn-alias*table-name.*

table-or-subquery:

schema-name.table-nameAStable-aliasINDEXEDBYindex-nameNOTINDEXEDtable-function-name(expr),AStable-alias(select-stmt)(table-or-subquery),join-clause

window-defn:

(base-window-namePARTITIONBYexpr,ORDERBYordering-term,frame-spec)

frame-spec:

GROUPSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWINGRANGEROWSUNBOUNDEDPRECEDINGexprPRECEDINGCURRENTROWexprPRECEDINGCURRENTROWexprFOLLOWINGexprPRECEDINGCURRENTROWexprFOLLOWINGEXCLUDECURRENTROWEXCLUDEGROUPEXCLUDETIESEXCLUDENOOTHERS

表约束：

CONSTRAINTnamePRIMARYKEY(indexed-column)conflict-clause,UNIQUECHECK(expr)FOREIGNKEY(column-name)foreign-key-clause,

conflict-clause:

ONCONFLICTROLLBACKABORTFAILIGNOREREPLACE

expr:

literal-valuebind-parameterschema-name.table-name.column-nameunary-operatorexprexprbinary-operatorexprfunction-name(DISTINCTexpr)filter-clauseover-clause,*(expr),CAST(exprAStype-name)exprCOLLATEcollation-nameexprNOTLIKEGLOBREGEXPMATCHexprexprESCAPEexprexprISNULLNOTNULLNOTNULLexprISNOTDISTINCTFROMexprexprNOTBETWEENexprANDexprexprNOTIN(select-stmt)expr,schema-name.table-function(expr)table-name,NOTEXISTS(select-stmt)CASEexprWHENexprTHENexprELSEexprENDraise-function

filter-clause:

FILTER(WHEREexpr)

literal-value:

CURRENT_TIMESTAMPnumeric-literalstring-literalblob-literalNULLTRUEFALSECURRENT_TIMECURRENT_DATE

over-clause:

OVERwindow-name(base-window-namePARTITIONBYexpr,ORDERBYordering-term,frame-spec)

frame-spec:

GROUPSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWINGRANGEROWSUNBOUNDEDPRECEDINGexprPRECEDINGCURRENTROWexprPRECEDINGCURRENTROWexprFOLLOWINGexprPRECEDINGCURRENTROWexprFOLLOWINGEXCLUDECURRENTROWEXCLUDEGROUPEXCLUDETIESEXCLUDENOOTHERS

ordering-term:

exprCOLLATEcollation-nameDESCASCNULLSFIRSTNULLSLAST

raise-function:

RAISE(ROLLBACK,error-message)IGNOREABORTFAIL

type-name:

name(signed-number,signed-number)(signed-number)

signed-number:

+numeric-literal-

foreign-key-clause:

REFERENCESforeign-table(column-name),ONDELETESETNULLUPDATESETDEFAULTCASCADERESTRICTNOACTIONMATCHnameNOTDEFERRABLEINITIALLYDEFERREDINITIALLYIMMEDIATE

indexed-column:

column-nameCOLLATEcollation-nameDESCexprASC

表选项：

WITHOUTROWIDSTRICT,2.创建表命令

“CREATE TABLE”命令用于在 SQLite 数据库中创建一个新表。CREATE TABLE 命令指定新表的以下属性：

新表的名称。

在其中创建新表的数据库。可以在主数据库、临时数据库或任何附加数据库中创建表。

表中每一列的名称。

表中每一列的