Select Query

MySQL provides an select query to retrieve the values from the table inserted by the insert statement is used to put values into the table

The select query is one of the most popular and mostly used queries. Syntax of the select query is as below.

SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;

what_to_select indicates what and which column needs to be selected from the table, it can be a single column, multiple columns or all columns of single or multiple tables

which_table indicates the name of the table or multiple tables from which data will be retrieved.

The WHERE clause is optional. If it is present, conditions_to_satisfy specifies one or more conditions that rows must satisfy to qualify for retrieval.

select query supports many more clauses

<pre>
<code class="language-sql">SELECT [ ALL | DISTINCT ] { what_to_select }
[ INTO new_table ]
FROM { table_source }
[ WHERE search_condition ]
[ GROUP BY group_expression ]
[ HAVING search_condition ]
[ ORDER BY order_clause ]</code></pre>

Let's look at all the select options with example.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), apple_price DECIMAL(10,2), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, apple_price, available, available_date ) VALUES (1, "Macintosh", 1.1, 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, apple_price, available, available_date ) VALUES (2,"Fuji", 2.2, 'Y', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, apple_price, available, available_date ) VALUES (3,"Gala",  3.3, 'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, apple_price, available, available_date ) VALUES (4,"Jonagold", 4.4, 'N', '2022-04-4');
INSERT INTO apple (apple_id, apple_name, apple_price, available, available_date ) VALUES (5,"GrannySmith", 5.5, 'Y', '2022-05-5');
INSERT INTO apple (apple_id, apple_name, apple_price, available, available_date ) VALUES (6,"PinkLady", 6.6, 'N', '2022-06-6');

select all data from the table

SELECT * FROM apple;

* is shorthand for select all columns of the Table

select data of a single column from the table

SELECT apple_name FROM apple;

select query with an arithmetic expression

select apple_price * 2 from apple;

select the total number of rows in the table.

SELECT COUNT(*) from apple;

select length of String of column.

SELECT LENGTH(apple_name) from apple;

select data of multiple columns from the table

SELECT apple_id, apple_name FROM apple;

select all the values of the table based on the condition

SELECT * FROM apple WHERE apple_id = 1;

select query with subquery

SELECT * from apple where apple_id = (select MAX(apple_id) from apple);

* is shorthand for select all columns of the Table

follow us on