A table is used to organize data in the form of rows and columns. It is used to store and display records in a structure format. it Is one of the first database element that we need to have to start working data. Table is like a spreadsheet where rows and columns are structured. Creating Table, requires the following three important elements
- The Name of Table
- The Field/Column Definition of Table
- The Description as well as DataType of the fields
Creating Table
In the creation process of Table , it is possible to use a command line or workbench IDE. The command used to create a table ‘CREATE TABLE table_name’ application is as follow
Syntax:-
CREATE TABLE tableName{
field1 dataType,
field2 dataType,
}
Figure 1.1 – CREATE TABLE
Show Table
Once the table created successfully, possible to see the created table applying a SHOW TABLES; command line. This will show all the table in the selected database.
Syntax:-
SHOW TABLES;
Example : -
SHOW TABLES;
Figure 1.2 – SHOW TABLE
Drop Table
Dropping table is common operation that is going to be used to remove a table that is not necessary anymore in the database. Inorder to drop a table, the following command will be used.
Syntax: -
DROP TABLE tableName;
Example : -
DROP TABLE Persons;
DROP TABLE EquipmentInfo;
Figure 1.3 – DROP Table
Alter Table
Alter is one of the common command used in database table design. The main reason is that it let us to Drop Column, Add Column as well as modify the datatype oof the column/field. The application of ALTER command is as follow along with other key words like DROP, ADD, MODIFY.
Syntax: -
ALTER TABLE tableName ADD columnName dataType;
ALTER TABLE tableName DROP columnName dataType;
ALTER TABLE tableName MODIFY columnName dataType;
Figure 1.4 – ALTER Table
Show Column Table
Show Table column is used to display the structure of the table considering the Table field name and the datatype belong to the each Table field. The application of the command ‘SHOW COLUMN FROM TABLE_NAME’ alternatively it is possible to add also a database name ‘SHOW COLUMN FROM DATABASE.TABLE_NAME’
Syntax:-
SHOW COLUMN FROM table_name;
SHOW COLUMN FROM databaseName.table_name;
Example:-
SHOW COLUMNS FROM Persons;
SHOW COLUMNS FROM equipment.Persons;
Figure 1.5 – SHOW Table