python3 Sqlite3

    最近出于某种需要将数据库换到sqlite3,python是自带sqlite的，不需要另外安装，但如果不是在python中使用，也可以去sqlite官网下载最新版，下载后解压，将你存放该文件的目录加入系统环境变量，即可运行。

    先介绍python3中使用sqlite3的主要接口（API，不知道叫接口对不对）

因为python 自带sqlite3 ,直接 import

import sqlite3

1.sqlite3.connect(database [,timeout ,other optional arguments])

第一个参数database是文件路径名，如果只有文件名，默认是当前目录下，且如果文件不存在会自动创建一个数据库文件。

2.connection.cursor([cursorClass])

创建一个游标，与mysql数据库一样（事实上大部分语法与mysql一样）。

3.cursor.execute(sql [, optional parameters])

前面是sql语句，后面可以是参数，sqlite3支持两种类型占位符：问号、命名占位符。

例如：

cursor.execute("insert into people values (?, ?)", (who, age))

4.connection.execute(sql [, optional parameters])

它实际是调用光标（cursor） 对象执行

5.cursor.executemany(sql, seq_of_parameters)

这里是对sql_of_parameters，所有参数执行前面的sql语句，

6.connection.executemany(sql[, parameters])

参见前面的第4条

7.cursor.executescript(sql_script)

执行sql脚本

8.connection.executescript(sql_script)

参见第4条

9.connection.total_changes()

返回自数据库连接打开以来被修改、 插入或删除的数据库总行数

10.connection.commit()

提交当前的事务,如果你不提交（commit）,在这之前你所做的所有修改，包括插入，删除，修改等对其它数据库来说是不可见的。

11.connection.rollback()

回滚自上一次调用 commit() 以来对数据库所做的更改

12.connection.close()

该方法关闭数据库连接。 请注意， 这不会自动调用 commit(),相当于你之前所做的更改没保存。

13.cursor.fetchone()

该方法获取查询结果集中的下一行， 返回一个单一的列表， 当没有可用的数据时， 则返回 None,实际使用时可能会返回[(0,)].

14.cursor.fetchmany([size=cursor.arraysize])

该方法获取查询结果集中的下一行组， 返回一个列表。 当没有更多的可用的行时， 则返回一个空的列表。 该方
法尝试获取由 size 参数指定的尽可能多的行.

15.cursor.fetchall()

该例程获取查询结果集中所有（剩余） 的行， 返回一个列表。 当没有可用的行时， 则返回一个空的列表。

下面介绍下sqlite3 比较常见的一些"点命令"，点命令与sql语句不同之处在于它不以";"结束，且是以”.“开头。

.help 这条命令会显示比较常用的”点命令“，并有基本解释。

.show 显示当前数据显示的设置,例如是否显示表头，输出到哪，行分隔符等。

通过查看.help命令后可以对自己的数据库显示做以下设置：

.header on    设置显示表头（这里我理解的是字段名）

.mode column     设置以列的形式展示结果

.timer on     设置显示查询所用的时间

另外要查看表的信息，命令是： .schema sqlite_master 它是主表，显示的是数据库中的表的信息。

所以，如果要查询某数据库中是否有某个特定名字的表，只需要从sqlite_master这张表查询即可。例如：我想查询数据库是否有"books"这张表：

SELECT
	count(*)
FROM
	sqlite_master
WHERE
	type = 'table'
AND name = 'books'

 如果有这张表，返回的是1，在python中用fetchone()返回的则是：一个元组，（1，），不存在是返回的并不是None,而是（0，）

 创建数据库：

在命令行下：sqlite3 database_name (这里的database_name,可以是文件名，可以是完整路径)

而在python3中，则是用：

sqlite3.connect(database [,timeout ,other optional arguments])

，参见上面第一条。

导出数据库：

.dump命令，例如：

sqlite3 test.db .dump > test.bak

 (注意是在命令行模式下，也就是并没有进入sqlite3 ,而且这语句不是以“;”结束，点命令不是么)那么，导出了想恢复怎么办呢？

恢复数据库：现在我们有test.db 和备份数据库test.bak,恢复的命令为：

