×
Clear all filters including search bar
Valeri Tandilashvili's MySQL Notes
Results: 175
Differences between NO ACTION and RESTRICT 
NO ACTION
is equivalent to RESTRICT. The MySQL Server rejects the delete and update operations of the parent table if there is a related foreign key value in the referenced table
FOREIGN KEY 
FOREIGN KEY
constraint protects data integrity. Let's create 
students
and 
note
tables
USE university;
DROP TABLE IF EXISTS students;
CREATE TABLE students (
    id int AUTO_INCREMENT,
    first_name varchar(50),
    last_name varchar(50),
    PRIMARY KEY(id)
);
Let's add 
FOREIGN KEY
called 
fk_student_note
to the 
notes
table
DROP TABLE IF EXISTS notes;
CREATE TABLE notes (
    id int AUTO_INCREMENT,
    student_id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id),
    INDEX(student_id),
    CONSTRAINT fk_student_note  
        FOREIGN KEY (student_id)  
        REFERENCES students(id) 
        ON UPDATE CASCADE 
        ON DELETE RESTRICT
);
IN
Selects all students that has one of the following last names 
გოგია
გაგუა
გოგუა
SELECT *
FROM students
WHERE last_name IN ('გოგია', 'გაგუა', 'გოგუა')
Filtering the result using 
IN
with sub-query:
SELECT
    *
FROM
    students
WHERE
    last_name IN(
        SELECT
            last_name
        FROM
            students
        WHERE
            id <= 5
    )
Alternative of the query using 
=
operator:
SELECT *
FROM students
WHERE last_name = 'გოგია' 
OR last_name = 'გაგუა'
OR last_name = 'გოგუა'
Set column at the top
Makes the column first - sets it at the top
ALTER TABLE note
CHANGE COLUMN title title VARCHAR(210) FIRST
The same thing using 
MODIFY
ALTER TABLE note
MODIFY COLUMN title VARCHAR(210) FIRST
Changes column position in a table 
ALTER TABLE note
CHANGE COLUMN title title VARCHAR(210) AFTER author_id
Another way to do the same thing is to use 
MODIFY
instead of 
CHANGE
ALTER TABLE note
MODIFY COLUMN title VARCHAR(210) AFTER description
Deletes specified column from a table
Deletes column 
title
from table 
note
ALTER TABLE note 
DROP COLUMN title;
COLUMN
is optional. The following query is alternative to the first one:
ALTER TABLE note 
DROP title;
Rename column
Renames the column 
name
to 
title
ALTER TABLE note CHANGE name title VARCHAR(210)
Change column type
Changes the column type to 
VARCHAR(200)
ALTER TABLE note
MODIFY COLUMN title VARCHAR(200) NOT NULL;
Add column to an existing table
Adds column 
author_id
to the table 
note
after 
title
column
ALTER TABLE note
ADD COLUMN author_id int not null default 0 AFTER title;
Change AUTO_INCREMENT value
Changes 
AUTO_INCREMENT
value for 
note
table to 
100000
ALTER TABLE note
AUTO_INCREMENT = 100000;
Results: 175