Most Popular MySQL Commands

The Structured Query Language SQL is a domain-specific language most commonly used by applications to communicate with a Database and manipulate information. By manipulating we can look at the most basic functionality that’s offered known as a CRUD – Create, Read, Update, Delete. In this article, you will see a list of the most popular MySQL commands including examples how to use them in real life. We also prepared an example how to change the URL of a WordPress Website.

The Database serves as a heart to a Web Application. It is the information that the Application revolves around and initially build to serve. Much of the information that’s stored in a Database must never be revealed to the public eye and as such it must be secured properly. Most applications nowadays are secured against SQL Injections and carefully tested for any information that could leak due to internal errors.

The SQL itself could be split into two sub-languages: Data Definition Language and Data Manipulation Language. One is used for defining the Database itself whether the other for interacting with the information that’s stored in it. Experiment using phpMyAdmin on our MySQL Hosting Packages!

In this article, we will review the basic usage for making a CRUD (Create, Read, Update, Delete) functionality and the most-used commands with MySQL that could also be run in a WHM / cPanel stack. Most of the commands in this article can be run through a phpMyAdmin interface and we will leave a reference to MySQL’s documentation. One other thing to notice – the SQL Syntax is always in UPPERCASE although the newest CLIs won’t judge you if you wrote most of the commands the way you want. It is necessary, however, to always end a command with ‘;’.

Create MySQL database command

First, we’ll need to create a Database. When I usually go about creating a new project I always specify the Character Set that will be used and the Collation of the tables and to make sure I haven’t already created it. Ref: https://dev.mysql.com/doc/refman/5.7/en/create-database.html

CREATE DATABASE IF NOT EXISTS `wrong_database_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Delete MySQL database command

Let’s say that I’ve gotten the Database name all wrong and I want to rename it – I’ll have to delete the Database first or DROP it and then create it with a valid name. Ref: https://dev.mysql.com/doc/refman/5.7/en/drop-database.html

DROP DATABASE IF EXISTS `wrong_database_name`;

Manipulating MySQL Databases

I’ll assume we’ve created a valid Database. Now what we need to do is to build up its structure. By this I mean add Tables to it. We’ll begin by using the Database and then adding a Table that will hold information for People – their name and age.

USE `database_name`;

CREATE TABLE people
(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
age INT
);

MySQL Database Relations

MySQL is a Relational Database, this means that it can store information about many entities, however, information about each entity will be in its own separate Table. For example, our project might be an e-commerce system in which we will offer books to our members. The application will hold data about Authors, Books and Clients. Each of those entities will have its own Table containing information just about them, like Name and Age for the Authors or Title and Date Published for Books. When those Tables are created the relation between must be made as well.

There are several Relations that can be used:

One-To-One
One-To-Many
Many-To-Many

A One-To-One might refer to an Employee to be associated with just one Organization and just that Organization to be employing this Person. In this case, we simply create one field in each Table that corresponds to the opposite entity.

A One-To-Many relation might refer to one Client having bought many products but those products will be purchased by just that Person. Here we create one field on the Many sides that points to the One entity that owns them.

A Many-To-Many might refer to Authors and Books. One Author might write many books during his career and one Book might have more than one Author. This is a case in which we create a separate table holding the relations in at least two fields.

In our basic example we won’t be using relations nor joins but I’ll be listing the most used MySQL commands which might have them.

Populate MySQL Database

Now that we have created our Table we’re ready to populate it. The ‘C’ of CRUD stands for Create and we’ll need to create a person or two. This is done with the INSERT command. Ref: https://dev.mysql.com/doc/refman/5.7/en/insert.html

INSERT INTO
`people` (`name`, `age`)
VALUES
('John', 24),
('Jane', 22);

This will create two entities in the People Table named John and Jane, aged 24 and 20 respectively.

Find information from MySQL Database

Now let’s say that we want to retrieve information about John and Jane, the ‘R” standing for Read. We’ll use the SELECT command. Ref: https://dev.mysql.com/doc/refman/5.7/en/select.html

SELECT
`name`,
`age`
FROM
`people`;

Run Selective MySQL Command

This will list all entries for the name and age columns from the People Table. In most cases (and when doing pagination) we want to limit the results or provide a WHERE clause. Let’s say we want the first Person entry that’s older than 22, we’ll use:

SELECT
`name`,
`age`
FROM
`people`
WHERE
`age` > 22
LIMIT
1

UPDATE MySQL Database Command

That will list just John but let’s say that Jane’s birthday comes and she becomes 23. We’ll need to update the Table’s information, or use the ‘U’ – Update command. Ref: https://dev.mysql.com/doc/refman/5.7/en/update.html

UPDATE
`people`
SET
`age` = 23
WHERE
`name` = 'Jane';

Delete MySQL Database Entry

The example could be for our Company’s Employees, in our example, we can say that Jane leaves the company and needs to be removed from the Database. Usually Deleting entries is a bad idea, instead, we should keep all entries in another Table used to view any inactive / “deleted” entry.

DELETE FROM
`people`
WHERE
`name` = 'Jane';

The above operations cover a very basic CRUD (Create, Read, Update, Delete) MySQL Database commands. As basic, they are, those commands are what is used on most of the interactions you do with an online Web Application.

How MySQL Database communication Works?

When creating a new account you’re interacting with the Database. It first looks to see if there’s already another User that has taken the Username – it uses SELECT for that. Then, if validated, you’ll create your account – the Application uses INSERT to store your account’s information. Any change that you make to your profile would invoke an UPDATE command and if you decide to close down your account you’re probably looking at a DELETE command although other options also exist.