Skip to main content

Posts

Showing posts from February, 2012

Basic Uses of SELECT

            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

SELECT Statements

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                                                    |  +-------------------------------------------------+  | Afghanistan                                          |  | Netherlands                                          |  | Netherlands Antilles                            |  | Albania        

Foreign Key Clause

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

Foreign Keys

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

SHOW INDEX

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

Indexing Tips

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

Table Indexing

When a row is inserted into a table, the database server does not attempt to put the data in any particular  location within the table.  Instead, the server simply places the data in the next available location within the  file.  When a query is sent against the table therefore, the server will need to inspect every row of the table  to answer the query.  Tables in MySQL can grow very large, and as a table gets bigger, retrievals from it  become slower.  An index in MySQL serves to assist in finding row data more quickly and easily, much like an index in the  back of a technical manual.  Database indexes are used to locate rows in a table.  Indexes are special  structures that, unlike normal tables, are kept in a specific order.  Instead of containing all of the data about  an entity, however, an index contains only the column(s) used to locate rows in the data table.  It also  contains information describing where the rows are physically located.  Usually, all the indexes requi

Altering Tables ( Drop Tables )

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; 

Altering Tables ( Emptying Tables )

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. 

Altering Tables ( Renaming Tables )

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.

Altering Tables ( Changing Columns )

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.

Altering Tables ( Modifying Columns )

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    |          

Altering Tables ( Add Columns )

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        |  

Altering Tables ( Remove Columns )

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

Temporary Tables

            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

Column Options

A table must have at least one column. Each column has to have a name and a data type. And there are several options for column data that can be used as part of the table creation command. These options modify how MySQL handles the associated column. Such  as the following; NULL   Allows values stored as  NULL   The default option NOT NULL Disallows NULL  values  Saves response time and in some cases disk space  A necessity for a  PRIMARY KEY DEFAULT    If no value is specified by the user at insertion of new data, this data will be stored in the field AUTO_INCREMENT  Only works for Integer data type columns Automatically generates a unique, positive number sequence for each row in a table (for UNIQUE or PRIMARY KEY  columns)  Using NULL will insert the next available number instead   Not specifying a value for the column will also work  Cannot exceed the upper limit of the  specific data type (i.e. 255 for a  TINYINT)   If rows are deleted containing valu

Table Properties

MySQL manages tables using storage engines, each of which handles tables that have a given set of characteristics. Different storage engines have differing performance characteristics, and can be chosen based on which engine most closely matches the char acteristics that are needed. For example, a table may require transactional capabilities and guaranteed data in tegrity even if a crash occurs, or it may require a very fast lookup table stored in memory for which the contents can be lost in a crash and reloaded at the next server startup.  With MySQL, these choices can be made on a per-table basis. Any given table is managed by a particular storage engine. Options can be added to the CREATE TABLE  command in order to control the manner in which the entire table is handled. ENGINE={MyISAM | InnoDB | MEMORY}   Indicates the storage engine to be used for the table  MyISAM is the default storage engine (unless --default-storage-engine has been set) COMMENT='<comme

Displaying Table Creation Information

The  SHOW CREATE TABLE  can be used to view the exact statement that was used to create an existing table. This can be helpful in understanding the structure of a table.  It can also be used as a basis for the syntax to create a new table.  For example to show  CREATE TABLE command to create the city table use the following statement; mysql> SHOW CREATE TABLE CountryLanguage\G *************************** 1. row ***************************        Table: CountryLanguage                 Create Table: CREATE TABLE `countrylanguage` (               ` Country` char(3) NOT NULL,                `Language` char(30) NOT NULL,                `IsOfficial` enum('True','False') NOT NULL DEFAULT 'False',                `Percentage` float(3,1) NOT NULL,                 PRIMARY KEY (`Country`,`Language`)                 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Lists Language                Spoken'      1 row in set (#.## sec)

Creating Database Tables

After the database structure is designed and the database has been created, individual tables can be added. Using accurate assignment of data types and their associated options, tables can be added to the database.   The command syntax is shown below, including the various column and table options;   CREATE TABLE <table> ( <column name> <column type> [<column options>], [<column name> <column type>  [<column options>],…,] [<index list>] )[<table options> ];   Example:   mysql> CREATE TABLE CountryLanguage (          ->  CountryCode CHAR(3) NOT NULL,          ->  Language CHAR(30) NOT NULL,          ->  IsOfficial ENUM('True', 'False') NOT NULL DEFAULT 'False',          ->  Percentage FLOAT(3,1) NOT NULL,          ->  PRIMARY KEY(Country, Language)          ->  )ENGINE = MyISAM COMMENT='Lists Language Spoken';   A line-by-line description of the above  CREATE TABLE st

Temporal Data Types

Date and time data types are referred to as temporal data types.  MySQL provides data types for storing  different kinds of temporal information. In the following descriptions, the terms YYYY, MM, DD, hh, mm,  and ss stand for a year, month, day of month, hour, minute, and second value, respectively. 

String Data Types

A string data type is a data type modeled on the idea of a formal string. They are commonly used to store  text or binary data. Types are available to hold values of varying maximum lengths and can be chosen  according to if the values are to be treated as text, binary or integer. Strings are such an important and  useful data type that they are implemented in nearly every programming language. Text String Type Summary For the storage requirement values, M represents the maximum length of a column. L represents the actual  length of a given value, which may be 0 to M. CHAR( )                       A fixed section from 0 to 255 characters long. VARCHAR( )               A variable section from 0 to 255 characters long. TINYTEXT                   A string with a maximum length of 255 characters. TEXT                            A string with a maximum length of 65535 characters. MEDIUMTEXT            A string with a maximum length of 16777215 characters. LONGTEXT

Numeric Data Type

       For storing numeric data, MySQL provides integer data  types, floating-point types that store approximate-value (real) numbers, a fixed-point type that stores exact-value (real) numbers, and a BIT type for bit-field  values.  When numeric data type is chosen, consider the following factors:   •   The range of values the data type represents   •   The amount of storage space that column values require   •   The column precision and scale for floating-point and fixed-point values   Precision and scale are terms that apply to floating-point and fixed-point values, which can have both an  integer part and a fractional part. Precision is the number of significant digits. Scale is the number of digits  to the right of the decimal point.  Integer Data Type  The integer data types are summarized in the following table, which indicates the amount of storage per  value that each type requires as well as its ra nge.  For integer values declared with the  UNSIGN

MySQL Data Types

           Before going to deep into the creation of tables, it is important to understand the data types that can be used within MySQL.  These data types will be assigned to  the columns that will make up the MySQL tables and will need to be understood, not only for ensuring the proper data is being stored but also to ensure a database that performs well.  One of the biggest problems associated with database performance is poor design, especially in the choice of data type s that are used in the columns.  In  MySQL the data types available can be broken down into three major categories:  Numeric      •    Numeric values (Integers, Floating-Point, Fixed-Point and Bit-field)  String          •    Text or binary data  Temporal    •   Time and dates               Within each category there are numerous specific data types that use varying amounts of memory and disk space, thus having varying effects on performance.  Choosing the best data type for the column has a rather sm