SQLite数据库的一些基本操作

什么是SQLite

SQLite 是一个小型的、无数据类型的、开源的，可嵌入的关系型数据库，它支持事务操作。

1.数据类型

1.1  SQLite使用动态数据类型，根据存入的值自动进行判断，对于每一个值都属于下面存储类型之一

NULL：空值；

INTEGER：整型

REAL：浮点数类型

TEXT：值为文本字符串

BLOB：存储Blob数据，该类型数据和输入的数据完全相同；

1.2  为了最大化SQLite和数据库引擎之间的数据库兼容，SQLite提出类型亲缘性（Type Affinity）的概念，在表字段声明后，SQLite会根据字段声明的类型选择一种亲缘类型。

2. sql语句(几个重要的参数 表名，字段名，数据类型 约束条件)

2.1 创建表

create table 表名 (字段名称 数据类型 约束 &＃8230;&＃8230;)

create table person(_id Integer primary key,name varchar(10),age Integer not null)

2.2 删除表

create table 表名

create table person

2.3 插入数据

insert into 表名 （字段名称1，字段名称2）values (值1，值2);

insert into person(_id,age) values (1,20)

insert into person values (2,&＃8221;gzc&＃8221;,20);

2.4 修改数据

update 表名 set 字段=新值 where 修改的条件

update person set name =&＃8221;gzc&＃8221; ,age = 23 where _id =1

2.5 删除数据

delete from 表名 where 删除的条件

delete from person where _id = 2

2.6 查询语句

select 字段名 from 表名 where 查询条件 group by 分组的字段 having 筛选的条件 order by 排序的字段

select _id,name from person

select * from person where _id = 1

select * from person where _id <>1

select * from person where _id=1 and age <18

select * from person where age between 10 and 20

select * from person where name like &＃8220;%陈%&＃8221;

select * from person where name like &＃8220;_陈%&＃8221;&＃8221;_&＃8221;代表一个字符 &＃8220;%&＃8221; 代表多个字符

select * from person where name is null

select * from person where age >20 order by _id

//增

sqlite> create table student(id integer , name varchar(3),age int);//创建一个table

create table student(id integer primary key autoincrement,age int,name varchar(30));

sqlite> .table//显示所有

sqlite> insert into student values(1,&＃8221;jack&＃8221;,18); //添加

sqlite> insert into student(stuNo,name)values(3,&＃8217;tom&＃8217;);

alter table student add column job Stirng; //增加某一列；

//删

delete from student;//删除所有数据；

delete from student where id = 1;//删除id = 1 的所有数据；

drop table student;

//改

update student set name = job,age = 12 where id = 1;

update student set name = &＃8216;lucy&＃8217; where id = 52;

//查

select * from student //查询全部；

sqlite> select name from student; //按照name选择

sqlite> select * from student where stuNo>2;//按照stuNo选择

sqlite> select * from student where stuNo>2 and name = &＃8216;tom&＃8217;;

sqlite> select * from student where stuNo>2 or name = &＃8216;tom&＃8217;;

sqlite> select * from student where stuNo in(2,3);//选择某一个区间的值

sqlite> select * from student where stuNo not in(2,3);//选择不再某个区间的值

select * from student order by stuNo desc;//降序

select * from student order by stuNo asc;//升序

聚合函数

select count(*) from student; //查询所有记录数；

select count(age) from student; //查询某个字段的集合；

select sum(age) from student;//求和

select min(stuNo) from student;//求最小值

select max(stuNo) from student;//求最大值

select avg(age) from student;//求平均值

select * from student where name like&＃8217;j%&＃8217;;//以j开头;

select * from student where name like&＃8217;%k&＃8217;;//以k结尾;

select * from student where name like&＃8217;%o%&＃8217;;//中间含有o;

select * from student where id in(5,7);

select * from student where id>=5 and id<=7;

select * from student where id between 5 and 7;