MySQL Select

The SELECT statement is used to read or retrieve rows(data) from one or more database tables or views. You can retrieve one, two, or three, or all columns depending on what you need. Each row you retrieve will have the same number of columns. Here is an example of a SELECT statement.

Select Official Syntax

SELECT 
    column_name1, column_name2, ...
FROM
    table_name1
[INNER | LEFT |RIGHT] JOIN table_name2 ON conditions
[INNER | LEFT |RIGHT] JOIN table_name3 ON conditions
WHERE
    conditions
GROUP BY column_name1, column_name2, ...
HAVING group_conditions
ORDER BY column_name1 [ASC|DESC], column_name2 [ASC|DESC],...
LIMIT offset, length;

Let’s break down these clauses that the SELECT statement has.
* SELECT(required) – select columns you want to retrieve.
* FROM(required) – this is the tables or views you are retrieving the data from.
* WHERE – filters what rows to return in the result set.
* GROUP BY – group the result set into groups and apply aggregate functions into each group.
* HAVING – filter groups defined or created by the GROUP BY clause.
* ORDER BY – sort the result set by columns.
* LIMIT – determine the number of rows to retrieve.

Why SELECT * is bad
Note that you can use SELECT * to select all columns in a table but I don’t recommend this approach for these reasons.
1. It will retrieve all columns but you might not need all of them. This is result in a slow query.
2. If you are mapping the result set to an object like a User object then you don’t know which column is what.

SELECT DISTINCT

If you want to remove duplicate rows in the result set, use DISTINCT after SELECT.

SELECT DISTINCT
    column_name1,column_name2,... 
FROM
    table_name
WHERE
    conditions;
SELECT  
	first_name 
FROM 
	customer
ORDER BY 
	first_name;

As you can see, using select without DISTINCT you have duplicate first names.

SELECT  DISTINCT
	first_name, last_name
FROM 
	customer
ORDER BY 
	first_name;

Don’t get confused about having duplicate first names. And yes you are seeing the right result set for the query above. I wanted to show how DISTINCT works with multiple columns. If multiple rows are selected with distinct then uniqueness is checked by row and not by just one column.

Locking rows with SELECT

If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried.InnoDB supports two types of  locking reads  that offer extra safety.

LOCK IN SHARE MODE

SELECT ... LOCK IN SHARE MODE

Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view.

All locks set by LOCK IN SHARE MODE and FOR UPDATE queries are released when the transaction is committed or rolled back.

Locking reads are only possible when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0.

A locking read clause in an outer statement does not lock the rows of a table in a nested subquery unless a locking read clause is also specified in the subquery.

SELECT * FROM customer WHERE id = 2 LOCK IN SHARE MODE;

After the LOCK IN SHARE MODE query returns the customer, you can safely add the child record to the CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the customer table waits until you are finished, that is, until the data in all tables is in a consistent state.

FOR UPDATE

SELECT ... FOR UPDATE
SELECT click_count FROM click_counter WHERE user_id = 4 FOR UPDATE;
UPDATE click_count SET click_count = click_count + 1 WHERE user_id = 4 ;

A  SELECT ... FOR UPDATE  reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL  UPDATE  would set on the rows.

 




Subscribe To Our Newsletter
You will receive our latest post and tutorial.
Thank you for subscribing!

required
required


Leave a Reply

Your email address will not be published.