{restrict}

Data manipulating with Select

Select:

Once data has been inserted or loaded into a table, the next most logical operation would be to retrieve the data inserted. The SELECT SQL command is used to achieve this. The SELECT command retrieves all rows and all columns from one or more tables.

Retrieving All Rows and All Columns

In order to view global table data, you have to mention all the Column Names of the table after the Select command.
Syntax:
<System prompt>SELECT <ColumnName1>, <ColumnName'N'> FROM <TableName>;

Example:
mysql>SELECT CustomerID, Name, CompanyName, Address, Telephone FROM ClientMaster;

Here, ColumnName1 to ColumnName'N' represents some or all table column names.

If a table has multiple columns, making mistakes when typing in column names is quite possible. To overcome this issue MySQL allows the use of theMeta character asterisk ( * ). This is expanded by MySQL to meanall rows and all columnsin a table.

Syntax:
<System prompt>SELECT * FROM <TableName>;

Example:
mysql>SELECT * FROM ClientMaster;

Diagram 1: Retrieving all data from the table

The MySQL database engine parses and compiles the SQL query, executes it and retrieves data from all rows and columns from the table and displays this in the VDU.

Retrieving Filtered Table Data

While viewing data from a table it is rare that all the data from the table will be required each time. Hence, SQL provides methods for filtering table data.

The ways of filtering table data are:

Selected columns and all rows

Selected rows and all columns

Selected columns and selected rows

Selected Columns and All Rows

The retrieval of specific columns from a table can be done as shown below.

Syntax:
SELECT <ColumnName1>, <ColumnName2> FROM <TableName>;

Example:
SELECT Name, Address FROM ClientMaster;

Diagram 2: Retrieving some column and all rows data from the table

Selected Rows and All Columns

If a particular customer's information must be retrieved from a table, its retrieval must be based on a specific condition.

Till now the SELECT statement displayed all rows in a table. This is because there was no condition specified that informed MySQL about how to choose a specific row (or a set of rows) from the table. To select a specific row (or a set of rows) MySQL uses a WHERE Clause in the SQL query.

When a WHERE Clause is specified in an SQL query, MySQL engine compares each record in the table with the condition specified and displays only those records that satisfy the condition.

Syntax:
SELECT * FROM <TableName> WHERE <Condition>;

Here, <Condition> is always quantified as <ColumnName = ‘Value’>
NOTE: If the value attribute is a string it must be passed within single or double quotes.

Example:
SELECT * FROM ClientMaster WHERE CompanyName = ‘AMCS’;

Diagram 3: Retrieving all columns and some rows data from the table