May 18, 2021 - MySQL 총정리 - 1

*5월 18일

-19일 넘어가기 직전인 18일이다. 10일 정도에 걸쳐서 DB 기본 개념, 셋업, 설계, DB의 중요도, DB의 역사?!(Oracle, MySQL, MariaDB에 얽힌 ㅎㅎ), SQL 구문에 대해 배웠다. DB가 모든 작업에 있어서 최초 시작이고 기준이 되어야 하는 부분이기에 기본적인 내용은 철저하게 체화시키고 넘어갈 수 있도록 하고 있다.

 

 

1)MySQL 명령 프롬프트 기본 명령어

 

net start mysql // 실행

net stop mysql // 종료

mysql -uroot -p // root 계정으로 접속

mysql -u[아이디] -p로 접속할 수 있다.

mysql -u[아이디] -p[비밀번호]와 같이 입력하면 바로 접속할 수 있다. 하지만 보안 상 매우 좋지 않으므로 철저히 연습용으로만 사용할 것. 

use mysq  // mysql DB 사용을 통해서 비밀번호를 설정하고 권한을 부여할 수 있다.

flush privileges;  //변경사항을 즉시 반영하도록 하는 명령어, 자주 쓰면 과부하됨....

\c   // Type '\c' to clear the current input statement 특정 명령문에 stuck 되었을 때 뒤로가기 명령어!

create database javatest; // 데이터베이스 생성

show databases; // 데이터베이스 목록 조회

use javatest; // 특정 DB 사용

show tables; // 테이블 조회

desc member; 특정 테이블의 모든 속성을 describe 하는 것 <-> order by에서 사용하는 desc는 descending!

alter user '사용자계정명'@'localhost' identified with mysql_native_password by '비밀번호';  //비밀번호 변경

drop database univ; // DB 삭제

 

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| num    | int(10)      | NO   |     | NULL    |       |
| name   | varchar(100) | NO   |     | NULL    |       |
| age    | int(3)       | NO   |     | NULL    |       |
| height | varchar(4)   | YES  |     | NULL    |       |
| memo   | varchar(500) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

 

 

2)DDL(Data Definition language)

-create, drop, alter, rename과 같은 명령어로 flush previleges 없이도 바로 반영되는 명령어임.

-데이터의 전체 골격을 정하는 역할을 함.

 

create table member(    //테이블 생성
    -> num int(10) not null,  //필수값
    -> name varchar(100) not null,    //필수값
    -> age int(3) not null,
    -> blood varchar(5),
    -> height varchar(4),
    -> memo varchar(500)
    -> );

 

create table if not exists emp_copy (  //존재하지않으면~ mysql용 기능;
eno int(11) auto_increment  primary key,  //11자리 숫자가 들어가는 크기, 자동증가, PK 지정.
ename varchar(20) not null,  // varchar는 가변 길이를 가지는 문자열(20자), 즉 20자가 들어오면 꽉채우고, 10자리만 들어오면 10자리에 맞춰서 줄임; (정확히는 20byte.. 영문자 1자가 1byte, 한글은 안맞음.....), null 불가 처리
salary decimal(6,2) default 0,  // (전체길이, 소수점몇자리까지) / 기본값 0으로 배정. double = decimal
dept tinyint(2)     //2자리 수 이하를 배정 시 사용, boolean 대체임, tinyint로 0,1 하고 java 단에서 받아서 쓰면됨.
hireddate timestamp    //mysql은 timestamp 씀!  , current_timestamp(현재시각);
);

 

delete from member where num = 1;  //칼럼 삭제

alter table emps modify age int(10) after ename; //칼럼 순서변경

alter table emps change age num int(10); //칼럼명칭 변경, int, varchar와 같은 속성값은 필수!

alter table user add userGender varchar(20);  //칼럼 추가

alter table user modify userEmail varchar(50); // 테이블 특정 칼럼 타입 변경

alter table user drop userGender; // 특정 필드 삭제

alter table user change userPassword userPass varchar(20); // 특정 필드명칭 변경

alter table user rename table1; //테이블 이름 변경

rename table table1 to user; //테이블 이름 변경

drop table if exists emp_copy cascade; // 테이블이 있으면 삭제

