SQLite 语法简单汇总

SQLite 是一个软件库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。。它是一个零配置的数据库,这意味着与其他数据库一样,您不需要在系统中配置。

[TOC]

为什么要用 SQLite?

SQLite 命令

SQLite可以支持数据库的常用操作,涵盖了其数据定义(DDL),数据操作(DML)和数据查询(DQL)。

DDL - 数据定义语言 (创建删除)

命令 描述
CREATE 创建一个新的表,一个表的视图,或者数据库中的其他对象。
ALTER 修改数据库中的某个已有的数据库对象,比如一个表。
DROP 删除整个表,或者表的视图,或者数据库中的其他对象。

DML - 数据操作语言 (增删改)

命令 描述
INSERT 创建一条记录。
UPDATE 修改记录。
DELETE 删除记录。

DQL - 数据查询语言(查)

命令 描述
SELECT 从一个或多个表中检索某些记录。

基本语法

QLite 是遵循一套独特的称为语法的规则和准则。 SQLite 是不区分大小写的,但也有一些命令是大小写敏感的,比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。

1 注释

SQLite 注释是附加的注释,可以在 SQLite 代码中添加注释以增加其可读性,他们可以出现在任何空白处,包括在表达式内和其他 SQL 语句的中间,但它们不能嵌套。

2 SQLite 语句格式

3 数据定义语句 Table

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );
ALTER TABLE table_name ADD COLUMN column_def...;
ALTER TABLE table_name RENAME TO new_table_name;
DROP TABLE database_name.table_name;

4 数据定义的约束

约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。约束可以是列级或表级。列级约束仅适用于列,表级约束被应用到整个表

```sql lite CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL UNIQUE, AGE INT NOT NULL CHECK(AGE > 0), ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );




### 5 数据操作之增加/删除/更新/查询

- SQLite INSERT INTO 语句:

INSERT INTO table_name( column1, column2….columnN) VALUES ( value1, value2….valueN);


- SQLite DELETE 语句:

DELETE FROM table_name WHERE {CONDITION};


- SQLite UPDATE 语句:

UPDATE table_name SET column1 = value1, column2 = value2….columnN=valueN [ WHERE CONDITION ];


- SQLite SELECT 语句:

SELECT column1, column2….columnN FROM table_name;




### 6 查询之函数修饰

- SQLite COUNT 子句:(用在属性上,返)

SELECT COUNT(column_name) FROM table_name WHERE CONDITION;


- SQLite DISTINCT 子句:

SELECT DISTINCT column1, column2….columnN FROM table_name;


- SQLite MAX 函数

```sql lite
SELECT max(salary) FROM COMPANY;

```sql lite SELECT min(salary) FROM COMPANY;


- SQLite AVG  函数

```sql lite
SELECT avg(salary) FROM COMPANY;

```sql lite SELECT sum(salary) FROM COMPANY;


- SQLite RANDOM 函数

```sql lite
SELECT random() AS Random;

```sql lite SELECT abs(5), abs(-15), abs(NULL), abs(0), abs(“ABC”);


- SQLite UPPER 函数

```sql lite
SELECT upper(name) FROM COMPANY;

```sql lite SELECT lower(name) FROM COMPANY;

- SQLite LENGTH 函数

```sql lite
SELECT name, length(name) FROM COMPANY;

