项目作者: Mehedi61

项目描述 :
original source:
高级语言:
项目地址: git://github.com/Mehedi61/SQLite-Cheat-Sheet.git
创建时间: 2018-04-19T09:50:59Z
项目社区:https://github.com/Mehedi61/SQLite-Cheat-Sheet

开源协议:

下载


SQLite Cheat Sheet

I was looking for a SQLite cheat sheet. Then I found this.
I have pushed the cheat sheet to this repository, so that I can use it easily.
I’ll update it as my needs.

SQLite cheat sheet lists the most common SQLite statements that help you work with SQLite more quickly and effectively.

Creating Database:

  1. sqlite3 database_name.db

Managing databases

Attach another database to the current database connection:
  1. ATTACH DATABASE file_name AS database_name;
List names and files of attached databases:
  1. .databases
Optimize the database:
  1. VACCUM

Managing Tables

Create a new table:
  1. CREATE TABLE [IF NOT EXISTS] table(
  2. primary_key INTEGER PRIMARY KEY,
  3. column_name type NOT NULL,
  4. column_name type NULL,
  5. ...
  6. );
Rename a table:
  1. ALTER TABLE table_name RENAME TO new_name;
Add a new column to a table:
  1. ALTER TABLE table ADD COLUMN column_definition;
Drop an existing column in a table:
  1. ALTER TABLE table DROP COLUMN column_name;
Drop a table and its data:
  1. DROP TABLE [IF EXISTS] table_name;
List of tables:
  1. .tables

Managing Indexes

Creating an index:
  1. CREATE [UNIQUE] INDEX index_name
  2. ON table_name (c1,c2,...)
Delete an index:
  1. DROP INDEX index_name;
Create an expression index:
  1. CREATE INDEX index_name ON table_name(expression);

Querying Data

Query all data from a table:
  1. SELECT * FROM table_name;
Query data from the specified column of a table:
  1. SELECT c1, c2
  2. FROM table_name;
Query unique rows:
  1. SELECT DISTINCT (c1)
  2. FROM table_name;
Query rows that match a condition using a WHERE clause:
  1. SELECT *
  2. FROM table_name
  3. WHERE condition;
Rename column in the query’s output:
  1. SELECT c1 AS new_name
  2. FROM table_name;
Query data from multiple tables using inner join, left join:
  1. SELECT *
  2. FROM table_name_1
  3. INNER JOIN table_name_2 ON condition;
  1. SELECT *
  2. FROM table_name_1
  3. LEFT JOIN table_name_2 ON condition;
Count rows returned by a query:
  1. SELECT COUNT (*)
  2. FROM table_name;
Sort rows using ORDER BY clause:
  1. SELECT c1, c2
  2. FROM table_name
  3. ORDER BY c1 ASC [DESC], c2 ASC [DESC],...;
Group rows using GROUP BY clause:
  1. SELECT *
  2. FROM table_name
  3. GROUP BY c1, c2, ...;
Filter group of rows using HAVING clause:
  1. SELECT c1, aggregate(c2)
  2. FROM table_name
  3. GROUP BY c1
  4. HAVING condition;

Changing Data

Insert a row into a table:
  1. INSERT INTO table_name(column1,column2,...)
  2. VALUES(value_1,value_2,...);
Insert multiple rows into a table in a single statement:
  1. INSERT INTO table_name(column1,column2,...)
  2. VALUES(value_1,value_2,...),
  3. (value_1,value_2,...),
  4. (value_1,value_2,...)...
Update all rows in a table:
  1. UPDATE table_name
  2. SET c1 = v1,
  3. ...
Update rows that match with a condition:
  1. UPDATE table_name
  2. SET c1 = v1,
  3. ...
  4. WHERE condition;
Delete all rows in a table:
  1. DELETE FROM table;
Delete rows specified by a condition:
  1. DELETE FROM table
  2. WHERE condition;
Search using LIKE operator:
  1. SELECT * FROM table
  2. WHERE column LIKE '%value%';
Search using full-text search:
  1. SELECT *
  2. FROM table
  3. WHERE table MATCH 'search_query';