|
--============================================================--
-- UPDATE
--============================================================--
-- 기존에 입력된 값을 변경하기 위해서는 UPDATE 문을 사용
UPDATE testTbl3
SET tel = '없음'
WHERE Fname = 'Kim'
-- 구매 테이블에서 현재의 단가에서 모든 단가가 1.5배
-- 인상되었다면 아래와 같이 사용할 수 있다.
USE sqlDB
UPDATE buyTbl SET price = price * 1.5
SELECT * FROM buyTbl
--============================================================--
-- INSERT
--============================================================--
-- INSERT는 데이터를 삽입하는 명령어 이다.
-- Create 테이블 생성
USE tempDB
create table testTbl1 (id int, username nchar(3), age int);
go
insert into testTbl1 values (1, '희동구', 25)
-- id, name을 입력하고 나이를 입력하고 싶지 않다면 아래와 같이
-- 테이블 이름 뒤에 입력할 열의 목록을 나열해준다.
insert into testTbl1(id, username) values( 2, '아드북')
-- 이 경우 age에는 NULL이 들어간다.
-- 열의 순서를 바꿔서 입력하고 싶을 때도 꼭 열 이름을 나열해줘야 한다.
insert into testTbl1(username, age, id) values('홍명보', 31, 3)
-- drop 해서 테이블 없애고, adventureWorks의 테이블 내용을
-- 복사해보자.
drop table testTbl1
use tempDB
create table testTbl3 (id int, Fname nvarchar(50), Lname nvarchar(50), tel nvarchar(25))
go
insert into testTbl3
select ContactID, FirstName, LastName, Phone
from Adventureworks.Person.Contact
use kss25
go
declare @tablecount nvarchar(100) -- 변수 선언
set @tablecount = '' -- 변수 초기화
-- 이 쿼리를 통해서 변수에 담는다.
select @tablecount =
(
select count(*) from tbl_rep_btinfo
)
print @tablecount -- 정보를 출력해 본다.
select * from tbl_kss_kicbo -- 정보를 파악한다.
--============================================================--
-- SELECT + GROUP BY, HAVING 절
--============================================================--
use sqlDB
go
-- 그룹으로 묶어 주는 역활을 한다.
-- 구매 테이블에서 사용자가 구매한 물품의 개수를 보기 위한 쿼리
select userid, amount from buyTbl order by userid
/* 결과
userid amount
-------- ------
AJH 10
CJC 5
CJC 5
CJC 2
CJC 2
LCS 1
PJY 3
PJY 2
PJY 1
SKH 5
SKH 1
SKH 1
*/
-- SUM() 함수를 사용해서 구매 개수를 합치고 사용자를 하나로 묶어 주는 절
select userid, sum(amount) from buyTbl group by userid
/* 결과
userid
-------- -----------
AJH 10
CJC 14
LCS 1
PJY 6
SKH 7
*/
-- 별칭을 붙여서 컬럼 이름을 정하는 쿼리
select userid as [사용자 아이디], sum(amount) as [총 구매 개수]
from buyTbl group by userid
/* 결과
사용자 아이디 총 구매 개수
-------- -----------
AJH 10
CJC 14
LCS 1
PJY 6
SKH 7
*/
-- 구매액의 총합을 출력해 보자. 구매액은 가격 * 개수이므로,
-- 총합은 sum()을 사용하는 쿼리
select userid as [사용자 아이디], sum(price*amount) as [총 구매 액]
from buyTbl group by userid
/* 결과
사용자 아이디 총 구매 액
-------- -----------
AJH 150
CJC 2120
LCS 200
PJY 1210
SKH 140
*/
--============================================================--
-- SELECT + 집계 함수
--============================================================--
/*
-- AVG() : 평균을 구한다.
-- MIN() : 최소값을 구한다
-- MAX() : 최대값을 구한다.
-- COUNT() : 행의 개수를 센다.
-- COUNT_BIG() : 행의 개수를 센다. 단 결과값이 bigint 형이다.
-- STDEV() : 표준편차를 구한다.
*/
-- 전체 구매자가 구매한 물품의 개수(AMOUNT)의 평균을 구해보자.
select avg(amount) as [평균구매개수] from buyTbl
/* 결과
평균구매개수
-----------
3
*/
-- CAST(), CONVERT() 함수 사용해서 실수로 변환
select avg(amount * 1.0) as [평균구매개수] from buyTbl
/* 결과
평균구매개수
---------------------------------------
3.166666
*/
-- 사용자별 평균 구매 개수를 구해보도록 하자.
-- group by 를 사용한다.
select userid, avg(amount * 1.0) as [평균구매개수] from buyTbl
group by userid
/* 결과
userid 평균구매개수
-------- ---------------------------------------
AJH 10.000000
CJC 3.500000
LCS 1.000000
PJY 2.000000
SKH 2.333333
*/
-- 가장 큰 키와 가장 작은 키의 값을 출력하는 쿼리문
select name, max(height), min(height) from userTbl
/* 결과
메시지 8120, 수준 16, 상태 1, 줄 1
열 'userTbl.name'이(가) 집계 함수나 GROUP BY 절에 없으므로
SELECT 목록에서 사용할 수 없습니다.
*/
-- group by 없이는 별도의 열을 집계 함수와 같이 사용할 수 없다는 메시지
select name, max(height), min(height) from userTbl group by name
/* 결과
name
---------- ------ ------
김남일 183 183
박주영 178 178
박지성 181 181
설기현 178 178
송종국 185 185
안정환 182 182
이영표 181 181
이천수 179 179
조재진 179 179
최진철 142 142
*/
-- 마지막 원하는 결과값
select name, height
from userTbl
where height = (select max(height) from userTbl)
or height = (select min(height) from userTbl)
/* 결과
name height
---------- ------
최진철 142
송종국 185
*/
--============================================================--
-- SELECT + HAVING
--============================================================--
-- 우선 이 쿼리 부터..
select userid as [사용자], sum(price * amount) as [총구매액]
from buyTbl
group by userid
/* 결과
사용자 총구매액
-------- -----------
AJH 150
CJC 2120
LCS 200
PJY 1210
SKH 140
*/
-- 이중에서 1000 이상인 사용자만 출력하고 싶을때 조건을 추가
select userid as [사용자], sum(price * amount) as [총구매액]
from buyTbl
where sum(price * amount) > 1000
group by userid
/* 결과
메시지 147, 수준 15, 상태 1, 줄 1
집계가 HAVING 절이나 SELECT 목록에 포함된 하위 쿼리 내에 없으면
WHERE 절에 나타날 수 없습니다. 또한 집계 중인 열은 외부 참조입니다.
>> 오류 메시지를 보면 집계 함수는 where 절에 나타날 수 없다는 예기이다.
이럴때 having를 사용한다.
*/
-- where error -> having 으로 교체
select userid as [사용자], sum(price * amount) as [총구매액]
from buyTbl
group by userid
having sum(price * amount) > 1000
/* 결과
사용자 총구매액
-------- -----------
CJC 2120
PJY 1210
*/
-- 추가로 총구매액이 적은 것부터 나타낸다.
select userid as [사용자], sum(price * amount) as [총구매액]
from buyTbl
group by userid
having sum(price * amount) > 1000
order by sum(price * amount) asc
/* 결과
사용자 총구매액
-------- -----------
PJY 1210
CJC 2120
*/
--============================================================--
-- SELECT
--============================================================--
-- 가장 간단한 select 구문
select * from userTbl
/* 결과
userID name birthYear addr mobile1 mobile2 height
-------- ---------- ----------- ---- ------- -------- ------
AJH 안정환 1979 강원 NULL NULL 182
CJC 최진철 1975 제주 011 0000000 142
JJJ 조재진 1986 충북 019 3333333 179
*/
--============================================================--
-- SELECT + WHERE + 관계 연산자
--============================================================--
-- where 가 포함된 select 구문
select * from userTbl where name = '박지성'
/* 결과
userID name birthYear addr mobile1 mobile2 height
-------- ---------- ----------- ---- ------- -------- ------
PJS 박지성 1983 서울 011 1111111 181
*/
-- 1980년 이후에 출생하고, 신장이 180 이상인 사람의 아이디와 이름을 조회
select userID, Name from userTbl where birthYear >= 1981 and height >= 180
/* 결과
userID Name
-------- ----------
LYP 이영표
PJS 박지성
*/
-- 1980년 이후에 출생했거나, 신장이 180 이상인 사람의 아이디와 이름을 조회
select userID, Name from userTbl where birthYear >= 1981 or height >= 180
/* 결과
userID Name
-------- ----------
AJH 안정환
JJJ 조재진
KNI 김남일
LCS 이천수
LYP 이영표
PJS 박지성
PJY 박주영
SJK 송종국
*/
--============================================================--
-- SELECT + WHERE + BETWEEN... AND 와 IN()
--============================================================--
-- 신장이 180 ~ 183인 사람을 조회
select name, height from userTbl where height >= 180 and height <= 183
/* 결과
name height
---------- ------
안정환 182
김남일 183
이영표 181
박지성 181
*/
-- 신장이 180 ~ 183인 사람을 조회 ( between )
select name, height from userTbl where height between 180 and 183
/* 결과
name height
---------- ------
안정환 182
김남일 183
이영표 181
박지성 181
*/
-- 키가 정확하게 178, 180, 182 인 사람의 정보만을 조회
select name, height from userTbl where height=178 or height=180 or height=182
/* 결과
name height
---------- ------
안정환 182
박주영 178
설기현 178
*/
--============================================================--
-- SELECT + WHERE + ANY, ALL, SOME 그리고 하위쿼리(SubQuery, 서브쿼리)
--============================================================--
-- 박지성의 보다 키가 큰 사람의 이름과 키를 출력하려면 where 조건에 박지성의
-- 키를 적어 줘야 한다.
select name, height from userTbl where height > 181
/* 결과
name height
---------- ------
안정환 182
김남일 183
송종국 185
*/
-- 이를 쿼리를 통해서 사용할 수 있다.
select name, height from userTbl
where height > (select height from userTbl where name='박지성')
/* 결과
name height
---------- ------
안정환 182
김남일 183
송종국 185
*/
-- 구문 011에 포함된, 내용이 두개 이상이기 때문에 오류가 납니다.
select name, height from userTbl
where height >= (select height from userTbl where mobile1='011')
-- >> 두개의 결과값이 한개 이상이라서 오류가 난다.
-- >> 이때 해결할 것은 ANY를 사용하면 된다.
/* 결과
메시지 512, 수준 16, 상태 1, 줄 1
하위 쿼리에서 값을 둘 이상 반환했습니다
*/
-- ANY 011에 포함된 내용을 모두 사용해서 결과값을 뽑는다.
select name, height from userTbl
where height >= ANY (select height from userTbl where mobile1='011')
/* 결과
name height
---------- ------
안정환 182
최진철 142
조재진 179
김남일 183
이천수 179
이영표 181
박지성 181
박주영 178
송종국 185
설기현 178
*/
-- ALL 을 사용해서 처리
select name, height from userTbl
where height >= ALL (select height from userTbl where mobile1='011')
/* 결과
name height
---------- ------
안정환 182
김남일 183
이영표 181
박지성 181
송종국 185
*/
--
select name, height from userTbl
where height = ANY (select height from userTbl where mobile1='011')
/* 결과
name height
---------- ------
최진철 142
조재진 179
이천수 179
이영표 181
박지성 181
박주영 178
설기현 178
*/
-- ANY, IN 두개 동일한 의미이다.
select name, height from userTbl
where height IN (select height from userTbl where mobile1='011')
/* 결과
name height
---------- ------
최진철 142
조재진 179
이천수 179
이영표 181
박지성 181
박주영 178
설기현 178
*/
--============================================================--
-- SELECT + WHERE + ORDER BY 절
--============================================================--
-- ORDER BY
select name, height from userTbl order by height desc
/* 결과
name height
---------- ------
송종국 185
김남일 183
안정환 182
이영표 181
박지성 181
이천수 179
조재진 179
박주영 178
설기현 178
최진철 142
*/
-- order by 를 두개 사용, desc, asc 두개를 혼합
select name, height from userTbl order by height desc, name asc
/* 결과
name height
---------- ------
송종국 185
김남일 183
안정환 182
박지성 181
이영표 181
이천수 179
조재진 179
박주영 178
설기현 178
최진철 142
*/
--============================================================--
-- SELECT + WHERE + DISTANCE, TOP(N), TABLESAMPLE
--============================================================--
-- 거주지역이 몇 군데인지 출력
select addr from userTbl
/* 결과
addr
----
강원
제주
충북
경북
인천
전북
서울
경기
경기
서울
*/
-- 거주지역에 order by 사용
select addr from userTbl order by addr
/* 결과
addr
----
강원
경기
경기
경북
서울
서울
인천
전북
제주
충북
*/
-- DISTINCT 를 사용해서 중복내용을 삭제
select distinct addr from userTbl
/* 결과
addr
----
강원
경기
경북
서울
인천
전북
제주
충북
*/
