MySQL Basics

This MySQL basics section teaches you how to use SQL statements to manage data in MySQL. It’ll provide you with everything you need to know to work with MySQL effectively.

Section 1. Querying data

SELECT FROM – show you how to use a simple SELECT FROM statement to query the data from a single table.
SELECT – learn how to use the SELECT statement without referencing a table.

Section 2. Sorting data

ORDER BY – show you how to sort the result set using the ORDER BY clause. You will also learn how to use custom sort order with the FIELD function.

Section 3. Filtering data

WHERE – learn how to use the WHERE clause to filter rows based on specified conditions.
SELECT DISTINCT – show you how to use the DISTINCT operator in the SELECT statement to eliminate duplicate rows in a result set.
AND – introduce you to the AND operator to combine Boolean expressions to form a complex condition for filtering data.
OR– introduce you to the OR operator and show you how to combine the OR operator with the AND operator to filter data.
IN – show you how to use the IN operator in the WHERE clause to determine if a value matches any value in a set.
NOT IN – negate the IN operator using the NOT operator to check if a value doesn’t match any value in a set.
BETWEEN – show you how to query data based on a range using the BETWEEN operator.
LIKE – query database on pattern matching using wildcards such as % and _.
LIMIT – use LIMIT to limit the number of rows returned by the SELECT statement
IS NULL – test whether a value is NULL or not by using the IS NULL operator.

Section 4. Joining tables

Table & Column Aliases – introduce you to table and column aliases.
Joins – give you an overview of joins supported in MySQL including inner join, left join, and right join.
INNER JOIN – query rows from a table that has matching rows in another table.
LEFT JOIN – return all rows from the left table and matching rows from the right table or null if no matching rows are found in the right table.
RIGHT JOIN – return all rows from the right table and matching rows from the left table or null if no matching rows are found in the left table.
Self-join – join a table to itself using a table alias and connect rows within the same table using inner join and left join.
CROSS JOIN – make a Cartesian product of rows from multiple tables.

Section 5. Grouping data

GROUP BY – show you how to group rows into groups based on columns or expressions.
HAVING – filter the groups by a specific condition.
HAVING COUNT – show you how to use the HAVING clause with the COUNT function to filter groups by the number of items.
ROLLUP – generate multiple grouping sets considering a hierarchy between columns specified in the GROUP BY clause.

Section 6. Subqueries

Subquery – show 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.
Derived table – introduce you to the derived table concept and show you how to use it to simplify complex queries.
EXISTS – test for the existence of rows.

Section 7. Set operators

UNION – combine two or more result sets of multiple queries into a single result set.
EXCEPT – show you how to use the EXCEPT operator to find the set difference between two sets of data.
INTERSECT – show you how to use the INTERSECT operator to find common rows of two or more queries.

Section 8. Managing databases

This section shows you how to manage MySQL databases.

Selecting a database – show you how to use the USE statement to set the current database.
CREATE DATABASE – show you step by step how to create a new database in MySQL Server.
DROP DATABASE – walk you through the steps of deleting a database from the database server.

Section 9. Working with tables

This section shows you how to manage the most important database objects in MySQL, including databases and tables.

MySQL storage engines– it is essential to understand the features of each storage engine so that you can use them effectively to maximize the performance of your databases.
CREATE TABLE – show you how to create new tables in a database using the CREATE TABLE statement.
MySQL data types – show you various data types in MySQL so that you can apply them effectively in designing database tables.
AUTO_INCREMENT – show you how to use an AUTO_INCREMENT column to generate unique numbers automatically for the primary key.
ALTER TABLE – learn how to change the structure of a table using the ALTER TABLE statement.
Renaming tables – show you how to rename a table using the RENAME TABLE statement.
Removing a column from a table – show 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 – show you how to add one or more columns to an existing table using the ALTER TABLE ADD COLUMN statement.
DROP TABLE – show you how to remove existing tables using the DROP TABLE statement.
Temporary tables – discuss MySQL temporary tables and show you how to manage temporary tables effectively.
TRUNCATE TABLE – show you how to delete all data from a table quickly and more efficiently using the TRUNCATE TABLE statement.
Generated columns – guide you on how to use the generated columns to store data computed from an expression or other columns.

Section 10. MySQL constraints

Primary key – guide you on how to use the primary key constraint to create the primary key for a table.
Foreign key – introduce you to the foreign key and show you step by step how to create and drop foreign keys.
Disable foreign key checks – learn how to disable foreign key checks.
NOT NULL– introduce you to the NOT NULL constraint and show you how to declare a NOT NULL column or add a NOT NULL constraint to an existing column.
UNIQUE constraint – show you how to use the UNIQUE constraint to enforce the uniqueness of values in a column or a group of columns in a table.
CHECK constraint – learn how to create CHECK constraints to ensure data integrity.
DEFAULT – show you how to set a default value for a column using the DEFAULT constraint.

Section 11. MySQL data types

