MySQL - Prevent Insertion of Record if ID and Timestamp Unique Combo Constraint Within Timeframe

I have a scenario in which 3 standalone agents are reporting uptime statuses for various hosts. If the hosts go down and are offline, a downtime record should be created. Unfortunately, since the agents report exactly at the same time with the same information, I've seen duplicate entries that are 1-2 seconds apart.

I have a scenario in which 3 standalone agents are reporting uptime statuses for various hosts. If the hosts go down and are offline, a downtime record should be created. Unfortunately, since the agents report exactly at the same time with the same information, I've seen duplicate entries that are 1-2 seconds apart.

I have a unique constraint that was created on the table for both the datetime and the host ID. Thus, they cannot be the same. But if the requests from the agents come in at the same time or a second apart, a duplicate might be created despite code checks looking for an existing entry (in this case, an entry hasn't been created yet in all three instances if the agents report at the same time). The unique constraint won't prevent the duplicates either, since the datetime might be 1 second ahead or behind when the PHP / MySQL call finishes getting processed...

So, what is the best way to handle this situation? Is there a way in MySQL to specify that if a unique constraint (which includes a datetime field) is within a certain time frame of another record with the unique constraint, it shouldn't be allowed to insert?

Do I need to run a job that removes entries within a few seconds of each other, or is there a way to get MySQL to do this for me somehow?

Table structure looks like this

entry_id host_id datetime

Entries might be

1 121 01/17/2019 02:38:04 AM

1 121 01/17/2019 02:38:05 AM

1 121 01/17/2019 02:36:04 AM

I want to prevent the insertion of the bold entry since it's within 1 second from the last entry that was inserted. Code checks won't work because no entries may exist at the time it checks for one. I already have a code check looking for an existing entry, and since it doesn't find one and the code can be run at the same time for each request, the check fails and says a new entry should be created.

There are more datetime columns in my table, but they aren't needed to understand this situation. Any help is appreciated.

MySQL Tutorial: A Beginners Guide To Learn MySQL

MySQL Tutorial: A Beginners Guide To Learn MySQL

MySQL Tutorial: A Beginners Guide To Learn MySQL - Let's take a look at a very in-depth MySQL tutorial that explores all the operations and commands that you need to explore your databases...

MySQL Tutorial: A Beginners Guide To Learn MySQL - Let's take a look at a very in-depth MySQL tutorial that explores all the operations and commands that you need to explore your databases...

MySQL Tutorial is the second article in this blog series. In the previous article, What is MySQL, I introduced you to all the basic terminologies that you needed to understand before you get started with this relational database. In this blog of MySQL, you will be learning all the operations and commands that you need to explore your databases.

The topics covered in this article are mainly divided into 4 categories: DDL, DML, DC, and TCL.

  • The DDL (Data Manipulation Language) consists of those commands which are used to define the database. Example: CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME.
  • The DML (Data Manipulation Language) commands deal with the manipulation of data present in the database. Example: SELECT, INSERT, UPDATE, DELETE.
  • The DCL (Data Control Language) commands deal with the rights, permissions and other controls of the database system. Example: GRANT, INVOKE
  • The TCL ( Transaction Control Language) consists of those commands which mainly deal with the transaction of the database.

Apart from the commands, following are the other topics covered in the blog:

  • Different Types Of Keys In Database
  • Constraints Used In Database
  • Nested Queries
  • Joins
  • Set Operations

We are going to cover each of these categories one by one.

In this blog on MySQL Tutorial, I am going to consider the below database as an example, to show you how to write commands.

So, let's get started now!

MySQL Tutorial: Data Definition (DDL) Commands

This section consists of those commands, by which you can define your database. The commands are:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • RENAME

Now, before I start with the commands, let me just tell you the way to mention the comments in MySQL.

Comments

Like any other programming language, there are mainly two types of comments.

  • **Single-Line Comments: **The single line comments start with ‘–‘. So, any text mentioned after — till the end of the line will be ignored by the compiler.

Example:

--Select all:
SELECT * FROM Students;

  • **Multi-Line Comments: *The Multi-line comments start with / and end with /. So, any text mentioned between / and */ will be ignored by the compiler.

Example:

/*Select all the columns
of all the records
in the Students table:*/
SELECT * FROM Students;

Now, that you know how to mention comments in MySQL, let's continue with the DDL commands.

CREATE

The create statement is used to either create a schema, tables or an index.

The 'CREATE SCHEMA' Statement

This statement is used to create a database.

Syntax:

CREATE SCHEMA Database_Name;

Example:

CREATE SCHEMA StudentsInfo;

The 'CREATE TABLE' Statement

This statement is used to create a new table in a database.

Syntax:
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Example:
CREATE TABLE Students
(
StudentID int,
StudentName varchar(255),
ParentName varchar(255),
Address varchar(255),
PostalCode int,
City varchar(255)
);

The 'CREATE TABLE AS' Statement

This statement is used to create a new table from an existing table. So, this table gets the same column definitions as that of the existing table.

Syntax:

CREATE TABLE     SELECT     FROM new_table_name AScolumn1, column2,...existing_table_name     WHERE ....;

Example:

CREATE TABLE ExampleTable AS
SELECT Studentname, Parentname
FROM Students;

ALTER

The ALTER command is used to add, modify or delete constraints or columns.

The 'ALTER TABLE' Statement

This statement is used to either add, modify or delete constraints and columns from a table.

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE Students
ADD DateOfBirth date;

DROP

The DROP command is used to delete the database, tables, or columns.

The 'DROP SCHEMA' Statement

This statement is used to drop the complete schema.

Syntax:

DROP SCHEMA schema_name;

Example:

DROP SCHEMA StudentsInfo;

The 'DROP TABLE' Statement

This statement is used to drop the entire table with all its values.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE table_name;

TRUNCATE

This statement is used to delete the data which is present inside a table, but the table doesn't get deleted.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Students;

RENAME

This statement is used to rename one or more tables.

Syntax:

RENAME TABLE 
     tbl_name TO new_tbl_name
     [, tbl_name2 TO new_tbl_name2] ...

Example:

RENAME Students TO Infostudents;

Now, before I move into the further sections, let me tell you the various types of Keys and Constraints that you need to mention while manipulating the databases.

Different Types of Keys in Database

There are mainly 5 types of Keys, that can be mentioned in the database.

  • **Candidate Key: **The minimal set of attributes which can uniquely identify a tuple is known as a candidate key. A relation can hold more than a single candidate key, where the key is either a simple or composite key.
  • **Super Key: **The set of attributes which can uniquely identify a tuple is known as Super Key. So, a candidate key is a superkey, but vice-versa isn't true.
  • Primary Key: A set of attributes that can be used to uniquely identify every tuple is also a primary key. So, if there are 3-4 candidate keys present in a relationship, then out those, one can be chosen as a primary key.
  • Alternate Key: The candidate key other than the primary key is called as an alternate key.
  • **Foreign Key: **An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers.
Constraints Used In Database

Refer to the image below are the constraints used in the database.

Figure 1:* Constraints Used In Database: MySQL Tutorial*

Now that you know the various types of keys and constraints, let's move on to the next section i.e Data Manipulation Commands.

Data Manipulation (DML) Commands

This section consists of those commands, by which you can manipulate your database. The commands are:

  • USE
  • INSERT
  • UPDATE
  • DELETE
  • SELECT

Apart from these commands, there are also other manipulative operators/functions such as:

USE

The USE statement is used to mention which database has to be used to perform all the operations.

Syntax:

USE Database_name;

Example:

USE StudentsInfo;

INSERT

This statement is used to insert new records in a table.

The INSERT INTO statement can be written in the following two ways:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
--You need not mention the column names
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Example:

INSERT INTO Infostudents(StudentID, StudentName, ParentName, Address, City, PostalCode, Country)
VALUES ('06', 'Sanjana','Jagannath', 'Banjara Hills', 'Hyderabad', '500046', 'India');
INSERT INTO Infostudents
VALUES ('07', 'Shivantini','Praveen', 'Camel Street', 'Kolkata', '700096', 'India');

UPDATE

This statement is used to modify the existing records in a table.

Syntax:

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

Example:

UPDATE Infostudents
SET StudentName = 'Alfred', City= 'Frankfurt'
WHERE StudentID = 1;

DELETE

This statement is used to delete existing records in a table.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Infostudents
WHERE StudentName='Salomao';

SELECT

This statement is used to select data from a database and the data returned is stored in a result table, called the result-set.

The following are the two ways of using this statement:

Syntax:

SELECT column1, column2, ...
FROM table_name;
--(*) is used to select all from the table
SELECT * FROM table_name;

Example:

SELECT StudentName, City FROM Infostudents;
SELECT * FROM Infostudents;

Apart from the individual SELECT keyword, we will be also seeing the following statements, which are used with the SELECT keyword:

The 'SELECT DISTINCT' Statement

This statement is used to return only distinct or different values. So, if you have a table with duplicate values, then you can use this statement to list distinct values.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example:

SELECT Country FROM Students;

The 'ORDER BY' Statement

This statement is used to sort the desired results in ascending or descending order. By default, the results would be sorted in ascending order. If you want the records in the result-set in descending order, then use the DESC keyword.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Example:

SELECT * FROM Infostudents
ORDER BY Country;  
SELECT * FROM Infostudents
ORDER BY Country DESC;
SELECT * FROM Infostudents
ORDER BY Country, StudentName;
SELECT * FROM Infostudents
ORDER BY Country ASC, StudentName DESC;

The 'GROUP BY' Statement

This statement is used with the aggregate functions to group the result-set by one or more columns.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example:

SELECT COUNT(StudentID), Country
FROM Infostudents
GROUP BY Country
ORDER BY COUNT(StudentID) DESC;

The 'HAVING' Clause Statement

Since the WHERE keyword cannot be used with aggregate functions, the HAVING clause was introduced.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Example:

SELECT COUNT(StudentID), City
FROM Infostudents
GROUP BY City
HAVING COUNT(Fees) > 23000;

LOGICAL OPERATORS

This set of operators consists of logical operators such as AND/OR/NOT.

AND OPERATOR

The AND operator is used to filter records that rely on more than one condition. This operator displays the records, which satisfy all the conditions separated by AND, and give the output TRUE.

Syntax:

ELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

Example:

SELECT * FROM Infostudents
WHERE Country='Brazil' AND City='Rio Claro';

OR OPERATOR

The OR operator displays those records which satisfy any of the conditions separated by OR and gives the output TRUE.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Example:

SELECT * FROM Infostudents
WHERE City='Toronto' OR City='Seoul';

NOT OPERATOR

This operator displays a record when the condition (s) is NOT TRUE.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example:

SELECT * FROM Infostudents
WHERE NOT Country='India';
--You can also combine all the above three operators and write a query like this:
SELECT * FROM Infostudents
WHERE Country='India' AND (City='Bangalore' OR City='Canada');

Arithmetic, Bitwise, Comparison, and Compound Operators

Refer to the image below.

Figure 2:* Arithmetic, Bitwise, Comparison & Compound Operators - MySQL Tutorial*

Aggregate Functions

This section of functions include the following functions:

MIN() Function

This function returns the smallest value of the selected column in a table.

Syntax:

SELECT MIN(column_name)
FROMtable_name
WHEREcondition;

Example:

SELECT MIN(StudentID) AS SmallestID
FROM Infostudents;

MAX() Function

This function returns the largest value of the selected column in a table.

Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Example:

SELECT MAX(Fees) AS SmallestFees
FROM Infostudents;

COUNT() Function

This function returns the number of rows that match the specified criteria.

Syntax:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example:

SELECT COUNT(StudentID)
FROM Infostudents;

AVG() Function

This function returns the average value of a numeric column that you choose.

Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example:

SELECT AVG(Fees)
FROM Infostudents;

SUM() Function

This function returns the total sum of a numeric column that you choose.

Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example:

SELECT SUM(Fees)
FROM Infostudents;

SPECIAL OPERATORS

This section includes the following operators:

BETWEEN Operator

This operator is an inclusive operator, which selects values(numbers, texts or dates) within a given range.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example:

SELECT * FROM Infostudents
WHERE Fees BETWEEN 20000 AND 40000;

IS NULL Operator

Since it is not possible to test for the NULL values with the comparison operators(=, <, >), we can use IS NULL and IS NOT NULL operators instead.

Syntax:

--Syntax for IS NULL
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
--Syntax for IS NOT NULL
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example:

SELECT StudentName, ParentName, Address FROM Infostudents
WHERE Address IS NULL;
SELECT StudentName, ParentName, Address FROM Infostudents
WHERE Address IS NOT NULL;

LIKE Operator

The mentioned below are the two wildcards that are used in conjunction with the LIKE operator:

  • % — The percent sign represents zero, one, or multiple characters
  • _ — The underscore represents a single character

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

Refer to the following table for the various patterns that you can mention with LIKE operator.

Example:

SELECT * FROM Infostudents
WHERE StudentName LIKE 'S%';

IN Operator

This is a shorthand operator for multiple OR conditions which allows you to specify multiple values in a WHERE clause.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example:

SELECT * FROM Infostudents
WHERE Country IN ('Algeria', 'India', 'Brazil');

Note: You can also use IN while writing Nested Queries. Consider the below syntax:

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

EXISTS Operator

This operator is used to test if a record exists or not.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Example:

SELECT StudentName
FROM Infostudents
WHERE EXISTS (SELECT ParentName FROM Infostudents WHERE StudentId = 05 AND Price < 25000);

ALL Operator

This operator is used with a WHERE or HAVING clause and returns true if all of the subquery values meet the condition.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

Example:

SELECT StudentName
FROM Infostudents
WHERE StudentID = ALL (SELECT StudentID FROM Infostudents WHERE Fees > 20000);

ANY Operator

Similar to the ALL operator, the ANY operator is also used with a WHERE or HAVING clause and returns true if any of the subquery values meet the condition.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

Example:

SELECT StudentName
FROM Infostudents
WHERE StudentID = ANY (SELECT SttudentID FROM Infostudents WHERE Fees BETWEEN 22000 AND 23000);

Now that I have told you a lot about DML commands, let me just tell you in short about Nested Queries, Joins, and Set Operations.

Nested Queries

Nested queries are those queries which have an outer query and inner subquery. So, basically, the subquery is a query which is nested within another query such as SELECT, INSERT, UPDATE or DELETE. Refer to the image below:

Fig 3:* Representation Of Nested Queries - MySQL Tutorial*

JOINS are used to combine rows from two or more tables, based on a related column between those tables. The following are the types of joins:

  • INNER JOIN: This join returns those records which have matching values in both the tables.
  • FULL JOIN: This join returns all those records which either have a match in the left or the right table.
  • LEFT JOIN: This join returns records from the left table, and also those records which satisfy the condition from the right table.
  • RIGHT JOIN: This join returns records from the right table, and also those records which satisfy the condition from the left table.

Refer to the image below.

Fig 4:* Representation Of Joins: MySQL Tutorial*

Let's consider the below table apart from the Infostudents table, to understand the syntax of joins.

INNER JOIN

Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT Courses.CourseID, Infostudents.StudentName
FROM Courses
INNER JOIN Infostudents ON Courses.StudentID = Infostudents.StudentID;

FULL JOIN

Syntax:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT Infostudents.StudentName, Courses.CourseID
FROM Infostudents
FULL OUTER JOIN Orders ON Infostudents.StudentID=Orders.StudentID
ORDER BY Infostudents.StudentName;

LEFT JOIN

Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT Infostudents.StudentName, Courses.CourseID
FROM Infostudents
LEFT JOIN Courses ON Infostudents.CustomerID = Courses.StudentID
ORDER BY Infostudents.StudentName;

RIGHT JOIN

Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT Courses.CourseID
FROM Courses
RIGHT JOIN Infostudents ON Courses.StudentID = Infostudents.StudentID 
ORDER BY Courses.CourseID;

Set Operations

There are mainly three set operations: UNION, INTERSECT, SET DIFFERENCE. You can refer to the image below to understand the set operations in SQL.

Now, that you guys know the DML commadsn. Let’s move onto our next section and see the DCL commands.

Data Control (DCL) Commands

This section consists of those commands which are used to control privileges in the database. The commands are:

GRANT

This command is used to provide user access privileges or other privileges for the database.

Syntax:

GRANT privileges ON object TO user;

Example:

GRANT CREATE ANY TABLE TO localhost;

REVOKE

This command is used to withdraw user’s access privileges given by using the GRANT command.

Syntax:

REVOKE privileges ON object FROM user;

Example:

REVOKE INSERT ON *.* FROM Infostudents;

Now, let’s move on to the last section of this blog i.e. the TCL Commands.

Transaction Control (TCL) Commands

This section of commands mainly deals with the transaction of the database. The commands are:

COMMIT

This command saves all the transactions to the database since the last COMMIT or ROLLBACK command.

Syntax:

COMMIT;

Example:

DELETE FROM Infostudents WHERE Fees = 42145;

COMMIT;

ROLLBACK

This command is used to undo transactions since the last COMMIT or ROLLBACK command was issued.

Syntax:

ROLLBACK;

Example:

DELETE FROM Infostudents WHERE Fees = 42145;

ROLLBACK;

SAVEPOINT

This command creates points within the groups of transactions in which to ROLLBACK. So, with this command, you can simply roll the transaction back to a certain point without rolling back the entire transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME; --Syntax for saving the SAVEPOINT

ROLLBACK TO SAVEPOINT_NAME; --Syntax for rolling back to the Savepoint command

Example:

SAVEPOINT SP1;

DELETE FROM Infostudents WHERE Fees = 42145;

SAVEPOINT SP2;

RELEASE SAVEPOINT

You can use this command to remove a SAVEPOINT that you have created.

Syntax:

RELEASE SAVEPOINT SAVEPOINT_NAME;

Example:

RELEASE SAVEPOINT SP2;

SET TRANSACTION

This command gives a name to the transaction.

Syntax:

SET TRANSACTION [ READ WRITE | READ ONLY ];

I hope you enjoyed reading this article on MySQL Tutorial. We have seen the different commands that will help you write queries and play around with your databases.

An Introduction to Queries in MySQL

An Introduction to Queries in MySQL

In this guide, we will discuss the basic syntax of SQL queries as well as some of the more commonly-employed functions and operators. We will also practice making SQL queries using some sample data in a MySQL database.


Introduction

Databases are a key component of many websites and applications, and are at the core of how data is stored and exchanged across the internet. One of the most important aspects of database management is the practice of retrieving data from a database, whether it’s on an ad hoc basis or part of a process that’s been coded into an application. There are several ways to retrieve information from a database, but one of the most commonly-used methods is performed through submitting queries through the command line.

In relational database management systems, a query is any command used to retrieve data from a table. In Structured Query Language (SQL), queries are almost always made using the SELECT statement.

In this guide, we will discuss the basic syntax of SQL queries as well as some of the more commonly-employed functions and operators. We will also practice making SQL queries using some sample data in a MySQL database.

MySQL is an open-source relational database management system. One of the most widely-deployed SQL-databases, MySQL prioritizes speed, reliability, and usability. It generally follows the ANSI SQL standard, although there are a few cases where MySQL performs operations differently than the recognized standard.


Prerequisites

In general, the commands and concepts presented in this guide can be used on any Linux-based operating system running any SQL database software. However, it was written specifically with an Ubuntu 18.04 server running MySQL in mind. To set this up, you will need the following:

With this setup in place, we can begin the tutorial.


Creating a Sample Database

Before we can begin making queries in SQL, we will first create a database and a couple tables, then populate these tables with some sample data. This will allow you to gain some hands-on experience when you begin making queries later on.

For the sample database we’ll use throughout this guide, imagine the following scenario:

You and several of your friends all celebrate your birthdays with one another. On each occasion, the members of the group head to the local bowling alley, participate in a friendly tournament, and then everyone heads to your place where you prepare the birthday-person’s favorite meal.

Now that this tradition has been going on for a while, you’ve decided to begin tracking the records from these tournaments. Also, to make planning dinners easier, you decide to create a record of your friends’ birthdays and their favorite entrees, sides, and desserts. Rather than keep this information in a physical ledger, you decide to exercise your database skills by recording it in a MySQL database.

To begin, open up a MySQL prompt as your root MySQL user:

sudo mysql

Note: If you followed the prerequisite the tutorial on Installing MySQL on Ubuntu 18.04, you may have configured your root user to authenticate using a password. In this case, you will connect to the MySQL prompt with the following command:

mysql -u root -p

Next, create the database by running:

CREATE DATABASE birthdays;

Then select this database by typing:

USE birthdays;

Next, create two tables within this database. We’ll use the first table to track your friends’ records at the bowling alley. The following command will create a table called tourneys with columns for the name of each of your friends, the number of tournaments they’ve won (wins), their all-time best score, and what size bowling shoe they wear (size):

CREATE TABLE tourneys (
name varchar(30),
wins real,
best real,
size real
);

Once you run the CREATE TABLE command and populate it with column headings, you’ll receive the following output:

OutputQuery OK, 0 rows affected (0.00 sec)

Populate the tourneys table with some sample data:

INSERT INTO tourneys (name, wins, best, size)
VALUES ('Dolly', '7', '245', '8.5'),
('Etta', '4', '283', '9'),
('Irma', '9', '266', '7'),
('Barbara', '2', '197', '7.5'),
('Gladys', '13', '273', '8');

You’ll receive an output like this:

OutputQuery OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

Following this, create another table within the same database which we’ll use to store information about your friends’ favorite birthday meals. The following command creates a table named dinners with columns for the name of each of your friends, their birthdate, their favorite entree, their preferred side dish, and their favorite dessert:

CREATE TABLE dinners (
name varchar(30),
birthdate date,
entree varchar(30),
side varchar(30),
dessert varchar(30)
);

Similarly for this table, you’ll receive feedback confirming that the command ran successfully:

OutputQuery OK, 0 rows affected (0.01 sec)

Populate this table with some sample data as well:

INSERT INTO dinners (name, birthdate, entree, side, dessert)
VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'),
('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'),
('Irma', '1941-02-18', 'tofu', 'fries', 'cake'),
('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'),
('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream');

OutputQuery OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

Once that command completes successfully, you’re done setting up your database. Next, we’ll go over the basic command structure of SELECT queries.


Understanding SELECT Statements

As mentioned in the introduction, SQL queries almost always begin with the SELECT statement. SELECT is used in queries to specify which columns from a table should be returned in the result set. Queries also almost always include FROM, which is used to specify which table the statement will query.

Generally, SQL queries follow this syntax:

SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply;

By way of example, the following statement will return the entire name column from the dinners table:

SELECT name FROM dinners;

Output+---------+
| name |
+---------+
| Dolly |
| Etta |
| Irma |
| Barbara |
| Gladys |
+---------+
5 rows in set (0.00 sec)

You can select multiple columns from the same table by separating their names with a comma, like this:

SELECT name, birthdate FROM dinners;

Output+---------+------------+
| name | birthdate |
+---------+------------+
| Dolly | 1946-01-19 |
| Etta | 1938-01-25 |
| Irma | 1941-02-18 |
| Barbara | 1948-12-25 |
| Gladys | 1944-05-28 |
+---------+------------+
5 rows in set (0.00 sec)

Instead of naming a specific column or set of columns, you can follow the SELECT operator with an asterisk (*) which serves as a placeholder representing all the columns in a table. The following command returns every column from the tourneys table:

SELECT * FROM tourneys;

Output+---------+------+------+------+
| name | wins | best | size |
+---------+------+------+------+
| Dolly | 7 | 245 | 8.5 |
| Etta | 4 | 283 | 9 |
| Irma | 9 | 266 | 7 |
| Barbara | 2 | 197 | 7.5 |
| Gladys | 13 | 273 | 8 |
+---------+------+------+------+
5 rows in set (0.00 sec)

WHERE is used in queries to filter records that meet a specified condition, and any rows that do not meet that condition are eliminated from the result. A WHERE clause typically follows this syntax:

. . . WHERE column_name comparison_operator value

The comparison operator in a WHERE clause defines how the specified column should be compared against the value. Here are some common SQL comparison operators:

Operator What it does = tests for equality != tests for inequality < tests for less-than > tests for greater-than <= tests for less-than or equal-to >= tests for greater-than or equal-to BETWEEN tests whether a value lies within a given range IN tests whether a row’s value is contained in a set of specified values EXISTS tests whether rows exist, given the specified conditions LIKE tests whether a value matches a specified string IS NULL tests for NULL values IS NOT NULL tests for all values other than NULL For example, if you wanted to find Irma’s shoe size, you could use the following query:

SELECT size FROM tourneys WHERE name = 'Irma';

Output+------+
| size |
+------+
| 7 |
+------+
1 row in set (0.00 sec)

SQL allows the use of wildcard characters, and these are especially handy when used in WHERE clauses. Percentage signs (%) represent zero or more unknown characters, and underscores (_) represent a single unknown character. These are useful if you’re trying to find a specific entry in a table, but aren’t sure of what that entry is exactly. To illustrate, let’s say that you’ve forgotten the favorite entree of a few of your friends, but you’re certain this particular entree starts with a “t.” You could find its name by running the following query:

SELECT entree FROM dinners WHERE entree LIKE 't%';

Output+--------+
| entree |
+--------+
| tofu |
| tofu |
+--------+
2 rows in set (0.00 sec)

Based on the output above, we see that the entree we have forgotten is tofu.

There may be times when you’re working with databases that have columns or tables with relatively long or difficult-to-read names. In these cases, you can make these names more readable by creating an alias with the AS keyword. Aliases created with AS are temporary, and only exist for the duration of the query for which they’re created:

SELECT name AS n, birthdate AS b, dessert AS d FROM dinners;

Output+---------+------------+-----------+
| n | b | d |
+---------+------------+-----------+
| Dolly | 1946-01-19 | cake |
| Etta | 1938-01-25 | ice cream |
| Irma | 1941-02-18 | cake |
| Barbara | 1948-12-25 | ice cream |
| Gladys | 1944-05-28 | ice cream |
+---------+------------+-----------+
5 rows in set (0.00 sec)

Here, we have told SQL to display the name column as n, the birthdate column as b, and the dessert column as d.

The examples we’ve gone through up to this point include some of the more frequently-used keywords and clauses in SQL queries. These are useful for basic queries, but they aren’t helpful if you’re trying to perform a calculation or derive a scalar value (a single value, as opposed to a set of multiple different values) based on your data. This is where aggregate functions come into play.


Aggregate Functions

Oftentimes, when working with data, you don’t necessarily want to see the data itself. Rather, you want information about the data. The SQL syntax includes a number of functions that allow you to interpret or run calculations on your data just by issuing a SELECT query. These are known as aggregate functions.

The COUNT function counts and returns the number of rows that match a certain criteria. For example, if you’d like to know how many of your friends prefer tofu for their birthday entree, you could issue this query:

SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu';

Output+---------------+
| COUNT(entree) |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)

