시험정리 2

USE SQLEXAMPLE_DB
GO
CREATE TABLE dbo.TEST_TABLE(
test_column1 int IDENTITY(1,1) NOT NULL PRIMARY KEY,
test_column2 varchar(50) NOT NULL,
test_column3 varchar(20) NOT NULL,
test_column4 char(10) NULL,
test_column5 int NULL
)
GO

IDENTITY(1,1)는 1부터 1씩 자동증가.
자주 삭제되는 열이 있을 경우 증가값 사이에 간격이 생길 수 있음.
간격이 발생하지 않도록 하거나, 기존 간격을 매우려면
IDENTITY를 사용하지 않거나, SET IDENTITY_INSERT를 ON 으로 설정.
(명시적으로 값을 입력하기 전에 간격이 있는지 확인해야 함.)

dbo (DataBaseOwner) 는 데이터베이스 내에 모든 동작을 수행할 수 있는 권한을 갖는 사용자.

int 와 char 는 입력 받을 값의 형태를 미리 명시 해주는 것.

varchar(50/20) 은 우선 50이나 20의 크기를 주지만, 앞에 var 옵션을 줌으로서 만약 준 크기보다 적은 값이 들어올 경우,
그에 따라 차지하는 크기가 자동으로 줄어든다.

test_column1 int IDENTITY(1,1) NOT NULL PRIMARY KEY, 에서

IDENTITY(1,1) 은 위에서 썼듯이 1부터 1증가하는 값을 자동으로 가짐.
NOT NULL 은 NULL이 아니란 의미로, 꼭 값이 들어가야 함.
PRIMARY KEY 는 다른 행 (test_column2,test_column3 등)과 겹치지 않아야 한다는 옵션.

———-Insert———–

방법1

USE SQLEXAMPLE_DB
GO

CREATE TABLE dbo.CH4_MEMBER(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
NAME VARCHAR(30),
AGE INT,
S CHAR(4),
ADDR VARCHAR(100),
BIRTHDAY CHAR(10),
ETC TEXT) //TEXT 문자열을 넣을 때 주는 옵션.
GO

INSERT INTO CH4_MEMBER(NAME,AGE,S,ADDR,BIRTHDAY,ETC)
VALUES(‘강마루’,19,’남’,’인천시’,’3′,”)

포인트.
테이블을 만들고 값을 넣을 때,
BIRTHDAY 와 같이 char로 옵션을 주었을 경우 넣는 값이 정수형이라도 문자열 처럼 ‘ 로 묶어야 한다.
하지만 기존에 옵션을 AGE와 같이 int로 주었을 경우 정수값을 넣을때 ‘ 로 묶지않고 정수만 쓴다.

방법2

USE SQLEXAMPLE_DB
GO

CREATE TABLE dbo.CH4_PIVOT_TABLE(
YEAR SMALLINT,
PART CHAR(1),
QUARTER SMALLINT,
AMOUNT DECIMAL(3,1))

포인트
SMALLINT 와 INT 의 차이

INT : -2^31(-2,147,483,648) ~ 2^31 – 1(2,147,483,647)
4 Bytes 차지

SMALLINT : 2^15(-32,768)~2^15 – 1(32,767)
2 Bytes 차지

DECIMAL : 실수값 옵션. DECIMAL(3,1) 은 전체 3 자리중에서 소숫점이 1자리 라는것이다.
(이때 실수값 옵션은 FLOAT 이나 REAL 형식도 있지만 근사 데이터 형식이어서 정확한 값을 저장하지 않고 가장 가까운 근사값을 저장하기 때문)

INSERT INTO CH4_PIVOT_TABLE VALUES (2007,’A’,1,10.1)
테이블이 생성된 순서대로, (YEAR,PART,QUARTER,AMOUNT 순) 집어 넣을 수도 있다.

——-SELECT ————

기본 형식
USE SQLEXAMPLE_DB
GO
SELECT * FROM CH4_MEMVER WHERE S=’남’
GO

* ==> 가져올 열명. 여기서는 *(아스타) 로 전체를 지정했다.
WHERE –> 검색 조건. 여기서는 S 가 ‘남’ 인 행만 검색했다.

WHERE 에서 사용할 수 있는 검색조건
1. 비교 연산자.
= != <> < > <= >= !< !>
!가 붙으면 반대의 의미를 같는다. EX) !< --> 크지 않다.

2. LIKE
특정 문자열이 포함된 데이터를 검색한다.
LIKE와 함께 사용이 가능한 와일드 카드 문자.

