MS-SQL Implementation 강의 노트

MS-SQL Implementation강의 노트 Written by 남현주

Transact-SQL구성요소 • 데이터제어언어 -GRANT,DENY,REVOKE 문 (Data Control Language, DCL) • 데이터정의언어 -CREATE,ALTER,DROP 문 (Data Definition Language, DDL) • 데이터조작언어 -SELECT,INSERT,UPDATE,DELETE (Data Manipulation Language, DML) • 변수, 연산자, 함수, 처리제어언어, 주석

데이터베이스 객체 • 데이타베이스 객체 이름 붙이기(4 part name) • Server_name.database_name.owner_name.object_name • Ex) select * from instructor1.pubs.dbo.titles • select * from pubs.dbo.titles • select * from dbo.titles • select * from titles • 중간의 값만 표기를 생략할 경우는 . 으로 표시

데이터 타입 • SQL 서버 2000추가된 데이터 타입 • BigInt • sql_variant • table

기본적인 select문 SELECT 나열할 컬럼 리스트 FROM 가져올 테이블 (WHERE 가져올 행의 조건) * FROM 절이 생략될 수 있나용 ? …

컬럼에 대한 처리 예) SELECT * FROM titles – titles테이블의전체자료가져오기 SELECT title_id, title, price, pub_id FROM titles –테이블의 일부 자료만 가져오기 SELECT title_id, pub_id, price, title FROM titles –원하는컬럼순으로가져오기

예) SELECT ‘책번호:’, title_id, pub_id, price, title FROM titles –임의의컬럼만들기 SELECT Title_no = title_id, [출판사번호] = pub_id, price, title FROM titles –컬럼의제목바꾸기(1) SELECT title_id as Title_no , pub_id as ‘출판사번호’, price, title FROM titles –컬럼의제목바꾸기(2) SELECT title_id Title_no , pub_id [출판사번호], price, title FROM titles –컬럼의제목바꾸기(3)

데이터 변경 SELECT title_id as Title_no, pub_id as [출판사번호], price, Convert(char(30), title) as [줄인제목] FROM titles –자료를변형시킴(1) SELECT title_id as Title_no, pub_id as ‘출판사번호’, price * 1.1 as ‘가격(부가세포함)’ FROM titles –자료를변형시킴(2) * 주의: SELECT 10/3 은?

함 수 형변환함수) CONVERT(), CAST() 연산함수) ABS() –절대값 CEILING() –같거나 큰, 가장 작은 정수값으로 변환 SELECT CEILING(29.9)  30 FLOOR() –같거나 작은, 가장 큰 정수값으로 변환 SELECT FLOOR(29.9)  29 ROUND() –반올림 SELECT ROUND(29.1, 0), ROUND(29.953, 1) 29.0 30.000

문자열함수) LTRIM() : 앞쪽의빈칸을모두삭제한다. RTRIM() : 뒤에오는빈칸을삭제한다. SUBSTRING() : 문자나이진문자열의일부분을 돌려준다. (한글도한글자가한문자로인식) SELECT SUBSTRING(‘아버지나는누구예요?’, 3,6) SELECT SUBSTRING(‘ABFZ!!!’, 1,3) REVERSE() –문자표현식을거꾸로돌려준다.

날짜함수) GETDATE() –현재의날짜와시간 SELECT CONVERT(varchar(30), GETDATE(), 102) DATEADD() –datepart부분에지정한숫자값을더한다. SELECT DATEADD(DD, 100, GETDATE()) – 오늘로부터100일후는? (SELECT GETDATE()) + 100 는 가능할까요?)

시스템 함수) ISNULL() –표현식이 널 일 때 지정한 값을 보여줌 예) SELECT CONVERT(INT,ISNULL(price, 0)) FROM titles CASE –조건 목록을 평가하고 가능한 여러 결과 식 중 하나를 반환한다 예) SELECT … , ‘등급’= CASE WHEN qty >= 50 THEN ‘A’ WHEN qty >= 30 THEN ‘B’ ELSE ‘C’ END FROM sales

메타데이타함수) OBJECT_ID() : 데이터베이스 개체 ID를 반환 OBJECT_NAME() : 데이터베이스 개체 이름을 반환 DB_ID() : 데이터베이스 ID 번호를 반환 DB_NAME() : 데이터베이스 이름을 반환 보안함수) SUSER_SNAME() : 시스템유저명을돌려준다. USER_NAME() : 지정된 ID 번호에서 사용자 데이터베이스 사용자 이름을 돌려준다.

