MySQL Tutorial

MySQL is the most popular and a free Open Source Relational Database Management System (RDBMS). An RDBMS system stores the data in the form of tables that might be related to each other. MySQL uses Structured Query Language (SQL) to store, manage and retrieve data, and control the accessibility to the data. It is one of the best RDBMS being used for developing web-based software applications.

MySQL is written in C and C++. Its SQL parser is written in yacc, but it uses a home-brewed lexical analyzer.

MySQL works on many system platforms, such as, Linux, macOS, Microsoft Windows, AIX, BSDi, FreeBSD, HP-UX, ArcaOS, eComStation, IBM i, IRIX, NetBSD, Novell NetWare, OpenBSD, OpenSolaris, OS/2 Warp, QNX, Oracle Solaris, Symbian, SunOS, SCO OpenServer, SCO UnixWare, Sanos and Tru64.

This tutorial will give you quick start with MySQL and make you comfortable with MySQL programming.

MySQL Examples

Consider an example table CUSTOMERS created in the MySQL database. This table contains the details of customers like ID, NAME, AGE, ADDRESS, SALARY.

IDNAMEAGEADDRESSSALARY
1Ramesh32Ahmedabad2000.00
2Khilan25Delhi1500.00
3Kaushik23Kota2000.00
4Chaitali25Mumbai6500.00
5Hardik27Bhopal8500.00
6Komal22Hyderabad4500.00
7Muffy24Indore10000.00

Since MySQL uses SQL to manage data, it also uses almost all DDL, DML and DCL statements. For instance, the following DML statement lists the records of all customers who are 25 years old.

SELECT * FROM CUSTOMERS WHERE AGE = 25;

Following records are displayed as a result-set −

IDNAMEAGEADDRESSSALARY
2Khilan25Delhi1500.00
4Chaitali25Mumbai6500.00

MySQL Online Editor

In this tutorial, we provide a MySQL Online Editor which helps you to Edit and Execute the MySQL code directly from your browser. Click on the run button icon to run the following MySQL code to be executed on the CUSTOMERS table and retrieve the records matching with the given condition.

SELECT * FROM CUSTOMERS WHERE NAME = ‘Chaitali’;

This Online Editor will save you the time to install the MySQL setup in order to execute any query. Try our MySQL Online Editor now.

Why to Learn MySQL?

MySQL is generally faster, more secure and efficient than other relational databases. Some of world’s fastest growing organizations make use of MySQL to efficiently power their high-volume web sites, business-critical systems and packaged software.

However, whether you choose MySQL for your application or not, totally depends on your organization’s resources and aim. Usually, MySQL is used by smaller businesses that do not have large data sets, because of its cost efficiency and simple setup.

MySQL Jobs and Opportunities

MySQL professionals are in high demand as more and more organizations are using this open-source database system for efficient data management. If you have the skills, you could earn an average salary of around $150,000 per year, but it can vary depending on your location, experience, and job role.

Here are some of the top companies actively looking for MySQL experts for roles like Database Administrator, Database Developer, Database Tester, Data Scientist, ETL Developer, Database Migration Expert, Cloud Database Expert, and more. They need people who can manage and optimize their databases, build data-driven applications, and extract insights from large datasets −

  • Google
  • Amazon
  • Netflix
  • Infosys
  • Tata Consultancy Services (TCS)
  • Tech Mahindra
  • Wipro
  • Pinterest
  • Uber
  • Wipro
  • Trello
  • And many more…

To get started, you can use our user-friendly tutorials, which are designed to help you learn MySQL and prepare for technical interviews or certification exams. You can learn at your own pace, anytime and anywhere.

With the right MySQL skills and knowledge, you can kickstart a rewarding career in the ever-expanding field of data management and analytics. You could be the part of the professionals who are driving innovation and data-driven decision-making in some of the world’s most renowned companies.

MySQL – Introduction

What is a Database?

A database is used to store a collection of data (which can either be structured or unstructured). Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds.

Other kinds of data storages can also be used to manage data, such as files on the file system or large hash tables in memory, but data fetching and writing would not be so fast and easy with those type of systems.

