[DB] | 0415 수업내용

DATE_FORMAT(), CONCAT(), IFNULL(), TRIM, CEIL() 많이 쓰인다.

---

EMPLOYEES 테이블에서 여성이면서 FIRST_NAME이 'S'로 시작하고
고용일이 1990년 이상인 사람들을 출력
HIRE_DATE >= '1990-01-01'

SQL

SELECT * FROM employees 
WHERE GENDER = 'F' 
AND FIRST_NAME LIKE 'S%'
AND HIRE_DATE >= 19900101 ;

SELECT * FROM employees 
WHERE GENDER = 'F' 
AND FIRST_NAME LIKE 'S%'
AND HIRE_DATE >= '1990-01-01' ;

---

!= 와 <> 같다.

SQL

SELECT * FROM titles
WHERE TITLE <> 'STAFF';

SELECT * FROM titles
WHERE TITLE != 'STAFF';

---

<서브쿼리>

• 서브쿼리에 IN 쓸 수 있다
• 컬럼이 여러개 절대 안돼
• 컬림은 하나인데 레코드가 여러개인 조건문을 쓰고 싶다. -> 이때는 = 사용하면 에러, 꼭 IN을 사용해야 한다.

SQL

SELECT * FROM titles
WHERE EMP_NO = 10007;

SELECT * FROM titles
WHERE TITLE IN (
	SELECT * FROM titles
WHERE EMP_NO = 10007
);

SELECT * FROM titles
WHERE TITLE IN ( 'SENIOR STAFF', 'STAFF' );

---

PRIMARY KEY(컬럼, 컬럼, ...) -> 컬럼 여러개를 기본 키 줄 수 있다.
그냥 속성으로 PRIMARY KEY를 주면 컬럼 두 개에 동시에 줄 수 없다.

AUTO_INCREMENT -> PRIMARY KEY, INT형에게만 줄 수 있다. INSERT할 때 무시해도 된다. 자기가 알아서 점점 증가하는 중복되지 않은 수를 넣어줄 테니까

SQL

USE JAVA;
CREATE TABLE TESTTBL2 (
	ID INT AUTO_INCREMENT,
	USERNAME CHAR(3),
	AGE INT,
	PRIMARY KEY(ID)
);

/*
CREATE TABLE TESTTBL3 (
	ID INT AUTO_INCREMENT PRIMARY KEY,
	USERNAME CHAR(3) PRIMARY KEY,
	AGE INT
);
*/

INSERT와 SELECT의 조합

SQL

INSERT INTO TESTTBL2
( USERNAME, AGE)
VALUES
('지민', 25),
('유나', 22),
('유경', 21);

INSERT INTO TESTTBL2
( USERNAME, AGE)
SELECT USERNAME, AGE FROM TESTTBL2;

SELECT USERNAME, 30 FROM TESTTBL2;

INSERT INTO TESTTBL2
( USERNAME, AGE)
SELECT USERNAME, 30 FROM TESTTBL2;
FROM TESTTBL2;

INSERT INTO TESTTBL2
(USERNAME, AGE)
SELECT '홍길동', AGE
FROM TESTTBL2
LIMIT 2;

SELECT AGE, AGE + 10 AS AGEPLUS10, 1 + 1 AS NUM2 FROM TESTTBL2;

---

<UPDATE>

TESTTBL2 3번 본인의 이름과 나이로 바꾸기

UPDATE 테이블명
SET 컬럼명 = 수정값
, 컬럼명 = 수정값
, 컬럼명 = 수정값
WHERE 조건식 (레코드 지정);

WHERE절에서는 , 쓰지 않고 AND 또는 OR 이용해서 추가한다.
UPDATE, DELETE에 오는 WHERE절에는 대부분 PK값이 온다.

SQL

UPDATE testtbl2 
SET USERNAME = '장현진'
, AGE = 26   
WHERE ID = 3;

---

<DELETE>

DELETE FROM 테이블명
WHERE 조건식;

WHERE을 쓰지 않으면 다 지워진다.

ID=10 인 곳을 지우기

SQL

DELETE FROM testtbl2
WHERE ID = 10;

---

테이블 복사 (내용만 데이터만, 스키마는 안됨, TYPE과 길이는 됨)

