MS-SQL 쿼리문 정리

테이블 생성

create table sawon

        (

                sa_no int not null,

                sa_irum nvarchar(10),

                dept_no int not null,

                jik nvarchar(10) default '사원',

                pay int,

                ibsail datetime default getdate(),       /* getdate() 오늘날짜 */

                sa_sex varchar(4),

                mgr int

        )

-------------------------------------------------------------------

데이터 추가

insert into tb_member values

 ('wonbin','123456','원빈',810000,1000000,'wonbin.naver.com','135-902','서울시 강남구 압구정','010-333-444',getdate(),1)

-------------------------------------------------------------------

데이터 업데이트

update com_man set m_name='장동권', m_h='영화' where m_name='가'

update com_man set m_h=null where num=4   /* num=4를 null로 바꾼다 */

update com_man set m_h='작업' where (m_h is null) or (m_h='')

update com_man set m_name='원빈' , c_id=3, m_h=null where num=6

-------------------------------------------------------------------

조건 검색 정렬

select sa_irum as '이름',pay as '급여',floor(pay*0.7) as '보너스', floor((pay*12)+(pay*0.7)*6 ) as '연봉' from sawon

--오늘 날짜에 3년을 더하라

select dateadd(year, 3, getdate()) as '3년후'

-- TB_member  에서 이름과 나이를 출력

select s_name as '이름',datediff(year,'19'+left(zumin1,2),getdate()) as '나이' from tb_member

-- 입사일로 부터 30년을 더한값을 정년일로 하여 정년일 출력

select dateadd(year, 30, ibsail) as '정년일' from sawon

select id as '코드번호' from tb_com where com_name='삼성전자'

select com_name from tb_com where id <= 4 order by com_name desc   /* 내림차순 */

select com_name, tel, id from tb_com where com_name like '한%'

select tel from tb_com where tel like ('%1111') or (tel is null)

select * from sawon where jik=(select jik from sawon where sa_irum='최진실')

select * from sawon where pay > (select avg(pay) from sawon)

select dept_no as '부서', sa_irum as '이름' from sawon where dept_no=(select dept_no from sawon where sa_irum='최진실') order by dept_no, sa_irum

select * from sawon where pay=(select max(pay) from sawon where jik='과장') and jik='과장'

select count(distinct p_id) as '상품 수' from tb_order -- 상품 갯수 확인(중복 제거 distinct)

select sum(su) as '판매된 상품수' from tb_order -- 상품수

select  jik as '직책', avg(pay) as  '직책별 급여 평균' , count(jik) as '직책별 사원 수' , sum(pay) as '직책별 급여 합' from sawon where jik<> '사장' group by jik having count(jik) > 4

select p_id as '주문량 10개 이상 상품' , sum(su) as '수량' from tb_order group by p_id having sum(su) >= 10

select count(ibsail) as '사원수' ,  year(ibsail) as '입사년도' from sawon group by year(ibsail)

select dept_no as '부서번호',sum(pay) as '급여총합' from sawon where dept_no<30 group by dept_no select dept_no as '부서번호',sum(pay) as '급여총합' from sawon group by dept_no having dept_no<30

select tb_order.p_id as '제품번호', p_name as '제품명',go_no as '고객번호',sa_irum as '사원이름', dept_name as '부서명'  from tb_order, products, sawon, dept  where (tb_order.p_id=products.p_id) and (tb_order.go_no=sawon.sa_no) and (sawon.dept_no=dept.dept_no)

select p_name as '제품명' , p_price as '금액', sa_irum as '주문자' , go_no as '고객번호' from products, tb_order, sawon  where (products.p_id=tb_order.p_id) and (sawon.sa_no=tb_order.go_no) and (p_price > 100000)

-------------------------------------------------------------------

데이터 삭제

delete tb_member where cur is null

-------------------------------------------------------------------

프로시져

create proc a_dd @add nvarchar(50)  as

select * from ziptable where  addr like '%'+@add+'%'

a_dd '청파동2가'

-----------------------

create proc input @input int, @input2 nvarchar(20), @input3 nvarchar(50), @input4 nvarchar(30)  as

 insert into tb_com values(@input,@input2,@input3,@input4)

input 7,'인텔','미국','001'

-----------------------

create proc p_hpsu

 @hp nvarchar(20)  as

select @hp as '통신번호',count(*) as '회원수' from tb_member where hp like @hp+'%'

p_hpsu '010'

-------------------------------------------------------------------

begin tran -- 복구 시작 위치를 지정

rollback tran -- 복구