SQL(DML, DDL, DCL) && 실습(mysql, cli)

SQL의 종류(✏️DML/✏️DDL/✏️DCL) *대소문자를 구별하지 않은, 데이터 같은 경우는 구별한다.

 

DML : 데이터 베이스의 테이블을 직접 조작하는 기능을 말함

👇 상세 설명 😎

Data Manipulation Language

- 테이블의 데이터를 CRUD하는 기능을 함

 

SQL문

INSERT

UPDATE

DELETE

SELECT

레코드 생성, 읽기, 갱신, 삭제

 

# 실습

🌱 SELECT 컬럼명 FROM 테이블명 WHERE 조건절;

select Name form city where CountryCode='kor' ;

 

 

🌱  INSERT INTO 테이블명(컬럼명) VALUES(값);

모든 컬럼에 대해서 지정될 경우 "테이블명(컬럼명)" 이 없어도 된다. 즉 위에 SQL문에서

insert into city values (10000, 'Sample', 'KOR','Test',100000);

라고 해도 된다.

 

 

🌱 UPDATE 테이블명 SET 컬럼명=값, ... WHERE 조건절;

city 테이블에서, ID가 10000인 애의 name을 "SanokeRevised"로 바꿔라

 

🌱 DELETE  FROM 테이블명 WHERE 조건절;

 

 

🔥 UPDATE와 DELETE 사용시 주의할 점!

 >> where 절에 매칭되는 값이 하나일 때는 문제가 없으나, 여러개의 레코드가 매칭될 떄는 모든 레코드가 수정되거나 삭제될 수 있으니 원하는 레코드인지 하나의 레코드가 맞는지 확인해야 한다.

>> select 하고 update, select 하고 delete 하는 습관을 길러야 한다!

 

 

 

🌱 🌱  DISTINCT

- Select문의 결과에서 특정 컬럼만 출력할 경우 중복된 값들이 나오면 이를 제거해서 표시하는 기능

SELECT DISTINCT 컬럼명1, 컬럼명2, ... FROM 테이블명

ex. 국가코드가 'KOR'인 도시들의 국가코드를 중복제거하여 표시하시오.

좌: disticnt , 우: 그냥 select

보통, 중복을 제거해서 실제 값이 얼마나 나왔는지 확인 할 떄 많이 사용한다. 

 

 

🌱 🌱  논리연산자(AND, OR, NOT)

- Select문의 조건절에 논리 조건 적용해서 적용할 수 있는 연산자

SELECT * FROM 테이블명 WHERE (NOT) 조건1 AND/OR (NOT)조건2 ... (not은 !=)

ex. 국가 코드가 'KOR' 이면서  인구가 100만 이상인 도시를 찾아라.

ex. 국가 코드가 'KOR', 'CHN', 'JPN'인 도시를 찾아라. // 국가 코드가 'KOR'이 아니면서 인구 100만 이상인것 찾아라 

 

 

🌱 🌱  논리연산자(IN, BETWEEN)

- '~ 중에서' , '어디에서 부터 ~ 어디까지' 

ex. city 테이블에서 국가코드가 'KOR'이고 인구가 100만 이상 500만 이하인 도시를 찾아라.

ex. 국가코드가 'KOR' 'CHN' 'JPN' 인 도시를 찾아라.

 

 

🌱 🌱  결과 정렬 (ORDER BY)

- select 문의 결과 값을 특정 컬럼 기준으로 오름차순/내림차순 정렬 (기본값은 오름차순 정렬임)

SELECT * FROM 테이블명 WHERE 조건절 ORDER BY 컬럼명 desc/asc

(컬럼 여러개이면 순서대로 1차 정렬, 2차 정렬 .. )

ex. city 테이블에서 국가코드와 인구수를 출력하라! 국가코드별 오름 차순, 동일 국가코드 일시에는 인구 수의 역순으로 표시해라.

 

 

🌱 🌱   결과값 일부 조회

