MySQL 기본: SQL 고급

MySQL의 데이터 형식

MySQL에서 지원하는 데이터 형식의 종류

숫자 데이터 형식

'

문자 데이터 형식

날짜와 시간 데이터 형식

기타 데이터 형식

변수의 사용

SET @변수이름 = 변수의 값; // 변수 선언 및 값 대입
SELECT @변수이름; // 변수의 값 출력
SET @myVar1 = 5;
SELECT @myVar1, Name FROM users WHERE height > 180; //이런식으로 사용 가능

데이터 형식과 형 변환

SELECT AVG(amount) AS '평균 구매 개수' FROM buys;

SELECT CAST(AVG(amount) AS SIGNED INTEGER) AS '평균 구매 개수' FROM buys;
SELECT CONVERT(AVG(amount), SIGNED INTEGER) AS '평균 구매 개수' FROM buys;
//이런식으로 반올림하는 형변환을 할 수 있다.

MySQL 내장 함수

SELECT ASCII('A'), CHAR(65); //문자의 아스키 코드값을 돌려주거나 숫자의 아스키 코드 값에 해당하는 문자를 돌려준다.

SELECT BIT_LENGTH('abc'), CHAR_LENGTH('abc'), LENGTH('abc');
SELECT BIT_LENGTH('가나다'), CHAR_LENGTH('가나다'), LENGTH('가나다');
// 할당된 비트 크기 또는 문자 크기를 반환한다.

SELECT CONCAT_WS('/', '2025', '01', '01'); //구분자 '/'를 추가해서 2025/01/01을 반환

SELECT FORMAT(123456.123456, 4); //1000단위마다 ,를 표시해주며 소숫점 아래 자릿수까지 표현 123,456.1235 반환

SELECT BIN(31), HEX(31), OCT(31) 2진수, 16진수, 8진수 값을 반환 1111, 1F, 37을 반환한다.

SELECT LEFT('abcdefghi', 3), RIGHT('abcdefghi', 3); //abc, ghi 반환

SELECT LOWER('abcDEFGH'), UPPER('abDEFGH') //대문자, 소문자로 변환 ABCDEFGH, abcdefgh 반환

SELECT LPAD('이것이', 5, '##"), RPAD('이것이', 5, '##') 
//문자열을 길이만큼 늘린 후에, 빈 곳을 채울 문자열로 채운다. '##이것이' 와 '이것이##' 반환

SELECT LTRIM('  이것이'), RTRIM('이것이  ') // 왼쪽 공백, 오른쪽 공백 제거 둘 다 이것이를 반환.

SELCT TRIM (' 이것이 ') // 공백제거 이것이 반환

SELECT REPEAT('이것이', 3); // 문자열을 횟수만큼 반복 이것이이것이이것이 반환

SELECT REPLACE ('이것이 MySQL이다', '이것이', 'This is'); 문자열에서 원래 문자열을 찾아서 바꿀 문자열로 변환 This is MySQL이다 반환

SELECT REVERSE ('MySQL'); //문자열의 순서를 거꾸로 만든다.

SELECT CONCAT('이것이', SPACE(10), 'MySQL이다'); //길이만큼 공백 반환

SELECT SUBSTRING('대한민국만세', 3, 2); 시작점부터 길이만큼 반환 '민국' 반환

SELECT ABS(-100) // 절댓값 반환

SELECT CEILING(4.7), FLOOR(4.7), ROUND(4.7) // 각각 올림, 내림, 반올림

SELECT MOD(157, 10) // 나머지 연산

SELECT POW(2, 3), SQRT(9) // 제곱 연산, 제곱근 연산 8, 3 반환

SELECT RAND() //0이상 1 미만의 실수

SELECT SIGN(100), SIGN(0), SIGN(-100.123)  // 음수인지 양수인지 0인지 판단 1, 0, -1 반환

SELECT TRUNCATE(12345.12345, 2), TRUNCATE(12345, 12345, -2) //정수위치까지 구한 후 나머지는 버린다. 12345.12와 12300 반환

CURDATE(), CURTIME(), NOW(), SYSDATE() //각각 현재의 연-월-일, 시-분-초, 연-월-일-시-분-초 NOW()와 SYSDATE()는 같음

조인

INNER JOIN(내부 조인)

조인 중에서 제일 많이 사용되는 조인으로 일반적으로 조인이라고 이야기하면 INNNER JOIN을 의미한다.

SELECT * FROM buys INNER JOIN users ON buys.userId = users.userId WHERE buys.userId = 'JYP';

OUTER JOIN(외부 조인)

조건에 만족되지 않는 행도 포함시키는 것

SELECT U.userId, U.name, B.prodName, U.addr FROM users U LEFT OUTER JOIN buys B ON U.userId = B.userId;

SELECT U.userId, U.name, B.prodName, U.addr FROM buys B RIGHT OUTER JOIN users U ON U.userId = B.userId;

CROSS JOIN(상호 조인)

양쪽 테이블의 모든 행을 조인하는 기능

select * from buys cross join users;

SELF JOIN(자체 조인)

자기 자신과 조인하는 기능. 하나의 테이블에 같은 데이터가 존재하되 의미는 다르게 존재하는 경우 사용

select a.emp as '부하직원', b.emp as '직소상관', b.empTel as '직속상관연락처' from emps a inner join emps b on a.manager = b.emp where a.emp = '우대리'

UNION / UNION ALL / NOT IN / IN

select stdName, addr from stdtbl union all select clubName, roomNo from clubtbl; //select 문장 1과 2의 결과 열의 개수가 같고, 데이터 형식도 각 열 단위로 호환되어야 한다. union all 옵션은 중복된 열까지 모두 출력한다.

select name from usertbl where name not in (select name from usertbl where mobile1 is null); // 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문

select name from usertbl where name in (select name from usertbl where mobile1 is null); // 두 번째 커리에 해당하는 것만 조회하기 위한 구문

SQL 프로그래밍

동적 SQL

PREPARE myQuery FROM 'SELECT * FROM usertbl WHERE userId = "EJW"';
EXCUTE myQuery;
DEALLOCATE PREPARE myQuery;

PREPARE myQuery FROM 'INSERT INTO myTable VALUES(NULL, ?)';//?로 비워놓고 EXECUTE 시에 값을 할당 할 수 있음.
EXECUTE myQuery USING @curDATE;
DEALLOCATE PREPARE myQuery;

위와 같이 미리 쿼리문을 준비한 후 나중이 실행하는 것을 동적 쿼리라고 한다.