We can run queries (that are inside

queries

file) from Linux command line

mysql -u test_user -p test_db < /var/www/queries.sql

The queries inside the file will be run into

test_db

using

test_user

user. Note: The above command can be used to restore database, or add some tables, or insert some rows.

Changes password for MySQL

root

user to

some password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'some password';

We run the command after installing

mysql-server

to set password for the

root

user

sudo mysql_secure_installation

After the command is executed, we enter the password for the MySQL root user

Installs

Git

on the server

sudo apt install git

Installs Nginx server

sudo apt install nginx

Installs MySQL Server

sudo apt install mysql-server

Installs php-fpm and php-mysql

sudo apt install php-fpm php-mysql

Installs additional necessary packages for PHP

sudo apt install php-pdo php-common php-dom php-mbstring php-gd php-json php-soap php-xml php-cli php-ldap php-zip

Selects all the fields from

students

table (

*

means - all fields)

SELECT * 
FROM students

Selects only

first_name

and

last_name

from

students

table (fields are separated by comma

,

)

SELECT first_name, last_name 
FROM students

WHERE

clause in

SELECT

statement

SELECT first_name, last_name 
FROM students
WHERE id = 5

WHERE

clause (combining several conditions with logical AND)

SELECT *
FROM students
WHERE points > 75 AND last_name = 'გელაშვილი'

WHERE

clause (combining several conditions with logical OR)

SELECT *
FROM students
WHERE last_name = 'გელაშვილი' OR last_name = 'გოგუაძე'

Logical OR with logical AND

SELECT *
FROM students
WHERE points > 75 AND (last_name = 'გელაშვილი' OR last_name = 'გოგუაძე')

ORDER BY

clause in

SELECT

statement

SELECT first_name, last_name 
FROM students
ORDER BY id DESC

ORDER BY

last_name

ascending

SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC

ASC

is optional because it's default value

ORDER BY

with several fields

SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC, first_name ASC

ASC

SELECT

with some primitive values

SELECT first_name, last_name, 20 AS age 
FROM students

SELECT

with only primitive values

SELECT 20, 40 AS age 
FROM students

Math operations in

SELECT

statement

SELECT 
    first_name, 
    last_name, 
    points * 1.1 /* + - * / % */ AS points
FROM students

Math operations in

WHERE

clause

SELECT *
FROM students
WHERE points * 1.5 >= 80

Order of the Math operations matters

SELECT 
    first_name, 
    last_name, 
    (points + 10) * 2 AS points
FROM students

Advantages of quotes in alias

SELECT 
    first_name, 
    last_name, 
    (points + 10) * 2 AS 'points calculated'
FROM students

DISTINCT

keyword in SELECT statement

SELECT DISTINCT
    first_name, 
    last_name
FROM students

GROUP BY

clause with several fields (results of the two queries are identical)

SELECT 
    first_name, 
    last_name
FROM students
GROUP BY first_name, last_name

GROUP BY

clause with one field and

COUNT()

function

SELECT 
    last_name,
    COUNT(last_name)
FROM students
GROUP BY last_name

GROUP BY

and

ORDER BY

clauses in one select

SELECT 
    last_name,
    COUNT(last_name) last_name_counter
FROM students
GROUP BY last_name
ORDER BY last_name_counter DESC

LIMIT

clause

SELECT 
    last_name,
    COUNT(last_name) last_name_counter
FROM students
GROUP BY last_name
ORDER BY last_name_counter DESC
LIMIT 2

LIMIT

clause for pagination

SELECT 
    first_name
    last_name
FROM students
ORDER BY points DESC
LIMIT 4, 2

Student with minimum points

SELECT *
FROM students
ORDER BY points
LIMIT 1

Student with minimum points using

MIN()

function

SELECT *
FROM students
WHERE points = (SELECT MIN(points) FROM students)

Student with maximum points

SELECT *
FROM students
ORDER BY points DESC
LIMIT 1

Student with maximum points using

MAX()

function

SELECT *
FROM students
WHERE points = (SELECT MAX(points) FROM students)

Average points grouped by lastname

SELECT 
    last_name,
    AVG(points) average_points
FROM students
GROUP BY last_name
ORDER BY average_points DESC

Select all students with points greater than 70 (comparison operators:

>

>=

<

<=

=

!=

<>

)

SELECT *
FROM students
WHERE points > 70

Select all students with points less than or equal to 70 (using NOT logical operator)

SELECT *
FROM students
WHERE NOT points > 70

Select all students with points between 80 and 90

SELECT *
FROM students
WHERE points >= 80
    AND points <= 90

Select all students with points between 80 and 90 (using BETWEEN AND operator)

SELECT *
FROM students
WHERE points BETWEEN 80 AND 90

Select all students with points more than average

SELECT *
FROM students
WHERE points > (SELECT AVG(points) FROM students)

Select all students where lastname is either 'გელაშვილი' or 'გოგუაძე' with

IN

operator

SELECT *
FROM students
WHERE last_name IN ('გელაშვილი', 'გოგუაძე')

Select all students which lastname is not any of the listed lastnames ('გელაშვილი', 'გოგუაძე') with

NOT IN

operator

SELECT *
FROM students
WHERE last_name NOT IN ('გელაშვილი', 'გოგუაძე')

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

Select all students where

last_name

contains

უა

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP 'უა'

Select all students where

last_name

starts with

გე

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP '^გე'

Select all students where

last_name

ends with

უა

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP 'უა$'

Select all students where

last_name

contains

უა

or

ია

SELECT *
FROM students
WHERE last_name REGEXP 'უა|ია'

Select all students where

last_name

contains

უა

or

ია

using square brackets

SELECT *
FROM students
WHERE last_name REGEXP '[იუ]ა'

Select all students where

last_name

ends with any one letter from the range

[ა-უ]

followed by

ა

using square brackets with range

SELECT *
FROM students
WHERE last_name REGEXP '[ა-უ]ა$'

Select all students where

last_name

contains

ვა

or ends with

ია

or starts with

ცი

SELECT *
FROM students
WHERE last_name REGEXP '^ცი|ია$|ვა'

Select all students which has no mail with

IS NULL

operator

SELECT *
FROM students
WHERE mail IS NULL 
    OR mail = ''

Select all students which has no mail using

IFNULL()

function

SELECT *
FROM students
WHERE IFNULL(mail, '') = ''

Select all students which has mail using

IS NOT NULL

operator

SELECT *
FROM students
WHERE mail IS NOT NULL
    AND mail != ''

Select all students which has mail using

NULLIF()

function

SELECT *
FROM students
WHERE NULLIF(mail, '') IS NOT NULL

Select notes with author names (using INNER JOIN)

SELECT students.first_name, notes.*
FROM `students` 
JOIN notes ON notes.student_id = students.id

Select notes with author names (from students using LEFT JOIN)

SELECT students.first_name, notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id

Select notes with author names (from students using RIGHT JOIN)

SELECT 
    students.first_name, 
    notes.*
FROM `notes` 
RIGHT JOIN students ON notes.student_id = students.id

Select notes with author names (from students using LEFT JOIN)

SELECT students.first_name, notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
WHERE notes.id > 0

Select notes with author names (from notes)

SELECT students.first_name, notes.*
FROM `notes` 
LEFT JOIN students ON notes.student_id = students.id
WHERE notes.id > 0

Select only students with notes (using INNER JOIN)

SELECT students.*, COUNT(students.id) notes_count 
FROM `students` 
JOIN notes ON notes.student_id = students.id
GROUP BY students.id
ORDER BY notes_count DESC

Selects only students with notes (using LEFT JOIN and HAVING)

SELECT students.*, COUNT(notes.id) notes_count 
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
GROUP BY students.id
HAVING notes_count > 0
ORDER BY notes_count DESC
LIMIT 2

Select popular notes with its authors and likes counts

SELECT 
    students.first_name, 
    notes.id AS note_id, 
    notes.note, 
    COUNT(note_likes.id) AS likes_count
FROM `notes` 
JOIN students ON notes.student_id = students.id
LEFT JOIN note_likes ON note_likes.note_id = notes.id
GROUP BY notes.id
ORDER BY likes_count DESC

Select only liked notes

SELECT 
    students.first_name, 
    notes.id AS note_id, 
    notes.note, 
    COUNT(note_likes.id) AS likes_count
FROM `notes` 
JOIN students ON notes.student_id = students.id
JOIN note_likes ON note_likes.note_id = notes.id
GROUP BY notes.id
ORDER BY likes_count DESC

Select only liked notes (without JOIN keywords)

SELECT 
    students.first_name, 
    notes.id AS note_id, 
    notes.note, 
    COUNT(note_likes.id) AS likes_count
FROM `notes`, students, note_likes
WHERE notes.student_id = students.id
    AND note_likes.note_id = notes.id
GROUP BY notes.id
ORDER BY likes_count DESC

Select liked notes with likes counts and authors (using sub-query)

SELECT 
    students.first_name, 
    note_id, 
    notes.note,
    liked_notes.likes_count
FROM (  
    	SELECT 
            note_likes.note_id AS note_id,
            COUNT(note_likes.id) AS likes_count
        FROM note_likes
        GROUP BY note_likes.note_id
	) AS `liked_notes`
JOIN notes ON liked_notes.note_id = notes.id
JOIN students ON notes.student_id = students.id
ORDER BY likes_count DESC