codecamedy의 learn SQL 2. Queries

https://www.codecademy.com/learn/learn-sql

1. SELECT

SELECT * 
FROM table_name;

SELECT column1, column2 
FROM table_name;

table의 column의 값들 확인

 

2. As

SELECT name AS 'Titles'
FROM movies;

 

3. Distinct

SELECT DISTINCT tools 
FROM inventory;

 

4. Where

SELECT *
FROM movies
WHERE imdb_rating > 8;

 

5. Like

SELECT * 
FROM movies
WHERE name LIKE 'Se_en';

where에 등호 대신 like를 써서 정규식 비슷한게 가능하다.

_ 엔 아무 글자나 가능. 한 글자.

 

SELECT * 
FROM movies
WHERE name LIKE 'A%';

SELECT * 
FROM movies 
WHERE name LIKE '%man%';

%은 정규식의 ?

 

6. Is Null

SELECT name
FROM movies 
WHERE imdb_rating IS NOT NULL;

is null, is not null

 

7. Between

SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;

1990 <= year <= 1999

SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';

주의할 점은 'A' <= name <= 'J' 까지라서 딱 'J' 까지만 되고 'Ja' 부터 안됨. 그래서 다음 글자인 'K' 로 하는것도 방법.

 

8. And

SELECT * 
FROM movies
WHERE year BETWEEN 1990 AND 1999
   AND genre = 'romance';

 

9. Or

SELECT *
FROM movies
WHERE year > 2014
   OR genre = 'action';

 

10. Order By

SELECT *
FROM movies
WHERE imdb_rating > 8
ORDER BY year DESC;

 

11. Limit

SELECT *
FROM movies
LIMIT 10;

SELECT *
FROM movies
order by imdb_rating DESC
LIMIT 3;

 

12. Case

SELECT name,
 CASE
  WHEN imdb_rating > 8 THEN 'Fantastic'
  WHEN imdb_rating > 6 THEN 'Poorly Received'
  ELSE 'Avoid at All Costs'
 END AS 'Review'
FROM movies;

즉석에서 column을 만드는 개념이다.

 

select name,
 CASE
  when genre = 'romance' then 'Chill'
  when genre = 'comedy' then 'Chill'
  else 'Intense'
 end as 'review'
from movies;

 

• SELECT is the clause we use every time we want to query information from a database.
• AS renames a column or table.
• DISTINCT return unique values.
• WHERE is a popular command that lets you filter the results of the query based on conditions that you specify.
• LIKE and BETWEEN are special operators.
• AND and OR combines multiple conditions.
• ORDER BY sorts the result.
• LIMIT specifies the maximum number of rows that the query will return.
• CASE creates different outputs.