가자미의 개발이야기

[데이터베이스] sql 기초 총정리! 본문

Computer Science/데이터베이스

[데이터베이스] sql 기초 총정리!

가자미 2021. 2. 17. 20:46

1. root 계정으로 데이터베이스 생성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
DROP DATABASE IF EXISTS madang;
DROP USER IF EXISTS madang@localhost;
create database madang;
create user madang@localhost identified WITH mysql_native_password by 'madang';
grant all privileges on madang.* to madang@localhost with grant option;
commit;
 
/* madang DB 자료 생성 */
USE madang;
CREATE TABLE Book(
 bookid        INTEGER PRIMARY KEY,
 bookname    varchar(40),
 publisher    varchar(40),
 price        integer
);
CREATE TABLE Customer(
 custid        INTEGER PRIMARY KEY,
 name        VARCHAR(40),
 address    VARCHAR(50),
 phone        VARCHAR(20)
 );
CREATE TABLE Orders (
 orderid    INTEGER PRIMARY KEY,
 custid        INTEGER,
 bookid        INTEGER,
 saleprice    INTEGER,
 orderdate    DATE,
 FOREIGN KEY (custid) REFERENCES Customer(custid),
 FOREIGN KEY (bookid) REFERENCES Book(bookid)
);
INSERT INTO BOOK VALUES(1, '축구의 역사', '굿스포츠', 7000);
INSERT INTO BOOK VALUES(2, '축구하는 여자', '나무수', 13000);
INSERT INTO BOOK VALUES(3, '축구의 이해', '대한미디어', 22000);
INSERT INTO BOOK VALUES(4, '골프 바이블', '대한미디어', 35000);
INSERT INTO BOOK VALUES(5, '피겨 교본', '굿스포츠', 8000);
INSERT INTO BOOK VALUES(6, '역도 단계별기술', '굿스포츠', 6000);
INSERT INTO BOOK VALUES(7, '야구의 추억', '이상미디어', 20000);
INSERT INTO BOOK VALUES(8, '야구를 부탁해', '이상미디어', 13000);
INSERT INTO BOOK VALUES(9, '올림픽 이야기', '삼성당', 7500);
INSERT INTO BOOK VALUES(10, 'Olympic Champions', 'Pearson', 13000);
INSERT INTO Customer VALUES(1, '박지성', '영국 맨체스터', '000-5000-0001');
INSERT INTO Customer VALUES(2, '김연아', '대한민국 서울', '000-6000-0001');
INSERT INTO Customer VALUES(3, '장미란', '대한민국 강원도', '000-7000-0001');
INSERT INTO Customer VALUES(4, '추신수', '미국 클리블랜드', '000-8000-0001');
INSERT INTO Customer VALUES(5, '박세리', '대한민국 대전', NULL);
INSERT INTO Orders VALUES(1, 1, 1, 6000, STR_TO_DATE('2014-07-01','%Y-%m-%d'));
INSERT INTO Orders VALUES(2, 1, 3, 21000, STR_TO_DATE('2014-07-03','%Y-%m-%d'));
INSERT INTO Orders VALUES(3, 2, 5, 8000, STR_TO_DATE('2014-07-03','%Y-%m-%d'));
INSERT INTO Orders VALUES(4, 3, 6, 6000, STR_TO_DATE('2014-07-06','%Y-%m-%d'));
INSERT INTO Orders VALUES(5, 4, 7, 20000, STR_TO_DATE('2014-07-05','%Y-%m-%d'));
INSERT INTO Orders VALUES(6, 1, 2, 12000, STR_TO_DATE('2014-07-07','%Y-%m-%d'));
INSERT INTO Orders VALUES(7, 4, 8, 13000, STR_TO_DATE('2014-07-08','%Y-%m-%d'));
INSERT INTO Orders VALUES(8, 3, 10, 12000, STR_TO_DATE('2014-07-08','%Y-%m-%d'));
INSERT INTO Orders VALUES(9, 2, 10, 7000, STR_TO_DATE('2014-07-09','%Y-%m-%d'));
INSERT INTO Orders VALUES(10, 3, 8, 13000, STR_TO_DATE('2014-07-10','%Y-%m-%d'));
 
CREATE TABLE Imported_Book(
 bookid        INTEGER,
 booknmae    VARCHAR(40),
 publisher    VARCHAR(40),
 price        INTEGER
 );
 
INSERT INTO Imported_Book VALUES(21, 'Zen Golf', 'Pearson', 12000);
INSERT INTO Imported_Book VALUES(22, 'Soccer Skills', 'Human Kinetics', 15000);
 cs

-root 계정으로 madang 데이터베이스와 madang 사용자 계정 생성.

2. 데이터 조작어 - 검색

요약

1
2
3
4
5
6
7
8
9
SELECT [ALL | DISTINCT] 속성이름들
FROM     테이블 이름들
[WHERE    검색조건]
[GROUP BY    속성이름]
[HAVING    검색조건들]
[ORDERED BY 속성이름[ASC | DESC]]
----------------------------------------
[] : 대괄호 안의 예약어는 선택적으로 사용
 | : 선택 가능한 문법 중 하나를 
 cs

