<DataBase_231027금> 테이블, DDL 제약조건

<SQL명령 (+ PL SQL)>

 

1. DDL (데이터 정의 명령) : create(생성), alter(수정), drop(삭제), rename, truncate

-> 이 명령을 쓰는 대상은 객체 (ex: 테이블, 유저, 인덱스)

-> rename이라는 명령이 있기는 하지만 너무 바꿔야 할 것이 많고 성능적으로 좋지 않기에 권장하지 않음.

-> 테이블 구조를 바꿀 때 쓰는 명령

 

2. DML (데이터 트랜잭션/조작/변경/처리/흐름 명령) : insert(입력), update(수정), delete(삭제-행이 다 사라짐), (select-추출)

-> 이 명령을 쓰는 대상은 데이터(행 단위,row)

-> 바로 하드로 안가고 버퍼(메모리)로 먼저 감.

 

3. DCL (데이터 제어관리 명령) : grant(부여), revoke(회수), (deny-거부)

-> 권한 관련 명령

-> 권한이 단어로 딱 끝나는건 관련 권한을 묶어놓은 권한집합체이다.(=role) -> ex: sysdba

-> 실제 권한은 명령처럼 생김.

 

4. TCL (Transaction  제어 명령) : commit(저장, 위에서 아래로), rollback(취소, 아래에서 위로), savepoint(저장점, 중간에 깃발 꽂는 것, 북마크 같은거), (save)

-> 데이터 흐름 제어문 (DML을 제어하는 것, 다른건 해당사항 없음)

 

----------------------------------------------------------

 

<관리자 말고 일반계정한테 주는 권한>

 

connect

resource (테이블생성, 시퀀스생성 등등)

 

----------------------------------------------------------

 

중복제거함수 : distinct

ex) select distinct job from emp;

 

----------------------------------------------------------

 

<와일드카드 문자>

%: 임의의 문자열을 허용함.
_: 단일 문자를 허용함.
[]: 지정된 범위의 문자를 허용함.

다음과 같은 쿼리문은 이름이 5글자로 시작하는 사람을 모두 반환함.

ex) select ename from emp
where ename like '_____%';

_____%는 이름의 첫 5개 문자가 임의의 문자이고, 그 이후에는 임의의 문자열을 허용하는 패턴을 나타냄.
이러한 와일드카드 문자를 사용하여 다양한 조건을 사용하여 데이터를 찾을 수 있음.

 

----------------------------------------------------------

 

// 연습문제

 

이름이 A_로 시작하는 사람

select ename from emp
where ename like 'A#_%' escape '#';

 

-- 해설 :

like 연산자는 특정 문자열 패턴과 일치하는 데이터를 찾는 데 사용됨.

이 경우 이름이 A_로 시작하는 사람을 찾고 있음.
 _(언더바)는 특수문자가 아니라 마스터로 인식됨.

이는 SQL에서 와일드카드 문자로 사용되는 %, _, []와 같은 문자를 의미함.

따라서 쿼리문에서 like 'A#_%'는 다음과 같이 해석됨.
A#_는 이름의 첫 번째 문자가 A이고 두 번째 문자가 _인 패턴을 나타냄.
%는 이름의 세 번째 문자부터 끝까지 임의의 문자열을 허용함.
escape '#'는 와일드카드 문자를 일반 문자로 인식하게 하는 옵션. 이 경우 #는 _를 일반 문자로 인식하게 함.

따라서 이 쿼리문은 이름이 A_로 시작하는 사람을 모두 반환.

예를 들어, 이름이 "A_man"인 사람은 이 쿼리문의 결과에 포함됨.

 

----------------------------------------------------------

 

sqlplus/nolog

conn sys/password

conn sys/password as sysdba

conn/as sysdba

conn scott/tiger

create table info(id number(3), name varchar2(10), tel varchar2(15));

desc info

alter table info
add addr varchar2(30);

alter table info
modify name varchar2(20);

desc info

alter table info
drop column tel;

desc info

drop table info;

select * from tab;

conn/as sysdba --login as manager account

create user crystal
identified by password
default tablespace users; --default tablespace를 써야 마스터쪽으로 안감.

grant connect, resource to crystal;

conn crystal/password

select * from tab;

select rowid from emp;

select rownum from emp
where rownum<5;

select job from emp;

