SQLite3 语法（一）

目录

SQLite3 (一)

一、 简介

1、 优点

  • 不需要一个单独的服务器进程或操作的系统（无服务器的）
  • 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）中运行

2、 常用命令

2.1 DDL - 数据定义语言

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

2.2 DML - 数据操作语言

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

2.3 DQL - 数据查询语言

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

二、 SQLite命令

命令描述
.backup ?DB? FILE备份 DB 数据库（默认是 "main"）到 FILE 文件。
.bail ON|OFF发生错误后停止。默认为 OFF。
.databases列出数据库的名称及其所依附的文件。
.dump ?TABLE?以 SQL 文本格式转储数据库。如果指定了 TABLE 表，则只转储匹配 LIKE 模式的 TABLE 表。
.echo ON|OFF开启或关闭 echo 命令。
.exit退出 SQLite 提示符。
.explain ON|OFF开启或关闭适合于 EXPLAIN 的输出模式。如果没有带参数，则为 EXPLAIN on，即开启 EXPLAIN。
.header(s) ON|OFF开启或关闭头部显示。
.help显示消息。
.import FILE TABLE导入来自 FILE 文件的数据到 TABLE 表中。
.indices ?TABLE?显示所有索引的名称。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表的索引。
.load FILE ?ENTRY?加载一个扩展库。
.log FILE|off开启或关闭日志。FILE 文件可以是 stderr（标准错误）/stdout（标准输出）。
.mode MODE设置输出模式，MODE 可以是下列之一：

  • csv 逗号分隔的值

  • column 左对齐的列

  • html HTML 的 <table> 代码

  • insert TABLE 表的 SQL 插入（insert）语句

  • line 每行一个值

  • list 由 .separator 字符串分隔的值

  • tabs 由 Tab 分隔的值

  • tcl TCL 列表元素

.nullvalue STRING在 NULL 值的地方输出 STRING 字符串。
.output FILENAME发送输出到 FILENAME 文件。
.output stdout发送输出到屏幕。
.print STRING...逐字地输出 STRING 字符串。
.prompt MAIN CONTINUE替换标准提示符。
.quit退出 SQLite 提示符。
.read FILENAME执行 FILENAME 文件中的 SQL。
.schema ?TABLE?显示 CREATE 语句。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表。
.separator STRING改变输出模式和 .import 所使用的分隔符。
.show显示各种设置的当前值。
.stats ON|OFF开启或关闭统计。
.tables ?PATTERN?列出匹配 LIKE 模式的表的名称。
.timeout MS尝试打开锁定的表 MS 毫秒。
.width NUM NUM为 "column" 模式设置列宽度。
.timer ON|OFF 开启或关闭 CPU 定时器。
.schema sqlite_master  -- 显示主表信息，里面存储了创建的所有的表的信息，其只读

三、 数据类型

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

同时，还有数据的亲和类型，大家可以通过：【https://www.runoob.com/sqlite/sqlite-data-types.html】来了解

四、 数据库操作

1、 创建

sqlite3 DatabaseName.db
or
.open test.db
--如果数据库存在，则打开，不存在则创建

2、 导出

sqlite3 testDB.db .dump > testDB.sql  --将sqlite里面的数据全部导出到SQL文件中
sqlite3 testDB.db < testDB.sql  --将SQL里面的数据全部导入到sqlite文件中

3、 附加

ATTACH DATABASE file_name AS database_name;  --将一个数据库附加到另一个数据库中

连接一中对于附加数据库的数据更新也同步回了原数据库的连接

SQLite的Attach DB机制是一种非常宽松的机制，对于附加数据库，可以同步和被同步

4、 分离

DETACH DATABASE 'Alias-Name';  --将名字为alias-name的数据库取消附加

五、 表操作

1、 创建表

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

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

2、 删除表

DROP TABLE database_name.table_name;

3、 修改表

ALTER TABLE database_name.table_name RENAME TO new_table_name;  --修改表名
ALTER TABLE database_name.table_name ADD COLUMN column_def...;  --添加列

Alter：不能进行其他的操作

4、 插入信息

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);  --给指定列添加值
or
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);  --如果为所有列添加值，则可以不用指定

使用另一张表，来填充这张表

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

5、 查找信息

SELECT column1, column2, columnN FROM table_name;  --指定列查找
SELECT * FROM table_name;  --查找所有列的信息

实例：

SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';  --列出数据库中创建的表，并且该表的名字为COMPANY的表的完整信息

6、 修改信息

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

六、 运算符

1、 算术运算符

运算符 描述 实例
+ 加法 - 把运算符两边的值相加 a + b 将得到 30
- 减法 - 左操作数减去右操作数 a - b 将得到 -10
* 乘法 - 把运算符两边的值相乘 a * b 将得到 200
/ 除法 - 左操作数除以右操作数 b / a 将得到 2
% 取模 - 左操作数除以右操作数后得到的余数 b % a will give 0

如：

.mode line --把内容在一行输出
SELECT 10 + 20  --计算10 + 20的值

2、 比较运算符

