sqlite常用操作语句

PRAGMA foreign_keys = 0; CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM Websites; DROP TABLE Websites; CREATE TABLE Websites ( WebsiteId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, WebsiteName VARCHAR (255), WebsiteDirectory VARCHAR (255), DbType INT DEFAULT (0), DbName VARCHAR (100), DbUid VARCHAR (50), DbPwd VARCHAR (30), BackupDirectory VARCHAR (255), BackupDateTime DATETIME, AddDateTime DATETIME ); INSERT INTO Websites ( WebsiteId, WebsiteName, WebsiteDirectory, DbType, DbName, DbUid, DbPwd, BackupDirectory, BackupDateTime, AddDateTime ) SELECT WebsiteId, WebsiteName, WebsiteDirectory, DbType, DbName, DbUid, DbPwd, BackupDirectory, BackupDateTime, AddDateTime FROM sqlitestudio_temp_table; DROP TABLE sqlitestudio_temp_table; PRAGMA foreign_keys = 1;

SQLite数据库中获取新插入数据的自增长ID

SQLite数据库中有一有列名为ID的自增列,项目需求要在向数据库在插入新数据的同时返回新插入数据行的ID。 我这里用事务,把插入和查询语句通过ExecuteReader一起提交,返回DbDataReader。 实现代码

  转载请注明出处:http://www.cnblogs.com/keitsi/p/5558985.html

各类程序员学习路线图

程序猿是一种非常特殊的、可以从事程序开发、维护的动物。一般分为程序设计猿和程序编码猿,但两者的界限并不非常清楚,都可以进行开发、维护工作。 前端工程师 基础:HTML教程, CSS教程 进阶:Javascript,jQuery 高级:AJAX 教程,JSON 教程,XML 教程 框架扩展学习:Bootstrap 教程,jQuery UI 教程,jQuery EasyUI 教程,jQuery Mobile 教程 调试工具:Firebug 教程 开发工具:HBuilder,Dreamweaver,Notepad++,Photoshop PHP 工程师 基础:HTML教程,PHP 教程 进阶:SQL 教程,MySQL 教程 高级:正则表达式教程,Mongodb教程, AJAX 教程,JSON 教程,XML 教程, 开发工具:Notepad++,phpMyAdmin,EasyEclipse for PHP Python 工程师 基础:Python 教程 进阶:SQL 教程,MySQL 教程 高级:正则表达式教程,Mongodb教程, AJAX 教程,JSON 教程,XML 教程, 开发工具:PyDev for Eclipse .NET 工程师 基础:C# 教程,ASP.NET 教程 进阶:SQL 教程 高级:正则表达式教程,Mongodb教程, AJAX 教程,JSON 教程,XML 教程, 开发工具:visual studio 2010 JAVA 工程师 基础:Java 教程,JSP 教程 进阶:SQL 教程,MySQL 教程 高级:正则表达式教程,Mongodb教程, AJAX 教程,JSON 教程,XML 教程, 开发工具:Eclipse ASP 工程师 ASP 现在应该比较少用,建议学习路线为:HTML教程,ASP 教程,SQL 教程 相关图谱 前端工程师必备技能图谱 各类程序开发语言概述 运维工程师必备技能图谱 大数据工程师必备技能图谱 嵌入式开发必备技能图谱 Hadoop 家族技能图谱 安全工程师必备技能图谱 云计算工程师必备技能图谱 iOS 开发工程师必备技能图谱 移动无线测试工程师必备技能图谱 from:http://www.runoob.com/coder-learn-path  

SQLite 日期 & 时间

