기린군 블로그

begin tran
--view
/*
보다, 실제테이블은 아니며, 실제 테이블의 특정 부분을 보기 위한
구문을 담고있는 객체.
보안 , 편리성
view를수정할경우 기본값도 수정됨
*/

create view view01
as
select empno, ename, sal
from emp

select * from view01

create view view02
as
select empno, emp.deptno, dname
from emp, dept
where emp.deptno = dept.deptno
 and emp.deptno in(10,20)
 
select* from view02

update view01
set sal = 9999
where ename = 'smith'

select * from emp

insert view01 values(8888,'tom',500)

alter view view01 with encryption
as
select empno, ename, sal
from emp

/*
view03
직업별 평균 월급을 담는 뷰 생성.
*/

alter view view01
as
select job , avg(sal) as [avgsal]
from emp
group by job

update view01
set avgsal = 3000
where job = 'clerk'

/*
view04
사원이름, 월급을 담는 뷰 생성 단, 월급이 높은 사람부터 출력(내림차순)
*/

--order by는 바로 view에 담길수없다.
alter view view02
as
select top 50 ename, sal
from emp
order by sal desc

/*
view05
사원번호, 이름, 월급을 담는 뷰, 단 월급이 2000이상
*/

alter view view02
as
select empno,ename,sal
from emp
where sal > 2000

insert view02 values(7777,'tom',2500)
insert view02 values(8888,'kim',1500)
insert view02 values(9999,'lee',500)

select * from emp

select * from view02

delete from emp
where empno in(7777,8888,9999)

--1. 아래와 같은 조건으로 'SQL좌석번호'이름의 데이터베이스를 만들어라.
CONDITION
/*1. FILE_GROUP: PRIMARY ONLY
2. DATA_FILE
 - LOGICAL_NAME: SQL좌석번호_DATA
 - PHYSICAL_NAME:
   C:\Program Files\Microsoft SQL Server\MSSQL\Data\sql좌석번호data.mdf
 - SIZE: 3MB
 - MAXSIZE: 10MB
 - FILE_GROWTH: 10%
3. LOG_FILE
 - LOGICAL_NAME: SQL좌석번호_LOG
 - PHYSICAL_NAME:
   C:\Program Files\Microsoft SQL Server\MSSQL\Data\sql좌석번호log.ldf
 - SIZE: 1MB
 - MAXSIZE: 8MB
 - FILE_GROWTH: 10%
*/
create database SQLNum
on primary
(
 name='SQLNum_DATA'
, filename='d:\test\SQLNumData.mdf'
, size=3mb
, maxsize=10mb
, filegrowth=10%
)
log on
(
 name='SQLNum_LOG'
, filename='d:\test\SQLNumLog.lef'
, size=1mb
, maxsize=8mb
, filegrowth=10%
)

exec sp_help SQLNum

-- 2. SQL좌석번호 데이터베이스에서 아래와 같이 EMP02라는 테이블을 만들고
-- 데이터를 입력하라.

create table EMP02
(
 EMPNO int
, ENAME varchar(12)
, SAL int
, HIREDATE datetime
)

alter table EMP02
add DEPTNO int

/*
COLUMN_NAME EMPNO ENAME  SAL HIREDATE DEPTNO
DATA_TYPE INT VARCHAR(12) MONEY SMALLDATETIME INT
  111 JOHN  3000 2000-06-30 1
  112 HUNTER  2500 2001-08-03 2
  113 SMITH  3500 1999-09-23 3
  114 CARRY  2500 2001-08-02 3
*/

-- 3. EMP02 테이블에 MGR 컬럼(INT)을 추가하라.

alter table emp02
add MGR int

-- 4. EMP02 테이블에서 SAL 컬럼의 데이터형을 INT로 바꾸어라.

alter table emp02
alter column SAL int

exec sp_help emp02

-- 5. EMP02 테이블에서 MGR 컬럼을 삭제하라.

alter table emp02
drop column MGR

-- 6. EMP02 테이블을 삭제하라.

-- 7. SQL좌석번호 데이터베이스를 삭제하라.

drop database SQLNum

create table test01
(
 eid int not null
, ename char(10)
)

--데이터베이스생성.
/*
testdb
d:\test
데이터파일 이름 testdb.mdf
처음크기 : 5mb
증가단위 : 1mb
최대크기 :10mb
로그파일 이름 testdblog.ldf

*/

create database testdb
on primary
( name='testdata'
, filename='d:\test\testdb.mdf'
, size=5mb
, maxsize=10mb
, filegrowth=1mb
)
log on
( name='testlog'
, filename='d:\test\testdblog.ldf'
, size=6
, maxsize=10mb
, filegrowth=10%
)

--select * from test01

drop database testdb

create table test01
(
 empno int,
 ename char(10)
)

exec sp_help test01

--alter 열추가
alter table test01
add hiredata datetime

--교체
alter table test01
alter column ename char(5)

--삭제
alter table test01
drop column hiredata

--@@ 대소문자구분

create table test01
(
 eid int
, ename char(10)
)

