MySQL Basics

MySQL is a popular database management system. Like PHP, it’s free and open source. With its rich features, it has been the choice for many database driven PHP applications.

MySQL Infrastructure

A MySQL installation provides support for creating databases in it and allows applications to communicate with these databases when correct credentials are given via a database connection. A MySQL database consists of set of data tables. MySQL uses Structured Query Language for its commands (MySQL has also got few non-SQL commands).

We can use SQL to create a database and then create tables in it according to the format we want. Usually in a web application, database and respective data tables are created at installer time. Then data is inserted, fetched, updated and deleted from data tables in the runtime of the application.

Above is a likely format if you are to store employee data in a MySQL table. Following is the SQL you need to create this table in a given database (Don’t worry if you don’t know SQL yet. We will cover it soon).

create table `employee` (
  `id` int(10),
  `first_name` varchar(40),
  `last_name` varchar(40),
  `age` tinyint(3),
  `joined_date` date,
  `records` text,
  primary key  (`id`)
);

Data Types

You can see five MySQL data types in above table definition. int for integers, varchar for short text, tinyint for short integers, date for dates and text for long text. In addition to these, MySQL provides some more data types. Choosing correct data type for each column is important for an optimal database.

Relational Behavior

Like many other enterprise-level database management systems, MySQL is a relational database management system and let you store related data in multiple tables and have relations among them.

For an example think that company in above example also needs to store the department details. Instead of storing these details in employee table, you can create two more tables and store department details to avoid data redundancy. First one can be defined as department which contains department details.

Second one (employee_department) can be defined to store relations between employee and department tables. Provided that an employee’s department can get changed time to time or an employee can work in multiple departments in the same time, this table would have records like below.

Now think that you want to show employee name and the departments they have worked in your application like below.

Robin Jackman : Sales
Robin Jackman : Marketing
Taylor Edward : Human Resource

You can fetch the records needed for above representation using following SQL query.

SELECT a.first_name, a.last_name, c.name
FROM employee a, employee_department b, department c
WHERE a.id = b.employee_id
AND b.department_id = c.id

Having primary keys (records in these columns should be unique) in employee and department tables is required to have this data relation. In the relation between employee and employee_department, id of employee acts as the primary key and employee_id of employee_department acts as the foreign key. And there is a one-to-many relationship between these two tables. That is, for each record in employee table, there can be one or more related records in employee_department table.

If we had the information that “once recruited an employee can only work in one department” then we can simply keep another column (say department_id) in employee table to store department ID and omit employee_department table. In that case, there is many-to-one relationship between employee table and department table.

Naming Conventions

There isn’t a standard naming convention for MySQL. Followings are some common conventions.

• All names are lower case (Ex: employee).
• Words are separated by underscore (Ex: first_name).
• Use singular form (Ex: employee, not employees).

Database Operations

MySQL allows all four CRUD (Create, Read, Update, Delete) operations. Often you would see these operations as Insert, Select, Update, Delete relevant to their SQL statements. We will look into each of these operations in separate articles.

Storage Engines

MySQL has several Storage Engines that enable different features on data tables. For an example, if you want to automatically delete all the corresponding records in employee_department table when an employee is deleted from employee table then you have to use InnoDB storage engine (for defining constraints). If you intend to use any storage engine other than default one (MyISAM), you have to mention it when creating a data table.

SQL Modes

MySQL has the ability to run in different SQL modes enabling different restrictions. For an example at default configuration, you can enter 0000-00-00 for a date field. But if you are running in Traditional mode, MySQL will throw an error when you try to insert 0000-00-00 into a date field since it’s not a valid date. You can set SQL mode per session (generally for the span of starting and stopping MySQL server) or set it permanently by configuring MySQL configuration file (my.ini in Windows and my.cnf in Linux).

User Types

MySQL allows to create users and to assign different privileges for these users. User credentials are needed when an application need to make a connection with MySQL and execute operations on a database. Allowing to have different privileges makes managing a database safe. For an example, for the same database, you can have one user with all the privileges and another with only viewing privileges.

Installing MySQL

MySQL can be downloaded and installed as a separate application. MySQL supports several operating systems including Windows and Linux. Like Apache, once installed, you have to start MySQL server before accessing databases. If you used an AMP stack like XAMPP, you could install Apache, MySQL and PHP in a single installation. These stacks also provide an interface where you can start/stop Apache and MySQL.

Summary

Intention of this article is to introduce you MySQL. MySQL has some advanced features like Views, Transactions, Prepared Statements, Stored Procedures and Triggers that we didn’t mention. We will elaborate more on MySQL basic features in other MySQL articles that would let you try out some advanced MySQL features. MySQL is a comprehensive technology on its own and learning it well would always be a plus when you create enterprise level database driven applications.