USE SQLEx
--입사한지12개월이 지난직원들을대상
--185만원이상되는각부서별평균월급
SELECT AVG(salary), depart
FROM tblGroup
WHERE workMonth > 12
GROUP BY depart
having avg(salary) >= 180
ORDER BY AVG(salary) DESC;
SELECT * FROM tblGroup;
--관리부, 생산부대상
SELECT depart, AVG(salary)
FROM tblGroup
GROUP BY depart
HAVING depart not in('영업부');
--compute
-- : 집계함수와사용
SELECT AVG(salary) FROM tblGroup;
SELECT * FROM tblGroup compute AVG(salary);
--******************************************************************************************
SELECT name, LEN(name) FROM tblCountry
SELECT * FROM tblCountry ORDER BY LEN(name) DESC, name DESC, popu DESC;
--1차나라이름정렬하되똑같은나라이름끼리는다음정렬
--국가명이2자인나라만출력
SELECT name FROM tblCountry WHERE LEN(name) =2;
--SQL : 1based index
SELECT SUBSTRING(name, 0, 2) AS [짜른것] FROM tblCountry;
--LEFT(), RIGHT() : substring() 동일
--왼쪽에서자르기, 오른쪽에서자르기
SELECT LEFT(name, 2) AS [왼쪽], RIGHT(name,2) AS [오른쪽] FROM tblCountry;
SELECT * FROM tblGroup WHERE LEFT(name, 1) = '김';
SELECT * FROM tblGroup WHERE LEFT(name, 1) = '김' AND LEN(name) = 3;
--TRIM
CREATE TABLE test6
(
name nchar(10) not null
)
INSERT INTO test6 values('홍길동');
INSERT INTO test6 values('홍동');
INSERT INTO test6 values('이순신');
INSERT INTO test6 values('남궁순신');
SELECT name FROM test6;
SELECT name + '님' FROM Test6;
--RTRIM() : 오른쪽에있는공백제거
SELECT RTRIM(name) + '님' FROM test6
SELECT LEN(NAME) FROM test6; --char글자수varchar 동일하게나온다.
--UPPER(), LOWER()
SELECT cont FROM tblCountry;
SELECT LOWER(cont) FROM tblCountry;
--수치함수
--ROUND() : 반올림
USE pubs;
SELECT title, price FROM titles;
SELECT title, price, ROUND(price, 2) FROM titles;--테스트
SELECT title, price, ROUND(price, -1) FROM titles;-- -1은소수점왼쪽첫자리
USE SQLEx
SELECT name, popu FROM tblCountry;
SELECT name, popu, ROUND(popu, -2) FROM tblCountry;--소수점왼쪽두째자리
--FLOOR()
--무조건내림함수
--값에서소수이하를버린다.
SELECT title, price, FLOOR(price) FROM titles;
--Celling()
--무조건올림함수
--값에서가장가까운큰정수를반환
--2.1 -> 3
SELECT title, price, CEILING(price) FROM titles;
--날짜시간함수
--1. getdate()
SELECT GETDATE(); -- 시스템의현재시간반환
CREATE TABLE tblMemo
(
seq int identity(1,1) not null primary key, --메모번호
memo varchar(1000) not null, --메모
writeDate datetime not null DEFAULT(GETDATE()) --시간
)
INSERT INTO tblMemo VALUES ('안녕하세요어쩌구저쩌구구구구', GETDATE());
INSERT INTO tblMemo VALUES('메모입니다.', DEFAULT);
INSERT INTO tblMemo VALUES('메모입니다.');
INSERT INTO tblMemo VALUES('메모입니다.2', '2012-05-31 12:56:35');
SELECT * FROM tblMemo;
--각부분추출하기
--YEAR(), MONTH(), DAY()
SELECT writeDate, YEAR(writeDate) AS [년], MONTH(writeDate) AS [월], DAY(writeDate) AS [일]
FROM tblMemo;
-- 각부분추출하기
-- datename() - 문자열, datepart() - 숫자: 기능동일
-- 1. 년도: YESR, YYYY
-- 2. 월: MONTH, M
-- 3. 일: DAY, D
-- 4. 주: WEEK, WW
-- 5. 요일: WEEKDAY, DW
-- 6. 시: HOUR, HH
-- 7. 분: MINUTE, N
-- 8. 초: SECOND, S
-- 9. 밀리초: MILLISECOND, MS
-- 10. 1년중몇째? : DAYOFYEAR, Y
SELECT writeDate, DATENAME(YYYY, writeDate) FROM tblMemo;
SELECT writeDate, DATENAME(HH, writeDate) + '시' FROM tblMemo;
SELECT writeDate, DATENAME(HH, writeDate) + 1 FROM tblMemo;
SELECT writeDate, DATEPART(HH, writeDate) + 1
SELECT 뿌잉뿌잉 FROM 한상원; -- 한상원테이블에뿌잉뿌잉있음.ㅡ
--******************************************************************************************
--DATETIME 연산
--1. DATEADD() : 시각+ 시각= 시각
--2. DATEDIFF() : 시각- 시각+ 시간
SELECT * FROM tblMemo;
--메모시간+ 100일
SELECT writeDate, DATEADD(hh, 1, writeDate) AS [1시간추가] FROM tblMemo;
--메모시간- 30분
SELECT writeDate, DATEADD(n, -30, writeDate) AS [-30분] FROM tblMemo;
--메모를쓴지얼마나?
SELECT writeDate, DATEDIFF(hh, writedate, getdate()) AS [지난시간] FROM tblMemo;
--형변환
--1. CONVERT() : MS-SQL에서만든함수
--2. CAST() : ANSI-SQL에서권장하는함수
SELECT writeDate AS [원본], LEFT(writeDate, 10) AS[10자리짜른것] FROM tblMemo;
SELECT popu + '만명' FROM tblCountry;
use SQLEx
--C# [varchar]popu = char로하게된다면공백이사라지지않기때문에varchar로변환
SELECT CAST(popu AS varchar(10)) + '만명' AS [CAST] FROM tblCountry;
SELECT CONVERT(varchar, popu, 10) + '만명' AS [CONVERT] FROM tblCountry;
--다똑같은형태원하는형태로출력
SELECT 1000/ 3;
SELECT CAST(1000 AS FLOAT) /3;
SELECT 1000 / 3.0;
SELECT popu / 3 FROM tblCountry;
SELECT popu / 3.0 FROM tblCountry;
SELECT SUM(popu) / COUNT(*) FROM tblCountry;
SELECT SUM(popu) / CAST(COUNT(*) AS DECIMAL) FROM tblCountry;
--시간날짜형변환-> 문자열로바꾸고싶다(원래getDate형식그대로...cast는style 넘버를적용시킬수없으니CONVER를이용해라
USE pubs;
SELECT writeDate, CAST(writeDate AS varchar(50)) FROM tblMemo;
--convert의세번째인자는style넘버(MSDN참고):MSDN 라이브러리에서CONVER SQL로검색=> STYLE 참고
SELECT writeDate, CONVERT(varchar, writeDate, 21) FROM tblMemo;
SELECT writeDate, LEFT(writeDate, 10) FROM tblMemo;
--***************최종본: CONVERT 이용해서원본형식으로바꾸고다시LEFT를원하는데이터값추출해내라
SELECT writeDate, LEFT(CONVERT(varchar, writeDate, 21), 10) FROM tblMemo;
SELECT * FROM tblMemo
--2012-03-30에작성된메모를모두보여주세요
--1
SELECT * FROM tblMemo WHERE writeDate BETWEEN '2012-03-30 00:00:00' AND '2012-03-30 23:59:59'
--2
SELECT LEFT(writedate, 10) FROM tblmemo WHERE LEFT(writeDate,10) = '03 30 2012';
--3
SELECT * FROM tblmemo WHERE LEFT(CONVER(varchar30), writeDate, 21), 10) = '2012-03-30';
--null 관련함수
--isnull(컬럼명, 준비값)
-- : 컬럼값이null이면준비값을반환하고, null이아니면원래컬럽값을반환
USE pubs;
SELECT * FROM titles;
SELECT ISNULL(price, 00000) FROM titles;
-- 물가상승으로인한책가격+1불상승
-- null이었던책은1불
SELECT title, ISNULL(price, 0) + 1 FROM titles;
