SQLite数据库简单操作

---

SQLite 简介

SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎

使用 sqlite 打开或创建一个数据库文件，操作该文件实现对数据库的操作

SQLite 命令可分为 点命令 和 SQL命令(SQL语句)

SQLite 命令可分为

DDL - 数据定义语言

命令	描述
CREATE	创建一个新的表
ALTER	修改数据库对象
DROP	删除整个表

DML - 数据操作语言

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

DQL - 数据查询语言

命令	描述
SELECT	检索某些记录

---

安装

Linux 系统自带 SQLite ， 无需安装

Windows 安装 SQLite

下载地址
https://www.sqlite.org/download.html

解压到同一个文件夹，并添加到环境变量

---

启动和配置

启动

在命令提示符下输入slqite3来启动程序，打开一个文件，这个文件就是一个数据库。如果指定的文件不存在，则将自动创建一个具有给定名称的新数据库文件。如果在命令行中没有指定数据库文件，则会创建一个临时数据库，并在sqlite3程序退出时自动删除。

# 文件名是数据库名，可添加路径在指定位置创建或打开指定位置的文件
sqlite3 文件名

# 创建或打开一个数据库 testdb
sqlite3 /home/mydb/testdb

# 退出 sqlite3
sqlite> .exit

出现 sqlite>说明成功了

配置

sqlite> .help -- 查看命令帮助
sqlite> .help --all  -- 查看详细命令帮助
sqlite> .help COMMAND -- 查看指定命令的帮助
sqlite> .COMMAND -- 查看指定命令的当前配置或可选配置

.show 查看当前设置

sqlite> .show
     echo: off			-- 是否显示执行的命令，执行的命令显示一遍，on 和 off
  explain: off			-- 显示方式，on 和 off
  headers: off			-- 是否显示列名， on 和 off
     mode: list			-- 显示方式，select 结果的显示方式 ，常用 list 和 column
nullvalue: ""			-- null 值显示方式 ，指定字符串
   output: stdout		-- 标准输出，可以输出到文件，文件路径 和 默认 stdout 输出到屏幕
separator: "|"			-- 更改输出模式和导入使用的分隔符，指定分隔符 默认 |
    stats: off			-- 是否显示统计信息，每行命令执行完毕显示统计信息，on 和 off
    width: 				-- 显示的列宽度，要分配给每列的字符数，负数表示右对齐，宽度为0表示自动选择列宽度，未指定的列宽度变为零，可指定数字  10 20 -10 

修改设置

sqlite> .headers on
sqlite> .mode column

---

使用

SQL 命令以 ; 分号结束 ，不区分大小写
SQLite 点命令以.点开始，区分大小写

创建一个数据库
在终端输入，创建的同时进入数据库
sqlite3 /home/db/mydb

表操作

创建表

CREATE TABLE 表名(
   列名 类型 约束规则, 
   列名 类型,
   ....
);

/* 约束规则 */
NOT NULL		-- 确保某列不能有 NULL 值
DEFAULT 		--当某列没有指定值时，为该列提供默认值
UNIQUE			--确保某列中的所有值是不同的
PRIMARY Key		--唯一标识数据库表中的各行/记录
CHECK			--确保某列中的所有值满足一定条件

查看表信息

.schema		-- 查看所有表结构
.schema 表名	-- 查看指定表结构

.tables		-- 查看所有表
.tables 表名	-- 可以判断当前表是否存在，存在返回表名，不存在什么都不返回

/* 使用 sqlite_master 查询数据库所有表 */
SELECT tbl_name FROM sqlite_master WHERE type = 'table';
/* 使用 sqlite_master 查看表结构	*/
SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = '表名';

插入字段

ALTER TABLE 表名 ADD COLUMN 列名 类型;

表重命名

ALTER TABLE 表名 RENAME TO 新表名;

删除表

DROP TABLE 表名;

删除列

ALTER TABLE 表名 DROP COLUMN 列名;

---

表数据

添加数据

/* 添加数据，指定列名 */
INSERT INTO 表名 [(列名1, 列名2, ..)] VALUES (值1, 值2,...);

