자주 쓰는 MariaDB, MySQL 쿼리 정리

2023-05-30

MySQL DDL 쿼리

테이블 기본키 추가

ALTER TABLE 테이블명 ADD PRIMARY KEY (`컬럼1`, `컬럼2`);

테이블 기본키 삭제

ALTER TABLE 테이블명 DROP PRIMARY KEY;

테이블 외래키 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조테이블명(참조컬럼명) ON DELETE CASCADE ON UPDATE CASCADE;

ON DELETE CASCADE 옵션은 참조테이블의 참조컬럼 값이 삭제되면, 참조하던 ROW들을 자동 삭제해 줍니다.

외래키 추가 시 에러메세지

Cannot add or update a child row: a foreign key constraint fails

외래키로 지정한 컬럼의 값으로 참조테이블 컬럼에 없는 값이 저장되어 있으면 외래키를 추가할 수 없습니다.
전체 INSERT문 추출하고 외래키 지정 후 제약조건에 맞는 INSERT문들만 실행하면 됩니다.

테이블 외래키 삭제

ALTER TABLE 테이블명 DROP FOREIGN KEY 제약조건명;

테이블 컬럼 추가

ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입;

특정 컬럼 뒤 추가

ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입 AFTER 특정컬럼명;

테이블 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

MySQL DQL 쿼리

INNER JOIN

SELECT * FROM 테이블1 A
INNER JOIN 테이블2 B ON A.키 = B.폴인키

조인 조건을 만족하는 테이블 A, 테이블 B 행들을 결합합니다.
게시판 댓글을 조회할 때 이용하면 좋습니다.

LEFT OUTER JOIN (=LEFT JOIN)

SELECT * FROM 테이블1 A
LEFT OUTER JOIN 테이블2 B ON A.키 = B.폴인키

왼쪽 테이블을 기준으로 우측 테이블 컬럼들을 조인합니다.
테이블1에 2건, 테이블2에 5건 있으면 총 5건의 결과가 나옵니다.

LEFT JOIN 그룹화

SELECT * FROM 테이블1 A
LEFT OUTER JOIN 테이블2 B ON A.키 = B.폴인키
GROUP BY A.키

테이블1에 2건, 테이블2에 5건 있으면 테이블1의 키컬럼 값을 그룹화하여 총 2건의 결과가 나옵니다.
테이블2의 컬럼 값은 무작위로 선택되어 나타나며, MIN 또는 MAX 함수로 제어할 수 있습니다.

전체 테이블 row 수 확인

SELECT 
	table_name, 
	table_rows
FROM
	information_schema.tables
WHERE
	table_schema = 'DB명'
AND
	table_rows > 0
ORDER BY table_name;

숫자형 캐스팅

SELECT * FROM 테이블명
WHERE 컬럼명 = CAST(#{파라미터명} AS UNSIGNED)

CAST 함수로 파라미터를 숫자 변환 후, 컬럼 값과 일치하면 조회합니다.

특정 컬럼 데이터 순으로 정렬

ORDER BY FIELD (컬럼명, "1순위", "2순위", "3순위")

조건에 따른 값 출력

SELECT
	CASE
		WHEN 컬럼명='조건1' THEN '값1'
		WHEN 컬럼명='조건2' THEN '값2'
		ELSE '값3'
	END AS '출력컬럼명'
FROM 테이블명

JSON 값 추출

SELECT
	컬럼명 ->> '$."JSON키"', -- JSON 값에서 양 옆의 ""를 제거하고 출력
	json_extract(컬럼명, '$.JSON키'), -- JSON 값 그대로 출력
	json_value(컬럼명, '$.JSON키') -- JSON 값 문자열 변환하여 출력
FROM
	테이블명

JOIN으로 조회된 행 DELETE

DELTE    
	a
FROM
	테이블1 a
INNER JOIN
	테이블2 b
ON
	b.키열 = a.키열
WHERE
	b.열 = '값'

DELETE문 실행 전에는 항상 FROM 이하 구문을 SELECT 해서 어떤 데이터를 삭제하는지 확인해야 합니다.

데이터 INSERT문

INSERT INTO 테이블명 (
	컬럼명1,
	컬럼명2
) VALUES (
	'값1',
	'값2'
);

INSERT INTO DB명.테이블명 시 에러

SQL Error [1142] [42000]: (conn=1043447) INSERT command denied to user 'DB명'@'10.0.0.4' for table '테이블명'

개발 DB명과 운영 DB명이 다른 경우, 쿼리에 DB명이 들어가면 위와 같은 에러가 날 수 있습니다.
쿼리에서 DB명을 제거하고, ‘INSERT INTO 테이블명’으로 실행하면 됩니다.

MySQL DML 쿼리

UPDATE 문

UPDATE 테이블명
SET 컬럼명 = '변경값'
WHERE 컬럼명 = '값'

조회 조건에 해당하는 row에 대해 컬럼 값을 변경합니다.