This basic MySQL tutorial explains some of the basic SQL statements. If this is the first time you have used a relational database management system, this tutorial gives you everything you need to work with MySQL database server such as querying data, updating data, managing databases, and creating tables.
If you’re already familiar with other relational database management systems such as PostgreSQL, Oracle, or Microsoft SQL Server, etc., you can use this entire tutorial to refresh your knowledge and understand how SQL dialect of MySQL is different from other database systems.
Section 1. Getting started with MySQL
This section helps you get started with MySQL. We will start installing MySQL, downloading a sample database, and loading data into the MySQL server for practicing.
- Installing MySQL database server – shows you step by step how to install MySQL database server on your computer.
- Downloading MySQL sample database – introduces you to a MySQL sample database named classicmodels. We will provide you links to download the sample database and its diagram.
- Loading the sample database into your own local MySQL database server – guides you how to load the classicmodels sample database into your MySQL database server for practicing.
Section 2. Querying data
This section helps you learn how to query data from the MySQL database server. We will start with a simple SELECT statement that allows you to query data from a single table.
- Using MySQL SELECT statement to query data – shows you how to simple SELECT statement to query the data from a single database table.
- Eliminating duplicate rows with DISTINCT Operator – learns how to use the DISTINCT operator in the SELECT statement to eliminate duplicate rows in a result set.
Section 3. Filtering data
- Filtering rows using MySQL WHERE – learns how to use the WHERE clause to filter rows based on specified conditions.
- Using AND operator – introduces you to the AND operator to combine Boolean expressions to form a complex condition for filtering data.
- Using OR operator – introduces you to the OR operator and shows you how to combine the OR operator with the AND operator to filter data.
- Querying data with MySQL IN operator – shows you how to use the IN operator in the WHERE clause to determine if a value matches any value in a list or a subquery.
- Querying data with BETWEEN Operator – shows you how to query data based on a range using BETWEEN operator.
- Using MySQL LIKE operator to select data based on patterns – provides you with technique to query data based on a specific pattern.
- MySQL LIMIT – using MySQL LIMIT to constrain the number of rows returned by SELECT statement
Section 4. Sorting data
- Sorting Rows with MySQL ORDER BY – shows you how to sort the result set using ORDER BY clause. The custom sort order with the FIELD function will be also covered.
- MySQL Natural Sorting with ORDER BY Clause – walk you through various natural sorting techniques in MySQL using the ORDER BY clause.
Section 5. Joining tables
- Using MySQL alias to make the queries more readable – introduces you to aliases including table alias and column alias to improve the readability of complex queries and avoid ambiguous error when querying data from multiple tables that have same column names.
- Joining tables using INNER JOIN – applies inner join technique to query data from multiple related tables.
- Joining tables using LEFT JOIN – learns how to use the left join to produce a result set that contains rows from the table on the left side of the join and finds the unmatched rows with NULL values.
- Joining a table to itself using MySQL self-join – joins a table to itself using table alias, and connects rows within the same table using other joins such as inner join or left join.
Section 6. Grouping data
- Grouping rows into subgroups using GROUP BY clause– shows you how to group rows into subgroups based on columns or expressions.
- Filtering groups using MySQL HAVING – filters the groups by a specific condition.
Section 7. MySQL subquery
- Nesting a query within another using MySQL subquery – shows you how to nest a query (inner query) within another query (outer query) and use the result of the inner query for the outer query. We will also introduce you to the correlated subquery concept.
Section 8. Using set operators
- Combining result sets using the MySQL UNION and UNION ALL – helps you combine two or more result sets from multiple SELECT statements into a single one using UNION and UNION ALL.
- MySQL INTERSECT simulation – this tutorial shows you a couple of ways to simulate the INTERSECT operator in MySQL.
Section 9. Modifying data in MySQL
In this section, you will learn how to insert, update, and delete data from tables using various MySQL statements.
- Inserting data into tables using MySQL INSERT statement – you will learn how to use various forms of the INSERT statement to insert data into database tables.
- Updating data using MySQL UPDATE statement – you will learn how to use UPDATE statement and its options to update data in database tables.
- Performing cross table update with MySQL UPDATE JOIN – this tutorial shows you how to perform cross table update using UPDATE JOIN statement with INNER JOIN and LEFT JOIN.
- Removing data using MySQL DELETE – this tutorial shows you how to use the DELETE statement to remove data from one or more tables.
- Removing data from multiple tables using MySQL ON DELETE CASCADE – learn how to use ON DELETE CASCADE referential action for a foreign key to delete data from a child table automatically when you delete data from a parent table.
- Removing data using DELETE JOIN – this tutorial shows you how to delete data from multiple tables using DELETE JOIN statement with INNER JOIN and LEFT JOIN.
- MySQL Replace – you will learn how to use the REPLACE statement to insert or update data.
- MySQL prepared statement – this tutorial shows you how to use the prepared statement to execute a query with placeholders to improve the speed of the query and make your query more secure.
Section 10. MySQL transaction
- MySQL transaction – you will learn about MySQL transactions, and how to use COMMIT and ROLLBACK to manage transactions in MySQL.
- MySQL table locking – you will learn how to use MySQL locking for cooperating table access between sessions.
Section 11. Managing MySQL databases and tables
This section shows you how to manage the most important database objects in MySQL including database and tables.
- Managing database in MySQL – you will learn various statements to manage MySQL databases including creating a new database, removing an existing database, selecting a database, and listing all databases.
- Understanding MySQL Table Types – it is essential to understand the features of each table type so that you can use them effectively to maximize the performance of your databases.
- Creating tables using MySQL CREATE TABLE statement – shows you how to create new tables in a database using CREATE TABLE statement.
- MySQL sequence – shows you how to use a sequence to generate unique numbers automatically for the primary key column of a table.
- Using MySQL ALTER TABLE to change table structure – you will learn about the ALTER TABLE statement that changes existing table structure such as adding or removing a column, changing column attribute, etc.
- Renaming table – we will show you how to rename a table using RENAME TABLE statement.
- Removing a column from a table – shows you how to use the ALTER TABLE DROP COLUMN statement to remove one or more columns from a table.
- Adding a new column to a table – this tutorial shows you how to add one or more columns to existing table using ALTER TABLE ADD COLUMN statement.
- Using MySQL DROP TABLE statement to remove tables – shows you how to remove existing tables using DROP TABLE statement.
- MySQL temporary table – discusses MySQL temporary table and shows you step by step how to manage temporary tables.
- MySQL TRUNCATE TABLE – this tutorial shows you how to use the TRUNCATE TABLE statement to delete all data in a table.
Section 12. MySQL indexes
- Managing MySQL database indexes – you will learn how to work with MySQL indexes and how to take advantages of indexes to speed up the data retrieval.
- MySQL UNIQUE index – shows you how to use the UNIQUE index to enforce the uniqueness of value in one or more columns.
Section 13. MySQL data types
- MySQL data types – shows you various MySQL data types so that you can apply them effectively in designing database tables.
- MySQL INT – shows you how to use integer data type. We also show you how to use ZEROFILL and display width attributes of the integer column.
- MySQL DECIMAL – shows you how to use DECIMAL data type to store exact values in decimal format.
- MySQL DATE – introduces you to the DATE data type and show you some date functions to handle the date data effectively.
- MySQL TIME – walks you through the features of TIME data type and shows you how to use some useful temporal functions to handle time data.
- MySQL DATETIME – introduces you to the DATETIME data type and some useful functions to manipulate DATETIME values.
- MySQL TIMESTAMP – introduces you to TIMESTAMP and its features called automatic initialization and automatic update that allow you to define auto-initialized and auto-updated columns for a table.
Section 14. MySQL constraints
- MySQL primary key – guides you how to use primary key constraint to create the primary key for a table.
- MySQL foreign key – introduces you to the foreign key and shows you step by step how to create and drop foreign keys.
- MySQL UNIQUE constraint – shows you how to use the UNIQUE constraint to enforce the uniqueness of values in a column or a group of columns in a table.
- MySQL CHECK constraint emulation – walk you through various ways to emulate the CHECK constraint in MySQL.
Section 15. MySQL globalization
- MySQL character Set – this tutorial discusses MySQL character set and shows you step by step how to perform various operations on character sets.
- MySQL collation – this tutorial discusses MySQL collation and shows you how to set character set and collations for the MySQL server, database, tables and columns.