MS SQL 쿼리문 모음
create Table tblAddress2
(
--name nvarchar(10) not null constraint primary key,
name nvarchar(10) not null primary key,
nick nvarchar(10) null unique, --중복불가, 생략가능
--nvarchar형이면, 유니코드문자열이란뜻의N을적어주는게좋다.
psycho nvarchar(20) not null default(N'이상없음'),
age int not null default(0)
)
--Check, ID 제약조건
create Table tblAddress3
(
seq int not null identity(1,1) primary key,
name nvarchar(10) not null,
nick nvarchar(10) null unique,
psycho nvarchar(20) not null default(N'이상없음'),
age int not null check(age>20)
)
테이블 수정
- 테이블 컬럼을 추가, 수정, 삭제 작업
create table Test
(
seq int identity(1,1) primary key not null,
num int not null,
string varchar(100) not null,
)
-- SQL 컬럼을추가하기
alter table Test
add data nvarchar(10) not null;
alter table Test
drop column data;
--컬럼수정
alter table Test
alter column string varchar(2) null;
--SQL(시퀄)
-- DML(데이터조작)
-- 1. select : 데이터를가져오기(*********)
-- 2. insert : 데이터를입력하기
-- 3. update : 데이터를수정하기
-- 4. delete : 데이터를삭제하기
-- INSERT
-- 필수암기!!! - 1번
-- insert [into] 테이블명(컬럼리스트) values (값리스트);
create table tblMemo
(
seq int identity(1,1) not null primary key, -- 메모번호
title varchar(200) not null, -- 메모제목
content varchar(4000) not null, -- 메모내용
regTime datetime not null -- 작성시간
)
-- 날짜기록
-- 1. datetime형
-- 2. varchar형
--메모1건추가
insert into tblMemo
(
title, content, regTime
)
values
(
'메모입니다.', '메모내용입니다.', '2012-03-28 14:45:00'
);
-- 컬럼리스트는테이블원본과순서가달라도상관없음
-- 컬럼리스트의순서와값리스트의순서는일치해야함!!!
insert into tblMemo (content, title, regTime)
values
(
'하하하', '호호호', '2012-03-28 14:45:00'
);
create table Test2
(
seq int identity(1,1) not null primary key,
data1 varchar(100) not null,
data2 varchar(100) null,
data3 varchar(100) not null default('기본값'),
data4 int not null,
data5 int not null default(100)
)
-- 1. 풀버전- 외워라!!!
insert into Test2 (data1, data2, data3, data4, data5)
values ('하나', '둘', '셋', 10, 20);
-- 2. 컬럼리스트를생략가능
-- (반드시값리스트의순서는테이블원본의컬럼순서대로기입*****)
-- (반드시값리스트의갯수는원본컬럼의갯수와일치)
insert into Test2 values ('one', 'two', 'three', 30, 40);
-- 3. 컬럼리스트의순서는마음대로
insert into Test2 (data4, data5, data1, data2, data3)
values (50, 60, 'aaa', 'bbb', 'ccc');
insert into Test2 values (50, 60, 'aaa', 'bbb', 'ccc'); ----이건안됨~~
-- 4-1. 컬럼에null값을넣기
insert into Test2 (data1, data2, data3, data4, data5)
values ('가', null, '다', 10, 10);
-- 4-2. 컬럼에null값을넣는다른방법.
insert into Test2 (data1, data3, data4, data5) --data2를빼버리자. 데이터에서도뺌.
values ('가', '다', 10, 10);
-- 4-1-1. 컬럼에null값을넣는다른방법.
insert into Test2 values ('가', null, '다', 10, 10);
-- 4-2-1
insert into Test2 values ('가', '다', 10, 10); --이문장은에러남
--5-1. default 값처리
insert into Test2 (data1, data2, data4)
values ('하나', '둘', 10);
--5-2. default 값처리
insert into Test2 values('하나', '둘', default, 10, default);
--select 문
--select 컬럼리스트from 테이블명[where절] [order by 절]
-- * : 모든컬럼(all)
select * from Test2; -- Test2테이블의모든컬럼과모든레코드반환
--select 질의후나온결과물
--1. 결과테이블
--2. 결과셋(ResultSet)
--3. 로우셋(RowSet)
--select의컬럼리스트
-- : 원본테이블에서보고자하는컬럼을명시
select data1 from Test2;
select seq, data4, data5 from Test2;
select * from tblAddress;
select name, psycho from tblAddress;
select name, name, name from tblAddress;
-- : 해당컬럼값의연산이나함수적용가능
print '하하';
Select '하하';
Select 10+20;
select '홍길동' + '님';
select '하하' + 100;
Select 100+ '하하';
select name from tblAddress;
select name+'님' from tblAddress;
select name, age+10 from tblAddress;
select name, age,age+10 from tblAddress;
--Alias : 별칭
--select 결과셋에서이름이없는컬럼에이름을부여하거나,
--원본의컬럼명을변경하고자할때..
select name + '님' from tblAddress;
select name + '님' as 고객명 from tblAddress;
select name , age from tblAddress;
select name as 이름, age as 나이 from tblAddress;
-- []대괄호사용하는경우
--[식별자] : []안에예약어가있어도무의미하게취급
create table Test3
(
[table] varchar(50) not null
[aaa] int not null
)
--Alias붙일때는[] 넣기를...
select [name], [name] + '님' as [고객명] from [tblAddress];
select * from tblAddress2;
select name, age from tblAddress2;
select name + nick from tblAddress2;
select name + '(' + nick + ')' from tblAddress2;
--국가정보테이블
create table tblCountry
(
name nvarchar(30) not null primary key, --국가명
capital nvarchar(30) null, -- 수도
popu int null, --인구수(만단위)
cont char(2) null, --소속대륙(Asia, South America, North America.....)
area int null -- 면적..
)
INSERT INTO tblCountry VALUES ('대한민국','서울',4405,'AS',10);
INSERT INTO tblCountry VALUES ('중국','베이징',120660,'AS',959);
INSERT INTO tblCountry VALUES ('일본','도쿄',12461,'AS',37);
INSERT INTO tblCountry VALUES ('미국','워싱턴',24963,'SA',936);
INSERT INTO tblCountry VALUES ('영국','London',5741,'EU',24);
INSERT INTO tblCountry VALUES ('이집트','카이로',5969,'AF',99);
INSERT INTO tblCountry VALUES ('오스트레일리아','Canberra',1787,'AU',768);
INSERT INTO tblCountry VALUES ('칠레','산티아고',1339,'SA',75);
INSERT INTO tblCountry VALUES ('우루과이','몬테비디오',317,'SA',17);
INSERT INTO tblCountry VALUES ('아르헨티나','부에노스아이레스',3388,'SA',278);
INSERT INTO tblCountry VALUES ('인도네시아','자카르타',19134,'AS',191);
INSERT INTO tblCountry VALUES ('네덜란드','암스테르담',1476,'EU',4);
INSERT INTO tblCountry VALUES ('케냐','나이로비',NULL,'AF',58);
INSERT INTO tblCountry VALUES ('벨기에','브뤼셀',1012,'EU',3);
select * from tblCountry;
--primary key는자동으로오름차순정렬이된다.
--select 컬럼리스트from 테이블명[where절] [order by절]
--where 조건절
-- : 조건에만족하는결과셋만가져오기
--연산자
--1.비교연산자
-- : A=B(A==B), A<>B(A!=), >, <, >=, <=
--2.논리연산자
-- : and(&&), or(||)
select * from tblCountry;
--if(area > 100)
--면적이100을초과하는나라의정보
select * from tblCountry where area > 100;
--면적이100을초과하는국가명?
select name from tblCountry where area > 100;
--select에서...
--컬럼리스트: 세로필터링
--where : 가로필터링
-- 아시아에속한국가명, 인구수, 수도
select name, popu, capital from tblCountry
where cont = 'AS';
-- 인구가1억(10000)이넘고면적이100이상인국가명+ 아시아
select name from tblCountry
where (popu > 10000) and (area > 100) and (cont = 'AS');
-- SQLEx.tblCountry
-- 1. 인구가1억미만이고, 면적이100이하이며,
-- 아시아에속하지않은나라의이름, 인구, 면적출력하시오.
select name, popu, area from tblCountry
where (popu < 10000) and (area <= 100) and (cont <> 'AS');
-- SQLEx.tblAddress2/3
-- 1. 정신상태가정상인사람의이름과나이를출력하시오.
select name, age from tblAddress2 where psycho = '이상없음';
-- pubs.titles
-- 1. 책가격(price)이20불이상인책의제목을출력하시오.
select title from titles where (price > 20);
-- 2. 분류(type)가'trad_cook' 이거나'mod_cook'에속하는책중
-- 가격이10불~20불이내의책제목을출력하시오.
select title from titles where ((type = 'trad_cook') or (type = 'mod_cook')) and ((price <= 20) and (price >= 10));
select * from titles;
-- 2012년3월29일
use SQLEx;
select * from tblCountry;
-- 인구수가1000 이상이고5000 이하인국가명, 인구수출력하시오.
select name as [국가명], popu as [인구수(만명)] from tblCountry where popu >= 1000 and popu <=5000;
-- between 문
-- 범위조건문
-- where 에서사용
-- 컬럼명between A and B
-- 해당컬럼의값이A 값과B 값의사이에있는지조건을건다.
-- 인구수가1000보다크고5000보다작은사이(A이상, B이하)
select name, popu from tblCountry where popu between 1012 and 4405;
use pubs;
select * from titles; -- 도서정보
-- 책가격이10불~20불이내도서명, 가격
select title, price from titles where price between 10 and 20;
-- 꼭숫자만범위를갖는건아니다
-- 날짜범위
-- 1992년이후에출간된책의제목과출간일
select title,pubdate from titles; -- 모든책의제목과모든책의출간일
select title,pubdate from titles where pubdate >= '1992-01-01 00:00:00';
-- 1992년~2000년이내에출간된책의제목과출간일
select title,pubdate from titles where pubdate between '1992-01-01 00:00:00' and '2000-12-31 23:59:59';
select title,pubdate from titles where pubdate between '1992-01-01' and '2000-12-31';
-- 문자비교(첫글자부터비교)
-- 문자는E*** 부터~ 딱E 까지
select title from titles where title between 'E' and 'E';
use SQLEx;
-- 국가중AS, EU 에속한나라만출력하시오.
select * from tblCountry where cont = 'AS' or cont='EU';
-- in 문
-- 열거형조건(where절)
-- 컬럼형in (열거형값리스트)
select * from tblCountry where cont in ('AS','EU');
-- like 문
-- 패턴조건(where절)
-- : 문자열의특정패턴이나타나는컬럼을검색
-- 컬럼명like 패턴
-- 패턴문자열
-- 1. _ : 불특정문자1개
-- 2. % : 불특정문자0개이상
-- 3. []
use pubs;
select * from titles;
select title from titles where title like 'B%';
use SQLEx;
-- tblAddress3 에서'홍'씨인사람을모두출력하시오.
select * from tblAddress3 where name like '홍%';
select * from tblAddress3 where name like '%순신';
select * from tblAddress3 where name like '%순%';
-- 게시판에서글내용중C#이있는게시물검색
select * from tblBoard where content like '%C#%';
-- 검색대상중에%에포함?
select * from tblAddress3 where name like '김[%]_';
-- '[' 를검색할땐어떻게하냐. - > '[하하]'
-- where 컬럼like '[[]'하하]'
-- 앞에[ 괄호가[]안에들어가면뒤에괄호는의미가없어진다.
--'홍'씨검색
select * from tblAddress3 where name like '홍%';
-- '홍'씨검색+ 이름이3자인사람만
select * from tblAddress3 where name like '홍__';
use pubs;
select * from titles;
-- 10달러대이면서.. 99센트인도서를출력하시오.
-- 10.99, 11,99, 12.99 ..... 19.99
-- 패턴이있다, 범위로는힘들다.
select * from titles where price like '1_.99';
-- 날짜like 검색확인
select * from titles where pubdate like '____%';
-- is null
-- 너null 이냐?
SELECT [title_id]
,[title]
,[type]
,[pub_id]
,[price]
,[advance]
,[royalty]
,[ytd_sales]
,[notes]
,[pubdate]
FROM [pubs].[dbo].[titles]
GO
-- 클래스표기
-- : 네임스페이스.클래스명
-- 테이블표기
-- : 서버명.DB명.스키마명.테이블명
-- 가격이미정(null)인도서를출력하시오.
-- null = > 없다라는뜻
-- 10 + null = ? -> null은연산의대상이될수없다. 즉피연산자로쓸수없다.
select * from titles where price = null; -- price == null
select * from titles where price is null;
-- 책가격이이미정해져있는책은?
select * from titles where price <> null;
select * from titles where price is not null; -- null 을부정
select * from titles where not price is null; -- 연산자체를부정
-- 정렬(Sort)
-- : 특정컬럼값을기준으로오름차순/내림차순으로정렬
-- order by 사용
-- order by 컬럼명[acs|decs] [, 컬럼명[asc|desc]]
-- null 은0보다작은값으로취급(정렬시에..)
-- 책제목을abc순으로..
select * from titles order by title asc; -- acsending 오름차순정렬
select * from titles order by title desc; -- descending 내림차순정렬
-- 가격이낮은도서부터출력~
select title,price from titles order by price asc;
-- 가격이정해져있는도서중가격이낮은도서부터출력~
select title, price from titles where price is not null order by price asc;
select title, price from titles where price is not null order by price asc, title asc;
select title, price from titles where price is not null order by price asc, title asc;
-- distinct
-- 레코드제한(where절)
-- : 중복값제거(***)
use SQLEx;
select * from tblCountry;
-- 대륙이무엇이있나?
select cont from tblCountry;
-- tblCountry 테이블에어떤종류의대륙이있나확인한다면?
select distinct cont from tblCountry
use pubs;
select * from titles;
-- 도서들은어떤분류에의해서?
select [type] from titles;
select distinct [type] from titles;
select title,[type] from titles;
select title, distinct [type] from titles;
select distinct [type],title from titles;
-- 회원테이블에서회원들이주거지분포?
select 주소 from 멤버테이블;
select distinct 주소 from 멤버테이블;
-- 서울시
-- 인천시
-- 부산시
-- top
-- 상위원하는갯수의레코드를가져오기
-- top n 컬럼명: n (레코드갯수)
-- %로상위% 출력도가능
-- top n percent 컬럼명
use SQLEx;
select * from tblCountry;
-- 인구수가가장많은나라3개국가
select top 3 * from tblCountry order by popu desc;
select top 3 * from tblCountry where popu is not null order by popu desc;
-- 인구수가1000 ~ 10000 이내인나라중인구낮은순서로3개나라를출력해라.
select top 3 name as [국가명],popu as [인구수(만명)] from tblCountry
where popu between 1000 and 10000 order by popu asc;
-- 인구수가적은나라중상위20%
select top 20 percent name, popu from tblCountry where popu is not null order by popu asc;
-- update
-- : 이미존재하는레코드의일부컬럼값을수정
-- update 테이블명set 컬럼명=값[,컬럼명=값] [where 조건]
select * from tblCountry;
-- 한국인구수5000
update tblCountry set
popu = 5000;
update tblCountry set
popu = 5000
where name = '대한민국'
-- 면적이100이넘는나라들이인구+100이증가됨.. 반영
update tblCountry set
popu = popu + 100
where area > 100;
-- 대한민국수도를'파주'로이전하고.. 면적+ 50 증가
update tblCountry set
capital = '파주',
area = area + 50
where name = '대한민국';
-- 사용불가!!
update tblCountry set
name = '고구려' --!!
where name = '대한민국';
-- 사용불가!!
update tblCountry set
name = '신라',--!!
capital = '경주'
where name = '고구려';
create table Test4
(
seq int identity(1,1) not null primary key,
data1 varchar(10) null,
data2 varchar(10) not null default('임시')
)
create table Test5
(
seq int identity(1,1) not null primary key,
data1 varchar(10) null,
data2 varchar(10) not null default('임시')
)
insert into Test4 (data1, data2)
values ('홍길동', '하하하');
select * from Test4;
-- null값으로update 실행
update Test4 set
data1 = null
where seq = 1; -- 홍길동삭제
-- 기본값으로update 실행
update Test4 set
data2 = default
where seq = 1;
insert into Test4 (data1, data2)
values ('홍길동', '하하하');
insert into Test4 (data1, data2)
values ('홍길동', '하하하');
insert into Test4 (data1, data2)
values ('홍길동', '하하하');
select * from Test4;
select * from Test5;
-- 테이블복사
set identity_insert Test5 off insert into Test5 select data1, data2 from Test4;
-- delete
-- : 테이블의레코드를삭제
-- delete [from] 테이블명[where절]
delete from tblCountry; -- 모든나라삭제(데이터삭제)
select * from tblCountry;
delete from tblCountry
where cont = 'AS'; -- 아시아의모든나라를삭제
-- 테이블의모든데이터를삭제
truncate table tblCountry; -- 비우는작업만..
delete from tblCountry; -- 로그기록..(보조)
-- 직원테이블
create table tblGroup
(
name nvarchar(10) not null primary key, --사원명
depart nvarchar(10) not null, -- 부서명
salary int not null, -- 월급
archieve int null, -- 실적
workMonth int null -- 근무개월
)
-- 친구테이블
create table tblFriend
(
pk int identity(1,1) not null primary key, -- 친구번호
name nvarchar(10) not null, --친구명
birth datetime not null, -- 생일
solar bit not null, --양/음력
addr nvarchar(50) null, --주소
tel varchar(20) null, --전화번호
height int null-- 키
)
INSERT INTO tblGroup VALUES ('김유신','관리부',180,88,12);
INSERT INTO tblGroup VALUES ('유관순','지원부',190,NULL,28);
INSERT INTO tblGroup VALUES ('안중근','영업부',185,76,19);
INSERT INTO tblGroup VALUES ('윤봉길','생산부',200,71,38);
INSERT INTO tblGroup VALUES ('강감찬','영업부',150,28,3);
INSERT INTO tblGroup VALUES ('정몽주','관리부',170,88,12);
INSERT INTO tblGroup VALUES ('안창남','생산부',175,75,18);
INSERT INTO tblGroup VALUES ('이윤복','지원부',210,70,19);
INSERT INTO tblGroup VALUES ('신숙주','영업부',220,NULL,13);
INSERT INTO tblGroup VALUES ('성삼문','영업부',205,87,22);
INSERT INTO tblGroup VALUES ('이자겸','생산부',205,83,32);
INSERT INTO tblGroup VALUES ('김정호','생산부',180,85,28);
INSERT INTO tblGroup VALUES ('조광조','관리부',190,74,38);
INSERT INTO tblGroup VALUES ('한명회','관리부',190,53,30);
INSERT INTO tblGroup VALUES ('김시민','생산부',195,55,36);
INSERT INTO tblGroup VALUES ('이율곡','관리부',185,51,5);
INSERT INTO tblGroup VALUES ('이사부','지원부',175,50,9);
INSERT INTO tblGroup VALUES ('안창호','영업부',170,34,14);
INSERT INTO tblGroup VALUES ('홍경래','영업부',165,95,14);
INSERT INTO tblGroup VALUES ('최치원','생산부',155,49,19);
INSERT INTO tblGroup VALUES ('김부식','관리부',150,67,15);
INSERT INTO tblGroup VALUES ('정약용','관리부',180,69,19);
INSERT INTO tblGroup VALUES ('홍길동','생산부',180,39,18);
INSERT INTO tblGroup VALUES ('대조영','영업부',190,49,12);
INSERT INTO tblGroup VALUES ('장보고','생산부',190,58,27);
INSERT INTO tblFriend (Name, Birth, Solar, Addr, Tel, Height) VALUES ('김상형','1970-06-29',0,'논현동','549-1107',180);
INSERT INTO tblFriend (Name, Birth, Solar, Addr, Tel, Height) VALUES ('박미영','1972-04-18',0,'논현동','123-4567',163);
INSERT INTO tblFriend (Name, Birth, Solar, Addr, Tel, Height) VALUES ('정현연','1974-05-01',0,'사직동','501-4156',161);
-- 함수(Function)
-- 집계함수(Aggregate)
-- : 통계값을산출하는함수, 합, 평균, 최대값, 최소값..
-- 1. count() 함수
-- : count(컬렴명)
-- : 현재select 를통해반환되고있는컬럼의레코드수를반환
-- tblGroup
select * from tblGroup; --25명
-- 직원은몇명입니까?
select COUNT(*) as [회원수] from tblGroup;
-- 유럽에속한나라는몇개국?
select COUNT(*) from tblCountry where cont = 'EU'
-- count() 함수의인자값이* 와컬럼명일때의차이
-- : 특정컬럼값을대상으로하면null 을갖는레코드는제외한다.
select COUNT(*) from tblGroup; --25
select COUNT(name) from tblGroup; --25
select COUNT(achieve) from tblGroup; --25
use pubs;
select COUNT(*) from titles; -- 모든도서의권수?
select COUNT(price) from titles; -- 가격이정해진도서의권수?
select COUNT(*) from titles where price is not null; -- 16(위의구문과동일함)
use SQLEx;
-- 사원들에게실적보너스100000000원/ 직원수
select COUNT(*) from tblGroup;
select 100000000/25; -- 4,000,000원
select 100000000/COUNT(*) from tblGroup; -- 실적유무와상관없이모든직원
select 100000000/COUNT(achieve) from tblGroup; --실적이있는직원
-- 실적이있는사원수?
select COUNT(achieve) from tblGroup; --23명
-- 실적이없는사원수?
select COUNT(*) - COUNT(achieve) from tblGroup; -- 2명
select COUNT(*) from tblGroup where achieve is null; -- 2명
-- 근무개월수가1년이상2년이하인직원수?
select * from tblGroup;
select COUNT(*) from tblGroup where workMonth between 12 and 24;
-- 회사에부서는총몇개?
select count(distinct depart) from tblGroup;
--대륙은총몇개? --5
select * from tblCountry;
select COUNT(distinct cont) from tblCountry;
-- 2. sum() 함수
-- : sum(컬럼명)
-- : 컬럼값의총합을반환
-- 전체직원의총실적수?
select achieve from tblGroup; -- 개인당실적수
select COUNT(achieve) from tblGroup; -- 실적을낸사람의수
select SUM(achieve) as [총질적수] from tblGroup; -- 1494 모든사람의실적수
-- 등록된나라의총인구수는?
select SUM(popu) from tblCountry; -- 20억
-- AS, EU 속한나라의총인구수?
select SUM(popu) from tblCountry where cont in ('AS', 'EU'); -- 16억
-- 3. avg() 함수
-- avg(컬럼명)
-- : 해당컬럼값들의평균을반환
-- 직원평균급여(salary)?
select salary from tblGroup;
select SUM(salary) from tblGroup; -- 한달소비인건비
select AVG(salary) from tblGroup; -- 평균급여
-- 근무년수2년차이상인직원평균급여는얼마냐
select AVG(salary) from tblGroup where workMonth >= 24;
-- 1인당평균실적수? (null 직원2명제외)
select AVG(achieve) from tblGroup;
select SUM(achieve) / COUNT(*) from tblGroup;
select SUM(achieve) / COUNT(achieve) from tblGroup;
-- 4. max() 함수, min() 함수
-- max(컬럼명), min(컬럼명)
-- : 최대값, 최소값
-- 월급이가장많은사람의월급은?
select MAX(salary) from tblGroup;
-- 월급이가장적은사람의월급은?
select min(salary) from tblGroup;
-- 주의점!!
-- *** 집계함수는where 에서사용불가능!!
-- 가장급여가높은사람의이름은?
select MAX(salary) from tblGroup; -- 220
select name from tblGroup where salary=220;
select name from tblGroup where salary=MAX(salary);
select top 1 name from tblGroup order by salary desc;
-- group by
-- : 특정컬럼값이동일한레코드끼리의통계값을반환
-- : 특정컬럼값이똑같은레코드끼리묶는역할
-- 전직원의평균근무개월수?
select AVG(workMonth) from tblGroup;
-- 부서별직원의평균근무개월수?
select distinct depart from tblGroup; --관리부, 생산부, 영업부, 지원부
select AVG(workMonth) from tblGroup where depart='관리부'; --18
select AVG(workMonth) from tblGroup where depart='생산부';
select AVG(workMonth) from tblGroup where depart='영업부';
select AVG(workMonth) from tblGroup where depart='지원부';
select AVG(workMonth), depart from tblGroup group by depart;
-- group by가들어간select 에서는group by 대상이되는컬럼값이외의컬럼은출력이불가능
-- group by에서는집계함수와group by 대상컬럼값만출력이가능(AVG 는집계함수)
select AVG(workMonth), depart from tblGroup group by depart;
-- 부서별월급?
select AVG(workMonth) as '[평균근속개월]', depart, avg(salary) as '[평균월급]' from tblGroup group by depart;
-- 대륙별인구수의총합과평균은? 최고인구? 최저인구?
select cont as '[대륙]',SUM(popu) as'[총인구수]', AVG(popu)as '[평균인구수]', MAX(popu) as '[최고인구수]', MIN(popu) as '[최저저인구수]' from tblCountry group by cont;
-- pubs.titles
-- 분야별(type) 도서평균가격? 총권수?, 가장비싼책의가격? 가장싼책의가격?
use pubs;
select * from titles;
select type as '[분야별]', AVG(price) as '[평균가격]', COUNT(*) as '[총권수]', MAX(price) as '[비싼책]',MIN(price) as '[싼책]' from titles where price is not null group by type;
-- 집계함수(count(), sum(), avg(), max(), min())
-- 집계함수와일반컬럼은동시에select 할수없다!!
select * from tblCountry;
select count(popu) from tblCountry; --null 자동제외
select count(popu), name from tblCountry;
--group by
-- 한개컬럼값이똑같은레코드끼리묶어주는역할
select max(popu), cont from tblCountry group by cont; --AS는AS끼리, EU은EU끼리... AF...
-- 교육원에서반별(group by) 학생수(count())?
select ? from 교육원 group by 강의실;
-- having
-- : group by의조건절(그룹을짓는데사용될조건)
-- : group by와같이사용
-- select의조건절? where절(데이터를가져오는데사용될조건)
--
select * from tblGroup;
-- 각부서별평균월급은?
select avg(salary), depart from tblGroup group by depart;
-- 185만원이상되는각부서별평균월급은?
select avg(salary), depart from tblGroup group by depart having avg(salary) >= 185;
-- 중학교1학년이총10개반: 국어시험: 국어평균이80점이넘어가는반이몇학급이되는지?
select count(*) from 성적테이블 group by 반 having avg(국어점수) >= 80;
-- 모든직원을대상으로하지않고
-- 입사한지12개월이지난직월들을대상으로한다.
-- 185만원이상되는각부서별평균월급은? -> 정렬
select avg(salary),count(*), depart from tblGroup where workMonth >= 12 group by depart having avg(salary) >= 180 order by avg(salary) asc;
-- 관리부, 생산부를대상으로..
-- having
-- : 조건으로사용가능한건.. 집계함수의결과값이나group by 대상컬럼만사용가능.
select depart, avg(salary) from tblGroup group by depart having depart in ('관리부', '생산부');
select depart, avg(salary) from tblGroup group by depart having salary > 100;
-- compute
-- : 집계함수와사용
select avg(salary) from tblGroup;
select * from tblGroup compute avg(salary);
select * from tblGroup compute max(salary);
-- 1. 문자열길이
-- len(컬럼값)
select name, len(name) from tblCountry;
-- 국가명이긴순서대로출력하시오.
-- 꼭보이는것만정렬을하는게아니다.
select name from tblCountry order by len(name) desc, popu desc;
-- 국가명이2자인나라만출력
select name from tblCountry where len(name) = 2;
-- Substring()
select name from tblCountry;
select substring(name, 1,2) from tblCountry;
-- left(), right() : subtring() 동일함
-- 왼쪽에서자르기, 오른쪽에서자르기
select left(name,2), right(name,2) from tblCountry;
select * from tblGroup;
select * from tblGroup where name like '김%';
select * from tblGroup where left(name, 1) = '김';
select * from tblGroup where substring(name,1,1) = '김';
select * from tblGroup where name like '김__';
select * from tblGroup where left(name, 1) = '김' and len(name) = 3;
-- trim
create table Test7
(
name nchar(10) not null
)
insert into Test7 values('홍길동');
insert into Test7 values('홍동');
insert into Test7 values('이순신');
insert into Test7 values('남궁순신');
select name from Test7;
select name+'님' from Test7;
-- rtrim()
select rtrim(name) + '님' from Test7;
select len(name) from Test7; -- char 글자수varchar 동일
-- upper(), lower()
select cont from tblCountry;
select upper(lower(cont)) From tblCountry;
-- 수치함수
-- round() : 반올림
use pubs;
select title, price from titles;
select title, price, round(price,-1) from titles;
select title, price, round(price,0) from titles;
use SQLEx;
select name, popu, round(popu, -2) from tblCountry;
-- floor()
-- 무조건내림함수
-- 값에서소수이하를버린다.
-- 2.9 -> 2
select title, price, floor(price) from titles;
-- ceiling()
-- 무조건올림함수
-- 값에서가까운큰정수를반환
-- 2.1 -> 3
select title, price, ceiling(price) from titles;
-- 날짜시간함수
-- 1. getdate() : DateTime.Now
select GETDATE();
create table tblMemo2
(
seq int identity(1,1) not null primary key, --메모번호
memo varchar(1000) not null, -- 메모
writeDate datetime not null default(getdate())-- 시간
)
delete from tblMemo2;
insert into tblMemo2 (memo, writeDate) values ('메모입니다',GETDATE());
insert into tblMemo2 (memo, writeDate) values ('메모입니다',default);
insert into tblMemo2 (memo) values ('메모입니다');
insert into tblMemo2 (memo,writeDate) values ('메모입니다','2012-05-31 10:51:35');
select * from tblMemo2;
-- 각부분추출하기
-- year(), month(), day()
select writeDate, YEAR(writeDate), MONTH(writeDate), DAY(writeDate) from tblMemo2;
-- 각부분추출하기
-- datename() - 문자열, datepart() - 숫자: 기능은동일
-- 1. 년도: year, 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 tblMemo2;
select writeDate, datename(M,writeDate) from tblMemo2;
select writeDate, datename(D,writeDate) from tblMemo2;
select writeDate, datename(WW,writeDate) from tblMemo2;
select writeDate, datename(DW,writeDate) from tblMemo2;
select writeDate, datename(HH,writeDate) from tblMemo2;
select writeDate, datename(N,writeDate) from tblMemo2;
select writeDate, datename(S,writeDate) from tblMemo2;
select writeDate, datename(MS,writeDate) from tblMemo2;
select writeDate, datename(Y,writeDate) from tblMemo2;
-- datetime 연산
-- 1. dateadd() : 시각+ 시간= 시각
-- 2. datediff() : 시각- 시각= 시간
select * from tblMemo2;
-- 메모시간+ 100일
select writeDate, DATEADD(d,100,writeDate) from tblMemo2;
-- 메모시간+ 1시간
select writeDate, DATEADD(hh,1,writeDate) from tblMemo2;
-- 메모시간- 30분
select writeDate, DATEADD(n,-30,writeDate) from tblMemo2;
-- 메모를쓴지얼마나(단위)?
select writeDate, DATEDIFF(hh,writeDate,getdate()) from tblMemo2;
-- 형변환
select writeDate, LEFT(writeDate, 10) from tblMemo2;
select popu + '만명' from tblCountry;
-- (varchar)popu
select CAST(popu as varchar(10)) + '만명' from tblCountry;
select CAST(popu as CHAR(10))+'만명' from tblCountry;
select CONVERT(varchar,popu,10)+'만명' from tblCountry;
select 1000/3; ---333
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;
select SUM(popu) / (COUNT(*) * 1.0) from tblCountry;
-- 시간날짜형변환-> 문자열
select writeDate, CAST(writeDate as varchar(5)) from tblMemo2;
select writeDate, convert(varchar, writeDate,20) from tblMemo2;
-- convert()함수를사용한시간날짜형변환***
select writeDate, LEFT(writeDate,10) from tblMemo2;
-- 최종본(********)
select writeDate, LEFT(convert(varchar, writeDate,20),10) from tblMemo2;
select writeDate, cast(YEAR(writeDate) as varchar(4)) + '-' + cast(MONTH(writeDate) as varchar(2)) + '-' + cast(DAY(writeDate) as varchar(2)) from tblMemo2;
select * from tblMemo2;
-- 2012-03-30에작성된메모를모두보여주세요!
-- 1. 잘못된방법
select * from tblMemo2 where writeDate = '2012-03-30';
-- 2.
select * from tblMemo2 where writeDate >= '2012-03-30 00:00:00' and writeDate < '2012-03-31 00:00:00';
select * from tblMemo2 where writeDate between '2012-03-30 00:00:00' and '2012-03-30 23:59:59';
-- 3.
select * from tblMemo2 where LEFT(writeDate, 10) = '03 30 2012';
-- 4.
select * from tblMemo2 where LEFT(CONVERT(varchar,writeDate,21), 10) = '2012-03-30';
-- null 관련함수
-- innull(컬럼명, 준비값)
-- : 컬럼값이null 이면준비값을반환하고, null 이아니면원래컬럼값을반환해준다.
use pubs;
select title,price from titles;
select title,isnull(price,0) from titles;
-- 물가상승으로인한책가격이1불씩올랐다.
-- null 이었던책은1불로
select title, isnull(price,0)+1 from titles;
create table tblStaff
(
name nvarchar(10) not null primary key, -- 직원명
salary int not null, -- 월급
addr nvarchar(20) not null, -- 주소
)
-- 특정회사의프로젝트명단
create table tblProject
(
prjID int identity(1,1) not null primary key, -- 프로젝트번호
prjName nvarchar(30) not null, -- 프로젝트명
staff nvarchar(10) not null -- 담당직원명(tblStaff 직원명)
)
-- 비디오대여점
-- 회원테이블
create table tblMember
(
pk int identity(1,1) not null primary key, -- 회원번호
name nchar(5) not null, -- 회원명
grade int not null, -- 회원등급(1=준,2=정,3=우수)
bYear int not null, --생년(성인)
tel varchar(15) not null, -- 연락처
addr nvarchar(50) null, -- 주소
[money] int not null, -- 예치금
)
-- 비디오장르테이블(장르별대여가격이다름)
create table tblGenre
(
name nchar(5) not null primary key, -- 장르명
price int not null, -- 대여가격
period int not null -- 대여기간
)
-- 비디오테이블(상품)
create table tblVideo
(
pk int identity(1,1) not null primary key, -- 테잎번호
name nvarchar(30) not null, -- 제목
num int not null, -- 보유갯수
company nvarchar(20) null, -- 제작사
director nvarchar(20) null, -- 감독
major nvarchar(20) null, -- 주연배우
genre nchar(5) not null -- 장르
)
-- 대여테이블
create table tblRent
(
pk int identity(1,1) not null primary key, -- 대여번호
who int not null, -- 대여회원번호
what int not null, -- 비디오번호
rentDate datetime not null default(getdate()), -- 대여날짜
retDate datetime null, --반납날짜
)
-- tblStaff Data
INSERT INTO tblStaff (Name, Salary, Addr) VALUES ('김상형', 150, '춘천시');
INSERT INTO tblStaff (Name, Salary, Addr) VALUES ('김기문', 140, '서울시');
INSERT INTO tblStaff (Name, Salary, Addr) VALUES ('김수동', 145, '삼척시');
-- tblProject Data
INSERT INTO tblProject (Staff, PrjName) VALUES ('김상형', '홍콩수출건');
INSERT INTO tblProject (Staff, PrjName) VALUES ('김상형', 'TV 광고건');
INSERT INTO tblProject (Staff, PrjName) VALUES ('김상형', '매출분석건');
INSERT INTO tblProject (Staff, PrjName) VALUES ('김기문', '경영혁신안작성');
INSERT INTO tblProject (Staff, PrjName) VALUES ('김기문', '대리점계획');
INSERT INTO tblProject (Staff, PrjName) VALUES ('김수동', '노조협상건');
INSERT INTO tblStaff (Name, Salary, Addr) VALUES ('아무개', 120, '원효대교밑');
INSERT INTO tblProject (Staff, Prjname) VALUES ('아무개', '원자재매입');
-- tblMember Data
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('김유신',1,1970,'123-4567','12-3번지301호',10000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('강감찬',1,1978,'111-1111','777-2번지101호',0);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('유관순',1,1978,'222-2222','86-9번지',20000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('이율곡',1,1982,'333-3333',NULL,15000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('신숙주',1,1988,'444-4444','조선APT 1012호',0);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('안중근',1,1981,'555-5555','대한빌라102호',1000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('윤봉길',1,1981,'666-6666','12-1번지',0);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('이순신',1,1981,'777-7777',NULL,1500);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('김부식',1,1981,'888-8888','73-6번지',-1000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('박지원',1,1981,'999-9999','조선APT 902호',1200);
-- tblGenre Data
INSERT INTO tblGenre VALUES ('액션',1500,2)
INSERT INTO tblGenre VALUES ('에로',1000,1)
INSERT INTO tblGenre VALUES ('어린이',1000,3)
INSERT INTO tblGenre VALUES ('코미디',2000,2)
INSERT INTO tblGenre VALUES ('멜로',2000,1)
INSERT INTO tblGenre VALUES ('기타',1800,2)
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('영구와땡칠이',5,'영구필름','심영래','땡칠이','어린이');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('어쭈구리',5,'에로프로덕션','김감독','박에로','에로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('털미네이터',3,'파라마운트','James','John','액션');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('육복성',3,'대만영화사','홍군보','생룡','코미디');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('뽀뽀할까요',6,'뽀뽀사','박감독','최지후','멜로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('우정과영혼',2,'파라마운트','James','Mike','멜로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('주라기유원지',1,NULL,NULL,NULL,'액션');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('타이거킹',4,'Walt','Kebin','Tiger','어린이');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('텔미에브리딩',10,'영구필름','강감독','심으나','멜로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('동무',7,'부산필름','박감독','장동근','액션');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('공동경쟁구역',2,'뽀뽀사','박감독','이병흔','액션');
-- tblRent Data
INSERT INTO tblRent (who, what, rentDate, retDate) VALUES (1,1,'2007-01-01',NULL);
INSERT INTO tblRent (Who, What, rentDate, retDate) VALUES (2,2,'2007-02-02','2001-02-03');
INSERT INTO tblRent (Who, What, Rentdate, retDate) VALUES (3,3,'2007-02-03',NULL);
INSERT INTO tblRent (Who, What, Rentdate, retDate) VALUES (4,3,'2007-02-04','2001-02-08');
INSERT INTO tblRent (Who, What, Rentdate, retDate) VALUES (5,5,'2007-02-05',NULL);
INSERT INTO tblRent (Who, What, Rentdate, retDate) VALUES (1,2,'2007-02-10',NULL);
select * from tblStaff;
select * from tblProject;
select * from tblGenre;
select * from tblMember;
select * from tblVideo;
select * from tblRent;
-- 하위쿼리, 서브쿼리
-- : 기존의쿼리내에또다른select 가포함된형태의쿼리
select * from tblVideo;
-- 가장많은재고를보유한테잎의갯수?
select MAX(num) from tblVideo; -- 10개
-- 가장많은재고를보유한테잎의제목?
select name from tblVideo where num = 10;
-- 서브쿼리사용
select name from tblVideo where num = (select MAX(num) from tblVideo);
-- 가장적은재고를보유한테잎의제목?
select name, num from tblVideo where num = (select Min(num) from tblVideo);
-- 아시아에서가장인구수가많은국가명?
select MAX(popu) from tblCountry; --120660
select name from tblCountry where popu = 120660;
select name from tblCountry where popu = (select MAX(popu) from tblCountry where cont = 'EU');
-- 영구와땡칠이(tblVidio)의대여가격(tblGenre)?
select genre from tblVideo where name = '영구와땡칠이'; -- 어린이
select price from tblGenre where name ='어린이'; -- 어린이장르는1000원입니다..
select price, period from tblGenre where name = (select genre from tblVideo where name = '영구와땡칠이');
select * from tblStaff;
select * from tblProject;
-- 서울시에사는직원이무슨프로젝트를담당하는지궁금
select name from tblStaff where addr='서울시';
select prjName from tblProject where staff = '김기문';
select staff,prjname from tblProject where staff = (select name from tblStaff where addr = '서울시');
-- 서브쿼리가결과가2개이상의레코드..
-- 대여가격이2,000원인비디오는무엇? (테잎제목)
select name from tblGenre where price =2000; -- 값이2000원인장르
select name from tblVideo where genre = '멜로' or genre ='코미디'; -- 멜로와코미디비디오이름
select name from tblVideo where genre = (select name from tblGenre where price = 2000);
select name from tblVideo where genre in (select name from tblGenre where price = 2000);
create database HomeWork;
