Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

Chapter 9 . SQL According to MySQL 225 Figure 9-53: Functions to test an expression or whether a value is NULL Grouping, ordering, and limiting with SELECT The SELECT statement enables powerful grouping and ordering functions that help to sort the results of the statements and thus make them more useful. Table 9-6 lists some grouping functions. Table 9-6 Grouping functions with SELECT Function Definition COUNT(X) Returns a count of the results from expression, X. COUNT(DISTINCT X) Returns the unique non-null results from expression, X. AVG(X) Returns the average of expression, X. MIN(X) Returns the minimum value of expression, X. MAX(X) Returns the maximum value of expression, X. SUM(X) Returns the sum of expression, X.
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

224 Part II . SQL Essentials Note Use the PASSWORD function to create a password entry for the user table of the MySQL grant tables. A frequently asked question among MySQL users is how to determine the number of the last ID used on an auto_incrementcolumn. The LAST_INSERT_ID function is for this purpose. Simply call SELECTLAST_INSERT_ID() as a statement and you will receive the ID of the last insert. The ID is valid only on a per-connection basis, so you will need to call this after the inserts in the same session. Figure 9-52: Examples of various other functions in MySQL. The IF, ISNULL, and IFNULL functions all perform tests and return a value based on the results of the test. The IF function runs a test on the first argument and returns the second value if the first expression is true; the function returns the final argument if the expression is false. The IFNULL function returns the second argument if the first argument is NULL, otherwise it returns the first argument. Finally, the ISNULL function returns 1 if the argument is NULL(otherwise the function returns 0). Here are some examples, with results shown in Figure 9-53: SELECT IF(1+1=2, Yes , No ); SELECT IFNULL(NULL, it is null ); SELECT IFNULL( notnull , it is null ); SELECT ISNULL( it is not null ); SELECT ISNULL(NULL);
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

222 Part II . SQL Essentials The final string-related SELECT/WHERE function that I cover in this chapter is the LOAD_FILE function. You can use this function to tell MySQL to read in the contents of a given file as a string. Note that the contents of the file must be smaller than the MySQL variable max_allowed_packet. The syntax for the LOAD_FILE function calls for you to provide the full path and filename. The function will return NULL if you don t have permission to read the file from the operating system. Examples of successful and failed usage of the LOAD_FILEfunction look like this: SELECT LOAD_FILE( /home/suehring/testfile.txt ); SELECT LOAD_FILE( /etc/shadow ); The results of these examples appear in Figure 9-51. Note You must have the FILEprivilege before you can use the LOAD_FILE function. In addition, you must have appropriate permissions to work with the file itself. Figure 9-51: A successful and failed use of the LOAD_FILE function
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

Chapter 9 . SQL According to MySQL 221 for the REPLACE function is the new string, or the one that you would like to use as a replacement. See Figure 9-50 for the results from the following syntax example: SELECT REPLACE( I want to work with Old string , Old string , New string ); The LENGTHfunction can also be quite helpful. The LENGTH function determines the length of a given string. A couple examples follow including a nesting of the REPLACE function. The results from the following examples are shown in Figure 9-50: SELECT LENGTH( What is the length of this string? ); SELECT LENGTH(REPLACE( I want to work with Old string , Old string , a New string )); One final example to include in Figure 9-50 is the REVERSE function. As you may have guessed, the REVERSEfunction reverses a string: SELECT REVERSE( Reverse this string ); SELECT REVERSE( abba ); I m not sure how useful this capability would be in the context I m using it, but when combined or nested with other functions, the REVERSE function can provide a meaningful purpose. Figure 9-50: The REPLACE, LENGTH, and REVERSE functions. Note the use of nesting with the LENGTH function.
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

220 Part II . SQL Essentials Figure 9-48: Pattern matching with LIKE and the ESCAPE and BINARY modifiers The difference is that the FIELDfunction returns the number of the argument in the argument list that matches a given string. The POSITION function returns the position of a substring in one string. The FIELD function looks for the first instance of the string the first argument in the list. In the following example, the FIELDfunction serves to determine the location of the string Suehring in the list of names. The POSITION function looks for a substring in the instance of the given string. (See Figure 9-49.) Note the difference in syntax between the two statements. If the string or substring is not found, both functions return a value of 0. Figure 9-49: The FIELD and POSITION functions in action. Note the difference in syntax between the two statements. The REPLACE function can be useful when preparing for large-scale changes to records. The syntax for the replace function calls for three arguments. The first argument is the string you re looking to search in. The second argument is the old string or the one you want to search for and replace. The third and final argument
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

