[MySQL] MySQL Grammar 모음(계속 업데이트)

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<>'컴퓨터정보';