The AVG function returns the average (mean) value of a column. Using our example table, you could find the average best score amongst your friends with this query:

SELECT AVG(best) FROM tourneys;

Output+-----------+
| AVG(best) |
+-----------+
| 252.8 |
+-----------+
1 row in set (0.00 sec)

SUM is used to find the total sum of a given column. For instance, if you’d like to see how many games you and your friends have bowled over the years, you could run this query:

SELECT SUM(wins) FROM tourneys;

Output+-----------+
| SUM(wins) |
+-----------+
| 35 |
+-----------+
1 row in set (0.00 sec)

Note that the AVG and SUM functions will only work correctly when used with numeric data. If you try to use them on non-numerical data, it will result in either an error or just 0, depending on which RDBMS you’re using:

SELECT SUM(entree) FROM dinners;

Output+-------------+
| SUM(entree) |
+-------------+
| 0 |
+-------------+
1 row in set, 5 warnings (0.00 sec)

MIN is used to find the smallest value within a specified column. You could use this query to see what the worst overall bowling record is so far (in terms of number of wins):

SELECT MIN(wins) FROM tourneys;

Output+-----------+
| MIN(wins) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)

Similarly, MAX is used to find the largest numeric value in a given column. The following query will show the best overall bowling record:

SELECT MAX(wins) FROM tourneys;

