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

use master  

create database sqlDB

go

use sqlDB

USE sqlDB

CREATE TABLE userTbl -- 사용자테이블

( userID  nchar(8) NOT NULL PRIMARY KEY, -- 사용자아이디

  name    nvarchar(10) NOT NULL, -- 이름

  birthYear   int NOT NULL,  -- 출생년도

  addr  nchar(4) NOT NULL, -- 지역(경기,서울,경남,전북식으로2글자만입력)

  mobile1 nchar(3), -- 휴대폰의국번(011, 016, 017, 018, 019, 010 등)

  mobile2   nchar(8), -- 휴대폰의나머지전화번호(하이픈제외)

height    smallint  -- 신장

)

GO

CREATE TABLE buyTbl -- 사용자구매테이블

(  num int IDENTITY NOT NULL PRIMARY KEY, -- 순번(PK)

   userid  nchar(8) NOT NULL --아이디(FK)

FOREIGN KEY REFERENCES userTbl(userid),

   prodName nchar(6) NOT NULL, -- 물품명

   groupName nchar(4)  , -- 분류

   price     int  NOT NULL, -- 단가

   amount    smallint  NOT NULL -- 수량

)

GO

INSERT INTO userTbl VALUES(N'PJS', N'박지성', 1983, N'서울', N'011', N'1111111', 181)

INSERT INTO userTbl VALUES(N'PJY', N'박주영', 1986, N'경기', N'011', N'2222222', 178)

INSERT INTO userTbl VALUES(N'JJJ', N'조재진', 1986, N'충북', N'019', N'3333333', 179)

INSERT INTO userTbl VALUES(N'LCS', N'이천수', 1983, N'인천', N'011', N'4444444', 179)

INSERT INTO userTbl VALUES(N'AJH', N'안정환', 1979, N'강원', NULL  , NULL      , 182)

INSERT INTO userTbl VALUES(N'KNI', N'김남일', 1977, N'경북', N'016', N'6666666', 183)

INSERT INTO userTbl VALUES(N'LYP', N'이영표', 1983, N'전북', NULL  , NULL      , 178)

INSERT INTO userTbl VALUES(N'SKH', N'설기현', 1978, N'서울', N'011', N'8888888', 182)

INSERT INTO userTbl VALUES(N'SJK', N'송종국', 1979, N'경기', N'018', N'9999999', 178)

INSERT INTO userTbl VALUES(N'CJC', N'최진철', 1975, N'제주', N'019', N'0000000', 185)

GO

INSERT INTO buyTbl VALUES(N'PJY', N'운동화', NULL   , 30,   2)

INSERT INTO buyTbl VALUES(N'PJY', N'노트북', N'전자', 1000, 1)

INSERT INTO buyTbl VALUES(N'LCS', N'모니터', N'전자', 200,  1)

INSERT INTO buyTbl VALUES(N'CJC', N'모니터', N'전자', 200,  5)

INSERT INTO buyTbl VALUES(N'PJY', N'청바지', N'의류', 50,   3)

INSERT INTO buyTbl VALUES(N'CJC', N'메모리', N'전자', 80,  10)

INSERT INTO buyTbl VALUES(N'AJH', N'책'    , N'서적', 15,   5)

INSERT INTO buyTbl VALUES(N'SKH', N'책'    , N'서적', 15,   2)

INSERT INTO buyTbl VALUES(N'SKH', N'청바지', N'의류', 50,   1)

INSERT INTO buyTbl VALUES(N'CJC', N'운동화', NULL   , 30,   2)

INSERT INTO buyTbl VALUES(N'SKH', N'책'    , N'서적', 15,   1)

INSERT INTO buyTbl VALUES(N'CJC', N'운동화', NULL   , 30,   2)

GO

SELECT * FROM userTbl

SELECT * FROM buyTbl

----------- 여기까지가  테이블 만들고 정보 입력하는거이다

USE master

BACKUP DATABASE sqlDB TO DISK = 'C:\sqlDB.bak'

SELECT * FROM userTbl

SELECT * FROM userTbl WHERE name = '박지성'

SELECT userID, Name FROM userTbl WHERE birthYear >= 1981 AND height >= 180

SELECT userID, Name FROM userTbl WHERE birthYear >= 1981 OR height >= 180

SELECT Name, height FROM userTbl WHERE height >= 180 AND height <= 183

SELECT Name, height FROM userTbl WHERE height BETWEEN 180 AND 183

SELECT Name, height FROM userTbl WHERE height=178 OR height=180 OR height=182

SELECT Name, height FROM userTbl WHERE height IN (178, 180, 182)

SELECT Name, height FROM userTBL WHERE height > 181

SELECT Name, height FROM userTbl 

  WHERE height > (SELECT height FROM userTbl WHERE Name = '박지성')

SELECT Name, height FROM userTbl 

  WHERE height >= (SELECT height FROM userTbl WHERE mobile1 = '019')

SELECT Name, height FROM userTbl 

  WHERE height >= ANY (SELECT height FROM userTbl WHERE mobile1 = '019')

SELECT Name, height FROM userTbl 

  WHERE height = ANY (SELECT height FROM userTbl WHERE mobile1 = '019')

SELECT Name, height FROM userTbl 

  WHERE height IN (SELECT height FROM userTbl WHERE mobile1 = '019')

SELECT Name, height FROM userTbl ORDER BY height

SELECT Name, height FROM userTbl ORDER BY height DESC

SELECT Name, height FROM userTbl ORDER BY height DESC, name ASC

SELECT addr FROM userTbl

SELECT addr FROM userTbl ORDER BY addr

SELECT DISTINCT addr FROM userTbl

USE sqlDB

SELECT * INTO buyTbl2 FROM buyTbl

SELECT * FROM buyTbl2

SELECT userid, prodName INTO buyTbl3 FROM buyTbl

SELECT * FROM buyTbl3

USE sqlDB

SELECT userid, price, amount FROM buyTbl ORDER BY userid

SELECT userid, SUM(amount)  FROM buyTbl GROUP BY userid

SELECT userid AS [사용자아이디], SUM(amount) AS [총구매개수]  

  FROM buyTbl GROUP BY userid

SELECT userid AS [사용자아이디], SUM(price*amount) AS [총구매액]  

  FROM buyTbl GROUP BY userid

SELECT AVG(amount) AS [평균구매개수] FROM buyTbl

SELECT AVG(amount*1.0) AS [평균구매개수] FROM buyTbl

  -- 또는

SELECT AVG(CAST(amount AS DECIMAL(10,6))) AS [평균구매개수] FROM buyTbl

SELECT userid, AVG(amount*1.0) AS [평균구매개수] FROM buyTbl

  GROUP BY userid

SELECT Name, MAX(height), MIN(height) FROM userTbl

SELECT Name, MAX(height), MIN(height) FROM userTbl GROUP BY Name

SELECT Name, height

FROM userTbl 

WHERE height = (SELECT MAX(height)FROM userTbl) 

   OR height = (SELECT MIN(height)FROM userTbl)

SELECT COUNT(*) FROM userTbl

SELECT COUNT(mobile1) FROM userTbl

--------------다양한 방법으로 selec 문을 실행