해리코딩

Top n 질의

형식)

-- top n  질의

SELECT 
    stu_no, stu_name, stu_height
FROM
    (SELECT 
        stu_no, stu_name, stu_height
    FROM
        student
    WHERE
        stu_height IS NOT NULL
    ORDER BY stu_height DESC
    LIMIT 5) AS sub;
      -- 질의에 대한 별명을 설정하지 않으면 에러가 발생
      

예시) 가장 최근에 입사한 3명의 사원번호, 사원이름, 입사일 검색

     
-- 가장 최근에 입사한 3명의 사원번호, 사원이름, 입사일 검색
SELECT 
    empno, ename, hiredate 
    FROM emp WHERE hiredate IS NOT NULL
    ORDER BY hiredate DESC LIMIT 3;
  

예시) 부서별 평균 급여가 가장 큰 부서 2개의 부서이름을 검색

  

-- 부서별 평균 급여가 가장 큰 부서 2개의 부서이름을 검색
select dname
from (select dname, avg(sal) 
from emp, dept where emp.deptno = dept.deptno 
group by dname order by 2 desc limit 2)as sub;

Join

• 두 개 이상의 테이블을 서로 묶어서 새로운 테이블로 만들어 내는것

Cross join (교차조인)

-- 교차 조인(cross join)
-- 두 개 이상의 테이블에서 가능한 모든 조합을 만들어내는 조인 방식
select student.*, enrol.*
from student
cross join enrol;

Inner join (이너조인 = equi join 이퀴조인))

• inner join = 두 테이블 간에 중복되는 데이터, 즉 양쪽 테이블 모두에 존재하는 데이터만을 결과로 반환하는 조인 방식
• equi join = 조인 조건이 등호(=)를 사용하는 경우
• where절에서 = 연산자 사용

-- inner join(equi join)
select stu.stu_no, stu_name, stu_dept, enr_grade
from student stu, enrol en
where stu.stu_no = en.stu_no;

-- 점수가 70점 이상인 학생 이름 검색
SELECT 
    stu_name
FROM
    student s,
    enrol e
WHERE
    s.stu_no = e.stu_no AND enr_grade >= 70;

-- 점수가 70점 이상인 학생 이름 검색(equi-join)
SELECT 
    stu_name
FROM
    student s,
    enrol e
WHERE
    s.stu_no = e.stu_no AND enr_grade >= 70;

-- 강종영 교수가 강의하는 과목을 수강하는 학생의 이름을 검색 (equi join)
SELECT 
    stu_name
FROM
    student s,
    enrol e,
    subject su
WHERE
    su.sub_no = e.sub_no
        AND e.stu_no = s.stu_no
        AND sub_prof = '강종영';

-- 컴퓨터개론을 수강하는 학생들의 학번과 이름을 검색해라(equi join)
SELECT 
    stu_name, s.stu_no
FROM
    student s,
    enrol e,
    subject su
WHERE
    su.sub_no = e.sub_no
        AND e.stu_no = s.stu_no
        AND sub_name = '컴퓨터개론';

Natural join (내추럴 조인)

• 이너조인보다 편리함 / 네추럴조인 앞뒤로 합치고 싶은 테이블 입력

-- natural join
select stu.stu_no, stu_name, stu_dept, enr_grade
from student stu natural join enrol en;

-- 학생테이블과 수강테이블을 natural join
SELECT 
    *
FROM
    student natual
        JOIN
    enrol;

-- 과목이름과 학번, 점수를 검색 natural join
SELECT 
    sub_name, enrol.stu_no, enr_grade
FROM
    subject
        NATURAL JOIN
    enrol;

-- 점수가 70점 이하인학색 이름 검색(natural join)
SELECT 
    stu_name
FROM
    student
        NATURAL JOIN
    enrol
WHERE
    enr_grade <= 70;

join~using

• 조인하려는 두 테이블에서 기준 열의 이름이 정확히 일치할 때 사용할 수 있으며, 쿼리를 간결하게 만들고 싶을 때
  유용합니다. 단, 단일 열에 대한 동등 비교만 가능합니다.

