SQL활용(기본SQL작성-DML)-명령문

01. 데이터베이스를 운영하기 위한 기본적인 SQL문

01. Select문 기본 구조

SELECT column_name FROM table_name WHERE condition;

02. WHERE절

• 조회하는 결과에 특정한 조건을 줘서, 원하는 데이터만 보고 싶을 때 사용
  

03. CREATE TABLE … SELECT 구문

• 테이블을 복사해서 사용할 경우에 주로 사용

04. GROUP BY절

• 역할은 지정된 열을 그룹으로 묶어주는 역할을 하며, 주로 집계 함수와 함께 사용

05. SQL문

• DML, DDL, DCL로 분류

06. INSERT/UPDATE/DELETE문

    데이터의 입력/수정/삭제의 기능

02. SELECT문

01. 원하는 데이터를 가져와 주는 기본적인 <SELECT … FROM>

02. 실제적으로 요약한 구조

[ WITH <Sub Query /> ]
  SELECT select_list
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING serach_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

03. 가장 자주 쓰이는 형식

SELECT column_name
FROM table_name
WHERE condition;

ex) 데이터 갖고 오기
<!-- Employees의 모든 데이터를 갖고 온다 -->
SELECT * FROM employees;
<!-- Jobs의 모든 데이터를 갖고 온다 -->
select * from jobs;
<!-- Employees의 first_name, phone_number, hire_date 데이터 갖고 오기 -->
select first_name, phone_number, hire_date from employees;

실습1) 스키마 이름, 테이블 이름, 필드 이름이 정확히 기억나지 않거나, 각 이름의 철자가 확실하지 않을 때 찾아서 조회하는 방법을 실습하자! 지금 조회하고자 하는 내용이 HR.employees 테이블의 department_name 열이라고 가정한다

1. root 계정 system에 연결

2. 다음 쿼리문으로 Oracle의 사용자(=스키마) 이름을 조회하자

• 조회 결과에서 찾고자 하는 스키마가 HR인것을 확인하자
• *dba는 databalse admin

SQL> select * from sys.dba_users;

3. HR 스키마(=사용자)에 있는 테이블의 정보를 조회한다

SQL>select * from sys.dba_tables where owner = 'HR';

4. HR.DEPARMENTS 테이블의 열(=컬럼)이 무엇이 있는지 확인

SQL>select * from sys.dba_columns where owner = 'HR' and table_name = 'DEPARTMENTS';

5. 최종적인 데이터를 조회

SQL>SELECT department_name from department;

실습2) 앞으로 책의 전과정에서 사용할 스키마(=사용자)와 테이블 생성하자

• 아직 배우지 않은 SQL문이 많이 나올 것이므로 잘 이해가 안 가더라도 우선은 또같이 진행
  앞으로 하나씩 계속 배워 나갈 것이다.

0. SQL developer를 종료하고 다시 실행

• local-system 으로 연결
• 이번에 입력할 쿼리는 앞으로 다른 장에서도 거의 비슷하게 많이 사용
• 이번 실습에서 입력한 쿼리를 저장해 놓는것이 나중에 편리

1. sqlDB 스키마를 만들자

CREATE USER sqlDB IDENTIFIED BY 1234 -- 사용자 이름 : sqlDB, 비밀번호 : 1234
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

-- 연결, 자원 사용, 자원 관리에 대한 권한을 sqlDB에게 준다
GRANT connect, resource, dba TO sqlDB;

2. 새로운 사용자 sqlDB에 접속을 만들자

• SQLDeveloper 내부에 접속자 생성

3. 테이블을 만들자

CREATE TABLE userTBL -- 회원테이블
(   userID      CHAR(8)         NOT NULL PRIMARY KEY, --사용자 아이디(PK)
    userName    nvarchar2(10)   not null, --사용자 이름
    birtYear    number(4)       not null, --출생 연도
    addr        nchar(2)        not null, --지역(경기, 서울, 경남 식으록 2글자만 입력)
    mobile1     CHAR(3), --휴대폰의 국번(010, 011, 016, 017, 018, 019 등)
    mobile2     CHAR(8), --휴대폰의 나머지 전화번호(- 제외)
    height      number(3), --키
    mDate       DATE --회원 가입일
);

CREATE TABLE buyTBL -- 회원구매테이블
(   idNum       number(8)       NOT NULL PRIMARY KEY, --순번(pk)
    userID      char(8)         not null, --사용자아이디
    prodName    nchar(6)        not null, --물품 명
    groupName   nchar(4), --분류
    price       number(8)       not null, -- 단가
    amount      number(3)       not null, -- 수량
    FOREIGN KEY (userID) REFERENCES userTBL(userID)
    -- 외래 키 설정하여 buyTBL의 userID를 userTBL(userID)를 참고하여 연결
);