BIT – introduce you to BIT datatype and how to store bit values in MySQL.
INT – show you how to use integer data type.
BOOLEAN – explain to you how MySQL handles Boolean values by using TINYINT(1) internally.
DECIMAL – show you how to use DECIMAL datatype to store exact values in decimal format.
CHAR – a guide to CHAR data type for storing the fixed-length string.
VARCHAR – give you the essential guide to VARCHAR datatype.
TEXT – show you how to store text data using TEXT datatype.
DATETIME – introduce you to the DATETIME datatype and some useful functions to manipulate DATETIME values.
TIMESTAMP – introduce you to TIMESTAMP and its features called automatic initialization and automatic update, which allow you to define auto-initialized and auto-updated columns for a table.
DATE – introduce you to the DATE datatype and show you some date functions to handle the date data effectively.
TIME – walk you through the features of TIME datatype and show you how to use some useful temporal functions to handle time data.
BINARY – show you how to store fixed-length byte data.
VARBINARY – learn how to store variable-length byte data.
BLOB – guide you on how to use the BLOB data type to store large binary data such as images, videos, and so on.
ENUM – learn how to use ENUM datatype correctly to store enumeration values.
JSON – show you how to use JSON data type to store JSON documents.
MySQL UUID Smackdown: UUID vs. INT for Primary Key – introduce you to MySQL UUID, shows you how to use it as the primary key (PK) for a table, and discusses the pros and cons of using it as the PK.

Section 12. Modifying data in MySQL

In this section, you will learn how to insert, update, and delete data from tables using various MySQL statements.

INSERT – use various forms of the INSERT statement to insert data into a table.
INSERT Multiple Rows – insert multiple rows into a table.
INSERT INTO SELECT – insert data into a table from the result set of a query.
INSERT IGNORE – explain the INSERT IGNORE statement that inserts rows into a table and ignores rows that cause errors.
Insert datetime values – show you how to insert datetime values into a DATETIME column.
Insert date values – learn how to insert date values into a DATE column.
UPDATE – learn how to use the UPDATE statement to update data in database tables.
UPDATE JOIN – show you how to perform cross-table updates using the UPDATE JOIN statement with INNER JOIN and LEFT JOIN.
DELETE – show you how to use the DELETE statement to delete rows from one or more tables.
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.
DELETE JOIN – show you how to delete data from multiple tables.
REPLACE – learn how to insert or update data depending on whether data exists in the table or not.

Section 13. Common Table Expressions

Common Table Expression – explain to you the common table expression concept and show you how to use CTE for querying data from tables.
Recursive CTE – use the recursive CTE to traverse the hierarchical data.

Section 14. MySQL Locking

Table locking – learn how to use MySQL locking for cooperating table access between sessions.

Section 15. MySQL globalization

Character Set – discuss character set and show you step by step how to perform various operations on character sets.
Collation – discuss collation and show you how to set character sets and collations for the MySQL server, database, tables, and columns.

Section 16. User-defined variables

Import CSV File Into MySQL Table – show you how to use LOAD DATA INFILE statement to import CSV files into a MySQL table.
Export MySQL Table to CSV – learn various techniques of how to export MySQL table to a CSV file format.

Section 17. MySQL import & export CSV

User-defined Variables – learn how to use MySQL user-defined variables in SQL statements.
SELECT INTO Variable – show you how to use the MySQL SELECT INTO variable to store query results in one or more variables.

Section 18. Advanced techniques

Natural sorting – walk you through various natural sorting techniques in MySQL using the ORDER BY clause.
Get Row Count in MySQL – show you various ways to get MySQL row count in the MySQL database.
Compare Two Tables – show you how to compare two tables to find the unmatched records in MySQL
Find Duplicate Values – show you step by step how to find duplicate values in one or more columns in MySQL.
Delete Duplicate Rows – learn how to delete duplicate rows in MySQL by using the DELETE JOIN statement or an immediate table.
Copy Tables – copy tables within the same database or from one database to another using CREATE TABLE LIKE and SELECT statements in MySQL.
Compare Successive Rows within the same table – how to compare successive rows within the same table.
Select Random Records – Show you various techniques to select random records from a database table.
Select The nth Highest Record – learn how to the nth highest record in a database table using various techniques.
Reset Auto Increment Values – reset auto-increment values of AUTO_INCREMENT columns.
MySQL Comments – show you how to use MySQL comments to document an SQL statement or a block of code.
MySQL NULL – learn how to work with MySQL NULL values. In addition, you’ll learn some useful functions to deal with the NULL values effectively.
Map NULL to Meaningful Values – map NULL to other values for a better data representation.
Managing Hierarchical Data in MySQL Using the Adjacency List Model – learn how to use the adjacency list model for managing hierarchical data in MySQL.
MySQL Interval – show you how to use the MySQL interval for date and time arithmetic with many practical examples.
MySQL commands – learn how to use the most commonly used mysql commands for mysql client.