Overview

This Document 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 know to work with MySQL 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 tutorial to refresh your knowledge and understand how SQL dialect of MySQL is different from other 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 – show you step by step how to install MySQL database server on your computer.
• Downloading MySQL sample database – introduce you to a MySQL sample database named.classicmodels We also provide you links for downloading the sample database and its diagrams.
• Loading the sample database into your own MySQL database server – walk you through steps of how to load the sampleclassicmodels 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 statementSELECT that allows you to query data from a single table.

• SELECT – show you how to use a simple SELECT statement to query the data from a single table.
• SELECT  DISTINCT  – learn how to use the operatorDISTINCT in the statementSELECT to eliminate duplicate rows in a result set.

Section 3. Filtering data

• WHERE – learn how to use the clauseWHERE to filter rows based on specified conditions.
• AND – introduce you to the operatorAND to combine Boolean expressions to form a complex condition for filtering data.
• OR– introduce you to the operatorOR and show you how to combine the operatorOR with the operatorAND to filter data.
• IN – show you how to use the operatorIN in the clauseWHERE to determine if a value matches any value in a list or a subquery.
• BETWEEN – show you how to query data based on a range using operatorBETWEEN.
• LIKE  – provide you with technique to query data based on a specific pattern.
• LIMIT – use LIMIT to constrain the number of rows returned by SELECT statement
• IS NULL – test whether a value is orNULL not by using operatorIS NULL.

Section 4. Sorting data

• ORDER BY – show you how to sort the result set using clauseORDER BY. The custom sort order with the functionFIELD will be also covered.
• Natural sorting using ORDER BY clause – walk you through various natural sorting techniques in MySQL by using the clauseORDER BY.

Section 5. Joining tables

• MySQL alias – introduce you to table alias and aliases to improve the readability of complex queries.
• MySQL Join  – give you an overview of joins supported in MySQL including cross join, inner join, left join, and right join.
• INNER JOIN – use inner join clause to query data from multiple related tables.
• LEFT JOIN – learn how to apply left join clause in various contexts.
• RIGHT JOIN – show you how to use right join to query data from two or more tables.
• CROSS JOIN – make a Cartesian product of rows from multiple tables.
• Self-join – joins a table to itself using table alias, and connects rows within the same table using other kinds of joins including inner join and left join.

Section 6. Grouping data

• GROUP BY clause – show you how to group rows into groups based on columns or expressions.
• HAVING – filter the groups by a specific condition.

 Section 7. MySQL subquery, derived table, and CTE

• MySQL 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.
• MySQL derived table – introduce you the derived table concept and show you how to use it to simplify complex queries.
• MySQL CTE – explain you the common table expression concept and show you how to use CTE for querying data from tables.
• Recursive CTE – guide you how to use the recursive CTE to traverse the hierarchical data.

Section 8. Using Set operators

• UNION and UNION ALL – combine two or more result sets of multiple queries into a single result set.
• INTERSECT –  show you a couple of ways to simulate the operatorINTERSECT in MySQL.
• MINUS – explain to you the SQL MINUS operator and show you how to simulate it 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.

• INSERT – learn how to use various forms of the statementINSERT to insert data into database tables.
• INSERT IGNORE  – explain you the statementINSERT IGNORE that inserts rows into a table and ignore rows that cause errors or exceptions.
• UPDATE – learn how to use statementUPDATE and its options to update data in database tables.
• UPDATE JOIN – show you how to perform cross table update using statementUPDATE JOIN with andINNER JOINLEFT JOIN.
• DELETE – show you how to use the statementDELETE to remove data 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 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 10. MySQL transaction

• MySQL transaction – learn about MySQL transactions, and how to use COMMIT and ROLLBACK to manage transactions in MySQL.
• MySQL table locking – 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.
• CREATE TABLE – show you how to create new tables in a database using statementCREATE TABLE.
• MySQL sequence – show you how to use a sequence to generate unique numbers automatically for the primary key column of a table.
• ALTER TABLE – learn how to use the statementALTER TABLE to change existing table’s structure.
• Renaming table –  show you how to rename a table using statementRENAME TABLE.
• Removing a column from a table – show you how to use the statementALTER TABLE DROP COLUMN 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 statementALTER TABLE ADD COLUMN.
• DROP TABLE – show you how to remove existing tables using statementDROP TABLE.
• MySQL temporary table – discuss MySQL temporary table and show you how to manage temporary tables.
• TRUNCATE TABLE – show you how to use the statementTRUNCATE TABLE to delete all data in a table fast.

Section 12. MySQL indexes

• Managing MySQL database indexes – learn how to work with MySQL indexes and how to take advantages of indexes to speed up the data retrieval.
• UNIQUE index – show you how to use the indexUNIQUE to enforce the uniqueness of value in one or more 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. We also show you how to use ZEROFILL and display width attributes of the integer column.
• DECIMAL – show you how to use DECIMAL data type to store exact values in decimal format.
• BIT – introduce you BIT data type and how to store bit values in MySQL.
• BOOLEAN – explain to you how MySQL handles Boolean values by using TINYINT(1) internally.
• CHAR – guide to CHAR data type for storing the fixed-length string.
• VARCHAR – give you the essential guide to VARCHAR data type.
• TEXT – show you how to store text data using TEXT data type.
• DATE – introduce you to the DATE data type and show you some date functions to handle the date data effectively.
• TIME – walk you through the features of TIME data type and show you how to use some useful temporal functions to handle time data.
• DATETIME – introduce you to the DATETIME data type and some useful functions to manipulate valuesDATETIME.
• TIMESTAMP – introduce you to TIMESTAMP and 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 ENUM data type correctly to store enumeration values.

Section 14. MySQL constraints

• NOT NULL constraint – introduce you to the constraintNOT NULL and show you how to define a  NOT NULL constraint for a column or add the constraintNOT NULL to an existing column.
• Primary key constraint  – guide you how to use primary key constraint to create the primary key for a table.
• Foreign key constraint  – introduce you to the foreign key and show you step by step how to create and drop foreign keys.
• UNIQUE constraint – show you how to use constraintUNIQUE 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 constraintCHECK in MySQL.

Section 15. MySQL globalization

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

Section 16. MySQL import & export

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

Source: Basic MySQL Tutorial