MySQL
Group By에 작성된 컬럼명만 SELECT 자리에 쓸 수 있고, SELECT에 COLUMN명은 못쓰지만 SUM과 같은 집계함수는 사용 가능
-- 집계함수 사용 그룹핑
USE sqldb;
-- GROUP BY에 작성된 컬럼명만 SELECT 자리에 쓸 수 있음 / Column은 사용불가하지만 sum과 같은 함수는 사용가능
SELECT userID as '아이디'
, sum(amount) as '구매개수'
FROM buytbl
GROUP BY userID;
SELECT userID as '아이디'
, avg(amount) as '평균구매개수'
FROM buytbl
GROUP BY userID;
-- HAVING
SELECT userID
, sum(price*amount) as '비용' -- 다음과 같이 집계함수를 통해 만들어진 '비용' WHERE 사용 불가/ 아래 HAVING 써야함
FROM buytbl
GROUP BY userID
HAVING 비용 >= 1000; -- 집계함수등의 결과값을 조건으로 필터링하기위함. WHERE같은 역할
-- Rollup
SELECT userID
, sum(price*amount) as '비용'
FROM buytbl
GROUP BY userID
WITH ROLLUP; -- 총합 또는 중간합계 필요한 경우 사용, Group By와 함께 사용하면 분류 별 합계 및 그 총합 구해짐
-- INSERT
INSERT INTO usertbl
(userID,name,birthYear,addr,mobile1,mobile2,height,mDate)
VALUES
('OYB','오윤범',1998,'부산','010','85150728',174,'2023-03-02');
-- addr 까지만 Not Null
INSERT INTO usertbl
(userID,name,birthYear,addr,mobile1,mobile2,height,mDate)
VALUES
('HGS','홍길순',2000,'한양',NULL,NULL,NULL,NULL);
-- Not Null 아예 안넣으면 알아서 NULL로 들어감
INSERT INTO usertbl
(userID,name,birthYear,addr)
VALUES
('SHM','손흥민',1991,'서울');
-- 컬럼 지정 생략 가능(단, 입력하는 값 순서 컬럼 순서랑 일치)
INSERT INTO usertbl VALUES
('SJM','손웅정',1969,'서울','010','11112222','176',NULL);
-- AUTO_INCREMENT의 경우 필드값 입력하지 않아도 됨(buytbl에서는 num이 AI로 설정되어있음)
INSERT INTO buytbl
(userID,prodName,groupName,price,amount) -- num 없어도 자동증가됨
VALUES
('OYB','노트북','전자',500000,1)
-- INSERT INTO SELECT
-- usertbl 컬럼 네개 가져와서 usertbl_copy에 집어넣는다
INSERT INTO usertbl_copy(userID,name,birthYear,addr)
SELECT userID,name,birthYear,addr FROM usertbl;
UPDATE/DELECT는 TRANSACTION과 COMMIT,ROLLBACK 적극활용
-- UPDATE&TRANSACTION
-- UPDATE문 사용시 COMMIT/ROLLBACK 통해서 미리 잘못된 UPDATE 방지해줘야함
START TRANSACTION;
-- UPDATE 구문에는 WHERE절 빼면 안됨!
UPDATE usertbl_copy
SET mobile1='010'
, mobile2='66667788'
, addr='부산'
WHERE userID='OYB';
COMMIT;
ROLLBACK;
-- DELETE
-- WHERE절이 빠지면 절대 안됨
DELETE FROM usertbl_copy
WHERE userID='OYB';
INSERT INTO proctbl (TOTAL_NAME)
VALUES('BBK');
SELECT * FROM proctbl;
DELETE FROM proctbl WHERE ID =3;
-- 완전초기화
TRUNCATE proctbl;
DELETE FROM proctbl;
-- 기존 데이터 2개인 상태에서 DELETE FROM proctbl로 다 삭제하고 다시 집어넣으면 id가 3번부터 시작됨
-- 완전 초기화를 하고싶다면 TRUNCATE 사용
함수 (형변환,NULL,CASE,문자열,수학함수,날짜 및 시간함수,COUNT)
-- 변수 사용
SET @myVar1 = 5;
SELECT @myVar1;
SET @myVar2 = '연예인 이름 ==> ';
SELECT @myVar2, name
FROM usertbl
WHERE height > 170;
SELECT name, height + @myVar1
FROM usertbl;
-- 형변환
SELECT CAST(birthYear AS CHAR) FROM usertbl;
SELECT CAST(CONVERT(birthYear, CHAR) AS SIGNED INTEGER) FROM usertbl;
SELECT CAST(addr AS DECIMAL) FROM usertbl;
SELECT CONVERT(addr, DECIMAL) FROM usertbl;
SELECT CAST('1000' AS DECIMAL);
-- 암시적 형변환
SELECT 200 + 300;
SELECT CONCAT('HELLO','World');-- 문자열 합치기
SELECT '200'+'300'; -- MySQL 이외에서는 '200300'
SELECT CONCAT(200,'300');
/* 내장함수 리스트 */
-- 흐름함수
-- 100>200 ? '참' : '거짓'
SELECT IF(100>200,'참','거짓');
-- NULL은 계산에 사용하면 오류나서 처리해줘야함
SELECT IFNULL(NULL,0)+100; -- NULL이면 0으로 바꿔달라는 말임
-- NULLIF는 많이 사용안됨
SELECT NULLIF(100,100);
-- 쿼리 작성할 때 많이 사용
-- CASE
SELECT name
, birthYear
, addr
, CASE addr -- CASE:IF문처럼 사용. 서울이면 수도권, 경기면 수도권, 나머지는 모름이라고 출력하라.
WHEN '서울' THEN '수도권'
WHEN '경기' THEN '수도권'
WHEN '부산' THEN '광역권'
WHEN '한양' THEN '조선권'
ELSE '지역권' END
FROM usertbl;
-- 문자열함수
SELECT ASCII('A'),CHAR(65),ASCII('a');
SELECT ASCII('안'),CHAR(236); -- 한글은 사용하면 안됨
-- CHAR_LENGTH : 글자길이 / LENGTH: 바이트
SELECT CHAR_LENGTH('ABC'),LENGTH('ABC');
-- UTF-8
SELECT CHAR_LENGTH('가나다'),LENGTH('가나다'); -- 한글 한글자당 3bytes , 글자 개수/바이트 길이 의미임.
-- 문자열
SELECT REPLACE('Hello World','Hello','New');
-- DB는 인덱스를 1부터 시작
SELECT INSTR('안녕하세요, 여러분','여'); -- 빈칸도 센다
-- LEFT,RIGHT
SELECT LEFT('ABCDEFGHIJKLMN',3),RIGHT('ABCDEFGHIJKLMN',3);
-- UPPER,LOWER
SELECT UPPER('Hello World'), LOWER('HELLO WORLD');
-- LTRIM,RTRIM,TRIM
SELECT LTRIM(' HELLO WORLD! ') AS 'LTRIM'
, RTRIM(' HELLO WORLD! ') AS 'RTRIM'
, TRIM(' HELLO WORLD! ') AS 'TRIM';
-- 'Hello'*3
SELECT REPEAT('Hello',3);
-- SUBSTRING
SELECT SUBSTRING('대한민국만세',5,2);
-- 수학함수
SELECT ABS(-10);
-- 올림,내림,반올림
SELECT CEILING(4.3),FLOOR(4.9),ROUND(4.4);
SELECT MOD(157,10);
-- RANDOM
SELECT RAND(),FLOOR(1+RAND() * (6));
-- 날짜 및 시간함수
SELECT NOW();
SELECT ADDDATE('2023-03-02', INTERVAL -10 DAY);
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT TIME(NOW());
SELECT DAYOFWEEK(NOW()); -- 1(일)~7(토)
SELECT LAST_DAY(NOW());
-- 시스템 함수
SELECT USER();
SELECT DATABASE();
-- ROW_COUNT()
USE sqldb;
UPDATE buytbl SET price=price*2;
SELECT ROW_COUNT();
SELECT VERSION();
-- 피벗
CREATE TABLE pivotTest
(
uname CHAR(3),
season CHAR(2),
amount INT
);
INSERT INTO sqldb.pivottest
(uname, season, amount)
VALUES
('김범수', '겨울', 10), ('윤종신', '여름', 15), ('김범수', '가을', 25),
('김범수', '봄', 3), ('김범수', '봄', 37), ('윤종신', '겨울', 40),
('김범수', '여름', 14), ('김범수', '겨울', 22), ('윤종신', '여름', 64);
-- 피벗기능
SELECT uName
, sum(if(season='봄',amount,0)) as '봄'
, sum(if(season='여름',amount,0)) as '여름'
, sum(if(season='가을',amount,0)) as '가을'
, sum(if(season='겨울',amount,0)) as '겨울'
, sum(amount) as '합계'
FROM pivottest
GROUP BY uName
/* Join */
-- inner join
SELECT b.num, b.userID, b.prodName
, u.name, u.addr, u.birthYear
FROM buytbl as b
INNER JOIN usertbl as u
ON b.userID = u.userID
ORDER BY b.num DESC;
-- WHERE u.userID='JYP';
/* OUTER JOIN */
-- std table
CREATE TABLE stdtbl(
stdName NVARCHAR(10) NOT NULL PRIMARY KEY -- Join 시에 Column 타입 안맞추면 오류 발생
, addr CHAR(4) NOT NULL
);
CREATE TABLE clubtbl(
clubname NVARCHAR(10) NOT NULL PRIMARY KEY -- Join 시에 Column 타입 안맞추면 오류 발생
, roomNo NCHAR(4) NOT NULL
);
CREATE TABLE stdclubtbl(
num int AUTO_INCREMENT NOT NULL PRIMARY KEY
, stdName NVARCHAR(10) NOT NULL -- Join 시에 Column 타입 안맞추면 오류 발생
, clubName NVARCHAR(10) NOT NULL
, FOREIGN KEY(stdName) REFERENCES stdtbl(stdName) -- 외래키로 stdName을 잡았기에 stdName에 있는 값만 갖고놀 수 있음
, FOREIGN KEY(clubName) REFERENCES clubtbl(clubName) -- clubName도 동일
);
-- OUTER JOIN
INSERT INTO stdtbl VALUES
('김범수','경남'),('성시경','서울'),('조용필','경기'),
('은지원','경북'),('바비킴','서울');
INSERT INTO clubtbl VALUES
('수영','101호'),('바둑','102호'),('축구','103호'),('봉사','104호');
INSERT INTO stdclubtbl VALUES
(NULL,'김범수','바둑'),(NULL,'김범수','축구'),(NULL,'조용필','축구'),
(NULL,'은지원','축구'),(NULL,'은지원','봉사'),(NULL,'바비킴','봉사');
-- INNER JOIN
SELECT s.stdName,s.addr,c.clubName,t.roomNo
FROM stdtbl AS s
JOIN stdclubtbl AS c
ON s.stdName=c.stdName
JOIN clubtbl AS t
ON c.clubName = t.clubName;
