MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table
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.
MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table