[DB] SQL Tutorial 💯️

goal

SQL Tutorial 💯️

SQL공식문서는 👉️ 여기를 참고

튜토리얼에는 DB를 생성한 후, 생성된 데이터를 다루는, 즉 table에 대해서 먼저 설명하지만, 개념이 생소한 나를 위해 DB 단부터 알아보자!

SQL Database

⭐️데이터베이스 생성

CREATE DATABASE [데이터베이스 이름];

⭐️데이터베이스 삭제

DROP DATABASE [생성했던 데이터베이스 이름];

기존 SQL 데이터베이스의 전체 백업

BACKUP DATABASE [데이터베이스 이름]
TO DIST = "[파일경로]";

⭐️테이블 생성 / datatype은 여기를 참고 - 공홈

CREATE TABLE [테이블 이름] (
column1 datatype,
column2 datatype,
column3 datatype,
....
);

• 예)
  CREATE TABLE topic(
-> id INT(11) NOT NULL AUTO_INCREMENT,
-> title VARCHAR(100) NOT NULL,
-> description TEXT NULL,
-> created DATETIME NOT NULL,
-> PRIMARY KEY(id));

⭐️테이블 삭제

DROP TABLE [생성했던 테이블 이름];

⭐️테이블의 column 변경 (추가, 삭제, 수정)

• 추가
  ALTER TABLE [테이블 이름]
ADD [column이름] [해당 데이터타입]; (ex. VARCHAR(100))
• 삭제
  ALTER TABLE [테이블 이름]
DROP COLUMN [column이름];
• 수정
  ALTER TABLE [테이블 이름]
MODIFY COLUMN [column이름] [해당 데이터타입]; (MySQL기준 => sql마다 다르니 확인 필요)

⭐️ 반드시 있어야 하는 column에는 NOT NULL Constraint

• 기본적으로 column은 NULL 값을 갖는다.
• 따라서, NOT NULL은 column이 NULL값을 갖지 않도록 강제한다.
• NOT NULL을 넣으면, field에 항상 값이 추가되어야 하므로, 값이 없으면, 새로운 record의 추가 삽입, 수정 등이 불가하다.
• 예)
  CREATE TABLE topic(
-> id INT(11) NOT NULL
...

⭐️ column의 값 중복여부 확인에는 UNIQUE Constraint

• PRIMARY KEY 는 자동으로 UNIQUE를 가진다.
• 테이블 당 하나의 PRIMARY KEY constraint만 가질 수 있다.
• 예)
  CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

⭐️ 각 테이블의 고유한 record를 식별하는 PRIMARY KEY constraint

• 하나의 테이블은 하나의 PRIMARY KEY를 가지다.
• PRIMARY KEY는 single or multiple columns로 구성될 수 있다.
• 예) mysql기준이며, 변경시 검색으로
  CREATE TABLE topic(
-> id INT(11) NOT NULL AUTO_INCREMENT,
-> title VARCHAR(100) NOT NULL,
-> PRIMARY KEY (id));

⭐️ 두 테이블을 연결할 때 사용되는 FOREIGN KEY constraint

• FOREIGN KEY는 테이블 간 연결을 파괴하는 것을 방지할 수 있다.
• 예)
  CREATE TABLE Orders (
-> OrderID int NOT NULL,
-> OrderNumber int NOT NULL,
-> PersonID int,
-> PRIMARY KEY (OrderID),
-> CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
-> REFERENCES Persons(PersonID)
-> );

⭐️ 기본값 DEFAULT constraint

• DEFAULT는 column에 대한 기본값을 제공하다.
• 다른 값을 지정하지 않으면 모든 새 record에 기본값이 추가된다.

⭐️ 자동 증가 AUTO_INCREMENT

• AUTO_INCREMENT를 사용하면 새 record에가 테이블에 삽입될 때 고유 번호가 자동으로 생성된다.

⭐️ 날짜와 관련된 Date 데이터타입

• 시간까지 고려하면 훨씬 복잡해질 수 있음 - 검색 ㄱㄱ
• 예) mysql
  DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: a unique number

---

1. SELECT

• 데이터를 선택할 때 사용
• 데이터는 result-set이라는 result table에 저장됨
• SELECT CustomerName, City FROM Customers;

// 부분
SELECT column1, column2, ...
FROM table_name;

// 전체
SELECT * FROM table_name;

2. WHERE

• records을 필터링할 때 사용
• 저장된 조건을 충족하는 records만 추출하는데 사용됨
• SELECT * FROM Customers WHERE Country='Mexico';

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• Operators
  

3. And, Or, Not

• WHERE과 결합할 수 있음
• SELECT * FROM Customers WHERE Country = "Germany" AND City = "Berlin"
• SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');
• SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
--
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
-- 
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

4. Order By

• result-set을 오름차순(ascend) or 내림차순(descend)으로 정렬(sort)하기 위해 사용
• 기본적으로는 오름차순으로 정렬 / 내림차순은 DESC 사용
• SELECT * FROM Customers ORDER BY Country, CustomerName; : 기본적으로 Country에 따라 정렬한 후, 각 나라 안에서 CustomerName 오름차순으로 정렬

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

