22일차

2024-01-09, 22일차

SQL 활용 정리

정렬

• 특정 컬럼을 지정해 그 컬럼의 값으로 행을 정렬.
  • order by 컬럼 asc/desc
    • asc : 오름차순
    • desc : 내림차순
    • Products 테이블에서 가격(Price)으로 오름차순, 제품번호(ProductId)로 내림차순 정렬하기

      select *
      from Products
      order by Price ASC, ProductID DESC;

별명

• 특정 컬럼을 새로운 별명을 붙여 사용. 주로 연산이나 함수를 통해 새로 나온 컬럼에 붙인다.

  • 컬럼 as 별명

  • Products 테이블에서 ProductId, SupplierId, CategoryId를 더한 값으로 새로운 컬럼을 만들고 컬럼 이름을 NewId로 정하기

  SELECT ProductId, SupplierId, CategoryId, ProductId + SupplierId + CategoryId as NewId FROM Products;

사칙연산

• +, *, /, -

  • 숫자 + 숫자 == 산술 연산

  • 문자 + 숫자 == 문자는 0 취급

  • 컬럼1 + 컬럼2 == 컬럼의 모든 행이 연산.

  • 사칙 연산 예제

  SELECT 10 + 20;
  SELECT 10 * 20;
  SELECT 10 / 20;
  SELECT 10 - 20;

비교연산

• , <, =, !=

  • 결과는 1(참, TRUE), 0(거짓, FALSE)
  • 비교 연산 예제

  SELECT 10 = 20;
  SELECT 10 != 20;
  SELECT 10 > 20;
  SELECT 10 < 20;
  SELECT 10 >= 20;
  SELECT 10 <= 20;

논리연산

• and, or

  • and : 조건이 여러개 일 때 모두 만족해야 맞다고 보는 연산

  • or : 조건이 여러개일 때 하나라도 만족하면 맞다고 보는 연산

  • Customers 테이블에서 CustomerId값이 10 이상이고 CustomerId값이 20 이하인 행만 조회

  SELECT * 
  FROM Customers
  where CustomerID >= 10 
  and CustomerId <= 20;

  • Customers 테이블에서 CustomerId값이 20 이하인것 + CustomerId값이 80이상인 행을 모두 조회

  SELECT * 
  FROM Customers
  where CustomerID <= 20 
  or CustomerId >= 80;

  • Customers 테이블에서 CustomerId갑이 20 이하인 것 + CustomerId값이 80이상인 모든 컬럼 중에서 city가 'London'인 행을 조회

  SELECT * 
  FROM Customers
  where (CustomerID <= 20 
  or CustomerId >= 80)
  and city = 'London';

사잇값

• 컬럼 between A and B
  • 컬럼값이 A와 B 사이인 것만 조회
  • Customers 테이블에서 CustomerId값이 10과 20 사이인 행을 조회

  SELECT * 
  FROM Customers
  where CustomerID BETWEEN 10 AND 20;

포함

• 컬럼 in (A, B, C ...)
  • 컬럼값이 A,B,C 인것을 모두 조회
  • Customers 테이블에서 city 값에 'London', 'Berlin', 'Madrid' 중 하나라도 포함하는 행을 모두 조회

    SELECT * 
    FROM Customers
    WHERE City in ('London', 'Berlin', 'Madrid');

그룹핑

• 그룹핑은 특정 컬럼을 기준으로 그룹을 나누어 모든 행을 하나의 그룹으로 편입시켜 그룹별로 집계를 할 때 사용.

• 예를 들어 ~별 ~결과를 봐야한다고 하면 그룹핑해야 한다고 생각하면 된다.

• ex )

  • 국가별 ~~ 석유 사용 현황 -> 국가로 그룹핑
  • 연령별 ~~ 투표참여율 -> 연령으로 그룹핑
  • 지역별 ~~ 초등학교 개수 -> 지역으로 그룹핑
  • 도시별 ~~ 평균 주택 가격 -> 도시로 그룹핑

• Customer 테이블에서 국가별 회원 수 보기

  SELECT Country, COUNT(Country) 
  FROM Customers 
  GROUP BY Country
  

