In this article, we will show you the difference between SQL, MySQL, Sqlite, Sqlite3. SQL stands for Structured Query Language. SQL is an example of 4GL or Fourth Generation Language.
1.1 First-Generation Language
First-generation languages were machine languages that involved entering instructions to a computer in the form of bits or binary digits, represented by 1s and 0s, on and off, true or false. As a result, programming or the creation of instructions for the computer proved to be very time-consuming, and very complex.
1.2 Second-Generation Language
Second-generation languages or assembly languages were then developed in which mnemonics or representations in English words are used to represent instructions, such as ADD, SUB, MOV, JMP. This allowed for easier visualization of the logic of the program but was still difficult because each assembly language instruction was assembled by an assembler into machine language, so there was usually a one-to-one correspondence between assembly language and machine language which is the only language a computer understands.
1.3 Third-Generation Language
Third-generation languages are a huge leap forward which includes languages such as C, C++, Basic, Fortran, Cobol, Java, Python, PHP, etc. in which one statement in these languages will translate into many assembly language and machine language instructions. As a result, more programmers or the authors of these instructions can dictate what a computer can do and how to do it.
1.4 Fourth-Generation Language
Fourth Generation Languages, such as SQL, direct a computer on what to do in terms of manipulating a database but not how to do it.
1.5 Fifth-Generation Language
Finally, Fifth Generation Languages, such as Prolog (“PRO”gramming “LOG”ic), allow for the creation of facts and rules on how to manipulate the facts.
2. SQL and Relational Databases
According to https://en.wikipedia.org/wiki/SQL, Donald D. Chamberlin and Raymond F. Boyce first developed SQL while working for IBM to implement the relational model of Edgar F. Codd in the early 1970s,
The relational model of data representation involves organizing data into tables which are themselves made up of rows of columns. Each table would be used to house entities. Each row would be an instance of an entity. Rows contain fields or particular characteristics or attributes of that entity.
The relational model of data representation also allows for some relations, links, or correspondences between tables or entities.
Finally, a relational database can be defined as a collection of such tables which are somehow related. Relational databases also include objects, in addition to tables, such as indices which allow for efficient retrieval of sorted data, permissions that control access to the data, and triggers that allow for the change to the data when certain events or criteria of the data are met.
Relational Database Management Systems (RDBMS) are built around the concept of relational databases to allow for the creation, manipulation, and visualization of the data.
Many RDBMS are in existence. IBM DB2, IBM Informix, Oracle, Microsoft SQLServer, PostgreSQL, Sybase, Microsoft Access, MySQL, SQLite, and SQLite3 come to mind.
MySQL, like Microsoft SQLServer, PostgresSQL, Oracle, Sybase, IBM DB2, is built around a Server-Client architecture. The Server is a computer or computer system, which has server software, that executes the SQL Statements forwarded to by the Client. The Client can be on the same computer with client software but typically the server is a dedicated computer due to the amount of work that the server must perform including handling requests from many clients. Websites, as well as desktop applications that require data, are usually consumers or clients of Server-based databases.
SQLite or SQLite3 do not require a server to store its data. Data is stored in a single file and therefore, dedicated to one user at a time.
SQLite use has exploded in step with the ubiquity of mobile devices. Due to its small digital footprint, it is perfectly suited for these devices. Databases is also typically housed in a single file with extensions such as .sqlite, .sqlite3, .db, .db3
SQLite3 differs from its predecessors (SQLite and SQLite2) in that numbers and BLOBs (Binary Large Objects) are stored in their native formats as opposed to its predecessors in which data is stored as strings. This may result in smaller database sizes.
SQL is what is used to provide instructions to MySQL, SQLite, SQLite, Oracle, SQLServer, databases.
3. SQL – The language
SQL is made up of many statements which fall into 5 major sublanguages:
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Query Language (DQL)
• Data Control Language (DCL)
• Transaction Control Language
4. Data Definition Language
Data Definition Language is used to define the database and the constructs with which the data is stored. Below are some examples
CREATE DATABASE
Creates a database
DROP DATABASE
Delete (drop) a database
CREATE TABLE
Creates a table by providing field names, field types, and other attributes of Fields
ALTER TABLE
Modify the structure of a table
DROP TABLE
Delete (drop) a table
The following will illustrate the use of these SQL Statements:
/*
mySQL:
*/
CREATE DATABASE baseball;
CREATE TABLE baseball.teams (id int primary key auto_increment, City varchar(30), Name varchar(30), Manager varchar(30));
ALTER TABLE baseball. teams DROP COLUMN Manager;
ALTER TABLE baseball.teams ADD Manager INTEGER;
DROP DATABASE baseball;
/*
sqlite3:
These statements are not available in SQLite3, SQLite
CREATE DATABASE baseball;
TRUNCATE TABLE teams;
below is illustrated the creation of the baseball database
*/
CREATE TABLE teams (id integer primary key autoincrement, City varchar(30), Name varchar(30), Manager varchar(30)); ALTER TABLE teams DROP COLUMN Manager; ALTER TABLE teams ADD Manager integer;
DROP TABLE teams;
5. Data Manipulation Language
Data Manipulation Language is used to add, delete, modify data in the database
INSERT
Add rows to a table
UPDATE
Modify rows to a table
DELETE
Remove rows from a table
/* mysql:
*/
truncate baseball.teams;
insert into baseball.teams (City, Name) values ('Baltimore', 'Orioles');
insert into baseball.teams (City, Name) values ('Boston', 'Red Sox');
insert into baseball.teams (City, Name) values ('New York', 'Yankees');
insert into baseball.teams (City, Name) values ('Tampa Bay', 'Rays');
insert into baseball.teams (City, Name) values ('Toronto', 'Blue Jays');
update baseball.teams set Name = 'Mets' where City = 'New York';
delete from baseball.teams where City = 'Toronto';
/*
sqlite3:
These statements are unavailable in SQLite3
TRUNCATE TABLE teams;
*/
insert into teams (City, Name) values ('Baltimore', 'Orioles');
insert into teams (City, Name) values ('Boston', 'Red Sox');
insert into teams (City, Name) values ('New York', 'Yankees');
insert into teams (City, Name) values ('Tampa Bay', 'Rays');
insert into teams (City, Name) values ('Toronto', 'Blue Jays');
update teams set Name = 'Mets' where City = 'New York';
delete from teams where City = 'Toronto';
6. Data Query Language
Data Query Language is used to retrieve information from the database
/*
mysql:
*/
select * from baseball.teams;
select * from baseball.teams where City = 'Boston';
/* sqlite3 */ select * from teams; select * from teams where City = 'Boston';
7. Data Control Language
Data Control Language is used to control access to the database
examples are GRANT and REVOKE
GRANT will set the permissions for a particular user for a particular operation to a particular
database object
REVOKE is the opposite of GRANT and remove such permission
8. Transaction Control Language
Transaction Control Language is used to determine the outcome of the transaction to the database
examples are COMMIT and ROLLBACK
COMMIT allows the transaction to be Permanently saved to the database
ROLLBACK allows the transaction to be ignored and will not be saved to the database
9. Summary
This article is an introduction to SQL and MySQL difference and how it is used to create a very simple data model to store information. There are more complex uses for SQL which the reader may choose to explore, such as:
Multi-table data models and joining tables using foreign keys
Creating Stored Procedures and functions
Using SQL from Applications written in Java, Python, PHP, C/C++, Ruby, etc.
You can download the scripts containing the SQL Statements for sql and mysql differences used in this article: What is the difference between SQL, MySql, Sqlite, Sqlite3
