Creates

UNIQUE

index for students not to have duplicate notes. The index is created with two fields

student_id

and

title

to make sure that student will not have more than one note with the same title

ALTER TABLE `notes` ADD UNIQUE `unique_student_note` (`student_id`, `title`);

Note: Several notes with the same name are allowed if they are created by different authors

One way is to add index when creating the table

CREATE TABLE IF NOT EXISTS note (
    id int AUTO_INCREMENT,
    note_id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id),
    INDEX ind_note_id (note_id)
);

Creates the same INDEX

 ind_note_id

for

note_id

field on

note

table using

ALTER TABLE

CREATE TABLE IF NOT EXISTS note (
    id int AUTO_INCREMENT,
    note_id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id)
);
ALTER TABLE note 
ADD INDEX ind_note_id (note_id);

Creates the same INDEX using

CREATE INDEX

statement

CREATE TABLE IF NOT EXISTS note (
    id int AUTO_INCREMENT,
    note_id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id)
);
CREATE INDEX ind_note_id ON note (note_id);

Creates

FULLTEXT

index for

first_name

column on

students

table

CREATE FULLTEXT INDEX ind_first_name ON students(first_name)

MATCH

and

AGAINST

keywords are used for searching in columns with

FULLTEXT

index. Searches in

first_name

column with

გიორგი

keyword

SELECT * 
FROM students 
WHERE MATCH(first_name) AGAINST('გიორგი')

It's not possible to use

MATCH(column) AGAINST('keyword')

syntax for columns with no FULLTEXT index. Let's run the following query on column

first_name

that has not FULLTEXT index:

SELECT * 
FROM students 
WHERE MATCH(last_name) AGAINST('გიორგი')

Then we will get the error:

#1191 - Can't find FULLTEXT index matching the column list

Note:

MATCH(column) AGAINST('keyword')

syntax can only be used on columns with FULLTEXT index

UUID

generates unique string combined with 32 alphanumeric symbols like:

8b652b09-8bb8-11eb-b0ac-b4b52f79163f

UUID

stands for Universally Unique IDentifier and is designed to be unique inside table, across different tables, databases and even servers. Let's add one column

uuid_value

to

students

table for

UUID

values

ALTER TABLE `students` 
ADD `uuid_value` VARCHAR(100) NOT NULL AFTER `id`;

Then update the table to assign

UUID

values

UPDATE students
SET uuid_value = UUID();

Function

NOW

Returns current date and time

SELECT 
    NOW()

Function

UNIX_TIMESTAMP

returns seconds since

1970-01-01 00:00:00

UTC

SELECT 
    UNIX_TIMESTAMP()

If any date is passed to the function as a parameter, it will calculate seconds since

1970-01-01 00:00:00

to the passed date. In this example, the function returns seconds to

2021-03-25

SELECT 
    UNIX_TIMESTAMP('2021-03-25')

Function

SYSDATE

Returns current date and time

SELECT 
    SYSDATE()

We can pass parameter

precision

to the function that indicates milliseconds and microseconds:

SELECT 
    SYSDATE(6)

Function

CURRENT_DATE

returns current date with the following syntax:

YYYY-MM-DD

SELECT 
    CURRENT_DATE()

Function

CURDATE

returns current date with the following syntax:

YYYY-MM-DD

SELECT 
    CURDATE()

Function

ADDDATE

adds some amount of time to the specified date

SELECT 
    ADDDATE('2021-03-25', INTERVAL 8 DAY)

Function

SUBDATE

subtracts some amount of time from the specified date

SELECT 
    SUBDATE('2021-03-25', INTERVAL 30 DAY)

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')

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')

Function

MAKETIME

takes three parameters as hour, minute and time and returns formatted time like:

18:15:19

SELECT 
    MAKETIME(18, 15, 19)

Function

LAST_DAY

takes date and returns the last date of the month. In the example the result is

28

because the year is not leap year

SELECT 
    LAST_DAY('2021-02-15')

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')

UNIX_TIMESTAMP

returns seconds since

1970-01-01 00:00:00

UTC

SELECT 
    UNIX_TIMESTAMP()

If any date is passed to the function as a parameter, it will calculate seconds since

1970-01-01 00:00:00

to the passed date. In this example, the function returns seconds to

2021-03-25

SELECT 
    UNIX_TIMESTAMP('2021-03-25')

NOW

Returns current date and time

SELECT 
    NOW()

Returns current date and time

SELECT 
    SYSDATE()

We can pass parameter

precision

to the function that indicates milliseconds and microseconds:

SELECT 
    SYSDATE(6)

Maximum

precision

is 6, if we pass more than the supported number, we will get an error

SELECT 
    SYSDATE(7)

After running the query, the following error will be generated:

#1426 - Too big precision 7 specified for 'sysdate'. Maximum is 6