Nowadays, we use relational database management systems (RDBMS) to store and manage huge volume of data. In such a database, the data is stored in a structured way with the help of different tables. Relations are established among these tables using primary keys or other keys known as Foreign Keys.

A Relational DataBase Management System (RDBMS) is a software that −

  • Enables you to implement a database with tables, columns and indexes.

  • Guarantees the Referential Integrity between rows of various tables.

  • Updates the indexes automatically.

  • Interprets an SQL query and combines information from various tables.

RDBMS Terminology

Before we proceed to explain the MySQL database system, let us revise a few definitions related to the database.

  • Database − A database is a collection of tables, with related data.

  • Table − A table is a matrix with data. A table in a database looks like a simple spreadsheet.

  • Column − One column (data element) contains data of one and the same kind, for example the column postcode.

  • Row − A row (= tuple, entry or record) is a group of related data, for example the data of one subscription.

  • Redundancy − Storing data twice, redundantly to make the system faster.

  • Primary Key − A primary key is unique. A key value can not occur twice in one table. With a key, you can only find one row.

  • Foreign Key − A foreign key is the linking pin between two tables.

  • Compound Key − A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.

  • Index − An index in a database resembles an index at the back of a book.

  • Referential Integrity − Referential Integrity makes sure that a foreign key value always points to an existing row.

MySQL Database

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. It is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons −

  • MySQL is released under an open-source license. So you have nothing to pay to use it.

  • MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.

  • MySQL uses a standard form of the well-known SQL data language.

  • MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.

  • MySQL works very quickly and works well even with large data sets.

  • MySQL is very friendly to PHP, the most appreciated language for web development.

  • MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).

  • MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.

History of MySQL

  • Development of MySQL by Michael Widenius & David Axmark beginning in 1994.

  • First internal release on 23rd May 1995.

  • Windows Version was released on the 8th January 1998 for Windows 95 and NT.

  • Version 3.23: beta from June 2000, production release January 2001.

  • Version 4.0: beta from August 2002, production release March 2003 (unions).

  • Version 4.1: beta from June 2004, production release October 2004.

  • Version 5.0: beta from March 2005, production release October 2005.

  • Sun Microsystems acquired MySQL AB on the 26th February 2008.

  • Version 5.1: production release 27th November 2008.

  • Oracle acquired Sun Microsystems on 27th January 2010.

  • Version 5.5: general availability on 3rd December 2010

  • Version 5.6: general availability on 5th February 2013

  • Version 5.7: general availability on 21st October 2015

  • Version 8.0: general availability on 19th April 2018

Before You Begin

Before you begin this tutorial, you should have a basic knowledge of the information covered in our PHP and HTML tutorials.

This tutorial focuses heavily on using MySQL in a PHP environment. Many examples given in this tutorial will be useful for PHP Programmers.

MySQL – Features

MySQL is a type of relational database that stores and manages the data based on Structured Query Language (SQL) queries. Thus, making it a structured database, i.e., the data stored in this relational databases is in the form of tables.

It is a fast, easy-to-use RDBMS being used for many small and big businesses, it is developed, marketed and supported by a Swedish Company called MySQL AB.

Features of MySQL

One of the major reasons MySQL is considered one of the most popular relational databases is because of its abundant features. Let us look at them one by one −

Open-Source

MySQL is open-source, which means this software can be downloaded, used and modified by anyone. It is free-to-use and easy-to-understand. The source code of MySQL can be studied, and changed based on the requirements. It uses GPL, i.e. GNU General Public license which defines rules and regulations regarding what can and can’t be done using the application.

Quick and Reliable

MySQL stores data efficiently in the memory ensuring that data is consistent, and not redundant. Hence, data access and manipulation using MySQL is quick. It is considered one of the fastest relational databases with higher productivity as well.

High Performance

MySQL provides comparatively higher performance without affecting its functionality. It also has a very little memory leakage making it memory efficient as well.

Scalable

Scalability refers to the ability of systems to work easily with small amounts of data, large amounts of data, clusters of machines, and so on. MySQL server was developed to work with large databases.

Data Types

