×
Clear all filters including search bar
Valeri Tandilashvili's Personal Professional Blog
Results: 1024
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;
Adding AUTO_INCREMENT to any varchar column
Adding 
AUTO_INCREMENT
to any 
varchar
column first requires the column to be converted into 
int
CREATE TABLE note (
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(title)
);
INSERT INTO `note` (`title`, `description`) VALUES ('first', 'value'), ('second', 'value');
Let's add 
AUTO_INCREMENT
to 
title
column. We are not able to add 
AUTO_INCREMENT
without changing it's type to 
int
ALTER TABLE note MODIFY title int NOT NULL AUTO_INCREMENT;
Note: In such cases If the table contains rows with string values, (for 
AUTO INCREMENT
field) the string values will be replaced with numbers starting with 1
Results: 1024