SQL(Structured Query Language) 기초 & 기초 실습

1. SQL이란?

관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어이다.

① DDL (데이터 정의 언어) : 데이터를 담는 그릇을 정의하는 언어, 관계형 데이터베이스의 구조 정의, 속성 등의 타입에 대한 전반적인 구조 정의 언어
② DML (데이터 조작 언어) : 데이터의 생명주기(삽입, 수정, 삭제 처리)를 제어하는 언어
③ DCL (데이터 제어 언어) : 데이터에 대한 액세스(접근권한)를 제어하는 언어

2. DDL

CREATE	ALTER
- 데이터베이스 오브젝트를 생성하는 구문  - 데이터 베이스 생성, 테이블 생성 등 구조적인 부분에 대한 구문	- 데이터 오브젝트를 변경. - 만들어진 오브젝트가 완벽하지 않을 때 변경 가능한 구문

DROP	TRUNCATE
- 데이터 오브젝트를 삭제. - 아예 없애 버리는 구문	- 데이터 오브젝트 내용 삭제. - Delete랑은 다름

3. DML - table 안에서 사용함

INSERT	SELECT	UPDATE	DELETE
테이블의 신규 내용 삽입	테이블의 내용 조회	테이블의 내용 변경	테이블의 내용 삭제

crud 데이트를 만들고 읽고 수정하고 삭제하고 조회 할 수 있다.

4. DCL -권한에 대한 명령어

GRANT - DCL	REVOKE - DCL	COMMIT - TCL	ROLLBACK - TCL	CHECHPOINT - TCL
데이터베이스 사용자 권한 부여	데이터베이스 사용자 권한 회수	트랜잭션 확정, 완료	트랜잭션 취소	복귀지점 설정

TCL은... 좀 더 있다 배우는 걸로....

mysql은 auto commit 

5. SQL 실습

1] 데이터베이스 생성 DDL

① MySQL 에 데이터 베이스 생성
    CREATE   DATABASE  ksmart35db                     DEFAULT CHARACTER SET UTF8
    생성해라 DATABASE  생성할 데이터베이스 이름  옵션(인코딩 방식)

② MySQL 에 생성된 모든 데이터 베이스 조회
    SHOW DATABASES;

③ MySQL 에 사용자 아이디 생성 
    CREATE USER 'ksamrt35id'@'%' IDENTIFIED BY 'ksmart35pw';

생성해라 사용자 아이디 비밀번호

④ MySQL 에 사용자 아이디 삭제
    DROP USER ksmart35id@'%';

2] 데이터베이스 권한 DCL

① MySQL 에 생성된 DB에 사용자 권한 부여
   GRANT ALL PRIVILEGES ON ksmart35db.* TO 'ksmart35idx'@'%';

② 변경된 내용을 메모리에 반영  -> 이거 안하면 저장 안될수도 있음
    FLUSH PRIVILEGES;

③ MySQL 에 생성된 DB에 사용자 권한 회수
    REVOKE ALL ON ksmart35db.* FROM ksmart35id;

3] 테이블 생성 DDL

① MySQL 에 생성된 DB 선택
    USE ksmart35db;

② 선택된 DB에 테이블 생성

    CREATE TABLE userTb 
    ( u_id 	VARCHAR(100)	NOT NULL PRIMARY KEY(식별자) 
     ,u_pw 	VARCHAR(100)	NOT NULL  
     ,u_name 	VARCHAR(100)	NOT NULL 
     ,u_birth 	DATE 
     ,u_add 	VARCHAR(100)	NOT NULL 
     ,u_mobile1 VARCHAR(100) 
     ,u_mobile2 VARCHAR(100) 
     ,u_date	DATE
     ); 

4] 테이블 데이터 삽입 DML

INSERT INTO usertb
( u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date
)VALUES(
'id001','pw001','홍01',NOW(),'덕진동','010','00010001',NOW()
);

5] 테이블 데이터 조회 DML 

① 모든 속성 선택

SELECT 
* 
FROM usertb;

② 조회 하고 싶은 속성 입력

SELECT
   u_id  AS '아이디'
  ,u_pw AS '비밀번호'
  ,u_name AS '이름'
  ,u_birth AS '생년월일'
  ,u_add AS '주소'
  ,u_mobile1 AS '연락처1'
  ,u_mobile2 AS '연락처2'
  ,u_date AS '등록날짜'
FROM
  usertb;

③ 특정 데이터 조회

SELECT
   u_id  AS '아이디'
  ,u_pw AS '비밀번호'
  ,u_name AS '이름'
  ,u_birth AS '생년월일'
  ,u_add AS '주소'
  ,u_mobile1 AS '연락처1'
  ,u_mobile2 AS '연락처2'
  ,u_date AS '등록날짜'
FROM
  usertb
WHERE
  u_id='id010';  <- id가 id010이라는 데이터를 조회하겠다.

