[MySQL] 06. SQL 기본

SELECT FROM WHERE

• SELECT 이전 USE를 사용하면 모든 쿼리를 해당 DB에서 수행한다.
• USE로 DB를 선택하지 않았다면 '데이터베이스이름.테이블이름'형식으로 표현 가능
• SELECT 뒤에 조회하고 싶은 열, FROM 이후 테이블/뷰 등을 입력
• SELECT시 AS를 사용하면 별칭 가능 (ex.  SELECT first_name AS 이름, ...)
• WHERE절에 사용 가능
  • 관계연산자
  • BETWEEN
  • AND
  • IN()
    • ex)  ... WHERE addr IN ('경남', '전남', '경북');
  • LIKE
    • LIKE '김%' -> 김이 제일 앞글자
    • LIKE '_가네' -> 맨 앞에 아무 글자 한 글자
• SubQuery
  • 쿼리 안에 하위 쿼리를 둔다.

SELECT name, height FROM usertb1 
WHERE height > (SELECT height FROM usertb1 Where Name = '가나다');

• ANY

SELECT name, height FROM usertb1 
WHERE height > ANY (SELECT height FROM usertb1 Where Name = '가나다');
-- 나온 결과가 하나가 아니라면 그중 가장 작은 것 보다 크면 된다는 의미
-- 서브쿼리의 여러 결과 중 한 가지만 만족해도 된다.

• ALL

SELECT name, height FROM usertb1 
WHERE height > ALL (SELECT height FROM usertb1 Where Name = '가나다');
-- 나온 결과가 하나가 아니라면 그중 가장 작은 큰 보다 크면 된다는 의미
-- 나온 결과를 모두 만족시켜야 된다.

• SOME
  • ANY와 동일한 의미로 사용된다.
• ORDER BY
  • 기본적으로 오름차순으로 정렬된다. (ASC)
  • 내림차순으로 정렬하기 위해선 DESC를 적어주면 된다.
  • SELECT, FROM, WHERE, GROUP BY, HAVING보다 뒤에 나와야 한다
  • MySQL 성능을 떨어뜨릴 소지가 있어 되도록 사용하지 않는 것이 좋다.
• DISTINCT
  • 중복된 것은 한 개씩만 보여주면서 출력된다.
• LIMIT
  • 출력 개수를 제한해준다
  • 형식
    • LIMIT 시작, 개수 
    • LIMIT 개수 OFFSET 시작
• 테이블 복사
  • CREATE TABLE 새로운 테이블 (SELECT 복사할 열 FROM 기존 테이블)로 테이블을 복사할 수 있다.

데이터베이스 개체 이름 규칙

• 알파벳, 숫자, $,_ 사용 가능
• 최대 64자
• 예약어는 사용할 수 없다.
• 공백은 안되지만 `` 사용하면 공백이 가능하다
• 짧으면서도 파악할 수 있는 것이 좋다
• Linux에서는 DB이름과 테이블 이름 모두 소문자로 사용해야 한다.

추가 명령어

• SHOW DATABASES
  • 현재 서버에 어떤 DB들이 있는지 조사 가능
• SHOW TABLE STATUS
  • 테이블의 정보를 조회할 수 있다.

GROUP BY 및 HAVING 그리고 집계함수

• GROUP BY절
  • 그룹으로 묶어주는 역할
  • 보통 집계함수와 같이 쓰인다
  • 예시) 사용자별로 구매한 개수를 출력하고싶다면

SELECT userID, SUM(amount) FROM buytb1 GROUP BY userID;

• 집계함수 종류
  • AVG()
  • MIN()
  • MAX()
  • COUNT()
  • COUNT(DISTINCT)
    • 중복은 1개만 인정하며 행의 개수를 센다
  • STDEV()
    • 표준편차
  • VAR_SAMP()
    • 분산
• HAVING 절
  • 꼭 GROUP BY절 다음에 나와야 한다.
  • WHERE절에 집계함수를 작성하고 싶을 때 사용한다.

SELECT userID, SUM(price*amount) 
FROM buytb1 
GROUP BY userID 
HAVING SUM(price*amount) > 1000;

• ROLLUP
  • 총합 또는 중간 함계가 필요할 때 사용
  • 분류(group name)별 합계를 보고싶을 때 사용

