sqlite3 常用语法 增删改查
来源:互联网 发布:淘宝女鞋代销 编辑:程序博客网 时间:2024/05/16 16:03
sqlite3 常用语法 增删改查
创建表名 t_student 主键为 id
CREATE TABLE t_student (id integer PRIMARY KEY, name text,age integer,score real);
如果不存在就创建
CREATE TABLE IF NOT EXISTS t_student (id integer PRIMARY KEY, name text,age integer,score real);
主键自动长
CREATE TABLE IF NOT EXISTS t_student (id integer PRIMARY KEY AUTOINCREMENT, name text,age integer,score real);
增加数据
INSERT INTO t_student (name,age,score) VALUES ('jams',10,10);
更新 全部设了99
UPDATE t_student SET score = 99;
删除内容 某
DELETE FROM t_student WHERE id = 2;
删除内容表内全部
DELETE FROM t_student;
删除整表
DROP TABLE t_student;
查找
SELECT * FROM t_student WHERE age > 50;
查找 总数
SELECT count(name) FROM t_student WHERE age > 50;
模糊查询 %5%
SELECT name , phone FROM t_student WHERE phone LIKE '%%%@%%'
/别名/
SELECT name as myName, age as myAge, score as myScore FROM t_student;
SELECT name myName, age myAge, score myScore FROM t_student;
SELECT s.name myName, s.age myAge, s.score myScore FROM t_student s WHERE s.age > 50;
/排序/
SELECT FROM t_student WHERE score > 50 ORDER BY age DESC;
SELECT FROM t_student WHERE score < 50 ORDER BY age ASC , score DESC;
/分页/ 从第几个后 开始 取多少个 0 5 5 5 10 5
SELECT * FROM t_student ORDER BY id ASC LIMIT 30, 10;
/简单约束/ 主键PRIMARY KEY 自动增长AUTOINCREMENT
不会空 NOT NULL 默认DEFAULT 1
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER);
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, age INTEGER);
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER DEFAULT 1);
------------------------oc创建数据库-------------------------------
添加动态库
import "ViewController.h"
import <sqlite3.h>
添加动态库
import "ViewController.h"
import <sqlite3.h>
@interface ViewController ()
{
sqlite3 *_db;
}
@end
@implementation ViewController
(void)viewDidLoad {
[super viewDidLoad];
//1. 创建了一个数据库的 文件//1> 数据库文件路径 2>数据库的句柄 3>
NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *dbPath = [path stringByAppendingPathComponent:@"test.sqlite"];
NSLog(@"%@",dbPath);
//打开数据库文件 如果没有就创建一个
int result = sqlite3_open(dbPath.UTF8String, &_db);if (result == SQLITE_OK) {
NSLog(@"创建数据库成功!");
//2. 创建表 指定字段 NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS t_student (id INTEGER PRIMARY KEY AUTOINCREMENT , name TEXT , score REAL)"]; char *eror = nil; int tableOk = sqlite3_exec(_db, sql.UTF8String, NULL, NULL,&eror); if (tableOk == SQLITE_OK) { NSLog(@"创建表成功!"); }else{ NSLog(@"创建表失败!"); } //3. 填充数据 //crud}else{ NSLog(@"创建数据库失败!");}
}
(IBAction)adddb {
//2. 增加 数据 100条
for (int i = 0; i < 100; i++) {
NSString *sql = [NSString stringWithFormat:@"INSERT INTO t_student (name , score) VALUES('JACK-%d',%f)",i,arc4random_uniform(1000)/10.0]; char *eror = nil; int tableOk = sqlite3_exec(_db, sql.UTF8String, NULL, NULL,&eror); if (tableOk == SQLITE_OK) { NSLog(@"添加数据成功!"); }else{ NSLog(@"添加数据失败!"); }
}
}
(IBAction)deletedb {
NSString sql = [NSString stringWithFormat:@"DELETE FROM t_student WHERE score < 20"];
char eror = nil;
int tableOk = sqlite3_exec(_db, sql.UTF8String, NULL, NULL,&eror);if (tableOk == SQLITE_OK) {
NSLog(@"删除数据成功!");
}else{
NSLog(@"删除数据失败!");
}
}(IBAction)updatedb {
NSString sql = [NSString stringWithFormat:@"UPDATE t_student SET name = 'kobe' WHERE id < 50"];
char eror = nil;
int tableOk = sqlite3_exec(_db, sql.UTF8String, NULL, NULL,&eror);if (tableOk == SQLITE_OK) {
NSLog(@"更新数据成功!");
}else{
NSLog(@"更新数据失败!");
}
}
- (IBAction)selectdb {
//参数1> 数据库的指针 2>sql语句 3> 负责接收处理结果sqlite3_stmt *stmt = nil;NSString *sql = [NSString stringWithFormat:@"SELECT id,name , score FROM t_student WHERE score > 60"];int result = sqlite3_prepare_v2(_db, sql.UTF8String, -1, &stmt, NULL);if (result == SQLITE_OK) { while(sqlite3_step(stmt) == SQLITE_ROW){ int s_id = sqlite3_column_int(stmt,0); unsigned char *name = (unsigned char *) sqlite3_column_text(stmt,1); double score = sqlite3_column_double(stmt,2); NSLog(@"%d - %@ - %f",s_id,[NSString stringWithUTF8String:(const char *)name],score); }}
}
@end
- sqlite3 常用语法 增删改查
- mysql的增删改查常用语法
- SQL常用语法 - 增删改查
- sqlite3增删查改应用
- mysql常用语句语法,增删改查方法。
- yii2 + mysql 常用增删改查操作语法以及事务
- sql增删改查语法
- oracle增删改查语法
- Sqlite3 增删改查操作实例
- IOS sqlite3 数据增删改查
- sqlite3增删改查C++类实现
- django sqlite3增删改查
- sqlite3增删改查,导入导出
- C++实现Sqlite3增删改查
- django sqlite3 后台数据库的增删改查
- yii 常用增删改查
- 增删查改常用代码
- mysql常用增删改查
- PHP格式化金额,小数位截取而非四舍五入,末尾控制是否0补齐
- Levenshtein distance
- HDU 5901 Count primes (Meisell-Lehmer算法 -- 模板)
- swift UIImageView
- 服务性能监控都包括哪些指标?
- sqlite3 常用语法 增删改查
- 五子棋(人机)-空子产生的序列的优化
- seeheim
- 笔记
- QT 透明 半透明 效果
- ToolBar自定义图标,关联DrawerLayout
- maven笔记1--安装及使用
- 实用网站总结
- 断开SVN连接