MySQL Create Table

We have seen how to create a database in the earlier post. You can refer it by clicking the following link.

In this post I am going to show you how to create a table in a specific database. Let us have a glance at this.

We list out all the existing databases:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| captcha            |
| mysql              |
| performance_schema |
| sports             |
| test               |
+--------------------+
6 rows in set (0.00 sec)

From the above list of databases I would like to use ‘sports’ database. For this we use the following syntax.

Syntax:

use database_name;

mysql> use sports;
Database changed

Now we are in sports database. To check whether the database ‘sports’ is having any tables or not.

mysql> show tables;
Empty set (0.00 sec)

The sports database is not having any tables so now we are going to create table. For this we use the following syntax:

CREATE TABLE table_name (column_name column_type…);

Here we are creating a table named ‘Players’ in database ‘sports’;

mysql> create table players(name varchar(10), age int,sport varchar(10),country
varchar(10));
Query OK, 0 rows affected (0.13 sec)

We can check whether the table Players is created in the database ‘sports’ or not.

mysql> show tables;
+------------------+
| Tables_in_sports |
+------------------+
| players          |
+------------------+
1 row in set (0.00 sec)

We can see the table structure as shown in below:

Syntax:

describe table_name;

(or)

desc table_name;

Example:

mysql> desc players;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(10) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sport   | varchar(10) | YES  |     | NULL    |       |
| country | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.05 sec)

Note:

Here it takes Null value as default because we haven’t mentioned in query while creating. If you don’t want to keep this field with NULL value then we must mention as NOT NULL in the create query. If you try to create a table with the same name you will be displayed an error. Hence I used table name as ‘players_1’.

mysql> create table players_1(name varchar(10) not null, age int,sport varchar(1
0)not null,country varchar(10));
Query OK, 0 rows affected (0.13 sec)

Now,

mysql> desc players_1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(10) | NO   |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| sport   | varchar(10) | NO   |     | NULL    |       |
| country | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Here the fields name and sport do not accept any null values as we mentioned them as not null in create query.

If we try to create a table by using if not exists, then you will be displayed a warning and is shown in below:

mysql> create table if not exists players_1(name varchar(10) not null, age int,s
port varchar(10)not null,country varchar(10));
Query OK, 0 rows affected, 1 warning (0.05 sec)

Click here

To watch this tutorial post on YouTube Channel

Example HTML page

VMS

MySQL Create Table

Leave a Reply Cancel reply

You May Also Like

MySQL Assignment 6

MySQL Union operator

MySQL Assignment 9

Leave a Reply Cancel reply

Pin It on Pinterest