Select 컬럼명1, 컬럼명2, … from 테이블명 where 조건절 limit 숫자

mySQL에서는 limit,  Oracle에서는 ROWNUM, SQLServer에서는 Top

ex. 국가코드가 ‘KOR’인 도시들 중 인구수 많은 순서로 상위 10개만 표시하시오.

Select * from city where CountryCode=’KOR’ ORDER BY desc limit 10

 

 

🌱 🌱 집합 함수

테이블의 전체 레코드를 대상으로 특정 컬럼을 적용해서 한 개의 값을 리턴하는 함수

• Count() – 레코드의 개수 리턴,
• avg() -컬럼값의 평균, sum() – 컬럼값의 합,
• min(), Max() – 컬럼값의 최소, 최대
• first(), last() - 주어진 ORDER BY절에 의한 값중 첫번째를 가리킨다 /주어진 ORDER BY절에 의한 값중 마지막

ex. City 테이블에서 국가 코드가 ‘KOR’인 도시들의 인구수 총합/ 평균을 구하시오.

Select count(*) from city where ContryCode=’KOR’

 

🌱 🌱 유용한 함수

• Length() : 레코드의 문자열 컬럼의 글자수를 리턴 SELECT length(OGPLC) FROM IL_ITEM
• Mid() : 문자열의 중간 부분을 리턴 mid(컬럼,시작인덱스,.길이)
• Upper()/lower() : 문자열을 대문자/소문자로 리턴
• Round() : 레코드의 숫자 컬럼값을 반올림해서 리턴 mid(컬럼,.반올림 자리수(0이 첫째자리))

Ex.

country테이블에서 나라 앞 세글자 대문자로:  SELECT upper(mid(NAME,1,3)) FROM Country

country테이블에서 기대수명을 소수점 첫째자리에서 반올림: SELECT round(life,0)) FROM Country

 

 

🌱 🌱 🌱 JOIN

: 서로 다른 테이블을 공통 컬럼 기준으로 합치는 테이블 단위 연산.

조인의 결과 테이블은 이전 테이블의 컬럼수의 합과 같다.

(city테이블에 컬럼이 10개가 있고, country에 컬럼이 5개가 있다면 join 후, 15개의 컬럼을 가지는 테이블이 된다.)

 

Select * from 테이블1 join 테이블2 on 테이블1.컬럼명 = 테이블2.컬럼명

조인시 서로 다른 테이블에 같은 컬럼명이 존재하면 구분을 위해 테이블명.컬럼명으로 사용해서 표시한다.

 

Ex. city테이블과 country 테이블을 조인하시오. (city.CountryCode = country.Code)

SELECT * FROM city Join CountryOn city.CountryCode = country.Code;

 

Ex. 국가코드와 해당 나라의 GNP를 표시 하시오.

SELECT city.CountryCode, country.GNP FROM city Join CountryOn city.CountryCode = country.Code;

SELECT DISTINCT city.CountryCode, country.GNP FROM city Join CountryOn city.CountryCode = country.Code;

 

JOIN의 종류

: 조인시 NULL값을 허용하는 내부조인(null값 불가)과 외부조인(null값 허용)으로 구분

INNER JOIN	조인시 NULL값을 허용하지 않음
LEFT JOIN	조인시 JOIN의 왼쪽 테이블의 NULL값을 포함해서 표시 A join B 일때, A에서 null값이 제외되면 안되는 상황에 사용
RIGHT JOIN	조인시 JOIN의 오른쪽 테이블의 NULL값을 포함해서 표시 A join B 일때, B에서 null값이 제외되면 안되는 상황에 사용
FULL JOIN	MYSQL은 지원하지 않음

Ex. country 테이블에는 존재하지만 도시가 하나도 없는 나라가 있는지 확인하시오. ( = country 값이 존재하지 않는 city는 포함)

SELECT COUNT(*) FROM city left join country on city.CountryCode = country.Code

