Data Type And Commands In SQLite

Data Type in SQLite: It basically specify the kind of data that object will store in it i.e the value it can take inside for storage. There are different type or data like numeric, alphabetic, alphanumeric etc. So to store all this type of data different data types are there. Also the data types defines the value to be stored or the operations that can be carried out over the data.

SQLite database is dynamic type i.e the type of value to be stored in the container is decided by the value itself. In other words SQLite uses static and rigid typing i.e allows us to define data as per value.

Let’s take example to understand the point more clear:

create table demo_table( Number int(15), Name varchar(50) );
insert into demo_table (Number , Name) values ('EA', '102');

Here in the above example we define datatype of Number as Integer and Name as Varchar. In second line we inserted character value in Number and Number in Name using insert command. This query will run without any error because SQLite engine allows rigid typing usually try to automatically convert values to the appropriate datatype.

Storage Classes

Null : The value is a NULL value.
Integer : The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
Real : The value is a floating point value, stored as an 8-byte IEEE floating point number.
Text : The value is a text string, stored using the database encoding (UTF- 8, UTF-16BE or UTF-16LE)
Blob : The value is a blob of data, stored exactly as it was input.

We have used above classes in commands of this article.

SQLite Affinity Type:

In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of “type affinity” on columns. Type affinity is the recommended type for data stored in that column. Any column can store any type of data. The preferred storage class for a column is called its “affinity”.

Each column has assigned following type affinities:

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

Affinity Type Names:

SQLite Commands

The SQLite commands are similar to that of SQL. SQLite works as Relational Database Management System. Like in other relational database the request is send from client to the database server, further the request is processed. But here there’s nothing like that as it as self-contained and serverless.

SQLite Commands are categorized as

There are 3 main divisions in this, but before this let’s know about how to create a database in SQLite.

Create Database: Database is a container which contain tables , views etc.

.open : User can just define database name with .open command it will open the existing database or create a new if database does not exist.

SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open new_Database               

1) DDL Commands :
DDL stands for Data Definition Language. This is used to define the structure of the database. It include commands like create, alter and drop.
CREATE : This command is used to create a table, index, trigger, view or other database objects. etc.
DROP : This is basically to drop the structure of the table, view etc. But user can not drop a column in SQLite.
ALTER : This command allow us to alter or modify the existing structure like rename table, add column etc but cannot alter a column.

2) DML Commands :

DML stands for Data Manipulation Language which basically manipulates the data/record like insert , update or delete.
INSERT : This command is used to insert a record in the table.
UPDATE : It update the table record.
DELETE : It delete the table record.

3) DQL Commands :
DQL stands for Data Query Language which basically is used to retrieve the records in the table.
SELECT : It allow us to retrieve the records.

Now let’s we explain all these commands one by one in detail:

CREATE TABLE : This command is used to create database objects like tables. User can create table in any database, by defining table name and its columns with specifications(datatypes).

create table < Table_Name >
(
 column 1 datatype, 
 column 2 datatype,
 .... 
column n datatype 
);

Example for Creating a table :

create table student
(
Roll_no int,
Name text,
Fees real,
address varchar
);

ALTER TABLE : This command is used to alter the database objects like tables. User can alter table add columns variants and can rename the table.

alter table < Table_Name >
add new_column _name column_defination;

Example of alter table : Adding new column

 alter table student add Father_name varchar(50);

Example to alter table : Renaming Table

 alter table student rename to stu;

DROP TABLE : This command is used to drop the table structure. With the table structure associated data, views, index , constraints etc are dropped.

drop table < Table_Name >;

Example to drop table

 drop table student;

INSERT: This command is used to insert record in the table. User define the table name and the data to be specified in the table.

insert into < Table_Name >
values(value 1, value 2,... value n);

Example to insert record in table

insert into student values ('12','Rohit','2500','Karnal','Shyam Lal');

DELETE: This command is used to delete record in the table. User define the table name and the data to be deleted from the table.

delete table  < Table_Name >
where <condition>;

Example to delete records in table

delete table student;

 delete table student where Roll_no='12';

UPDATE: This command is used to update the record in the table. User define the table name and the data to be modified from the table with specified condition.

update table  < Table_Name >
set column1 = value1 ,  column2 = value2 ......  columnN = valueN
where <condition>;

Example to update records in table

 update student set Fees = '30000' where Roll_no='1';

SELECT : This command is used to retrieve records from the table. User can retrieve records as per your requirement from multiple tables by applying conditions.

select column1, column2, ...columnN from table  < Table_Name >

Example to update records in table

 select Name, Roll_No from student;

To retrieve all data use:

select * from student;

DOWNLOAD THIS FREE eBook!

This free eBook will help you master the learning of Android App Development in Android Studio!

Name

Email

Subscribe & Download eBook Now >>