SQL语言的分类
SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，
数据定义语言DDL，数据控制语言DCL。
1. 数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>
2 .数据操纵语言DML
数据操纵语言DML主要有三种形式：
1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE
3. 数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象—–表、视图、
索引、同义词、聚簇等如：
1 2 3
| CREATE TABLE/VIEW/INDEX/SYN/CLUSTER | | | | | 表 视图 索引 同义词 簇
|
4. 数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
1) GRANT：授权。
2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚—ROLLBACK
回滚命令使数据库状态回到上次最后提交的状态。其格式为：
SQL>ROLLBACK;
3) COMMIT [WORK]：提交。
在数据库的插入、删除和修改操作时，只有当事务在提交到数据
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
别说明这三种类型。
(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；
(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；
Sqlite4Unity
地址
建立连接
1 2
| var dbPath = string.Format(@"Assets/StreamingAssets/tempDB.sqlite3"); _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
|
删除table
1
| _connection.DropTable<Person>();
|
创建table
1
| _connection.CreateTable<Person>();
|
插入数据
1 2 3 4
| _connection.Insert(new Person {typeId = 1, Name = "Tom"}); _connection.Insert(new Person {typeId = 2, Name = "Dom"}); _connection.Insert(new Person {typeId = 1, Name = "Jake"}); _connection.Insert(new Person {typeId = 2, Name = "Rose"});
|
整体插入数据
1 2 3 4 5 6
| _connection.InsertAll( new[] { new Person {typeId = 1, Name = "Ben"}, new Person {typeId = 2, Name = "Lily"}, });
|
修改数据
1 2 3 4 5 6
| _connection.Table<Person>() .Where(p => p.typeId == 1) .ToObservable() .Do(p=>p.Name="Jim") .Do(p => _connection.Update(p)) .Subscribe();
|
删除数据
1 2 3 4 5
| _connection.Table<Person>() .Where(p => p.typeId == 1) .ToObservable() .Do(p => _connection.Delete(p)) .Subscribe();
|
查找
1
| _connection.Find<Person>(x => x.Id == 4).Name.print();
|
1 2 3 4 5 6
| foreach (var map in _connection.TableMappings) { var item = _connection.Find(1, map); if (item != null) item.ToString().print(); }
|
查询
1 2 3 4
| _connection.Query<Person>("select * from Person") .ToObservable() .Do(t => t.Name.print()) .Subscribe();
|
相当于
1 2 3 4 5 6
| SQLiteCommand cmd = new SQLiteCommand(_connection); cmd.CommandText = "select * from Person"; cmd.ExecuteQuery<Person>() .ToObservable() .Do(t => t.Name.print()) .Subscribe();
|
查询多张表
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| _connection.Query<val>("select typeId as id,TypeName as N from Person,PersonType") .ToObservable() .Do(t => { t.id.print(); t.N.print(); }) .Subscribe(); public class val { public int id { get; set; } public string N { get; set; } }
|
获取
1 2
| var item=_connection.Get<Person>(2); item.Name.print();
|
GetMapping
1 2
| var item = _connection.GetMapping<Person>(); item.GetByPrimaryKeySql.print();
|
执行sql语句删除
1 2
| int id = 5; _connection.Execute("delete from Person where id=?", id);
|
执行sql语句创建
1 2 3 4 5 6
| string sql = "create table contacts ( id integer primary key," + "name text not null collate nocase," + "phone text not null default 'unknown'," + "email text not null default '' collate nocase," + "unique (name,phone));"; _connection.Execute(sql);
|
事务–快速插入
1 2 3 4 5 6 7 8 9 10
| SQLiteCommand cmd = new SQLiteCommand(_connection); _connection.BeginTransaction(); Observable.Range(0, 20) .Do(t => { cmd.CommandText = string.Format("insert into Person values(null,2,'{0}')",t); cmd.ExecuteNonQuery(); }) .Subscribe(); _connection.Commit();
|
添加字段
1 2 3 4
| _connection.Table<Person>(); SQLiteCommand cmd = new SQLiteCommand(_connection); cmd.CommandText = "alter table Person add name2 text not null default noknow;"; cmd.ExecuteNonQuery();
|
重命名字段
删除字段
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| SQLiteCommand cmd = new SQLiteCommand(_connection); string[] sql = { "drop table if exists PersonOld;", "alter table Person rename to PersonOld;", "create table Person(Id,Name);", "insert into Person select Id,Name from PersonOld;", "drop table PersonOld", }; _connection.BeginTransaction(); sql.ToObservable() .Do(t => { cmd.CommandText = t; cmd.ExecuteNonQuery(); }) .Subscribe(); _connection.Commit();
|
ExecuteScalar
返回结果第一行的第一列，如果结果集为空，则为空引用。
最多返回2033个字符。
1 2
| cmd.CommandText = "select count(*) from Person"; cmd.ExecuteScalar<int>().print();
|
架构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| Exception + ++SQLiteException + ++Not Null Constration Violation Exception Attribute + ++Table Attribute | ++ColumnAttribute | ++PrimaryKeyAttribute | ++AutoIncrementAttribute | ++IndexedAttribute+-------+UniqueAttribute | ++IgnoreAttribute | ++MaxLengthAttribute | ++CollationAttribute | ++NotNullAttribute
|
TableMapping
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| TableMapping + | +-----------------------------+ | |SetAutoIncPK | | |FindColumnWithPropertyName | +--->+FindColumn | | |GetInsertCommand | | |CreateInsertCommand | | |Dispose | | | | | +-----------------------------+ | +---> Column + | +-----------+ +--> |SetValue | |getValue | +-----------+
|
orm
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
| Orm + | +----------+ | | SqlDecl | +-> | SqlType | | | IsPK | | +----------+ | +----> Collation | + +----------------+ | | |IsAutoInc | | | |GetIndices | | +--> |MaxStringLength | | |isMarkedNotNull | | +----------------+ +--->SQLiteCommand | + | | +---------------------------+ | | | ExecuteNonQuery | | | | ExecuteDeferredQuery | | | | ExecuteQuery | | | | OnInstanceCreated | | | | ExecuteScalar | | | | Bind | | | | Prepare | | +---> | Finalize | | | BindAll | | | BindParameter | | | ReadCol | | | Binding(Name,Value,Index) | | +---------------------------+ | | +---> Prepare Sqlite Insert Command: IDisposable | + | | +----------------+ | | |ExecuteNonQuery | | +----> |Prepare | | |Dispose | | |~ | | +----------------+ | +-------> TableQuery : BaseTableQuery + | +------------------+ | |Clone | | |Where | | |Take | | |Skip | | |ElementAt | | |Deferred | | |OrderBy | | |OrderByDescending | | |ThenBy | +--> |ThenByDescending | | |AddOrderBy | | |AddWhere | | |Join | | |Select | | |GenerateCommand | | |CompileResult | | |CompileExpr | | +------------------+ | +---> SQLite3 + +-----------------------+ | | Open | | | EnableLoadExtension | | | Close | | | Initialize | | | Shutdown | +------> | Config | | SetDirectory | | BusyTimeout | | Changes | | Prepare2 | | Step | | Reset | | Finalize | | LastInsertRowid | | Errmsg | | GetErrmsg | | BindParameterIndex | | BindNull | | BindInt | | BindDouble | | BindText | | BindBlob | | ColumnCount | | ColumName | | ColumnType | | ColumnInt | | ColumnDouble | | ColumnText | | ColumnBlob | | ColumnBytes | | ColumnString | | ColumnByteArray | | ExtendedErrCode | | LibVersionNumber | +-----------------------+
|
SQLiteConnection
SQLiteConnection
构建了一种新的sqliteconnection并打开一个指定databasepath SQLite数据库。
1 2
| var dbPath = string.Format(@"Assets/StreamingAssets/tempDB.sqlite3"); _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
|
EnableLoadExtension
GetNullTerminatedUtf8
GetMapping
检索给定类型的自动生成的映射。
该映射表示数据库的列的架构，并包含设置和获取对象的属性的方法。
DropTable
在数据库上执行“drop table”。这是不可恢复的。
CreateTable
在数据库上执行“create table if not
exists”它也在表的列上创建任何指定的索引。它使用一个自动生成的模式从指定的类型。以后您可以通过调用getmapping访问模式。返回添加到数据库模式的条目的数目。
CreateIndex
为指定的表和列创建索引。
GetTableInfo
MigrateTable
NewCommand
创建一个新的sqlitecommand。可以重写为一个子类。
CreateCommand
创建一个新的sqlitecommand给定的命令文本的理由。放置一个“？”在每个参数的命令文本中。
Execute
创建一个sqlitecommand给定的命令文本（SQL）的参数。放置一个“？”在每个参数的命令文本中，然后执行该命令。使用此方法，而不是查询时，您不期望行返回。这种情况包括插入、更新和删除。你可以设置连接的痕迹或timeexecution属性来执行配置文件。返回由于此执行，在数据库中修改的行数。
其实就是执行的cmd.ExecuteNonQuery ();
ExecuteScalar
返回结果第一行的第一列，如果结果集为空，则为空引用。
最多返回2033个字符。
Query
创建一个sqlitecommand给定的命令文本（SQL）的参数。放置一个“？”在每个参数的命令文本中，然后执行该命令。它使用给定类型的自动生成的映射返回结果的每一行。
返回 List
DeferredQuery
返回 IEnumerable
Table
返回一个queryable界面，通过给定的类型表。
Get
试图从与指定类型关联的表中检索给定主键的对象。使用这种方法需要特定类型指定一个主键（使用primarykeyattribute）。返回给定主键的对象。如果找不到对象，抛出一个未找到的异常。
Find
试图从与指定类型关联的表中检索给定主键的对象。使用这种方法需要特定类型指定一个主键（使用primarykeyattribute）。
返回主键或空的对象。
BeginTransaction
开始一个新的事务
IsInTransaction
是否在事务中
SaveTransactionPoint
创建在事务当前时间点在数据库中保存。如果一个不在进行中，就开始一个新的事务。
返回保存点的字符串名称
Rollback
回滚事务的开始点或者保存点
Release
释放一个保存点
DoSavePointExecute
Commit
提交已开始的事务
RunInTransaction
在执行动作（可能是嵌套的）被包装在一个事务保存点。如果异常发生时整个事务回滚，不只是现在的保存点。唯一的例外是重抛出。
InsertAll
插入所有指定的对象。
Insert
插入指定的对象和检索其自增主键如果有。
InsertOrReplace
插入指定的对象和检索其自增主键如果有。如果一个唯一的约束冲突发生在一些预先存在的对象上，这个函数将删除旧的对象。
Update
更新使用指定的对象的表的所有列，除了它的主键以外。
UpdateAll
更新所有指定的对象。
Delete
使用它的主键从数据库中删除给定的对象。
DeleteAll
从指定的表中删除所有对象。
Dispose
Close
多表连接
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
| using SQLite4Unity3d; using UnityEngine; using UniRx; public class test : MonoBehaviour { SQLiteConnection _connection; void Start() { var dbPath = string.Format(@"Assets/StreamingAssets/tempDB.sqlite3"); _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create); createPerson(); createPersonType(); var cmd = new SQLiteCommand(_connection); cmd.CommandText = //"select Person.Name as name,PersonType.TypeName as type from Person,PersonType where Person.typeId = PersonType.id limit 10;"; //左外连接 //"select Person.Name as name,PersonType.TypeName as type from Person left outer join PersonType on Person.typeId = PersonType.id limit 10;"; //内连接 "select Person.Name as name,PersonType.TypeName as type from Person join PersonType on Person.typeId = PersonType.id limit 10;"; //自然连接 //"select Person.Name as name,PersonType.TypeName as type from Person natural join PersonType limit 10;"; var result = cmd.ExecuteQuery<val>(); result.ToObservable() .Do(t => { var s = string.Format("name:{0},type:{1}", t.name, t.type); s.print(); }) .Subscribe(); } public class val { public string name { get; set; } public string type { get; set; } } void createPerson() { //删除table _connection.DropTable<Person>(); //创建table _connection.CreateTable<Person>(); //插入数据 _connection.Insert(new Person {typeId = 1, Name = "Tom"}); _connection.Insert(new Person {typeId = 2, Name = "Dom"}); _connection.Insert(new Person {typeId = 1, Name = "Jake"}); _connection.Insert(new Person {typeId = 2, Name = "Rose"}); //整体插入 _connection.InsertAll( new[] { new Person {typeId = 3, Name = "Ben"}, new Person {typeId = 3, Name = "Lily"}, }); } void createPersonType() { _connection.DropTable<PersonType>(); _connection.CreateTable<PersonType>(); _connection.Insert(new PersonType {TypeName = "hehe"}); _connection.Insert(new PersonType {TypeName = "lala"}); } } public static class StringExtension { public static void print(this string ss) { Debug.Log(ss); } public static void print(this int ii) { print(ii.ToString()); } } public class Person { [PrimaryKey,AutoIncrement] public int Id { get; set; } public int typeId { get; set; } public string Name { get; set; } } public class PersonType { [PrimaryKey,AutoIncrement] public int Id { get; set; } public string TypeName { get; set; } }
|