```sql lite SELECT sqlite_version() AS ‘SQLite Version’;




### 7 查询之结合多个表/结果

- **SQLite Joins** 
  SQLite 的 **Joins** 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段。多用于FROM语句,链接多个表。SQL 定义了三种主要类型的连接:
  - **交叉连接 - CROSS JOIN**
    - 交叉连接(CROSS JOIN)把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列,则结果表有 **x*y** 列。(i.e. 罗列全部的组合)

    - ```sql
      SELECT ... FROM table1 CROSS JOIN table2 ...
      ```

  - **内连接 - INNER JOIN**

    - 内连接(INNER JOIN)根据连接谓词结合两个表(table1 和 table2)的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较,找到所有满足连接谓词的行的匹配对。当满足连接谓词时,A 和 B 行的每个匹配对的列值会合并成一个结果行。

    - 内连接(INNER JOIN)是最常见的连接类型,是默认的连接类型。INNER 关键字是可选的

    ```sql
    SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
    ```



    - 为了避免冗余,并保持较短的措辞,可以使用 **USING** 表达式声明内连接(INNER JOIN)条件
    
      ```sql
      SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
      ```
    
    - 自然连接(NATURAL JOIN)类似于 **JOIN...USING**,只是它会自动测试存在两个表中的每一列的值之间相等值
    
      ```sql
      SELECT ... FROM table1 NATURAL JOIN table2...
      ```

  - **外连接** - OUTER JOIN (SQLite只支持LEFT OUTER JOIN)

    - 外连接(OUTER JOIN)是内连接(INNER JOIN)的扩展。虽然 SQL 标准定义了三种类型的外连接:LEFT、RIGHT、FULL,但 SQLite 只支持 **左外连接(LEFT OUTER JOIN)**。
    - 外连接(OUTER JOIN)声明条件的方法与内连接(INNER JOIN)是相同的,使用 ON、USING 或 NATURAL 关键字来表达
    - 最初的结果表以相同的方式进行计算。一旦主连接计算完成,外连接(OUTER JOIN)将从一个或两个表中任何未连接的行合并进来,外连接的列使用 NULL 值,将它们附加到结果表中。

    ```sql
    /* 基本语法 */
    SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
    SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...
    /* 实例 */
    SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
            ON COMPANY.ID = DEPARTMENT.EMP_ID;
    ```

- **SQLite 集合操作** - Unions 子句

  - SQLite的 **UNION** 子句/运算符用于合并两个或多个 SELECT 语句的结果,不返回任何重复的行, 需要重复的结果可以用 **UNION ALL**。类似的集合操作有,UNION,INTERSEC,EXCEPT。
  - 注意:为了使用 UNION,每个 SELECT 被选择的列数必须是相同的,相同数目的列表达式,相同的数据类型,并确保它们有相同的顺序,但它们不必具有相同的长度。

```sql
/* 基本语法 */
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION [ALL]

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

```sql lite /* 基本语法 */ SELECT column1, column2…. FROM table_name AS alias_name WHERE [condition];

SELECT column_name AS alias_name FROM table_name WHERE [condition];

/* 实例 */ SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;

SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;

/* 别名的关键字 as 可以被省略,下面两个查询返回相同的结果 */ SELECT id AS identification, name AS nickname FROM company; SELECT id identification, name AS nickname FROM company;





### 8 查询之条件限制

- SQLite WHERE 子句:(设置条件)

SELECT column1, column2….columnN FROM table_name WHERE CONDITION;


- SQLite AND/OR 子句:(链接多个条件)

SELECT column1, column2….columnN FROM table_name WHERE CONDITION-1 {AND|OR} CONDITION-2;


- SQLite BETWEEN 子句:(制定值的范围)

SELECT column1, column2….columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2;


- SQLite EXISTS 子句:(制定值的值域)

SELECT column1, column2….columnN FROM table_name WHERE column_name EXISTS (SELECT * FROM table_name );



- SQLite IN 子句:

SELECT column1, column2….columnN FROM table_name WHERE column_name IN (val-1, val-2,…val-N);


- SQLite NOT IN 子句:

SELECT column1, column2….columnN FROM table_name WHERE column_name NOT IN (val-1, val-2,…val-N);


- SQLite Like 子句:(字符串操作)

SELECT column1, column2….columnN FROM table_name WHERE column_name LIKE { PATTERN };



- SQLite GLOB 子句:

SELECT column1, column2….columnN FROM table_name WHERE column_name GLOB { PATTERN };


### 9 查询之更多条件限制
- SQLite **GROUP BY** 子句:

SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name;


- SQLite **HAVING** 子句:

SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition);


- SQLite **ORDER BY** 子句:

SELECT column1, column2….columnN FROM table_name WHERE CONDITION ORDER BY column_name {ASC|DESC};


- SQLite **Limit** 子句
注意sqlite并不支持**TOP**,只用用Limit变向得实现

SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]

```sql
SELECT * FROM COMPANY LIMIT 6;
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

10 附加/分离数据库

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
DETACH DATABASE 'Alias-Name';

11 效率提升 view/index/trigger

CREATE VIEW database_name.view_name  AS
SELECT statement....;
CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);
CREATE TRIGGER database_name.trigger_name 
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN 
   stmt1; 
   stmt2;
   ....
END;
DROP INDEX database_name.index_name;
DROP VIEW view_name;
DROP TRIGGER trigger_name

12 功能性语句

ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;
COMMIT;
COMMIT;
EXPLAIN INSERT statement...;
or 
EXPLAIN QUERY PLAN SELECT statement...;
PRAGMA pragma_name;

For example:

PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);
RELEASE savepoint_name;
REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;
ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;
SAVEPOINT savepoint_name;
VACUUM;
BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;
Table of Contents