SQLite的安装和一些常用的基本操作

SQLite是一个开源的嵌入式关系数据库，实现自包容、零配置、支持事务的SQL数据库引擎。特点是高度便携，使用方便、结构紧凑、高效、可靠。与其他数据库管理系统不同，SQLite的安装和运行非常简单，如果对并发性要求不是很高，SQLite是一个不错的选择。

SQLite安装

鳄鱼君Ba使用的是Windows系统，所以主要介绍再Windows上的安装和使用。SQLite下载页面，根据Windows系统版本下载sqlite-dll-*.zip和sqlite-tools-win32*.zip两个压缩包，解压后，将两者放到合适的位置，并且将SQLite3路径添加到环境变量的PATH即可，这样只是为了方便我们打开CMD就可以启动sqlite3.

SQLite语法

SQL是一门ANSI的标准计算机语言，用来访问和操作数据库系统，用于取回和更新数据库中的数据，并与数据库程序协同工作。虽然存在很多不同版本的SQL语言，每个数据库都有一些它们独特的SQL语法，但是为了与ANSI标准相兼容，就必须以相似的方式共同地来支持一些主要的关键词，例如：SELECT、UPDATE、DELETE、INSERT、WHERE等，所以这里主要整理常用的操作。

SQL语言主要分为两部分：数据定义语言（DDL）和数据操作语言（DML）。根据名字就知道大致的意思了，数据定义语言主要就是创建或删除表格，定义索引，规定表之间的链接，施加表与表间的约束。数据操作语言，主要执行查询、更新、插入和删除等一些操作。SQL对大小写不敏感,接下来我会一直使用大写。

一、数据定义语言（DDL）

CREATE DATABASE用于创建数据库，语法格式：

CREATE DATABASE database_name;
# 创建名称为spider的数据库
CREATE DATABASE spider;

DROP DATABASE用于删除数据库，语法格式：

DROP DATABASE database_name;
# 删除名称为spider的数据库
DROP DATABASE spider;

CREATE TABLE语句用于创建数据库中的表，语法格式：

CREATE TABLE 表名称 (
列名称1数据类型,
列名称2数据类型,
列名称3数据类型,
...);

SQL支持的数据类型：

1. integer（size）、int（size）、smallint（size）、tinyint（size）：代表整数、size代表整数的最大位数
2. decimal（size，d)：代表小数，size为小鼠的最大位数，d为小数点右侧的最大位数
3. char（size）：代表固定长度的字符串，size为字符串的长度，输入的字符串长度小于size，依然会分配size的大小
4. varchar（size）：代表可变长度的字符串，size为字符串的最大长度，会根据字符串实际的大小分配长度
5. date（yyymmdd）：代表日期

# 创建一个名称为student的表，表里面包含5列，列名分别是："id"、"Name"、"Birth"、"Address"以及"City"
CREATE TABLE student(id integer, 
    Name varchar(255), 
    Birth date, 
    Address varchar(255), 
    City varchar(255));
# id列的数据类型是integer，包含整数，Birth为日期类型，其余的数据类型是varchar，最大长度为255个字符。

ALTER TABLE语句用于在已有的表中添加、修改或删除列：

1. 在表中添加列：ALTER TABLE table_name ADD column_name datatype
   
2. 修改表中某一列的数据类型：ALTER TABLE table_name ALTER COLUMN column_name datatype
   
3. 删除表中的某一列：ALTER TABLE table_name DROP COLUMN column_name
   

# 在之前创建的student表中添加名为class的一列
ALTER TABLE student ADD class varchar(255);
# 将class列的数据类型改为char(10)
ALTER TABLE student ALTER COLUMN class char(10);
# 将class列删除
ALTER TABLE student DROP COLUMN cdex_name;

DROP TABLE语句用于删除表（表的结构、属性以及索引也会被删除）

# 删除表名为student的表
DROP TABLE student;

CREATE INDEX index_name ON table_name;

CREATE INDEX index_name ON table_name(column_name); 

1. 唯一索引：不允许任何重复的值插入到表中，语法如下：

CREATE UINQUE INDEX index_name on table_name(column_name);

2. 组合索引：对一个表中的几列进行索引，语法如下：

CREATE INDEX index_name on table_name(column1,column2);

3. 隐式索引：在创建对象时，由数据库服务器自动创建的索引。在之前的student表中对Name添加名称为name_index的索引，语句为：

CREATE INDEX name_index ON student (Name);

DROP INDEX index_name;
# 将上面创建的name_index索引删除
DROP INDEX name_index;

二、数据操作语言（DML）

常用的操作语言：

