[SQL문] select문

희망 보고서 | 최강땡칠
http://blog.naver.com/west372/80055289595

 

select 문

select rand(100),rand(),rand() --랜덤값발생함수(시드점)

select floor(29.9),floor(29.1)-- 소숫점자리내림

select ceiling(29.9),ceiling(29.1) -- 소숫점자리올림

select round(29.1,0),round(29.9,0),round(29.956,1) -- 소숫점자리반올림(값,반올림자리)

select ascii('A'), char(65)

-- 아스키값반환, 캐릭터반환

select unicode('A'), nchar(65),unicode('가'), nchar(44032)

-- 유니코드값반환, 유니코드캐릭터값반환

select charindex('server','SQL server 2005')

-- 찾고자하는문자를검색(찾고자하는문자,찾을데이타)

select len('SQL server 2005') -- 문자열의길이반환(문자열)

select left('SQL server 2005',3), right('SQL server 2005',4)

--왼쪽문자열반환, 오른쪽문자열반환(문자열,갯수)

select substring('SQL server 2005',5,5) -- 문자열반환(문자열, 시작위치,갯수)

select lower('SQL server 2005'), upper('SQL server 2005') -- 소문자변환, 대문자변환

select 'abc', 'abc', ltrim('abc'), rtrim('abc') -- 공백없애기

select ltrim(rtrim('abc')) -- 앞뒤로공백없애기

select replace('SQL server 2005','server','서버') -- 문자수정하기(문자열,바뀔문자,바꿀문자)

select replicate('sql ',10) -- 복제하기(문자열,복제수)

select reverse('SQL server 2005') --문자열뒤집기(문자열)

select stuff('SQL server 2005',5,6,'서버') -- 문자수정하기(문자열,바뀔문자열시작,갯수,바꿀문자)

 

서버 replicate (복제) – 분산을 위해서

backup (백업) – 자료 보존(원본을 보호)

 

최대값 캐스팅

 

create table tblMAX

(

c1 varchar(MAX), --크기를최대값을지정

c2 nvarchar(MAX)

)

insert into tblMAX values(replicate('A',1000000), replicate('가',1000000)) -- 테이블에1000000에의문자를넣는다

select len(c1),len(c2) from tblMAX

-- 실제로1000000가아닌8000,4000개만들어간다, 최대값에맞게들어감

delete from tblMAX

insert into tblMAX values

(

replicate(cast('A' as varchar(MAX)),1000000),

-- 1000000개를넣고싶다면최대값으로캐스팅해야함.

replicate(cast('가' as varchar(MAX)),1000000)

)

select len(c1),len(c2) from tblMAX

수정(성능차이)

1.replace

2.stuff

3.update à 컬럼이름.write()

create table tblMAX

(

c1 varchar(MAX), --크기를최대값을지정

c2 nvarchar(MAX)

)

insert into tblMAX values(replicate('A',1000000), replicate('가',1000000))

-- 테이블에1000000에의문자를넣는다

select len(c1),len(c2) from tblMAX

-- 실제로1000000가아닌8000,4000개만들어간다, 최대값에맞게들어감

delete from tblMAX

insert into tblMAX values

(

replicate(cast('A' as varchar(MAX)),1000000),

-- 1000000개를넣고싶다면최대값으로캐스팅해야함.

replicate(cast('가' as varchar(MAX)),1000000)

)

select len(c1),len(c2) from tblMAX

update tblMAX-- stuff을이용한문자열수정

set c1=stuff((select c1 from tblMAX),999991,10,replicate('B',10)), -- c1의999991

c2=stuff((select c2 from tblMAX),999991,10,replicate('나',10))

update tblMAX -- write를이용한문자열수정

set c1.write('CCCCCCCCCC',999990,10),c2.write('다다다다다다다다다다',999990,10)

select substring((select c1 from tblMAX),999991,10),substring((select c2 from tblMAX),999991,10)

select 컬럼들.. from table명

top- order by (asc(기본값), desc)

distinct

use pubs

select * from sales

select top(5) * from sales order by qty asc

-- top은order(정렬)과함께쓴다, qty를오름차순으로하고상위5개출력

select top(5) stor_id,qty from sales order by qty desc-- sot_id값과qty만출력

select top(5) percent stor_id,qty from sales order by qty desc-- 상위5%로출력

select top 6 with ties * from sales order by qty desc-- with ties는같은값도표시(동점을표시)

select distinct stor_id from sales --중복값제거

select * from titles

-- 가장가격이저렴한책3권검색(책번호, 책이름, 가격) 가격이널인값을제외한.

select top(3) pub_id, title, price from titles where price IS NOT NULL order by price asc

-- 책의종료에는무엇이있는지검색(종류만검색)

select distinct type from titles

select 컬럼들.. from table명 where 조건절

select * from sales

-- sales 테이블에서서점번호가7066번인서점의번호와

-- 주문날짜, 책번호를검색하라. 단컬럼명은서점아이디, 주문날짜, 책아이디로출력하라