运算符 描述 实例
== 检查两个操作数的值是否相等，如果相等则条件为真。 (a == b) 不为真。
= 检查两个操作数的值是否相等，如果相等则条件为真。 (a = b) 不为真。
!= 检查两个操作数的值是否相等，如果不相等则条件为真。 (a != b) 为真。
<> 检查两个操作数的值是否相等，如果不相等则条件为真。 (a <> b) 为真。
> 检查左操作数的值是否大于右操作数的值，如果是则条件为真。 (a > b) 不为真。
< 检查左操作数的值是否小于右操作数的值，如果是则条件为真。 (a < b) 为真。
>= 检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。 (a >= b) 不为真。
<= 检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。 (a <= b) 为真。
!< 检查左操作数的值是否不小于右操作数的值，如果是则条件为真。 (a !< b) 为假。
!> 检查左操作数的值是否不大于右操作数的值，如果是则条件为真。 (a !> b) 为真。

在sqlite里面，使用0和1来表示假真

3、 逻辑运算符

运算符 描述
AND AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。
IN IN 运算符用于把某个值与一系列指定列表的值进行比较。
NOT IN IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKE LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOB GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULL NULL 运算符用于把某个值与 NULL 值进行比较。
IS IS 运算符与 = 相似。
IS NOT IS NOT 运算符与 != 相似。
|| 连接两个不同的字符串，得到一个新的字符串。
UNIQUE UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。

4、 位运算符

运算符 描述 实例
& 如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中。 (A & B) 将得到 12，即为 0000 1100
| 如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中。 (A | B) 将得到 61，即为 0011 1101
~ 二进制补码运算符是一元运算符，具有"翻转"位效应，即0变成1，1变成0。 (~A ) 将得到 -61，即为 1100 0011，一个有符号二进制数的补码形式。
<< 二进制左移运算符。左操作数的值向左移动右操作数指定的位数。 A << 2 将得到 240，即为 1111 0000
>> 二进制右移运算符。左操作数的值向右移动右操作数指定的位数。 A >> 2 将得到 15，即为 0000 1111

5、 表达式

SELECT column1, column2, columnN 
FROM table_name 
WHERE [CONDITION | EXPRESSION];

sqlite里面还有一些内置的表达式

七、 表操作高级

1、 查找

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]  -- 根据条件查找内容，可以结合AND和OR运算符来使用

实例：

SELECT AGE FROM COMPANY 
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);  --当存在年薪大于65000的人时，返回公司里面的所有年龄

2、 删除选中数据

DELETE FROM table_name
WHERE [condition];  --删除指定条件的数据
DELETE FROM COMPANY;  --删除表中的所有数据

3、 模糊查找

3.1 LIKE匹配

通配符：

  • %：零个、一个或多个数字或字符
  • _：一个单一的数字或字符

实例：

语句 描述
WHERE SALARY LIKE '200%' 查找以 200 开头的任意值
WHERE SALARY LIKE '%200%' 查找任意位置包含 200 的任意值
WHERE SALARY LIKE '_00%' 查找第二位和第三位为 00 的任意值
WHERE SALARY LIKE '2_%_%' 查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY LIKE '%2' 查找以 2 结尾的任意值
WHERE SALARY LIKE '_2%3' 查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY LIKE '2___3' 查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

3.2 Glob匹配

这个大小写敏感

通配符：

  • *：零个、一个或多个数字或字符
  • ?：一个单一的数字或字符

实例：

语句 描述
WHERE SALARY GLOB '200*' 查找以 200 开头的任意值
WHERE SALARY GLOB '200' 查找任意位置包含 200 的任意值
WHERE SALARY GLOB '?00*' 查找第二位和第三位为 00 的任意值
WHERE SALARY GLOB '2??' 查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY GLOB '*2' 查找以 2 结尾的任意值
WHERE SALARY GLOB '?2*3' 查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY GLOB '2???3' 查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

4、 限制返回数据的数量

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows];
SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num];  --行数向下偏移

例子：

SELECT *
FROM table_name
LIMIT 3 OFFSET 2;  --选取3到5行数据

5、 排序

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];  --ASC升序，DESC降序，如果有多列数据，则，先排第一列，如果第一列数据相同，再看第二列，以此类推

例如：

SELECT * FROM COMPANY ORDER BY SALARY ASC;  --根据薪水升序排序

6、 分组

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 * FROM users
   ...> ;
id  name  pwd
--  ----  ---
1   kun   123
2   kun   124
3   q     234
sqlite> SELECT * FROM users GROUP BY name;  --选择第一个数据
id  name  pwd
--  ----  ---
1   kun   123
3   q     234

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件（使用 HAVING 子句条件一定要作用在由 GROUP BY 子句指定列上）

7、 查找唯一内容

它只获取唯一一次记录，而不是获取重复记录

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

例如：

SELECT DISTINCT name FROM COMPANY;  -- 名字这列重复的内容会自动去重
posted @ 2022-06-06 10:50  Kenny_LZK  阅读(257)  评论(0)    收藏  举报