[Day17 - DB] SELECT/FROM, WHERE, 문자열 함수

시작 전 알면 좋은 것들

1. 문자열의 타입은 VARCHAR(문자 크기)로 선언됨

단, 30으로 크기를 지정한다고 하더라도 30을 다 채우는 것이 아닌 5를 채우면 5만 생성되며, 30이 넘어가면 에러 발생

 

2. 날짜 타입은 DATE / DATETIME 타입이며, 입력시 형식을 맞춰서 작성해줘야 하며 형식이 맞지 않을 경우 에러가 발생. 크기 비교도 가능함.

 

3. 문자열은 기본 왼쪽 정렬, 숫자는 기본 오른쪽 정렬이 디폴트

 

4. 문자열을 사용 시에는 작은 따옴표를 이용하며, 큰 따옴표는 alias에서 별칭 지정시에만 사용 가능

 

5. 배열과 달리 순서를 지정할 때 1번이 첫 번째

---

SELECT

 

SELECT FROM : 테이블의 데이터를 조회할 때 사용

SELECT [조회할 칼럼명] FROM [테이블명];

 

-- emp 테이블의 전체 컬럼 조회
SELECT * FROM emp; 

-- emp 테이블의 empno, enmae 칼럼 조회
SELECT empno, ename FROM emp; 

-- dept 테이블에서 전체 컬럼 조회
SELECT * FROM dept;

-- dept 테이블에서 deptno, dname 조회
SELECT deptno, dname FROM dept;

-- student 테이블에서 학번, 이름, 생일, 전화번호 조회
SELECT studno, name, birthday, tel FROM student;

 

 

1. ORDER BY [칼럼명]

: 정렬 (디폴트는 내림차순 ASC이며, DESC를 이용해 오름차순 정렬 가능)

SELECT [조회할 칼럼명] FROM [테이블명] ORDER BY [칼럼명|숫자];

--  emp 테이블의 sal을 정렬
SELECT * FROM emp ORDER BY sal; -- 오름차순
SELECT * FROM emp ORDER BY sal DESC; -- 내림차순
SELECT * FROM emp WHERE deptno=10 ORDER BY sal DESC; -- 내림차순

 

: 칼럼이 여러개일 경우 칼럼별로 순서대로 정렬을 진행하며, asc/desc는 칼럼별로 지정 필요

-- emp 테이블에서 deptno는 오름차순, sal은 내림차순으로 정렬 조회
SELECT * FROM emp ORDER BY deptno asc, sal desc; 

-- student 테이블에서 학년순 정렬, 같은 학년은 키가 큰 학생이 먼저 오도록 정렬
SELECT * FROM student ORDER BY grade, height desc;

 

 

1-1. ORDER BY [숫자]

: SELECT 칼럼을 기준으로 숫자번째에 해당하는 칼럼 기준 정렬

-- ORDER BY 숫자는 해당 숫자에 해당하는 SELECT 칼럼을 기준으로 정렬
SELECT studno, name FROM student ORDER BY 2;
SELECT studno, name FROM student ORDER BY name;

-- student 테이블에서 4학년 학생들의 학번, 이름, 생일, 학과번호를 생일 순으로 정렬
SELECT studno, name, birthday, deptno1 FROM student WHERE grade=4 ORDER BY 3;
SELECT studno, name, birthday, deptno1 FROM student WHERE grade=4 ORDER BY birthday;

 

 

2. DISTINCT(칼럼명)

: 중복된 데이터를 한 번만 조회

SELECT DISTINCT(칼럼명) FROM [테이블명];

-- deptno1을 중복하지 않고 출력
SELECT DISTINCT(deptno1) FROM student;

 

 

3. Alias

: 테이블 명이나 칼럼명이 길어 간략하게 할 때 사용

-- alias : 칼럼명을 바꿔서 조회 (as는 생략 가능)
SELECT empno as 사번, ename as 이름, job as 직무 FROM emp WHERE job='CLERK' OR job='SALESMAN'; 
SELECT empno 사번, ename 이름, job 직무 FROM emp WHERE job='CLERK' OR job='SALESMAN'; 

-- alias를 쓸 때에만 큰 따옴표 사용, alias 사용 시 스페이스가 들어가야 한다면 큰따옴표 사용
SELECT empno "사   번", ename "이   름", job "직   무" FROM emp WHERE job='CLERK' OR job='SALESMAN'; 

