보건의료DB (2) 김성환 (bios@live.co.kr)

차례 DATABASE의 정의와 태동 DATABASE의 역사 DATA와 정보 DATA와 지식과의 관계 DATA의 종류 SQL 이란? 데이터베이스 객체란? 데이터베이스 객체의 종류 DATABASE 기출 문제 예시 DATABASE (SQL 기본퀴리 구조) SQL 문장의 종류 ACID? DATABASE Table이란? 스키마 다이어그램 SELECT 와 FROM 절 WHERE 절과 filtering 변수의 사용 ORDER BY 절 SQL JOIN SQL Subqueries CREATE TABLE SQL Inserting, Updating, and Deleting SQL Constraints 실습 INDEX 란? MS EXPRESS의 설치

SELECT 와 FROM 절 select title from hr.Employees; SELECT country SELECT DISTINCT country SELECT empid, lastname FROM HR.Employees ORDER BY empid; ORDER BY 1; SELECT empid, firstname + ' ' + lastname SELECT empid, firstname + ' ' + lastname AS fullname

WHERE 절과 filtering(1) SELECT empid, firstname, lastname, country, region, city FROM HR.Employees WHERE country = 'USA';

WHERE 절과 filtering(2) --------------------------------------------------------------------- -- Predicates, Three-Valued-Logic and Search Arguments -- content of Employees table SELECT empid, firstname, lastname, country, region, city FROM HR.Employees -- employees from the United States WHERE country = N'USA'; -- employees from Washington State WHERE region = N'WA'; -- employees that are not from Washington State WHERE region <> N'WA'; -- employees that are not from Washington State, resolving the NULL problem WHERE region <> N'WA' OR region IS NULL; -- orders shipped on a given date DECLARE @dt AS DATETIME = '20070212'; -- incorrect treatment of NULLs SELECT orderid, orderdate, empid FROM Sales.Orders WHERE shippeddate = @dt; -- correct treatment and also a SARG WHERE shippeddate = @dt OR (shippeddate IS NULL AND @dt IS NULL);

WHERE 절과 filtering(3) --------------------------------------------------------------------- -- Filtering Character Data -- regular character string SELECT empid, firstname, lastname FROM HR.Employees WHERE lastname = 'Davis'; -- Unicode character string WHERE lastname = N'Davis'; -- employees whose last name starts with the letter D. WHERE lastname LIKE N'D%'; -- Filtering Date and Time Data -- language-dependent literal SELECT orderid, orderdate, empid, custid FROM Sales.Orders WHERE orderdate = '02/12/07'; -- language-neutral literal WHERE orderdate = '20070212'; -- not SARG WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2; -- SARG WHERE orderdate >= '20070201' AND orderdate < '20070301';

WHERE 절과 filtering(4) --------------------------------------------------------------------- -- Sorting Data -- query with no ORDER BY doesn't guarantee presentation ordering SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth FROM HR.Employees WHERE country = N'USA' AND region = N'WA'; -- Simple ORDER BY example WHERE country = N'USA' AND region = N'WA' ORDER BY city; -- use descending order ORDER BY city DESC; -- order by multiple columns ORDER BY city, empid; -- order by ordinals (bad practice) ORDER BY 4, 1; -- change SELECT list but forget to change ordinals in ORDER BY SELECT empid, city, firstname, lastname, MONTH(birthdate) AS birthmonth -- order by elements not in SELECT SELECT empid, city ORDER BY birthdate;

변수의 사용(1) YEAR SELECT custid, YEAR(orderdate) FROM Sales.Orders ORDER BY 1, 2; SELECT DISTINCT custid, YEAR(orderdate) AS orderyear FROM Sales.Orders;

변수의 사용(2) MAX SELECT custid, orderid FROM Sales.Orders GROUP BY custid; SELECT custid, MAX(orderid) AS maxorderid FROM Sales.Orders GROUP BY custid; ERROR발생!!!

