Creating Databases and Tables Using SQL Commands

From Techotopia
Revision as of 14:11, 19 March 2009 by Neil (Talk | contribs) (→‎Creating Tables with SQL)

Jump to: navigation, search
PreviousTable of ContentsNext
Creating New Databases and Tables with MySQL AdministratorUpdating and Deleting MySQL Tables

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 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 MySQL Query Browser.


Contents

Creating a New MySQL Database

A new database is created using the CREATE DATABASE SQL statement followed by the name of the database to be created. The CREATE SCHEMA statement may also be used for this purpose. For example, to create a new database called MySampleDB the following statement would be entered at the mysql> prompt: 

CREATE DATABASE MySampleDB;

If successful, the command will generate output similar to the following: 

Query OK, 1 row affected (0.00 sec)

If the database name specified conflicts with an existing database MySQL will display and error message reporting this fact: 

ERROR 1007 (HY000): Can't create database 'MySampleDB'; database exists

In this situation, a different database name should be selected, or the IF NOT EXISTS option should be used. This option only creates the database if it does not already exist: 

CREATE DATABASE IF NOT EXISTS MySampleDB;

Creating Tables with SQL

<google>ADSDAQBOX_FLOW</google> New tables are added to an existing database using the SQL CREATE TABLE statement. The CREATE TABLE statement is followed by the name of the table to be created followed by a comma separated list of the names and definitions of each table column:

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

USE MySampleDB;

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

CREATE TABLE customer 
( 
customer_id int NOT NULL AUTO_INCREMENT, 
customer_name char(20) NOT NULL, 
customer_address char(20) NULL, 
PRIMARY KEY (customer_id) 
) ENGINE=InnoDB;

Understanding NULL and NOT NULL Values

When a column is specified to be NULL then a row can be added to a database when there is no value assigned to that column. Conversely, if a column is defined as NOT NULL then it must have a value assigned to it before the row can be added to table.

Primary Keys

As covered in Database Basics a primary key is a column used to identify individual records in a table. The value of a primary key column must be unique within the context of the table in which it exists, or if multiple columns are combined to constitute a primary key, the combination of key values must be unique to each row.

The primary key is defined using the PRIMARY KEY statement during table creation. If multiple columns are being used they are comma separated: 

PRIMARY KEY (column_name, column_name ... )

In the following example, a table is created using two columns as the primary key: 

CREATE TABLE product 
( 
  prod_code INT NOT NULL AUTO_INCREMENT, 
  prod_name char(30) NOT NULL, 
  prod_desc char(60) NULL, 
  PRIMARY KEY (prod_code, prod_name) 
) ENGINE=InnoDB;

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

SELECT last_insert_value();

Defining Default Values During Table Creation

Default values during table creation allow values to be specified for each column to be used when a value is not specifically defined when rows are inserted into to a database.

Default values are specified using the DEFAULT keyword in the CREATE TABLE statement. For example, the following SQL statement specifies a default value for the sales_quantity column: 

CREATE TABLE sales
{
      sales_number   int    NOT_NULL,
      sales_quantity    int    NOT_NULL DEFAULT 1,
      sales_desc     char(20) NOT_NULL,
      PRIMARY KEY (sales_number)
) ENGINE=MyISAM;

If no quantity is defined when a new row is inserted, MySQL will insert the default value of 1.

MySQL Database Engine Types

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 searching.
  • 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 suitable for temporary table storage.

Engine types may be mixed 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: 

CREATE TABLE tmp_orders
{
      tmp_number   int    NOT_NULL,
      tmp_quantity    int    NOT_NULL,
      tmp_desc     char(20) NOT_NULL,
      PRIMARY KEY (tmp_number)
) ENGINE=MEMORY;
PreviousTable of ContentsNext
Creating New Databases and Tables with MySQL AdministratorUpdating and Deleting MySQL Tables
Retrieved from "https://www.techotopia.com/index.php?title=Creating_Databases_and_Tables_Using_SQL_Commands&oldid=6895"