alter table dept_copy add constraint pk_dept primary key (dept);  //dept_copy테이블에 pk_dept라는 명칭으로 PK를 dept칼럼에 추가

alter table dept_copy add dept tinyint(2) primary key; //특정칼럼추가

alter table emp_copy drop column dept; // 특정 칼럼 삭제

create table test1 as select * from emp; // 테이블 복사

create table emp_summary as select empno, ename, job, sal from emp;

 

CREATE USER 'SQLUSER'@'LOCALHOST' identified by 'mysql';  // 로컬호스트 접근권한 가진 유저 생성
create user 'testuser'@'localhost' identified by 'mysql';

rename user 'testuser'@'localhost' to 'tester'@'localhost'; // 유저 아이디 변경

 

 

 

3)DML(Data Manipulation Language)

-select, insert, update, delete 등의 명령어로, 기본 SQL query문임.

-테이블의 row와 column의 모든 데이터를 조작하는 언어이며, 일반적인 백엔드 개발자가 가장 많이 사용함.

 

insert into member(num, name, age) values(1, 'jongki', 31);  //칼럼 지정 시 지정한 것만 넣음

insert into member values(2, 'gu', 30, 'b', '161', '이쁨'); // 칼럼 미지정 시 전부 다 넣어야함

update member set memo = '수강생' where num =1;  //칼럼 수정

UPDATE table_name SET country = '대한민국'; // 테이블 내 모든 값의 country를 변경

UPDATE table_name SET name = '테스트 변경', country = '대한민국' WHERE id = 1105; // 일부값 지정 변경

delete from member where num = 1;  //칼럼 삭제

DELETE FROM table_name; // 테이블 내 전체 데이터 삭제

DELETE FROM table_name WHERE id = 1105; // 테이블 내 일부 데이터 삭제

alter table student drop state; // 특정 칼럼 삭제

alter table student modify dob date not null; // 특정 칼럼 속성 변경

 

insert into emp_copy (ename, salary, hireddate) select ename, sal, hiredate from emp; // 타 테이블 데이터 가져와서 삽입

insert into dept_copy values (90, 'hr', 'busan', 0), (80, 'accounting', 'daegu', 0), (70, 'it', 'jeju', 0);  // 여러 데이터 한꺼번에 insert 하기

update projects set manager=(select eno from emp_copy where dept (select dept from dept_copy where  project=30)) where pno=50; // 2단 subquery 활용하여 업데이트

 

update emp_copy set mgr = (
select manager from projects where pno= (
select project from dept_copy where dept = 70)
) where eno = 10;

 

-비밀번호변경

use mysql; // 비번 변경은 관리자 권한으로 mysql DB 접속 후에만 가능;

update user set authentication_string=password('mysql1') where user='tester';

 

-유저조회

use mysql;

select user, host, authentication_string from user;

 

-유저삭제

drop user 'tester'@'localhost';

 

4)DCL(Data Control Language)

-grant, revoke, commit, rollback 등이 있음.

-데이터에 접근하거나 객체에 권한을 주는 역할을 함.  DB관리자 아닌 이상 그다지 쓸일 없음

-작업 후 반영 위해 flush privileges 필요

 

grant all on work_sql.* to 'dev_sql'@'localhost'; // 권한부여, DB명 to 유저명

show grants for 'sqluser'@'localhost'; // 권한확인

revoke all on sqlex.* from 'tester'@'localhost'; // 권한회수

 

*TCL(Transaction Control Language)

-DCL에서 transaction을 제어하는 명령인 commit과 rollback만 따로 구분해서 TCL이라고 표현하기도 함.

-TCL 사용 위해서는 autocommit을 0으로 잡고 해야함. default가 1임.

 

select @@autocommit; // autocommit status 조회

set autocommit=0; // 셋업

 

*Transaction

-기능적 집합? atm 인출 과정을 하나의 transaction으로 보는 것. 그 기능적 집합 중 하나라도 실패 시, 다 실패로 보고 rollback 시키는 것. 즉, all or nothing임.
-start transaction~commit이 현금 인출의 transaction 범위임... 

-sp1, sp2 순서로 savepoint 만들고, sp1으로 롤백시키면 다시는 sp2로 못감; // 'ROLLBACK TO SAVEPOINT implicitly destroys all savepoints that were established after the named savepoint.'

