Hi everyone in this article I’m explaining about how to download SQLite Database and working with SQLite database.
Description:
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 article will give you quick start with SQLite and make you comfortable with SQLite programming.
What is SQLite?
SQLite is an Open Source database. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. The database requires limited memory at runtime (approx. 250 KByte) which makes it a good candidate from being embedded into other runtimes.
SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before getting saved in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa.
The sqlite3 tool:
The sqlite3 tool is a terminal based frontend to the SQLite library. It evaluates queries interactively and displays the results in multiple formats. It can also be used within scripts. It has its own set of meta commands including .tables, .load, .databases, or .dump. To get the list of all instructions, we type the .help command.
Step 1: Download Database.NET (An Intuitive Multiple Database Manager) from: http://fishcodelib.com/Database.htm.
Step 2: Now extract your zip file and run .exe file
Step 4: Now create a new database
Click File >> Connect >> SQLite >> Create
Then ask a path where are you want to save database file
Give a suitable path for database file you found new message
Click yes for use your database.
Step 5: Now create a table
CREATE TABLE Student
(
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name VARCHAR(100),
EmailId VARCHAR(200),
Address TEXT,
ContactNO CHAR(12),
ZipCode NVARCHAR(20),
Height REAL
)
After write this syntax execute this code and create table successfully.
Step 6: Now insert record in Student table
INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('Kamlakar singh','kamlakar@sample.com','Allahabad','1234567890','212106',5.5)
INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('pawan shukla','pawan@sample.com','Allahabad','1234567890','212106',5.5)
INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('Rohit kesharwani','rohit@sample.com','Allahabad','1234567890','212106',5.5)
INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('Haider','haider@sample.com','Allahabad','1234567890','212106',5.5)Step 7: Now fetch record from Student table
SELECT * FROM Student
Step 8: Now delete record from Student table
DELETE FROM Student WHERE Id=4
Step 9: Now update record in Student table
UPDATE Student SET Name='Kamlakar Kumar Singh', Address='Rewa' WHERE Id=1
SQLite has many built-in functions for performing processing on string or numeric data. Following is the list of few useful SQLite built-in functions and all are case in-sensitive which means you can use these functions either in lower-case form or in upper-case or in mixed form.
For more details, you can check official documentation for SQLite:
1. SQLite COUNT Function: The SQLite COUNT aggregate function is used to count the number of rows in a database table.
2. SQLite MAX Function: The SQLite MAX aggregate function allows us to select the highest (maximum) value for a certain column.
3. SQLite MIN Function: The SQLite MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
4. SQLite AVG Function: The SQLite AVG aggregate function selects the average value for certain table column.
5. SQLite SUM Function: The SQLite SUM aggregate function allows selecting the total for a numeric column.
6. SQLite RANDOM Function: The SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.
7. SQLite ABS Function: The SQLite ABS function returns the absolute value of the numeric argument.
8. SQLite UPPER Function: The SQLite UPPER function converts a string into upper-case letters.
9. SQLite LOWER Function: The SQLite LOWER function converts a string into lower-case letters.
10. SQLite LENGTH Function: The SQLite LENGTH function returns the length of a string.
11. SQLite sqlite_version Function: The SQLite sqlite_version function returns the version of the SQLite library.
In my next post i'll explain about Bootstrap Tokenfield and autocomplete
Leave Comment