You are previewing Using SQLite.

Using SQLite

Cover of Using SQLite by Jay A. Kreibich Published by O'Reilly Media, Inc.
  1. Using SQLite
  2. Dedication
  3. A Note Regarding Supplemental Files
  4. Preface
    1. SQLite Versions
    2. Email Lists
    3. Example Code Download
    4. How We Got Here
    5. Conventions Used in This Book
    6. Using Code Examples
    7. Safari® Books Online
    8. How to Contact Us
  5. 1. What Is SQLite?
    1. Self-Contained, No Server Required
    2. Single File Database
    3. Zero Configuration
    4. Embedded Device Support
    5. Unique Features
    6. Compatible License
    7. Highly Reliable
  6. 2. Uses of SQLite
    1. Database Junior
    2. Application Files
    3. Application Cache
    4. Archives and Data Stores
    5. Client/Server Stand-in
    6. Teaching Tool
    7. Generic SQL Engine
    8. Not the Best Choice
    9. Big Name Users
  7. 3. Building and Installing SQLite
    1. SQLite Products
    2. Precompiled Distributions
    3. Documentation Distribution
    4. Source Distributions
      1. The Amalgamation
      2. Source Files
      3. Source Downloads
    5. Building
      1. Configure
      2. Manually
      3. Build Customization
    6. Build and Installation Options
    7. An sqlite3 Primer
    8. Summary
  8. 4. The SQL Language
    1. Learning SQL
    2. Brief Background
      1. Declarative
      2. Portability
    3. General Syntax
      1. Basic Syntax
      2. Three-Valued Logic
      3. Simple Operators
    4. SQL Data Languages
    5. Data Definition Language
      1. Tables
      2. Views
      3. Indexes
    6. Data Manipulation Language
      1. Row Modification Commands
      2. The Query Command
    7. Transaction Control Language
      1. ACID Transactions
      2. SQL Transactions
      3. Save-Points
    8. System Catalogs
    9. Wrap-up
  9. 5. The SELECT Command
    1. SQL Tables
    2. The SELECT Pipeline
      1. FROM Clause
      2. WHERE Clause
      3. GROUP BY Clause
      4. SELECT Header
      5. HAVING Clause
      6. DISTINCT Keyword
      7. ORDER BY Clause
      8. LIMIT and OFFSET Clauses
    3. Advanced Techniques
      1. Subqueries
      2. Compound SELECT Statements
      3. Alternate JOIN Notation
    4. SELECT Examples
      1. Simple SELECTs
      2. Simple JOINs
      3. JOIN...ON
      4. JOIN...USING, NATURAL JOIN
      5. OUTER JOIN
      6. Compound JOIN
      7. Self JOIN
      8. WHERE Examples
      9. GROUP BY Examples
      10. ORDER BY Examples
    5. What’s Next
  10. 6. Database Design
    1. Tables and Keys
      1. Keys Define the Table
      2. Foreign Keys
      3. Foreign Key Constraints
      4. Generic ID Keys
      5. Keep It Specific
    2. Common Structures and Relationships
      1. One-to-One Relationships
      2. One-to-Many Relationships
      3. Many-to-Many Relationships
      4. Hierarchies and Trees
    3. Normal Form
      1. Normalization
      2. Denormalization
      3. The First Normal Form
      4. The Second Normal Form
      5. The Third Normal Form
      6. Higher Normal Forms
    4. Indexes
      1. How They Work
      2. Must Be Diverse
      3. INTEGER PRIMARY KEYs
      4. Order Matters
      5. One at a Time
      6. Index Summary
    5. Transferring Design Experience
      1. Tables Are Types
      2. Keys Are Backwards Pointers
      3. Do One Thing
    6. Closing
  11. 7. C Programming Interface
    1. API Overview
      1. Structure
      2. Strings and Unicode
      3. Error Codes
      4. Structures and Allocations
      5. More Info
    2. Library Initialization
    3. Database Connections
      1. Opening
      2. Special Cases
      3. Closing
      4. Example
    4. Prepared Statements
      1. Statement Life Cycle
      2. Prepare
      3. Step
      4. Result Columns
      5. Reset and Finalize
      6. Statement Transitions
      7. Examples
    5. Bound Parameters
      1. Parameter Tokens
      2. Binding Values
      3. Security and Performance
      4. Example
      5. Potential Pitfalls
    6. Convenience Functions
    7. Result Codes and Error Codes
      1. Standard Codes
      2. Extended Codes
      3. Error Functions
      4. Prepare v2
      5. Transactions and Errors
      6. Database Locking
    8. Utility Functions
      1. Version Management
      2. Memory Management
    9. Summary
  12. 8. Additional Features and APIs
    1. Date and Time Features
      1. Application Requirements
      2. Representations
      3. Time and Date Functions
    2. ICU Internationalization Extension
    3. Full-Text Search Module
      1. Creating and Populating FTS Tables
      2. Searching FTS Tables
      3. More Details
    4. R*Trees and Spatial Indexing Module
    5. Scripting Languages and Other Interfaces
      1. Perl
      2. PHP
      3. Python
      4. Java
      5. Tcl
      6. ODBC
      7. .NET
      8. C++
      9. Other Languages
    6. Mobile and Embedded Development
      1. Memory
      2. Storage
      3. Other Resources
      4. iPhone Support
      5. Other Environments
    7. Additional Extensions
  13. 9. SQL Functions and Extensions
    1. Scalar Functions
      1. Registering Functions
      2. Extracting Parameters
      3. Returning Results and Errors
      4. Example
    2. Aggregate Functions
      1. Defining Aggregates
      2. Aggregate Context
      3. Example
    3. Collation Functions
      1. Registering a Collation
      2. Collation Example
    4. SQLite Extensions
      1. Extension Architecture
      2. Extension Design
      3. Example Extension: sql_trig
      4. Building and Integrating Static Extensions
      5. Using Loadable Extensions
      6. Building Loadable Extensions
      7. Loadable Extension Security
      8. Loading Loadable Extensions
      9. Multiple Entry Points
      10. Chapter Summary
  14. 10. Virtual Tables and Modules
    1. Introduction to Modules
      1. Internal Modules
      2. External Modules
      3. Example Modules
      4. SQL for Anything
    2. Module API
    3. Simple Example: dblist Module
      1. Create and Connect
      2. Disconnect and Destroy
      3. Query Optimization
      4. Custom Functions
      5. Table Rename
      6. Opening and Closing Table Cursors
      7. Filtering Rows
      8. Extracting and Returning Data
      9. Virtual Table Modifications
      10. Cursor Sequence
      11. Transaction Control
      12. Register the Module
      13. Example Usage
    4. Advanced Example: weblog Module
      1. Create and Connect
      2. Disconnect and Destroy
      3. Other Table Functions
      4. Open and Close
      5. Filter
      6. Rows and Columns
      7. Register the Module
      8. Example Usage
    5. Best Index and Filter
      1. Purpose and Need
      2. xBestIndex()
      3. xFilter()
      4. Typical Usage
    6. Wrap-Up
  15. A. SQLite Build Options
    1. Shell Directives
      1. ENABLE_READLINE
    2. Default Values
      1. SQLITE_DEFAULT_AUTOVACUUM
      2. SQLITE_DEFAULT_CACHE_SIZE
      3. SQLITE_DEFAULT_FILE_FORMAT
      4. SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT
      5. SQLITE_DEFAULT_MEMSTATUS
      6. SQLITE_DEFAULT_PAGE_SIZE
      7. SQLITE_DEFAULT_TEMP_CACHE_SIZE
      8. YYSTACKDEPTH
    3. Sizes and Limits
      1. SQLITE_MAX_ATTACHED
      2. SQLITE_MAX_COLUMN
      3. SQLITE_MAX_COMPOUND_SELECT
      4. SQLITE_MAX_DEFAULT_PAGE_SIZE
      5. SQLITE_MAX_EXPR_DEPTH
      6. SQLITE_MAX_FUNCTION_ARG
      7. SQLITE_MAX_LENGTH
      8. SQLITE_MAX_LIKE_PATTERN_LENGTH
      9. SQLITE_MAX_PAGE_COUNT
      10. SQLITE_MAX_PAGE_SIZE
      11. SQLITE_MAX_SQL_LENGTH
      12. SQLITE_MAX_TRIGGER_DEPTH
      13. SQLITE_MAX_VARIABLE_NUMBER
    4. Operation and Behavior
      1. SQLITE_CASE_SENSITIVE_LIKE
      2. SQLITE_HAVE_ISNAN
      3. SQLITE_OS_OTHER
      4. SQLITE_SECURE_DELETE
      5. SQLITE_THREADSAFE
      6. SQLITE_TEMP_STORE
    5. Debug Settings
      1. SQLITE_DEBUG
      2. SQLITE_MEMDEBUG
    6. Enable Extensions
      1. SQLITE_ENABLE_ATOMIC_WRITE
      2. SQLITE_ENABLE_COLUMN_METADATA
      3. SQLITE_ENABLE_FTS3
      4. SQLITE_ENABLE_FTS3_PARENTHESIS
      5. SQLITE_ENABLE_ICU
      6. SQLITE_ENABLE_IOTRACE
      7. SQLITE_ENABLE_LOCKING_STYLE
      8. SQLITE_ENABLE_MEMORY_MANAGEMENT
      9. SQLITE_ENABLE_MEMSYS3
      10. SQLITE_ENABLE_MEMSYS5
      11. SQLITE_ENABLE_RTREE
      12. SQLITE_ENABLE_STAT2
      13. SQLITE_ENABLE_UPDATE_DELETE_LIMIT
      14. SQLITE_ENABLE_UNLOCK_NOTIFY
      15. YYTRACKMAXSTACKDEPTH
    7. Limit Features
      1. SQLITE_DISABLE_LFS
      2. SQLITE_DISABLE_DIRSYNC
      3. SQLITE_ZERO_MALLOC
    8. Omit Core Features
  16. B. sqlite3 Command Reference
    1. Command-Line Options
    2. Interactive Dot-Commands
      1. .backup
      2. .bail
      3. .databases
      4. .dump
      5. .echo
      6. .exit
      7. .explain
      8. .headers
      9. .help
      10. .import
      11. .indices
      12. .iotrace
      13. .load
      14. .log
      15. .mode
      16. .nullvalue
      17. .output
      18. .prompt
      19. .quit
      20. .read
      21. .restore
      22. .schema
      23. .separator
      24. .show
      25. .tables
      26. .timeout
      27. .timer
      28. .width
  17. C. SQLite SQL Command Reference
    1. SQLite SQL Commands
      1. ALTER TABLE
      2. ANALYZE
      3. ATTACH DATABASE
      4. BEGIN TRANSACTION
      5. COMMIT TRANSACTION
      6. CREATE INDEX
      7. CREATE TABLE
      8. CREATE TRIGGER
      9. CREATE VIEW
      10. CREATE VIRTUAL TABLE
      11. DELETE
      12. DETACH DATABASE
      13. DROP INDEX
      14. DROP TABLE
      15. DROP TRIGGER
      16. DROP VIEW
      17. END TRANSACTION
      18. EXPLAIN
      19. INSERT
      20. PRAGMA
      21. REINDEX
      22. RELEASE SAVEPOINT
      23. REPLACE
      24. ROLLBACK TRANSACTION
      25. SAVEPOINT
      26. SELECT
      27. UPDATE
      28. VACUUM
  18. D. SQLite SQL Expression Reference
    1. Literal Expressions
    2. Logic Representations
    3. Unary Expressions
    4. Binary Expressions
    5. Function Calls
    6. Column Names
    7. General Expressions
      1. AND
      2. BETWEEN
      3. CASE
      4. CAST
      5. COLLATE
      6. EXISTS
      7. GLOB
      8. IN
      9. IS
      10. ISNULL
      11. LIKE
      12. MATCH
      13. NOTNULL
      14. OR
      15. RAISE
      16. REGEXP
      17. SELECT
  19. E. SQLite SQL Function Reference
    1. Scalar Functions
      1. abs()
      2. changes()
      3. coalesce()
      4. date()
      5. datetime()
      6. glob()
      7. ifnull()
      8. hex()
      9. julianday()
      10. last_insert_rowid()
      11. length()
      12. like()
      13. load_extension()
      14. lower()
      15. ltrim()
      16. match()
      17. max()
      18. min()
      19. nullif()
      20. quote()
      21. random()
      22. randomblob()
      23. regex()
      24. replace()
      25. round()
      26. rtrim()
      27. sqlite_compileoption_get()
      28. sqlite_compileoption_used()
      29. sqlite_source_id()
      30. sqlite_version()
      31. strftime()
      32. substr()
      33. time()
      34. total_changes()
      35. trim()
      36. typeof()
      37. upper()
      38. zeroblob()
    2. Aggregate Functions
      1. avg()
      2. count()
      3. group_concat()
      4. max()
      5. min()
      6. sum()
      7. total()
  20. F. SQLite SQL PRAGMA Reference
    1. SQLite PRAGMAs
      1. auto_vacuum
      2. cache_size
      3. case_sensitive_like
      4. collation_list
      5. count_changes
      6. database_list
      7. default_cache_size
      8. encoding
      9. foreign_keys
      10. foreign_key_list
      11. freelist_count
      12. full_column_names
      13. fullfsync
      14. ignore_check_constraints
      15. incremental_vacuum
      16. index_info
      17. index_list
      18. integrity_check
      19. journal_mode
      20. journal_size_limit
      21. legacy_file_format
      22. locking_mode
      23. lock_proxy_file
      24. lock_status
      25. max_page_count
      26. omit_readlock
      27. page_count
      28. page_size
      29. parser_trace
      30. quick_check
      31. read_uncommitted
      32. recursive_triggers
      33. reverse_unordered_selects
      34. schema_version
      35. secure_delete
      36. short_column_names
      37. sql_trace
      38. synchronous
      39. table_info
      40. temp_store
      41. temp_store_directory
      42. user_version
      43. vdbe_trace
      44. vdbe_listing
      45. writable_schema
  21. G. SQLite C API Reference
    1. API Datatypes
      1. sqlite3
      2. sqlite3_backup
      3. sqlite3_blob
      4. sqlite3_context
      5. sqlite3_int64, sqlite3_uint64, sqlite_int64, sqlite_uint64
      6. sqlite3_module
      7. sqlite3_mutex
      8. sqlite3_stmt
      9. sqlite3_value
      10. sqlite3_vfs
    2. API Functions
      1. sqlite3_aggregate_context()
      2. sqlite3_auto_extension()
      3. sqlite3_backup_finish()
      4. sqlite3_backup_init()
      5. sqlite3_backup_pagecount()
      6. sqlite3_backup_remaining()
      7. sqlite3_backup_step()
      8. sqlite3_bind_xxx()
      9. sqlite3_bind_parameter_count()
      10. sqlite3_bind_parameter_index()
      11. sqlite3_bind_parameter_name()
      12. sqlite3_blob_bytes()
      13. sqlite3_blob_close()
      14. sqlite3_blob_open()
      15. sqlite3_blob_read()
      16. sqlite3_blob_write()
      17. sqlite3_busy_handler()
      18. sqlite3_busy_timeout()
      19. sqlite3_changes()
      20. sqlite3_clear_bindings()
      21. sqlite3_close()
      22. sqlite3_collation_needed()
      23. sqlite3_column_xxx()
      24. sqlite3_column_bytes()
      25. sqlite3_column_count()
      26. sqlite3_column_database_name()
      27. sqlite3_column_decltype()
      28. sqlite3_column_name()
      29. sqlite3_column_origin_name()
      30. sqlite3_column_table_name()
      31. sqlite3_column_type()
      32. sqlite3_commit_hook()
      33. sqlite3_compileoption_get()
      34. sqlite3_compileoption_used()
      35. sqlite3_complete()
      36. sqlite3_config()
      37. sqlite3_context_db_handle()
      38. sqlite3_create_collation()
      39. sqlite3_create_function()
      40. sqlite3_create_module()
      41. sqlite3_data_count()
      42. sqlite3_db_config()
      43. sqlite3_db_handle()
      44. sqlite3_db_mutex()
      45. sqlite3_db_status()
      46. sqlite3_declare_vtab()
      47. sqlite3_enable_load_extension()
      48. sqlite3_enable_shared_cache()
      49. sqlite3_errcode()
      50. sqlite3_errmsg()
      51. sqlite3_exec()
      52. sqlite3_extended_errcode()
      53. sqlite3_extended_result_codes()
      54. sqlite3_file_control()
      55. sqlite3_finalize()
      56. sqlite3_free()
      57. sqlite3_free_table()
      58. sqlite3_get_autocommit()
      59. sqlite3_get_auxdata()
      60. sqlite3_get_table()
      61. sqlite3_initialize()
      62. sqlite3_interrupt()
      63. sqlite3_last_insert_rowid()
      64. sqlite3_libversion()
      65. sqlite3_libversion_number()
      66. sqlite3_limit()
      67. sqlite3_load_extension()
      68. sqlite3_log()
      69. sqlite3_malloc()
      70. sqlite3_memory_highwater()
      71. sqlite3_memory_used()
      72. sqlite3_mprintf()
      73. sqlite3_mutex_alloc()
      74. sqlite3_mutex_enter()
      75. sqlite3_mutex_free()
      76. sqlite3_mutex_held()
      77. sqlite3_mutex_leave()
      78. sqlite3_mutex_notheld()
      79. sqlite3_mutex_try()
      80. sqlite3_next_stmt()
      81. sqlite3_open()
      82. sqlite3_open_v2()
      83. sqlite3_overload_function()
      84. sqlite3_prepare_xxx()
      85. sqlite3_profile()
      86. sqlite3_progress_handler()
      87. sqlite3_randomness()
      88. sqlite3_realloc()
      89. sqlite3_release_memory()
      90. sqlite3_reset()
      91. sqlite3_reset_auto_extension()
      92. sqlite3_result_xxx()
      93. sqlite3_result_error_xxx()
      94. sqlite3_rollback_hook()
      95. sqlite3_set_authorizer()
      96. sqlite3_set_auxdata()
      97. sqlite3_shutdown()
      98. sqlite3_sleep()
      99. sqlite3_snprintf()
      100. sqlite3_soft_heap_limit()
      101. sqlite3_sourceid()
      102. sqlite3_sql()
      103. sqlite3_status()
      104. sqlite3_step()
      105. sqlite3_stmt_status()
      106. sqlite3_strnicmp()
      107. sqlite3_table_column_metadata()
      108. sqlite3_threadsafe()
      109. sqlite3_total_changes()
      110. sqlite3_trace()
      111. sqlite3_unlock_notify()
      112. sqlite3_update_hook()
      113. sqlite3_user_data()
      114. sqlite3_value_xxx()
      115. sqlite3_value_bytes()
      116. sqlite3_value_numeric_type()
      117. sqlite3_value_type()
      118. sqlite3_version[]
      119. sqlite3_vfs_find()
      120. sqlite3_vfs_register()
      121. sqlite3_vfs_unregister()
      122. sqlite3_vmprintf()
  22. Index
  23. About the Author
  24. Colophon
  25. Copyright
O'Reilly logo

Data Manipulation Language

The Data Manipulation Language is all about getting user data in and out of the database. After all the data structures and other database objects have been created with DDL commands, DML commands can be used to load those data structures full of useful data.

The DML supported by SQLite falls into two basic categories. The first category consists of the “update” commands, which includes the actual UPDATE command, as well as the INSERT and DELETE commands. As you might guess, these commands are used to update (or modify), insert, and delete the rows of a table. All of these commands alter the stored data in some way. The update commands are the primary means of managing all the data within a database.

The second category consists of the “query” commands, which are used to extract data from the database. Actually, there is only one query command: SELECT. The SELECT command not only prints returned values, but provides a great number of options to combine different tables and rows and otherwise manipulate data before returning the final result.

SELECT is, unquestionably, the most complex SQL command. It is also, arguably, the most important SQL command. This chapter will only cover the very basics of SELECT, and then we will spend the next chapter going through all of its parts, bit by bit. To address the full command syntax in detail, SELECT gets a whole chapter to itself (Chapter 5).

Row Modification Commands

There are three commands used for adding, modifying, and removing data from the database. INSERT adds new rows, UPDATE modifies existing rows, and DELETE removes rows. These three commands are used to maintain all of the actual data values within the database. All three update commands operate at a row level, adding, altering, or removing the specified rows. Although all three commands are capable of acting on multiple rows, each command can only directly act upon rows contained within a single table.

INSERT

The INSERT command is used to create new rows in the specified table. There are two meaningful versions of the command. The first version uses a VALUES clause to specify a list of values to insert:

INSERT INTO table_name (column_name [, ...]) VALUES (new_value [, ...]);

A table name is provided, along with a list of columns and a list of values. Both lists must have the same number of items. A single new row is created and each value is recorded into its respective column. The columns can be listed in any order, just as long as the list of columns and the list of values line up correctly. Any columns that are not listed will receive their default values:

INSERT INTO parts ( name, stock, status ) VALUES ( 'Widget', 17, 'IN STOCK' );

In this example, we attempt to insert a new row into a “parts” table. Note the use of single quotes for text literals.

Technically, the list of column names is optional. If no explicit list of columns is provided, the INSERT command will attempt to pair up values with the table’s full list of columns:

INSERT INTO table_name VALUES (new_value [, ...]);

The trick with this format is that the number and order of values must exactly match the number and order of columns in the table definition. That means it is impossible to use default values, even on INTEGER PRIMARY KEY columns. More often than not, this is not actually desirable. This format is also harder to maintain within application source code, since it must be meticulously updated if the table format changes. In general, it is recommended that you always explicitly list out the columns in an INSERT statement.

When bulk importing data, it is common to loop over data sets, calling INSERT over and over. Processing these statements one at a time can be fairly slow, since each command will update both the table and any relevant indexes, and then make sure the data is fully written out to physical disk before (finally!) starting the next INSERT. This is a fairly lengthly process, since it requires physical I/O.

To speed up bulk inserts, it is common to wrap groups of 1,000 to 10,000 INSERT statements into a single transaction. Grouping the statement together will substantially increase the overall speed of the inserts by delaying the physical I/O until the end of the transaction. See Transaction Control Language for more information on transactions.

Note

Bulk inserts can be sped up by wrapping large groups of INSERT commands inside a transaction.

The second version of INSERT allows you to define values by using a query statement. This is very similar to the CREATE TABLE...AS SELECT command, although the table must already exist. This is the only version of INSERT that can insert more than one row with a single command:

INSERT INTO table_name (column_name, [...]) SELECT query_statement;

This type of INSERT is most commonly used to bulk copy data from one table to another. This is a common operation when you need to update the definition of a table, but you don’t want to lose all the data that already exists in the database. The old table is renamed, the new table is defined, and the data is copied from the old table into the new table using an INSERT INTO...SELECT command. This form can also be used to populate temporary tables or copy data from one attached database to another.

As with the VALUES version of INSERT, the column list is technically optional but, for all the same reasons, it is still recommended that you provide an explicit column list.

All versions of the INSERT command also support an optional conflict resolution clause. This conflict clause determines what should be done if the results of the INSERT would violate a database constraint. The most common example is INSERT OR REPLACE, which comes into play when the INSERT would, as executed, cause a UNIQUE constraint violation. If the REPLACE conflict resolution is present, any existing row that would cause a UNIQUE constraint violation is first deleted, and then the INSERT is allowed to continue. This specific usage pattern is so common that the whole INSERT OR REPLACE phrase can be replaced by just REPLACE. For example, REPLACE INTO table_name....

See INSERT and UPDATE in Appendix C for more information on the details of conflict resolution.

UPDATE

The UPDATE command is used to assign new values to one or more columns of existing rows in a table. The command can update more than one row, but all of the rows must be part of the same table. The basic syntax is:

UPDATE table_name SET column_name=new_value [, ...] WHERE expression

The command requires a table name followed by a list of column name/value pairs that should be assigned. Which rows are updated is determined by a conditional expression that is tested against each row of the table. The most common usage pattern uses the expression to check for equality on some unique column, such as a PRIMARY KEY column.

Caution

If no WHERE condition is given, the UPDATE command will attempt to update the designated columns in every row of a table.

It is not considered an error if the WHERE expression evaluates to false for every row in the table, resulting in no actual updates.

Here is a more specific example:

-- Update the price and stock of part_id 454:
UPDATE parts SET price = 4.25, stock = 75 WHERE part_id = 454;

This example assumes that the table parts has at least three columns: price, stock, and part_id. The database will find each row with a part_id of 454. In this case, it can be assumed that part_id is a PRIMARY KEY column, so only one row will be updated. The price and stock columns of that row are then assigned new values.

The full syntax for UPDATE can be found at UPDATE in Appendix C.

DELETE

As you might guess, the DELETE command is used to delete or remove one or more rows from a single table. The rows are completely deleted from the table:

DELETE FROM table_name WHERE expression;

The command requires only a table name and a conditional expression to pick out rows. The WHERE expression is used to select specific rows to delete, just as it is used in the UPDATE command.

Caution

If no WHERE condition is given, the DELETE command will attempt to delete every row of a table.

As with UPDATE, it is not considered an error if the WHERE expression evaluates to false for every row in the table, resulting in no actual deletions.

Some specific examples:

-- Delete the row with rowid 385:
DELETE FROM parts WHERE part_id = 385;

-- Delete all rows with a rowid greater than or equal to 43
-- and less than or equal to 246:
DELETE FROM parts WHERE part_id >= 43 AND part_id <= 246;

These examples assume we have a table named parts that contains at least one unique column named part_id.

As noted, if no WHERE clause is given, the DELETE command will attempt to delete every row in a table. SQLite optimizes this specific case, truncating the full table, rather than processing each individual row. Truncating the table is much faster than deleting each individual row, but truncation bypasses the individual row processing. If you wish to process each row as it is deleted, provide a WHERE clause that always evaluates to true:

DELETE FROM parts WHERE 1; -- delete all rows, force per-row processing

The existence of the WHERE clause will prevent the truncation, allowing each row to be processed in turn.

The Query Command

The final DML command to cover is the SELECT command. SELECT is used to extract or return values from the database. Almost any time you want to extract or return some kind of value, you’ll need to use the SELECT command. Generally, the returned values are derived from the contents of the database, but SELECT can also be used to return the value of simple expressions. This is a great way to test out expressions, for example:

sqlite> SELECT 1+1, 5*32, 'abc'||'def', 1>2;
1+1         5*32        'abc' || 'def'  1>2       
----------  ----------  --------------  ----------
2           160         abcdef          0         

SELECT is a read-only command, and will not modify the database (unless the SELECT is embedded in a different command, such as a CREATE TABLE...AS SELECT or an INSERT INTO...SELECT).

Without question, SELECT is the most complex SQL command, both in terms of syntax as well as function. The SELECT syntax tries to represent a generic framework that is capable of expressing a great many different types of queries. While it is somewhat successful at this, there are areas where SELECT has traded away simplicity for more flexibility. As a result, SELECT has a large number of optional clauses, each with its own set of options and formats.

Understanding how to mix and match these optional clauses to get the result you’re looking for can take some time. While the most basic syntax can be shown with a good set of examples, to really wrap your head around SELECT, it is best to understand how it actually works and what it is trying to accomplish.

Because SELECT can be so complex, and because SELECT is an extremely important command, we will spend the whole next chapter looking very closely at SELECT and each of its clauses. There will be some discussion about what is going on behind the scenes, to provide more insight into how to read and write complex queries.

For now, we’ll just give you a taste. That should provide enough information to play around with the other commands in this chapter. The most basic form of SELECT is:

SELECT output_list FROM input_table WHERE row_filter;

The output list is a list of expressions that should be evaluated and returned for each resulting row. Most commonly, this is simply a list of columns. The output list can also include a wildcard (*) that indicates all known columns should be returned.

The FROM clause defines the source of the table data. The next chapter will show how tables can be linked and joined, but for now we’ll stick with querying one table at a time.

The WHERE clause is a conditional filtering expression that is applied to each row. It is essentially the same as the WHERE clause in the UPDATE and DELETE commands. Those rows that evaluate to true will be part of the result, while the other rows will be filtered out.

Consider this table:

sqlite> CREATE TABLE tbl ( a, b, c, id INTEGER PRIMARY KEY );
sqlite> INSERT INTO tbl ( a, b, c ) VALUES ( 10, 10, 10 );
sqlite> INSERT INTO tbl ( a, b, c ) VALUES ( 11, 15, 20 );
sqlite> INSERT INTO tbl ( a, b, c ) VALUES ( 12, 20, 30 );

We can return the whole table like this:

sqlite> SELECT * FROM tbl;
a           b           c           id         
----------  ----------  ----------  ----------
10          10          10          1         
11          15          20          2         
12          20          30          3         

We can also just return specific columns:

sqlite> SELECT a, c FROM tbl;
a           c         
----------  ----------
10          10        
11          20        
12          30        

Or specific rows:

sqlite> SELECT * FROM tbl WHERE id = 2;
a           b           c           id        
----------  ----------  ----------  ----------
11          15          20          2         

For more specifics, see Chapter 5 and SELECT in Appendix C.

The best content for your career. Discover unlimited learning on demand for around $1/day.