关于SQLite3笔记

.help

.quit

.exit

创建和连接数据库:在linux中 sqlite3 数据库名没有就创建有就连接

.show 显示各种设置的当前值.

.echo ON|OFF echo命令

.headers ON|OFF 头部显示

.mode column 见 .mode MODE

.timer ON|OFF CPU 定时器测量

.stats ON|OFF 统计

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

.backup ?DB? FILE 备份DB数据库(默认是”main”)到FILE文件

.bail ON|OFF 发生错误后停止.默认为OFF.

.databases 列出附加数据库的名称和文件.

.dump ?TABLE? 以SQL文本格式转储数据库.如果指定了TABLE表,则只转储匹配LIKE模式的TABLE表.

.read FILENAME 从txt文档中执行sql语句

.explain ON|OFF 适合于EXPLAIN的输出模式开关,如没参数,则为EXPLAIN ON 开启

.import FILE TABLE 导入来自file文件的数据到table表中

.indices ?TABLE? 显示所有索引的名称,如果指定的表,就只显示匹配like模式的table表的索引.

.load FILE ?ENTRY? 加载一个扩展库.

.log FILE|off 开启或关闭日志,FILE文件可能是sderr(标准错误)/stdout(标准输出)

.mode MODE 设置输出模式，MODE 可以是下列之一：

csv 逗号分隔的值
column 左对齐的列
html HTML 的代码
insert TABLE 表的 SQL 插入（insert）语句
line 每行一个值
list 由 .separator 字符串分隔的值
tabs 由 Tab 分隔的值
tcl TCL 列表元素

.nullvalue STRING 在NULL值的地方输出STRING字符串

.output FILENAME 发送输出到FILENAME文件.

.output stdout 发送输出到屏幕

.print STRING… 逐字地输出STRING字符串

.prompt MAIN CONTINUE 替换标准提示符

.read FILENAME 执行FILENAME文件中的SQL.

.schema ?TABLE? 显示CREATE语句.如果指定了表,则只显示匹配LIKE模式的表.显示表的个体信息.

.separator STRING 改变输出模式和.import所使用的分隔符

.tables ?PATTERN? 列出匹配like模式的表和名称.

.timeout MS 尝试打开锁定的表 MS微秒

.width NUM NUM 为”column”模式设置列宽度.

— 一般不区分大小写但是也有个别命令是区分的 GLOB和glob 就不一样

— 注释用两个连续的 –(一行) 也可以用 /* 开始 */结束 (多行)

所有的语句以关键字开始以 ; 分号结束
数据类型是一个用来指定任何对象数据类型的属性.(每一列,每个变量和表达式都有相关的数据类型)

SQLite存储类如下 :

NULL 是一个NULL值

INTEGER 是一个带符号的整数根据值的大小存储在1/2/3/4/6/8/字节中.

REAL 是一个浮点值,存储为8字节的IEEE浮点数字.

TEXT 是一个文本字符串,使用数据库编码(UTF-8 UTF-16BE或UTF-16LE)存储

BLOB 是一个blog数据,完全根据它的输入存储,二进制数据.

char(n) n 长度的字串，n不能超过 254。

varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。

UNIQUE – 唯一

DEFAULT – 默认例: DEFAULT ‘中国’

PRIMARY KEY – 主键

FOREIGN KEY – 外键

CHECK –必须符合一定的条件才允许存入.

SQLite 亲和(Affinity)类型略

SQLite 亲和类型(Affinity)及类型名称略

Boolean数据类型: 0 (false) 和 1(true).

Date 与Time数据类型:

SQLite没有一个单独用于存储日期/时间的下存储类,但sqlit能够把日期和时间存储为TEXT,REAL或INTEGER值:

TEXT 格式为 “YYYY-MM-DD HH:MM:SS.SSS” 的日期。

REAL 从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。

INTEGER 从 1970-01-01 00:00:00 UTC 算起的秒数。

创建数据库: sqlite3 test.db

会在当前目录下创建一个文件test.db,这个文件将被SQLite用作数据库.

然后就可以用 .databases 查看数据库信息用 .quit 退出

.dump命令:在一个文本文件中导出完整的数据库.

test.db .dump > test.sql :会转换test.db数据库的内容到SQLite语句中,并将其转储到ASCLL文本文件test.sql中.

test.db < test.sql: 会把test.sql中的数据恢复到test.db中.

SQLlite附加数据库 : 如果同一时间有多个数据库可用,要使用其中任何一个.

