Creating a table in MYSQL database As you know, tables are used to store data.
Here, we will create a very simple table Courses containing three fields :Course id (the primary key),Course name (the name of the Course), and Duration (Duration of the Course).
First, you need to select the database you just created. Once you have selected a database, any database – manipulation commands you enter work on that database. Type the following code:
USE mydatabase;
Press Enter, and you will see Database changed
mysql >
Now create the table. Type the code at the mysql > prompt:
mysql > CREATE TABLE Course (
– > Course id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
– > Course name VARCHAR(30) NOT NULL,
– > Duration VARCHAR(30) NOT NULL,
– > PRIMARY KEY (Course id)
– > );
Press Enter at the end of each line.MySQL displays these automatically each time you press Enter, to inform you that your statement is being continued on a new line. If the code is executed successfully then you will see the response as Query OK, 0 rows affected.
As now you have created your table. To see the table use the SHOW TABLES command:
mysql > SHOW TABLES;
Output
Tables_in_mydatabase
Course
1 row in set
You can even see the structure of your created table by using the EXPLAIN command, as follows:
mysql > EXPLAIN Course;
Output
Field | Type | Null | Key | Default | Extra |
Course id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
Course name | varchar(30) | NO | NULL | ||
Duration | varchar(30) | NO | NULL |
3 rows in set