4-1. 회원 테이블 입력

insert into userTBL values('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
insert into userTBL values('KBS', '김범수', 1979, '경남', '011', '22222222', 173, '2012-4-4');
insert into userTBL values('KKH', '김경호', 1971, '전남', '019', '33333333', 177, '2007-7-7');
insert into userTBL values('JYP', '조용필', 1950, '경기', '011', '44444444', 166, '2009-4-4');
insert into userTBL values('SSK', '성시경', 1979, '서울', NULL,  NULL,       186, '2013-12-12');
insert into userTBL values('LJB', '임재범', 1963, '서울', '016', '66666666', 182, '2009-9-9');
insert into userTBL values('YJS', '윤종신', 1969, '경남', NULL,  NULL,       170, '2005-5-5');
insert into userTBL values('EJW', '은지원', 1972, '경북', '011', '88888888', 174, '2014-3-3');
insert into userTBL values('JLW', '조관우', 1965, '경기', '018', '99999999', 172, '2010-10-10');
insert into userTBL values('BBK', '바비킴', 1973, '서울', '010', '00000000', 176, '2013-5-5');

4-2. 구매 테이블 입력

-- 시퀀스 참조 https://cocodo.tistory.com/12
create SEQUENCE idSEQ; -- 순차번호 입력을 위해서 시퀀스 생성
insert into buyTBL values(IDSEQ.nextval, 'KBS', '운동화', null,  30,    2);
insert into buyTBL values(IDSEQ.nextval, 'KBS', '노트북', '전자', 1000,  1);
insert into buyTBL values(IDSEQ.nextval, 'JYP', '모니터', '전자', 200,   1);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '모니터', '전자', 200,   5);
insert into buyTBL values(IDSEQ.nextval, 'KBS', '청바지', '의류', 50,    3);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '메모리', '전자', 80,    10);
insert into buyTBL values(IDSEQ.nextval, 'SSK', '책',    '서적', 15,     5);
insert into buyTBL values(IDSEQ.nextval, 'EJW', '책',    '서적', 15,     2);
insert into buyTBL values(IDSEQ.nextval, 'EJW', '청바지', '의류', 50,    1);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '운동화', null,  30,     2);
insert into buyTBL values(IDSEQ.nextval, 'EJW', '책',    '서적', 15,     1);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '운동화', null,  30,     2);

5. 입력한 테이블을 커밋하고, 테이터 확인

commit;
SELECT * FROM usertbl;
SELECT * FROM buytbl;

04. 특정한 조건의 데이터만 조회하는<SELECT...FROM...WHERE>

1. 기본적인 WHERE 절

// WHERE절은 조회하는 결과_원하는 데이터만 보고 싶을 때 사용
SELECT fieldName FROM tablenName WHERE condition;
                  
// WHERE 조건 없이 조회
SELECT * FROM userTBL;

// userTBL에 있는 값 중에 userName이 김경호인 아이를 검색
SELECT * FROM userTBL where userName = '김경호';

2. 관계 연산자의 사용

• 조건 연산자(=, <. >, <=, >=, <>, != 등)와
  관계 연산자(NOT, AND, OR 등)를 잘 조합하면
  다양한 조건의 쿼리를 생성할 수 있음

-- 아이디와 이름을 조회
select userID, userName from userTBL where birtYear >= 1970 and height >= 182;
-- 추가 조건 아이디와 이름을 조회
select userID, userName from userTBL where birtYear >= 1970 OR height >= 182;
-- 연속적인 값일 경우 between...and 사용조회
select userName, height from userTBL where height between 180 and 183;
select userName, height from userTBL where height >= 180 and height <= 183;
-- 비연속적인 값일 경우 사용 조회
select userName, addr from userTBL where addr='경남' or addr='전남' or addr='경북';

3. in() 사용 조회

select userName, addr from userTBL where addr IN ('경남','전남','경북');

4. like 사용 조회

• 문자열 비교, % 전체, _ 한글자

select userName, height from userTBL where userName like '김%';
select userName, height from userTBL where userName like '_종%';
select userName, height from userTBL where userName like '김__';

5. ANY/ALL/SOME 그리고 서브쿼리(SubQuery, 하위쿼리)

• 서브쿼리란 간단히 얘기하면 쿼리문 안에 또 쿼리쿤이 들어 있는 것을 얘기 함

select userName, height from userTBL where height > 177;

-- where 조건에 김경호의 키를 직접 입력(서브쿼리), 서브쿼리는 단일 값이어야 한다
select userName, height from userTBL where height > (select height from userTBL where userName = '김경호');

-- 쿼리를 통해서 사용, 동일 값 추출, 서브쿼리의 조건문은 단일 값이어야 하며 복수이면 오류가 생긴다
select userName, height from userTBL where height >=  (select height from userTBL where addr = '경남');

-- any(), 복수 값을 전부 비교하는데 둘 중 하나라도 만족하면 된다
select userName, height from userTBL where height >=  any(select height from userTBL where addr = '경남');

-- =any(), 복수 값을 전부 비교하는데 둘 중 하나라도 만족하면 된다
select userName, height from userTBL where height =  any(select height from userTBL where addr = '경남');

-- in, =any()과 같음
select userName, height from userTBL where height in (select height from userTBL where addr = '경남');

-- all(), 복수 값을 전부 비교하는데 모든 값을 만족해야 된다
select userName, height from userTBL where height >=  all(select height from userTBL where addr = '경남');

6. ORDER BY

• 원하는 순서대로 정렬하여 출력
• ORDER BY 절은 결과물에 대해 영향을 미치지는 않지만, 결과가 출력되는 순서를 조절
• ORDER BY에 나온 열이 select 다음에 꼭 있을 필요는 없음, 맨 마지막에 입력
• 기본 오름차순, desc는 내림차순, asc 오름차순

select userName, mDate from userTBL order by mDate asc;
select userName, height, mDate from userTBL order by height asc, mDate desc;

7. DISTINCT

• 중복된 것은 하나만 남김
• 중복된 것은 1개씩만 보여주면서 출력

select addr from userTBL;
select addr from userTBL order by addr;
select distinct addr from userTBL;

8. ROWNUM

• 출력되는 개수를 조절할 수 있음, where문 뒤에 사용

select * from userTBL where rownum <= 5;
select * from (select userID, userName, addr from userTBL) where rownum <= 5;

9. SAMPLE(퍼센트)

• 임의의 데이터를 추출

-- sample(5)는 5%라는 뜻으로 현재 테이블이 10개이기 때문에 5%면 0.5이기에 1개의 데이터만 나온다
-- 데이터는 랜덤하게 갖고온다
select * from userTBL sample(5);

10. CREATE TABLE ... AS SELECT

• 테이블을 복사

-- create table ... as select구문은 테이블을 복사해서 사용할 경우에 주로 사용
-- 기존 테이블의 값도 같이 복사
-- CREATE TABLE newTableName AS (SELECT copyTableColumn FROM copyTableName);
-- CREATE TABLE 새로운테이블 AS (SELECT 복사할 열 FROM 기존 테이블);
create table userTBL2 as (select userID, addr, height from userTBL);
select * from userTBL2;
CREATE table buyTBL2 as (select * from buyTBL);
select * from buyTBL2;
create table buyTBL3 as (select userID, prodName from buyTBL);
select * from buyTBL3;

11. GROUP BY 절

• 그룹화를 하기 위해서 사용

SELECT select_expr 
    [FROM table_reference]
    [WHERE where_condition]
    [GROUP BY {col_userName | expr | position}]
    [HAVING where_condition]
    [ORDER BY {col_userName | expr | position}]

// 구매 테이블에서 사용자가 구매한 물품 개수 확인
SELECT userID, amount FROM buyTBL ORDER BY userID;

// 각 사용자별로 구매한 개수를 합쳐서 출력
SELECT userID, SUM(amount) FROM buyTBL GROUP BY userID;

// 별칭을 사용해서 결과 보기
SELECT userID AS "사용자 아이디", SUM(amount) AS "총 구매 개수" FROM buyTBL GROUP BY userID;

// 구매액의 총합을 출력
SELECT userID AS "사용자 아이디", SUM(price * amount) AS "총 구매액" FROM buyTBL
GROUP BY  userID;

AS 별칭

-- AS를 사용한 별칭
SELECT userID AS "사용자 아이디", SUM(amount) AS "총 구매 개수" 
FROM buyTBL GROUP BY userID;

-- AS를 사용한 별칭, 집계함수에 수식을 작성
SELECT userID AS "사용자 아이디", SUM(price*amount) AS "총 구매액" 
FROM buyTBL GROUP BY userID;
SELECT userID AS "사용자 아이디", groupname, SUM(price*amount) AS "총 구매액"
FROM buyTBL GROUP BY userID, groupname;

-- AS를 사용한 별칭, 집계함수에 수식을 작성, order by로 정렬(default 오름차순)
SELECT userID AS "사용자 아이디", groupname, SUM(price*amount) AS "총 구매액"
FROM buyTBL GROUP BY userID, groupname ORDER BY userID;

