MySQL HistoryMySQL 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 InfrastructureA 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.
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 TypesYou 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 BehaviorLike 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.
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 ConventionsThere 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).