Home > MySQL > Create and Dropprinter version

MySQL Create and Drop

This tutorial assumes that you have access to the MySQL command prompt.

MySQL Syntax

The nice thing about SQL commands is that they read like English sentences and do exactly what they say.

SQL commands are SQL statements with attributes and by convention the statements are in upper-case and the attributes in lower-case.

SQL ignores white space therefore commands always end with the ; character.

Creating a Database

The CREATE DATABASE database_name statement creates a new empty database where database_name is the name of the database to create.

Before you can work on a database you must issue the command USE database_name where database_name is the name of the database you wish to use.

Creating and Using a Database

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.02 sec)

mysql> USE test;
database changed
mysql>

Creating a Table

A database is made up of tables that hold different types of data. The table below shows some common data types.

Data Type	Description
int (length)	Integer of length specified.
float (length, decimal)	Floating point number of length specified.
char (length)	Fixed length text string padded with spaces to length specified.
varchar (length)	Variable length text string unpadded with the specified maximum length.
text	Text string up to 64K in size.
timestamp (length)	Holds a date and time in the format YYYYMMDDHHMMSS. Updates each time the row is modified or may be changed manually. Displays to the length specified.
blob	Holds up to 64K of data.

Note: Many attribute types have variants of tiny, small, medium and big that allow more precise control of size e.g. smallint.

The CREATE TABLE command has three parts.

The CREATE TABLE statement followed by a table name that may contain any character except a forward slash or a period.
An opening bracket followed by a list of attributes, data types and optional modifiers separated by commas.
A list of keys that define which attributes are to be indexed for fast access, ending with a closing bracket.

Attributes

These are the names of the table columns.

Data Types

These are any of the data types shown in the table above.

Modifiers

These may be applied to attributes e.g. DEFAULT sets the value of the attribute to whatever value follows the DEFAULT modifier if no data is supplied. NOT NULL means that data can't be added without this attribute having a value.

Keys

Keys are used to allow fast access to commonly used rows in a table and are composed of one or more attributes in combination.

A PRIMARY KEY is an attribute that is a unique identifier in the table. It must be defined as NOT NULL and any value inserted must be unique.

Creating a Table

mysql> CREATE TABLE people (
id int(6) DEFAULT '0' NOT NULL auto_increment,
firstname varchar(30) NOT NULL,
lastname varchar(30) NOT NULL,
address varchar(100) NOT NULL,
county varchar(40),
country varchar(30) DEFAULT 'UK',
age int(3),
PRIMARY KEY (id)
);
Query OK, 0 rows affected (0.16 sec)

mysql>

Altering Tables

Once created a table may be altered. Attributes or keys may be added or deleted with the ALTER TABLE statement.

Adding an Attribute to a Table

mysql> ALTER TABLE people ADD COLUMN postcode varchar(8) AFTER address;
Query OK, 0 rows affected (0.09 sec)
Records : 0 Duplicates : 0 Warnings : 0

mysql>

The statement above adds an additional attribute called postcode after the address attribute. The statement below then deletes the postcode attribute.

Deleting an Attribute from a Table

mysql> ALTER TABLE people DROP COLUMN postcode;
Query OK, 0 rows affected (0.05 sec)
Records : 0 Duplicates : 0 Warnings : 0

mysql>

Dropping a Database or Table

DROP database_name and DROP table_name respectively are used to destroy a database and table.

Example of Dropping a Database and Table

mysql> DROP TABLE people
Query OK, 0 rows affected (0.14 sec)

mysql> DROP DATABASE test
Query OK, 0 rows affected (0.11 sec)

mysql>

Note: When you DROP a table or database all data within the table or database is lost and is not recoverable.

Previous - MySQL Basics Next - Show and Describe