MySQL Table Query

MySQL table query

A database stored a lot of data and divided them into different relations known as tables. Each database can contain more than one table. These tables are created by using SQL commands. The MySQL database is created using the specific language (SQL language) because it can be readable by MySQL easily. After creating the table, several types of operations like insertion, updation, deletion of data, and many other MySQL functions can be performed in the table.

Let's see all these operations in detail.

Create MySQL table

CREATE statement inMySQL is used to create tables in theselected database. A table can contain several rows and columns. Each column has its own data type. These columns can be divided into several keys: primary key, foreign key, candidate key, etc. All have some specific functions in the table.

We must have to select the database before creating a table where you have to create the table.

We can use the following syntax for selecting the database:

USE databasename;

Heredatabasename is the name of the database.

After selecting the database, we can create the table.

The syntax for creating a table in MySQL is given below:

CREATE TABLE tablename (column1 datatype1, column2 datatype2, column3 datatype3, …………..);

In the above syntax, tablename is the name of the table. Column1, column2, column 3, …  are the column you want to add to the table. The datatype in the syntax is the column datatype. The entirecolumns havetheir specific data type.

For example: Suppose you have a database SCHOOL. Here you want to create a table name as STUDENT having columns like STU_NAME, STU_ID, STU_ROLL_NO, STU_ADDRESS, STU_CONTACT, and STU_AGE.All these columns contain specific data types like STU_ID contain VARCHAR, STU_ROLL_NO contain INT, etc.

We can create this table by using the below statement in MySQL:

CREATE TABLE STUDENT (STU_ID VARCHAR(32),STU_NAME VARCHAR(32),STU_ROLL_NO INT(11),STU_CITY VARCHAR(32),STU_CONTACT INT(11),STU_AGE INT(11));

The table SCHOOL has been successfully created using the above syntax.

Show MySQL table

We know that the database contains a lot of tables. These tables can be displayed using a show statement. The syntax for showing the tables:

SHOW TABLES;

For example, you have a database SCHOOL. If we want to know that how many tables are available in this database, you can execute the below command:

SHOW TABLES;

Let’s see the result in MySQL.

There is only one table available in this database, so only one table is shown in the list.

Rename MySQL table

Suppose you want to change the table's name or, by mistake, you had given the wrong name to the table or some spelling mistake.In that case, we need to change the name or rename the table. To do this, we can use the syntax as follows:

ALTER TABLE tablename RENAME TO newtablename;

Here tablename is the name of the existing table, and newtablename is the name you want to give to the table.

For example, You have a table STUDENT in the database SCHOOL. You want to change the name of the table from STUDENT to CLASS. Change the name of the table with the help of the given syntax.

ALTER TABLE STUDENT RENAME TO CLASS;

Here is the result:

Here we see that the name of the table has been changed to CLASS.

Insert MySQL table

After creating a table, we need to insert values into this table. To insert the value (data) in the table, we use the insert statement. The syntax for inserting data in the table is given below:

INSERT INTO table_name VALUES (value1, value2, value3,………..);

In the syntax, the Valuesclause indicates the data that you want to insert in the table. The value that we are inserting will be in the same format as the columns are arranged in the table. Suppose column STU_ID is present in the first column of the table, then value1 will contain the ID value only according to the datatype.

For example, We have a table CLASS and have to insert values in the table.

Command to insert value in the table:

INSERT INTO TABLE VALUES (“A1”,”RAM”, 098765,”JAIPUR”, 0543-5676, 20);

Use this command in MySQL and see the result.

The value has been inserted successfully.

Alter MySQL table

We have a database, and it contains several tables. If you want to add some extra columns in the given table or delete any table, we use the alter statement.This statement is also used to change the data type of the selected column.We use ALTER statement to add or delete columns and modify the existing column's data type. There is a different syntax for every condition. Let's discuss it one by one.

Add column in the table.

Syntax to add column in the table:

ALTER TABLE table_name ADD columnnamedatatype;

Here table_name is the table's name in which you want to add the column, columnname is the name of the column that you want to add to the table, and datatypeis the column's data type.

For example, in the database SCHOOL, we have a table STUDENT having columns STU_ID, STU_NAME, STU_ROLL_NO, STU_CITY, STU_CONTACT, and STU_AGE. We are going to add an extra column name as STU_DATE_OF_BIRTH.

We can do this as follows:

ALTER TABLE SCHOOL ADD STU_DATE_OF_BIRTH INT (11);

Before adding the column, the table looks like below:

After adding the column (STU_DATE_OF_BIRTH), thetable looks like below.

We can see that the column has been added successfully.

Delete column:

Syntax to delete a column from the table:

ALTER TABLE table_name DROP COLUMNcolumnname;

Here table_name is the table's name, and columnname is the name of the column that you want to delete.

For example, we have a table STUDENT with many columns, and we want to delete the STU_DATE_OF_BIRTH column from the table STUDENT. Let's try this in MySQL.

Command to delete a column from the table:

ALTER TABLE STUDENT DROP COLUMN STU_DATE_OF_BIRTH;

In MySQL, before deleting the column.

After deleting the column, our table is shown below. Here, we can see thecolumn has been deleted successfully.

Modify the data type of the column.

Syntax to modify the data type of the column.

ALTER TABLE table_name MODIFYcolumnnamedatatype;

Here table_name is the name of the table and columnname is the column's name, and the datatype is the data type of the column.

For example, we want to change the data type of column STU_ROLL_NO from INT to VARCHAR. We can use the below command to change the data type:

ALTER TABLE STUDENT MODIFY STU_ROLL_NO VARCHAR (32);

In MySQL, before changing the data type.

After changing the data type, we can see the data type of the column STU_ROLL_NO changed successfully.

Copy MySQL table

Sometimes, we need to copy the data of one table to another table. For that, we need to use the SELECT syntax. This syntax will copy all the data of the table to another table. It is noted that the column name and data typeof both the table should be the same.

Syntax to copy the data from one table to another:

SELECT * INTO new_table_name FROMold_table_name;

                                Or

SELECT * INTO new_table_name FROM old_table_name WHERE condition;

For example, we have a table STUDENT and another table CLASS. We have to copy the data of table STUDENT to the table CLASS.

Command to copy the data.

SELECT * INTO CLASS FROM STUDENT;

Truncate MySQL table

Suppose you want to delete all the table rows, but the tablestructure, columns, and data type should remain there. For this, we use a truncate statement in SQL. Truncate statement used to delete all the rows of the selected table.

Syntax to delete all the data from the table:

TRUNCATE TABLE table_name;

Here table_name is the name of the table.

For example, we have a table STUDENT. If we want to delete all the data present in the table, we use the syntax as below:

TRUNCATE TABLE STUDENT:

The below image shows that after using the truncate statement, all the row gets deleted, and the output shown is an empty set.

Drop MySQL table

Drop statement deletes the table from the database. This statement deletes all the data along with the table structure.

The syntax used to delete the table:

DROP TABLE table_name;

Here table_name is the name of the table.

For example, we have a database SCHOOL that contains two tables, student and class. Let’s delete table STUDENT from the database in MySQL using this syntax.

DROP TABLE STUDENT;

In the below image, before using the drop command, we had two tables. But after using the command,the table student has been deleted.

Note: We cannot get the table again, so be careful while using this command.