기록해! 정리해!

5-SQL 기본문법 정리 본문

Oracle

5-SQL 기본문법 정리

zsuling 2022. 5. 23. 12:33

1) PK 제약조건

 

create table pk_user1(
id nchar(10) primary key,
name varchar(20)
);

 

create table pk_user2(
id nchar(10),
name varchar(20),
primary key (id)
);

 

create table pk_user3(
id nchar(10),
name varchar(20),
constraint pk_user3_1 primary key (id)
);

 

create table pk_user4(
id nchar(10),
name varchar(20),
constraint pk_user3_2 primary key (id, name) --컬럼 2개에 제약조건 걸려면 밑에 하는 수 밖에 없다
);

 

create table user11(
id nchar(10),
name varchar(20)not null
);

 

create table user11(
id nchar(10),
name varchar(20)
constraint user11_user3_1 not null (id) --에러/ not null은 컬럼 옆에 붙을 수 있다
); 

 

값을 무조건 받아야하는 상황이면 not null 제약조건을 걸어야한다

 

2) FK 제약조건

 

create table fk_user11(
id1 nchar(10) not null,
name varchar(20)not null,
foreign key(id1) references user1(id) --에러/ user1이 pk로 안잡혀있음
); 
--fk는 pk를 참조해야함

 

create table fk_user11(
id1 nchar(10) not null,
age number(3),
foreign key(id1) references pk_user1(id)
);

id랑 id1 이름이 같을 필요는 없다

id1은 id에 없는 값을 넣을 순 없다 

 

3) Unique 제약조건

 

create table uk_user1(
id nchar(10)unique,
name varchar(20)
);

 

insert into  uk_user1( id,name)
values ('ppk','영심이');

insert into  uk_user1( id,name)
values ('ppk','영심이'); --에러 / 중복이라고 유니크제약조건 걸림

insert into  uk_user1( id,name)
values ('','영심이'); --null값 가능

 

insert into  uk_user1( id,name)
values ('','하늘이'); --null은 중복 가능

 

create table uk_user2(
id nchar(10) not null unique,
name varchar(20) not null unique
);

-- 의도치않게 pk역할과 동일한 역할이 됨

 

즉,unique 제약조건은 null값을 넣으려고 사용하는 것

 

4) Check 제약조건 (339p))

 

Create table ck_user3 (

id nchar(10) not null UNIQUE ,

loc varchar(20) not null ,

check ( loc in('서울','기타' ))

);

 

insert into ck_user3 (id, loc) values('ppk','서울');

insert into ck_user3 (id, loc) values('ppk1','경기'); --에러/ 체크제약조건

insert into ck_user3 (id, loc) values('young','기타');

 

5)defualt 제약조건

 

Create table default_user2 (

id nchar(10) ,

loc varchar(20) DEFAULT '서울' ,

age number(3) DEFAULT 21

);

 

insert into default_user2(id)

values('young1');

 

insert into t200(id, pwd, age, loc)   --이렇게 컬럼이 들어가 있으면 디폴트가 아니라 넣은 값이 나옴
values('mibn','1217', '', '');              -- 들어온 컬럼에 공백이 있으니까 null로 나옴

 

6) 제약조건 추가하기, 삭제하기

 

1. 추가하기

alter table user100
add CONSTRAINT user100_pk PRIMARY key (id);

--(레코드가 이미 있을 때 제약조건을 걸기 쉽지않음 제거하고 추가하기)

 

2. 확인하기

select * from ALL_CONSTRAINTS
where table_name ='USER100'; -- 테이블 이름 필히 대문자 작성

 

3. 제거하기

alter table user100 drop CONSTRAINT USER100_PK;

 

7) 컬럼 수정하기

 

-- 컬럼 추가 하기 (1)

alter table user100 add email varchar(50) ;

 

-- 컬럼 추가 하기 (2)

alter table user100 add age number(3) DEFAULT '17' ;

 

-- 컬럼제거하기

alter table user100 drop COLUMN age ;

 

-- 테이블 확인하기

desc user100;

 

-- 컬럼 수정하기

alter table user200 modify (age number(3) ) ;

 

8) sequence

: 자동증가 할 수 있는 값

: pk를 sequence로 잡기 위해서 사용, 중복되지 않기 위해서 

: 오라클에만 있음

 

create table t100(
id varchar(10),
pwd varchar(20)
);

create sequence s_table
increment by 
start with 1001;

insert into t100(id,pwd)
values(s_table.nextval, '1234');

 

insert into t100(id,pwd)
values(s_table.nextval, '1234가나다라마바사');  --에러 

 

select * from t100; --에러값도 기억하고 있음

 

delete from t100
where id='1007';

select * from t100; -- 지우고 다시해도 기억하고 있음

 

9) dual (임시테이블)

 

-1) 자동형변환

 

select 5+7
from dual; --12

 

select '5'+'7'
from dual; --57이 안나오고 12가 나옴 (자동형변환)

select '5'||'7'
from dual; --57 문자를 연결해줌 ,갯수에 상관없이 연결가능, 오라클에만 있음(파이프라인)
-- 하나의 컬럼에 나타낼 때 사용

 


select concat ('5','7')
from dual; --57 문자를 연결해줌, 2개의 문자열만 가능

 

-2) 길이 length

 

select length('한글')from dual;


select length('한글'),  length('abdfd') from dual;


select length('한글'),  lengthb ('abdfd'), length('abdfd')  from dual; --앞 길이 와 뒷 길이를 곱(B)해줘서 나옴

 

10)

delete from school; --DML(롤백 가능)
drop table school; --DDL(롤백 불가능)

 

11) 쉼표 찾기 

select instr(학생정보,',')from v_school;
select instr(학생정보,',',1)from v_school; --첫글자부터 쉼표찾기

select instr(학생정보,',',1),instr(학생정보,',',8),instr(학생정보,',',14) from v_school;

 

12) 문자 바꾸기

insert into school(idx, sname, age, tel)
values ('200', 'Park min', 25, '010-1212-5555');

select lower(sname),upper(sname),initcap(sname) from school;
--소문자 / 대문자 / 첫글자만 대문자

 

13) 뷰 별칭

create view v_school1
as
select lower(sname)소문자,upper(sname)대문자,initcap(sname)첫글자 from school;

 

create view v_school2(소문자,대문자,첫글자)
as
select lower(sname),upper(sname),initcap(sname) from school;

 

14) 변환함수 (253p)

-1) replace : 문자열을 문자열로 변환하는 함수 

select replace (tel,'-','') from school;
--  -가 ''(공백)으로 변함

 

select sname, replace(lower(sname),'min','민') 
,replace (tel,'-','') from school;

 

-2) translate : replace처럼 문자열을 통째로 바꾸는 것이 아니라 한글자씩 1:1 대응하여 변환
select tel, translate(tel,'0123','영일이삼')from school;

--0123이 영일이삼 으로 바뀜

 

-3) substr : 시작위치부터 지정된 길이까지의 문자를 출력

select tel, substr(tel,0,3) from school;
--0번째부터해서 3번째 글자 나옴

 

select substr(tel,1,3)||substr(tel,5,4)||substr(tel,10,4) as "전화번호" from school
where tel is not null ;  --하나의 컬럼으로 만들 때

 

 

 

 

'Oracle' 카테고리의 다른 글

6-2  (0) 2022.05.23
6-예제 2(SQL예상문제)  (0) 2022.05.23
6-1  (0) 2022.05.23
6-예제1(shopping)  (0) 2022.05.23
5-예제2  (0) 2022.05.20
Comments