% 어떤 문자열이 오던 상관없음. EX) SMIL% 은 SMILE SMILING SMILASDF 모두 될 수 있다.
_ 단일 문자가 와야함. EX) SMIL_ 은 SMILA SMILB SMILC 등이 될 수 있다.
[] 지정된 문(또는 범위)의 단일 문자가 와야함 EX) SMIL[AB] –> SMILA, SMILB SMIL[A-C] –> SMILA SMILB SMILC
[^] 지정된 문자 또는 범위에 없는 단일 문자 EX) SMIL[^A] A를 제외한 문자가 모두 가능. SMILB SMILC SMILD

LIKE 이용시
USE SQLEXAMPLE_DB
GO
SELECT * FROM CH4_MEMBER WHERE NAME LIKE ‘김%’
GO
–> 김씨 성을 가진 사람을 조회한다.

3. BETWEEN

USE SQLEXAMPLE_DB
GO
SELECT * FROM CH4_MEMBER WHERE AGE BETWEEN 25 AND 30
GO

AGE가 25에서 30사이인 사람을 검색한다.

4. IN
검색목록을 지정한다.

USE SQLEXAMPLE_DB
GO
SELECT * FROM CH4_MEMBER WHERE AGE IN(19,21,31)
GO
— 비교연산자를 이용, 다르게 표현할 경우

USE SQLEXAMPLE_DB
GO
SELECT * FROM CH4_MEMBER WHERE AGE = 19 OR AGE = 21 OR AGE = 31
GO

–> 나이가 19,21,31 인 사람만 조회한다.

5. IS NULL / IS NOT NULL
NULL또는 NOT NULL이 아닌 값을 조회할 때 사용한다.

IS NOT NULL을 이용할 경우.

USE SQLEXAMPLE_DB
GO
SELECT * FROM CH4_MEMBER WHERE ADDR IS NOT NULL
GO
–> 주소가 NULL이 아닌 사람만 조회한다.

6. ANY, SOME, ALL
이 키워드를 사용하면 WHERE 절의 조건을 값 대신 하위 쿼리로 대신할 수 있다.

ANY,SOME을 사용시
USE SQLEXAMPLE_DB
GO
SELECT * FROM CH4_MEMBER
WHERE S = ‘남’ AND AGE > ANY(SELECT AGE FROM CH4_MEMBER WHERE S=’여’)
GO
–> 나이가 가장 적은 여자보다 나이가 많은 남자들을 조회.
ANY와 SOME은 같은 의미

ALL키워드를 사용시

USE SQLEXAMPLE_DB
GO
SELECT * FROM CH4_MEMBER
WHERE AGE>ALL(SELECT AGE FROM CH4_MEMBER WHERE S = ‘남’)
나이가 가장 많은 남자보다 나이가 많은 사람을 조회한다.

ANY나 SOME은 OR의 의미를 가짐.
“WHERE AGE>ANY(SELECT AGE FROM CH4_MEMBER WHERE S=’여’) 에서,
CH4_MEMBER의 S=’여’ 중 어느 것이라도 AGE>[조건식] 을 만족하면 출력한다.
따라서, “가장 나이가 적은 여성보다” 라는 조건이 탄생.

반대로 ALL은 AND의 의미를 가짐.
WHERE AGE>ALL(SELECT AGE FROM CH4_MEMBER WHERE S=’남’) 에서,
CH4_MEMBER의 S=’남’ 중 모든 AGE>[조건식]을 만족해야 하므로
“가장 나이가 많은 남자” 라는 조건이 탄생한다.

/* LIKE 끝 */

ORDER BY –정렬

USE SQLEXAMPLE_DB
GO
SELECT *
FROM CH4_MEMBER
ORDER BY AGE DESC
GO

SELECT * FROM CH4_MEMBER ORDER BY AGE DESC 에서,
AGE는 열 이름.
DESC는 내림차순을 의미한다.

오름차순, 내림차순 각각 ASC, DESC 라는 옵션을 갖는다.

위 내용을 오름차순으로 정렬할 경우,

SELECT * FROM CH4_MEMBER ORDER BY AGE ASC 가 된다.

USE SQLEXAMPLE_DB
GO
SELECT * FROM CH4_MEMBER ORDER BY AGE DESC, NAME
에서는 AGE가 같을 경우, NAME 순으로 정렬한다.

TOP –검색할 행의 갯수.

USE SQLEXAMPLE_DB
GO
SELECT TOP(3) * FROM CH4_MEMBER ORDER BY AGE DESC

나이순으로 내림차순 정렬한 것 들중 상위 세개의 결과만을 출력한다.
–> 나이가 많은 세명을 출력한다.

뒤에 PERCENT 옵션을 줄 수도 있는데,

USE SQLEXAMPLE_DB
GO
SELECT TOP(20) PERCENT * FROM CH4_MEMBER ORDER BY AGE DESC
GO
–> 나이가 많은 20%를 검색한다.

USE SQLEXAMPLE_DB
GO
SELECT TOP(50) PERCENT WITH TIES * FROM CH4_MEMBER ORDER BY AGE DESC
GO