Output+-----------+
| MAX(wins) |
+-----------+
| 13 |
+-----------+
1 row in set (0.00 sec)

Unlike SUM and AVG, the MIN and MAX functions can be used for both numeric and alphabetic data types. When run on a column containing string values, the MIN function will show the first value alphabetically:

SELECT MIN(name) FROM dinners;

Output+-----------+
| MIN(name) |
+-----------+
| Barbara |
+-----------+
1 row in set (0.00 sec)

Likewise, when run on a column containing string values, the MAX function will show the last value alphabetically:

SELECT MAX(name) FROM dinners;

Output+-----------+
| MAX(name) |
+-----------+
| Irma |
+-----------+
1 row in set (0.00 sec)

Aggregate functions have many uses beyond what was described in this section. They’re particularly useful when used with the GROUP BY clause, which is covered in the next section along with several other query clauses that affect how result sets are sorted.


Manipulating Query Outputs

In addition to the FROM and WHERE clauses, there are several other clauses which are used to manipulate the results of a SELECT query. In this section, we will explain and provide examples for some of the more commonly-used query clauses.

One of the most frequently-used query clauses, aside from FROM and WHERE, is the GROUP BY clause. It’s typically used when you’re performing an aggregate function on one column, but in relation to matching values in another.

For example, let’s say you wanted to know how many of your friends prefer each of the three entrees you make. You could find this info with the following query:

SELECT COUNT(name), entree FROM dinners GROUP BY entree;

Output+-------------+---------+
| COUNT(name) | entree |
+-------------+---------+
| 1 | chicken |
| 2 | steak |
| 2 | tofu |
+-------------+---------+
3 rows in set (0.00 sec)

The ORDER BY clause is used to sort query results. By default, numeric values are sorted in ascending order, and text values are sorted in alphabetical order. To illustrate, the following query lists the name and birthdate columns, but sorts the results by birthdate:

SELECT name, birthdate FROM dinners ORDER BY birthdate;

Output+---------+------------+
| name | birthdate |
+---------+------------+
| Etta | 1938-01-25 |
| Irma | 1941-02-18 |
| Gladys | 1944-05-28 |
| Dolly | 1946-01-19 |
| Barbara | 1948-12-25 |
+---------+------------+
5 rows in set (0.00 sec)

Notice that the default behavior of ORDER BY is to sort the result set in ascending order. To reverse this and have the result set sorted in descending order, close the query with DESC:

SELECT name, birthdate FROM dinners ORDER BY birthdate DESC;

Output+---------+------------+
| name | birthdate |
+---------+------------+
| Barbara | 1948-12-25 |
| Dolly | 1946-01-19 |
| Gladys | 1944-05-28 |
| Irma | 1941-02-18 |
| Etta | 1938-01-25 |
+---------+------------+
5 rows in set (0.00 sec)