SELECT groupName, SUM(price * amout)
FROM buytb1
GROUP BY groupName
WITH ROLLUP;

SQL의 분류

DML(Data Manipulation Language)

데이터를 조작(선택, 삽입, 수정, 삭제) 시 사용되는 언어

• SELECT
• INSERT
  • 테이블 이름 다음에 나오는 열은 생략이 가능
  • 생략 할 경우 VALUES다음에 나오는 값들의 순서 및 개수는 정의된 열 순서 및 개수와 같아야 한다.
  • INSERT INTO 테이블(열1, 열2, ...) VALUES (값1, 값2, ...)
  • AUTO_INCREMENT
    • INCERT 시 해당 열이 없다고 생각하고 입력하면 된다.(NULL값을 입력)
    • 자동으로 1부터 증가하는 값을 입력해주기 때문에
    • PRIMARY KEY 혹은 UNIQUE로 지정해주어야 된다.
    • SELECT LAST_INSERT_ID(); 로 마지막 입력된 값을 알 수 있다.
    • 특정 번호부터 지정하고 싶다면 " AUTO_INCREMENT= 특정번호 " 를 사용
  • SELECT문과 함께 사용하면, 한번에 대량의 데이터를 입력할 수도 있다.
  • IGNORE를 함께 사용하면, 중간에 입력이 실패해도 나머지 데이터들은 추가로 입력한다.
  • ( INSERT IGNORE INTO memberTBL VALUES('a', 'b', ...); )
  • ON DUPLICATE UPDATE로 PK가 중복되지 않으면 일반 INSERT 중복되면 UPDATE문이 수행되도록 할 수 있다.
• UPDATE
  • WHERE절은 생략이 가능하지만, 생략 시 테이블 전체의 행이 변경된다.
• DELETE
  • 행 단위로 삭제를 진행한다
  • WHERE절이 생략되면 전체 데이터를 삭제한다
    • 트랜잭션 로그를 기록하는 작업으로 인해 삭제가 오래 걸린다
    • DROP문은 트랜잭션 없이 삭제하므로 속도가 빠르다
    • TRUNCATE 또한 로그를 기록하지 않기에 속도가 빠르다
    • 테이블 자체를 삭제시엔 DROP, 테이블 구조는 남겨두고싶다면 TRUNCATE가 효율적이다.
  • LIMIT구문과 함께 사용하여 상위 몇 건만 삭제할 수도 있다.
• 트랜잭션이 발생하는 SQL

DDL(Data Definition Language)

데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할

• CREATE
• DROP
• ALTER
• 트랜잭션을 발생시키지 않는다 (ROLLBACK, COMMIT을 시킬 수 없다)
• 실행 즉시 MySQL에 적용

DCL(Data Control Language)

사용자에게 권한을 부여하거나 빼앗을 때

• GRANT
• REVOKE
• DENY

WITH절과 CTE (Common Table Expression)

CTE

• 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있다
• 더 간결한 식으로 보여줄 수 있다
• 중복 CTE가 허용된다.
• 비재귀적, 재귀적 두 가지 방법이 있다.
• 비재귀적 CTE
  • 재귀적이지 않은 CTE

-- 형식
WITH CTE_테이블이름(열 이름)
AS
(
    <쿼리문>
)
SELECT 열 이름 FROM CTE_테이블이름;

-- 예시
-- 총 구매액이 많은 사용자 순서로 정렬하고싶다면? (ORDER BY문으로 작성 할 수 있지만 CTE로 간단하게)
WITH abc (userid, total)
AS
(SELECT userid, SUM(price*amount)
FROM buytb1 GROUP BY userid)
SELECT * FROM abc ORDER BY total DESC;

-- 중복 CTE라면
WITH
AAA (컬럼들)
AS (AAA 쿼리문),
    BBB (컬럼들)
    AS (BBB 쿼리문),
        CCC (컬럼들)
        AS (CCC의 쿼리문)
SELECT * FROM [AAA or BBB or CCC]
-- CCC는 AAA,BBB를 참조할 수 있지만 AAA가 BBB, AAA가 CCC, BBB가 CCC등은 참조가 안된다
-- 아직 정의되지 않은 CTE는 참조할 수 없다.