Summary

Chapter 7 - Advanced SQL Usage
byGareth Downes-Powellet al.
Wrox Press 2003

In this chapter we have had a in-depth review of most of the SQL syntax that will help you in producing recordsets for your web application.

We reviewed the SELECT statement, which is made of multiple clauses that could change radically the information you want to retrieve. A SELECT statement is primarily made up of a select_expression that will contain the definition of the column of your recordset. The column definition is basically constructed around a column name and eventually completed with expressions made of functions in order to get more comprehensive data. This column name expression can be aliased with the keyword AS to have a nice column definition for your recordset.

In a SELECT statement, the data comes from tables, and these tables are specified within the FROM clause. You can use multiple tables in a query, either simply by listing them, or by using the keywords LEFT JOIN or INNER JOIN. These tables can be joined with relations that are constructed with foreign keys. You will specify the relation with which you want to link tables in a JOIN ... ON ... clause.

The data retrieved from a SELECT statement can be filtered with the keyword WHERE. The WHERE clause is made up of a logical expression; this expression may be built with logical operators and other expressions.

Some rows of a recordset can be grouped into a single row with the keyword GROUP BY. The GROUP BY function can be implicitly called by using the group functions without telling on which column you want to group. In this case MySQL will automatically group all rows into a single one. You can use the GROUP BY functions to know about the elements that have been grouped together: count(), avg(), max(), min(), sum().

There is a second keyword to filter rows from a recordset: HAVING. The HAVING keyword is different from the WHERE keyword, in that WHERE works at the table column name level while HAVING works at the recordset column name level.

You can sort the results of a SELECT statement by using the ORDER BY keyword. The keyword ASC or DESC can set the way the sorting is done.

The final clause that you can add in a SELECT statement is the LIMIT clause. This clause will select a portion of the global recordset by specifying an offset and a number of rows.

To change the structure of an existing table, you can use the ALTER TABLE sql command. With this command you can add a column in your table, change an existing column definition, remove a column from your table and rename your table.

A key concept in SQL is the primary key. A primary key is a set of fields in a table that identifies in a unique manner each and every row of a table.

Finally we looked at the concept of indexes. An index is a data structure that will make the SELECT a lot faster. The index data are hidden to the user. The creation, modification, and deletion of indexes is performed with the ALTER TABLE statement.

If you want to learn more about MySQL syntax, can find the complete MySQL documentation at http://www.mysql.com/doc/.



Dreamweaver MX PHP Web Development
Dreamweaver Mx: Advanced Php Web Development
ISBN: 1904151191
EAN: 2147483647
Year: 2001
Pages: 88

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net