Home arrow MySQL arrow Page 4 - MySQL: Open-Source Power

MySQL: Open-Source Power

MySQL is the most popular, free, open-source database management system for the Unix/Linux platform. The MySQL database management system contains an enormous amount of functionality and power. In this article, Mitchell will describe some of its most popular features and most powerful functions.

Author Info:
By: Mitchell Harper
Rating: 3 stars3 stars3 stars3 stars3 stars / 8
December 09, 2001
  1. · MySQL: Open-Source Power
  2. · Popular features of MySQL
  3. · Powerful functions of MySQL explained
  4. · Some MySQL language syntax explained
  5. · Conclusion

print this article

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’);
blog comments powered by Disqus

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials