常用 SQLite 语句 及 SQLite3 的使用

1.创建表

CREATE 创建
TABLE 表

SQL约束 用于限制加入表的数据的类型，在创建表时规定约束，主要有以下几种约束：
PRIMARY KEY主键 每个表中都应有且只能有一个主键 主键列不能为NULL值
AUTOINCREMENT自动增长
NOT NULL 非空 约束强制列不接受空值 空值无法插入新纪录或更新纪录
UNIQUE 唯一的 约束唯一标识数据库表中的某个字段

语法:
CREATE TABLE 表名 (字段名1 字段类型，字段名2 字段类型，……)

CREATE TABLE Teacher1 (teacher_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, teacher_name TEXT,teacher_sex BOOLEAN)

CREATE TABLE Teacher2 (teacher_id INTEGER PRIMARY KEY AUTOINCREMENT, teacher_name TEXT,teacher_sex BOOLEAN)

2.插入数据
INSERT 插入 用于表中数据的增加

语法1:字段和值对应
INSERT INTO 表名 （字段1，字段2，字段3，字段4）VALUES (值1，值2，值3，值4)

INSERT INTO Students (name,sex) VALUES ('zhangsan',0)

语法2: 下方形式必须值跟字段一一对应 一个都不能少
INSERT INTO 表名 VALUES (值1，值2，值3，值4，值5)

INSERT INTO Students VALUES('张三','男', 23,175,70)

当值为文本时 需要加单引号;当值为整型不需要加引号

3.查询数据
SELECT 查询

• 全部 (表示查询表中所有数据)
  FROM

语法:
SELECT 字段名 FROM 表名
SELECT 字段名，字段名，字段名，…… FROM 表名
SELECT * FROM 表名

SELECT * FROM Students

语法:
在SELECT查询语句后+WHERE 字段名 运算符 值
WHERE语句用于有条件地从表中选取数据，也可将WHERE子句添加到SELECT语句

SELECT * FROM Students WHERE number<10
SELECT * FROM Students WHERE number<10 OR sex=1
SELECT * FROM Students WHERE number<10 AND number>5
SELECT * FROM Students WHERE age BETWEEN 10 AND 100

提供运算符有=，!=，>，<，>=，<=，BETWEEN(在某个范围内 前闭后闭)
AND和OR运算符 用于基于一个或多个条件对数据进行过滤
如果两个条件都成立 AND运算符返回一条数据
只要一个条件成立 OR运算符就返回一条数据

like 匹配
SELECT * FROM Students WHERE name like 'zhang%'
SELECT * FROM Students WHERE name like 'zhang_'
LIKE（搜索某种模式 %可以代表任意一串字符 代表一个任意字符）
%通配 多个字符，通配一个字符

count() 查询表中数据的条数。
SELECT count() FROM student

数据库提供内置函数：MAX（最大值）, MIN(最小值), AVG（平均值）SUM(总值)
语法：
SELECT MAX(字段名) FROM 表名
max(列名) min(列名) 查询这一列的最大,最小值.

SELECT max(student_age) FROM student

嵌套：
SELECT * FROM student WHERE student_age = (SELECT max(student_age) FROM student)

ORDER BY语句用于对结果集进行排序
根据指定列对结果集进行排序 默认按照升序对记录进行排序，降序DESC，升序ASC
desending 降序
asending 升序
语法：
SELECT 字段名 FROM 表名称 ORDER BY 字段名 条件（DESC或ASC）
SELECT 字段名，字段名 FROM 表名称 ORDER BY 字段名 条件，字段名 条件（DESC或ASC）

SELECT * FROM Students ORDER BY name DESC, age DESC
SELECT * FROM student ORDER BY student_age ASC

LIMIT 限制
从第六条开始 往后的15条
SELECT * FROM Students LIMIT 5,15

4.修改数据
UPDATE 更新语句用来修改表中的数据
set 设置

语法：
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
UPDATE 表名称 SET 列名称 = 新值，列名称 = 新值 WHERE 列名称 = 某值

UPDATE Students SET age = 20 WHERE name = '李雷'
UPDATE Students SET age = 20,height = 190.5 WHERE name = '李雷'
UPDATE Students set name='lisi',sex=1 where number < 10 AND number>5

5.删除数据
DELETE 删除用于删除表中的行

语法:
DELETE FROM 表名称 WHERE 列名称 = 值

DELETE FROM Students WHERE name = '小芳'
DELETE FROM Students WHERE number > 25

SQLite3的使用

/*
使用数据库的准备工作
1.导入libsqlite3.dylib静态库
2.导入sqlite3.h头文件         #import <sqlite3.h>

使用数据库注意事项
1.使用数据库时,要保证数据库文件存在
2.使用数据库前,要保证数据库是打开状态
3.使用数据库后,要关闭数据库
 */



#import "ViewController.h"
#import <sqlite3.h>
@interface ViewController ()
{
    sqlite3 *_db;
}
@property (retain, nonatomic) IBOutlet UITextField *nameText;
@property (retain, nonatomic) IBOutlet UITextField *ageTextField;