select stor_id as [서점아이디], ord_date as [주문날짜], title_id as [책아이디] from sales where stor_id = 7066

-- sales에서mc2222이란책을판매한서점의아이디와판매부수를검색하라

select stor_id as [서점아이디], qty as [판매부수] from sales wheretitle_id= 'mc2222'

-- sales에서1992년도에팔린책번호와서점아이디를검색

select title_id as [책번호], stor_id as [서점아이디], ord_date as [주문날짜] from sales where year(ord_date)= 1992

select title_id as [책번호], stor_id as [서점아이디], ord_date as [주문날짜] from sales where ord_date>='1992.1.1' and ord_date<='1992.12.31'

select title_id as [책번호], stor_id as [서점아이디], ord_date as [주문날짜] from sales where ord_date between '1992.1.1' and '1992.12.31'

-- titles에서가격이19달러이상22달러미만인책의아이디와책이름, 가격검색

select title_id, title, price from titles where price>=19 and price<22

-- titles에서책아이디가pc1035, ps1372, pc9999인책의아이디와그책을판매하는출판사아이디, 책이름검색

select title_id, pub_id, title from titles where title_id='pc1035' or title_id='ps1372' or title_id='pc9999'

select title_id, pub_id, title from titles where title_id in('pc1035','ps1372','pc9999')

컬럼 like 값% : 0글자 이상

_ : 1글자

select title_id, title from titles

where title_id like 'BU%'

select title_id, title from titles

where title_id like 'BU____'

select * from authors

-- authors 에서성에s가포함되고state가CA인작가의full name은?

use pubs

select au_lname + ' ' + au_fname from authors where upper(au_lname) like '%S%' and state='CA'

use testDB

select * from usertbl

-- 성이김씨인지이씨인지둘중하나이고

-- 이름이순신이라는사람의이름과아이디를검색해라

select name, userID from usertbl where name like '[김이]순신'

-- pubs 데이타베이스, authors 테이블에서

-- 성에G로시작하고두번째글자는확실히R은아니결우의

-- 작가full name을검색하되성과이름을한컬럼으로출력

select au_fname + ' ' + au_lname from authors where au_lname like 'GR[e]%'

select * from titles where price is not null -- 값이null 아닌것

-- testDB 데이타베이스, usertbl 테이블에서

-- 태어난곳이강원이거나휴대폰이없고키가180이상인

-- 사람의아이디와이름, 주소, 키를검색하시오.

use testDb

select * from userTbl

select userid,name,addr,height from testDB..usertbl where addr='강원' or (mobile1 is null and height>=180)

select … from talbe명 where 조건 order by 컬럼(1차), 컬럼(2차) [asc/desc]

select name, height from usertbl order by height, name

select … from talbe명 where 조건 group by컬럼

select * from buytbl order by userid

select userid, sum(price) as '총구입금액' ,sum(amount) as '총구입양'from buytbl group by userid

-- 제품카테고리별로팔린총갯수를검색하라

select groupname, sum(amount) from buytbl group by groupname

select … from table명 group by 컬럼 having 조건

where : 구룹으로 묶기 전 조건

having : 구룹으로 묶은 후 결과값에 따른.. 집계함수을 사용

select * from buytbl

select userid, sum(price) from buytbl

where amount >=3 group by userid

select userid, sum(price) from buytbl

group by userid having sum(amount) >=3

select * from pubs..sales

--(1)

-- pubs 데이터베이스, sales 테이블

-- 책아이디에따라총팔린갯수의합을검색

select title_id, sum(qty) from pubs..sales group by title_id

--(2)

-- 위의결과에서합계를낸수량이20권이상인것만검색

select title_id, sum(qty) from pubs..sales group by title_id having sum(qty)>=20

--(3)

-- 책별로팔린수량이40권이상인책들중에서

-- 총팔린수량의책의합이50권이상인것은?

select title_id, sum(qty) from pubs..sales where qty>=40 group by title_id having sum(qty)>=50

select … from table명 compute/ compute by

select *, (price*amount) as [구입금액] from buytbl

select *, (price*amount) as [구입금액] from buytbl compute sum(price*amount) compute avg(price*amount)

select *, (price*amount) as [구입금액] from buytbl

order by groupname -- cmpute by는반드시정렬이필요함

compute sum(price*amount) by groupname

select … from table명 guoup by rollup, cube

select num,groupname, sum(price*amount) as '비용' from buytbl

group by groupname, num

with rollup – 중간합계와 총합을 구함

use testDB

create table tblcube

(

prodname nchar(3),

color nchar(2),

amountint

)

insert into tblcube values('컴퓨터','검정',11)

insert into tblcube values('컴퓨터','파랑',22)

insert into tblcube values('모니터','검정',33)

insert into tblcube values('모니터','파랑',44)

select * from tblcube

select prodname,color,sum(amount) as '수량합계' from tblcube

group by prodname,color – 여러 개의 기준으로 합을 구한다

with cube