select stu.stu_no, stu_name, stu_dept, enr_grade
from student stu join enrol using(stu_no);

-- 과목이름과 학번, 점수를 검색해라(join using)
SELECT 
    sub_name, enrol.stu_no, enr_grade
FROM
    subject
        JOIN
    enrol USING (sub_no);

SELECT 
    stu_name, enr_grade
FROM
    student s
        JOIN
    enrol e USING (stu_no)
WHERE
    enr_grade >= 60;

join~on

• 두 테이블 간의 조인 조건을 자유롭게 지정할 수 있으며, 열 이름이 다르거나 복잡한 조건을 사용해야 하는 경우에
  적합

select stu.stu_no, stu_name, stu_dept, enr_grade
from student stu join enrol en on stu.stu_no = en.stu_no;

Outer join(외부조인)

• 한 쪽에는 데이터가 있지만, 다른 한 쪽에는 데이터가 없는경우 데이터가 있는 쪽 테이블의 내용을 모두 출력
• left outer join  - 왼쪽에 있는 테이블의 모든 결과를 가져온 후 오른쪽 테이블의 데이터를 매칭
• right outer join  - 오른쪽에 있는 테이블의 모든 결과를 가져온 후 왼쪽 테이블의 데이터를 매칭

select*
from subject natural join enrol;

select a.*, sub_name
from enrol a right outer join subject b
on a.sub_no = b.sub_no;

select a.*, sub_name
from enrol a left outer join subject b
on a.sub_no = b.sub_no;

Self join

-- self join
select a.empno as 사원번호, a.ename as 사원이름,
b.empno as 상급자사원번호, b.ename as 상급자이름 
from emp a join emp b on a.mgr=b.empno;

SQL 분류

DML(Data Manipulation Language)

1. 데이터 조작 언어
2. DML 구문이 사용되는 대상은 테이블의 행
3. DML 사용하기 위해서는 꼭 그 이전에 테이블이 정의되어 있어야 함
4. SQL문 중 SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당
5. 트랜잭션이 발생하는 SQL도 이 DML에 속함

• 테이브르이 데이터를 변경(입력/수정/삭제)할 때 실제 테이블에 완전히 적용하지 않고, 임시로 적용시키는 것
• 취소가능

Insert

데이터 삽입

 insert into a_enrol(sub_no,stu_no,enr_grade)
values(108,20151062,92);

개수와 필드에 맞게 값 넣으면 필드 생략가능

insert into a_enrol values(109,20151063,95);

insert into a_enrol(sub_no,stu_no) values (110, 20152088);

insert into a_enrol
select *from enrol
where stu_no like '2015%';

select *from a_enrol;

예시)

-- 학생테이블로부터 학년이 1 또는 2학년인 조건들만 복사하여 student1 테이블을 생성한다.
CREATE TABLE student1 AS SELECT * FROM student WHERE stu_grade IN (1 , 2);

-- 과목테이블을 복사하여 subject1 테이블을 생성한다.
CREATE TABLE subject1 AS SELECT * FROM subject;
    
-- 수강테이블을 복사하여 enrol1 테이블을 생성한다.
CREATE TABLE enrol1 AS SELECT * FROM enrol;
    
-- 다 한 후 복사된 테이블의 내용을 확인한다.
select * from student1;
select * from subject1;
select * from enrol1;

-- 학번 20101059, 이름 조병준, 학과 컴퓨터정보, 학년 1, 반B, 키 164, 몸무게 70인남학생이 추가되었다.
insert into student1 values(20101059, '조병준', '컴퓨터정보', 1,'B','M', 164,70) ;

-- 학번 20102038, 이름 남지선, 학과 전기전자, 학년 1, 반C, 여학생이 추가되었다.
insert into student1 values(20102038, '남지선', '전기전자', 1, 'C','F',null, null) ;

-- Student1 테이블에 학생 테이블의 3학년 학생들 데이터를 입력하라.
insert into Student1 select* from student where stu_grade=3;

