📄 lang_createtable.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"><html><head><title>SQLite Query Language: CREATE TABLE</title><style type="text/css">body { margin: auto; font-family: "Verdana" "sans-serif"; padding: 8px 1%;}a { color: #45735f }a:visited { color: #734559 }.logo { position:absolute; margin:3px; }.tagline { float:right; text-align:right; font-style:italic; width:240px; margin:12px; margin-top:58px;}.toolbar { font-variant: small-caps; text-align: center; line-height: 1.6em; margin: 0; padding:1px 8px;}.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }.toolbar a:visited { color: white; }.toolbar a:hover { color: #80a796; background: white; }.content { margin: 5%; }.content dt { font-weight:bold; }.content dd { margin-bottom: 25px; margin-left:20%; }.content ul { padding:0px; padding-left: 15px; margin:0px; }/* rounded corners */.se { background: url(images/se.png) 100% 100% no-repeat #80a796}.sw { background: url(images/sw.png) 0% 100% no-repeat }.ne { background: url(images/ne.png) 100% 0% no-repeat }.nw { background: url(images/nw.png) 0% 0% no-repeat }</style><meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head><body><div><!-- container div to satisfy validator --><a href="index.html"><img class="logo" src="images/SQLite.gif" alt="SQLite Logo" border="0"></a><div><!-- IE hack to prevent disappearing logo--></div><div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div><table width=100% style="clear:both"><tr><td> <div class="se"><div class="sw"><div class="ne"><div class="nw"> <div class="toolbar"> <a href="about.html">About</a> <a href="sitemap.html">Sitemap</a> <a href="docs.html">Documentation</a> <a href="download.html">Download</a> <a href="copyright.html">License</a> <a href="news.html">News</a> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> <a href="support.html">Support</a> </div></div></div></div></div></td></tr></table> <a href="lang.html"><h2>SQL As Understood By SQLite</h2></a><h3>CREATE TABLE</h3><table cellpadding="10"><tr><td align="right" valign="top"><i><font color="#ff3434">sql-command</font></i> ::=</td><td><b><font color="#2c2cf0">CREATE </font></b>[<b><font color="#2c2cf0">TEMP </font></b><big>|</big><b><font color="#2c2cf0"> TEMPORARY</font></b>]<b><font color="#2c2cf0"> TABLE </font></b>[<b><font color="#2c2cf0">IF NOT EXISTS</font></b>]<b><font color="#2c2cf0"> </font></b>[<b><font color="#2c2cf0"></font></b><i><font color="#ff3434">database-name</font></i><b><font color="#2c2cf0"> <big>.</big></font></b>]<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">table-name</font></i><b><font color="#2c2cf0"> <big>(</big><br> </font></b><i><font color="#ff3434">column-def</font></i><b><font color="#2c2cf0"> </font></b>[<b><font color="#2c2cf0"><big>,</big> </font></b><i><font color="#ff3434">column-def</font></i><b><font color="#2c2cf0"></font></b>]<b><font color="#2c2cf0"></font></b><big>*</big><b><font color="#2c2cf0"><br> </font></b>[<b><font color="#2c2cf0"><big>,</big> </font></b><i><font color="#ff3434">constraint</font></i><b><font color="#2c2cf0"></font></b>]<b><font color="#2c2cf0"></font></b><big>*</big><b><font color="#2c2cf0"><br><big>)</big></font></b></td></tr><tr><td align="right" valign="top"><i><font color="#ff3434">sql-command</font></i> ::=</td><td><b><font color="#2c2cf0">CREATE </font></b>[<b><font color="#2c2cf0">TEMP </font></b><big>|</big><b><font color="#2c2cf0"> TEMPORARY</font></b>]<b><font color="#2c2cf0"> TABLE </font></b>[<b><font color="#2c2cf0"></font></b><i><font color="#ff3434">database-name</font></i><b><font color="#2c2cf0"><big>.</big></font></b>]<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">table-name</font></i><b><font color="#2c2cf0"> AS </font></b><i><font color="#ff3434">select-statement</font></i><b><font color="#2c2cf0"></font></b></td></tr><tr><td align="right" valign="top"><i><font color="#ff3434">column-def</font></i> ::=</td><td><b><font color="#2c2cf0"></font></b><i><font color="#ff3434">name</font></i><b><font color="#2c2cf0"> </font></b>[<b><font color="#2c2cf0"></font></b><i><font color="#ff3434">type</font></i><b><font color="#2c2cf0"></font></b>]<b><font color="#2c2cf0"> </font></b>[<b><font color="#2c2cf0"></font></b>[<b><font color="#2c2cf0">CONSTRAINT </font></b><i><font color="#ff3434">name</font></i><b><font color="#2c2cf0"></font></b>]<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">column-constraint</font></i><b><font color="#2c2cf0"></font></b>]<b><font color="#2c2cf0"></font></b><big>*</big><b><font color="#2c2cf0"></font></b></td></tr><tr><td align="right" valign="top"><i><font color="#ff3434">type</font></i> ::=</td><td><b><font color="#2c2cf0"></font></b><i><font color="#ff3434">typename</font></i><b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"><br></font></b><i><font color="#ff3434">typename</font></i><b><font color="#2c2cf0"> <big>(</big> </font></b><i><font color="#ff3434">number</font></i><b><font color="#2c2cf0"> <big>)</big> </font></b><big>|</big><b><font color="#2c2cf0"><br></font></b><i><font color="#ff3434">typename</font></i><b><font color="#2c2cf0"> <big>(</big> </font></b><i><font color="#ff3434">number</font></i><b><font color="#2c2cf0"> <big>,</big> </font></b><i><font color="#ff3434">number</font></i><b><font color="#2c2cf0"> <big>)</big></font></b></td></tr><tr><td align="right" valign="top"><i><font color="#ff3434">column-constraint</font></i> ::=</td><td><b><font color="#2c2cf0">NOT NULL </font></b>[<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">conflict-clause</font></i><b><font color="#2c2cf0"> </font></b>]<b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"><br>PRIMARY KEY </font></b>[<b><font color="#2c2cf0"></font></b><i><font color="#ff3434">sort-order</font></i><b><font color="#2c2cf0"></font></b>]<b><font color="#2c2cf0"> </font></b>[<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">conflict-clause</font></i><b><font color="#2c2cf0"> </font></b>]<b><font color="#2c2cf0"> </font></b>[<b><font color="#2c2cf0">AUTOINCREMENT</font></b>]<b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"><br>UNIQUE </font></b>[<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">conflict-clause</font></i><b><font color="#2c2cf0"> </font></b>]<b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"><br>CHECK <big>(</big> </font></b><i><font color="#ff3434">expr</font></i><b><font color="#2c2cf0"> <big>)</big> </font></b><big>|</big><b><font color="#2c2cf0"><br>DEFAULT </font></b><i><font color="#ff3434">value</font></i><b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"><br>COLLATE </font></b><i><font color="#ff3434">collation-name</font></i><b><font color="#2c2cf0"></font></b></td></tr><tr><td align="right" valign="top"><i><font color="#ff3434">constraint</font></i> ::=</td><td><b><font color="#2c2cf0">PRIMARY KEY <big>(</big> </font></b><i><font color="#ff3434">column-list</font></i><b><font color="#2c2cf0"> <big>)</big> </font></b>[<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">conflict-clause</font></i><b><font color="#2c2cf0"> </font></b>]<b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"><br>UNIQUE <big>(</big> </font></b><i><font color="#ff3434">column-list</font></i><b><font color="#2c2cf0"> <big>)</big> </font></b>[<b><font color="#2c2cf0"> </font></b><i><font color="#ff3434">conflict-clause</font></i><b><font color="#2c2cf0"> </font></b>]<b><font color="#2c2cf0"> </font></b><big>|</big><b><font color="#2c2cf0"><br>CHECK <big>(</big> </font></b><i><font color="#ff3434">expr</font></i><b><font color="#2c2cf0"> <big>)</big></font></b></td></tr><tr><td align="right" valign="top"><i><font color="#ff3434">conflict-clause</font></i> ::=</td><td><b><font color="#2c2cf0">ON CONFLICT </font></b><i><font color="#ff3434">conflict-algorithm</font></i><b><font color="#2c2cf0"></font></b></td></tr></table><p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"followed by the name of a new table and a parenthesized list of columndefinitions and constraints. Tables names that begin with "<b>sqlite_</b>" are reservedfor use by the engine.</p><p>Each column definition is the name of the column followed by thedatatype for that column, then one or more optional column constraints.The datatype for the column does not restrict what data may be putin that column.See <a href="datatype3.html">Datatypes In SQLite Version 3</a> foradditional information.The UNIQUE constraint causes an unique index to be created on the specifiedcolumns. All NULL values are considered different from each other and fromall other values for the purpose of determining uniqueness, hence a UNIQUEcolumn may contain multiple entries with the value of NULL.The COLLATE clause specifies what text <a href="datatype3.html#collation">collating function</a> to use when comparing text entries for the column. The built-in <a href="datatype3.html#collation">BINARY</a> collating function is used by default.<p>The DEFAULT constraint specifies a default value to use when doing an <a href="lang_insert.html">INSERT</a>.The value may be NULL, a string constant or a number. Starting with<a href="releaselog/3_1_0.html">version 3.1.0</a>,the default value may also be one of the special case-independantkeywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value isNULL, a string constant or number, it is literally inserted into the columnwhenever an INSERT statement that does not specify a value for the column isexecuted. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, thenthe current UTC date and/or time is inserted into the columns. ForCURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The formatfor CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".</p><a name="rowid"></a><p>Specifying a PRIMARY KEY normally just creates a UNIQUE indexon the corresponding columns. However, if primary key is on a single columnthat has datatype INTEGER, then that column is used internallyas the actual key of the B-Tree for the table. This means that the columnmay only hold unique integer values. (Except for this one case,SQLite ignores the datatype specification of columns and allowsany kind of data to be put in a column regardless of its declareddatatype.) If a table does not have an INTEGER PRIMARY KEY column,then the B-Tree key will be a automatically generated integer.The B-Tree key for a row can always be accessed using one of thespecial names "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>".This is true regardless of whether or not there is an INTEGERPRIMARY KEY. An INTEGER PRIMARY KEY column can also include thekeyword <a href="autoinc.html">AUTOINCREMENT</a>. The <a href="autoinc.html">AUTOINCREMENT</a> keyword modified the waythat B-Tree keys are automatically generated. Additional detailon automatic B-Tree key generation is available<a href="autoinc.html">separately</a>.</p><p>According to the SQL standard, PRIMARY KEY should imply NOT NULL.Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite. SQLite allows NULL valuesin a PRIMARY KEY column. We could change SQLite to conform to thestandard (and we might do so in the future), but by the time theoversight was discovered, SQLite was in such wide use that we fearedbreaking legacy code if we fixed the problem. So for now we havechosen to continue allowing NULLs in PRIMARY KEY columns.Developers should be aware, however, that we may change SQLite toconform to the SQL standard in future and should design new programsaccordingly.</p><p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"and "TABLE" then the table that is created is only visiblewithin that same database connectionand is automatically deleted whenthe database connection is closed. Any indices created on a temporary tableare also temporary. Temporary tables and indices are stored in aseparate file distinct from the main database file.</p><p> If a <database-name> is specified, then the table is created in the named database. It is an error to specify both a <database-name>and the TEMP keyword, unless the <database-name> is "temp". If nodatabase name is specified, and the TEMP keyword is not present,the table is created in the main database.</p><p>The optional <a href="lang_conflict.html">conflict clause</a> following each constraintallows the specification of an alternative defaultconstraint conflict resolution algorithm for that constraint.The default is abort ABORT. Different constraints within the sametable may have different default conflict resolution algorithms.If an <a href="lang_insert.html">INSERT</a> or <a href="lang_update.html">UPDATE</a> statement specifies a different conflictresolution algorithm, then that algorithm is used in place of thedefault algorithm specified in the CREATE TABLE statement.See the section titled<a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p><p>CHECK constraints are supported as of <a href="releaselog/3_3_0.html">version 3.3.0</a>. Priorto version 3.3.0, CHECK constraints were parsed but not enforced.</p><p>The number of columns in a table is limited by the<a href="limits.html#max_column">SQLITE_MAX_COLUMN</a> compile-time parameter.A single row of a table cannot store more than<a href="limits.html#max_length">SQLITE_MAX_LENGTH</a> bytes of data.Both of these limits can be lowered at runtime using the<a href="c3ref/limit.html">sqlite3_limit()</a> C/C++ interface.</p><p>The CREATE TABLE AS form defines the table to bethe result set of a query. The names of the table columns arethe names of the columns in the result.</p><p>The textof each CREATE TABLE statement is stored in the <b>sqlite_master</b>table. Every time the database is opened, all CREATE TABLE statementsare read from the <b>sqlite_master</b> table and used to regenerateSQLite's internal representation of the table layout.If the original command was a CREATE TABLE AS then then an equivalentCREATE TABLE statement is synthesized and store in <b>sqlite_master</b>in place of the original command.The text of CREATE TEMPORARY TABLE statements are stored in the<b>sqlite_temp_master</b> table.</p><p>If the optional IF NOT EXISTS clause is present and another tablewith the same name aleady exists, then this command becomes a no-op.</p><p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> statement. </p><hr><small><i>This page last modified 2008/04/27 17:48:52 UTC</i></small></div></body></html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -