SQLite Quick Guide

Advertisements


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 the one database, which is zero-configured, that means like other database 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. The SQLite accesses its storage files directly.

Why SQLite?

  • SQLite does not require a separate server process or system to operate(erverless).

  • 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 the 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).

History:

  1. 2000 -- D. Richard Hipp had designed SQLite for the purpose of no administration required for operating a program.

  2. 2000 -- In August, SQLite 1.0 releAsed with GNU Database Manager.

  3. 2011 -- Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database).

SQLite - Installation

The 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

  • Go to SQLite download page, and download precompiled binaries from Windows section.

  • You will need to download sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files.

  • 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.

  • Add C:\>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command which should display a result something as below.

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 flavours 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 or not.

$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 above result, then it means you do not have SQLite installed on your Linux machine. So let's follow the following steps to install SQLite:

  • Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section.

  • Follow the following steps:

$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local
$make
$make install

Above procedure will end with SQLite installation on your Linux machine which you can verify as explained above.

Install SQLite On Mac OS X

Though latest version of Mac OS X comes pre-installed with SQLite but if you do not have installation available then just follow the following steps.

  • Go to SQLite download page, and download sqlite-autoconf-*.tar.gz from source code section.

  • Follow the following steps:

$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local
$make
$make install

Above procedure will end with SQLite installation on your Mac OS X machine which you can verify by issuing 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 to do your exercises.

SQLite - Commands

Let's start with typing a simple sqlite3 command at command prompt which will provide you 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" at any time. For example:

sqlite>.help

Above command will display a list of various important SQLite dot commands, which are as follows:

CommandDescription
.backup ?DB? FILEBackup DB (default "main") to FILE
.bail ON|OFFStop after hitting an error. Default OFF
.databasesList 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|OFFTurn command echo on or off
.exitExit SQLite prompt
.explain ON|OFFTurn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on.
.header(s) ON|OFFTurn display of headers on or off
.helpShow this message
.import FILE TABLEImport 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|offTurn logging on or off. FILE can be stderr/stdout
.mode MODESet 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 STRINGPrint STRING in place of NULL values
.output FILENAMESend output to FILENAME
.output stdout Send output to the screen
.print STRING...Print literal STRING
.prompt MAIN CONTINUEReplace the standard prompts
.quitExit SQLite prompt
.read FILENAMEExecute SQL in FILENAME
.schema ?TABLE?Show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE.
.separator STRINGChange separator used by output mode and .import
.showShow the current values for various settings
.stats ON|OFFTurn stats on or off
.tables ?PATTERN?List names of tables matching a LIKE pattern
.timeout MSTry opening locked tables for MS milliseconds
.width NUM NUMSet column widths for "column" mode
.timer ON|OFF Turn the CPU timer measurement on or off

SQLite - Syntax

SQLite is followed by unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQLite by listing all the basic SQLite Syntax:

Case Sensitivity

Important point to be noted is that SQLite is case insensitive but their is some command which is 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 can not 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 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,
   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:

DELETE FROM table_name
WHERE  {CONDITION};

SQLite DETACH DATABASE Statement:

DETACH DATABASE 'Alias-Name';

SQLite DISTINCT Clause:

SELECT DISTINCT column1, column2....columnN
FROM   table_name;

SQLite DROP INDEX Statement :

DROP INDEX database_name.index_name;

SQLite DROP TABLE Statement:

DROP TABLE database_name.table_name;

SQLite DROP VIEW Statement :

DROP INDEX database_name.view_name;

SQLite DROP TRIGGER Statement :

DROP INDEX database_name.trigger_name;

SQLite EXISTS Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name EXISTS (SELECT * FROM   table_name );

SQLite EXPLAIN Statement :

EXPLAIN INSERT statement...;
or 
EXPLAIN QUERY PLAN SELECT statement...;

SQLite GLOB Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name GLOB { PATTERN };

SQLite GROUP BY Clause:

SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name;

SQLite HAVING Clause:

SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

SQLite INSERT INTO Statement:

INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

SQLite IN Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name IN (val-1, val-2,...val-N);

SQLite Like Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name LIKE { PATTERN };

SQLite NOT IN Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name NOT IN (val-1, val-2,...val-N);

SQLite ORDER BY Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION
ORDER BY column_name {ASC|DESC};

SQLite PRAGMA Statement:

PRAGMA pragma_name;

For example:

PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);

SQLite RELEASE SAVEPOINT Statement:

RELEASE savepoint_name;

SQLite REINDEX Statement:

REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;

SQLite ROLLBACK Statement:

ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;

SQLite SAVEPOINT Statement:

SAVEPOINT savepoint_name;

SQLite SELECT Statement:

SELECT column1, column2....columnN
FROM   table_name;

SQLite UPDATE Statement:

UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE  CONDITION ];

SQLite VACUUM Statement:

VACUUM;

SQLite WHERE Clause:

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION;

SQLite - Data Type

SQLite data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQLite.

You would use these data types while creating your tables. SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

SQLite Storage Classes:

Each value stored in an SQLite database has one of the following storage classes:

Storage ClassDescription
NULLThe value is a NULL value.
INTEGERThe value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REALThe value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXTThe value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)
BLOBThe value is a blob of data, stored exactly as it was input.

SQLite storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths.

SQLite Affinity Type:

SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Each table column in an SQLite3 database is assigned one of the following type affinities:

AffinityDescription
TEXTThis column stores all data using storage classes NULL, TEXT or BLOB.
NUMERICThis column may contain values using all five storage classes.
INTEGERBehaves the same as a column with NUMERIC affinity with an exception in a CAST expression.
REALBehaves like a column with NUMERIC affinity except that it forces integer values into floating point representation
NONEA column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.

SQLite Affinity and Type Names:

Following table lists down various data type names which can be used while creating SQLite3 tables, and corresponding applied affinity also has been shown:

Data TypeAffinity
  • INT

  • INTEGER

  • TINYINT

  • SMALLINT

  • MEDIUMINT

  • BIGINT

  • UNSIGNED BIG INT

  • INT2

  • INT8

INTEGER
  • CHARACTER(20)

  • VARCHAR(255)

  • VARYING CHARACTER(255)

  • NCHAR(55)

  • NATIVE CHARACTER(70)

  • NVARCHAR(100)

  • TEXT

  • CLOB

TEXT
  • BLOB

  • no datatype specified

NONE
  • REAL

  • DOUBLE

  • DOUBLE PRECISION

  • FLOAT

REAL
  • NUMERIC

  • DECIMAL(10,5)

  • BOOLEAN

  • DATE

  • DATETIME

NUMERIC

Boolean Datatype:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

Date and Time Datatype:

SQLite does not have a separate storage class for storing dates and/or times but SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

Storage ClassDate Formate
TEXTA date in a format like "YYYY-MM-DD HH:MM:SS.SSS".
REALThe number of days since noon in Greenwich on November 24, 4714 B.C.
INTEGERThe number of seconds since 1970-01-01 00:00:00 UTC.

SQLite - CREATE Database

The SQLite sqlite3 command is used to create new SQLite database. You do not need to have any sepcial privilege to create a database.

Syntax:

Basic syntax of sqlite3 command is as follows:

$sqlite3 DatabaseName.db

Always database name should be unique within the RDBMS.

SQLite - ATTACH Database

Consider a case when you have multiple databases available and you want to use any one of them at a time. SQLite ATTACH DTABASE statement is used to select a particular database and after this command, all SQLite statements will be executed under the attached database.

Syntax:

Basic syntax of SQLite ATTACH DATABASE statement is as follows:

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

Above command will also create a database in case database is already not created, otherwise it will just attach database file name with logical database 'Alias-Name'.

SQLite - DETACH Database

SQLite DETACH DTABASE statement is used to detach and dissociates a named database from a database connection which was previously attached using ATTACH statement. If the same database file has been attached with multiple aliases, then DETACH command will disconnect only given name and rest of the attachement will still continue. You cannot detach the main or temp databases.

If the database is an in-memory or temporary database, the database will be destroyed and the contents will be lost.

Syntax:

Basic syntax of SQLite DETACH DATABASE 'Alias-Name' statement is as follows:

DETACH DATABASE 'Alias-Name';

Here 'Alias-Name' is the same alias which you had used while attaching database using ATTACH statement.

SQLite - CREATE Table

The SQLite CREATE TABLE statement is used to create a new table in any of the given database. Creating a basic table involves naming the table and defining its columns and each column's data type.

Syntax:

Basic syntax of CREATE TABLE statement is as follows:

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

CREATE TABLE is the keyword telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Optionally you can specify database_name alongwith table_name.

SQLite - DROP Table

The SQLite DROP TABLE statement is used to remove a table definition and all associated data, indexes, triggers, constraints, and permission specifications for that table.

You have to be careful while using this command because once a table is deleted then all the information available in the table would also be lost forever.

Syntax:

Basic syntax of DROP TABLE statement is as follows. You can optionally specify database name along with table name as follows:

DROP TABLE database_name.table_name;

SQLite - INSERT Query

The SQLite INSERT INTO Statement is used to add new rows of data into a table in the database.

Syntax:

There are two basic syntax of INSERT INTO statement is as follows:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

Here column1, column2,...columnN are the names of the columns in the table into which you want to insert data.

You may not need to specify the column(s) name in the SQLite query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQLite INSERT INTO syntax would be as follows:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

SQLite - SELECT Query

SQLite SELECT statement is used to fetch the data from a SQLite database table which returns data in the form of result table. These result tables are also called result-sets.

Syntax:

The basic syntax of SQLite SELECT statement is as follows:

SELECT column1, column2, columnN FROM table_name;

Here column1, column2...are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field then you can use following syntax:

SELECT * FROM table_name;

SQLite - Operators

An operator is a reserved word or a character used primarily in an SQLite statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

Operators are used to specify conditions in an SQLite statement and to serve as conjunctions for multiple conditions in a statement.

  • Arithmetic operators

  • Comparison operators

  • Logical operators

  • Bitwsie operators

SQLite Arithmetic Operators:

Assume variable a holds 10 and variable b holds 20 then:

Show Examples

OperatorDescriptionExample
+Addition - Adds values on either side of the operator a + b will give 30
-Subtraction - Subtracts right hand operand from left hand operand a - b will give -10
*Multiplication - Multiplies values on either side of the operator a * b will give 200
/Division - Divides left hand operand by right hand operand b / a will give 2
%Modulus - Divides left hand operand by right hand operand and returns remainder b % a will give 0

SQLite Comparison Operators:

Assume variable a holds 10 and variable b holds 20 then:

Show Examples

OperatorDescriptionExample
== Checks if the value of two operands are equal or not, if yes then condition becomes true. (a == b) is not true.
= Checks if the value of two operands are equal or not, if yes then condition becomes true. (a = b) is not true.
!= Checks if the value of two operands are equal or not, if values are not equal then condition becomes true. (a != b) is true.
<>Checks if the value of two operands are equal or not, if values are not equal then condition becomes true. (a <> b) is true.
> Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. (a > b) is not true.
< Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. (a < b) is true.
>= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. (a >= b) is not true.
<= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. (a <= b) is true.
!< Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true. (a !< b) is false.
!> Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true. (a !> b) is true.

SQLite Logical Operators:

Here is a list of all the logical operators available in SQLite.

Show Examples

OperatorDescription
ANDThe AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
BETWEENThe BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTSThe EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
INThe IN operator is used to compare a value to a list of literal values that have been specified.
NOT INThe negation of IN operator which is used to compare a value to a list of literal values that have been specified.
LIKEThe LIKE operator is used to compare a value to similar values using wildcard operators.
GLOBThe GLOB operator is used to compare a value to similar values using wildcard operators. Also, GLOB is case sensitive, unlike LIKE.
NOTThe NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negate operator.
ORThe OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
IS NULLThe NULL operator is used to compare a value with a NULL value.
ISThe IS operator work like =
IS NOTThe IS operator work like !=
|| Adds two different string and make new one.
UNIQUEThe UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

SQLite Bitwise Operators:

