This basic MySQL tutorial section explains some of the basic SQL statements. If this is the first time you have used a database, this section gives you the basic information you need to interact with MySQL database server such as managing databases, creating tables, and querying data. Alternatively, if you’re already familiar with other RDBMS such as PostgreSQL, Oracle, or Microsoft SQL Server, you can use this section to refresh your knowledge and also understand how SQL dialect of MySQL is different from other database management systems.
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 in 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.
Querying data from MySQL database
This section helps you learn how to query data from the MySQL database sever. We will start with a simple
SELECT statement that allows you to query data from a single table. And then we will demonstrate some advanced techniques to query data from multiple tables such as join and union.
- Using MySQL SELECT statement to query data – shows you how to simple
SELECTstatement to query the data from a single database table.
- Filtering rows using MySQL WHERE – learns how to use
WHEREclause to filter rows based on specified conditions.
- 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.
- Eliminating duplicate rows with DISTINCT Operator – learns how to use
DISTINCToperator in the
SELECTstatement to eliminate duplicate rows in a result set.
- Using MySQL LIMIT to constrain the number of rows returned by SELECT statement
- Querying data with MySQL IN operator – shows you how to use
WHEREclause 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 ranges using
- Using MySQL LIKE operator to select data based on patterns – provides you with technique to query data based on specific pattern.
- 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 tables that have same column names.
- Combining result sets using MySQL UNION and UNION ALL – helps you combine two or more result sets from multiple
SELECTstatements into a single one using
- 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 left join to produce a result set that contains rows from the table on the left side of the join and fill 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.
- 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.
- Nesting a query within another using MySQL subquery – shows you how to nest a query (inner query) within another query (outer query) and use result of the inner query for the outer query. We will also introduce you to the correlated subquery concept.
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
REPLACEstatement to insert or update data.
- 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.
- MySQL prepared statement – this tutorial shows you how to use prepared statement to execute a query with placeholders to improve the speed of the query and make your query more secure.
Working with MySQL databases, tables, and indexes
This section shows you how to manage the most important database objects in MySQL including database, tables, and indexes. Note that we have specific sections for stored procedures, triggers, and views.
- 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.
- 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
ZEROFILLand display width attributes of the integer column.
- MySQL DECIMAL – shows you how to use
DECIMALdata type to store exact values in decimal format.
- MySQL DATE – introduces you to the
DATEdata type and show you some date functions to handle the date data effectively.
- MySQL TIME – walks you through the features of
TIMEdata type and shows you how to use some useful temporal functions to handle time data.
- MySQL DATETIME – introduces you to the
DATETIMEdata type and some useful functions to manipulate
- MySQL 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.
- Creating tables using MySQL CREATE TABLE statement – shows you how to create a new tables in a database using
- MySQL sequence – shows you how to use sequence to generate unique numbers automatically for the primary key column of a table.
- MySQL primary key – you will learn how to use primary key constraint to create the primary key for the table.
- MySQL foreign key – introduces you to foreign key and shows you step by step how to create and drop foreign keys.
- Using MySQL ALTER TABLE to change table structure – you will learn about the
ALTER TABLEstatement that changes existing table structure such as adding or removing 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 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 MySQL DROP TABLE statement to remove tables – shows you how to remove existing tables using
- MySQL temporary table – discusses about MySQL temporary table and shows you step by step how to manage temporary tables.
- 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 TRUNCATE TABLE – this tutorial shows you how to use the
TRUNCATE TABLEstatement to delete all data in a table.
- MySQL character Set – this tutorial discusses about MySQL character set and shows you step by step how to perform various operations on character sets.
- MySQL collation – this tutorial discusses about MySQL collation and shows you how to set character set and collations for the MySQL server, database, tables and columns.