[데이터베이스] CRUD

 

테이블을 두개 만들어 Foreign Key를 이용해 데이터를 연결 시킬 것이다.

 

레퍼런스가 될 buyTbl2의 groupName이 설정이 되어 있지 않다.

 

코드로 해결 할 수 있지만 셋팅으로 바꿔보자

 

 

 

 

Revert를 눌러서 돌아오자

 

groupName을 추가하고 apply

 

 

그후 database -reverse -sqldb선택

 

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;