SQL CREATE Table

By | September 6, 2020
SQL CREATE Table

After creating and selecting a database using CREATE and USE commands now we can create a table inside that database, and keep remember we can create more than one table in a single database.

What is a Table in the Database?

A table is a collection of rows and columns which form cells like structure and the data reside in these cells, and each data has a relation with its corresponding row and column.

Vamware

CREATE TABLE commands

With the help of CREATE TABLE command, we can create a new table in a database and make sure while you creating a table you must have selected a specific database.

Syntax

Follow this Syntax to CREATE a table:
CREATE TABLE table_name (
column_name_1 datatype,
column_name_2 datatype,
……
column_name_n datatype)

Example

First, select the database:

USE student_db;

Output

Database Changed;

Create Table Query

CREATE TABLE students (
    id INT(6),
    name VARCHAR(30),
    age INT(4),
    grades VARCHAR(2),
    marks INT(4)
    );

Output

Query OK, 0 rows affected (0.50 sec)

Verify If the table is created:

Query

SHOW TABLES;

Output

+----------------------+
| Tables_in_student_db |
+----------------------+
| students             |
+----------------------+
1 rows in set (0.00 sec)

To verify if your table has been created successfully along with its attributes, use the DESC SQL command.

Query

DESC students;

Output

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(6)      | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| age    | int(4)      | YES  |     | NULL    |       |
| grades | varchar(2)  | YES  |     | NULL    |       |
| marks  | int(4)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.14 sec)

Create a Table using Another table:

In a single database, we can have multiple tables, and all the tables can be created using the CREATE TABLE command. With the help of CREATE table command, we can copy an existing table and create a new one, as a backup table.

Syntax

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name;

Example

Create a duplicate table Query

CREATE TABLE student_backup as SELECT * FROM students;

Output

Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verify Query

SHOW TABLES;

Output

+----------------------+
| Tables_in_student_db |
+----------------------+
| student_backup       |
| students             |
+----------------------+
2 rows in set (0.00 sec)

Summary

  • We can create multiple tables inside a database.
  • A table is a collection of rows and columns, which form cells.
  • Data reside in the cells.
  • Each data has a relation with its corresponding row and column.
  • To create a table, we use CREATE TABLE command.

Leave a Reply

Your email address will not be published. Required fields are marked *