ms-sql 기본내용

@@XXX : 시스템 함수
@XXX : 사용자 함수
##XXX : 임시테이블, 프로시저
#XXX : 전역 임시 개체

선언
DECLARE @X1 sql_variant,@X2 sql_variant
초기화
set @X1 = 12345
set @X2 = '문자열'
출력
select @X1
select @X2


테이블 정의 하는 방법

DECLARE @X TABLE (id int,name varchar(10))
INSERT @X values(100,'mssql')
SELECT * FROM @X

함수활용

평균값 구하기


select avg(price) as 평균 from titles

합계값 구하기

select sum(price)as 합계 from titles

개수 구하기
select count(price) as 갯수 from titles
null 값은 제외

select count(distinct price) as 중복제외 from titles

select count(*) from titles
-- null 포함

select max(price) as 최대값 from titles

select min(price) as 최소값 from titles

날짜 함수 중요

select getdate() -- 시스템의 날짜시간 값을 리턴

dateadd -- 기존날짜 데이터에 일,월,년 등의 날짜데이터를 더한다.
select pubdate from titles
select dateadd(day,21,pubdate) as 날짜 from titles

datediff -- 날짜 데이터로부터 지정일까지의 년,월,일,시간을 리턴
select datediff(day,pubdate,getdate()) as [날짜 간격] from titles
select datediff(month,pubdate,getdate()) as [월 간격] from titles
select datediff(year,pubdate,getdate()) as [년 간격] from titles


datepart -- 지정년도의 년,월 ,일 등의 특정 부분만 리턴
select datepart(month,getdate()) as 이번달

1. ABS : 절대값을 리터

select abs(-1)
select abs(100)

2. RAND -- 0 ~ 1 사이의 값을 랜덤하게 발생
select rand(12345)
select rand(1)

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )

3. ROUND -- 지정한 특정 소수점에서 반올림
select round(111.123456,3) -- 소수점 3째자리에서 반올림

-- STRING FUNCTION --

1. LEFT -- 문자열의 왼쪽부터 지정된 자리수 까지 리턴
select left(title,6) from titles

2. RIGHT -- 문자열의 오른쪽부터 지정된 자리수 까지 리턴
select right(title,6) from titles

3. LEN -- 문자열의 문자 개수를 리턴
select len(title) as '타이틀의 문자갯수' from titles
LOWER -- 대문자를 소문자로 변환
select lower(title) as '대문자를 소문자로' from titles
UPPER -- 소문자를 대문자로 변환
select upper(title) as '소문자를 대문자로' from titles

4. LTRIM : 문자열의 왼쪽에 공백을 제거
RTRIM: 문자열의 오른쪽에 공백을 제거

DECLARE @XXX CHAR(10)
SET @XXX = ' 12345'
SELECT LTRIM(@XXX)
SELECT @XXX

DECLARE @XXX CHAR(10)
SET @XXX = '12345 '
SELECT RTRIM(@XXX)
SELECT @XXX

5. PATINDEX -- 문자열에서 지정한 패턴이 시작되는 위치(숫자값)을 리턴
select title from titles
select patindex('%t%' , title) from titles

6. REPLACE -- 문자열에서 특정 문자를 지정한 문자로 바꿔줌
select replace('데이터베이스반 공부 잘 해요','잘','안')

7. STUFF -- 문자열에서 지정한 위치에서 위치까지 특정 문자열를 지정한 문자로 바꿔줌
select stuff('디비반 사실은 공부 열심히 잘 해요',6,5,'부공')

8. substring -- 문자열의 지정 위치에서 지정 개수만큼 문자열을 리턴
select substring(title,3,10) from titles

-- where 조건절 --

select 컬럼명 from 테이블명(뷰)
where 조건

select * from sales
where qty > 40

select * from sales
where (qty > 10) and (qty < 40)

< , > , <= , >= , = , l ,& , %, ! , !=

between -- ~서 ~ 까지의 범위
like -- 일치하는 데이터

is null -- 널인경우,
is not null -- 일치하지 않는

select * from titles
where price is null

select * from titles
where price is not null

select * from titles
where type in('mod_cook')

-- 위와 같음
select * from titles
where type = 'mod_cook'

-- 두개가 포함된 레코드만 출력
select * from titles
where type in('mod_cook','business')

-- 위와 같음
select * from titles
where type = 'mod_cook' or type = 'business'

--반대의 경우
select * from titles
where type not in('mod_cook','business')

-- LIKE 함수 --

select 컬럼명 from 테이블명(뷰)
where 조건 like '문자열 및 패턴'

select * from titles
where title like '%with%'

