How to Create MySQL Database tables?

Creating Database tables

USE database_name : Selects the database identified by the database_name. Once you got into cmd window and changed the directory to mysql bin folder. First you need to enter mysql.exe and your credentials, then you need to select the database you will use.

mysql> USE test;
Database changed
mysql>

Now you are in the database named test.

SHOW TABLES : List all the tables from the selected database.

mysql> SHOW TABLES;
Empty set

 If there is no table at the database you will get the empty set message as shown above.

CREATE TABLE : Creates tables at the selected database

 

CREATE TABLE SYNTAX:

CREATE TABLE Table_Name  (

                                           column_name  column_type(size),

                                           column_name  column_type(size),

                                          column_name  column_type(size),

                                          ...

                                          ) ;

Column types (mysql data types) are either numeric (int, FLOAT, REAL etc.) or date and time or string (CHAR, VARCHAR etc).

In addition to the data type, you can also set the column not to be NULL by adding NOT NULL next to the column_type(size) and you can also set the column values to increase automatically as the other columns expands by adding AUTO_INCREMENT next to the column_type(size). Size value here is in bytes.

example

mysql> CREATE TABLE table_1(
-> id INT(3) NOT NULL AUTO_INCREMENT,
       -> column1 VARCHAR(30) NOT NULL,
-> column2 DATETIME,
-> PRIMARY KEY (id)
-> );

DROP TABLE : Deletes the table

example

mysql> DROP TABLE table_1;