MySQL - TABLE Statement

Table of content

MySQL TABLE Statement
With the ORDER BY clause
With the LIMIT clause
With the UNION clause
With the SELECT statement

Quiz

MySQL TABLE Statement

The TABLE statement in MYSQL is used to retrieve the rows and columns of a specified table. It is similar to the statement SELECT * FROM... statement. But, unlike SELECT statement if you try to retrieve the contents of a table using the TABLE statement, you cannot filter the rows of a table (using WHERE clause). The TABLE Statement always returns the complete rows of the specified table.

Syntax

Following is the syntax of the TABLE statement −

TABLE table_name;

Example

Assume we have created a table named EMP using the CREATE statement as shown below −

CREATE TABLE EMP (
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
);

Now, let us insert values in the above created table using the INSERT statement as shown below −

INSERT INTO EMP VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Alexandra', 'Botez', 26, 'F', 2000);

Following query retrieves the contents of the above created table using the TABLE statement −

TABLE EMP;

Output

Following is the output of the above query −

FIRST_NAME	LAST_NAME	AGE	SEX	INCOME
Krishna	Sharma	19	M	2000
Raj	Kandukuri	20	M	2000
Ramya	Ramapriya	25	F	5000
Alexandra	Botez	26	F	2000

With the ORDER BY clause

The ORDER BY clause is used to arrange the records of a table based on the specified column we can use this clause along with the TABLE statement as shown below −

TABLE table_name ORDER BY column_name;

Where table_name is the name of the table and column_name is the name of the column based on which you need to arrange the specified table.

Example

Following query arranges and retrieves the contents of the EMP table based on the FIRST_NAME column −

TABLE EMP ORDER BY FIRST_NAME;

Output

The above query will produce the following output −

FIRST_NAME	LAST_NAME	AGE	SEX	INCOME
Alexandra	Botez	26	F	2000
Krishna	Sharma	19	M	2000
Raj	Kandukuri	20	M	7000
Ramya	Ramapriya	25	F	5000

With the LIMIT clause

While fetching records if you want to limit them by a particular number, you can do so, using the LIMIT clause of MYSQL. You can use this clause too along with thw TABLE statement as shown below −

TABLE table_name LIMIT lt_number OFFSET off_number;

Where, table_name is the name of the table, lt_number is the number of records to be retrieved and off_number is the offset number.

If you need to limit the records starting from nth record (not 1st), you can do so, using OFFSET along with LIMIT.

Example

Following query arranges the records of the EMP table based on the INCOME column and retrieves the first two records −

TABLE EMP ORDER BY INCOME LIMIT 2;

Output

Following is the output of the above query −

FIRST_NAME	LAST_NAME	AGE	SEX	INCOME
Krishna	Sharma	19	M	2000
Alexandra	Botez	26	F	2000

With the UNION clause

The MySQL UNION clause is used to combine the results of two or more SELECT/TABLE statements without returning any duplicate rows.

To use this UNION clause, each SELECT statement must have

The same number of columns selected.
The same number of column expressions.
The same data type and,
Have them in the same order

Following is the syntax to use the UNION clause (with the TABLE statement) −

TABLE table_name1 UNION TABLE table_name2;

Example

Assume we have created a table named Student using the CREATE statement shown below −

CREATE TABLE Student (Name VARCHAR(20), age INT);

Now, let us insert some records into it −

INSERT INTO Student VALUES 
('Krishna', 22),
('Raju', 20),
('Rahman', 21);

You can verify the contents of the student table as shown below −

TABLE Student;

Output

Following is the output of the above mysql query −

Name	age
Krishna	22
Raju	20
Rahman	21

Suppose we have another table with same number of rows along (with the data types) −

CREATE TABLE Staff (Name VARCHAR(20), age INT);

Now, let's try to insert some records into the Staff table −

INSERT INTO Staff VALUES 
('Amit', 35),
('Nanda', 33),
('Swathi', 39);

You can verify the contents of the student table as shown below −

SELECT * FROM Staff;

Output

The above query will generate the following output −

Name	age
Amit	35
Nanda	33
Swathi	39

Following query combines the above two tables using the JOIN clause −

TABLE Student UNION TABLE Staff;

Output

Following is the output of the above query −

Name	age
Krishna	22
Raju	20
Rahman	21
Amit	35
Nanda	33
Swathi	39

With the SELECT statement

You can also insert the contents of one table to another using the INSERT statement along with TABLE. Following is the syntax to do so −

INSERT INTO table1 TABLE table2;

Example

Assume we have created a table with name student and inserted 4 records into it as shown below −

Create table Student(Name Varchar(35), age INT, Score INT);

Now, let's try to insert some records into the Student table −

INSERT INTO student values 
('Jeevan', 22, 8),
('Raghav', 26, -3),
('Khaleel', 21, -9),
('Deva', 30, 9);

Suppose we have another table with name columns and types created as −

Create table Data(Name Varchar(35), age INT, Score INT);

Following query inserts the contents of the Student table into the table Data −

INSERT INTO Data TABLE Student;

Verification

If you verify the contents of the Data table using the SELECT statement you can observe the inserted data as −

SELECT * FROM data;

Output

The above query produces the following output −

Name	age	Score
Jeevan	22	8
Raghav	26	-3
Khaleel	21	-9
Deva	30	9

Print Page