-- student 테이블에서 4학년 학생들의 학번, 이름, 학년을 조회(칼럼명 : 학 번, 이 름, 학 년)
SELECT studno "학 번", name "이 름", grade "학 년" FROM student WHERE grade=4;

 

---

WHERE

WHERE : 행에 대한 조건문

SELECT [칼럼명] FROM [테이블명] WHERE [행에 대한 조건];

-- emp 테이블에서 deptno=0인 행만 조회
SELECT * FROM emp WHERE deptno=10;

-- emp 테이블에서 deptno=10인 empno, enmae, deptno 칼럼 조회
SELECT empno, ename, deptno FROM emp WHERE deptno=10;

-- emp 테이블에서 deptno가 10보다 큰 직원의 사번, 이름, 직무, 부서번호 조회
SELECT empno, ename, job, MGR FROM emp WHERE deptno>10;

-- student 테이블에서 4학년 학생들의 학번, 이름, 생일, 전화번호, 학년을 조회
SELECT studno, name, birthday, tel, grade FROM student WHERE grade=4;

 

 

WHERE 절 연산자

1. AND, OR 연산자

A AND B : A와 B 모두 해당

A OR B : A 또는 B, 둘 중 하나

-- student 테이블에서 2학년 또는 3학년인 학생의 정보 조회
SELECT * FROM student WHERE grade=2 OR grade=3;
SELECT * FROM student WHERE 2<=grade AND grade<=3;

 

 

2. IN(a, b, c, ...)

: WHERE절 내에서 특정값을 여러개 선택하는 연산자로, 괄호 값 중 일치하는 것이 있으면 true

-- student 테이블에서 2학년 또는 3학년인 학생의 정보 조회
SELECT * FROM student WHERE grade IN(2, 3);

-- student 테이블에서 4학년이 아닌 학생 정보 조회
SELECT * FROM student WHERE grade IN(1,2,3);
SELECT * FROM student WHERE grade NOT IN(4);

 

 

3. BETWEEN A AND B

: A이상 B이하에 해당하는 행

: BETWEEN 대신 부호 연산자 사용 가능

-- student 테이블에서 4학년이 아닌 학생 정보 조회
SELECT * FROM student WHERE 1<=grade AND grade<=3;
SELECT * FROM student WHERE grade BETWEEN 1 AND 3;

 

 

4. LIKE '문자열'

: 칼럼의 문자열에 특정 문자열이 포함된 것을 거를 때 사용

: 문자열에서만 사용 가능

: 와일드 카드를 사용 (% : 어떤 문자를 포함한 모든 것을 조회, _ : 한 개인 단일 문자를 의미)

-- 이름의 성이 '서'인 학생
SELECT * FROM student WHERE name LIKE '서%';

-- job에 MAN이 포함된 것만 조회
SELECT * FROM emp WHERE job LIKE '%MAN%';

-- job의 두 번째 글자에 A인 것 조회
-- _(언더바)가 한 글자를 의미
SELECT * FROM emp WHERE job LIKE'_A%';

-- 주민번호를 가지고 9월이 생일인 학생의 학번, 이름, 학년, 주민번호 조회
SELECT studno, name, grade, jumin FROM student WHERE jumin LIKE '__09%';

 

 

5. IFNULL(칼럼명, 대체할 값)

: 해당하는 칼럼이 null일 경우 대체하는 값 지정

-- Null과 연산하면 Null이 나오기 때문에 bonus가 Null이면 pay+bonus가 Null로 출력
SELECT profno, name, pay, bonus, pay+bonus FROM professor;

-- Bonus가 Null 이면 0으로 처리
SELECT profno, name, pay, bonus, pay+IFNULL(bonus, 0) FROM professor;

 

6. IS NULL, IS NOT NULL

: NULL인 경우의 비교는 = 가 아닌 IS를 사용해서 비교 가능

-- professor 테이블에서 홈페이지가 null이 아닌 교수 목록 조회
SELECT * FROM professor WHERE hpage IS NULL;
SELECT * FROM professor WHERE hpage IS NOT NULL;

