SQLite Tutorial for Beginners

Resource Provider : Muhammad Rizwan (Visiting Lecturer) IUB-RYK

SQLite Tutorial for Beginners SQLite is a RDBMS (Relational Database Management System) similar to MySQL or Oracle. It has all the features of relational database engine including normalization. It’s lightweight and one of the most often used database engine across the globe, particularly with mobile applications. Why Use SQLite? SQLite is a database engine designed with an objective to keep database design and management as simple as possible. SQLite is easier to administer administer with no overhead, overhead, easy to operate operate and embed in an an application, application, simpler to maintain, and extremely efficient in terms of resource usage and customization. customization. The following are some of the distinguishing attributes attributes of SQLite Database. Features of SQLite 1. Zero configuration – SQLite does not need to be Installed as there is no setup procedure to use it. 2. Serverless – SQLite is not implemented as a separate server process. With SQLite, SQLite, the process process that wants wants to access the the database database reads and writes directly directly from the database files files on disk as there there is no intermediary intermediary server process. 3. Stable Cross-Platform Database File – The SQLite file format is cross-platform. A database file written on one machine can be copied to and used on a different machine with a different architecture. 4. Single Database File – An SQLite database is a single ordinary disk file that can be located anywhere in the directory hierarchy. 5. Compact – When optimized for size, the whole SQLite library with everything enabled is less than 400KB in size

When to Use Use SQLite? SQLite? Due to the small size of SQLite code structure that makes it memory efficient, it’s widely used in mobile application development o wing o wing to the limitations limitations of memory and storage on hand held devices. It is also preferable to use SQLite in small to medium size websites since there is no need for complex

1


configuration and maintenance, which also makes SQLite a replacement for an enterprise database engine for testing and early launch of an application. SQLite Commands SQLite commands interact with the relational database in a way similar to SQL. There are three groups of these commands based on the category of operations they perform. These are Data Definition Language (CREATE, ALTER and DROP), Data Manipulation Language (INSERT, UPDATE and DELETE) and Data Query Language (SELECT). SQLite Data Types Unlike SQL, SQLite uses a dynamic type system. In SQL, the data type of a value is determined by its column data type in a table. In contrast, the data type of value is related to the value itself. Every value stored in a SQLite database is associated with one of five storage classes. 









‘NULL’ value. ‘INTEGER’ is a signed 64 bit numeric value. SQLite optimizes the storage of small integers by itself, which is why it is stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the integer value. ‘REAL’ is a 64 bit floating point value. ‘TEXT’ is a text string, which is stored using database encoding such as UTF-8. ‘BLOB’ is a blob of data, which is stored as direct input. Both ‘Text’ and ‘BLOB’ have default size of 1,000,000,000 bytes.

SQLite uses the concept of ‘type affinity’ on columns of a table that reduces the compatibility issues with other database engines. Any column can store any type of data, but the recommended storage class for a column is called its affinity. These type affinities are ‘TEXT’, ‘NUMERIC’, ‘INTEGER’, ‘REAL’ and ‘NONE’. The following is the details of the data type s that are used to create the database table and their affinities. 





‘TEXT’ affinity applies on VARCHAR, NCHAR, NVARCHAR, and TEXT, etc. ‘NUMERIC’ applies on BOOLEAN, DATE and DATETIME, etc. ‘INTEGER’ applies on INT, TINYINT, SMALLINT, MEDIUMINT and BIGINT, etc. 2






‘REAL’ applies on REAL, DOUBLE and FLOAT, etc. ‘NONE’ applies on BLOB and column with no specified data type.

From the above list of affinities, it is clear that there is no specified data type available for date-time in SQLite, but by default, these values are saved as numeric data (number of seconds since the Jan 01, 1970 midnight). Sometimes, a text string is also used to save the date-time. Same goes for a Boolean where true and false are stored as 1 and 0 respectively in a column. SQLite CRUD Statement SQLite statements start with keywords such as CREATE, INSERT, UPDATE, DELETE, SELECT, ALTER, DROP, etc. and is case insensitive. CRUD (Create, Read, Update and Delete) are commonly used operations in every database engine. The ‘CREATE’ statement is used to create new tables in SQLite database. Basic syntax and a simple example of ‘CREATE’ ‘TABLE’ are as follow. Create Table Statement Syntax: CREATE TABLE DATABASE_NAME. TABLE_NAME ( column1 datatype, column2 datatype, … columnN datatype, PRIMARY KEY (one or more columns) ); Create EMPLOYEE Table: sqlite3> CREATE TABLE COMPANY.EMPLOYEE ( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL,

3


AGE INT NOT NULL, ); The ‘INSERT INTO’ statement inserts new rows in a table and ‘UPDATE’ is used to modify the existing rows based on a certain criteria (‘WHERE’ clause). Following is the SQLite syntax and examples for these statements. Insert Into Statement Syntax: INSERT INTO TABLE_NAME (column1, column2… columnN) VALUES (value1, value2… valueN); Update Statement Syntax: UPDATE TABLE_NAME SET column1=value1, column2=value2 … columnN=valueN [where criteria_To_Update_Row]; Insert 1 row in EMPLOYEE Table: INSERT INTO EMPLOYEE (ID, NAME, AGE) VALUES (1, ‘John’, 22); Update 1 row in EMPLOYEE Table: UPDATE EMPLOYEE SET AGE = 21 WHERE ID = 1; The ‘SELECT’ statement retrieves the specific data of specific rows in table. Finally ‘DELETE’ removes the particular ro ws based on criteria or all the rows in table. Following example demonstrates the syntax of these statements in SQLite. Select Statement Syntax: SELECT column1, column2, columnN FROM TABLE_NAME [where criteria_To_Retrieve_Row]; Delete Statement Syntax: DELETE FROM TABLE_NAME [where criteria_To_Delete_Row]; 4


Select All EMPLOYEE’s Data with AGE greater than 25: SELECT * FROM EMPLOYEE WHERE AGE > 25; Delete All EMPLOYEE’s Data: DELETE FROM EMPLOYE;

5

SQLite Tutorial for Beginners

Recommend Documents