SQL(Structured Query Language)即結構化查詢語言。本文先用最快速度過一遍常用語句,再通過一個完整的學生信息管理示例,把 SQLite 在 iOS 中的增刪改查走通。
建表
create table if not exists Teacher(
tea_id integer primary key autoincrement,
tea_name text,
tea_gender text,
tea_age integer,
tea_salary integer
);
增
insert into Teacher(tea_id, tea_name, tea_gender, tea_age, tea_salary)
values(110, '小韓哥', '男', 18, 1000);
查
select * from Teacher; -- 全部字段、全部數據
select tea_name, tea_gender from Teacher; -- 指定字段
select * from Teacher where tea_name = '小韓哥'; -- 按條件
改(務必帶唯一標識,否則會影響整表)
update Teacher set tea_gender = '女' where tea_id = 111;
刪
delete from Teacher where tea_id = 110; -- 刪除指定行
delete from Teacher; -- 清空所有數據
drop table Teacher; -- 刪除整張表
一句話記住區別:delete 刪數據、表結構還在;drop 連表一起刪。
布局很簡單:4 個 UITextField(學號、姓名、性別、年齡)+ 5 個按鈕(增、刪、改、查全部、查單個)。

// Student.h
@interface Student : NSObject
@property (nonatomic, assign) NSInteger number;
@property (nonatomic, copy) NSString *name;
@property (nonatomic, copy) NSString *gender;
@property (nonatomic, assign) NSInteger age;
@end
// Student.m
@implementation Student
- (void)dealloc
{
self.name = nil;
self.gender = nil;
[super dealloc];
}
- (NSString *)description
{
return [NSString stringWithFormat:@"%ld %@ %@ %ld",
(long)self.number, self.name, self.gender, (long)self.age];
}
@end
把數據庫邏輯集中到一個單例類裡,界面層只調接口,職責清晰。
// DataBaseHandle.h
@class Student;
@interface DataBaseHandle : NSObject
+ (DataBaseHandle *)shareDataBaseHandle; // 單例
- (void)openDataBase; // 打開
- (void)closeDataBase; // 關閉
- (void)insertStudent:(Student *)student; // 增
- (NSMutableArray *)selectAllStudent; // 查全部
- (Student *)selectOneStudentByNumber:(NSInteger)number; // 查單個
- (void)deleteOneStudentByNumber:(NSInteger)number; // 刪
- (void)updateStudentGender:(NSString *)gender ByNumber:(NSInteger)number; // 改
@end
實現文件的核心部分:
#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "Student.h"
@implementation DataBaseHandle
static DataBaseHandle *handle = nil;
static sqlite3 *db = nil;
+ (DataBaseHandle *)shareDataBaseHandle
{
@synchronized (self) {
if (handle == nil) {
handle = [[DataBaseHandle alloc] init];
[handle openDataBase]; // 一創建就具備訪問能力
}
}
return handle;
}
// 數據庫放在 Documents 目錄下
- (NSString *)dataBasePath
{
return [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject]
stringByAppendingPathComponent:@"student.sqlite"];
}
使用 SQLite 前記得引入 libsqlite3.0.dylib 動態庫並導入 sqlite3.h。建議引用快捷方式而非實體版本號,這樣系統升級時不必改配置。
打開數據庫並建表
- (void)openDataBase
{
NSString *dbPath = [self dataBasePath];
// 文件不存在會自動創建,存在則直接打開
int result = sqlite3_open([dbPath UTF8String], &db);
if (result == SQLITE_OK) {
NSString *sqlString = @"create table if not exists Student("
"stu_number integer primary key autoincrement,"
"stu_name text,"
"stu_gender text,"
"stu_age integer)";
sqlite3_exec(db, [sqlString UTF8String], NULL, NULL, NULL);
} else {
NSLog(@"數據庫打開失敗");
}
}
- (void)closeDataBase
{
int result = sqlite3_close(db);
NSLog(@"%@", (result == SQLITE_OK) ? @"關閉成功" : @"關閉失敗");
}
增
- (void)insertStudent:(Student *)student
{
[self openDataBase];
NSString *sqlString = @"insert into Student(stu_name, stu_gender, stu_age) values(?, ?, ?)";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
// ? 的下標從 1 開始
sqlite3_bind_text(stmt, 1, [student.name UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [student.gender UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 3, (int)student.age);
sqlite3_step(stmt); // 真正執行
}
sqlite3_finalize(stmt); // 釋放 statement
[self closeDataBase];
}
這裡刻意用了參數綁定而不是字符串拼接,既能避免拼接錯誤,也能防止 SQL 注入——即使 demo 簡單,這個習慣也值得從第一次寫起就養成。
查全部
- (NSMutableArray *)selectAllStudent
{
[self openDataBase];
NSString *sqlString = @"select * from Student";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
if (result != SQLITE_OK) {
sqlite3_finalize(stmt);
[self closeDataBase];
return nil;
}
NSMutableArray *array = [NSMutableArray arrayWithCapacity:0];
// 每調用一次 sqlite3_step 就前進一行,返回 SQLITE_ROW 說明還有數據
while (sqlite3_step(stmt) == SQLITE_ROW) {
int number = sqlite3_column_int(stmt, 0); // 列下標從 0 開始
NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSString *gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
int age = sqlite3_column_int(stmt, 3);
Student *stu = [[Student alloc] init];
stu.number = number;
stu.name = name;
stu.gender = gender;
stu.age = age;
[array addObject:stu];
[stu release];
}
sqlite3_finalize(stmt);
[self closeDataBase];
return array;
}
按學號查單個
- (Student *)selectOneStudentByNumber:(NSInteger)number
{
[self openDataBase];
NSString *sqlString = @"select * from Student where stu_number = ?";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
if (result != SQLITE_OK) {
sqlite3_finalize(stmt);
[self closeDataBase];
return nil;
}
sqlite3_bind_int(stmt, 1, (int)number);
Student *stu = [[Student alloc] init];
while (sqlite3_step(stmt) == SQLITE_ROW) {
stu.number = number;
stu.name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
stu.gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
stu.age = sqlite3_column_int(stmt, 3);
}
sqlite3_finalize(stmt);
[self closeDataBase];
return [stu autorelease];
}
刪
- (void)deleteOneStudentByNumber:(NSInteger)number
{
[self openDataBase];
NSString *sqlString = @"delete from Student where stu_number = ?";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
sqlite3_bind_int(stmt, 1, (int)number);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
[self closeDataBase];
}
改
- (void)updateStudentGender:(NSString *)gender ByNumber:(NSInteger)number
{
[self openDataBase];
NSString *sqlString = @"update Student set stu_gender = ? where stu_number = ?";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
sqlite3_bind_text(stmt, 1, [gender UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 2, (int)number);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
[self closeDataBase];
}
- (IBAction)insertStudent:(UIButton *)sender
{
// 有空值就直接返回,避免寫入髒數據
if (0 == self.nameField.text.length
|| 0 == self.genderField.text.length
|| 0 == self.ageField.text.length) {
return;
}
Student *stu = [[Student alloc] init];
stu.name = self.nameField.text;
stu.gender = self.genderField.text;
stu.age = [self.ageField.text integerValue];
[[DataBaseHandle shareDataBaseHandle] insertStudent:stu];
[stu release];
}
- (IBAction)updateStudent:(UIButton *)sender
{
NSInteger number = [self.numberField.text integerValue];
NSString *gender = self.genderField.text;
[[DataBaseHandle shareDataBaseHandle] updateStudentGender:gender ByNumber:number];
}
- (IBAction)deleteStudent:(UIButton *)sender
{
NSInteger number = [self.numberField.text integerValue];
[[DataBaseHandle shareDataBaseHandle] deleteOneStudentByNumber:number];
}
- (IBAction)selectAllStudent:(UIButton *)sender
{
NSMutableArray *contentArray = [[DataBaseHandle shareDataBaseHandle] selectAllStudent];
for (Student *stu in contentArray) {
NSLog(@"%@", stu);
}
}
- (IBAction)selectOneStudent:(id)sender
{
NSInteger number = [self.numberField.text integerValue];
Student *stu = [[DataBaseHandle shareDataBaseHandle] selectOneStudentByNumber:number];
NSLog(@"%@", stu);
}
- (void)dealloc
{
[_numberField release];
[_nameField release];
[_genderField release];
[_ageField release];
[super dealloc];
}
無論增刪改查,流程幾乎一致:
實踐中還可進一步優化:把「打開 / 關閉」抽成一次性的連接管理,避免每次操作都開關數據庫;把 Frequent 的查詢放到後台隊列,避免阻塞主線程造成界面卡頓。