参照学习菜鸟教程:仅作为熟悉记录SQLite – C/C++ | 菜鸟教程
目录
环境搭建
通过命令sudo apt install sqlite3
安装成功:
- 命令:称之为点命令,可以执行许多动作。可以通过点命令来创建,删除,修改db文件。
数据类型
每个存储在 SQLite 数据库中的值都具有以下存储类之一:
存储类 描述 NULL 值是一个 NULL 值。 INTEGER 值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中。 REAL 值是一个浮点值,存储为 8 字节的 IEEE 浮点数字。 TEXT 值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储。 BLOB 值是一个 blob 数据,完全根据它的输入存储。 亲和类型
创建数据库
如下操作:创建--查看test.db
zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$ sqlite3 test.db #创建 SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> .open test.db #打开 sqlite> .quit #退出 zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$ ls test.db zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$
通过dump命令实现.db和.sql文件转换
# db转sql文件 zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$ sqlite3 testDB.db .dump > testDB.sql #sql文件转db文件 zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$ sqlite3 testDB.db < testDB.sql
附加数据库
语法:ATTACH DATABASE file_name AS database_name;
zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$ sqlite3 SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. #ATTACH sqlite> ATTACH DATABASE 'testDB.db' as 'TEST'; sqlite> .databases main: TEST: /home/zion6135/Desktop/sqlite3/testDB.db #main不可用于连接,注意错误 sqlite> ATTACH DATABASE 'testDB.db' as 'main'; Error: database main is already in use
分离数据库
语法:DETACH DATABASE 'Alias-Name';
# attach到tttt sqlite> ATTACH DATABASE 'testDB.db' as 'tttt'; sqlite> .databases main: TEST: /home/zion6135/Desktop/sqlite3/testDB.db tttt: /home/zion6135/Desktop/sqlite3/testDB.db #detachtttt sqlite> DETACH DATABASE 'tttt'; sqlite> .databases main: TEST: /home/zion6135/Desktop/sqlite3/testDB.db
创建表
语法 CREATE TABLE 语句的基本语法如下:
CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype, );#创建表COMPANY sqlite> CREATE TABLE COMPANY( ...> ID INT PRIMARY KEY NOT NULL, ...> NAME TEXT NOT NULL, ...> AGE INT NOT NULL, ...> ADDRESS CHAR(50), ...> SALARY REAL ...> ); #创建表DEPARTMENT sqlite> CREATE TABLE DEPARTMENT( ...> ID INT PRIMARY KEY NOT NULL, ...> DEPT CHAR(50) NOT NULL, ...> EMP_ID INT NOT NULL ...> ); #查看有的表 sqlite> .tables COMPANY DEPARTMENT #查看表COMPANY的具体内容 sqlite> .schema COMPANY CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
删除表
语法:DROP TABLE database_name.table_name;#查看有的表 sqlite> .tables COMPANY DEPARTMENT #删除表 sqlite> DROP TABLE COMPANY; #查看:表COMPANY被删除 sqlite> .tables DEPARTMENT sqlite>
Insert语句
用于向数据库的某个表中添加新的数据行 语法:INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);#向COMPANY表插入信息 INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
语法:INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);#向COMPANY表插入信息 INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
Select语句
从 SQLite 数据库表中获取数据,以结果表的形式返回数据。这些结果表也被称为结果集。
- 获取所有可用的字段 语法:SELECT * FROM table_name;
sqlite> SELECT * FROM COMPANY; 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
- 获取特定的列的内容 语法:SELECT column1, column2, columnN FROM table_name;
sqlite> SELECT ID, NAME, SALARY FROM COMPANY; 1|Paul|20000.0 2|Allen|15000.0 3|Teddy|20000.0 4|Mark|65000.0 5|David|85000.0 6|Kim|45000.0
- 用SELECT来查询数据库中相关内容
#查找table sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table'; DEPARTMENT COMPANY sqlite> #查找COMPANY table sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY'; COMPANY sqlite>
-
运算符
- 算术运算符(+ - * /)
sqlite> select 1+2 ...> ; 3 sqlite>
- 比较运算符
WHERE 子句是用来设置 SELECT 语句的条件语句
sqlite> SELECT * FROM COMPANY WHERE SALARY > 50000; 4|Mark|25|Rich-Mond |65000.0 5|David|27|Texas|85000.0 sqlite>
- 逻辑运算符
#相当于 if (AGE > 25 && SALARY >= 65000) sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; 4|Mark|25|Rich-Mond |65000.0 5|David|27|Texas|85000.0 sqlite>
- 位运算符
sqlite> select (1 << 2); 4 sqlite>
Update语句来修改表
sqlite> SELECT * FROM COMPANY; 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|Texas|45000.0 #修改ID为6的内容ADDRESS改为Texas5 sqlite> UPDATE COMPANY SET ADDRESS = 'Texas5' WHERE ID = 6; sqlite> SELECT * FROM COMPANY; 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|Texas5|45000.0
Delete 语句
DELETE FROM table_name WHERE [condition];sqlite> SELECT * FROM COMPANY; 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|Texas5|45000.0 #删除ID为6的内容 sqlite> DELETE FROM COMPANY WHERE ID = 6; sqlite> SELECT * FROM COMPANY; 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
Like子语句
百分号(%)代表零个、一个或多个数字或字符。下划线(_)代表一个单一的数字或字符。
sqlite> SELECT * FROM COMPANY; 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 #中间有字符-的内容 sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%'; 4|Mark|25|Rich-Mond |65000.0
GLOB子句
GLOB 运算符是用来匹配通配符指定模式的文本值。和LIKE很像
sqlite> SELECT * FROM COMPANY; 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 #找有2*的字符 sqlite> SELECT * FROM COMPANY WHERE AGE GLOB '2*'; 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 sqlite>
Limit子句
LIMIT子句限制SELECT返回的数据数量 SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]sqlite> SELECT * FROM COMPANY LIMIT 6; 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 #限制返回最多一条 sqlite> SELECT * FROM COMPANY LIMIT 1; 1|Paul|32|California|20000.0
Order By语句
基于一个或多个列按升序或降序排列数据
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];#SALARY 升序排列 sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC; 2|Allen|25|Texas|15000.0 1|Paul|32|California|20000.0 3|Teddy|23|Norway|20000.0 4|Mark|25|Rich-Mond |65000.0 5|David|27|Texas|85000.0 #SALARY 降序排序 sqlite> SELECT * FROM COMPANY ORDER BY SALARY DESC; 5|David|27|Texas|85000.0 4|Mark|25|Rich-Mond |65000.0 1|Paul|32|California|20000.0 3|Teddy|23|Norway|20000.0 2|Allen|25|Texas|15000.0 sqlite>