SQL Advanced in MySQL

Course review:

DQL (Data Query Language)

• Correlation in FROM, WHERE, SELECT and HAVING clauses
• Correlation and performance
• Using CASE, IF, COALESCE functions
• Using variables
• Casting and converting
• Dealing with NULL, NULL-safe operators
• Using regular expression with REGEXP operator
• Useful MySQL specific group by functions (GROUP_CONCAT, etc.)
• GROUP BY WITH ROLLUP
• EXISTS, ALL, ANY
• Multitable OUTER JOIN
• Rewriting subqueries as joins

DML (Data Modification Language)

• Multi-row inserts
• INSERT by SELECT
• Using subqueries in DML statements
• Using variables in DML queries
• Locking tables and rows
• Updating data in many tables
• IGNORE clause
• REPLACE clause
• DELETE versus TRUNCATE

DDL (Data Definition Language)

• Creating tables with select
• Temporary tables

Stored Procedures

• Short introduction to MySQL stored procedures