더 상세한 버전은

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT  [ALL | DISTINCT] [테이블이름.]
        {*|속성이름[[AS] 속성이름별칭]}
FROM     {테이블 이름들 [AS 테이블이름별칭]}
        [INNER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN
        {테이블이름 [ON 검색조건]}
        | FULL [OUTER] JOIN {테이블이름}]]
[WHERE    검색조건]
[GROUP BY    속성이름]
[HAVING    검색조건들]
[질의 UNION 질의 | 질의 UNION ALL 질의]
[ORDERED BY 속성이름[ASC | DESC]]
----------------------------------------
[] : 대괄호 안의 예약어는 선택적으로 사용
{} : 중괄호 안의 예약어는 반드시 사용.
 | : 선택 가능한 문법 중 하나를 사용
 cs

SELECT

1
2
SELECT publisher
FROM Book
 cs

 

-중복을 포함해서 조회한다.

-DISTINCT를 publisher 앞에 붙여주면 중복을 제거한다.

-*는 모든 값을 포함하는 의미를 가진다.

 

 

WHERE 조건

WHERE의 연산자

술어 연산자 사용 예
비교 =,<,> 등... price <= 12000
범위 BETWEEN price BETWEEN 10000 AND 20000
집합 IN, NOT IN price IN (10000,20000,30000)
패턴 LIKE bookname LIKE '축구의 역사'
NULL IS NULL, IS NOT NULL price IS NULL
복합조건 AND, OR, NOT (price < 200000) AND (bookname LIKE '축구의 역사')

LIKE과 주로 같이 쓰이는 와일드 문자

와일드 문자 의미 사용 예
+ 문자열을 연결 '골프'+'바이블' : '골프 바이블'
% 0개 이상의 문자열과 일치 '%축구%' : 축구를 포함하는 문자열
[ ] 1개의 문자와 일치 '[0-5]%' : 0-5 사이 숫자로 시작하는 문자열
[^] 1개의 문자와 불일치 '[^0-5]%' : 0-5 사이 문자로 시작하지 않는 문자열
_ 특정 위치의 1개의 문자와 일치 '_구%' : 두번째 위치에 '구'가 들어가는 문자열

 

1
2
3
SELECT Bookname
FROM Book
WHERE Bookname LIKE '%축구%';
 cs

 

ORDER BY 조건에 따라 정렬

1
2
3
SELECT *
FROM Book
ORDER BY price DESC, publisher ASC;

-도서를 가격의 내림차순으로 검색. 만약 가격이 같다면 출판사의 오름차순으로 출력.

 

GROUP BY 와 집계함수

집계 함수 문법 사용 예
SUM SUM([ALL | DISTINCT] 속성이름) SUM(price)
AVG AVG([ALL | DISTINCT] 속성이름) AVG(price)
COUNT COUNT({[[ALL | DISTINCT] 속성이름] | *}) COUNT(*)
MAX MAX([ALL | DISTINCT] 속성이름) MAX(price)
MIN MIN([ALL | DISTINCT] 속성이름) MIN(price)

집계함수 사용 예시

1
2
3
4
5
SELECT SUM(saleprice) AS 총합,
        AVG(saleprice) AS 평균,
        MIN(saleprice) AS 최소액,
        MAX(saleprice) AS 최대액
FROM orders;
 cs

 

 

 

 

집계함수와 group by 사용 예시

1
2
3
SELECT        custid, COUNT(*) AS 도서수량, SUM(saleprice) AS 총액
FROM        Orders
GROUP BY    custid;
 cs

 

HAVING : GROUP BY 절의 결과에 나타나는 그룹을 제한.

-반드시 GROUP BY 절과 같이

-WHERE 절 뒤에 등장

-검색조건에 집계함수가 등장해야

1
2
3
4
5
SELECT        custid, COUNT(*) AS 도서수량
FROM        Orders
WHERE        saleprice>=8000
GROUP BY    custid
HAVING        count(*)>=2;
 cs

조인

1
2
SELECT        *
FROM        Orders, Customer;
 cs

-연산결과가 카티전 프로덕트가 된다.

 

1
2
3
4
SELECT        *
FROM        Customer, Orders
WHERE        Customer.custid=Orders.custid
ORDER BY    Customer.custid;
 cs

-고객 주문에 관한 데이터를 고객별로 정렬

 

1
2
3
4
SELECT        Customer.name, saleprice
FROM        Customer LEFT OUTER JOIN Orders
                        ON Customer.custid=Orders.custid;
 
 cs

-외부 조인

명령 문법 설명
일반 조인 SELECT    <속성들>
FROM      테이블 1, 테이블 2
WHERE     <조인조건> AND <검색조건>		 SQL 문에서는 주로 동등조인을 사용.
두 가지 문법 중 하나를 사용할 수 있다.
SELECT    <속성들>
FROM      테이블 1 INNER JOIN 테이블 2 ON <조인조건>
WHERE     <검색조건>
외부조인 SELECT    <속성들>
FROM      테이블 1 {LEFT | RIGHT | FULL [OUTER]}
              JOIN 테이블 2 ON <조인조건>
