sqlite简单速查

June 28, 2023

database

文档：https://www.sqlite.org/docs.html

语法参见：https://www.sqlite.org/syntaxdiagrams.html

数据类型参见：

函数参见：https://www.sqlite.org/lang_corefunc.html

SQLite 支持：https://www.sqlite.org/fullsql.html

SQLite 不支持：https://www.sqlite.org/omitted.html

SQLite3 Cheat Sheet

from https://opensource.com/sites/default/files/gated-content/cheat_sheet_sqlite_0.pdf

SQLite is a public domain C-language library implementing a small, fast, self-contained, reliabile, and full-featured, SQL database engine.

Manipulating data

Create database

sql

> .open filename.db;

Create table and define fields

sql

> CREATE TABLE IF NOT EXISTS mytable (foo TEXT NOT NULL);

View tables in database

sql

> .tables

Insert data into a table

sql

> INSERT INTO mytable (foo) VALUES ('aaa'), ('bbb'), ('ccc');

View table schema

sql

> .schema mytable

Add a new column to mytable

sql

> ALTER TABLE mytable ADD bar INTEGER;

Update data in a table

sql

> UPDATE mytable SET bar=123 WHERE foo='aaa';

Joins

Display an inner join

sql

> SELECT * FROM mytable
→ INNER JOIN othertable
→ ON mytable.rowid=othertable.foo;

Display a left join

sql

> SELECT * FROM mytable LEFT JOIN
→ ON mytable.id=othertable.foo;

Display a cross join

sql

> SELECT * FROM mytable
→ CROSS JOIN othertable;

Data types & Some SQLite functions

Data types		Some SQLite functions
TEXT	Text data	abs()	Absolute value
INTEGER	Whole number	max() min()	Maximum and minimum values
REAL	Fleating point number	upper() lower()	Convert case of string
BLOB	Binary data	length()	Length of string
NULL	Null value	random()	(Pseudo) random integer

Select

Display all data

sql

> SELECT * FROM mytable;

Display data of the third row

sql

> SELECT * FROM mytable
→ WHERE rowid=3;

Display foo and bar columns

sql

> SELECT foo, bar FROM mytable;

Display first 10 results

sql

> SELECT * FROM mytable LIMIT 10;

Sort by column foo

sql

> SELECT * FROM mytable ORDER BY foo;

Views

Create a new view

sql

> CREATE VIEW myview AS
→ SELECT foo FROM mytable
→ WHERE example > 10;

Show existing views

sql

> .tables

Display data with a view

sql

> SELECT * FROM myview;

Delete (drop) a view

sql

> DROP VIEW myview;

Column constraints

Set default text for a field

sql

DEFAULT 'default text'

Enforce unique value

sql

UNIQUE

Designate a column as a unique identifier

sql

PRIMARY KEY

sql

> CREATE TABLE mytable (Id INTEGER PRIMARY KEY);

Pointer to a primary key of a different table

sql

FOREIGN KEY

Impose a condition for validation

sql

CHECK

sql

> CREATE TABLE mytable (CHECK(condition> 0), bar TEXT);

Prevent NULL values

sql

NOT NULL

SQLite Cheat Sheet

from https://raw.githubusercontent.com/Mehedi61/SQLite-Cheat-Sheet/master/README.md

Creating Database:

sql

sqlite3 database_name.db

Managing databases

Attach another database to the current database connection:

sql

ATTACH DATABASE file_name AS database_name;

List names and files of attached databases:

sql

.databases

Optimize the database:

sql

VACCUM

Managing Tables

Create a new table:

sql

CREATE TABLE [IF NOT EXISTS] table(
   primary_key INTEGER PRIMARY KEY,
   column_name type NOT NULL,
   column_name type NULL,
   ...
);

Rename a table:

sql

ALTER TABLE table_name RENAME TO new_name;

Add a new column to a table:

sql

ALTER TABLE table ADD COLUMN column_definition;

Drop an existing column in a table:

sql

ALTER TABLE table DROP COLUMN column_name;

Drop a table and its data:

sql

DROP TABLE [IF EXISTS] table_name;

List of tables:

sql

.tables

Managing Indexes

Creating an index:

sql

CREATE [UNIQUE] INDEX index_name
ON table_name (c1,c2,...)

Delete an index:

sql

DROP INDEX index_name;

Create an expression index:

sql

CREATE INDEX index_name ON table_name(expression);

Querying Data

Query all data from a table:

sql

SELECT * FROM table_name;

Query data from the specified column of a table:

sql

SELECT c1, c2
FROM table_name;

Query unique rows:

sql

SELECT DISTINCT (c1)
FROM table_name;

Query rows that match a condition using a WHERE clause:

sql

SELECT *
FROM table_name
WHERE condition;

Rename column in the query's output:

sql

SELECT c1 AS new_name
FROM table_name;

Query data from multiple tables using inner join, left join:

sql

SELECT *
FROM table_name_1
INNER JOIN table_name_2 ON condition;

sql

SELECT *
FROM table_name_1
LEFT JOIN table_name_2 ON condition;

Count rows returned by a query:

sql

SELECT COUNT (*)
FROM table_name;

Sort rows using ORDER BY clause:

sql

SELECT c1, c2
FROM table_name
ORDER BY c1 ASC [DESC], c2 ASC [DESC],...;

Group rows using GROUP BY clause:

sql

SELECT *
FROM table_name
GROUP BY c1, c2, ...;

sql

SELECT c1, aggregate(c2)
FROM table_name
GROUP BY c1
HAVING condition;

Changing Data

Insert a row into a table:

sql

INSERT INTO table_name(column1,column2,...)
VALUES(value_1,value_2,...);

Insert multiple rows into a table in a single statement:

sql

INSERT INTO table_name(column1,column2,...)
VALUES(value_1,value_2,...),
      (value_1,value_2,...),
      (value_1,value_2,...)...

Update all rows in a table:

sql

UPDATE table_name
SET c1 = v1,
    ...

Update rows that match with a condition:

sql

UPDATE table_name
SET c1 = v1,
    ...
WHERE condition;

Delete all rows in a table:

sql

DELETE FROM table;

Delete rows specified by a condition:

sql

DELETE FROM table
WHERE condition;

Search

Search using LIKE operator:

sql

SELECT * FROM table
WHERE column LIKE '%value%';

Search using full-text search:

sql

SELECT *
FROM table
WHERE table MATCH 'search_query';

←Back to home