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

Function

JSON_ARRAY

takes parameters as array members and returns JSON string as array

SELECT
    JSON_ARRAY("2020-05-25", "2030-05-18", "number", 5)

Function

JSON_OBJECT

returns JSON string. It takes comma separated key / value pairs as parameters

INSERT INTO students (
    first_name, 
    last_name, 
    gender, 
    points, 
    document_details
) 
VALUES (
    'Beka', 
    'Gelashvili', 
    '1', 
    '82', 
    JSON_OBJECT('issue_date', "2020-05-25", "expire_date", "2030-05-18", "number", 5)
)

Extracts values from JSON object. In this example we extract two values:

number

,

issue_date

SELECT
    document_details,
    JSON_EXTRACT(document_details, '$.number'),
    JSON_EXTRACT(document_details, '$.issue_date'),
    JSON_UNQUOTE(JSON_EXTRACT(document_details, '$.issue_date'))
FROM students

Note: We can use

JSON_UNQUOTE()

function to remove quotes around JSON string values

Function

ROUND

rounds the passed value using standard Math rules. The first argument

2.3

will be rounded to 2 and the second

2.5

becomes 3

SELECT
    ROUND(2.3),
    ROUND(2.5)

Function

FLOOR

rounds the argument down to the greater integer less then the decimal argument. Both of the arguments will be rounded down to

2

SELECT
    FLOOR(2.3),
    FLOOR(2.8)

Function

CEIL

rounds the number up to the lowest integer value greater than the passed decimal argument. Both of the arguments will be rounded to

3

SELECT
    CEIL(2.3),
    CEIL(2.8)

Function

RADIANS

converts degrees to radians.

90

degrees converted to radians gives us half of

PI

:

1.5707963267948966

.

180

degrees converted to radians gives us

PI

:

3.141592653589793

.

SELECT
    RADIANS(90),
    RADIANS(180)

Function

DEGREES

converts radians back to degrees. Half of

PI

radians

1.5707963267948966

converted to degrees gives us

90

degrees.

PI

:

3.141592653589793

radians converted to degrees gives us

180

degrees.

SELECT
    DEGREES(1.5707963267948966),
    DEGREES(3.141592653589793)

These two functions

RADIANS

and

DEGREES

are opposite to each other.

180

degrees converted to radians gives us

PI

:

3.141592653589793

PI

:

3.141592653589793

radians converted to degrees gives us

180

degrees.

SELECT
    RADIANS(180),
    DEGREES(3.141592653589793)

Function

POWER

raises the first argument to the power of another argument. The code below returns

16

because

2

to the power of

4

is

16

SELECT 
    POWER(2, 4)

Note:

POW

and

POWER

are the aliases for the same command The function

CONV

converts the first argument from one number system (the second argument) to another (the third argument) Converts

5

from

10

base system to

2

SELECT
    CONV(5, 10, 2)

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

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

Implicit JOIN syntax

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

The following explicit JOIN returns the same result as the above implicit one:

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

If we forget

WHERE

clause on implicit JOIN, we will get CROSS JOIN result

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

Which is the equivalent of the following explicit JOIN syntax:

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

CONS

of the implicit JOIN is that chance is higher to forget

WHERE

clause

Using

CREATE

`keyword we can create different database objects. Creates database

university

CREATE DATABASE university

Creates table:

CREATE TABLE students (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(50)
)

Creates database

user1

which will have password

some-pass

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'some-pass';

The relationship caused problems We should not write this many to many relationship inside User's model if we use Spatie permissions package

public function roles()
{
    return $this->belongsToMany(Role::class, 'model_has_roles', 'model_id', 'role_id');
}