0. 介绍

SQLite 是一个软件库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。

1. 安装

tar xvzf sqlite-autoconf-3071502.tar.gz 解压源码
cd sqlite-autoconf-3071502 进入目录
./configure --prefix=/usr/local
make && make install 编译安装

2. 配置说明

进入服务:sqlite3

3. 命令

1.数据库
sqlite3 DatabaseName.db          //创建数据库
sqlite>.databases                         //检查数据库
sqlite>.quit                             //退出
sqlite3 rule.db .dump > aa.sql     //导出数据库成sql语句
sqlite3 tools.db < aa.sql          //导入sql语句到库中
2.附加数据库

假设这样一种情况,当在同一时间有多个数据库可用,您想使用其中的任何一个。SQLite 的 ATTACH DATABASE 语句是用来选择一个特定的数据库,使用该命令后,所有的 SQLite 语句将在附加的数据库下执行。

sqlite>ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';

注:数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。

3.分离数据库

SQLite的 DETACH DTABASE 语句是用来把命名数据库从一个数据库连接分离和游离出来,连接是之前使用 ATTACH 语句附加的。如果同一个数据库文件已经被附加上多个别名,DETACH 命令将只断开给定名称的连接,而其余的仍然有效。您无法分离 main 或 temp 数据库。

sqlite>DETACH DATABASE 'Alias-Name';
sqlite>DETACH DATABASE 'TEST';
4.创建表/删除表
CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

create table yh_user( id INTEGER primary key autoincrement, username varchar(255) default '', password char(32) default '', real_name varchar(20) default '', sex tinyint default 0 );

DROP TABLE database_name.table_name;

查看表信息

sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
5.数据类型

SQLite 数据类型是一个用来指定任何对象的数据类型的属性。SQLite 中的每一列,每个变量和表达式都有相关的数据类型。
您可以在创建表的同时使用这些数据类型。SQLite 使用一个更普遍的动态类型系统。在 SQLite 中,值的数据类型与值本身是相关的,而不是与它的容器相关。

SQLite 存储类 每个存储在 SQLite 数据库中的值都具有以下存储类之一:

存储类 描述
NULL 值是一个 NULL 值。
INTEGER 值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL 值是一个浮点值,存储为 8 字节的 IEEE 浮点数字。
TEXT 值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储。
BLOB 值是一个 blob 数据,完全根据它的输入存储。

SQLite 亲和(Affinity)类型
SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据,当数据插入时,该字段的数据将会优先采用亲缘类型作为该值的存储方式。SQLite目前的版本支持以下五种亲缘类型:

亲和类型 描述
TEXT 数值型数据在被插入之前,需要先被转换为文本格式,之后再插入到目标字段中。
NUMERIC 当文本数据被插入到亲缘性为NUMERIC的字段中时,如果转换操作不会导致数据信息丢失以及完全可逆,那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据,如果转换失败,SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据,SQLite将不做任何转换,直接以NULL或BLOB的方式存储该数据。需要额外说明的是,对于浮点格式的常量文本,如"30000.0",如果该值可以转换为INTEGER同时又不会丢失数值信息,那么SQLite就会将其转换为INTEGER的存储方式。
INTEGER 对于亲缘类型为INTEGER的字段,其规则等同于NUMERIC,唯一差别是在执行CAST表达式时。
REAL 其规则基本等同于NUMERIC,唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE 不做任何的转换,直接以该数据所属的数据类型进行存储。  

SQLite 亲和类型(Affinity)及类型名称
下表列出了当创建 SQLite3 表时可使用的各种数据类型名称,同时也显示了相应的亲和类型:

数据类型 亲和类型
INT、INTEGER、TINYINT、SMALLINT、MEDIUMINT、BIGINT、UNSIGNED、BIG、INT、INT2、INT8 INTEGER
CHARACTER(20)、VARCHAR(255)、VARYING、CHARACTER(255)、NCHAR(55)、NATIVE、CHARACTER(70)、NVARCHAR(100)、TEXT、CLOB TEXT
BLOB、no datatype specified NONE
REAL、DOUBLE、DOUBLE、PRECISION、FLOAT REAL
NUMERIC、DECIMAL(10,5)、BOOLEAN、DATE、DATETIME NUMERIC 
6.命令

Insert、Select、Update、Delete、Like、Glob、Distinct、Limit、Having

交叉连接 : CROSS JOIN
交叉连接(CROSS JOIN)把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行,则结果表有 x*y 行。由于交叉连接(CROSS JOIN)有可能产生非常大的表,使用时必须谨慎,只在适当的时候使用它们。

select emp_id, name, dept from company cross join department;

内连接 : INNER JOIN 内连接(INNER JOIN)根据连接谓词结合两个表(table1 和 table2)的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较,找到所有满足连接谓词的行的匹配对。当满足连接谓词时,A 和 B 行的每个匹配对的列值会合并成一个结果行。

select emp_id, name, dept from company inner join department on company.id = department.emp_id;

外连接 :OUTER JOIN
外连接(OUTER JOIN)是内连接(INNER JOIN)的扩展。虽然 SQL 标准定义了三种类型的外连接:LEFT、RIGHT、FULL,但 SQLite 只支持 左外连接(LEFT OUTER JOIN)。

select emp_id, name, dept from company left outer join department on company.id = department.emp_id;

Unions 子句 SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果,不返回任何重复的行。

为了使用 UNION,每个 SELECT 被选择的列数必须是相同的,相同数目的列表达式,相同的数据类型,并确保它们有相同的顺序,但它们不必具有相同的长度。

UNION ALL 运算符用于结合两个 SELECT 语句的结果,包括重复行。

适用于 UNION 的规则同样适用于 UNION ALL 运算符。

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
     SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

TRUNCATE TABLE//等同于下面数据 DELETE FROM table_name; DELETE FROM sqlite_sequence WHERE name = 'table_name'; UPDATE sqlite_sequence SET seq = 0 WHERE name = 'table_name';

7.SQLite 约束
  • NOT NULL 约束:确保某列不能有 NULL 值。

  • DEFAULT 约束:当某列没有指定值时,为该列提供默认值。

  • UNIQUE 约束:确保某列中的所有值是不同的。

  • PRIMARY Key 约束:唯一标识数据库表中的各行/记录。

  • CHECK 约束:CHECK 约束确保某列中的所有值满足一定条件。

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL DEFAULT 0,
   NAME           TEXT    NOT NULL UNIQUE,
   AGE            INT     NOT NULL DEFAULT 0,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
); 

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);
8.索引(Index)

索引(Index)是一种特殊的查找表,数据库搜索引擎用来加快数据检索。简单地说,索引是一个指向表中数据的指针。一个数据库中的索引与一本书后边的索引是非常相似的。

CREATE INDEX index_name ON table_name;

//单列索引

CREATE INDEX index_name ON table_name (column_name);

//唯一索引

CREATE UNIQUE INDEX index_name on table_name (column_name);

//联合索引

CREATE INDEX index_name on table_name (column1, column2);

//查看表索引

.indices COMPANY

//删除索引

DROP INDEX index_name;

什么情况下要避免使用索引?

  • 虽然索引的目的在于提高数据库的性能,但这里有几个情况需要避免使用索引。使用索引时,应重新考虑下列准则:

  • 索引不应该使用在较小的表上。

  • 索引不应该使用在有频繁的大批量的更新或插入操作的表上。

  • 索引不应该使用在含有大量的 NULL 值的列上。

  • 索引不应该使用在频繁操作的列上。

results matching ""

    No results matching ""

    results matching ""

      No results matching ""