It contains multiple data types such as unsigned integers, signed integers, float (FLOAT), double (DOUBLE), character (CHAR), variable character (VARCHAR), text, blob, date, time, datetime, timestamp, year, and so on.

Character Sets

It supports different character sets, and this includes latin1 (cp1252 character encoding), German, Ujis, other Unicode character sets and so on.

Secure

It provides a secure interface since it has a password system which is flexible, and ensures that it is verified based on the host before accessing the database. The password is encrypted while connecting to the server.

Support for large databases

It comes with support for large databases, which could contain about 40 to 50 million records, 150,000 to 200,000 tables and up to 5,000,000,000 rows.

Platform Independent

MySQL can be run on various operating systems including Windows, Linux, macOS etc. in several programming languages like C, C++, Java, Python, Perl, PHP etc.

Client and Utility Programs

MySQL server also comes with many client and utility programs. This includes Command line programs such as ‘mysqladmin’ and graphical programs such as ‘MySQL Workbench’. MySQL client programs are written in a variety of languages. Client library (code encapsulated in a module) can be written in C or C++ and would be available for clients that have C bindings.

MySQL – Versions

Versions are introduced in any product to upgrade by adding extra features and removing unnecessary ones, fixing the bugs etc. The process of versioning is actually important to make the product more efficient with growing technology.

A product is generally released after performing phases of testing: alpha testing, beta testing, gamma testing, and then it is produced once all these tests are passed.

Whenever MySQL is installed, we must choose the version to install along with its distribution format. The latest version of MySQL is 8.0 with its minor version being 8.0.34. To install the MySQL server, there are two methods: Development release and General Availability.

The Development Release contains all the latest features but it is not recommended to be used in production. The General Availability Release is more of a production release which can be stably used in production.

MySQL Versions

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. It is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL was first internally released on 23rd May, 1995, and until Oracle acquired Sun Microsystems, version 3.19 to version 5.1 were released.

Version 5.1

The version 5.1 of MySQL had its production release on 27th November, 2008 by adding extra features like event scheduler, partitioning, plugin API, row-based replication, server log tables.

But, version 5.1 contained 20 known bugs that gave out wrong results, along with the 35 bugs from version 5.0. However, almost all of them are fixed as of release 5.1.51. Also, MySQL 5.1 and 6.0 (in alpha test phase) showed poor performance in data warehousing, which could partially be due to its inability to utilize multiple CPU cores for processing a single query.

Version 5.5

MySQL Server 5.5 was a General Availability Release as of December 2010. The improved features in this version include −

  • The default storage engine is InnoDB with improved I/O subsystem, which supports transactions and referential integrity constraints.

  • Improved SMP support

  • Semi-synchronous replication.

  • SIGNAL and RESIGNAL statement was added in compliance with the SQL standard.

  • Support for supplementary Unicode character sets utf16, utf32, and utf8mb4.

  • New options for user-defined partitioning.

Version 5.6

The General Availability of MySQL 5.6 was released in February 2013. New features of this version included:

  • Query optimizer has more efficient performance.

  • Higher transactional throughput in InnoDB.

  • New NoSQL-style memcached APIs.

  • Improvements to partitioning for querying large tables.

  • Better management of very large tables.

  • TIMESTAMP column type that correctly stores milliseconds.

  • Improvements to replication.

  • Better performance monitoring by expanding the data available through the PERFORMANCE_SCHEMA.

  • The InnoDB storage engine also provides support for full-text search and improved group commit performance.

Version 5.7

MySQL 5.7 was made generally available in October 2015. For the minor versions of MySQL 5.7, MySQL 5.7.8 and later, a support for a native JSON data type defined by RFC 7159 by August 2015.

Version 8.0

MySQL Server 8.0 was announced in April 2018, with new improved features. Currently, the minor versions in MySQL 8.0 start from 8.0.0 to 8.0.34. Previous MySQL Server 8.0.0-dmr (as a Development Milestone Release) was announced on 12th September, 2016.

Features Added in MySQL 8.0

