sqlite3数据库知识

Navicat数据库可视化管理软件

安装教程参考

破解软件安装步骤(断网破解)

1.断网
2.打开注册码
注册软件的使用

SQL数据库知识点

参考教程

Sqlite3介绍

下载源码(环境Unbunt)

官方下载

SQLite简介
SQLite三种线程模型

单线程模型: 这种模型下，所有互斥锁都被禁用，同一时间只能由一个线程访问。（没有进行互斥，多线程使用不安全）
多线程模型: 这种模型下，一个连接在同一时间内只有一个线程使用就是安全的。（每个线程都要有自己的数据库open句柄）
串行模型: 开启所有锁，可以随意访问。(多个线程中不加互斥的使用同一个数据库open句柄)

编译时选择线程模式

可以通过定义SQLITE_THREADSAFE宏来指定线程模式。
定义宏
SQLITE_THREADSAFE=1 指定使用串行模式
SQLITE_THREADSAFE=0 使用单线程模式
SQLITE_THREADSAFE=2 使用多线程模式
如果没有指定，默认为串行模式。

cmd

sqlite3_threadsafe()
返回值：
指定了单线程模式，函数返回false
指定了串行或者多线程模式，函数返回true。
NOTE:
sqlite3_threadsafe()函数要早于多线程模式以及启动时和运行时的模式选择，
所以它既不能区别多线程模式和串行模式也不能区别启动时和运行时的模式。

启动时选择线程模式

假如在编译时没有指定单线程模式，就可以在应用程序初始化时使用函数修改线程模式。

cmd

sqlite3_config()
参数:
SQLITE_CONFIG_SINGLETHREAD可指定为单线程模式
SQLITE_CONFIG_MULTITHREAD指定为多线程模式
SQLITE_CONFIG_SERIALIZED指定为串行模式

运行时选择线程模式

如果没有在编译时和启动时指定为单线程模式，那么每个数据库连接在创建时可单独的被指定为多线程模式或者串行模式，
但是不能指定为单线程模式。如果在编译时或启动时指定为单线程模式，就无法在创建连接时指定多线程或者串行模式。

cmd

创建连接时用：
SQLITE_OPEN_NOMUTEX标识创建多线程模式的连接
SQLITE_OPEN_FULLMUTEX标识创建串行模式的连接
如果没有指定标识，或者使用sqlite3_open()或sqlite3_open16()函数来创建数据库连接，
那么在编译时或启动时指定的线程模式将作为默认的线程模式使用。

Build脚本(环境Unbunt)

cmd

#!/bin/bash

CC=gcc
AR=ar
AR_FLAG=-rcv
THREAD_SAFE=-DSQLITE_THREADSAFE=2
# If not need extern load sqlite3_load_extension() and load_extension() function feature.
NO_EXTERN=-DSQLITE_OMIT_LOAD_EXTENSION

# Build the sqlit3 bin.
$CC $THREAD_SAFE shell.c sqlite3.c -lpthread -ldl -o sqlite3

# Builde the sqlite3 dynamic lib.
$CC -o sqlite3.o sqlite3.c -c -fPIC -lpthread -ldl  
$CC -o libsqlite3.so sqlite3.o -shared
rm -rf sqlite3.o

# Builde the sqlite3 static lib.
$CC -o sqlite3.o -c sqlite3.c -lpthread -ldl
$AR $AR_FLAG libsqlite3.a sqlite3.o
rm -rf sqlite3.o

Sqlite3线程安全

cmd

如果你想在多线程环境下使用数据库，那么你得确保所有的线程使用的都是同一个数据库连接。
单个数据库指的是sqlite3_open一次，然后得到的db供多个线程使用！

SQLite 采用单线程模型，用专门的线程/队列（同时只能有一个任务执行访问）进行访问
SQLite 采用多线程模型，每个线程都使用各自的数据库连接 （即 sqlite3 *）
SQLite 采用串行模型，所有线程都公用同一个数据库连接。
因为写操作的并发性并不好，当多线程进行访问时实际上仍旧需要互相等待，
而读操作所需要的 SHARED 锁是可以共享的，所以为了保证最高的并发性

使用多线程模式
使用WAL模式
单线程写，多线程读（各线程都持有自己对应的数据库连接)
避免长时间事务
缓存 sqlite3_prepare 编译结果
多语句通过 BEGIN 和 COMMIT 做显示事务，减少多次的自动事务消耗

简单数据库表

测试的数据库
表Student

ID	name	age	description	sex
1	xiao hong	3	是最好的学生	W
2	xiao ming	4	是最差的学生	M
8	xiao hua	4	不是学生	M

表Call

ID Name phone

1 xiao hong 110

2 xiao ming 112

15 xiao fang 117

ID	Name	phone
1	xiao hong	110
2	xiao ming	112
15	xiao fang	117

数据库操作

.database显示数据库信息

附加数据库

为数据库取新名字，在新的连接数据上操作，不影响原始数据库，相当于创建Linux的软链接

cmd

1	ATTACH DATABASE <原始数据库> AS <附加数据名>;

分离数据库

分离的连接数据库，不影响原始数据库，相当于取消Linux的软链接

cmd

1	DETACH DATABASE <数据库别名>;

创建表

cmd

