Sqlite3安装及使用

1.简介

SQLite是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。

文件类型的数据库

嵌入式数据库

2.安装

# 环境：linux-ubuntu
sudo apt update
sudo apt install sqlite3

3.使用

--新建一个数据库
sqlite3 testDB.db
--在“sqlite>”模式下打开一个数据库，若不存在，则自动创建
.open test1.db
--删除库就像删除一个文件那样
rm -r test1.db
--查看数据库所在路径
.database

表

--创建表
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

--查看数据库中的所有表的名称
.tables
--查看数据库中的所有表的结构信息
.schema
--删除表
drop table company;
--复制一张表
create table company_copy as select * from company;
--复制一张表的部分内容
create table company_part as select*from company where id = 6;
--修改表名
alter table tb rename to tbl;

增 INSERT

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
--或者
INSERT INTO COMPANY VALUES (1, 'Paul', 32, 'California', 20000.00 );

--例子
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Paul', 32, 'California', 20000.00 ),
(2, 'Allen', 25, 'Texas', 15000.00 ),
(3, 'Teddy', 23, 'Norway', 20000.00 ),
(4, 'Mark', 25, 'Rich-Mond ', 65000.00 ),
(5, 'David', 27, 'Texas', 85000.00 ),
(6, 'Kim', 22, 'South-Hall', 45000.00 ),
(7, 'James', 24, 'Houston', 10000.00 );

查 SELECT

--查询表的所有数据
select * from company;
--调整格式（显示表头，按列排）
.header on   --列名（表头）显示
.mode column --左对齐列

--查询指定字段
select id,name,salary from company;

--查询工资 大于 50000的所有记录
select * from company where salary > 50000;

1.且 或 AND OR

--且 AND
--查询年龄大于等于 25 且 工资大于等于 65000.00 的所有记录
select * from compay where age > 25 AND salary >65000;
--或 OR
--查询年龄大于等于 25 或 工资大于等于 65000.00 的所有记录
select * from compay where age > 25 OR salary >65000;

2.模糊查询（文本字段） LIKE,GLOB

--like ,GLOB
--查询名字开头为 Ki 的所有记录，两种方式 like ， GLOB（只能大写）
select * from company where name LIKE 'ki%';
SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';

3.匹配数值 IN

--匹配 IN
--查询年龄的值为25，27或32 的所有记录
select * from company where age in (25,27,32);

4.范围 BETWEEN AND

--范围 BETWEEN AND
--查询年龄的值在25 到 27的所有记录
select * from company where age between 25 and 27;

5.限制 LIMIT

--限制 LIMIT
--查询前4个记录
select * from company limit 4;
--从第3个记录开始查询 4个记录 （偏移2个记录）
select * from company limit 4 offset 2;

6.排序 order by

--排序 order by
--升序 ASC
--按照工资升序排列查询所有记录
select * from company order by salary asc;
--降序 DESC
--按照工资降序排列查询所有记录
select * from company order by salary desc;

--查询 工资范围在20000到65000之间的升序排列的前3个记录
select * from (select * from company where salary between 20000 and 65000) order by salary asc limit 3;

7.分组 GROUP BY

--分组 GROUP BY
--查询 每个名字所拥有的工资总额
select name ,sum(salary) from company group by name;

8.过滤 分组结果 HAVING

--过滤 分组结果 HAVING
--查询姓名出现次数小于2的所有记录
select * from company group by name having count(name) <2;

9.去重 DISTINCT

--去重 DISTINCT
--查询姓名列（不能有重复记录）
select distinct name from company;

删 DELETE

--删除id为7的用户
delete from company where id = 7;
--删除表company中的所有记录
delete from company;

改 UPDATE

--将id为6的用户的地址修改为 Texas
update company set address = 'Texas' where id = 6;
--将所有用户的地址修改为 Texas
update company set address = 'Texas';

导出导入

--csv
--导出数据  sqlite>
.output company.csv    --设置导出文件
.header off            --去掉表头
.mode list             --列表化
select * from company; --要导出的表的信息
.output stdout         --执行导出

--导入数据
--前提：表company的表头结构符合要导入的数据结构
.import company.csv company   --将company.csv文件内的数据信息导入到表company中

--sql
--导出数据 
sqlite3 testDB.db .dump > testDB.sql
--导入数据
sqlite3 testDB.db < testDB.sql

事务

begin;  --开始一个事务，之后的所有操作都可以取消
commit; --使begin后的所有命令得到确认
rollback; --取消begin后的所有操作

--例子
begin;
delete form tbl;  --删除表
rollback;    --回滚

函数

文本处理函数

length()   --返回字符串的长度
lower()    --将字符串转换为小写
upper()    --将字符串转换为大写

--例子
select id,lower(name),length(address) from company1;

sqlite> select id,lower(name),length(address) from company1;
ID  lower(name)  length(address)
--  -----------  ---------------
1   paul         10
2   allen        5
3   teddy        6
4   mark         10
5   david        5
6   kim          10
7   james        7

聚集函数

avg()  --返回某列的 平均值
count() --行数
max()   --最大值
min()   --最小值
sum()   --之和

--例子
select avg(salary) as salary_avg from company;
select count(id) from company;
select count(*) from company;
select max(salary) from company;
select min(salary) from company;
select sum(salary) from company;

sqlite> select count(id) from company;
count(id)
---------
7
sqlite> select max(salary) from company;
max(salary)
-----------
85000.0
sqlite> select sum(salary) from company;
sum(salary)
-----------
260000.0