Sqlite

sqlite3 db

• sqlite3 命令

.help #获取可用命令的清单
.show #显示当前各种设置的值
.timeout MS #尝试打开锁定的表的时间

.timer on/off #开启或关闭cpu定时器
.width NUM NUM  #为column 模式设置列宽度
.mode MODE # csv,column,html,line,list,tabs,tcl 
.stats on|off  #开启或关闭统计
.header on/off #开启或关闭头部显示
.echo on/off #开启或关闭echo命令

.tables #列出表的名称
.databases #列出数据库的名称极其所依附的文件
.quit #退出sqlite提示符
.exit #退出sqlite提示符

.schema sqlite_master #主表中保存数据库的关键信息，并把它命名为sqlite_master

• SQLITE 数据类型
  • NULL //Null值
  • INTEGER //有符号整数
  • REAL //浮点数
  • TEXT //文本字符串
  • BLOB //blob数据，完全根据它的输入存储
• SQLITE 亲和类型
  • TEXT 数值型在被插入前，需要先被转换为文本格式，再被插入到目标字段中
  • NUMERIC 当文本数据被插入到亲缘行为NUMERIC的字段中，如果转换操作不会导致数据信息丢失以及完全可逆，将该文本数据转换为INTEGER或REAL类型的数据。 如果转换失败，仍会以TEXT的方式存储。NULL BLOB不做任何转换，直接以NULL或BLOB的方式存储。 浮点数可以转换为整数类型而不会丢失数据信息，那么将以INTEGER的类型存储。
  • INTEGER 基本等同于NUMERIC
  • REAL 数字以real存储，文本能转换则转换
  • NONE 不做任何转换
• 亲和类型及类型名称

• BOOLEAN
  • sqlite 没有单独的boolean存储类型，整数0和1表示false和true
• DATRE & TIME
  • sqlite 没有单独的用于存储日期和时间的存储类型，能够把日期表示为TEXT REAL INTEGER类型

sqlite3 DatabaseName.db
.open test.db
.quit

sqlite3 testDB.db .dump > testDB.sql
sqlite2 testDB.db < testDB.sql

ATTACH DATABASE 'TESTdb.DB' AS "TEST";
.database
DETACH DATABASE 'TEST';
.databases

#创建表
CREATE TABLE database_name.table_name (
	col1 datatype PRIMARY KEY(one or more columns),
    col2 datatype,
    ...
);
CREATE TABLE COMPANY(
	ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL,
);
.tables

#删除表
DROP TABLE database_name.table_name;
.tables
DROP TABLE COMPANY;

#在表中新增数据
INSERT INTO table_name [(col1,col2,col3,...)] VALUES (val1,val2,val3,...);
INSERT INTO table_name VALUES(val1,val2,...);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1,'PAUL',32,'California',20000.00);
INSERT INTO COMPANY VALUES (2,'Allen',23,"Texas",29998.9);
INSERT INTO COMPANY [(col1,col2,col3,...)] SELECT col1,col2,col3,... FROM COMPANY2 [WHERE condition];

#SELECT 查询语句
SELECT col1,col2,... FROM table_name;
SELECT * FROM COMPANY;
SELECT ID,NAME,SALARY FROM COMPANY;
SELECT tbl_name FROM  sqlite_master WHERE type = "table";
SELECT sql FROM sqlite_master WHERE type ="table" AND tbl_name = "COMPANY";

#sqlite 表达式
SELECT CURRENT_TIMESTAMP; //日期表达式
SELECT (15 + 16) AS ADDITION; //数值表达式
SELECT * FROM COMPANY WHERE SALARY = 10000; //布尔表达式

#SELECT WHERE 语句
SELECT col1,col2,... FROM table_name WHERE [condition];
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
SELECT * FROM COMPANY WHERE AGE >= 35 OR SALARY <= 22000;
SELECT * FROM COMPANY WHERE NAME LIKE "Ki%";
SELECT * FROM COMPANY WHERE GLOB "Ki*";
SELECT * FROM COMPANY WHERE AGE IN (25,35);
SELECT * FROM COMPANY WHERE AGE NOT IN (25,27);
SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
SELECT * FROM WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