5. Insert Into

• 새로운 recodes를 삽입할 때 사용
• 2가지 방법
• 특정 열에만 데이터를 삽입하면, 각 column에 해당하는 값은 추가되고, 지정하지 않은 열에는 null

1. 삽입할 열 이름과 값을 모두 지정

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

2. 테이블의 모든 열에 대한 값을 추가하는 경우, 쿼리에서 열 이름을 지정할 필요 X

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

6. Null Values

• 값이 없는 field
• ZERO VALUES와는 따름
• 연산자를 사용해서 null values를 확인할 수 있음

// IS NULL - null값을 찾기 위해 사용한다 (null이 있는지 여부 파악)
SELECT column_names
FROM table_name
WHERE column_name IS NULL;

// IS NOT NULL - 비어있지 않은 값을 확인하기 위해 사용한다
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

7. Update

• 기존의 recodes를 수정하기 위해 사용
• UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1; : 하나씩 수정
• UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico'; : 여러개 수정
• UPDATE Customers SET ContactName='Juan'; : WHERE을 생략하면, 전체를 대상으로 수정

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

8. Delete

• 테이블에서 recodes를 삭제하기 위해 사용
• WHERE 절을 생략하면 테이블의 모든 레코드가 삭제되므로 주의할 것
• DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste'; : 조건삭제
• DELETE FROM table_name; : 전체삭제

DELETE FROM table_name WHERE condition;

9. Count

• COUNT : 지정된 기준과 일치하는 행 수를 반환
• AVG : 숫자 열의 평균 값을 반환
• SUM : 숫자 열의 총합을 반환

SELECT COUNT/AVG/SUM(column_name)
FROM table_name
WHERE condition;

10. Like

• WHERE 절의 열에서 지정된 패턴을 검색하기위해 사용
• %-백분율 기호는 0, 1 또는 여러 문자를 나타냅니다.
• _-밑줄은 단일 문자를 나타냅니다.

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

• LIKE 연산자
  
  • 'a%' : a로 시작하는 모든 값을 찾음
  • '%a' : a로 끝나는 모든 값을 찾음
  • '%or%' : any position에 or이 있는 모든 값을 찾음
  • '_r%' : 두번째 위치에 r이 있는 모든 값을 찾음
  • 'a_%' : a로 시작하고 길이가 2자 이상인 모든 값을 찾음
  • 'a__%' : a로 시작하고 길이가 3자 이상인 모든 값을 찾음
  • 'a%o' : a로 시작하고 o로 끝나는 모든 값을 찾음

11. Wildcards

• 문자열에서 하나 이상의 문자를 대체하기 위해 사용
• LIKE연산자와 함께 사용
• MS Access의 와일드카드 문자
  
• SQL Server의 와일드 카드 문자
  

12. Aliases

• 테이블 or 테이블 열에 임시로 별명을 제공하기 위해 사용
• 별명은 쿼리 기간 동안만 존재함

// Alias Column syntax
SELECT column_name AS alias_name
FROM table_name;

// Alias Table syntax
SELECT column_name(s)
FROM table_name AS alias_name;


// 여러개도 가능
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers

// 별칭에 공백이 포함된 경우, 큰 따옴표 or 대괄호가 필요함
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

// 여러 개의 열을 결합해서, 새로운 별칭(Adress)을 만듦
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;

// etc... 여러가지가 있음

13. Join

• 둘 이상의 테이블 사이의 관련 column을 기반으로 row를 결합하기 위해 사용

---

헷갈리는 SQL query 모음

• Select all the different values from the Country column in the Customers table.
  : SELECT DISTINCT Country FROM Customers;

• With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?
  : SELECT * FROM Persons WHERE FirstName LIKE 'a%'

• With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?
  : SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName<'Pettersen'

• With SQL, how can you insert a new record into the "Persons" table?
  : INSERT INTO Persons VALUES ('Jimmy', 'Jackson')

• With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?
  : INSERT INTO Persons (LastName) VALUES ('Olsen')

• How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?
  : UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

• Which operator is used to select values within a range?
  : BETWEEN

• Which operator is used to search for a specified pattern in a column?
  : LIKE

• Select all records where the value of the City column starts with the letter "a".
  : SELECT * FROM Customers WHERE City LIKE 'a%';

• Select all records where the value of the City column contains the letter "a".
  : SELECT * FROM Customers WHERE City LIKE '%a%;

• Select all records where the value of the City column starts with letter "a" and ends with the letter "b".
  : SELECT * FROM Customers WHERE City LIKE 'a%b';

• Select all records where the second letter of the City is an "a".
  : SELECT * FROM Customers WHERE City LIKE '_a%';

• Select all records where the first letter of the City is an "a" or a "c" or an "s".
  : SELECT * FROM Customers WHERE City LIKE '[acs]%';

• Select all records where the first letter of the City starts with anything from an "a" to an "f".
  : SELECT * FROM Customers WHERE City LIKE '[a-f]%';