Chapter 9 . SQL According to MySQL 219 Figure 9-47: Some basic examples with the LIKE function to match patterns, some with wildcards The LIKE function has two useful modifiers: the ESCAPEand BINARY keywords. (See Figure 9-48.) The ESCAPEmodifier changes the character used to escape a literal percent sign or underscore in a pattern. The BINARY keyword changes the case-sensitivity of the pattern match. However, if a column is a binary column already (such as the user column of the MySQL GRANTS database), the BINARY keyword does not change the behavior. SELECT user,host FROM user WHERE user LIKE steve|_% ESCAPE | ; SELECT suehring LIKE SUEHRING ; SELECT suehring LIKE BINARY SUEHRING ; MySQL also includes regular expression type matching via the REGEXP or RLIKE function. Using the REGEXPfunction, of which the RLIKE function is synonym for REGEXP, you can utilize more complicated pattern matching inside the SELECT and WHERE statement. The LIKE and REGEXPfunctions can be negated with the use of the NOTkeyword, in effect, using NOT LIKEwill match those records unlike the pattern. The FIELD and POSITIONfunctions are somewhat similar and at the same time different. The similarity between the two functions is that they both look for a string position. Consider the following examples: SELECT FIELD( Suehring , Spencer , Tuescher , Guthrie , Suehring , Leu , Hein ); SELECT POSITION( Sue IN Steve Suehring );
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

218 Part II . SQL Essentials Figure 9-46: The STRCMP function compares strings and returns an integer value based upon the results. Other methods exist for comparing strings in MySQL. Most notable is the LIKE function. The syntax for the LIKE function is different from the others that I ve been covering in the sections on SELECT and WHEREfunctions. As with the other functions that utilize parentheses for their arguments, the LIKEfunction compares the strings much like an equals sign (=) would. However, the LIKE function matches a pattern instead of a just a literal string. SELECT string1 LIKE string1 ; SELECT string1 LIKE string_ ; The LIKE function returns 1 if the comparison matches or 0 otherwise. The power of LIKE really emerges with the use of wildcards such as the underscore and the percent sign, as shown in Figure 9-47. Further, the LIKEfunction is most used in the WHERE portion of a SELECTstatement for non-exact matches. SELECT user,host FROM user WHERE user LIKE sueh% ; If you want to match a literal percent sign or underscore in a pattern, be sure to escape the percent with a backslash. For example, if I wanted to find a value that contained a percent sign, I can query for that by adding a backslash before the percent sign, like this: SELECT user,host FROM user WHERE host LIKE 192.168.1.% ;
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

Chapter 9 . SQL According to MySQL 217 Figure 9-45: The CONCAT and CONCAT_WS functions as well as an example of the SPACE function The STRCMP function takes two strings as arguments and determines whether they are the same. For example, consider the following instances: SELECT STRCMP( big , bigger ); SELECT STRCMP( larger , bigger ); SELECT STRCMP( same , same ); SELECT STRCMP( this is the same string , this is the same string ); If the strings are the same, the STRCMPfunction will return 0. If the first string is smaller than the second according to the current ordering, the function returns 1. The STRCMP function returns 1 in all other cases. (See Figure 9-46.)
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

216 Part II . SQL Essentials Figure 9-44: The UCASE and LCASE functions for converting strings to upper and lowercase. Sometimes you just need some space. The good news is that MySQL includes a function to provide you with as many spaces as you d like. The function is aptly titled SPACEand expects the number of spaces as an argument. I ll use the SPACE function in an example with the CONCAT function. Both CONCAT and CONCAT_WSperform similar functions. Both functions take strings and concatenate or join them together. The CONCAT_WS function enables you to specify a separator between the strings, such as a space. One way to use the CONCAT_ WS function is to join a first name and last name separated by a space: SELECT CONCAT( Firststring , Secondstring ); SELECT CONCAT( Firststring , Secondstring , Thirdstring ); SELECT CONCAT_WS( , Firstname , Lastname ); SELECT CONCAT_WS( , Ms. , Firstname , Lastname ); As an alternative to the CONCAT_WS function, you could use the SPACEfunction in the argument list with CONCAT, like this: SELECT CONCAT( Firstname ,SPACE(1), Lastname ); The results from the syntax examples given here appear in Figure 9-45.
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

Chapter 9 . SQL According to MySQL 215 Figure 9-42: Some MySQL string functions in action Figure 9-43: Examples of the MySQL TRIM function The LCASEand UCASEfunctions are also related. LCASEconverts a string to all lowercase characters while UCASEconverts the string to all uppercase characters. The syntax for their use is the same and is quite simple. The results are shown in Figure 9-44. SELECT LCASE( A Cow Says Moo ); SELECT LCASE( I DON T LIKE PEOPLE WHO SHOUT IN E-MAIL ); SELECT UCASE( Change all 7 of these words to uppercase );
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.