We can

SELECT

from one table and

INSERT

the selected data to another table using

INSERT & SELECT

statement

INSERT INTO `students` (`first_name`, `last_name`, `points`) 
SELECT first_name, last_name, points * 1.2 FROM students WHERE id = 3

The only thing we must consider is column positions (position must be the same)

We can clone a table using

CREATE & SELECT

statement

CREATE TABLE students_archived AS
SELECT * FROM students

Updates students table based on notes table column

notes.id

UPDATE 
  students 
SET 
  points = points + 1 
WHERE 
  student_id = (
    SELECT 
      student_id 
    FROM 
      notes 
    WHERE 
      notes.id = 1
  )

We can get the same result using explicit join

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

Updates students table based on notes table column -

notes.id

using

explicit JOIN

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

Updates students table based on notes table column -

notes.id

using

implicit JOIN

UPDATE notes, students
SET points = points+1
WHERE notes.student_id = students.id
	AND notes.note = 'My first note'

We can give

alias

to tables or table columns. In this example table column

first_name

will be displayed as

student_name

SELECT 
    id,
    first_name AS student_name
FROM students

The keyword

AS

is optional, but it's better to use it, because the query is much more readable. The following query returns the same result

SELECT 
    id,
    first_name student_name
FROM students

We can use

alias

in

ORDER BY

clause to order the result

SELECT *, price * quantity AS total
FROM `orders` 
ORDER BY total DESC

If

alias

is given to table, column of the table can be accessed by the alias followed by

.

SELECT 
    s.id,
    s.first_name
FROM `students` s

In

SELF JOIN

alias is required, because MySQL needs to treat the tables as different tables

SELECT  
    s1.*,
    s2.first_name
FROM `students` s1
JOIN `students` s2 ON s2.santa_id = s1.id

quotes

or backticks can be used in alias. Possible options: single quote

'

, double quote

"

or backtick

 ` 

SELECT
    id,
    first_name AS 'student_name'
FROM 
    students

Advantage of using

quotes

is that we can use space separated names in alias

SELECT 
    first_name, 
    last_name, 
    (points + 10) * 2 AS 'points calculated'
FROM students

sub-query

used in

SELECT

clause. Calculates student's point in percent based on max points

SELECT
    id,
    first_name,
    points,
    (
        points * 100 /
        (
            SELECT
                MAX(points)
            FROM
                students
        )
    ) AS percent
FROM
    students

sub-query

used in

IF

conditional statement. Highlights the student with title

Highest

which has highest points

SELECT *, 
    IF(points>=90, IF(points=(SELECT MAX(points) FROM students), "Highest", "Brilliant"), "Lazy") AS class
FROM `students`
ORDER BY points DESC

sub-query

used in

FROM

clause. Select liked notes with likes counts and authors

SELECT 
    students.first_name, 
    note_id, 
    notes.note,
    liked_notes.likes_count
FROM (  
    	SELECT 
            note_likes.note_id AS note_id,
            COUNT(note_likes.id) AS likes_count
        FROM note_likes
        GROUP BY note_likes.note_id
    ) AS `liked_notes`
JOIN notes ON liked_notes.note_id = notes.id
JOIN students ON notes.student_id = students.id
ORDER BY likes_count DESC

sub-query

used in

WHERE

clause. Selects all students that have max points

SELECT
    id,
    first_name,
    points
FROM
    students
WHERE
    points = (
        SELECT
            MAX(points)
        FROM
            students
    )

sub-query

used in

INSERT

statement. Before inserting the record,

sub-query

gets

gender_id

based on the provided gender name

INSERT INTO students (
    first_name, 
    last_name, 
    points,
    gender_id
) 
VALUES (
    'ილია', 
    'დავითაშვილი', 
    '84',
    (SELECT id FROM genders WHERE name = 'Male')
)

sub-query

used in

WHERE

clause in

UPDATE

statement. Updates students table based on notes table column

notes.id

UPDATE 
  students 
SET 
  points = points + 1 
WHERE 
  student_id = (
    SELECT 
      student_id 
    FROM 
      notes 
    WHERE 
      notes.id = 1
  )

Returns

COUNT

of all rows of the

students

table

SELECT COUNT(*) AS students_count
FROM students

Returns

minimum

points from

students

table

SELECT MIN(points) AS minimum_points
FROM students

Returns

maximum

points from

students

table

SELECT MAX(points) AS maximum_points
FROM students

Returns

average

points from

students

table

SELECT AVG(points) AS average_points
FROM students

Returns

SUM

of all students points

SELECT SUM(points) AS 'sum of all points'
FROM students

Aggregate functions behaves differently when there is any number of additional columns with any of the aggregate functions in

SELECT

clause.

GROUP BY

clause is needed when there is any additional column from the table. In this example rows are grouped by

last_name

and the value that

SUM

function returns is aggregated. We get all the unique last names with its aggregated points

SELECT last_name, SUM(points) AS 'sum of all points'
FROM students
GROUP BY last_name

We can do the same for all the other aggregated functions Complete list of aggregate functions: https://dev.mysql.com/doc/refman/5.6/en/aggregate-functions.html

We can use

sub-query

for

INSERT

statement. Before inserting the record,

sub-query

gets

gender_id

based on the provided gender name

INSERT INTO students (
    first_name, 
    last_name, 
    points,
    gender_id
) 
VALUES (
    'ილია', 
    'დავითაშვილი', 
    '84',
    (SELECT id FROM genders WHERE name = 'Male')
)

REPLACE INTO

is the similar to

INSERT INTO

statement. The difference is that it updates the record if it already exists. It's required one of the listed columns to be a private key of the table

REPLACE INTO students (id, first_name, points)
VALUES (41, 'ილიკო', 147)

Note 1

: If primary key is not listed, it will insert the record, but if the primary key is one of the listed columns, it will update the specified row that matches the primary key.

Note 2

: If the primary key exists, all the other omitted columns will get the default values after updating the record. Similar to the

REPLACE INTO

statement is

ON DUPLICATE KEY

. The only difference is that on duplicate key it updates only the listed columns but omitted values stays the same

INSERT INTO students (id, first_name, points) 
VALUES (41, 'გიორგი', 149) 

ON DUPLICATE KEY 

UPDATE first_name = 'გიორგი', points = 123