ATTACH DATABASE ‘DatabaseName’ As ‘Alias-Name’;:如果数据库没有,将创建一个数据库,如果有则把数据库文件名称与逻辑数据库’Alias-Name’绑定在一起.

例子:ATTACH DATABASE ‘test.db’ as ‘test’;

SQLite 分离数据库:把命名数据库从一个数据库连接分离出来.用: DETACH DATABASE ‘test’;

SQLite 创建表: CREATE TABLE关键字后面跟表名.

CREATE TABLE COMPANY(

ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL,

AGE INT NOT NULL ,

ADDRESS CHAR(50),

SALARY REAL

);

也可以创建多个表.

用.tables 查看有多少个表.

用.schema 得到表的完整信息.

修改表 ALTER TABLE:仅仅支持一部分功能,可以更改一个表名字,也可向表中增加一个列,但是不能删除一个已经存在的字段,或者更改一个已经存在的字段的名称,数据类型,限定符等等.

ALTER TABLE 旧表名 RENAME TO 新表名 –改变表名

ALTER TABLE 表名 ADD COLUMN 列名数据类型限定符 –增加一列

SQLite 删除表:删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。

语句: DROP TABLE COMPANY;

SQLite Insert 语句:用于向数据库的某个表中添加新的数据行。

INSERT INTO TABLE_NAME (列名1, 列名2, 列名3,…列名N)]

VALUES (value1, value2, value3,…valueN);

如果要为表中的所有列添加值,也可以不指定列名,要确保值的顺序与列在表中的顺序一致.

INSERT INTO 表名 VALUES(value1, value2, value3,…valueN);

用一个表来填充另一个表:可以通过在一个有一组字段的表上使用select语句直译数据到另一个表中.语法为:

INSERT INTO first_table_name [(column1, column2, … columnN)]

SELECT column1, column2, …columnN

FROM second_table_name

[WHERE condition];

SQLite Select 语句:用于从数据库中获取数据,以结果表的形式返回数据.这些结果表也称为结果集.

语句: SELECT column1, column2, columnN FROM table_name;

column是要获取的值如果想获取全部,就用 *

如果只想获取表中指定的字段,用: SELECT ID, NAME, SALARY FROM COMPANY;

设置输出列的宽度:

.width 10 , 20 10 就可以设置第一列的宽度10,第二列宽度20,第三列宽度10.

Schema信息:所有的点命令只在提示符中可用,所以在编程时,要使用带有sqlite_master表的SELECT语句来列出所有在数据库中创建有表:

SELECT tbl_name FROM sqlite_master WHERE type = ‘table’;

也可以列同关于COMPANY表的完整信息:

SELECT sql FROM sqlite_master WHERE type = ‘table’ AND tbl_name = ‘COMPANY’;

SQLite 运算符:是一个保留字或字符,用于WHERE子名中执行操作,如比较和算术运算.用于指定sqlite中的条件,在语句中连接多个条件.

–把运算符两边的值相加

– –左操作数减去右操作数

–把运算符两边的值相乘

/ — 左操作数除以右操作数

% –左操作数除以右操作数后得到的余数

例子: select 10 + 20 ; –> 10+20=30

SQLite 比较运算符

== –检查两个扣件数的值是否相等,如果相等则条件为真

= –检查两个操作数的值是否相等,如果相等则条件为真

!= –不等于

<> –检查两个操作数的值是否相等,如果不相等则条件为真

–大于

< –小于

= –大于等于

<= –小于等于

!< –不小于

!> –不大于

例子: SELECT * FROM COMPANY WHERE SALARY > 5000; –找出COMPANY中SALARY大于5000的信息.

SQLite 逻辑运算符

AND –允许在一个SQL语句的WHERE子句中的多个条件的存在. 例:SELECT * FORM COMPANY WHERE AGE >= 25 AND SALARY >=6500;

BETWEEN –用于在给定最小值和最大值范围内的一系列值中搜索值.例:SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

EXISTS –符用于在满足一定条件的指定表中搜索行的存在.SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

IN –用于把某个值与一系列指定列表的值进行比较.例:SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

NOT IN –和IN相反. 例:SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

LIKE –把某个值与使用通配符运算符的相似值进行比较.例:SELECT * FROM COMPANY WHERE NAME LIKE ‘Ki%’;

GLOB –把某个值与使用通配符去处符的相似值进行比较.GLOB是区分大小写的.

NOT –所用逻辑运算符的对立面.

OR –结合一个WHERE子句中的多个条件. 例:SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

