- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - TABLE Statement
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 |