Are you struggling to create well optimized, professional database table? Read this article or watch this YouTube tutorial to learn how to create professional and well optimized table in MySQL database.
In this article we will learn what is a table in database? And some important SQL statement related to DB table. To learn practically, you must have MySQL installed on your computer. If you are using Windows operating system. Then, checkout this article showing, how to install MySQL 8 on Windows 10 OS.
Database Table
A DB table is used to store data. It is a systematic structure of columns and rows. The vertical structures are columns or fields and data rows or records are horizontal. Each column represents the property of an item, while each row is an item. A Cell is a unit where a column and row intersect. And the values are stored inside cells. The tables has a specified number of columns but it can contain any number of rows. A single database can contain multiple numbers of tables but each tablename must be unique.
Important SQL Statements
Create Test Database
For testing purpose create a new school database as follows,
CREATE DATABASE school;Before creating a table we must select a database. Using the following SQL statement to select newly created school database.
USE school;Then display all tables inside the selected school database using following SQL statement.
Show Tables SQL Statement
SHOW TABLES;As, we just created school database, in the result we will get no table. Now, create a new database table using “create table statement”. The create table statement inside parentheses contains column names and their data types.
Create a New DB Table Statement
CREATE TABLE students (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(40) NOT NULL,
last_name VARCHAR(40) NOT NULL,
class VARCHAR(20) NOT NULL,
age TINYINT(2) UNSIGNED NOT NULL
);Press Enter, and the new table named students has been created successfully. We can confirm newly crated table using show tables again.
SHOW TABLES;We can see the table definition using, describe tablename, here students as follows.
DESCRIBE students;And we can also add some test data rows to our newly created table using insert table like,
Add Test Data Rows in Newly Added Table
INSERT INTO students (first_name, last_name, class, age) VALUES ("John", "Doe", "First", 5);we can see the data rows inside our students table using,
Display Data Rows in a Table
SELECT * FROM students;Finally, we can delete our students table using drop table tablename here students as follows.
Delete or Drop a Table
DROP TABLE students;And confirm, if the table has been removed successfully using show tables statement.
SHOW TABLES;This time, we can’t see any table under our school database. The table and our data is gone.
Please note: When you are deleting a table, you’ll not see any confirmation message. As soon as you execute the statement, the table and data will get deleted. And we can’t recover a deleted table and its data. So, always make sure you are deleting the correct table. Or backup your database before executing any drop statement.
In this article, we learned different SQL statements about the table in a database.