MySQL Tutorial

The backbone of our digital universe is one ever in­creas­ing mountain of data. This means that a key element of the internet and our online world is making use of database man­age­ment systems like MySQL. They enable us to process and store huge amounts of data elec­tron­i­cal­ly. Complex data sets can be easily sub­di­vid­ed into handy subsets and used ac­cord­ing­ly. In our MySQL tutorial for beginners, we’ll take you through the basics of this data man­age­ment system and show you examples of how you can use MySQL to get the most out of data man­age­ment for your web project.

MySQL is one of the most popular re­la­tion­al database man­age­ment systems worldwide, alongside Oracle and Microsoft SQL (check out db-engines.com for a current ranking of database model pop­u­lar­i­ty). The software, developed in 1994 by Swedish company MySQL AB, is today con­trolled by the Oracle Cor­po­ra­tion and is operated through a dual license system: In addition to the pro­pri­etary en­ter­prise version, Oracle offers a GPL licensed, open source version as well. This double licensing gives companies the op­por­tu­ni­ty to develop their own ap­pli­ca­tions formed on a MySQL basis, without having to adhere to a strict license. However, the Oracle takeover of MySQL has been met with some fierce criticism in the open source community. MySQL is written in C and C++ and has a Yacc-based SQL parser with a privately developed tokenizer (lexical scanner). The database man­age­ment system is known for its broad level of support for different operating systems.

The in­cor­po­ra­tion of the MySQL project into the Oracle product portfolio has mainly been met with mistrust and criticism in the developer scene. This is primarily due to the fact that the dif­fer­ences between the GPL licensed MySQL version and the paid en­ter­prise product are gradually in­creas­ing. New functions for the database man­age­ment system are more and more often only available in the pro­pri­etary version of the software. Un­pub­lished error databases and a lack of testing also suggest that the open source project is being neglected now that it’s under the control of the software giant Oracle. As a result, backing within the open source community is on the wane.

As early as 2009, the core de­vel­op­ment team sup­port­ing the MySQL founder Michael ‘Monty’ Widenius had begun to turn their backs on the popular database system and started focusing on ini­ti­at­ing an open source fork of the software with MariaDB. By the end of 2012, Fedora, OpenSUSE, Slackware, and Arch Linux became the first Linux dis­tri­b­u­tions to switch from MySQL to MariaDB as their standard in­stal­la­tion. Numerous open source projects, as well as big name software firms and web platforms, followed this example — among them, Mozilla, Ubuntu, Google, Red Hat En­ter­prise Linux, Web of Trust, Team Speak, the Wikipedia Foun­da­tion, and the afore­men­tioned software project XAMPP.

It’s already clear that MariaDB is being developed more regularly than the open source MySQL version. So it’s safe to assume that the fork will soon overtake its mother project.

Tolkien is listed in the authors table with the primary key author_id1. To call up all works by the author, we can simply use the foreign key in the Works table. This allows us to retrieve all rows that are linked to author_id1.

In practical use, MySQL database op­er­a­tions work with stan­dard­ized SQL-commands like SELECT, INSERT, UPDATE, and DELETE. We’ll explore these in more detail in the following sections of our MySQL tutorial.

We could have stored all the data on both of our authors and their works in just one table of course. But this would result in a database with a huge number of redundant entries, as we would have to fill out the first name and surname table for every single row, even though many would repeat. This also puts extra strain on your database memory and means that if you wish to update a table, you need to do so for every row in­di­vid­u­al­ly rather than being able to change a whole data set in one go. Because of this, most de­vel­op­ers stick to one topic focus per table when working with re­la­tion­al database systems. This is known as nor­mal­iza­tion of data.

The central ap­pli­ca­tion field for MySQL is storing data for dynamic web pages. The com­bi­na­tion of MySQL with the web server software Apache and the scripting languages PHP or Perl has been es­tab­lished as the classic software framework for web de­vel­op­ment. This web stack is known as LAMP (Linux), MAMP (MacOS), or WAMP (Windows), depending on which server operating system it’s used with.