Update

과목번호가 104번인 성적을 10더함

update a_enrol set enr_grade=enr_grade+10 where sub_no=(select sub_no from subject
where sub_name='시스템분석설계');

Delete

삭제

delete from a_enrol where stu_no='20131001';
// 테이블은 남아있지만 데이터가 없다(모든 튜플을 삭제했기 때문에)
delete from a_enrol;
select *from a_enrol;

truncate = Delete

-- t_tbl 모든 데이터 삭제
truncate table t_tbl; 

종합예시)

select *from student1;
select *from enrol1;
select *from subject1;

 -- 1. 20153088학생이 자퇴했다
 
delete from student1 where stu_no=20153088;

-- 2. 과목번호 112, 과목번호 자동화시스템, 교수명 고종민, 학년 3, 학과 기계 추가
insert into subject1 values(112,' 자동화시스템','고종민',3,'기계');

-- 3. 과목번호 110이 501로 변경되었다
update subject1 set sub_no='501' where sub_no='110';

DDL(Data Definition Language)

1. 데이터 정의언어
2. 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할
3. CREATE, DROP, ALTER 구문
4. DDL은 트랜잭션 발생시키지 않음
   • 롤백이나 커밋 사용 불가
   • DDL문은 실행 즉시 MySQL에 적용

create

테이블복사

create table a_enrol
as
select *
from enrol
where stu_no<20150000;

예시)

create table t_tbl(
t_empno int(4) not null, -- 기본키를 정해놨으므로 not null은 생략 가능
t_ename varchar(40),
t_job varchar(9),
t_mgr int(4),
t_hiredate date,
t_sal decimal(7,2),
t_comm decimal(7,2),
t_deptno int(2),
primary key(t_empno));

desc t_tbl;

-- 부서번호 20인 데이터만 뽑아 t_tbl에 삽입
-- 대량 데이터 삽입 select*
insert into t_tbl select* from emp where deptno=20;

select *from t_tbl;

alter

테이블 구조 변경

-- t_tbl에 성별 열 삽입 - char(1)
alter table t_tbl
add(t_gender char(1));

-- 성별 열의 varchar(10)으로 변경
alter table t_tbl modify t_gender varchar(10);

drop

- 열 삭제

-- 열 삭제
alter table t_tbl drop t_gende

DCL(Data Control Language)

1. 데이터 제어 언어
2. 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용 되는 구문
3. GRANT,REVOK,DENY구문

select 문 / use문

• 데이터베이스 내 테이블에서 원하는 정보를 추출하는 기능

use khw;

select *from student;
select *from subject;
select *from enrol;

SQL 사용 시에 처음으로 use 스키마이; 을 해주면 데이터베이스가 지정되어 다른 곳에 입력되는 것을 방지할 수 있음

중복제거(distinct)

use khw;

select *from student;
select *from subject;
select *from enrol;

select stu_no, stu_name from student;
select stu_dept from student;

-- 중복제거(distint)
select distinct stu_dept from student;
select distinct stu_grade, stu_class from student;

별명(alias)설정하기

-- 원하는 별명(alias)으로 데이터 가져오기
-- 학번, 과목번호, 성적 출력
select stu_no, sub_no, enr_grade+10 from enrol;
select stu_no as ID, stu_name as NAME from student;

열 합치기(concat)

-- 두개 이상의 열을 합쳐서 검색
-- 학과, 학생이름 추출 -> 학생테이블로부터
select stu_dept, stu_name from student;
select concat(stu_dept, stu_name) as 학과성명 from student;
select concat(stu_dept,' ', stu_name,'입니다') as 학과성명
 from student;

where절 사용하기

• 조회하는 결과에 특정한 조건을 줘서 원하는 데이터만 출력하고 싶을 때

-- where절 사용
select stu_name, stu_dept, stu_grade, stu_class from student 
where stu_dept='컴퓨터정보';

select stu_name, stu_dept, stu_grade, stu_class from student
 where stu_dept='컴퓨터정보' and stu_grade=2;

