MySQL Tutorial

The backbone of our digital universe is one ever increasing mountain of data. This means that a key element of the internet and our online world is making use of database management systems like MySQL. They enable us to process and store huge amounts of data electronically. Complex data sets can be easily subdivided into handy subsets and used accordingly. In our MySQL tutorial for beginners, we’ll take you through the basics of this data management system and show you examples of how you can use MySQL to get the most out of data management for your web project.

MySQL is one of the most popular relational database management systems worldwide, alongside Oracle and Microsoft SQL (check out db-engines.com for a current ranking of database model popularity). The software, developed in 1994 by Swedish company MySQL AB, is today controlled by the Oracle Corporation and is operated through a dual licence system: In addition to the proprietary enterprise version, Oracle offers a GPL licensed, open source version as well. This double licensing gives companies the opportunity to develop their own applications formed on a MySQL basis, without having to adhere to a strict licence. 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 management system is known for its broad level of support for different operating systems.

The incorporation 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 differences between the GPL licensed MySQL version and the paid enterprise product are gradually increasing. New functions for the database management system are more and more often only available in the proprietary version of the software. Unpublished 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 development team supporting the MySQL founder Michael ‘Monty’ Widenius had begun to turn their backs on the popular database system and started focusing on initiating an open source fork of the software with MariaDB. By the end of 2012, Fedora, OpenSUSE, Slackware, and Arch Linux became the first Linux distributions to switch from MySQL to MariaDB as their standard installation. Numerous open source projects, as well as big name software firms and web platforms, followed this example — among them, Mozilla, Ubuntu, Google, Red Hat Enterprise Linux, Web of Trust, Team Speak, the Wikipedia Foundation, and the aforementioned 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 operations work with standardised 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 individually rather than being able to change a whole data set in one go. Because of this, most developers stick to one topic focus per table when working with relational database systems. This is known as normalisation of data.

The central application field for MySQL is storing data for dynamic web pages. The combination of MySQL with the web server software Apache and the scripting languages PHP or Perl has been established as the classic software framework for web development. 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 environment XAMPP in order to get first-hand experience with this complicated database management 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 introduction will work on the basis of the testing environment XAMPP. Code snippets and screenshots will be concentrated on database operations 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 management systems are so compatible that you’ll be able to copy over everything you learn directly with a 1:1 translation. Because of this, it makes no difference for the purpose of this beginner’s tutorial as to whether you use MySQL or MariaDB. Our XAMPP tutorial demonstrates how to install a test environment locally on your Windows computer. If you want to learn how to deal with relational databases right from the start, we strongly recommend that you get to grips with MariaDB directly. An alternative test environment 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 installation tutorials for the different platforms can be found in the documentation for MySQL and MariaDB.

When it comes to managing MySQL, we’ll be using the free web application phpMyAdmin here. This is part of the installation for the XAMPP software bundle but can also be downloaded from the official project website free of charge in a separate download package. phpMyAdmin is considered the standard software for administration of MySQL databases online. This web application, written in PHP and JavaScript, offers a graphic user interface for executing database operations. This allows users to create and manage tables for a relational database easily in a web browser. Prior knowledge of the corresponding SQL commands is also not required.

On the left-hand side of the user interface, you will find a navigation panel. Here is where all tables are listed with which you can access your database with the help of phpMyAdmin. In the upper left corner, underneath the program’s logo, the software offers links to the homepage as well as to the official documents. Additionally, you also have the possibility of configuring the navigation panel and updating your individual 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 navigation panel on the left-hand side of the screen. Initially, newly created databases will contain no content. In order to start depositing files, next you will need to create a table.

A description 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 requirements.

The following settings were determined 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 identification number functions here as the primary key for the table users. A tick next to ‘A_I’ (Auto_Increment) lets the database management system know that IDs for each input should be automatically generated as sequential 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 characters 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 management 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 automatically 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 description 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 navigation panel under the database test.

The navigation 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. Furthermore, phpMyAdmin, makes it possible for you to search through tables, manage authorisations, as well as export data sets or even import ones from other tables.

Under certain circumstances, changes to the table structure can lead to a result of data. Before you begin editing or deleting already created columns, you should definitely 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 alternative to the database backup via phpMyAdmin is offered by the backup program MySQLDumper.

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 connection. 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 disadvantaged in that it doesn’t support prepared statements or named parameters. 

• MySQLi: This is an improved version of the classic PHP extension for access to MySQL databases. The interface functions both procedurally as well as object-orientated. Use is limited to MySQL and MariaDB databases.

• PDO: PHP Data Objects (PDO) is an object-orientated interface that provides an abstraction layer for data access. This means that via PDO and not just MySQL databases can be integrated as well as other database systems like PostgreSQL, Oracle, MSSQL, or SQLite in PHP.

In the case of this MySQL course, we have limited it to database connections via PDO.

In order to be able to make a PHP script query to a database, it must first be authenticated. A database connection via PDO can be set up by using the following line of code:

It is recommended that you include this at the start of each script that contains database operations.

We use the PHP keyword new to create a PDE basic class entity. Three parameters are required to go about constructing this; the Data Source Name (DSN), the username, and the database password — if applicable. In this case, the DSN is made up of the following parameters:

• 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 connection is saved in the variable $pdo. This makes it possible to refer back to the database connection at a later point in the program code.

If a connection to the database is established, you can send any number of queries to the database in the subsequent script code. Once the script is terminated, the database connection is also stopped.

