Learn MySQL: The King of Open-source Databases

MySQL (pronounced My S-Q-L) is an open source relational database. It’s free, and most web hosting providers include it in their most basic packages. Both of these facts have helped to cement its popularity among amateur and professional web designers alike.

If you only have time to learn about one database, MySQL is a safe bet. It’s designed to be easy to use, and its scalability and flexibility make it suitable for practically any application. Its massive user base is partly thanks to its use in many popular web applications, including WordPress, Drupal and many other open source CMS tools.

A Brief History of MySQL

MySQL was developed in the mid-1990s to get around the speed limitations of another database, mSQL. The development team retained a degree of compatibility between mSQL and MySQL, so that code could be ported between the two. Initially, MySQL gained traction because it was tightly integrated with PHP 3, which made MySQL a logical choice for dynamic web applications.

MySQL can be installed on a variety of operating systems and platforms, and because it’s open source, it’s commonly provided as a standard feature of web hosting packages, which means it’s incredibly accessible to newbies and experienced developers alike.

The company behind MySQL was sold to Sun Microsystems in 2008 in a $1 billion deal, following a failed acquisition bid from Oracle in 2006. Two years later, Sun was acquired by Oracle, which finally gave Oracle control of MySQL. This was allegedly contrary to the original agreement with Sun, which was that Oracle would never get control of the product.

MySQL is named after My Widenius, the daughter of one of the MySQL founders, Finnish developer Michael “Monty” Widenius. Widenius is reportedly unhappy with the evolution of the database since Oracle took it over; 9.2.1.1 is the last non-Oracle version. Widenius has also focused on promoting his forked database, MariaDB, in competition with MySQL. He quit Sun before the Oracle deal was complete.

How MySQL Works

MySQL is often installed as part of the LAMP stack, or XAMPP stack if you’re not tied to Linux. It’s impossible to say how many websites depend on MySQL, but we can safely assume that it’s very widely deployed, since it works on so many different platforms.

The databse allows users to manipulate data using structured query language (SQL). Data is stored in tables, and within tables, it’s stored in rows. Users can select from a range of engines to process the data; MyISAM is the default. MySQL has been used successfully to manage more than 50 million records in one database.

Users of MySQL can add data to their database, retrieve it, delete it, and edit it. They can connect directly to the database to work with it, or use a scripting language like PHP. Different data types within the database let users constrain and validate the data within each row.

Most web hosts provide phpMyAdmin alongside MySQL. This is a helpful web-based tool that lets you view your data in a simple format, which can help you to visualise tables and rows, and understand data types in more detail.

What is a Relational Database?

A relational database is designed to understand the relationship between different data. It does this by using keys.

Each table in a MySQL database has a primary key, which is a unique identifier for each record. Each record can only have one primary key, and it cannot be blank (null). For example, in an employee database, we could use each person’s Social Security number as a unique reference for their data.

Each table in the database can reference keys in the other tables in order to create a relationship between different data sets.

MySQL Resources

The web is brimming with free MySQL information, help, and tools. A quick web search should turn up almost every resource you can think of. To get you started, we’ve collected some of our favorite resources, links, and guides.

Tutorials

• TutorialsPoint Learn MySQL: while this tutorial lacks visual appeal, it contains comprehensive MySQL tutorials that go right back to basics. Some knowledge of PHP is helpful.
• MySQL for Absolute Beginners: a single-page tutorial that will walk you through installation and basic commands.
• SQL Tutorial at W3Schools: learning some basic statements will help you to understand how to interrogate your MySQL database. W3Schools is one of the most authoritative websites when it comes to website development and coding.
• MySQL by Example for Beginners: if you know how to code, but you’re new to MySQL, this tutorial is pitched at your level. Learn how MySQL works by following examples.
• TizTag PHP/ MySQL Tutorial: again, perhaps not the most accessible guide in terms of layout, but this tutorial will help you to learn PHP alongside MySQL. If you intend to go straight into web development, try it.
• PHP and MySQL Tutorial: speed through the basics and work towards more advanced coding. It progresses very quickly from the basic components of SQL statements through to database abstraction, joins, and good practice tips for efficient database design.
• Learn MySQL: a very basic introductory guide from About.com, which will familiarize you with SQL and the basics of relational databases. A handy guide for newcomers to SQL and database technologies.
• SQL Injection Tutorial: when you’re new to coding, it’s essential that you learn how to avoid an SQL injection attack that could reveal the contents of your database, or result in data deletion and corruption. This guide provides background information and tweaks that you can use in your code to protect it from hackers.
• Android PHP/ MySQL Tutorial: once you’re armed with the basics of MySQL, you can learn how to build database functionality into an Android app with this guide.

Videos

• 95% of What You’ll Need to Know About MySQL: this video comes in at just over 40 minutes, although it is showing its age. However, most of the steps are useful and the guide is thorough.
• Free Udemy MySQL Course: pick up the basics with this free 10.5 hour video course from Udemy.
• SQL Basics: this video claims that it will provide you with a good working knowledge of SQL in just one hour.
• Create a Database and Table in phpMyAdmin: a look at some common commands in phpMyAdmin, a web-based MySQL interface.
• Learn SQL and MySQL in 3 Hours: this course requires a small upfront fee, but promises to teach the basics in an afternoon. This is another course from Udemy.
• SQL Injection Explained: 7Safe’s video walks through the basics of SQL injection attacks. Although there’s no narration, this is great video for new coders who are just starting to put their own scripts together, as it explains the process of an attack in layman’s terms.