12. 집계 함수

• SUM() 외에 GROUP BY와 함께 자주 사용되는 집계 함수(또는 집합 함수)

SUM()				: 합계를 구한다
SELECT userID, SUM(amount) FROM buyTBL GROUP BY userID;

AVG()				: 평균을 구한다
SELECT userID, AVG(amount) FROM buytbl GROUP BY userID;

MIN()				: 최소값을 구한다
SELECT userID, MIN(amount) FROM buytbl GROUP BY userID;

MAX()				: 최대값을 구한다
SELECT userID, MAX(amount) FROM buytbl GROUP BY userID;

COUNT()			 	: 행의 개수를 센다
SELECT COUNT(*) FROM usertbl;

COUNT(DISTINCT)		: 행의 개수를 센다(중복은 1개만 인정)
STDEV()				: 표준편차를 구한다
VARIANCE()			: 분산을 구한다

-- 전체 구매자가 구매한 물품의 개수의 평균
SELECT AVG(amount) AS "평균 구매 개수" FROM buyTBL;

-- 소수점을 조절 CAST() 함수 사용
SELECT CAST(AVG(amount) AS NUMBER(5,3)) AS "평균 구매 개수" FROM buyTBL;

-- 평균적으로 몇 개 구매했는지 평균 구하기
-- 평균을 구하기 위해서는 2개 이상의 값이 필요로 하며, 그렇기 위해서 그룹화를 진행
SELECT userID, CAST(AVG(amount) AS NUMBER(5,3)) AS "평균 구매 개수" FROM buyTBL GROUP BY userID;

-- group by없이는 별도의 이름 열을 집계 함수와 같이 사용할 수 없다는 오류
SELECT userName, MAX(height), MIN(height) FROM userTBL;

-- 수정
SELECT userName, MAX(height), MIN(height) FROM userTBL GROUP BY userName;

-- 서브쿼리와 조합, userTBL 중 가장 큰 사람과 가장 작은 사람
SELECT userName, height FROM userTBL WHERE
height = (SELECT MAX(height) FROM userTBL) OR
height = (SELECT MIN(height) FROM userTBL);

-- 사용자의 수를 카운트
SELECT COUNT(*) FROM usertbl;

-- NULL 값인 것은 제외하고 카운트
SELECT COUNT(mobile1) AS "휴대폰이 있는 사용자" FROM userTBL;

13. HAVING 절

• WHERE와 비슷한 개념으로 조건을 제한하는 것이지만, 집계 함수에 대해서 조건을 제한하는 것
• 집계함수에 대해서 조건은 HAVING절을 사용하며 GROUP BY 뒤에 작성해야 한다

-- 사용자별 총 구매액
SELECT userID AS "사용자", SUM(amount*price) as "총 구매액"
FROM buyTBL
GROUP BY userID;

-- 집계 함수는 WHERE절에 나타날 수 없음
-- "group function is not allowed here", 집계함수는 WHERE문에서 사용 불가
SELECT userID AS "사용자", SUM(amount*price) as "총 구매액" FROM buyTBL
WHERE SUM(amount*price) GROUP BY userID;

-- HAVING절 사용, HAVING 절은 꼭 GROUP BY절 다음에 나와야 함
SELECT userID AS "사용자", SUM(amount*price) as "총 구매액"
FROM buyTBL
GROUP BY userID HAVING SUM(amount*price) > 1000;

-- ORDER BY를 사용
SELECT userID AS "사용자", SUM(amount*price) as "총 구매액" FROM buyTBL
GROUP BY userID HAVING SUM(amount*price) > 1000
ORDER BY SUM(amount*price);

05. INSER & UPDATE & DELETE

• INSERT A. INSERT INTO tableName (columnName1, columnName2, columnName3, ...)
  VALUES (value1, value2, value3, ... );
• INSERT B. INSERT INTO tableName VALUES (value1, value2, value3, ... );
• UPDATE. UPDATE tableName SET columName = 'changeValue'
  WHERE condition;
• DELETE. DELETE FROM tableName WHERE condition;

-- A
INSERT INTO userTBL(userID, userName, birtYear, addr) VALUES('MMS', '문민승', 1997, '대구');

-- B
INSERT INTO userTBL VALUES('MMS', '문민승', 1997, '대구', '010', '12341234', 168, '2008-8-8');

-- UPDATE
UPDATE userTBL SET addr = '남구' where userName = '문민승';

-- DELETE
DELETE FROM userTBL where userName = '문민승';