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
SELECTstatement to query the data from a single database table.
- Eliminating duplicate rows with DISTINCT Operator – learns how to use the
DISTINCToperator in the
SELECTstatement to eliminate duplicate rows in a result set.
Section 3. Filtering data
- Filtering rows using WHERE – learns how to use the
WHEREclause to filter rows based on specified conditions.
- Using AND operator – introduces you to the
ANDoperator to combine Boolean expressions to form a complex condition for filtering data.
- Using OR operator – introduces you to the
ORoperator and shows you how to combine the
ORoperator with the
ANDoperator to filter data.
- Querying data with IN operator – shows you how to use the
INoperator in the
WHEREclause to determine if a value matches any value in a list or a subquery.
- Selecting data with BETWEEN Operator – shows you how to query data based on a range using
- Using LIKE operator to select data based on patterns – provides you with technique to query data based on a specific pattern.
- Constraining data using LIMIT – using
LIMITto constrain the number of rows returned by
- Testing NULL using IS NULL – tests whether a value is
NULLor not by using
Section 4. Sorting data
- Sorting rows with MySQL ORDER BY – shows you how to sort the result set using
ORDER BYclause. The custom sort order with the
FIELDfunction will be also covered.
- Implementing natural sorting with ORDER BY clause – walks you through various natural sorting techniques in MySQL by using the
Section 5. Joining tables
- Making the queries more readable with MySQL alias – 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
- 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 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 UNION and UNION ALL – helps you combine two or more result sets from multiple
SELECTstatements into a single one using
- MySQL INTERSECT simulation – this tutorial shows you a couple of ways to simulate the
INTERSECToperator 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
INSERTstatement to insert data into database tables.
- Updating data using MySQL UPDATE statement – you will learn how to use
UPDATEstatement 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 JOINstatement with
- Removing data using MySQL DELETE – this tutorial shows you how to use the
DELETEstatement to remove data from one or more tables.
- Removing data from multiple tables using MySQL ON DELETE CASCADE – learn how to use
ON DELETE CASCADEreferential 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 JOINstatement with
- MySQL Replace – you will learn how to use the
REPLACEstatement 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
ROLLBACKto 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 a table by using CREATE TABLE statement – shows you how to create new tables in a database using
- MySQL sequence – shows you how to use a sequence to generate unique numbers automatically for the primary key column of a table.
- Using ALTER TABLE to change table structure – you will learn about the
ALTER TABLEstatement 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
- Removing a column from a table – shows you how to use the
ALTER TABLE DROP COLUMNstatement 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 COLUMNstatement.
- Using DROP TABLE statement to remove tables – shows you how to remove existing tables using
- MySQL temporary table – discusses MySQL temporary table and shows you step by step how to manage temporary tables.
- TRUNCATE TABLE – this tutorial shows you how to use the
TRUNCATE TABLEstatement 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
UNIQUEindex to enforce the uniqueness of value in one or more columns.
Section 13. MySQL data types
- MySQL data types – shows you various data types in MySQL so that you can apply them effectively in designing database tables.
- INT – shows you how to use integer data type. We also show you how to use
ZEROFILLand display width attributes of the integer column.
- DECIMAL – shows you how to use
DECIMALdata type to store exact values in decimal format.
- BIT – introduces you
BITdata type and how to store bit values in MySQL.
- BOOLEAN – explains you how MySQL handles Boolean values by using
- CHAR – guides to
CHARdata type for storing the fixed-length string.
- VARCHAR – gives you the essential guide to
- TEXT – shows you how to store text data using
- DATE – introduces you to the
DATEdata type and show you some date functions to handle the date data effectively.
- TIME – walks you through the features of
TIMEdata type and shows you how to use some useful temporal functions to handle time data.
- DATETIME – introduces you to the
DATETIMEdata type and some useful functions to manipulate
- TIMESTAMP – introduces you to
TIMESTAMPand 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
- NOT NULL constraint – introduces you to the
NOT NULLconstraint and shows you how to define a
NOT NULLconstraint for a column or add the
NOT NULLconstraint to an existing column.
- Primary key constraint – guides you how to use primary key constraint to create the primary key for a table.
- Foreign key constraint – introduces you to the foreign key and shows you step by step how to create and drop foreign keys.
- UNIQUE constraint – shows you how to use the
UNIQUEconstraint to enforce the uniqueness of values in a column or a group of columns in a table.
- CHECK constraint emulation – walk you through various ways to emulate the
CHECKconstraint 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.