(MariaDB)SQL기본

SQL

1. 데이터베이스 확인 SHOW DATABASES;
2. 사용할 데이터베이스 지정 USE 데이터베이스;
3. 테이블 확인 SHOW TABLES;
4. 더 자세히 조회 SHOW TABLE STATUS;
5. 테이블의 컬럼정보 조회 DESCRIBE 테이블; 혹은 DESC 테이블;

USE구문

사용할 데이터베이스 지정

USE 데이터베이스 이름;

SELECT문

select문 작성할 때 from -> where -> select 작동

• SELECT 컬럼명 AS 새로운 컬럼명 FROM 테이블 WHERE 조건
  위의 코드를보면 새로운 컬럼명으로 조건을 검색하면 오류가 발생한다. 왜냐하면 from -> where -> select순으로 작동하기 때문에 where에서 새로운 컬럼명을 인식하지 못하기 때문이다.
• 작은 따옴표 큰따옴표 구분 없음

SELECT 구문

데이터베이스 내의 테이블에서 원하는 정보를 추출하는 명령

SELECT 열이름
FROM 테이블이름
WHERE 조건

관계 연산자

AND -여러 개의 조건을 모두 만족하는 데이터
OR - 여러 개의 조건 중 최소 하나만 만족해도 되는 데이터
조건연산자(=,<,>,<=,>=,!=등) 와 관계 연산자(NOT, AND, OR 등)을 잘 조합해 쿼리 생성

SELECT *
FROM 테이블명
WHERE 조건 1 AND(OR) 조건 2 AND(OR) 조건3 ...;

BETWEEN AND

연속적인 값을 가지고 있는 범위 내의 데이터를 추출할 때 사용

SELECT *
FROM 테이블
WHERE 컬럼명 BETWEEN 범위1 AND 범위2

IN()

연속적인 값이 아닌 이산적인 값을 검색할 때 사용, OR과 같은 기능으로 사용가능

SELECT *
FROM 테이블
WHERE 컬럼명 IN('데이터1','데이터2','데이터');

LIKE

비교하려는 문자기준으로 앞뒤로 문자열을 비교해서 처리가능

SELECT *
FROM 테이블
WHERE 컬럼명 LIKE '%비교하려는문자열'

• 문자열의 내용검색
• DB성능 저하의 원인 ( 큰 DB는 피할 것)

1. %문자열 - 문자열이 가장 뒤에 글자인 데이터
2. 문자열% - 문자열이 가장 앞에 글자인 데이터
3. __문자열 - 문자열과 앞의 두칸을 포함해서 문자열 +2의 길이를 가질 수 있는 데이터

---

연습문제

Q1) 사원 테이블(EMP)의 모든 데이터를 출력하자.

SELECT *
FROM emp;

Q2) 사원 테이블에서 사원의 이름(ENAME), 사원의 번호(EMPNO), 월급(SAL)을 출력하자.

SELECT ENAME,EMPNO,SAL
FROM emp;

Q3) 사원 테이블에서 사원의 이름과 연봉을 출력하자.

SELECT ENAME,SAL*12 AS YSAL
FROM emp;

Q4) 사원의 이름, 입사일(HIREDATE), 부서번호(DEPTNO)를 출력하자.

SELECT ENAME,HIREDATE,DEPTNO
FROM emp;

Q5) 사원의 이름과, 사원을 관리하고있는 관리자(MGR)를 출력하자.

SELECT ENAME,MGR
FROM emp;

Q6) 사원 테이블에서 사원의 이름, 월급, 커미션(COMM)을 출력하자.

SELECT ENAME,SAL,COMM
FROM emp;

Q7) 사원 테이블의 모든 데이터를 "OO님이 0000-00-00에 입사를 하고 OO의 월급을 받습니다." 형식인 하나의 컬럼으로 출력하자.

SELECT CONCAT(ENAME,'님이',HIREDATE,'에 입사를 하여',SAL,'의 월급을 받습니다')AS 'col'
FROM emp;

Q8) 부서 테이블(DEPT)의 모든 데이터를 출력하자.

SELECT *
FROM dept;

Q9) 부서 테이블의 구조를 보자

DESC dept;

---

ANY

SELECT USERNAME,HEIGHT 
FROM usertbl 
WHERE HEIGHT>= ANY(SELECT HEIGHT FROM usertbl WHERE ADDR='경남');

OR과 같은 방식으로 작동

ALL

SELECT USERNAME,HEIGHT 
FROM usertbl 
WHERE HEIGHT>= ALL(SELECT HEIGHT FROM usertbl WHERE ADDR='경남');

AND 와 같은 방식으로 작동

IN

SELECT USERNAME,HEIGHT 
FROM usertbl 
WHERE HEIGHT IN(SELECT HEIGHT FROM usertbl WHERE ADDR='경남');
            (==ANY 와 같음)

Q1) 사원테이블에서 사원번호가 '7844' 인 사원의 사원번호, 이름, 월급을 출력하자.

SELECT EMPNO,ENAME,SAL
FROM emp
WHERE EMPNO=7844;

Q2) 사원테이블에서 'SMITH'의 사원번호, 이름, 월급을 출력하자.

SELECT EMPNO,ENAME,SAL
FROM emp
WHERE ENAME='SMITH';

Q3) 사원테이블에서 입사일이 1980년 12월 17일인 사원의 모든 데이터를 출력하자.

SELECT *
FROM emp
WHERE HIREDATE='1980-12-17';

Q4) 1980년도에서 1982년도 사이에 입사한 사원의 이름과 입사일을 출력하자.

