은성이의 프로그램X파일

sql실습.txt

create table customer (
id varchar2(10) primary key,
name varchar2(20) not null,
age number(3) not null,
tel varchar2(13) not null,
bloodtype varchar2(2),
gender char(1),
constraint coustomer_gender_pk check(gender IN ('M','F'))
-- constraint coustomer_pk primary key(id)
);

select * from tab; -- 테이블 전체보기

desc customer; -- 테이블 구조보기

drop table customer; --테이블 삭제, 다시 불러올 수 있다.

purge recyclebin; --휴지통 비우기, 테이블 완전 삭제

alter table customer add(mileage number(8) not null); --  마일리지 컬럼 생성

alter table customer drop column mileage; -- 마일리지 컬럼 삭제

insert into customer (id,name,age,tel,bloodtype,gender) values('id-1','홍길동',20,'010-222-333','B','M'); -- 데이터 삽입

select * from customer; -- 테이블 전체 데이터 조회

insert into customer (id,name,age,tel) values('id-2','이순신',30,'010-555-7777'); -- not null 데이터까지만 넣기

select * from customer where id='id-2'; -- id-2 테이터만 조회

insert into customer (id,name,age,tel,bloodtype,gender) values('id-3','신사임당',50,'010-888-9993','AB','F');

insert into customer(id,name,age,tel,bloodtype) values('id-4','김문수',34,'010-4434-4533','O');

insert into customer(id,name,age,tel,bloodtype,gender) values('id-5','오영심',30,'010-323-4443','A','F');

insert into customer(id,name,age,tel) values('id-6','길광호',47,'010-434-2221');

insert into customer values('id-7','이영호',22,'010-323-4555','AB','M');

insert into customer values('id-8','알리스타',45,'011-334-5445','B','M');

insert into customer values('id-9','레오나',35,'010-433-7756','O','F');

insert into customer values('id-10','미스포츈',30,'010-234-5644','A','F');

select * from customer;

update customer set bloodtype='O' where id='id-2'; --데이터 갱신

update customer set bloodtype='A'; -- 형액형 모두 A형으로

rollback --되돌리기

update customer set bloodtype='AB' where age >=20 and age <30; --20대만 AB형 주기

update customer set bloodtype='O' where age between 20 and 29; --20대만 바꾸기

update customer set bloodtype='B' where age=20; -- 나이가 20세만 B형으로

update customer set bloodtype='B' where id='id-7'; -- 이영호의 형액형을 B형으로

alter table customer add constraint customer_bloodtype_pk check(bloodtype IN('A','B','AB','O'));

update customer set bloodtype='AB', age='31' where id='id-7'; --이영호의 혈액형과 나이를 바꾸기

select * from customer;

--delete는 레코드 단위로 지운다, 만약에 데이터에 null값을 줄려면 update를 이용해서 준다.

delete from customer; --지운것을 rollback되살릴 수 있다. 특정한 조건을 줘서 지울 수 있다.

delete from customer; //customer에 있는 모든 데이터 삭제

drop form customber; //테이블 삭제

rollback;

select * from customer; --전체 데이터 조회

select * from customer order by age desc; --나이가 많은 사람부터 조회 desc는 오름차순

select * from customer order by age asc; -- 나이가 적은 사람부터 조회 asc는 내림차순

-- 첫번째 정렬 후 똑같은 값을 또 다시 조회하고 싶을 때

select * from customer order by age desc, name asc; -- 나이가 많은 사람부터 조회한 후, 이름을 내림차순으로

select age from customer; -- 나이만 조회

select *  from customer where gender='M'; -- 성별이 'M'인 사람만 조회

select * from customer where gender='M' and bloodtype='B'; -- 성별이 남자인 사람중에서 혈액형이 O형인 사람만 조회

select * from customer where gender='M' and bloodtype='B' and name like '홍%'; -- 성별이 남자인 사람중에서 혈액형이 O형인 사람중에서 홍씨인 사람

select id,name,nvl(bloodtype, '혈액형이 없음') from customer where id='id-6'; -- null값이 들어간 부분에 메세지를 띄움, 결과는 혈액형에 null값이 없으므로 혈액형이 없음 이라고 뜸

select * from customer;

select sysdate from dual; --시스템 날짜 조회 데이터가 datatype

select to_char(sysdate) from dual; -- 시스템 날짜 조회 데이터가 문자열타입

-- y : 연도, yy(2자리), yyyy(4자리)
-- m : 월, d : 일, h : 시 - (24:hh24), 분 - mi, 초 - s

select to_char(sysdate, 'yyyy/mm/dd hh:mi:ss') from dual; -- 포맷 형태로 출력

select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') from dual; -- 포맷형태로 출력하되, 시간을 24간 형태로 출력

select avg(age) from customer; --전체나이 평균

select avg(age) from customer where age>=20 and age <30; --20대만 평균

select min(age) from customer; -- 나이가 젤 낮은값 출력

select age from customer; -- 전체 나이 조회

select sum(age) from customer; -- 나이의 전체 합

select max(age) from customer; -- 가장 높은 나이 출력

select bloodtype, max(age) from customer group by bloodtype; -- 각 혈액형 별 가장 높은 나이 출력

select bloodtype, max(age) from customer group by bloodtype having max(age) >= 40; -- 그룹에서 조건을 줄 때 having을 써서 조건을 준다 결과는 나이가 40보다 같거나 클 때 출력

create table address(
zipcode char(7) primary key,
si varchar2(30) not null,
dong varchar2(30) not null
);

desc address;

select * from tab;

alter table customer add(address_id char(7)); -- 외래키 설정을 위한 컬럼 추가

desc customer; -- customer 구조 보기

-- 제약조건 설정 구문 : constraint 이름 제약조건(column)
-- references address(zipcode) : address 테이블의 zipcode를 참조할꺼임

alter table customer add constraint customer_address_fk foreign key(address_id) references address(zipcode); -- 외래키 설정

select * from user_cons_columns; -- 제약조건 조회

insert into address values('111-111','서울시','송파구');
insert into address values('222-222','서울시','관악구');
insert into address values('333-333','서울시','동대문구');

select * from customer;

update customer set address_id='111-111' where id in('id-1','id-2','id-5'); -- customer 테이블에 address_id 넣기

select * from customer where id='id-1'; -- 1번 id-1인 거 조회

select * from address where zipcode='111-111'; -- 2번 111-111을 가진 거 조회

--위에 1번과 2번을 한꺼번에 조회

select id,name,age,tel,bloodtype,gender,zipcode,si,dong from customer,address where id = 'id-1' and zipcode=address_id; --한개의 데이터만 조회

select id,name,age,tel,bloodtype,gender,zipcode,si,dong from customer,address where id in('id-1','id-2') and zipcode=address_id; --두개의 데이터만 조회

select * from customer,address where address_id=zipcode; -- 주소가 있는 사람만 출력 -- equi join

select * from customer,address where address_id=zipcode(+); -- 주소가 있든 없든 모든 사람 출력 -- out join

select * from customer c, address a where c.address_id = a.zipcode(+); -- 별칭주기