sqlite_教程(sqlite_tutorial.pdf)

【实例截图】

【核心代码】

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
SQLite
v
SQLite ─ Injection..................................................................................................................................123
Preventing SQL Injection .............................................................................................................................123
SQLite ─ EXPLAIN ..................................................................................................................................125
SQLite ─ VACUUM.................................................................................................................................127
Manual VACUUM ........................................................................................................................................127
Auto-VACCUM .............................................................................................................................................127
SQLite ─ Date & Time ............................................................................................................................129
Time Strings.................................................................................................................................................129
Modifiers .....................................................................................................................................................130
Formatters...................................................................................................................................................130
SQLite ─ Useful Functions......................................................................................................................133
SQLite COUNT Function...............................................................................................................................134
SQLite MAX Function...................................................................................................................................135
SQLite MIN Function....................................................................................................................................135
SQLite AVG Function ...................................................................................................................................135
SQLite SUM Function...................................................................................................................................136
SQLite RANDOM Function ...........................................................................................................................136
SQLite ABS Function ....................................................................................................................................136
SQLite UPPER Function................................................................................................................................136
SQLite LOWER Function...............................................................................................................................137
SQLite LENGTH Function .............................................................................................................................137
SQLite sqlite_version Function ....................................................................................................................138
SQLITE INTERFACES ................................................................................................................. 139
SQLite ─ C/C   ......................................................................................................................................140
C/C   Interface APIs....................................................................................................................................140
Connect to Database ...................................................................................................................................141
Create a Table..............................................................................................................................................142
INSERT Operation ........................................................................................................................................143
SELECT Operation ........................................................................................................................................145
UPDATE Operation ......................................................................................................................................147
DELETE Operation........................................................................................................................................149
SQLite ─ Java.........................................................................................................................................152
Installation...................................................................................................................................................152
Connect to Database ...................................................................................................................................152
Create a Table..............................................................................................................................................153
INSERT Operation ........................................................................................................................................154
SELECT Operation ........................................................................................................................................155
UPDATE Operation ......................................................................................................................................157
DELETE Operation........................................................................................................................................159
SQLite ─ PHP .........................................................................................................................................162
Installation...................................................................................................................................................162
PHP Interface APIs.......................................................................................................................................162
Connect to Database ...................................................................................................................................163
Create a Table..............................................................................................................................................164
SQLite
vi
INSERT Operation ........................................................................................................................................165
SELECT Operation ........................................................................................................................................166
UPDATE Operation ......................................................................................................................................168
DELETE Operation........................................................................................................................................169
SQLite ─ Perl .........................................................................................................................................172
Installation...................................................................................................................................................172
DBI Interface APIs........................................................................................................................................172
Connect to Database ...................................................................................................................................174
Create a Table..............................................................................................................................................175
INSERT Operation ........................................................................................................................................176
SELECT Operation ........................................................................................................................................177
UPDATE Operation ......................................................................................................................................179
DELETE Operation........................................................................................................................................180
SQLite ─ Python ....................................................................................................................................183
Installation...................................................................................................................................................183
Python sqlite3 module APIs.........................................................................................................................183
Connect to Database ...................................................................................................................................186
Create a Table..............................................................................................................................................186
INSERT Operation ........................................................................................................................................187
SELECT Operation ........................................................................................................................................188
UPDATE Operation ......................................................................................................................................189
DELETE Operation........................................................................................................................................190