6.8. Extended Functionality
Both MySQL and mSQL have a few quirky extensions that do not really have counterparts in the other database engine. Most of MySQL's extensions are generally in line with the ANSI SQL standard. mSQL's extensions are simply related to special variables you can access while working with an mSQL database.
6.8.1. MySQL Features
MySQL goes well beyond mSQL's support for SQL by supporting functions and a limited concept of outer joins. Functions in SQL are similar to functions in other programming languages like C and Perl. The function takes zero or more arguments and returns some value. For example, the function SQRT(16) returns 4. Within a MySQL SELECT statement, functions may be used in either of two places:
# Select the name of each event as well as the date of the event # formatted in a human-readable form for all events more # recent than the given time. The FROM_UnixTIME() function # transforms a standard Unix time value into a human # readable form. SELECT name, FROM_UnixTIME(date) FROM events WHERE time > 90534323 # Select the title of a paper, the full text of the paper, # and the length (in bytes) of the full text for all # papers authored by Stacie Sheldon. # The LENGTH() function returns the character length of # a given string. SELECT title, text, LENGTH(text) FROM papers WHERE author = 'Stacie Sheldon'
# Randomly select the name of an entry from a pool of 35 # entries. The RAND() function generates a random number # between 0 and 1 (multiplied by 34 to make it between 0 # and 34 and incremented by 1 to make it between 1 and # 35). The ROUND() function returns the given number # rounded to the nearest integer, resulting in a whole # number between 1 and 35, which should match one of # the ID numbers in the table. SELECT name FROM entries WHERE id = ROUND( (RAND()*34) + 1 ) # You may use functions in both the value list and the # WHERE clause. This example selects the name and date # of each event less than a day old. The UNIX_TIMESTAMP() # function, with no arguments, returns the current time # in Unix format. SELECT name, FROM_UnixTIME(date) FROM events WHERE time > (Unix_TIMESTAMP() - (60 * 60 * 24) ) # You may also use the value of a table field within # a function. This example returns the name of anyone # who used their name as their password. The ENCRYPT() # function returns a Unix password-style encryption # of the given string using the supplied 2-character salt. # The LEFT() function returns the left-most n characters # of the given string. SELECT name FROM people WHERE password = ENCRYPT(name, LEFT(name, 2))
Finally, MySQL supports a more powerful joining than the simple inner joins we have used so far. Specifically, MySQL supports something called a left outer join (also known as simply outer join). This type of join is similar to an inner join, except that it includes data in the first column named that does not match any in the second column. If you remember our author and book tables from earlier in the chapter, you will remember that our join would not list any authors who did not have a book in our database. It is common that you may want to show entries from one table that have no corresponding data in the table to which you are joining. That is where an outer join comes into play:
SELECT book.title, author.name FROM author LEFT JOIN book ON book.author = author.id
+----------------+----------------+ | book.title | author.name | +----------------+----------------+ | The Green Mile | Stephen King | | Guards, Guards!| Terry Pratchett| | Imzadi | Peter David | | Gold | Isaac Asimov | | Howling Mad | Peter David | | NULL | Neil Gaiman | +----------------+----------------+
MySQL takes this concept one step further through the use of a natural outer join. A natural outer join will combine the rows from two tables where the two tables have identical column names with identical types and the values in those columns are identical:
SELECT my_prod.name FROM my_prod NATURAL LEFT JOIN their_prod
6.8.2. mSQL Features
Copyright © 2001 O'Reilly & Associates. All rights reserved.