DDL - MySQL (TABLE)

Create a table (CREATE)

Syntax

CREATE TABLE <table_name> (
    attr1 type_attr1 COMMENT "optional comment",
    aatrn type_attrn
) COMMENT "optional comment";

Usage

• Create a database named student.
• Has 4 Attributes, which is student_id, student_name, age, gender
• Their data types are, int, varchar(255), varchar(1), int.
• For attribute gender, using M and F only to represents Male and Female.
• Comment of the table and on some attributes is required.

INFO

• For Data Types in MySQL: Click here

mysql> CREATE TABLE IF NOT EXISTS student (
    -> student_id int,
    -> student_name varchar(255) comment "Name",
    -> age int comment "Age",
    -> gender varchar(1) comment "Gender(M/F)"
    -> ) comment "students table";

INFO

• When pressing Enter it will open a new row, but it's not end of the SQL code.
• -> means a new line of a SQL code.
• Only Semicolon ; is added on the end of the SQL then it will be the end of the SQL code.

Output:

Query OK, 0 rows affected (0.03 sec)

INFO

• Method of validate if the table is created: Click here

Show Tables (SHOW)

Syntax

SHOW TABLES;

Usage

Validate if table is created.

mysql> SHOW TABLES;

Output:

+----------------+
| Tables_in_test |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

Describle Tables (DESC)

• Describle table is use, when we want to get the structure of the tables.

Syntax

DESC <table_name>;

Usage

mysql> DESC student;

Output:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int          | YES  |     | NULL    |       |
| student_name | varchar(255) | YES  |     | NULL    |       |
| age          | int          | YES  |     | NULL    |       |
| gender       | varchar(1)   | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

Show the SQL code for a table that has created (SHOW CREATE TABLE)

Syntax

SHOW CREATE TABLE <table_name>;

Usage

mysql> SHOW CREATE TABLE student;

Output:

+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                      |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `student_id` int DEFAULT NULL,
  `student_name` varchar(255) DEFAULT NULL COMMENT 'Name',
  `age` int DEFAULT NULL COMMENT 'Age',
  `gender` varchar(1) DEFAULT NULL COMMENT 'Gender(M/F)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='students table' |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)