[MS-SQL] 야구 데이터로 연습하는 SQL

환경세팅

• MS SQL 설치 - Express 에디션
  Express를 설치해준다.

• SMSS 설치

SSMS(SQL Server Management Studio)는 SQL Server에서 Azure SQL Database까지 모든 SQL 인프라를 관리하기 위한 통합 환경입니다.

• BASEBALL STATS SAMPLE DATA 다운받기

• MS_SQL DB가져오기
  
  
  

---

RDBMS : 개발자들의 엑셀
SQL : RDBMS를 조작하기 위한 명령어
SQL은 영어 어순을 따른다고 생각하면 편함(한글과 반대)

---

SELECT FROM WHERE

SELECT namefirst, nameLast, birthYear, birthCountry -- 열 이름 
FROM players -- 어디에서
WHERE birthYear = 1866 and birthCountry != 'USA'; -- 조건 

널값 제외

SELECT *
FROM players
WHERE deathYear is not null;

패턴 매칭

• % 임의의 문자열
• _ 임의의 문자

Ex. U + random string

 SELECT * 
 FROM players
 WHERE birthCountry LIKE 'U%'; 

Ex. U + random char

 SELECT * 
 FROM players
 WHERE birthCountry LIKE 'US_'; 

ORDER BY

USE BaseballData -- 사용할 DB

SELECT TOP 100 * -- Filtering 상위 100개
FROM players -- 플레이어에서
WHERE birthDay IS NOT NULL -- 생일 정보가 있는 경우에만 
ORDER BY birthYear DESC, birthMonth DESC, birthDay DESC; -- 내림차순(=생일이 느린순)

수치 연산

수치 연산도 가능하다.
연산자 + = * / 일반 프로그래밍 같이 사용가능

SELECT 2022 - birthYear AS KoreanAge 
-- 당해연도 - 출생연도를 KoreanAge라는 컬럼으로 선택
FROM players
WHERE deathYear IS NULL and birthYear IS NOT NULL 
-- 사망하지 않고 출생이 확실한 자들 중에서 
ORDER BY KoreanAge DESC; 
-- 오름차순으로 

나이가 비정상적으로 많은 사람을 제하기 위해
KoreanAge <= 80 이라는 조건을 추가하자.

WHERE 절에서 KoreanAge를 아직 인식하지 못한다.
ORDERBY에서는 KoreanAge라는 별칭을 잘 사용했는데
왜 그 위에 WHERE절에서는 사용하지 못할까?

그 이유는
연산순서

컴퓨터가 SQL문을 실행할 때는
어디서(FROM) 어떤 조건(WHERE)의 정보들을 정렬(ORDER BY)하여
무엇을 출력(SELECT)할 지를 결정한다.

실제 연산순서 : FROM -> WHERE -> ORDERBY -> SELECT

때문에 WHERE에서는 아직 SELECT에서 별칭을 설정해준
KoreanAge의 정체에 대해서는 아직 알 수 없다.

SELECT 2022 - birthYear AS KoreanAge 
FROM players
WHERE deathYear IS NULL and birthYear IS NOT NULL AND (2022 - birthYear) <= 80
ORDER BY KoreanAge; 

문자열

SELECT 'Hello world';
SELECT N'안녕하세요'; --2byte 문자열을 읽기 위해서는 N을 붙인다.
SELECT SUBSTRING('20220130',1,3); -- 1~3번째 문자열 자르기

SELECT nameFirst + ' ' + nameLast AS fullName
FROM players
WHERE nameFirst IS NOT NULL AND nameLast IS NOT NULL;

날짜 시간

날짜를 문자열로 관리 할 때 1일 후, 6개월 뒤
이러한 정보를 표현하기에 상당하 까다롭다.

때문에 날짜를 관리할 때는 DateTime 이라는 날짜관련한 형식을
사용해주는 것이 바람직하다.

테이블을 하나 만들어 주자.

Datatype을 datetime으로 만들어서 저장하고 원하는 이름을 정한뒤
저장이 가능하다.

만든 DB 테이블에 정보를 추가하려면,

VALUES('날짜')를 기입해서 간단하게 INSERT 해줄수 있다.

    SELECT CAST('20220201 05:04' AS DATETIME);
    SELECT GETDATE();
    SELECT CURRENT_TIMESTAMP 

캐스팅 하지 않더도 type을 datetime으로
선택해주었으므로 자동으로 캐스팅되어서 테이블이 생성 된다.

SELECT ('20220201')

PC기준이 아닌 UTC기준으로 날짜를 가져올 때

SELECT GETUTCDATE();

시간 더하기 빼기

    SELECT DATEADD(YEAR, 1, '20200201');
    SELECT DATEADD(DAY, 1, '20200201');
    SELECT DATEADD(SECOND, 60, '20200201');
    SELECT DATEADD(DAY,-1, '20200201');

두 시간과이 차치 DATEDIFF

SELECT DATEDIFF(SECOND, '20220201', '20200202'); 

특정 날짜(년월일)

    SELECT DAY('20220201');
    SELECT YEAR('20220201');
    SELECT MONTH('20220201');

CASE

SELECT *, CASE 구문을 사용하여 조건에 따라서 새로운 컬럼값을 생성해 줄 수도 있다.

SELECT *, 
     CASE birthMonth
     WHEN 1 THEN N'겨울'
     WHEN 2 THEN N'겨울'
     WHEN 3 THEN N'봄'
     WHEN 4 THEN N'봄'
     WHEN 5 THEN N'봄'
     WHEN 6 THEN N'여름'
     WHEN 7 THEN N'여름'
     WHEN 8 THEN N'여름'
     WHEN 9 THEN N'가을'
     WHEN 10THEN N'가을'
     WHEN 11THEN N'가을'
     WHEN 12THEN N'겨울'
     ELSE  N'몰라요'
     END AS birthSeason