-

 

5)각종 함수

-기본적으로 select 앞에 붙이기.

 

 

-- 문자관련 함수

concat 칼럼 병합하여 하나의 새로운 칼럼에 출력

SELECT CONCAT(Address, " ", PostalCode, " ", City) AS Address FROM Customers;

 

length 데이터를 길이로

SELECT CustomerName, LENGTH(CustomerName) AS LengthOfNameFROM Customers;

 

lower 소문자변환

SELECT LOWER(CustomerName) AS LowercaseCustomerNameFROM Customers;

 

repeat 여러번 부르기 

SELECT REPEAT(CustomerName, 2) FROM Customers;

 

replace

SELECT REPLACE("XYZ FGH XYZ", "X", "M");

결과값 : MYZ FGH MYZ

 

substr

SELECT SUBSTR(CustomerName, 2, 5) AS ExtractString FROM Customers;

 

substring

SELECT SUBSTRING(CustomerName, 2, 5) AS ExtractString FROM Customers;

 

trim, ltrim, rtrim (left-trim, right-trim) - 앞뒤 공백 제거/왼쪽/오른쪽

SELECT TRIM("    SQL Tutorial    ") AS TrimmedString;

 

lpad, rpad  (left-pad, right-pad) - 양쪽 padding용 문자 추가해서 총길이 맞추기

ELECT LPAD(CustomerName, 30, "ABC") AS LeftPadCustomerName FROM Customers;

 

 

locate - 특정 문자의 위치값(1~)

SELECT CustomerName, LOCATE("a", CustomerName) FROM Customers;

 

format - 소수점 자리수 설정, 반올림

SELECT FORMAT(250500.5634, 0);

 

position - 특정문자 위치값(1~)

SELECT CustomerName, POSITION("a" IN CustomerName) FROM Customers;

 

reverse - 문자 뒤집기

SELECT REVERSE(CustomerName) FROM Customers;

 

-- 숫자관련 함수

abs - 절대값

SELECT ABS(-243.5);

 

avg - 칼럼의 평균

SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);

 

ceil - 올림

SELECT CEIL(25.35);

 

count

select count(*) from room; // DB내 존재하는 데이터 확인

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;

 

floor - 내림

SELECT FLOOR(25.75);

 

max / min  = greatest / least

SELECT MAX(Price) AS LargestPrice FROM Products;

pow, power - 제곱

SELECT POW(4, 2);  //4의 2승

 

round - 반올림

SELECT ROUND(135.374, 2);

 

sqrt - 제곱근 루트값

SELECT SQRT(64); // 8

 

truncate - 자르기

SELECT TRUNCATE(135.375, 2);

 

rand - 랜덤값

 

mod - 몫 

 

sum - 더하기

 

-- 날짜관련 함수

current_date

SELECT CURRENT_DATE();

2021-05-14

 

CURRENT_DATE() + 1

20210515

 

current_time

CURRENT_TIME()

03:06:53

 

 

current_timestamp

CURRENT_TIMESTAMP()

2021-05-14 03:07:13

 

date

DATE("2017-06-15")

2017-06-15

 

 

date_format

DATE_FORMAT("2017-06-15", "%Y")

2017

 

time_format

TIME_FORMAT("19:30:10", "%H %i %s")

19 30 10

 

 

year, month, day, hour, minute // 같은 맥락

YEAR("2017-06-15")

2017

 

now

NOW()

2021-05-14 03:09:50

 

sysdate

SYSDATE()

2021-05-14 03:10:03

 

time

TIME("19:30:10")

19:30:10

 

timestamp

TIMESTAMP("2017-07-23", "13:10:11")

2017-07-23 13:10:11

 

week - 52주 중 몇주차

WEEK("2017-06-15")

24

 

 

-- 날짜연산관련 함수

 

adddate

ADDDATE("2017-06-15", INTERVAL 10 DAY)

2017-06-25

 

 

addtime

SELECT ADDTIME("2017-06-15 09:34:21", "2");

 

dayofmonth

DAYOFMONTH("2017-06-15")

15

 

 

dayofweek // 일요일이 1, 토요일이 7

