Mysql your visual blueprint for creating open source databases- P8

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:20

lượt xem

Mysql your visual blueprint for creating open source databases- P8

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Mysql your visual blueprint for creating open source databases- P8:The show tables command displays a list of tables available in the currently selected database. You can use this command when you are unsure of the exact name of a table within the database.

Chủ đề:

Nội dung Text: Mysql your visual blueprint for creating open source databases- P8

  1. USING SELECT QUERIES 6 When you use GROUP BY, you can select any number of This example uses a simple new table, columns as well as functions such as COUNT and AVG that scores. The CREATE TABLE command aggregate the data from the grouped rows. When you are for this table only needs to specify two not using GROUP BY, and you use a function like COUNT or columns. AVG, you cannot select any normal column values in the same query. The following table describes the available Example: functions in MySQL for use with GROUP BY clauses. CREATE TABLE scores ( name VARCHAR(50), FUNCTION DESCRIPTION score INT UNSIGNED); COUNT Number of rows Although this is a very basic table, you can achieve impressive results by storing AVG Average value multiple scores for each name and using SUM Total value the various MySQL grouping functions. MIN Minimum value MAX Maximum value STD Standard deviation BIT_OR Bitwise OR BIT_AND Bitwise AND ° Type SELECT name, · Type GROUP BY name; ‚ Type SELECT name, ± Type FROM scores GROUP COUNT(*) FROM scores and press Enter. AVG(score), and press Enter. BY name; and press Enter. and press Enter. s The number of scores for — Type MIN(score), s The name, average, each student is displayed. MAX(score) and press Enter. minimum score, and maximum score are displayed for each student. 127
  2. MySQL DISPLAY DATA FROM MULTIPLE TABLES relationship between the two tables. In the example above, M ySQL is known as a relational database system, and one of the most important features of a the WHERE condition indicates that rows should be matched relational database is the ability to work with when the author columns from both tables are equal. relationships between different tables. You can use SELECT To refer to columns when working with multiple tables, you to retrieve related data from multiple tables. must specify the table name for each column. Separate the For example, the quotes table stores quotations and their table and column names with a period. The column you corresponding authors. If you had a separate table, named match between the tables does not have to be included in authors, that stored birth and death dates for a list of authors, the results. The following SELECT query displays only the you could combine the two in a single SELECT query: quote field from the quotes table and the corresponding born field from the authors table: SELECT * FROM quotes, authors WHERE =; SELECT quotes.quote, authors.born FROM quotes, authors This query combines, or joins, data from the two tables. WHERE =; Each row in the result includes a combination of the columns from the quotes table and the columns of the Although the columns that match across the two tables authors table. A row is returned for each row that matches in this example are both named author, the names do not between the tables. need to be similar. However, they should contain the same type of data, such as a number or a string of text. MySQL The WHERE clause is required when working with multiple will attempt to compare the columns even if they have tables. The first WHERE condition should identify the different formats, but the results may not be consistent. DISPLAY DATA FROM MULTIPLE TABLES Note: This example uses the quotes ¤ Type CREATE TABLE › Type INSERT INTO authors Á Type ("G. B. Shaw", 1856, table, which you can import from the authors (author VARCHAR(50), (author, born, died) VALUES 1950), and press Enter. CD-ROM, and creates a new authors and press Enter. and press Enter. table. ‡ Type ("W. Shakespeare", ‹ Type born INT, died INT); ˇ Type ("Mark Twain", 1835, 1564,1616); and press Enter. ⁄ From the MySQL monitor, and press Enter. 1910), and press Enter. type USE testdb; and press s This inserts three rows into Enter to select the database. s This creates the authors the authors table. table. 128
  3. USING SELECT QUERIES 6 You can use aliases to assign shorter names to the tables in a SELECT query. This is especially useful when you are retrieving data from multiple tables, because you must refer to a table name with each column name. To use an alias, include the AS keyword and the alias name after a table name. For example, the following query displays data from the quotes and authors tables using aliases: Example: SELECT * FROM quotes AS q, authors AS a WHERE =; This statement assigns the aliases q and a to the two tables. This technique is most useful when you are working with tables with long names. Even when the tables have short names, aliases can be useful if you are retrieving several column values using SELECT, as in the next example. Example: SELECT q.quote,, a.born, a.died FROM quotes AS q, authors AS a WHERE =; When you define an alias in this manner, you can refer to columns throughout the query using either the alias name or the full column name. ° Type SELECT * FROM s The contents of the authors · Type SELECT quotes.quote, ± Type WHERE authors; and press Enter. table are displayed., and press =; and press Enter. Enter. ‚ Type authors.born, s The rows that match both authors.died and press Enter. tables are shown as if they were a single table. — Type FROM quotes, authors and press Enter. 129
  4. MySQL USING JOIN OPTIONS if an entry in the authors table is not used in any rows of I n database terminology, a query that returns data from multiple tables is called a join. MySQL includes a JOIN the quotes table, it is not included in the result. keyword that you can use as an alternate syntax for If you use the NATURAL JOIN keywords, MySQL combining data from tables. In addition, you can use automatically joins the tables on any column names that various keywords with JOIN to request different match between the tables. Because the author column has combinations of data from the tables. the same name in both tables, the following query returns The basic type of join uses the INNER JOIN keyword. This the same results as the previous one: is also the type used when you simply specify multiple table SELECT * FROM quotes NATURAL JOIN authors; names with commas, as in the previous section. In an inner join, the only rows returned will be those that contain a You can use the LEFT JOIN keywords to combine tables matching, non-NULL value in both tables. The following differently. In a left join, all of the rows of the first (left) example uses the quotes and authors tables with an inner table you specify are included in the result, whether or not join: there is a corresponding row in the second table. The following query displays every row of the quotes table, and SELECT * FROM quotes INNER JOIN authors includes information from the authors table when available. ON =; Otherwise, it includes NULL values for the fields of the The ON keyword is used to specify the condition that links author table. the two tables. In this example, if an author entry in the SELECT * FROM quotes LEFT JOIN authors quotes table does not have a corresponding listing in the ON =; authors table, the row is not included in the result. Similarly, USING JOIN OPTIONS Note: This example uses the testdb ⁄ From the MySQL monitor, ¤ Type SELECT * FROM ‹ Type ON = database and the quotes and authors type USE testdb; and press quotes INNER JOIN authors; and press tables. See the CD-ROM if you have Enter. and press Enter. Enter. not yet created one of these. s The database is now s This displays matching selected. rows from both tables. 130
  5. USING SELECT QUERIES 6 You can use the NATURAL LEFT JOIN keywords to return the same result as LEFT JOIN, except that MySQL automatically matches identically-named columns between the two tables. The following query lists all rows of the quotes table and includes the corresponding information, if any, from the authors table: Example: SELECT * FROM quotes NATURAL LEFT JOIN authors; Another variation, RIGHT JOIN, is similar to LEFT JOIN but includes all of the rows of the second (right) table rather than the first. The following query lists all of the quotations for all of the authors listed in the authors table. If an author is not listed in the quotes table, a single row is returned with NULL values for the fields of the quotes table. Example: SELECT * FROM quotes RIGHT JOIN authors ON =; The conditions you specify using the ON keyword will be used to match rows between the tables. If you want to include only certain rows, you can add a WHERE clause. The following example uses RIGHT JOIN to list all of the rows of the quotes table for each author in the authors table, but only includes the rows for a single author: Example: SELECT * FROM quotes RIGHT JOIN authors ON = WHERE"Mark Twain"; › Type SELECT * FROM s This also displays Á Type SELECT * FROM s This query displays all quotes and press Enter. matching rows from both quotes LEFT JOIN authors and rows from the quotes table, tables. press Enter. regardless of whether the ˇ Type NATURAL JOIN author was found in the authors; and press Enter. ‡ Type ON = authors table.; and press Enter. 131
  6. MySQL DISPLAY DATA WITH MYSQLGUI MySQL server. When the server returns the query result, it T he MySQLGUI utility, available from the MySQL Web site at, provides a friendly graphical is displayed on a separate screen. interface to a MySQL server. You can use MySQLGUI The following is a simple query using the quotes table that to perform most of the same tasks you use the MySQL you can test using MySQLGUI: monitor for, including displaying the results of queries. See Chapter 1 for information on installing and running SELECT quote, author FROM quotes MySQLGUI. This returns two columns of data. You can also use When you run MySQLGUI, you are prompted for a MySQLGUI to try any of the other queries presented in this password for the MySQL server. The root user is used by chapter. default. If you need to specify a different username, see Chapter 1 for information on configuring MySQLGUI. After MySQLGUI keeps track of the most recent successful the password is entered correctly, the MySQLGUI dialog box queries and displays them in the lower portion of the is displayed. You can select a database to work with using a window. You can click a query in this list to copy it to the drop-down list. query field, and then click the Execute Query button to execute the query again. To perform a SELECT query using MySQLGUI, first be sure you have selected the database. Next, enter the query into MySQLGUI also includes an option to save the results of a the text box. Unlike the MySQL Monitor, you should not query to a disk file after it has been processed. This is useful end queries with a semicolon. After your query is complete, for backing up data in MySQL tables or exporting it to click the Execute Query button to send the query to the other applications. DISPLAY DATA WITH MYSQLGUI ⁄ MySQLGUI prompts you Note: See Chapter 1 for information s The main MySQLGUI Note: This example uses the testdb for a password. Enter the on configuring this utility to use a screen is displayed. database and the quotes table. See correct password and specific username or server. the CD-ROM for instructions on click OK. ¤ Click the database drop- creating them. down menu and select the testdb database. 132
  7. USING SELECT QUERIES 6 The query results screen in MySQLGUI includes a Save to file button. To save your query results to a file on the local computer, click this button. A file selection dialog box is displayed, and you can select an existing file or enter the name for a new file. The file will be an ASCII text file with the .res extension. If you do not select another path, it will be saved in the same directory as the MySQLGUI program. By default, if you select an existing file, the query results are appended to the file. You can choose the Append or Create options from the file selection dialog box to control whether to append or replace an existing file. The files created by this utility are comma-delimited files. Each text field value is enclosed in single quotes. A comment at the top of the file indicates the query that was used to retrieve the results and the column names that are included. This is a convenient way to export data from a SELECT query to a text file. MySQL includes a variety of other methods of exporting and backing up data from tables or databases. For details on these methods, see Chapter 8. ‹ Click the query field › Click the Execute query s The results of your query ˇ Click the Exit button to to select it, and then type button. are displayed. return to the main MySQLGUI SELECT quote, author FROM screen. quotes and press Enter. s The query is now sent to the MySQL server. 133
  8. MySQL MATH FUNCTIONS as INTEGER or TINYINT columns, or decimal values, such M ySQL includes a wide variety of functions and operators for working with numeric values. All of as FLOAT or DOUBLE columns. these functions will work with integer values, such Arithmetic Operators Positive and Negative Numbers MySQL supports the standard arithmetic operators for MySQL can work with negative numbers. You can adding, subtracting, multiplication, and division. These specify a negative number with a hyphen, as in -3. You are described in the table below. can also use a hyphen to convert a value into its opposite with the - operator. For example, this query OPERATOR DESCRIPTION EXAMPLE would convert the score value to a negative number if positive, and to a positive number if negative: + Addition a + 3 - Subtraction a - 1 SELECT - score FROM table; * Multiplication a * 2 You can use the ABS (absolute value) function to convert a number to its positive form: the absolute / Division a / 2 value of 31 is 31, and the absolute value of –5 is 5. This % Modulo (remainder) a % 2 example returns the absolute value of the score column: The modulo (%) operator returns the remainder for a division operation. For example, the result of a % 2 is SELECT ABS(score) FROM table; the remainder when the value of a is divided by 2. The SIGN function is used to determine the sign of a Random Numbers value. It returns 1 for positive numbers, –1 for negative numbers, or zero for zero. This example returns the sign The RAND function returns a random floating-point of the score column: number between zero and one. You can optionally specify a seed, or starting point for the calculation, for SELECT SIGN(score) FROM table; the random number generator. Any time you obtain random numbers using the same seed, the same Comparison Functions numbers will be returned. The following example Two MySQL functions allow you to compare a list of displays a random number: numbers. The LEAST function accepts two or more SELECT RAND(); arguments and returns the smallest value from the list. The GREATEST function is similar, but returns the You can also use RAND() within an ORDER BY clause to largest value from the list. The following statements make a SELECT statement return the records in random would both return the number 17: order. The following query displays the rows of the SELECT LEAST(97, 17, 22, 43, 23); quotes table in random order: SELECT * FROM quotes ORDER BY RAND(); SELECT GREATEST(2, 3, 17, 9, 4); 134
  9. USING MYSQL FUNCTIONS 7 Round Numbers Logarithmic Functions MySQL includes a variety of functions for rounding MySQL includes several functions for working with decimal numbers to integers. The FLOOR function logarithms. The EXP function returns the value of e (the rounds a number down to the nearest integer. The logarithmic constant, approximately 2.71) raised to the following example returns a rounded version of a power you specify. The following SELECT statement column called average using FLOOR: displays the value of e raised to the 7th power: SELECT FLOOR(average) FROM table; SELECT EXP(7); The CEILING function is similar, but rounds up instead The LOG function returns the natural logarithm (base e of down: 3.1, 3.6, and 3.9 would all be rounded to 4. logarithm) of the number you specify. The following The ROUND function is more intelligent, rounding to the SELECT statement displays the natural logarithm of 20: nearest integer: 3.1 to 3.49 would be rounded down to 3, and 3.5 to 3.9 would be rounded up to 4. SELECT LOG(20); You can optionally specify the number of decimal Finally, the LOG10 function returns the base-10 places for the rounded number. If you do not specify logarithm of the number you specify. The following this value, ROUND rounds the number to the nearest SELECT statement displays the base-10 logarithm of 20: integer. The following example displays a rounded SELECT LOG10(20); version of the average column with two decimal places: SELECT ROUND(average,2) FROM table; Geometry and Trigonomety Functions MySQL includes a wide variety of functions useful for The TRUNCATE function is similar to ROUND, but simply geometry and trigonometry. The following table lists removes the decimal digits beyond the specified these functions and their uses: number of places. This is similar to FLOOR, but not limited to integers. The following example uses FUNCTION DESCRIPTION TRUNCATE to return the average column’s value with only one decimal place: PI The value of PI (approximately 3.14) SELECT TRUNCATE(average,1) FROM table; SIN Returns the sine of the argument COS Returns the cosine of the argument Exponential Functions TAN Returns the tangent of the argument The POWER function returns a number raised to the power you specify. You can abbreviate this function as ATAN Returns the arc tangent of the argument POW. The following statement displays the value of 2 ASIN Returns the arc sine of the argument raised to the 7th power: ACOS Returns the arc cosine of the argument SELECT POWER(2,7); COT Returns the cotangent of the result The SQRT function returns the square root of a number DEGREES Converts from radians to degrees you specify. The following SELECT statement displays RADIANS Converts from degrees to radians the square root of 36: SELECT SQRT(36); 135
  10. MySQL USING MATH FUNCTIONS You can use any MySQL column name or names in an M ySQL includes a wide variety of mathematical functions and operators. You can test any of these expression like this, along with numeric constants and the functions with a simple SELECT statement. For results of MySQL functions. This allows you to calculate a example, this statement displays the result of a mathematical result on the fly rather than storing unnecessary data in the expression: table. SELECT 17 * 3 + 2; Some functions can also be used within the ORDER BY clause of a SELECT statement. For example, the following You can combine any number of mathematical operators and query displays five rows of the quotes table in random functions to produce a result. The following example displays order, using the RAND function: the value of 17 divided by 3, rounded to two decimal places: SELECT * FROM quotes SELECT ROUND(17/3, 2); ORDER BY RAND() LIMIT 5; These functions can also be used within a SELECT You can also use column names, operators, and MySQL statement that works with a table. The following statement functions in the ORDER BY clause. This allows you to displays the rows of the scores table. For each row, it modify a column’s value or combine the values of two displays the name column, the score column, and the value columns, and use the result to order the table. of 10 times the score column: SELECT name, score, 10*score FROM scores; USING MATH FUNCTIONS Note: This example uses the testdb s The database is now ‹ Type SELECT ROUND(17/3, s This displays a result database and the quotes and scores selected. 2); and press Enter. rounded to two decimal tables. These are available on the places. CD-ROM. ¤ Type SELECT 17 * 3 + 2; and press Enter. ⁄ From the MySQL monitor, type USE testdb; and press s MySQL computes and Enter. displays the result. 136
  11. USING MYSQL FUNCTIONS 7 The following example of a SELECT statement using MySQL’s mathematical operators was included in this section: Example: SELECT 17 * 3 + 2; If you work out this expression using a calculator, you will find there are two different results, depending on the order you evaluate the operators. If you first multiply 17 by 3, and then add 2, the result is 53. If you first add 3 and 2, and then multiply by 17, the result is 85. MySQL, like other computer languages, solves this dilemma by using a standard set of rules for operator precedence. In MySQL’s rules of precedence, multiplication and division are always evaluated before addition and subtraction, so either 2 + 17 * 3 or 17 * 3 + 2 would return the answer 53. You can also use parentheses to enforce your own rules of precedence. The following example performs the addition before the multiplication: Example: SELECT 17 * (3 + 2); › Type SELECT name, score, ˇ Type FROM scores; and Á Type SELECT * FROM s This displays five random 10*score and press Enter. press Enter. quotes and press Enter. rows from the table. s You are prompted for the s This displays the table's ‡ Type ORDER BY RAND() next line. rows, including a computed LIMIT 5; and press Enter. value. 137
  12. MySQL COMPARE NUMERIC VALUES You can use MySQL’s comparison operators anywhere a A long with the standard arithmetic operators, MySQL includes a variety of functions that you can use to number is expected. The most common use for them is in compare numeric values. The most basic of these is a WHERE clause to return records that match one or more the = operator, which indicates whether two values are criteria. This SELECT statement uses a numeric comparison equal. MySQL also includes < (less than) and > (greater in a WHERE clause: than) operators. The following simple SELECT statement SELECT * FROM scores indicates whether one number is greater than another: WHERE score > 80; SELECT 17 > 33; MySQL also includes the GREATEST function, which If you use a comparison operator in a SELECT statement accepts two or more values and returns the greatest value like this, it is evaluated to one if true and zero if false. Thus, from the list. The LEAST function is similar but returns because 17 is less than 33, this statement will display a zero the lowest value. These provide an easy way to compare result. several constants or column values at once. The following statement displays the largest value from a list: You can also use = (greater than or equal) in comparisons. The or != operators SELECT GREATEST(27, 2, 33, 31, 55, 10); mean not equal, and return the opposite of the value returned by the = operator. COMPARE NUMERIC VALUES ⁄ From the MySQL monitor, ¤ Type SELECT 33 33; and press press Enter. 2, 33, 31, 55, 10); and press 33, 31, 55, 10); and press Enter. Enter. Enter. s The result is 1, meaning s The smallest number from s The result is zero, meaning true. s The largest number from the list is displayed. false. the list is displayed. 138
  13. USING MYSQL FUNCTIONS 7 MySQL allows you to compare text values using many of the same operators and functions that work with numeric values. Strings of text are compared in alphabetical order, with strings that would be ordered earlier treated as lesser, and strings that would be ordered later treated as greater. The following query indicates whether one string is greater than another: SELECT "abalone" > "xylophone"; You can also use the GREATEST and LEAST functions to work with text. The following example displays the string that occurs last in alphabetical order from the specified list: SELECT GREATEST("abalone", "xylophone", "breeze", "garden"); While functions such as GREATEST and LEAST work with either numeric or text values, most of MySQL’s functions for working with numeric values do not apply to text strings. If you use a function such as ABS or SQRT with a string value, MySQL will first convert the string to a number if it contains a numeric value. Otherwise, a value of zero is used. Note: These examples use the scores Á Type SELECT * FROM ° Type SELECT name, score, s The third column indicates table and the testdb database. These scores and press Enter. and press Enter. whether the score column's are included on the CD-ROM. value is over 80. ‡ Type WHERE score > 80; · Type score > 80 FROM ˇ Type USE testdb; and and press Enter. scores; and press Enter. press Enter. s Rows with a matching column are displayed. 139
  14. MySQL UNDERSTANDING STRING FUNCTIONS M ySQL includes many functions for working with strings, or text values. You can use these functions with quoted strings or with the values of text columns such as CHAR, VARCHAR, and TEXT columns. BASIC STRING FUNCTIONS The functions in this section perform simple but useful functions, such as combining the values of strings. CONCAT CONCAT_WS The CONCAT function accepts two or more string values The CONCAT_WS, or concatenate with separator, as arguments and returns a string that combines, or function is similar to CONCAT. This function accepts a concatenates, the strings into a single string. You can separator string as the first argument, and uses this as a specify numeric values as arguments, and they will be separator between each of the combined strings. converted automatically into strings. LENGTH The LENGTH function returns the length of a string value, or the number of characters it contains. MySQL includes two alternate functions, CHAR_LENGTH and CHARACTER_LENGTH, that serve the same purpose. WORK WITH SUBSTRINGS MySQL also includes several functions that allow you to work with substrings, or portions of string values. LEFT MID and SUBSTRING The LEFT function accepts a string as its first parameter The MID or SUBSTRING function accepts three and a number as the second parameter. It returns the arguments: a string value, a position to start from, and a specified number of characters from the beginning of length. It returns the specified number of characters the string. The example displays the first five characters beginning at the specified position. If you omit the last of a string. argument, the substring continues to the end of the string. The example displays five characters starting at Example: the third position. SELECT LEFT("abcdefghij",5); Example: RIGHT SELECT MID("abcdefghijk", 3, 5); The RIGHT function is similar to LEFT, but returns a specified number of characters starting at the end of a string. The example displays the last five characters of a string. Example: SELECT RIGHT("qrstuvwxyz",5); 140
  15. USING MYSQL FUNCTIONS 7 WORKING WITH SPACES SEARCH AND REPLACE MySQL includes a variety of functions that allow you to MySQL includes several functions that allow you work with space characters, such as removing extra spaces to search for a substring within a larger string, or from a string. to insert or replace portions of a string value. LTRIM LOCATE The LTRIM function removes any spaces from the The LOCATE function searches for a substring beginning of a string and returns the result. This is useful within a larger string. It returns the position of for removing unnecessary spaces from user input. the substring as an integer, or zero if the substring was not found. To use LOCATE, RTRIM specify the substring followed by the larger The RTRIM function removes any spaces from the end of string. You can optionally specify an index as a a string and returns the result. third argument. If this is specified, LOCATE begins the search at the position you specify. TRIM Example: The TRIM function is a generalized version of LTRIM and RTRIM. If you use TRIM with only a string as an argument, SELECT LOCATE("def", "abcdefghi"); it trims any spaces from the beginning and end of the string and returns the result. You can also specify the keywords INSTR LEADING, TRAILING, or BOTH to indicate which ends of The INSTR function is similar to LOCATE, but the string should be trimmed, and an optional character to with the arguments in reverse order. To search remove instead of a space. The example removes any for a substring using INSTR, specify the large periods from the beginning or end of the string. string and then the substring. Example: Example: SELECT TRIM(BOTH "." FROM "...abcdef..."); SELECT INSTR("abcdefghi", "def"); REPLACE CONVERSION FUNCTIONS The REPLACE function finds occurrences of a substring within a larger string and replaces them with the string you specify. To use MySQL includes a number of functions that convert REPLACE, specify the string, the substring to strings in one fashion or another. search for, and then the replacement string. The example replaces the string "xx" with "def". FUNCTION DESCRIPTION Example: UPPER(string) Converts all letters in a string to SELECT REPLACE("abcxxghijkl", "xx", "def"); uppercase LOWER(string) Converts all letters in a string to INSERT lowercase The INSERT function replaces a substring with REPEAT Repeats a string the a specified string. To use INSERT, you specify (string, num) specified number of times the string to work with, the position to start SPACE(num) Returns a string containing the replacing, the number of characters to replace, specified number of spaces and a replacement string. The example replaces two characters, starting at the fourth REVERSE Reverses the order of the characters position, with the new string "def". string) in a string ASCII(string) Returns the ASCII code value for the Example: first character in a string SELECT INSERT("abcxxghijkl", 4, 2, "def"); CHAR(num) Returns a string converted from the specified ASCII codes 141
  16. MySQL COMPARE STRING VALUES You can also use the LIKE operator to compare strings, as M ySQL allows you to compare string values as easily as numbers. The basic string comparison uses described in Chapter 6. This is similar to the = operator, but the = operator. This SELECT statement indicates allows the use of the wildcard characters % (zero or more whether two strings are equal: characters) and _ (one character). The NOT LIKE operator is the opposite of LIKE, and returns a value of 1 if the SELECT "apple" = "orange"; strings do not match. String comparisons can be used anywhere MySQL expects MySQL also includes an operator, REGEXP or RLIKE, a value, but are most useful within WHERE clauses. The which is similar to LIKE but supports a standard regular following SELECT query displays data from a table using a expression syntax instead of simple wildcards. For example, string comparison: the following SELECT query uses a regular expression to find names beginning with "T" and at least three letters SELECT * FROM scores WHERE name="Tom"; long: For a more sophisticated string comparison, you can use SELECT * FROM scores the STRCMP function. This function returns a value of 0 if WHERE name REGEXP "T.{2}"; the two strings you specify are equal, –1 if the first string is smaller than the second when compared alphabetically, and 1 if the second string is smaller. For example, the following SELECT statement will display all of the names that start with a letter before N from the scores table: SELECT * FROM scores WHERE STRCMP(name,"N") = -1; COMPARE STRING VALUES ⁄ From the MySQL monitor, s The result is zero, because Note: The remaining examples ‹ Type SELECT * FROM type SELECT "apple" = the strings are not equal. use the scores table in the testdb scores and press Enter. "orange"; and press Enter. database, available on the CD-ROM. › Type WHERE ¤ Type USE testdb; and press name="Tom"; and press Enter. Enter. s The matching rows are 142 displayed.
  17. USING MYSQL FUNCTIONS 7 The syntax used in MySQL’s RLIKE and REGEXP operators follows the extended POSIX regular expression standard. The following table lists some of the special characters supported in MySQL regular expressions. CHARACTER DESCRIPTION . Matches any character [abc] Matches any of the characters in brackets ^ Anchors the match to the beginning of the string $ Anchors the match to the end of the string | Matches the previous characters or the following ones * Repeats the previous character zero or more times + Repeats the previous character one or more times ? Repeats the previous character zero or one times {3} Matches the previous character exactly 3 times {2,5} Matches the previous character between 2 and 5 times ˇ Type SELECT * FROM Á Type WHERE ‡ Type SELECT * FROM scores s All rows with names scores and press Enter. STRCMP(name,"N") = -1; and press Enter. beginning with "T" are and press Enter. displayed. ° Type WHERE name LIKE s Rows with a name lower "T%"; and press Enter. than "N" in alphabetical order are displayed. 143
  18. MySQL USING STRING FUNCTIONS comparison functions, you can also convert a column’s Y ou can use MySQL’s string functions to work with data from a table in a SELECT statement. One reason value before comparing it with a known value. you may want to use functions is to convert the data The UPDATE query in MySQL is often used with one from the table columns into a more useful format. For or more functions. For example, the REPLACE function example, you could use the CONCAT function to combine replaces part of a string with another string. You can two fields into a single string: use this function with UPDATE to replace a string in all SELECT CONCAT(name, " ", score) of a table’s rows. For example, this query changes any FROM scores; occurrence of "G." to "George" within the author field of the quotes table: As another example, you can use the UPPER function to convert a column to uppercase before displaying its value. UPDATE quotes SET The following query displays the quote and author fields of author = REPLACE(author, "G.", "George"); the quotes table in uppercase form: You can use any combination of column values from the SELECT UPPER(quote), UPPER(author) table, constants, and MySQL functions and operators FROM quotes; within an UPDATE query. You can also use a WHERE clause, if desired, to make sure that the update affects only the MySQL functions are also useful in the WHERE clause of a rows you want to update. SELECT statement. While this is most often used with USING STRING FUNCTIONS Note: This example uses the scores ⁄ From the MySQL monitor, ¤ Type SELECT ‹ Type FROM scores; and and quotes tables in the testdb type USE testdb; and press CONCAT(name, " ", score) press Enter. database, available on the CD-ROM. Enter. and press Enter. s The calculated value is s The database is now s You are prompted for the displayed for each row of the selected. next line. table. 144
  19. USING MYSQL FUNCTIONS 7 When you use a MySQL function or other calculation within a SELECT query to return a value, you can optionally assign an alias to the calculated value. This is useful to give a shorthand name to the result. More importantly, if you are retrieving the data from the query using a program, using an alias allows the program to access the calculated value. To assign an alias, you use the AS keyword and assign a name, which should follow the same rules as a valid column name. For example, this query concatenates the name and score fields from the scores table and returns the result as the alias nscore, along with the normal column values. Example: SELECT name, score, CONCAT(name, " ", score) AS nscore FROM scores; You can use any number of aliases and calculated fields within a SELECT query. This allows you to avoid calculations outside of the MySQL server. › Type SELECT ˇ Type FROM quotes; and Á Type UPDATE quotes SET s The replacement is UPPER(quote), UPPER(author) press Enter. and press Enter. performed on all matching and press Enter. rows. s The uppercase values are ‡ Type author = displayed for each row. REPLACE(author, "G.", ° Type SELECT * FROM "George"); and press Enter. quotes; and press Enter. s The updated contents of the table are displayed. 145
  20. MySQL WORK WITH SUBSTRINGS The third substring function is MID. You can use this M ySQL includes a number of functions that allow you to divide a text string into smaller pieces, or function to find a number of characters anywhere in a text substrings. You can use these functions when you value. The first numeric parameter is the starting position, need to extract a portion of a text column’s value. and the second is the number of characters. The starting position can range from one to the length of the string. The The first substring function, LEFT, returns the number of following example returns ten characters from the quote characters you specify starting at the beginning of the column, starting with the fifth character: string. For example, the following query uses this function to display the first ten characters of the quote field for each SELECT author, MID(quote, 5, 10) row of the quotes table: FROM quotes; SELECT author, LEFT(quote,10) MySQL also includes a SUBSTRING function that is identical FROM quotes; to MID. You can use the two interchangeably. If you do not specify the number of characters to return, these functions The RIGHT function is similar, but returns the specified return the substring that starts at the specified index and number of characters starting at the end of the string. The ends at the end of the string. The following query displays following example displays the last ten characters of the the portion of the quote column’s value from the fifth quote column for each row of the quotes table: character to the end: SELECT author, RIGHT(quote, 10) SELECT author, SUBSTRING(quote, 5) FROM quotes; FROM quotes; WORK WITH SUBSTRINGS Note: This example uses the quotes ⁄ From the MySQL monitor, ¤ Type SELECT author, s This displays the author table in the testdb database. If you type USE testdb; and press LEFT(quote, 10) and press column and the first ten have not created them, see the CD- Enter. Enter. characters of the quote ROM for instructions. column for each row. s The database is now ‹ Type FROM quotes; and selected. press Enter. 146
Đồng bộ tài khoản