#3 SQL

데이터베이스에서 SQL을 사용하면

• 데이터 조회, 수정, 삭제처럼 조작(DML, Data Manipulation)하거나,
• 데이터 스키마를 정의(DDL, Data Definition)하거나,
• 데이터 접근권한등을 설정(DCL, Data Control)할 수 있습니다.

국제 표준 협회(ANSI)에서 만든 SQL 표준문법이 존재해서, 데이터베이스마다 사용법은 비슷합니다.

다만 특정 DB에 특화된 기능들은 표준문법에 없어서 호환이 안되는데, 이를 DB 방언 (Database Dialect)라고 합니다.

 

# 훑어보기

조회는 이렇게 할 수 있다.

SELECT col1, col2, col3, ...  -- 조회할 칼럼을 선택합니다.
FROM table1                   -- 테이블을 선택합니다.
WHERE col4 = 1 AND col5 = 2   -- 데이터 조건을 겁니다.
GROUP BY …                    -- 특정 칼럼 값을 기준으로, 그룹으로 묶습니다.
HAVING count(*) > 1           -- GROUP BY와 함께 사용되며, 집계함수를 이용해 조건을 겁니다.
ORDER BY col2                 -- 선택한 데이터를 정렬합니다.

-- 부서별 사원수 조회
SELECT '2005년' year,  deptno 부서번호,  COUNT(*) 사원수
 FROM emp                -- 테이블명 emp
 GROUP BY deptno         -- deptno를 기준으로 그룹핑
 ORDER BY COUNT(*) DESC; -- 정렬
 
/* 
YEAR     부서번호     사원수
------ ---------- ----------
2005년         30          6
2005년         20          5
2005년         10          3
*/

SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균", ROUND(SUM(sal)) "급여합계"
FROM emp
GROUP BY deptno;
 
  DEPTNO   COUNT(*)    급여평균    급여합계
-------- ---------- ---------- ----------
      30          6       1567       9400
      20          5       2175      10875
      10          3       2917       8750

 

데이터는 이렇게 추가한다.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
 
-- Value의 개수가 테이블 칼럼 개수와 같다면 생략해도 상관없다. 순서대로 들어간다
INSERT INTO table_name -- N 개의 칼럼을 가진 테이블
VALUES (value1, value2, value3, ... valueN);

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
 
INSERT INTO Customers ( Address, City, PostalCode, Country)
VALUES ('Skagen 21', 'Stavanger', '4006', 'Norway');

-- 개행은 상관없다.
INSERT INTO table1
       (id, first_name, last_name)
VALUES (1, 'Rebel', 'Labs');

-- 다른 테이블에서 조회한 내용을 삽입할 수도 있다.
INSERT INTO table1 (id, first_name, last_name)
  SELECT id, last_name, first_name FROM table2

 

삭제와 수정은 이렇게 한다.

참고로 조건에 해당하는 모든 레코드를 업데이트한다. 하나만 업데이트하고 싶다면 고유한 ID를 이용하자.

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

DELETE 
FROM Customers 
WHERE CustomerName='Alfreds Futterkiste';

-- Customers 테이블 데이터 전부 삭제
DELETE FROM Customers;

-- 참고로 AS를 이용하여 결과로 받을 칼럼 이름을 바꿀 수 있다
-- 숫자가 아닌 레코드 값은 0으로 취급한다.
SELECT MIN(Column) AS NewColumnName
FROM Table;
 
SELECT MAX(Price) AS LargestPrice
FROM Products;
 
SELECT COUNT(ProductID)
FROM Products;
 
SELECT AVG(Price)
FROM Products;
 
SELECT SUM(Quantity)
FROM OrderDetails;

 

# 0. AS ( 별명 짓기 )

-- 결과로 받아올 칼럼이나 테이블의 이름을 바꿀 수 있다.
SELECT column_name AS alias_name
FROM table_name;

-- 테이블에 별명을 지을 수도 있다.
SELECT MyTable.column1, MyTable.column2
FROM table_name AS MyTable;

-- 여러개는 , 로 구분
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

-- 칼럼이름에 공백이 있다면 [~] 나 "~" 로 감싸야한다.
SELECT ContactName AS [Contact Person]
FROM Customers;

다음과 같이 여러 칼럼을 하나로 만들 수도 있다.