IS NULL –运算符用于把某个值与NULL值进行比较.

IS –与 = 相似

IS NOT — 与 != 相似. 例:SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

|| — 连接两个不同的字符串,得到一个新的字符串

UNIQUE — 搜索指定表中的每一行,确保不重复.

SQLite 位运算符

不会,略

SQLite 表达式:一个或多个值,运算符和计算值的SQL函数的组合.

SELECT column1, column2, columnN FROM table_name WHERE [CONTION | EXPRESSION];

SQLlite 布尔表达示

SELECT * FROM COMPANY WHERE SALARY = 10000;

SQLite – 数值表达式:用来执行查询中的任何数学运算.

SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION];

这里的numerical_expression可以是任何数学公式.

例:SELECT (15+6) AS ADDITION; –> ADDITION = 21

有几个内置的函数,比如 avg() sum() count() 等等执行被称为对一个表或一个特定的表列的汇总数据计算

例: SELECT COUNT(*) AS ‘RECORDS’ FROM COMPANY ;

SQLite – 日期表达式:日期表达式返回当前系统日期和时间值，这些表达式将被用于各种数据操作。

例:SELECT CURRENT_TIMESTAMP;

SQLite Where 子句:用于指定从一个表或多个表中获取数据的条件.如果满足给定的条件,(为真时),则人表中返回特定的值.可以用WHERE子名来过滤记录,只获取需要的记录.也可有用在UPDATE DELETE 语句中.

基本语句: SELECT column1,column2,columnN FROM table_name WHERE [condition]

也可以使用比较或逻辑运算符指定条件,比如: < > = LIKE NOT 等等.

例:SELECT * FROM COMPANY WHERE AGE >= AND SALARY >= 65000;

例:SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

例:SELECT * FROM COMPANY WHERE NAME GLOB ‘ki*’;

例:SELECT * FROM COMPANY WHERE AGE IN (25,27);

例:SELECT * FROM COMPANY WHERE AGE NOT IN (25,27);

例:SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

例:SELECT * FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);找 SALARY > 65000 的带有 AGE 字段的所有记录，后边的 WHERE 子句与 EXISTS 运算符一起使用，列出了外查询中的 AGE 存在于子查询返回的结果中的所有记录：

例:SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);查找 SALARY > 65000 的带有 AGE 字段的所有记录，后边的 WHERE 子句与 > 运算符一起使用，列出了外查询中的 AGE 大于子查询返回的结果中的年龄的所有记录：

SQLite AND/OR 运算符:用于编译多个条件来缩小在 SQLite 语句中所选的数据

AND 运算符:只有当所有条件都为真（true）时，整个条件为真（true）。

SELECT column1, column2, columnN FROM table_nameWHERE [condition1] AND [condition2]…AND [conditionN];

例:SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

OR 运算符: 只要当条件中任何一个为真（true）时，整个条件为真（true）。

SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]…OR [conditionN]

例:SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

SQlite Update语句:用于修改表中已有的记录.可以使用带有WHERE 子句的IPDATE 查询来更新选定行.否则所有行都会被更新.

基本用法: UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition];

例:UPDATE COMPANY SET ADDRESS = ‘TEXAS’ WHERE ID = 6; –修改ID为6的 ADDRESS = ‘TEXAS’

例:UPDATE COMPANY SET ADDRESS = ‘TEXAS’,SALARY = 20000; — 修改表中所有的ADDRESS和SALARY的值.

SQLite Delete 语句:用于删除表中已有的记录.可以使用带有WHERE子句的DELETE 查询来删除选定行,否则所有记录都会被删除.

DELETE FROM table_name WHERE [codition];

例: DELETE FROM COMPANY WHERE ID = 7 ; –删除ID 为7的客户.

例: DELETE FROM COMPANY ; –删除COMPANY表中的所有记录.

SQLite Like 子句:用来匹配通配符指定模式的文本值.如果搜索表达式与模式表达式匹配,LIKE运算符将返回真(true),也就是1.有两个通配符与LIKE去处符一起使用

% –代表0个,一个或多个数字或字符.

_ –代表一个单一的数字或字符. 可以组合使用.

例: SELECT FROM TALBE_NAME WHERE COLUMN LIKE ‘XXX%’

例: SELECT FROM TALBE_NAME WHERE COLUMN LIKE ‘%XXX%’

例: SELECT FROM TALBE_NAME WHERE COLUMN LIKE ‘XXX_’

例: SELECT FROM TALBE_NAME WHERE COLUMN LIKE ‘_XXX’

