What is MySQL? A Beginner’s Guide to SQL Databases
So, what is “SQL”? SQL stands for “Structured Query Language,” pronounced “ess-que-el.” Database developers use SQL to interact with their databases, creating new data sets, modifying existing data sets, and deleting data as needed. SQL is not only a simple language (with easily understood commands), but there are also many varieties of SQL. MySQL, Microsoft SQL, and PostgreSQL are some of the most popular SQL platforms.
In this article, we will be talking specifically about MySQL, its uses, and its relationship with SQL syntax. Originally developed in 1995, MySQL is one of the leading SQL technologies with 53.5 percent of the SQL database market share. Particularly popular for web applications, MySQL is free to use and often integrated into open source packages.
What is “Data” in MySQL?
To understand MySQL, we first need to understand how it manipulates and stores data. But first, what is data, and why is it important?
One of the easiest ways to understand data in MySQL is to consider a simple user account. To create an account on a user site, a user needs to provide their:
- Email address
Last Updated April 2022
Become an In-demand SQL Master by creating complex databases and building reports through real-world projects | By Colt Steele, Ian SchoonoverExplore Course
The website needs a way of storing this data so that it can look up the user’s name and see whether their password is correct. They also need to be able to look up the user’s email address and see what the user’s name is. In other words, this data has to be connected.
Connecting data in this fashion means that you’re able to update someone’s password even if you only know their email address — or vice versa. In web applications, it’s common for this process to be automated so that the software solution interacts with the database and the database interacts with the data.
This form of data management is known as a relational database, of which MySQL is the most popular.
What is a Relational Database?
There are both relational databases and non-relational databases. MySQL is a relational database. Today, non-relational databases are growing, but they have very specific use cases.
Relational databases like SQL store data in a way that each piece of data has a relationship with each other. A single database contains tables, each of which is very much like a spreadsheet with lines of information that each comprise a record. Each singular record or entry contains a set of data.
A list of books would be a relational database, as would a list of movies. A list of user accounts or items for sale, for example, are relational databases. If you can imagine it on an Excel spreadsheet, it’s a relational database.
Comparatively, a non-relational database, such as NoSQL, is an unstructured database. Rather than an Excel sheet, think of a folder that contains an assortment of documents, videos, and images. It still contains data, but this data is disorganized, and there are no relationships between the types of data. Most use unstructured databases along with machine learning solutions, which can comb through unrelated, unstructured data more easily than a human ever could.
Both types of databases operate on a client-server model. There is a database that serves the data and a client that requests it. But most web applications are going to be using a relational database like MySQL rather than a non-relational database like NoSQL.
In this article, we’re going to be discussing a simple user table. Within this table would be multiple users, and we would need to have some general information for all of them. Each user would be a separate entry in a table, like a separate line of a spreadsheet.
Let’s take a look at the table:
This is a very simplified example. In real life, for instance, JohnDoe and JaneDoe’s passwords would be encrypted, rather than being stored in plain text. But it shows us that we have different name columns and email columns and that we have a JohnDoe row as well as a JaneDoe row.
But while the structure may be similar to Excel, there’s a very specific difference — how the data interacts.
The Four Basic SQL Operations, or CRUD
There are many SQL commands, but there are four general operations:
- Create – filling data into tables
- Read – query data out of a table
- Update – change data already in a table
- Delete – remove data from the table
The first letters of these basic SQL operations create the acronym CRUD. These operations are thought to be a fundamental set of four basic functions or features that every database must have. Believe it or not, this is most of what you would ever need to create and use a database.
By covering this for basic features, we will cover basic and most important SQL commands: CREATE, INSERT, SELECT, UPDATE, DELETE, and DROP.
When developing a system, you may be interfacing directly with the database through the MySQL console or MySQL workbench. But during development, you will often be inserting your SQL queries through source code. Different programming languages interface with databases in different ways, but all will work with a SQL server.
Either way, most programmers will find it fairly easy to get started with SQL, though mastering it can take a lifetime of learning. Along with the basic commands we will discuss, there are also issues of optimization and resource usage (ensuring that the commands you use are the least resource-intensive).
CREATE: Adding Data to MySQL
First, we need to create a database. We make these MySQL databases with the following command:
CREATE DATABASE example;
In this case, we’ve created a database with the name “example.” Before we can create a table, we would need to:
But that’s just creating the database. In SQL, all the data itself is inside of tables. In SQL, we would create a simple table of users as such:
CREATE TABLE users (name varchar(25), password varchar(255), email varchar(25);
MySQL now knows to expect related data that includes a name, password, and email address. Much like in a spreadsheet, this data will be associated line by line so that MySQL will always know that the name, password, and email of a given account is connected.
In the example above, “varchar” is the type of data being stored. It refers to a variable number of characters, up to the parenthetical amount. In the above example, the password will likely be an encrypted hash. Other types include:
- Int, smallint, mediumint, or bigint, for integers.
- Date, datetime, or timestamp, for date information.
But there are many types of MySQL data. The above example will create a single table within a database. A database usually consists of many tables. But that’s just storing the data. The data still has to be able to be manipulated and accessed.
To add information into a database, you would use the INSERT command.
INSERT into users (name, password, email) VALUES (‘JohnDoe’,’StrongPassword’, ‘[email protected]’);
Now the table looks like:
But the advantage to storing data this way is that you could store five users or 50,000 users, yet you would always be able to read your data in the same way.
Note that the same database can contain many tables. Our “example” database currently just contains users. But it could likewise contain something like “items_for_sale” or “pictures_of_users_cats.”
Reading Data from MySQL
Now that we have saved data into the database, we can now turn it into a query. Let’s start with a simple SELECT statement.
SELECT email FROM users WHERE name = ‘JohnDoe’;
The SELECT/FROM/WHERE is the most popular and simple MySQL query. In this case, it will return a single record, the one we created, which is [email protected].
But let’s say there were 25,000 records. We could also:
SELECT email FROM users;
Note that we have no conditions here, which means it will give us the full list of 25,000 emails in the database. We could also:
SELECT email FROM users LIMIT 100 ORDER BY name;
This would give us only the first 100 alphabetically by the user’s name. Additionally, we could use a wild card:
SELECT email FROM users WHERE email LIKE ‘%.edu’;
The % sign is a wildcard. This would pull all email addresses that end in .edu. In our current database, it would return nothing because we only have a .com email address. When we use wildcards, we need to use LIKE rather than an = sign.
In addition to =, we can also compare data in a number of other ways:
- < or > for less than or greater than values.
- <= or >= for less than or greater than or equal to values.
- BETWEEN for values that are between two values.
- LIKE for values that are similar to our values.
- IN for values inside of a set.
So, while SQL language is syntactically simple, it is powerful because it has an impressive number of tools at its disposal. Using the commands LIMIT and ORDER BY makes it even more possible to control the data that returns to you.
Updating Data in MySQL
Placing data into a MySQL database would be useless if we weren’t allowed to update data. Updating data operates much like selecting data. While you use the “update” statement, the “where” condition, which filters out which data you update, will be the same as the “select” statement.
Here’s an example:
UPDATE users SET email = ‘[email protected]Doe.edu’ where name = ‘JohnDoe’;
In this example, John has realized that he made a mistake. His email address is actually [email protected], not [email protected]. Life is confusing for a theoretical amalgam. But we’re able to fix this easily by using the UPDATE function. The SET syntax says that we’re setting the email address and the WHERE syntax defines under what conditions.
We could likewise:
UPDATE users SET email = ‘[email protected]’;
But that would be a terrible move because that would set all users to the same email address. In this scenario, only John is in our experimental database so that it wouldn’t matter. But in a real database, it would be a big problem.
If we were feeling particularly chaotic, we could:
UPDATE users SET password = null WHERE name LIKE ‘J%’;
What would that do? Well, it would make every user’s password blank if their name started with J, for starters. In this situation, it would delete JohnDoe’s password entirely.
Deleting Data from MySQL
Finally, you might need to delete data. Before we do this, a brief discussion:
In general practices, most never delete data, opting to archive data instead. So while it’s possible to delete data from a table or a database, it’s rarely done these days because data storage is now very cheap. If you want to optimize a program and save on resources, intelligent deletion is key. But at the same time, many applications archive information rather than deleting it.
That said, deleting can be necessary. And it’s done with the DELETE statement.
DELETE FROM users WHERE name = ‘JohnDoe’;
Goodbye John! We’ve now deleted our only user (we never even added Jane). We could have also:
DELETE FROM users WHERE email = ‘[email protected]’;
Or if we wanted to be extra careful:
DELETE FROM users WHERE name = ‘JohnDoe’ && email = ‘[email protected]’ && password = ‘StrongPassword’;
Or if we wanted to be more comprehensive:
DELETE FROM users WHERE name = ‘JohnDoe’ || email = ‘[email protected]’ || password = ‘StrongPassword’;
That would delete from users if a name were JohnDoe or an email address was ‘[email protected]’ or a password was StrongPassword, but poor John meets all these criteria regardless.
We can also delete a table entirely. At this point, our users’ table is not really helping us at all, especially since we’ve deleted everything from it! So let’s go ahead:
DROP TABLE users;
And our table is gone, just like that. Now, that’s not like deleting all the rows within the table; the entire table and its architecture is gone. If we had wanted to delete all users from the table, we could have simply:
DELETE FROM users;
While that would have deleted all the data from the table, it wouldn’t have deleted the table itself, and the table would still be ready to go. This is often done during testing to wipe the table clean so that you can fill in the correct data.
According to ZipRecruiter, the average MySQL developer makes upwards of $110,000 a year. In this article, we’ve taken a look at the four most fundamental database functions of MySQL:
- Creating new data: CREATE TABLE, INSERT INTO.
- Reading existing date: SELECT.
- Updating existing data: UPDATE.
- Deleting existing data: DELETE, DROP.
These basic SQL commands exist in virtually every version of SQL, not just MySQL. But MySQL is the most popular platform for SQL usage.
While these basic SQL commands may provide for a significant amount of data manipulation, they aren’t every covered command. Not only does SQL contain many more commands, but there are many variations, data sets, and formulas that can make it even easier to use. That said, these basics should give even a novice developer a lot to work with — whether they are just starting out with databases, trying to learn SQL, or trying to use the MySQL platform.
Top courses in MySQL
MySQL students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.