SQL 종류:
1. DQL(Data Query Language): 데이터 질의 언어. select 문장
2. DML(Data Manipulation Language): 데이터 조작 언어.
- insert, update, delete.
- 테이블에 행(row)을 추가, 변경, 삭제하는 SQL 문장.
3. DDL(Date Definition Language): 데이터 정의 언어.
- create, alter, truncate, drop.
- 테이블, 사용자 계정을 생성, 변경, 삭제하는 SQL 문장.
4. TCL(Transaction Control Language): 트랜잭션 관리(제어) 문장.
- commit: 데이터 베이스의 변경 내용을 영구 저장.
- rollback: 직전의 commit 상태로 되돌리기.테이블 생성:
create table 테이블이름(
컬럼이름 데이터타입 [제약조건 기본값],
...
);데이터 타입의 이름(키워드)은 데이터베이스 종류에 따라서 다름!
Oracle의 데이터 타입: number(정수, 실수 다 저장), varchar2(숫자 문자열 타입), date(날짜 년월일), timestamp(시간 년월일시분초)
테이블 이름: students
컬럼:
- stuno: 학생 아이디, 학번, 숫자(6자리 정수).
- stuname: 학생 이름. 문자열(10글자)
- birthday: 학생 생일. 날짜create table students (
stuno number(6),
stuname varchar2(10 char),
birthday date
);테이블에 행(row) 추가(삽입):
insert into 테이블 (컬럼1, 컬럼2, ...) values (값1, 값2,...);
insert into 테이블 values (값1, ...);
- values에서 나열하는 값의 개수는 컬럼 수와 같아야, 값의 순서는 컬럼의 순서와 같아야 함.
insert into students
values (1, '홍길동', '2023/04/11');
insert into students
values (2, '홍길동'); -- 3번째 값이 없어서 저장 안됨
insert into students
values (2, '홍길동', null);
INSERT INTO students (stuno, stuname)
VALUES (3, '홍길동');
SELECT * FROM students;
INSERT INTO students
VALUES ('홍길동', 4, to_date('2000/01/01')); -- 수치 부적합으로 삽입 불가
INSERT INTO students (stuname, stuno, birthday)
VALUES ('김길동', 5, to_date('2000/01/01'));
INSERT INTO students (stuno)
VALUES (1234567); -- 컬럼 자릿수보다 더 큰 수를 insert하려는 에러, 숫자 크기를 줄이는 방법밖에 없다.
INSERT INTO students (stuname)
VALUES ('qwertyuiopa'); -- 최대값 10, 현재 value 11.
COMMIT; -- 현재까지 작업 내용을 DB에 영구 저장.테이블 생성할 때 컬럼의 기본 값 설정하기:
CREATE TABLE ex_user(
no NUMBER(4),
userid VARCHAR2(20), -- 20 byte까지의 문자열.
password VARCHAR2(100),
age NUMBER(3) DEFAULT 0,
created_date DATE DEFAULT sysdate
);INSERT INTO ex_user(no, userid, password)
VALUES (1, 'guest', 'guest0000');
--> default 값이 설정된 컬럼들은 insert 하지 않으면 기본값이 insert됨.
INSERT INTO ex_user(userid, password)
VALUES ('admin', 'admin0000');
--> default 값이 설정되지 않은 컬럼들은 insert하지 않으면 null됨.
SELECT * FROM ex_user;
COMMIT;제약 조건: (1) primary key(고유키). (2) not null. (3) check. (4) foreign key(외부키)
CREATE TABLE ex1(
col1 NUMBER(2)
PRIMARY KEY, -- NULL 이 아니고, 중복되지 않는 유일한 값 -> 유일한 행 1개를 검색.
col2 VARCHAR2(100)
NOT NULL,
col3 VARCHAR2(100)
UNIQUE,
col4 NUMBER(2)
CHECK(col4 >= 0),
col5 NUMBER(2)
);SELECT * FROM ex1;
INSERT INTO ex1
VALUES (1, '홍길동', 'test', 10, 0);
INSERT INTO ex1(col1, col2)
VALUES (1, '홍길동');
--> PK 위배: 중복되는 값.
INSERT INTO ex1(col2)
VALUES ('홍길동');
--> PK 위배: PK는 NULL이 되면 안 됨.
INSERT INTO ex1(col1)
VALUES (3);
--> col2가 NN이라는 제약조건 위배
INSERT INTO ex1(col1, col2, col3)
VALUES (3, '홍길동', 'test');
--> col3이 중복된 값을 허용하지 않는다(unique)는 제약조건에 위배.
INSERT INTO ex1(col1, col2, col4)
VALUES (3, '홍길동', -10);
--> 체크 제약조건 위배
SELECT * FROM ex1;
COMMIT;제약조건(constraint):
테이블에 데이터들을 insert할 때, 조건을 만족하는 값들만 insert될 수 있도록 만들어 주는 기능.
테이블을 생성(create table)할 때 제약조건을 정의
테이블을 변경(alter table)할 때 제약조건을 추가/변경/삭제.
종류: primary key, not null, unique, check, foreign key
테이블 생성할 때 제약조건 이름을 설정하지 않으면, 시스템에서 자동으로 이름을 부여.
(예)SYS_C08813 -> insert할 때 제약조건에 위배가 되면 에러 메시지에서 볼 수 있음.
테이블을 생성하면서 제약 조건 이름 만들기 1: 컬럼 정의에서.
CREATE TABLE ex2 (
id NUMBER(4)
CONSTRAINT ex2_id_pk PRIMARY KEY, -- constraint 이름 내용
name VARCHAR2(100)
CONSTRAINT ex2_name_nn NOT NULL,
email VARCHAR2(100)
CONSTRAINT ex2_email_uq UNIQUE,
gender VARCHAR2(1)
CONSTRAINT ex2_gender_ck CHECK(gender IN ('M', 'F'))
);테이블을 생성하면서 제약 조건 이름 만들기 2: 컬럼 정의와 제약조건 정의를 따로
CREATE TABLE ex3 (
-- 컬럼 정의:
id NUMBER(4),
name VARCHAR2(100),
email VARCHAR2(100),
gender VARCHAR2(1),
-- 제약 조건 정의: constraint 이름 내용 (컬럼)
CONSTRAINT ex3_id_pk PRIMARY KEY (id),
CONSTRAINT ex3_name_nn CHECK (name IS NOT NULL), -- not null은 괄호(컬럼)로 선언이 안됨.
CONSTRAINT ex3_email_uq UNIQUE (email),
CONSTRAINT ex3_gender_ck CHECK (gender IN ('M', 'F'))
);foreign key (외래키): 다른 테이블의 primary key(고유키)를 참조하는 제약조건.
데이터를 insert할 때 다른 테이블의 PK값에 없는 값는 insert되지 않도록 하기 위해서.
ex_dept 테이블에서 deptid 컬럼: PK
ex_emp 테이블의 deptid 컬럼 ex_dept.deptid 컬럼을 참조 -> FK
CREATE TABLE ex_dept (
deptid NUMBER(2)
CONSTRAINT dept_deptid_pk PRIMARY KEY,
dname VARCHAR2(100)
CONSTRAINT dept_dname_nn NOT NULL
);CREATE TABLE ex_emp1 (
empid NUMBER(4)
CONSTRAINT emp1_empid_pk PRIMARY KEY,
ename VARCHAR2(100)
CONSTRAINT emp1_ename_nn NOT NULL,
deptid NUMBER(2)
-- [constraint 이름] references 테이블 (PK인 컬럼 이름)
CONSTRAINT emp1_deptid_fk REFERENCES ex_dept (deptid)
);CREATE TABLE ex_emp2 (
empid NUMBER(4),
ename VARCHAR2(100),
deptid NUMBER(2),
CONSTRAINT emp2_empid_pk PRIMARY KEY (empid),
CONSTRAINT emp2_ename_nn CHECK(ename IS NOT NULL),
-- constraint 이름 foreign key (컬럼) references 테이블 (PK 컬럼)
CONSTRAINT emp2_deptid_fk FOREIGN KEY (deptid) REFERENCES ex_dept (deptid)
);순서대로 명령어 실행
INSERT INTO ex_emp1 VALUES (1001, '오쌤', 10);
--> ex_dept 테이블에 PK로 10이 생성되어져있지 않기 때문에 에러가 발생.
--> ex_emp1 테이블에 부서아이드를 insert하기 전에, 부서 아이디가 ex_dept 테이블에 먼저 만들어져 있어야 함.
INSERT INTO ex_dept VALUES (10, '개발 1팀');
INSERT INTO ex_dept VALUES (20, '인사팀');
INSERT INTO ex_emp1 VALUES (1001, '오쌤', 10);
INSERT INTO ex_emp1 VALUES (2001, '홍길동', 20);
INSERT INTO ex_emp1 VALUES (3001, '김길동', 30);
INSERT INTO ex_dept VALUES (30, '팀');
SELECT * FROM ex_dept;
SELECT * FROM ex_emp1;
COMMIT;DDL (Data Definition Language): 데이터 정의 언어.
create, alter, truncate, drop
테이블 삭제:
truncate
truncate table 테이블 이름; - 테이블의 모든 행(레코드)을 삭제하고, 테이블 이름만 남기는 것.
drop
drop table 테이블 이름; - 테이블 (객체)가 삭제됨.
-- delete from table과 비교!
TRUNCATE TABLE ex_dept;
--> PK 컬럼이 다른 테이블에서 FK로 참조되고 있기 때문에 삭제가 안됨.
--> 참조하고 있는 다른 테이블의 레코드들을 먼저 삭제해야 테이블 삭제가 가능
TRUNCATE TABLE ex_emp1; --> 성공
SELECT * FROM ex_emp1;
TRUNCATE TABLE ex_dept; --> 참조하는 다른 테이블의 레코드가 없기 때문에 삭제 성공.
DROP TABLE ex_emp2; --> 테이블 자체가 삭제됨.DML(Data Manipulation Language): 데이터 조작 언어.
insert, update, delete. [select]
insert into TABLE (COLUMN, ...) values (값, ...);
insert into TABLE1
select COLUMN, ... from TABLE2 [where ...];
update TABLE set COLUMN = 값, ... [where 조건식];
delete from TABLE [where 조건식];
create table ... as select 구문: 테이블의 모양(컬럼 이름, 데이터 타입)과 데이터를 복사
CREATE TABLE my_emp
AS SELECT * FROM emp;
SELECT * FROM my_emp;테이블 모양만 복사, 데이터는 복사하지 않는 경우:
CREATE TABLE my_emp1
AS SELECT * FROM emp WHERE empno = -1;
ALTER TABLE my_emp1 RENAME TO my_emp2;my_emp2 테이블에 emp 테이블의 모든 내용을 insert
INSERT INTO my_emp2 SELECT * FROM emp;
SELECT * FROM my_emp2;
COMMIT;my_emp2 테이블에서,
사번이 1004인 직원의 급여를 6000으로 설정.
UPDATE my_emp2
SET sal = 6000
WHERE empno = 1004;
COMMIT;
UPDATE my_emp2 SET sal = 6000;
--> 모든 행의 sal 값 변경
ROLLBACK;
--> 이전으로 되돌리기 (commit 전에만 가능)ROLLBACK (commit 전에만 가능)
사번이 1004인 직원의 직무를 manager 입사날짜는 2000/12/31 부서번호는 40으로 업데이트
UPDATE my_emp2
SET job = 'MANAGER', hiredate = '2000/12/31', deptno = 40
WHERE empno = 1004;accounting 부서에서 일하는 직원들의 급여를 10% 인상
UPDATE my_emp2
SET sal = sal*1.1
WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'ACCOUNTING');sal grade가 1인 직원들의 급여를 20% 인상
UPDATE my_emp2
SET sal = sal * 1.2
--WHERE 1 = (SELECT grade FROM salgrade WHERE sal BETWEEN losal AND hisal);
WHERE sal BETWEEN
(SELECT losal FROM salgrade WHERE grade = 1)
AND
(SELECT hisal FROM salgrade WHERE grade = 1);테이블에서 행 삭제
DELETE FROM my_emp2;
ROLLBACK;delete 명령은 rollback이 가능한 DML
truncate table 명령은 rollback이 불가능한 DML
직원 번호 1004인 직원 레코드(행)을 삭제:
DELETE FROM my_emp2
WHERE empno = 1004;1987년도에 입사한 지구어늘 삭제
DELETE FROM my_emp2
WHERE to_char(hiredate, 'YYYY') = '1987';
SELECT * FROM my_emp2;