MySQL Basics

This MySQL basics section guides you on how to use various SQL statements to manage objects and data in MySQL. This will enable you with everything you need to know to work with MySQL effectively.

Chapter 1: Managing Databases

  • Creating Database – show how to use CREATE DATABASE statement to create a database in MySQL.
  • Selecting a Database – Learn how to connect to a database using CONNECT statement.
  • Renaming Database – show you how to rename an existing database in MySQL Server.
  • Show Database – show you how to list all databases in MySQL Server.
  • Copy Database – learn how to copy a MySQL database.
  • Dropping Database – walk you through how to drop an existing database from MySQL Server.

Chapter 2: Managing Tables

  • MySQL storage engines – discussed different types of storage engines available in MySQL. It is important to know the features of each storage engine so that we can use them effectively to increase the performance of MySQL.
  • Creating table – show you how to create a table in the MySQL database using CREATE TABLE statement.
  • AUTO_INCREMENT – show you how to generate unique numbers automatically for the primary key using AUTO_INCREMENT feature.
  • ALTER TABLE – show you how to modify an existing table in MYSQL using ALTER TABLE statement.
  • Renaming table – this tutorial will show you how to rename an existing table in MySQL using RENAME TABLE statement.
  • Adding a new column in a table – guide you on how now add new columns in an existing MySQL table.
  • Removing a column from a table – show you how to drop one or more columns from an existing table in MySQL.
  • Renaming column – learn how to rename one or multiple columns in MySQL.
  • Truncate table – guide you on how to truncate a table in MySQL.
  • Dropping table – walk you through how to drop tables from the MySQL database using the DROP TABLE statement.
  • Generated columns – guide you on how to use the generated columns to store computed data from an expression or other columns.
  • Temporary tables – show how to use temporary tables in MySQL.

Chapter 3: MySQL Constraints

  • Primary key – introduce you to the primary key concept and how to use the PRIMARY KEY constraint for creating a primary key column.
  • Foreign key – introduce you to the foreign key concept and show you how to create and drop foreign keys.
  • Disable foreign key checks – show you how to disable foreign key checks in MySQL.
  • NOT NULL– show you how to use NOT NULL constraints for columns while creating a new table or change an existing column by adding NOT NULL constraints.
  • DEFAULT – shows you how to set a default value for a column using the DEFAULT constraint.
  • UNIQUE constraint – learn 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 use CHECK constraints to ensure data integrity.

Chapter 4: Querying Data

  • SELECT FROM – shows you how to use a SELECT FROM statement to query from one or more than one table.
  • SELECT – learn how to use the SELECT statement without referencing a table.

Chapter 5: Sorting Data

  • ORDER BY – learn how to sort the result-set in ascending and descending order.

Chapter 6: Filtering data

  • WHERE – show you how to use the WHERE clause to filter rows based on specified conditions.
  • AND – show you how the AND operator is used along with the WHERE to form a complex condition for filtering data.
  • OR – learn how OR operator is used along with WHERE clause and AND operator to filter data.
  • IN – learn 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 IN 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 BETWEEN operator.
  • LIKE – provides you with a technique to query data based on a pattern.
  • LIMIT – use LIMIT to constrain the number of rows returned by the SELECT statement
  • IS NULL – test whether a value is NULL or not by using IS NULL operator.
  • SELECT DISTINCT – learn how to use the DISTINCT operator in the SELECT statement to eliminate duplicate rows in a result set.

Chapter 7: Grouping data

  • GROUP BY – group rows into groups based on columns or expressions.
  • HAVING – filter the groups using result sets using a specific condition.
  • ROLLUP – generate multiple grouping sets considering a hierarchy between columns specified in the GROUP BY clause.

Chapter 8: Set operators

  • UNION and UNION ALL – combine two or more result sets of multiple queries into a single result set.
  • INTERSECT – show you how to use the INTERSECT operator in MySQL.
  • MINUS – Learn how the MINUS operator is used and show you how to simulate it.

Section 9: Modifying data

  • INSERT – learn about various forms of the INSERT statement to insert data into a table.
  • INSERT Multiple Rows – show you how to insert multiple rows into a table.
  • INSERT INTO SELECT – insert data into a table from the result set of a query.
  • INSERT IGNORE –learn about the INSERT IGNORE statement that inserts rows into a table and ignores rows that cause errors.
  • UPDATE – show you how to use the UPDATE statement and its options to update data in database tables.
  • UPDATE JOIN – show you how to perform the 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 – shows 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.
  • Prepared Statement – show you how to use the prepared statement to execute a query.

Chapter 10: 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, etc.
  • 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.
  • CROSS JOIN – make a Cartesian product of rows from multiple tables.
  • Self-join – join a table to itself using a table alias and connect rows within the same table using inner join and left join.

Chapter 11: Subqueries

  • 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.
  • 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.

Chapter 12: Common Table Expressions

  • Common Table Expression or CTE – 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.

Chapter 13: MySQL transactions

  • Transaction – learn about MySQL transactions, and how to use COMMIT and ROLLBACK to manage transactions in MySQL.
  • Table locking – learn how to use MySQL locking for cooperating table access between sessions.

Chapter 14: Common Table Expressions

  • Common Table Expression or CTE – 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.

Chapter 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.

Chapter 16: MySQL import & export CSV

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