But for MySQL beginners, we recommend using the local text en­vi­ron­ment XAMPP in order to get first-hand ex­pe­ri­ence with this com­pli­cat­ed database man­age­ment system. This uses the current version of MariaDB.

In order to take you through the basics of MySQL, we’ll work with practical examples. Our MySQL in­tro­duc­tion will work on the basis of the testing en­vi­ron­ment XAMPP. Code snippets and screen­shots will be con­cen­trat­ed on database op­er­a­tions made possible via PHP with the help of an Apache HTTP server on a local Windows computer. Instead of the classic MySQL database, we’ll be using the fork MariaDB. But don’t worry — both database man­age­ment systems are so com­pat­i­ble that you’ll be able to copy over every­thing you learn directly with a 1:1 trans­la­tion. Because of this, it makes no dif­fer­ence for the purpose of this beginner’s tutorial as to whether you use MySQL or MariaDB. Our XAMPP tutorial demon­strates how to install a test en­vi­ron­ment locally on your Windows computer. If you want to learn how to deal with re­la­tion­al databases right from the start, we strongly recommend that you get to grips with MariaDB directly. An al­ter­na­tive test en­vi­ron­ment based on MySQL exists in the form of AMPPS and is also available free of charge. It’s also possible to work with a user-defined web stack. MySQL and MariaDB can be combined with a range of different operating systems, web servers, and scripting languages. You can access GPL-licensed download packages free of charge at mysql.com and mariadb.com. Detailed in­stal­la­tion tutorials for the different platforms can be found in the doc­u­men­ta­tion for MySQL and MariaDB.

When it comes to managing MySQL, we’ll be using the free web ap­pli­ca­tion ph­p­MyAd­min here. This is part of the in­stal­la­tion for the XAMPP software bundle but can also be down­loaded from the official project website free of charge in a separate download package. ph­p­MyAd­min is con­sid­ered the standard software for ad­min­is­tra­tion of MySQL databases online. This web ap­pli­ca­tion, written in PHP and JavaScript, offers a graphic user interface for executing database op­er­a­tions. This allows users to create and manage tables for a re­la­tion­al database easily in a web browser. Prior knowledge of the cor­re­spond­ing SQL commands is also not required.

On the left-hand side of the user interface, you will find a nav­i­ga­tion panel. Here is where all tables are listed with which you can access your database with the help of ph­p­MyAd­min. In the upper left corner, un­der­neath the program’s logo, the software offers links to the homepage as well as to the official documents. Ad­di­tion­al­ly, you also have the pos­si­bil­i­ty of con­fig­ur­ing the nav­i­ga­tion panel and updating your in­di­vid­ual display.

We will begin our MySQL tutorial by showing you how to set up your first database.

Confirm your entry by clicking on ‘Create’. Your newly created database will appear in the nav­i­ga­tion panel on the left-hand side of the screen. Initially, newly created databases will contain no content. In order to start de­posit­ing files, next you will need to create a table.

A de­scrip­tion of the possible table structure formats can be found in the following table.

The options outlined here cover the most important settings for the formation of table columns. If you use the scroll bar to scroll further to the right, you will find further settings. These are not covered in this MySQL tutorial for beginners.

The table below lists the different types of data that can be processed with MySQL and MariaDB, as well as their range of values and storage re­quire­ments.

The following settings were de­ter­mined for our example table users:

Possible values for the column id are selected as Integers (INT) and carry the attribute UNSIGNED. This means that id can only accept positive numerical values. In the ‘Index’ we selected the setting PRIMARY for id. The iden­ti­fi­ca­tion number functions here as the primary key for the table users. A tick next to ‘A_I’ (Auto_Increment) lets the database man­age­ment system know that IDs for each input should be au­to­mat­i­cal­ly generated as se­quen­tial numbers.

