×
Clear all filters including search bar
Valeri Tandilashvili's Personal Professional Blog
Results: 1022
NATURAL JOIN 
NATURAL
join tries to find the fields with the same name and joins them
SELECT 
    students.*,
    notes.*
FROM `notes`
NATURAL JOIN `students`
Note: using 
NATURAL
join is dangerous, because we don't know explicitly which fields will be joint. 
SELECT students.*, notes.* 
FROM `notes` 
JOIN `students`
	ON notes.student_id = students.student_id
    AND notes.id = students.id
Returns the same result as the above query
USING
If the fields names that we want to join are the same, we can use 
USING
keyword
SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT JOIN notes USING(student_id)
The alternative of the above query using 
ON
clause:
SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.student_id
UNION
There is no such 
FULL JOIN
in MySQL but we can get the same result using 
UNION
SELECT 
    students.*, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
UNION
SELECT 
    students.*, 
    notes.*
FROM `students` 
RIGHT JOIN notes ON notes.student_id = students.id
As default, 
UNION
removes duplicate rows. If we want to get all the rows including duplicates, we can use 
UNION ALL
SELECT 
    students.*, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
UNION ALL
SELECT 
    students.*, 
    notes.*
FROM `students` 
RIGHT JOIN notes ON notes.student_id = students.id
Divides all the students into different classes
SELECT *, 'Brilliant' AS class
FROM `students`
WHERE points >= 90
UNION
SELECT *, 'Gold'
FROM `students`
WHERE points >= 80 
    AND points < 90
UNION
SELECT *, 'Silver'
FROM `students`
WHERE points >= 60 
    AND points < 80
UNION
SELECT *, 'Lazy'
FROM `students`
WHERE points < 60
ORDER BY points DESC
OUTER JOINS
There are two types of 
OUTER
JOINS: 1. LEFT JOIN - same as 
LEFT OUTER JOIN
2. RIGHT JOIN - same as 
RIGHT OUTER JOIN
LEFT JOIN example
SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
These two joins are the same, they produce the same result
LEFT OUTER JOIN
example:
SELECT 
    students.first_name, 
    notes.*
FROM `students` 
LEFT OUTER JOIN notes ON notes.student_id = students.id
Note: The same goes for 
RIGHT JOIN
and 
RIGHT OUTER JOIN
, they produce the same result
LIMIT 
LIMIT
clause format: Starts from 
X
rows, Selects 
Y
rows
-- LIMIT Y
-- LIMIT X Y
LIMIT
clause is used to limit the number of rows returned as a result
SELECT
    id,
    first_name
FROM students
LIMIT 2
LIMIT
clause for pagination. If two arguments are passed to LIMIT clause, the first one 
offset
, which indicates how many rows we want to skip. In this example, we want to skip the first 10 records and get the following 5 records. In this case we want to get 3'th page
SELECT 
    first_name
    last_name
FROM students
ORDER BY points DESC
LIMIT 10, 5
Selects the student with the highest points
SELECT *
FROM students
ORDER BY points DESC
LIMIT 1
ORDER BY 
ASC
is optional because it's default value 
ORDER BY
clause in 
SELECT
statement
SELECT first_name, last_name 
FROM students
ORDER BY id DESC
Orders the result by 
last_name
with 
ascending
order
SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC
ORDER BY
with several fields
SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC, first_name ASC
Grouped by 
last_name
and Ordered by 
Average points
SELECT 
    last_name,
    AVG(points) average_points
FROM students
GROUP BY last_name
ORDER BY average_points DESC
Mathematical expression used to order the result
SELECT *
FROM `orders` 
ORDER BY price * quantity DESC
Order the result using 
alias
SELECT *, price * quantity AS total
FROM `orders` 
ORDER BY total DESC
Orders the result by first name with 
ASC
and by id with 
DESC
using column numbers
SELECT
    id,
    first_name
FROM `students`
ORDER BY 2, 1 DESC
LIKE 
%
- represents any number of characters 
_
- represents one single character Select all students which lastname contains 'უა' with 
LIKE
operator
SELECT *
FROM students
WHERE last_name LIKE '%უა%'
Select all students which 
lastname
ends with 'უა' using 
LIKE
operator
SELECT *
FROM students
WHERE last_name LIKE '%უა'
Select all students which 
lastname
does not contain 'უა' with 
NOT LIKE
operator
SELECT *
FROM students
WHERE last_name NOT LIKE '%უა%'
Select all students where 
lastname
starts with any two character followed with 
უა
and we don't care what the other following symbols are (with 
_
)
SELECT *
FROM students
WHERE last_name LIKE '__უა%'
Select all students where 
lastname
is exactly 5 characters long and the second symbol is 
უ
SELECT *
FROM students
WHERE last_name LIKE '_უ___'
Select all students where 
lastname
is exactly 5 characters, starts with 
a
and ends with 
y
and has exacly any three letters between them
SELECT *
FROM students
WHERE last_name LIKE 'a___y'
Select all students where 
lastname
ends with either 
ძე
or 
შვილი
SELECT *
FROM students
WHERE last_name LIKE '%ძე' OR last_name LIKE '%შვილი'
DELETE statement
Deletes all the records FROM 
notes
table. This is too dangerous command because there is no undo when it comes to databases. If we update or delete something, the original values are gone.
DELETE FROM notes;
Deletes the row with the specified private key - 
id
. We should always use 
WHERE
clause, not to delete everything from the database
DELETE FROM `requests` WHERE `id` = '1548630';
Deletes the student with the specified first name and last name
DELETE FROM `requests` WHERE (first_name = 'John' AND last_name = 'Doe');
MySQL system users
Shows system users
SELECT user, authentication_string, plugin, host FROM mysql.user;
disable safe update 
# Disables safe update temporarily 
SET SQL_SAFE_UPDATES = 0;

# Updates multiple rows at once
UPDATE notes SET priority = 100 WHERE priority = 0;

# Enables safe update
SET SQL_SAFE_UPDATES = 1;
Results: 1022