#SELECT AND 运算符
SELECT col1,col2,... FROM table_name WHERE [condition1] AND [condition2] AND [conditon3];
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
#SELECT OR 运算符
SELECT col1,col2,... FROM table_name WHERE [condition1] OR [condition2] OR [condition3];
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

#更新标桩已有的数据
UPDATE table_name SET col1=val1, col2=val2, ... WHERE [condition];
UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
UPDATE COMPANY SET ADDRESS = 'Texas',SALARY = 2000.00;

#删除表中已有的记录
DELETE FROM tabel_name WHERE [condition];
DELETE FROM COMPANY WHERE ID = 1;
DELETE FROM COMPANY; #删除全部数据

#限制select语句返回的数据数量
SELECT * FROM COMPANY LIMIT 6;
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;  #从第3个开始返回3个数据

#基于一列或多列进行排序
SELECT * FROM COMPANY ORDER BY SALARY ASC;
SELECT * FROM COMPANY ORDER BY AGE DESC;
SELECT * FROM COMPANY ORDER BY NAME,SALARY ASC;

#对相同的数据进行分组
SELECT NAME,SUM(SALARY) FROM COMPANY GROUP BY NAME;
SELECT NAME,SUM(SALARY) FROM COMAPNY GROUP BY NAME ORDER BY NAME;

#指定条件过滤
SELECT * FROM COMPANY GROUP BY NAME HAVING COUNT(NAME) >2;
SELECT * FROM COMPANY GROUP BY NAME HAVING COUNT(NAME) <2 ORDER BY NAME;

#去重复
SELECT DISTINCT NAME FROM COMPANY;

• SQLITE PRAGMA

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;
#mode 0 / NONE  默认模式，禁用auto_vacuum，数据库尺寸不会缩小，除非手动使用VACUUM命令

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;
#pages 表示在缓存中的页面数

PRAGMA case_sensitive_like = [true|false];
#控制like表达式的大小写敏感度

PRAGMA count_changes;
PRAGMA count_changes = [true | false];
#pragma 获取或设置数据操作语句的返回值默认false不返回任何值

PRAGMA database_list;
#列出所有的数据库连接

PRAGMA encoding;
PRAGMA encoding = format;
#格式可以是utf-8 utf-16 utf-16e之一

PRAGMA [database.]freelist_count;
#当前被标记为免费和可用的数据库页面数

PRAGMA [database.]index_info(index_name);
#返回关于数据库索引的信息

PRAGMA [database.]index_list(index_name);
#列出所有与表相关的索引

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;
#获取和设置控制日志文件如何存储和处理的日志模式
DELETE 默认模式，事物结束后，日志文件将被删除
TRUNCATE 日志文件被截断为0字节长度
PERSIST 日志文件被留在原地，但头部被重写，表明日志不再有效
MEMORY 日志记录保留在内存上，而不是磁盘上
OFF		不保留任何日志记录

PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;
#获取和设置数据库允许的最大页树;

PRAGMA [database.]page_count;
#获取和设置当前数据库的网页数量

PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;
#数据库页面大小  512 1024 2048 4096 8192 32768

PRAGMA parser_trace = [true | false];
#随着解析SQL命令来控制打印的调试状态

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true| false];
#递归触发器功能

PRAGMA [database.]schema_version = number;
#获取或设置存储在数据库头中的架构版本信息

PRAGMA secure_delete;
PRAGMA secure_delete = [true | false];
PRAGMA [database.]secure_delete = [true | false];
#控制内容是如何从数据库中删除的 默认关闭

PRAGMA sql_trace;
PRAGMA sql_trace = [true | false];
#用于把sql 跟踪结果转储到屏幕上

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;
# 获取或设置当前磁盘的同步模式
0 OFF 不进行同步
1 NORMAL 在关键的磁盘操作的每个序列后同步
2 FULL 在每个关键的磁盘操作后同步

PRAGMA temp_store;
PRAGMA temp_store = mode;
#获取或设置临时数据库文件所使用的存储模式
0 DEFAULT 默认使用编译时的模式 FILE
1 FILE    使用基于文件的存储
2 MEMORY  使用基于内存的存储

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = "dir_path";
#获取或设置用于临时数据库文件的位置