SQLite 支持以下五个日期和时间函数: 序号 函数 实例 1 date(timestring, modifier, modifier, …) 以 YYYY-MM-DD 格式返回日期。 2 time(timestring, modifier, modifier, …) 以 HH:MM:SS 格式返回时间。 3 datetime(timestring, modifier, modifier, …) 以 YYYY-MM-DD HH:MM:SS 格式返回。 4 julianday(timestring, modifier, modifier, …) 这将返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数。 5 strftime(format, timestring, modifier, modifier, …) 这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。 上述五个日期和时间函数把时间字符串作为参数。时间字符串后跟零个或多个 modifier 修饰符。strftime() 函数也可以把格式字符串 format 作为其第一个参数。下面将为您详细讲解不同类型的时间字符串和修饰符。 时间字符串 一个时间字符串可以采用下面任何一种格式: 序号 时间字符串 实例 1 YYYY-MM-DD 2010-12-30 2 YYYY-MM-DD HH:MM 2010-12-30 12:10 3 YYYY-MM-DD HH:MM:SS.SSS 2010-12-30 12:10:04.100 4 MM-DD-YYYY HH:MM 30-12-2010 12:10 5 HH:MM 12:10 6 YYYY-MM-DDTHH:MM 2010-12-30 12:10 7 HH:MM:SS 12:10:01 8 YYYYMMDD HHMMSS 20101230 121001 9 now 2013-05-07 您可以使用 "T" 作为分隔日期和时间的文字字符。 修饰符(Modifier) 时间字符串后边可跟着零个或多个的修饰符,这将改变有上述五个函数返回的日期和/或时间。任何上述五大功能返回时间。修饰符应从左到右使用,下面列出了可在 SQLite 中使用的修饰符: NNN days NNN hours NNN minutes NNN.NNNN seconds NNN months NNN years start of month start of year start of day weekday N unixepoch localtime utc 格式化 SQLite 提供了非常方便的函数 strftime() 来格式化任何日期和时间。您可以使用以下的替换来格式化日期和时间: 替换 描述 %d 一月中的第几天,01-31 %f 带小数部分的秒,SS.SSS %H 小时,00-23 %j 一年中的第几天,001-366 %J 儒略日数,DDDD.DDDD %m 月,00-12 %M 分,00-59 %s 从 1970-01-01 算起的秒数 %S 秒,00-59 %w 一周中的第几天,0-6 (0 is Sunday) %W 一年中的第几周,01-53 %Y 年,YYYY %% % symbol 实例 现在让我们使用 SQLite 提示符尝试不同的实例。下面是计算当前日期:

下面是计算当前月份的最后一天:

下面是计算给定 UNIX 时间戳 1092941466 的日期和时间:

下面是计算给定 UNIX 时间戳 1092941466 相对本地时区的日期和时间:

下面是计算当前的 UNIX 时间戳:

下面是计算美国"独立宣言"签署以来的天数:

下面是计算从 2004 年某一特定时刻以来的秒数:

下面是计算当年 10 月的第一个星期二的日期:

