SQLite笔记

一、基本命令

1、进入命令行环境：sqlite3
2、命令行帮助：.help
3、退出命令行环境 .quit 或者 .exit 都可以退出

---

二、数据库和表的相关命令

1. 创建一个新的数据库：sqlite3 文件名
   sqlite3 test.db
2. 打开一个已经存在的数据库：sqlite3 已经存在的文件名
3. 导入数据：.read 数据文件.read test.sql
4. 列出所有的数据表： .tables
5. 显示数据库结构：.schema
6. 显示表的结构：.schema 表名
7. 导出某个表的数据： .dump 表名
8. 设置导出目标：
   .output 文件名
   或者
   .output stdout

---

三、数据显示相关命令

1. 设置分隔符：.separator 分隔符
2. 设置显示模式：.mode 模式（默认的是 list 显示模式，一般我们使用 column 显示模式）
3. 显示标题栏：.headers on
4. 设置每一列的显示宽度：.width w1,w2,w3………
5. 设置 NULL 值显示成什么样子： .nullvalue 你想要的 NULL 值格式
6. 列出当前显示格式设置情况：.show
7. 配置文件 .sqliterc

---

四、SQLite 中的数据类型

SQLite 数据库中的数据一般由以下几种常用的数据类型组成：
NULL - 空值
INTEGER - 有符号整数
REAL - 浮点数
TEXT - 文本字符串
BLOB - 二进制数据，如图片、声音等等
SQLite 也可以接受其他数据类型。

---

五、操作表
创建表

sqlite>
sqlite>CREATE TABLE Students(Id integer,Name text,age integer);
sqlite>.tables
Students
sqlite>.schema Students
CREATE TABLE Students(Id integer,Name text,age integer);
sqlite>

修改表

改变表名 - ALTER TABLE 旧表名 RENAME TO 新表名
增加一列 - ALTER TABLE 表名 ADD COLUMN 列名 数据类型 限定符

sqlite>
sqlite>.tables
Students
sqlite>ALTER TABLE Students RENAME TO Teachers;
sqlite>.tables
Teachers
sqlite>

删除表 - DROP TABLE 表名

sqlite>
sqlite>.tables
Teachers
sqlite>DROP TABLE Teachers;
sqlite>.tables
sqlite>

---

六、约束条件

1. 主键 PRIMARY KEY（可自动增长）
   CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text);
   主键必须唯一
2. 默认值 DEFAULT
   CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text,Country text DEFAULT '中国');
3. 非空 NOT NULL
   CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text,Age integer NOT NULL,City text);
4. 唯一 UNIQUE
   CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text UNIQUE);
5. 条件检查 CHECK
   CREATE TABLE Teachers(Id integer PRIMARY KEY,Age integer CHECK(Age>22));
6. 外键 FOREIGN KEY
   CREATE TABLE Students (Id integer PRIMARY KEY, TeacherId integer, FOREIGN KEY(TeacherId)REFERENCES Teachers(id) );

---

七、增删改查

1. 插入数据INSERT INTO Teachers(Id,Name,Age,Country) VALUES(3,'Charls',33,'USA');
   2.修改数据UPDATE 表 SET 列 = ‘新值’ 【WHERE 条件语句】
   UPDATE Teachers SET Country='China';
2. 删除数据 DELETE FROM 表 【WHERE 条件语句】DELETE FROM Teachers WHERE Age>30;
3. 查找数据 SELECT 列… FROM 表
   SELECT * FROM Cars;
4. 限制返回数量 SELECT 列… FROM 表 LIMIT 数量 OFFSET 位置
   SELECT * FROM Cars LIMIT 4;
SELECT * FROM Cars LIMIT 4 OFFSET 2;
5. 别名 SELECT 列 AS 别名，列 AS 别名 FROM
   SELECT Name , Cost AS 'Price Of Car' FROM Cars;
6. 条件查询 SELECT 列 FROM 表 【WHERE 条件语句】
   a. LIKE
   LIKE 用通配符匹配字符串
   下划线 _ 匹配一个字符串
   百分号 % 匹配多个字符串
   LIKE 匹配字符串时不区分大小写

SELECT * FROM Cars WHERE Name Like '____';
SELECT * FROM Cars WHERE Name Like '%en';
SELECT * FROM Cars WHERE Name Like '%EN';

b. GLOB
GLOB 用通配符匹配字符串
下划线 ? 匹配一个字符串
百分号 * 匹配多个字符串
GLOB匹配字符串时,区分大小写

c. BETWEEN 值1 AND 值2

SELECT * FROM Cars WHERE Cost BETWEEN 20000 AND 55000;

d. IN （集合）

SELECT * FROM Cars WHERE Name IN ('Audi','Hummer');

e. 排序 ORDER BY 列 ASC (DESC)
ASC 为升序，DESC 为降序

SELECT Name, Cost FROM Cars ORDER BY Cost DESC;

f. 区分 DISTINCT 列
SELECT DISTINCT Customer FROM ORDERS;
分组 GROUP BY 列

SELECT sum(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer;