Sqlite3Tutorial入门教程资源-CSDN文库

需积分: 10 167 浏览量 2017-03-04 14:02:51 上传评论收藏 1.48MB PDF 举报

SQLite是一种自包含、无服务器、零配置、事务型的SQL数据库引擎软件库。它是世界上部署最广泛的SQL数据库引擎。SQLite的源代码在公共领域，即任何人都可以自由地使用和修改。本教程将引导初学者快速开始使用SQLite，并帮助他们舒适地掌握SQLite编程。本教程为初学者准备，帮助他们理解与SQL数据库引擎相关的从基础到高级的概念。在开始练习本参考文献中给出的各类示例之前，我们假设您已经了解什么是数据库，特别是关系型数据库管理系统（RDBMS），以及什么是计算机编程语言。在本教程中，您将学习以下SQLite相关知识点： SQLite概述：了解SQLite是什么，它的简要历史，以及它的局限性。SQLite是一个轻量级的数据库引擎，它不需要配置，不需要独立的服务器进程运行。它适用于需要将数据库嵌入应用程序中的场景。由于其小巧和高效性，SQLite被广泛应用于各种嵌入式系统和移动设备中。安装SQLite：本部分将向您展示如何在Windows、Linux和Mac OS X平台上安装SQLite。对于Windows用户，可以下载可执行的安装包；对于Linux用户，可以通过包管理器安装；而Mac OS X用户则可以使用Homebrew或直接从SQLite官方网站下载预编译的二进制包。 SQLite命令：介绍一些基本的SQLite命令，这些命令可以用来操作SQLite数据库，包括创建数据库、创建表、插入数据、查询数据等。 SQLite语法：学习SQLite的基本语法规则，以及如何编写有效的SQLite语句。 SQLite数据类型：了解SQLite支持的数据类型，包括其存储类别和亲缘类型，以及如何在创建表时指定列的数据类型。 SQLite基本操作：包括如何创建和删除数据库和表，如何插入数据到表中，以及如何执行基本的查询和选择数据。 SQLite操作符：SQLite中的操作符是什么，包括它们的使用场景和作用，如比较运算符、逻辑运算符、算术运算符等。在探索SQLite的世界时，你会发现它是一个功能强大且易于使用的工具，非常适合于轻量级应用开发。由于其广泛的部署和开源特性，SQLite也为数据库学习提供了良好的平台，无论是对于初学者还是经验丰富的开发人员来说都是如此。记住，在实践示例之前，你应该已经对数据库有一定的了解，并且了解至少一种编程语言，这样可以更好地理解SQLite的工作原理和应用场景。请注意，本教程的所有内容和图形的版权归TutorialsPoint(I) Pvt. Ltd.所有。本电子书的用户不得未经出版商书面同意，以任何形式重新使用、保留、复制、分发或重新发布本电子书的任何内容或部分内容。尽管我们将尽力及时、精确地更新我们网站和教程的内容，但内容可能包含不准确之处或错误。TutorialsPoint(I) Pvt. Ltd.不保证我们网站或其内容（包括本教程）的准确性、及时性或完整性。如果你在我们的网站或本教程中发现任何错误，请通过***告知我们。

资源推荐

资源详情

资源评论

SQLite 
 
ii 
 
Table of Contents 
About the Tutorial ............................................................................................................................................ i 
Audience ........................................................................................................................................................... i 
Prerequisites ..................................................................................................................................................... i 
Disclaimer & Copyright ..................................................................................................................................... i 
Table of Contents ............................................................................................................................................ ii 
SQLITE BASICS ............................................................................................................................. 1 
  SQLite ─ Overview ..................................................................................................................................... 2 
What is SQLite? ............................................................................................................................................... 2 
Why SQLite? .................................................................................................................................................... 2 
SQLite ─ A Brief History ................................................................................................................................... 3 
SQLite Limitations ............................................................................................................................................ 3 
SQLite Commands ........................................................................................................................................... 3 
  SQLite ─ Installation .................................................................................................................................. 5 
Install SQLite on Windows ............................................................................................................................... 5 
Install SQLite on Linux ..................................................................................................................................... 5 
Install SQLite on Mac OS X ............................................................................................................................... 6 
  SQLite ─ Commands .................................................................................................................................. 7 
  SQLite ─ Syntax ....................................................................................................................................... 11 
  SQLite ─ Data Type .................................................................................................................................. 18 
SQLite Storage Classes ................................................................................................................................... 18 
SQLite Affinity Type ....................................................................................................................................... 18 
SQLite Affinity and Type Names .................................................................................................................... 19 
  SQLite ─ CREATE Database ...................................................................................................................... 21 
The .dump Command .................................................................................................................................... 22 
  SQLite ─ ATTACH Database ..................................................................................................................... 23 
  SQLite ─ DETACH Database ..................................................................................................................... 24 
  SQLite ─ CREATE Table ............................................................................................................................ 25 
  SQLite ─ DROP Table ............................................................................................................................... 27 
  SQLite ─ INSERT Query ............................................................................................................................ 28 
  SQLite ─ SELECT Query ............................................................................................................................ 31 
  SQLite ─ Operators .................................................................................................................................. 34 
