If you want to learn MySQL, you came to the right place. In this extensive MySQL tutorial, we will cover everything you need to work with this database. Besides that, we will see how to be a pro with it by covering some optimization and design techniques.
In this MySQL tutorial, we will focus only on the MySQL part. Instead, we won’t cover how to install MySQL. If you are looking for that, you should first read this get started with MySQL guide. Otherwise, if MySQL is already on your system, just read on.
We divide this ultimate MySQL Tutorial into the following sections.
- Introduction to MySQL
- Queries: Database and Tables
- Queries: Records
- How to design a MySQL Database
- Final thoughts
Without further indulging, it is time to start this MySQL tutorial!
Introduction to MySQL
In this section, we will give you a general introduction to MySQL. It is important you read that because it will help you better understand the other sections.
What is MySQL?
MySQL is a completely free Relational Database Management System (RDBMS). This acronym may sound complex, but it isn’t. We can break it down in the following points.
- MySQL is a database, a system to store digital pieces of data generally related to one another. You can virtually store any kind of data in a database, text, images, documents, whatever.
- It is a relational database. This is just a type of database and indicates the way the database work. For a relational database, as you can imagine, relationships are quite important (more on that later).
- In reality, MySQL is not just a database. Instead, it is a database management system. This is because it provides you with everything you need to manage your data. The database is only the place where you store the data, but MySQL also provides you a language to interact with the database. Furthermore, it provides some additional optimizations under the hood that we will see later.
Now, there are many relational databases out there. The thing is, they all have literally the same concepts. They mainly differ in the language you have to interact with the data. However, even here, they are quite similar.
In the introduction to MySQL, we will see how relational databases work, then in the following sections, we will focus on the MySQL-specific syntax.
Relational Databases
A relational database is a structure to hold data. In fact, you have to define such a structure before you can start storing data in it. Typically, you use a database to store many things of the same type, so it makes sense to have a pre-defined structure.
Imagine literally any application that works with accounts. You have many different accounts, but each with the same kind of information: email, password, name, credit card number, and so on. It makes sense to define a structure beforehand that applies to all the accounts.
In a relational database, we do it by using tables. Each table represents a type of object (e.g. the account mentioned above, a post in a blog, a comment to a post, etc.). However, we simply cannot represent all the information just with tables.
Relationships between tables allow us to represent complex data. For example, we may have a table for blog posts and another table for comments to blog posts. We need to somehow relate these two tables, and this is possible in MySQL.
With this little introduction, we can now introduce you to the main components of MySQL: tables, columns, records, and keys.
Table, Column, Record, Keys
A table is a structure to hold many items of the same type. All the items in the table have the same information. For example, consider the table “posts” that will contain all our blog posts.
A column is a field within a table we use to describe a propriety of the object we want to represent in the table. For example, in our posts table, we may have many columns: title, short description, content. For a table to exist, it must have at least one column.
Instead, a record is a row in a table and represents an actual object of that type. In our example table posts, we have one row (a record) for each post we have. To continue our example, we might have our first post that has “the ultimate MySQL tutorial” as title, “learn MySQL in this complete guide” as a short description and the whole body as content.
Everything is straightforward so far. It is time to introduce a special type of column, the key. MySQL uses keys to reference tables and to enable a faster search. We mainly have three types of keys: indexes, primary keys, and foreign keys.
Indexes
An index is a special column allowing MySQL to search faster for a given record in your table. You can take any existing column you already have in your table, and tell MySQL to treat that as an index.
MySQL will do some behind-the-scenes work to make you happy. From now on, searching for that column will be faster. Before diving into how this is possible, it is worth making an example.
Imagine you have a table to store invoices, and you realize you often search invoices by issue date. To improve your speed, you convert your “issue date” column into an index, and now everything is much faster.
Under the hood, you are sacrificing a little bit of disk and memory to save CPU time (generally a good thing). That is because MySQL builds an index table (that you don’t see) for that column. That table acts as the index of a book. For searches, MySQL will look in that table and once it has identified the record in that table, it will go in the main table to actually fetch it. Pretty much the same way you look for something in the index of a book and then you go straight to the page.
The Primary Key
The primary key is a column or combination of columns, you want to use to identify a record in a unique way. In other words, two records in a table cannot have the same primary key value. If in your posts table your primary key is the title, then you can’t have two posts with the same title.
A common practice is to add a dedicated column for the primary key. That column is generally a number with automatic increment, meaning that every time you add a new record MySQL assigns a new unique primary key.
Why bother with primary keys? Isn’t it just a limit for our database? Yes, but it is a limit that enables relationships. The fact of having primary keys means that you can reference each record unequivocally. So, if you want to associate comments to posts, you can associate each comment with a specific post, with no doubt.
The Foreign Key
The foreign key is a column that contains the value of the primary key of another record. Such other records may be in another table, but also in the same table.
We can continue the example of posts and comments. Each post has a numeric ID and each comment references such ID in its “post” foreign key column. In this way, you can quickly find the post each comment belongs to. On the other way, you can also quickly find all the comments on a given post, by searching for the foreign key.
In short, foreign keys allow relationships. Yet, we have mainly three types of relationships.
One-to-One Relationships
In a one-to-one relationship, a record references only one other record. You do that by adding a foreign key on one record referencing the other. There is no constraint here, you can add such foreign key on either one table or the other, it makes no difference.
You rarely use this relationship, as we will see. An example of that is having a separate table to store some specific information about another record in another table. For example, you may have your “account” table and store payment information in another “payment information” table. One account can have only one payment information, and one payment information must belong only to one account.
One-to-Many Relationships
In a one-to-many relationship, the record on one side can have multiple references to the record on the other side. You do that by adding the foreign key on the “many” side.
The classical example here is posts and comments. Each post can have multiple comments, but each comment can belong only to one post. Thus, the post is the “one” and the comment is the “many”. In the comments table, you add a column to reference the post of belonging.
You can consider this a “belongs to” relationship (e.g. a comment belongs to a post).
Many-to-Many Relationships
In a many-to-many relationship, the item on one side can have multiple references to the items on the other side. However, the same is true also in the opposite direction. Things get trickier because you can’t represent this relationship with a simple foreign key.
Instead, we need to use a service table that will simply hold two foreign keys: one on one side, and one on the other side.
An example of that could be posts and authors. Multiple authors can work on the same post, and every single author can work on many posts. Thus, we add an additional table that we could call “post_author”, and that will contain two foreign keys: one for the post, for the author.
Queries: Database and Tables
Now that we have a solid understanding of the logic behind our database, we can start modeling it. We will do it by using queries, instructions to the database engine.
What is a MySQL Query?
A MySQL Query is an instruction you send to your database engine in a special language. With such instruction, you tell your database to do something: create a new database, create tables, manipulate records, and so on.
As we have seen before, MySQL wants you to define your database structure first. This means that, before we can start working with records, we need to work with databases and tables. We will see just that in this section.
Note: to give MySQL instructions, you need to access the MySQL CLI. You can do that with mysql -u root -p command on Linux, or from the CLI embedded in phpMyAdmin on Windows. If you are not sure about it, read how to open the MySQL CLI.
Working with databases
We can have multiple databases in our MySQL installation. In this section, we will see how to work with them.
Creating a database
Creating a database to work with is the first thing we can do. Luckily, this is extremely simple with the CREATE DATABASE instruction. We only need to provide a database name. Use the following query to create a database that we name “test”.
CREATE DATABASE test;The full syntax allows you to specify some more options. You can add an “if not exists” to check if the database already exists before proceeding. You can also set the character set to use, in case you want to use special characters in your database (e.g. Chinese, Russian). You can also set the collation, which is the strategy MySQL will use to compare characters and sort alphabetically. Nothing we need to worry about unless in special cases.
Below, the full syntax for this query. Parts within squared brackets are optional, and – should you decide to use them – you don’t need to write the square brackets.
CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET set_name]
[COLLATE collation_name];Using a database
Since you can have multiple databases in your system, before you can do anything inside a database you need to tell MySQL which database you want to use.
This is as simple as it can get, you need the USE command and the database name. In our case, we are going to use our “test” database we just created.
USE test;Deleting a database
At some point, you may want to delete a database because you don’t need it anymore. This is also easy, we just need to use the DROP DATABASE instruction. Of course, this will give an error if the database does not exist. To avoid that, we can add “if exists”. The syntax of the command is below.
DROP DATABASE [IF EXISTS] database_name;Modify the database structure
Now we have one database (test) and we are inside it (we are using it). It is time to work with tables and the structure of the database itself.
View existing tables
The first thing you may want to do is check the tables you already have in your database. You can do that with the following command.
SHOW TABLES;However, since we just created our database, we won’t have any tables in it. Thus, this command will yield an empty result.
We can also get information about an existing table with the following command.
DESCRIBE table_name;Creating tables
It is time to create some tables. As you can imagine by now, the command we are going to use is CREATE TABLE. This command wants to know the table name and then a set of column definitions: the list of columns we want to have inside our table. In addition to that, we also need to provide any constraint, that are the foreign key definitions.
The syntax for this command is the one below.
CREATE TABLE [IF NOT EXISTS] table_name(
column_definiton_1,
column_definition_2,
column_definition_3,
constraint_1,
constraint_2,
...
)The command is fairly simple. The complexity lays in the column definitions and constraint definition, but as we will see they are not too complex either.
A column definition is simply the name of the column, followed by the type (e.g. number, text), followed by additional constraints. Such constraints may be that the column cannot be empty, default value if none is provided when adding records, or even that the column is the primary key and has automatic increment. We can summarize this column definition as follow.
column_name TYPE [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] [PRIMARY_KEY]Below, an example of a valid statement to create a table to hold posts. It will have a numeric id as primary key, a title of variable length up to 255 characters (varchar), and a text body. As you can see, the title cannot be empty (not null).
CREATE TABLE IF NOT EXISTS posts(
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
body TEXT
);With this, we are all set to create tables, we only need to see what are the column types. However, we still need to dive a little deeper. Specifically, we need to see how to define foreign keys in a creation statement, and how to define primary keys over multiple columns.
Column types
When we create a table, we need to define all the columns and what value they will hold. To do that, we need to specify the column type. We have many options, below the most common ones.
| Type | Description |
|---|---|
TINYINT | 1-byte integer |
SMALLINT | 2-bytes integer |
MEDIUMINT | 3-bytes integer |
INT | 4-bytes integer |
BIGINT | 8-bytes integer |
DECIMAL(P, D) | A fixed-point number. You have to provide P, which is the total number of digits you want, and D, the number of digits you want after the point. |
FLOAT | Single-precision floating-point number. |
DOUBLE | Double-precision floating-point number. |
BIT | A bit value. |
For all the numeric types, you can add UNSIGNED before the definition to do not allow the storage of negative numbers. This will help you optimize storage space if you already know numbers can be exclusively above zero (like in a primary key with auto-increment).
| Type | Description |
|---|---|
CHAR(L) | A string of characters with a fixed length of L characters. |
VARCHAR(L) | A string of characters that has a variable length, up to L characters. |
BINARY(L) | A binary string with a fixed length of L characters. |
VARBINARY(L) | A binary string with a variable length, up to L characters. |
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB | Binary fields with different sizes. |
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT | Text fields with different sizes. |
ENUM('val1', 'val2', ...) | Enumeration, a field that can assume only one of the values provided. |
SET | A set of values. |
| Type | Description |
|---|---|
DATE | A date (YYYY-MM-DD). |
TIME | A time of the day (hh:mm:ss) |
DATETIME | A date and time of the day (YYYY-MM-DD hh:mm:ss) |
Modify existing MySQL tables
To modify an existing table, we need to use ALTER TABLE. After that, we can continue with different instructions. We can either add (ADD) a new column, or modify (MODIFY) an existing one. Instead, if we want to simply rename a column, we can use CHANGE COLUMN. Instead, to remove a column or a field, use DROP. Below, the generic syntax and some examples.
ALTER TABLE table_name
ADD column_name column_definition
[AFTER column_name | FIRST],
MODIFY column_name column_definition,
CHANGE COLUMN old_name new_name column_definition,
DROP column_name,
DROP PRIMARY KEYWith this example, we add the slug column to our table.
ALTER TABLE posts
ADD
slug VARCHAR(255) NOT NULL DEFAULT "no-slug"
AFTER title;With this other example, we convert our existing body from text to LONGTEXT.
ALTER TABLE posts
MODIFY body LONGTEXT;The primary key
We already know how to set a primary key to a single field with auto-increment when creating a table. However, we may want to use as primary key two or more fields together. This is common when you have a support table to create a many-to-many relationship.
The syntax in the table creation is simple and takes the place of a column definition:
PRIMARY KEY (column_1, column_2, ...)To see this in action, we can create a simple authors table as follow.
CREATE TABLE IF NOT EXISTS authors(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255) NOT NULL
);Then, we can create our support table to make this many-to-many relationship. Since this is just a support table, we don’t need any additional fields to use as a primary key. We can use the reference to the post together with the one to the author as the primary key.
CREATE TABLE IF NOT EXISTS posts_authors(
author_id INT NOT NULL,
post_id INT NOT NULL,
PRIMARY KEY (author_id, post_id)
);This is a good example of how to set a complex primary key. However, this table is flawed: we need to define the foreign keys. We will do that later on.
If you need to modify an existing primary key, you can use the following syntax.
ALTER TABLE table_name DROP PRIMARY KEY, ADD PRIMARY KEY (column_1, column_2, ...)Creating indexes
Creating an index on a column will make searching on that column faster. It is quite easy to do, just follow the following syntax.
CREATE INDEX index_name ON table_name(column_name)Remember that the index name must be unique within the database, not within the single table. Thus, as a best practice, include the table name in the index name. Below, an example if we want to create an index on the post slug.
CREATE INDEX posts_slug ON posts(slug);To remove an index, we simply use DROP INDEX by specifying the index name and the table it references.
DROP INDEX posts_slug ON posts;References and foreign keys
Basic syntax
And now the most beautiful yet ugliest part of MySQL, or SQL in general. Foreign keys, a way to reference other tables. Having foreign keys is crucial because it allows faster queries between tables.
We define foreign keys in a way that is quite similar to primary keys. However, here we need to specify which table we are referencing, and which column on that table.
To make things clearer, we can go back to our posts_authors example table. This table contains two columns: post_id and author_id. The first (post_id) references the column id in the “posts” table, while the second (author_id) references the column id in the “authors” table.
The syntax to define a foreign key is simple, we can use it in both ALTER and CREATE statements, as follows.
FOREIGN KEY (column_name)
REFERENCES other_table_name (column_in_other_table_name)
[ON UPDATE reference_option]
[ON DELETE reference_option]The first two rows are easy to understand: we specify the column name on the current table and the column to reference on the destination table. The last two statements are optional, and tell how the database should behave in specific conditions.
The constraints: ON UPDATE, ON DELETE
ON UPDATE indicates what to do if the referenced record is updated. For example, what happens to the posts_authors table if we change the value of ID on an author that is referenced from post_authors? We have several reference options:
RESTRICT(the default) will prevent the change.NO ACTIONwill also prevent the change.CASCADEis generally the best way to go. It will update the reference in posts_authors as well, automatically.SET NULLwill set to null the reference in posts_authors. However, for that to work, the reference must be nullable, which isn’t always the case. In our example, in fact, we have created our table so that it cannot contain null values, so we cannot use this one.
ON DELETE, as you can imagine by now, indicates what to do if the referenced record is deleted. So, what if we delete an author or a post? Also here we have the same reference options:
RESTRICT(the default) will prevent the deletion.NO ACTIONwill also prevent the deletion.CASCADEwill delete the record in posts_authors as well. This may be okay in our specific case, but it is dangerous in some cases (particularly one-to-one and one-to-many relationships). You risk deleting something you don’t want to delete.SET NULLwill set the reference to null, and it is probably the best way to go if you are unsure.
Foreign keys in ALTER and CREATE
Below an example of defining foreign keys on an existing table.
ALTER TABLE posts_authors
ADD FOREIGN KEY (author_id) REFERENCES authors (id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
ADD FOREIGN KEY (post_id) REFERENCES posts (id)
ON UPDATE CASCADE
ON DELETE RESTRICT;We can also use the same logic when creating a table.
CREATE TABLE IF NOT EXISTS posts_authors(
author_id INT NOT NULL,
post_id INT NOT NULL,
PRIMARY KEY (author_id, post_id),
FOREIGN KEY (author_id) REFERENCES authors (id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
FOREIGN KEY (post_id) REFERENCES posts (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);Deleting a table
Deleting a table is easy, provided there are no references to it. If they are, you first need to delete the foreign keys that point to that table with ALTER. For example, you can use the following statement to delete references to the “authors” table. This doesn’t mean you will delete the columns, but only the references.
ALTER TABLE posts_authors DROP FOREIGN KEY (author_id);Once this is done, you can use the DROP TABLE statement to remove a table.
DROP TABLE [IF EXISTS] table_name;Queries: Records
At this point, we have a basic database with three tables: posts, authors, and posts_authors. We also have a little nice many-to-many relationship. However, our database is just an empty structure: it holds no data. It is time to add some records in it, and then start manipulating them.
Adding records
The query to add a new record into a table is INSERT. We need to provide the table name where we want to insert data and, within brackets, the value we need to add. We can add multiple records at a time by using multiple sets of data, always within brackets. Here you have the syntax.
INSERT INTO table_name
(columns_list)
VALUES
(set_of_values_record_1),
(set_of_values_record_2),
...Besides the name of the destination table, we also need to provide a column list. This is because we need to tell MySQL which columns we are populating. As you can imagine, we don’t need to populate all the columns, because we can leverage the default values.
Then, we define the values each record will have for the chosen columns. Each record is within brackets. Remember, you can write numbers as they are, but strings and text go within quotes (single or double).
In the example below, we start by adding three posts.
INSERT INTO posts
(title, slug, body)
VALUES
("An example post", "an-example-post", "Some random body"),
("My second post", "2nd-post", "Text..."),
("Hello MySQL!", "hello-mysql", "I love MySQL");We purposefully omitted the “id” column, which is the primary key. Since it has auto-increment, MySQL will take care of it for us, automatically. Do something similar for authors and for authors_posts on your own!
Searching for records
At this point, we should have some records in our database. We have at least three posts, and possibly a couple of authors that reference the posts. We can start to search for some records.
Basic search with WHERE
To search for a record, we need to select id from all the records. It is because of it that the query is SELECT. In this query, we need to provide the list of columns we want to see, the table we are searching into, and a WHERE statement. We use WHERE to indicate which records we want to see. If we omit that, we will get all the records. The syntax is the one below.
SELECT
column_1, [column_2, ...]
FROM table_name
[WHERE condition];If we want to see all columns, instead of providing all their names, we can use a star (*). For example, to get all the posts, we can use the following query.
SELECT * FROM posts;A simple condition is “column equals value”. For example, we can get the post with the first ID as follow.
SELECT * FROM posts WHERE id = 1;We can use all the standard comparison operators, like != (not equal), >, <, >= and <=. In addition, we can chain multiple conditions with AND (they must be true at the same time), and with OR (either one must be true).
SELECT * FROM posts WHERE id = 1 OR slug = "2nd-post";Using IN
With IN, we can use a range to search for a record. For example, if we want to get posts with ID 1, 2, and 3, instead of using 3 “OR” statements, we can use the following.
SELECT * FROM posts WHERE id IN (1, 2, 3);Using LIKE
Sometimes we don’t know exactly which record we are looking for. Particularly in our case where we have posts in the database, we may want to search for some text. For example, we may want to search for all posts containing the “MySQL” keyword in their bodies. The thing is, we don’t know where in the body they have such a keyword. They may have at the beginning, at the end, or most likely somewhere in the middle.
The LIKE operator comes to the rescue. It basically tells MySQL “I am looking for a record where this field is like this”. Of course, you need to provide a hint.
After the LIKE operator, we write a string that resembles what we are searching for. We can use % to represent any number of undefined characters (including zero), and ? to represent one undefined character. So, the string "%mysql%" will basically mean “everything that contains mysql at least once”.
SELECT * FROM posts WHERE body LIKE "%mysql%";Querying multiple tables
Querying a single table is great, but you can’t go much far with it. Modern applications require you to model complex relationships and then query against them. For example, we may want to know all the posts an author has written.
We need to query against multiple tables at the same time and orchestrate that well. This adds more complexity, but nothing we cannot manage. Take a look at the following query.
SELECT
authors.name AS author_name.
posts.title AS post_title,
posts.slug AS post_slug
FROM
posts,
authors,
posts_authors pa
WHERE
authors.name LIKE "Alessandro%" AND
authors.id = pa.author_id AND
posts.id = pa.post_id;We start by saying that we only want the author’s name, the title, and the slug, but in the final result, we want to rename them (author_name, post_title, and post_slug). To do that, we use the AS keyword.
Then, we tell MySQL the tables we are going to use for this query. We will need posts, authors, but also posts_authors. We will need this one to follow the relationship, even if we won’t display it in the final result. Since posts_authors is a long name, we create an alias “pa” for it, so that we can later use it.
Finally, we start with our WHERE. The first statement is nothing new, search for all the authors whose name starts with “Alessandro”. Then, in the following row, we tell MySQL to find the posts_authors records that reference this author. Finally, we tell MySQL to reference all the posts referenced by the post_id in the support table.
Just remember, when you query multiple tables you need to provide the table name before the column name and separate the two with a dot (table.column).
Joins in MySQL
Querying multiple tables is fun, and frankly fairly easy. Well, with joins we add another level of complexity. Check and see.
What is a join query in MySQL?
Joins are a way to query against multiple tables that is more SQL-compliant. While the technique we saw above is wonderful and works perfectly, it isn’t something we have in all SQL engines, it is specific of MySQL.
If you want to query against multiple tables and do it in a SQL-compliant fashion, joins are the way to go.
Basically, with joins, you are putting together two tables. We have four basic types of joins:
- Inner join returns only the value where both tables match
- Left join returns all the values from the first table and only the values where you have a match from the second table.
- Right join returns all the values from the second table and only the values where you have a match from the second table.
- Outer join returns unmatched records from both the left and the right table. It basically excludes records where you have a match.
We can also chain joins together, one after another. Everything will be clearer with some examples.
We write joins after the “FROM” clause.
Inner Join
With an inner join, we only get the records of a table if we have a match on another table. Below, a representation of this type of query.
The syntax for an inner join is the following.
INNER JOIN table_on_the_right ON field_1 = field_2We can use this approach to obtain all the posts written by the authors where the name starts with “Alessandro” as we did previously.
SELECT
*
FROM posts
INNER JOIN posts_authors ON posts_authors.post_id = posts.id
INNER JOIN authors ON posts_authors.author_id = authors.id
WHERE
authors.name LIKE "alessandro%";Left Join
With a left join, we get all the records of the table on the left, regardless if they have a match on the right or not. Instead, records on the right appear only if they have a match.
This is useful to see all records, regardless if they have a correspondence or not. The syntax to do it is straightforward.
LEFT JOIN table_on_the_right ON field_1 = field_2For example, we can use this to see all posts and their association with an author, even if they have no author. In the following query, any post with no author will still appear, but information about the author will be set to NULL.
SELECT
*
FROM posts
LEFT JOIN posts_authors ON posts_authors.post_id = posts.id
LEFT JOIN authors ON posts_authors.author_id = authors.id;Right Join
The right join is identical to the left join, but takes all the records on the table on the right.
The syntax is always simple.
RIGHT JOIN table_on_the_right ON field_1 = field_2We can use this join to slightly modify the previous query and get all the authors and the posts they published, regardless if they published some posts or not.
SELECT
*
FROM posts
RIGHT JOIN posts_authors ON posts_authors.post_id = posts.id
RIGHT JOIN authors ON posts_authors.author_id = authors.id;In this case, if there is an author with no posts, it will appear with information about posts set to NULL.
Left and Right Outer Join
Finally, the outer join. This query is trickier because it gets only the records on both sides where you have no match on the other side. To reference our previous example, we would get both posts with no authors and authors with no posts.
Why the hell would you do that? Well, this is not a query you normally use, but it can be handy from time to time. Particularly, it is a great query to use when you want to search for all orphan records, records where you should have a relationship, but for some issues you have none.
In some SQL languages, you can quickly get unmatched records on both tables. In MySQL, you can not. You need to get unmatched records on the left with one query, and unmatched records on the right with another query. The syntax is always simple.
LEFT OUTER JOIN table_on_the_right ON field_1 = field_2Of course, if you want the records on the right, just replace LEFT at the beginning with RIGHT. With the following query, we fetch the posts with no match on the author. Since we can tell if a post has no author by simply checking the posts_authors table, we don’t need to query the “authors” table anymore.
SELECT
*
FROM posts
LEFT OUTER JOIN posts_authors ON posts_authors.post_id = posts.id;Union in MySQL
Unions are a simple concept you may use to consolidate complex queries. They are literally a way to chain the result of a query below the result of another query. The only condition here is: both queries must return the same columns. The syntax is as simple as it gets.
query_1 UNION query_2 [UNION query_3 ...]Below, a quick example. Even if in this case it is obviously pointless to use a UNION, it may make sense in trickier situations.
SELECT title FROM posts WHERE id = 1
UNION SELECT title FROM posts WHERE id = 2;Updating records
Records are not static, you may want to modify them over time. You can do this with the UPDATE query.
UPDATE table_name
SET
column_1 = value_1,
column_2 = value_2,
column_3 = value_3
[WHERE condition]Be careful, if you don’t specify any condition you will update all the records in your table to the same values. Imagine we want to change the slug of a post. We can do that with the following query.
UPDATE posts
SET
slug = "my-second-post"
WHERE
slug = "2nd-post";You can set as many columns as you need, and build complex WHERE clauses in the same way you would with a SELECT query.
Deleting records
Deleting records is also simple, and to do that we use the DELETE statement. This statement wants to know the criteria we want to use to delete records, which is nothing more than a where clause.
DELETE FROM table_name WHERE condtionFor example, if we want to delete our post with “my-second-post” as slug, we could do the following query. Of course, we need to ensure we have no foreign key constraint before doing so.
DELETE FROM posts WHERE slug = "my-second-post";A tip: to empty an entire table, use WHERE 1 as the condition. This will delete all records in that table.
How to design a MySQL Database
At this point, you should have more or less an understanding of what working with MySQL means. Don’t worry, it is normal to be overwhelmed at this point! This final section will help you to make order in your mind and look more like a pro with MySQL.
MySQL: storage vs CPU caveats
In MySQL like in life, it is all about compromise. Like any other application, you need to balance your CPU usage with your memory usage, both RAM and disk.
- The more you divide your database into smaller tables, the more you reduce your storage usage. This is because you will privilege references instead of writing multiple times the same piece of information. The downside of that is that queries become complex, take longer to execute, and thus consume more CPU.
- Instead, the more you keep your database simple, the fewer relationships you want to have. The fewer the relationships, the lower the impact on the CPU. However, to make this possible with complex data structures, it means some data must be repeated. This consumes more disk and increases the logic in your application because now you have to ensure data is updated everywhere.
Remember the golden rule of IT CPU is expensive as hell, memory is cheap. So, always optimize for CPU and never for the disk. The disk can be a major concern for an application that eats a huge amount of data. If you are developing that application, maybe MySQL is not the way to go. Even more, honestly, maybe you should go a little bit beyond this MySQL tutorial before committing to such an application.
How to leverage indexes
Study your database closely. Look at the code of your application, or if you are just working on the database get in touch with the developers. Understand how they use your database, understand their queries.
Then, create indexes on the most-searched fields. You should do this with trial-and-error, experimenting bit by bit. Optimize a field and look for improvements in query performances. If you find some improvement, try tweaking a little more with indexes. If not, roll back the change and try something different.
Remember that the needs of your application may change over time. A database that was once optimized may be now slow because its use has changed. Always stay in sync with the developers of your application.
Documents and caching
Full-text search (searching in large texts) should be your enemy. If you have something like that, try to reduce it as much as possible. For example, if you store invoices in a document and you find that your user always searches for dates in that document, try to extract the date and put it in a dedicated column.
Even better, don’t have large documents and binary files in your database. Store them in file storage, like AWS S3, and then put in your database only a reference to the file. This will make your database dramatically smaller, containing only things that matter (relationships mainly).
Final thoughts
Hopefully, this ultimate MySQL tutorial will get you started with MySQL. With the information you have here, you can comfortably build a production-ready database and run an application on it. However, even if extensive, this post is not 100% comprehensive of what MySQL is like. Many topics were not covered, like queries-in-queries, triggers, or views. Those are for you to explore, once you have a solid grip on the content presented here.
Now it’s time for you to go out there and build awesome databases! If you find any issue, try dropping a comment below or looking in the comments if someone had the same issue before.
