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.
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.
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.