Select students with

gmail

account. Using

IF

statement:

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

Using

OR

logical operator:

SELECT *
FROM  `students` 
WHERE 
    (`mail` LIKE '%gmail.com%' AND LENGTH(mail))
    OR 
    (mail2 LIKE '%gmail.com%') 
ORDER BY points

Using

CASE

SELECT * 
FROM `students` 
WHERE 
    (CASE
        WHEN LENGTH(mail) THEN mail
        ELSE mail2
    END) LIKE '%gmail.com%'
ORDER BY points

Grants all privileges to

some_user

user on all

some_db

database tables

GRANT ALL PRIVILEGES ON some_db.* TO 'some_user'@'localhost';

Activates MySQL console

mysql -u some_user -p

After running the command, we will be required to enter

password

of the mysql user

some_user

After that, MySQL

console

will be activated where we can run MySQL queries

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

Selects every 5th row of

sdt_prices

table

SELECT *
FROM ( 
    SELECT @row := @row +1 AS rownum, sdt_prices.*
    FROM (
        SELECT @row :=0) r, sdt_prices where token_id = 1 order by id desc
    ) ranked
WHERE rownum %5 = 1

Note: If the function parameter is not surrounded by

quotation marks

, then the parameter is going to be interpreted as a column value. Converts a string or column value to

UPPER-CASE

SELECT 
    UPPER(first_name), 
    UPPER('Converts a string or column value to UPPER-CASE') 
FROM students

Converts a string or column value to

lower-case

SELECT 
    LOWER(first_name), 
    LOWER('Converts a string or column value to lower-case') 
FROM students

Joins strings and columns together

SELECT 
    CONCAT(first_name, ' ', 'joins', ' ', 'strings', ' ', 'and', ' ', 'columns') 
FROM students

The function

LENGTH

Returns the length (in bytes)

SELECT 
    id, 
    first_name,
    LENGTH(first_name) first_name_length 
FROM students

Note: Each Georgian letter takes

three bytes

The function is similar to

CONCAT

where

WS

means - With Separator. The first parameter is the separator between each one of the additional fields, that we pass as next parameters

SELECT 
    CONCAT_WS(' - ', first_name, 'joins', 'strings', 'and', 'columns', 'with', 'separator') 
FROM students

The function

TRIM

removes leading and trailing spaces

SELECT 
    LENGTH(' text ') length,
    LENGTH(TRIM(' text ')) length_with_trim

The function

RTRIM

removes trailing spaces (removes spaces from the end)

SELECT 
    LENGTH(' text ') length,
    LENGTH(RTRIM(' text ')) length_with_right_trim

The function

LTRIM

remove leading spaces (removes spaces from the beginning)

SELECT 
    LENGTH(' text ') length,
    LENGTH(LTRIM(' text ')) length_with_left_trim

The function

LEFT

returns leftmost characters. In this case 5 characters because we pass 5 as second parameter

SELECT 
    LEFT(first_name, 5) AS 'five leftmost characters'
FROM students

The function

RPAD

appends string (third parameter) the specified number of times (second parameter) to the first parameter. In this example each one of the student's last name that is less than 10 characters long, is filled with

-

SELECT 
    RPAD(first_name, 10, '-') AS 'student name'
FROM students

Complete list of string functions on the official documentation: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

Adds column

personal_id

after

api_method

column in

requests

table

ALTER TABLE requests
ADD COLUMN personal_id VARCHAR(15) AFTER api_method;

If we run the same

alter

statement one more time, we will get the following error with code

1060

Error Code: 1060. Duplicate column name 'personal_id'

If we want to delete a column from a table, we use the following command

ALTER TABLE `requests` 
DROP COLUMN `status_id`;

The function gets data table, passes to recursive function to get associative array back. Encodes the array to JSON and returns back to user:

public function getCategoryTreeJSON()
{
    $query = "  SELECT
                    c1.id,
                    c1.name,
                    c1.parent_id,
                    (
                        SELECT
                            COUNT(c2.id) 
                        FROM
                            categories AS c2 
                        WHERE
                            c2.parent_id = c1.id
                    )
                    AS children_count 
                FROM
                    categories AS c1";

    $categoryDetails = json_decode(json_encode(DB::select($query)), true);
    
    $array_tree = $this->getCategoryTreeArray($categoryDetails);

    return json_encode($array_tree);
}

Recursive function that returns multi level associative array. Base case: when

children_count

is equal to zero.

public function getCategoryTreeArray($items, $parent_id = 0)
{
    $branch = [];
    $array_JSON = [];  

    foreach ($items as $item) {
        if ($item['parent_id'] == $parent_id) {

            if ($item['children_count'] > 0) {
                
                $array_JSON[] = [
                    'id'=>$item['id'],
                    'title'=>$item['name'],
                    'subs'=>$this->getCategoryTreeArray($items, $item['id'])
                ];
                
            } else {
                
                $array_JSON[] = [
                    'id'=>$item['id'],
                    'title'=>$item['name']
                ];
            }
            
        }
    }
    return $array_JSON;
}

Function

JSON_PRETTY

prettifies JSON content

SELECT 
    JSON_PRETTY(document_details) 
FROM students

Note:

MariaDB

does not include

JSON_PRETTY

function

Function

JSON_TYPE

takes JSON content and returns its type

SELECT 
    JSON_TYPE('{}'),  -- returns OBJECT
    JSON_TYPE('[]'),  -- returns ARRAY
    JSON_TYPE(NULL)   -- returns NULL