SQLite 语法简单汇总
SQLite 是一个软件库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。。它是一个零配置的数据库,这意味着与其他数据库一样,您不需要在系统中配置。
[TOC]
为什么要用 SQLite?
- 不需要一个单独的服务器进程或操作的系统(无服务器的)。
- SQLite 不需要配置,这意味着不需要安装或管理。
- 一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。
- SQLite 是非常小的,是轻量级的,完全配置时小于 400KiB,省略可选功能配置时小于250KiB。
- SQLite 是自给自足的,这意味着不需要任何外部的依赖。
- SQLite 事务是完全兼容 ACID 的,允许从多个进程或线程安全访问。
- SQLite 支持 SQL92(SQL2)标准的大多数查询语言的功能。
- SQLite 使用 ANSI-C 编写的,并提供了简单和易于使用的 API。
- SQLite 可在 UNIX(Linux, Mac OS-X, Android, iOS)和 Windows(Win32, WinCE, WinRT)中运行。
- 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 语句的中间,但它们不能嵌套。
- 注释以两个连续的 “-“ 字符(ASCII 0x2d)开始,并扩展至下一个换行符(ASCII 0x0a)或直到输入结束,以先到者为准
- 也可以使用 C 风格的注释,以 “/” 开始,并扩展至下一个 “/” 字符对或直到输入结束,以先到者为准。SQLite的注释可以跨越多行。
2 SQLite 语句格式
- 所有的 SQLite 语句可以以任何关键字开始,如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等
- 所有的语句以分号(;)结束
- 虽然不区分大小写,但为了增强可读性,最好将关键字大写,变量名小写
3 数据定义语句 Table
- SQLite CREATE TABLE 语句:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
- SQLite CREATE VIRTUAL TABLE 语句:
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );
- SQLite ALTER TABLE 语句:
ALTER TABLE table_name ADD COLUMN column_def...;
- SQLite ALTER TABLE 语句(Rename):
ALTER TABLE table_name RENAME TO new_table_name;
- SQLite DROP TABLE 语句:
DROP TABLE database_name.table_name;
4 数据定义的约束
约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。约束可以是列级或表级。列级约束仅适用于列,表级约束被应用到整个表
- NOT NULL 约束:确保某列不能有 NULL 值。
- DEFAULT 约束:当某列没有指定值时,为该列提供默认值。
- UNIQUE 约束:确保某列中的所有值是不同的。
- PRIMARY Key 约束:唯一标识数据库表中的各行/记录。
- CHECK 约束:CHECK 约束确保某列中的所有值满足一定条件。
```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;
- SQLite MIN 函数
```sql lite SELECT min(salary) FROM COMPANY;
- SQLite AVG 函数
```sql lite
SELECT avg(salary) FROM COMPANY;
- SQLite SUM 函数
```sql lite SELECT sum(salary) FROM COMPANY;
- SQLite RANDOM 函数
```sql lite
SELECT random() AS Random;
- SQLite ABS 函数
```sql lite SELECT abs(5), abs(-15), abs(NULL), abs(0), abs(“ABC”);
- SQLite UPPER 函数
```sql lite
SELECT upper(name) FROM COMPANY;
- SQLite LOWER 函数
```sql lite SELECT lower(name) FROM COMPANY;
- SQLite LENGTH 函数
```sql lite
SELECT name, length(name) FROM COMPANY;
- SQLite sqlite_version 函数
```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]
- SQLite 别名
- 您可以暂时把表或列重命名为另一个名字,这被称为别名。使用表别名是指在一个特定的 SQLite 语句中重命名表。重命名是临时的改变,在数据库中实际的表的名称不会改变。
```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 附加/分离数据库
- SQLite ATTACH DATABASE 语句:
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
- SQLite DETACH DATABASE 语句:
DETACH DATABASE 'Alias-Name';
11 效率提升 view/index/trigger
- SQLite CREATE VIEW 语句:
CREATE VIEW database_name.view_name AS
SELECT statement....;
- SQLite CREATE INDEX 语句:
CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );
- SQLite CREATE UNIQUE INDEX 语句:
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);
- SQLite CREATE TRIGGER 语句:
CREATE TRIGGER database_name.trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
stmt1;
stmt2;
....
END;
- SQLite DROP INDEX 语句:
DROP INDEX database_name.index_name;
- SQLite DROP VIEW 语句:
DROP VIEW view_name;
- SQLite DROP TRIGGER 语句:
DROP TRIGGER trigger_name
12 功能性语句
- SQLite ANALYZE 语句:
ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;
- SQLite COMMIT 语句:
COMMIT;
- SQLite COMMIT TRANSACTION 语句:
COMMIT;
- SQLite EXPLAIN 语句:
EXPLAIN INSERT statement...;
or
EXPLAIN QUERY PLAN SELECT statement...;
- SQLite PRAGMA 语句:
PRAGMA pragma_name;
For example:
PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);
- SQLite RELEASE SAVEPOINT 语句:
RELEASE savepoint_name;
- SQLite REINDEX 语句:
REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;
- SQLite ROLLBACK 语句:
ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;
- SQLite SAVEPOINT 语句:
SAVEPOINT savepoint_name;
- SQLite VACUUM 语句:
VACUUM;
- SQLite BEGIN TRANSACTION 语句:
BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;