<html xmlns="http://www.w3.org/1999/xhtml"><head><meta charset="utf-8"><meta name="generator" content="pdf2htmlEX"><meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"><link rel="stylesheet" href="https://csdnimg.cn/release/download_crawler_static/css/base.min.css"><link rel="stylesheet" href="https://csdnimg.cn/release/download_crawler_static/css/fancy.min.css"><link rel="stylesheet" href="https://csdnimg.cn/release/download_crawler_static/11975106/raw.css"><script src="https://csdnimg.cn/release/download_crawler_static/js/compatibility.min.js"></script><script src="https://csdnimg.cn/release/download_crawler_static/js/pdf2htmlEX.min.js"></script><script>try{pdf2htmlEX.defaultViewer = new pdf2htmlEX.Viewer({});}catch(e){}</script><title></title></head><body><div id="sidebar" style="display: none"><div id="outline"></div></div><div id="pf1" class="pf w0 h0" data-page-no="1"><div class="pc pc1 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="https://csdnimg.cn/release/download_crawler_static/11975106/bg1.jpg"><div class="c x0 y1 w2 h0"><div class="t m0 x1 h2 y2 ff1 fs0 fc0 sc0 ls0 ws0">SQL As Understood By SQLite<span class="ff2"> </span></div><div class="t m0 x2 h3 y3 ff3 fs1 fc0 sc0 ls0 ws0">SQLite understands most of the standard SQL lan<span class="_ _0"></span>guage. But it does <span class="_ _1"></span><span class="fc1">omit some features</span> while at the same </div><div class="t m0 x2 h3 y4 ff3 fs1 fc0 sc0 ls0 ws0">time adding a few features of its own. This document attempts to describe precisely what parts of the SQL </div><div class="t m0 x2 h3 y5 ff3 fs1 fc0 sc0 ls0 ws0">language SQLite does and does not support. A <span class="_ _0"></span>list of <span class="_ _1"></span><span class="fc1">SQL keywords</span> is also provided. The SQL language </div><div class="t m0 x2 h3 y6 ff3 fs1 fc0 sc0 ls0 ws0">syntax is described by <span class="fc1">syntax diagrams</span><span class="ls1">. </span> </div><div class="t m0 x2 h3 y7 ff3 fs1 fc0 sc0 ls0 ws0">The following syntax documentation topics are available: </div></div><div class="c x3 y8 w3 h4"><div class="t m0 x4 h5 y9 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">aggregate functions<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 ya ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">ALTER TABLE<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yb ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">ANALYZE<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yc ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">ATTACH DATABASE<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yd ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">BEGIN TRANSACTION<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 ye ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">comment<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yf ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">COMMIT TRANSACTION<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y10 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">core functions<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y11 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">CREATE INDEX<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y12 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">CREATE TABLE<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y13 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">CREATE TRIGGER<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y14 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">CREATE VIEW<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y15 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">CREATE VIRTUAL TABLE<span class="fc0"> </span></span></span></div></div><div class="c x5 y8 w4 h4"><div class="t m0 x4 h5 y9 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">date and time functions<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 ya ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">DELETE<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yb ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">DETACH DATABASE<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yc ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">DROP INDEX<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yd ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">DROP TABLE<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 ye ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">DROP TRIGGER<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yf ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">DROP VIEW<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y10 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">END TRANSACTION<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y11 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">EXPLAIN<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y12 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">expression<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y13 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">INDEXED BY<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y14 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">INSERT<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y15 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">keywords<span class="fc0"> </span></span></span></div></div><div class="c x6 y8 w5 h4"><div class="t m0 x4 h5 y9 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">ON CONFLICT clause<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 ya ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">PRAGMA<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yb ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">REINDEX<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yc ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">RELEASE SAVEPOINT<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yd ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">REPLACE<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 ye ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">ROLLBACK TRANSACTION<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 yf ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">SAVEPOINT<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y10 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">SELECT<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y11 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">UPDATE<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y12 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">UPSERT<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y13 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">VACUUM<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y14 ff4 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">o</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _2"> </span><span class="ff3 fs1 fc1">WITH clause<span class="fc0"> </span></span></span></div></div><div class="c x0 y1 w2 h0"><div class="t m0 x2 h3 y16 ff3 fs1 fc0 sc0 ls0 ws0">The routines <span class="fc1">sqlite3_prepare_v2()</span><span class="ls1">, </span><span class="fc1">sqlite3_<span class="_ _1"></span>prepare()</span><span class="ls2">, </span><span class="fc1">sqli<span class="_ _1"></span>te3_prepare16()</span><span class="ls1">, </span><span class="fc1">sqlite3_prepare16_v2()</span><span class="ls1">, </span></div><div class="t m0 x2 h3 y17 ff3 fs1 fc1 sc0 ls0 ws0">sqlite3_exec()<span class="fc0">, and </span>sqlite3_get_table()<span class="_ _1"></span><span class="fc0"> accept an SQL statement list (sql-stmt-list) which is a semicolon-</span></div><div class="t m0 x2 h3 y18 ff3 fs1 fc0 sc0 ls0 ws0">separated list of statements. </div><div class="t m0 x2 h6 y19 ff2 fs1 fc1 sc0 ls0 ws0">sql-stmt-list:<span class="ff3 fc0"> </span></div><div class="t m0 x7 h3 y1a ff3 fs1 fc0 sc0 ls0 ws0"> </div><div class="t m0 x2 h3 y1b ff3 fs1 fc0 sc0 ls0 ws0">Each SQL statement in the statement list is an instance of the following: </div><div class="t m0 x2 h6 y1c ff2 fs1 fc1 sc0 ls0 ws0">sql-stmt:<span class="ff3 fc0"> </span></div></div><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a></div><div class="pi" data-data='{"ctm":[1.568627,0.000000,0.000000,1.568627,0.000000,0.000000]}'></div></div></body></html>
<div id="pf2" class="pf w0 h0" data-page-no="2"><div class="pc pc2 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="https://csdnimg.cn/release/download_crawler_static/11975106/bg2.jpg"><div class="c x0 y1 w2 h0"><div class="t m0 x8 h3 y1d ff3 fs1 fc0 sc0 ls0 ws0"> </div><div class="t m0 x2 h7 y1e ff3 fs2 fc3 sc0 ls0 ws0"> </div><div class="t m0 x2 h7 y1f ff3 fs2 fc3 sc0 ls0 ws0">Aggregate Functi<span class="_ _1"></span>ons </div><div class="t m0 x2 h6 y20 ff2 fs1 fc1 sc0 ls0 ws0">aggregate-function-invocation:<span class="ff3 fc0"> </span></div><div class="t m0 x9 h8 y21 ff3 fs3 fc0 sc0 ls0 ws0"> </div><div class="t m0 x2 h6 y22 ff2 fs1 fc1 sc0 ls0 ws0">filter-clause:<span class="fc0"> </span></div><div class="t m0 xa h3 y23 ff3 fs1 fc0 sc0 ls0 ws0"> </div><div class="t m0 x2 h3 y24 ff3 fs1 fc0 sc0 ls0 ws0">The aggregate functions shown <span class="_ _0"></span>below are available by default. Additi<span class="_ _1"></span>onal aggregate functions written in C </div><div class="t m0 x2 h3 y25 ff3 fs1 fc0 sc0 ls0 ws0">may be added using the <span class="fc1">sqlite3_create_function()</span> API. </div><div class="t m0 x2 h3 y26 ff3 fs1 fc0 sc0 ls0 ws0">In any aggregate function that takes a single argument, that <span class="_ _0"></span>argument can be preceded by the keyword </div><div class="t m0 x2 h3 y27 ff3 fs1 fc0 sc0 ls0 ws0">DISTINCT. In such cases, duplicate elements are filtered before being passed into the aggregate function. </div></div><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a></div><div class="pi" data-data='{"ctm":[1.568627,0.000000,0.000000,1.568627,0.000000,0.000000]}'></div></div>
<div id="pf3" class="pf w0 h0" data-page-no="3"><div class="pc pc3 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="https://csdnimg.cn/release/download_crawler_static/11975106/bg3.jpg"><div class="c x0 y1 w2 h0"><div class="t m0 x2 h3 y28 ff3 fs1 fc0 sc0 ls0 ws0">For example, the function "count(distinct X)" will return the number of distinct values of column X instead of </div><div class="t m0 x2 h3 y29 ff3 fs1 fc0 sc0 ls0 ws0">the total number of non-null values in column X. </div><div class="t m0 x2 h3 y3 ff3 fs1 fc0 sc0 ls0 ws0">If a FILTER clause is provided, then only rows for which the <span class="ff6">expr</span> is true are included in the aggregate<span class="_ _1"></span>. </div></div><div class="c xb y2a w6 h9"><div class="t m0 x4 h5 y2b ff7 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">•</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _3"> </span><span class="ff3 fs3 fc1">avg(X)<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y2c ff7 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">•</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _3"> </span><span class="ff3 fs3 fc1">count(*)<span class="fc0"> </span></span></span></div></div><div class="c xc y2a w7 h9"><div class="t m0 x4 h5 y2b ff7 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">•</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _3"> </span><span class="ff3 fs3 fc1">count(X)<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y2c ff7 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">•</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _3"> </span><span class="ff3 fs3 fc1">group_concat(X)<span class="_ _0"></span><span class="fc0"> </span></span></span></div></div><div class="c xd y2a w8 h9"><div class="t m0 x4 h5 y2b ff7 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">•</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _3"> </span><span class="ff3 fs3 fc1">group_concat(X,<span class="_ _0"></span>Y)<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y2c ff7 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">•</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _3"> </span><span class="ff3 fs3 fc1">max(X)<span class="fc0"> </span></span></span></div></div><div class="c xe y2a w9 h9"><div class="t m0 x4 h5 y2b ff7 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">•</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _3"> </span><span class="ff3 fs3 fc1">min(X)<span class="fc0"> </span></span></span></div><div class="t m0 x4 h5 y2c ff7 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">•</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _3"> </span><span class="ff3 fs3 fc1">sum(X)<span class="fc0"> </span></span></span></div></div><div class="c xf y2a wa h9"><div class="t m0 x4 h5 y2b ff7 fs2 fc0 sc0 ls0 ws0"><span class="fc2 sc0">•</span><span class="ff5"><span class="fc2 sc0"> </span><span class="_ _3"> </span><span class="ff3 fs3 fc1">total(X)<span class="fc0"> </span></span></span></div></div><div class="c x0 y1 w2 h0"><div class="t m0 x10 h8 y2d ff3 fs3 fc0 sc0 ls0 ws0"> </div><div class="t m0 x2 h6 y2e ff2 fs1 fc0 sc0 ls0 ws0">avg(<span class="ff8">X</span>)<span class="ff3"> </span></div><div class="t m0 x11 h3 y2f ff3 fs1 fc0 sc0 ls0 ws0">The avg() function returns the average value of all non-NULL <span class="ff6">X</span> within a group. String and BLOB </div><div class="t m0 x11 h3 y30 ff3 fs1 fc0 sc0 ls0 ws0">values that do not look like numbers are interpreted as 0. <span class="_ _0"></span>The result of avg() is always a floating </div><div class="t m0 x11 h3 y31 ff3 fs1 fc0 sc0 ls0 ws0">point value as long as at there is at least one non-NULL input even if all inputs are integers. The </div><div class="t m0 x11 h3 y32 ff3 fs1 fc0 sc0 ls0 ws0">result of avg() is NULL if and only if there are no non-NULL inputs. </div><div class="t m0 x2 h6 y33 ff2 fs1 fc0 sc0 ls0 ws0">count(<span class="ff8">X</span>) </div><div class="t m0 x2 h6 y34 ff2 fs1 fc0 sc0 ls0 ws0">count(*)<span class="ff3"> </span></div><div class="t m0 x11 h3 y35 ff3 fs1 fc0 sc0 ls0 ws0">The count(X) function returns a <span class="_ _0"></span>count of the number of times that <span class="_ _1"></span><span class="ff6">X</span> is not NULL in a group. The </div><div class="t m0 x11 h3 y36 ff3 fs1 fc0 sc0 ls0 ws0">count(*) function (with no arguments) returns the total number of rows in the group. </div><div class="t m0 x2 h6 y37 ff2 fs1 fc0 sc0 ls0 ws0">group_concat(<span class="ff8">X</span>) </div><div class="t m0 x2 h6 y38 ff2 fs1 fc0 sc0 ls0 ws0">group_concat(<span class="ff8">X</span>,<span class="ff8">Y</span>)<span class="ff3"> </span></div><div class="t m0 x11 h3 y39 ff3 fs1 fc0 sc0 ls0 ws0">The group_concat() function returns a string which is the concatenation of all non-NULL values of <span class="ff6">X</span><span class="ls1">. </span></div><div class="t m0 x11 h3 y3a ff3 fs1 fc0 sc0 ls0 ws0">If parameter <span class="ff6">Y</span> is present then it is used as the separator between instances of <span class="ff6">X</span>. A comma (",") is </div><div class="t m0 x11 h3 y3b ff3 fs1 fc0 sc0 ls0 ws0">used as the separator if <span class="ff6">Y</span> is omitted. The order of the concatenated elements is arbitrary. </div><div class="t m0 x2 h6 y3c ff2 fs1 fc0 sc0 ls0 ws0">max(<span class="ff8">X</span>)<span class="ff3"> </span></div><div class="t m0 x11 h3 y3d ff3 fs1 fc0 sc0 ls0 ws0">The max() aggregate function returns the max<span class="_ _0"></span>imum value of all values in the group. The maximum </div><div class="t m0 x11 h3 y3e ff3 fs1 fc0 sc0 ls0 ws0">value is the value that would be returned last in an ORDER BY on the same column. Aggregate max() </div><div class="t m0 x11 h3 y3f ff3 fs1 fc0 sc0 ls0 ws0">returns NULL if and only if there are no non-NULL values in the group. </div><div class="t m0 x2 h6 y40 ff2 fs1 fc0 sc0 ls0 ws0">min(<span class="ff8">X</span>)<span class="ff3"> </span></div><div class="t m0 x11 h3 y41 ff3 fs1 fc0 sc0 ls0 ws0">The min() aggregate function returns th<span class="_ _0"></span>e minimum non<span class="_ _1"></span>-NULL value of all values in the group. The </div><div class="t m0 x11 h3 y42 ff3 fs1 fc0 sc0 ls0 ws0">minimum value is the first non-NULL value that would appear in an ORDER BY of the column. </div><div class="t m0 x11 h3 y43 ff3 fs1 fc0 sc0 ls0 ws0">Aggregate min() returns NULL if and only if th<span class="_ _0"></span>ere are no non-NULL <span class="_ _1"></span>values in the group. </div><div class="t m0 x2 h6 y44 ff2 fs1 fc0 sc0 ls0 ws0">sum(<span class="ff8">X</span>) </div><div class="t m0 x2 h6 y45 ff2 fs1 fc0 sc0 ls0 ws0">total(<span class="ff8">X</span>)<span class="ff3"> </span></div><div class="t m0 x11 h3 y46 ff3 fs1 fc0 sc0 ls0 ws0">The sum() and total() aggregate functions return sum of all non-NULL values in the group. If there </div><div class="t m0 x11 h3 y47 ff3 fs1 fc0 sc0 ls0 ws0">are no non-NULL input rows then sum() returns NULL but total() return<span class="_ _0"></span>s 0.0. NULL is not normally a </div><div class="t m0 x11 h3 y48 ff3 fs1 fc0 sc0 ls0 ws0">helpful result for the sum of no rows bu<span class="_ _0"></span>t the SQL standard requires it and most other SQL database </div><div class="t m0 x11 h3 y49 ff3 fs1 fc0 sc0 ls0 ws0">engines implement sum() that way so SQLite does it in the same way in order to be compatible. The </div><div class="t m0 x11 h3 y4a ff3 fs1 fc0 sc0 ls0 ws0">non-standard total() function is provided as a convenient way to work aroun<span class="_ _0"></span>d this design problem in </div><div class="t m0 x11 h3 y4b ff3 fs1 fc0 sc0 ls0 ws0">the SQL language. </div><div class="t m0 x11 h3 y4c ff3 fs1 fc0 sc0 ls0 ws0">The result of total() is always a floating point value. The result of sum() is an int<span class="_ _1"></span>eger value if all non-</div><div class="t m0 x11 h3 y4d ff3 fs1 fc0 sc0 ls0 ws0">NULL inputs are integers. If any input to su<span class="_ _0"></span>m() is neither an integer or a NULL then sum() returns a </div><div class="t m0 x11 h3 y4e ff3 fs1 fc0 sc0 ls0 ws0">floating point value which might be an approximation to the true su<span class="_ _0"></span>m.<span class="_ _1"></span> </div></div><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a></div><div class="pi" data-data='{"ctm":[1.568627,0.000000,0.000000,1.568627,0.000000,0.000000]}'></div></div>
<div id="pf4" class="pf w0 h0" data-page-no="4"><div class="pc pc4 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="https://csdnimg.cn/release/download_crawler_static/11975106/bg4.jpg"><div class="c x0 y1 w2 h0"><div class="t m0 x11 h3 y28 ff3 fs1 fc0 sc0 ls0 ws0">Sum() will throw an "integer overflow" exception if all inputs are integers or NULL and an integer </div><div class="t m0 x11 h3 y29 ff3 fs1 fc0 sc0 ls0 ws0">overflow occurs at any point during the computation. Total() never throws an integer overflow. <span class="_ _1"></span> </div><div class="t m0 x2 h7 y4f ff3 fs2 fc3 sc0 ls0 ws0"> </div><div class="t m0 x2 h7 y50 ff3 fs2 fc3 sc0 ls0 ws0">ALTER TABLE<span class="_ _1"></span> </div><div class="t m0 x2 h6 y51 ff2 fs1 fc1 sc0 ls0 ws0">alter-table-stmt:<span class="ff3 fc0"> </span></div><div class="t m0 x12 h8 y52 ff3 fs3 fc0 sc0 ls0 ws0"> </div><div class="t m0 x2 h6 y53 ff2 fs1 fc4 sc0 ls0 ws0">column-def:<span class="fc1"> </span></div><div class="t m0 x13 h3 y54 ff3 fs1 fc0 sc0 ls0 ws0"> </div><div class="t m0 x2 h3 y55 ff3 fs1 fc0 sc0 ls0 ws0">SQLite supports a limited subset of ALTER TAB<span class="_ _0"></span>LE. The ALTER TABLE command in SQLite allows the user to </div><div class="t m0 x2 h3 y56 ff3 fs1 fc0 sc0 ls0 ws0">rename a table, to rename a column within a table, or to add a new column to an existing table. </div><div class="t m0 x2 h3 y57 ff3 fs1 fc5 sc0 ls0 ws0">ALTER TABLE RENAME </div><div class="t m0 x2 h3 y58 ff3 fs1 fc0 sc0 ls0 ws0">The RENAME TO syntax changes the name <span class="_ _0"></span>of <span class="_ _4"></span>table-name<span class="_ _4"></span> <span class="ls3">to <span class="_ _4"></span></span>new-<span class="_ _0"></span>table-name<span class="_ _4"></span>. This command cannot be used </div><div class="t m0 x2 h3 y59 ff3 fs1 fc0 sc0 ls0 ws0">to move a table between attached databases, only to rename <span class="_ _0"></span>a table within the sam<span class="_ _1"></span>e database. If the table </div><div class="t m0 x2 h3 y5a ff3 fs1 fc0 sc0 ls0 ws0">being renamed has triggers or indices, then these remain attached to the table af<span class="_ _0"></span>ter it has been renamed. <span class="_ _1"></span> </div><div class="t m0 x2 ha y5b ff2 fs3 fc0 sc0 ls0 ws0">Compatibili<span class="_ _0"></span>ty Note:<span class="ff3"> The beh<span class="_ _0"></span>avior of<span class="_ _0"></span> ALTER TABLE<span class="_ _0"></span> when ren<span class="_ _0"></span>aming a table<span class="_ _0"></span> was enhance<span class="_ _0"></span>d in versi<span class="_ _0"></span>ons 3.25.0<span class="_ _0"></span> (2018-<span class="ls4">09<span class="_ _1"></span></span>-15)<span class="_ _0"></span> </span></div><div class="t m0 x2 h8 y5c ff3 fs3 fc0 sc0 ls0 ws0">and 3.26.0 (<span class="_ _0"></span>2018-<span class="ls5">12</span>-<span class="_ _0"></span>01) in order t<span class="_ _0"></span>o carry th<span class="_ _0"></span>e rename o<span class="_ _0"></span>peration forward<span class="_ _0"></span> into triggers<span class="_ _0"></span> and views<span class="_ _0"></span> that refe<span class="_ _0"></span>rence the </div><div class="t m0 x2 h8 y5d ff3 fs3 fc0 sc0 ls0 ws0">renamed table. Th<span class="_ _0"></span>is is consid<span class="_ _0"></span>ered an imp<span class="_ _0"></span>rovement<span class="_ _0"></span>. Applications tha<span class="_ _0"></span>t depend <span class="_ _0"></span>on the older<span class="_ _0"></span> (and argua<span class="_ _0"></span>bly buggy)<span class="_ _0"></span> behavio<span class="_ _0"></span>r </div><div class="t m0 x2 h8 y5e ff3 fs3 fc0 sc0 ls0 ws0">can use the <span class="fc1">PR<span class="_ _0"></span>AGMA legacy<span class="_ _0"></span>_alter_table=<span class="_ _0"></span>ON<span class="fc0"> statement <span class="_ _0"></span>or the <span class="fc1">S<span class="_ _0"></span>QLITE_DBCONF<span class="_ _0"></span>IG_LEGA<span class="_ _0"></span>CY_ALTER_T<span class="_ _0"></span>ABLE<span class="fc0"> configuratio<span class="_ _0"></span>n </span></span></span></span></div><div class="t m0 x2 h8 y5f ff3 fs3 fc0 sc0 ls0 ws0">parameter on <span class="fc1">sqli<span class="_ _0"></span>te3_db_conf<span class="_ _0"></span>ig()<span class="fc0"> interface to<span class="_ _0"></span> make ALTE<span class="_ _0"></span>R TABLE RENAME<span class="_ _0"></span> behave as<span class="_ _0"></span> it did prior to<span class="_ _0"></span> version 3.2<span class="_ _0"></span>5.0. </span></span></div><div class="t m0 x2 h3 y60 ff3 fs1 fc0 sc0 ls0 ws0">Beginning with release 3.25.0 (2018<span class="_ _1"></span>-<span class="ls6">09</span>-15), references to the table within trigger bodies and view </div><div class="t m0 x2 h3 y61 ff3 fs1 fc0 sc0 ls0 ws0">definitions are also renamed. </div><div class="t m0 x2 h3 y62 ff3 fs1 fc0 sc0 ls0 ws0">Prior to version 3.26.0 (2018-<span class="ls7">12</span>-01), FOREIGN KEY references to a table that is renamed were only edited if </div><div class="t m0 x2 h3 y63 ff3 fs1 fc0 sc0 ls0 ws0">the <span class="fc1">PRAGMA foreign_keys=ON</span>, or in other words if <span class="fc1">foreign key constraints</span> were begin enforced. With </div><div class="t m0 x2 h3 y64 ff3 fs1 fc1 sc0 ls0 ws0">PRAGMA foreign_keys=OFF<span class="fc0">, FOREIGN KEY constraints would not be changed when the table that the foreign </span></div><div class="t m0 x2 h3 y65 ff3 fs1 fc0 sc0 ls0 ws0">key referred to (the "<span class="fc1">parent table</span>") was renamed. Beginning with version 3.26.0, FOREIGN <span class="_ _0"></span>KEY constraints </div><div class="t m0 x2 h3 y66 ff3 fs1 fc0 sc0 ls0 ws0">are always converted when a table is renamed, unless the <span class="fc1">PRAGMA legacy_alter_table=ON<span class="_ _1"></span></span> setting is </div><div class="t m0 x2 h3 y67 ff3 fs1 fc0 sc0 ls0 ws0">engaged. The following table summaries the difference: </div></div><div class="c x2 y68 wb hb"><div class="t m0 x0 ha y69 ff2 fs3 fc0 sc0 ls0 ws0">PRAGMA forei<span class="_ _0"></span>gn_keys<span class="_ _0"></span><span class="fc2 sc0"> </span><span class="fc2 sc0"> </span></div></div><div class="c x1 y68 wc hb"><div class="t m0 x0 ha y69 ff2 fs3 fc0 sc0 ls0 ws0">PRAGMA legac<span class="_ _0"></span>y_alter_tabl<span class="_ _0"></span>e<span class="fc2 sc0"> </span><span class="fc2 sc0"> </span></div></div><div class="c x14 y68 wd hb"><div class="t m0 x0 ha y69 ff2 fs3 fc1 sc0 ls0 ws0">Parent Tabl<span class="_ _0"></span>e<span class="fc0"> references ar<span class="_ _0"></span>e updated<span class="_ _0"></span><span class="fc2 sc0"> </span><span class="fc2 sc0"> </span></span></div></div><div class="c x15 y68 we hb"><div class="t m0 x0 ha y69 ff2 fs3 fc0 sc0 ls0 ws0">SQLite versio<span class="_ _0"></span>n<span class="fc2 sc0"> </span><span class="fc2 sc0"> </span></div></div><div class="c x2 y6a wb hc"><div class="t m0 x16 h8 y6b ff3 fs3 fc0 sc0 ls0 ws0">Off </div></div><div class="c x1 y6a wc hc"><div class="t m0 x2 h8 y6b ff3 fs3 fc0 sc0 ls0 ws0">Off </div></div><div class="c x14 y6a wd hc"><div class="t m0 x17 h8 y6b ff3 fs3 fc0 sc0 ls8 ws0">No<span class="ls0"> </span></div></div><div class="c x15 y6a we hc"><div class="t m0 x18 h8 y6b ff3 fs3 fc0 sc0 ls0 ws0">< 3.26.0 </div></div><div class="c x2 y6c wb hc"><div class="t m0 x16 h8 y6b ff3 fs3 fc0 sc0 ls0 ws0">Off </div></div><div class="c x1 y6c wc hc"><div class="t m0 x2 h8 y6b ff3 fs3 fc0 sc0 ls0 ws0">Off </div></div><div class="c x14 y6c wd hc"><div class="t m0 x19 h8 y6b ff3 fs3 fc0 sc0 ls0 ws0">Yes </div></div><div class="c x15 y6c we hc"><div class="t m0 x1a h8 y6b ff3 fs3 fc0 sc0 ls0 ws0">>= 3.26.0 </div></div><div class="c x2 y6d wb hb"><div class="t m0 x16 h8 y69 ff3 fs3 fc0 sc0 ls9 ws0">On<span class="ls0"> </span></div></div><div class="c x1 y6d wc hb"><div class="t m0 x2 h8 y69 ff3 fs3 fc0 sc0 ls0 ws0">Off </div></div><div class="c x14 y6d wd hb"><div class="t m0 x19 h8 y69 ff3 fs3 fc0 sc0 ls0 ws0">Yes </div></div><div class="c x15 y6d we hb"><div class="t m0 x1b h8 y69 ff3 fs3 fc0 sc0 lsa ws0">all <span class="ls0"> </span></div></div><div class="c x2 y6e wb hc"><div class="t m0 x16 h8 y6b ff3 fs3 fc0 sc0 ls0 ws0">Off </div></div><div class="c x1 y6e wc hc"><div class="t m0 x2 h8 y6b ff3 fs3 fc0 sc0 ls9 ws0">On<span class="ls0"> </span></div></div><div class="c x14 y6e wd hc"><div class="t m0 x17 h8 y6b ff3 fs3 fc0 sc0 ls8 ws0">No<span class="ls0"> </span></div></div><div class="c x15 y6e we hc"><div class="t m0 x1b h8 y6b ff3 fs3 fc0 sc0 lsa ws0">all <span class="ls0"> </span></div></div><div class="c x2 y6f wb hb"><div class="t m0 x16 h8 y69 ff3 fs3 fc0 sc0 ls9 ws0">On<span class="ls0"> </span></div></div><div class="c x1 y6f wc hb"><div class="t m0 x2 h8 y69 ff3 fs3 fc0 sc0 ls9 ws0">On<span class="ls0"> </span></div></div><div class="c x14 y6f wd hb"><div class="t m0 x19 h8 y69 ff3 fs3 fc0 sc0 ls0 ws0">Yes </div></div><div class="c x15 y6f we hb"><div class="t m0 x1b h8 y69 ff3 fs3 fc0 sc0 lsa ws0">all <span class="ls0"> </span></div></div><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a></div><div class="pi" data-data='{"ctm":[1.568627,0.000000,0.000000,1.568627,0.000000,0.000000]}'></div></div>
<div id="pf5" class="pf w0 h0" data-page-no="5"><div class="pc pc5 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="https://csdnimg.cn/release/download_crawler_static/11975106/bg5.jpg"><div class="c x0 y1 w2 h0"><div class="t m0 x2 h3 y70 ff3 fs1 fc5 sc0 ls0 ws0">ALTER TABLE RENAME COLUMN </div><div class="t m0 x2 h3 y71 ff3 fs1 fc0 sc0 ls0 ws0">The RENAME COLUMN TO syntax changes the <span class="_ _4"></span>column-name<span class="_ _5"></span> of table <span class="_ _5"></span>t<span class="_ _1"></span>able-name<span class="_ _5"></span> into <span class="_ _4"></span>new-column-name<span class="_ _5"></span><span class="ls1">. </span></div><div class="t m0 x2 h3 y72 ff3 fs1 fc0 sc0 ls0 ws0">The column name is changed both within the table definition itself and also within all indexes, triggers, and </div><div class="t m0 x2 h3 y73 ff3 fs1 fc0 sc0 ls0 ws0">views that reference the column. If the column name change would result in a semantic ambiguity in a </div><div class="t m0 x2 h3 y74 ff3 fs1 fc0 sc0 ls0 ws0">trigger or view, then the RENAME COLUMN fails with an error and no ch<span class="_ _0"></span>anges are applied. <span class="_ _1"></span> </div><div class="t m0 x2 h3 y75 ff3 fs1 fc5 sc0 ls0 ws0">ALTER TABLE ADD COLUMN </div><div class="t m0 x2 h3 y76 ff3 fs1 fc0 sc0 ls0 ws0">The ADD COLUMN syntax is used to add a new column to an ex<span class="_ _0"></span>isting table<span class="_ _1"></span>. The new column is always </div><div class="t m0 x2 h3 y77 ff3 fs1 fc0 sc0 ls0 ws0">appended to the end of the list of existing columns. The <span class="fc1">column-<span class="lsb">def</span></span> rule defines the characteristics of the </div><div class="t m0 x2 h3 y78 ff3 fs1 fc0 sc0 ls0 ws0">new column. The new column may take any of <span class="_ _0"></span>the forms permissible in a <span class="_ _1"></span><span class="fc1">CREATE TABLE</span> statement, with the </div><div class="t m0 x2 h3 y79 ff3 fs1 fc0 sc0 ls0 ws0">following restrictions: </div><div class="t m0 x19 h5 y7a ff7 fs2 fc0 sc0 ls0 ws0">•<span class="ff5"> <span class="_ _3"> </span><span class="ff3 fs3">The column ma<span class="_ _0"></span>y not ha<span class="_ _0"></span>ve a PR<span class="_ _0"></span>IMARY KEY or UN<span class="_ _0"></span>IQUE const<span class="_ _0"></span>raint. </span></span></div><div class="t m0 x19 h5 y7b ff7 fs2 fc0 sc0 ls0 ws0">•<span class="ff5"> <span class="_ _3"> </span><span class="ff3 fs3">The column ma<span class="_ _0"></span>y not ha<span class="_ _0"></span>ve a default value <span class="_ _0"></span>of CURRENT<span class="_ _0"></span>_TIME,<span class="_ _0"></span> CURRENT_DATE<span class="_ _0"></span>, CURRENT<span class="_ _0"></span>_TIMESTAM<span class="_ _0"></span>P, or <span class="_ _1"></span>an </span></span></div><div class="t m0 x11 h8 y7c ff3 fs3 fc0 sc0 ls0 ws0">expression in par<span class="_ _0"></span>entheses.<span class="_ _0"></span> </div><div class="t m0 x19 h5 y7d ff7 fs2 fc0 sc0 ls0 ws0">•<span class="ff5"> <span class="_ _3"> </span><span class="ff3 fs3">If a NOT NULL <span class="_ _0"></span>constrain<span class="_ _0"></span>t is specified,<span class="_ _0"></span> then the c<span class="_ _0"></span>olumn must<span class="_ _0"></span> have a de<span class="_ _0"></span>fault value oth<span class="_ _0"></span>er than NULL<span class="_ _0"></span>. </span></span></div><div class="t m0 x19 h5 y7e ff7 fs2 fc0 sc0 ls0 ws0">•<span class="ff5"> <span class="_ _3"> </span><span class="ff3 fs3">If <span class="fc1">foreign key <span class="_ _0"></span>constraint<span class="_ _0"></span>s<span class="fc0"> are </span>enabled<span class="fc0"> and a<span class="_ _0"></span> column with<span class="_ _0"></span> a <span class="fc1">REFERENCES<span class="_ _0"></span> clause<span class="fc0"> is added,<span class="_ _0"></span> the colu<span class="_ _0"></span>mn must ha<span class="_ _0"></span>ve a </span></span></span></span></span></span></div><div class="t m0 x11 h8 y7f ff3 fs3 fc0 sc0 ls0 ws0">default value o<span class="_ _0"></span>f NULL. </div><div class="t m0 x2 h3 y80 ff3 fs1 fc0 sc0 ls0 ws0">Note also that when adding a <span class="fc1">CHECK constraint</span>, the CHECK constraint is not tested against preexisting rows </div><div class="t m0 x2 h3 y81 ff3 fs1 fc0 sc0 ls0 ws0">of the table. This can result in a table that contains data that is in violation of the CHECK constraint. Future </div><div class="t m0 x2 h3 y82 ff3 fs1 fc0 sc0 ls0 ws0">versions of SQLite might change to validate CHECK constraints as they are a<span class="_ _0"></span>dded.<span class="_ _1"></span> </div><div class="t m0 x2 h3 y83 ff3 fs1 fc0 sc0 ls0 ws0">The ALTER TABLE command works <span class="_ _0"></span>by modifying the SQL text of the schema stored in the <span class="_ _1"></span><span class="fc1">sqlite_master </span></div><div class="t m0 x2 h3 y84 ff3 fs1 fc1 sc0 ls0 ws0">table<span class="fc0">. No changes are made to table content. Because of this, the <span class="_ _0"></span>execution time of the ALTER TABLE </span></div><div class="t m0 x2 h3 y85 ff3 fs1 fc0 sc0 ls0 ws0">command is independent of the amount of <span class="_ _0"></span>data in the table. The ALTER TABLE command runs as quickly on </div><div class="t m0 x2 h3 y86 ff3 fs1 fc0 sc0 ls0 ws0">a table with 10 million rows as it does on a table with 1 row. </div><div class="t m0 x2 h3 y87 ff3 fs1 fc0 sc0 ls0 ws0">After ADD COLUMN has been run on a database, that database will not be readable by SQLite version <span class="_ _0"></span>3.1.3 </div><div class="t m0 x2 h3 y88 ff3 fs1 fc0 sc0 ls0 ws0">(2005-<span class="ls6">02<span class="_ _1"></span></span>-20) and earlier. </div><div class="t m0 x2 h3 y89 ff3 fs1 fc5 sc0 ls0 ws0">Making Other Kinds Of Table Schema Changes </div><div class="t m0 x2 h3 y8a ff3 fs1 fc0 sc0 ls0 ws0">The only schema altering commands directly supported by SQLite are the "<span class="fc1">rename table</span>", "<span class="fc1">rename column</span>", </div><div class="t m0 x2 h3 y8b ff3 fs1 fc0 sc0 ls0 ws0">and "<span class="fc1">add column</span>" commands shown above. However, applications can make other arbitrary changes to the </div><div class="t m0 x2 h3 y8c ff3 fs1 fc0 sc0 ls0 ws0">format of a table using a simple sequence of operations.<span class="_ _0"></span> The steps to make arbitrary changes to the schema </div><div class="t m0 x2 h3 y8d ff3 fs1 fc0 sc0 ls0 ws0">design of some table X are as follows: </div><div class="t m0 x19 hd y8e ff3 fs1 fc0 sc0 ls7 ws0">1.<span class="ff5 ls0"> <span class="_ _6"> </span><span class="ff3">If foreign key constraints are enabled, disable them using <span class="fc1">PRAGMA foreign_keys=OFF</span><span class="ls1">. </span> </span></span></div><div class="t m0 x19 hd y8f ff3 fs1 fc0 sc0 ls7 ws0">2.<span class="ff5 ls0"> <span class="_ _6"> </span><span class="ff3">Start a transaction. </span></span></div><div class="t m0 x19 hd y90 ff3 fs1 fc0 sc0 ls7 ws0">3.<span class="ff5 ls0"> <span class="_ _6"> </span><span class="ff3">Remember the format of all indexes, triggers, and v<span class="_ _0"></span>iews associated with table<span class="_ _1"></span> X. This information will </span></span></div><div class="t m0 x11 h3 y91 ff3 fs1 fc0 sc0 ls0 ws0">be needed in step 8 below. One w<span class="_ _0"></span>ay to do this is to run a query like the following: SELECT type, sql </div><div class="t m0 x11 h3 y92 ff3 fs1 fc0 sc0 ls0 ws0">FROM sqlite_master WHERE tbl_name='X'. </div><div class="t m0 x19 hd y93 ff3 fs1 fc0 sc0 ls7 ws0">4.<span class="ff5 ls0"> <span class="_ _6"> </span><span class="ff3">Use <span class="fc1">CREATE TABLE</span> to construct a new table "new_X" that is in <span class="_ _0"></span>the desired revised format of table<span class="_ _1"></span> X. </span></span></div><div class="t m0 x11 h3 y94 ff3 fs1 fc0 sc0 ls0 ws0">Make sure that the name "new_X"<span class="_ _0"></span> does not collide with any existing table<span class="_ _1"></span> name, <span class="_ _0"></span>of course. <span class="_ _1"></span> </div><div class="t m0 x19 hd y95 ff3 fs1 fc0 sc0 ls7 ws0">5.<span class="ff5 ls0"> <span class="_ _6"> </span><span class="ff3">Transfer content from X into new_X using a statement like: INS<span class="_ _0"></span>ERT INTO new_X SELECT ... FROM <span class="_ _1"></span><span class="lsc">X. </span> </span></span></div><div class="t m0 x19 hd y96 ff3 fs1 fc0 sc0 ls7 ws0">6.<span class="ff5 ls0"> <span class="_ _6"> </span><span class="ff3">Drop the old table X: <span class="fc1">DROP TABLE X</span><span class="ls1">. </span> </span></span></div><div class="t m0 x19 hd y97 ff3 fs1 fc0 sc0 ls7 ws0">7.<span class="ff5 ls0"> <span class="_ _6"> </span><span class="ff3">Change the name of n<span class="_ _0"></span>ew_X to X using: ALTER TABLE new_X RENAME TO X. <span class="_ _1"></span> </span></span></div><div class="t m0 x19 hd y98 ff3 fs1 fc0 sc0 ls7 ws0">8.<span class="ff5 ls0"> <span class="_ _6"> </span><span class="ff3">Use <span class="fc1">CREATE INDEX</span><span class="ls1">, </span><span class="fc1">CREATE TRIGGER</span>, and <span class="fc1">CREATE VIEW</span> to reconstruct indexes, triggers, and views </span></span></div><div class="t m0 x11 h3 y99 ff3 fs1 fc0 sc0 ls0 ws0">associated with table X. Perhaps use the old format of the triggers, indexes, and views saved from </div><div class="t m0 x11 h3 y9a ff3 fs1 fc0 sc0 ls0 ws0">step 3 above as a guide, making changes as appropriate for the alteration. </div><div class="t m0 x19 hd y9b ff3 fs1 fc0 sc0 ls7 ws0">9.<span class="ff5 ls0"> <span class="_ _6"> </span><span class="ff3">If any views refer to table X in a way that is affected by the schema <span class="_ _0"></span>change, then drop those views </span></span></div><div class="t m0 x11 h3 y9c ff3 fs1 fc0 sc0 ls0 ws0">using <span class="fc1">DROP VIEW</span> and recreate them with whatever ch<span class="_ _0"></span>anges are necessary to accommodate the </div><div class="t m0 x11 h3 y9d ff3 fs1 fc0 sc0 ls0 ws0">schema change using <span class="fc1">CREATE VIEW</span><span class="ls1">. </span> </div><div class="t m0 x19 hd y9e ff3 fs1 fc0 sc0 ls7 ws0">10.<span class="ff5 ls0"> <span class="_ _5"></span><span class="ff3">If foreign key constraints were originally enabled then run <span class="fc1">PRAGMA foreign_key_check</span> to verify that </span></span></div><div class="t m0 x11 h3 y9f ff3 fs1 fc0 sc0 ls0 ws0">the schema change did not break any foreign key constraints. </div></div><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a><a class="l" rel='nofollow' onclick='return false;'><div class="d m1"></div></a></div><div class="pi" data-data='{"ctm":[1.568627,0.000000,0.000000,1.568627,0.000000,0.000000]}'></div></div>