limit 걸기(원하는 만큼만 !)

-- 원하는 만큼만 데이터가져오기
SELECT *from student limit 5;
SELECT *from student limit 1,5; -- 인덱스 1부터 5개

범위조건 between , and

-- 범위조건
select *from student where stu_weight between 60 and 70;
SELECT *from student 
where stu_no between '20140001' and '20149999';

SELECT *FROM student WHERE stu_dept='기계' AND stu_grade=2;

n개 이상문자 출력 (like  '_ , %')

-- % : 0개 이상 문자
-- _ : 1개 이상 문자
SELECT stu_no, stu_name, stu_dept
 FROM student WHERE stu_name LIKE '김%';
-- 첫글자가 김으로만 시작함
SELECT stu_no, stu_name, stu_dept
 FROM student WHERE stu_name LIKE '_수%';
-- _수% cf) 배수지, 김수지, 이수지 
SELECT *FROM student WHERE stu_no like '2014%';

또는 (in)

SELECT stu_no, stu_name, stu_dept FROM student WHERE stu_dept in('컴퓨터정보','기계');
-- in = or 과 같다

또는 in 이외(3가지 방법) not

SELECT *FROM student WHERE not stu_dept='컴퓨터정보';
SELECT *FROM student WHERE stu_dept not in('컴퓨터정보');
SELECT *FROM student WHERE stu_dept <>('컴퓨터정보');

조건걸어보기

SELECT *FROM student WHERE stu_height >= 170;

Null 존재 여부

-- 널 값 존재 여부
SELECT stu_no, stu_name, stu_height
 FROM student WHERE stu_height is null;
 
SELECT stu_no, stu_name, stu_height
 FROM student WHERE stu_height is not null;

순서정렬(Order by, Desc)

-- 순서화(오름차순 정렬)
SELECT stu_no, stu_name FROM student ORDER BY stu_no;

-- 순서화(내림차순 정렬)
SELECT stu_no, stu_name FROM student ORDER BY stu_no DESC;

-- 별칭이 붙어있는 열을 기준으로 정렬
SELECT stu_no, stu_name, stu_dept, stu_weight-5 AS target
 FROM student ORDER BY target;
 
-- 열의 순서번호 이용하여 정렬
SELECT stu_no, stu_name, stu_dept, stu_weight-5 AS target
 FROM student ORDER BY 3;
 
SELECT stu_no, stu_name, stu_dept, stu_weight-5 AS target
  FROM student ORDER BY stu_weight-5;
  
SELECT stu_no, stu_name, stu_dept, stu_weight-5 AS target
   FROM student ORDER BY stu_dept, target;
   // 앞이 있는거 먼저 정렬된다!
   
SELECT stu_no, stu_name, stu_dept, stu_weight-5 AS target
    FROM student ORDER BY stu_dept, target DESC; 
    // ASC는 default값이므로 앞에있는 stu_dept는 ASC로 정렬

   
   

사칙연산

-- 사칙연산
SELECT 1 + 2;
SELECT 3 * (2 + 4) / 2, 'hi';
SELECT 10 % 3;

--
SELECT 'AB' + 3; -- 문자열에 사칙연산을 가하면 0으로 인식
SELECT 'AB' * 3;
SELECT TRUE, FALSE;
SELECT TRUE IS TRUE;
SELECT 2 + 4 = 6 OR 2 * 4 = 8;
SELECT 'B' = 'b'; -- MySQL의 기본 사친 연산자는 대소문자 구분 X
SELECT 10 BETWEEN 15 AND 20;
SELECT 'apple' NOT BETWEEN 'banana' AND 'computer';
SELECT 1+2 in (2,3,4);
SELECT 'hi' IN (1 , TRUE, 'hi');

반올림함수 (Round)

-- round 함수
SELECT round(345.678), round(345.67,0),
round(345.678,1), round(345.678,-1) from dual
//346   346   345.7   350

대문자,소문자로 출력upper,lower / 뒷자리 없애기truncate/ 이어붙이기concat/절댓값abs / 승(n*n)출력pow, power, sqrt

SELECT LOWER('KOREA'); //korea
select UPPER('korea'); //KOREA

SELECT abs(1),abs(-1),abs(3-10); // 1  1  7
SELECT pow(2,3),power(5,2),sqrt(16); //8 25 4
SELECT truncate(1234.5678,1); //1234.5
SELECT truncate(1234.5678,2); //1234.56
SELECT truncate(1234.5678,-1); //1230
SELECT truncate(1234.5678,-2); //1200

SELECT concat('hello',' ','2024','03','13');

-- 괄호 안에 내용이 첫번째 매개변수로 이어붙여짐
SELECT concat_ws('-',2024,3,13,'PM');

~부터 출력하기(substr)

SELECT substr('ABCDEFGH',3); // CDEFGH
SELECT substr('ABCDEFGH',3,2); //  CD
SELECT substr('ABCDEFGH',-4); // EFGH 뒤에서부터 4글
SELECT substr('ABCDEFGH',-4,2); // EF

문자열 길이(length)

SELECT length('ABCDEFGH'), char_length('ABC');
// 바이트 8                   // 문자열의 문자길이 3
SELECT length('안녕하세요'), char_length('안녕하세요');
// 바이트 15                  // 문자열의 문자길이 5

공백제거(trim)

SELECT 
    CONCAT('|', LTRIM(' hello'), '|'), // 왼쪽공백제거
    CONCAT('|', RTRIM(' hello  '), '|'), //오른쪽공백제거
    CONCAT('|', TRIM('   hello  '), '|') // 양쪽공백제

문자 이어붙임(pad), 대체(replace), 위치반환(instr)

   -- lpad(S, N, P) : S가 N이 될 때까지 P를 왼쪽에 이어붙임
SELECT lpad('ABCD',5,'#'); -- left pad //#ABCD

-- rpad(S, N, P) : S가 N이 될 때까지 P를 오른쪽에 이어붙임
SELECT rpad('AB',5,'@'); -- left pad //AB@@@

SELECT replace('버거킹에서 버거킹 햄버거를 먹었다','버거킹','맥도날드');

-- instr(S,s) : S중 s의 첫 위치반환
SELECT instr('ABCDE','ABC'), //1
instr('ABCDE','BC'), //2
instr('ABCDE','C'); /3

형 변환(Cast, Convert)

-- CAST(A AS T) : A 를 T 자료형으로 변환
SELECT '01'='1', cast('01' as decimal) = cast('1' as decimal);
-- CONVERT(A,T) : A 를 T 자료형으로 변환
SELECT '01'='1', convert('01',decimal) = convert('1',decimal);

cf)
select empno, ename, cast(hiredate as char),
date_format(hiredate, '%Y-%m') as 입사년월
from emp;

★★날짜함수★★

-- 날짜함수
select date(now()); -- 년월일
select curdate(); -- 년월일
select time(now()); -- 시분초
select timestamp(now()); -- 년월일 시분초
select sysdate(); -- 년월일 시분초

SELECT ADDDATE(NOW(), INTERVAL 1 DAY); 
-- 오늘기준으로 다음날 출력

SELECT ADDDATE(NOW(), INTERVAL 1 MONTH); 
-- 오늘기준으로 다음달 출력

SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH); 
--  adddate와 동일

SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK); 
-- 오늘기준으로 1주 전 출력

SELECT ADDDATE(CURDATE(), -WEEKDAY(CURDATE())) AS MONDAY;
-- 이번주 월요일
SELECT ADDDATE(CURDATE(), WEEKDAY(CURDATE())) AS MONDAY;
 -- 이번주 금요일

-- ADDDATE(DATE, INTERVAL EXPR)

SELECT DATE_ADD('2010-12-31 23:59:59',INTERVAL 1 DAY);
SELECT curdate(),CURTIME(), NoW();

-- 월0 화1 수2 목3 금4 토5 일6
-- adddate(date,interval);

