[DAY-27] SELECT, INSERT

6.1 SELECT문

1. 특정한 조건의 데이터만 조회

 

1) WHERE 조건문

ㆍ 서브쿼리

쿼리문 안에 쿼리문

SELECT name, height FROM usertbl WHERE height > 177;

-- 서브쿼리
SELECT name, height FROM usertbl
WHERE height > (SELECT height FROM usertbl WHERE Name = '김경호');

 

ㆍ ANY와 ALL

/* 
만약 하위커리의 반환 값이 두 개 이상일 경우 오류가 남
SELECT name, height FROM usertbl
WHERE height >= (SELECT height FROM usertbl WHERE addr ='경남');
*/

- 

-- 하위쿼리의 반환값이 173, 170일 때

-- ANY
SELECT name, height FROM usertbl
WHERE height >= ANY (SELECT height FROM usertbl WHERE addr ='경남');
-- 173보다 크거나 같은 사람 또는 170보다 크거나 같은 사람을 출력(OR)
-- 결과적으로 170보다 크거나 같은 사람

-- ALL
SELECT name, height FROM usertbl
WHERE height >= ALL (SELECT height FROM usertbl WHERE addr ='경남');
-- 173보다 크거나 같고 그리고 170보다 크거나 같은 사람을 출력(AND)
-- 결과적으로 173보다 크거나 같은 사람

 

- 동일한 값 출력

-- 하위쿼리의 반환값이 173, 170일 때
SELECT name, height FROM usertbl
WHERE height = ANY (SELECT height FROM usertbl WHERE addr ='경남');
-- 173, 170에 해당하는 값 출력

-- IN(서브쿼리)와 같음
SELECT name, height FROM usertbl
WHERE height IN (SELECT height FROM usertbl WHERE addr ='경남');

SELECT name, height FROM usertbl
WHERE height = ALL (SELECT height FROM usertbl WHERE addr ='경남');
-- 값 두 개 이상일 경우 동시에 만족하는 값 없음 (오류는x)

 

2) 원하는 순서대로 출력: ORDER BY

- 결과가 출력되는 순서를 조절

SELECT name, mDate FROM usertbl ORDER BY mDate; -- 기본 오름차순(생략가능)

SELECT name, mDate FROM usertbl ORDER BY mDate DESC; --오름차순
SELECT name, mDate FROM usertbl ORDER BY mDate ASC; --내림차순

 

- 여러 조건으로 정렬

앞에 조건으로 정렬했을 때 동일한 경우 뒤에 조건으로 정렬

SELECT name, height FROM usertbl ORDER BY height DESC, name ASC;

 

- ORDER BY 되도록 사용하지 않는 것이 좋음. mySQL 성능을 떨어뜨림.

 

3) 중복된 것 하나만 남기기:  DISTINCT

- 

SELECT DISTINCT addr FROM usertbl;

 

4) 출력하는 개수를 제한하기: LIMIT

- LIMIT N

USE employees;
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC
LIMIT 5;

 

- LIMIT 시작, 개수

USE employees;
SELECT emp_no, hire_date FROM employees
ORDER BY hire_date ASC
LIMIT 0 5;

 

5) 테이블을 복사하기: CTRATE TABLE~ SELECT

 

use sqldb;
CREATE TABLE buytbl2(SELECT * FROM buytbl);
SELECT * FROM buytbl2;

CREATE TABLE buytbl3(SELECT userID, prodName FROM buytbl);
SELECT * FROM buytbl3;

* 테이블 복사 시 PRIMARY KEY, FOREGIN KEY 등 제약조건 복사 안됨.

 

 

2. 집계함수

 

1) GROUP BY절

- 그룹을 묶어주는 역할

형식:
SELECT select_expr
   [FROM table_references]
   [WHERE where_condition]
   [GROUP BY {col_name | expr  | position}]
   [HAVING BY where_condition]
   [ORDER BY {col_name | expr | position}]

-- 사용자별로 구매개수 출력
SELECT userID, SUM(amount)
	FROM buytbl
    GROUP BY userID;
    
-- 별칭 붙이기o
SELECT userID '사용자 아이디', SUM(amount) '총 구매 개수'
	FROM buytbl
    GROUP BY userID;

