【SQLite3】 1_sql 通用语句介绍 [常用基础]

基础数据类型

类型	说明
int（integer）	-2^31(-2,147,483,648)到2^31(2,147,483,647)的整型数字
float	-1.79E+308到1.79E+308可变精度的数字
real	-3.04E+38到3.04E+38可变精度的数字
char	定长非Unicode的字符型数据，最大长度8000
varchar	变长非Unicode的字符型数据，最大长度8000
text	变长非Unicode的字符型数据，最大长度为2^31-1(2G)

例:

date（出生日期）	name（姓名）	gender（性别）	hobby(爱好)
char(16)	varchar(256)	char(16)	text
19940801	小白	男	C++
19940802	小黑	女	PHP

sql 通用语句

注：命令行语句结束要加 ; (分号)

创建数据库

• .open 数据库名

注: sqlite3 提供的命令行功能语句，非 sql 通用语句

.open person.db

创建数据表格

• create table 表名(字段名 数据类型, 字段名 数据类型, 字段名 数据类型);

create table child(date char(16), name varchar(256), gender char(16), hobby text);

插入数据

• insert into 表名 values('字段数据', '字段数据', ' 字段数据', '字段数据');

注：如果数据类型是 char,varchar,text 数据必须使用 '' 或 "" 引用

insert into child values('19940801', '小白', '男', 'C++');
insert into child values('19940802', '小黑', '女', 'PHP');

查询数据

• select 字段名, ..., 字段名 from 表名;

注：字段名如果是多个可以用 " , " 逗号隔开，如果是所有可以用 " * " 星号

查询全部：

sqlite> select * from child;
19940801|小白|男|C++
19940802|小黑|女|PHP

查询多个：

sqlite> select name, date from child;
小白|19940801
小黑|19940802

• select 字段名, ..., 字段名 from 表名 where 条件;

sqlite> select * from child where gender='女';
19940802|小黑|女|PHP

• 模糊条件查询使用 like（"%" 通配符）

sqlite> select * from child where date like '%1';
19940801|小白|男|C++

• and (两个条件同时成立)

sqlite> select * from child where date like '%1' and hobby like 'C%';
19940801|小白|男|C++

• or （其中一个条件成立）

sqlite> select * from child where date like '%1' or  hobby like 'P%';
19940801|小白|男|C++
19940802|小黑|女|PHP

更新数据

• update 表名 set 字段1=字段1值， 字段2=字段2值... where 条件表达式

sqlite> update child set hobby='JAVA' where name = '小黑';

sqlite> select * from child;
19940801|小白|男|C++
19940802|小黑|女|JAVA

删除数据

delete form 表名; 删除整个表数据，不会删除表格，表格任在数据库中
drop table 表名; 整个表格从数据库中删除
delete from 表名 where 条件；

.table
child

sqlite> select * from child;
19940801|小白|男|C++
19940802|小黑|女|JAVA

sqlite> delect from child where hobby='JAVA';
sqlite> select * from child;
19940801|小白|男|C++

.table
child

delete from child;
sqlite> select * from child;
sqlite>

sqlite> .table
child

.table
child

sqlite> drop table child;
sqlite> .table
sqlite>

查询创建表命令

• .schema 表名

注: sqlite3 提供的命令行功能语句，非 sql 通用语句

sqlite> .schema child
CREATE TABLE child(date char(16), name varchar(256), gender char(16), hobby text);

添加字段

• alter table 表名 add column 字段名 数据类型 [default 对应值];

sqlite> select * from child;
19940801|小白|男|C++
19940802|小黑|女|PHP

sqlite> alter table child add column height int;
sqlite> select * from child;
19940801|小白|男|C++|
19940802|小黑|女|PHP|

sqlite> update child set height=171;
sqlite> select * from child;
19940801|小白|男|C++|171
19940802|小黑|女|PHP|171

sqlite> alter table child add column addr varchar(256) default '南京';
sqlite> select * from child;
19940801|小白|男|C++|171|南京
19940802|小黑|女|PHP|171|南京

查询表结构信息

• pragma table_info(表名);

sqlite> pragma table_info(child);
0|date|char(16)|0||0
1|name|varchar(256)|0||0
2|gender|char(16)|0||0
3|hobby|text|0||0
4|height|int|0||0
5|addr|varchar(256)|0|'南京'|0

创建表格时设置字段约束

属性	说明
integer promary key autoincrement	作为主键，自动递增
not NULL	不能为 NULL
unique	唯一，不能重复
default	默认值

例：

id	name	status	online
1	led1	0	0
2	led2	0	0
3	led3	0	0
4	led4	0	1

create table device(id integer primary key autoincrement,   // 设置 id 为主键，并自增长
    name varchar(256) unique,                               // 设置 name 唯一
    status int not NULL default 0,                          // 设置 status 不能为空，默认值为 0
    online int not NULL);                                   // 设置 online 不能为空

例：

sqlite> crate table device(id integer primary key autoincrement, name varchar(256) unique, status int not NULL default 0, online int not NULL);

sqlite> .table
device

sqlite> create table device(id integer primary key autoincrement, name varchar(256) unique, status int not NULL default 0, online int not NULL);
Error: table device already exists

• if not exists 判断表格是否存在，如果不存在则创建

sqlite> .table
device

sqlite> create table if not exists device(id integer primary key autoincrement, name varchar(256) unique, status int not NULL default 0, online int not NULL);  // 注意这里 ！！
sqlite> create table if not exists device(id integer primary key autoincrement, name varchar(256) unique, status int not NULL default 0, online int not NULL);  // 注意这里 ！！

sqlite> .table
device

• 插入数据

主键的唯一性 ； unique 属性的唯一性

sqlite> select * from device;

sqlite> insert into device values(1, 'led1', 0, 0);
sqlite> select * from device;
1|led1|0|0

sqlite> insert into device values(1, 'led1', 0, 0); // 注意这里！！
Error: UNIQUE constraint failed: device.id

sqlite> insert into device values(2, 'led1', 0, 0); // 注意这里！！
Error: UNIQUE constraint failed: device.name

sqlite> insert into device values(2, 'led2', 0, 0);
sqlite> select * from device;
1|led1|0|0
2|led2|0|0
sqlite>

默认值的使用 （指定字段（列）插入，没有指定的将使用默认值）

sqlite> select * from device;
1|led1|0|0
2|led2|0|0

sqlite> insert into device (name, online) values('led3', 0);    // 注意这里 ！！
sqlite> insert into device (name, online) values('led4', 1);    // 注意这里 ！！

sqlite> select * from device;
1|led1|0|0
2|led2|0|0
3|led3|0|0
4|led4|0|1