insert test01 values(1,'aaa')
insert test01 values(2,'AAA')
insert test01 values(3,'Aaa')
insert test01 values(4,'aAa')
insert test01 values(5,'aaA')
insert test01 values(6,'AAa')
insert test01 values(7,'aAA')
insert test01 values(8,'AaA')

select * from test01
where ename = 'aaa'

create table test02
(
 eid int
, ename char(5) collate korean_wansung_cs_ai
)

insert test02 values(1,'aaa')
insert test02 values(2,'AAA')
insert test02 values(3,'Aaa')
insert test02 values(4,'aAa')
insert test02 values(5,'aaA')
insert test02 values(6,'AAa')
insert test02 values(7,'aAA')
insert test02 values(8,'AaA')

select * from test02 where ename = 'Aaa'

exec sp_help emp
select * from emp

--테이블명 수정.
exec sp_rename test02 , test05

exec sp_rename 'test05.ename' , 'sname'

exec sp_renamedb test408 , test

create table test03
(
 eid int
, ename char(10)
)

exec sp_help test03

alter table test03
alter column eid char(5)

alter table test03
alter column ename int

insert test03 values('12345',345)

alter table test03
alter column eid char(3)

alter table test03
alter column ename char(10)

create table test04
(
 eid int
, ename char(10)
)

insert test04 values (1,'a');
insert test04 values (2,'bbb');
insert test04 values (3,'cccc');

alter table test04
alter column ename int

insert test01 values(1,'aaa')
insert test01 values(2,'bbb')
insert test01 values(null,'ccc')
insert test01 values(3,null)

select * from test01

2. unique
/*
유일한 값, 중복되는 값이 없다.
테이블에 여러 컬럼에 선언이 가능하다.
null이 한번만 가능하다.
*/

create table test02
(
 eid int unique
, ename char(10)
)

insert test02 values(1,'aaa')
insert test02 values(2,'bbb')
insert test02 values(1,'ccc') --에러
insert test02 values(null,'bbb')
insert test02 values(null,'ddd') --에러
insert test02 values(3,'aaa')

exec sp_help test02
exec sp_helpconstraint test02

create table test03
(
 eid int constraint uq_test03 unique
, ename char(10)
)

alter table test03
drop constraint uq_test03

alter table test03
add constraint uq_test03_01 unique(eid)

exec sp_helpconstraint test03

3. default

create table test04
(
 eid int
, ename char(10)
, city char(10) constraint df_test04 default '서울'
)

insert test04 values(1,'aaa','대구')
insert test04 values(2,'bbb','인천')
insert test04 values(3,'ccc','광주')
insert test04 values(3,'ccc','광주')
insert test04 (eid, ename) values(4,'ddd')
insert test04 (eid, ename)values(5,'eee')
insert test04 (eid, ename)values(6,'fff')

select * from test04

4. check
/*
컬럼에 원하는 특정한 값만 입력을 받고싶을 때 사용
insert,update에서만 검사한다.
*/

create table test05
(
 eid int
, ename char(10)
, deptno int constraint ch_test05 check(deptno in(10,20,30))
)

insert test05 values(1,'aaa',10)
insert test05 values(2,'bbb',20)
insert test05 values(3,'ccc',30)
insert test05 values(4,'ddd',20)
insert test05 values(5,'eee',50) --에러

alter table test05
drop constraint ch_test05

select * from test05

alter table test05 with nocheck
add constraint ch_test05 check (deptno in(10,20,30))

exec sp_helpconstraint test05

create table test06
( eid int
, ename char(10)
, deptno int
)

alter table test06
alter column eid int not null

alter table test06
add constraint uq_test06 unique(ename)

exec sp_helpconstraint test06

alter table test06
add constraint df_test06 default 10 for deptno

5. primary key
/*
테이블당 하나의 제약 조건만 선언 가능하다.
not null 속성, unique 속성을 갖고 있다.
*/

create table test07
(
 eid int constraint pk_test07 primary key
, ename char(10)
)

insert test07 values (1,'aaa')
insert test07 values (2,'bbb')
insert test07 values (3,'ccc')
insert test07 values (1,'ddd')--에러
insert test07 values (null,'eee')--에러

create table test08
( eid int
, ename char(10)
)

exec sp_help test08

alter table test08
alter column eid int not null

alter table test08
add constraint pk_test08 primary key(eid)

alter table test08
drop constraint pk_test08

6.foreign key
/*
primary key 를 참조하는 제약 조건
*/

create table pktest
( eid int
, ename char(10)
, deptno int constraint pk_pktest primary key
)

add constraint pk_pktest primary key

create table fktest
( saname char(10)
, sadept int constraint fk_fktest foreign key
 references pktest(deptno)
)

insert pktest values(1,'aaa',10)
insert pktest values(2,'aaa',20)
insert pktest values(3,'aaa',30)
insert pktest values(3,'aaa',40)

insert fktest values('tom',10)
insert fktest values('bill',20)
insert fktest values('kim',30)
insert fktest values('lee',40)
insert fktest values('park',50)--에러

alter table fktest
drop constraint fk_pktest

alter table fktest
drop constraint fk_fktest

alter table pktest
add constraint pk_pktest primary key(sadept)

alter table fktest
add constraint fk_fktest foreign key(sadept)
references pktest(deptno)