혼자 공부하는 SQL 1주차

7기 혼공자 & 혼공파에 이어서 8기 혼공S로 돌아왔습니다~~~

요새 회사에서 제일 많이 하는 게 SQL 쿼리짜는 일이기에 이번 기회에 제대로 SQL 정복하는 게 목표입니다~~

---

• SQL 코드 작성 시에 주석 처리하는 방법

-- 단일 주석처리하기
-- 한 문장씩 주석이 적용된다.

-- /* */ 여기 내에 있는 모든 글이 주석처리된다.
/*
주
석
처
리
*/

 

• SQL 코드 작성 시 어떤 것을 대문자로 쓰고 소문자로 쓰는 지에 대한 구분은 많이 없다.
  • 최우선은 보기 편하게 하는 것이다.

/*
    보통 Alias는 소문자
    SELECT, UPDATE, DELETE 등은 대문자로
    TABLE은 입력하는 사람에 따라서 달라지는 거 같다..
    SQL문은 최대한 깔끔하게 줄을 맞춰서 입력해주는 것이 보기 편하다.
    이후 유지보수하기에도 아주 좋음.
*/

---

database(schema) 생성하기

-- schema(database) 생성하기
CREATE SCHEMA shop_db;

-- schema(database) 생성하기2
CREATE DATABASE shop_db;

 

table 생성하기

table 생성하는 2가지 방법이 있다.

1. CREATE TABLE 'schema 명'.'table 명'( );
   • schema명을 선택하지 않고 만들게 되면 처음 들어간 connection 안에 table이 만들어지기 때문에 'schema명'.'table명'을 사용한다!
2. CREATE TABLE 'table명'( );
   • USE 'schema명' 
   • 'schema'에서 table을 생성하게 되면 'schema명'.'table명'을 할 필요가 없다!

-- table 생성하기
CREATE TABLE shop_db.member (
	  member_id CHAR(8) NOT NULL
	, member_name CHAR(8) NOT NULL
	, member_addr CHAR(20) NULL
	, PRIMARY KEY(member_id)
);
-- table 삭제하기
DROP TABLE shop_db.member;

-- schema 사용하기
USE shop_db; -- shop_db(== schema명)
-- table 생성하기2
CREATE TABLE member (
	  member_id CHAR(8) NOT NULL
	, member_name CHAR(8) NOT NULL
	, member_addr CHAR(20) NULL
	, PRIMARY KEY(member_id)
);

 

table에 데이터 INSERT하기

SQL 코드를 입력할 때에는 아래처럼 코드의 줄을 맞춰서 입력하는 게 보기 편하다!!

• 위 쪽 괄호에는 INSERT할 칼럼명을 모두 입력하고
• 아래 쪽 괄호에는 INSERT할 데이터를 칼럼 순서에 맞춰서 입력해주면 된다.

-- table에 데이터 insert하기
INSERT INTO member (
	  member_id
	, member_name
	, member_addr
) VALUES (
	  'hero'
	, '임영웅'
	, '서울 은평구 증산동'
);

 

UPDATE

-- 아이유 회원의 id 변경하기
UPDATE member
SET 
	member_id = 'IU'
WHERE member_name = '아이유';

• MySQL에서 위의 코드를 실행하려고 하니 ERROR:1175가 발생하였다.

• 1175 ERROR 코드는 Update나 Delete 시에 key값을 이용하지 않을 때 생기는 오류이다.
  • 이는 key값을 이용하는 방법과 safe mode를 해제하는 방법이 있다.

• key값을 이용하여 UPDATE 수행하기

-- id가 iyou인 회원의 주소 변경하기
UPDATE member SET 
	member_addr = '아이유 house'
WHERE member_id = 'iyou';

-- update 결과 확인하기
SELECT * FROM member WHERE member_id = 'iyou';

• SAFE MODE 해제하기
  • 상단의 탭 Edit > Preferences -> SQL Editor에서 'safe mode' disabled 처리하기(체크 해제하기)

상단 탭 Edit > Preferences...

