Creating an Advanced Recordset
|
We saw in Chapter 5 the general structure of the SELECT statement. SELECT is used to retrieve rows of data selected from one or more tables. In this chapter, we will show you a lot of SQL examples. You can try them on whichever tool you prefer, but for consistency, we will show you only the SQL query and its results. As well as retrieving data from tables, SELECT may also be used to retrieve rows without any reference to a table. For example:
SELECT 1+2; will give you:
3 This example shows you one thing: even if in most of the cases where you will use the SELECT statement, you will specify at least two pieces of information - what you want to select and from where you want to select it - this is not entirely true. This example will compute the expression '1+2' and return a recordset made of one line and one row containing the value '3'.
The SELECT Statement
The Syntax of SELECTThe SELECT syntax can be summarized as follows:
SELECT [DISTINCT | DISTINCTROW] select_expression, ... [FROM table_references] [WHERE where_definition] [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...] [LIMIT [offset,] rows] As you can see, the SELECT statement is structured in different parts that will tell the SQL server not only which rows to retrieve, but also how to show them and order them. In the previous example, the 'select_expression' was the expression '1+2'. As we saw on the previous example, the 'select_expression' is the only non-optional element. This is a total must and non-negotiable rule. All other parts may be omitted. But it is very unlikely that you will be in situation where you can omit the 'FROM' part, because then you can't retrieve any database information.
Using DISTINCTThe keywords DISTINCT or DISTINCTROW are shortcuts for not selecting the same row twice. It may happen that when you select some fields only once in your query, that the result contains the exact same row multiple times. Using the DISTINCT keyword will return only the rows that are different for at least one column. As an example, imagine we have the table ' exampletable' that contains the following data:
As you can see row 1 and 3 have the exact same values. Using a 'SELECT DISTINCT ...' statement would return only rows 1 and 2.
Choosing ColumnsThe SELECT statement allows you to specify which columns you want to retrieve. You should not forget that when talking about the recordset's column we don't necessarily talk about the table's column. In the previous example we had: SELECT 1+2. In this example, the select_expression was '1+2' and defined one recordset column without using a table. Suppose we have the following:
SELECT 1+2, 2*3; The result will be:
As you see, we specified two columns. Basically the purpose of the select_expression is to define the column of the recordset. It is a list of column_expressions separated by the "," character. We will now analyze how to build the column_expression.
Selecting Table ColumnsTo select a table column you just need to use the name of the column. Example: We want to select the columns ID, price, bed, and number from the table room:
SELECT ID, price, bed, number FROM room which will give you:
Note that this is pure example data. Further in this chapter, we will see how to retrieve data from multiple tables with one query. The rule given above stays true in multiple table context except in one case: if the tables you are selecting from have a column name in common, and you are selecting one of these columns in your 'select_expression' you must precede the column name by the table's name and character. For example, in the table 'room' and the table bookings we have the column 'ID' in common. Suppose now that we want to retrieve the room's ID and the bookings' ID. You must use the following query:
SELECT room.ID, bookings.ID FROM room, bookings
Using MySQL FunctionsIn the above section, we saw that the simpliest form of the 'column_expression' is just the column name. In this section, we will see that 'column_expression' can be a very complex expression using many MySQL functions. For example, suppose that the hotel stores the price of the room in pounds sterling. But, for our application, we would like to show the price of the room in a different currency. We would like to know the price of the room in Euros and Dollars. Let's say the coefficient for converting pounds in Euros is 1.61 and the coefficient to convert pounds to Dollars is 1.45. We will modify the first query as follows:
SELECT ID, price*1.61,price*1.45,bed, number FROM room which will give you:
We could even go further. You want to make sure of the currency you're working on. And therefore, you want to add the currency symbol in your recordset. We will use the following query:
SELECT ID, concat(price*1.61,'*'), concat(price*1.45,'$'), bed, number FROM room which will give you:
In this example, we used the function concat() to concatenate a list of strings into one.
Recordset Column AliasingIn the last example, you can see that the column names of the recordset are taken from the select_expression. The second column expression was 'concat (price*1.61)' and so was the recordset column name. If your column_expression uses a very complex expression, the column name may grow very long. The recordset's column name can be aliased with the keyword AS. So we can modify the previous query to make the results look nicer:
SELECT ID, concat(price*1.61,'*') as priceeuro,concat(price*1.45,'$') as pricedollar,bed, number FROM room which will give you:
As you can see, using the as theprice keyword we change the resulting column name. This is extremely important especially in our context: web application development. When you will be making reference to the different columns of your recordset it will be much easier for you if all your columns have clean and concise names. The column name is also important when using the HAVING keyword, this will be covered later.
Selecting All Columns of a Table At OnceThere is a shortcut in SQL that you can use to select all the columns of a table at once: the * element. The * element will return all the columns of a table. For example:
SELECT * FROM room which will give you:
The * may seem useful at first but you cannot specify the order in which you would like the columns to appear. You also cannot specify any expression on the column, or rename the column of the recordset. The name is taken directly from the table, so be careful when using the * element.
Tables, Joining Tables, and Foreign KeysIn the previous example, we introduced the FROM keyword. When selecting the value from the table room, we used the FROM room element. In the previous examples, we have used the FROM keyword several times. It is now time to define it thoroughly. The FROM keyword tells MySQL which table you want to take values from. The simplest usage of the FROM keyword is to define the name of the table you're working on. In the example about the price of the room, we used the following FROM syntax:
SELECT ID, price, bed, number FROM room The 'FROM room' tells MySQL that you want to retrieve the column ID, price, bed, and number from the table 'room'. SQL allows you to do much more powerful queries: you can use multiple tables in one query; you can also JOIN tables into virtually any new table.
Multiple Table ReferencesIn the FROM section, tables are referenced by a list of table names separated by the "," character. When using a multiple table reference, MySQL will operate what we call a scalar product of the two tables. Suppose we have the following tables:
The scalar product of these two tables consists in having each row of the second table appended to each row of the first one. As an example the following query:
SELECT * FROM table1, table2 will give you:
As you can see the number of rows you get is the product of the number of rows of the first table and the second while the number of columns you get is the sum of the columns from each table. The values of the different columns are not multiplied or added in any way, they stay the same. Using the multiple table reference is rare and will generally not give you the result you expected, so we will focus on other forms of JOIN in this book.
Linking TablesTo explain the concept of table linking we will use the tables: room, bookings and clients. Our goal will be to have a historical list of the booking for each room. We will know the room ID and for one specific room we will get the list of each booking, the start date, the end date, the client's name, and the price paid for this reservation. In order to achieve that goal, we will use the concept of Foreign Keys. A foreign key is a set of columns that will, for each row of a table, contain the value of the primary key of another table.
When looking at the table 'room' and 'bookings' you will see a special column: the room ID. In the room table it is called ID and in the table bookings it is called roomID. The following diagram shows you the table definition: The diagram shows that the column roomID in the table 'bookings' represents the room's ID to which this booking refers. In this case, we could say the column roomID is a foreign key to the table 'room' because it is linked to the identifier column of the room. In the note below we stated that MySQL doesn't enforce the foreign keys. And indeed, you have to remember that the column roomID in the table 'bookings' is a foreign key to the table room. You have no way to tell MySQL explicitly about the foreign key. The foreign key concept is what actually makes a database a relational. When using the column 'roomID' to link to the room's ID, you are creating a relation that links these two tables and you set the meaning of the column roomID. For example, if we have a booking that starts the 22nd of March 2002 and ends the 24th of March 2002 for client 1 and this client had room 2, then we will have the following row in the bookings table:
Now suppose we want to know the list of booking for each room, we will use the following statement:
SELECT room.ID, bookings.ID, clientID, roomID, startDate, endDate FROM room LEFT JOIN bookings ON room.ID = roomID We will review the syntax of the LEFT JOIN statement later on, but here we are implementing the concept of the roomID foreign key in this specific statement, thanks to the 'LEFT JOIN bookings ON room.ID = roomID' part of the query. This query will give you:
There we have something extremely interesting, even if it doesn't appear sol We have a recordset that gives us not just the room information or the bookings information, but both in one recordset. If we wanted to have a journal of bookings for each room, then we have it. But our goal was to also have the client information. The following diagram shows another part of the relationship of our table: As you have probably already guessed, we have another foreign key: the clientID column. To get information from the table client, we will do as we did for the first query: we will use the LEFT JOIN keyword on the clients table. The new SQL query will look like this:
SELECT room.ID, bookings.ID, clientID,roomID,startDate,endDate, clients.firstname, clients.lastname FROM room LEFT JOIN bookings ON room.ID = roomID LEFT JOIN clients ON bookings.clientID=clients.ID Note that this query will return the rooms that have not been booked already. This is due to the nature of the LEFT JOIN. Further we will see how to retrieve only rooms that have been booked at least once, with the INNER JOIN syntax. In this query, we have modified two parts of the query: the select_expression now has the clients.firstname and clients.lastname. We have also added a new LEFT JOIN ... ON ... sequence. This time we are joining the table clients and we link the client.ID to the clientID that can be found in the bookings table. It works exactly the same way as when we linked the bookings table to the room table. We have linked the table bookings to the table room because the meaning of the column booking.roomID was the room's ID. Therefore we do exactly the same by linking the table bookings to the table clients, because the columun booking.clientID was the client's ID. To summarize, the LEFT JOIN tablename ON clause will link two tables by checking that the condition clause is TRUE. In our example,e we had the two clauses following LEFT JOIN:
The interesting thing in the LEFT JOIN is that it allows you to construct queries with multiple tables thanks to the relationship you defined when constructing your table.
Joining Tables and Foreign KeysIn the previous section we introduced the concept of joining tables. In the short term, joining can be thought of as a way to retrieve information from multiple tables at once. Joining tables is the most powerful concept in SQL. You've often heard about Relational databases or relational database management system (RDBMS). The relational part comes from this very concept. Here we will review the JOIN syntaxes. In the previous series of examples, we used the LEFT JOIN syntax to join the room table with the bookings and clients table. We have also reviewed multiple table references, which are also a kind of joining. Here are the other kinds, and their syntax:
table_reference, table_reference table_reference [CROSS] JOIN table_reference table_reference INNER JOIN table_reference join_condition table_reference STRAIGHT_JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference join_condition table_reference LEFT [OUTER] JOIN table_reference table_reference NATURAL [LEFT [OUTER]] JOIN table_reference table_reference RIGHT [OUTER] JOIN table_reference join_condition table_reference RIGHT [OUTER] JOIN table_reference table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference Note that the elements enclosed with [...] are optional. In this group of different JOIN syntax, the table_reference expression means the expression that you will construct to refer to the table you want to join. The expression table_reference is defined as:
table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)] And the expression join_condition is defined as:
ON conditional_expr OR USING (column_list) We already have presented the multiple table reference as well as the LEFT JOIN syntax. The LEFT JOIN syntax is the most frequently used flavor of JOIN. Note that the OUTER keyword and the RIGHT keyword are syntactic keywords.
Simple Joining of TablesAt the beginning of this section we introduced the multiple table reference. The CROSS JOIN and the JOIN keywords do exactly the same type of joining. For example, the statement:
SELECT * FROM room, bookings could be rewritten as:
SELECT * FROM room JOIN bookings
INNER JOINThe INNER JOIN is used when you want the conditional clause to be always TRUE. That may sound strange. In the previous example, we used the LEFT JOIN because we wanted to have a listing of all rooms. We therefore used the LEFT JOIN. Suppose that we now want to see only the rooms that have been reserved at least once. Being reserved at least once means that we have an entry in the table bookings. By using the INNER JOIN we ensure not only that the room.ID and bookings.roomID are equal, but that we take only the rows of the table bookings for which the column roomID exists in the table room and for the column ID. You should never have any conditions in the ON part that are used to restrict which rows you have in the result set. If you want to restrict which rows should be in the result, you have to do this in the WHERE clause.
NATURAL JOINThe NATURAL JOIN or the NATURAL LEFT JOIN is equivalent to an INNER JOIN or LEFT JOIN where the ON clause would list all the existing columns in both tables. That mainly serves as a shortcut. For example, suppose we have two tables: A and B. Table A has the column c1 and C2, and table B has the column C1 and C2 also. A following NATURAL JOIN statement:
SELECT * FROM A NATURAL JOIN B could be rewritten in:
SELECT * FROM A INNER JOIN B ON A.C1=B.C1 AND A.C2=B.C2
USINGThere is another shortcut that is very useful: the USING element. The following example:
SELECT * FROM A LEFT JOIN B USING (C1, C2) is semantically equivalent to:
SELECT * FROM A LEFT JOIN B ON A.C1 = B.C1 AND A.C2 = B.C2 To be able to use the USING shortcut, the column name must be the same. So if you plan to use it, you should name the column you want to have in the USING clause with identical names in both tables. For example, we cannot use the USING keyword with the Hotel database the way it has been designed. If we wanted to do so, we should rename the column ID in the table room to be 'roomID'. Then we would have two columns named roomID: one in the room table, the other in the table bookings. Then, we would use a LEFT JOIN with a USING clause like this one:
SELECT * FROM room LEFT JOIN bookings USING (roomID) And that would give us the equivalent statement:
SELECT * FROM room LEFT JOIN bookings ON room.roomID=bookings.roomID
Table AliasingTables may be aliased for easier use. By using table aliasing you can also join the same table multiple times. The following example shows you how to do that:
SELECT * FROM room JOIN bookings JOIN room as room2
Filtering RowsThe WHERE part of a SELECT statement is used when you want to filter the recordset. The core of a search engine resides in the WHERE part of a query. It is important to note that the WHERE clause works with table columns only. If you did make some computation on the table columns and aliased them to be able to filter the recordset according to the new value, you must use the HAVING clause. The where_expression is built as a logical expression that will return either TRUE of FALSE.
The Values of TRUE and FALSEAnything different from 0 is always TRUE. The only possible value for FALSE is 0
Building Logical ExpressionsHere is a short example of a logical expression:
SELECT * FROM room WHERE ID > 1 AND startDate='2002-31-12' The logical expression is: ID > 1 AND startDate='2002-31-12' To build your logical expression you will use operators. There are four types of operators:
Some of these operators will return the NULL value. This value cannot be compared to TRUE or FALSE, because it has its own meaning. If you want to know if a value is NULL or not, use the is NULL operator or IS NOT NULL.
ParenthesisUse the (...) to force the order of evaluation of the subexpression contained in the parenthesis, for example:
SELECT * FROM ROOM WHERE (3 * 2) -2 will give you all the rows in the table room because (3*2) - 2 evaluates to 4, therefore TRUE, while this one:
SELECT * FROM ROOM WHERE 3 * (2-2) will return no results because 3*(2-2) evaluates to 0.
Comparison Operators:The comparison operators always return the value 0 (FALSE) or 1 (TRUE) or eventually NULL. We will talk about the NULL value in the flow control operators section. These functions work both for strings and numbers. Strings are automatically converted to numbers and numbers to strings as needed. The following table shows you the list of comparison operators and their meanings as well as an example when the returned value is TRUE or FALSE. Note that the word 'expr' is an abbreviation of 'expression'.
Logical OperatorsThere are three logical operators. They return either TRUE or FALSE or eventually NULL. For logical operators the NULL value is in most cases the same as a FALSE value.
At the beginning of the filtering section, we introduced a simple query to show you a logical expression:
SELECT * FROM room WHERE ID > 1 AND startDate='2002-31-12' There we see the AND operator, which is used to tell MySQL that we want both of the conditions: ID > 1 and startDate= '2002-31-12' to be TRUE. If we want only one of these conditions, we would write the query like this:
SELECT * FROM room WHERE ID > 1 OR startDate='2002-31-12'
Flow Control OperatorsThe flow control operators are extremely helpful when you want to build a powerful query. They act as mini branching control in your query.
We have seen that the LEFT JOIN returns the values of the first table and each row of the second table for which the ON clause is TRUE. However, it may happen that the first table has no counterpart value in the second table. That would be the case if a room has never been booked. There wouldn't be any bookings row that would have the column roomID pointing at that room. In this case a NULL value is used for each column value of the second table. Suppose we want to return the value 'never booked' when there is no booking value for that room, we would use the following query:
SELECT room.ID, IF(bookings.roomID IS NULL, ' never booked','at least once') as booked FROM room LEFT JOIN bookings ON room.ID=bookings.roomID Note that in the IF operator, expr1 is evaluated as an integer value, which means that if you are testing floating-point or string values, you should do so using a comparison operation:
IF(0.1,1,0) which will return 0 because in the IF context, 0.1 will be converted into an integer and then the expr1 will be FALSE. When testing floating point, use:
IF(0.1<>0,1,0) which will return 1.
Using LIKEThe expr1 LIKE expr2 operator compares expr1 and expr2 and returns TRUE if expr1 is like expr2 and FALSE otherwise. The 'like' word means that you can use special characters in 'expr2' to tell MySQL about the level of likeliness of your comparison. These characters are the following:
The following table will show examples of the LIKE operator and the results:
The LIKE operator is extremely useful to make a quick search of specific keywords in your database, and is therefore used often in web site design. If you are comparing case-sensitive strings with any of the standard operators (=, <>..., but not LIKE) end space will be ignored.
GroupingThe GROUP BY keyword allows you to regroup the rows of a recordset according to the recordset column names into one single row. This means that for example, if you have a recordset that has 50 rows, but one column of this recordset can take only four different values, grouping your recordset on that column will return a new recordset containing only four rows. The grouping is done on the recordset column name and not necessarily on the table column name. If you selected some complex expression from multiple table fields and if this expression is aliased with the AS keyword, you can group the rows by using the aliased column name.
How Grouping WorksWe will illustrate the concept of grouping with the following exercise: We want to know for each table how many reservations have been registered. To do that, we will have to use the JOIN of two tables: room to get a list of each row and bookings to know about the bookings that are present in the database. First of all, we will use an example of data contained in these two tables:
SELECT * FROM room has:
while the table bookings:
SELECT * FROM bookings has:
As you can see, in the table bookings the column roomID has two occurrences of the value 2. The ID of the first room is not present, which means it has never been booked. Therefore, we know the results we would like to have: room 2 has had 2 bookings while room 1 has 0 reservations. Now we will build the query to get all this information at once:
SELECT room.ID as roomID,price, bed,number,bookings.ID as bookingID FROM room LEFT JOIN bookings ON room.ID=roomID which will return the following:
We are almost done. The important point is the recordset column roomID. The recordset contains the following values for this column: 1, 2, and 2. We will insert a GROUP BY sequence to group the rows of the whole recordset that have the same value for the column roomID. And finally we are going to introduce the count() function. The SQL query becomes:
SELECT room.ID as roomID,price, bed,number,bookings.ID as bookingID, count(roomID) as bookCount FROM room LEFT JOIN bookings ON room.ID=roomID GROUP BY room.ID This will give us the expected results:
As you can see, the recordset column bookCount contains the number of bookings for each room. But the power of this query is that it not only requires one SQL statement, but that it also contains each piece of information about the room. So, you don't have to perform multiple queries in order to know about the room and the number of reservations.
Implicit GroupingMySQL optimizes the use of the GROUP BY by doing an implicit grouping if you use some grouping function in your query. If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. For example:
SELECT count(*) FROM room INNER JOIN bookings ON roomID=room.ID will give you the number of rooms that have at least one booking. In this case, the number is 2. See the section about the group by function to see other examples of implicit grouping.
GROUP BY SyntaxThe following syntax shows you the syntax for the GROUP BY structure:
GROUP BY {unsigned_integer | col_name | formula) [ASC | DESC], ... This syntax above shows you that the GROUP BY expression can be made of a list of grouping elements. It means that you can group the column successively with different column names. For example, the following query:
SELECT * FROM room GROUP BY number, price will group your recordset first by the number of the room, and then with the price column. The unsigned_integer expression is the column number of your recordset section starting from 1. The col_name expression represents one of the recordset column names. If your recordset column is made of a complex subexpression you must alias it with the AS keyword and use that alias as the column name. The formula expression means a formula computed from anything that is valid SQL. For example, you could use the following:
SELECT * FROM room INNER JOIN bookings ON roomID=room.ID GROUP BY room.ID/2 Be careful that if your formula is an unsigned integer it will be used like a column number. If you really want to group rows by an integer, then use a formula: 0+19 for example. This example will show you the difference between the types of grouping. Note that we supposed that the data in the table bookings are the same as for the previous example:
SELECT * FROM bookings GROUP BY 2 which will give you:
Because the groupment has been made on an unsigned integer, which means the second column of your recordset. As the table bookings has the value '2' for the roomID in all the rows, the result is only one row.
SELECT * FROM bookings GROUP by clientID which will give you:
Because the column clientID has two different values, the grouping doesn't do anything, and you get the whole content of your table room. Now the following query:
SELECT * FROM bookings GROUP BY 0+1 which will give you:
because the group by expression, '0+1', is a formula and always evaluates to the same value: 1, therefore, all the rows of your recordset are grouped together.
Functions To Use in GROUP BYIn the previous part we introduced the function count(). We will now review a number of functions that imply the use of a GROUP BY clause.
count(expr)The count (expr) returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement. Note that it doesn't return the sum of the values, just the number of rows. In the example about the reservation of a table, we introduced the function count().
SELECT room.ID as roomID,price, bed,number,bookings.ID as bookingID, count(roomID) as bookCount FROM room LEFT JOIN bookings ON room.ID=roomID GROUP BY room.ID As you can see on this statement, the count(roomID) will return the number of rows that have the same roomID, thanks to the grouping made on room.ID. The expression COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:
select COUNT(*) from room will return the number of rows contained in the table room.
count(DISTINCT ...)The count (DISTINCT exprl, ...) function returns a count of the number of different non-NULL values.
avg(expr)The avg(expr) returns the average value of the grouped column value. For example, if we want to know the average price of the room, we will use the following query:
SELECT avg(price) FROM room which will give you the average price of the rooms. You may notice that we computed the average value on the column price, but did not specify any groupment clause. This is an example of the implicit grouping that we saw above. The implicit grouping will group all the rows into one single row. Therefore, the average will be computed on all rows, which is exactly what we want.
min(expr) and max(expr)The min(expr) and max(expr) returns the minimum and the maximum value of the grouped value. For example, if we want to know the lowest priced room as well as the highest priced room, we will do as follows:
SELECT min(price),max(price) FROM room which will give you the following result:
sum(expr)The sum(expr) function is used to compute the sum of all grouped values. This could let us know, for example, the amount of money the hotel could make, if all the rooms are booked.
SELECT sum(price) FROM room Will give you 90 as the result.
Filtering Using HAVINGWith the WHERE clause, we have already had a chance to look at how to filter a recordset, but the WHERE clause can only work on the table column level. If you use a complex expression as a recordset column, you won't be able to use a WHERE clause on that column. SQL provides a second mechanism to address this issue: the HAVING clause. HAVING works almost the same way as the WHERE clause, except that it works on the recordset column while WHERE works at the table columns level. To illustrate the HAVING clause, try to execute the following query:
SELECT year(startDate) as startyear ,year(endDate) as endyear FROM bookings WHERE startyear=2002 Executing this query will result in a syntax error telling you that the column 'startyear' is unknown in the WHERE clause. Now try to execute this query:
SELECT year(startDate) as startyear ,year(endDate) as endyear FROM bookings HAVING startyear=2002 This will return the correct recordset. The HAVING expression is built with the exact same syntax as the WHERE clause. You can use the entire WHERE operators and functions.
Sorting the RecordsetThe ORDER BY clause is used for ordering the rows retrieved in a specific order. The syntax for the ORDER BY is as follows:
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...] The unsigned_integer expression is, like in the GROUP BY, relative to the recordset column and starts from 1. The following example shows you how to order a recordset by the second column:
SELECT * FROM room ORDER by 2 DESC which will give you:
Note that the DESC keyword has been used to reverse the order of the sort. The ASC keyword is used to order the recordset in normal order. It is optional and you can omit it. The col_name expression represents the name of one of the recordset columns. We could have written the previous example as:
SELECT * FROM room ORDER by price DESC If you want to order by the number of beds, use the following query:
SELECT * FROM room ORDER by bed The formula expression represents a formula computed from anything that is valid SQL. For example, you could use the following:
SELECT * FROM room ORDER BY rand() This example will return a recordset ordered by random values. Another example: In the GROUP BY part we have learned how to group the room to retrieve the bookings. The following query will show you how to order the result by the room number.
SELECT room.ID as roomID,price, bed,number,bookings.ID as bookingID, count(roomID) as count FROM room LEFT JOIN bookings ON room.ID=roomID GROUP BY room.ID ORDER BY room.number which will give you:
Limiting the Number of RowsSometimes the number of rows you will get from a SELECT statement will be huge and you will want to limit the amount of rows. Other times you will just be interested in the first 10 rows. You may even want to implement recordset navigation by selecting small portions of a larger recordset. To do this, we will use the LIMIT clause. The syntax of the LIMIT clause is shown below:
[LIMIT [offset,] rows] The offset is the first row that you want to limit, and the rows element is the number of rows you want to limit. To illustrate the LIMIT concept, we will use a table with the following content:
The following queries will show you the result of the LIMIT clause:
SELECT * FROM EXAMPLE LIMIT 2 which will give you:
SELECT * FROM EXAMPLE LIMIT 2,3 which will give you:
A Lottery ExampleAn interesting application of the LIMIT clause is when you want to pick a random row. Suppose that in our hotel, each month a room is picked randomly and its current occupant is given a free day. To select the room, we will use the following query:
SELECT * from room ORDER BY rand() limit 1 This query will select all rows from the table room, sort them in a random order, then will just output the first row. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
EAN: 2147483647
Pages: 88