SQLite数据库操作

SQLite 数据库操作

.databases //查看数据库列表
.tables    //查看数据表列表
.schema    //查看数据表结构
.quit .exit //退出

创建数据库

sqlite3 test.db

SQLite数据类型

1.NULL：空值。
2.INTEGER：带符号的整型，具体取决有存入数字的范围大小。
3.REAL：浮点数字，存储为8-byte IEEE浮点数。
4.TEXT：字符串文本。
5.BLOB：二进制对象。

sqlite3也支持如下的数据类型：

 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 包含了 年、月、日、时、分、秒、千分之一秒。
 datetime 包含日期时间格式，必须写成'2010-08-05'不能写为'2010-8-5'，否则在读取时会产生错误！

数据定义语言(DDL)

primary key  主键： 唯一，自增长， 一张表只能有一个主键
unique  唯一约束：修饰的列值必须唯一，一张表可以有多个唯一约束
check   检查约束，create table User(id Integer primary key, name char, age Integer check(age>0));//检查age大于0
not null 修饰列的值不能为空
default  修饰列的默认值，当没有指定值的时候

CREATE 创建表

create table User(id integer primary key, name char not null, age integer);

ALTER 修改表结构

//添加一列
alter table table_name add addr char;

//修改表名称
alter table table_name rename to new_table_name;

DROP 删除表

//删除表
drop table table_name;

复制一张表

create table [new_table_name] as select * from [table_name];

复制一张表的部分内容

create table [new_table_name] as select * from [table_name] where id between 1001 and 1002;

修改表结构

create table [new_table_name](id Integer primary key, name char, age Integer, addr char);//创建一张新表

insert into [new_table_name](id,name,age) select id,name,age from [table_name] ;//插入从旧表查询到的数据

drop table [table_name];//删除旧表

alter table [new_table_name] rename to [table_name];//更新新表名称为旧表名称

数据操作语言(DML)

INSERT 插入数据

insert into [table_name] (id, name, age) values (1001, 'zhangshan', 18);

UPDATE 更新数据

update [table_name] set name='wangwu',age=21 where id=1001;

DELETE 删除数据

delete from [table_name] where id=1001;
delete from [table_name] where id=1001 or name='zhangshan';

数据查询语言(DQL)

SELECT 查询数据

where条件

select * from [table_name] where id>=1001;//查找id>=1001的数据

select * from [table_name] where id between 1001 and 1002;//查找左右闭区间

select * from [table_name] where id in(1001,1002);//查找id为in里面的条件，查找id=1001，id=1002的数据

select * from [table_name] where name like '%shan';//%可以匹配多个字符，这表示匹配字符串为shan结尾的数据

like 模糊匹配

%    匹配零个、一个、多个 数字或字符
_  	 匹配单一数字或字符
*	 零个、一个、多个 数字或字符
?	 单一 数字或字符

select * from [table_name] where name like '%shan';//%可以匹配多个字符，这表示匹配字符串为shan结尾的数据

limit 分页查询

select * from user limit 0,2;//从0开始查询2行数据

order by 排序

ASC   默认值，从小到大排序
DESC  从大到小排序

select * from user order by age asc;

group by 分组

select class,count(id) from student group by class;//查找每个class组中的数据个数

select class,count(id) from student group by class having id>= 1001;//查找每个class组中,id>=1001的数据个数

distinct消除重复数据

select distinct columnName from tableName;

事务

begin;//开启事务
rollback;//回滚事务
commit;//提交事务

函数

avg(columnName) 求平均值
count(columnName) 求数据个数
max(a, b) 求最大值
min(a, b) 求最小值
sum(columnName) 求和
length() 求字符串长度
abs() 求绝对值
upper() 转大小字母
lower() 转小写字母

多表查询

CROSS JOIN：笛卡尔积

//查找结果为两张表的乘积
select * from student,teacher;

INNER JOIN：内连接(查询两张表中的交集数据)

//隐式内连接
select student.name,teacher.name from student,teacher where student.tid=teacher.id;

//隐式内连接(表的别名)
select s.name,t.name from student s,teacher t where s.tid=t.id;

//显式内连接
select s.name,t.name from student s inner join teacher t on s.tid=t.id;

OUTER JOIN：外连接(简写：LEFT JOIN 查询左表中的所有数据，右表数据没有值为空)

select s.name,t.name from student s left join teacher t on s.tid=t.id;

参考文档

https://blog.youkuaiyun.com/qq_45025976/article/details/127944030