MySQL Create Table

A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). A Database table simply organizes the information into rows and columns.

MySQL create table syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

In MySQL, following command is use to create a table:

CREATE TABLE student (
    id INT,
    name VARCHAR(50),
    dob DATE,
    phone VARCHAR(15)
);

MySQL create table with constraints:

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    dob DATE,
    phone VARCHAR(15) NOT NULL UNIQUE
);

Note: You can execute the commnad DESC table_name; to see the column information or structure of any table in MySQL.

Create Table If Not Exists

If you try to create a table that is already exists inside the database you'll get an error message, to avoid this in MySQL you can use an optional clause IF NOT EXISTS as follow:

CREATE TABLE IF NOT EXISTS student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    dob DATE,
    phone VARCHAR(15) NOT NULL UNIQUE
);

MySQL - Duplicate a Table

You can duplicate a table and its contents by executing the following command:

CREATE TABLE new_table AS SELECT * FROM original_table;

The above command creates a new table and copy contents from existing table.

CREATE TABLE new_table LIKE original_table;

The above command makes the structure of new_table exactly like the original_table, but DOES NOT copy the data.

The AND and OR operators are used to filter records based on more than one condition

Note: If you want to see the list of tables inside the currently selected database, you can execute SHOW TABLES;

MySQL - Rename a Table

Sometime it is required to rename a table. MySQL provides a useful syntax that can rename one or more tables in the current database.

RENAME TABLE old_table TO new_table;

new_table is the new name that must not exist in the database, and old_table should be present in the database.

MySQL RENAME TABLE is also use to change more than one table name:

RENAME TABLE old_tab1 TO new_tab1, old_tab2 TO new_tab2, old_tab3 TO new_tab3;

Every table is broken up into smaller entities called fields. The fields in the Student table consist of StudentID, StudentName, ContactNumber, Address, PostalCode and Course. A field is a column in a table that is designed to maintain specific information about every record in the table. A column is a vertical entity in a table.

A record, also called a row, is each individual entry that exists in a table.

Ad: visit this page

Ad: math videos for kids