MySQL ISNULL
IS NULL is used in the WHERE clause to check if a value is NULL or not.
IS NULL in action.
MySQL Limit
The LIMIT clause, as you might guess, is used to limit the number of rows returned in the result set. It has two parameters or arguments. The first parameter is offset which tells mysql how many rows to skip before starting to retrieve. The second parameter is count which tells mysql how many rows to retrieve.
SELECT
column_name1,column_name2,...
FROM
table_name
LIMIT offset , count;
Here is an example.
As you can see here, the offset is 2 which means mysql will skip row one and two and start retrieving from row number 3. The count is 5 which means it will retrieve 5 rows. As a result, you see rows [3,4,5,6,and 7].
Offset is optional
The offset argument is optional. If you don’t use an offset then offset will be default to zero.
SELECT
column_name1,column_name2,...
FROM
table_name
LIMIT count;
The query above is the same as the query below.
SELECT
column_name1,column_name2,...
FROM
table_name
LIMIT 0, count;
Here is an example
LIMIT without the offset argument, just the count argument.
MySQL Like
The LIKE operator is used to check if a string contains another string.
These two wildcards are used with LIKE:
1. “%” – check for a match of one or more characters
2. “_” – check for a match of a single character
If you are searching for something that ends with a certain pattern of string then put % in the front.
If you are searching for something that starts with a certain pattern of string then put the % at the end.
If you are searching for something that contains a certain pattern of string no matter where it is in the string then put the % in the front and also at the end.
Here is an example of % where we are looking for vendor’s names that end with bay.
Here we are looking for vendor’s names that contain the letter a.
LIKE with _ wildcard
Here we are looking for vendor names that start with any character and end with bay.
Here we are looking for vendor names that start with E, followed by any character, followed by a, followed by any character.
MySQL Where
The WHERE clause is used in a SELECT statement with conditions to filter the result set. It is also used in the UPDATE and DELETE statement.
SELECT
column_name1,column_name2,...
FROM
table_name
WHERE
conditions;
There can be one or many conditions and a condition must be evaluated to true or false.
A row must satisfy all conditions to be included in the result set.
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.