Sqlite3入门简记
一,安装Sqlite3
1.入门时看http://www.runoob.com/sqlite/sqlite-intro.html,说的简单,但是适合入门
2.在终端输入sqlite3,没有返回信息,表示系统没有安装sqlite3,否则系统已安装(系统一般附带有安装)
3.需要自己安装时,到http://www.sqlite.org/sqlite-autoconf-3070500.tar.gz下载,
然后:(1) tar -zxvf sqlite-autoconf-3070500.tar.gz
(2) cd sqlite-autoconf-3070500
(3) ./configure --prefix=/xx/xxxx(/xx/xxxx表示文件生成目录)
(4) make & make install (成功后在/xx/xxxx目录下生成(bin include lib share 4个目录))
其中bin放置可执行文件sqlite3,./sqlite3 可进入sqlite命令行界面
include放置头文件
lib放置库文件
二,用的较多的命令
(1)获取点命令清单:.help
(2)退出提示符:.quit .exit
(3)输出到屏幕:output stdout
(4)列出数据库名称:.databases
(5)以SQL文本格式转存数据库:.dump
三,用的较多的存储类
NULL、INTEGER、REAL、TEXT
四,很有用的语法
(1)创建数据库:sqlite3 xxx.db
(2)创建表
(3)删除表
(4)INSERT INTO语法
(5)SELECT语法
(6)WHERE语法
(7)UPDATE语法
(8)DELETE语法
五,Sqlite3 API 使用
(1)最重要的三个函数:
int sqlite3_open(const char*, sqlite3**);
int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
int sqlite3_close(sqlite3*);
还有2个:
const char *sqlite3_errmsg(sqlite3*);
void sqlite3_free(void*);
查看更多函数,下载sqlite源码包,只需要其中的sqlite3.c、sqlite.h即可。
(2)sqlite3_open 返回一个整数错误代码,=0 表示成功码,> 0都是错误码,详情看Sqlite3手册说明
函数用于打开/创建一个函数库
const char* 指定文件名,sqlite3** 指定数据库句柄,用户通过数据库句柄操作数据库
(2)sqlite3_exec返回0表示sql指令执行完毕,否则说明这次执行没有成功
函数用于执行一条或多条SQL语句,SQL语句之间用“;”隔开
sqlite3*指定已打开的数据库句柄,const char *sql 指定SQL指令,sqlite_callback 在回调函数中可以获得SQL执行的结果
void* 指定传给回调函数的数据 , char** 指定命令执行失败的详细错误信息
(3)回调函数
(4) sqlite3_close 关闭数据库文件,参数是数据库句柄
(5)sqlite3_errmsg 返回错误码所对应的文字说明,参数是数据库句柄
(6)sqlite3_free 释放存放错误信息的内存空间,sqlite3_errmsg 返回的errmsg必须用此函数释放
(7)简单测试代码
#include <stdio.h>
#include <sqlite3.h> int callback(void *pv,int argc,char **argv,char **col)
{
int cnt_i = ;
for(cnt_i =;cnt_i < argc;cnt_i++)
{
printf("%s\t%s\n",col[cnt_i],argv[cnt_i]);
}
printf("\n");
return ;
} int main(void)
{
sqlite3 *db;
int result = ;
char *rerrmsg = NULL;
char *sql = NULL;
char *data = "callback"; result = sqlite3_open("sample.db",&db);
if(result > )
{
printf("open database err:%s\n",sqlite3_errmsg(db));
return -;
} else
{
printf("open database successfully!\n"); sql = "CREATE TABLE STUDENT(" \
"NUM INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"SORCE REAL);"; result = sqlite3_exec(db,sql,callback,NULL,&rerrmsg);
if(result != )
{
printf("creat table err:%s\n",rerrmsg);
sqlite3_free(rerrmsg);
return -;
} else
{
printf("create table successfully!\n"); sql = "INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(1,'Paul',13,99.1);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(2,'Kate',15,94.1);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(3,'Jim',12,95.1);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(4,'Tom',13,99.4);" \
"INSERT INTO STUDENT(NUM,NAME,AGE,SORCE)" \
"VALUES(5,'Jack',13,89.1);"; result = sqlite3_exec(db,sql,callback,NULL,&rerrmsg);
if(result != )
{
printf("insert data err:%s\n",rerrmsg);
sqlite3_free(rerrmsg);
return -;
} else
{
printf("insert data successfully!\n"); sql = "SELECT * FROM STUDENT";
result = sqlite3_exec(db,sql,callback,(void *)data,&rerrmsg);
if(result != )
{
printf("select data err:%s\n",rerrmsg);
sqlite3_free(rerrmsg);
return -;
} else
{
printf("select data successfully!\n");
}
}
}
} sqlite3_close(db); return ;
}
六,图形界面管理工具
以上,
2017/03/30
Sqlite3支持的数据类型
NULL,INTEGER,REAL,TEXT,BLOB
以及:
smallint 16 位元的整数。
interger 32 位元的整数。
decimal(p,s) p 精确值和 s 大小的十进位整数,精确值p是指全部有几个数(digits)大小值,s是指小数点後有几位数。如果没有特别指定,则系统会设为 p=5; s=0 。
float 32位元的实数。
double 64位元的实数。
char(n) n 长度的字串,n不能超过 254。
varchar(n) 长度不固定且其最大长度为 n 的字串,n不能超过 4000。
graphic(n) 和 char(n) 一样,不过其单位是两个字元 double-bytes, n不能超过127。这个形态是为了支援两个字元长度的字体,例如中文字。
vargraphic(n) 可变长度且其最大长度为 n 的双字元字串,n不能超过 2000
date 包含了 年份、月份、日期。
time 包含了 小时、分钟、秒。
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。
参考:
type | description |
---|---|
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, BYTEA | String types of unlimited length. Binary data must be safely encoded, see text. |
CHAR(), VARCHAR(), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT | String types of unlimited length. There is no chopping or padding performed by the database engine. |
ENUM | String type of unlimited length. In contrast to MySQL, choosing ENUM over VARCHAR does not save any storage space. |
SET | String type of unlimited length. In contrast to MySQL, the input is not checked against the list of allowed values. |
YEAR | String type of unlimited length. MySQL stores 2 or 4 digit years as a 1 byte value, whereas the SQLite drivers stores the string as provided. |
TINYINT, INT1, CHAR | A 1 byte type used to store one character, a signed integer between -128 and 127, or an unsigned integer between 0 and 255. |
SMALLINT, INT2 | 2 byte (short) integer type used to store a signed integer between -32768 and 32767 or an unsigned integer between 0 and 65535. |
MEDIUMINT | 3 byte integer type used to store a signed integer between -8388608 and 8388607 or an unsigned integer between 0 and 16777215. |
INT, INTEGER, INT4 | 4 byte (long) integer type used to store a signed integer between -2147483648 and 2147483647 or an unsigned integer between 0 and 4294967295. |
BIGINT, INT8, INTEGER PRIMARY KEY | 8 byte (long long) integer type used to store a signed integer between -9223372036854775808 and 9223372036854775807 or an unsigned integer between 0 and 18446744073709551615. See below for a discussion of INTEGER PRIMARY KEY. |
DECIMAL, NUMERIC | A string type of unlimited length used to store floating-point numbers of arbitrary precision. |
TIMESTAMP, DATETIME | A string type of unlimited length used to store date/time combinations. The required format is 'YYYY-MM-DD HH:MM:SS', anything following this pattern is ignored. |
DATE | A string type of unlimited length used to store a date. The required format is 'YYYY-MM-DD', anything following this pattern is ignored. |
TIME | A string type of unlimited length used to store a time. The required format is 'HH:MM:SS', anything following this pattern is ignored. |
FLOAT, FLOAT4, REAL | A 4 byte floating-point number. The range is -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. Please note that MySQL treats REAL as an 8 byte instead of a 4 byte float like PostgreSQL. |
DOUBLE, DOUBLE PRECISION, FLOAT8 | An 8 byte floating-point number. The range is -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. |
最新文章
- java 之return
- 【Bugly干货分享】微信文件微起底Ⅰ
- SPOJ #536. How many Fibs
- 【英语】Bingo口语笔记(29) - Run系列
- Matlab心得及学习方法(不断更新)
- 1182-IP地址转换
- ASCII码排序,hdu-2000
- 小谷的战斗Jquery(三)--水平和垂直菜单
- iOS 加载Image的两种方式
- 关于RDD
- K:树、二叉树与森林之间的转换及其相关代码实现
- More is better(并查集)
- c#二进制移位运算符 ";<;<;";及";>;>;";,";&;";,";|";,";^";
- 吴恩达课后作业学习2-week1-1 初始化
- 【python练习题】程序17
- python 使用gevent模块实现手动挡切换多协程。
- Xamarin.Android 调用原生的Jar包
- 译:2. RabbitMQ Java Client 之 Work Queues (工作队列)
- fjwc2019 D6T1 堆(组合数+打表)
- phpexcel导出excel等比例缩放图片
热门文章
- 代码生成工具更新--快速生成Winform框架的界面项目
- 关于ajax请求数据,并将数据赋值给全局变量的一些解决方法
- Pytorch学习(一)基础语法篇
- CommonJs规范详解---【XUEBIG】
- ETL测试教程
- [nodemon] clean exit - waiting for changes before restart
- VB中Winsock连续发送出现接收不到的异常问题解决方法
- 10_ for 练习 _ is Prime Number ?
- [LeetCode] Most Profit Assigning Work 安排最大利润的工作
- oracle统计数据时,涉及两个表的数据