Lesson 1 - MySQL step by step: Introduction and installation

Welcome to the first lesson of the MySQL database course. This course is dedicated to anyone who hasn't yet worked with databases and needs to use them in any language (for example, on the web with PHP or in a desktop application with Java). The purpose is to teach you basics of the SQL language, which are similar for all SQL databases (MySQL, MS-SQL, SQLite ...). We'll go step by step, create tables, insert data, modify them, and finally filter, search, and perform complex queries over multiple tables.

We'll work only with the database itself and no other programming language. It won't be as boring as it sounds because we'll use phpMyAdmin's graphical interface. We're going to show you how to get things done visually through phpMyAdmin (those that it's capable of) and how to write the same thing as an SQL query. The graphical interface will help you understand exactly what you're doing and then to remember the related SQL statement. You'll be able to use the knowledge gained in this course in any programming language that can work with the MySQL database (e.g. PHP) and with minimal effort you can even use it for any other SQL database. Once you complete this course, you'll be able to work with the database at a level that allows you to create a simple Content Management System (CMS) or similar applications.

Relational databases

MySQL is a relational database. This term refers to a database based on tables. Each table contains items of one type. So we can have a user table, an article table, and another comment table, for example.

We can imagine such a database table as a table in Excel. The user table might look like this:

We store the items as rows, each representing one user in this case. The columns then specify the attributes (properties, if you'd like) the items have. The MySQL database is type specific, meaning that each column is of a fixed data type (we distinguish between numbers, characters, short texts, long texts...) and can contain values of only that type. If we want to use a relational database properly, each row in a table should be provided with a unique identifier. The users could have a birth number, but artificial identifiers are used more often. We'll simply assign unique IDs to the users, starting with 1. We'll get to that later.

The word relational refers to a relationship between tables or between entities in one table. But let's leave it for another time and till then, we'll work only with one table at a time.

RDBMS

You may be asking yourself, why databases? You could just store the data in text files using an editor or come up with some other alternative. You'd get it to work somehow...right? Or wouldn't you?

The term database is actually inaccurate, and in technical literature we can encounter the term RDBMS (Relation DataBase Management System). The database engine (MySQL in our case) isn't just a data storage. It's a very sophisticated and optimized tool, that take care of a lot of issues for us and are very simple to use. We use the SQL language to work with databases, whose syntax is more or less human-readable sentences. Along with data storage, many other things need to be managed. We might come up, for example, with security or performance optimizations. But RDBMS does even more, it solves the problem when the same item is being edited by multiple users at the same time, which could otherwise cause inconsistencies in the database. In this case, the RDBMS locks the data and then unlocks it once the writing is done. It also allows us to combine several queries into a transaction when either all the queries in the series are completed or neither of them is executed. It can't happen that only a part of them will be executed. These features of the database engine are summarized by the ACID acronym, let's explain it.

ACID

ACID is an acronym of the words Atomicity, Consistency, Isolation, and Durability. The individual words have the following meanings:

• Atomicity - The operations within a transaction are performed as a single atomic operation. It means, that if any part of the operation fails, the database returns to its original state and no parts of the transaction are executed. A real example would be, for example, transferring money to a bank account. If the money cannot be subtracted from one account, they won't be added to the other account. Otherwise, the database would be inconsistent. If we handled the data transfer by ourselves, this could easily happen to us.
• Consistency - The database state after completing a transaction is always consistent, which means valid according to all the defined rules and constraints. I'll never happen that the database is in an inconsistent state.
• Isolation - The operations are isolated and don't affect each other. If multiple queries happen to need to write into the same row at the same time, they're executed sequentially as in a queue.
• Durability - All data is written to a permanent data storage immediately (usually on a hard drive), in case of a blackout or any other interruption of the RDBMS operation, everything remains as it was before the failure.

So the database (more precisely the database engine) is a black box, that our application communicates with and stores all the data. It's very simple to use and offers data manipulation features we'd hardly be able to provide by ourselves. We don't have to worry about how the data is stored physically, we communicate with the database using the simple SQL query language, see further. Nowadays, it makes no sense to bother with the issue of storing data, we simply go for a database, there is a wide range of them and are mostly free. The database is sometimes referred as to the 3rd layer of the application (the 1st layer is the user interface, the 2nd is the business application logic, the 3rd one is the data layer).

The tools we need

Let's start. We're going to need a configured Apache webserver and the MySQL database. That's very easy to obtain by installing the XAMPP package: Installing Apache, Mysql and PHP on Windows.

phpMyAdmin

phpMyAdmin is the most popular environment for working with the MySQL database and MySQL is the most used database for web applications. I have to say, that there are better administration tools (such as Adminer) and better databases (such as SQLite or PostgreSQL). However, you will find MySQL with phpMyAdmin on any web hosting, and if you ever get to someone else's project, it's very likely to be built upon these technologies. So let's take these 2 tools as the standard (as they really are) and start with the databases by using them.

After installing XAMPP, open the XAMPP control panel and run Apache and MySQL (beware of Skype, if often blocks the port 80). Type the following address to the web browser:

localhost/phpmyadmin/

You should see the phpMyAdmin web interface that looks like this:

The items in the left column are databases. It may be a little bit misleading, but, of course, we can create more databases. These, which we already see, are some XAMPP configuration and testing databases. If we run phpMyAdmin on our webhosting, these would be hidden. In the next lesson, MySQL step by step: Creating a Database And a Table, we'll jump right into it