• Customer 테이블에서 도시별 회원 수 보기

  SELECT Country, City, COUNT(City) 
  FROM Customers 
  GROUP BY Country, City
  

HAVING

• 일반적으로 select문은 from > where > group by > having > select > order by 순으로 실행된다.

• where가 group by 보다 먼저 실행되기 때문에 group by 이후에 where로 조건을 따질 수 없다. 그래서 having을 사용한다.

• Customers 테이블에서 회원수가 5이상인 국가만 보기

  SELECT *, country, count(*) cnt FROM Customers 
  group by country
  having cnt > 5

  LIKE, NOT LIKE

• 특정 문자로 시작여부, 끝나는지 여부, 포함하는지 여부

• 찾고자하는 문자열을 %기호와 조합해서 사용. 여기서 %는 나머지 문자를 의미

  • 'A%' : A로 시작하는 문자열

  • '%A' : A로 끝나는 문자열

  • '%A%' : A를 포함하는 문자열

  • Employees 테이블에서 LastName이 D로 시작하는 행만 조회

  SELECT * FROM Employees
  where LastName like 'D%';

  • Employees 테이블에서 LastName이 D로 시작하지 않는 행만 조회

  SELECT * FROM Employees
  where LastName not like 'D%';

CEIL, ROUND, FLOOR

• 소수점을 올림, 반올림, 버림
  • CEIL() : 올림
  • ROUND() : 반올림
  • FLOOR() : 버림
• 1.2를 반올림

  SELECT ROUND(1.2); 

• Products 테이블의 Price 컬럼값들을 반올림한다.

  SELECT ROUND(Price) FROM Products;

AVG, MAX, MIN, SUM, COUNT

• AVG(컬럼) : 컬럼의 모든 행의 평균값 구하기

• MAX(컬럼) : 컬럼의 모든 행중 최대값 구하기

• MIN(컬럼) : 컬럼의 모든 행중 최소값 구하기

• SUM(컬럼) : 컬럼의 모든 행의 합 구하기

• COUNT(컬럼) : 컬럼의 모든 행의 개수를 구하기 (NULL값은 카운팅에서 제외됨)

  • Categories 테이블에서 CategoryName 컬럼의 모든 행의 개수를 구하기.

  SELECT count(CategoryName) FROM Categories;

CONCAT(문자1, 문자2, 문자3 ...)

• 문자들을 이어 붙여줌.
• 문자열 'taejin'과 'cha'를 이어 붙이기

   select concat('taejin', ' ','cha') as name;
   

• Employees 테이블의 LastName과 FirstName을 이어 붙여 FullName이라는 새로운 컬럼으로 만들기

  SELECT EmployeeId, BirthDate, Photo, Notes, concat(LastName, ' ', FirstName) as FullName FROM Employees;

SUBSTR(대상문자, 시작위치, 자르는길이)

• 문자열을 잘라줌
• 문자열 'taejincha'를 1번째 문자부터 6개 짜르기

  select substr('taejincha', 1, 6) as fisrtName;

• Employees 테이블에서 BirthDate 컬럼의 모든 값을 1번째 문자부터 4개 짜르기(년도만 나오게 하기)

  SELECT substr(BirthDate, 1, 4) as `year` FROM Employees

REPLACE(대상문자(컬럼), 문자열1, 문자열2)

• 대상 문자(컬럼)에 문자열1인 부분을 찾아 문자열2로 치환해주기.

  • Customers 테이블의 city 컬럼의 값에서 B문자를 찾아 b로 바꾸기

  SELECT replace(city, 'B', 'b') FROM Customers;

CURDATE(), NOW()

• 오늘날짜 구하기
  • CURDATE : 연월일
  • NOW : 연월일 시분초

ADDDATE(기준날짜, interval n day), SUBDATE()

• 기준날짜로 부터 n만큼의 거리(간격)

DATE_DIFF(날짜1, 날짜2)

• 날짜1과 날짜2의 차이를 구함

  select datediff('2022-03-21', '2021-03-21');

DISTINCT

• 조회 결과에서 중복되는 행을 제거한다.
• Suppliers에서 Country를 중복제거해서 조회하기

    SELECT DISTINCT Country 
    FROM Suppliers