1. CONCAT
- 여러 문자열을 하나로 합치거나 연결
SELECT CONCAT('string1', 'string2', ...);예제
SELECT CONCAT('concat', ' ', 'test');
+-------------------------------+
| CONCAT('concat', ' ', 'test') |
+-------------------------------+
| concat test |
+-------------------------------+
SELECT CONCAT('이름 : ', name) FROM celeb;
+---------------------------+
| CONCAT('이름 : ', name) |
+---------------------------+
| 이름 : 아이유 |
| 이름 : 이미주 |
| 이름 : 송강 |
| 이름 : 강동원 |
| 이름 : 유재석 |
| 이름 : 차승원 |
| 이름 : 이수현 |
+---------------------------+2. ALIAS
- 컬럼이나 테이블에 붙이는 별칭
- ALIAS가 출력 됨 (~AS 는 생략 가능)
SELECT column1 AS alias1, column2 AS alias2, ... FROM table_name;
SELECT column1 alias1, column2 alias2, ... FROM table_name alias;
SELECT column1, column2, ... FROM table_name AS alias;3. DISTINCT
- 검색에 대한 중복 제거
SELECT DISTINCT column1, column2, ... FROM table_name;예제
(1) 연예인 소속사 정보 검색
- 중복 데이터 출력하며 다 보여주기
SELECT agency FROM celeb;- 중복 데이터 제외하며 보여주기
SELECT DISTINCT agency FROM celeb;(2) 가수 중 성별과 직업별 종류 검색
- 중복 데이터 출력하며 다 보여주기
SELECT sex, job_title FROM celeb WHERE job_title LIKE '%가수%';- 중복 데이터 제외하며 보여주기
SELECT DISTINCT sex, job_title FROM celeb WHERE job_title LIKE '%가수%'; 4. LIMIT
- 검색 결과를 정렬된 순에서 LIMIT로 주어진 숫자만큼 조회
SELECT column1, column2, ... FROM table_name WHERE condition LIMIT number;예제
(1) celeb 테이블에서 id로 정렬된 데이터 3개
SELECT * FROM celeb LIMIT 3;(2) 나이가 가장 적은 연예인 4명 검색
SELECT * FROM celeb ORDER BY age LIMIT 4;===============================================================
5. SCALAR Functions
실습환경 : AWS RDS (database-1) zerobase DB, sandwich 테이블
- 입력값을 기준으로 단일 값을 반환
* UCASE
- 영문을 대문자로 변환
SELECT UCASE("string");SELECT UCASE('This is ucase test.');
+------------------------------+
| UCASE('This is ucase test.') |
+------------------------------+
| THIS IS UCASE TEST. |
+------------------------------+(예제) $15넘는 메뉴를 대문자로 조회
SELECT UCASE(menu), price FROM sandwich WHERE price > 15;* LCASE
- 영문을 소문자로 변환
SELECT LCASE("string");
SELECT LCASE("This IS LCASE Test.");
+------------------------------+
| LCASE("This IS LCASE Test.") |
+------------------------------+
| this is lcase test. |
+------------------------------+(예제) 가격이 $5 보다 작은 메뉴를 소문자 조회
SELECT LCASE(menu), price FROM sandwich WHERE price < 5;* MID
- 문자열의 부분을 위치 등에 따라 골라서 반환
SELECT MID("string", start_position, lenth); (string, start, length)예제
(1) 1번 위치에서 4글자 조회
SELECT MID('This is mid test', 1, 4);(2) 6번 위치에서 5글자 조회
SELECT MID('This is mid test', 6, 5);(3) -4번 위치(뒤에서 4번째 위치)에서 4글자 조회
SELECT MID('This is mid test', -4, 4);(4) -8번 위치(뒤에서 8번째 위치)에서 3글자를 조회
SELECT MID('This is mid test', -8, 3);(5) 11위 카페이름 중 두번째 단어만 조회: 6번 위치에서 4글자
하나씩 단계별 조회해서 추출할 단어 위치를 찾기
mysql> SELECT cafe FROM sandwich WHERE ranking=11;
+-----------+
| cafe |
+-----------+
| Lula Cafe |
+-----------+
mysql> SELECT MID(cafe, 6, 4) FROM sandwich WHERE ranking=11;
+-----------------+
| MID(cafe, 6, 4) |
+-----------------+
| Cafe |
+-----------------+* LENGTH
- 문자열 길이 반환
SELECT LENGTH("string");(예제)
(1) 문장의 길이 조회
SELECT LENGTH('This is len test.');(2) ''에 공백이 없는 경우 길이는 0
SELECT LENGTH('');(3) ''에 공백이 있는 경우 길이는 1
SELECT LENGTH(' ');(4) NULL의 경우 길이가 없으므로 결과는 NULL
SELECT LENGTH(NULL);(5) sandwich Table에서 Top 3의 주소 길이를 검색
SELECT LENGTH(address), address FROM sandwich ORDER BY ranking LIMIT 3;
또는
SELECT LENGTH(address), address FROM sandwich WHERE ranking <= 3;* ROUND
- 지정한 자리에서 숫자 반올림 (0은 소수점 첫째 자리)
SELECT ROUND(number, deciamls_place); (예제)
(1) 위치를 지정하지 않을 경우, 소수점 첫번째 자리(0)에서 반올림
SELECT ROUND(315.625); 답: 316(2) 소수점 첫번째 위치는 0
SELECT ROUND(315.625, 0); 답: 316(3) 두번째 소수점 위치는 1
SELECT ROUND(315.625, 1); 답: 315.6(4) 세번째 소수점 위치는 2
SELECT ROUND(315.625, 2); 답: 315.63(5) 자연수 일단위 위치는 -1
SELECT ROUND(315.625, -1); 답: 320(6) 자연수 십단위 위치는 -2
SELECT ROUND(315.625, -2); 답: 300(7) sandwich 테이블에서 소수점 자리는 반올림하여 1달러 단위까지만 표시 (단, 최하위 3개만 표시)
SELECT ranking, price, ROUND(price, 0) FROM sandwich
ORDER BY ranking DESC
LIMIT 3* NOW
- 현재 날짜 및 시간 반환
SELECT NOW(); 2024-02-27 17:42:33 이렇게 반환* FORMAT
- 문자나 숫자를 천단위 콤마 있는 형식으로 반환
SELECT FORMAT(number, decimals_place); decimal은 소수점 위치(예제)
(1) 소수점을 표시하지 않을 경우 0
SELECT FORMAT(12345.6789, 0); 정답 : 12,346(2) 소수점 두자리까지 표시할 경우 2
SELECT FORMAT(12345.6789, 2); 정답 : 12,345.68(3) 소수점 열자리까지 표시
SELECT FORMAT(12345.6789, 10);(4) oil_price Table에서 가격이 백원단위에서 반올림 했을 때 2000원 이상인 경우. 천원 단위에 콤마를 넣어서 조회
SELECT FORMAT(가격, 0) FROM oil_price WHERE ROUND(가격, -3) >= 2000;===============================================================
6. PRIMARY KEY
- 후보키 (Candidate Key)들 중 개체(Table) 구별을 위해 선택된 하나의 키
- 후보키 (Candidate Key) - 하나의 릴레이션(Relation)에서 유일성과 최소성을 가지고 있는 모든 속성 혹은 속성 집합
- 기본키로 선택된 속성은 중복되지 않는 고유값 보유
- 정의되지 않은 NULL 값을 포함할 수 없다. → NOT NULL
- 다른 테이블의 외래키에 의해 참조될 수 있음
(1) PRIMARY KEY 문법
- 테이블 생성시 지정
CREATE TABLE table_name( column1 datatype NOT NULL, column2 datatype NOT NULL, ... [CONSTRAINT constraint_name] PRIMARY KEY (column1, column2, ...) ); --> CONSTRAINT은 생략 가능. 생략하면 자동으로 생성 됨. ``` - 이미 정의되어 있는 테이블에 기본키를 설정하는 방법
ALTER TABLE table_name ADD PRIMARY KEY (column1, column2, ...) ```
- 테이블 삭제시
ALTER TABLE table_name DROP PRIMARY KEY; ```
(예제)
(1) person 테이블 생성하면서 Primary Key 지정
```
CREATE TABLE person(
pid int NOT NULL,
name varchar(16),
age int,
sex char,
PRIMARY KEY (pid)
);
```(2) person 테이블 기본키 삭제
```
ALTER TABLE person DROP PRIMARY KEY;
```(3) 이미 정의 되어 있는 person 테이블의 기본키 설정
```
ALTER TABLE person ADD PRIMARY KEY (pid);
```====================================================================
7. FOREIGN KEY
- 외래키는 두 Relation 간의 관계를 맺어줄 때 사용되는 키
- 외래키로 정의된 속성(Attribute)는 반드시 상대 Relation의 기본키여야 함
- 기본키와 달리 외래키는 한 테이블 내에 여러 개일 수 있음. 즉, 여러 개의 테이블 참조 가능
- 외래키 확인을 위해 CONSTRAINT으로 지정한 이름을 확인 가능- CONSTRAINT를 생략하면 이름이 자동 생성
- 이 constraint_name을 알아야 해당 외래키를 삭제(DROP) 가능
(1) FOREIGN KEY 문법
- 테이블 생성 후에도 ALTER TABLE을 통해 FOREIGN KEY를 지정 가능
*** 테이블 생성 시 지정
CREATE TABLE table_name(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
column3 datatype,
column4 datatype,
...
[CONSTRAINT constraint_name] PRIMARY KEY (column1, column2, ...)
[CONSTRAINT constraint_name]
PRIMARY KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
);- 기존 테이블에서 외래키 설정
ALTER TABLE table_name ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column)
--> CONSTRAINT이 생략되어 있지만 자동으로 생성- 자동 생성된 CONSTRAINT 확인
SHOW CREATE TABLE table_name;*** 테이블 삭제 시 지정
- 외래키는 한 테이블에 여러 개가 존재할 수 있으므로 삭제하려는 외래키를 지정해 줘야 함
ALTER TABLE table_name DROP FOREIGN KEY FK_constrain_name;(예제)
(1) CREATE TABLE 에서 외래키 지정
CREATE TABLE orders(
oid int NOT NULL,
order_no varchar(16),
pid int,
PRIMARY KEY (oid),
[CONSTRAINT FK_person] FOREIGN KEY (pid) REFERENCES person(pid)
);
SHOW CREATE TABLE orders; #pid의 key가 MUL로 나옴(2) CREATE TABLE에서 외래키를 지정하는 경우, CONSTRAINT를 생략 가능
CREATE TABLE job(
jid int NOT NULL,
name varchar(16),
pid int,
PRIMARY KEY (jid),
FOREIGN KEY (pid) REFERENCES person(pid)
);
SHOW CREATE TABLE job;(3) 이미 정의 되어 있는 orders 테이블에서 외래키 설정
ALTER TABLE orders
ADD [CONSTRAINT FK_person] FOREIGN KEY (pid) REFERENCES person(pid);