sqlite3 test2.db < test.bak

 (注意这里是将备份恢复到test2.db,注意两边的文件名，以即这里是小于号)

选择数据库：

在mysql中是用use 命令，sqlite3 中则是 Attach(附加；附属；伴随),记忆中mysql的use只是使用，好像没有取别名的作用，待考证。个人认为这个attach相当于建立一个连接

sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';     （看到前面的sqlite>你就知道这是数据库，而不是cmd的命令行，并且如果testDB.db数据库不存在，那么会创建）

这时使用sqlite> .databases 点命令你就知道，当前有哪些数据库在使用中，有两个数据名不能执行此操作：main, temp，比如你偿试：

sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP';

会得到这样的错误信息：Error: database temp is already in use
同样，有选择就有放弃

分离数据库：

现在我再附加一个数据库名：ATTACH DATABASE 'testDB.db' as 'test2';

执行点命令sqlite> ：.database

main:
test: D:PythontestDB.db
test2: D:PythontestDB.db
此时我想放弃test这个数据库名，只用test2就行了，命令为：

sqlite> DETACH DATABASE 'test';

创建表：

基本语法为：

CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);

 如果已经选择数据库，这里的database_name.table_name可以直接写table_name(后面同理),执行该命令后可以用点命令查看表是否创建成功：

sqlite>.tables  （显示当前数据库有哪些表）

如果要看表的详细信息则可以用点命令sqlite>.schema table_name    (会显示表名，字段名，数据类型等)

删除表：
DROP TABLE database_name.table_name;

插入数据INSERT语句：

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);

 前面是列，后面是对应的值。

但如果是对所有列插入数据：则可以简写：

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

 但一定要注意，数值与对应的列的顺序要一致，即使如此这种方法还是不要用的好。

用一个表的数据填充另一个表：

INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];

 事实上插入语句还是没变，只不对后面的values是通过一个子句来筛选出来的。

选择Select 语句 ：

SELECT column1, column2, columnN FROM table_name;

 如果要显示所有数据用通匹符*

select * From table_name;

有时因为设置了.mode column,会导致数据被截断的情况，那么可以通过.width num,这里的num是一个整数，如10.

Schema

由于所有的点命令只能在sqlite 提示符下使用，即我所说的进入sqlite后，有sqlite>提示。这导致一个问题，在sqlite编程时，要想查看数据库有哪些表，或者是否有某个特定的表，要怎么办呢？这时就需要 sqlite_master表（当然sqlite_master表在进入sqlite后仍然可用），

例如要查看有哪些表：

SELECT tbl_name FROM sqlite_master WHERE type = 'table';

 查询一特定名称的表：

SELECT count(*) FROM sqlite_master WHERE type = 'table' AND tbl_name = 'table_name';

 sqlite_master：

sqlite_master是只读的，字段名及类型如下，可以通过.schema sqlite_master  查看：

type TEXT, 
name TEXT, 
tbl_name TEXT, 
rootpage INTEGER, 
sql TEXT ，

type  是 table ,而name 则可以是表的名字， table_name是表名，但与name是有一点区别的，rootpage不知道表示什么（吐槽下，在网上找了半天，发现都是抄来抄去，有些完全一样，目前为止没发现谁对这个rootpage作了解释，但是你可以通过：Select * 查出来，第4个参数即是rootpage），sql则是创建这个表或索引时的sql语句。那么，回头说name, 与tbl_name的区别，当你要查找的是表名，也就是where type='table'时，两者都是表名，但如果type='index'时，则，tbl_name返回表名，而name返回的则是创建索引的名字，像这样：sqlite_autoindex_COMPANY_1,这里的COMPANY 是一个表。

SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'table_name';

 这里查找的是创建table_name时的sql语句。

运算符：

算术运算符：+ - * / %

如果想在提示符下进行测试，建议开启行显示模式 ： . mode line 这样进行测试就类似Python的shell,很直观

比较运算符： ==、 =、 ！=、 <> 、>=、 <= 、！> 、!< 等。

