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)