In order to retrieve files from our database, we refer back to the database language SQL. This is based semantically on the English language and deliberately kept quite simple. The SQL syntax is to a large extent self-explanatory.

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

For example, a basic SELECT query is made up of the following components:

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.

Additionally, you also have the possibility of extending the statement to include an optional condition like a sorting or grouping function:

The convention is that SQL commands are capitalised, while databases, tables, and fieldnames are written in lower case. This is simply to make them more readable. SQL is principally an unformatted language and therefore does not differentiate between upper and lower case.

If you decide to use the table and column names of the already pre-defined corresponding SQL keywords (something which is not recommended), THEN they must be placed in backticks (``).

Below we demonstrate the syntax of basic SQL statements 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 previously mentioned table, you should create a new PHP file text.php in the htdocs directory of your XAMPP environment 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 connection 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 management system is instructed 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 possibility 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 connection established 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 individual 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 placeholder 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 originally 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 organised according to alphabetical order of users’ first names:

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:

Translated 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. Transferred 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 possibility of identifying 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 invariably 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 authorised a NULL value for the column(s) in question.

PDO allows for database operations to be transformed into so-called prepared statements. These days, such ‘preconceived queries’ are standard practice in the world of web development and are therefore supported by all modern database management systems.

The previous examples have seen us transfer parameter values directly in the SQL statement. However prepared statements work with placeholders, which are then only subsequently assigned values. This makes it possible for the database management system to check the validity of parameters before they are processed. This acts as effective protection 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 incorporate 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 transferred with static parameters via $_GET, this gives hackers the opportunity to augment the input with meta characters. This can lead to undesired effects if they manage to get into the SQL interpreter without masking. This can be effectively avoided with parameterised queries. In this way, prepared statements function as templates for SQL commands for SQL commands, which are transferred to the database, separately from the actual parameters. This not only validates the transferred data but also masks the meta characters automatically and inserts the parameter into the SQL statement, instead of the placeholder.

Alongside these safety features, prepared statements also offer performance advantages. This becomes obvious when the same SQL command is run in a loop with various parameters. Once a prepared statement is parsed once, it remains present in the database system and only needs to be implemented with new parameters. This then means that complicated queries are accelerated significantly.

In PDO prepared statements are implemented 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 placeholder for the respective value.

The following example of code shows the database operation INSERT as a prepared statement with unnamed parameters:

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 placeholder.

If it is the case that a statement contains only placeholders, separately transferred 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 corresponds 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 parameters. The current example sees us specify variable values for two data sets. The execution of these preconceived SQL statements takes place for each data set via execute().

Names parameters are clearer and more visible than the question mark placeholder. In this case, we are talking about a user defined placeholder, which can be named according to the following schema:

Named parameters should contain neither spaces nor hyphens (-). Instead, you should use the underscore (_).

In the following example, the database operation INSERT is shown as a prepared statement with named parameters:

Within the prepared statement are the named parameters :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 parameters as well as the variables of the columns within our example table. This is not specified by the syntax. For this reason, a standardised naming in the sense of an easily readable source code is recommended. 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 information. 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 application 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 synchronise this with the current date. Herewith the SELECT command only selects the entries whose timestamp corresponds with today’s date.

Alternatively, 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 information extracted from the timestamp is collated with a concrete date. Furthermore, 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 conditions:

Additionally, you can also link several conditions 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 predetermined format. However, with MySQL and MariaDB, they are not necessarily set to this. The DATE_FORMAT() function offers you optional parameters allowing you to format dates and times as you please.

The table below shows the possible parameters for the DATE_FORMAT() function according to MySQL documentation.

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 necessarily lead to an error message. Often the desired result remains in place without any indication of the failed operation.

With errorInfo() you have a function that allows you to access advanced error information 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 combination 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 successful. 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 information via errorInfo():

If we execute the script via the web server, the following information is displayed:

It’s generally the case that detailed error information is rarely issued only via the web browser. With this information, users can usually do only very little. Whereas potential attackers use error messages to help them understand SQL queries and through this detect an application’s weak points. Therefore, it is highly recommended that the information provided to users regarding errors stays quite general and that more specific error information is stored internally. An example of how this can be done is as follows:

This table shows a selection of our favourite 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 normalisation process and then link them with the help of the foreign key.

Good database design can be characterised by a minimum level of redundancy. Duplicate entries can be avoided through the normalisation of data tables. In the area of the relational database model, three consecutive normal forms, each one built upon the other, have been established. These specify set rules for the optimal data structuring.

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 information. This is clearly demonstrated 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 information within the table in a separate table, we have supposedly decided to make it easier for ourselves by simply placing information 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 information is placed under the table heading of artist. However, we may one day come to regret this sloppiness 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 information on the album as well as the artist, we should link three separate tables with the help of an SQL command and the respective foreign key.

Foreign key relationships 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 management 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 correspond.

• OUTER JOIN: With an OUTER JOIN you differentiate 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 combination with the placeholder * instructs the database management system to select the values from all columns for which the conditions 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. Furthermore, with the help of the WHERE clause, you can define the optional filter function.

This is made clear by using the example of our normalised 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 restricted 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 information 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 demonstrating easy and practical examples of database operations. If your interests in the software’s possibilities should go beyond those described here, we recommend the literature from DBMS MySQL and MariaDB already linked above in the introductory chapter. Apart from this, there are also countless websites which offer tutorials and examples of use from the most popular database management system. Also recommended is the internet platform Stack Overflow. Here a user community of more than 6.5 million developers poses questions and exchanges advice on current issues and problems regarding software development. 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.