操作符，这个没什么好的说的，就字面意思。

逻辑运算符：

And:可以理解成并列吧，让多个条件并列存在，像上面讲的的sqlite_master中查询时用到的：type='table' AND name='table_name'

BETWEEN: 给定一个范围，如1~100之间，那么查询就限定在这个范围内进行。

Exists:用于在满足一定条件的  特定的表中   搜索行  的存在。

IN:把某个值与某系列进行比对，感觉与python的in类似

NOT IN:参见in

LIKE： 用来与通配符指定模式的文本进行匹配，说到通配符sqlite3有两个可以与like一起用的通配符：%， _百分号代表任意个数字或字符，下划线则代表一个单一的字符或数字。

GLOB:与LIKE类似，但大小写敏感，如果搜索表达式与通配符组成的模式匹配则GLOB返回TRUE,同样有两个通配符*，？星号代表任意个数字或字符，问号代表一个单一的数字或字符。

NOT:否定运算符

OR: 理解成或应该是可以的，主要用于结合多个条件

IS NULL ：把某个值与Null进行比较

IS:类似于=

IS NOT ：类似于！=

||:连接两个不同的字符串，得到一个新字符串

UNIQUE:确保唯一性，即不重复

位运算符：

&  |  ~ << >>

位运算符暂时用不到，以后用到再写

sqlite表达式：

表达式就是一些查询语句或者它们的组合，这个没什么好说的。

SELECT CURRENT_TIMESTAMP;

 current_timestamp是当前时间，这条语句是获取当前时间

WHERE 子句：

where子句主要用来对所选列进行数据过滤，当where后的条件为真时（可以理解为当条件成立），则返回指定的值。

基本语法：

SELECT column1, column2, columnN
FROM table_name
WHERE [condition]

 这里的condition就是所谓的条件。可以用在其他查询中如：select , update, delete等，而且事实尽量将where子句用在里面，否则update,delete等可能将整个数据都更改了。

UPDATE 子句：

update子句主要是用来修改已经存在的数据

基本语法：

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

 DELETE 子句：

delete子句用来删除已经有的数据

基本语法：

DELETE FROM table_name
WHERE [condition];

 LIMIT子句：

用于限制由 SELECT语句返回的数据数量
基本语法为：

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

 如果与offset一起用，则是：

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

 ORDER BY:

用来对数据升序或降序进行设定

基本语法为：

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

 GROUP BY：

用来对相同的数据进行分组，在 SELECT语句中， GROUP BY 子句放在 WHERE子句之后， 放在 ORDER BY 子句之前

基本语法：

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

 为什么说是对相同的数据进行分组呢，BY后面这个字段是分组的字段，如果这个字段有一个或多个元素，那么就会被分到一组，因为共同的特性。

Having：

Having子句与where类似，但它主要是用来过滤Group by分组的结果

基本方法：

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

 它在Select语句中的位置为：

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Distinct 关键字：

Distinct主要用来消除重复记录，并且只获取一次记录。

基本语法：

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

 UNION子句：

Union子句用于合并，但不返回重复的行。为了使用 UNION， 每个 SELECT被选择的列数必须是相同的， 相同数目的列表达式， 相同的数据类型， 并确保它们有
相同的顺序， 但它们不必具有相同的长度

基本语法：

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition] 

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

基本语法：

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

 

Joins:

用于结合两个或者多个表中的记录，是通过共同的值来结合两个表的字段的手段

有三种结合（或者称之为连接）

交叉连接 - CROSS JOIN ：

又叫笛卡尔积，匹配前一个表与后一个表的每一行和每一列，这样得到的结果集为n*m行（n, m分别为每张表的行数），x+y列（x, y分别为每张表的列数）。

Select的字段可能在两个表中，那么连接的共同的值就可能是ID。

内连接 - INNER JOIN ：

内连接（INNER JOIN） 根据连接谓词结合两个表（table1 和 table2） 的列值来创建一个新的结果表。 查询会把 table1 中的每一行与 table2 中的每一行进行比较，

找到所有满足连接谓词的行的匹配对。当满足连接谓词时， A 和 B 行的每个匹配对的列值会合并成一个结果行。

