pNQXB25N

1Managing databases
2Attach another database to the current database connection:
3
4
5ATTACH DATABASE file_name AS database_name;
61
7ATTACH DATABASE file_name AS database_name;
8Optimize the database:
9
10
11VACUUM
121
13VACUUM
14Managing Tables
15Create a new table:
16
17
18CREATE TABLE [IF NOT EXISTS] table(
19   primary_key INTEGER PRIMARY KEY,
20   column_name type NOT NULL,
21   column_name type NULL,
22   ...
23);
241
252
263
274
285
296
30CREATE TABLE [IF NOT EXISTS] table(
31   primary_key INTEGER PRIMARY KEY,
32   column_name type NOT NULL,
33   column_name type NULL,
34   ...
35);
36Rename a table:
37
38
39ALTER TABLE table_name RENAME TO new_name;
401
41ALTER TABLE table_name RENAME TO new_name;
42Add a new column to a table:
43
44
45ALTER TABLE table ADD COLUMN column_definition;
461
47ALTER TABLE table ADD COLUMN column_definition;
48Drop an existing column in a table:
49
50
51ALTER TABLE table DROP COLUMN column_name;
521
53ALTER TABLE table DROP COLUMN column_name;
54Drop a table and its data:
55
56
57DROP TABLE [IF EXISTS] table_name;
581
59DROP TABLE [IF EXISTS] table_name;
60Managing indexes
61Creating an index
62
63
64CREATE [UNIQUE] INDEX index_name
65ON table_name (c1,c2,...)
661
672
68CREATE [UNIQUE] INDEX index_name
69ON table_name (c1,c2,...)
70Delete an index:
71
72
73DROP INDEX index_name;
741
75DROP INDEX index_name;
76Create an expression index:
77
78
79CREATE INDEX index_name ON table_name(expression);
801
81CREATE INDEX index_name ON table_name(expression);
82Querying Data
83Query all data from a table
84
85
86SELECT * FROM table_name;
871
88SELECT * FROM table_name;
89Query data from the specified column of a table:
90
91
92SELECT c1, c2
93FROM table_name;
941
952
96SELECT c1, c2
97FROM table_name;
98Query unique rows
99
100
101SELECT DISTINCT (c1)
102FROM table_name;
1031
1042
105SELECT DISTINCT (c1)
106FROM table_name;
107Query rows that match a condition using a WHERE clause.
108
109
110SELECT *
111FROM table_name
112WHERE condition;
1131
1142
1153
116SELECT *
117FROM table_name
118WHERE condition;
119Rename column in the query’s output:
120
121
122SELECT c1 AS new_name
123FROM table_name;
1241
1252
126SELECT c1 AS new_name
127FROM table_name;
128Query data from multiple tables using inner join, left join
129
130
131SELECT * 
132FROM table_name_1
133INNER JOIN table_name_2 ON condition;
1341
1352
1363
137SELECT * 
138FROM table_name_1
139INNER JOIN table_name_2 ON condition;
140
141SELECT * 
142FROM table_name_1
143LEFT JOIN table_name_2 ON condition;
1441
1452
1463
147SELECT * 
148FROM table_name_1
149LEFT JOIN table_name_2 ON condition;
150Count rows returned by a query:
151
152
153SELECT COUNT (*)
154FROM table_name;
1551
1562
157SELECT COUNT (*)
158FROM table_name;
159Sort rows using ORDER BY clause:
160
161
162SELECT c1, c2
163FROM table_name
164ORDER BY c1 ASC [DESC], c2 ASC [DESC],...;
1651
1662
1673
168SELECT c1, c2
169FROM table_name
170ORDER BY c1 ASC [DESC], c2 ASC [DESC],...;
171Group rows using GROUP BY clause.
172
173
174SELECT *
175FROM table_name
176GROUP BY c1, c2, ...;
1771
1782
1793
180SELECT *
181FROM table_name
182GROUP BY c1, c2, ...;
183Filter group of rows using HAVING clause.
184
185
186SELECT c1, aggregate(c2)
187FROM table_name
188GROUP BY c1
189HAVING condition;
1901
1912
1923
1934
194SELECT c1, aggregate(c2)
195FROM table_name
196GROUP BY c1
197HAVING condition;
198Changing Data
199Insert a row into a table:
200
201
202INSERT INTO table_name(column1,column2,...)
203VALUES(value_1,value_2,...);
2041
2052
206INSERT INTO table_name(column1,column2,...)
207VALUES(value_1,value_2,...);
208Insert multiple rows into a table in a single statement:
209
210
211INSERT INTO table_name(column1,column2,...)
212VALUES(value_1,value_2,...),
213      (value_1,value_2,...),
214      (value_1,value_2,...)...
2151
2162
2173
2184
219INSERT INTO table_name(column1,column2,...)
220VALUES(value_1,value_2,...),
221      (value_1,value_2,...),
222      (value_1,value_2,...)...
223Update all rows in a table:
224
225
226UPDATE table_name
227SET c1 = v1,
228    ...
2291
2302
2313
232UPDATE table_name
233SET c1 = v1,
234    ...
235Update rows that match with a condition:
236
237
238UPDATE table_name
239SET c1 = v1,
240    ...
241WHERE condition;
2421
2432
2443
2454
246UPDATE table_name
247SET c1 = v1,
248    ...
249WHERE condition;
250Delete all rows in a table
251
252
253DELETE FROM table;
2541
255DELETE FROM table;
256Delete rows specified by a condition:
257
258
259DELETE FROM table
260WHERE condition;
2611
2622
263DELETE FROM table
264WHERE condition;
265Search
266Search using LIKE operator:
267
268
269SELECT * FROM table
270WHERE column LIKE '%value%'
2711
2722
273SELECT * FROM table
274WHERE column LIKE '%value%'
275Search using full-text search:
276
277
278SELECT * 
279FROM table
280WHERE table MATCH 'search_query';
2811
2822
2833
284SELECT * 
285FROM table
286WHERE table MATCH 'search_query';