Articles in MySQL Category

MySQL Basics

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

MySQL History

MySQL was founded by Michael Widenius and David Axmark in 1994. It was then developed under the company MySQL AB where the company provided both open source and commercial licenses.

In January 2008, Sun Microsystems acquired MySQL AB and in January 2010, Oracle acquired Sun Microsystems making MySQL owned by Oracle. Read the MySQL Milestones in Wikipedia for more information.

Since Oracle is primarily a proprietary software company, concerns have been raised about the future of MySQL. MySQL forks like MariaDB and alternatives like PostgreSQL have gained more attention with these concerns. However still MySQL is used by many open source software and large scale websites.

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.

employee MySQL Table

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

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.

department MySQL Table

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.

employee_department MySQL Table

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,
FROM employee a, employee_department b, department c
WHERE = b.employee_id
AND b.department_id =

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.

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

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 Mac OS/Linux).

User Types

MySQL allows to create users and to assign different privileges for defined 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 operations on 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, Mac OS and Linux. 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.

Where to Head from Here...
Share with Your Peers...

We Value Your Feedback...

We love to hear what you think about this article. Please provide your opinion, suggestions and improvements using following form. Note that submitted feedback is not displayed but we will get back to you if it needs a reply.