Create Table in MySQL

MySQL Table is a collection of structured data, where each row represents a fact, and the columns represent properties of the fact. During the creation of a table, each column is declared to hold a specific datatype.

It is a good design practice to keep the number of columns in a table to less than about 20. Because increase in the number of columns demand more RAM to create temporary tables during a select query execution and also the demand increases geometrically with the number of columns.

Syntax

Syntax to create a table in MySQL database is given below.

</>
Copy
CREATE TABLE tablename ( 
  column_1_name datatype,
  column_2_name datatype
);

where

  • tablename : name of the DB table
  • column_i_name : ith column name
  • datatype : type of the data, the column is going to hold

MySQL can hold multiple databases. To create a table in a database using mysql prompt, first select a database using ‘USE <database_name>’.

Consider a database “studentsDB” in MySQL, in which we shall create a table called students with properties (columns) – Name and Roll Number.

</>
Copy
CREATE TABLE students (name VARCHAR(50), rollno INT);

where

  • students is the table name
  • name is a column that represents name of student and holds a maximum of 50 characters
  • rollno is a column that represents roll number of student and holds an integer

To check if the table is created, we can use sql query, “SHOW TABLES;“

+----------------------+
| Tables_in_studentsDB |
+----------------------+
| students             |
+----------------------+
1 row in set (0.00 sec)

Conclusion

In this MySQL Tutorial, we have learnt how to create table in MySQL Database.