Many of you have heard about SQL. In this web hosting blog post we will cover the fundamental idea behind the SQL, what is SQL and different versions that are used nowadays.
What is SQL?
SQL stands for Structured Query Language. It is the language that many applications use for storing and reading Data. There are generally two variations of SQL software – Relational Databases such as MySQL, PostreSQL and NoSQL like MongoDB. The Relational Database usually contains many Tables, each describing entities that are part of the logic which the application uses to solve business problems. The general rule is that each Table represents the information for just one object and no more. NoSQL databases, on the other hand, store their data in Key-Value Pairs using Unique IDs for keys.
The SQL language was originally made for secretaries and was meant to be easy to use – no programming experience was required when your assistant had to enter data for a client or a meeting in their digital notebook. As such it was made to be extremely easy to be read and understood, however, the office employees didn’t quite catch up with the language and it’s syntax thus applications with graphical user interfaces were developed that abstracted working with SQL which was still the software under the hood.
SQL = DML + DDL
SQL could generally be divided into two groups – DDL and DML. DDL stands for Data Definition Language while DML is Data Manipulation Language. For example, you can imagine SQL being a building, DDL would be the foundations – the blocks that build each floor and the rooms on them while DML will be the ability to obtain information for each object or person on the floor and manipulate it. The most used commands or statements are:
Popular DML and DDL SQL commands & statements
DDL Statements:
CREATE
– this command is used to create objects like Databases and Tables
ALTER
– Changes the database structure
RENAME
– with this command, you can rename an object
TRUNCATE
– removes all records from a table
DROP
– command to delete all objects from a table
DML Statements:
SELECT
– Used to read data
INSERT
– Used to save data
DELETE
– Used to delete data
MERGE
– Inserts or Updates
CALL
– command used to call an already created Function
EXPLAIN
– command that provides information about an object
SQL and its usage for CRUD
The DML statements could also be defined by the term CRUD – Create, Read, Update, Delete – which is the functionality that they offer for managing the already defined structure by the DDL.
In the example above we can think that the DDL will dictate how the floors are built, their size, how many rooms each floor could have and what the dimensions of each room are. DML could provide us with means to see how many employees are on each floor (Read), move one employee from one room to another (Update), add another employee (or elephant) in a room (Create) or remove them from the building permanently (Delete).
It is well known that PHP stands behind most of the applications that are hosted in a Shared Environment but it’s not very well known that there are several drivers that are used for MySQL connection – mysql, mysqli and pdo.
PHP MySQL | SQL Drivers
The MySQL driver
The first driver that was used for MySQL connection was mysql. It was easy to be exploited and required extra work to implement defences against MySQL injections. The extension was deprecated in PHP 5.5.0 and was completely removed in PHP 7.0 and above.
The mysqli driver
After MySQL was deprecated, its “improved” version was implemented. The name mysqli comes from MySQL + i for Improved. It’s also the first implementation that provided Object Oriented connection to MySQL and could also be used procedural, however, it wasn’t entirely OOP based. It’s also used by WordPress for it’s DB Connection.
The PDO driver
The PHP Data Object (PDO) was introduced after PHP 5.1.0 and is the full Object Oriented approach to establishing a connection to a SQL database. It’s also advertised that it supports multiple database implementations with its Data Source Name, meaning that the functionality should be the same across multiple SQL softwares like OracleDB and MySQL.
Further abstraction
There is one more layer that abstracts the work with PDO and is most commonly used by frameworks – this is the Database Abstraction Layer. This aims to achieve working with the Database purely by using OOP instead of manually coding queries instead. Two of the most used ones are Doctrine (Symfony) and Eloquent (Laravel)
MySQL in cPanel
The most used Shared Hosting setup is cPanel with MySQL and the phpMyAdmin interface to manage your Databases. You can easily create a Database with the GUI, make a User and assign it to the Database. After that, all the information that you’ll need is the Database Name, User and it’s Password to make the connection. If you need a fast connection you can always navigate to the phpMyAdmin tool and be directly logged in to all Databases that your account has ownership of.
A short Summary of SQL = DML + DDL
SQL is the programming language that stands behind most applications and is responsible for managing their information storage. It can be viewed as a combination of Data Definition Language and Data Manipulation Language, the first being responsible for the structure that will store the information and the second taking care of manipulating that information. MySQL is the most used software for Shared Hosting and it’s easily manageable through cPanel and phpMyAdmin’s interfaces.
You can benefit from a fast and reliable SSD MySQL Hosting with our shared hosting services. Now with a greatly discounted price! Start your new project that needs databases without investing a large amount of money and grow your online presence! And we can help you start and grow your site with our unlimited databases hosting offer!