The values for the columns forename, surname, email and password are selected as data type VARCHAR. This then means that we are dealing with a string whose length (M) we have limited to 50 char­ac­ters using the option ‘Length/Value’. With regards to the email column, the index option UNIQUE has been activated. This allows us to ensure that each email address in our table is only saved once.

For the columns created_at und updated_at, the data type TIMESTAMP has been selected. The database man­age­ment system saves time-related data for the creation and updating of data sets in the format YYYY-MM-DD HH:MM:DD. Given that the system au­to­mat­i­cal­ly generates a timestamp for each new entry, we select the standard value CURRENT_TIMESTAMP for the created_at column. This then means that the updated_at will only become relevant when we decide to update an entry. For this reason, we allow null values for this column and set NULL as the standard value.

A detailed de­scrip­tion of SQL syntax can be found below in the section on database queries.

By simply clicking on ‘Save’ you can save your settings. The table users is displayed in the nav­i­ga­tion panel under the database test.

The nav­i­ga­tion bar offers several tabs when it comes to managing your data tables. Click on the ‘Structure’ tab if you wish to change the structure of a data table. New data sets can be added under the ‘Insert’ tab. Fur­ther­more, ph­p­MyAd­min, makes it possible for you to search through tables, manage au­tho­riza­tions, as well as export data sets or even import ones from other tables.

Under certain cir­cum­stances, changes to the table structure can lead to a result of data. Before you begin editing or deleting already created columns, you should def­i­nite­ly make sure to back up your database. If you move to the tab titled ‘Export’, select the desired file format for the backup and confirm this by clicking on ‘Go’. This will open dialog window in which your web browser will inquire the save location for the download. An al­ter­na­tive to the database backup via ph­p­MyAd­min is offered by the backup program MySQL­Dumper.

After we have filled our example table users with entries, the next chapter will address how the stored files can be retrieved via PHP through the Apache web server.

The first step towards doing this is to set up a database con­nec­tion. For this there are three interface functions available in PHP: MySQL Extension, MySQL Improved Extension (MySQLi) and PHP Data Objects (PDO).

• MySQL extension: MSQL extension refers to a MySQL interface that used to be very popular but today is rather outdated. Compared with MySQLi and PDO, the old MySQL extension is dis­ad­van­taged in that it doesn’t support prepared state­ments or named pa­ra­me­ters. 

• MySQLi: This is an improved version of the classic PHP extension for access to MySQL databases. The interface functions both pro­ce­du­ral­ly as well as object-ori­en­tat­ed. Use is limited to MySQL and MariaDB databases.

• PDO: PHP Data Objects (PDO) is an object-ori­en­tat­ed interface that provides an ab­strac­tion layer for data access. This means that via PDO and not just MySQL databases can be in­te­grat­ed as well as other database systems like Post­greSQL, Oracle, MSSQL, or SQLite in PHP.

In the case of this MySQL course, we have limited it to database con­nec­tions via PDO.

In order to be able to make a PHP script query to a database, it must first be au­then­ti­cat­ed. A database con­nec­tion via PDO can be set up by using the following line of code:

It is rec­om­mend­ed that you include this at the start of each script that contains database op­er­a­tions.

We use the PHP keyword new to create a PDE basic class entity. Three pa­ra­me­ters are required to go about con­struct­ing this; the Data Source Name (DSN), the username, and the database password — if ap­plic­a­ble. In this case, the DSN is made up of the following pa­ra­me­ters:

• PDO database driver: mysql
• Database server (host=): localhost
• Database name (dbname=): test
• Character set (charset=): utf8

If it’s the case that you have not specified any login details for your database, you can enter the username root and leave the password field empty:

The database con­nec­tion is saved in the variable $pdo. This makes it possible to refer back to the database con­nec­tion at a later point in the program code.

If a con­nec­tion to the database is es­tab­lished, you can send any number of queries to the database in the sub­se­quent script code. Once the script is ter­mi­nat­ed, the database con­nec­tion is also stopped.