SELECT ENAME,HIREDATE
FROM emp
WHERE HIREDATE BETWEEN YEAR('1980-01-01') AND YEAR('1982-12-31');

Q5) 월급이 2000 이하인 사원의 이름과 월급을 출력하자.

SELECT ename,sal
FROM emp
WHERE sal <=2000;

Q6) 월급이 1000에서 2000 사이인 사원의 이름과 월급을 출력하자.

SELECT ename,sal
FROM emp
WHERE sal>=1000 AND sal<=2000;

Q7) 사원번호가 7369, 7499, 7521인 사원들의 이름과 월급을 출력하자.

1. SELECT ename,sal
   FROM emp
   WHERE empno='7369' OR empno='7499' OR empno='7521';
2. SELECT ENAME,SAL
   FROM emp
   WHERE EMPNO IN ('7369','7499','7521');

---

서브쿼리(하위쿼리)

WHERE 조건에서 필요한데이터를 리터럴로 넣는 것 대신에 쿼리를 사용해서 표현

• 서브쿼리의 결과물이 여러개의 값을 비교하도록 만들면 오류 (이런 것들을 처리하기 위해서 ANY나 ALL을 사용)

SELECT *
FROM 테이블1
WHERE 컬럼명
연산자 (SELECT 컬럼명 FROM 테이블2 WHERE 조건)

---

Q1)부서번호가 10번인 사원들과 같은 월급을 받는 사원의 이름과 월급을 출력하자.

1. SELECT ename,sal
   FROM emp
   WHERE SAL = ANY(SELECT SAL FROM emp WHERE DEPTNO=10);

2. SELECT ename,sal
   FROM emp
   WHERE SAL IN (SELECT SAL FROM emp WHERE DEPTNO=10);

Q2)직업이 'CLERK'인 사원과 같은 부서에서 근무하는 사원의 이름과 월급, 부서번호를 출력하자.

1. SELECT ENAME,SAL,DEPTNO
   FROM emp
   WHERE DEPTNO IN (SELECT DEPTNO FROM emp WHERE JOB='CLERK');

2. SELECT ENAME,SAL,DEPTNO
   FROM emp
   WHERE DEPTNO =ANY(SELECT DEPTNO FROM emp WHERE JOB='CLERK');

Q3)'CHICAGO'에서 근무하는 사원들과 같은 부서에서 근무하는 사원의 이름과 월급을 출력하자.

1. SELECT ENAME, SAL
   FROM emp
   WHERE DEPTNO IN(SELECT DEPTNO FROM dept WHERE LOC='CHICAGO');

2. 조인
   SELECT emp.ENAME,emp.SAL
   FROM emp,dept
   WHERE emp.DEPTNO=dept.DEPTNO
   AND dept.DEPTNO IN (SELECT DEPTNO FROM dept WHERE LOC='CHICAGO');

Q4)부하직원이 있는 사원의 사원번호와 이름을 출력하자. 자기 자신이 다른 사원의 관리자인 사원)

1. SELECT empno,ename
   FROM emp
   WHERE empno IN(SELECT mgr FROM emp WHERE mgr IS NOT NULL);

2. SELECT EMPNO,ENAME
   FROM emp
   WHERE EMPNO IN(SELECT MGR FROM emp);

Q5)부하직원이 없는 사원의 사원번호와 이름을 출력하자.

• not in 은 != , <> 와 같음
• nvl(컬럼,지정값) : 해당 컬럼의 값들 중에 null을 찾아서 지정한 값으로 대체

1.
SELECT empno,ename
FROM emp
WHERE empno NOT IN(SELECT mgr FROM emp WHERE mgr IS NOT NULL);

2.
SELECT EMPNO,ENAME
FROM emp
WHERE EMPNO NOT IN(SELECT nvl(MGR,0) FROM emp);

Q6)'KING'에게 보고하는 사원의 이름과 월급을 출력하자. (관리자가 'KING'인 사원)

SELECT ename,sal
FROM emp
WHERE mgr IN(SELECT empno FROM emp WHERE ename='king');

Q7)20번 부서의 사원 중 가장 많은 월급을 받는 사원보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하자.

SELECT ENAME,SAL
FROM emp
WHERE SAL > (SELECT MAX(SAL) FROM emp WHERE DEPTNO=20)

Q8)직업이 'SALESMAN' 인 사원중 가장 큰 월급을 받는 사원보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하자.
단, MAX함수를 사용하지 말자.(ANY, ALL 연산자)

SELECT ename,sal
FROM emp
WHERE sal >all(SELECT sal FROM emp WHERE job='salesman');

ORDER BY

원하는 순서대로 정렬하여 출력

• 결과물에 영향을 미치지 않음
• 기본값 : 오름차순
• 내림차순 : DESC
• SELECT, FROM, WHERE, GROUP BY, HAVING 중 제일 뒤
• MariaDB의 성능을 상당히 떨어뜨릴 소지가 있기 때문에 꼭 필요한 경우가 아니라면 사용X

SELECT USERNAME,HEIGHT FROM usertbl ORDER BY HEIGHT ASC;
SELECT USERNAME,HEIGHT FROM usertbl ORDER BY HEIGHT DESC;
SELECT USERNAME,ADDR,HEIGHT FROM usertbl ORDER BY  ADDR ASC,HEIGHT DESC;

DISTINCT

중복된 것은 하나만 남김

SELECT DISTINCT * FROM 테이블;

LIMIT

출력개수를 제한
-ORDER BY 상위 N개 출력

SELECT * FROM 테이블 LIMIT(N);

CREATE TABLE ..SELECT

• 제약조건은 복사되지 않음

CREATE TABLE 테이블명 SELECT 복사할 열 FROM 복사 테이블;