In this hour, you learned the basics of SQL, from table creation to manipulating records. The table creation command requires three important pieces of information?the table name, the field name, and the field definitions. Field definitions are important because a well-designed table will help speed along your database. MySQL has three different categories of data types: numeric, date and time, and string.
The INSERT command, used to add records to a table, names the table and columns you want to populate, and then defines the values. When placing values in the INSERT statement, strings must be enclosed with single or double quotes. The SELECT SQL command is used to retrieve records from specific tables. The * character enables you to easily select all fields for all records in a table, but you can also specify particular column names. If the result set is too long, the LIMIT clause provides a simple method for extracting slices of results if you indicate a starting position and the number of records to return. To order the results, use the ORDER BY clause to select the columns to sort. Sorts can be performed on integers, dates, and strings, in either ascending or descending order. The default order is ascending. Without specifying an order, results are displayed in the order they appear in the table.
You can pick and choose which records you want to return using WHERE clauses to test for the validity of conditions. Comparison or logical operators are used in WHERE clauses, and sometimes both types are used for compound statements. Selecting records from multiple tables within one statement is as advanced as it gets, as these types of statements?called JOIN?require forethought and planning to produce correct results. Common types of JOIN are INNER JOIN, LEFT JOIN, and RIGHT JOIN, although MySQL supports many different kinds of JOIN.
The UPDATE and REPLACE commands are used to modify existing data in your MySQL tables. UPDATE is good for changing values in specific columns or to change values in multiple records based on specific conditions. REPLACE is a variation of INSERT that deletes, and then reinserts a record with a matching primary key. Be very careful when using UPDATE to change values in a column because failure to add a condition will result in the given column being updated throughout all records in the table.
The DELETE command is a simple one?it simply removes whole records from tables. This also makes it very dangerous, so be sure you give DELETE privileges only to users who can handle the responsibility. You can specify conditions when using DELETE so that records are removed only if a particular expression in a WHERE clause is true. Also, you can delete smaller portions of the records in your table using a LIMIT clause. If you have an exceptionally large table, deleting portions is less resource-intensive than deleting each record in a huge table.