How to Create Table in MySQL Command Line

Now that we have created our database. In this tutorial, we are going to see how to create a table in MySQL using command line.

CREATE TABLE command is used to create a new table in a database. The CREATE TABLE command requires three things:

• Table name
• Field names
• Definitions for each field

 

Syntax:

CREATE TABLE tableName (
	column1 type,
	column2 type,
	column3 type,
	...
);

 

How to Create Table in MySQL using Command Line

The following statement creates a new table named “Persons”:

CREATE TABLE Persons (
    PersonID int AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Age int,
    Address VARCHAR(100)
);

The “Persons” table contains the following columns:

• PersonID is a column that automatically increments. If you use the INSERT statement to insert a new row into the table without specifying a value for the PersonID column, MySQL will automatically generate a sequential integer for the PersonID column starting at 1.
• Name column is a string column with a maximum length of 20. This means that you cannot insert strings longer than 20 in this column. NOT NULL constraint indicates that the column does not accept NULL value. In other words, you must provide a non-NULL value when you insert or update this column.
• The same for Address column, except that it accepts NULL value
• “Age” column is an int type column that accepts NULL value.

 
PersonID column is a primary key of “Persons” table. This means that the values in PersonID column uniquely identify the rows in the table.

Once you have executed CREATE TABLE statement to create the “Persons” table, you can see its structure using DESCRIBE statement:

mysql> DESCRIBE Persons;

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More