^

indicates the beginning of a string

&

indicates the end of a string

|

is used to represent multiple search patterns (logical OR)

[]

is used to represent any of the listed characters

[abcdef]

[-]

is used to represent any characters from the range

[a-g]

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 '^ცი|ია$|ვა'

sub-query

used in

SELECT

clause. Calculates student's point in percent based on max points

SELECT
    id,
    first_name,
    points,
    (
        points * 100 /
        (
            SELECT
                MAX(points)
            FROM
                students
        )
    ) AS percent
FROM
    students

sub-query

used in

IF

conditional statement. Highlights the student with title

Highest

which has highest points

SELECT *, 
    IF(points>=90, IF(points=(SELECT MAX(points) FROM students), "Highest", "Brilliant"), "Lazy") AS class
FROM `students`
ORDER BY points DESC

sub-query

used in

FROM

clause. Select liked notes with likes counts and authors

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

sub-query

used in

WHERE

clause. Selects all students that have max points

SELECT
    id,
    first_name,
    points
FROM
    students
WHERE
    points = (
        SELECT
            MAX(points)
        FROM
            students
    )

sub-query

used in

INSERT

statement. Before inserting the record,

sub-query

gets

gender_id

based on the provided gender name

INSERT INTO students (
    first_name, 
    last_name, 
    points,
    gender_id
) 
VALUES (
    'ილია', 
    'დავითაშვილი', 
    '84',
    (SELECT id FROM genders WHERE name = 'Male')
)

sub-query

used in

WHERE

clause in

UPDATE

statement. Updates students table based on notes table column

notes.id

UPDATE 
  students 
SET 
  points = points + 1 
WHERE 
  student_id = (
    SELECT 
      student_id 
    FROM 
      notes 
    WHERE 
      notes.id = 1
  )

IF

has three parameters: 1 - Condition. 2 - Executes if the condition evaluates to

true

3 - Executes if the condition evaluates to

false

IF(condition , [expression when true], [expression when false]);

Returns

false

because the first parameter

0

evaluates to false

SELECT IF(0, 'true', 'false') AS Boolean

Simple

IF

statement in

SELECT

clause

SELECT *, 
    IF(points>=90, "Brilliant", "Lazy") AS class
FROM `students`
ORDER BY points DESC

Nested IF

conditional statement. Categorizes students based on their points

SELECT *, 
    IF(points>=90, "Brilliant", IF(points>=80, "Gold", IF(points>=60, "Silver", "Lazy"))) AS class
FROM `students`
ORDER BY points DESC

Sub-query inside

IF

. Highlights the student with highest points

SELECT *, 
    IF(points>=90, IF(points=(SELECT MAX(points) FROM students), "Highest", "Brilliant"), IF(points>=80, "Gold", IF(points>=60, "Silver", "Lazy"))) AS class
FROM `students`
ORDER BY points DESC

IF

statement in

WHERE

clause. Checks

mail

if it is not empty otherwise checks

mail2

SELECT * 
FROM `students` 
WHERE IF(LENGTH(mail), mail, mail2) LIKE '%gmail.com%'

When we need to join a table to itself, it is called

SELF JOIN

SELECT  
    s1.*,
    s2.first_name
FROM `students` s1
JOIN `students` s2 ON s2.santa_id = s1.id

This type of join is often used when we have tree structure in a table. Note:

alias

is mandatory in

SELF JOIN

We can give

alias

to tables or table columns. In this example table column

first_name

will be displayed as

student_name

SELECT 
    id,
    first_name AS student_name
FROM students

The keyword

AS

is optional, but it's better to use it, because the query is much more readable. The following query returns the same result

SELECT 
    id,
    first_name student_name
FROM students

We can use

alias

in

ORDER BY

clause to order the result

SELECT *, price * quantity AS total
FROM `orders` 
ORDER BY total DESC

If

alias

is given to table, column of the table can be accessed by the alias followed by

.

SELECT 
    s.id,
    s.first_name
FROM `students` s

In

SELF JOIN

alias is required, because MySQL needs to treat the tables as different tables

SELECT  
    s1.*,
    s2.first_name
FROM `students` s1
JOIN `students` s2 ON s2.santa_id = s1.id

quotes

or backticks can be used in alias. Possible options: single quote

'

, double quote

"

or backtick

 ` 

SELECT
    id,
    first_name AS 'student_name'
FROM 
    students

Advantage of using

quotes

is that we can use space separated names in alias

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

REPLACE

statement updates the listed columns if primary key (one of the listed columns) already exists. Otherwise inserts as a new record

REPLACE INTO students (id, first_name, points)
VALUES (41, 'ილიკო', 147)

Similar to the

REPLACE INTO

statement is

ON DUPLICATE KEY

. The only difference is that on duplicate key it updates only the listed columns but omitted values stays the same

INSERT INTO students (id, first_name, points) 
VALUES (41, 'გიორგი', 149) 

ON DUPLICATE KEY 

UPDATE first_name = 'გიორგი', points = 123

Note 1

: If primary key is not listed, it will insert the record, but if the primary key is one of the listed columns, it will update the specified row that matches the primary key.

Note 2

: If the primary key exists, all the other omitted columns will get the default values after updating the record.

Updates

paramVal

field if the row exists, otherwise inserts as a new row

INSERT INTO sysData (paramName, paramVal) 
VALUES ('payprocess', 1) 

ON DUPLICATE KEY 

UPDATE paramVal = 1 WHERE paramName = 'payprocess';

REPLACE INTO

is the similar to

INSERT INTO

statement. The difference is that it updates the record if it already exists. It's required one of the listed columns to be a private key of the table

REPLACE INTO students (id, first_name, points)
VALUES (41, 'ილიკო', 147)

Note 1

: If primary key is not listed, it will insert the record, but if the primary key is one of the listed columns, it will update the specified row that matches the primary key.

Note 2

: If the primary key exists, all the other omitted columns will get the default values after updating the record. Similar to the

REPLACE INTO

statement is

ON DUPLICATE KEY

. The only difference is that on duplicate key it updates only the listed columns but omitted values stays the same

INSERT INTO students (id, first_name, points) 
VALUES (41, 'გიორგი', 149) 

ON DUPLICATE KEY 

UPDATE first_name = 'გიორგი', points = 123

Categorizes students based on their points

SELECT *, 
    IF(points>=90, "Brilliant", IF(points>=80, "Gold", IF(points>=60, "Silver", "Lazy"))) AS class
FROM `students`
ORDER BY points DESC

The same result using

UNION

keyword

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

The same result using

CASE WHEN

conditional statement

SELECT *, 
    CASE 
      	WHEN points>90 THEN "Brilliant"
      	WHEN points>80 THEN "Gold"
      	WHEN points>60 THEN "Silver"
      	ELSE "Lazy"
    END as 'class'
FROM `students`
ORDER BY points DESC

Creates

backup.sql

backup file of

some_db

database using

some_user

MySQL user. The database is located at

34.mysql.servage.net

server.

mysqldump -h 34.mysql.servage.net -u some_user -p some_db > backup.sql

Creates

backup.sql

backup file of

some_db

database with the same MySQL user but without

-h

flag. If the database is located on the same server (localhost)

-h

flag is not necessary to specify

mysqldump -u some_user -p some_db > backup.sql

If we want to include all the routines (procedures, functions), then we need to use flag

-R

mysqldump -u some_user -p some_db -R > backup.sql

If we want to export only one or several tables, then we should list tables after database name

mysqldump -u root -p sdtokens sdt_prices another_table > sdt_prices.sql

After running the command, we will be required to enter password of the

some_user

mysql user. After that, MySQL will create

backup.sql

file on the same location