What other words we can use when creating a table

Word Example Decription
default 
create table example(
   name	 char(24) not null,
   areacode smallint(3) default 304
);
 Sets a dafault value for the attribute
unique [key] 
create table example(
  name  char(32) not null default 'bob',
  code  smallint(3) default 304,
  phone int(7) unique
);
or 
create table example(
  name  char(32) not null default 'bob',
  code  smallint(3) default 304,
  phone int(7),
  unique (code, phone)
);
 Set a candidate key. Remember you can have several candidate keys.
primary key 
create table example(
  name  char(32) not null default 'bob',
  code  smallint(3) not null default 304,
  phone int(7) not null,
  primary key (code, phone)
);
 Sets the primary key of the table. Remember all attributes included in the primary key must be nul NULL. If you can't request this make this combination unique and choose a different primary key.
foreign key 
create table fk_test (
  name char(32) not null primary key,
  boss int(11),
  foreign key (boss) references def_test(id)
);
 MySQL Server does parse the FOREIGN KEY syntax in CREATE TABLE commands, but without further action being taken. The FOREIGN KEY syntax without ON DELETE ... is mostly used for documentation purposes. Some ODBC applications may use this to produce automatic WHERE clauses, but this is usually easy to override. FOREIGN KEY is sometimes used as a constraint check, but this check is unnecessary in practice if rows are inserted into the tables in the right order. In MySQL Server, you can work around the problem of ON DELETE ... not being implemented by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys.

Inserting data

There are several ways to insert data into a table.
  1. Insert statement
    The usual form of INSERT statement is:
    insert [into] table_name [(column_name_1, column_name_2, column_name_3, ...)] values (value_1, value_2, value_3, ...);
    Example 1: 
     insert into customers values (NULL, 'Julie Smith', '25 Oak St', 'Airport West');
    Example 2: 
     insert into customers (name, city) values ('Melissa Jones', 'Leeton');
  2. Load statement Instead of inserting data row by row you can load a whole text file into a table. In order to do this you need to run LOAD command: 
     LOAD DATA INFILE "pet.txt" INTO TABLE pet;
    This will read data from the file pet.txt into the table pet. By default, data fields in the file must be separated by tabs and enclosed in single quotes, and each row must be separated by a new line (\n).
    You can find more information and complete syntax of this command here.
  3. Select statement MySQL Server doesn't yet support the Oracle SQL extension: SELECT ... INTO TABLE .... MySQL Server supports instead the ANSI SQL syntax INSERT INTO ... SELECT ..., which is basically the same thing. See INSERT ... SELECT Syntax.
    Example: 
     INSERT INTO tblTemp2 (fldID)
   SELECT tblTemp1.fldOrder_ID
	 FROM tblTemp1
	WHERE tblTemp1.fldOrder_ID > 100;
    A couple of restrictions:

Dropping tables

To drop a table use DROP TABLE command.
Example: 
drop table pet;
This command will remove the table from the database and delete all the rows.

Altering tables

If you realized that the table you created is not in the desirable form any more you can drop it and create it again, but doing this you lose all your data. To modify your table and keep the data use alter table command. The usual format of this command is:
alter table table_name alteration [, alteration ...];
The different types of alteration clause are shown in the table below.

Syntax Description
ADD [COLUMN] coulmn column_description
[FIRST | AFTER coulmn]		 Add a new coulmn in the specified location (if not specified, the column goes at the end).
ADD [COULMN] (coulmn column_description
[, coulmn column_description ...])		 Add one or more columns at the end of the table.
ADD INDEX [index_name] (column [, column ...]) Add an index to the table on the specified column or columns.
ADD PRIMARY KEY (column [, column ...]) Make the specified column or columns the primary key.
ADD UNIQUE INDEX [index_name] (column
[, column ...]) 		Add a unique index to the table on the specified column or columns.
ALTER [COLUMN] column {SET DEFAULT value | DROP DEFAULT} Add or remove a default value for a particular column.
CHANGE [COLUMN] column new_column_name new_column_description Change the column called column so it has the description listed.
MODIFY [COLUMN] column column_description Similar to CHANGE. Can be used to change column types, not names.
DROP [COLUMN] column Delete the name column.
DROP PRIMARY KEY Delete the primary index (but not the column).
DROP INDEX index Delete the named index.
RENAME [AS] new_table_name Rename a table.

Example: 

create table alter_test (
  id   bigint(10),
  name char(10)
);
alter table alter_test modify id int not null;
alter table alter_test add primary key (id),
	  modify id int not null auto_increment;
alter table alter_test add column lastname varchar(32) binary after id;
alter table alter_test alter column lastname set default 'Smith';
alter table alter_test add unique (lastname, name);
alter table alter_test change name firstname char(24) not null;