행에 대한 처리 예)조건에맞는행가져오기 SELECT title_id, qty FROM sales WHERE title_id = ‘BU1032’ * 비 교 = > < >= <= <>

* NULL SELECT title_id, price FROM titles WHERE price IS NULL 주의) SELECT title_id, price FROM titles WHERE price = NULL

* 정 렬 SELECT title_id, qty FROM sales ORDER BY title_id, qty 주의) SELECT title_id, qty FROM sales ORDER BY title_id, qty DESC

* 범 위 SELECT title_id, qty FROM sales WHERE qty BETWEEN 10 AND 20 (WHERE qty >= 10 AND qty <= 20) * 목 록 SELECT title_id, qty FROM sales WHERE title_id IN (‘BU1032’,’BU1111’,’MC3021’) (WHERE title_id =‘BU1032’ OR title_id =’BU1111’ OR title_id =’MC3021’)

* 문자열 비교 SELECT title_id, title FROM titles WHERE title LIKE ‘%computer%’ 패턴 : % , _ , [ ] , [ ^ ]

표현식 해당되는것 해당되지않는것 book_ books, booka, booky book, booked book% book, books, booked abook, atbooks, adbooked %book% abook, abooks, book brook, brooks [st]ing sing, ting ving, king, k, v, vin [b-f]ing bing, cing, ding, eing ,fing aing, b, f, ging M[^c]% Mike, Many, Mickey McDonald, McAthur, M, Mc LIKE와패턴매칭 예)

* 중복된 행 제거 – DISTINCT DISTINCT를 사용하면, 합계, 평균 또는 개수를 계산하기 전에 중복 값이 삭제됩니다. SELECT AVG(DISTINCT price) FROM titles WHERE type = 'business' * 출력 결과 제한 - TOP n 결과 집합에 있는 행 중에서 맨 위부터 n개의 행만 출력되도록 지정합니다.

/* 성능향상을위한제안 */ .NOT 연산자를사용하지말 것 SELECT title_id, qty FROM sales WHERE qty NOT BETWEEN 10 AND 20 .연산자앞에는컬럼만오게할것 SELECT title_id, price FROM titles WHERE price * 1.1 < 20.00 .적절한( )와띄어쓰기 :가독성을좋게한다.

요약 정보 처리 집계 함수) AVG() : 표현식 전체나 각각의 평균값 COUNT() : 표현식 전체나 각각의 개수 COUNT(*) : 선택된 모든 행의 개수 MAX() : 표현식에서 가장 큰 값 MIN() : 표현식에서 가장 작은 값 SUM() : 수치 표현식에서 전체나 각각의 합계 예) SELECT SUM(price), COUNT(*), COUNT(price) FROM titles

* GROUP BY/ HAVING (조건) : SELECT문 – WHERE (예선탈락) GROUP BY – HAVING (본선탈락) SELECT title_id, sum(qty) FROM sales WHERE qty < 40 GROUP BY title_id HAVING sum(qty) >= 30

* GROUP BY ALL : WHERE절에서 제외된 것도 결과에 포함 시킬 수 있다. SELECT title_id, sum(qty) FROM sales WHERE qty > 40 GROUP BY ALL title_id

* COMPUTE/ COMPUTE BY 주의! COMPUTE BY를 사용하려면 ORDER BY가 COMPUTE BY에서 지정된 순서대로, 또는 그 일부분의 순서대로 COMPUTE BY를 사용해야 한다. 참고: COMPUTE BY, COMPUTE 문은 ANSI 호환성이 없다.  ROLLUP과 CUBE사용할 것!

* COMPUTE/ COMPUTE BY… SELECT type, title_id, price FROM titles ORDER BY type COMPUTE avg(price) BY type 그러나, 결과 값이 새로운 행으로 나타나기 때문에 즉, 테이블의 구조를 깨어지게 하므로 ROLLUP을 사용하는 것이 낫다.

* ROLLUP 과 CUBE 가지고 있는 데이터를 분석할 용도로 쓰일 수 있다. SELECT type, pub_id, AVG(price) FROM titles GROUP BY type, pub_id WITH ROLLUP

2,3을 한꺼번에 사용하는 방법이 CUBE SELECT pub_id, type, AVG(price) FROM titles GROUP BY pub_id, type WITH CUBE  데이터웨어하우징, 데이터마트, 데이터마이닝 등에 활용될 수 있다. 단, 표현식은 10개까지 허용하고 GROUP BY ALL은 사용할 수 없다.