1. SELECT:用于查询数据库表中数据。
2. UPDATE:用于更新数据库表中数据。
3. DELETE:用于从数据库表中删除数据。
4. INSERT INTO:用于向数据库表中插入数据。

SELECT用来从表中选取数据，结果存储在结果集中。语法格式为：

SELECT 列名称1， 列名称2... FROM 表名称;
SELECT * FROM 表名称;

以上面创建的student表为例，可以这样查询：

# 获取Name和Birth列
SELECT Name,Birth FROM student;
# 获取student表中所有列
SELECT * FROM student;

如果表中包含重复值，可以使用关键词DISTINCT，用于返回唯一不同的值：

# 获取Name和Birth列,并去重
SELECT DISTINCT Name,Birth FROM student;

条件查找，需要使用WHERE子句：

# 语法格式
SELECT 列名称 FROM 表名称 WHERE 列运算符;

可在WHERE子句中使用的运算符：

1. =：等于
2. <>：不等于，有的可以写为！=
3. >:大于
4. <：小于
5. >=：大于等于
6. <=：小于等于
7. BETWEEN:在这个范围内
8. LIKE:按某种模式搜索

列如像下面的使用方式：

#选取City为henan的记录
SELECT Name,City FROM student WHERE  City='henan';
# 选取id大于1的记录
SELECT Name,City FROM student WHERE id>1;
# 文本字符串需要使用单/双引号，数值不用

可以在WHERE子句中添加OR或者AND，跟英文含义一样。AND必须所有条件都成立；OR有一个条件成立即可。具体的使用方法：

# 选取id大于1 或者City等于henan
SELECT Name,City FROM student WHERE id>1 OR City='henan';

# 选取id大于1并且City等于henan
SELECT * FROM student WHERE id>1 AND City='henan';

# 选取id大于1 City等于henan 或者id大于3
SELECT * FROM student WHERE (id>1 AND City='henan') OR id>3;
# 使用多个条件筛选，可以使用圆括号组成复杂的表达式

# 按照id升序
SELECT * FROM student ORDER BY id;
# 按照id降序
SELECT * FROM student ORDER BY id DESC;

# Name字母升序，id降序
SELECT * FROM student ORDER BY Name ASC,id DESC;

UPDATE语句用于修改表中数据。使用方法如下：

UPDATE 表名称 SET 列名称=新值 WHERE 列名称=某值;

# 将id=1的Name修改为eyujun
UPDATE student SET Name=eyujun WHERE id=1;

DELETE语句用于删除表中的行，使用方法如下：

DELETE FROM student WHERE 列名称=值;

# 删除id=2的记录
DELETE FROM student WHERE id=2;

# 插入一行数据
INSERT INTO 表名称 VALUES(值1，值2...);
INSERT INTO student VALUES('zuan','1999-01-30','henan','henan');

# 指定插入一列数据
INSERT INTO table_name (列1，列2....) VALUES(值1，值2...);
INSERT INTO student (Name,City) VALUES('lexia','beijing');

介绍了基本语法，详细你已经可以熟练的使用SQLite了。打开下载的sqlite-tools文件夹，找到sqlite3程序文件，在当前文件夹打开CMD命令窗口，如果你配置环境变量的话，可以直接启动CMD，不用到sqlite3这个目录下。

SQLite操作

命令行输入sqlite3 D:/test.db，就会在D盘创建test.db数据库，接着创建数据库表student，包含id，name，birth，city

sqlite3 D:/test.db
create table student(id primary key,
 name varchar(255),
 birth date,
 city varchar(255));

插入一条数据：

insert into student (name,city) values('eyujun','henan');

将name修改为鳄鱼君

update student set name='鳄鱼君' where name='eyujun';

查询student表中的记录

select * from student;

删除name为鳄鱼君的记录

delete from student where name='鳄鱼君';

SQLite常用命令

 # 显示表结构
.schema

# 获取指定表的索引列表
.indices table

# 导处数据库到SQL文件
.output filename
.dump
.output stdout

# 从SQL文件导入数据库
.read filenam

# 格式化输出数据到CSV
.output filename.csv
.separator
select * from test;
.output stdout

# 从CSV文件导入到数据表中
create  table  newtable  (id  integer  primary  key, name  varchar(20), age integer ); # 先创建表
.import [filename.csv ] newtable
# 备份数据库
sqlite3 test.db .dump > backup.sql

# 恢复数据库：
sqlite3 test.db < backup.sql

SQLite事务

数据库事务指的是单个逻辑工作单元执行的一些列操作，要么完全执行，要么完全不执行。控制事务的命令基本都是下面的几种：

1. begin transaction:启动事务
2. commit：保存更改，或者使用begin transaction
3. rollback；事务回滚