PRAGMA [database.]user_version = number;
#获取或设置存储在数据库头的用户自定义的版本，可以由开发人员设置，用于版本跟踪的目的

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];
#获取或设置是否能够修改系统表

• SQLITE 约束

//NOT NULL
//默认情况下列是可以保存NULL，可以指定该列上不允许存在NULL值
CREATE TABLE company(
	ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL, 
    ADRRESS CHAR(50),
    SALARY REAL
);
//DEFAULT
//在没有提供一个特定的值的时候，提供一个默认值
CREATE TABLE company(
	ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL DEFAULT 50000.00
);
//UNIQUE
//防止在一个特定的列存在的两个记录具有相同的值
CREATE TABLE company(
	ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL UNIQUE,
    ADRRESS CHAR(50),
    SALARY REAL DEFAULT 50000.00
);
//PRIMARY KEY
//约束唯一标识数据库中的每个记录，一个只能有一个主键
CREATE TABLE company(
	ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL
);
//CHECK
//启用输入一条记录要检查值的条件，如果条件值为false,则记录违反了约束，且不能输入到表中
CREATE TABLE company (
	ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL CHECK(SALARY > 0)
);
//删除约束
ALTER TABLE table_name DROP CONSTRAINT primary_key_name;
ALTER TABLE table_name DROP CONSTRAINT unique_constraint_name;
ALTER TABLE table_name DROP  CONSTRAINT foreign_key_constraint_name;

• SQLITE JOIN

//CROSS JOIN 
//把第一个表的每一行与第二个表的每一行进行匹配
SELECT emp_id,name,dept FROM company CROSS JOIN department;

//INNER JOIN
//根据连接谓词结合两个表的列值来创建一个新的结果表,为默认的连接类型
SELECT emp_id,name,dept FROM company INNER JOIN deparment ON company.id = department.emp_id;

//OUTER JOIN
//INNER JOIN后使用NULL填充
SELECT emp_id,name,dept FROM  compamy OUTER JOIN department ON company.id = department.id;

• SQLITE TRIGGER

CREATE TRIGGER trigger_name [BEFORE|AFTER] [UPDATE|INSERT|DELETE]
ON table_name
BEGIN
	...trigger logic 
END;


CREATE TABLE company(
	ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL
);

CREATE TABLE audit(
	emp_id INT NOT NULL,
    entry_date TEXT NOT NULL
);

CREATE TRIGGER audit_log AFTER INSERT ON company
BEGIN
	INSERT INTO audit VALUES (new.ID,datatime('now'));
END;

//列出触发器
SELECT * FROM sqlite_master WHERE type='trigger' AND tbl_name='company';
DROP TRIGGER trigger_name;//删除触发器

• 索引
• 一种特殊的查找表，数据库引擎用来加快数据检索，索引是指向表中数据的指针。
• 能够加快select where语句的速度，但会减慢update insert语句的数据输入

#单列索引
CREATE INDEX index_name ON table_name (column_name);
#唯一索引
CREATE UNIQUE index_name ON table_name (column_name);
#组合索引
CREATE INDEX index_name ON table_name (col1,col2,...);

//隐式索引，创建对象时数据库自动创建的索引

//删除索引
DROP INDEX index_name;


CREATE INDEX salary_index ON company (salary);
.indices company
SELECT * FROM slqite_master WHERE type = 'index';
DROP INDEX salary_index


//使用索引查找
SELECT |DELETE | UPDATE col1,col2,...
INDEXED BY (index_name)
table_name
WHERE (condition);

SELECT * FROM company INDEXED BY salary_index WHERE salary > 5000;

• SQLITE ALTER
• 用于修改表名字和添加列

ALTER TABLE database_name.table_name RENAME TO new_table_name;
ALTER TABLE database_name.table_name ADD COLUMN column_def ...;

• SQLITE INCREMENT

CREATE TABLE company(
	id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INT NOT NULL,
    address CHAR(50),
    salary REAL
);//AUTOINCREMENT 用于表中的字段值自动递增

• SLQITE TRANSACTION

BEGIN;COMMIT;ROLLBACK;

//一个例子
BEGIN;
DELETE FROM company WHERE age = 35;
ROLLBACK; //取消删除

//一个例子
BEGIN;
DELETE FROM company WHERE age = 34;
COMMIT;//成功删除