다중 테이블 쿼리하기 조인이란 두 개 이상의 테이블에 쿼리하여 각 테이블의 행과 컬럼을 통합하여 하나의 결과 세트를 만들어 내는 작업이다. 즉, 정규화로 나누어진 테이블 혹은 컬럼 들을 다시 모아오는 것이다. ANSI 문법을 사용하도록 하자

INNER JOIN) 가장 일반적인 JOIN이다. 디폴트임 예) SELECT title, price, pub_name FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id EquiJOIN) 전체 컬럼을 가져와서 키가 되는 컬럼이 중복되게 하는 것

CROSS JOIN)– Cartesian Product 양쪽 테이블의 모든 행에 대해 서로 연결되게 한다. 학술적인 의미 외엔 의미 없다. OUTER JOIN) INNER JOIN은 두 테이블에 있는 키 값이 일치하는 데이터만 가져오는 것에 비해 OUTER JOIN은 어느 한 쪽의 데이터를 모두 가져온다. 실무에서 빈번히 사용된다. 기준인 테이블을 지정해서 LEFT, RIGHT를 적어준다.

예) SELECT t.title_id, qty, title FROM titles t LEFT OUTER JOIN sales s ON t.title_id = s.title_id (T-SQL 문법 SELECT t.title_id, qty, title FROM titles t, sales s WHERE t.title_id *= s.title_id )

ANSI 문법과 T-SQL 문법의 차이  팔린 적이 없는 책만 보고싶다면…? SELECT t.title_id, qty, title FROM titles t LEFT OUTER JOIN sales s ON t.title_id = s.title_id WHERE qty IS NULL SELECT t.title_id, qty, title FROM titles t ,sales s WHERE t.title_id *= s.title_id AND qty IS NULL

 결과는 전혀 다르게 나온다. 이유는 WHERE절의 조건이 JOIN 의 조건인지 WHERE의 조건인지 명확하지 않기 때문이다. 따라서 ANSI 문법을 사용해야 한다. SELF JOIN) 자기 자신을 다시 조인하는 경우이다. SELECT * FROM titleauthor ORDER BY title_id, au_ord

작가가 두 명 이상인 책 목록을 보고 싶다고 할 경우, 다음과 같이 자신을 조인할 수 있다. SELECT t1.title_id, t1.au_id, t2.au_id FROM titleauthor t1, titleauthor t2 셀프 조인은 반드시 알리아스를 써야한다. WHERE t1.title_id = t2.title_id AND t1.au_id < t2.au_id ORDER BY t1.title_id

부 질의(subquery) SELECT 혹은 INSERT, UPDATE, DELETE 문 안에 들어가 있는 SELECT를 부질의라고 부른다. 쿼리를 중첩하면 많은 편리함을 제공한다. SELECT title FROM titles WHERE title_id IN (SELECT title_id FROM sales)

상관 부 질의(Correlated subquery) SELECT stor_id, title_id, qty FROM sales s WHERE qty = (SELECT MAX(qty) FROM sales s2 WHERE s2.stor_id = s.stor_id) ORDER BY stor_id, title_id 가급적 부 질의와 상관 부 질의는 사용하지 않는게 성능향상을 위해 좋다.

union 비정규화된 테이블을 연결시키기 위해 사용한다. - 열의 개수와 순서가 모든 쿼리에서 동일해야 한다. - 데이터 형식이 호환되어야 한다. select title_id, price from titles union select title_id, qty from sales • * UNION ALL : 중복 행 포함

INSERT 1. INSERT 문의 기본 문법 테이블 또는 뷰 에 데이터를 입력할 수 있다. SELECT의 부 질의를 사용하지 않는 이상은 하나의 INSERT문으로는 하나의 데이터행만 입력할 수 있다. 예) INSERT INTO Northwind.dbo.Shippers (CompanyName, Phone) VALUES ('Snowflake Shipping', '(503)555-7233')

2. IDENTITY와 DEFAULT IDENTITY는… .테이블에 하나의 IDENTITY컬럼만이 허용된다. .IDENTITY 컬럼은 NULL 값을 허용하지 않는다. .IDENTITY 컬럼은 정수, 숫자 혹은 소수형 데이터 형식으로 사용되어야 한다. .DENT_SEED : 초기값 반환, IDENT_INCR : 증가분 .세션 동안 마지막으로 삽입된 IDENTITY 값을 추축하기 위해 @@ IDENTITY 함수를 사용 예) CREATE TABLE class ( student_id int IDENTITY(100,5) NOT NULL, name VARCHAR(16) )