DAYOFWEEK("2017-06-15") 

5

 

 

dayofyear

DAYOFYEAR("2017-06-15")

166

 

extract
EXTRACT(MONTH FROM "2017-06-15")
6

datediff // (비교1, 비교2)의 차이를 출력
-- '이번달 말일-입사일'   datediff(비교1, 비교2)의 차이를 출력
select empno, ename, hiredate, datediff(last_day(current_date()),  hiredate) as 근무일수
from emp;

last_day
SELECT LAST_DAY("2017-06-15");

subdate // 날짜 감산
SUBDATE("2017-06-15", INTERVAL 10 DAY)
2017-06-05

subtime // 시간 감산
SELECT SUBTIME("2017-06-15 10:24:21.000004", "5.000001");
Try it Yourself »


-- 그외 자주 사용되는 함수

 

case - when~then - else -end

SELECT OrderID, Quantity,

CASE 

WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

if
SELECT IF(500<1000, "YES", "NO");

ifnull  // 첫번째 인자가 null이면, 두번째 인자 리턴, 아니라면 첫번째 인자 그대로
SELECT IFNULL(NULL, "W3Schools.com");

isnull // null이면 1리턴, 아니면 0 리턴
SELECT ISNULL(NULL);  // 1

nullif  // 인자값 2개가 같으면 null 리턴 아니면 첫번째 인자 리턴

 

-함수 관련 예제

 

-- 사원번호 홀수인 사람 출력
select * from emp where empno % 2 = 1;
select * from emp where mod(empno, 2) = 1;

-- 1987년 입사자 출력
select * from emp where year(hiredate) = 1987;

-- 사원이름 M으로 시작하거나 S로 끝나는 직원
select * from emp where ename like 'M%' or ename like '%S';
select * from emp where substr(ename, 1, 1) = 'M' or right(ename,  1) = 'S';

-- 매니저가 없는 사원
select * from emp where mgr is null;

-- '이번달 말일-입사일'
select empno, ename, hiredate, datediff(last_day(current_date()),  hiredate) as 근무일수
from emp;
->datediff(인자1, 인자2)는 인자1과 인자2의 날짜 차이를 구하는 함수임!!

-- 현재 달의 마지막 날
select last_day(current_date());

-- mgr값이 null이면 CEO로 바꿔서 출력
select empno, ename, ifnull(mgr, 'CEO') as mgr from emp where mgr is null;

-- 특정 칼럼 뒷 자리를 비밀번호와 같이 '**'으로 처리하기
select empno, replace(empno, substr(empno, 3, 2), "**") as masking_empno,
ename, replace(ename, substr(ename, 2), "****") as masking_ename
from emp where length(ename) >= 5 and length(ename) <= 6;

 

 

 

6)참조 관계
-FK(foreign key)는 외래키, 즉 타 테이블 참조키라고 보면됨(직접 입력해서 가져오는게 아니라 외부에서 참조되어 만들어진다는 것)
-받는 테이블이 FK를 가지게 됨.
-STUDENT_ID는 PK이면서 동시에 FK임.
-STUD_HOBBY 입장에서는 STUDENT_ID는 받아오고, HOBBY는 직접 생성;

 

PK, FK 관련 수업 예시

 

*참조 관계 추가 예시(oracle 사 기본 테이블 예제 사용 emp, dept. etc....)

-참조 관계 설정 코드

-emp_copy테이블의 dept는 dept_copy의 pk인 dept를 참조하라는 설정

 

alter table EMP_COPY
add constraint FK_EMPCOPY foreign key (DEPT) // FK 제약 조건 추가, 
references DEPT_COPY (DEPT);  // 'references' [참조할테이블(대상칼럼)]. 즉 원본은 dept_copy pk로 존재.
on delete set NULL  //삭제 시 NULL값으로
on update CASCADE; //업데이트되면 참조, 즉 함께 업뎃 하라는 것;

 

*FK 추가/삭제/확인

 

-추가
alter table [테이블명] add constraint foreign key(컬럼명) references [참조할테이블(대상칼럼)][ON DELETE CASCADE / ON UPDATE CASECADE]; // 기타 조건, cascade가 붙으면 참조 제공 테이블(부모 테이블)에 맞춰서 각각 삭제/업뎃 하라는 설정

 

