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

In Laravel 8+

tinyInteger

method generates

tinyint(4)

data type in MySQL

$table->tinyInteger('column_name');

Whereas

boolean

method generates

tinyint(1)

data type

$table->$table->boolean('column_name');

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;
}

Updates students table based on notes table column -

notes.id

using

implicit JOIN

UPDATE notes, students
SET points = points+1
WHERE notes.student_id = students.id
	AND notes.note = 'My first note'

Updates students table based on notes table column -

notes.id

using

explicit JOIN

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

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
  )

We can get the same result using explicit join

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

We can clone a table using

CREATE & SELECT

statement

CREATE TABLE students_archived AS
SELECT * FROM students

We can

SELECT

from one table and

INSERT

the selected data to another table using

INSERT & SELECT

statement

INSERT INTO `students` (`first_name`, `last_name`, `points`) 
SELECT first_name, last_name, points * 1.2 FROM students WHERE id = 3

The only thing we must consider is column positions (position must be the same)

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

SELECT *
FROM students
WHERE points BETWEEN 80 AND 90

We can get the same result by using

AND

logical operator

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

Updates all rows. Increases every student's points by one

UPDATE `students` SET `points` = points+1;

Updates several columns in one row using

private key

UPDATE `students` SET `first_name` = 'თენგიზ', `last_name` = 'ბოჭორიშვილი', mail = 'tengiz@gmail.com' WHERE `students`.`id` = 38;

Updates students table based on notes table column -

notes.id

using

Implicit JOIN

UPDATE notes, students
SET points = points+1
WHERE notes.student_id = students.id
	AND notes.note = 'My first note'

Updates students table based on notes table column -

notes.id

using

explicit JOIN

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

Updates students table based on notes table column -

notes.id

using sub-query

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

Alternative of the above three updates:

SELECT student_id FROM `notes` WHERE id = 5;
UPDATE students SET points = points + 1 WHERE id = 3;