@end

@implementation ViewController

- (void)viewDidLoad
{
    [super viewDidLoad];
    NSLog(@"%@",NSHomeDirectory());
}

- (void)dealloc {
    [_nameText release];
    [_ageTextField release];
    [super dealloc];
}
#pragma mark -- 创建数据库文件并打开数据库
-(BOOL)openDatabase
{
    //数据库文件路径
    NSString *filePath = [NSHomeDirectory()stringByAppendingPathComponent:@"Documents/student.sqlite"];
    //UTF8String把oc的字符串转化成c的字符串
    const char *filename = [filePath UTF8String];
    // sqlite3_open 打开数据库的方法; 如果有数据库文件就直接打开,如果没有数据库文件系统会自动创建一个数据库文件,并打开
    //参数1.数据库文件的路径 2.数据库对象,要打开的数据库
    //返回值表示操作的状态码
    int result = sqlite3_open(filename, &_db);
    if (result == SQLITE_OK)
    {
        NSLog(@"数据库打开成功");
        return YES;
    }
    else
    {
        NSLog(@"数据库打开失败");
        //sqlite3_close 关闭数据库的方法
        sqlite3_close(_db);
        return NO;
    }
}
#pragma mark --创建表
- (IBAction)createTable:(id)sender
{
    //1.打开数据库
    if (![self openDatabase])
    {
        return;
    }
    //2.创建表
    NSString *str = @"CREATE TABLE IF NOT EXISTS student (student_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT , age INTEGER)";
    char *error = nil;
    //sqlite3_exec对数据库的操作:创建表,增删改,都是用这个方法
    //参数 1.数据库对象 2.sql语句 3.回调函数nil 4.回调函数的参数nil
    if (sqlite3_exec(_db, [str UTF8String], nil, nil, &error)==SQLITE_OK)
    {
        NSLog(@"创建表成功");
    }
    else
    {
        NSLog(@"创建表失败:%s",error);
    }
    //3.关闭数据库
    sqlite3_close(_db);
}
#pragma mark -- 添加数据
- (IBAction)insertInto:(id)sender
{
    //1.打开数据库2.执行增删改查的操作3.关闭数据库
    if (![self openDatabase])
    {
        return;
    }
    NSString *str = [NSString stringWithFormat:@"insert into student (name , age) values ('%@',%d)",_nameText.text,_ageTextField.text.intValue];
    char *error = nil;
    if (sqlite3_exec(_db, [str UTF8String], nil, nil, &error)==SQLITE_OK)
    {
        NSLog(@"插入数据成功");
    }
    else
    {
        NSLog(@"插入数据失败:%s",error);
    }
    sqlite3_close(_db);
}
#pragma mark -- 删除数据
- (IBAction)delete:(id)sender
{
    if (![self openDatabase])
    {
        return;
    }
    NSString *str =@"delete from student where student_id = 6";
    char *error = nil;
    if (sqlite3_exec(_db, [str UTF8String], nil, nil, &error)==SQLITE_OK)
    {
        NSLog(@"删除成功");
    }
    else
    {
        NSLog(@"删除失败:%s",error);
    }
    sqlite3_close(_db);
}
#pragma mark -- 修改数据
- (IBAction)upData:(id)sender
{
    if (![self openDatabase])
    {
        return;
    }
    NSString *str = [NSString stringWithFormat:@"update student set name = '%@' where student_id =%d",@"马云",2];
    if (sqlite3_exec(_db, [str UTF8String], nil, nil, nil)==SQLITE_OK)
    {
        NSLog(@"修改数据成功");
    }
    else
    {
        NSLog(@"修改数据失败");
    }
    sqlite3_close(_db);
}
#pragma mark --查询数据
- (IBAction)select:(id)sender
{
    //先打开数据库,查询,关闭数据库
    if (![self openDatabase])
    {
        return;
    }
    NSString *str = @"select * from student";
    //缓存区
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare 把sql语句放入缓存区
    //参数1.数据库对象2.sql语句 3.sql语句的长度,-1全部的sql语句 4.缓存区 5.剩余部分的sql语句
    if (sqlite3_prepare(_db, [str UTF8String], -1, &stmt, nil)==SQLITE_OK)
    {
        //sqlite3_step 单步查询数据,一次只查询一条数据
        //SQLITE_ROW表示数据库中还有数据继续下一个查询
        //SQLITE_DONE 表示数据查询完毕
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            //sqlite3_column_text 查询字符串的方法
            //参数1.缓存区 2.字段所在的列号
            char *cname = (char *)sqlite3_column_text(stmt, 1);
            int age = sqlite3_column_int(stmt, 2);
            //stringWithUTF8String 把c的字符串转化成oc的字符串
            NSString *name = [NSString stringWithUTF8String:cname];
            NSLog(@"%@---%d",name,age);
        }
        //查询结束,释放缓存区
        sqlite3_finalize(stmt);
    }
    sqlite3_close(_db);
}
-(void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event
{
    [self.view endEditing:YES];
}
@end