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 2. Sorting data
- ORDER BY – show you how to sort the result set using
ORDER BYclause. The custom sort order with the
FIELDfunction will be also covered.
Section 3. Filtering data
- WHERE – learn how to use the
WHEREclause to filter rows based on specified conditions.
- SELECT DISTINCT – show you how to use the
DISTINCToperator in the
SELECTstatement to eliminate duplicate rows in a result set.
- AND – introduce you to the
ANDoperator to combine Boolean expressions to form a complex condition for filtering data.
- OR– introduce you to the
ORoperator and show you how to combine the
ORoperator with the
ANDoperator to filter data.
- IN – show you how to use the
INoperator in the
WHEREclause 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
- LIKE – provide you with technique to query data based on a pattern.
- LIMIT – use
LIMITto constrain the number of rows returned by
- IS NULL – test whether a value is
NULLor not by using
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 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 found in the left table.
- CROSS JOIN – make a Cartesian product of rows from multiple tables.
- Self-join – join a table to itself using table alias and connect rows within the same table using inner join and left join.
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 9. 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
INSERTstatement 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 you the
INSERT IGNOREstatement that inserts rows into a table and ignores rows that cause errors.
- UPDATE – learn how to use
UPDATEstatement and its options to update data in database tables.
- UPDATE JOIN – show you how to perform cross-table update using
UPDATE JOINstatement with
- DELETE – show you how to use the
DELETEstatement to delete rows from one or more tables.
- 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.
- DELETE JOIN – show you how to delete data from multiple tables.
- REPLACE – learn how to insert or update data depends on whether data exists in the table or not.
- Prepared Statement – show you how to use the prepared statement to execute a query.
Section 11. Managing databases
This section shows you how to manage MySQL databases.
Section 12. 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
- AUTO_INCREMENT – show you how to use an AUTO_INCREMENT column generate unique numbers automatically for the primary key.
- ALTER TABLE – learn how to change the structure of a table using the
- Renaming tables – show you how to rename a table using
- Removing a column from a table – show 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 – show you how to add one or more columns to an existing table using
ALTER TABLE ADD COLUMNstatement.
- DROP TABLE – show you how to remove existing tables using
- 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 fast and more efficient using the
- Generated columns – guide you on how to use the generated columns to store data computed from an expression or other columns.
Section 13. MySQL data types
- MySQL data types – show you various data types in MySQL so that you can apply them effectively in designing database tables.
- INT – show you how to use integer data type.
- DECIMAL – show you how to use
DECIMALdatatype to store exact values in decimal format.
- BIT – introduce you
BITdatatype and how to store bit values in MySQL.
- BOOLEAN – explain to you how MySQL handles Boolean values by using
- CHAR – a guide to
CHARdata type for storing the fixed-length string.
- VARCHAR – give you the essential guide to
- TEXT – show you how to store text data using
- DATE – introduce you to the
DATEdatatype and show you some date functions to handle the date data effectively.
- TIME – walk you through the features of
TIMEdatatype and show you how to use some useful temporal functions to handle time data.
- DATETIME – introduce you to the
DATETIMEdatatype and some useful functions to manipulate
- TIMESTAMP – introduce you to
TIMESTAMPand its features called automatic initialization and automatic update that allows you to define auto-initialized and auto-updated columns for a table.
- JSON – show you how to use JSON data type to store JSON documents.
- ENUM – learn how to use
ENUMdatatype correctly to store enumeration values.
Section 14. MySQL constraints
- NOT NULL– introduce you to the
NOT NULLconstraint and show you how to declare a
NOT NULLcolumn or add a
NOT NULLconstraint to an existing column.
- 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.
- UNIQUE constraint – show you how to use
UNIQUEconstraint to enforce the uniqueness of values in a column or a group of columns in a table.
- CHECK constraint – learn how to create
CHECKconstraints to ensure data integrity.
- DEFAULT – show you how to set a default value for a column using the DEFAULT constraint.
- CHECK constraint emulation – if you use MySQL 8.0.15 or earlier version, you can emulate
CHECKconstraints using views or triggers.
Section 17. Advanced techniques
- Natural sorting – walk you through various natural sorting techniques in MySQL using the