FROM players;

다음 코드도 같은 의미이다.

 SELECT *, 
     CASE
     WHEN birthMonth <= 2 THEN N'겨울'
     WHEN birthMonth <= 5 THEN N'봄'
     WHEN birthMonth <= 8 THEN N'여름'
     WHEN birthMonth <= 11 THEN N'가을'
     ELSE N'겨울'
     END AS birthSesson
FROM players;

집계함수

SELECT birthYear
FROM players

__ 중복되는 연도는 제외 하려면 DISTINCT __

SELECT DISTINCT birthYear
FROM players

__ SUM __

SELECT SUM(weight) / COUNT(weight)
FROM players;

__ AVG __

SELECT AVG(weight)
FROM players;

_단, weight IS NULL 인 경우라면 weight = 0 _

SELECT AVG(CASE WHEN weight IS NULL THEN 0 ElSE weight END)
FROM players;

__ MIN, MAX __

SELECT MIN(weight), MAX(weight)
FROM players;

실습

 SELECT *
FROM batting
  -- Q1. 보스턴 선수들만 출력
SELECT *
FROM batting
WHERE teamID = 'BOS' 
-- Q2. 보스턴 선수 소속들의 수는 몇명? (중복불가)
SELECT COUNT(DISTINCT playerID)
FROM batting
WHERE teamID = 'BOS' 
-- Q3. 보스턴 팀이 2004년도에 친 홈럼 갯수
SELECT SUM(HR)
FROM batting
WHERE teamID = 'BOS'  AND yearID = '2004'
-- Q4. 보스턴 팀 소속으로 단일년도 최다 홈런을 친 사람의 정보 
SELECT TOP 1 *
FROM batting
WHERE teamID = 'BOS'  
ORDER BY HR DESC

---

GROUP BY

'팀별로 묶어서' 분석하고 싶을 때
2004년도에 가장 많은 홈런을 날린 팀은? -> GROUPING이 필요
SELECT는 Grouping 한 컬럼만 할 수 있다.

SELECT TOP 1 teamID, SUM(HR) AS homeRuns
FROM batting
WHERE yearID = 2004 
GROUP BY teamID
ORDER BY homeRuns DESC;

2004년도에 200 홈런 이상을 날린 팀의 목록?

SELECT teamID, SUM(HR) AS homeRuns
FROM batting
WHERE yearID = 2004 
GROUP BY teamID
HAVING SUM(HR) >= 200
ORDER BY homeRuns DESC;

연산순서!! FROM -> WHERE -> GROUP BY -> HAVING SELECT -> ORDER BY

__ GROUP BY 는 하나의 열뿐 아니라 여러개의 열도 조합해서 그룹핑 가능__

--Q 단일 연도에 가장 많은 홈런을 날린 팀은?

SELECT teamID, yearID, SUM(HR) AS homeRuns
FROM batting
GROUP BY teamID, yearID

-- BOS 2004, BOS 2005 -> 2004년도의 BOS 과 2005년도으 BOS는 다른 팀으로 인식
HAVING SUM(HR) >= 200 
ORDER BY homeRuns DESC;

INSERT DELETE UPDATE

 -- INSERT DELETE UPDATE
SELECT *
FROM salaries
ORDER BY yearID DESC;

INSERT

-> INSERT INTO [테이블명] VALUES [값, ..]

INSERT INTO salaries
VALUES (2020, 'KOR', 'NL', 'Noah', 12345);

-- 열을 지정하여 INSERT 구문
-> INSERT INTO [테이블명](열, ..._ VALUES [값, ..]

INSERT INTO salaries(yearID, teamID, lgID, playerID, salary)
VALUES (2020, 'KOR', 'NL', 'Noah2', 54321);
2020

테이블상태가 Null일 때 Defualt를 정해준 테이블 상태라면 INSERT시 생략가능하다
테이블 설계 상태에 따라 다름

DELETE

-- DELETE FROM [테이블명] WHERE[조건]

DELETE FROM salaries
WHERE playerID = 'Noah';

PlayerID 가 Noah인 행은 사라졌다.

UPDATE

UPDATE [테이블명] SET [열 = 값,] WHERE[조건]

UPDATE salaries
SET salary = salary * 2 
WHERE teamID = 'KOR'

teamID가 KOR일 행의 Slary를 2배 시켜주었다.

DELETE VS UPDATE
한번 DELETE 하면 복구 시키기 매우 어렵다.
UPDATE를 통한 상태관리로 논리적으로 삭제가 실제 개발에서는 더 많이 사용.

USE BaseballData;

--  SubQuery 
-- Select in SQL

--  highest salary player

SELECT TOP 1*
FROM salaries
ORDER BY salary DESC;

-- rodrial01
SELECT *
FROM players
WHERE playerID = 'rodrial01';

--> USING SUQQUERY

SELECT *
FROM players
WHERE playerID = (SELECT TOP 1 playerID FROM salaries ORDER BY salary DESC);


-- what if not just 1 but several value existe ? 
-- mutiple -> IN -> any value

SELECT *
FROM players
WHERE playerID IN (SELECT TOP 20 playerID FROM salaries ORDER BY salary DESC);

--
SELECT (SELECT COUNT(*) FROM players) AS playerCount, (SELECT COUNT(*) FROM batting) as BattingCount;

SELECT *
FROM salaries
ORDER BY yearID DESC;

-- INSERT INTO
INSERT INTO salaries
VALUES (2020, 'KOR', 'NL', 'DEV KIM', (SELECT MAX(salary) FROM salaries));