下面是计算从 UNIX 纪元算起的以秒为单位的时间(类似 strftime('%s',’now') ,不同的是这里有包括小数部分):

在 UTC 与本地时间值之间进行转换,当格式化日期时,使用 utc 或 localtime 修饰符,如下所示:

from:http://www.runoob.com/sqlite/sqlite-date-time.html

SQLite 常用函数

SQLite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数,且所有函数都是大小写不敏感,这意味着您可以使用这些函数的小写形式或大写形式或混合形式。欲了解更多详情,请查看 SQLite 的官方文档: 序号 函数 & 描述 1 SQLite COUNT 函数 SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。 2 SQLite MAX 函数 SQLite MAX 聚合函数允许我们选择某列的最大值。 3 SQLite MIN 函数 SQLite MIN 聚合函数允许我们选择某列的最小值。 4 SQLite AVG 函数 SQLite AVG 聚合函数计算某列的平均值。 5 SQLite SUM 函数 SQLite SUM 聚合函数允许为一个数值列计算总和。 6 SQLite RANDOM 函数 SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。 7 SQLite ABS 函数 SQLite ABS 函数返回数值参数的绝对值。 8 SQLite UPPER 函数 SQLite UPPER 函数把字符串转换为大写字母。 9 SQLite LOWER 函数 SQLite LOWER 函数把字符串转换为小写字母。 10 SQLite LENGTH 函数 SQLite LENGTH 函数返回字符串的长度。 11 SQLite sqlite_version 函数 SQLite sqlite_version 函数返回 SQLite 库的版本。 在我们开始讲解这些函数实例之前,先假设 COMPANY 表有以下记录:

SQLite COUNT 函数 SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite MAX 函数 SQLite MAX 聚合函数允许我们选择某列的最大值。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite MIN 函数 SQLite MIN 聚合函数允许我们选择某列的最小值。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite AVG 函数 SQLite AVG 聚合函数计算某列的平均值。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite SUM 函数 SQLite SUM 聚合函数允许为一个数值列计算总和。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite RANDOM 函数 SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite ABS 函数 SQLite ABS 函数返回数值参数的绝对值。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite UPPER 函数 SQLite UPPER 函数把字符串转换为大写字母。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite LOWER 函数 SQLite LOWER 函数把字符串转换为小写字母。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite LENGTH 函数 SQLite LENGTH 函数返回字符串的长度。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite sqlite_version 函数 SQLite sqlite_version 函数返回 SQLite 库的版本。下面是实例:

上面的 SQLite SQL 语句将产生以下结果:

SQLite 注入

如果您的站点允许用户通过网页输入,并将输入内容插入到 SQLite 数据库中,这个时候您就面临着一个被称为 SQL 注入的安全问题。本章节将向您讲解如何防止这种情况的发生,确保脚本和 SQLite 语句的安全。 注入通常在请求用户输入时发生,比如需要用户输入姓名,但用户却输入了一个 SQLite 语句,而这语句就会在不知不觉中在数据库上运行。 永远不要相信用户提供的数据,所以只处理通过验证的数据,这项规则是通过模式匹配来完成的。在下面的实例中,用户名 username 被限制为字母数字字符或者下划线,长度必须在 8 到 20 个字符之间 – 请根据需要修改这些规则。

为了演示这个问题,假设考虑此摘录:To demonstrate the problem, consider this excerpt:

函数调用是为了从用户表中检索 name 列与用户指定的名称相匹配的记录。正常情况下,$name 只包含字母数字字符或者空格,比如字符串 ilia。但在这里,向 $name 追加了一个全新的查询,这个对数据库的调用将会造成灾难性的问题:注入的 DELETE 查询会删除 users 的所有记录。 虽然已经存在有不允许查询堆叠或在单个函数调用中执行多个查询的数据库接口,如果尝试堆叠查询,则会调用失败,但 SQLite 和 PostgreSQL 里仍进行堆叠查询,即执行在一个字符串中提供的所有查询,这会导致严重的安全问题。 防止 SQL 注入 在脚本语言中,比如 PERL 和 PHP,您可以巧妙地处理所有的转义字符。编程语言 PHP 提供了字符串函数 sqlite_escape_string() 来转义对于 SQLite 来说比较特殊的输入字符。

虽然编码使得插入数据变得安全,但是它会呈现简单的文本比较,在查询中,对于包含二进制数据的列,LIKE 子句是不可用的。 请注意,addslashes() 不应该被用在 SQLite 查询中引用字符串,它会在检索数据时导致奇怪的结果。   from:http://www.runoob.com/sqlite/sqlite-injection.html

SQLite Autoincrement(自动递增)

SQLite 的 AUTOINCREMENT 是一个关键字,用于表中的字段值自动递增。我们可以在创建表时在特定的列名称上使用 AUTOINCREMENT 关键字实现该字段值的自动增加。 关键字 AUTOINCREMENT 只能用于整型(INTEGER)字段。 语法 AUTOINCREMENT 关键字的基本用法如下:

实例 假设要创建的 COMPANY 表如下所示:

现在,向 COMPANY 表插入以下记录:

这将向 COMPANY 表插入 7 个元组,此时 COMPANY 表的记录如下:

from:http://www.runoob.com/sqlite/sqlite-autoincrement.html

SQLite 事务(Transaction)

事务(Transaction)是一个对数据库执行工作单元。事务(Transaction)是以逻辑顺序完成的工作单位或序列,可以是由用户手动操作完成,也可以是由某种数据库程序自动完成。 事务(Transaction)是指一个或多个更改数据库的扩展。例如,如果您正在创建一个记录或者更新一个记录或者从表中删除一个记录,那么您正在该表上执行事务。重要的是要控制事务以确保数据的完整性和处理数据库错误。 实际上,您可以把许多的 SQLite 查询联合成一组,把所有这些放在一起作为事务的一部分进行执行。 事务的属性 事务(Transaction)具有以下四个标准属性,通常根据首字母缩写为 ACID: 原子性(Atomicity):确保工作单位内的所有操作都成功完成,否则,事务会在出现故障时终止,之前的操作也会回滚到以前的状态。 一致性(Consistency):确保数据库在成功提交的事务上正确地改变状态。 隔离性(Isolation):使事务操作相互独立和透明。 持久性(Durability):确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。 事务控制 使用下面的命令来控制事务: BEGIN TRANSACTION:开始事务处理。 COMMIT:保存更改,或者可以使用 END TRANSACTION 命令。 ROLLBACK:回滚所做的更改。 事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用,因为这些操作在数据库中是自动提交的。 BEGIN TRANSACTION 命令 事务(Transaction)可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去,直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时,事务处理也会回滚。以下是启动一个事务的简单语法:

COMMIT 命令 COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令。 COMMIT 命令把自上次 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库。 COMMIT 命令的语法如下:

ROLLBACK 命令 ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务命令。 ROLLBACK 命令只能用于撤销自上次发出 COMMIT 或 ROLLBACK 命令以来的事务。 ROLLBACK 命令的语法如下:

实例 假设 COMPANY 表有以下记录:

现在,让我们开始一个事务,并从表中删除 age = 25 的记录,最后,我们使用 ROLLBACK 命令撤消所有的更改。

检查 COMPANY 表,仍然有以下记录:

现在,让我们开始另一个事务,从表中删除 age = 25 的记录,最后我们使用 COMMIT 命令提交所有的更改。

检查 COMPANY 表,有以下记录:

from:http://www.runoob.com/sqlite/sqlite-transaction.html

SQLite 索引(Index)

索引(Index)是一种特殊的查找表,数据库搜索引擎用来加快数据检索。简单地说,索引是一个指向表中数据的指针。一个数据库中的索引与一本书后边的索引是非常相似的。 例如,如果您想在一本讨论某个话题的书中引用所有页面,您首先需要指向索引,索引按字母顺序列出了所有主题,然后指向一个或多个特定的页码。 索引有助于加快 SELECT 查询和 WHERE 子句,但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除,但不会影响数据。 使用 CREATE INDEX 语句创建索引,它允许命名索引,指定表及要索引的一列或多列,并指示索引是升序排列还是降序排列。 索引也可以是唯一的,与 UNIQUE 约束类似,在列上或列组合上防止重复条目。 CREATE INDEX 命令 CREATE INDEX 的基本语法如下:

单列索引 单列索引是一个只基于表的一个列上创建的索引。基本语法如下:

唯一索引 使用唯一索引不仅是为了性能,同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下:

组合索引 组合索引是基于一个表的两个或多个列上创建的索引。基本语法如下:

是否要创建一个单列索引还是组合索引,要考虑到您在作为查询过滤条件的 WHERE 子句中使用非常频繁的列。 如果值使用到一个列,则选择使用单列索引。如果在作为过滤的 WHERE 子句中有两个或多个列经常使用,则选择使用组合索引。 隐式索引 隐式索引是在创建对象时,由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。 实例 下面是一个例子,我们将在 COMPANY 表的 salary 列上创建一个索引:

现在,让我们使用 .indices 命令列出 COMPANY 表上所有可用的索引,如下所示:

这将产生如下结果,其中 sqlite_autoindex_COMPANY_1 是创建表时创建的隐式索引。

您可以列出数据库范围的所有索引,如下所示:

DROP INDEX 命令 一个索引可以使用 SQLite 的 DROP 命令删除。当删除索引时应特别注意,因为性能可能会下降或提高。 基本语法如下:

您可以使用下面的语句来删除之前创建的索引:

什么情况下要避免使用索引? 虽然索引的目的在于提高数据库的性能,但这里有几个情况需要避免使用索引。使用索引时,应重新考虑下列准则: 索引不应该使用在较小的表上。 索引不应该使用在有频繁的大批量的更新或插入操作的表上。 索引不应该使用在含有大量的 NULL 值的列上。 索引不应该使用在频繁操作的列上。 from:http://www.runoob.com/sqlite/sqlite-index.html

SQLite Unions 子句

SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果,不返回任何重复的行。 为了使用 UNION,每个 SELECT 被选择的列数必须是相同的,相同数目的列表达式,相同的数据类型,并确保它们有相同的顺序,但它们不必具有相同的长度。 语法 UNION 的基本语法如下:

这里给定的条件根据需要可以是任何表达式。 实例 假设有下面两个表,(1)COMPANY 表如下所示:

(2)另一个表是 DEPARTMENT,如下所示:

现在,让我们使用 SELECT 语句及 UNION 子句来连接两个表,如下所示:

这将产生以下结果:

UNION ALL 子句 UNION ALL 运算符用于结合两个 SELECT 语句的结果,包括重复行。 适用于 UNION 的规则同样适用于 UNION ALL 运算符。 语法 UNION ALL 的基本语法如下:

这里给定的条件根据需要可以是任何表达式。 实例 现在,让我们使用 SELECT 语句及 UNION ALL 子句来连接两个表,如下所示:

这将产生以下结果:

SQLite Joins

SQLite 的 Joins 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段。 SQL 定义了三种主要类型的连接: 交叉连接 – CROSS JOIN 内连接 – INNER JOIN 外连接 – OUTER JOIN 在我们继续之前,让我们假设有两个表 COMPANY 和 DEPARTMENT。我们已经看到了用来填充 COMPANY 表的 INSERT 语句。现在让我们假设 COMPANY 表的记录列表如下:

另一个表是 DEPARTMENT,定义如下:

下面是填充 DEPARTMENT 表的 INSERT 语句:

最后,我们在 DEPARTMENT 表中有下列的记录列表:

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

基于上面的表,我们可以写一个交叉连接(CROSS JOIN),如下所示:

上面的查询会产生以下结果:

内连接 – INNER JOIN 内连接(INNER JOIN)根据连接谓词结合两个表(table1 和 table2)的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较,找到所有满足连接谓词的行的匹配对。当满足连接谓词时,A 和 B 行的每个匹配对的列值会合并成一个结果行。 内连接(INNER JOIN)是最常见的连接类型,是默认的连接类型。INNER 关键字是可选的。 下面是内连接(INNER JOIN)的语法:

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

自然连接(NATURAL JOIN)类似于 JOIN…USING,只是它会自动测试存在两个表中的每一列的值之间相等值:

基于上面的表,我们可以写一个内连接(INNER JOIN),如下所示:

上面的查询会产生以下结果:

外连接 – OUTER JOIN 外连接(OUTER JOIN)是内连接(INNER JOIN)的扩展。虽然 SQL 标准定义了三种类型的外连接:LEFT、RIGHT、FULL,但 SQLite 只支持 左外连接(LEFT OUTER JOIN)。 外连接(OUTER JOIN)声明条件的方法与内连接(INNER JOIN)是相同的,使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成,外连接(OUTER JOIN)将从一个或两个表中任何未连接的行合并进来,外连接的列使用 NULL 值,将它们附加到结果表中。 下面是左外连接(LEFT OUTER JOIN)的语法:

为了避免冗余,并保持较短的措辞,可以使用 USING 表达式声明外连接(OUTER JOIN)条件。这个表达式指定一个或多个列的列表:

基于上面的表,我们可以写一个外连接(OUTER JOIN),如下所示:

上面的查询会产生以下结果: