TIL[82].SQL基礎知識整理

SQLクエリー文のクリーンアップ

CREATE TABLE

CREATE TABLE Person (
    id int,
    name varchar(50),
    age int NOT NULL,
    gender varchar(10),
);

SELECT

Select all rows

SELECT * FROM Person;

特定の列の選択

SELECT name, age FROM Person;

Fetch distinct rows

SELECT DISTINCT name, age FROM Person;

Fetch specific number of rows

SELECT * FROM Person LIMIT 10;

LIMIT AND OFFSET keywords

SELECT * FROM Person OFFSET 5 LIMIT 5;

WHERE Clause

Filter by single column

SELECT * FROM Person WHERE id = 1;

Filter by comparison operators

WHERE age > 18;
WHERE age >= 18;
WHERE age < 18;
WHERE age <= 18;
WHERE age != 18;

BETWEEN Clause

SELECT * FROM Person WHERE age BETWEEN 10 AND 20;

LIKE operator

WHERE name like '%A%';
WHERE name like binary '%A%';
WHERE name like 'A%';
WHERE name like binary 'A%';
WHERE name like '%A';
WHERE name like binary '%A';

IN operator

WHERE id in (1, 2);

AND, OR and NOT Operators

WHERE gender='male' AND age > 25;
WHERE gender='male' OR age > 25;
WHERE NOT gender='male';

NULL Values

WHERE age is NULL;
WHERE age is NOT NULL;

ORDER BY Keyword

Ascending Order

SELECT * FROM Person order by age;

Descending Order

SELECT * FROM Person ORDER BY age DESC;

INSERT INTO Statement

INSERT INTO Person VALUES ('Jack', '23', 'male');

UPDATE Statement

Update single row

UPDATE Person SET age = 20 WHERE id = 1;

Update multiple rows

UPDATE Person SET age = age * 1.5;

DELETE Statement

Delete all rows

DELETE FROM Person;

Delete specific rows

DELETE FROM Person WHERE age < 10;

Aggregation

MIN Function

SELECT MIN(age) FROM Person;

MAX Function

SELECT MAX(age) FROM Person;

AVG Function

SELECT AVG(age) FROM Person;

SUM Function

SELECT SUM(age) FROM Person;

COUNT Function

SELECT COUNT(*) FROM Person;

GROUP BY Statement

Count of Person by gender

SELECT gender, COUNT(*) as count FROM Person GROUP BY gender;

HAVING Clause

Count of Person by gender if number of person is greater than 1

SELECT gender, COUNT('gender') as count FROM Person GROUP BY gender HAVING count > 1;

JOINS

Fetch publisher name for a book

SELECT name
FROM Book
LEFT JOIN Publisher
ON Book.publisher_id = Publisher.id
WHERE Book.id=1;

Fetch books which have specific publisher

SELECT *
FROM Book
WHERE Book.publisher_id = 1;