YOUR WEBSITE

The MySQL-specific statement REPLACE works exactly like INSERT, except that if an old row in the  table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted  before the new row is inserted. REPLACE is a MySQL extension to the SQL standard. It either inserts, or  deletes and inserts. Uses the following general syntax; REPLACE INTO table_name (column_list) VALUES(value_list); In this example, we are replacing a current row of data (containing three columns) in the people database; REPLACE INTO people (id,name,age) VALUES(12,'Bruce',25); Note that unless the table has a PRIMARY KEY or a UNIQUE index, using a REPLACE statement makes  no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a  new row duplicates another. Values for all columns are taken from the values specified in the REPLACE statement. Any missing  columns are set to their default values, just as happens for INSE

UPDATE also allows a LIMIT clause, which places a limit on the number of records updated. For example, if there are two identical people records with the name value of 'Victor' and there is need to change just one of them to 'Vic', use this statement: UPDATE people SET name='Vic' WHERE name='Victor' LIMIT 1; Note: ORDER BY and LIMIT may be used together in the same UPDATE statement. Row contents will be as follows; +-------------------+ | id | name | age | +-------------------+ | 1 | Vic | 21 | +-------------------+

The statement succeeds if it updates id values first by setting 2 to 1 and then 3 to 2. However, it fails if it  first tries to set 3 to 2. That would result in two records having an id value of 2, so a duplicate-key  violation occurs. To solve this problem, add an ORDER BY clause to cause the row updates to occur in a  particular order: UPDATE people SET id=id-1 ORDER BY id; Row contents will be as follows; +-------------------+ | id | name | age | +-------------------+ | 1 | Victor | 21 | | 2 | Susan | 15 | +-------------------+

To renumber the id values to being at 1, the following UPDATE statement: UPDATE people SET id=id-1; Row contents will be as follows; +-------------------+ | id | name | age | +-------------------+ | 2 | Susan | 15 | | 1 | Victor | 21 | +-------------------+

The UPDATE statement modifies the contents of the existing records. To use it, name the table needing to  be updated, provide a SET clause that lists one or more column value assignments, and optionally specify a  WHERE clause that identifies which records to update. The Update statement uses the following general  syntax; UPDATE table_name SET column=expression(s) WHERE condition [options]; For example, to update the Country table by doubling the Population column for every country, the  following statement would be used; mysql> UPDATE Country            -> SET Population = Population * 2; Query OK, 232 rows affected, 1 warning (#.## sec) Rows matched: 239 Changed: 232 Warnings: 1 Note: The above statement returns a warning and completes the update for only most of the rows, but not  all. This is because some of the updated numbers exceed the number of characters allowed for that column. The effects of column assignments made by an UPDATE are subje

In general, if a column has no default value, the effect of omitting it from the INSERT depends on whether it can take NULL values and SQL mode being used: 􀂃 If the column can take NULL values, it is set to NULL. 􀂃 If the column cannot take NULL values, it is set to the implicit default for the column data type if strict SQL mode is not enabled. If the strict mode is enabled, an error occurs. 􀂃 To get the next available number in an AUTO_INCREMENT column, leave the column out of the column list, or give it the value of NULL in the value list. The same applies to TIMESTAMP type columns.

The MySQL-specific option LAST_INSERT_ID(expr) can be used after an INSERT to   after the insert takes place; mysql> INSERT INTO City (name, countrycode) -> VALUES ('Sarah City', 'USA'); Query OK, 1 row affected (#.## sec) mysql> SELECT LAST_INSERT_ID(); +---------------------------+ | LAST_INSERT_ID() | +----------------------------+ | 4080                            | +-----------------------------+ If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row  instead, LAST_INSERT_ID() is not meaningful. However, LAST_INSERT_ID(expr) can be used  to worked around this.

The SELECT command can also be used with insert in order to populate an existing table with matching  column data from another table. In this case, we have created a table like City and called it City2, then  added all the column/row data from the original City table in to this new table; INSERT INTO City2 SELECT * FROM City;

Now that tables have been created, the next step is to populate them with data. Although there are a variety  of ways to get data into MySQL tables, the INSERT statement is the most common method for getting data  into a table. The INSERT statement uses the following general syntax; INSERT INTO table_name (column_list) VALUES(value_list); An insert can include a only the value(s) without a column list (if the exact column order, quantity and types  are known), as follows; INSERT INTO numbers VALUES(250); Row contents will be as follows; +------+ | n         | +------+ | 250    | +------+ To add multiple column data into a table (containing an inventory of used books) on a First Edition copy of  the book A Tale of Two Cities by Charles Dickens, the following would be used; I NSERT INTO used_books (author, title, edition) VALUES  ('Charles Dickens', 'A Tale of Two Cities', 'First Edition'); Row contents will be as follows; +----

Temporal values include dates, times, and date/time values that have both a date and time. More  specialized temporal types are timestamp (commonly used for recording “current date and time”) and year  (for temporal values that require a resolution only to year units). Direct use of temporal values in expressions occurs primarily in comparison operations, or in arithmetic  operations that add an interval to or subtract an interval from a temporal value. Otherwise, most temporal  value operations are performed by using functions. Temporal data may be generated via any of the following means: 􀂃 Copying data from an existing date, datetime, or time column 􀂃 Executing a built-in function that returns a date, datetime, or time column 􀂃 Building a string representation of the temporal data to be evaluated by the server Date Components: Type                       Default Format DATE                        YYYY-MM-DD TIME                          HH:MM:SS DATETIME      

Operators such as = and != are useful for finding values that are equal to or not equal to a specific exact  comparison value. When it's necessary to find values based on similarity instead, a pattern match is useful.  To perform a pattern match, use value LIKE 'pattern', where value is the value to test and  'pattern' is a pattern string that describes the general form of values to match. Patterns used with the LIKE pattern-matching operator can contain two special characters (called  “metacharacters” or “wildcards”) that stand for something other than themselves: • Percent (%) Character - The percent character matches any sequence of zero or more characters. For example, the pattern 'a%' matches any string that begins with 'a', '%b' matches any string  that ends with 'b', and '%c%' matches any string that contains a 'c'. The pattern '%' matches  any string, including empty strings. • Underscore

String comparisons are somewhat more complex than numeric or temporal comparisons. A letter in  uppercase may compare as the same or different than the same letter in lowercase, and a letter with one  type of accent may be considered the same or different than that letter with another type of accent. • Character Sets and Collations - String expressions contain characters from a particular character  set, which is associated with one of the collations (sorting orders) available for the character set. Characters may consist of single or multiple bytes. A collation can be case insensitive (lettercase  is not significant) or case sensitive (lettercase is significant). The rules that govern string comparison apply in several ways. They determine the result of comparisons  performed explicitly with operators such as = and <, and comparisons performed implicitly by ORDER BY, GROUP BY, and DISTINCT operations. The default character set and collation for literal strings depend on the val

           Literal strings in expressions are written as quoted values. By default, either single quotes or double quotes  can be used, although single quotes are more standard. Also, if the ANSI_QUOTES SQL mode is enabled, double quotes are interpreted as identifier-quoting characters, so literal strings can be quoted only with  single quotes. The data types for representing strings in tables include CHAR, VARCHAR and the TEXT and BLOB  types. The type chosen to be used depends on factors such as the maximum length of values and whether  fixed-length or variable-length values are required. Direct use of strings in expressions occurs primarily in comparison operations. Otherwise, most string  operations are performed by using functions. The usual comparison operators apply to string values (=, <>, <, BETWEEN, and so forth): Operator Definition <                                   Less than <=                                 Less than or equal to =            

Numbers can be exact-value literals or approximate-value literals.  Exact-value literals are used just as  given in SQL statements when possible and thus are not subject to the inexactness produced by rounding  error.  On the other hand, approximate-value literals are subject to rounding error and may not necessarily  be used exactly as given.   Exact-value literals are written with no exponent. Approximate-value literals are written in scientific  notation with an exponent.  For example, the numeric values -43, 368.93, and .00214 are exact values,  whereas -4.3E1, 3.6893E2, and 2.14E-3 are approximate values.  Even though the two sets of numbers look  like they have the same values, internally they are represented in different ways:   •  Exact-value numbers -                        These numbers are integer values with no fractional part after the decimal  point or decimal values with a fractional part.  They're represented internally like an integer or  DECIMAL dat

Expressions are a common element of SQL statements,  and they occur in many contexts.  For example,  expressions often occur in the  WHERE clause of  SELECT,  DELETE, or  UPDATE statements to identify  which records to retrieve, delete, or update.  But  expressions may be used in many other places; for  example, in the output column list of a SELECT statement, or in ORDER BY or GROUP BY clauses.   Terms of expressions consist of constants (literal numbers, strings, dates, and times),  NULL values,  references to table columns, and function calls.  Terms may be combined using operators into more  complex expressions.  Many types of operators are available, such as those for arithmetic, comparison, logical, and pattern-matching operations.  Here are some examples of expressions:   •  The following statement refers to table columns to select country names and populations from the  Country table:   SELECT Name, Population FROM Country; •  Literal data values that aren't st

A SELECT statement can produce a list of rows that match a given set of conditions.  This list provides the  details about the selected rows. However, to know about the overall characteristics of the rows, a summary  can be more valuable. Aggregate functions (also known as Summary functions) perform summary  operations on a set of values, such as counting, averaging, or finding minimum or maximum values.  They  calculate a single value based on a group of values. However, the resulting value is based only on non  NULL values from the selected rows (with the exception that COUNT(*) counts all rows).   Functions such as AVG() that calculate summary values for groups are known as “aggregate” functions  because they are based on aggregates or groups of values.  There are several aggregate functions.  Some of  the most common are as follows;  Aggregate Functions               Definition         MIN()                                  Find the smallest value         MAX()          

The primary purpose of the MySQL Query Browser is to provide a graphical user interface for performing  table queries, such as those covered in this section. All the above SELECT queries can also be executed from  the Query Browser GUI window. 

When a query returns many rows, but only a few of them needs to be seen, add a LIMIT clause.  This is a  MySQL option that allows the output of a query to be  limited to the first rows of the result that would  otherwise be returned.  The LIMIT clause may be given with either one or two arguments: ƒ LIMIT row_count ƒ LIMIT skip_count, row_count limit,h Each argument must be represented as an integer constant.  Expressions, user variables, and so forth can  not be used. First Rows from the Beginning  When followed by a single integer, row_count, LIMIT returns the first row_count rows from the  beginning of the result set. To select just the first 8 rows of a result set, use LIMIT 8; mysql> SELECT ID, Name FROM City LIMIT 8; +----+-------------------+ | ID | Name               | +----+-------------------+ |  1 | Kabul                | |  2 | Qandahar         | |  3 | Herat                 | |  4 | Mazar-e-Sharif | |  5 | Amsterdam       |