An Advanced MySQL Tutorial

MySQL is a relational database system that organizes data into tables. Once you create your MySQL database, there are advanced functions and techniques you can utilize to make your queries run efficiently.

Data Types

MySQL has several text types (example: "Varchar"), several number types (example: "Int") and several date types (example: "Time), and choosing the smallest possible type can save memory.

Indexes

Indexes on columns are set by adding "Primary Key," "Key," "Unique" or "Index to Create or Alter" statements, and improve the data recovery speeds of the database at the expense of memory.

Searching Multiple Tables

The keyword "Join" can be used with "Select," "Delete" and "Update" statements to return results from multiple tables by joining data from matching columns.

Combining Results

Combing the results from multiple "Select" statements into one table can be accomplished with "Union" when returned data has matching columns where table1's first column name equals table2's first column name and so on.

Aggregate Functions

In MySQL, aggregate functions perform calculations on and combine data from columns into a single record and can be used with "Group By" to affect unique values from a column or columns.

Control Flow Functions

Functions such as "Case," "If," "Ifnull" and "Nullif" allow the user to place conditions on SQL statements, and return values based on whether the particular condition is true or false.

References

• Sun's MySQL 5.4 Reference Manual
• MySQL Tutorial

Resources

• Sun's MySQL Documentation