Safe Updates 체크 해제하기!

 

DELETE

-- id가 iyou인 회원의 데이터 삭제하기
DELETE FROM member
WHERE member_id = 'iyou';

 

---

기본미션 : shop_db에서 '아이유' 회원에 대한 정보만 추출하기

-- select할 칼럼명 모두 입력하기
SELECT	  member_id
	, member_name
	, member_addr
FROM member
WHERE member_name = '아이유';

-- * 사용하기
SELECT *
FROM member
WHERE member_name = '아이유';

• select할 칼럼명 모두 입력하기

• Alias 사용해서 select하기
  • SELECT 시 칼럼명 뒤에 AS '이름'
  • Alias 사용하게 될 경우 아래와 같이 select 결과 테이블의 첫 줄의 이름이 바뀐다.

---

선택 미션 : 데이터베이스 개체 3가지 설명하기

데이터베이스의 개체에는 '인덱스, 뷰, 스토어드 프로시저, 트리거, 함수, 커서' 등이 있다.

인덱스

• 데이터가 많아질수록 결과가 나오는 시간이 길어짐을 해결하기 위한 방법

-- 인덱스 생성하기
CREATE INDEX idx_member_name ON member(member_name);

• 아래 사진과 같이
  • shop_db(데이터베이스) 내에 member(Table) 내에 index를 보면 'idx_member_name'이 만들어져있음을 알 수 있다.!!

---

뷰

• 뷰는 실제로 만들어지는 테이블이 아니라 일종의 가상의 테이블이다.
• member 테이블을 그대로 view로 만든 'member_view'

• member 테이블 중에서 member_name만 추출하여 view로 만든 'name_view'

• MySQL에서 만들어진 모습 확인

---

스토어드 프로시저

• 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

-- 보통 프로시저 생성 형태는 아래와 같다.
DELIMITER //
CREATE PROCEDURE [프로시저명]
BEGIN
     수행할 쿼리
END //
DELIMITER ;

-- myProc라는 스토어드 프로시저 생성하기
DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
	SELECT * FROM member WHERE member_name = '나훈아';
	SELECT * FROM member WHERE member_name = '박진영';
END //
DELIMITER ;
-- 스토어드 프로시저 myProc() 호출하기
CALL myProc();

• myProc를 호출하게 되면 BEGIN 내부에 있는 쿼리 2개를 2개의 창에 띄워서 보여주게 된다.

---

함수

• 오라클 11g로 진행

/*
 * 함수 만들기
 * 구구단 'a * b = c' 형식으로 나올 수 있게 함수 생성해보기
 */
   
CREATE OR REPLACE TYPE NUM_FNC IS TABLE OF VARCHAR2(200);

CREATE OR REPLACE FUNCTION fn_info (g_no NUMBER)
    RETURN NUM_FNC
IS 
   num NUM_FNC := NUM_FNC();
BEGIN
    for i in 1..9
    loop
        num.extend;
        num(i) := g_no || ' * ' || i || ' = ' || g_no * i;
    end loop;
    
    return num;
END;

-- 함수 실행시키기
SELECT * FROM table(fn_info(3));

SELECT * FROM table(fn_info(3)); 실행 결과

---

DB 관리툴 소개

DBeaver

장점

• Community Edition 버전을 사용하면 라이센스(Apache License)가 무료이다.
• 자바/이클립스 기반으로 개발되어서 윈도우, 리눅스, MAC에서 구동된다.
• JDBC 기반으로 DB를 연결해서 그런지 엄청나게 많은 DB를 지원한다. (Oracle, MySQL, MariaDB, PostgreSQL ... )
• 개발소스가 공개되어서 버그픽스가 가능하고 새로운 기능을 개발하여 사용이 가능하다.

DBeaver라는 DB관리툴은 회사에 와서 처음 들어보았다. 팀원분들이 많이 사용하는 툴이기도 하다...!!

아직까지 사용해보지는 않아서 정확하고 자세한 설명은 못하지만..이런 게 있어서 소개를 하고 싶었다...!!!