×
Clear all filters including search bar
Valeri Tandilashvili's MySQL Notes
JSON_ARRAY
takes parameters as array members and returns JSON string as arraySELECT
JSON_ARRAY("2020-05-25", "2030-05-18", "number", 5)
JSON_OBJECT
returns JSON string.
It takes comma separated key / value pairs as parametersINSERT 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)
)
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 valuesROUND
rounds the passed value using standard Math rules.
The first argument 2.3
will be rounded to 2 and the second 2.5
becomes 3SELECT
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.htmlCONCAT
where WS
means - with separator.
The first parameter is the separator between each one of the additional fields, that we pass as next parametersSELECT
CONCAT_WS(' - ', first_name, 'joins', 'strings', 'and', 'columns', 'with', 'separator')
FROM students
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 resultSELECT
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
clauseCREATE
`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';
SELECT CONVERT(varchar, getdate(), 23)
MSSQL:SELECT DATE_FORMAT(curdate(), "%Y-%m-%d");
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
token_id
and base_price_pair
combined will be in the tableALTER TABLE sdtokens.sdt_prices_unique
ADD UNIQUE (token_id, base_price_pair);