What is an Operator in SQLite? ..................................................................................................................... 34 
SQLite Arithmetic Operators ......................................................................................................................... 34 
SQLite Comparison Operators ....................................................................................................................... 35 
SQLite Logical Operators ............................................................................................................................... 38 
SQLite Bitwise Operators ............................................................................................................................... 42 

SQLite 
 
iii 
 
  SQLite ─ Expressions ............................................................................................................................... 44 
SQLite - Boolean Expression .......................................................................................................................... 44 
SQLite - Numeric Expression ......................................................................................................................... 45 
SQLite - Date Expression ................................................................................................................................ 45 
  SQLite ─ WHERE Clause ........................................................................................................................... 46 
  SQLite ─ AND & OR Operators ................................................................................................................. 50 
The AND Operator ......................................................................................................................................... 50 
The OR Operator ........................................................................................................................................... 51 
  SQLite ─ UPDATE Query .......................................................................................................................... 53 
  SQLite ─ DELETE Query ............................................................................................................................ 55 
  SQLite ─ LIKE Clause ................................................................................................................................ 57 
  SQLite ─ GLOB Clause .............................................................................................................................. 60 
  SQLite ─ LIMIT Clause .............................................................................................................................. 63 
  SQLite ─ ORDER BY Clause ....................................................................................................................... 65 
  SQLite ─ GROUP BY Clause ...................................................................................................................... 67 
  SQLite ─ HAVING Clause .......................................................................................................................... 70 
  SQLite ─ DISTINCT Keyword .................................................................................................................... 72 
ADVANCED SQLITE .................................................................................................................... 74 
  SQLite ─ PRAGMA ................................................................................................................................... 75 
auto_vacuum Pragma.................................................................................................................................... 75 
cache_size Pragma ........................................................................................................................................ 76 
case_sensitive_like Pragma ........................................................................................................................... 76 
count_changes Pragma ................................................................................................................................. 76 
database_list Pragma .................................................................................................................................... 76 
encoding Pragma ........................................................................................................................................... 76 
freelist_count Pragma ................................................................................................................................... 77 
index_info Pragma ......................................................................................................................................... 77 
index_list Pragma .......................................................................................................................................... 77 
journal_mode Pragma ................................................................................................................................... 77 
max_page_count Pragma .............................................................................................................................. 78 
page_count Pragma ....................................................................................................................................... 78 
page_size Pragma .......................................................................................................................................... 78 
parser_trace Pragma ..................................................................................................................................... 78 
recursive_triggers Pragma ............................................................................................................................. 79 
schema_version Pragma................................................................................................................................ 79 
secure_delete Pragma ................................................................................................................................... 79 
sql_trace Pragma ........................................................................................................................................... 79 
synchronous Pragma ..................................................................................................................................... 80 
temp_store Pragma ....................................................................................................................................... 80 

SQLite 
 
iv 
 
temp_store_directory Pragma ...................................................................................................................... 80 
user_version Pragma ..................................................................................................................................... 81 
writable_schema Pragma .............................................................................................................................. 81 
  SQLite ─ Constraints ................................................................................................................................ 82 
NOT NULL Constraint ..................................................................................................................................... 82 
DEFAULT Constraint ...................................................................................................................................... 83 
UNIQUE Constraint ........................................................................................................................................ 83 
PRIMARY KEY Constraint ............................................................................................................................... 84 
CHECK Constraint .......................................................................................................................................... 84 
Dropping Constraint ...................................................................................................................................... 85 
  SQLite ─ JOINS ......................................................................................................................................... 86 
The CROSS JOIN ............................................................................................................................................. 87 
The INNER JOIN ............................................................................................................................................. 88 
The OUTER JOIN ............................................................................................................................................ 89 
  SQLite ─ UNION Clause............................................................................................................................ 90 
The UNION ALL Clause ................................................................................................................................... 92 
  SQLite ─ NULL Values .............................................................................................................................. 94 
  SQLite ─ ALIAS Syntax ............................................................................................................................. 97 
  SQLite ─ Triggers ................................................................................................................................... 100 
Listing Triggers ............................................................................................................................................. 102 
Dropping Triggers ........................................................................................................................................ 103 
  SQLite ─ Indexes.................................................................................................................................... 104 
The CREATE INDEX Command ..................................................................................................................... 104 
The DROP INDEX Command ........................................................................................................................ 106 
  SQLite – INDEXED BY Clause .................................................................................................................. 107 
  SQLite ─ ALTER TABLE Command .......................................................................................................... 109 
  SQLite ─ TRUNCATE TABLE Command ................................................................................................... 111 
  SQLite ─ Views ...................................................................................................................................... 112 
Creating Views ............................................................................................................................................. 112 
Dropping Views ........................................................................................................................................... 113 
  SQLite ─ Transactions ............................................................................................................................ 114 
Properties of Transactions ........................................................................................................................... 114 
Transaction Control ..................................................................................................................................... 114 
  SQLite ─ Subqueries .............................................................................................................................. 117 
Subqueries with SELECT Statement ............................................................................................................. 117 
Subqueries with INSERT Statement ............................................................................................................. 118 
Subqueries with UPDATE Statement ........................................................................................................... 119 
Subqueries with DELETE Statement ............................................................................................................ 120 
  SQLite ─ AUTOINCREMENT ................................................................................................................... 121 