PK값은 절대 바꾸지 않는다.
1 ~ 20 번이 주어진 PK 중에 10번이 주어진다고 9 다음 바로 11이 된다고 11부터 20번까지 1씩 떙겨버리면 안된다.
어떤 사람이 죽었다고 뒤에 주민등록번호를 가진 사람들의 주민등록번호를 땡기는 것이나 마찬가지이다.

SQL

CREATE TABLE TESTTBL3
AS
SELECT * FROM TESTTBL2;

CREATE TABLE TESTTBL4 (SELECT * FROM testtbl2);

---

<테이블 삭제하는 법>

DROP -> 테이블 자체를 날리는 것, 파괴
안의 데이타만 날리고 스키마는 살아있다. 테이블은 사라지지 않는다.

SQL

DROP TABLE testtbl2;
DELETE FROM testtbl3;
TRUNCATE testtbl4;

아예 사라진 TESTTBL2

틀은 남아있는 TESTTBL3

---

<IGNORE>
에러가 터지면 무시하고 다음 SQL을 실행한다.

IGNORE하면 에러 터진 SQL문 뒤의 문장들은 실행되지 않는다.

SQL

INSERT INTO testtbl3
(ID, USERNAME, AGE) VALUES (1, '지민', 25);

INSERT INTO testtbl3
(ID, USERNAME, AGE) VALUES (1, '유나', 22);

INSERT INTO testtbl3
(ID, USERNAME, AGE) VALUES (3, '유경', 21);


INSERT IGNORE INTO testtbl3
(ID, USERNAME, AGE) VALUES (1, '지민', 25);

INSERT IGNORE INTO testtbl3
(ID, USERNAME, AGE) VALUES (1, '유나', 22);

INSERT IGNORE INTO testtbl3
(ID, USERNAME, AGE) VALUES (3, '유경', 21);

---

< IF EXISTS >

• IGNORE같은 거
• IF NOT EXISTS 도 있다. CREAT할 때 쓴다.
• 에러가 나더라도 밑의 문장들이 계속 실행된다.
• DROP TESTTBL2는 이미 DROP되어 없어서 이 문장은 에러 터진다 그러면 밑의 문장들이 실행안된다
• 그런데 IF EXISTS쓰면 밑의 문장들이 계속 실행된다.

SQL

DROP TESTTBL2
DROP TABLE IF EXISTS TESTTBL2

---

p224 , INT

• 데이터베이스는 UNSINGED가 있다. 뜻은 부호가 없도록 호칭을 줄 수 있다.

p226 DATE와 형변환 (CAST)

데이터베이스는 정수 위주 '10'+10 = 20이다

문자열은 더하기가 안되서 꼭 COMCAT을 써야한다

• DATEFORMAT

SQL

SELECT NOW();

SELECT CAST('2021-04-16 12:21:58' AS DATE) AS D_DATE;

SELECT CAST('2021-04-16 12:21:58' AS DATETIME) AS D_DATETIME;

SELECT CAST ('10' AS INT), '10';

SELECT CAST ('10' AS INT), 'A10' + '10', CONCAT('10', '10', '10'), 'AAA' + 'BBB';

SELECT CAST ('10' AS INT) + 10, '10' + '10';

SELECT DATE_FORMAT(NOW(), '%m - %y - %d');

SELECT DATE_FORMAT(NOW(), '%y년 %m월 %d일');

SELECT DATE_FORMAT('2021-04-16 12:21:58', '%y년 %m월 %d일');

SELECT DATE_FORMAT(NOW(), '%Y년 %M월 %D일');

p234

IF(IF문 안에 IF문 사용 가능), CASE(복잡한 거 할 때 주로 사용)

SQL

USE EMPLOYEES;

SELECT EMP_NO, GENDER,
IF(GENDER='M', '남성', '여성') AS KOREAN_GENDER
FROM EMPLOYEES;

SELECT EMP_NO, GENDER
, CASE WHEN GENDER = 'M'
			THEN '남성'
			ELSE '여성'
	END
FROM EMPLOYEES;

IFNULL 대박 많이 쓴다.

NULL 찾을 때는 IS NULL로 찾아야 한다.

NULLIF

<CASE>
SELECT
CASE WHEN,

CASE 컬럼 WHEN,

SQL

SELECT
	CASE 10
	WHEN 1 THEN '일'
	WHEN 5 THEN '오'
	WHEN 10 THEN '십'
	ELSE '뭐지'
	END AS c_case;

PRODUCTTBL에 COMPANY 컬럼 값이
'대우' > 탱크대우
'LG' > 사랑해요 LG
'삼성' > 또 하나의 가족
도 아닌 경우 > 바보

