MySQL CREATE TABLE
To create a new table in a database, MySQL provides the MySQL CREATE TABLE statement.
Syntax 1: To create a Table in MySQL.
CREATE TABLE table_name (
column_1 data_type column_constraint,
column_2 data_type column_constraint,
... );
Syntax 2: To see all the already created Tables in a database.
SHOW tables;
Syntax 3: To see the structure of an already created Table.
DESCRIBE table_name;
Parameters:
table_name: It is used to specify the table name.
column definition: column1, column2, … column n is used to specify the name of the multiple columns that you want to add to the table. The MySQL database does not allow a total number of columns of more than 32. A datatype is a must for each column. The data type of a column can be INT, VARCHAR, etc. The column constraint mainly defines each column as “NULL” or “NOT NULL”, with the value of “NULL” as default. One other common column constraint is the Primary Key.
Example 1: Creating a table with NULL and NOT NULL column constraints.
CREATE TABLE students ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, class VARCHAR(50) );
Explanation:
Column 1:
id: It is the name of the first column.
INT: It is the datatype of the first column which is Integer.
NOT NULL: It defines the column constraint of the first column and thus Column 1 cannot contain null values.
Column 2:
name: It is the name of the second column.
VARCHAR: It is the data type of the second column which also specifies a maximum limit of 100 characters in length for the “name”.
NOT NULL: It defines the column constraint of the second column and thus Column 2 cannot contain null values.
Column 3:
class: It is the name of the third column.
VARCHAR: It is the datatype of the third column which also specifies a maximum limit of 50 characters in length for the “class”.
NULL: There is no column constraint defined for the third column, thus it is NULL by default and thus Column 3 can contain null values.
Example 2: Creating a table with a PRIMARY KEY column constraint.
CREATE TABLE students ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, class VARCHAR(50) PRIMARY KEY(id) );
Explanation:
All three columns and their definitions are the same as that in example 1. The difference is in the existence of the Primary Key in the second example.
The PRIMARY KEY clause is a field or combination of fields that specifies a column as the primary key column. The primary key column is used for distinguishing a unique row in a table. In MySQL, a table can hold only one primary key, and every field of the primary key must contain NOT NULL values. In the above example, “id” is defined as the Primary Key Column.