-- 사용자별 구매액의 총합
SELECT userID '사용자 아이디', SUM(price*amount) '총 구매액'
	FROM buytbl
    GROUP BY userID;

 

2) HAVING 절

- 집계함수에 대해서 조건을 제한

** 순서! 꼭 GROUP BY절 다음에 나와야 함.

-- 집계함수 WHERE절에 들어가면 오류
/* WHERE 조건에 맞는 값을 SELECT로 출력하는 건데 SUM이라는 열 없어 순서상 오류가 남
SELECT userID, SUM(price*amount)
   FROM buytbl
   WHERE SUM(price*amount) > 1000
   GROUP BY userID;
*/

-- GROUP~SELECT 다 한 후 HAVING으로 넘어감 
SELECT userID, SUM(price*amount)
   FROM buytbl
   GROUP BY userID
   HAVING SUM(price*amount) > 1000;

 

3) ROLLUP문

- 총합 또는 중간 합계를 구할 때 사용

- GRUP BY절과 함께 WITH ROLLUP문 사용

SELECT num, groupName, SUM(price*amount) AS '비용'
   FROM buytbl
   GROUP BY groupName, num
   WITH ROLLUP;

 

* SQL의 분류

ㆍ DML
데이터를 조작(선택, 삽입, 수정, 삭제)하는 데 사용되는 언어
SELECT / INSERT / UPDATE / DELETE
트랜잭션이 발생하는 SQL도 DML

ㆍDDL
데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스를 생성/삭제.변경 역할

ㆍ DCL
권한을 부여하거나 빼앗을 때 사용

 

---

6.2 데이터의 변경을 위한 SQL문

1. 데이터 삽입: INSERT문

ㆍ기본 INSERT문

INSERT [INTO] 테이블(열1, 열2,..)  VALUES(값1, 값2,...)

USE sqldb;

CREATE TABLE testTbl1 (id int, userName char(3), age int);

INSERT INTO testTbl1 VALUES (1, '홍길동', 25);
INSERT INTO testTbl1(id, userName) VALUES (2, '설현');
INSERT INTO testTbl1(userName, age, id) VALUES ('하니', 26, 3);

 

ㆍ AUTO_INCREMENT

insert에서 해당 열이 null 값이 

USE sqldb;
CREATE TABLE testTbl2
(id int auto_increment primary key,
userName char(3),
age int);
INSERT INTO testTbl2 VALUES (NULL, '지민', 25);
INSERT INTO testTbl2 VALUES (NULL, '유나', 22);
INSERT INTO testTbl2 VALUES (NULL, '유경', 21);

 

- SELECT LAST_INSERT_ID();

현재 어느숫자까지 증가되었는지 확인할 때 사용

 

 

- ALTER

입력값을 해당 숫자부터 입력되도록 변경

ALTER TABLE testTbl2 AUTO_INCREMENT=100;
INSERT INTO testTbl2 VALUES (NULL, '찬미', 23);
SELECT * FROM testTbl2;

 

- @@auto_increment_increment 변수

증가 값을 지정하고 싶을 때

USE sqldb;
CREATE TABLE testTbl3
(id int auto_increment primary key,
userName char(3),
age int);
-- 1000부터 3씩 증가
ALTER TABLE testTbl3 AUTO_INCREMENT = 1000;
SET @@auto_increment_increment=3;
INSERT INTO testTbl3 VALUES (NULL, '나연', 20);
INSERT INTO testTbl3 VALUES (NULL, '정연', 18);
INSERT INTO testTbl3 VALUES (NULL, '모모', 19);
SELECT * FROM testTbl3;

 

ㆍ 대량의 샘플 데이터 생성(SELECT INTO ~ SELECT)

- SELECT문의 결과 열의 개수는 INSERT할 테이블의 열 개수와 일치해야 함

형식:
INSERT INTO 테이블이름( 열 이름1, 열 이름2, ...)
   SELECT문 ;

 

2. 데이터 수정: UPDATE

-  기존에 입력되어 잇는 값을 변경

- WHERE절 생략하면 테이블의 전체 행 변경됨

-- 값 하나하나 바꿔
UPDATE 테이블이름
   SET 열1=값1, 열2=값2 ...
   WHERE 조건;

-- 이런 조건에 있는 값 바꿔줘
UPDATE testtbl4
	SET Lname = '없음'
    WHERE Fname = 'Kyoichi';

-- 전체 바꿔줘
UPDATE buytbl SET price = price * 1.5;

 

3. 데이터 삭제: DELETE FROM

- 행  단위로 삭제

DELETE FROM 테이블이름 WHERE 조건;

USE sqldb;
DELETE FROM testTbl4 WHERE Fname = 'Aamer';

- LIMIT구문 이용하여 개수 제한하여 삭제 가능

 

* WHERE문 생략되면 전체 데이터 삭제됨!! 주의!!

그러나 테이블은 남아있음,,,

* DROP은 테이블 전체 삭제됨

 

DELETE vs DROP vs TRUNCATE

DELETE FROM bigTbl1;
DROP Table bigTbl2;
TRUNCATE Table bigTbl3;

DELETE => DML문으로 트랜잭션 로그를 기록함, 삭제가 오래 걸림
DROP => DDL문으로 테이블 자체를 삭제, 트랜잭션을 발생시키지 않음
TRUNCATE => DELETE와 동일하지만 트랜책션 로그 기록하지 않음

 

4. 조건부 데이터 입력, 변경

** INSERT 

데이터를 넣다가 오류가 나면 나머지 값도 입력되지 않음

PRIMARY키가 같아서 안들어가

USE sqldb;
CREATE Table memberTbl (SELECT userID, name, addr FROM usertbl LIMIT 3);
ALTER TABLE memberTbl
	ADD CONSTRAINT pk_memberTbl PRIMARY KEY (userID);
SELECT * FROM memberTbl;

-- PRIMARY KEY 중복 오류나
/*INSERT INTO memberTbl Values('BBK', '비비코', '미국');
INSERT INTO memberTbl Values('SJH, '서장훈', '서울');
INSERT INTO memberTbl Values('HYJ', '현주엽', '경기');
SELECT * FROM memberTbl;
*/

 

1) IGNORE

- PK 중복일 경우 오류 발생시키지 않고 무시하고 넘어감

INSERT IGNORE memberTbl Values('BBK', '비비코', '미국'); -- PRIMARY KEY인 BBK 중복이어서 안들어감
INSERT IGNORE memberTbl Values('SJH', '서장훈', '서울');
INSERT IGNORE memberTbl Values('HYJ', '현주엽', '경기');

 

2) DUPLICATE

- PK중복되지 않으면 INSERT, 중복이면 UPDATE

INSERT INTO memberTbl VALUES('BBK', '비비코', '미국')
	ON DUPLICATE KEY UPDATE name='비비코', addr='미국'; -- BBK 중복=> 비비코로 UPDATE
INSERT INTO memberTbl VALUES('DJM', '동짜몽', '일본')
	ON DUPLICATE KEY UPDATE name='동짜몽', addr='일본'; -- DJM 없는 값=> INSERT

 

 

---

6.3 WITH절과 CTE

WITH절 CTE를 표현하기 위한 구문

 

1) 비재귀적 CTE

- 단순한 형태로 복잡한 쿼리문장을 단순화 시키는 데에 적합하게 사용

WITH CTE_테이블이름(열 이름)
AS
(
   <쿼리문>
)
SELECT 열 이름 FROM CTE_테이블이름;

USE sqldb;
SELECT userid AS '사용자', SUM(price*amount) '총구매액'
	FROM buyTbl GROUP BY userid;

WITH abc(userid, total)
AS
(SELECT userid, SUM(price*amount)
	FROM buytbl GROUP BY userid)
SELECT * FROM abc ORDER BY total DESC;

WITH cte_userTbl(addr, maxHeight)
AS
(SELECT addr, MAX(height) FROM usertbl GROUP BY addr)
SELECT AVG(maxHeight * 1.0) AS '각 지역별 최고키의 평균' FROM cte_usertbl;

- with 테이블이 실제 존재하는 것이 아니라 abc라는 가상의 테이블이 만들어짐

- AS (SELECT..)에서 조회하는 열의 개수와 WITH 열 개수가 일치해야 함

 

 

---

b(binary)
UN (unsigned data type)
음수 값이 안들어가
zero fill 지정한 값 안채워졌으면
AI auto_increment
G 어떤 공식에 의해서 값이 들어갈 때

ㆍ