In order to retrieve files from our database, we refer back to the database language SQL. This is based se­man­ti­cal­ly on the English language and de­lib­er­ate­ly kept quite simple. The SQL syntax is to a large extent self-ex­plana­to­ry.

In SQL you work with state­ments, which are also referred to as queries or requests.

For example, a basic SELECT query is made up of the following com­po­nents:

Next, you need to specify the SQL command SELECT and then specify the various columns and tables to which the command should refer to. A semicolon then brings the statement to an end.

Ad­di­tion­al­ly, you also have the pos­si­bil­i­ty of extending the statement to include an optional condition like a sorting or grouping function:

The con­ven­tion is that SQL commands are cap­i­tal­ized, while databases, tables, and field­names are written in lower case. This is simply to make them more readable. SQL is prin­ci­pal­ly an un­for­mat­ted language and therefore does not dif­fer­en­ti­ate between upper and lower case.

If you decide to use the table and column names of the already pre-defined cor­re­spond­ing SQL keywords (something which is not rec­om­mend­ed), THEN they must be placed in backticks (``).

Below we demon­strate the syntax of basic SQL state­ments using the examples of the commands SELECT, INSERT, UPDATE and DELETE.

The command SELECT is used to retrieve selected data rows from a desired number of tables. For example, if you wish to display in the web browser the names and surnames, as well as the email addresses of all users from the pre­vi­ous­ly mentioned table, you should create a new PHP file text.php in the htdocs directory of your XAMPP en­vi­ron­ment and then input the following script:

The example code can be read as follows: first we begin with the script that has the PHP start tag <?php. In line 2 then we establish the con­nec­tion to our database test on localhost and save this in the variable $pdo. The SQL statement with the command SELECT can be found in line 3. Here the database man­age­ment system is in­struct­ed to retrieve the columns forename, surname, and email from the users table. We then save the statement in the $sql variable. 

Columns 4 to 7 display a foreach loop. This offers the pos­si­bil­i­ty of iterating over any array, such as going through a data structure step by step. Selecting which array we wish to iterate and how the requested files should be saved can be specified in brackets behind the foreach construct:

The $pdo variable responds to the desired database through the con­nec­tion es­tab­lished in line 2. We send this along with the function query() to the SQL statement stored in the variable $sql.

What happens here is that the web server retrieves the columns forename, surname, and email of the users table from the database and, within the framework of foreach loop, goes through each in­di­vid­ual table row. As to where the extracted data should be saved, this is specified by the PHP keyword as in the array $row variable.

This is how the array looked in the first round of the foreach loop:

If it is the case that all the columns of a table should be read through, you should use the asterisks as a place­hold­er in the SQL statement.

In the case of both previous examples, the web server gives us the user details in the order in which we orig­i­nal­ly entered them in the users table (according to the ID). If you wish to sort the data in a different order, you can do this through the use of the SQL keyword ORDER BY. The following example will show the data being organized according to al­pha­bet­i­cal order of users’ first names:

The creation of database entries via ph­p­MyAd­min only happens very rarely. Generally, data that is part of an executed script from the web server is written in the database, e.g., when an internet user fills out an online formula on a website or when a customer leaves a comment on an internet store’s site. In both cases, the SQL command INSERT is deployed in the back­ground. A SQL statement with the command INSERT is created according to the following schema:

This can be read as follows: call up the named table and then enter the values 1, 2 and 3 in the columns 1, 2 and 3.

A basic PHP script, to add a further data set to our example table, could appear as follows:

If you wish to update the existing data sets, use the SQL command UPDATE as per the following basic schema:

Trans­lat­ed into simple English, this statement means: select the indicated table and replace the value in Column1 with Value1 and the value in Column2 with Value2, provided that Column3 contains Value3. Note: if you forget the condition, MySQL will overwrite the affected fields across all data sets.

In this example, we are dealing with an SQL statement that links a database operation to a condition. Trans­ferred to our example table, the email address of the user John Doe can be updated via the following PHP script:

