2018 June 02 database-sqlite

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;

Copyright © 2018 Zhen Zhao. All rights reserved.

Buit with Jekyll and 3-Jekyll theme • Hosted on Github

Table of Contents