MySQL Create Table

  • CREATE TABLE creates a table with the given name.
  • We must have the CREATE privilege for the table.
  • By default, tables are created in the default database.

MySQL Create Table Syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name (create_definition,...) [table_options] [partition_options]

Example:

1. Create employee table

CREATE TABLE EMPLOYEE (EMPNO NUMERIC(4) NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR NUMERIC(4), HIREDATE DATETIME, SAL NUMERIC(7, 2), COMM NUMERIC(7, 2), DEPTNO NUMERIC(2));

To get list of tables in current database:

mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | EMP | | EMPLOYEE | +-------------------+

mysql> describe EMPLOYEE; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | EMPNO | decimal(4,0) | NO | | NULL | | | ENAME | varchar(10) | YES | | NULL | | | JOB | varchar(9) | YES | | NULL | | | MGR | decimal(4,0) | YES | | NULL | | | HIREDATE | datetime | YES | | NULL | | | SAL | decimal(7,2) | YES | | NULL | | | COMM | decimal(7,2) | YES | | NULL | | | DEPTNO | decimal(2,0) | YES | | NULL | |

MySQL Create Table