Use the following UPDATE statement to transfer the values:

Given that we wish to update all data sets, we therefore outline no condition for this update:

If it’s the case that you are working with IDs in your databases, there is the pos­si­bil­i­ty of iden­ti­fy­ing the entries to be deleted. For example, if you wish to delete entry 5 in our example table then enter the following:

The SQL command DELETE in­vari­ably deletes a full database column. If you wish to only delete values in certain columns of a database, you can do this with an UPDATE statement. UPDATE table SET column = NULL WHERE … you can assign the value NULL to a column, provided of course that you have au­tho­rized a NULL value for the column(s) in question.

PDO allows for database op­er­a­tions to be trans­formed into so-called prepared state­ments. These days, such ‘pre­con­ceived queries’ are standard practice in the world of web de­vel­op­ment and are therefore supported by all modern database man­age­ment systems.

The previous examples have seen us transfer parameter values directly in the SQL statement. However prepared state­ments work with place­hold­ers, which are then only sub­se­quent­ly assigned values. This makes it possible for the database man­age­ment system to check the validity of pa­ra­me­ters before they are processed. This acts as effective pro­tec­tion against SQL injection. For such patterns of attack, hackers create or alter SQL commands in order to get access to sensitive data, overwrite data, or to in­cor­po­rate their own commands into a system.

SQL injection is based on a known security breach in the area of SQL databases. For example, if a user’s entry is trans­ferred with static pa­ra­me­ters via $_GET, this gives hackers the op­por­tu­ni­ty to augment the input with meta char­ac­ters. This can lead to undesired effects if they manage to get into the SQL in­ter­preter without masking. This can be ef­fec­tive­ly avoided with pa­ra­me­ter­ized queries. In this way, prepared state­ments function as templates for SQL commands for SQL commands, which are trans­ferred to the database, sep­a­rate­ly from the actual pa­ra­me­ters. This not only validates the trans­ferred data but also masks the meta char­ac­ters au­to­mat­i­cal­ly and inserts the parameter into the SQL statement, instead of the place­hold­er.

Alongside these safety features, prepared state­ments also offer per­for­mance ad­van­tages. This becomes obvious when the same SQL command is run in a loop with various pa­ra­me­ters. Once a prepared statement is parsed once, it remains present in the database system and only needs to be im­ple­ment­ed with new pa­ra­me­ters. This then means that com­pli­cat­ed queries are ac­cel­er­at­ed sig­nif­i­cant­ly.

In PDO prepared state­ments are im­ple­ment­ed with the help of the function prepare(). This prepares a statement for the execution and also returns a statement object. Either a question mark (?) or a named parameter will be used as a place­hold­er for the re­spec­tive value.

The following example of code shows the database operation INSERT as a prepared statement with unnamed pa­ra­me­ters:

Next, with the help of the prepare() function, we create a statement object of the desired query and then save this in the $statement array. Instead of concrete parameter values, the question mark is deployed as a place­hold­er.

If it is the case that a statement contains only place­hold­ers, sep­a­rate­ly trans­ferred values in the following code must be bound to it. In PHP the bindParam() function is used. We use the arrow operator (->) to access the bindParam() method of the $statement object and then assign this variable (1 cor­re­sponds to the first question mark, 2 to the second, and so on).

This SQL template can then be executed as often as you want with the desired pa­ra­me­ters. The current example sees us specify variable values for two data sets. The execution of these pre­con­ceived SQL state­ments takes place for each data set via execute().

Names pa­ra­me­ters are clearer and more visible than the question mark place­hold­er. In this case, we are talking about a user defined place­hold­er, which can be named according to the following schema:

Named pa­ra­me­ters should contain neither spaces nor hyphens (-). Instead, you should use the un­der­score (_).

In the following example, the database operation INSERT is shown as a prepared statement with named pa­ra­me­ters:

Within the prepared statement are the named pa­ra­me­ters :forename, :surname, :email, and :password. We then bind this to the variables $forename, $surname, $email, and $password via bindParam(). In the current example, we have named both the pa­ra­me­ters as well as the variables of the columns within our example table. This is not specified by the syntax. For this reason, a stan­dard­ized naming in the sense of an easily readable source code is rec­om­mend­ed. The assigning of the variable value and the execution of the SQL statement is analogous to the previous example.

MySQL and MariaDB support a diverse range of functions to be able to work with date and time in­for­ma­tion. A complete list of them can be found here. In this MySQL tutorial for beginners, we limit ourselves to one choice.

An example of a possible scenario for the ap­pli­ca­tion of the time and date function in MySQL is a database query, in which all data sets created on a certain day should be read out.

The following script provides us with all data sets from our example table users, which were created today:

To ensure that only the entries from today are displayed, we use the following condition in SQL statement:

Next, with the help of the DATE() function, we extract the date from the timestamp saved in the created_at column, and then syn­chro­nize this with the current date. Herewith the SELECT command only selects the entries whose timestamp cor­re­sponds with today’s date.

Al­ter­na­tive­ly, we can also select the entry that we updated on 16.12.2016. In order to do this we only need to adjust the condition of our SQL statement:

In this case, the date in­for­ma­tion extracted from the timestamp is collated with a concrete date. Fur­ther­more, you can narrow down queries to a specific year, month, or day.

The following statement relates to all entries in the users table, which were created in December:

Along with the equals sign, SQL supports the following operators in con­di­tions:

Ad­di­tion­al­ly, you can also link several con­di­tions with logical symbols:

For an example, the following statement selects all entries which were created after February and prior to April:

Up till now, we have saved the date and time details in our database in the pre­de­ter­mined format. However, with MySQL and MariaDB, they are not nec­es­sar­i­ly set to this. The DATE_FORMAT() function offers you optional pa­ra­me­ters allowing you to format dates and times as you please.

The table below shows the possible pa­ra­me­ters for the DATE_FORMAT() function according to MySQL doc­u­men­ta­tion.

If it so happens that a script is not executed as desired, this is usually as a result of syntactic errors in the source code or wrongly named tables, columns, and/or variables. In this case, it does not nec­es­sar­i­ly lead to an error message. Often the desired result remains in place without any in­di­ca­tion of the failed operation.

With errorInfo() you have a function that allows you to access advanced error in­for­ma­tion relating to the most recent database operation — e.g. to issue these via the web browser.

The following script for updating email addresses sees the errorInfo() function being used in com­bi­na­tion with an if loop. For this to happen, the correct execution of the SQL statement is required. If this is carried out error free, then the web server will return the string Update suc­cess­ful. Otherwise, it will be executed under the specified code else.

In the current example, we inform the user about the fact that an SQL error has occurred and thus issue the affected SQL statement as well as advanced error in­for­ma­tion via errorInfo():

If we execute the script via the web server, the following in­for­ma­tion is displayed:

It’s generally the case that detailed error in­for­ma­tion is rarely issued only via the web browser. With this in­for­ma­tion, users can usually do only very little. Whereas potential attackers use error messages to help them un­der­stand SQL queries and through this detect an ap­pli­ca­tion’s weak points. Therefore, it is highly rec­om­mend­ed that the in­for­ma­tion provided to users regarding errors stays quite general and that more specific error in­for­ma­tion is stored in­ter­nal­ly. An example of how this can be done is as follows:

This table shows a selection of our favorite songs from the 1960s and serves as an example of a poor quality database design.

The table features redundant data fields. We remove these by splitting the files as part of the nor­mal­iza­tion process and then link them with the help of the foreign key.

Good database design can be char­ac­ter­ized by a minimum level of re­dun­dan­cy. Duplicate entries can be avoided through the nor­mal­iza­tion of data tables. In the area of the re­la­tion­al database model, three con­sec­u­tive normal forms, each one built upon the other, have been es­tab­lished. These specify set rules for the optimal data struc­tur­ing.