1. select 찾아줘라  2. u_id 등등 속성에 대해서 3. from 어디 테이블에서 4. where 조건값을

1. from 어느 테이블에  2. where 어떤 조건 값을 3. u_id 등등 속성에 대해서 4. select 보여줘라

6] 테이블 데이터 변경 DML

UPDATE usertb                 <- 수정명령어
SET                                <- 수정할 내용
   u_pw='pw011'
  ,u_name='홍길동'
  ,u_birth=NOW()
  ,u_add='인후동'
  ,u_mobile1='010'
  ,u_mobile2='00110011'
  ,u_date=NOW()
WHERE u_id='id010';     <- 수정할 데이터영역

7] 테이블 데이터 삭제 DML

① 삭제할 데이터행 지정 - 행 하나 하나 지우는 명령어

DELETE FROM usertb
WHERE
   u_id='id010';

② 테이블의 모든 데이터 삭제 - 테이블을 아예 없애버리고 다시 생성
    DELETE FROM usertb;

③ 테이블의 모든 데이터 삭제 DDL 

    TRUNCATE TABLE usertb;

8] 테이블 데이터 여러행 삽입

INSERT INTO usertb (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)
VALUES
('id001', 'pw001', '홍01', '2020-03-10', '덕진동', '010', '00010001', '2020-03-10'),
('id002', 'pw002', '홍02', '2020-03-10', '호성동', '010', '00020002', '2020-03-10'),
('id003', 'pw003', '홍03', '2020-03-10', '금암동', '010', '00030003', '2020-03-10');

9] 테이블 복제 DDL, DML

① 특정 테이블의 특정 컬럼만 복제

CREATE TABLE usertb_backup
(SELECT
    b.u_id
   ,b.u_pw
   ,b.u_name
 FROM
   usertb AS b);

② 특정 테이블 전체 복제

CREATE TABLE usertb_backup_all
(SELECT
      *
 FROM
    usertb);

10] 테이블 삽입 키 중복시 데이터 수정 DML

INSERT INTO usertb (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)
VALUES
('id001', 'pw001', '홍01', '2020-01-10', '덕진동', '010', '00010001', '2020-03-10')
ON DUPLICATE KEY UPDATE <-수정할때 set, update 구문
u_birth = '2019-01-10’;

11] 테이블 데이터 삽입 키 중복 시 데이터 무시

①

INSERT INTO usertb (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)
VALUES('id010','pw010','홍10','2020-10-10','덕진동','010','00010001','2020-03-10');
INSERT INTO usertb (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)
VALUES('id011','pw011','홍11','2020-11-10','덕진동','010','00010001','2020-03-10');

② IGNORE 사용

INSERT IGNORE INTO usertb (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)
VALUES('id010','pw010','홍10','2020-10-10','덕진동','010','00010001','2020-03-10');
INSERT IGNORE INTO usertb (u_id, u_pw, u_name, u_birth, u_add, u_mobile1, u_mobile2, u_date)
VALUES('id011','pw011','홍11','2020-11-10','덕진동','010','00010001','2020-03-10');

12] 테이블 데이터 조회 정렬 ORDER BY

SELECT
   u_id         AS '아이디’
  ,u_pw        AS '비밀번호’
  ,u_name     AS '이름’
  ,u_birth      AS '생년월일’
  ,u_add       AS '주소’
  ,u_mobile1 AS '연락처1’
  ,u_mobile2 AS '연락처2’
  ,u_date      AS '등록날짜'
FROM
   usertb
ORDER BY u_id DESC;    <- DESE 는 내림차순, ACS는 오름차순

13] 테이블 데이터 출력 개수 제한 LIMIT

SELECT
   u_id  AS '아이디’
  ,u_pw AS '비밀번호’
  ,u_name AS '이름’
  ,u_birth AS '생년월일’
  ,u_add AS '주소’
  ,u_mobile1 AS '연락처1’
  ,u_mobile2 AS '연락처2’
  ,u_date AS '등록날짜'
FROM
  usertb
ORDER BY u_id DESC
LIMIT 5;

LIMIT는 인자값을 두 개로 사용 할 수 있고, 0부터 시작한다.

14] 테이블 중복 데이터 하나만 조회 DISTINCT

① 테이블의 데이터 조회

SELECT
   DISTINCT u_add AS '주소'
FROM
   usertb;

② 테이블의 데이터 한글 정렬 

SELECT
   DISTINCT u_add AS '주소’
FROM 
   usertb
ORDER BY CONVERT(u_add USING BINARY);

15] 테이블 데이터 그룹 출력 GROUP BY

SELECT
   u_add AS '주소’
  ,COUNT(u_id) AS '고객분포수'
FROM
   usertb
GROUP BY u_add;

16] 테이블 데이터 그룹 출력 조건식 HAVING

SELECT
   u_add AS '주소’
  ,COUNT(u_id) AS '고객분포수'
FROM
   usertb
GROUP BY u_add
HAVING COUNT(u_id) > 1;