[ 5일차 ] SQL default값 설정하기, 뷰 테이블 별칭 실습+ lower,upper,initcap,replace,substr 함수
create table t100(
id varchar(10),
pwd varchar(20)
);
--1.컬럼 추가하기 age 형은 number(3)
alter table t100
add age number(3);
--2.컬럼 추가 loc 형은 nchar(3) 디폴트 '서울'
alter table t100
add loc nchar(3) default '서울';
--3.pwd의 형은 varchar(25)로 변경
alter table t100
modify (pwd varchar(25));
--4.id 에 기본키 추가
alter table t100
add primary key (id);
--5.임의의 레코드 3개 추가하기
insert into t100(id, pwd, age, loc) values ('aaa','a1234',22,''); --null값으로 보임 /칼럼명을 지정을해서 '' 공백으로 넣어주면 null 값으로 보임
insert into t100(id, pwd, age) values ('aaa','a1234',22); -- 칼럼명을 빼고 넣어야 없는 값이어서 디폴트값이 들어가게 된다
insert into t100(id, pwd, age, loc) values ('aaa','a1234',22,null); null로 넣으면 null 값으로 들어감
insert into t100(id, pwd, age, loc) values ('bbb','b1234',32,'인천');
insert into t100(id, pwd, age, loc) values ('ccc','c1234',42,'경기');
※※ null 인 값을 삭제 하려면
delete from t100
where loc is null;
--6.t100을 이용하여 v_t100 만들기
create view v_t100
as
select * from t100;
--7.v_t100을 이용하여 id와 pwd만 출력할 수 있는 v_t200을 만드시오
create view v_t200
as
select id, pwd from v_t100;
[뷰 생성시에 컬럼 별칭 짓기]
create view vv_t701(아이디,암호)
as
select id, pwd from v_t700;
create view vv_t702
as
select id "아이디1", pwd "암호1" from v_t700;
drop table school;
문제1. idx에 p.k 를 부여하여 school을 만드시오.
create table school(
idx number(3) primary key,
s_name nvarchar2(10),
age number(2),
tel nvarchar2(15)
);
문제2. 레코드를 추가하시오.
insert into school(idx, s_name, age, tel) values(101,'둘리',12,'010-5037-6133');
insert into school(idx, s_name, age, tel) values(102,'하니',15,'011-1234-1212');
insert into school(idx, s_name, age, tel) values(104,'영심이',17,'017-5555-0341');
insert into school(idx, s_name, age, tel) values(105,'young won',20,'010-5555-6363');
insert into school(idx, s_name, age, tel) values(107,'대한민국',22,'019-3245-1234');
insert into school(idx, s_name, age, tel) values(109,'아름이',25,'070-1212-5555');
문제3. 이름을 기준으로 내림차순 정렬하시오.
select * from school
order by s_name desc;
문제4. 번호가 101, 104, 107 인 학생의 레코드를 출력하시오
select * from school
where idx in (101, 104, 107);
문제5. 105번의 이름을 영원으로 변경하시오.
update school set s_name= '영원'
where idx= 105;
문제6. 104번의 나이를 15살로 수정하시오.
update school set age= 15
where idx= 104;
문제7. 이름이 아름이인 학생의 전화번호를 삭제하시오
update school set tel = ' '
where s_name='아름이';
문제8. 전체 학생의 나이를 19살로 수정하시오.
update school set age=19;
문제9. 102번 학생을 삭제하시오.
delete from school
where idx=102;
문제10. 전체 레코드를 삭제하시오.
delete from school;
--1)s_name, tel의 값의 길이를 구하시오
select s_name, length(s_name) as 길이1 ,tel, length(tel) as 길이2
from school;
--2)전체의 값을 하나의 컬럼에 나타내시오
select idx|| s_name|| age|| tel as 학생정보
from school;
select '번호:' ||idx||',이름:' ||s_name|| ',나이:'|| age|| ',전화번호:'|| tel as 학생정보
from school;
--뷰 생성 하기
create view v_school
as
select '번호:' ||idx||',이름:' ||s_name|| ',나이:'|| age|| ',전화번호:'|| tel as 학생정보
from school;
select * from v_school;
--첫번째 쉼표의 위치가 어디있는지 찾아보기
select instr (학생정보, ',',1) from v_school;
--두번째 있는 쉼표의 위치가 어디있는지 찾아보기 (찾을 시작위치를 적어주는 것)
select instr (학생정보, ',',8) from v_school;
select instr (학생정보, ',',16) from v_school;
select instr (학생정보, ',',22) from v_school;
--기본 테이블 확인
select * from school;
insert into school(idx, s_name, age, tel) values(200,'Choi Young',25,'070-1212-5555');
--문자열 소문자(lower), 대문자(upper), 첫번째 글자를 대문자(initcap)로 바꾸기
select lower(s_name) , upper(s_name) , initcap(s_name) from school;
--위의 테이블로 뷰를 만들기
create view v_school2
as
select lower(s_name) , upper(s_name) , initcap(s_name)
from school;
이렇게하면 오류가 난다 칼럼명에는 괄호를 못쓰기 때문에
create view v_school2
as
select lower(s_name) as 소문자, upper(s_name) as 대문자 , initcap(s_name) as 첫글자대문자
from school;
별칭을 써서 만들어주면 생성 된다
create view v_school2 (소문자, 대문자, 첫글자대문자)
as
select lower(s_name) , upper(s_name) , initcap(s_name)
from school;
뷰이름 옆에 괄호로 컬럼명을 설정해 줄 수도 있다
select * from v_school2;
-- 기본 테이블 나타내기
select * from school;
--문자열을 문자열로 변환하는 함수 REPLACE
SELECT replace(tel, '-','') from school;
tel 에서 -를 공백('') 으로 바꾸기
--소문자로 바꿔준 young를 찾아서 한글 영 으로 바꿔준것
SELECT s_name, replace(lower(s_name), 'young','영') from school;
select tel,translate(tel,'0123','영일이삼') from school;
이런 경우에 1대1로 바뀌어서 0인게 영 1인게 일 2인게이 3인게삼으로 다 바뀜
묶여서 '0123'만 '영일이삼'으로 바뀌는게 아님
select * from school;
--선택한 문자열만 보기
select tel, substr(tel,1,3) from school;
-- substr(컬럼명, 시작위치, 길이)
select tel, substr(tel,5,4) from school;
select tel, substr(tel,1,3), substr(tel,5,4), substr(tel,10,4) from school;
--합쳐서 하나의 컬럼으로 만들기, null값은 안보이게 하기
select substr(tel,1,3)||substr(tel,5,4)|| substr(tel,10,4) as 전화번호 from school
where tel is not null;
--위의 것을 참조하여 테이블을 만들기
create table t11
as
select substr(tel,1,3)||substr(tel,5,4)|| substr(tel,10,4) as 전화번호 from school
where tel is not null;
select * from t11;
--전화번호 컬럼에 다시 기호'-' 를 추가하기
select substr(전화번호,1,3)||'-'||substr(전화번호,4,4)||'-'||substr(전화번호,8,4) from t11;