<GROUP BY> **반드시 알아야 한다.
집계 함수를 써먹기 위해서 !
----------------------------------------------------
SELECT * FROM 테이블명 GROUP BY 열1 ,열2 ....
[실습] 샘플51 name 열을 그룹바이 후 name열만 표시
[실습2]샘플51 name열을 그룹바이 후, 아래와 같이
name열, name열의 각 수량 quantity열의 합계 표시
mysql> select name , count(name), SUM(quantity) from sample51 GROUP BY name;
----------------------------------------------------
[실습] 샘플 51 name열이 1인행을 선택한후 , name열을
그룹바이후 , name,count(name) 출력
[오류 코드]
select name, count(name) FROM sample51 where count(name) = 1 GROUP BY name;
[해결 코드]
mysql> select name, count(name) FROM sample51 GROUP BY name HAVING COUNT(name)=1;
HAVING은 WHERE랑 동일하다 단, WHERE은 그룹바이보다
먼저 실행 되지만, HAVING은 그룹바이 뒤에 실행 된다.
+------+-------------+
| name | count(name) |
+------+-------------+
| B | 1 |
| C | 1 |
+------+-------------+
----------------------------------------------------
[실습] 샘플 51 name 열을 n count(name)을 cn으로 표시후
cn이 1인 행을 선택하고 n을 그룹바이
n,cn열 표시
mysql> select name as n, count(name) as cn from sample51
GROUP BY name HAVING count(name)=1;
+------+----+
| n | cn |
+------+----+
| B | 1 |
| C | 1 |
+------+----+
----------------------------------------------------
[실습2] 샘플51 name열을 그룹바이후, no,name,quantity열 표시
mysql> select no,name,quantity from sample51 GROUP BY name;
+------+------+----------+
| no | name | quantity |
+------+------+----------+
| 1 | A | 1 |
| 3 | B | 10 |
| 4 | C | 3 |
| 5 | NULL | NULL |
+------+------+----------+
----------------------------------------------------
[실습3] 샘플51 name열을 그룹바이후, no의 최소값, name,quantity의 합계 표시
mysql> select min(no),sum(name),sum(quantity) from sample51 GROUP BY name;
+---------+-----------+---------------+
| min(no) | sum(name) | sum(quantity) |
+---------+-----------+---------------+
| 1 | 0 | 3 |
| 3 | 0 | 10 |
| 4 | 0 | 3 |
| 5 | NULL | NULL |
+---------+-----------+---------------+
----------------------------------------------------
[실습1] 샘플51 no ,quantity열을 그룹바이후, no,quantity열 표시
mysql> select no,quantity from sample51 GROUP BY no , quantity;
+------+----------+
| no | quantity |
+------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 10 |
| 4 | 3 |
| 5 | NULL |
+------+----------+
----------------------------------------------------
[실습2] 샘플51 QUANTITY, NO열을 그룹바이후 no,quantity 열 표시
mysql> select no,quantity from sample51 GROUP BY quantity,no;
+------+----------+
| no | quantity |
+------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 10 |
| 4 | 3 |
| 5 | NULL |
+------+----------+
----------------------------------------------------
[실습3] 샘플51 name열을 그룹바이후, name, name열의 수량, quantity열의 합계를 quantity열의 합계가 높은순부터 정렬하여 표시
mysql> select name, count(name), sum(quantity) from sample51
-> GROUP BY name
-> order by sum(quantity) DESC;
+------+-------------+---------------+
| name | count(name) | sum(quantity) |
+------+-------------+---------------+
| B | 1 | 10 |
| A | 2 | 3 |
| C | 1 | 3 |
| NULL | 0 | NULL |
+------+-------------+---------------+
----------------------------------------------------
[실습1] 샘플54 참조
mysql> select * from sample54;
+------+------+
| no | a |
+------+------+
| 1 | 100 |
| 2 | 900 |
| 3 | 20 |
| 4 | 80 |
+------+------+
[실습2] 샘플54의 a열 최소값 구하기
mysql> select min(a) from sample54;
+--------+
| min(a) |
+--------+
| 20 |
+--------+
[실습3] 샘플54의 a열중 최소값인 행 찾아 삭제
DELETE FROM sample54
-> WHERE a = (SELECT a FROM (SELECT MIN(a) AS a FROM sample54) AS t
#이런식으로 치환해서 삭제 해야 한다 .
mysql> select * from sample54;
+------+------+
| no | a |
+------+------+
| 1 | 100 |
| 2 | 900 |
+------+------+
[실습4] 샘플54 참조
----------------------------------------------------
<서브 쿼리 >
mysql> SELECT
-> (SELECT COUNT(*) FROM sample51) AS sq1,
-> (SELECT COUNT(*) FROM sample54) AS sq2;
#
#mysql에서는 사용이 가능하지만 ,오라클에선 사용이 불가능 하다
+------+------+
| sq1 | sq2 |
+------+------+
| 5 | 4 |
+------+------+
-------------------------------------------------------------------
[실습] 샘플54의 a열 값들을 샘플54의 최대값으로 변경
mysql> UPDATE sample54
-> SET
-> a =(SELECT a FROM (SELECT MAX(a) as a FROM sample54) AS t);
+------+------+
| no | a |
+------+------+
| 2 | 900 |
+------+------+
-------------------------------------------------------------------
[실습2] 샘플541에 a열에 샘플51의 행의수 ,b열에 샘플54의 행의 수 입력
mysql> INSERT INTO sample541
-> VALUES(
-> (SELECT COUNT(*) FROM sample51),
-> (SELECT COUNT(*) FROM sample54));
Query OK, 1 row affected (0.01 sec)
mysql> select * from sample541;
+------+------+
| a | b |
+------+------+
| 5 | 2 |
+------+------+
-------------------------------------------------------------------
[실습] 샘플54에 3,20 입력
mysql> INSERT INTO sample54 VALUES(3,20);
Query OK, 1 row affected (0.01 sec)
mysql> select * from sample54;
+------+------+
| no | a |
+------+------+
| 1 | 900 |
| 2 | 900 |
| 3 | 20 |
+------+------+
[실습] 샘플 54의 no열이 1인행의 a열을 100으로 변경
update sample54 set a = 100 where no =1;
[실습] 샘플54 no열이 4인행의 a열을 20, no열이 3인 행의 a열을 80으로 변경
update sample54 set a = 20 where no =4;
update sample54 set a = 80 where no =3;
-------------------------------------------------------------------
<상관 서브 쿼리>
EXISTS (SELECT 명령)
SELECT * FROM sample551;
SELECT * FROM sample552;
----------------------------------------------------------
[실습] 샘플552의 no2열의 값이 샘플551의 no열의
값과 같은게 있다면
해당 샘플551의 a열의 값을 '있음'으로 변경
mysql> UPDATE sample551
-> SET a = '있음'
-> WHERE
-> EXISTS (SELECT * FROM sample552 WHERE sample552.no2 = sample551.no);
mysql> select * from sample551
-> ;
+------+------+
| no | a |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | 있음 |
| 4 | NULL |
| 5 | 있음 |
+------+------+
-------------------------------------------------------------------
[실습] 샘플552의 no2열의 값이 샘플 551의 no열의 값과
같지 않은게 있다면, 샘플551의 a열 값을 '없음'으로 변경
mysql> UPDATE sample551
-> set a ='없음'
-> WHERE
-> NOT EXISTS
-> (SELECT * FROM sample552 WHERE sample552.no2 = sample551.no);
mysql> select * from sample551
-> ;
+------+------+
| no | a |
+------+------+
| 1 | 없음 |
| 2 | 없음 |
| 3 | 있음 |
| 4 | 없음 |
| 5 | 있음 |
+------+------+
-------------------------------------------------------------------
[실습] 아래 코드의 결과를 확인하고 내용 이해하기
/* 단순 서브쿼리 */
DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54);
SELECT MIN(a) FROM sample54;
/* 상관 서브 쿼리 */
UPDATE sample551 SET a = '있음' WHERE EXISTS(SELECT * FROM sample552 WHERE no2=no);
SELECT * FROM sample552 WHERE no2=no
/*열에 테이블명 붙이기 */
UPDATE sample551 SET a ='있음'
WHERE EXISTS (SELECT * FROM sample552 WHERE sample552.no = sample551.no);
-------------------------------------------------------------------
/* 상관 서브쿼리 */
[실습] 아래코드 실행후 IN의 사용법 이해하기
SELECT * FROM sample551 WHERE no IN(3,5);
[실습] IN을 사용 샘플551의 no열이 샘플552의 no2열에 있는 값들에 포함되어 있는것들만 출력
SELECT * FROM sample551 WHERE no IN (SELECT no2 FROM sample552);
[실습] 샘플 551의a열에 null이 있는지 찾을 때 IN,NOT IN을 사용하지 않고 결과 확인
SELECT * FROM sample552 WHERE no2 IN (null); # 에러 반환
-------------------------------------------------------------------
<데이터 베이스 객체>
-데이터베이스 객체란 테이블이나 뷰 , 인덱스등 데이터베이스 내에 정의하는 모든것
-객체는 데이터베이스 내에서 실체를 가진다
-객체는 이름을 가진다. 데이터베이스내에서 객체를 작성할 때는 이름이 겹치지 않도록한다.
<데이터 베이스 객체의 명명규칙(제약사항)>
-기존이름이나 예약어와 중복하지 않는다.
-숫자로 시작할 수 없다
- _언더스코어 이외의 기호는 사용할 수없다
-한글을 사용할때는 더블쿼트로 둘러싼다
-시스템이 허용하는 길이를 초과 하지 않는다.
-이름은 객체의 종류와는 관계없다 예를들어 foo라는 이름의 테이블을 한번 만들면,
같은 종류의 테이블은 물론이고 뷰와같은 다른종류의 객체 역시 똑같은 이름으로
작성할 수 없다
-------------------------------------------------------------------
< 테이블 작성 삭제 변경 > DDL
CREATE TABLE 테이블명 () 구조 --> 테이블 생성
-괄호 안에 열 정의 [1열명 자료형 NULL/NOT NULL],[2열명 자료형 NULL/NOT NULL]
[실습] 샘플62 테이블 no(정수형, NULL불가), a(가변 문자형 30자리), b(날짜형)열 새로 생성
mysql> CREATE TABLE sample62(no integer not null,a VARCHAR(30),b DATE);
[실습2] 샘플62 테이블 구조 확인
mysql> desc sample62;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no | int | NO | | NULL | |
| a | varchar(30) | YES | | NULL | |
| b | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
-------------------------------------------------------------------
<테이블 삭제>
DROP TABLE '삭제하려는 테이블 명' (테이블에 저장된 데이터도 삭제)
DELETE는 기본 구조는 두고 데이터만 삭제
TRUNCATE TABLE NAME --> 뼈대는 남겨두고 전체 데이터 삭제
-------------------------------------------------------------------
<테이블 변경>
ALTER TABLE 테이블 변경 명령
-테이블에 저장되어 있는 데이터는 그대로 남긴 채 구성만 변경 가능
-열추가 : ALTER TABLE 테이블명 ADD 열이름 자료형;
[실습] 샘플62 테이블에 정수타입을 저장하는 newcol 열 추가
mysql> ALTER TABLE sample62 ADD newcol integer;
mysql> DESC sample62;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| no | int | NO | | NULL | |
| a | varchar(30) | YES | | NULL | |
| b | date | YES | | NULL | |
| newcol | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
-------------------------------------------------------------------
-열 속성 변경 : ALTER TABLE 테이블명 MODIFY 열이름 자료형;
[실습] 샘플62 테이블 newcol열의 타입을 가변문자형 20자리로 변경
mysql> ALTER TABLE sample62 MODIFY newcol VARCHAR(20);
mysql> desc sample62;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| no | int | NO | | NULL | |
| a | varchar(30) | YES | | NULL | |
| b | date | YES | | NULL | |
| newcol | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
-------------------------------------------------------------------
-열 이름 변경 : ALTER TABLE 테이블명 CHANGE 기본 열이름 새 열이름 자료형;
[실습] 샘플62 테이블 newcol열의 이름을 C로 변경
mysql> ALTER table sample62 CHANGE newcol C VARCHAR(20);
mysql> desc sample62;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no | int | NO | | NULL | |
| a | varchar(30) | YES | | NULL | |
| b | date | YES | | NULL | |
| C | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
-------------------------------------------------------------------
-열삭제 : ALTER TABLE 테이블명 DROP 열 이름 ;
[실습] 샘플62 테이블 c열을 삭제
mysql> ALTER TABLE sample62 DROP C;
mysql> desc sample62;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no | int | NO | | NULL | |
| a | varchar(30) | YES | | NULL | |
| b | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
-------------------------------------------------------------------
<제약 조건>
-데이터의 무결성을 지키기 위한 제한된 조건 의미
-특정 데이터를 입력 시 어떠한 조건을 만족했을 때에 입력되도록 제약
ex)동일한 아이디로 회원가입이 안되는것
[데이터 무결성을 위한 제약조건]
-PRIMARY KEY 제약 조건
-FOREIGN KEY 제약 조건
-UNIQUE 제약 조건
-CHECK 제약 조건
-DEFAULT 정의
-NULL 값 허용
-------------------------------------------------------------------
기본키 (PRIMARY) 란 ?
-테이블에 존재하는 많은 행의 데이터를 구분할 수 있는 식별자
-중복이나 NULL값이 입력될 수 없음
ex)회원 테이블의 회원 아이디, 학생 테이블이 학번
-기본 키로 생성한 것은 자동으로 클러스터형 인덱스 생성
-테이블에서는 기본 키를 하나 이상 열에 설정 가능
-기본 키 생성 방법
-------------------------------------------------------------------
[기본키 실습]
[실습] 샘플634 테이블 생성,
p열(정수형, NULL 불가), a열(가변 문자형 30자리) , p열에 PRIMARY 제약
설정
-->CREATE table sample634(p INT PRIMARY KEY not null,a VARCHAR(30));
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| p | int | NO | PRI | NULL | |
| a | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
[실습] 1행에 (1,A) ,2행에 (2,B), 3행에(1,C)를 추가 해봅시다
3행에 1,C추가가 불가능하다.
mysql> INSERT INTO sample634 VALUES(1,'A');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO sample634 VALUES(2,'B');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO sample634 VALUES(1,'C');
ERROR 1062 (23000): Duplicate entry '1' for key 'sample634.PRIMARY'
mysql> select * from sample634;
+---+------+
| p | a |
+---+------+
| 1 | A |
| 2 | B |
+---+------+
[실습] P열의 값이 2인행의 P열 데이터를 1로 변경
P는 PRIMARY키가 설정 되어있기 때문에 안된다.
-------------------------------------------------------------------
기본키 [PRIMARY KEY]
[실습] 샘플635 구조 확인하기
desc sample635;
[실습] 샘플635 내용 확인하기
select * from sample635;
[실습] a열에 2, b열에 3을 넣어 행 추가 하기
mysql> INSERT INTO sample635 VALUES(2,3);
mysql> select * from sample635;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
+---+---+
[실습] a열에 2 ,b열에 3을 넣어 다시 행 추가 하기
--> PRIMARY키로 인해 추가 불가능
-------------------------------------------------------------------
외부키 (FOREIGN KEY)
하나의 테이블에 있는게 아니라 두 테이블 사이의 관계를 형성한다.
외래키 테이블이 참조하는 기준 테이블의 열은 반드시 PRIMARY KEY 이거나,
UNIQUE 제약조건이 설정되어 있어야한다 .
외래키의 옵션중 ON DELETE CASCADE 또는 ON UPDATE CASCADE
-기분 테이블의 데이터가 변경되었을때
외래 키 테이블도 자동으로 적용하도록 설정
-------------------------------------------------------------------
방법1
--> CREATE TABLE 끝에 FOREIGN KEY 키워드로 설정
방법2
--> ALTER TABLE 구문이용
-------------------------------------------------------------------
<자동으로 증가하는 AUTO_INCREMENT >
AUTO_INCREMENT는 자동으로 1부터 증가하는 값을 입력해준다 .
AUTO_INCREMENT로 지정할 때는 꼭 PRIMARY 키에서만 사용이 가능하다.
-------------------------------------------------------------------
[실습 userTBL 테이블 생성하기]
mysql> CREATE TABLE userTBL
-> (
-> user ID VARCHAR(3) PRIMARY KEY,
-> userName VARCHAR(4) NOT NULL,
-> birthYear INT NOT NULL,
-> addr VARCHAR(2) NOT NULL,
-> tel SMALLINT NULL, #smallINT는 2바이트
-> mobile INT NULL,
-> height INT NOT NULL,
-> subDate DATE NOT NULL
-> );
-------------------------------------------------------------------
[실습 BuyTBL 테이블 생성한 후, userTBL의 userID를 참조]
mysql> CREATE TABLE buyTBL
-> (
-> no INT PRIMARY KEY,
-> userID VARCHAR(3) NOT NULL,
-> pName VARCHAR(4) NOT NULL,
-> category VARCHAR(2),
-> price INT NOT NULL,
-> amount INT NOT NULL,
-> FOREIGN KEY (userID) REFERENCES userTBL (userID));
-----외래키는 다른 테이블의 KEY를 가져올 수 있으며 중복데이터가 쌓이기 때문에
효율적인 관리를 위해서 사용한다. userTBL에 있는 userID를 굳이
buyTBL에서 다시 쓸 필요는 없기 때문이다.
-------------------------------------------------------------------
[이미 생성한 테이블에 AUTO_INCREMENT 추가 하기 ]
ALTER TABLE 테이블명 MODIFY id INT NOT NULL AUTO_INCREMENT;