Reference documents

• Official MySQL Documentation: for definitive information, get help here. Be sure to choose the correct reference manual for your version of MySQL.
• PHP and MySQL For Dummies — Cheat Sheet: with this single-page chart, you can quickly remind yourself of the correct syntax as you code. This is a handy extract from the book of the same name.
• MySQL Workbench Reference Manual: get the guide for the free database design tool from the Oracle website. Access it online, or download it in PDF format for offline or ebook use.
• PhpMyAdmin Documentation: phpMyAdmin is frequently offered as part of a web hosting package, alongside MySQL. Learn how to use this friendly and powerful web-based tool to administer your databases, review the information within them, and change MySQL settings.
• SQL Joins Cheat Sheet: a great visual reference that uses pie charts to teach the principles of joins. Bookmark this handy resource and ensure your code is effective and accurate.
• MySQL Cheat Sheet: a quick reference to the most common SQL commands you’re likely to encounter. While the layout isn’t the greatest, there’s plenty to learn from.

Tools

• MySQL Workbench: the official graphical environment for MySQL that allows you to design and manage your databases.
• Sequel Pro: manage your MySQL databases on your Mac or Mac Server. It includes a SQL query editor, tabbed layout, user management, and more.
• Webmin: a web-based MySQL manager that serves as an alternative to phpMyAdmin.
• HeidiSQL: interact with your MySQL, MS SQL and PostgreSQL databases in one convenient tool. Can be installed natively on Windows, or on Linux or Mac via Wine.
• Navicat: a GUI tool aimed at enterprise users, although it claims to be equally useful to MySQL novices. Navicat is also compatible with the cousin of MySQL, MariaDB.

Drivers and Libraries

• MySQL Connectors: view a list of official and user-contributed drivers for different languages, including Perl, Ruby and Python.
• MySQL PHP Drivers — Overview: learn about the different PHP drivers in the official PHP manual.

Communities

• Official MySQL Forum: this forum offers support, discussion, and official product announcements — straight from the development team.
• MySQL on DBForums: a small community of developers assisting each other with MySQL problems. Threads are posted in a question and answer format.
• Recent MySQL Questions on StackExchange: review expert answers and community discussion on MySQL usage. This page shows the most recent discussions that are tagged “MySQL.”

Books

• PHP & MySQL in Easy Steps (2012) by Mike McGrath: a good general introduction for complete newbies at an inexpensive price point. If you are completely new to relational databases, SQL, and PHP, this book will ground your learning.
• SQL Hacks: Tips and Tools for Digging into Your Data (2006) by Cumming and Russell: an advanced SQL guide for experienced database administrators.
• Hackish PHP Pranks and Tricks (2006) by Michael Flenov: this book and CD-ROM get you experimenting with PHP to produce more efficient and secure code. Available on Google Books as an eBook for an affordable price tag.
• MySQL Cookbook (2014) by Paul DuBois: this huge reference tome offers quick solutions for a variety of challenging coding problems. Recycle the code and develop your own variations.
• MySQL Developer’s Library (2013) by Paul DuBois: learn how to administer and manage MySQL at the expert level with this huge resource book.
• MySQL in a Nutshell (2008) by Russell JT Dyer: a handy quick reference guide for MySQL that doubles up as a tutorial resource on certain topics.

Other Information

• The History of MySQL AB: a full timeline by Dries Buytaert, covering the birth, growth and eventual acquisition of the company behind MySQL. This fascinating history runs from 1995 through to the present day, through the acquisition by Sun, and subsequent purchase of Sun by Oracle.
• The Oracle MySQL Website: Oracle’s branded repository of information and downloads on MySQL.
• Planet MySQL: a wealth of information and advanced usage tips from the MySQL developers themselves.
• 10 Reasons to Migrate [From MySQL] to MariaDB: learn about the arguments for leaving MySQL behind and moving to its successor, MariaDB.
• Michael Widenius on Twitter
• MySQL Certification: learn how to become a certified professional in MySQL database administration or development.

Summary

MySQL is a ubiquitous database technology that is easy to use and yet incredibly powerful. It is compatible with a massive range of operating systems, and essential for thousands of scripts and applications.

If you’re learning to code, or planning to get into website design, learning about MySQL and SQL is a safe bet. Even if you don’t plan to get into complex SQL yet, knowing how to pull and push data will be an immense help when learning the basics of relational databases, and those principles can be applied to other databases as your knowledge improves.

There’s a huge amount of free information and support on the web, and despite the controversy over its acquisition, MySQL appears to be an enduring favorite in the web developer’s toolkit.

Further Reading and Resources

We have more guides, tutorials, and infographics related to coding and development:

• SQL Resources: our general SQL resource that is critical for all relational database developers.
• Introduction to ADO.NET: learn all about this system for using any database at all inside the .NET framework.
• PHP Introduction and Resources: get started learning the most popular server-side coding language.

Ultimate Guide to Web Hosting

If you are going to be creating database driven applications, you are going to need to host them somewhere. Check out our Ultimate Guide to Web Hosting. It will explain everything you need to know in order to make an informed choice.

Ultimate Guide to Web Hosting