WHERE     <검색조건>		 외부조인은 FROM 절에 조인 종류를 적고 ON을 이용하여 조인조건을 명시한다.

 

 

부속질의 SQL문 내에 다른 SQL

1
2
3
4
5
6
7
SELECT        name
FROM        Customer
WHERE        custid IN(SELECT    custid
                      FROM        Orders
                      WHERE        bookid IN(SELECT    bookid
                                          FROM        Book
                                          WHERE        publisher='대한미디어'));
 cs

-대한미디어에서 출판한 도서를 구매한 고객의 이름 찾기

-대한미디어의 책을 구하고/해당 책 id를 주문한 고객 id를 찾고/고객테이블에서 해당 고객 id의 이름 속성 반환

 

1
2
3
4
5
SELECT        b1.bookname
FROM        Book b1#별칭부여
WHERE        b1.price > (SELECT        AVG(b2.price)
                        FROM        Book b2#별칭부여
                        WHERE        b2.publisher=b1.publisher);
  

-출판사별로 평균가보다 비싼 도서 이름 찾기

-별칭 사용에 주목

집합 연산

1
2
3
4
5
6
7
8
9
SELECT        name
#대한민국에 거주하는 고객이름
FROM        Customer
WHERE        address LIKE '대한민국%'
UNION #합집합(중복불포함)
#도서 구매를 한 고객 이름
SELECT        name
FROM        Customer
WHERE        custid IN (SELECT custid FROM Orders);
  

-UNION 합집합은 중복 미포함

-UNION ALL은 중복 포함

EXISTS 주문이 있는 고객을 알고싶다.

1
2
3
4
5
SELECT        name, address
FROM        Customer cs
WHERE        EXISTS (SELECT    *
                    FROM    Orders od
                    WHERE    cs.custid=od.custid);
 cs

-부속질의문의 어떤 행이 조건에 만족하면 참.

3. 데이터 정의어

CREATE 문

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE 테이블이름
    (    {속성이름 데이터타입
            [NULL | NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건]
        }
            [PRIMARY KEY 속성이름들]
            [FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]
                    [ON DELETE {CASCADE | SET NULL}]
    )
=========================================================================
UNIQUE : 중복값이 있으면 안됨
DEFAULT : 값이 입력되지 않은 경우 입력되는 값
CHECK : 조건을 설정
ON DELETE : 참조되는 테이블의 투플이 삭제될 때 취할 수 있는 동작
CASCADE : 연쇄 삭제
SET NULL : NULL 값 할당
NO ACTION : 
 cs
데이터 타입 설명 ANSI SQL 표준 타입
INTEGER
INT		 4바이트 정수형 저장 INTEGER, INT,
SMALLINT
NUMBER(m, d)
DECIMAL(m, d)		 전체 자릿수 m, 소수점이하 자릿수 d를 가진 숫자형을 저장한다. DECIMAL(p, s)
NUMERIC[(p, s)]
CHAR(n) 문자형 고정길이, 문자 저장후 남은 공간을 여백 CHARACTER(n)
CHAR(n)
VARCHAR(n) 문자형 가변길이를 저장 CHARACTER VARYING(n)
CHAR VARYING(n)
DATE 날짜형, 연도 등 저장  

 

ALTER 문

1
2
3
4
5
6
7
8
9
ALTER TABLE 테이블이름
    [ADD 속성이름 데이터타입]
    [DROP COLUMN 속성이름]
    [ALTER COLUMN 속성이름 데이터타입]
    [ALTER COLUMN 속성이름 [NULL | NOT NULL]]
    [ADD PRIMARY KEY(속성이름)]
    [[ADD | DROP] 제약이름]
==============================================
테이블 속성이나 속성의 제약 변경, 기본키나 외래키를 변경
  

 

DROP 문

DROP TABLE 테이블이름

 

3. 데이터 정의어

INSERT 문

1
2
3
4
INSERT INTO 테이블이름[(속성리스트)]
       VALUES (값리스트);
====================================
새로운 투플을 삽입하는 명령
 cs

-다량 삽입

1
2
3
INSERT INTO Book(bookid, bookname, price, publisher)
       SELECT    bookid, bookname,price, publisher
       FROM        Imported_book;
 cs

UPDATE 문

1
2
3
UPDATE        테이블이름
SET            속성이름 1=값 1[, 속성이름 2= 값 2,...]
[WHERE        <검색조건>];
  

특정 속성값 변경

 

DELETE 문

DELETE FROM 테이블이름

[WHERE 검색조건];

 

'Computer Science > 데이터베이스' 카테고리의 다른 글

[데이터베이스] MySQL의 my.ini 수정 권한이 없다고 나올 때 해결방법.  (0) 2021.02.23
[데이터베이스] SQL 내장함수  (0) 2021.02.23
[데이터베이스] 관계대수  (0) 2021.02.16
[데이터베이스] 관계 데이터 모델  (0) 2021.02.16
[데이터베이스]데이터베이스 시스템의 개념  (0) 2021.02.09
'Computer Science/데이터베이스' Related Articles more