Bitwise operator works on bits and perform bit by bit operation. The truth table for & and | is as follows:

pqp & qp | q
0000
0101
1111
1001

Assume if A = 60; and B = 13; Now in binary format they will be as follows:

A = 0011 1100

B = 0000 1101

-----------------

A&B = 0000 1100

A|B = 0011 1101

~A  = 1100 0011

The Bitwise operators supported by SQLite language are listed in the following table. Assume variable A holds 60 and variable B holds 13 then:

Show Examples

OperatorDescriptionExample
& Binary AND Operator copies a bit to the result if it exists in both operands. (A & B) will give 12 which is 0000 1100
| Binary OR Operator copies a bit if it exists in either operand. (A | B) will give 61 which is 0011 1101
~ Binary Ones Complement Operator is unary and has the effect of 'flipping' bits. (~A ) will give -61 which is 1100 0011 in 2's complement form due to a signed binary number.
<< Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand. A << 2 will give 240 which is 1111 0000
>> Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand. A >> 2 will give 15 which is 0000 1111

SQLite - Expressions

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value.

SQL EXPRESSIONs are like formulas and they are written in query language. You can also use to query the database for specific set of data.

Syntax:

Consider the basic syntax of the SELECT statement as follows:

SELECT column1, column2, columnN 
FROM table_name 
WHERE [CONTION | EXPRESSION];

There are different types of SQLite expression, which are mentioned below:

SQLite - Boolean Expressions:

SQLite Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax:

SELECT column1, column2, columnN 
FROM table_name 
WHERE SINGLE VALUE MATCHTING EXPRESSION;

SQLite - Numeric Expression:

These expressions are used to perform any mathematical operation in any query. Following is the syntax:

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

Here numerical_expression is used for mathematical expression or any formula. Following is a simple examples showing usage of SQL Numeric Expressions:

sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21

There are several built-in functions like avg(), sum(), count() etc to perform what is known as aggregate data calculations against a table or a specific table column.

sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; 
RECORDS = 7

SQLite - Date Expressions:

Date Expressions return current system date and time values and these expressions will be used in various data manipulation.

sqlite>  SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP = 2013-03-17 10:43:35

SQLite - WHERE Clause

The SQLite WHERE clause is used to specify a condition while fetching the data from one table or multiple tables.

If the given condition is satisfied, means true then it returns specific value from the table. You would use WHERE clause to filter the records and fetching only necessary records.

The WHERE clause not only used in SELECT statement, but it is also used in UPDATE, DELETE statement etc. which we would study in subsequent chapters.

Syntax:

The basic syntax of SQLite SELECT statement with WHERE clause is as follows:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

SQLite - AND and OR Operators

The AND Operator:

The AND operator allows the existence of multiple conditions in an SQLite statement's WHERE clause. While using AND operator, complete condition will be assumed true when all the conditions are true. For example [condition1] AND [condition2] will be true only when both condition1 and condition2 are true.

Syntax:

The basic syntax of AND operator with WHERE clause is as follows:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

You can combine N number of conditions using AND operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE.

The OR Operator:

The OR operator is also used to combine multiple conditions in an SQLite statement's WHERE clause. While using OR operator, complete condition will be assumed true when atleast any of the the conditions is true. For example [condition1] OR [condition2] will be true if either condition1 or condition2 is true.

Syntax:

The basic syntax of OR operator with WHERE clause is as follows:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

You can combine N number of conditions using OR operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, only any ONE of the conditions separated by the OR must be TRUE.

SQLite - UPDATE Query

The SQLite UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows otherwise all the rows would be updated.

Syntax:

The basic syntax of UPDATE query with WHERE clause is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

You can combine N number of conditions using AND or OR operators.

SQLite - DELETE Query

The SQLite DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.

Syntax:

The basic syntax of DELETE query with WHERE clause is as follows:

DELETE FROM table_name
WHERE [condition];

You can combine N number of conditions using AND or OR operators.

Conclusion

If you enjoyed this short and quick tutorial and interested in reading more then I will recommend you to go through the detailed tutorial because still you are missing many important concepts related to SQLite.



Advertisements
Advertisements