변수의 사용(3) SUM SELECT shipperid, SUM(freight) AS totalfreight FROM Sales.Orders WHERE freight > 20000.00 GROUP BY shipperid; SELECT shipperid, SUM(freight) AS totalfreight FROM Sales.Orders GROUP BY shipperid HAVING totalfreight > 20000.00; ERROR발생!!! SELECT shipperid, SUM(freight) AS totalfreight FROM Sales.Orders GROUP BY shipperid HAVING SUM(freight) > 20000.00;

ORDER BY 절(1)

ORDER BY 절(2) --------------------------------------------------------------------- -- Sorting Data -- query with no ORDER BY doesn't guarantee presentation ordering SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth FROM HR.Employees WHERE country = N'USA' AND region = N'WA'; -- Simple ORDER BY example WHERE country = N'USA' AND region = N'WA' ORDER BY city; -- use descending order ORDER BY city DESC; -- order by multiple columns ORDER BY city, empid; -- order by ordinals (bad practice) ORDER BY 4, 1; -- change SELECT list but forget to change ordinals in ORDER BY SELECT empid, city, firstname, lastname, MONTH(birthdate) AS birthmonth -- order by elements not in SELECT SELECT empid, city ORDER BY birthdate;

ORDER BY 절과 TOP(4) DESC PERCENT DECLARE

(3)-4 ORDER BY 절과 TOP --------------------------------------------------------------------- -- Filtering Data with TOP -- return the three most recent orders SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC; -- can use percent SELECT TOP (1) PERCENT orderid, orderdate, custid, empid GO -- can use expression, like parameter or variable, as input DECLARE @n AS BIGINT = 5; SELECT TOP (@n) orderid, orderdate, custid, empid -- no ORDER BY, ordering is arbitrary FROM Sales.Orders; -- be explicit about arbitrary ordering ORDER BY (SELECT NULL); -- non-deterministic ordering even with ORDER BY since ordering isn't unique

SQL JOIN(1) Cross Join Database 는 여러개의 Table로 구성되어 있고 하나의 데어터베이스 또는 여러 데이터베이스에 분산되어 있어, 각각의 TABLE을 join 하여 사용한다. 여러개의 Table로 데이터를 분할하는 작업을 normalized 한다고 하며, 그 반대의 경우를 De-norlmalized 한다고 명명한다. normalization은 database에 동일 필드에 중복입력을 예방 할 수 있으며, table에 속하여있는 field의 무결성을 유지시키는데 도움이 된다. 단점은 하나의 Table에 여러개의 field를 두어 사용할수 있으나, 과거에는 computing power가 부족하여, resource 절약에 촛점이 맞추어져서 많이 사용되었다. 관계형데이터베이스는 많은 부분 JOIN operation이 필요하며, Bigdata operation에서는 bulk data를 바로 사용하는 경우가 많다. JOIN Operation은 database에 많은 부담을 줄수 있으며, 사용시 매우 주의가 필요하다. 예) 10,000 X 10,000 = 10,000,000,000  10억회!!!!

SQL JOIN(2) Inner Join 일반적인 join Operation의 형태이다.

SQL JOIN(3) Left Outer Join SELECT S.companyname AS supplier, S.country, P.productid, P.productname, P.unitprice FROM Production.Suppliers AS S LEFT OUTER JOIN Production.Products AS P ON S.supplierid = P.supplierid WHERE S.country = N'Japan'; Here’s the output of this query. supplier country productid productname unitprice --------------- -------- ---------- -------------- ---------- Supplier QOVFD Japan 9 Product AOZBW 97.00 Supplier QOVFD Japan 10 Product YHXGE 31.00 Supplier QOVFD Japan 74 Product BKAZJ 10.00 Supplier QWUSF Japan 13 Product POXFU 6.00 Supplier QWUSF Japan 14 Product PWCJB 23.25 Supplier QWUSF Japan 15 Product KSZOI 15.50 Supplier XYZ Japan NULL NULL NULL

