MySQL Tutorial

MySQL is one of the most popular open-source relational database management systems and is widely used in web development.

In this track you will learn MySQL installation, administration, SQL syntax, and how PHP works with MySQL.

What is a database?

A database is an organized store of data structured for efficient access and management.

Each database exposes APIs to create, access, manage, search, and replicate stored data.

You can store data in plain files, but reading and writing files is relatively slow for large workloads.

That is why we use relational database management systems (RDBMS) for big data sets. A relational database is built on the relational model and uses set-based operations to manage data.

RDBMS characteristics

• Data appears in tables
• Each row is a record
• Each column is a field with a consistent data type
• Many rows and columns form a table
• Many tables form a database

RDBMS terminology

Before diving into MySQL, learn these common terms:

• Database: a collection of related tables
• Table: a matrix of data, like a spreadsheet
• Column: a field of the same data type (e.g. postal codes)
• Row: one record (e.g. one user subscription)
• Redundancy: storing duplicate data — less efficient but can improve safety
• Primary key: unique identifier for a row; each table has at most one
• Foreign key: links two tables
• Composite key: multiple columns used together as an index key
• Index: sorted structure for fast lookup, like a book index
• Referential integrity: relationships must not reference missing rows

MySQL is a relational database: data lives in tables rather than one giant file:

• Header: column names
• Column: values of one type
• Row: one record
• Value: cell data matching the column type
• Key: unique value(s) in a column

About MySQL

MySQL was created by MySQL AB (Sweden) and is now owned by Oracle. It stores related data in separate tables instead of one monolithic store, which improves speed and flexibility.

• Open source (GPL); Oracle maintains the product line
• Handles very large databases (tens of millions of rows)
• Uses standard SQL
• Runs on many OS platforms and supports many languages (C, C++, Python, Java, Perl, PHP, Ruby, Tcl, …)
• Excellent PHP support for web apps
• Large deployments: 50M-row warehouses; up to 4 GB table files on 32-bit systems, 8 TB on 64-bit
• Source can be customized under GPL

Before you start

You should know basic PHP and HTML. Many examples use PHP to talk to MySQL.

If PHP is new to you, see our JavaScript track for web fundamentals first, then PHP-oriented examples will make more sense.

Learning path

1. MySQL Installation
2. MySQL Administration
3. MySQL PHP Syntax
4. MySQL Connection
5. MySQL Create Database
6. MySQL Drop Database
7. MySQL Select Database
8. MySQL Data Types
9. MySQL Create Tables
10. MySQL Drop Tables
11. MySQL INSERT
12. MySQL SELECT
13. MySQL WHERE Clause
14. MySQL UPDATE
15. MySQL DELETE
16. MySQL LIKE Clause
17. MySQL UNION
18. MySQL ORDER BY
19. MySQL GROUP BY
20. MySQL JOIN
21. MySQL NULL Values
22. MySQL Regular Expressions
23. MySQL Transactions
24. MySQL ALTER
25. MySQL Indexes
26. MySQL Temporary Tables
27. MySQL Clone Tables
28. MySQL Metadata
29. MySQL Sequences
30. MySQL Handling Duplicates
31. MySQL & SQL Injection
32. MySQL Export Data
33. MySQL Import Data
34. MySQL Functions
35. MySQL Operators Next: MySQL Installation