1. #SQL쿼리문

#1 sql 쿼리문

 

-- 한줄 주석
/* 여러 줄 주석 */
--DB 생성
create database MemoDB
use MemoDB
select getdate()
--[1] Create문

 

create table dbo.Memos
(
num int identity(1,1) not null primary key,
name varchar(25) not null,
email varchar(50) null,
title varchar(150) not null,
postdate datetime default(getdate()),
postip varchar(15)
)
sp_help memos

--[2] 입력
insert into Memos (name,email,title,postdate,postip)
values('홍길동','h@h.com','안녕하세요',getdate(),'127.0.0.1')

insert into Memos (name,email,title,postdate,postip)
values('김길동','k@k.com','안녕하세요2',getdate(),'127.0.0.1')

insert into Memos (name,email,title,postdate,postip)
values('양길동','y@y.com','안녕하세요3',getdate(),'127.0.0.1')

--[3]출력
select * from Memos order by num desc;

--[4]상세
select num,name,title,postdate
from Memos where num=1

--[5]수정
update Memos
set name='백두산', email='b@b.com'
where num=1

--[6]삭제
delete from Memos where num=1
select * from Memos

 

#2 연습

create database ProductDB
use ProductDB

create table dbo.ProductDB
(

num int identity(1,1) not null primary key,
pocode varchar (25) not null,
pname varchar(50) null,
amount int not null,
price int not null
)
sp_help Product

insert into ProductDB (pocode,pname,amount,price)
values('p1','서버','100','1000')

insert into ProductDB (pocode,pname,amount,price)
values('p2','리눅스','200','25000')

insert into ProductDB (pocode,pname,amount,price)
values('p3','DB','10','1000')

select * from ProductDB

 

#3 연습2

declare @name varchar
declare @kor int
declare @eng int
declare @mat int
declare @tot int
declare @avg int

set @name='홍길동'
set @kor=80
set @eng=70
set @mat=70

set @tot = @kor + @eng + @mat
set @avg = @tot /3
select '평균' + CAST(@tot as nvarchar ) + '총점' + CAST(@avg as varchar)

 

#4 연습3

--저장 프로시저
--[입력]
create proc dbo.addMemo
(
 @name varchar(25),
 @email varchar(50),
 @tiile varchar(150),
 @postip varchar(15)
)
as
insert Memos(name,email,title,postdate,postip)
values(@name,@email,@tiile,GETDATE(),@postip)
exec addMemo '백라산','h@h.com','백라산입니다','127.0.0.1'
exec addMemo '송라산','h@h.com','송라산입니다','127.0.0.1'
exec addMemo '막라산','h@h.com','막라산입니다','127.0.0.1'
select * from Memos

--[2]출력
create proc dbo.getmemos
as
select * from Memos order by num desc

exec getmemos

--[3]상세
create proc dbo.GetMemo
@num int
as
select * from memos where num=@num
exec getmemo 2

--[4]수정
create proc dbo.EditMemo
@name varchar(20),
@Email varchar(50),
@num int
as
update memos
set name=@name,email=@Email
where num=@num
exec EditMemo '바보','b@b.com',2
exec GetMemo 2

sp_help addmemo

--[5]삭제
create proc dbo.DeletcMemo
@num int
as
delete from  Memos where num=@num
exec DeletcMemo 2
--프로 시저 삭제 drop proc프로 시저명