03. SQL 기초

Posted on: 2023년 04월 22일 2023년 04월 22일
Categories: 강의자료, 데이터베이스

SQL 분류
1. 데이터 정의어(DDL) : CREATE, ALTER, DROP
2. 데이터 조작어(DML) : INSERT, DELETE, UPDATE, SELECT
3. 데이터 제어어(DCL) : GRANT, REVOKE
SELECT 문
1. 기본 문법
  1. 예제
    1. 학생 테이블의 모든 튜플을 검색
    2. 학생 테이블에서 학번, 이름, 전화번호를 검색
    3. 개설과목 테이블에서 과목코드, 과목명, 학점을 검색
    4. 개설과목 테이블에서 최대수강인원이 30명 이상인 튜플을 검색
2. 조건 검색(WHERE)
  1. 비교 : =, <>, <, <=, >, >=
  2. 범위 : BETWEEN
  3. 집합 : IN, NOT IN
  4. 패턴 : LIKE
  5. NULL : IS NULL, IS NOT NULL
  6. 복합조건 : AND, OR, NOT
  7. 예제 2
    1. 개설과목 테이블에서 최대수강인원이 25명 이상, 40명 미만인 튜플을 검색
    2. 교수 테이블에서 학과가 인공지능 또는 일본언어문화 학과인 모든 튜플을 검색
    3. 학생 테이블에서 성이 김씨인 모든 튜플을 검색
3. 와일드 문자(오라클 기준)
  1. + : 문자열 연결
  2. % : 0개 이상의 문자열과 일치
  3. _ : 특정위치의 1개의 문자와 일치
  4. [] : 대괄호 안의 문자중 1개의 문자
  5. [^] : 대괄호 안의 문자를 제외한 1개의 문자
  6. 예제
    1. [^ABCD]% : A,B,C,D 가 아닌 한문자로 시작하는 모든
    2. 김% : 김으로 시작하는 모든
    3. 김_ _ : 김으로 시작하고 3 문자인
    4. [ABCD]_ _ : A,B,C,D 중 한 문자로 시작하고 3 문자인
    5. [^ABCD]% : A,B,C,D 가 아닌 한 문자로 시작하는 모든
4. 정렬(ORDER BY)
  1. ORDER BY hakbun
  2. ORDER BY dept, name
  3. ORDER BY dept DESC, name ASC
5. 집계함수
  1. SUM, AVG, COUNT, MAX, MIN
  2. 속성에는 집계함수만 나올 수 있음
  3. 예제
    1. SELECT COUNT(*) FROM tstudent
    2. SELECT COUNT(dept) FROM tstudent
    3. SELECT COUNT(dept) as cnt FROM tstudent
    4. SELECT COUNT(ALL dept) as cnt FROM tstudent
    5. SELECT COUNT(DISTINCT dept) as cnt FROM tstudent
6. GROUP BY 검색
  1. 속성은 GROUP BY에서 사용한 속성과 집계함수만 나올 수 있음
  2. HAVING은 WHERE보다 뒤쪽에 와야 함
  3. 예제
    1. SELECT dept, COUNT(dept) FROM tstudent
    2. SELECT dept, COUNT(dept) FROM tstudent GROUP BY dept
    3. SELECT dept, COUNT(dept) FROM tstudent GROUP BY dept HAVING COUNT(dept) > 1
    4. SELECT dept, COUNT(dept) AS cnt FROM tstudent GROUP BY dept HAVING cnt > 1
    5. SELECT dept, COUNT(dept) AS cnt FROM tstudent WHERE gender=’M’ GROUP BY dept HAVING cnt > 1
7. 조인
  1. SELECT * FROM tstudent, tsugang : 여러개의 테이블 연결 : 각 투플의 개수를 곱한 결과가 나옴
  2. SELECT * FROM tstudent, tsugang WHERE tstudent.hakbun=tsugang.hakbun
  3. SELECT * FROM tstudent, tsugang WHERE tstudent.hakbun=tsugang.hakbun ORDER BY tstudent.name
  4. SELECT * FROM tstudent a, tsugang b WHERE a.hakbun=b.hakbun ORDER BY a.name
  5. 뷰를 2개 만들고 조인 연습하기
    1. CREATE VIEW vst AS SELECT * FROM tstudent WHERE gender=’M’
    2. CREATE VIEW vsu AS SELECT * FROM tsugang WHERE hakbun < 201600000
    3. SELECT * FROM vst, vsu WHERE vst.hakbun = vsu.hakbun
    4. SELECT * FROM vst A, vsu B WHERE A.hakbun = B.hakbun
    5. SELECT * FROM vst INNER JOIN VSU ON vst.hakbun = vsu.hakbun
    6. SELECT * FROM vsu LEFT OUTER JOIN VST ON vst.hakbun = vsu.hakbun