SQL JOIN(4) Right Outer Join SELECT S.companyname AS supplier, S.country, P.productid, P.productname, P.unitprice FROM Production.Suppliers AS S LEFT OUTER JOIN Production.Products AS P ON S.supplierid = P.supplierid AND S.country = N'Japan'; Observe what’s different in the result (shown here in abbreviated form) and see if you can explain in your own words what the query returns now. supplier country productid productname unitprice --------------- -------- ---------- -------------- ---------- Supplier SWRXU UK NULL NULL NULL Supplier VHQZD USA NULL NULL NULL Supplier STUAZ USA NULL NULL NULL Supplier QOVFD Japan 9 Product AOZBW 97.00 Supplier QOVFD Japan 10 Product YHXGE 31.00 Supplier QOVFD Japan 74 Product BKAZJ 10.00 Supplier EQPNC Spain NULL NULL NULL ...

SQL JOIN(5) Full Outer Join

SQL Subqueries (1) Self-Contained Subqueries 하나의 SQL문 안에 포함된 또 다른 SQL문 SELECT productid, productname, unitprice FROM Production.Products WHERE unitprice = (SELECT MIN(unitprice) FROM Production.Products); productid productname unitprice ---------- -------------- ---------- 33 Product ASTMN 2.50

SQL Subqueries (2) Self-Contained Subqueries 하나의 SQL문 안에 포함된 또 다른 SQL문 1) SELECT MIN(unitprice) FROM Production.Products; 2) SELECT productid, productname, unitprice FROM Production.Products WHERE unitprice = 2.50; 결과 : 2.50 productid productname unitprice ---------- -------------- ---------- 33 Product ASTMN 2.50

SQL Subqueries (3) EXISTS의 활용 ( True/False 값만 가져오므로 빠르다. 검색시 한개를 만나면 나머지는 검색 중지) 하나의 SQL문 안에 포함된 또 다른 SQL문 SELECT custid, companyname FROM Sales.Customers AS C WHERE NOT EXISTS (SELECT * FROM Sales.Orders AS O WHERE O.custid = C.custid AND O.orderdate = '20070212'); custid companyname ------- --------------- 72 Customer AHPOP 58 Customer AHXHT 25 Customer AZJED 18 Customer BSVAR 91 Customer CCFIZ ...

SQL Subqueries (4) IN의 활용 (반환되는 값이 멀티값 일때는 IN, 단일값일때는 ‘=‘) 하나의 SQL문 안에 포함된 또 다른 SQL문 SELECT productid, productname, unitprice FROM Production.Products WHERE supplierid IN (SELECT supplierid FROM Production.Suppliers WHERE country = N'Japan'); productid productname unitprice ---------- -------------- ----- 9 Product AOZBW 97.00 10 Product YHXGE 31.00 74 Product BKAZJ 10.00 13 Product POXFU 6.00 14 Product PWCJB 23.25 15 Product KSZOI 15.50

Create TABLE(1) Insert를 위한 Table 생성 USE TSQL2012; IF OBJECT_ID('Sales.MyOrders') IS NOT NULL DROP TABLE Sales.MyOrders; GO CREATE TABLE Sales.MyOrders (orderid INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_MyOrders_orderid PRIMARY KEY, custid INT NOT NULL, empid INT NOT NULL, orderdate DATE NOT NULL CONSTRAINT DFT_MyOrders_orderdate DEFAULT (CAST(SYSDATETIME() AS DATE)), shipcountry NVARCHAR(15) NOT NULL, freight MONEY NOT NULL);

Create TABLE(2) 기존에 table이 있는지 여부확인 후 존재하면 table 삭제 USE TSQL2012; --TSQL2012 데이터베이스 사용 IF OBJECT_ID('Sales.MyOrders') IS NOT NULL DROP TABLE Sales.MyOrders; -- 존재하면 삭제 GO –-실행명령 SQL 문장 아님

