SQL
-- DDL DCL DML
-- DML -> select insert update delete
-- DDL -> create drop alter
-- DCL -> grant revoke
-- DDL
CREATE TABLE testTbl3
(id int AUTO_INCREMENT PRIMARY KEY,
userName char(3),
age int);
-- DDL
ALTER TABLE testTbl3 AUTO_INCREMENT=1000; -- 시작 인크리먼트
-- 변수 선언
SET @@auto_increment_increment=3; -- 3씩 증가
-- DML
INSERT INTO testTbl3 VALUES (NULL, '나연', 20);
INSERT INTO testTbl3 VALUES (NULL, '정연', 18);
INSERT INTO testTbl3 VALUES (NULL, '모모', 19);
SELECT*FROM testTbl3;
--
CREATE TABLE testTbl4 (id int, Fname varchar(50), Lname varchar(50));
-- employees.employees 테이블 데이터를 testTbl4에 복사
INSERT INTO testTbl4
SELECT emp_no, first_name, last_name
FROM employees.employees;
select*from testTbl4;
CREATE TABLE testTbl5
(SELECT emp_no, first_name, last_name FROM employees.employees);
select*from testTbl5;
--
select*from testTbl4 WHERE Fname = 'Kyoichi';
-- Lname -> 없음 Fname에서 Kyoichi인 사람만
UPDATE testTbl4
set Lname = '없음'
where Fname = 'Kyoichi';
--
select*from buytbl;
-- 물가 인상 가격 -> 50% 인상
UPDATE buytbl
set price = price * 1.5;
--
CREATE TABLE bigTbl1 (SELECTFROM employees.employees);
CREATE TABLE bigTbl2 (SELECTFROM employees.employees);
CREATE TABLE bigTbl3 (SELECT*FROM employees.employees);
DELETE FROM bigTbl1; -- 테이블 데이터 내용 모두 삭제
DROP TABLE bigTbl2; -- 테이블 자체가 지워진다.
TRUNCATE TABLE bigTbl3; -- 테이블 데이터 내용 모두 삭제
--
DELETE FROM testTbl4 WHERE Fname = 'Aamer' LIMIT 5;
DELETE FROM testTbl4 WHERE Fname = 'Aamer';
CREATE TABLE memberTBL (SELECT userID, name, addr FROM usertbl LIMIT 3); -- 3건만 가져옴
-- ALTER 수정 ADD CONSTRAINT(추가) -> 기본키 추가
ALTER TABLE memberTBL
ADD CONSTRAINT pk_memberTBL PRIMARY KEY (userID); -- PK를 지정함
SELECT*FROM memberTBL;
--
SELECT*FROM memberTBL;
INSERT INTO memberTBL VALUES('BBK', '비비코', '미국');
INSERT IGNORE INTO memberTBL VALUES('BBK', '비비코', '미국');
-- 없으면 추가하고 있으면 수정해
INSERT INTO memberTBL VALUES('BBK', '비비코', '미국')
ON DUPLICATE KEY UPDATE name='비비코', addr='미국';
--
-- 임시 테이블
WITH abc(userid, total)
AS
(SELECT userid, SUM(priceamount)
FROM buyTBL GROUP BY userid)
SELECTFROM abc ORDER BY total DESC;
--
SELECT CAST('2020-10-19 12:35:29.123' AS DATE) AS 'DATE';
SELECT CAST('2020-10-19 12:35:29.123' AS TIME) AS 'TIME';
SELECT CAST('2020-10-19 12:35:29.123' AS DATETIME) AS 'DATETIME';
SET @myVar1 = 3; -- 변수선언
PREPARE myQuery
FROM 'SELECT Name, height FROM usertbl ORDER BY height LIMIT ?';
EXECUTE myQuery USING @myVar1;
SELECT '100' + '200' ; -- 문자와 문자를 더함 (정수로 변환돼서 연산됨)
SELECT CONCAT('100','200'); -- 문자와 문자를 연결 (문자로 처리)
SELECT CONCAT(100,'200'); -- 정수와 문자를 연결 (정수가 문자로 변환돼서 처리)
SELECT 1 > '2mega'; -- 정수인 2로 변한되어서 비교
SELECT 3 > '2MEGA'; -- 정수인 2로 변환되어서 비교
SELECT 0 > 'mega2'; -- 문자는 0으로 변환
SELECT IF(100 > 200, '참이다', '거짓이다');
--
-- 수식1이 null 이 아니면 수식 1이 반환 수식1이 null이면 수식 2가 반환
SELECT IFNULL(NULL, '널이군요'), IFNULL(100, '널이군요');
-- 수식 1과 수식 2가 같으면 NULL 반환 다르면 수식 1 반환
SELECT NULLIF(100,100), NULLIF(200,100);
SELECT CASE 10
WHEN 1 THEN '일'
WHEN 5 THEN '오'
WHEN 10 THEN '십'
ELSE '모름'
END AS 'CASE연습';
--
-- 문자열 왼쪽 3글자, 오른쪽 3글자 추출
SELECT LEFT('abcdefghi', 3), RIGHT('abcdefghi', 3);
-- 왼쪽/오른쪽에 지정 문자 추가해서 길이 맞춤
SELECT LPAD('이것이', 5, '##'), RPAD('이것이', 5, '##');
-- 왼쪽/오른쪽 공백 제거
SELECT LTRIM(' 이것이'), RTRIM('이것이 ');
-- 양쪽 공백 제거, 양쪽의 'ㅋ' 문자 제거
SELECT TRIM(' 이것이 '), TRIM(BOTH 'ㅋ' FROM 'ㅋㅋㅋ재밌어요.ㅋㅋㅋ');
-- 문자열 연결 (SPACE는 공백 10개 생성)
SELECT CONCAT('이것이', SPACE(10), 'MySQL이다');
-- 지정 위치부터 문자열 추출 (3번째부터 2글자)
SELECT SUBSTRING('대한민국만세', 3, 2);
-- 날짜에 31일 추가, 1개월 추가
SELECT ADDDATE('2025-01-01', INTERVAL 31 DAY),
ADDDATE('2025-01-01', INTERVAL 1 MONTH);
-- 날짜에 31일 추가, 1개월 빼기
SELECT ADDDATE('2025-01-01', INTERVAL 31 DAY),
SUBDATE('2025-01-01', INTERVAL 1 MONTH);
-- 시간 더하기 (날짜 포함/시간만)
SELECT ADDTIME('2025-01-01 23:59:59', '1:1:1'),
ADDTIME('15:00:00', '2:10:10');
-- 시간 더하기와 빼기
SELECT ADDTIME('2025-01-01 23:59:59', '1:1:1'),
SUBTIME('15:00:00', '2:10:10');
-- 현재 날짜의 년, 월, 일 추출
SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAYOFMONTH(CURDATE());
-- 현재 시간의 시, 분, 초, 마이크로초 추출
SELECT HOUR(CURTIME()),
MINUTE(CURRENT_TIME()),
SECOND(CURRENT_TIME()),
MICROSECOND(CURRENT_TIME());
-- 현재 날짜와 시간에서 날짜/시간만 추출
SELECT DATE(NOW()), TIME(NOW());
-- 두 날짜 차이(일), 두 시간 차이 계산
SELECT DATEDIFF('2025-01-01', NOW()),
TIMEDIFF('23:23:59', '12:11:10');
-- 요일 번호, 월 이름, 올해 몇 번째 날인지 출력
SELECT DAYOFWEEK(CURDATE()),
MONTHNAME(CURDATE()),
DAYOFYEAR(CURDATE());
-- 해당 날짜의 마지막 날짜 반환
SELECT LAST_DAY('2025-02-01');
-- 해당 연도의 몇 번째 날로 날짜 생성
SELECT MAKEDATE(2025,32);
-- 시/분/초로 시간 생성
SELECT MAKETIME(12,11,10);
-- 월 단위 계산 (추가, 차이)
SELECT PERIOD_ADD(202501, 11),
PERIOD_DIFF(202501, 202312);
--
CREATE TABLE pivotTest
(uName CHAR(3),
season CHAR(2),
amount INT);
INSERT INTO pivotTest VALUES
('김범수', '겨울', 10), ('윤종신', '여름', 15), ('김범수', '가을', 25),
('김범수', '봄', 3), ('김범수', '봄', 37), ('윤종신', '겨울', 40),
('김범수', '여름', 14), ('윤종신', '겨울', 22), ('윤종신', '여름', 64);
SELECT*FROM pivotTest;
-- 피벗
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;
SELECT season,
sum(IF(uName='김범수', amount, 0)) AS '김범수',
sum(IF(uName='윤종신', amount, 0)) AS '윤종신',
SUM(amount) AS '합계' FROM pivotTest group by season;
--
SELECT *
FROM buytbl
INNER JOIN usertbl
ON buytbl.userID = usertbl.userID
WHERE buytbl.userID = 'JYP';
SELECT *
FROM buytbl
INNER JOIN usertbl
ON buytbl.userID = usertbl.userID
ORDER BY num;
SELECT buytbl.userID, name, prodName, addr, mobile1 + mobile2 AS '연락처'
FROM buytbl
INNER JOIN usertbl
ON buytbl.userID = usertbl.userID
ORDER BY num;
SELECT buytbl.userID, name, prodName, addr, mobile1 + mobile2 AS '연락처'
FROM buytbl, usertbl
WHERE buytbl.userID = usertbl.userID
ORDER BY num;
SELECT U.userID, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS '연락처'
FROM usertbl U
INNER JOIN buytbl B
ON U.userID = B.userID
WHERE B.userID = 'JYP';
--
SELECT DISTINCT U.userID, U.name, U.addr
FROM usertbl U
INNER JOIN buytbl B
ON U.userID = B.userID
ORDER BY U.userID;
SELECT U.userID, U.name, U.addr
FROM usertbl U
WHERE EXISTS (
SELECT *
FROM buytbl B
WHERE U.userID = B.userID );
--
CREATE TABLE stdtbl
(stdName VARCHAR(10) NOT NULL PRIMARY KEY,
addr CHAR(4) NOT NULL);
CREATE TABLE clubtbl
(clubName VARCHAR(10) NOT NULL PRIMARY KEY,
roomNo CHAR(4) NOT NULL);
CREATE TABLE stdclubtbl
(num int AUTO_INCREMENT NOT NULL PRIMARY KEY,
stdName VARCHAR(10) NOT NULL,
clubName VARCHAR(10) NOT NULL,
FOREIGN KEY(stdName) REFERENCES stdtbl(stdName),
FOREIGN KEY(clubName) REFERENCES clubtbl(clubName));
INSERT INTO stdtbl VALUES('김범수', '경남'), ('성시경', '서울'),
('조용필', '경기'), ('은지원', '경북'), ('바비킴', '서울');
INSERT INTO clubtbl VALUES('수영', '101호'), ('바둑', '102호'), ('축구', '103호'), ('봉사', '104호');
INSERT INTO stdclubtbl VALUES(NULL, '김범수', '바둑'), (NULL, '김범수', '축구'),
(NULL, '조용필', '축구'), (NULL, '은지원', '축구'), (NULL, '은지원', '봉사'), (NULL, '바비킴', '봉사');
selectfrom stdtbl;
selectfrom clubtbl;
select*from stdclubtbl;
SELECT S.stdName, S.addr, SC.clubName, C.roomNo
FROM stdtbl S
INNER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
INNER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY S.stdName;
SELECT C.clubName, C.roomNo, S.stdName, S.addr
FROM stdtbl S
INNER JOIN stdclubtbl SC
ON SC.stdName = S.stdName
INNER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY C.clubName;
-- 동아리가 축구부인 사람과 클럽 이름 룸 번호가 나오게 만들어라.
SELECT C.clubName, C.roomNo, S.stdName
FROM stdtbl S
INNER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
INNER JOIN clubtbl C
ON SC.clubName = C.clubName
WHERE C.clubName = '축구'
ORDER BY C.clubName;
--
SELECT U.userID, U.name, B.prodName,
U.addr, CONCAT(U.mobile1, u.mobile2) AS '연락처'
FROM usertbl U -- 왼쪽
LEFT OUTER JOIN buytbl B -- 오른쪽
On U.userID = B.userID
ORDER BY U.userID;
SELECT U.userID, U.name, B.prodName, U.addr,
CONCAT(U.mobile1, u.mobile2) AS '연락처'
FROM buytbl B -- 왼쪽
LEFT OUTER JOIN usertbl U -- 오른쪽
ON U.userID = B.userID
ORDER BY U.userID;
--
SELECT U.userID, U.name, B.prodName,
U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buytbl B -- 왼쪽
right OUTER JOIN usertbl U -- 오른쪽
ON U.userID = B.userID
ORDER BY U.serID;
--
SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY S.stdName;
SELECT C.clubName, S.roomNo, S.stdName, S.addr
FROM stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON SC.stdName = S.stdName
RIGHT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY C.clubName;
--
SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName
union
SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON SC.stdName = S.stdName
RIGHT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName;
SELECT*FROM buytbl
CROSS JOIN usertbl;
--
CREATE TABLE empTbl (emp CHAR(3), manager CHAR(3), empTel VARCHAR(8));
INSERT INTO empTbl VALUES('나사장',NULL,'0000');
INSERT INTO empTbl VALUES('김재무','나사장','2222');
INSERT INTO empTbl VALUES('김부장','김재무','2222-1');
INSERT INTO empTbl VALUES('이부장','김재무','2222-2');
INSERT INTO empTbl VALUES('우대리','이부장','2222-2-1');
INSERT INTO empTbl VALUES('지사원','이부장','2222-2-2');
INSERT INTO empTbl VALUES('이영업','나사장','1111');
INSERT INTO empTbl VALUES('한과장','이영업','1111-1');
INSERT INTO empTbl VALUES('최정보','나사장','3333');
INSERT INTO empTbl VALUES('윤차장','최정보','3333-1');
INSERT INTO empTbl VALUES('이주임','윤차장','3333-1-1');
SELECT*FROM empTbl;
-- 문제
SELECT A.emp AS '부하직원', B.manager AS '직속상관', B.emptel AS '직속상관 연락처'
FROM empTbl A
INNER JOIN empTbl B
ON A.manager = B.emp
WHERE A.emp = '우대리';
SELECTFROM usertbl;
SELECTFROM buytbl;
-- 문제
SELECT U.userID,
U.name,
SUM(price amount) AS '총 구매액',
IF(SUM(price amount) >= 1500, '최우수 고객',
IF(SUM(price amount) >= 1000, '우수 고객',
IF(SUM(price amount) >= 1, '일반고객', '유령고객'))) AS '고객 등급'
FROM buytbl B
RIGHT OUTER JOIN usertbl U
ON B.userID = U.userID
GROUP BY U.userID, U.name
ORDER BY SUM(price * amount) DESC;
마무리
오늘도 SQL을 나갔다.
마무리하고 내일부턴 스프링부트를 활용해 쇼핑몰을 만들기로 했다.
재미있을 거 같다.