As mentioned previously, the WHERE clause is used to filter results based on specific conditions. However, if you use the WHERE clause with an aggregate function, it will return an error, as is the case with the following attempt to find which sides are the favorite of at least three of your friends:

SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3;

OutputERROR 1111 (HY000): Invalid use of group function

The HAVING clause was added to SQL to provide functionality similar to that of the WHERE clause while also being compatible with aggregate functions. It’s helpful to think of the difference between these two clauses as being that WHERE applies to individual records, while HAVING applies to group records. To this end, any time you issue a HAVING clause, the GROUP BY clause must also be present.

The following example is another attempt to find which side dishes are the favorite of at least three of your friends, although this one will return a result without error:

SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3;

Output+-------------+-------+
| COUNT(name) | side |
+-------------+-------+
| 3 | fries |
+-------------+-------+
1 row in set (0.00 sec)

Aggregate functions are useful for summarizing the results of a particular column in a given table. However, there are many cases where it’s necessary to query the contents of more than one table. We’ll go over a few ways you can do this in the next section.


Querying Multiple Tables

More often than not, a database contains multiple tables, each holding different sets of data. SQL provides a few different ways to run a single query on multiple tables.

The JOIN clause can be used to combine rows from two or more tables in a query result. It does this by finding a related column between the tables and sorts the results appropriately in the output.