여러 칼럼이 Address로 합쳐진 모습

나중에 배우겠지만, 여러 테이블을 조인할때 테이블 별명을 이용하면 편하다.

SELECT o.OrderID, o.OrderDate, c.CustomerName -- 각각의 테이블에서 필요한 칼럼을 추출한다.
FROM Customers AS c, Orders AS o -- 테이블에 c, o 라는 별명을 지어주었다.
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID; -- 조건

 

---

# 1. Select

/*  Select 열이름 From 테이블 이름 */
SELECT column1, column2, ...
FROM table_name;

/* 한줄로 적어도 된다. 모든 열을 가져오고 싶으면 * 을 사용한다. */
SELECT * FROM table_name;

-- Customers 테이블에 있는 모든 열을 가져온다.
SELECT * FROM Customers;

---

# 1-1. Select Distinct

Select 문에 Distinct를 붙이면 중복을 제거한 테이블을 얻을 수 있다. 참고로 레코드 중복 제거이다. (한 행의 모든 값이 같은 경우 중복으로 판단)

SELECT DISTINCT Country FROM Customers;

-- 레코드의 개수를 얻고싶다면 COUNT 함수를 이용하자.
SELECT COUNT(DISTINCT Country) FROM Customers;

-- 보통은 기본값이라 생략하지만, Select ALL 이라고 적어도 동작하긴 한다.
SELECT ALL Country FROM Customers; -- 일반 Select 문
SELECT DISTINCT Country FROM Customers; -- 중복제거 Select 문

 

# 1-2 상위 n개 Select

테이블의 상위 레코드 일부만 보고싶으면 보통 LIMIT를 사용하는데, 이는 데이터베이스마다 명령어가 조금씩 다르다.

-- MYSQL
SELECT column_name(s)
FROM table_name
WHERE condition --> 생략가능
LIMIT 개수;

-- Orcale v12 (옛날에는 Fetch 대신 Where ROWNUM <= 개수 사용)
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) --> 생략가능
FETCH FIRST 개수 ROWS ONLY;

---

# 2. Where

Select, Update, Delete 등을 사용할 때 적용할 테이블의 조건을 지정 할 수 있다.

SELECT * FROM Customers
WHERE CustomerID=1;

-- 텍스트는 작은 따음표('~') 를 이용하여 입력한다.
SELECT * FROM Customers
WHERE Country='Mexico';

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Where에 사용가능한 연산자들은 아래와 같다. 조건들은 AND OR NOT 등의 논리 연산으로 묶을 수 있다.

-- BETWEEN a AND b은 연속적인 숫자를 나타낼 때 사용된다 (a <= 값 <= b)
SELECT * FROM USER WHERE height >=180 AND height <=185 ;
SELECT * FROM USER BETWEEN 180 AND 185;

-- 텍스트로 범위를 지정해 테이블에서 연속된 레코드 값들을 가져올수도있다.
SELECT * FROM Products
WHERE ProductName BETWEEN 'Boston Crab Meat' AND 'Longlife Tofu'
ORDER BY ProductName;

-- #1999/09/09# 을 이용하면 날짜도 입력가능
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;

-- 연속적이지 않다면 IN을 이용하면 된다. (A, B, C)
SELECT * FROM USER WHERE address ='A' OR address ='B' OR address ='C';
SELECT * FROM USER WHERE address IN ('A', 'B', 'C');

 

--- 가격이 10~20 이고 CategoryID가 (1,2,3)이 아닌 레코드.
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);

# 2-1 LIKE (문자 패턴매칭)

참고로 패턴매칭에 사용되는 와일드카드는 DB마다 조금씩 다르다.

-- 문자열을 정규표현식처럼 검색하고싶을 때 LIKE를 사용한다. 
-- [%] 와 [_] 기호를 사용하며, 정규표현식으로 같은 의미인 [*] 와 [?]를 같이 지원해주기도 한다.

--> [??] 이름이 2글자인 단어 찾기
SELECT * FROM USER WHERE name = '__';
SELECT * FROM USER WHERE name LIKE '__';

--> [김*] 김으로 시작하는 모든 단어
SELECT * FROM USER WHERE name = '김%'; 
SELECT * FROM USER WHERE name LIKE '김%'; 
 