alter table table_name add constraint fk_name key_kind (col_name)
references table_name (col_name);

-삭제
alter table [테이블명] drop foreign key [제약조건명];

-확인
select * from information_schema.table_constraints where table_name = 'table_name';
select * from information_schema.table_constraints where constraint_schema = 'DB_name';

*foreign key 관련 용어

-Cascade : 말 그대로 cascading하는 것. 부모가 변경되면 자식도 변경되고, 제거되면 제거된다.

-delete set null : 삭제시 null 처리

-No action : 참조무결성이고 뭐고 간에 다 무시하겠다는 것.

-Restrict : 글자 그대로 '제한', 자식이 존재하는데 부모를 지운다던가 데이터를 고치려고 하면 warning msg 뜸!

 

 

7)기타

-subquery는 일반적으로 3단계 정도,, 많으면 6단계까지. 하지만 3단계 이상가면 속도저하가 심해짐.

 

-select 절, 읽어들이는 순서가 중요함(from ->  where -> select)
select eno, ename from emp_copy;
select eno, ename, dept from emp_copy where mgr=3;

-기본 select 구문 읽는 순서(from -> where -> group by -> having -> order  by)
select dept, count(eno) from emp_copy where eno > 0 group by dept
having dept > 60 order by dept desc;

-내부 쿼리가 있는 경우 (subquery from -> subquery select -> from  -> where -> group by -> having -> order by)
select dept, count(eno) from (select eno, ename, salary, dept  from emp_copy) where eno > 0 group by dept
having dept > 60 order by dept desc;

-distinct : 중복된 값은 하나로 표현해서 출력
select distinct dept from emp_copy;

 

- primary key 추가
alter table student
add constraint PK_STUDENT primary key (STUDENT_ID);

- primary key 제거
alter table dept_copy
drop primary key;

 

-SQL에서 같다는 '=',   '==' 아님!!

 

*기타 DB 역사
-Oracle유료 vs MySQL무료 경쟁하다가  GPL(general public license), 2010년 후반에 oracle이 mysql을 인수해버림!!
-mysql 5.대 버전은 오리지널, 8.대 버전은 oracle에게 인수되고 나서 생긴것으로 약간 불안함;
-mariaDB가 이 사건 이후에 또 나와서 무료로 DB 만듬(천주교 maria), 10.대로 시작함....   우리버전이야~

 

8)Where

-최우선은 괄호 > NOT 연산자 > 비교연산자, SQL 비교 연산자 > AND > OR
-데이터값은 대소문자 구분함; 칼럼이나 테이블명, 명령어는 대소문자 구분하지 않음.
-조건절로하면 대소문자 관계없이 찾아주지만, 실제 데이터값은 대소문자 구분되어 들어감.

 

select * from emp where sal > 3000;
select * from emp where sal between 1000 and 3000;
select * from emp where sal = 1600;
select * from emp where comm is not null;

-- 같지 않다  <> 또는 !=, DB에서는 <>를 주로 씀.
select * from emp sal <> 1500;
select * from emp sal != 1500;

 

-- 10이거나 20인 것 / 10이거나 20이 아닌 것
select * from emp where deptno in (10, 20, 30);
select * from emp where deptno not in (10, 20);

 

-- ename이 J로 시작하는 사람, K로 끝나는 사람; %는 wild card!
select * from emp where ename like 'J%';
select * from emp where ename like '%K';

-- 특정 문자 포함하는 것
select * from emp where ename like '%C%';

-- ename의 2번째 자리가 I인 사람, _ 개수가 문자 개수임.
select * from emp where ename like '_I%';

-- ename의 뒤에서 3번째 자리가 N인 사람, _ 개수가 문자 개수임.
select * from emp where ename like '%N__';

 

-- AND, OR 조건
select * from emp where sal > 1000 and job='manager';
select * from emp where sal > 3000 or comm is not null;

-- not like
select * from emp where ename not like 'S%';

 

-- And, Or 중첩 사용
select empno, ename, job, sal, deptno from emp where (sal > 2000)  and (deptno = 20 or deptno = 30);

-- not and 조건
select * from emp where not sal >= 2000 and sal <= 3000;

-- 특정 문자 포함, not between / 3중 and 조건
select ename, empno, sal, deptno from emp where (deptno = 30) and  (ename like '%E%') and (sal not between 1000 and 2000);

-- 상급자o & 매니저x & 직책 a or b & 이름 2번째가 L 이 아닌 사람
select * from emp where (comm is null) and (mgr is not null)
and (job = 'manager' or job = 'clerk') and (ename not like  '_L%');

 

9)In, any & all

 

*in조건
-여러 값을 OR 관계로 묶어 나열하는 조건을 WHERE 절에 사용할 때 쓸 수 있는 키워드

-IN 연산자는 조건의 범위를 지정하는 데 사용된다. 값은 콤마( , )로 구분하여 괄호 내에 묶으며, 이 값 중에서 하나 이상과 일치하면 조건에 맞는 것으로 처리됨.

 

*IN조건 장점
-목록에 넣을 값이 여러개 일때, IN 연산자가 OR보다 쓰기도, 보기도 이해하기도 쉽다.
-IN을 사용하면 조건 순서를 보다 쉽게 관리 할수 있고, 연산자 수도 줄어든다
-IN 연산자가 OR 연산자 보다 실행 속도가 빠르다
-IN 연산자안에 다른 SELECT 문(서브쿼리)를 넣을 수 있다.

 

-예시

-- OR조건 : WHERE Condition = 'A' OR Condition = 'B'
-- IN조건 : WHERE Condition IN ('A','B')

 

*Any조건 & all조건
-any는 하위 쿼리에서 하나의 조건만 만족하면 true / all은 모든 값이 만족하면 true
-결국 ANY는 나올 수 있는 모든 조건에 OR 연산을 수행한것과 동일한 결과를 얻는다.  
-ALL은 나올 수 있는 모든 조건에서 AND 연산 수행

 

 

10)subquery

-select문에는 scala subquery / from에는 inline view / where에는 subquery로 불림.

 

10-1)단일행 서브쿼리
-결과값이 하나, 비교연산자 사용 가능( >=, <=, ==)

 

-- 단일행 서브쿼리
SELECT dname FROM dept WHERE deptno = (SELECT deptno FROM emp  where ename ='SMITH');
select ename, sal from emp where sal > (select avg(sal) from  emp);

10-2)다중행 서브쿼리
-결과값이 여러개, 논리연산자까지 사용 가능(and, or 등등)

-- 다중행 서브쿼리
-- 서브쿼리 리턴 값이 여러개 -> 비교 연산이 불가능함. 'SQL Error  [1242] [21000]: Subquery returns more than 1 row'
select ename, sal from emp where deptno = (select distinct deptno  from emp where sal >= 3000); //에러 출력
select distinct deptno from emp where sal >= 3000;  //중복값 제외하고도 2개   // 중복값제외
select deptno from emp where sal >= 3000; // 중복값 같이 나옴

--급여가 3천을 넘는 사원을 포함하는 부서에서 일하는 사람들
-- in은 부등호 들어가면 안됨!!  'deptno = in' -> X!!
select ename, sal, deptno from emp where deptno in (select deptno from emp where sal >= 3000)
order by deptno;

-- 결과값이 다중으로 나옴; 30번 부서 급여 최대치보다 큰놈만, 즉 30번 부서가면 연봉 탑 먹는애들 리스트
select ename, sal from emp where sal > (select max(sal) from emp where deptno=30);
select sal from emp where deptno=30;

-- 결과값이 여러개 나오는 상황,, 부등호에 따라 최소값, 최대값 구하기
-- all은 min, max임 주로..
-- all은 앞 뒤 조건을 모두 만족하는 것....?!  &&이라고 생각하면됨!!! any, in보다 사용빈도는 낮음   / 즉 하단 예제에서는 내부 쿼리 만족 + 부등호 만족
select ename, sal from emp where sal > all (select sal from emp where deptno=30);  //상단 max(sal)과 같은 개념, 최대로 잡히네..~

-- 30번 부서 최소 급여보다 더 큰 급여 받는 사람
select ename, sal from emp where sal > (select min(sal) from emp  group by deptno having deptno = 30);  

-- Any는 비교 연산자 가능  <-> in은 기본적으로 매칭.. 비교 연산자  사용 자체가 안됨..
-- any는 조건을 하나만 만족하는것... OR 조건!
-- 나올 수 있는 모든 조건들 중 OR로!!
select ename, sal from emp where sal > any (select sal from emp  where deptno = 30);   //

30번 부서 급여 전체가 하기와 같다면,

 

1600
1250
2850
1500
950

부등호가 sal > 이기에, 최하점인 950점 기준으로 큰 값들이 다 출력된다는 것! 내부쿼리문 출력은 무조건 포함 +@로 부등호 조건 포함

 

10-3)다중칼럼 서브쿼리

-- 다중칼럼 서브쿼리, where(인자, 인자2)라면, in에서 구한 값을  where에 인자로 던지는 것.
select empno, ename, sal from emp where (deptno, sal)
in (select deptno, min(sal) from emp group by deptno);  //각 부서별 최소 샐러리를 가진 

-- from 절에 사용하는 서브쿼리 -> 인라인 뷰 혹은 동적 뷰로 불림.
-- 최초에 가져오는 테이블의 데이터(로우,칼럼)의 양이 매우 많고 조건이  복잡할 때 사용///
select empno, ename, sal from emp where sal > 1000 and deptno = 30;
select empno, ename, sal from (select empno, ename, sal from emp  where deptno = 30) v where sal > 1000;

-- select 절에 사용하는 서브쿼리 => 스칼라 서브쿼리(칼럼 대신에 옴);,  스칼라는 세로로 된 하나의  칼럼이라고 생각하면됨.
-- 연관성 있게 조건을 부여하는게 중요함!!
select ename, sal, e1.deptno, (select round(avg(sal)) from emp e2  where e2.deptno = e1.deptno) as 부서별평균급여
from emp e1;

select ename, (select dname from dept d where d.deptno  = e.deptno) dname, job from emp e where job ='manager';  // where로 dept테이블과 emp 테이블의 deptno를 일치시킨 후, dname을 가져나옴!!

 

 

11)수업 예제 짬뽕..

 

-- sam1 테이블 사원 중 급여가 10000 이상인 사원들의 급여를 5000씩  삭감;
select * from sam01 where sal > 10000;
update sam01 set sal = sal - 5000 where sal > 10000;

-- sam02 테이블 생성; emp 테이블의 ename, sal, hiredate, deptno  칼럼으로만 구성
create table sam02 as select ename, sal, hiredate, deptno from  emp;

-- sam02 테이블 중 dallas에 위치한 부서의 소속 사원들의 급여를 1000  인상하기; dallas는 deptno = 20임!
select * from sam02 where deptno = 20;
update sam02 set sal = sal + 1000 where deptno = (select deptno  from dept where loc = 'dallas');

-- sam02 테이블의 모든 사원의 급여와 입사일을 이름이 king인 사원의  급여와 입사일과 같게 변경;
select * from sam02;
update sam02 set sal = (select sal from emp where ename='king'),
hiredate = (select hiredate from emp where ename='king');

-- sam01 테이블 중 직급이 없는 사원 삭제; null값 삭제!
select * from sam01;
delete from sam01 where job is null;

-- sam02 테이블에서 research 부서 소속 사원들 삭제
select * from sam02;
delete from sam02 where deptno = (select deptno from dept where dname = 'research');

 

-- subquery문 예제
select ename, deptno from emp where deptno = (select  deptno from emp where ename="scott");
select * from emp where job = (select job from emp  where ename="scott");
select ename, sal from emp where sal >= (select sal  from emp where ename="scott");
select * from dept;

-- 다중값 리턴 시, '='이 아닌 'in'으로 !! dallas에서  근무하는 사원 정보~
select ename, deptno from emp where deptno in(select  deptno from dept where loc="dallas");

-- sales 부서에서 근무하는 사원 정보
select ename, sal, deptno from emp where deptno in(select deptno from dept where dname="sales");

-- row값만 매칭 될 수 있다면, 꼭 칼럼명 같지 않아도 됨!(mgr, empno) king이 직속상관인  사람들~
select ename, sal from emp where mgr = (select empno  from emp where ename="king");

-- smith와 동일한 직급을 가진 사원의 이름과 직급
select ename, job from emp where job = (select job from emp where ename="smith");