例: SELECT FROM TALBE_NAME WHERE COLUMN LIKE ‘XXX’

也可以AND 或 OR 来结合N个数量的条件.

WHERE SALARY LIKE ‘200%’ 查找以 200 开头的任意值

WHERE SALARY LIKE ‘%200%’ 查找任意位置包含 200 的任意值

WHERE SALARY LIKE ‘_00%’ 查找第二位和第三位为 00 的任意值

WHERE SALARY LIKE ‘2_%_%’ 查找以 2 开头，且长度至少为 3 个字符的任意值

WHERE SALARY LIKE ‘%2’ 查找以 2 结尾的任意值

WHERE SALARY LIKE ‘_2%3’ 查找第二位为 2，且以 3 结尾的任意值

WHERE SALARY LIKE ‘2___3’ 查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

SELECT * FROM COMPANY WHERE AGE LIKE ‘2%’; 显示 COMPANY 表中 AGE 以 2 开头的所有记录

SELECT * FROM COMPANY WHERE ADDRESS LIKE ‘%-%’; 它显示 COMPANY 表中 ADDRESS 文本里包含一个连字符（-）的所有记录

SQLite Glob 子句:用来匹配通配符指定模式的文本值,与LIKE运算符不同的是GLOB大小写敏感.

–代表0个,一个或多个数字或,字符.

?　–代表一个单一的数字或字符.

ps:和LIKE基本一样只不过把关键字换为GLOB,并且大小写敏感.

SQLite Limit 子句: 用于限制由SELECT 语句返回的数据数量.

SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]

SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num] –将返回从下一行开始直到给定的 OFFSET 为止的所有行.

例:SELECT * FROM COMPANY LIMIT 6; –提取表的前六行.

例:SELECT * FROM COMPANY LIMIT 3 OFFSET 2; –LIMIT提取几行,OFFSET 从第几行后面开始.

SQLite Order By:基于一个或多个列按升序或降序排列数据.

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];

例:SELECT * FROM COMPANY DRDER BY SALARY ASC; –按SALARY 升序排序.

例:SELECT * FROM COMPANY DRDEP BY NAME AND SALARY ASC; –按NAME和SALARY 升序排序.

例:SELECT * FROM COMPANY DRDEP BY NAME DESC; –按NAME降序排序;

SQLite Group By : 与SELECT一起使用,来对相同的数据进行分组.GROOUP BY子句在放在WHERE 子句之后,放在ORDER BY 子句之前.

SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2….columnN ORDER BY column1, column2….columnN

例:SELECT NAME ,SUM(SALARY) FROM COMPANY GROUP BY NAME; –每个人的工资总额,则可使用GROUP BY 查询

SQLite Having 子句 :允许指定条件来过滤将出现在最终结果中的分组结果.WHERE在所先列上设置条件,而HAVING则在由GROUP BY子句创建的分组上设置条件.

SELECT FROM WHERE GROUP BY HAVING ORDER BY

在一个查询中HAVING子句必须放在GROUP BY 子句之后,必须放在ORDER BY 子句之前:

SELECT column1, column2 FROM table1, table2 WHERE [conditions] GROP BY column1, column2 HAVING [conditons] ORDER BY column1, column2

例:SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2; –显示名称计数小于2的所有记录;

例:SELECT * FROM COMPANY GROUP BY name HAVING conun(name) > 2; –显示名称计数大于2的所有记录.

SQLite Distinct 关键字:与SELECT语句一直使用,来消除所有重复的记录,并只获取唯一一次记录.

SELECT DISTINCT column1, column2,….columnN FROM table_name WHERE [DONDITION]

例:SELECT name FROM COMPANY;这样重复的也会显示出来.用:SELECT DISTINCT name FROM COMPANY; 这样输出的结果就没有重复的了.

SQLite PRAGMA:是一个特殊命令,可以用在SQLite环境内控制各种环境变量和状态标志.一个PRAGMA值可以被读取,也可以根据需求进行设置.

要查询当前的PRAGMA值,只需要提供该pragma的名字: PRAGMA pragma_name;

要设置一个新的值: PRAGMA pragma_name = value;

设置模式,可以是名称或等值的整数,但返回的值将始终是一个整数..

后面的不懂回头再看略.

在Python3中的接口要注意,conn.execute()的括号内的各种方法字符串是要加引号的!不是你给的值是字符串就行了!因为是要用这个语句,这在格式化字符串再交给execute处理时很常见

不要忘记conn.commit()和conn.close()提交事务和关闭!