만약 같은 값이 있어 TOP(N) N의 갯수보다 더 클 경우, WITH TIES 옵션으로 같은 값 모두를 출력할 수도 있다.
이때, WITH TIES 는 ORDER BY 와 함께 사용해야 한다.

/* TOP() 종료 */

AS

SELECT 구문에서 열 이름을 다른 이름으로 지정할때 사용.

USE SQLEXAMPLE_DB
GO
SELECT TOP(3)
NAME AS 이름,
AGE AS 나이,
BIRTHDAY AS 생일
FROM CH4_MEMBER ORDER BY AGE DESC
GO
–> NAME을 ‘이름’ 으로, AGE를 ‘나이’로, BIRTHDAY를 ‘생일’로 바꾸어 출력한다.

집계함수

USE SQLEXAMPLE_DB
GO
SELECT AVG(AGE) AS 평균,
MAX(AGE) AS 최대,
MIN(AGE) AS 최소,
SUM(AGE) AS 합,
COUNT(AGE) AS 개수
FROM CH4_MEMBER
GO

GROUP BY
해당 열의 데이터에 따라 정렬하고 그룹을 만듬.

USE SQLEXAMPLE_DB
GO
SELECT S, AVG(AGE) AS 평균나이 FROM CH4_MEMBER GROUP BY S
GO

출력 :
S 평균나이
1 남 21
2 여 25

S의 값인 ‘남’,’여’ 에 따라 AVG(AGE)를 구하고 출력한다.

GROUP BY ALL
SELECT문에 WHERE로 조건문을 넣었을때, 그 조건에 만족하지 못하는 데이터도 NULL로 반환되게 하는것.

USE EXAMPLE_DB
GO
SELECT S,
AVG(AGE) AS 평균나이
FROM CH4_MEMBER WHERE S = ‘남’
GROUP BY ALL S
GO
–> 남자의 나이 평균을 구하고 나머지는 NULL로 표기.

HAVING
GROUP BY 문의 검색조건

USE SQLEXAMPLE_DB
GO
SELECT S,AVG(AGE) AS 평균나이 FROM CH4_MEMBER GROUP BY S HAVING AVG(AGE) > 22
GO

–> AVG(AGE)가 22 이상인 것만 출력한다.

/* SELECT,INSERT,CREATE 종료 */

/* DELETE, UPDATE 시작 */

UPDATE

UPDATE 기본 양식
UPDATE [TABLE_NAME]
SET [LINE_NAME] = [DATA or NAME]
WHERE [conditions]

USE SQLEXAMPLE_DB
GO
BEGIN TRAN –> ROLLBACK 과 COMMIT을 위함.
UPDATE CH4_1_MEMBER
SET BIRTHDAY = ‘FEB’
GO
SELECT * FROM CH4_1_MEMBER
ROLLBACK TRAN
GO
SELECT * FROM CH4_1_MEMBER
GO

BIRTHDAY를 모두 FEB 로 변경한다.

WHERE 절을 사용할 경우.

USE SQLEXAMPLE_DB
GO
BEGIN TRAN
UPDATE CH4_1_MEMBER
SET BIRTHDAY = ‘FEB’
WHERE BIRTHDAY=’2′
GO
SELECT * FROM CH4_1_MEMBER
ROLLBACK TRAN

BIRTHDAY가 2 인 열만 FEB로 바뀐다.
저기 ‘FEB’ 자리에, AGE*2 같은 계산식도 넣을 수 있다.
(그럼 해당하는 열의 나이의 두배가 BIRTHDAY 행에 담기게 된다.)

DELETE

기본 양식
DELETE [TABLE_NAME]
WHERE [conditions]
(WHERE 절 없이 사용시 해당 TABLE의 모든 데이터가 날아감)

USE SQLEXAMPLE_DB
GO
BEGIN TRAN
DELETE CH4_1_MEMBER
GO
SELECT * FROM CH4_1_MEMBER
ROLLBACK TRAN
GO

CH4_1_MEMBER의 모든 값을 삭제한다.

USE SQLEXAMPLE_DB
GO
BEGIN TRAN
DELETE CH4_1_MEMBER
WHERE AGE>25
GO
SELECT * FROM CH4_1_MEMBER
ROLLBACK TRAN
GO

나이가 25세 이상인 사람만 삭제한다.

/* DELETE, UPDATE 끝 */

/* UNION VIEW 시작 */
두 쿼리의 결과를 하나의 집합으로 만들어줌.
두 테이블의 열 이름은 달라도 열의 갯수는 같고, 형식은 같거나 호환이 되어야 함.

기본 형식
SELECT 가져올 열이름 FROM [TABLE_NAME1]
UNION (ALL)
SELECT 가져올 열이름2 FROM [TABLE_NAME2]