/* 添加数据，按列顺序 */
INSERT INTO 表名 VALUES (值1,值2,...);

更新表数据
如果不使用 where 子语句，会更新所有行

UPDATE 表名 SET  列名=要修改的值  WHERE 列名=值;

删除表数据
如果不使用 where 子语句，会删除所有行

DELETE FROM 表名 WHERE 列名=值;

---

查询数据

常规查询

SELECT 列名1, 列名2 FROM 表名;  -- 查询指定列数据
SELECT * FROM 表名;			  -- 查询表所有数据

条件查询
where 指定查询条件

select 列名 from 表名 where 列名=值;

匹配语句 like ， 匹配的关键字大小写不敏感

百分号 %     -- 匹配零个、一个、多个 数字或字符
下划线 _  	-- 匹配单一数字或字符

select 列名 from 表名 where 列名 like 匹配语句;
select 列名 from 表名 where 列名 like '%关键字%';

匹配语句 like , 匹配的关键字大小写敏感， 遵循 linxu 语法

星号 *		-- 零个、一个、多个 数字或字符
问好 ?		-- 单一 数字或字符

select 列名 from 表名 where 列名 like 匹配语句;
select * from 列名 where 表名 like '*关键字*';

限制返回的行数

limit   -- 返回几行数据
offset  -- 返回从第几行开始的数据  , 不能单独使用

select 列名 from 表名 limit 数字 offset 数字;

/* 返回从第 3 行开始的 5 行数据 */
select 列名 from 表名 limit 5 offset 3;

排序
order by 排序，数字按大小，字母按顺序排序

ASC   -- 默认值，从小到大，升序排列
DESC  -- 从大到小，降序排列

select * from 表名 order by 列名 desc;

分组
group by 对相同的数据进行分组
在 select 语句中，group by 子句放在 where 子句之后，放在 order by 子句之前

select 列名 from 表名 group by 列名;

过滤
having 指定条件来过滤数据
where 子句在所选列上设置条件，而 having 子句则在由 group by 子句创建的分组上设置条件

select 列名 from 表名 where 条件 group by 列名 having 条件 order by 列名;

消除重复数据
distinct 消除所有重复的记录，并只获取唯一一次记录

SELECT DISTINCT 列名 FROM 表名;

交叉连接 CROSS JOIN
把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行

select 列名 from 表1 cross join 表2

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

SELECT 列名 FROM 表1 INNER JOIN 表2 ON 表1.列名 = 表2.列名;

外连接 OUTER JOIN
外连接声明条件的方法与内连接是相同的

SELECT 列名 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.列名 = 表2.列名;

---

运算

运算 用于 where 子句中执行操作
算术运算

+ 	-- 加法运算
- 	-- 减法运算
* 	-- 乘法运算
/ 	-- 整除运算
% 	-- 取模运算

比较运算
比较运算符返回 0 false 和 1 true

==  =  	-- 等于
!=  <>  -- 不等于
<  >  <=  >=	-- 关系运算
!<   !>   -- 不小于，不大

逻辑运算

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

位运算

&	-- 如果同时存在于两个操作数中，二进制 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

---

常用函数

/* sqlite_version 函数返回 SQLite 库的版本 */
select sqlite_version();

/* COUNT 计算数据库表中的行数 */
select count(列名) from 表名;

/* MAX 返回列的最大值 */
/* MIN 返回列的最小值 */
/* AVG 返回列的平均值 */
/* SUM 返回数值列计算总和 */
/* ABS 返回数值参数的绝对值 */
/* LENGTH 返回字符串的长度 */
/* UPPER 函数把字符串转换为大写字母 */
/* LOWER 函数把字符串转换为小写字母 */
select upper('字母');
select LOWER (列名) from 表名;

/* RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数 */
select random();
select random() as 别名;

---

参考文档

SQLite 中文网教程
https://www.sqlite.net.cn/tutorial/2.html
SQLite 菜鸟教程
https://www.runoob.com/sqlite/sqlite-tutorial.html