Creating Tables in MySQL

A table is where actual data is stored in rows and columns.
 Before creating tables, a database must be selected using the USE command.

1. CREATE TABLE (Without Primary Key)

Description

Creates a table without any constraints.
 Such tables allow duplicate and NULL values and are mostly used for temporary or staging purposes.

Syntax

CREATE TABLE table_name (

column datatype,

column datatype

);

Example

CREATE TABLE emp (

emp_id INT,

emp_name VARCHAR(50),

gender VARCHAR(1),

location VARCHAR(50),

mobile INT

);

2. CREATE TABLE with PRIMARY KEY (Method 1 – Inline)

Description

Defines the primary key directly along with the column.
 A primary key ensures uniqueness and does not allow NULL values.

Syntax

column datatype PRIMARY KEY

Example

CREATE TABLE emp1 (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(50),

gender VARCHAR(1),

location VARCHAR(50),

mobile INT

);

3. CREATE TABLE with PRIMARY KEY (Method 2 – Separate Definition)

Description

Defines the primary key at the end of the table creation statement.
 This method is preferred when working with composite primary keys or large tables.

Syntax

PRIMARY KEY (column_name)

Example

CREATE TABLE emp2 (

emp_id INT,

emp_name VARCHAR(50),

gender VARCHAR(1),

location VARCHAR(50),

mobile INT,

PRIMARY KEY (emp_id)

);

4. SHOW TABLES

Description

Displays all tables in the currently selected database.

Syntax

SHOW TABLES;

Example

SHOW TABLES;

5. DESCRIBE TABLE (DESC)

Description

The DESC command shows the structure of a table including column names, data types, constraints, and keys.

Syntax

DESC table_name;

Example

DESC emp;

6. DROP TABLE

Description

Deletes a table and all its records permanently from the database.

Syntax

DROP TABLE table_name;


Example

DROP TABLE emp2;

Key Takeaways