数据库
sqlite
1 用下载好的安装包安装
linux@ubuntu:~/sqlite3$ sudo dpkg -i libsqlite3-0_3.7.2-1ubuntu0.1_i386_1.deb
[sudo] password for linux:
(Reading database ... 189580 files and directories currently installed.)
Preparing to replace libsqlite3-0 3.7.2-1ubuntu0.1 (using libsqlite3-0_3.7.2-1ubuntu0.1_i386_1.deb) ...
Unpacking replacement libsqlite3-0 ...
Setting up libsqlite3-0 (3.7.2-1ubuntu0.1) ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place
linux@ubuntu:~/sqlite3$ sudo dpkg -i libsqlite3-dev_3.7.2-1ubuntu0.1_i386_2.deb
(Reading database ... 189580 files and directories currently installed.)
Preparing to replace libsqlite3-dev 3.7.2-1ubuntu0.1 (using libsqlite3-dev_3.7.2-1ubuntu0.1_i386_2.deb) ...
Unpacking replacement libsqlite3-dev ...
Setting up libsqlite3-dev (3.7.2-1ubuntu0.1) ...
linux@ubuntu:~/sqlite3$ sudo dpkg -i sqlite3_3.7.2-1ubuntu0.1_i386_3.deb
Selecting previously unselected package sqlite3.
(Reading database ... 189580 files and directories currently installed.)
Unpacking sqlite3 (from sqlite3_3.7.2-1ubuntu0.1_i386_3.deb) ...
Setting up sqlite3 (3.7.2-1ubuntu0.1) ...
Processing triggers for man-db ...
linux@ubuntu:~/sqlite3$
linux@ubuntu:~/sqlite3$ sqlite3 new.db //创建一个数据库并进入SQLITE命令环境
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .help //点开头的命令不用加分号 .help 查看帮助文件
sqlite> .quit //退出
数据库:将所有的数据 各种类型 描述各种事物的数据放到一起
数据表:描述某一个事物基本信息的表
记录:表里的一条信息
字段:列名
数据类型:
interger 32位整数
double 64位实数
varchar(n) 长度不固定 且其最大长度n n不能超过4000
sqlite> create table child_info(name varchar(20),age integer,sex varchar(10));//创建表
sqlite> .schema child_info//.schema 查看表名
CREATE TABLE child_info(name varchar(20),age integer,sex varchar(10));
sqlite> insert into child_info values("zhao",23,"femal");//插入记录 所有字段都填入数据
sqlite> .schema child_info
CREATE TABLE child_info(name varchar(20),age integer,sex varchar(10));
sqlite> select *from child_info;//查看表内信息
zhao|23|femal
sqlite> insert into child_info values("qian",21,"male");
sqlite> select *from child_info;
zhao|23|femal
qian|21|male
sqlite> insert into child_info(name)values("sun"); //只给某些字段添加值
sqlite> select *from child_info;
zhao|23|femal
qian|21|male
sun||
sqlite> delete from child_info where name=‘sun’;//删除名字为"sun"的记录
sqlite> select *from child_info;
zhao|23|femal
qian|21|male
li|12|
||femal
sqlite> select *from child_info where name='zhao' and age=23; //查找名字为zhao 年龄为23的记录
其他各种查询
1 查询年龄是25的记录
select * from stu_info where age = 25;
2 查询年龄 > 25的记录
select * from stu_info where age > 25;
3 查询记录 只想看编号、姓名字段
select number, name from stu_info where age = 25;
4 如果想按顺序输出所有记录,按年龄排序
select * from stu_info order by age; //默认情况是升序排列
select * from stu_info order by age desc; //降序排列
5 模糊查询(假设有一个人名我记不住了,但能记住名字中的一个字, 就可以用模糊查询)
查询名字当中带l字符的人
select * from stu_info where name like '%li%';
6 多条件查询
想查询年龄大于 25, 编号 > 10001 的学生信息
select * from stu_info where age > 26 and number > 1001 and name like '%l%';
7 统计记录条数
select count(*) from stu_info;
select count(*) from stu_info where age = 25;
8 统计所有人的年龄总和
select sum(age) from stu_info;
9 统计所有人的年龄平均值
select avg(age) from stu_info;
删除记录
delete from stu_info; //删除所有记录
delete from stu_info where name = 'lisi';
修改记录
zhangsan , 年龄改成30
update stu_info set age = 30 where name = 'zhangsan';
update stu_info set age = 30, sex = 'nan' where name = 'zhangsan';
c操作数据库
1 打开数据库
编译时 加:-lsqlite3
#include<sqlite3.h>
sqlite3_open(char *path,sqlite3 **db);
参数1:数据库名
参数2:创建的数据库连接的对象
成功返回0 失败-1
#include<stdio.h>
#include<sqlite3.h>
int main()
{
sqlite3 *p = NULL;
if(sqlite3_open("snew.db",&p)==0)//如果数据库不存在 则创建
{
printf("database open suc!\n");
}
return 0;
}
编译:gcc a.out -lsqlite3
int sqlite3_exec(sqlite3*db,const char *sql,sqlite3_callbak,void *data,char**errmsg);
参数1:打开的数据库
参数2:将要执行的sql语句
参数3,4,5 传NULL
#include<stdio.h>
#include<sqlite3.h>
int main()
{
sqlite3 *p = NULL;
if(sqlite3_open("new.db",&p)==0)
{
printf("database open suc!\n");
sqlite3_exec(p,"delete from child_info where name='zhao'",NULL,NULL,NULL);
}
sqlite3_close(p);
return 0;
}
int sqlite3_get_table(sqlite3 *db,//数据库连接对象
const char *sql;//将要执行的sql语句
char***result,//查询结果集
int *row;//结果集的行数(不包括字段)
int *column;//结果集的列数
char **errmsg;//查询的 错误信息
)
功能:获取表查询结果
void sqlite3_free_table(char **result);
功能:释放查询结果的内存
#include<stdio.h>
#include<sqlite3.h>
int main()
{
sqlite3 *p = NULL;
if(sqlite3_open("new.db",&p)==0)
{
printf("database open suc!\n");
}
int i;
char *sql = "select *from child_info";
char **result= NULL;
int row,col;
sqlite3_get_table(p,sql,&result,&row,&col,NULL);
for(i=0;i<(row+1)*col;i++)
{
printf("%12s",result[i]);
}
printf("\n");
sqlite3_free_table(result);
sqlite3_close(p);
return 0;
}