딱 딱 떨어질 때

USE JAVA;

SELECT COMPANY, 
	CASE COMPANY
		WHEN '대우' THEN '탱크대우'
		WHEN 'LG' THEN '사랑해요 LG'
		WHEN '삼성' THEN '또 하나의 가족'
		ELSE '바보'
	END AS addment FROM PRODUCTTBL;

범위를 하고 싶을 때

SELECT *,
	CASE 
		WHEN SALARY >= 80000 THEN '최고급인력'
		WHEN SALARY >= 70000 THEN '고급인력'
	ELSE '저급인력'
	END AS POSITION
FROM SALARIES; 

---

<CONCAT_WS()>
문자열과 문자열을 합칠 때 어떤 것을 합쳐질 때 마다 삽입해준다.

SELECT *
, CONCAT(FIRST_NAME, ' ', LAST_NAME, ' ', GENDER) AS NAME1
, CONCAT_WS(' ', FIRST_NAME, LAST_NAME, GENDER) AS NAME2
, CONCAT_WS(',', FIRST_NAME, LAST_NAME, GENDER) AS NAME3
FROM EMPLOYEES;

---

FORMAT () -> ,가 아니라 .이 찍힌다.
,가 있으면 절대 정수가 아니다.
0 하면 천단위 , 찍힌다.

SELECT FORMAT(9123456.123456789, 0);

SELECT FORMAT(9123456.123456789, 1);

SELECT FORMAT(9123456.123456789, 2);

SELECT FORMAT(9123456.123456789, 3);

SELECT FORMAT(9123456.123456789, 4);

SELECT FORMAT(36500, 0);

SELECT FORMAT(36500, 1);

SELECT FORMAT(36500, 2);

---

SELECT * , INSERT(DEPT_NO, 2, 2, 'I')
FROM DEPT_EMP;

SELECT * , INSERT(DEPT_NO, 2, 0, 'I')
FROM DEPT_EMP;

---

글자 자르기

SELECT *
, UPPER(LEFT(FIRST_NAME, 2))
, LOWER(RIGHT(FIRST_NAME, 3))
FROM EMPLOYEES;

글씨 채우기

SELECT 
FIRST_NAME
, LPAD(FIRST_NAME, 10, '#')
, RPAD(FIRST_NAME, 10, '#')
FROM EMPLOYEES;

빈칸 지우기 (양쪽만, 중간은 x)

SELECT TRIM('     안녕          ') AS HI;
, TRIM('    하   하하          ') AS SMILE;

빈칸 다 지우기
원본, 이러한 값이 있으면, 이걸로 바꿔라

SELECT TRIM('     안녕          ') AS HI
, TRIM('    하   하하          ') AS SMILE;
, REPLACE('    하   하하          ', ' ', '');

SUBSTRING SUBSTR
년도도 가져올 수 있고, 일도 가져올 수 있다. 그런데 월은?

SELECT BIRTH_DATE, 
LEFT(BIRTH_DATE, 4) AS YEAR,
RIGHT(BIRTH_DATE, 2) AS DAY,
CAST(RIGHT(BIRTH_DATE, 2) AS INT) AS DAY_INT,
SUBSTRING(BIRTH_DATE, 6, 2) AS MONTH,
CAST(SUBSTRING(BIRTH_DATE, 6, 2) AS INT) AS MONTH_INT
FROM EMPLOYEES;

SUBSTRING 2개 그 자리에서 몇 번째 자리까지
1개 그자리에서 끝까지

---

테이블 생성 쿼리
-> 복사 붙여넣기 하면 똑같이 만들 수 있다.

1. SHOW CREATE 테이블명
2. 툴 이용하기
   

다 외울려고 하지말고 이해하고 넘어가는 게 중요하다
그때 그떄 필요할 때 검색해서 사용할 줄 아는 것이 중요하다.

---

CEIL() = CEILING( )

SELECT BIRTH_DATE, 
LEFT(BIRTH_DATE, 4) AS YEAR,
RIGHT(BIRTH_DATE, 2) AS DAY,
CAST(RIGHT(BIRTH_DATE, 2) AS INT) AS DAY_INT,
SUBSTRING(BIRTH_DATE, 6, 2) AS MONTH,
CAST(SUBSTRING(BIRTH_DATE, 6, 2) AS INT) AS MONTH_INT
FROM EMPLOYEES;