USE sqlDB;
SELECT * FROM usertbl;
INSERT INTO usertbl VALUES ('HGD', '홍길동', 1900, '서울', NULL, NULL, 181, '2024-05-28');
DESC usertbl;
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(id, userName, age) Values (2, '설현', 26);
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(userName,age) VALUES ('유나', 22);
INSERT INTO testTbl2 VALUES(NULL, '유정', 21);
SELECT * FROM testTbl2;
ALTER TABLE testTbl2 AUTO_INCREMENT = 100;
INSERT INTO testTBL2 VALUES (NULL, '찬미', 23);
SELECT * FROM testTBL2;
INSERT INTO testTBL2 VALUES (NULL, '유진' ,24);
SELECT * FROM testTBL2; -- 유진은 기본값이 id가 100으로 수정되어서 +1인 101로 자동지정된다.
CREATE TABLE testTbl3(
id int AUTO_INCREMENT PRIMARY KEY,
userName char(3),
age int);
ALTER TABLE testTbl3 AUTO_INCREMENT 1000;
SET @@auto_increment_increment = 3; -- AUTO_INCREMENT의 증가를 3씩 설정
INSERT INTO testTbl3 VALUES(NULL, '나연', 20);
INSERT INTO testTbl3 VALUES(NULL, '정연', 18);
INSERT INTO testTbl3 VALUES(NULL, '모모', 19);
SELECT * FROM testTbl3;
-- 마지막 id 가져오기
SELECT last_insert_id();
-- 최대 id 가져오기
SELECT MAX(id) as 'last_insert_id' FROM testTbl3;
-- 한꺼번에 INSERT
INSERT INTO testTbl3 VALUES (NULL, '꺽정', 55), (NULL, '길산', 45);
SELECT * FROM testTbl3;
-- 대량의 샘플 데이터 생성
USE employees;
DESC employees;
USE sqlDB;
CREATE TABLE testTbl4 (id int, Fname VARCHAR(50), Lname varchar(50));
INSERT INTO testTbl4
SELECT emp_no, first_name, last_name -- 여러 개의 데이터타입 중 가지고 오고 싶은 3개 추출
FROM employees.employees; -- 서브쿼리
SELECT * FROM testTbl4;
-- 데이터의 수정 : UPDATE
-- WHERE 절은 생략가능하지만 모든 열이 바뀔 수 있다.
SELECT * FROM testTbl4 where Fname = 'Kyoichi'; -- 확인을 먼저 한다
UPDATE testTbl4
SET Lname = '없음'
WHERE Fname ='Kyoichi';
-- SQL 모드 중 하나인 "safe-updates" 모드를 해제하는 명령
SET SQL_SAFE_UPDATES = 0;
SELECT * FROM testTbl4;
-- 'Kyoichi' 정보를 복구하자
desc employees.employees;
SELECT emp_no, first_name, last_name From employees.employees
WHERE first_name = 'Kyoichi';
SELECT first_name FROM userTbl4 WHERE first_name = 'KYOICHI';
UPDATE testTbl4
SET Lname = (SELECT last_name
FROM employees.employees
WHERE first_name = 'Kyoichi'
AND employees.emp_no = testTbl4.id)
WHERE Fname = 'Kyoichi';
SELECT * FROM testTbl4
WHERE Fname = 'Kyoichi';
-- 가격을 인상시킨 테이블을 사용하자
USE sqldb;
SELECT * FROM buyTbl;
UPDATE buyTbl2 SET price = price * 1.5;
SELECT * FROM buyTbl2;
DESC buyTbl2;
DESC employees.employees;
CREATE TABLE increaseTbl (
id INT AUTO_INCREMENT PRIMARY KEY,
groupName CHAR(6) NOT NULL,
val FLOAT NOT NULL DEFAULT 1
);
/* => 컬럼의 값을 바꾸고 싶다면
ALTER TABLE increaseTbL
ALTER COLUMN VAL SET DEFAULT 1;
*/
DESC increaseTbl;
SELECT DISTINCT groupName FROM buytbl2;
INSERT INTO increaseTbl(groupName,val)
values ('전자', 1.2), ('의류', 1.3), ('서적', 1.4);
SELECT * FROM increaseTbl;
SELECT * FROM buyTbl2;
-- 모든 groupName이 전자에 해당하는 아이템을들 찾아서 price를
-- increaseTbl에 있는 전자에 해당하는 인상률 만큼 올려준다
SELECT * FROM buyTbl2
WHERE groupName = '전자';
UPDATE buyTbl2
SET price = price * (SELECT val FROM increaseTbl WHERE groupName = '전자')
WHERE groupName = '전자';
SELECT * FROM buyTbl2;
-- DELETE
SELECT * FROM testTbl4 WHERE Fname = 'Aamer';
DELETE FROM testTbl4 WHERE Fname = 'Aamer' LIMIT 5;
-- 30만건 복사
CREATE TABLE bigTbl1 (SELECT * FROM employees.employees);
CREATE TABLE bigTbl2 (SELECT * FROM employees.employees);
CREATE TABLE bigTbl3 (SELECT * FROM employees.employees);
-- 삭제(속도 확인하기)
DELETE FROM bigTbl1;
DROP TABLE bigTbl2; -- 전부 지울 때
TRUNCATE TABLE bigTbl3; -- 테이블 구조를 남길 때
-- INSERT 문의 확장 기능 중 하나인 "ON DUPLICATE KEY UPDATE"를 사용
CREATE TABLE memberTBL (SELECT userID, name, addr FROM usertbl LIMIT 3); -- 3건만 가져옴
ALTER TABLE memberTBL
ADD CONSTRAINT pk_memberTBL PRIMARY KEY (userID); -- PK 지정
SELECT * FROM memberTBL;
INSERT INTO memberTBL VALUES ('BBK', '비비코' , '미국');
INSERT INTO memberTBL VALUES ('SJH', '서장훈' , '서울');
INSERT INTO memberTBL VALUES ('HJY', '현주엽', '경기');
SELECT * FROM memberTbl;
INSERT IGNORE INTO memberTBL VALUES ('BBK', '비비코' , '미국');
INSERT IGNORE INTO memberTBL VALUES ('SJH', '서장훈' , '서울');
INSERT IGNORE INTO memberTBL VALUES ('HJY', '현주엽', '경기');
SELECT * FROM memberTbl;
INSERT INTO memberTBL VALUES('BBK', '비비코', '미국')
ON DUPLICATE KEY UPDATE name = '비비코', addr = '미국';
INSERT INTO memberTBL VALUES('DJW', '동짜몽', '일본')
ON DUPLICATE KEY UPDATE name = '동짜몽', addr = '일본';
SELECT * FROM memberTbl;
SELECT userID AS '사용자', SUM(price*amount) AS '총 구매액'
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;
SELECT addr, MAX(height) FROM usertbl GROUP BY addr;
WITH cte_userTBL(addr, maxHeight) AS (
SELECT addr, MAX(height) AS maxHeight
FROM usertbl
GROUP BY addr
)
SELECT * FROM cte_userTBL;
WITH cte_userTBL(addr, maxHeight) AS (
SELECT addr, MAX(height) AS maxHeight
FROM usertbl
GROUP BY addr
)
SELECT AVG(maxHeight * 1.0) FROM cte_userTBL;
select * from usertbl;
select addr, Max(height) as maxheight from usertbl
group by addr;
-- 첫 번째 방법: 서브쿼리를 사용하여 주소별 최대 키를 먼저 계산한 후에 평균을 구합니다.
SELECT AVG(maxheight) FROM (
SELECT addr, MAX(height) AS maxheight FROM usertbl GROUP BY addr
) AS subquery;
-- 두 번째 방법: `GROUP BY`를 사용하여 주소별 최대 키를 먼저 계산한 후에 평균을 구합니다.
SELECT AVG(maxheight) FROM (
SELECT addr, MAX(height) AS maxheight FROM usertbl GROUP BY addr
) AS subquery;
