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.<\/p>\n\n\n\n
SELECT FROM<\/code> statement to query the data from a single table.<\/li>\n\n\n\n- SELECT<\/a> – learn how to use the SELECT statement without referencing a table.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 2. Sorting data<\/h2>\n\n\n\n\n- ORDER BY <\/a>– 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.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 3. Filtering data<\/h2>\n\n\n\n\n- WHERE <\/a>– learn how to use the WHERE clause to filter rows based on specified conditions.<\/li>\n\n\n\n
- SELECT DISTINCT<\/a> – show you how to use the DISTINCT operator in the SELECT statement to eliminate duplicate rows in a result set.<\/li>\n\n\n\n
- AND<\/a> – introduce you to the AND operator to combine Boolean expressions to form a complex condition for filtering data.<\/li>\n\n\n\n
- OR<\/a>– introduce you to the OR operator and show you how to combine the OR operator with the AND operator to filter data.<\/li>\n\n\n\n
- IN <\/a>– show you how to use the IN operator in the WHERE clause to determine if a value matches any value in a set.<\/li>\n\n\n\n
- NOT IN<\/a> – negate the IN operator using the NOT operator to check if a value doesn’t match any value in a set.<\/li>\n\n\n\n
- BETWEEN<\/a> – show you how to query data based on a range using the BETWEEN operator.<\/li>\n\n\n\n
- LIKE \u00a0<\/a>– query database on pattern matching using wildcards such as
%<\/code> and _<\/code>.<\/li>\n\n\n\n- LIMIT<\/a> – use\u00a0LIMIT to limit the number of rows returned by the SELECT statement<\/li>\n\n\n\n
- IS NULL<\/a> – test whether a value is NULL or not by using the IS NULL operator.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 4. Joining tables<\/h2>\n\n\n\n\n- Table & Column Aliases <\/a>– introduce you to table and column aliases.<\/li>\n\n\n\n
- Joins<\/a> – give you an overview of joins supported in MySQL including inner join, left join, and right join.<\/li>\n\n\n\n
- INNER JOIN <\/a>– query rows from a table that has matching rows in another table.<\/li>\n\n\n\n
- LEFT JOIN <\/a>– 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.<\/li>\n\n\n\n
- RIGHT JOIN<\/a> – 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.<\/li>\n\n\n\n
- Self-join <\/a>– join a table to itself using a table alias and connect rows within the same table using inner join and left join.<\/li>\n\n\n\n
- CROSS JOIN<\/a> – make a Cartesian product of rows from multiple tables.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 5. Grouping data<\/h2>\n\n\n\n\n- GROUP BY<\/a> – show you how to group rows into groups based on columns or expressions.<\/li>\n\n\n\n
- HAVING <\/a>– filter the groups by a specific condition.<\/li>\n\n\n\n
- HAVING COUNT<\/a> – show you how to use the HAVING clause with the COUNT function to filter groups by the number of items.<\/li>\n\n\n\n
- ROLLUP<\/a> –\u00a0generate multiple grouping sets considering a hierarchy between columns specified in the GROUP BY clause.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 6. Subqueries<\/h2>\n\n\n\n\n- Subquery <\/a>– 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.<\/li>\n\n\n\n
- Derived table<\/a> – introduce you to the derived table concept and show you how to use it to simplify complex queries.<\/li>\n\n\n\n
- EXISTS<\/a> – test for the existence of rows.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 7. Set operators<\/h2>\n\n\n\n\n- UNION <\/a>– combine two or more result sets of multiple queries into a single result set.<\/li>\n\n\n\n
- EXCEPT<\/a> – show you how to use the EXCEPT operator to find the set difference between two sets of data.<\/li>\n\n\n\n
- INTERSECT<\/a> – show you how to use the INTERSECT operator to find common rows of two or more queries.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 8. Managing databases<\/h2>\n\n\n\n
This section shows you how to manage MySQL databases.<\/p>\n\n\n\n
\n- Selecting a database<\/a> – show you how to use the USE statement to set the current database.<\/li>\n\n\n\n
- CREATE DATABASE<\/a> – show you step by step how to create a new database in MySQL Server.<\/li>\n\n\n\n
- DROP DATABASE<\/a> – walk you through the steps of deleting a database from the database server.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 9. Working with tables<\/h2>\n\n\n\n
This section shows you how to manage the most important database objects in MySQL, including databases and tables.<\/p>\n\n\n\n
\n- MySQL storage engines<\/a>– 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.<\/li>\n\n\n\n
- CREATE TABLE <\/a>– show you how to create new tables in a database using the CREATE TABLE statement.<\/li>\n\n\n\n
- MySQL data types <\/a> – show you various data types in MySQL so that you can apply them effectively in designing database tables.<\/li>\n\n\n\n
- AUTO_INCREMENT <\/a>– show you how to use an AUTO_INCREMENT column to generate unique numbers automatically for the primary key.<\/li>\n\n\n\n
- ALTER TABLE <\/a>– learn how to change the structure of a table using the ALTER TABLE statement.<\/li>\n\n\n\n
- Renaming tables<\/a> – show you how to rename a table using the RENAME TABLE statement.<\/li>\n\n\n\n
- Removing a column from a table<\/a> – show you how to use the ALTER TABLE DROP COLUMN statement to remove one or more columns from a table.<\/li>\n\n\n\n
- Adding a new column to a table<\/a> – show you how to add one or more columns to an existing table using the ALTER TABLE ADD COLUMN statement.<\/li>\n\n\n\n
- DROP TABLE<\/a> – show you how to remove existing tables using the DROP TABLE statement.<\/li>\n\n\n\n
- Temporary tables <\/a>– discuss MySQL temporary tables and show you how to manage temporary tables effectively.<\/li>\n\n\n\n
- TRUNCATE TABLE <\/a>– show you how to delete all data from a table quickly and more efficiently using the TRUNCATE TABLE statement.<\/li>\n\n\n\n
- Generated columns<\/a> – guide you on how to use the generated columns to store data computed from an expression or other columns.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 10. MySQL constraints<\/h2>\n\n\n\n\n- Primary key<\/a> – guide you on how to use the primary key constraint to create the primary key for a table.<\/li>\n\n\n\n
- Foreign key<\/a> – introduce you to the foreign key and show you step by step how to create and drop foreign keys.<\/li>\n\n\n\n
- Disable foreign key checks<\/a> – learn how to disable foreign key checks.<\/li>\n\n\n\n
- NOT NULL<\/a>– 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.<\/li>\n\n\n\n
- UNIQUE constraint<\/a> – 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.<\/li>\n\n\n\n
- CHECK constraint<\/a> – learn how to create CHECK constraints to ensure data integrity.<\/li>\n\n\n\n
- DEFAULT<\/a> – show you how to set a default value for a column using the DEFAULT constraint.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 11. MySQL data types<\/h2>\n\n\n\n\n- BIT<\/a> – introduce you to BIT datatype and how to store bit values in MySQL.<\/li>\n\n\n\n
- INT<\/a> – show you how to use integer data type.<\/li>\n\n\n\n
- BOOLEAN<\/a> – explain to you how MySQL handles Boolean values by using TINYINT(1) internally.<\/li>\n\n\n\n
- DECIMAL<\/a> – show you how to use DECIMAL datatype to store exact values in decimal format.<\/li>\n\n\n\n
- CHAR<\/a> – a guide to CHAR data type for storing the fixed-length string.<\/li>\n\n\n\n
- VARCHAR<\/a> – give you the essential guide to VARCHAR datatype.<\/li>\n\n\n\n
- TEXT<\/a> – show you how to store text data using TEXT datatype.<\/li>\n\n\n\n
- DATETIME<\/a> – introduce you to the DATETIME datatype and some useful functions to manipulate DATETIME values.<\/li>\n\n\n\n
- TIMESTAMP <\/a>– 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.<\/li>\n\n\n\n
- DATE<\/a> – introduce you to the DATE datatype and show you some date functions to handle the date data effectively.<\/li>\n\n\n\n
- TIME <\/a>– walk you through the features of TIME datatype and show you how to use some useful temporal functions to handle time data.<\/li>\n\n\n\n
- BINARY<\/a> – show you how to store fixed-length byte data.<\/li>\n\n\n\n
- VARBINARY<\/a> – learn how to store variable-length byte data.<\/li>\n\n\n\n
- BLOB<\/a> – guide you on how to use the BLOB data type to store large binary data such as images, videos, and so on.<\/li>\n\n\n\n
- ENUM<\/a> – learn how to use ENUM datatype correctly to store enumeration values.<\/li>\n\n\n\n
- JSON<\/a> – show you how to use JSON data type to store JSON documents.<\/li>\n\n\n\n
- MySQL UUID Smackdown: UUID vs. INT for Primary Key<\/a> – 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.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 12. Modifying data in MySQL<\/h2>\n\n\n\n
In this section, you will learn how to insert, update, and delete data from tables using various MySQL statements.<\/p>\n\n\n\n
\n- INSERT <\/a>– use various forms of the INSERT statement to insert data into a table.<\/li>\n\n\n\n
- INSERT Multiple Rows<\/a> – insert multiple rows into a table.<\/li>\n\n\n\n
- INSERT INTO SELECT<\/a> – insert data into a table from the result set of a query.<\/li>\n\n\n\n
- INSERT IGNORE<\/a> – explain the INSERT IGNORE statement that inserts rows into a table and ignores rows that cause errors.<\/li>\n\n\n\n
- Insert datetime values<\/a> – show you how to insert datetime values into a DATETIME column.<\/li>\n\n\n\n
- Insert date values<\/a> – learn how to insert date values into a DATE column.<\/li>\n\n\n\n
- UPDATE <\/a>– learn how to use the UPDATE statement to update data in database tables.<\/li>\n\n\n\n
- UPDATE JOIN <\/a>– show you how to perform cross-table updates using the UPDATE JOIN statement with INNER JOIN and LEFT JOIN.<\/li>\n\n\n\n
- DELETE <\/a>– show you how to use the DELETE statement to delete rows from one or more tables.<\/li>\n\n\n\n
- ON DELETE CASCADE <\/a> – 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.<\/li>\n\n\n\n
- DELETE JOIN <\/a>– show you how to delete data from multiple tables.<\/li>\n\n\n\n
- REPLACE <\/a>– learn how to insert or update data depending on whether data exists in the table or not.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 13. Common Table Expressions<\/h2>\n\n\n\n\n- Common Table Expression<\/a> – explain to you the common table expression concept and show you how to use CTE for querying data from tables.<\/li>\n\n\n\n
- Recursive CTE<\/a> – use the recursive CTE to traverse the hierarchical data.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 14. MySQL Locking<\/h2>\n\n\n\n\n- Table locking<\/a> – learn how to use MySQL locking for cooperating table access between sessions.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 15. MySQL globalization<\/h2>\n\n\n\n\n- Character Set <\/a>– discuss character set and show you step by step how to perform various operations on character sets.<\/li>\n\n\n\n
- Collation <\/a>– discuss collation and show you how to set character sets and collations for the MySQL server, database, tables, and columns.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 16. User-defined variables<\/h2>\n\n\n\n\n- Import CSV File Into MySQL Table<\/a> – show you how to use LOAD DATA INFILE statement to import CSV files into a MySQL table.<\/li>\n\n\n\n
- Export MySQL Table to CSV<\/a> – learn various techniques of how to export MySQL table to a CSV file format.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 17. MySQL import & export CSV<\/h2>\n\n\n\n\n- User-defined Variables<\/a> – learn how to use MySQL user-defined variables in SQL statements.<\/li>\n\n\n\n
- SELECT INTO Variable<\/a> – show you how to use the MySQL SELECT INTO variable to store query results in one or more variables.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n\n
Section 18. Advanced techniques<\/h2>\n\n\n\n