SqLite本地数据库常规用法
SqLite数据库介绍
SqLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库一样，您不需要在系统中配置。
就像其他数据库，SqLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SqLite 直接访问其存储文件。
为什么要用SqLite
- 不需要一个单独的服务器进程或操作的系统（无服务器的）。
- 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）中运行。
创建SqLite数据库
使用命令行创建数据库
cd. >test3.db
使用代码创建数据库
//方式一
System.IO.File.Create(@"c:\test.db");
//方式二
//需要安装Neget包 System.Data.SQLite.Core
System.Data.SQLite.SQLiteConnection.CreateFile(@"c:\test2.db");
使用官网工具创建数据库
下载SqLite工具包
创建数据库
工具包下载完成后j解压，可以看到SqLite3.exe文件，直接在命令行当前目录中以下命令创建数据库。
sqlite3 "test.db" ".databases" ".quit"
连接数据库
test.db文件创建完成以后，默认会创建一个main名称的数据库,然后就可以直接用数据库IDE打开连接数据库即可。
数据库IDE：
代码操作数据库
链接字符串
- 基本
--database可以不填
Data Source=filepath; 如：Data Source=C:\test.db
Data Source=filepath;database=main;
实体类操作Sqlite数据库
需要Neget安装sqlite-net-pcl包。
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using SQLite;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
string path = @"F:\DownLoad\sqlite\text4.db";
NoteDatabase noteDatabase = new NoteDatabase(path);
Note note = new Note();
note.Text = "123456";
note.Date = DateTime.Now;
noteDatabase.SaveNoteAsync(note).Wait();
}
}
public class Note
{
//主键,自增
[PrimaryKey, AutoIncrement]
public int ID { get; set; }
//唯一标识，不为空
[Unique, NotNull]
public string Text { get; set; }
[MaxLength(30), NotNull]
public DateTime Date { get; set; }
//忽略某个字段
[Ignore]
public string Name { get; set; }
}
public class NoteDatabase
{
readonly SQLiteAsyncConnection database;
readonly SQLite.SQLiteConnection database2;
public NoteDatabase(string connectionString)
{
database = new SQLiteAsyncConnection(connectionString);
database.CreateTableAsync<Note>().Wait();
}
public Task<List<Note>> GetNotesAsync()
{
//Get all notes.
return database.Table<Note>().ToListAsync();
}
public Task<Note> GetNoteAsync(int id)
{
// Get a specific note.
return database.Table<Note>()
.Where(i => i.ID == id)
.FirstOrDefaultAsync();
}
public Task<int> SaveNoteAsync(Note note)
{
if (note.ID != 0)
{
// Update an existing note.
return database.UpdateAsync(note);
}
else
{
// Save a new note.
return database.InsertAsync(note);
}
}
public Task<int> DeleteNoteAsync(Note note)
{
// Delete a note.
return database.DeleteAsync(note);
}
}
}
ADO.NET
需要安装Neget包System.Data.SQLite.Core或者Microsoft.Data.Sqlite。
using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SqlHelper helper = new SqlHelper();
string strSql = "Select * From Test";
DataTable dt = helper.GetDataTable(strSql);
string updateSql = "update Test set id=4 where id=3";
int count = helper.ExecuteNonQuery(updateSql);
Console.WriteLine(count);
}
}
public class SqlHelper
{
//创建数据库连接字符串
private static string ConnString = @"Data Source=F:\DownLoad\sqlite\test.db;database=main;";
//创建数据库连接对象
private static SQLiteConnection Conn = null;
//增删改
public int ExecuteNonQuery(string sqlStr)
{
Conn = new SQLiteConnection(ConnString);
Conn.Open();
IDbCommand cmd = new SQLiteCommand(sqlStr, Conn);
int result = cmd.ExecuteNonQuery();
Conn.Close();
return result;
}
/// <summary>
/// 获得datatable
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable GetDataTable(string sqlStr)
{
Conn = new SQLiteConnection(ConnString);
DataTable dt = new DataTable();
DbDataAdapter dap = new SQLiteDataAdapter(sqlStr, Conn);
dap.Fill(dt);
Conn.Close();
return dt;
}
}
}
Dapper
需要Neget安装System.Data.SQLite和Dapper包。
using Dapper;
using System.Data.SQLite;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
using (var conn = new SQLiteConnection(@"Data Source=F:\DownLoad\sqlite\text4.db;database=main;"))
{
var data = conn.Query<object>("Select * From Test");
int a = conn.Execute("update Test set id=4 where id=3");
}
}
}
}
数据库操作
常用脚本
--查看数据库版本
select sqlite_version(*)
--创建表,没有备注，只能在工具DDL中查看
/*
一般使用`INTEGER/REAL/TEXT`三个数据类型
REAL 代表小数，DM值一般为(7,8)或(11,4)或(13,2),不然会存在精度丢失的问题
1234567.12345678
12345678911.1234
1234567899913.12
*/
create table if not exists "User" (
"Id" integer not null primary key autoincrement, --用户id
"Guid" text NOT NULL default (uuid()) , --GUID,uuid方法可能会报错
"Name" text not null default 'a', --姓名
"UserName" text collate utf8_general_ci null default null, --用户名
"Price" real not null default 0 --金额
);
create table if not exists `Sms` (
`Id` char(36) not null primary key,
`PhoneAmount` integer null default null,
`Name` text null default null,
`UserName` varchar(100) null default null,
`CreateTime` varchar(100) null default null
);
--查看所有表和索引
select * from sqlite_master
--查看所有表名和建表Sql
select * from sqlite_master where type='table';
--查看表的字段信息
PRAGMA table_info(Info)
- notnull 0:允许Null 1:不允许
- pk:1是主键
--添加列
ALTER TABLE User ADD COLUMN UserName text NOT NULL DEFAULT '1'
--GUID
select lower(hex(randomblob(16))) AS UUID
--小写GUID
SELECT lower(hex(randomblob(16))) AS UUID ;
--当前时间
select DATETIME();
select datetime('now');
select datetime('now','localtime');
--ISNULL
SELECT ifnull(NULL,1)
