sql

SQL and an introduction to SQLite, SQLite3, and MySQL

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));
MySQL: CREATE DATABASE – CREATE TABLE
ALTER TABLE baseball. teams DROP COLUMN Manager;
MySQL: ALTER TABLE DROP COLUMN

ALTER TABLE baseball.teams ADD Manager INTEGER;
MySQL: ALTER TABLE ADD
DROP DATABASE baseball;
MySQL: DROP TABLE
/*
   sqlite3:
       These statements are not available in SQLite3, SQLite

      CREATE DATABASE baseball;
      TRUNCATE TABLE teams;

      below is illustrated the creation of the baseball database
*/
SQLITE: CREATE A 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;
SQLITE: CREATE TABLE, ALTER TABLE, INSERT
DROP TABLE teams;
SQLITE: DROP TABLE

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';
MySQL: UPDATE row
delete from baseball.teams where City = 'Toronto';
MySQL: DELETE row
/* 
     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';

 
SQLITE: INSERT UPDATE row
SQLITE: DELETE row

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';
MySQL: SELECT
/*   sqlite3
*/
select * from teams;
select * from teams where City = 'Boston';
SQLITE: SELECT
SQLITE: SELECT

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.

Download
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

Frank Yee

Frank holds a Bachelor of Science in Biology from The City University of New York (The City College of New York) and a Bachelor of Arts in Computer Science from The City University of New York (Hunter College). As an undergraduate, he had taken courses where programs were written in Fortran, Basic, Pascal, COBOL, and Assembly Language for the IBM 4341 Mainframe. He has been a software developer for over 30 years having built desktop apps, mobile apps and web apps. He has built software in C/C++, VisualBasic, TCL, Java, PHP, XML/XSLT, Perl, Shell Scripting and Python. Frank was also an educator in The City University of New York (Borough of Manhattan Community College, New York City College of Technology) having taught courses in Pascal, VisualBasic, C++ for over 10 years. Finally, he has recently helped students create programs in Lisp, R, Masm, Haskell, Scheme and Prolog.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button