SQLite (Tutorials Point) (Z-Library)

Page 1

(This page has no text content)

Page 2

SQLite i About the Tutorial SQLite is a software library that implements a self-contained, serverless, zero- configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain. This tutorial will give you a quick start with SQLite and make you comfortable with SQLite programming. Audience This tutorial has been prepared for beginners to help them understand the basic-to- advanced concepts related to SQLite Database Engine. Prerequisites Before you start practicing various types of examples given in this reference, we assume that you are already aware about what is a database, especially RDBMS and what is a computer programming language. Disclaimer & Copyright Copyright 2017 by Tutorials Point (I) Pvt. Ltd. All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at contact@tutorialspoint.com.

Page 3

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

Page 4

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

Page 5

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

Page 6

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

Page 7

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

Page 8

SQLite 1 SQLite Basics

Page 9

SQLite 2 This chapter helps you understand what is SQLite, how it differs from SQL, why it is needed and the way in which it handles the applications Database. SQLite is a software library that implements a self-contained, serverless, zero- configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines around, but that's growth in terms of popularity, not anything to do with its size. The source code for SQLite is in the public domain. What is SQLite? SQLite is an in-process library that implements a self-contained, serverless, zero- configuration, transactional SQL database engine. It is a database, which is zero- configured, which means like other databases you do not need to configure it in your system. SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly. Why SQLite? SQLite does not require a separate server process or system to operate (serverless). SQLite comes with zero-configuration, which means no setup or administration needed. A complete SQLite database is stored in a single cross-platform disk file. SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted. SQLite is self-contained, which means no external dependencies. SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads. SQLite supports most of the query language features found in SQL92 (SQL2) standard. SQLite is written in ANSI-C and provides simple and easy-to-use API. SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT). SQLite ─ Overview

Page 10

SQLite 3 SQLite ─ A Brief History 2000 - D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program. 2000 - In August, SQLite 1.0 released with GNU Database Manager. 2011 - Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database). SQLite Limitations There are few unsupported features of SQL92 in SQLite which are listed in the following table. Feature Description RIGHT OUTER JOIN Only LEFT OUTER JOIN is implemented. FULL OUTER JOIN Only LEFT OUTER JOIN is implemented. ALTER TABLE The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported. Trigger support FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers. VIEWs VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. GRANT and REVOKE The only access permissions that can be applied are the normal file access permissions of the underlying operating system. SQLite Commands The standard SQLite commands to interact with relational databases are similar to SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature. DDL - Data Definition Language Command Description CREATE Creates a new table, a view of a table, or other object in database. ALTER Modifies an existing database object, such as a table.

Page 11

SQLite 4 DROP Deletes an entire table, a view of a table or other object in the database. DML - Data Manipulation Language Command Description INSERT Creates a record UPDATE Modifies records DELETE Deletes records DQL - Data Query Language Command Description SELECT Retrieves certain records from one or more tables

Page 12

SQLite 5 SQLite is famous for its great feature zero-configuration, which means no complex setup or administration is needed. This chapter will take you through the process of setting up SQLite on Windows, Linux and Mac OS X. Install SQLite on Windows Step 1: Go to SQLite download page, and download precompiled binaries from Windows section. Step 2: Download sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files. Step 3: Create a folder C:\>sqlite and unzip above two zipped files in this folder, which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files. Step 4: Add C:\>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command, which should display the following result. C:\>sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> Install SQLite on Linux Today, almost all the flavors of Linux OS are being shipped with SQLite. So you just issue the following command to check if you already have SQLite installed on your machine. $sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> If you do not see the above result, then it means you do not have SQLite installed on your Linux machine. Following are the steps to install SQLite: Step 1: Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section. SQLite ─ Installation

Page 13

SQLite 6 Step 2: Run the following command. $tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local $make $make install The above command will end with SQLite installation on your Linux machine, which you can verify as explained above. Install SQLite on Mac OS X Though the latest version of Mac OS X comes pre-installed with SQLite, but if you do not have installation available then just follow these steps: Step 1: Go to SQLite download page, and download sqlite-autoconf-*.tar.gz from source code section. Step 2: Run the following command. $tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local $make $make install The above procedure will end with SQLite installation on your Mac OS X machine, which you can verify by issuing the following command: $sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> Finally, you have SQLite command prompt where you can issue SQLite commands for your exercises.

Page 14

SQLite 7 This chapter will take you through simple and useful commands used by SQLite programmers. These commands are called SQLite dot commands and exception with these commands is that they should not be terminated by a semi-colon (;). Let's start with typing a simple sqlite3 command at command prompt which will provide you with SQLite command prompt where you will issue various SQLite commands. $sqlite3 SQLite version 3.3.6 Enter ".help" for instructions sqlite> For a listing of the available dot commands, you can enter ".help" any time. For example: sqlite>.help The above command will display a list of various important SQLite dot commands, which are listed in the following table. Command Description .backup ?DB? FILE Backup DB (default "main") to FILE .bail ON|OFF Stop after hitting an error. Default OFF .databases List names and files of attached databases .dump ?TABLE? Dump the database in an SQL text format. If TABLE specified, only dump tables matching LIKE pattern TABLE .echo ON|OFF Turn command echo on or off .exit Exit SQLite prompt .explain ON|OFF Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on .header(s) ON|OFF Turn display of headers on or off .help Show this message SQLite ─ Commands