DEFAULT 제약은… INSERT문에 값이 지정되지 않았을 때 컬럼에 값을 삽입한다. .DEFAULT 제약은 오직 INSERT문에만 적용된다. .하나의 컬럼에는 오직 하나의 DEFAULT 제약만이 정의된다. .IDENTITY속성을 가진 컬럼이나 데이터 유형이 timestamp인 컬럼에는 사용될 수 없다. .함수를 사용하여 디폴트 값을 제공할 수 있다. 예) CREATE TABLE t1 ( id int IDENTITY PRIMARY KEY , name char(20) NOT NULL , city char(10) DEFAULT '서울' , photo image NULL)

4. INSERT…SELECT문 이미 존재하는 테이블에 여러 행의 새로운 데이터를 입력하고자 할 때 INSERT…SELECT 한 테이블에 있는 데이터를 가져와서 새로운 테이블 에 입력하고자 할 때  SELECT…INTO 예1) VALUES 대신에 SELECT 문을 넣는 것이라 생 각 괄호를 넣으면 에러가 발생하므로 주의. INSERT t2 SELECT name,city,photo FROM t1 GO 예2) SELECT name,city,photo TO t2 FROM t1

DELETE 1. DELETE 문의 기본 문법 예) DELETE t1 WHERE id = 3 2. 트랜잭션에 대한 기초 개념 예) BEGIN TRAN DELETE t1 SELECT * FROM t1 ROLLBACK TRAN -- OR COMMIT TRAN

3. 다른 테이블을 기반으로 지우기 예) BEGIN TRAN DELETE t2 FROM t1 JOIN t2 ON t1.id = t2.id SELECT * FROM t1 SELECT * FROM t2 COMMIT TRAN --ROLLBACK TRAN

4. TRUNCATE TABLE WHERE절 없이 모두 지울 때 사용 DELETE 트리거가 없는 테이블에서만 수행 예) begin tran truncate table test select * from test rollback

UPDATE 1. UPDATE 문의 기본 문법 예) UPDATE authors SET authors.au_fname = 'Annie' WHERE au_fname = 'Anne' 2. 다른 테이블을 기반으로 수정하기 예) UPDATE titles SET ytd_sales = titles.ytd_sales + sales.qty FROM titles, sales WHERE titles.title_id = sales.title_id AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

한 UPDATE문은 한 행에 한 번만 UPDATE할 수 있다! (앞의 예제는 오류 없이 실행되지만 각 제목은 한 번의 영업에서만 업데이트 된다. 이는 하나의 UPDATE 문이 같은 행을 두 번 업데이트 할 수 없기 때문이다. 따라서 다음과 같이 고쳐야 한다.) UPDATE titles SET ytd_sales = (SELECT SUM(qty) FROM sales WHERE sales.title_id = titles.title_id AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales)) FROM titles, sales

설명 방법들 영역(domain) 특정한 범위의 값만이 와야 한다. CHECK, DEFAULT 실체, 개체(entity) 행의 존재는 고유해야 한다. PRIMARY KEY, UNIQUE 참조(referential) 다른 속성과의 관계를 위반하지 않아야 한다. FOREIGN KEY • DATA INTEGRITY 무결성 : 유효한 데이터만이 데이터베이스에 저장될 수 있도록 지켜주는 것 - 무엇을 지켜주는가(종류)

설명 방법들 절차적 방법 먼저 정의하고, 다시 바인딩하는 방법으로 절차를 거쳐 정의하고 사용한다. 재활용이 가능하다. CREATE INDEX, CREATE RULE, CREATE DEFAULT 서술적 방법 테이블을 서술할 때 함께 서술한다. 재활용이 불가능하지만, 문서화 하기에 매우 쉽고, 읽기 좋다. CHECK, DEFAULT, PRIMARY KEY, FOERIGN KEY - 어떻게 지켜주는가(구현)

* 제약 사용하기 제약을 생성할 때 SQL Server는 시스템이 정하는 복잡한 이름을 붙이기 때문에, 제약에 대한 이름은 지정하는 것이 좋다. (수정시 용이) 컬럼 레벨 제약–컬럼 하나에 대한 제약 테이블 레벨 제약–다중 컬럼에 대해 제약

MS-SQL Implementation 강의 노트