--> [.김*] 2번째 글자가 김으로 시작하는 모든 단어 찾기 
SELECT * FROM USER WHERE name = '_김%';
SELECT * FROM USER WHERE name LIKE '_김%';
 
--> [..김*] 3번째 글자가 김으로 시작하는 모든 단어 찾기 
SELECT * FROM USER WHERE name = '__김%';
SELECT * FROM USER WHERE name LIKE '__김%';
 
--> [*김*] a이라는 글자가 포함된 모든 단어 찾기 
SELECT * FROM USER WHERE name = '%a%';
SELECT * FROM USER WHERE name LIKE '%a%';

 

# 2-2 IS NULL

참고로 Where절에 NULL 값 여부를 판별할 수도 있다.

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

---

# 3. Order By

데이터를 오름차순(Ascending)이나 내림차순(Descending)으로 정렬 할 수 있다. 기본값은 오름차순이며 정렬 기준이 여러개인 경우 먼저 적은 열부터 적용한다. (값이 동일하면 다음에 적은 열 적용)

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

-- ASC, DESC는 전체 적용이 아니라 열(column)마다 적용된다.
SELECT * FROM Customers
ORDER BY Country DESC, Address DESC;

---

# 4. Insert Into

테이블에 레코드를 삽입할 때 사용된다. 나중에 배우겠지만 테이블을 만들 때 AUTO_INCREMENT 필드를 지정해놓으면, 따로 지정하지 않아도 새로운 레코드가 들어올 때 마다 자동으로 다음 번호가 부여되게 만들 수도있다.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

-- Value의 개수가 테이블 칼럼 개수와 같다면 생략해도 상관없다. 순서대로 들어간다
INSERT INTO table_name -- N 개의 칼럼을 가진 테이블
VALUES (value1, value2, value3, ... valueN);

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

INSERT INTO Customers ( Address, City, PostalCode, Country)
VALUES ('Skagen 21', 'Stavanger', '4006', 'Norway');

 

# 5. Update & Delete

Where 조건에 해당하는 레코드의 값을 바꾼다. 동일한 값이 여러개라면 모두 적용된다.

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

DELETE FROM Customers 
WHERE CustomerName='Alfreds Futterkiste';

참고로 조건이 없으면 모든 레코드를 삭제(=테이블 삭제)한다.

-- Customers 테이블 삭제
DELETE FROM Customers;

 

# 6. 산술연산 (Min, Max, Count, Avg, Sum)

-- 참고로 AS를 이용하여 결과로 받을 칼럼 이름을 바꿀 수 있다
-- 숫자가 아닌 레코드 값은 0으로 취급한다.
SELECT MIN(Column) AS NewColumnName
FROM Table;

SELECT MAX(Price) AS LargestPrice
FROM Products;

SELECT COUNT(ProductID)
FROM Products;

SELECT AVG(Price)
FROM Products;

SELECT SUM(Quantity)
FROM OrderDetails;

---

# 7. IN을 이용한 서브쿼리

Where IN 에서 서브쿼리를 이용할 수도 있다.

-- 조건을 직접 지정하는 방법
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

-- 서브쿼리를 이용해 조건을 만드는 방법
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

---

# 8. Join

연관된 여러 테이블을 하나로 결합할 때 조인을 사용한다.

- Inner Join

테이블의 공통된 요소를 통해 결합한다. 공통요소가 없으면 값이 없어지며 칼럼 기준은 ON 을 이용해서 정한다.

SELECT table1.col1, table1.col2, ..., table2.col1, table2.col2, ...
FROM table1 [table1의 별칭]
JOIN table2 [table2의 별칭] ON table1.col1 = table2.col2

 

---

- Left Join (Left Outer 조인)

왼쪽 테이블의 데이터를 전부 살리고, 왼쪽을 기준으로 오른쪽 테이블을 결합한다.

참고로 outer 조인을 하면 null 값이 생길 수 있다. (오른쪽 테이블 table2에는 없는 칼럼이 있을 수 있으니까)

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

---

- Right Join (Right Outer 조인)

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

---

- Full Join (Full Outer 조인)

두 테이블의 모든 데이터를 살리는 방법.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

---

- Self Join

기존의 테이블을 다른형태로 바꿀 때 (ex 학생별 수강신청 정보 -> 과목별 학생정보) 사용한다.

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City 
ORDER BY A.City;

 

 

..작성중인 글입니다.