MySQL: Open-Source Power - Some MySQL language syntax explained

(Page 4 of 5 )

The MySQL language structure is extremely powerful and flexible, and should be considered on par with other popular database management systems such as Oracle, DB2 and Microsoft SQL Server 2000. Explained below are some useful SQL functions and language syntax elements:

limit: The limit function is used in conjunction with a SELECT query, and can control the number of records returned from a query, as well as the starting record from which they are returned. Limit takes one or two parameters. To select the first ten records from a query, use the limit keyword with one parameter, like this:

select * from myTable limit 10;

To select records 11 to 21 from a table, use the limit function with two parameters like this:

select * from myTable limit 11, 10;

left outer join: The left outer join function is used to merge the results of two SELECT queries based on a join condition. You should use the left outer join command when you are sure that the first SELECT query will return at least one result. If the second SELECT query doesn’t return any matching results, then each field will be NULL. Use the left outer join function like this:

select cust.id, cust.name

from customers as cust

left outer join orders ord

on cust.custid = ord.custid

where cust.id = ‘mitchell@devarticles.com’

order by cust.name asc

version: The version function is used to retrieve the version information for the currently installed copy of MySQL. To use the version function, simply include “version()” as part of a select statement, like this:

select version();

cos, sec and tan: You can use these three trigonometric functions to calculate the equivalent cos, sec and tan values for any number. Use the functions like this:

select cos(10);

select sec(m.number) from mathsTable m;

select tan m.number – 10) from mathsTable m;

now: The now function is used to return the current date and time as a single time-stamp field. Use the now function in a query like this:

select now();

bin, oct, hex: Use the bin, oct, and hex functions to generate binary, octal and hexadecimal representations of numbers respectively, like this:

select bin(10), oct(7), hex(16);

The above example would return 1010, 7 and 16 respectively.

locate: The locate function returns the position of a string within a string. The location function accepts three parameters, as shown below:

locate(sub string to find, string, start pos);

To get the position of “art” in “devarticles.com”, we would use the location function like this:

select locate(‘art’, ‘devarticles.com’, 1);

The location function example shown above would return 4.

instr: Used the same way as the locate function, accept that the arguments are opposite:

instr(string, sub string to find);

soundex: The soundex function returns a soundex representation of a string. A soundex string contains several syllables and represents how the string sounds. A soundex string can be used to compare how one word sounds in relation to another, and is very handy when developing a search application. The soundex function takes a string variable and returns a soundex string which represents that string, for example:

select soundex(‘test’) as t, soundex(‘best’) as b;

Would return the value T230, B230. As you can tell, these words rhyme, and have a different first character. The T230 and B230 soundex strings represent this. You can use the LIKE function to deduce whether two words sound alike. If the like function returns 1, then the are alike:

select ‘%’ + soundex(‘test’) like ‘%’+ soundex(‘best’);

Next: Conclusion >>
 

More MySQL Articles
More By Mitchell Harper

Comments On: MySQL: Open-Source Power

>>> Be the FIRST to comment on this article!