Results: 175
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
CREATE keyword
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';
date format: yyyy-mm-dd
MySQL:
SELECT CONVERT(varchar, getdate(), 23)
MSSQL:
SELECT DATE_FORMAT(curdate(), "%Y-%m-%d");
Show table sizes
Shows all the tables with their sizes in MB with descending order
SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) AS MB_size 
FROM information_schema.TABLES 
WHERE table_schema = "database_name"
order by MB_size desc
UNIQUE several columns combined
Columns
token_id
and
base_price_pair
combined will be in the table
ALTER TABLE sdtokens.sdt_prices_unique
ADD UNIQUE (token_id, base_price_pair);
Results: 175