사용 예

USE EXAMPLE_DB
GO
SELECT * FROM CH5_UNION_TABLE1
SELECT * FROM CH5_UNION_TABLE2
GO
SELECT NUMBER, NAME FROM CH5_UNION_TABLE1
UNION
SELECT NUM,N FROM CH5_UNION_TABLE2
GO
–> 두 테이블에서 중복제외 모든 자료 조회한다.

출력은 처음 SELECT 로 따름.
NUMBER NAME
1 강마루
2 김승욱
3 .
4 .
. .
. .
. .
. .

VIEW
SELECT 구문으로 한개 이상의 테이블을 조회하는 뷰.

뷰 생성
USE SQLEXAMPLE_DB
GO
CREATE VIEW CH5_1_BUY_LIST2
AS
SELECT M.NAME, B.GOODS, B.NUM
FROM CH5_1_MEMBER AS M INNER JOIN CH5_1_BUY_LIST AS B ON M.ID = B.ID
GO
SELECT * FROM CH5_1_MEMBER
SELECT * FROM CH5_1_BUY_LIST
SELECT * FROM CH5_1_BUY_LIST2
GO
–> CH5_1_MEMBER 와 CH5_1_BUY_LIST 를 조인하여 성명,상품명,수량을 조회.

/* UNION VIEW 종료 */

/* JOIN 시작 */

JOIN은 두개 이상의 테이블에서 서로간의 논리적인 관계를 이용하여 하나의 결과집합을 만듬.

USE EXAMPLE_DB
GO
SELECT * FROM CH5_SALESMAN
SELECT * FROM CH5_SALES_TABLE
GO
SELECT S.NUMBER, S.NAME, T.KIND, T.AMOUNT
FROM CH5_SALESMAN AS S INNER JOIN CH5_SALES_TABLE AS T
ON S.NUMBER = T.NUMBER
ORDER BY NUMBER
GO

수정은 CREATE 를 ALTER로 바꾸기만 하면 됨.

CH5_SALESMAN 내용

NUMBER NAME
1 강마루
2 김승욱
3 김재훈
4 라명은
5 류윤호
6 류재승

CH5_SALES_TABLE 내용

NUMBER KIND AMOUNT
1 마우스 15
2 키보드 10
4 지우개 45
5 연필 55
6 계산기 5
1 키보드 5
4 연필 15
5 계산기 2

출력내용

NUMBER NAME KIND AMOUNT
1 강마루 마우스 15
1 강마루 키보드 5
2 김승욱 키보드 10
4 라명은 지우개 45
4 라명은 연필 15
5 류윤호 계산기 2
5 류윤호 연필 65
6 류재승 계산기 5

CH5_SALESMAN 과 CH5_SALES_TABLE 의 NUMBER 대로 행을 일치화 시키고
NUMBER로 정렬한 결과가 출력내용.

/* JOIN 종료 */

/* PROCEDURE 시작 */

저장 프로시저 예
USE SQLEXAMPLE_DB
GO
CREATE PROCEDURE userp_birthday
@age1 CHAR(10)
AS
SELECT NAME, AGE, S, ADDR, BIRTHDAY
FROM CH5_MEMBER
WHERE AGE > @age1

–> 앞에 @가 붙은것은 변수.
–> 입력받은 변수(나이)보다 많은 사람을 조회하는 저장 프로시저.

수정은 VIEW와 같이 CREATE를 ALTER 로 변경하면 됨.

저장 프로시저 OUTPUT 매개 변수

USE SQLEXAMPLE_DB
GO
CREATE PROCEDURE userp_birthday2
@birthday1 CHAR(10),
@out_id INT OUTPUT
AS
SELECT @out_id=ID
FROM CH6_MEMBER
WHERE BIRTHDAY=@birthday1
GO

실행

DECLARE @out_id int
EXEC userp_birthday 2
GO

–> 생일이 2보다 큰 사람을 조회한다.

삭제
USE SQLEXAMPLE_DB
GO
DROP PROCEDURE userp_birthday
GO

/* 프로시저 종료 */

/* TRIGGER 시작 */

트리거란,
어떤 이벤트(INSERT DELETE UPDATE)에 의해 자동으로 실행되는 저장 프로시저이다.

DML 트리거 생성 예
(DML이란, INSERT DELETE UPDATE 등을 말함)

USE SQLEXAMPLE_DB
GO
CREATE TRIGGER TEST_TRIGGER
ON CH7_MEMBER
AFTER INSERT –> “INSERT 후에” 라는 조건.
AS PRINT(‘SUCCESS!!’)
GO

수정은 CREATE 대신 ALTER 넣는다.

삭제
USE SQLEXAMPLE_DB
GO
DROP TRIGGER TEST_TRIGGER
GO