Right join이면, country 중 도시 수가 하나도 없는 country는 포함

 

 

🌱 🌱  별명 ALIAS

쿼리 결과 생성시, 컬럼명에 대한 별명을 사용해 표시하는 기능. (조인할 때 많이 사용한다.)

SELECT 테이블명1.컬럼명1 AS 별명1, 테이블명2.컬럼명2 AS 별명2 FROM ~

Ex. City 와 country 조인해서 국가코드가 ‘kor’안 나라의 축약표시명과 정식명을 표시해라!

SELECT city.countryCode AS ABBR, country.Name AS fullName FROM city WHERE city.CountryCode ='KOR'

 

 

🌱 🌱  뷰 VIEW

쿼리 결과 값을 임시테이블로 저장해서 사용할 수 있다. (사용 끝나면 DROP VIEW로 삭제 해야 한다.)

CREATE VIEW 뷰명 AS ( SELECT… 쿼리) : ( SELECT… 쿼리) 결과 값을 테이블 뷰로 만들어라!

 

EX.국가 코드가 “KOR”인 도시들의 국가코드와 국가명(Name)을 뷰로 생성하시오.

CREATE VIEW sampleView as select city.countryCode As Abbr, country.Name As FulName

from city join country

on city.CountryCode = country.Code where city.CountryCode = ‘KOR’;

 

뷰 결과를 select * from sampleView; 로 확인 하면 된다

DROP VIEW

 

 

🌱 🌱 🌱 SELECT INTO

: 쿼리결과를 새 테이블로 만든다.

MYSQL에서는 CREATE TABLE 테이블명 SELECT * FROM 테이블명

: 기존에 존재하지 않는 테이블이 새로 생성된다. (일종의 뷰와 같은 효과)

Ex. city테이블의 내용에서 국가코드가 ‘KOR’인 도시를 찾아 city_new 테이블에 넣으시오.

CREATE TABLE city_new SELECT * from city

 

 

🌱 🌱 🌱 INSERT INTO SELECT

: 쿼리결과를 기존의 테이블에 추가한다. (기존 테이블이 존재해야 함)

INSERT INTO 테이블명1 SELECT * FROM 테이블명2 WHERE 조건절

: SELECT 하는 테이블과 INSERT 하는 테이블은 동일한 구조를 가져야 한다.

: 두개의 별도 쿼리를 하나로 합침

Ex. City 테이블의 내용에서 국가코드가 ‘KOR’인 도시를 찾아 city_kor 테이블에 넣으시오.

 

 

 

🌱 🌱 🌱 CASE … WHEN …END

: SQL의 조건문(if/switch)에 해당한다.

CASE WHEN 조건값1 THEN …

           WHEN 조건값2 THEN…

                      ELSE…

END

Ex. City 테이블에서 도시명이 3자가 넘어가는 경우에 앞쪽 세 자만 대문자로 출력하고 도시의 인구를 같이 표시하시오.

 

SELECT CASE WHEN length(NAME)>3 THEN upper(mid(NAME,1,3))

WHEN LENGTH(NAME) <=3 THEN NAME

END, POPULATION FROM City;

 

 

 

 

---

DDL :  데이터 베이스 테이블 생성, 수정, 삭제 할 수 있는 기능을 말함

👇 상세 설명 😎

Data Definition Language

- 테이블 스키마를 정의, 수정하는 기능(테이블 생성/ 컬럼 추가/ 타입 변경/ 각종 제약조건 지정/수정 등)

 

SQL문

CREATE

DROP

ALTER 

---

DCL : 데이터 베이스 테이블에 접근 권한 조작할 수 있는 기능을 말함 

👇 상세 설명 😎

Data Control Language

- 디비나 테이블 접근권한이나, CRUD 권한을 정의하는 기능

- 특정 사용자에게 테이블의 조회 권한 허가/ 금지하는 기능

 

SQL문

GRANT

REVOKE

 

---

SQL : Structured Query Language