8. 부속질의
  1. SELECT * FROM tsubject WHERE dcode=(SELECT MAX(dcode) FROM tsugang);
  2. SELECT * FROM tstudent WHERE hakbun in (SELECT hakbun FROM tsugang WHERE dcode=’15571-01′);
  3. SELECT * from tsungjuk WHERE final >= (SELECT AVG(final) FROM tsungjuk);
  4. SELECT * from tsungjuk a1 WHERE a1.final >= (SELECT AVG(a2.final) FROM tsungjuk a2 WHERE a1.dcode=a2.dcode);
  5. SELECT * FROM tstudent WHERE EXISTS (SELECT * FROM tsugang WHERE dcode=’15571-01′);
9. 집합연산
  1. CREATE VIEW vsp AS SELECT * from tstudent WHERE post>55100
  2. SELECT * from vst UNION SELECT * from vsp
  3. SELECT * from vst EXCEPT SELECT * from vsp
  4. SELECT * from vst INTERSECT SELECT * from vsp

데이터 정의어(DDL)

CREATE TABLE

예제1

CREATE TABLE TSTUDENT(
HAKBUN CHAR(9) NOT NULL,
NAME VARCHAR(50),
GENDER CHAR(5) CHECK(GENDER IN('M','F')),
JUMIN CHAR(14),
DEPT VARCHAR(50) DEFAULT '인공지능학과',
HP VARCHAR(20),
POST CHAR(5) DEFAULT '00000',
ADDR VARCHAR(200),
PRIMARY KEY(HAKBUN)
);

CREATE TABLE TSTUDENT(

HAKBUN CHAR(9) NOT NULL,

NAME VARCHAR(50),

GENDER CHAR(5) CHECK(GENDER IN('M','F')),

JUMIN CHAR(14),

DEPT VARCHAR(50) DEFAULT '인공지능학과',

HP VARCHAR(20),

POST CHAR(5) DEFAULT '00000',

ADDR VARCHAR(200),

PRIMARY KEY(HAKBUN)

);

예제2

CREATE TABLE TSUBJ( SCODE CHAR(5) NOT NULL, ISU VARCHAR(20), TITLE VARCHAR(50), CREDIT NUMBER DEFAULT 3, PRIMARY KEY(SCODE) );

1
2
3
4
5
6
7

CREATE TABLE TSUBJ(
SCODE CHAR(5) NOT NULL,
ISU VARCHAR(20),
TITLE VARCHAR(50),
CREDIT NUMBER DEFAULT 3,
PRIMARY KEY(SCODE)
);

예제3

CREATE TABLE TSUBJECT(
DCODE CHAR(8) NOT NULL,
SCODE CHAR(5) NOT NULL,
SEMESTER CHAR(6) NOT NULL,
PROF CHAR(5) NOT NULL,
ROOM VARCHAR(500),
CAPACITY INTEGER DEFAULT 40,
PRIMARY KEY(DCODE),
FOREIGN KEY(SCODE) REFERENCES TSUBJ(SCODE) ON DELETE CASCADE,
FOREIGN KEY(PROF) REFERENCES TPROFESSOR(SABUN) ON DELETE CASCADE
);

CREATE TABLE TSUBJECT(

DCODE CHAR(8) NOT NULL,

SCODE CHAR(5) NOT NULL,

SEMESTER CHAR(6) NOT NULL,

PROF CHAR(5) NOT NULL,

ROOM VARCHAR(500),

CAPACITY INTEGER DEFAULT 40,

PRIMARY KEY(DCODE),

FOREIGN KEY(SCODE) REFERENCES TSUBJ(SCODE) ON DELETE CASCADE,

FOREIGN KEY(PROF) REFERENCES TPROFESSOR(SABUN) ON DELETE CASCADE

);

예제4