基本语法：

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

 所谓的连接主谓词就是这里的On后面的条件语句

为了避免冗余， 并保持较短的措辞， 可以使用 USING 表达式声明内连接（INNER JOIN） 条件。 这个表达式指定一个
或多个列的列表：

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ... 

外连接 - OUTER JOIN :只支持left outer join

　外连接是对内连接的补充，至少包含左侧表的每一行,对于连接后不存在的字段值，则赋NULL。这里没怎么搞懂，留待以后补充

约束：

约束主要用来插入数据的类型

NOT NULL约束： 确保某列不能有 NULL值。注意，NULL与没有数据（mysql中的blank）是不一样的，它代表未知的数据
DEFAULT约束： 当某列没有指定值时， 为该列提供默认值。
UNIQUE约束： 确保某列中的所有值是不同的。
PRIMARYKey 约束： 唯一标识数据库表中的各行/记录。
CHECK约束： CHECK 约束确保某列中的所有值满足一定条件，例如字段名是age ,check(age>10)，那么当age<=10就不能插入到数据库中。

别名：

别名就是暂时把表或者列重命名成另一名字，但事实是只是临时的，表的名字事实上并没有改变。

基本语法：

表：

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

 列：

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

 感觉这别名并没有多大实际作用。

 触发器：

触发器是数据库的回调函数，在指定的数据库事件发生时自动执行。

要点：

　　SQLite 的触发器（Trigger） 可以指定在特定的数据库表发生 DELETE、 INSERT或 UPDATE时触发， 或在一个或多个指定表的列发生更新时触发。

　　SQLite 只支持 FOR EACH ROW 触发器（Trigger） ， 没有 FOR EACH STATEMENT触发器（Trigger） 。 因此， 明确指定 FOR EACH ROW 是可选的。
　　WHEN 子句和触发器（Trigger） 动作可能访问使用表单 NEW.column-name 和 OLD.column-name 的引用插入、 删除或更新的行元素， 其中 column-name 是从与触发器关联的表的列的名称。

　　如果提供 WHEN 子句， 则只针对 WHEN 子句为真的指定行执行 SQL语句。 如果没有提供 WHEN 子句， 则针对所有行执行 SQL语句。
　　BEFORE或 AFTER 关键字决定何时执行触发器动作， 决定是在关联行的插入、 修改或删除之前或者之后执行触发器动作。

　　当触发器相关联的表删除时， 自动删除触发器（Trigger） 。

　　要修改的表必须存在于同一数据库中， 作为触发器被附加的表或视图， 且必须只使用 tablename， 而不是database.tablename。
　　一个特殊的 SQL函数 RAISE() 可用于触发器程序内抛出异常

基本语法：

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;

 event_name可以是Insert, delete, update等。

有时想查看下有哪些触发器：

SELECT name FROM sqlite_master
WHERE type = 'trigger';

 这样就能查看所有的触发器，而删除触发器则是：

DROP TRIGGER trigger_name;

索引：

索引是一种指向数据表中的数据的指针，类似有些书中的索引。索引能加快Select 和Where子句，但是会减慢UPDATE和 INSERT
创建索引的基本语法：

CREATE INDEX index_name ON table_name;

 当然这里的table_name如果换成column_name就变成了针对某一列创建索引。有时在Create Index之间可以加上Unique，这样就创建了一个唯一索引。

删除索引：

DROP INDEX index_name;

有时需要指定用哪个索引

SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);

Alter:

事实上Alter只能用来修改表名或者在原有的表中添加额外的列。基本语法分别为：

ALTER TABLE database_name.table_name RENAME TO new_table_name;

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

有时需要删除数据表，我们可以用Drop table_name命令，但也可以用Delete命令，基本语法如下：

 DELETE FROM table_name;

这时可以使用Vacuum命令来清空空间。

视图：

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

这里的Temp, Temporary关键字决定了是否是一个临时视图，如果需要查询视图，查询方法与查询一张表一样，因为视图创建一张虚表。

删除视图：

DROP VIEW view_name;