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