CREATE TABLE database_name.table_name(
   <列表1> <数据类型> PRIMARY KEY(one or more columns) NOT NULL,
   <列表2> <数据类型> <约束参数>,
   <列表3> <数据类型> <约束参数>,
   .....
   <列表N> <数据类型> <约束参数>
);

删除表

cmd

1	DROP TABLE <数据库名(可不写)>.<表名>;

查询数据

查询所有数据

cmd

1	SELECT * FROM <表名>

cmd

1
2
3

SELECT 1;   // First check the sql is connect.
-- SELECT 1; // 注释查询语句
SELECT * FROM Student;  // 查询students表的所有数据

条件查询数据

cmd

1	SELECT * FROM <表名> WHERE <条件表达式>

常用的条件表达式

常用的条件表达式

AND条件

cmd

1	SELECT * FROM <表名> WHERE (<条件1> AND <条件2>)

cmd

1	SELECT * FROM Student WHERE (age = 3 AND name = 'xiao hong');

OR条件

cmd

1	SELECT * FROM <表名> WHERE (<条件1> OR <条件2>)

cmd

1	SELECT * FROM Student WHERE (name = 'xiao ming' OR age = 3);

OR条件

cmd

1	SELECT * FROM <表名> WHERE (NOT <条件1>)

cmd

1	SELECT * FROM Student WHERE (NOT age = 3);

投影查询数据
常用表达式

cmd

1
2
3

SELECT 列1, 列2, 列3 FROM <表名>
SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM <表名>
SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM <表名> WHERE <条件表达式>

cmd

1
2
3

SELECT name, ID FROM Student;
SELECT name sname, id sid FROM Student;
SELECT name, id stu_id FROM Student WHERE (name = 'xiao ming');

排序查询数据

cmd

1	SELECT * FROM <表名> WHERE <条件表达式> ORDER BY 列1 <排序>, 列2 <排序>

排序	关键字
升序	ASC
倒序	DESC

cmd

1	SELECT age FROM Student WHERE (age > 2) ORDER BY ID ASC, age DESC;

分页查询数据

cmd

1
2
3

SELECT * FROM <表名> WHERE <条件表达式> ORDER BY 列1 <排序>, 列2 <排序> LIMIT <M> OFFSET <N>
M = 每页最大数
N = 每页最大数 * (想查第几页 - 1)

cmd

1	SELECT name, id FROM Student WHERE (age > 2) ORDER BY id ASC LIMIT 3 OFFSET 0;

聚合查询数据

聚合函数查询数据

cmd

1	SELECT <函数> 别名 FROM <表名> WHERE <条件表达式>

常用聚合函数

cmd

1	SELECT Max(age) max_old FROM Student WHERE (age > 1);

聚合分组查询数据

cmd

1	SELECT <函数> 别名 FROM <表名> WHERE <条件表达式> GROUP BY <列1>, 列2

cmd

1	SELECT name, age, sex, COUNT(*) num FROM Student GROUP BY sex;

多表查询数据(获取M*N数据)

cmd

1	SELECT 表1别名.列1 别名1, 表2别名.列2 别名2 FROM <表1> <表1别名>, <表2> <表2别名> WHERE <条件表达式>

cmd

1	SELECT s.ID, s.name, c.phone FROM Student s, Call c WHERE (s.ID = c.ID);

连接查询

cmd

1	SELECT ... FROM tableA <连接函数> JOIN tableB ON tableA.column1 = tableB.column2;

连接函数	关键字
相交包含	INNER
右包含	RIGHT OUTER JOIN
左包含	LEFT OUTER JOIN
全部包含	FULL OUTER JOIN

连接图

连接图

cmd

// 相互交查询
SELECT Student.name name FROM Student INNER JOIN Call ON Student.name = Call.Name;
(SELECT s.name name FROM Student s INNER JOIN Call c ON s.name = c.Name;)

// 右边包含查询
SELECT Student.name name FROM Student RIGHT OUTER JOIN Call ON Student.name = Call.Name;

// 左边包含查询
SELECT Student.name name FROM Student LEFT OUTER JOIN Call ON Student.name = Call.Name;

// 全部包含查询
SELECT Student.name name FROM Student FULL OUTER JOIN Call ON Student.name = Call.Name;

修改数据库数据

INSERT操作

cmd

1	INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...) (值1, 值2, ...);

cmd

INSERT INTO <表1> (column1, column2, ... columnN)
   SELECT column1, column2, ...columnN 
   FROM <表2>
   WHERE <条件表达式>;

cmd

1	INSERT INTO Call ( Name, phone ) VALUES ( 'xiao bing', 109 ), ( 'xiao yin', 175 );

UPDATE操作

cmd

1	UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

cmd

1 2	SELECT * FROM Call WHERE (Name = 'xiao bing'); // 先做检测项存在 UPDATE Call SET phone = 119 WHERE (Name = 'xiao bing'); // 再修改值

DELETE操作

cmd

1	DELETE FROM <表名> WHERE ...;

cmd

1 2	SELECT COUNT(*) FROM Call WHERE (Name = 'xiao bing') OR (Name = 'xiao yin'); // 先做检测项存在 DELETE FROM Call WHERE (Name = 'xiao bing') OR (Name = 'xiao yin'); // 再修删除

数据理论学习(sqlite3)