A table belongs to the first normal form if all of its attribute values are atomic. Attribute values are deemed to be atomic if they contain only one piece of in­for­ma­tion. This is clearly demon­strat­ed in our bad example above.

For example, take a look at the columns album_title and artist in the table album. Instead of listing each piece of in­for­ma­tion within the table in a separate table, we have sup­pos­ed­ly decided to make it easier for ourselves by simply placing in­for­ma­tion regarding the release year of an album, as well as how long the band has been in existence, in brackets after the title of the album. All of this in­for­ma­tion is placed under the table heading of artist. However, we may one day come to regret this slop­pi­ness if, for example, we wish to retrieve all titles that were released in a certain year.

For that reason, we recommend creating tables based on the rules of the first normal form instead. With this in mind, our example table should then look like this:

If we now wish to output a specific title in our database including in­for­ma­tion on the album as well as the artist, we should link three separate tables with the help of an SQL command and the re­spec­tive foreign key.

Foreign key re­la­tion­ships make it possible for you to retrieve data from different tables at the same time, all with just one single SQL statement. For this there are four types of JOIN available in MySQL and MariaDB:

• INNER JOIN: With an INNER JOIN, the database man­age­ment system searches for common entries in both tables bound via JOIN. The only data sets that will be scanned are those in which there are matches, i.e., in which the values in the linked columns (primary key and foreign key) from both tables cor­re­spond.

• OUTER JOIN: With an OUTER JOIN you dif­fer­en­ti­ate between left and right databases. It is different to the INNER JOIN in that it is not just data sets that contain matches in both tables that are scanned. All the rest of the data sets of the right or left table are also scanned as well.

• LEFT JOIN: All data sets from the left table are scanned as well as all of those from the right table, in which matches are found.

• RIGHT JOIN: All data sets from the right table are scanned as well as all of those from the left table, in which matches are found.

For the sake of brevity in our MySQL for beginners tutorial, we will limit ourselves to the INNER JOIN.

The syntax of an INNER JOIN looks like the following basic schema:

The SQL command SELECT in com­bi­na­tion with the place­hold­er * instructs the database man­age­ment system to select the values from all columns for which the con­di­tions of the ON- and WHERE- clause are valid.

As we are dealing with an INNER JOIN, only the data sets where there is a match between the foreign key of table1 and the primary key of table2 are retrieved from the database. Fur­ther­more, with the help of the WHERE clause, you can define the optional filter function.

This is made clear by using the example of our nor­mal­ized tables album, artist and track:

The example script shows an INNER JOIN in which the table album is linked with the table artist. We select only the data sets wherein there is a match between the primary and foreign key.

We have then scanned the details from the artist table, as well as the details regarding the album title and the record’s year of release. 

Which of the join’s data sets are displayed is re­strict­ed by a condition of the WHERE clause. If, for example, we wish to output the albums that were released in the year 1968, we can enter the following:

The JOIN command allows you to bring as many tables as you want together in a data network. In the following example, we link the album table with the artist and track tables in an INNER JOIN. This then means that we can output all in­for­ma­tion relating to the tracks stored in the database.

Our MySQL tutorial aimed at beginners can be seen as a crash course with the aim of making you more familiar with the basics of SQL-based database systems and demon­strat­ing easy and practical examples of database op­er­a­tions. If your interests in the software’s pos­si­bil­i­ties should go beyond those described here, we recommend the lit­er­a­ture from DBMS MySQL and MariaDB already linked above in the in­tro­duc­to­ry chapter. Apart from this, there are also countless websites which offer tutorials and examples of use from the most popular database man­age­ment system. Also rec­om­mend­ed is the internet platform Stack Overflow. Here a user community of more than 6.5 million de­vel­op­ers poses questions and exchanges advice on current issues and problems regarding software de­vel­op­ment. And naturally, here in the IONOS Digital Guide there are many extensive articles on the topic of databases. These can be found using the tags listed below.