The latest version of MySQL is 8.0. The following features are some of the newly added features to it:

  • Data dictionary − In previous MySQL releases, dictionary data was stored in metadata files and non-transactional tables. MySQL now incorporates a transactional data dictionary that stores information about database objects.

  • Atomic Data Definition Language(Atomic DDL) statements − An Atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction.

  • Upgrade procedure − Previously, after installation of a new version of MySQL, the MySQL server automatically upgrades the data dictionary tables at the next startup, after which the DBA is expected to invoke mysql_upgrade manually to upgrade the system tables in the mysql schema, as well as objects in other schemas such as the ‘sys’ schema and user schemas.

    As of MySQL 8.0.16, the server also performs the tasks previously handled by mysql_upgrade. In addition, the server updates the contents of the help tables as well. A new –upgrade server option provides control over how the server performs automatic data dictionary and server upgrade operations.

  • Session Reuse − MySQL Server now supports SSL session reuse by default with a timeout setting to control how long the server maintains a session cache that establishes the period during which a client is permitted to request session reuse for new connections. All MySQL client programs support session reuse. In addition, C applications now can use the C API capabilities to enable session reuse for encrypted connections.

  • Security and account management − The security is improved greatly and greater DBA flexibility in account management is also enabled.

  • Resource management − MySQL now supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group.

  • Table encryption management − Table encryption can now be managed globally by defining and enforcing encryption defaults.

  • InnoDB enhancements − Several InnoDB enhancements were added, like, auto-increment counter value, index tree corruption, mem-cached plug-in, InnoDB_deadlock_detect, tablespace encryption feature, storage engine, InnoDB_dedicated_server, creating temporary tables in temporary tablespace, zlib library etc.

  • Character set support − The default character set has changed from latin1 to utf8mb4. The utf8mb4 character set has several new collations, including utf8mb4_ja_0900_as_cs, the first Japanese language-specific collation available for Unicode in MySQL.

  • JSON enhancements − Several enhancements and additions were made to MySQL’s JSON functionality.

  • Data type support − MySQL now supports use of expressions as default values in data type specifications. This includes the use of expressions as default values for the BLOB, TEXT, GEOMETRY, and JSON data types, which previously could not be assigned default values at all.

  • Optimizer − Optimizer is enhanced in various ways as well.

  • Improved hash join performance − MySQL 8.0.23 reimplements the hash table used for hash joins, resulting in several improvements in hash join performance.

  • Common table expressions − MySQL now supports common table expressions, both non-recursive and recursive.

  • Window functions − MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row.

  • Lateral derived tables − A derived table now may be preceded by the LATERAL keyword to specify that it is permitted to refer to (depend on) columns of preceding tables in the same FROM clause.

  • Aliases in single-table DELETE statements − In MySQL 8.0.16 and later, single-table DELETE statements support the use of table aliases.

  • Regular expression support − Previously, MySQL used the Henry Spencer regular expression library to support regular expression operators.

  • Internal temporary tables − The TempTable storage engine replaces the MEMORY storage engine as the default engine for in-memory internal temporary tables.

  • Logging − Logging process is also improved in the newer versions.

In addition to all these, there are many other features added to the new versions of MySQL as well.

Features Deprecated in MySQL 8.0

The following are some of the many features that are deprecated in MySQL 8.0 and may be removed in the future series. Several alternatives mentioned can be used by applications.

  • The utf8mb3 character set is deprecated. Please use utf8mb4 instead.

  • ucs2, macroman and macce, dec, hp8 are also deprecated. You should use utf8mb4 instead.

  • User-defined collations are deprecated.

  • sha256_password is deprecated.

  • The plugin form of validate_password is still available, but deprecated.

  • The ENGINE clause for the ALTER TABLESPACE and DROP TABLESPACE statements is deprecated.

  • The PAD_CHAR_TO_FULL_LENGTH SQL mode is deprecated.

  • AUTO_INCREMENT support is deprecated for columns of type FLOAT and DOUBLE (and any synonyms).

  • The UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms)

  • FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated.

  • The ZEROFILL attribute for numeric data types and the display width attribute for integer data types are deprecated.

  • The BINARY attribute is deprecated. However, the use of BINARY to specify a data type or character set remains unchanged.

  • ASCII and UNICODE are deprecated (MySQL 8.0.28 and later). Use CHARACTER SET instead, in both cases.