<참조> http://www.tcpschool.com/mysql/
MySQL 쿼리 기능 요약
자세한 내용은 위 참조 링크에 있습니다.
데이터 타입
----------------------------------------------------------------------------
TINYINT 1바이트
SMALLINT 2바이트
MEDIUMINT 3바이트
INT 4바이트 INT(11) : 11자리 정수
UNSIGNED 4바이트
BIGINT 8바이트
FLOAT(p) 실수, p 소수점 비트수(0~24: FLOAT, 25~53: DOUBLE)
FLOAT(n, m) 실수 4바이트
DOUBLE(n, m) 실수 8바이트
DECIMAL(n, m) 전체 길이, 소수점 자리 수 DECIMAL(7,3) -99999.99 ~ 99999.99
CHAR(n) 고정 길이 n 문자열 (0~255)
VARCHAR(n) n은 문자열 최대 길이 (0~65535)
BINARY(M) 이진 데이터 저장
VARBINARY(M) 이진 데이터 저장
BIT(m) m은 비트 길이, 이진 데이터 저장 (짧으면 앞에 0 채움)
비트 형태 출력시 MySQL BIN( ) 함수 이용
BLOB Binary Large Object
TINYBLOB, BLO, MEDIUMBLOB, LONGBLOB
TEXT VARCHAR과 비슷하나 기본값 없음,
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
ENUM('남성', '여성', '미성년자') 지정 값 중에 하나만 사용 가능
SET('콜라', '사이다', '쥬스', '생수') 지정 값 중에서 여러 개 사용 가능
TIME '-838:59:59' ~ '838:59:59'
DATE 'YYYY-MM-DD' (1000-01-01 ~ 9999-12-31) 오류시 0 저장
DATETIME 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP '197--01-01 00:00:01'UTC ~ '2038-01-19 03:14:07'UTC
YEAR(4) MySQL 5.7.5부터 YEAR(2)는 지원 안함
----------------------------------------------------------------------------
CONCAT(..., ...) : 여러 문자열이나 컬럼 값을 결합한다.
IFNULL(컬럼 , 널대체값) : 컬럼 값이 NULL인 경우 대체 값을 지정
이것도 가능 --> IF(IS NULL(컬럼), 대체값, 컬럼) : 널인 경우 대체 값 사용
CASE
WHEN 조건식1 THEN 식1
WHEN 조건식2 THEN 식2
ELSE 식n
END
CASE
WHEN 컬럼 IS NULL THEN "None"
ELSE 컬럼
END
COALESCE : 지정한 표현식들 중에 NULL이 아닌 첫 번째 값 반환, 모두 NULL이면 NULL 반환
// NULL 처리 상황
SELECT COALESCE(컬럼명1, NULL일 때 대체할 값)
FROM 테이블
// 배타적 OR 관계 열
// 컬럼1 ~ 4 중 NULL 아닌 첫 Column 출력
SELECT COALESCE(컬럼명1, 컬럼명2, 컬럼명3, 컬럼명4)
FROM 테이블
--------------------------------------------------------------------------------------
산술연산자: +, -, *, /, DIV(정수나누기), % 또는 MOD
대입연산자: =, := (확실한 대입연산자)
비교연산자: =, !=(<>), <, <=, >, >=,
<=> : 모두 NULL이면 참
IS : 왼쪽 피연산자와 오른쪽 피연산자가 같으면 참 반환
(오른쪽 피연산자가 불 값 TRUE, FALSE, UNKNOWN 일 때 사용)
IS NOT : 왼쪽 피연산자와 오른쪽 피연산자가 같지 않으면 참 반환
(오른쪽 피연산자가 불 값 TRUE, FALSE, UNKNOWN 일 때 사용)
IS NULL 피연산자 값이 NULL이면 참 반환
IS NOT NULL 피연산자 값이 NULL이 아니면 참 반환.
BETWEEN min AND max
NOT BETWEEN min AND max
IN() 피연산자 값이 리스트에 존재하면 참 반환. 5 IN (2, 3, 4, 5)
NOT IN() 피연산자 값이 리스트에 존재하지 않으면 참 반환
논리연산자 설명
AND, && 논리식이 모두 참이면 참을 반환
OR, || 논리식 중에서 하나라도 참이면 참을 반환
XOR 논리식이 서로 다르면 참을 반환함.
NOT, ! 논리식 결과가 참이면 거짓, 거짓이면 참을 반환
비트연산자: &, |, ^, ~, <<, >> (이진수 표기: b'1000')
MySQL 연산자 우선순위
-----------------------------------------------------------------------------------
순위 연산자
-----------------------------------------------------------------------------------
1 INTERVAL
2 BINARY, COLLATE
3 !
4 - (단항 연산자), ~ (비트 연산자)
5 ^
6 *, /, DIV, %, MOD
7 - (이항 연산자), +
8 <<, >>
9 &
10 |
11 = (관계 연산자), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
12 BETWEEN, CASE, WHEN, THEN, ELSE
13 NOT
14 AND, &&
15 XOR
16 OR, ||
17 = (대입 연산자), :=
----------------------------------------------------------------------------------
SELECT CASE 0
WHEN 0 THEN 'zero'
WHEN 1 THEN 'one'
ELSE 'more'
END;
SELECT IF(0 < 1, 'yes', 'no'); 조건에 따라 선택
SELECT IFNULL(NULL, '값이 null입니다.'); NULL이면 에러 메시지 선택
SELECT NULLIF(3, 5); 두 값이 같으면 NULL, 아니면 3 반환
패턴 매칭 (와일드카드: %(모두) , _(한개만) )
SELECT * FROM Reservation
WHERE Name LIKE '장%';
SELECT * FROM Reservation
WHERE Name NOT LIKE '장%';
REGEXP 연산자는 정규 표현식 패턴 매칭을 제공
--------------------------------------------------------------------------
패턴 설명
--------------------------------------------------------------------------
. 줄 바꿈 문자(\n)를 제외한 임의의 한 문자를 의미함.
* 해당 문자 패턴이 0번 이상 반복됨.
+ 해당 문자 패턴이 1번 이상 반복됨.
^ 문자열의 처음을 의미함.
$ 문자열의 끝을 의미함.
| 선택을 의미함.(OR)
[...] 괄호([]) 안에 있는 어떠한 문자를 의미함.
[^...] 괄호([]) 안에 있지 않은 어떠한 문자를 의미함.
{n} 반복되는 횟수를 지정함.
{m,n} 반복되는 횟수의 최솟값과 최댓값을 지정함.
-----------------------------------------------------------------------
SELECT * FROM Reservation
WHERE Name REGEXP '^홍|산$'; // 홍으로 시작하거나 산으로 끝나는
SELECT * FROM Reservation
WHERE Name NOT REGEXP '^홍|산$'; // 홍 시작 안하고 산으로 안 끝나는
BINARY 이진문자열 연산자
SELECT BINARY 'a' = 'A', <-- 0 바이너리로 비교하면 바이트 값으로 비교
'a' = 'A'; <-- 1 그냥 비교하면 같은 A로 처리함
-------------------------------------------------------------------------------------
데이터 형변환 CAST()
CAST() 함수는 인수로 전달받은 값을 명시된 타입으로 변환하여 반환.
변환하고자 하는 타입을 AS 절을 이용하여 직접 명시할 수 있다.
형식: CAST(식1 AS 타입1)
AS 절에서 사용할 수 있는 타입은 다음과 같다.
1. BINARY, 2. CHAR, 3. DATE, 4. DATETIME, 5. TIME
6. DECIMAL, 7. JSON (MySQL 5.7.8부터 제공됨)
8. NCHAR, 9. SIGNED [INTEGER], 10. UNSIGNED [INTEGER]
SELECT 4 / '2', <-- 묵시적 형변환 후 정수 나누기
4 / 2, <-- 실수나누기
4 / CAST('2' AS UNSIGNED); <-- 명시적 형변환 후 실수 나누기
CONVERT() 함수 : CAST() 함수처럼 명시된 타입으로 변환 반환.
두 번째 인수로 변환하고자 하는 타입을 직접 전달할 수 있다.
문법
1. CONVERT(식1, 타입1) <-- 타입은 CAST() 함수와 같음
2. CONVERT(식1r USING 문자셋변환방식)
USING 절은 다른 문자셋(character set) 변환을 위해 사용
문자열 데이터의 문자셋을 UTF-8로 변환하는 예제
SELECT CONVERT('abc' USING utf8);
--------------------------------------------------------------------------
ALTER TABLE Reservation ADD Price INT; Price 컬럼 추가
ALTER TABLE Reservation
MODIFY COLUMN RoomNo FLOAT(7,2); RoomNum 컬럼 타입 변경
INSERT INTO Reservation(Note) VALUES('abcd');
MySQL에서 사용할 수 있는 제약 조건
1. NOT NULL : NULL 저장 금지이지만 입력 필수는 아님
2. UNIQUE
3. PRIMARY KEY : 기본키는 한 필드만 가능 (NOT NULL + UNIQUE)
4. FOREIGN KEY : 외래키 대상 테이블 필드는 UNIQUE or PRIMARY KEY
5. DEFAULT
CREATE TABLE Test
(
ID INT NOT NULL, <-- 테이블 생성 시 제약조건 설정
Name VARCHAR(30),
ReserveDate DATE,
RoomNum INT
);
ALTER TABLE Test
MODIFY COLUMN RoomNum INT NOT NULL <-- 제약조건 추가 설정
ALTER TABLE Reservation
ADD CONSTRAINT reservedRoom UNIQUE (RoomNum);
<-- UNIQUE 제약 설정하면, 자동으로 인덱스(INDEX)로 만들어진다.
ALTER TABLE Reservation
DROP INDEX reservedRoom; <-- INDEX 제약 조건 제거
CREATE TABLE Test
(
ID INT PRIMARY KEY,
Name VARCHAR(30),
ReserveDate DATE,
RoomNum INT
);
ALTER TABLE Reservation
DROP PRIMARY KEY; <-- 기본 키 설정 제거
CREATE TABLE Test2
(
ID INT,
ParentID INT,
FOREIGN KEY (ParentID) <-- 외래키 설정 필드
REFERENCES Test1(ID) ON UPDATE CASCADE <-- 외래키 연결 대상
);
ON DELETE : 참조 테이블 값이 삭제된 경우 처리 방식 지정
ON UPDATE: 참조 테이블 값이 갱신된 경우 처리 방식 지정
1. CASCADE: 참조 테이블 데이터 삭제/수정 시, 연결 테이블도 삭제/수정
2. SET NULL: 참조 테이블 데이터 삭제/수정 시, 연결 테이블은 NULL 변경.
3. NO ACTION: 연결 테이블의 데이터는 유지함
4. SET DEFAULT: 참조 테이블 데이터 삭제/수정 시, 연결 테이블은 기본값.
5. RESTRICT: 참조 테이블 데이터 있으면, 연결 테이블 데이터 삭제/수정 금지
CREATE TABLE 테이블이름
(
필드이름 필드타입 DEFAULT 기본값, <-- 생략시 기본값
...
)
CREATE TABLE Test
(
ID INT,
Name VARCHAR(30) DEFAULT 'Anonymous',
ReserveDate DATE,
RoomNum INT
);
ALTER TABLE Reservation
ALTER Name SET DEFAULT 'Anonymous'; <-- 기본값 추가 설정
ALTER TABLE Reservation
ALTER Name DROP DEFAULT; <-- 기본값 제약조건 삭제
---------------------------------------------------------------------
JOIN은 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현한다. JOIN은 보통 SELECT 문과 함께 자주 사용된다.
표준 SQL에서는 레코드를 조합하는 방식에 따라 JOIN을 다음과 같이 구분한다.
1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
INNER JOIN
INNER JOIN은 ON 절과 함께 사용되며, ON 절의 조건을 만족하는 데이터만 가져온다.
1. 첫번째테이블이름
INNER JOIN 두번째테이블이름
ON 조건
2. 첫번째테이블이름
JOIN 두번째테이블이름
ON 조건
ON 절에서는 WHERE 절에서 사용할 수 있는 모든 조건을 사용할 수 있다.
표준 SQL과는 달리 MySQL에서 JOIN, INNER JOIN, CROSS JOIN이 같은 의미이다.
다음 예제는 Reservation 테이블 Name 필드와 Customer 테이블 Name 필드가 일치하는 레코드만 INNER JOIN으로 가져오는 예제다.
SELECT *
FROM Reservation
INNER JOIN Customer
ON Reservation.Name = Customer.Name;
SELECT *
FROM Reservation
JOIN Customer
ON Reservation.Name = Customer.Name;
LEFT JOIN
LEFT JOIN은 첫 번째 테이블을 기준으로, 두 번째 테이블을 조합하는 JOIN이다.
이때 ON 절의 조건을 만족하지 않는 경우에는 첫 번째 테이블 필드 값은 그대로 가져온다.
하지만 해당 레코드의 두 번째 테이블의 필드 값은 모두 NULL로 표시된다.
첫번째테이블이름
LEFT JOIN 두번째테이블이름
ON 조건
ON 절에서는 WHERE 절에서 사용할 수 있는 모든 조건을 사용할 수 있습니다.
다음 예제는 Reservation 테이블의 Name 필드를 기준으로 Customer 테이블의 Name 필드와 일치하는 레코드만을 LEFT JOIN으로 가져온 후, 그 중에서 ReserveDate 필드의 값이 2016년 02월 01일 이후인 레코드만을 선택하는 예제다.
SELECT *
FROM Reservation
LEFT JOIN Customer
ON Reservation.Name = Customer.Name
WHERE ReserveDate > '2016-02-01';
LEFT JOIN
LEFT JOIN은 첫 번째 테이블을 기준으로, 두 번째 테이블을 조합하는 JOIN이다.
이때 ON 절의 조건을 만족하지 않는 경우에는 첫 번째 테이블 필드 값은 그대로 가져온다.
하지만 해당 레코드의 두 번째 테이블의 필드 값은 모두 NULL로 표시됩니다.
첫번째테이블이름
LEFT JOIN 두번째테이블이름
ON 조건
ON 절에서는 WHERE 절에서 사용할 수 있는 모든 조건을 사용할 수 있다.
다음 예제는 Reservation 테이블 Name 필드를 기준으로 Customer 테이블 Name 필드와 일치하는 레코드만을 LEFT JOIN으로 가져온 후, 그 중에서 ReserveDate 필드 값이 2016년 02월 01일 이후인 레코드만을 선택하는 예제다.
SELECT *
FROM Reservation
LEFT JOIN Customer
ON Reservation.Name = Customer.Name
WHERE ReserveDate > '2016-02-01';
UNION
UNION은 여러 SELECT 문 결과를 하나의 테이블이나 결과 집합으로 표현할 때 사용한다.
각각 SELECT 문으로 선택된 필드 개수와 타입은 모두 같아야 하며, 필드 순서도 같아야 한다.
SELECT 필드이름
FROM 테이블이름
UNION
SELECT 필드이름
FROM 테이블이름
다음 예제는 두 SELECT 문 결과를 UNION을 이용하여 하나 테이블로 출력하는 예제다.
SELECT Name
FROM Reservation
UNION
SELECT Name
FROM Customer;
위 예제에서 두 SELECT 문 결과는 하나로 합쳐져 출력된다. 이때 두 SELECT 문 결과에서 중복된 레코드 '홍길동'은 한 번만 표시한다.
UNION ALL
UNION은 DISTINCT 키워드를 따로 명시하지 않아도 중복되는 레코드를 제거한다. 중복되는 레코드까지 모두 출력하고 싶다면, ALL 키워드를 사용한다.
SELECT 필드이름
FROM 테이블이름
UNION ALL
SELECT 필드이름
FROM 테이블이름
다음 예제는 두 SELECT 문 결과를 UNION ALL을 이용하여 한 테이블로 출력하는 예제다.
SELECT Name
FROM Reservation
UNION ALL
SELECT Name
FROM Customer;
위 예제에서 두 SELECT 문 결과는 중복된 레코드까지 모두 표시한다.
---------------------------------------------------------------
서브쿼리란 다른 쿼리 내부에 포함되어 있는 SELETE 문을 의미한다. 서브쿼리를 포함하고 있는 쿼리를 외부쿼리(outer query)라고 부르며, 서브쿼리는 부쿼리(inner query)라고도 부른다. 서브쿼리는 반드시 괄호(())로 감싸져야 한다.
MySQL에서 서브쿼리를 포함할 수 있는 외부쿼리는 SELECT, INSERT, UPDATE, DELETE, SET, DO 문이 있다. 이 서브쿼리는 다시 다른 서브쿼리 안에 포함될 수 있다.
다음은 주소가 서울인 고객이 예약한 예약 정보만을 선택하는 예제다.
SELECT ID, ReserveDate, RoomNum
FROM Reservation
WHERE Name IN (SELECT Name
FROM Customer
WHERE Address = '서울')
우선 서브쿼리가 먼저 실행되어 Customer 테이블 Address 필드 값이 '서울'인 레코드의 Name 필드를 모두 선택한다. 그리고서 외부쿼리가 실행되어 Reservation 테이블에서 서브쿼리에 의해 선택된 결과 집합에 포함된 Name 필드와 일치하는 레코드만 다시 선택한다.
서브쿼리를 사용하면 다음과 같은 장점을 얻을 수 있습니다.
1. 서브쿼리는 쿼리를 구조화시키므로, 쿼리 각 부분을 명확히 구분할 수 있게 한다.
2. 서브쿼리는 JOIN이나 UNION 같은 동작을 수행할 수 있는 또 다른 방법을 제공한다.
3. 서브쿼리는 복잡한 JOIN이나 UNION 보다 읽기 편하다.
다음 예제는 위에서 살펴본 주소가 서울인 고객이 예약한 예약 정보만 선택하는 예제를 JOIN을 사용하여 표현한 것이다. 쿼리가 복잡한 경우에는 JOIN이나 UNION을 사용하는 것보다 서브쿼리를 사용하는 것이 가독성 측면에서 좋다.
SELECT r.ID, r.ReserveDate, r.RoomNum
FROM Reservation AS r, Customer AS c
WHERE c.Address = '서울' AND r.Name = c.Name;
하지만 서브쿼리에서 사용된 테이블이나 그 결과 집합은 수정할 수 없다.
UPDATE Reservation ...
WHERE ReverseDate = (SELECT ... FROM Reservation ...);
FROM 절의 서브쿼리
서브쿼리는 SELECT 문의 FROM 절에서도 사용할 수 있다. 이때 서브쿼리에 의해 선택된 결과 집합은 FROM 절에서 하나의 테이블로 사용된다.
SELECT ...
FROM (서브쿼리) [AS] 이름
...
SELECT 문의 FROM 절에서 사용되는 모든 테이블에는 이름이 필요하다. 따라서 FROM 절에서 사용되는 서브쿼리는 위 문법처럼 이름을 정의해야 한다.
SELECT Name, ReservedRoom
FROM (SELECT Name, ReserveDate, (RoomNum + 1) AS ReservedRoom
FROM Reservation
WHERE RoomNum > 1001) AS ReservationInfo;
----------------------------------------------------------
인덱스는 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용한다. 자주 사용되는 필드 값으로 만들어진 원본 테이블의 사본이라고 생각할 수 있다. MySQL은 데이터를 검색할 때 첫 번째 필드부터 차례대로 테이블 전체를 검색한다. 따라서 테이블이 크면 클수록 데이터를 탐색하는 시간도 많이 늘어난다. 하지만 인덱스를 사용하면 테이블 전체를 읽지 않아도 되므로, 검색과 질의에 대한 처리가 빠르게 이루어진다. 이러한 인덱스는 사용자가 직접 접근할 수는 없으며, 검색과 질의에 대한 처리에서 사용된다.
이렇게 인덱스가 설정된 필드 값을 포함한 데이터 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우, 인덱스도 함께 수정되어야 한다. 따라서 인덱스가 설정된 테이블 처리 속도가 느려질 수 있다. 그러므로 인덱스는 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 더 좋다.
인덱스 생성
CREATE 문을 사용하여 인덱스를 생성할 수 있습니다. MySQL에서 인덱스를 생성하는 문법은 다음과 같다.
CREATE INDEX 인덱스이름
ON 테이블이름 (필드이름1, 필드이름2, ...)
이때 쉼표(,)를 사용하여 여러 필드를 가지는 인덱스를 생성할 수도 있다.
다음은 Reservation 테이블의 Name 필드에 NameIdx라는 인덱스를 설정하는 예제다.
CREATE INDEX NameIdx
On Reservation (Name);
인덱스 정보 보기
위와 같이 생성한 인덱스는 다음과 같은 문법을 통해 확인할 수 있다.
SHOW INDEX
FROM 테이블이름
인덱스 정보 필드 값은 다음과 같다.
1. Table : 테이블 이름
2. Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0
3. Key_name : 인덱스 이름을 표시하며, 인덱스가 해당 테이블 기본 키면 PRIMARY로 표시
4. Seq_in_index : 인덱스에서의 해당 필드 순서
5. Column_name : 해당 필드 이름.
6. Collation : 인덱스에서 해당 필드가 정렬되는 방법
7. Cardinality : 인덱스에 저장된 유일한 값들의 수
8. Sub_part : 인덱스 접두어
9. Packed : 키가 압축되는(packed) 방법
10. Null : 해당 필드가 NULL을 저장할 수 있으면 YES, 저장할 수 없으면 ''
11. Index_type : 인덱스에 사용되는 메소드(method)
12. Comment : 인덱스에 관한 기타 정보
13. Index_comment : 인덱스에 관한 모든 기타 정보
UNIQUE INDEX 생성
UNIQUE INDEX는 중복 값을 허용하지 않는 인덱스이다. MySQL에서 UNIQUE INDEX를 생성하는 문법은 다음과 같다.
CREATE UNIQUE INDEX 인덱스이름
ON 테이블이름 (필드이름1, 필드이름2, ...)
또한, 쉼표(,)를 사용하여 여러 필드에 UNIQUE INDEX를 설정해 줄 수도 있다. 다음 예제는 Reservation 테이블의 ID 필드에 IdIdx라는 UNIQUE INDEX를 설정한다
CREATE UNIQUE INDEX IdIdx
On Reservation (ID);
인덱스 정렬
인덱스를 생성할 때 인덱스에 포함되는 필드의 정렬 방식을 설정할 수 있다.
DESC 키워드를 사용하면 내림차순, ASC 키워드를 사용하면 오름차순 정렬이다.
1. CREATE INDEX 인덱스이름
ON 테이블이름 (필드이름 DESC)
2. CREATE INDEX 인덱스이름
ON 테이블이름 (필드이름 ASC)
다음 예제는 Reservation 테이블 Name 필드에 NameDescIdx라는 인덱스를 설정한다. 이때 Name 필드의 값들은 내림차순으로 정렬된다.
CREATE INDEX NameDescIdx
On Reservation (Name DESC);
기본 인덱스 추가
기본 인덱스에서 필드의 값은 같은 값이 여러 번 저장될 수 있으며, NULL 값을 가질 수도 있다.
MySQL에서 기본 인덱스를 추가하는 문법은 다음과 같다.
ALTER TABLE 테이블이름
ADD INDEX 인덱스이름 (필드이름)
UNIQUE INDEX 추가
UNIQUE INDEX에서 필드의 값은 중복될 수 없으나, NULL 값을 가질 수는 있다.
MySQL에서 UNIQUE INDEX를 추가하는 문법은 다음과 같다.
ALTER TABLE 테이블이름
ADD UNIQUE 인덱스이름 (필드이름)
MySQL에서 FULLTEXT INDEX를 추가하는 문법은 다음과 같다.
ALTER TABLE 테이블이름
ADD FULLTEXT INDEX이름 (필드이름)
FULLTEXT INDEX는 일반 인덱스와 달리 매우 빠르게 테이블 모든 텍스트 필드를 검색한다. 이 인덱스는 검색 엔진과 유사한 방법으로 자연어를 이용하여 데이터를 검색할 수 있도록 모든 데이터의 문자열 단어를 저장한다.
인덱스 삭제
1. ALTER 문으로 인덱스 삭제
ALTER 문을 사용하면 해당 테이블에서 명시된 인덱스를 삭제할 수 있다.
ALTER TABLE 테이블이름
DROP INDEX 인덱스이름
다음 예제는 Reservation 테이블에서 NameIdx라는 이름의 인덱스를 삭제하는 예제다.
ALTER TABLE Reservation
DROP INDEX NameIdx;
2. DROP 문으로 인덱스 삭제
DROP 문을 사용하면 해당 테이블에서 명시된 인덱스를 삭제할 수 있다.
DROP INDEX 인덱스이름
ON 테이블이름
DROP 문은 내부적으로 ALTER 문으로 변환되어 명시된 이름 인덱스를 삭제한다.
-----------------------------------------------------------------------------------
뷰(view)는 데이터베이스에 존재하는 일종의 가상 테이블을 의미한다. 뷰는 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하고 있지는 않다. MySQL에서 뷰(view)는 다른 테이블이나 다른 뷰에 저장되어 있는 데이터를 보여주는 역할만 수행한다. 즉, 뷰를 사용하면 여러 테이블이나 뷰를 하나의 테이블처럼 볼 수 있다.
MySQL에서 뷰가 가지는 장점은 다음과 같습니다.
1. 특정 사용자에게 테이블 전체가 아닌 필요한 필드만 보여줄 수 있다.
2. 복잡한 쿼리를 단순화해서 사용할 수 있다.
3. 쿼리를 재사용할 수 있다.
뷰는 다음과 같은 단점을 가지고 있다.
1. 한 번 정의된 뷰는 변경할 수 없다.
2. 삽입, 삭제, 갱신 작업에 많은 제한사항을 가진다.
3. 자기 인덱스를 가질 수 없다.
뷰 생성
CREATE 문을 사용하여 뷰를 생성할 수 있다. MySQL에서 뷰를 생성하는 문법은 다음과 같다.
CREATE VIEW 뷰이름 AS
SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
WHERE 조건
뷰 이름을 명시하고, AS 키워드 다음에 SELECT 문을 사용하여 해당 뷰가 접근할 수 있는 필드를 명시한다. 이때 WHERE 절을 사용하여 특정 조건을 설정할 수도 있다. 이렇게 CREATE VIEW 문은 SELECT 문에서 선택된 필드를 가지는 새로운 뷰를 생성한다. 뷰는 원본 테이블과 같은 이름을 가질 수 없다.
다음 예제는 Reservation 테이블에서 Name, ReserveDate, Dday 필드를 가지는 MyView라는 이름의 뷰를 생성한다.
CREATE VIEW MyView AS
SELECT Name, ReserveDate, ReserveDate - Curdate() AS Dday
FROM Reservation;
위 예제에서 생성된 MyView의 Dday 필드는 ReserveDate 필드 값과 현재 날짜 차이를 계산하여 생성된다. Curdate() 함수는 현재 날짜를 받아오는 함수다.
뷰는 테이블과 마찬가지로 SELECT 문을 사용하여 저장하고 있는 데이터를 확인할 수 있다.
뷰 대체
CREATE 문에 OR REPLACE 절을 추가하여 기존에 존재하는 뷰를 새로운 뷰로 대체할 수 있다.
MySQL에서 뷰를 대체하는 문법은 다음과 같습니다.
CREATE OR REPLACE VIEW 뷰이름 AS
SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
WHERE 조건
CREATE OR REPLACE VIEW 문은 OR REPLACE 절이 추가된 것을 제외하면, 뷰를 생성하는 문법과 같다. 이때 해당 뷰가 존재하지 않으면 CREATE VIEW 문과 같은 결과를 반환한다.
다음 예제는 앞 예제에서 생성한 MyView에서 ReserveDate 필드를 RoomNum 필드로 대체한다
CREATE OR REPLACE VIEW MyView AS
SELECT Name, RoomNum, Date - Curdate() AS Dday
FROM Reservation;
다음은 Reservation 테이블에서 IdIdx라는 이름의 인덱스를 삭제하는 예제다.
DROP INDEX IdIdx
ON Reservation;
뷰 수정
CREATE 문을 사용하여 생성된 뷰는 ALTER 문을 사용하여 수정할 수 있다. MySQL 뷰를 수정하는 문법은 다음과 같다.
ALTER VIEW 뷰이름 AS
SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
MySQL에서 뷰를 수정하는 문법은 뷰를 생성하는 문법과 거의 같다. 다음은 생성된 MyView 이름의 뷰를 ID와 Name 필드만 가지도록 수정하는 예제다.
ALTER VIEW MyView AS
SELECT ID, Name
FROM Reservation;
뷰 삭제
DROP 문을 사용하여 생성된 뷰를 삭제할 수 있다. MySQL에서 뷰를 삭제하는 문법은 다음과 같다.
DROP VIEW 뷰이름
다음은 MyView라는 뷰를 삭제하는 예제다.
DROP VIEW MyView;
-------------------------------------------------------------------------
MySQL 내장 함수
MySQL에서 미리 정의하여 제공해 주는 대표적인 내장 함수의 종류는 다음과 같다.
1. 문자열 함수
2. 수학 함수
3. 날짜와 시간 함수
LENGTH() 함수는 전달받은 문자열의 길이를 반환.
SELECT LENGTH('12345678');
CONCAT() 함수는 전달받은 문자열을 모두 결합하여 하나의 문자열로 반환.
만약 전달받은 문자열 중 하나라도 NULL이 존재하면, NULL을 반환.
SELECT CONCAT('Ora', 'cle Cor', 'poration'),
CONCAT('Oracle', NULL, 'Corporation'); <-- NULL
LOCATE() 함수는 인수로 전달받은 문자열이 특정 문자열에서 처음으로 나타나는 위치를 찾아서, 해당 위치를 반환. 만약 전달받은 문자열이 특정 문자열 내에 존재하지 않으면 0을 반환.
MySQL에서는 문자열 첫 번째 문자의 인덱스를 1부터 계산하므로, 주의하자. 세 번째 인수로 특정 문자열에서 전달받은 문자열을 찾기 시작할 인덱스를 전달할 수 있다.
SELECT LOCATE('abc', 'ababcDEFabc'), 3
LOCATE('abc', 'ababcDEFabc', 4); 9
LEFT() 함수는 전달받은 문자열의 왼쪽부터 명시한 개수만큼 문자를 반환.
RIGHT() 함수는 전달받은 문자열의 오른쪽부터 명시한 개수만큼 문자를 반환.
SELECT LEFT('MySQL PHP HTML Java', 5),
RIGHT('MySQL PHP HTML Java', 4);
LOWER() 함수는 전달받은 문자열의 문자를 모두 소문자로 변경.
UPPER() 함수는 전달받은 문자열의 문자를 모두 대문자로 변경
SELECT LOWER('MySQL PHP HTML Java'),
UPPER('MySQL PHP HTML Java');
REPLACE() 함수는 특정 문자열을 대체 문자열로 교체
SELECT REPLACE('MySQL', 'My', 'MS ');
TRIM() 함수는 전달받은 문자열의 앞이나 뒤, 또는 양쪽 모두에 있는 특정 문자를 제거.
TRIM() 함수에서 사용할 수 있는 지정자는 다음과 같다.
1. BOTH : 전달받은 문자열의 양 끝에 존재하는 특정 문자를 제거함. (기본 설정)
2. LEADING : 전달받은 문자열 앞에 존재하는 특정 문자를 제거함.
3. TRAILING : 전달받은 문자열 뒤에 존재하는 특정 문자를 제거함.
만약 지정자를 명시하지 않으면, 자동으로 BOTH로 설정.
또한, 제거할 문자를 명시하지 않으면, 자동으로 공백을 제거.
SELECT TRIM(' !!!MySQL PHP HTML Java!!! '),
TRIM(LEADING '!' FROM '!!!MySQL PHP HTML Java!!!')
FORMAT() 함수는 숫자 타입의 데이터를 세 자리마다 쉼표(,) 사용하는 형식으로 변환.
반환되는 데이터 형식이 문자열 타입이므로 주의하자. 두 번째 인수로 반올림할 소수 부분 자릿수까지 전달할 수 있다.
SELECT FORMAT(123456789.123456, 3); 123,456,789.12346
올림과 내림
FLOOR() 함수는 인수로 전달받은 값과 같거나 작은 수 중에서 가장 큰 정수를 반환.
CEIL() 함수는 반대로 인수로 전달받은 값과 같거나 큰 수 중에서 가장 작은 정수를 반환.
SELECT FLOOR(10.95), 10
FLOOR(11.01), 11
FLOOR(-10.95), -11
FLOOR(-11.01); -12
SELECT CEIL(10.95), 11
CEIL(11.01), 12
CEIL(11), 11
CEIL(-10.95), -10
CEIL(-11.01); -11
소수점 반올림은 ROUND() 함수를 사용하여 수행할 수 있다. 두 번째 인수로 반올림할 소수점을 전달할 수 있다.
SELECT ROUND(10.49), 10
ROUND(10.5), 11
ROUND(-10.5), -11
ROUND(-10.49); -10
지수와 로그
SQRT() 함수는 전달받은 수의 제곱근 중 양수값 반환.
POW() 함수는 전달받은 수의 거듭제곱을 반환. 첫째 인수는 밑수, 두 번째는 지수 전달.
EXP() 함수는 인수로 지수를 전달받아, e의 거듭제곱을 계산하여 반환.
LOG() 함수는 전달받은 수의 자연로그 값을 계산하여 반환
삼각 함수
MySQL에서는 다음 함수를 사용하여 쿼리에 간단히 삼각 함수를 사용할 수 있다.
SIN() 함수는 전달받은 수의 사인값
COS() 함수는 코사인값
TAN() 함수는 탄젠트값
SELECT SIN(PI()/2),
COS(PI()),
TAN(PI()/4);
ABS(X) 함수는 전달받은 수의 절댓값을 반환.
RAND() 함수는 0.0보다 크거나 같고 1.0보다 작은 실수를 무작위로 반환.
ROUND(RAND()*100, 0);
예제에서 RAND() 함수는 ROUND() 함수 인수로 전달되어 0~100 사이 무작위 정수를 생성.
----------------------------------------------------------------------------
현재 날짜와 시간 정보
NOW() 함수는 현재 날짜와 시간을 반환하며 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 형태다.
CURDATE() 함수는 현재 날짜를 반환하면 'YYYY-MM-DD' 또는 YYYYMMDD 형태다.
CURTIME() 함수는 현재 시각을 반환하며 'HH:MM:SS' 또는 HHMMSS 형태다.
SELECT NOW(),
CURDATE(),
CURTIME();
DATE() 함수는 전달받은 값에 해당하는 날짜 정보를 반환
MONTH() 함수는 전달받은 값에서 월에 해당하는 값만을 반환, 0부터 12 사이 값.
DAY() 함수는 전달받은 값에서 일자에 해당하는 값만 반환, 0부터 31 사이 값.
HOUR() 함수는 전달받은 값에서 시간에 해당하는 값만 반환하며, 0부터 23 사이 값
MINUTE() 함수는 전달받은 값에서 분에 해당하는 값만 반환하며, 0부터 59 사이 값
SECOND() 함수는 전달받은 값에서 초에 해당하는 값만 반환하며, 0부터 59 사이 값.
SELECT DATE('2016-02-19 12:34:56'),
MONTH('2016-01-02 12:34:56'),
DAY('2016-01-02 12:34:56'),
HOUR('12:34:56'),
MINUTE('12:34:56'),
SECOND('12:34:56');
요일과 월 이름
MONTHNAME() 함수는 전달받은 값의 월에 해당하는 이름을 반환.
DAYNAME() 함수는 전달받은 값의 요일에 해당하는 이름을 반환.
SELECT MONTHNAME('2017-02-19'), February
DAYNAME('2017-02-19'); Sunday
DAYOFWEEK() 함수는 전달받은 값의 일자가 해당 주에서 몇 번째 날인지 반환
이 함수는 1부터 7 사이의 값을 반환 (일요일 = 1, 토요일 = 7)
DAYOFMONTH() 함수는 전달받은 값의 일자가 해당 월에서 몇 번째 날인지 반환.
이 함수는 0부터 31 사이의 값을 반환.
DAYOFYEAR() 함수는 전달받은 값의 일자가 해당 연도에서 몇 번째 날인지 반환.
이 함수는 1부터 366 사이의 값을 반환.
SELECT DAYOFMONTH('2017-02-19'),
DAYOFWEEK('2017-02-19'),
DAYOFYEAR('2017-02-19');
DATE_FORMAT() 함수는 전달받은 형식에 맞춰 날짜와 시간 정보를 문자열로 반환한다. 인수로 전달할 수 있는 날짜와 시간 표현 형식은 다음과 같다.
형태 설명 예시
------------------------------------------------------------------------------------------------------------------
%a 요일을 세 개의 문자로 표현함. Sun부터 Sat
%b 월의 축약형을 세 개의 문자로 표현함. Jan부터 Dec
%c 월을 숫자로 표현함. 0부터 12
%D 날짜 뒤에 영어 서수를 붙임 0th, 1st, 2nd, 3rd, ...
%d 날짜를 두 자리 숫자로 표현함. 00부터 31
%e 날짜를 숫자로 표현함. 0부터 31
%f 마이크로초를 여섯 자리 숫자로 표현함. 000000에서 999999
%H 24시간 형식 시간을 두 자리 숫자로 표현함. 00부터 23
%h 12시간 형식 시간을 두 자리 숫자로 표현함. 01부터 12
%I 12시간 형식 시간을 두 자리 숫자로 표현함. 01부터 12
%i 분을 두 자리 숫자로 표현함. 00부터 59
%j 일 년 중 몇 번째 날인지를 세 자리 숫자로 표현함. 001부터 366
%k 24시간 형식으로 시간을 표현함. 0부터 23
%l 12시간 형식으로 시간을 표현함. 1부터 12
%M 월을 완전한 문자열로 표현함. January부터 December
%m 월을 두 자리 숫자로 표현함. 00부터 12
%p 오전과 오후의 대문자를 표현함. AM 또는 PM
%r hh:mm:ss AM/PM 형식으로 시간을 표현함. (12시간 형식) 00:00:01 AM
%S 초를 두 자리 숫자로 표현함. 00부터 59
%s 초를 두 자리 숫자로 표현함. 00부터 59
%T hh:mm:ss 형식으로 시간을 표현함. (24시간 형식) 23:01:01
%U 일년 중 몇 번째 주인지 숫자로 표현. (주는 일요일부터 시작) 00부터 53
%u 일년 중 몇 번째 주인지 숫자로 표현. (주는 월요일부터 시작) 00부터 53
%V 일년 중 몇 번째 주인지 숫자로 표현. (일요일 시작하고, %X 형식) 00부터 53
%v 일년 중 몇 번째 주인지 숫자로 표현. (월요일 시작하고, %x 형식) 00부터 53
%W 요일을 완전한 문자열로 표현함. Sunday부터 Saturday
%w 요일을 숫자로 표현함. 0(일요일)부터 6(토요일)
%X 연도를 네 자리 숫자로 표현. (주는 일요일 시작, %V 형식) 1999나 2003
%x 연도를 네 자리 숫자로 표현. (주는 월요일 시작, %v 형식) 1999나 2003
%Y 연도를 완전한 네 자리 숫자로 표현함. 1999나 2003
%y 연도를 두 자리 숫자로 표현함. 99나 03
%% '%' 문자 %
SELECT DATE_FORMAT('2017-02-19 12:34:56', '%D %y %a %d %m %b %j');
<-- 19th 17 Sun 19 02 Feb 050
----------------------------------------------------------------
그룹(aggregate) 함수
MySQL에서는 집합에 대해서 동작하는 그룹(aggregate) 함수를 제공한다. MySQL에서 자주 사용되는 대표적인 그룹 함수는 다음과 같다. 대부분의 그룹 함수는 NULL 값을 제외하고 동작한 결과를 반환.
1. COUNT()
2. MIN()과 MAX()
3. SUM()
4. AVG()
COUNT() 함수는 선택된 필드에서 특정 조건을 만족하는 레코드 개수를 반환.
조건을 만족하는 레코드가 없다면, COUNT() 함수는 0을 반환.
이때 중복된 값을 제외하려면, 필드 이름 앞에 DISTINCT 키워드를 사용.
다음 예제는 Reservation 테이블의 총 레코드 개수를 구하는 예제다.
SELECT COUNT(*) AS NumberOfRervation
FROM Reservation;
MIN() 함수는 선택된 필드에 저장된 값 중 가장 작은 값을 반환.
MAX() 함수는 선택된 필드에 저장된 값 중 가장 큰 값을 반환.
MIN() 함수와 MAX() 함수는 문자열 타입도 최솟값과 최댓값을 구할 수 있다.
SUM() 함수는 선택된 숫자 타입의 필드에 저장된 값의 총 합을 반환.
AVG() 함수는 선택된 숫자 타입의 필드에 저장된 값의 평균값을 반환.
-------------------------------------------------------------------------
GROUP BY 절
선택 레코드 집합을 필드 값이나 표현식에 의해 그룹화한 결과 집합을 반환.
즉, 하나의 그룹을 하나의 레코드로 반환하므로, 결과 집합 크기를 줄여주는 역할을 한다.
이러한 GROUP BY 절은 SELECT 문에서만 사용할 수 있으며, 그룹 함수를 사용할 때 자주 사용.
SELECT 필드이름, 그룹함수(필드이름)
FROM 테이블이름
[WHERE 조건]
GROUP BY 필드이름;
다음은 Customer 테이블에서 각 나이별 회원수를 구하는 예제다.
SELECT Age, COUNT(*) AS NumOfCustomer
FROM Customer
GROUP BY Age;
GROUP BY 절에 사용된 필드 이름은 반드시 SELECT 문에도 명시되어야 한다.
HAVING 절
HAVING 절은 GROUP BY 절에 의해 반환되는 결과 집합 조건을 설정할 수 있게 해준다.
SELECT 필드이름, 그룹함수(필드이름)
FROM 테이블이름
[WHERE 조건]
GROUP BY 필드이름
HAVING 조건;
다음은 Customer 테이블에서 도시별 가장 나이가 많은 레코드 중 15살 이상 레코드만 선택.
SELECT Address, Name, MAX(Age) AS MaxAge
FROM Customer
GROUP BY Address
HAVING MaxAge > 15;
'DBMS, 데이터베이스' 카테고리의 다른 글
| MariaDB, MySQL에서 페이지 단위로 검색하기 (0) | 2023.04.22 |
|---|---|
| 파이썬, PostgreSQL의 with 문을 사용한 경우 연결 객체 누수 (0) | 2023.04.22 |
| CentOS 8, MongoDB 몽고디비 설치 (0) | 2022.07.02 |
| MongoDB 몽고디비 공식 매뉴얼 사이트 (0) | 2022.07.02 |
| [MongoDB] 시작, 종료 (0) | 2022.06.26 |