MariaDB CREATE TABLE Statement


Objectives

  • Understanding of MariaDB CREATE TABLE Statement

  • MariaDB CREATE TABLE

  • MariaDB CREATE TABLE from Different Table


Understanding of MariaDB CREATE TABLE Statement

CREATE TABLE <table_name> (
    column_name1 datatype constraint other_constraint,
    column_name2 datatype constraint other_constraint,
    column_name3 datatype constraint other_constraint
    ....
)

OR

CREATE TABLE IF NOT EXISTS <table_name> (
    column_name1 datatype constraint other_constraint,
    column_name2 datatype constraint other_constraint,
    column_name3 datatype constraint other_constraint
    ....
)
Here

  • CREATE TABLE : Keywords, responsible for create table

  • <table_name> : Name of the table. (Person, Customer etc.)

  • column_name : Name of columns. (first_name, last_name, email. etc.)

  • datatype : There is various data types available. (int, varchar, double. etc.). More about data types

  • constraint : It’s used to specify rules for data in table. (NULL, NOT NULL. etc.) More about constraint

  • IF NOT EXISTS : It’s a condition which ensure that if table already not exist then will create table.


MariaDB CREATE TABLE

Problem: Create table called person, and add the following column:

  • id (int, auto increment)

  • first_name (text, required)

  • last_name (text, not required)

  • email (text, not required)

  • age (int, not required)

  • income (double, not required)

Syntax

CREATE TABLE IF NOT EXISTS <table_name> (
    column_name1 datatype constraint other_constraint,
    column_name2 datatype constraint other_constraint,
    column_name3 datatype constraint other_constraint
    ....
);

Example

CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
);

Output

Query OK, 0 rows affected (0.222 sec)
In the table creation

  • datatype : You have seen various data types int(12), varchar(150), double, it’s means that what type of data will store by the column. You can see the whole Data types in details by following this link.

  • constraint : In create table SQL has AUTO_INCREMENT, NOT NULL, PRIMARY KEY etc. all are various constraint which has special task individually, you will learn those later on this tutorial.


MariaDB CREATE TABLE from Different Table

Problem: Create table called customer, it should be copied from person table considering following fields:

  • id

  • first_name

  • last_name

  • email

Syntax

CREATE TABLE IF NOT EXISTS <new_table_name> AS SELECT column1, column2,... FROM <existing_table_name> WHERE conditions;

Example

CREATE TABLE IF NOT EXISTS customer AS SELECT id, first_name, last_name, email FROM person;

Output

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

Note : Here primary constraint will not copy, you have to do this by ALTER table


MariaDB CREATE TABLE Video tutorial

Share