×
Clear all filters including search bar
Valeri Tandilashvili's MySQL Notes
Results: 175
Update VIEW
Updates VIEW 
student_notes
, increases author's points by one in 
students
table based on note's ID. First we need to create the VIEW
CREATE VIEW IF NOT EXISTS student_notes AS
SELECT 
    students.first_name,
    students.last_name,
    students.points,
    notes.*
FROM students
JOIN notes ON notes.student_id = students.id
Then we update the VIEW as a regular table
UPDATE `student_notes` 
SET points = points + 1 
WHERE id = 1;
Create VIEW "student_notes"
Creates 
VIEW
called 
student_notes
that selects all notes with their authors
CREATE VIEW IF NOT EXISTS student_notes AS
SELECT 
    students.first_name,
    students.last_name,
    notes.*
FROM students
JOIN notes ON notes.student_id = students.id
After creating the 
VIEW
then we can use it as regular table like this example:
SELECT * 
FROM student_notes
Create function "full_name"
Creates function 
full_name
that concatenates the two parameters and returns the result
DROP FUNCTION IF EXISTS full_name;

CREATE FUNCTION full_name ( first_name VARCHAR(100), last_name VARCHAR(100) )
RETURNS VARCHAR(210)
RETURN CONCAT(first_name, ' ', last_name);
After creating the function we can call it:
SELECT 
    full_name('John', 'Swift');
DATE_FORMAT
Function 
DATE_FORMAT
returns formatted version of the datetime string passed as the first parameter
SELECT 
    DATE_FORMAT('2022-03-25 18:24:14', '%Y-%m-%d %H:%i:%s')
In this example we only need to get month and day with underscore between the values
SELECT 
    DATE_FORMAT('2022-03-25 18:24:14', '%m_%d')
MAKETIME
Function 
MAKETIME
takes three parameters as hour, minute and time and returns formatted time like: 
18:15:19
SELECT 
    MAKETIME(18, 15, 19)
TIMESTAMPDIFF
Function 
TIMESTAMPDIFF
calculates the difference between two times. The result is in unites that we want to measure the difference between the times
SELECT 
    TIMESTAMPDIFF(DAY, '2021-03-13', '2021-03-25')
DATEDIFF
Function 
DATEDIFF
Calculates the number of days between the two parameters. In this example the function returns 
12
which is the number of days between the dates
SELECT 
    DATEDIFF('2021-03-25', '2021-03-13')
The function returns 
negative number
if the second date is greater than the first one. In the example the result will be 
-12
SELECT 
    DATEDIFF('2021-03-13', '2021-03-25')
SUBDATE
Function 
SUBDATE
subtracts some amount of time from the specified date
SELECT 
    SUBDATE('2021-03-25', INTERVAL 30 DAY)
ADDDATE
Function 
ADDDATE
adds some amount of time to the specified date
SELECT 
    ADDDATE('2021-03-25', INTERVAL 8 DAY)
CURDATE 
CURDATE
returns current date with the following syntax:
YYYY-MM-DD
SELECT 
    CURDATE()
Results: 175