select distinct job from emp; -- job의 종류를 추출 (중복 안되게)

 

----------------------------------------------------------

 

<선생님이 정리해주신>

 

1. 컬럼 단위
create table student(
    hakbun number(10) constraint st_hakbun_pk primary key,
    sname varchar2(10) constratint st_sname_nn not null,
    age    number(3) consratint st_age_ck check(age>=20 and age<=30),
    addr   varhcar2(30) default '서울시 구로구',
);

create table lib(
   sno number(10) constraint lib_sno_fk references student(hakbun) [on delete cascase],
   book varchar2(100) constraint lib_book_nn not null,
   loan_date date default sysdate
);

2. 테이블 단위
create table student(
    hakbun number(10),
    sname varchar2(10) constraint st_sname_uq unique,
    age    number(3),
    addr   varchar2(30) default '서울시 구로구',
    constraint st_hakbun_pk primary key(hakbun),
    constraint st_age_ck check(age>=20 and age<=30)
);

create table lib(
   sno number(10),
   book varchar2(100) constraint lib_book_nn not null,
   loan_date date default sysdate,
   constraint lib_sno_fk foreign key(sno) references student(hakbun)
);

--제약조건 수정
alter table table_name
modify book varchar2(100) [ constraint lib_book_nn not null | null ];   // not null/null만 modify로!

alter table table_name
--add constraint st_hakbun_pk primary key(hakbun);   // 제약추가
--drop constraint st_hakbun_pk;         // 제약삭제

 

----------------------------------------------------------

 

<선생님이 정리해주신>

<테이블>

 

--Dept Table 만들기
create table dept(
    deptno  Number(3) ,
                dname  Varchar2(10) , 
                loc  Varchar2(10),
                constraint dept_deptno_pk primary key(deptno),
    constraint dept_dname_uq unique(dname)
);

insert into dept values(10, '총무부','서울');
insert into dept values(20, '영업부','대전');
insert into dept values(30, '전산부','부산');
insert into dept values(40, '관리부', '광주');

--Sawon Table 만들기
create table sawon
(sabun number(3), 
 saname varchar2(10) constraint sawon_saname_nn not null, 
 deptno number(3), 
 sajob varchar2(10),  
 sapay number(10), 
 sahire date default sysdate, 
 sasex varchar2(4), 
 saMgr number(3), 
 constraint sawon_sabun_PK primary key(sabun), 
 constraint sawon_deptno_FK foreign key(deptno) references dept(deptno), 
 constraint sawon_sasex_ck check(sasex in ('남자','여자')), 
 constraint sawon_saMgr_FK foreign key(samgr) references sawon(sabun)) ;

Insert Into sawon Values(1,'홍길동',10,'회장',5000,'1980/01/01','남자',null);
Insert Into sawon Values(2,'한국남',20,'부장',3000,'1988/11/01', '남자',1);
Insert Into sawon Values(3,'이순신',20,'과장',3500,'1985/03/01','남자', 2);
Insert Into sawon Values(5,'이순라',20,'사원',1200,'1990/05/01','여자', 3);
Insert Into sawon Values(7,'놀기만',20,'과장',2300,'1996/06/01','여자', 2);
Insert Into sawon Values(11,'류별나',20,'과장',1600,'1989/12/01','여자', 2);
Insert Into sawon Values(14,'채시라',20,'사원',3400,'1993/10/01','여자', 3);
Insert Into sawon Values(17,'이성계',30,'부장',2803,'1984/05/01','남자', 1);
Insert Into sawon Values(13,'무궁화',10,'부장',3000,'1996/11/01','여자', 1);
Insert Into sawon Values(19,'임꺽정',20,'사원',2200,'1988/04/01','남자', 7);
Insert Into sawon Values(20,'깨똥이',10,'과장',4500,'1990/05/01','남자', 13);
Insert Into sawon Values(6,'공부만',30,'과장',4003,'1995/05/01','남자', 17);
Insert Into sawon Values(8,'채송화',30,'대리',1703,'1992/06/01','여자', 17);
Insert Into sawon Values(12,'류명한',10,'대리',1800,'1990/10/01','남자', 20);
Insert Into sawon Values(9,'무궁화',10,'사원',1100,'1984/08/01','여자', 12);
Insert Into sawon Values(4,'이미라',30,'대리',2503,'1983/04/01','여자', 17);
Insert Into sawon Values(10,'공부해',30,'사원',1303,'1988/11/01','남자', 4);
Insert Into sawon Values(15,'최진실',10,'사원',2000,'1991/04/01','여자', 12);
Insert Into sawon Values(16,'김유신',30,'사원',400,'1981/04/01','남자', 4);
Insert Into sawon Values(18,'강감찬',30,'사원',1003,'1986/07/01','남자', 4);