-- professor 테이블에서 보너스가 있는 교수들의 교수번호, 이름, 급여, 보너스 조회
SELECT profno, name, pay, bonus FROM professor WHERE bonus IS NOT NULL;
SELECT profno, name, pay, bonus, pay+bonus FROM professor WHERE bonus IS NOT NULL;

---

문자열 함수

1. CONCAT(문자열1, 문자열2)

: 문자열1과 문자열2를 결합

SELECT CONCAT(문자열1, 문자열2) FROM [테이블명];

-- emp 테이블에서 ename(job) 형태로 만들어 출력
SELECT CONCAT(ename, '(', job, ')') AS 'ename_job' FROM emp;

-- SMITH's sla is $sal 형태로 만들어 출력
-- 문자열은 작은 따옴표를 이용하기 때문에 '를 출력하기 위해서는 ''로 작성해야 출력됨
SELECT CONCAT(ename,'''s sal is $',sal) FROM emp;

 

 

2. INSERT(기준 문자열, 시작위치, 변경 문자 길이, 변경할 문자)

: 문자열의 일부를 변경

SELECT INSERT(문자열, 시작 위치, 변경 문자열 길이, 변경할 문자) FROM [테이블명];

-- naver를 kosta로 변경
SELECT INSERT('http://naver.com', 8, 5, 'kosta');

-- student 테이블에서 주민번호 뒤 7자리를 *로 변경
SELECT studno, name, INSERT(jumin, 7, 7, '*******') 주민번호, grade 학년 FROM student;

-- gogak 테이블의 고객번호와 이름 조회(단, 이름은 가운데 글자를 *로 변경)
SELECT gno 고객번호, INSERT(gname, 2, 1, '*') 이름 FROM gogak;

 

 

3. INSTR(문자열, 찾는 특정 문자)

: 문자열 내에서 찾는 특정 문자의 위치(숫자)를 반환

SELECT INSTR(문자열, 특정 문자) FROM [테이블명];

-- 문자열에서 n이 위치한 위치 출력
SELECT INSTR('http://naver.com', 'n');

-- student 테이블의 tel에서 )의 위치 구하기
SELECT INSTR(tel, ')') FROM student;

 

 

4. SUBSTR(문자열, 시작 위치) , SUBSTR(문자열, 시작위치, 출력 문자열 길이), SUBSTRING(문자열, 시작위치, 출력 문자열 길이)

: 문자열에서 시작 위치를 기준으로 문자열 반환 (출력 문자열의 길이가 정해져 있지 않으면 끝까지 출력)

: 문자열의 비교이기 때문에 따옴표를 쓰지 않아도 문자로 인식하여 자동 형변환 진행

SELECT SUBSTR(문자열, 시작 위치, 출력 문자열 길이) FROM [테이블명];
SELECT SUBSTR(문자열, 시작 위치) FROM [테이블명];
SELECT SUBSTRING(문자열, 시작 위치, 출력 문자열 길이) FROM [테이블명];
SELECT SUBSTRING(문자열, 시작 위치) FROM [테이블명];

-- 8번째부터 5개 가져오기
SELECT SUBSTR('http://naver.com', 8, 5);

-- 8번째부터 맨 뒤까지 가져오기
SELECT SUBSTR('http://naver.com', 8);

-- student 테이블에서 tel의 )를 이용하여 지역번호 출력하기
SELECT SUBSTR(tel, 1, INSTR(tel, ')')-1) 지역번호 FROM student;

-- student 테이블에서 주민번호 생년월일이 9월인 학생 조회
SELECT * FROM student WHERE SUBSTR(jumin, 3, 2)='09';

-- 숫자 앞에는 0이 올 수 없기 때문에 09를 문자로 인식하여 자동으로 형변환을 해줌
-- 9를 쓸 경우에는 SUBSTR을 숫자로 변환해주어서 비교해주기 때문에 오류가 나지 않음
SELECT * FROM student WHERE SUBSTR(jumin, 3, 2)=09;

-- student 테이블에서 tel의 가운데 자리 숫자 조회
SELECT SUBSTR(tel, INSTR(tel, ')')+1, (INSTR(tel, '-')-INSTR(tel, ')'))-1) 가운데번호 FROM student;

 

 

5. LENGTH(문자열)

: 공백을 포함해 문자열의 길이(바이트 수)를 반환

: 영어는 한 글자 당 1byte, 한글은 한 글자 당 3byte

: 출력하고자 하는 문자열, 칼럼명에 바로 사용 가능

SELECT LENGTH('칼럼명') FROM [테이블명];

-- length : 문자열의 바이트 길이 구하기 (영문 한 글자 : 1byte, 한글 한 글자 : 3byte)
SELECT email, LENGTH(tel) FROM student;

-- professor 테이블의 이메일에서 @ 뒤의 문자열의 길이를 구하기
SELECT LENGTH(email) FROM professor;
SELECT email, INSTR(email, '@') FROM professor;
SELECT email, LENGTH(SUBSTR(email, INSTR(email, '@')+1)) '@ 뒤 길이' FROM professor;

-- @뒤의 메일 형식을 kost.com으로 변환
SELECT email, INSERT(email, INSTR(email, '@')+1, LENGTH(SUBSTR(email, INSTR(email, '@')+1)), 'kosta.com') 변환메일 FROM professor;

-- 이름의 바이트 길이 출력 (영문과 한글의 문자열 길이가 다름) 문자열의 길이가 아닌 Byte 수, 한글은 한 글자에 3Byte
SELECT ename, LENGTH(ename) FROM emp;
SELECT name, LENGTH(name) FROM student;

 

 

6. CHAR_LENGTH(문자열)

: 문자열의 글자수를 반환

SELECT CHAR_LENGTH(칼럼명) FROM [테이블명];

-- 이름의 길이(바이트 길이 아님) 출력
SELECT ename, CHAR_LENGTH(ename) FROM emp;
SELECT name, CHAR_LENGTH(name) FROM student;

 

 

7. LOWER(문자열), LCASE(문자열)

: 영문자를 소문자로 변환

SELECT LOWER(칼럼명) FROM [테이블명];
SELECT LCASE(칼럼명) FROM [테이블명];

-- emp 테이블의 ename을 소문자로 변환
SELECT ename, LOWER(ename) FROM emp;
SELECT ename, LCASE(ename) FROM emp;

 

 

8. UPPER(문자열), UCASE(문자열)

: 영문자를 대문자로 변환

SELECT UPPER(칼럼명) FROM [테이블명];
SELECT  UCASE(칼럼명) FROM [테이블명];

 

 

9. TRIM(문자열, 지정된 문자) / RTRIM(문자열, 지정된 문자) / LTRIM(문자열, 지정된 문자)

: 왼쪽 및 오른쪽 / 오른쪽 / 왼쪽에서 지정된 문자를 삭제

: 지정된 문자가 주어지지 않은 경우 공백 삭제

SELECT TRIM(칼럼명) FROM [테이블명];

-- trim : 앞 뒤 공백 제거
SELECT LENGTH('test.     '), LENGTH(TRIM('  .test '));
SELECT LENGTH('t e s t'), LENGTH(TRIM('t e s t'));

---------------------------------------

-- ltrim : 왼쪽 공백 제거
SELECT LENGTH('  test   '), LENGTH(LTRIM('  test   '));

---------------------------------------

-- rtrim : 오른쪽 공백 제거
SELECT LENGTH('  test   '), LENGTH(RTRIM('  test   '));

 

 

10. LPAD(문자열, 길이, 채울 문자), RPAD(문자열, 길이, 채울 문자)

: 문자열의 왼쪽/오른쪽을 특정 문자로 채워 넣고 반환

SELECT LPAD(칼럼명, 채울 문자) FROM [테이블명];
SELECT RPAD(칼럼명, 채울 문자) FROM [테이블명];

-- lpad(칼럼명, 길이, 왼쪽에 채울 문자) : 왼쪽을 특정 문자로 채워넣기 (문자가 여러개면 남는 자리만큼 해당 문자를 채워넣음)
SELECT sal, lpad(ename, 20, ' ') 이름 FROM emp;
SELECT sal, lpad(ename, 20, '8') 이름 FROM emp;
SELECT LPAD(email, 20, '123456789') FROM professor;

-- rpad(칼럼명, 길이, 오른쪽에 채울 문자) : 오른쪽을 특정 문자로 채워넣기
SELECT sal, rpad(ename, 20, ' ') 이름 FROM emp;
SELECT sal, rpad(ename, 20, '8') 이름 FROM emp;