SELECT statements that include a JOIN clause generally follow this syntax:

SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.related_column=table2.related_column;

Note that because JOIN clauses compare the contents of more than one table, the previous example specifies which table to select each column from by preceding the name of the column with the name of the table and a period. You can specify which table a column should be selected from like this for any query, although it’s not necessary when selecting from a single table, as we’ve done in the previous sections. Let’s walk through an example using our sample data.

Imagine that you wanted to buy each of your friends a pair of bowling shoes as a birthday gift. Because the information about your friends’ birthdates and shoe sizes are held in separate tables, you could query both tables separately then compare the results from each. With a JOIN clause, though, you can find all the information you want with a single query:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
JOIN dinners ON tourneys.name=dinners.name;

Output+---------+------+------------+
| name | size | birthdate |
+---------+------+------------+
| Dolly | 8.5 | 1946-01-19 |
| Etta | 9 | 1938-01-25 |
| Irma | 7 | 1941-02-18 |
| Barbara | 7.5 | 1948-12-25 |
| Gladys | 8 | 1944-05-28 |
+---------+------+------------+
5 rows in set (0.00 sec)

The JOIN clause used in this example, without any other arguments, is an inner JOIN clause. This means that it selects all the records that have matching values in both tables and prints them to the results set, while any records that aren’t matched are excluded. To illustrate this idea, let’s add a new row to each table that doesn’t have a corresponding entry in the other:

INSERT INTO tourneys (name, wins, best, size)
VALUES ('Bettye', '0', '193', '9');

INSERT INTO dinners (name, birthdate, entree, side, dessert)
VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream');

Then, re-run the previous SELECT statement with the JOIN clause:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
JOIN dinners ON tourneys.name=dinners.name;

Output+---------+------+------------+
| name | size | birthdate |
+---------+------+------------+
| Dolly | 8.5 | 1946-01-19 |
| Etta | 9 | 1938-01-25 |
| Irma | 7 | 1941-02-18 |
| Barbara | 7.5 | 1948-12-25 |
| Gladys | 8 | 1944-05-28 |
+---------+------+------------+
5 rows in set (0.00 sec)

Notice that, because the tourneys table has no entry for Lesley and the dinners table has no entry for Bettye, those records are absent from this output.

It is possible, though, to return all the records from one of the tables using an outer JOIN clause. In MySQL, JOIN clauses are written as either LEFT JOIN or RIGHT JOIN.

A LEFT JOIN clause returns all the records from the “left” table and only the matching records from the right table. In the context of outer joins, the left table is the one referenced by the FROM clause, and the right table is any other table referenced after the JOIN statement.

Run the previous query again, but this time use a LEFT JOIN clause:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
LEFT JOIN dinners ON tourneys.name=dinners.name;

This command will return every record from the left table (in this case, tourneys) even if it doesn’t have a corresponding record in the right table. Any time there isn’t a matching record from the right table, it’s returned as NULL or just a blank value, depending on your RDBMS:

Output+---------+------+------------+
| name | size | birthdate |
+---------+------+------------+
| Dolly | 8.5 | 1946-01-19 |
| Etta | 9 | 1938-01-25 |
| Irma | 7 | 1941-02-18 |
| Barbara | 7.5 | 1948-12-25 |
| Gladys | 8 | 1944-05-28 |
| Bettye | 9 | NULL |
+---------+------+------------+
6 rows in set (0.00 sec)

Now run the query again, this time with a RIGHT JOIN clause:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
RIGHT JOIN dinners ON tourneys.name=dinners.name;

This will return all the records from the right table (dinners). Because Lesley’s birthdate is recorded in the right table, but there is no corresponding row for her in the left table, the name and size columns will return as NULL values in that row:

Output+---------+------+------------+
| name | size | birthdate |
+---------+------+------------+
| Dolly | 8.5 | 1946-01-19 |
| Etta | 9 | 1938-01-25 |
| Irma | 7 | 1941-02-18 |
| Barbara | 7.5 | 1948-12-25 |
| Gladys | 8 | 1944-05-28 |
| NULL | NULL | 1946-05-02 |
+---------+------+------------+
6 rows in set (0.00 sec)

Note that left and right joins can be written as LEFT OUTER JOIN or RIGHT OUTER JOIN, although the OUTER part of the clause is implied. Likewise, specifying INNER JOIN will produce the same result as just writing JOIN.

As an alternative to using JOIN to query records from multiple tables, you can use the UNION clause.

The UNION operator works slightly differently than a JOIN clause: instead of printing results from multiple tables as unique columns using a single SELECT statement, UNION combines the results of two SELECT statements into a single column.

