SQLite基础（一）

2019-08-06 本文已影响0人 Showdy

什么是 SQLite？

SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库一样，您不需要在系统中配置。

就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。

为什么要用 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 中，SQL92 不支持的特性如下所示：

特性	描述
RIGHT OUTER JOIN	只实现了 LEFT OUTER JOIN。
FULL OUTER JOIN	只实现了 LEFT OUTER JOIN。
ALTER TABLE	支持 RENAME TABLE 和 ALTER TABLE 的 ADD COLUMN variants 命令，不支持 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT。
Trigger 支持	支持 FOR EACH ROW 触发器，但不支持 FOR EACH STATEMENT 触发器。
VIEWs	在 SQLite 中，视图是只读的。您不可以在视图上执行 DELETE、INSERT 或 UPDATE 语句。
GRANT 和 REVOKE	可以应用的唯一的访问权限是底层操作系统的正常文件访问权限。

SQLite 命令

与关系数据库进行交互的标准 SQLite 命令类似于 SQL。命令包括 CREATE、SELECT、INSERT、UPDATE、DELETE 和 DROP。这些命令基于它们的操作性质可分为以下几种：

DDL-数据定义语言
- CREATE : 创建一个新的表，一个表的视图，或者数据库中的其他对象。
- ALTER: 修改数据库中的某个已有的数据库对象，比如一个表。
- DROP: 删除整个表，或者表的视图，或者数据库中的其他对象。
DML: 数据操作性语言
- INSERT:创建一条记录。
- UPDATE:修改记录。
- DELETE: 删除记录。
DQL: 数据查询语言
- SELECT : 从一个或多个表中检索某些记录。

SQLite 数据类型

存储类	描述
NULL	值是一个 NULL 值。
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB	值是一个 blob 数据，完全根据它的输入存储。

SQLite 的存储类稍微比数据类型更普遍。INTEGER 存储类，例如，包含 6 种不同的不同长度的整数数据类型。

创建表

CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型。

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

sqlite> CREATE TABLE Company(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

sqlite> CREATE TABLE Department(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

删除表

DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。

DROP TABLE database_name.table_name;
sqlite> DROP TABLE Company;

Insert语句

INSERT INTO 语句用于向数据库的某个表中添加新的数据行。

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

sqlite> INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

INSERT INTO [COMPANY]
  VALUES (1, 'Paul', 32, 'California', 20000.00);

INSERT INTO [COMPANY]
  VALUES (2, 'Allen', 25, 'Texas', 15000.00);

INSERT INTO [COMPANY]
  VALUES (3, 'Teddy', 23, 'Norway', 20000.00);

INSERT INTO [COMPANY]
  VALUES (4, 'Mark', 25, 'Rich-Mond', 65000.00);

INSERT INTO [COMPANY]
  VALUES (5, 'David', 27, 'Texas', 85000.00);

INSERT INTO [COMPANY]
  VALUES (6, 'Kim', 22, 'South-Hall', 45000.00);

INSERT INTO [COMPANY]
  VALUES (7, 'James', 24, 'Houston', 10000.00);

表Company：

ID	NAME	AGE	ADDRESS	SALARY
1	Paul	32	California	20000.0
2	Allen	25	Texas	15000.0
3	Teddy	23	Norway	20000.0
4	Mark	25	Rich-Mond	65000.0
5	David	27	Texas	85000.0
6	Kim	22	South-Hall	45000.0
7	James	24	Houston	10000.0

Select 语句

SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集

SELECT column1, column2, columnN FROM table_name;

sqlite> SELECT * FROM Company;

Update语句

UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新。
可以使用 AND 或 OR 运算符来结合 N 个数量的条件。

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

sqlite> UPDATE Company SET ADDRESS = 'Texas' WHERE ID = 6;

Delete语句

- DELETE FROM table_name WHERE [condition];
- sqlite> DELETE FROM Compay WHERE ID=7;
- sqlite> DLETE FORM Company;

运算符

算术运算符
- +
- -
- *
- /
- %

比较运算符

==
=
!=
<>
>
<
>=
<=
!<
!>

sqlite>  SELECT * FROM COMPANY WHERE SALARY = 20000;
sqlite> SELECT * FROM COMPANY WHERE SALARY <> 20000;
sqlite> SELECT * FROM COMPANY WHERE SALARY >= 65000;

逻辑运算符

AND
BETWEEN
EXISTS
IN
NOTIN
LIKE
GLOB
NOT
OR
IS NULL
IS
IS NOT
||
UNIQUE

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
sqlite>  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
sqlite> SELECT AGE FROM COMPANY 
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
sqlite> SELECT * FROM COMPANY 
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

位运算符
- &
- |
- ~
- ^

Link子句

百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。
可以使用 AND 或 OR 运算符来结合 N 个数量的条件.

SELECT FROM table_name WHERE column LIKE 'xxx%' or '_xxx';
sqlite> SELECT * FROM COMPANY WHERE AGE  LIKE '2%';

Glob子句

星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。这些符号可以被组合使用。
您可以使用 AND 或 OR 运算符来结合 N 个数量的条件

SELECT FROM table_name WHERE column GLOB 'XXXX*';
sqlite> SELECT * FROM COMPANY WHERE AGE  GLOB '2*';

Limit 子句

SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。

SELECT column1, column2, columnN FROM table_name LIMIT [no of rows];
sqlite> SELECT * FROM Company Limit 6;

可能需要从一个特定的偏移开始提取记录。可以配合OFFSET使用：

SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num];
sqlite> SELCT * FROM Company LIMIT 3 OFFSET 2;

Order By 子句

SQLite 的 ORDER BY 子句是用来基于一个或多个列按升序（ASC）或降序(DESC)顺序排列数据。

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

sqlite> SELECT * FROM Company ORDER BY SALARY ASC;

Group By子句

SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。
在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

sqlite> SELECT NAME,SUM(SALARY) FROM Company GROUP BY NAME;
sqlite> SELECT NAME, SUM(SALARY) FROM Company GROUP BY NAME ORDER BY NAME DESC;

Having 子句

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。
WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

sqlite > SELECT * FROM COMPANY GROUP BY NAME HAVING COUNT(NAME) > 2;

Distinct关键字

DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

sqlite> SELECT DISTINCT NAME FROM Company;

参考：

RUNBOO.COM