MySQL Introduction
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.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.
| id | first_name | last_name | age | joined_date | records |
|---|---|---|---|---|---|
| 1 | Anuj | kumar | 25 | 2018-01-12 | |
| 2 | Sarita | Pandey | 22 | 2017-04-12 |
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).
|
1 2 3 4 5 6 7 8 9 |
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 the table definition above: 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 the 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 lets you store related data in multiple tables and have relations among them.
For example, assume that the company in the example above also needs to store the department details. Instead of storing these in the employee table, you can create two more tables and store department details in them to avoid data redundancy. The first one can be defined as department and contains department details.
| id | name | contact_details |
|---|---|---|
| 1 | Sales | 423423 |
| 2 | HR | 8787867 |
| 3 | Marketing | 1312312 |
The second one (employee_department) can be defined to store the relationships between the employee and department tables. Provided that an employee’s department can be changed time to time or an employee can work in multiple departments at the same time, this table would have records as shown below.
| employee_id | department_d |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
Now assume that you want to show employee name and the departments they have worked in your application as below. Robin Jackman : Sales Robin Jackman : Marketing Taylor Edward : Human Resource You can fetch the records needed for the above representation using the following SQL query.
|
1 2 3 4 |
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 |
Naming Conventions
There isn’t a standard naming convention for MySQL. Following 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, and Delete relevant to their SQL statements.
Storage Engines
MySQL has several Storage Engines that enable different features on data tables. For example, if you want to automatically delete all the corresponding records in employee_department table when an employee is deleted from the 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 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 the SQL mode per session (generally for the span of starting and stopping the MySQL server) or set it permanently by configuring the MySQL configuration file (my.ini in Windows and my.cnf in Mac OS/Linux).
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.
