YOUR WEBSITE

           When creating a table in MySQL, the storage engine that will be used has to be chosen.  If one is not chosen, MySQL uses the default storage engine chosen in the configuration of the server.  MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables. There are numerous storage engines that MySQL supports; however the most common storage types that are used with PHP are listed below:  •  MyISAM -                     This storage engine is  supported in all MySQL configurations and is the default storage engine for all new tables created  (when a specific storage engine is not assigned) in the majority of default installations.  This  storage engine manages non-transactional tables.  It provides high-speed storage and retrieval, as  well as fulltext searching capabilities.  MyISAM is a disk based storage engin

Table Properties           The heart of any database is the tables, because the tables contain the data that is used to provide information  to the end users.  Tables are contained in databases, and in MySQL each database has an associated "data  directory" to store all the physical components of the database, to include tables.  This physical  representation on disk of the tables  is dependent on the type of storage engine that the table is utilizing;  however, every table, no matter of the type of storage engine that is being used, contains a format file  (table_name.frm) which describes the table definition and structure.              In addition to the physical representation of the table, there is also the better known logical aspect of the  table. This logical aspect is represented in rows and columns.  Rows within a table, consist of a collection of  values that describe an entity (ex. an employee).  Columns within a table, consist of a collection of similar  dat

When a database is no longer needed, it can be removed with DROP DATABASE:  DROP DATABASE my_db;  It is an error if the database does not exist.  To cause a warning instead, include an IF EXISTS clause;  DROP DATABASE IF EXISTS my_db;   Any warning generated when IF EXISTS is used can be displayed with SHOW WARNINGS.  DROP DATABASE  does not require the database to be empty.  When dropping the database, MySQL removes any objects that it contains; such as tables, stored procedures, triggers, etc.  A successful DROP DATABASE returns a row count that indicates  the number of tables dropped. (This actually is the number of  .frm files removed, which amounts to the same thing.)  Use the  SHOW DATABASES command to verify that the database has been dropped.  CAUTION: When Using DROP DATABASE  A database is represented by a directory under the data directory. The server deletes only files and directories that it can identify as having been created by itself (for example,  .

    Creating a database has no effect on which database currently is selected as the default database.  To make  the new database the default database, issue a USE statement:   USE my_db;  After a database has been created, it can be populated with objects such as tables or stored routines.  The  CREATE statement for tables is discussed in a later chapter.  Displaying Database Structure   The easiest way to display the database structure (as far as the database container) is to issue a SHOW CREATE DATABASE command. This command shows the  CREATE DATABASE statement that was used to create  the database (or could be used to recreate the database structure):   mysql> SHOW CREATE DATABASE my_db\G  ************************* 1. row *************************         Database: my_db         Create Database: CREATE DATABASE `my_db`                   /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_danish_ci */ Note: SCHEMA can be interchanged with DATABASE to perform the a

To create a new database, use the  CREATE DATABASE statement.  The following statement creates a  database named my_db:   CREATE DATABASE my_db;  If an attempt is made to create a database that already exists, an error occurs.  To ensure that the database  exists, add an IF NOT EXISTS clause to the statement:   CREATE DATABASE IF NOT EXISTS my_db;  With the additional clause, the statement creates the database only if it does not already exist.  Otherwise,  the statement does nothing and no error occurs.  This can be useful in applications that need to ensure that a  given database is available, without disrupting any existing database with the same name.   The CREATE DATABASE statement has two optional clauses, CHARACTER SET and COLLATE, that assign a default character set and collation for the database.  If given, they appear at the end of the statement following the database name.  The following statement specifies that the mydb database has a default character set

            The are five kinds of identifiers that need to be considered in MySQL: databases, tables, columns, indexes  and aliases.  Databases, as stated are the containers for the tables and other associated database components.  Tables hold the data that is used to produce information.  Columns are the individual entities of the tables that  contain the smallest portion of the data.  Indexes in databases refer to the data structures that improve the  speed of operations in a table. Aliases are temporary names assigned to database components. The identifiers  that can be assigned to each component have their limitations: •  Size -               The following table describes the max length each database identifier can be assigned: Identifier Maximum Length Database 64 Bytes Table 64 Bytes Column 64 Bytes Index 64 Bytes Alias 255 Bytes       The length of th

In database discussions, it is common to hear the term normalization or database design come up. However, for the most parts these discussions revolve around splitting up the data that is stored between different  tables to improve performance and to eliminate duplication.  This is extremely important and should be  discussed in any discussion on tables but for the most part the discussions do not involve splitting up data  between databases.  This is either assumed or just not put into practice as often as it should be.  Either way,  it is important to consider when designing an application that will need to access data. Information Commonality Data could be defined as the actual values that are stored in the database itself and are static until they are changed by some process (either manually or automated). Data by itself is useless if not processed in such a way as to give it meaning.  Information, on the other hand, is data processed in such a way as to give it meani