10. sql 기본 명령어 -- sql 실제로 활용해보기

use sqlDB

create table categories

(

categoryID int identity(1,1) not null primary key,--카테고리 번호

categoryName varchar(25) not null   --카테고리 이름

)

go

insert categories values('가전')

insert categories values('컴퓨터')

insert categories values('서적')

create table products

(

productID int identity(1,1) not null primary key,-- 제품고유번호

modelName varchar(25) not null, -- 상품명

sellPrice int null, --판매가격

categoryID int null -- 카테고리(1,2,3)

)

go

insert products values('냉장고',100, 1)

insert products values('노트북',200, 2)

insert products values('데스크탑',150, 3)

select * from products

select * from categories

--SQL SERVER 조인구문

select modelName, sellPrice, categoryName

from products, categories

where products.categoryID = categories.categoryID

--SQL 조인구문

select products.modelName, products.sellPrice, categories.categoryName

from products join categories 

on products.categoryID = categories.categoryID

select 열이름

from 첫번째테이블 join 두번째테이블

on 조건절

--조인구문 상세

select *

from inner join

on

go

select products.modelName, products.sellPrice, categories.categoryName

from products inner join categories 

on products.categoryID = categories.categoryID

select p.modelName, p.sellPrice, c.categoryName

from products p join categories c

on p.categoryID = c.categoryID

-- 서브쿼리를 사용한 조인

select modelName, sellprice, categoryID

from products

where categoryID in(select categoryID from categories)

--inner join(내부조인)

-- 

select <열목록>

from <첫번째테이블>

inner join <두번째테이블>

on <조인이 될 조건>

where 검색 조건

select * from usertbl

select * from buytbl

--sql join

select * 

from buytbl

inner join usertbl

on buytbl.userid = usertbl.userid

where buytbl.userid = 'LCS'

--sql server 

select * 

from buytbl, usertbl

where buytbl.userid = usertbl.userid

-- 상세 조인구문

select num, buytbl.userid, prodName, amount, addr

from buytbl

inner join usertbl

on buytbl.userid = usertbl.userid

where buytbl.userid = 'LCS'