YOUR WEBSITE

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

          By default, the rows in the result set produced by a SELECT statement are returned by the server to the  client in no particular order.  When a query is issued, the server is free to return the rows in any convenient  order.  This order can be affected by factors such as the order in which the rows are actually stored in the  table, or which indexes are used to process the query.  If the output rows need to be returned in a specific  order, include an ORDER BY clause that indicates how to sort the results.  There is no guarantee about the order in which the server returns rows, unless the order is specified.  To do  so, add an ORDER BY clause to the statement that defines the sort order desired.  Single Column Sorts  The following example returns country names (in the Country table of the world database) alphabetically  by Country Name;  mysql> SELECT Name FROM Country ORDER BY Name;  +------------------------------+  | Name                            |  +--------

The WHERE clause is the mechanism for filtering out unwanted rows from the result set.  Rows can be  selected by looking for column values that satisfy various criteria, and different types of values can be  filtered out.  In the statement below, the query is selecting information from three columns,  from a given  table with a clause  where a specific city name is to be used. In other words, it is answering the  question…"What is the ID and district for the city of New York?", as follows;  mysql> SELECT ID, Name, District FROM City           -> WHERE Name = 'New York';  +-------+--------------+-------------+  | ID      | Name        | District     |  +-------+--------------+--------------+  | 3793 | New York | New York  |  +-------+--------------+--------------+   1 row in set (#.## sec)  Expressions in WHERE clauses can use the following types of operators;  ƒ                              Arithmetic -- for calculation  ƒ

I f a query returns a result that contains duplicate rows, the duplicates can be removed to produce a result set  in which every row is unique.  To do this, include the clause DISTINCT after SELECT and before the  column list.  Caution needs to be present when using DISTINCT due to the fact that DISTINCT will  compare whole rows when processing.  In the case below, the query results in a set that includes duplicate  rows:  mysql> SELECT Continent FROM Country;  +--------------------+  | Continent          |  +--------------------+  | Asia                  |  | Europe             |  | North America |  | Europe             |  | Africa                |  | Oceania           |         ...       | Antarctica        |  | Oceania           |  +--------------------+  239 rows in set (#.## sec)  By adding DISTINCT to the statement, the duplicates are removed and only unique rows are returned:  mysql> SELECT DISTINCT Continent FROM Country; 

            There are many clauses, and combinations thereof, that can be used with a SELECT statement to yield  particular table query results.  They range from very basic, commonly-used options to very specialized and  complex.  The following basic, optional clauses will be covered in this section:  Clauses                   Definition DISTINCT                Eliminates duplicate row data  FROM                      Specifies what table(s) to retrieve data from  WHERE                   Decides what data to show  ORDER BY             Sorts data by specified order criteria  LIMIT                        Reduces the amount of records received  Example of SELECT statement with clauses:  SELECT DISTINCT values_to_display FROM table_name WHERE expression ORDER BY how_to_sort LIMIT row_count;               The above syntax shows the correct order and usage of each of the above optional clauses.  This statement is specifically  selecting  distinct data rows  from a named ta

The FOREIGN KEY clause has several parts:   •  It names the column in the referring table (country_code).   •  It names the code column in the country_parent table as the referenced column. This column is  the “foreign” key.   •  It specifies what actions to take if records are modified in the referenced table. The foreign key  definition shown specifies the  CASCADE action for both  UPDATE and  DELETE operations. This  means that changes in the parent table are cascaded down to the child table. If a  code value is  changed in the  country_parent table, InnoDB changes any corresponding  city_child  records with that value in the country_code column to match. If a country_parent record is  deleted, InnoDB also deletes any  city_child records with the same country code. (InnoDB  supports actions other than CASCADE, but they are not covered here.   Instead of  CASCADE a SET NULL or RESTRICT can be specified.  In a foreign key relationship, the referring column and the

A foreign key relationship allows for the declaration of an index in one table to be related to an index in  another.  It also allows for the placing of constraints on what may be done to the tables in the relationship.   The database enforces the rules of this relationship to maintain referential integrity.  Using Foreign Keys with InnoDB  The InnoDB storage engine supports the use of foreign keys (unlike other storage engines).  This capability  enables the declaration of relationships between columns in different tables, and InnoDB maintains  integrity between the tables by prohibiting operations that violate those relationships.  For example, the  following restrictions must be addressed:   •  A table must contain only records with ID values that are known in another reference table.   •  If an ID in the referenced table is changed, the ID in all matching records in the referencing table  must be changed to match.   •  If a record with a given ID in the referenced t

To view the status of indexes in a table use the following statement;  SHOW INDEX FROM <table>;  Example:  mysql> SHOW INDEX FROM countrylanguage\G  *************************** 1. row ***************************         Table: countrylanguage  Non_unique: 0     Key_name: PRIMARY  Seq_in_index: 1  Column_name: CountryCode     Collation: A  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null:  Index_type: BTREE       Comment:  *************************** 2. row ***************************         Table: countrylanguage  Non_unique: 0     Key_name: PRIMARY  Seq_in_index: 2  Column_name: Language     Collation: A  Cardinality: 984     Sub_part: NULL       Packed: NULL         Null:  Index_type: BTREE       Comment:  2 rows in set (0.16 sec)  The result of the  SHOW INDEX command done on the  CountryLanguage table lists two indexes;  CountryCode and Language.  This confirms the setting of a composite index in the CREATE TABLE  c

•  Declare a NOT NULL indexed column if possible.   NULL requires additional server decisions   and can be processed more simply and faster.  •  Avoid over indexing; don't index a column just because it can be indexed. If a column is never  going to be referred to in comparisons, then there's no need to index it.  •  Every index created slows down table updates.  If a row is inserted, an entry must be added to  each of the table's indexes.  •  An index on a column that has very few distinct values is unlikely to do much good.  •  Choose unique and non-unique indexes appropriately.  The choice may be influenced by the data  type of a column and the level of distinctness.  •  Index a column prefix rather than the entire column. Shortening the length of the key values can  improve performance by reducing the amount of disk I/O needed.  •  Avoid creating multiple indexes that overlap (have the same initial columns). 

DROP TABLE removes one or more tables. All table data and the table definition are  removed, so  be careful with this statement!   DROP [TEMPORARY] TABLE [IF EXISTS] table1;  Use IF EXISTS to prevent an error from occurring for tables that do not exist. An error is generated for each non-existent table when using IF EXISTS. The TEMPORARY keyword has the following effects:               1.  The statement drops only TEMPORARY tables.               2.  The statement does not end an ongoing transaction Using TEMPORARY is a good way to ensure that non-TEMPORARY table is not deleted by accident. DROP TABLE examples:  DROP TABLE table1;  DROP TABLE IF EXISTS table1;  DROP TEMPORARY TABLE eu_countries_temp; 

To remove records from a table without removing the table itself, use the DELETE or TRUNCATE TABLE  statement. Either of the following statements completely empties the named table:   DELETE FROM t;  TRUNCATE TABLE t;  DELETE takes an optional WHERE clause that identifies which records to remove. This is useful when only  a given subset of records from a table need to be deleted. The following statement removes only those  records from t that have a status column value of 'expired', in order by the id column and output limited to  4 rows:   DELETE FROM t WHERE status = 'expired' ORDER BY id LIMIT 4;  TRUNCATE also will reset the metadata held within the format files of the table being truncated ( .frm )  such as the AUTO_INCREMENT starting value. 

Renaming a table changes neither a table's structure nor its contents. The following statement renames table t1 to t2: ALTER TABLE t1 RENAME TO t2; Another way to rename a table is by using the RENAME TABLE statement: RENAME TABLE t1 TO t2; RENAME TABLE has an advantage over ALTER TABLE in that it can perform multiple table renames in a  single operation. One use for this feature is to swap the names of two tables: RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2; For TEMPORARY tables, RENAME TABLE does not work. The ALTER TABLE command must be used  instead.

The second way to alter a column definition is to use a CHANGE clause. The CHANGE clause enables  the modification of both the column's definition and its name. To use this clause, specify the CHANGE  keyword, followed by the column's existing name, its new name, and its new definition, in that order. Note  that this means the existing name must be specified twice to change only the column definition (and not the  name). For example, change the last_name column from CHAR(30) to CHAR(40) without renaming  the column as follows: ALTER TABLE head_of_state CHANGE last_name last_name CHAR(40) NOT  NULL; To change the name as well (for example, to Surname), provide the new name following the existing  name: ALTER TABLE head_of_state CHANGE last_name Surname CHAR(40) NOT NULL; To change a column at a specific position within a table row, use FIRST or AFTER col_name. The  default is to change the last column.

One method to alter a column definition is to use a  MODIFY  clause.  The name of the column to be  changed must be specified, followed by its new definition. Assume that the ID column's data type must be  changed from  INT to  BIGINT, to allow the table to accommodate larger identification numbers.  In  addition, the column needs to be changed to  UNSIGNED to disallow negative values. The following  statement accomplishes this task:  ALTER TABLE eu_countries MODIFY new_population BIGINT(12) NOT NULL; That ALTER TABLE statement changes the table structure as follows:  +----------------------+-------------------+-------+------+----------+--------+  | Field                    | Type                | Null  | Key  | Default | Extra |  +----------------------+-------------------+-------+------+----------+--------+  | name                   | char(52)          | NO   |         | NULL    |          |  | new_population | decimal(12,0) | NO   |         | NULL    |          

After creating a table, there might be a change of requirements that forces changes to the structure of the  table.  If that happens, the table's structure can be modified without having to re-create the table.  This  section addresses some of the methods available for modifying the table structure and data.  Add Columns  For example, to add a column named Id to the eu_countries table, the following statement can be issued:  ALTER TABLE eu_countries ADD Id INT(6) NOT NULL;  That ALTER TABLE/ADD statement changes the table structure as follows:  mysql> DESCRIBE eu_countries;  +----------------------+-------------------+------+-----+-----------+--------+  | Field                    | Type                | Null | Key | Default  | Extra |  +----------------------+-------------------+------+-----+-----------+--------+  | name                   | char(52)          | NO   |       | NULL    |           |  | new_population | decimal(12,1) | NO   |       | 0.0        |  

To drop a column, use a DROP clause that names the column to be removed: ALTER TABLE eu_countries DROP id; The above ALTER TABLE/DROP statement changes the table structure as follows: +----------------+---------------+------+-----+---------+-------+ | Field                 | Type                | Null   | Key | Default    | Extra   | +----------------+---------------+------+-----+---------+-------+ | name                 | char(52)          | NO   |          | NULL    |           | | new_population | decimal(12,1)  | NO   |          | 0.0         |           | +----------------+---------------+-----+------+---------+-------+

            It is sometimes useful to create tables for temporary use.  This can be done by using the  CREATE  TEMPORARY TABLE command rather than  CREATE TABLE. A temporary table differs from a non-  temporary table in the following ways:   •  It's visible only to the client (and connection) that created it and may be used only by that client  (and connection).  This means that different clients (even with different connections) can create  temporary tables that have the same name and no conflict occurs.   •  A temporary table exists only for the duration of the connection in which it was created.  The  server drops a temporary table automatically when the client connection ends if the client has not  already dropped it.  •  A temporary table may have the same name as a non-temporary table.  The non-temporary table  becomes hidden to the client that created the temporary table as long as the temporary table exists.   •  A temporary table can be renamed