34,333
edits
Changes
no edit summary
<hr>
In the previous chapter ([[Creating New Databases and Tables with MySQL Administrator]]) we created a new database and table using a graphical tool that did not rquire require us to use any SQL commands. In this chapter we will learn how to create new databases and tables by issuing SQL commands using the ''mysql'' client.
This chapter assumes that the ''mysql'' tool is running and connected to the the MySQL database server. If this is not the case and you are unsure as to how to achieve this refer to [[The mysql Command-Line Tool]] chapter. Alternatively, the SQL commands outlined in that chapter may be executed in the [[The MySQL Query Browser | MySQL Query Browser]].
</pre>
In this situation, a different database name should be selected, or the ''IF NOT EXISTS'' option, which should be used. This option only creates the database if it does not already exists, and does not report an error if it doesexist:
<pre>
CREATE TABLE ''table_name'' ( ''column_name definitions'', ''table_name definitions'' ..., PRIMARY KEY=(''column_name'') ) ENGINE=''engine_type'';
The definitions field for each column define information such as the data type of the column, whether the column can be NULL and whether the column value auto increments. The CREATE TABLE statemnt statement also allows a column (or group of columns) to be specified as the primary key (see [[Database Basics]] for a description of primary keys).
Before a table can be created a database must first be selected so that MySQL knows where to create the table. This is achieved using the ''USE'' SQL statement:
</pre>
Having selected a database, the following example creates a table consisting of three columns named ''customer_id'', ''customer_name'' and ''customer_address''. The ''customer_id'' and ''customer_name'' columns must contain values (i.e NOT NULL). The ''customer_id'' holds an integer value whichj which will auto increment as new rows are added and the others hold character strings up to 20 characters in length. The primary key is defined to be the ''customer_id''
<pre>
== AUTO_INCREMENT ==
AUTO_INCREMENT is one of the simplest, yet most useful column definitions in the SQL language. Essentially, when a column is defined using AUTO_INCREMENT the value of the column is increased automatically each time a new row is added to a table. This is especially useful when using a column as a primary key. By using AUTO_INCREMENT it is not necessary to write SQL statements to calculate a new unique id for each row. This is all handled by the MySQL server when the row is added.
There are two rules that must be obeyed when using AUTO_INCREMENT. Firstly, only one column per table may be assigned AUTO_INCREMENT status. Secondly, the AUTO_INCREMENT column must be indexed (e.g. by declaring it as the primary key).
It is possible to override the AUTO_INCREMENT value of a column simply by specifying a value when executing an INSERT statement. As long as the specified value to is unique the value provided will be used in the new row and subsequent increments will start at the newly inserted value.
MySQL may be interrogated to retrieve the most recent increment value using the ''last_insert_id()'' function as follows:
Each of the example table creation statements used so far in this chapter has included an ''ENGINE='' definition. MySQL ships with a number of different database engines, each of which have particular strengths. Using the ''ENGINE='' directive it is possible to select which database engine is used on a per table basis. MySQL database engines currently available are:
* '''InnoDB'' - The InnoDB was introduced with MySQL version 4.0 and is categorized as a ''transaction-safe'' database. A transaction safe database engine ensures that all database transactions are 100% completed, and rolls back any partially completed transactions (for example as the result of a server or power failure). This ensures that a database is never subject to partially completed data updates. A drawback of the InnoDB database engine is that it does not support full-text serachingsearching.
* '''MyISAM''' - The MySQL MyISAM database engine is a high-performance engine with support for full-text searching. This performance and functionality comes at the price of not being transaction safe.
* ''MEMORY''' - The MEMORY database engine is equivalent to the MyISAM database in terms of functionality with the exception that all data is stored in memory as opposed to being disk based. This makes the engine extremely fast. The transient nature of data in memory makes this engine more sauitable suitable for temporary table storage.
Engine types may be mixed withion within a database, for example some tables may use the InnoDB engine, whilst others use MyISAM. If no engine is specified during table creation, MySQL will default to MyISAM for that table.
To specify an engine type for a table simply place the appropriate ''ENGINE='' definition after the table columns has been defined. The following example specified the ''MEMORY'' engine: