Shows system users

SELECT user, authentication_string, plugin, host FROM mysql.user;

following code will return users with their latest published post

$latestPosts = DB::table('posts')
                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->where('is_published', true)
                   ->groupBy('user_id');

$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })->get();

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');

%

- 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 '%შვილი'

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

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

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

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

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

CROSS JOIN

is the same as

INNER

or

implicit

joins. Without conditions, both result in Cartesian product:

SELECT 
    students.*,
    notes.*
FROM `notes`
CROSS JOIN `students` 

JOIN

example:

SELECT 
    students.*,
    notes.*
FROM `notes`
JOIN `students` 

JOIN

is the short form of

INNER JOIN

SELECT 
    students.*,
    notes.*
FROM `notes`
INNER JOIN `students` 

Implicit

join example

SELECT 
    students.*,
    notes.*
FROM `notes`, `students` 

Both the queries return the exact same result