Working With MySQL

MySQL — The Relational Database

MySQL is a relational database management system (RDBMS). So, what is a database? A database is an application that stores a collection of data from various sources. It has the features like ‘Creating the database’, ‘Searching the database’, ‘Modifying the database’, ‘Managing the database’ and so on.

What this article is about

This article gives a brief introduction to MySQL and its implementation on the Windows platform. Then we will get to know about databases and tables.

Setting the environment up

In here we will work with MySQL Community Server 8.0.13. Download the installer and install it. We can work with MySQL in two ways —’ Command Line Client’ & ‘Workbench’. We will use both in this article depending upon the ease to work with.

Database & Table Creation

A MySQL server can have multiple databases and each database can have multiple tables. Type the following the printout the existing databases.

CREATE TABLE first_table (id INT NOT NULL, movie VARCHAR(50) NOT NULL, director VARCHAR(50), genre VARCHAR(50), PRIMARY KEY(id));

CRUD Operations

CRUD stands for CREATE, READ, UPDATE & DELETE. For every database, we have data, also called as records, to be inserted, read, updated or deleted from a table. CREATE means inserting a record into the table, READ means retrieve the record from the table, UPDATE means modifying the existing record in the table and DELETE means deleting a record from the table. We will take a look at these operations now.

CREATE

To insert a new record into a table in MySQL we have the following format.

INSERT INTO table_name (column1, column2, column3,....) VALUES (value1, value2, value3,....);
INSERT INTO first_table VALUES (1, "Troy", "Wolfgang Peterson", "Historical Fiction");
INSERT INTO first_table VALUES 
(2, "Inception", "Christopher Nolan", "Science Fiction"),
(3, "The Greatest Showman, "Michael Gracey", "Musical Drama");

READ

When we want to retrieve the records from a table we use the ‘SELECT’ clause as follows.

UPDATE

Sometimes, we will have to update the existing records, then we use the UPDATE clause. Here it goes.

UPDATE table_name 
SET column1 = value1, column2 = value2...
WHERE condition

DELETE

To delete one or more records we use the DELETE clause along with the WHERE clause. Here is the syntax for deleting the records.

DELETE FROM table_name WHERE condition;

Logical and Comparative Operators

Now we will load a sample SQL database named ‘sampledatabase.sql’ load it into our Workbench by going to File ->Open SQL Script->Load the ‘.sql’ file from the folder where it resides. Execute the file in order to insert the data into the various tables in the database.

Logical Operators

These are the operators used in SQL language to perform the logical operations such AND, OR, NOT etc. We will execute some commands to see how these work. Now we are going to use the ‘customers’ table of the sampledatabse.sql imported into our database ‘new’.

SELECT * FROM customers where country = "Australia" AND state = "Victoria";
SELECT * FROM customers WHERE country = "Norway" OR country = "France";

Comparison Operators

The common comparison operators are less than ‘<’, greater than ‘>’, less than or equal to ‘<=’, greater than or equal to ‘>=’ or equal to ‘=’.

Aggregate Functions

These functions operate on all the rows to give a combined or single result based on the criteria specified. The mostly used aggregate functions are COUNT(), SUM(), AVG(), MIN(), MAX().

SELECT COUNT(*) AS total_num_of_rows, SUM(creditLimit) AS total_sum, MIN(creditLimit) AS minimum, MAX(creditLimit) AS maximum FROM customers;

Grouping and Ordering the records

Grouping

‘GROUP BY’ is the clause to arrange identical data into groups to yield the result by one or more columns. For eg, consider the case of customers who are purchasing more than once. If we want to know total how much each customer spent can be found out using grouping. Let’s see an example to understand it more. Now, we will use the ‘payments’ table from the ‘sampledatabase.sql’.

SELECT customerNumber, COUNT(amount) FROM payments GROUP BY customerNumber;

Ordering or Sorting

‘ORDER BY’ is the clause to sort the result in ascending or descending order. ‘ASC’ and ‘DESC’ are the keywords used to sort a column in the ascending and descending order. After the ‘ORDER BY’ clause, we have to specify the column with which we need to sort and it comes after the ‘GROUP BY’ clause. Let’s see it in action with the last example.

SELECT customerNumber, AVG(amount) as totalAmount FROM payments GROUP BY customerNumber ORDER BY totalAmount DESC;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store