select * from titles
where title like '%sheets'

-- _ (언더바) 문자하나의 와일드 카드에 해당함
select * from titles
where title like 'cookin_%'


문제) pubs 의 sales 테이블에서 stor_id 가 7066 이상 이고 qty 가 20 이상인 데이터에 날짜를 24일 더한 결과를 도출하라.

select stor_id,ord_num,dateadd(day,24,ord_date) as '날짜 24일 더하기' ,qty,payterms,title_id from sales
where stor_id >= 7066 and qty >= 20


-- 중복 제거 --
distinct

select distinct qty from sales
select qty from sales

-- 단 text , image, ntext의 데이터 형식에는 distinct 를 사용할수 없다.

-- 정렬 --
select 컬럼명 from 테이블(뷰)
where 조건
order by 컬럼

select * from sales
where qty > 10
order by qty asc

-- 기본적인 정렬은 오름차순(asc) 이다 생략가능

select * from sales
where qty > 10
order by qty desc -- 내림차순 생략불가


select * from sales
where qty > 10
order by qty desc , stor_id asc


-- group by --
반드시 집계함수가 써야 되고 집계함수를 사용한 컬럼에 대한

select 컬럼명 from 테이블(뷰)
where 조건
group by 컬럼
order by 컬럼

select * from titles

select pub_id as 저자id, type as 종류,price as 가격
from titles
group by pub_id , type , royalty, ytd_sales

-- 책종류별로 팔린 SUM 가격 값을 알고자 할때
select type as 종류,sum(price) as 가격
from titles
group by type
-- 해빙은 group by 시 조건을 줄때 사용
having sum(price) is not null and sum(price) > 50

select pub_id as 저자id, type as 종류 , price as 가격
from titles

          *** top ***

결과집합에 반환되는 행수를 제한
top n -- 상위 n개 도출
top n precent -- 상위 n% 도출
top n with ties -- 같은 순위의 것을 모두 가지고 올때

use pubs
select * from sales
select top 6 qty, title_id from sales
select top 6 with ties qty,title_id from sales
order by qty

*** 범위 ***

between ~ and ~ : ~에서 ~까지의 데이터를 가지고 온다

select title_id ,qty from sales
where qty between 10 and 20


*** compute / compute by ***
select type,price,advance
from titles
order by type
compute sum(price) , avg(price) by type

grouping
행이 cube 또는 rollup 연산자를 통해 추가될때 추가열의 출력이 1 아니면 0 이 출력
(월래 있는 데이터는 0 이고 새롭게 생긴 열들은 1로 표현)
그룹화는 cube 과 rollup


문제1) pubs 데이터베이스에 sales 테이블 수량이 20 이상인 행들을 가지고 있고,
연산후에 그결과 나나탄 수량의 합이 30 이상인 것?
(TITLE_ID, QTY)

select title_id,sum(qty) as 합계 from sales
where qty >= 20
group by title_id
having sum(qty) >=30
order by sum(qty)

select sum(qty) from sales
where qty >=20

문제2) pubs 데이터베이스에 sales 테이블 stor_id 가 7067 과 7131인 데이터들의
qty 합계에 대한 소계를 보는 퀴리를 작성 하세요.
(STOR_ID, QTY)

select stor_id , sum(qty) as 합계 from sales
where stor_id != 7067 and stor_id != 7131
--where stor_id not in (7067,7131)
group by stor_id
with rollup



sp_tables
select * from authors
select * from publishers

*** inner 조인 ***

-- 반드시 별칭을 주어야 합니다.
-- 조인 대상이 어디 있는지를 지정하기 위해서 별칭을 주어야 합니다.
-- 가독성을 높이는 역할을 합니다.

select *
from authors as a inner join publishers as p
on a.city = p.city

SELECT *
FROM AUTHORS AS A , PUBLISHERS AS P
WHERE A.CITY = P.CITY


--- inner 는 생략 가능 ---
select *
from authors as a join publishers as p
on a.city = p.city

*** outer 조인 ***
LEFT or RIGHT OUTER JOIN
LEFT 는 조금 사용 RIGHT 는 거의 사용안함
없는 경우 NULL(정의 되지 않는)

select title,stor_id,ord_num qty, ord_date
from titles left outer join sales
on titles.title_id = sales.title_id



문제3) pubs , titles , titleauthor
price가 20 이상인 데이터에 대한
title_id, title,price , au_id 정보를 보이세요

select t.title_id,t.title,t.price,ta.au_id from
titles as t inner join titleauthor as ta
on t.title_id = ta.title_id
where t.price >=10



다운로드
stand.jpg (26.5KB)

의견 1 신규등록      목록