-- 다중
-- 부서별로 가장 급여를 많이 받는 사원의 정보, 다중 칼럼  대표적인 예제... max(sal)만 던지면 가장 급여가 낮은 부서의  최대치 기준으로 가져와서, 높은부서의 값 2개가 출력됨
select empno, ename, sal, deptno from emp where (deptno, sal) in (select deptno, max(sal) from emp  group by deptno);  //다중은 반드시 in 조건!!, 그룹은 group by!

-- 9번 job이 manager인 사람이 속한 부서의 부서번호와 부서명  지역 출력.
select deptno, dname, loc from dept where deptno in(select deptno from emp where job ='manager');

-- 10번 영업 사원들보다 급여를 많이 받는(avg) 사원들 /  영업직은 제외
select ename, sal, job from emp where sal > all(select sal from emp where job = 'salesman') and job <>  'salesman';

-- 11번 영업 사원들의 최소 급여보다 많이 받는 사원들의  이름과 급여와 직책을 출력하되 영업 사원은 출력하지 않음;   // max(sal)로 가져와도 4천이라치면 4천이상만 꼽힘,, sal로 가져와도 전체인데, 그거보다 어차피 큰놈이니깐 관계없다는 것임..
select ename, sal, job from emp where sal >  all(select sal from emp where job = 'salesman') and job  <> 'salesman';

-- 12번 직책이 SALESMAN인 사원이 받는 급여들의 최대 급여보다 많이 받는 사원들의  이름과 급여를 출력하되 부서번호가 20번인 사원은 제외;
select ename, sal, deptno from emp where sal >  all(select sal from emp where job = 'salesman') and  deptno <> 20;

 

-- 1번
select empno, ename, hiredate, deptno from emp where  ename like '%S%';

-- 2번
select * from emp where comm is not null;

-- 3번
select ename, deptno, sal from emp where deptno=30 and  sal >= 1500;

-- 4번, 합치는방법 + ifnull사용(널아니면 1번인자, 널이면 2번인자 반환)
select ename, sal, comm, (sal+ ifnull(comm,0)) as total  from emp order by total desc;

-- 5번
select ename, deptno, sal, ((sal*12) + (sal*1.5)) as  'yearly income' from emp where deptno=30;

-- 6번, 하루 8시간 근무 기준
select ename, sal, round((sal / 12 / 5), 1) as 'hourly  pay' from emp where deptno = 20;

-- 7번, 각 부서별 같은 업무를 하는 사람의 인원수; 복습필요  꽤 오래걸림... group by를 2개로 하는게 핵심... count안에는 아무거나 공통
-- 소계 withrollup 넣어서, 각 부서별 인원수까지 추가
select deptno, job, count(empno) from emp group by  deptno, job with rollup;

-- 8번
select deptno, round(avg(sal)) as 평균월급, sum(sal),  max(sal), min(sal) from emp group by deptno order by avg(sal) desc;

-- 9번 풀이
-- group by에서 읽어온 데이터의 조건을 부여할 때는 having
select deptno, job, count(empno) from emp group by deptno, job with rollup
having job is not null and count(empno) >= 4;

-- 10번 의외로 쉽네??? 다시 익혀보기.. 왜 삽질?!   // select deptno이있으면, group by에도 deptno가 있어야함!! 기본 일치 생각하기;
select deptno, round(max(sal+ifnull(comm, 0)),1) as  최대, round(min(sal+ifnull(comm, 0)),1) as 최소,  round(avg(sal+ifnull(comm, 0)), 1) as 평균 from emp group by deptno;

-- 11번 월별 입사자수... 꽤 오래걸렸음.. 복습필요; group by month(hiredate)가 핵심;
select month(hiredate) as 월, count(empno) as 입사자수  from emp group by month(hiredate);

-- 11번 +@, 연월
select substr(hiredate, 1, 7) as 입사년월, count(empno)  as 인원수
from emp group by substr(hiredate, 1, 7);

-- 12번
select ename, if(length(ename) >= 5, "청팀", "백팀") as  팀 from emp order by 팀;

-- 13번
select ename, sal, if(sal >= 2000, sal*1.1, sal*1.2) as  급여인상 from emp;