SQL语句三种分类:DML、DDL、DCL(数据库控制语言)
DDL 数据库操作语言:
--创建表:
CREATE TABLE IF NOT EXISTS TestTable (field1 TEXT PRIMARY KEY, field2 TEXT NOT NULL, field3 TEXT);
--更改表结构,添加一个新人字段(不能直接删除字段)
ALTER TABLE TestTable ADD field4 TEXT;
--删除表
DROP TABLE TestTable;
DML 数据库定义语言 :
--更
UPDATE TestTable SET field1 = "update" WHERE field1 = "condition";
--删
DELETE FROM TestTable WHERE field1 = "condition1" AND field2 = "condition2";
DELETE FROM TestTable WHERE field1 = "condition1" OR field2 = "condition2";
--改
INSERT OR REPLACE INTO TestTable (field1, field2, field3) VALUES("p1", "p2", "p3");
--查
SELECT * FROM TestTable; --所有
SELECT field1, field2 FROM TestTable;
SELECT COUNT(*) FROM TestTable; --数量
SELECT COUNT(DISTINCT(*)) FROM TestTable; --去除重复的数量
//-----------------------------在IOS编程中使用sql-------------------------------------
在IOS编程中使用sql:
//使用SQLite数据库导入libsqlite3.0.dylib
//并#import <sqlite3.h>
//常用方法:
//打开数据库
//使用C的函数接口,所以NSString要使用[string UTF8String]
sqlite3_open(const char *filename, sqlite3 **ppDb);
//关闭数据库
sqlite3_close(sqlite3 *);
//执行sql语句
sqlite3_exec(sqlite3 *, const char *sql, int (*callback)(void *, int, char **, char **), void *, char **errmsg);
//编译sql语句
sqlite3_prepare_v2(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail);
//执行查询sql语句
sqlite3_step(sqlite3_stmt *);
//结束sql语句
sqlite3_finalize(sqlite3_stmt *pStmt);
//绑定参数(text是绑定参数类型)
sqlite3_bind_text(sqlite3_stmt *, int, const char *, int n, void (*)(void *));
//查询字段上的数据
sqlite3_column_text(sqlite3_stmt *, int iCol);
使用SQL
- (void)_initSQLite {
sqlite3 *sqlite = nil;
NSString *filePath = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/data.sqlite"];
//如果文件不存在,sqlite3_open会创建这个sqlite文件(测试)
if (sqlite3_open([filePath UTF8String], &sqlite) != SQLITE_OK) {
NSLog(@"打开数据库失败");
}
//创建表的SQL语句
NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS TestTable (field1 TEXT NOT NULL PRIMARY KEY, field2 TEXT NOT NULL)";
char *error;
//执行SQL语句,判断是否执行成功
if (sqlite3_exec(sqlite, [sqlStr UTF8String], NULL, NULL, &error) != SQLITE_OK) {
NSLog(@"创建表失败, %s", error);
return;
}
//----------------------------数据-----------------------
sqlite3_stmt *stmt = nil;
//创建插入数据的SQL语句,?表示点位符可逐个填充
NSString *insertStr = @"INSERT OR REPLACE INTO TestTable (field1, field2) VALUES (?, ?)";
//编译SQL语句
sqlite3_prepare_v2(sqlite, [insertStr UTF8String], -1, &stmt, NULL);
NSString *field1Str = @"first";
NSString *field2Str = @"hehe";
//往SQL中填充数据,起始位是1(没有0)
sqlite3_bind_text(stmt, 1, [field1Str UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [field2Str UTF8String], -1, NULL);
//执行SQL语句
int result = sqlite3_step(stmt);
if (result == SQLITE_ERROR || result == SQLITE_MISUSE) {
NSLog(@"执行SQL语句失败1");
}
//不用占位符
NSString *insertStr2 = @"INSERT OR REPLACE INTO TestTable (field1, field2) VALUES ('second', 'haha')";
sqlite3_prepare_v2(sqlite, [insertStr2 UTF8String], -1, &stmt, NULL);
result = sqlite3_step(stmt);
if (result == SQLITE_ERROR || result == SQLITE_MISUSE) {
NSLog(@"执行SQL语句失败2");
}
//不用编译直接执行
NSString *insertStr3 = @"INSERT OR REPLACE INTO TestTable (field1, field2) VALUES ('third', 'en')";
result = sqlite3_exec(sqlite, [insertStr3 UTF8String], NULL, NULL, &error);
if (error != Nil) {
NSLog(@"执行SQL语句失败3, %s", error);
}
//-------------------------------查询------------------------------------------
NSString *searchStr = @"SELECT * FROM TestTable";
sqlite3_stmt *selectStmt = nil;
sqlite3_prepare_v2(sqlite, [searchStr UTF8String], -1, &selectStmt, NULL);
result = sqlite3_step(selectStmt);
//#define SQLITE_ROW 100 /* sqlite3_step() has another row ready */
//#define SQLITE_DONE 101 /* sqlite3_step() has finished executing */
while (result == SQLITE_ROW) {
//取出索引为0、1、2字段的数据
char *field1 = (char *)sqlite3_column_text(selectStmt, 0);
char *field2 = (char *)sqlite3_column_text(selectStmt, 1);
NSLog(@"field1:%s, field2:%s", field1, field2);
//游标移到下一条结果数据
result = sqlite3_step(selectStmt);
}
//关闭数据库句柄
sqlite3_finalize(stmt);
sqlite3_finalize(selectStmt);
//关闭数据库
sqlite3_close(sqlite);
}