select weekday(curdate());
select adddate(curdate(), -weekday(curdate()));
-- 2024/03/13 수요일 -2(== -weekday(curdate())) = 2024-03-11
select adddate(curdate(), weekday(curdate()));
-- 2024/03/13 수요일 +2(== weekday(curdate())) = 2024-03-15
 
 

제어 흐름 함수(ifnull / nullif)

-- ifnull / nullif
-- ifnull(수식1, 수식2) : 수식 1이 null이 아니면 수식1 반환, null이면 수식2 반환
select ifnull(stu_height,0) from student;

-- nullif(인수1, 인수2) : 두개값 비교해서 값 같으면 null반환, 아니면 인수1반환
select ifnull(nullif('A','A'),'널값');
--    =ifnull(null,'널값');
--    -> 널값

집계함수

최대값,최소값 / 카운트

-- 최대값, 최소값 
SELECT 
    MAX(enr_grade), MIN(enr_grade)
FROM
    enrol;

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

SELECT 
    MAX(stu_weight), MIN(stu_weight)
FROM
    student
WHERE
    stu_dept = '기계';

-- count는 null값 포함 X---------------------
SELECT 
    COUNT(*), COUNT(stu_height)
FROM
    student; -- 10 ,8 출력됨
    
SELECT 
    COUNT(stu_dept), COUNT(DISTINCT stu_dept)
FROM
    student;

Group by(그룹 묶기) (where 이 있으면 무조건 where 뒤에 적기!)

-- group by

SELECT 
    stu_dept, COUNT(*)
FROM
    student
GROUP BY stu_dept;

SELECT 
    stu_dept, COUNT(*)
FROM
    student
WHERE
    stu_weight >= 50
GROUP BY stu_dept;

SELECT 
    stu_dept, AVG(stu_weight)
FROM
    student
GROUP BY stu_dept;

-- 소수점 제거(format(값,소수점 자리수)
SELECT 
    stu_dept, FORMAT(AVG(stu_height),0)
FROM
    student
GROUP BY stu_dept;

SELECT 
    stu_dept, stu_grade, COUNT(*)
FROM
    student
GROUP BY stu_dept , stu_grade;

Group by + Having 절 ( where문과 비슷, 무조건Group by 뒤에 나와야함)

SELECT 
    stu_grade, FORMAT(AVG(stu_height), 1)
FROM
    student
WHERE
    stu_dept = '기계'
GROUP BY stu_grade
HAVING (AVG(stu_height)) >= 160;

-- 기계
-- 1학년 평균키가 160이상
-- 2학년 평균키가 160이상
-- 3학년 평균키가 160이상

-- 학과는 기게학과이면서, 
-- 학년별로 평균키가 160이상인 평균 키 값 출력
------------------------------------------------

SELECT 
    stu_dept, MAX(stu_height)
FROM
    student
GROUP BY stu_dept
HAVING MAX(stu_height) >= 175;

부 질의

-- 부 질의
-- 옥성우 학생의 키
select stu_height from student where stu_name='옥성우';

-- 옥성우 학생보다 키 큰 학생의 이름 출력
SELECT 
    stu_name
FROM
    student
WHERE
    stu_height > 
    (select stu_height from student where stu_name='옥성우');
    
-- 박희철 학생과 몸무게가 같은 학생의 이름 출력
select stu_name from student where stu_weight = 
(select stu_weight from student where stu_name='박희철')
 and stu_name<>'박희철';
 
 -- 평균 키보다 큰 학생의 정보 출력
 select *
 from student where stu_height >
 (select avg(stu_height) from student);
 
 -- all 모든 조건의 결과를 만족시켜야함
 select *
 from student
 where stu_height >
 all(select avg(stu_height) from student group by stu_dept);
 
 
  -- any 서브쿼리의 조건 중 하나만 만족시켜도 가능 any = in = some
  select *
 from student
 where stu_height >
 any(select avg(stu_height) from student group by stu_dept);

 select *
 from student 
 where stu_class 
 in(select stu_class from student where stu_dept='컴퓨터정보')
 and stu_dept<>'컴퓨터정보';