Page 15

SQLite 8 .import FILE TABLE Import data from FILE into TABLE .indices ?TABLE? Show names of all indices. If TABLE specified, only show indices for tables matching LIKE pattern TABLE .load FILE ?ENTRY? Load an extension library .log FILE|off Turn logging on or off. FILE can be stderr/stdout .mode MODE Set output mode where MODE is one of: csv Comma-separated values column Left-aligned columns html HTML <table> code insert SQL insert statements for TABLE line One value per line list Values delimited by .separator string tabs Tab-separated values tcl TCL list elements .nullvalue STRING Print STRING in place of NULL values .output FILENAME Send output to FILENAME .output stdout Send output to the screen .print STRING... Print literal STRING .prompt MAIN CONTINUE Replace the standard prompts .quit Exit SQLite prompt

Page 16

SQLite 9 .read FILENAME Execute SQL in FILENAME .schema ?TABLE? Show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE .separator STRING Change separator used by output mode and .import .show Show the current values for various settings .stats ON|OFF Turn stats on or off .tables ?PATTERN? List names of tables matching a LIKE pattern .timeout MS Try opening locked tables for MS milliseconds .width NUM NUM Set column widths for "column" mode .timer ON|OFF Turn the CPU timer measurement on or off Let's try .show command to see default setting for your SQLite command prompt. sqlite>.show echo: off explain: off headers: off mode: column nullvalue: "" output: stdout separator: "|" width: sqlite> Make sure there is no space in between sqlite> prompt and dot command, otherwise it will not work.

Page 17

SQLite 10 Formatting Output You can use the following sequence of dot commands to format your output. sqlite>.header on sqlite>.mode column sqlite>.timer on sqlite> The above setting will produce the output in the following format. ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 CPU Time: user 0.000000 sys 0.000000 The sqlite_master Table The master table holds the key information about your database tables and it is called sqlite_master. You can see its schema as follows: sqlite>.schema sqlite_master This will produce the following result. CREATE TABLE sqlite_master ( type text, name text, tbl_name text, rootpage integer, sql text );

Page 18

SQLite 11 SQLite is followed by a unique set of rules and guidelines called Syntax. This chapter lists all the basic SQLite Syntax. Case Sensitivity Important point to be noted is that SQLite is case insensitive, but there are some commands, which are case sensitive like GLOB and glob have different meaning in SQLite statements. Comments SQLite comments are extra notes, which you can add in your SQLite code to increase its readability and they can appear anywhere; whitespace can occur, including inside expressions and in the middle of other SQL statements but they cannot be nested. SQL comments begin with two consecutive "-" characters (ASCII 0x2d) and extend up to and including the next newline character (ASCII 0x0a) or until the end of input, whichever comes first. You can also use C-style comments, which begin with "/*" and extend up to and including the next "*/" character pair or until the end of input, whichever comes first. C-style comments can span multiple lines. sqlite>.help -- This is a single line comment SQLite Statements All the SQLite statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., and all the statements end with a semicolon (;). SQLite ANALYZE Statement ANALYZE; or ANALYZE database_name; or ANALYZE database_name.table_name; SQLite AND/OR Clause SELECT column1, column2....columnN FROM table_name WHERE CONDITION-1 {AND|OR} CONDITION-2; SQLite ─ Syntax

Page 19

SQLite 12 SQLite ALTER TABLE Statement ALTER TABLE table_name ADD COLUMN column_def...; SQLite ALTER TABLE Statement (Rename) ALTER TABLE table_name RENAME TO new_table_name; SQLite ATTACH DATABASE Statement ATTACH DATABASE 'DatabaseName' As 'Alias-Name'; SQLite BEGIN TRANSACTION Statement BEGIN; or BEGIN EXCLUSIVE TRANSACTION; SQLite BETWEEN Clause SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2; SQLite COMMIT Statement COMMIT; SQLite CREATE INDEX Statement CREATE INDEX index_name ON table_name ( column_name COLLATE NOCASE ); SQLite CREATE UNIQUE INDEX Statement CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...columnN); SQLite CREATE TABLE Statement CREATE TABLE table_name( column1 datatype, column2 datatype,

Page 20

SQLite 13 column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) ); SQLite CREATE TRIGGER Statement CREATE TRIGGER database_name.trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN stmt1; stmt2; .... END; SQLite CREATE VIEW Statement CREATE VIEW database_name.view_name AS SELECT statement....; SQLite CREATE VIRTUAL TABLE Statement CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log ); or CREATE VIRTUAL TABLE database_name.table_name USING fts3( ); SQLite COMMIT TRANSACTION Statement COMMIT; SQLite COUNT Clause SELECT COUNT(column_name) FROM table_name WHERE CONDITION; SQLite DELETE Statement