×
Clear all filters including search bar
Valeri Tandilashvili's MySQL Notes
Results: 175
Create UNIQUE index for student notes
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
Create table and add several INDEXES
Creates the table 
note
and adds several indexes 
(ind_note_id, ind_title_description)
at the same time
CREATE TABLE note (
    id int AUTO_INCREMENT,
    note_id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id, note_id),
    INDEX ind_note_id (note_id),
    INDEX ind_title_description (title, description)
);
Create INDEX with several fields
Creates one 
INDEX
with several fields for table 
notes
. Combines the two fields 
title, description
to create one index
CREATE TABLE note (
    id int AUTO_INCREMENT,
    note_id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id, note_id),
    INDEX ind_title_description (title, description)
);
If we need to filter the result with both fields 
(title, description)
at the same time, then 
ind_title_description
is a great index
Create procedure that checks and prints income level 
DROP PROCEDURE IF EXISTS printIncomeLevel;

DELIMITER //

CREATE PROCEDURE printIncomeLevel (IN monthly_value INT)

BEGIN

   DECLARE income_level varchar(20);

   IF monthly_value <= 4000 THEN
        SET income_level = 'Low Income';
   ELSEIF monthly_value > 4000 AND monthly_value <= 7000 THEN
        SET income_level = 'Avg Income';
   ELSE
        SET income_level = 'High Income';
   END IF;

   INSERT INTO log (description)
   VALUES ('getIncomeLevel procedure was called');

   SELECT income_level;

END;
After creating the procedure, we can call it using 
CALL
keyword:
CALL printIncomeLevel(450)
Create function salary level 
DROP FUNCTION IF EXISTS getIncomeLevel;

DELIMITER //

CREATE FUNCTION getIncomeLevel ( monthly_value INT )
RETURNS varchar(20)

BEGIN

   DECLARE income_level varchar(20);

   IF monthly_value <= 4000 THEN
        SET income_level = 'Low Income';
   ELSEIF monthly_value > 4000 AND monthly_value <= 7000 THEN
        SET income_level = 'Avg Income';
   ELSE
        SET income_level = 'High Income';
   END IF;

   INSERT INTO log (description)
   VALUES ('getIncomeLevel function was called');

   RETURN income_level;

END; //

DELIMITER ;
After creating the function we can call it:
SELECT getIncomeLevel(450)
Foreign key constraint Referential Actions
MySQL supports several referential actions: 
CASCADE
- deletes or updates appropriate rows in the child table 
SET NULL
- sets the foreign key column of the child table to 
NULL
RESTRICT
- rejects the operation for the parent table 
NO ACTION
- equivalent to 
RESTRICT
Note: 
RESTRICT
is the same as omitting the 
ON DELETE
or 
ON UPDATE
clause, which means that it's default action
Create TRIGGER before INSERT
Creates trigger that runs 
before
insert
and checks & replaces gender values
DROP TRIGGER IF EXISTS before_student_insert;

DELIMITER $$

    CREATE TRIGGER before_student_insert BEFORE INSERT 
    ON students
    FOR EACH ROW 
    BEGIN

    INSERT INTO log (description)
    VALUES ('One student will be inserted');
    
    IF (NEW.gender = 'Female') THEN
        SET NEW.gender = 2;
    ELSE
        SET NEW.gender = 1;
    END IF;

    END$$

DELIMITER ;
Trigger types
Trigger can run 
before
or 
after
some database event. The event can be 
insert
, 
update
or 
delete
. It means there are six types of triggers: 
before / after
insert 
before / after
update 
before / after
delete
Add FOREIGN KEY constraint after creating a table
Creates 
FOREIGN KEY
constraint with 
student_id
column and references it to 
students.id
ALTER TABLE notes 
ADD CONSTRAINT fk_student_note
    FOREIGN KEY (student_id)  
    REFERENCES students(id) 
    ON UPDATE CASCADE 
    ON DELETE RESTRICT
We can omit 
CONSTRAINT fk_student_note
sub-clause. The difference is that if we omit the sub-clause, MySQL names the constraint automatically
ALTER TABLE notes 
ADD FOREIGN KEY (student_id)  
    REFERENCES students(id) 
    ON UPDATE CASCADE 
    ON DELETE RESTRICT
Delete FOREIGN KEY by its name 
ALTER TABLE notes 
DROP FOREIGN KEY fk_student_note`;
Results: 175