YOUR WEBSITE

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           |  ...

            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 r...

The  SELECT statement is primarily used to retrieve zero or more  rows from one or more tables in a  database.  In MySQL,  SELECT is the most commonly used DML (Data Manipulation Language)  command.  In specifying a SELECT query, the user specifies a description of the desired result set.  It is  built with optional clauses that specify how and what data to retrieve.  The general command syntax is  shown below;  SELECT [<clause options>] <column list> [FROM] <table>  [<clause options>];  Basic SELECT statement example using world database (and the result data):  mysql> SELECT Name FROM Country;  +-------------------------------------------------+  | Name                                                    |  +-----------------------...

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  ...

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 ...

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    ...

•  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 indexe...