Create TABLE(3) Insert를 위한 Table 생성 CREATE TABLE Sales.MyOrders (orderid INT NOT NULL IDENTITY(1, 1) –-1 부터 시작 후 1씩 증가 CONSTRAINT PK_MyOrders_orderid PRIMARY KEY, custid INT NOT NULL, -- 반드시 값을 넣으라는 문장 empid INT NOT NULL, orderdate DATE NOT NULL CONSTRAINT DFT_MyOrders_orderdate DEFAULT (CAST(SYSDATETIME() AS DATE)), --현재시간 자동입력 shipcountry NVARCHAR(15) NOT NULL, freight MONEY NOT NULL);

SQL Inserting, Updating, and Deleting(1) INSERT VALUES Table에 하나의 row를 입력하기 위한 명령 INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) VALUES(2, 19, '20120620', N'USA', 30.00);

SQL Inserting, Updating, and Deleting(2) INSERT VALUES Table에 여러개의 row를 입력하기 위한 명령 INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) VALUES (2, 11, '20120620', N'USA', 50.00),(5, 13, '20120620', N'USA', 40.00),(7, 17, '20120620', N'USA', 45.00);

SQL Inserting, Updating, and Deleting(3) UPDATE 문장 Table에 존재하는 row를 수정하기 위한 명령 UPDATE <target table> SET <col 1> = <expression 1>, <col 2> = <expression 2>, ..., <col n> = <expression n> WHERE <predicate>; 변경전 data 확인 SELECT * FROM Sales.MyOrderDetails WHERE orderid = 10251;

SQL Inserting, Updating, and Deleting(4) UPDATE 문장 변경 UPDATE Sales.MyOrderDetails SET discount += 0.05 WHERE orderid = 10251;

SQL Inserting, Updating, and Deleting(5) UPDATE 문장 확인 select * from Sales.MyOrderDetails where orderid = 10251

SQL Inserting, Updating, and Deleting(6) Delete Table에 하나 또는 그 이상의 row를 삭제하기 위한 명령 실습 준비 사항 IF OBJECT_ID('Sales.MyOrderDetails', 'U') IS NOT NULL DROP TABLE Sales.MyOrderDetails; IF OBJECT_ID('Sales.MyOrders', 'U') IS NOT NULL DROP TABLE Sales.MyOrders; IF OBJECT_ID('Sales.MyCustomers', 'U') IS NOT NULL DROP TABLE Sales.MyCustomers; SELECT * INTO Sales.MyCustomers FROM Sales.Customers; ALTER TABLE Sales.MyCustomers ADD CONSTRAINT PK_MyCustomers PRIMARY KEY(custid); SELECT * INTO Sales.MyOrders FROM Sales.Orders; ALTER TABLE Sales.MyOrders ADD CONSTRAINT PK_MyOrders PRIMARY KEY(orderid); SELECT * INTO Sales.MyOrderDetails FROM Sales.OrderDetails; ALTER TABLE Sales.MyOrderDetails ADD CONSTRAINT PK_MyOrderDetails PRIMARY KEY(orderid, productid);

SQL Inserting, Updating, and Deleting(7) Delete Table에 하나 또는 그 이상의 row를 삭제하기 위한 명령 DELETE FROM <table> WHERE <predicate>; DELETE FROM Sales.MyOrderDetails WHERE productid = 11;

SQL Inserting, Updating, and Deleting(8) Truncate Table 구조는 유지하고 내용을 삭제하기 위한 명령 (DML 처리하지 않아 효과 좋다.) TRUNCATE TABLE Sales.MyOrderDetails; USE TSQL2012; GO CREATE TABLE Production.CategoriesTest ( categoryid INT NOT NULL IDENTITY );

SQL Constraints 실습 (1) Truncate Table 구조는 유지하고 내용을 삭제하기 위한 명령 (DML 처리하지 않아 효과 좋다.) 실습TABLE 생성 CREATE TABLE Production.CategoriesTest ( categoryid INT NOT NULL IDENTITY); 원본 TABLE과 같게 컬럼 변경 ALTER TABLE Production.CategoriesTest ADD categoryname NVARCHAR(15) NOT NULL; GO ADD description NVARCHAR(200) NOT NULL;