To illustrate, run the following query:

SELECT name FROM tourneys UNION SELECT name FROM dinners;

This query will remove any duplicate entries, which is the default behavior of the UNION operator:

Output+---------+
| name |
+---------+
| Dolly |
| Etta |
| Irma |
| Barbara |
| Gladys |
| Bettye |
| Lesley |
+---------+
7 rows in set (0.00 sec)

To return all entries (including duplicates) use the UNION ALL operator:

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners;

Output+---------+
| name |
+---------+
| Dolly |
| Etta |
| Irma |
| Barbara |
| Gladys |
| Bettye |
| Dolly |
| Etta |
| Irma |
| Barbara |
| Gladys |
| Lesley |
+---------+
12 rows in set (0.00 sec)

The names and number of the columns in the results table reflect the name and number of columns queried by the first SELECT statement. Note that when using UNION to query multiple columns from more than one table, each SELECT statement must query the same number of columns, the respective columns must have similar data types, and the columns in each SELECT statement must be in the same order. The following example shows what might result if you use a UNION clause on two SELECT statements that query a different number of columns:

SELECT name FROM dinners UNION SELECT name, wins FROM tourneys;

OutputERROR 1222 (21000): The used SELECT statements have a different number of columns

Another way to query multiple tables is through the use of subqueries. Subqueries (also known as inner or nested queries) are queries enclosed within another query. These are useful in cases where you’re trying to filter the results of a query against the result of a separate aggregate function.

To illustrate this idea, say you want to know which of your friends have won more matches than Barbara. Rather than querying how many matches Barbara has won then running another query to see who has won more games than that, you can calculate both with a single query:

SELECT name, wins FROM tourneys
WHERE wins > (
SELECT wins FROM tourneys WHERE name = 'Barbara'
);

Output+--------+------+
| name | wins |
+--------+------+
| Dolly | 7 |
| Etta | 4 |
| Irma | 9 |
| Gladys | 13 |
+--------+------+
4 rows in set (0.00 sec)

The subquery in this statement was run only once; it only needed to find the value from the wins column in the same row as Barbara in the name column, and the data returned by the subquery and outer query are independent of one another. There are cases, though, where the outer query must first read every row in a table and compare those values against the data returned by the subquery in order to return the desired data. In this case, the subquery is referred to as a correlated subquery.

The following statement is an example of a correlated subquery. This query seeks to find which of your friends have won more games than is the average for those with the same shoe size:

SELECT name, size FROM tourneys AS t
WHERE wins > (
SELECT AVG(wins) FROM tourneys WHERE size = t.size
);

In order for the query to complete, it must first collect the name and size columns from the outer query. Then, it compares each row from that result set against the results of the inner query, which determines the average number of wins for individuals with identical shoe sizes. Because you only have two friends that have the same shoe size, there can only be one row in the result set:

Output+------+------+
| name | size |
+------+------+
| Etta | 9 |
+------+------+
1 row in set (0.00 sec)

As mentioned earlier, subqueries can be used to query results from multiple tables. To illustrate this with one final example, say you wanted to throw a surprise dinner for the group’s all-time best bowler. You could find which of your friends has the best bowling record and return their favorite meal with the following query:

SELECT name, entree, side, dessert
FROM dinners
WHERE name = (SELECT name FROM tourneys
WHERE wins = (SELECT MAX(wins) FROM tourneys));

Output+--------+--------+-------+-----------+
| name | entree | side | dessert |
+--------+--------+-------+-----------+
| Gladys | steak | fries | ice cream |
+--------+--------+-------+-----------+
1 row in set (0.00 sec)

Notice that this statement not only includes a subquery, but also contains a subquery within that subquery.


Conclusion

Issuing queries is one of the most commonly-performed tasks within the realm of database management. There are a number of database administration tools, such as phpMyAdmin or pgAdmin, that allow you to perform queries and visualize the results, but issuing SELECT statements from the command line is still a widely-practiced workflow that can also provide you with greater control.


Learn More

MySQL Databases With Python Tutorial

SQL vs NoSQL or MySQL vs MongoDB

Learn NoSQL Databases from Scratch - Complete MongoDB Bootcamp 2019

The Complete SQL Bootcamp

The Complete Oracle SQL Certification Course

SQL for Newbs: Data Analysis for Beginners

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

Originally published by Mark Drake at https://www.digitalocean.com

Sharded MySQL on Kubernetes

Sharded MySQL on Kubernetes

Sharded MySQL on Kubernetes: Vitess has continued to evolve into a massively scalable sharded solution for the cloud. It's is now used for storing core business data for companies like Slack, Square, JD.com, and many others. This webinar will cover the high-level features of Vitess with a focus on what makes it cloud-native. We'll conclude with a demo of the powerful materialized views feature that most sharded systems have yet to solve.

Vitess has continued to evolve into a massively scalable sharded solution for the cloud. It's is now used for storing core business data for companies like Slack, Square, JD.com, and many others. This webinar will cover the high-level features of Vitess with a focus on what makes it cloud-native. We'll conclude with a demo of the powerful materialized views feature that most sharded systems have yet to solve.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading about MySQL on Kubernetes

An illustrated guide to Kubernetes Networking

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

An Introduction to Kubernetes

Google Kubernetes Engine By Example

SQL vs NoSQL or MySQL vs MongoDB

An Introduction to Queries in MySQL