CREATE TABLE TSUGANG(
HAKBUN CHAR(9) NOT NULL,
DCODE CHAR(5) NOT NULL,
SAVE_TIME  VARCHAR(50),
FOREIGN KEY(HAKBUN) REFERENCES TSTUDENT(HAKBUN) ON DELETE CASCADE,
FOREIGN KEY(DCODE) REFERENCES TSUBJECT(DCODE) ON DELETE CASCADE,
PRIMARY KEY(HAKBUN, DCODE)
);

CREATE TABLE TSUGANG(

HAKBUN CHAR(9) NOT NULL,

DCODE CHAR(5) NOT NULL,

SAVE_TIME VARCHAR(50),

FOREIGN KEY(HAKBUN) REFERENCES TSTUDENT(HAKBUN) ON DELETE CASCADE,

FOREIGN KEY(DCODE) REFERENCES TSUBJECT(DCODE) ON DELETE CASCADE,

PRIMARY KEY(HAKBUN, DCODE)

);

예제5

CREATE TABLE TSUNGJUK(
HAKBUN CHAR(9) NOT NULL,
DCODE CHAR(5) NOT NULL,
PRESENT NUMBER(6,2),
REPORT NUMBER(6,2),
MID NUMBER(6,2),
FINAL NUMBER(6,2),
SAVE_TIME  VARCHAR(50),
FOREIGN KEY(HAKBUN) REFERENCES TSTUDENT(HAKBUN) ON DELETE CASCADE,
FOREIGN KEY(DCODE) REFERENCES TSUBJECT(DCODE) ON DELETE CASCADE,
PRIMARY KEY(HAKBUN, DCODE)
);

CREATE TABLE TSUNGJUK(

HAKBUN CHAR(9) NOT NULL,

DCODE CHAR(5) NOT NULL,

PRESENT NUMBER(6,2),

REPORT NUMBER(6,2),

MID NUMBER(6,2),

FINAL NUMBER(6,2),

SAVE_TIME VARCHAR(50),

FOREIGN KEY(HAKBUN) REFERENCES TSTUDENT(HAKBUN) ON DELETE CASCADE,

FOREIGN KEY(DCODE) REFERENCES TSUBJECT(DCODE) ON DELETE CASCADE,

PRIMARY KEY(HAKBUN, DCODE)

);

ALTER TABLE
1. ALTER TABLE tstudent ADD email VARCHAR(100);
2. ALTER TABLE tstudent MODIFY post NUMBER;
3. ALTER TABLE tstudent DROP email;
4. ALTER TABLE tstudent MODIFY post NUMBER NOT NULL;
5. ALTER TABLE tstudent ADD PRIMARY KEY(jumin);
DROP TABLE
1. DROP TABLE tstudent;
2. DROP TABLE tsugang;

데이터 조작어 – 삽입, 수정, 삭제
1. INSERT
  1. INSERT INTO tstudent(hakbun, name, gender, dept, hp, jumin, post, addr) VALUES (‘201514005′,’백지혜’,’F’,’역사문화’,’010-8781-6030′,’031212-1234567′,’55060′,’효자동3가’);
  2. INSERT INTO tsubj(scode, isu, title, credit) VALUES (‘05426′,’교양필수’,’보험과사회’,’3′);
  3. INSERT INTO tprofessor(sabun, name, gender, dept, hp, jumin, post, addr) VALUES (‘12345′,’백호민’,’F’,’역사문화’,’010-1234-9708′,’890105-2423567′,’55060′,’효자동3가’);
  4. INSERT INTO tsubject(scode, dcode, semester, prof, room, capacity) VALUES (‘05426′,’05426-01′,’2023-1′,’12345′,’화01,02,03(자유122),목01,02,03(자유122)’,’40’);
  5. INSERT INTO tsugang(hakbun, dcode, save_time) VALUES (‘201514005′,’06855-01′,’2023-02-25’);
  6. INSERT INTO tsungjuk(hakbun, dcode, present, report, mid, final, save_time) VALUES (‘201514005′,’06855-01′,’9′,’12’,’26’,’22’,’2023-03-21 8:47′);
2. UPDATE
  1. UPDATE tstudent SET hp=’010-1234-5678′, post=’12345′ WHERE hakbun=’201514005′;
3. DELETE
  1. DELETE FROM tstudent WHERE hakbun=’201514005′;

whan0623

516