SQL Constraints 실습 (2) Truncate Table 구조는 유지하고 내용을 삭제하기 위한 명령 (DML 처리하지 않아 효과 좋다.) TABLE에 원본 데이터 복사하여 입력 INSERT Production.CategoriesTest (categoryid, categoryname, description) SELECT categoryid, categoryname, description FROM Production.Categories; GO ******* Error 발생!!! SET IDENTITY ON 설정 후 입력 SET IDENTITY_INSERT Production.CategoriesTest ON; INSERT Production.CategoriesTest (categoryid, categoryname, description) SELECT categoryid, categoryname, description FROM Production.Categories; GO SET IDENTITY_INSERT Production.CategoriesTest OFF; GO

SQL Constraints 실습 (3) NULL column 실습 IF OBJECT_ID('Production.CategoriesTest','U') IS NOT NULL DROP TABLE Production.CategoriesTest; GO TABLE 생성 CREATE TABLE Production.CategoriesTest ( categoryid INT NOT NULL IDENTITY, categoryname NVARCHAR(15) NOT NULL, description NVARCHAR(200) NOT NULL, CONSTRAINT PK_CategoriesTest PRIMARY KEY(categoryid) ); 원본 데이터 복사 후 입력 SET IDENTITY_INSERT Production.CategoriesTest ON; INSERT Production.CategoriesTest (categoryid, categoryname, description) SELECT categoryid, categoryname, description FROM Production.Categories; SET IDENTITY_INSERT Production.CategoriesTest OFF; GO

SQL Constraints 실습 (4) Primary and Foreign Key 실습 SELECT productname FROM Production.Products WHERE productid = 1; SET IDENTITY_INSERT Production.Products ON; GO INSERT INTO Production.Products (productid, productname,supplierid,categoryid,unitprice,discontinued) VALUES (1, N'Product TEST', 1, 1, 18, 0); GO -- ERROR 발생!!!! SET IDENTITY_INSERT Production.Products OFF; 데이터입력 INSERT INTO Production.Products (productname,supplierid,categoryid,unitprice,discontinued) VALUES (N'Product TEST', 1, 1, 18, 0); GO -- Delete the row DELETE FROM Production.Products WHERE productname = N'Product TEST'; GO

SQL Constraints 실습 (5) Primary and Foreign Key 실습 INSERT INTO Production.Products (productname,supplierid,categoryid,unitprice,discontinued) VALUES (N'Product TEST', 1, 99, 18, 0); GO -- ERROR 발생!!!! --INSERT 문이 FOREIGN KEY 제약 조건 "FK_Products_Categories"과(와) 충돌했습니다. Constraint 제거 후 입력 (CategoriID 는 1 ~ 8 까지만 입력 되도록 제약을 두었다 ALTER TABLE Production.Products DROP CONSTRAINT FK_Products_Categories; GO -- invalid categoryid = 99; 입력 성공 INSERT INTO Production.Products (productname,supplierid,categoryid,unitprice,discontinued) VALUES (N'Product TEST', 1, 99, 18, 0);

SQL Constraints 실습 (6) foreign key constraint back in WITH CHECK 실습 ALTER TABLE Production.Products WITH CHECK ADD CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid) REFERENCES Production.Categories (categoryid); ERROR 발생!!! UPDATE Production.Products SET categoryid = 1 WHERE productname = N'Product TEST'; GO –- 1 ~ 8까지의 categoryid 만 허용되므로 ’99’ 값을 1로 바꾸어 준다. ALTER TABLE Production.Products WITH CHECK ADD CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid) REFERENCES Production.Categories (categoryid); GO

SQL Constraints 실습 (7) foreign key constraint back in WITH CHECK 실습 DELETE FROM Production.Products WHERE productname = N'Product TEST'; GO -- cleanup

MS Express의 설치

MS Express의 설치

MS Express의 설치

MS Express의 설치

MS Express의 설치

Q & A