MsSQL 공부 1

테이블 생성 및 Foreign Key 설정

 1 

 2 IF NOT EXISTS (

 3     SELECT name

 4         FROM sys.databases

 5         WHERE name = N'Test1DB'

 6 )

 7   CREATE DATABASE Test1DB;

 8 GO 

 9 USE Test1DB;

10 GO

11 CREATE TABLE buyer (

12     buyer_id     int         IDENTITY

13     PRIMARY KEY NONCLUSTERED,

14     buyer_name   varchar(20) NOT NULL

15 );

16 GO

17 CREATE TABLE product (

18     product_id   int         IDENTITY

19     PRIMARY KEY NONCLUSTERED,

20     product_name varchar(20) NOT NULL

21 );

22 GO

23 CREATE TABLE orders (

24   orders_id    int         IDENTITY

25     PRIMARY KEY NONCLUSTERED,

26     buyer_id     int         NOT NULL,

27     product_id   int         NOT NULL,

28     qty          int         NOT NULL

29 );

30 ALTER TABLE orders

31   ADD

32     FOREIGN KEY (buyer_id)

33       REFERENCES buyer;

34 ALTER TABLE orders

35   ADD

36     FOREIGN KEY (product_id)

37       REFERENCES product;

38 GO

39 --초기 데이터 삽입

40 INSERT INTO buyer VALUES ('한국유통');

41 INSERT INTO buyer VALUES ('대한물류');

42 SELECT * FROM buyer;

43 GO

44 INSERT INTO product VALUES ('수박');

45 INSERT INTO product VALUES ('배추');

46 INSERT INTO product VALUES ('고구마');

47 SELECT * FROM product;

48 GO

49 INSERT INTO orders VALUES (1,1,10);

50 INSERT INTO orders VALUES (1,2,100);

51 INSERT INTO orders VALUES (2,1,20);

52 INSERT INTO orders VALUES (2,2,200);

53 SELECT * FROM orders;

IF NOT EXISTS 

 - 같은 이름의 테이블 또는 컬럼이 있는지 없는지

   체크해주는 명령어

30 ALTER TABLE orders

31   ADD

32     FOREIGN KEY (buyer_id)

33       REFERENCES buyer;

 - orders 테이블에 Foreign Key 설정

---

Join의 종류

Cross Join  (두 테이블 모두 가져옴)

1 USE Test1DB;

2 GO

3 SELECT *

4   FROM buyer

5   CROSS JOIN orders;

Inner Join (조건을 만족하는 행만 가져옴)

1 SELECT b.buyer_id, b.buyer_name, o.orders_id, o.product_id, o.qty

2   FROM buyer AS b

3     INNER JOIN orders AS o

4       ON b.buyer_id = o.buyer_id;

Outer Join (조건을 만족하지 않는 행까지 가져옴)

1 SELECT p.product_id, p.product_name, o.orders_id, o.buyer_id, o.qty

2   FROM product AS p

3     LEFT OUTER JOIN orders AS o

4       ON p.product_id = o.product_id

5         AND p.product_name <> '수박';

Self Join (자기 자신의 테이블과 하는 조인)

1 SELECT a.SalesOrderID, a.SalesOrderDetailID AS 'a.SalesOrderDetailID',

2     b.SalesOrderDetailID AS 'b.SalesOrderDetailID'

3   FROM SalesLT.SalesOrderDetail a

4     INNER JOIN SalesLT.SalesOrderDetail b

5       ON a.SalesOrderID = b.SalesOrderID

6         AND a.SalesOrderDetailID < b.SalesOrderDetailID

7   ORDER BY a.SalesOrderID, a.SalesOrderDetailID, b.SalesOrderDetailID;

---

하위 쿼리

(이왕이면 조인으로 사용하는게 좋다)

단순 하위 쿼리

1 SELECT SalesOrderDetailID, UnitPriceDiscount, UnitPriceDiscount - (

2     SELECT AVG(UnitPriceDiscount) FROM SalesLT.SalesOrderDetail

3   ) AS 'UnitPriceDiscount의 평균값과의 차이'

4   FROM SalesLT.SalesOrderDetail;

상관 하위 쿼리

1 SELECT DISTINCT SalesOrderID, UnitPrice AS '최고 단가'

2   FROM SalesLT.SalesOrderDetail AS a

3   WHERE UnitPrice = (

4     SELECT MAX(UnitPrice)

5       FROM SalesLT.SalesOrderDetail AS b

6       WHERE a.SalesOrderID = b.SalesOrderID

7   );

EXISTS 와 NOT EXISTS

(한 행이라도 결과 집합을 돌려주는지 판단)

1 SELECT *

2   FROM Test1DB.dbo.product AS p

3   WHERE EXISTS (

4     SELECT 1

5       FROM Test1DB.dbo.orders AS o

6       WHERE p.product_id = o.product_id

7   );

or

1 SELECT *

2   FROM Test1DB.dbo.product AS p

3   WHERE NOT EXISTS (

4     SELECT 1

5       FROM Test1DB.dbo.orders AS o

6       WHERE p.product_id = o.product_id

7   );

---

유니온 (UNION)

(둘 이상의 Select문들의 결과 집합을 하라로 만들어주는 연산자)

1 SELECT FirstName + ' ' + LastName AS Name, 'Contact' AS source, EmailAddress

2   FROM AdventureWorks.Person.Contact

3 UNION

4 SELECT ReviewerName, 'ProductReview' AS source, EmailAddress

5   FROM AdventureWorks.Production.ProductReview;