MySQL CREATE a DATABASE and TABLE
Showing existing databases
The SHOW DATABASES statement used to retrieve the existing databases. This statement returns the databases that are currently in the MySQL server, and the results are useful to avoid the conflicts while creating a new database.
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
Creating and using a databasse
The CREATE DATABASE statement used to create a database where all the tables maintained in the DATABASE directory. The USE statement notifies MySQL to use the mentioned database for subsequent database and table operations.
mysql> CREATE DATABASE DB;
Query OK, 1 row affected (0.11 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| DB                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> USE DB;
Database changed
Creating Table
The CREATE TABLE statement used to create a table, and the DESCRIBE statement used to obtain information about the table structure. Please consult the following syntax to create a database and table.
mysql> CREATE TABLE holders(
    -> account_no BIGINT PRIMARY KEY,
    -> name VARCHAR(30) NOT NULL,
    -> city VARCHAR(20),
    -> dob DATE,
    -> bank VARCHAR(15),
    -> amount BIGINT NOT NULL);
Query OK, 0 rows affected (0.52 sec)
Show and describe a table
mysql> SHOW TABLES;
+--------------+
| Tables_in_DB |
+--------------+
| holders      |
+--------------+
1 row in set (0.01 sec)

mysql> DESCRIBE holders;
mysql> DESC holders;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| account_no | bigint(20)  | NO   | PRI | NULL    |       |
| name       | varchar(30) | NO   |     | NULL    |       |
| city       | varchar(20) | YES  |     | NULL    |       |
| dob        | date        | YES  |     | NULL    |       |
| bank       | varchar(10) | YES  |     | NULL    |       |
| amount     | bigint(20)  | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
What Next?
MySQL FOREIGN KEY MySQL NOT NULL Constraint MySQL ALTER TABLE (ADD, DROP, MODIFY, CHANGE, RENAME TO)
Advertisement