SQLite数据库之使用

1、创建数据库

在cmd命令中使用sqlite3 test.db即可创建数据库，若已存在，则打开。

C:\Users\hj>sqlite3 test.dbSQLite version 3.9.2 2015-11-02 18:31:45Enter ".help" for usage hints.

2、查看数据库

.database 显示当前打开的数据库文件

sqlite> .databaseseq name file                                                      ---  ---------------  -------------------------------------0    main             C:\Users\hj\test1.db         

.tables 显示当前数据库的所有表

sqlite> .tablesstudentsqlite>

.schema table_name 查看表的结构

sqlite> .schema studentCREATE TABLE student(id int primary key not null,name text not null,age int not null,address char(50));

3、操作数据库

create table table_name (n1 type1, n2 type2,…);创建新表

sqlite> create table student(id int primary key not null,name text not null,age int not null,address char(50));

drop table table_name； 删除表

sqlite> drop table student;

insert into table table_name [(column1, column2,column3,…columnN)] valuse (value1, value2, value3,…valueN);向表中插入数据[]内的可省略，但后面的值的数量要和表中项目数相等。

sqlite> insert into student values (1,'zhangsan',15,'beijing');sqlite> insert into student values (2,'zhangsan',15);Error: table student has 4 columns but 3 values were suppliedsqlite> insert into student (id,name,age) values (2,'zhangsan',15);sqlite>

select column1, column2, columnN from table_name;查询指定记录
或
select * from table_name;查询所有记录

sqlite> select * from student;1|zhangsan|15|beijing2|zhangsan|15|sqlite> select id,name from student;1|zhangsan2|zhangsan

delete from table_name where [condition];从表中删除指定记录

sqlite> delete from student where id=2;sqlite> select * from student;1|zhangsan|15|beijingsqlite>

update table_name
set column1 = value1, column2 = value2…., columnN = valueN
where [condition];更新数据

sqlite> select * from student;1|zhangsan|15|beijingsqlite> update student set id=2,name=lisi where name=zhangsan;Error: no such column: lisisqlite> update student set id=2,name='lisi' where name=zhangsan;Error: no such column: zhangsansqlite> update student set id=2,name='lisi' where 'name=zhangsan';sqlite>