--Gogek Table 만들기
create table gogek(gobun number(3), 
                   goname varchar2(10), 
                   gotel varchar2(20), 
                   gojumin varchar2(14), 
                   godam number(3));

ALTER TABLE GOGEK 
ADD constraint gogek_gobun_PK primary key(gobun);

ALTER TABLE GOGEK 
ADD constraint gogek_gojumin_UQ unique(gojumin);

ALTER TABLE GOGEK 
ADD constraint gogek_godam_FK foreign key(godam) 
references sawon(sabun);

insert into gogek values(1,'류민', '123-1234', '700113-1537915',3);
insert into gogek values(2,'강민', '343-1454', '690216-1627914',2);
insert into gogek values(3,'영희', '144-1655', '750320-2636215',null);
insert into gogek values(4,'철이', '673-1674', '770430-1234567',4);
insert into gogek values(5,'류완', '123-1674', '720521-1123675',3);
insert into gogek values(6,'캔디', '673-1764', '650725-2534566',null);
insert into gogek values(7,'똘이', '176-7677', '630608-1648614',7);
insert into gogek values(8,'쇠돌', '673-6774', '800804-1346574',9);
insert into gogek values(9,'홍이', '767-1234', '731225-1234689',13);
insert into gogek values(10,'안나','767-1677', '751015-2432168',4);
commit;

 

------------------------------------------------------------

 

<선생님이 정리해주신>

<DDL_제약조건>

 

*** 테이블 생성

1. 생성
SQL> create table info(id number(3), name varchar2(10), tel varchar2(15));

테이블이 생성되었습니다.

SQL> desc info
 이름                                                              널?      유형
 ----------------------------------------------------------------- -------- ------------------
 ID                                                                         NUMBER(3)
 NAME                                                                    VARCHAR2(10)
 TEL                                                                        VARCHAR2(15)

2. 변경
SQL> alter table info
  2  add addr varchar2(30); // 컬럼 추가

테이블이 변경되었습니다.

SQL> alter table info
  2  modify name varchar2(20); // 컬럼 속성 변경

테이블이 변경되었습니다.

SQL> alter table info
  2  drop column tel; // 컬럼 삭제

테이블이 변경되었습니다.

-- 연습계정 생성
SQL> conn /as sysdba
연결되었습니다.

SQL> create user sky 
identified by pass 
default tablespace users;

사용자가 생성되었습니다.

SQL> grant connect, resource to sky;

권한이 부여되었습니다.

SQL> conn sky/pass
연결되었습니다.

*** 제약조건(Constraint)
1. 컬럼 단위
create table student(
 hakbun number(10) constraint st_hakbun_pk primary key,
 sname varchar2(10) constratint st_sname_nn not null,
 age    number(3) consratint st_age_ck check(age>=20 and age<=30),
 addr   varhcar2(30) default '서울시 구로구',
);

create table lib(
sno number(10) constraint lib_sno_fk references student(hakbun) [on delete cascase],
book varchar2(100) constraint lib_book_nn not null,
loan_date date default sysdate
);

2. 테이블 단위
create table student(
 hakbun number(10),
 sname varchar2(10) constraint st_sname_uq unique,
 age    number(3),
 addr   varchar2(30) default '서울시 구로구',
 constraint st_hakbun_pk primary key(hakbun),
 constraint st_age_ck check(age>=20 and age<=30)
);

create table lib(
sno number(10),
book varchar2(100) constraint lib_book_nn not null,
loan_date date default sysdate,
constraint lib_sno_fk foreign key(sno) references student(hakbun)
);

--제약조건 수정
alter table table_name
modify book varchar2(100) [ constraint lib_book_nn not null | null ]; // not null/null만 modify로!

alter table table_name
--add constraint st_hakbun_pk primary key(hakbun); // 제약추가
--drop constraint st_hakbun_pk; // 제약삭제