Creating A Table In MYSQL

TITLE : CREATING MYSQL TABLE
DESCRIPTION: This tutorial about mysql table. It contains the mysql table creation, alteration, deletion, description funcatioalities.
KEYWORDS : 4mysql table, mysql create table, show table, table syntax, mysql alter table, mysql drop table

Database have one or more tables. Tables are used to store data. Data will be stored as row. Every row of table is called record.

MYSQL Constraints

MySQL CONSTRAINT are declared at the time of creating a table. Constraints is used to define the rules to allow or restrict What values can be stored in columns.
MYSQL Constraints are

  • NULL – column can not contain any NULL value
  • UNIQUE – Mysql does not allow to insert a duplicate value in a column.
  • PRIMARY KEY – Mysql create a unique index for accessing the table faster
  • FOREIGN KEY – Mysql creates a link between two tables
  • DEFAULT – Each column must contain a value. If we will not give any value, DEFAULT value will set

CREATE Table

CREATE TABLE statement used to create a table.
Syntax:

CREATE  TABLE [table name]                                   
([column name] [data type]([size]) [column constraint]
 .................
[table constraint] ([[column name]....])
..................
); 

Example:

CREATE DATABASE book;

CREATE TABLE testdb.book(
      name  VARCHAR(50),
      author  VARCHAR(20)
);

Table output:

--------------------
name    | author    |
--------------------

VARCHAR() is a datatype of mysql. You can read more data type Numeric Data type, Date and Time Data type, String Data Type

SHOW Table

SHOW TABLES

SHOW TABLES statement will list out all the tables of current database.

----------------
Tables_in_book |
----------------
| book         |
----------------

DROP Table

DROP statement used to remove the table permanently from database.
Syntax:

DROP TABLE table_name

Example

DROP TABLE book

DESCRIBE Table

Syntax:

DESCRIBE DATABASE_NAME.TABLE_NAME

Example:

DESCRIBE book

To verify that your table was created the way you expected, use a DESCRIBE statement:

--------------------------------------------------------------
Field   | Type          | Null  | Key   | Default   | Extra  |
--------------------------------------------------------------
name    | varchar(50)   | YES   |       | null      |        |
--------------------------------------------------------------
author  | varchar(20)   | YES   |       | null      |        |
--------------------------------------------------------------  

ALTER Table

Using ALTER Table statement, we can modify the table like change existing column, add new column, remove column etc.

ADD

ADD “isbn” column in book.
Syntax:

ALTER TABLE book ADD col_name column_definition

Example:

ALTER TABLE book ADD isbn VARCHAR(30)

CHANGE

You can rename a column using a CHANGE old_col_name new_col_name column_definition clause. Example CHANGE the name “isbn” to “book_isbn”
Syntax:

ALTER TABLE book CHANGE old_col_name new_col_name column_definition

Example:

ALTER TABLE book CHANGE isbn book_isbn VARCHAR(20)

DROP

DROP “isbn” column in book.
Syntax:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

Example:

ALTER TABLE book DROP isbn

MODIFY

You can also use MODIFY to change a column’s type without renaming it.
Syntax:

ALTER TABLE book MODIFY col_name column_definition

Example:

ALTER TABLE book MODIFY book_isbn VARCHAR(10)

MORE ALTER ACTIONS

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | MAX_ROWS = rows
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO|AS] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | PARTITION BY partitioning_expression
  | REMOVE PARTITIONING

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name

table_options:
    table_option [[,] table_option] ...  (see CREATE TABLE options)

partition_options:
    (see CREATE TABLE options)

Examples

Table With Null

CREATE TABLE IF NOT EXISTS book(
    book_id INT(11) NOT NULL,
    book_name VARCHAR(20) NOT NULL
);

Table With UNIQUE, NOT NULL, PRIMARY KEY

Previous example table will accept duplicate book_id.

CREATE TABLE IF NOT EXISTS book(
    book_id INT(11) NOT NULL UNIQUE,
    book_name VARCHAR(20) NOT NULL,
    PRIMARY KEY (book_id)                 
);

Table With DEFAULT Value

CREATE TABLE IF NOT EXISTS book(
    book_id INT(11) NOT NULL UNIQUE,
    book_name VARCHAR(20) NOT NULL,
    country VARCHAR(20) DEFAULT 'INDIA'
);