SQL

======1일차======
MYSQL 설정
mysql은 workbench라는 에디터를 사용

schemas -> DB. 이 안에 테이블들이 존재
드럼통모양 DB생성
그 오른쪽 테이블 생성

가장 오른쪽은 새로고침
가장 왼쪽은 SQL 파일 추가

테이블 옆에 버튼 3개 : 정보 보기, 구조 보기, SQL문..

 DB 프로그램마다 함수, 자료형이 조금씩 다르다. 다만, 큰 틀은 같음.

sys 데이터는 시스템이 관리하는 데이터. 건들지 마셈

administration : 권한 설정, 서버 상태, 데이터 내보내기 등..

root(관리자)는 모든 권한을 가진다.
root가 user를 생성해서 권한을 부여함

시스템 환경 변수 설정하기! > cmd 창을 사용하기 위해서?
C:\Program Files\MySQL\MySQL Server 8.0\bin
오라클을 먼저 인식하기 때문에 MySQL 위로 이동해주기

// cmd 창에서..
mysql --version : 버전 확인 (환경변수 설정됐는지 확인)
mysql -uroot -p(비밀번호) : 루트 (관리자) 로그인
구문을 끝낼 때는 ;(세미콜론) 붙이기 (엔터는 구문 끝내기 xxx)
show databases; : 가지고 있는 db 목록 보여줌. 숨겨진 목록까지 전부 표시
use 데이터베이스명; : 쓰고자 하는 db 선택
show tables; : 테이블 보기
 select * from user; : user 정보 출력
desc user; : user 테이블 구조 출력

＊ 25.02.11 cmd 파일 참고

화면에서 어떤 정보를 어떻게 받을 것인지를 미리 설정한 후에 변수에 따른 구조(null값, pk값 등)를 설정

중복성과 종속성을 제거하기 위해 테이블을 분리하여 정보를 관리함

기본키(PK)는 테이블에서 하나의 유효한 값으로, 권장 요소. 없어도 되지만 거의 필수다! 
한 테이블에 하나는 꼭 두는 게 좋음.

데이터에 변경,삭제가 이루어졌을 때 어떻게 할지 >> 설계에서 잘.. 규칙 설정하셈

========================================
C (create) 생성
R (Read) 읽기 => 조회
U (Update) 갱신, 수정
D  (Delete) 삭제


정의어에서 검색은 뭐.. show 쓰셈
ppt 내용 정리/필기 확인!
=======================================
MySQL 실습 
1. 유저 생성 -> DB 생성 -> 권한 부여 
(유저, DB 뭘 먼저 생성하든지 상관 X)

// ->SQL 주석


DB 생성 : 루트 계정에서 실시
create database test;
>> Query OK가 나오면 잘 생성된 것

SHOW DATABASES;
>> DB 생성 잘 됐는지 확인. 잘 됨

＊ SHOW DATABASES 했을 때 MYSQL이라는 테이블이 보이는데 MYSQL은 루트에게만 있는 테이블로, 유저를 관리하는 테이블에 해당한다. 

// MYSQL이 user를 관리하는 테이블로 선택
use MYSQL;
>> Database changed가 나오면 테이블 전환 잘 된 것

// user 생성 구문
 create user 'mysqluser'@'localhost'; 
>> 로컬호스트에서 이 이름으로 생성
identified by 'mysql'; 을 붙이면 인증번호??인듯

 grant all privileges on test.* to 'mysqluser'@'localhost';
// test의 모든 권한을 mysqluser에게 준다.

 grant all privileges on test.* to 'mysqluser'@'localhost' with grant option';
// with grand option : 다른 유저에게 권한을 부여할 수 있는 기능

// 권한 적용
flush privileges;

====테이블 형성 시 값 설정=======
pk : 기본키 
nn : not null (null 포함 x) 체크안하면 null 가능
nq : 중복 불가능
b : 이진값 (0과 1만 가능)
un : 마이너스 불가
zf : 숫자앞에 0으로 채우는거
ai : 자동증가
g : 속성 자동 생성(디폴트..) 잘 안씀
default : 나이 등에 기본값을 줄 수 있다.

=============================
[제약 조건]
PK : Primary Key => 기본키 속성 추가
NN : Not Null => 데이터 값으로 null이 오지 못하게 막음
AI : Auto Increment => 자동 증가 : 정수만 가능
UQ : unique => 중복 안됨.
B : 바이너리 값(이진수. 0과 1)
UN : Unsigned => 음수 안됨
ZF : zero fill => 숫자 앞에 0으로 채움 ex. 0001
G : generated => 속성 자동 생성

default : 기본값
==============================
[데이터 자료형 - MYSQL용]
＊ (m) : 값이 들어감
숫자

(정수)
- bit(m) : 1~64 Binary 값을 지정
- bool : TRUE(값으로는 1) / FALSE (값으로는 0) == TINYINT(1)
- tinyint(m) : 0-255 사이의 작은수 표현 (-128 ~ 127) ★ 
// 많이 씀
- smallint(m) : 2BYTE 0 ~ 65536 (-32768 ~ 32767)
- mediumint(m) : 3BYTE 
- int(m) : 4바이트 => 자바 int와 동일 // 많이 씀
- bigint(m) : 8바이트 => 자바 long과 동일 // 많이 씀
(자바랑 자료형을 많이 맞추므로 int랑 bigint를 많이 쓰는거임)

(실수)

- float(m,d) : 정밀도가 작은 부동소수점 표현 (d : 소수 자리수)
- double(m,d) : 정밀도가 큰 부동소수점 표현
=> MySQL 8.0.17 이후로 사용되지 않음
- decimal(m,d) : 고정 소수점 표현 (소수점으로 통합된다면 통합되어 쓰인다

문자
- char(m) : 고정 길이 문자열 (0-255)★
-> 변하는 길이가 아니라 고정되어있는 글자에 쓰는 것이 좋음★
- varchar(m) : 가변길이 문자열(0-65536)★
-> 메모리를 효율적으로 쓸 수 있도록 함
(고정된 길이가 아니라면 대부분 varchar 사용)
- tinytext : 1바이트 문자열 저장 => 가변길이★ (글자가 많을 떄.. 게시글 작성 시 등)
- tinyBOLB : 바이너리 데이터 저장용 => 이미지, 동영상 처리
(BOLB 자체가 그런 용도로 쓰인다)
- text : 2바이트 가변길이 
- mediumtext : 3바이트 
- longtext : 4바이트

날짜 
- date : 날짜
- datetime : 날짜 + 시간 => 가장 많이 사용함 (보통 시간까지 같이 체크하는 경우가 많아서)
- timestamp : insert, update 연산에 유리한 형태(DB에 들어갈 때 연산의 형태라면 많이 사용)
- year : 연도만
- time : 시간만
==========================================
테이블명 : test1
속성 
- id : int AI PK NN
- name " varchar(10) NN
- age : int 기본값 20
- address : varchar(50);

==테이블 추가==
create table test1( 
id int auto_increment not null,
name varchar(10) not null,
age int default 20, // default 있어도 값 얼마든지 넣을 수 있음
address varchar(50),
primary key(id)
);

--- 데이터 추가 구문(insert into ~values)
insert into test1(name, age, address) values('hong', 30, 'seoul');

--- 데이터 잘 들어갔는지 확인
select * from test1;

--- in


create table student(
   num int,
   name varchar(30) not null,
   age int default 20,
   address varchar(45),
   major varchar(45),
   score int
    );
     age int default 20,
   address varchar(45),
    major varchar(45),
   score int
    );

insert into student(num, name, address, major, score) values (2222, '강길순', 'Seoul', 'English', 87);
insert into student(num, name, age, address, major, score) values (3333, '이순신', 22, 'Incheon', 'Computer', 57);
insert into student(num, name, address, major, score) values (4444, '강감찬', 'Incheon', 'English', 67);


MySQL 주석 자동 : ctrl + / 
(mysql만 해당!)

-- 한줄 주석 // 얘는 MySQL 주석 아님! 아마 오라클이었던걸로 기억..
/* 여러 줄 주석 */

============
전화번호(tel) 칼럼 추가
add(추가), drop(삭제), modify(수정), change(수정:필드명수정가능)

alter table 테이블명 add 필드명 속성;


===update(수정)===
update 테이블명 set 변경필드 = 값 where 조건;

tel 필드를 추가하면 null값으로 처리된다.
update를 통해 데이터 수정 null -> 01012345678


// 성별 추가
ALTER TABLE student add gender varchar(2) after age;
// 성별 삭제
 alter table student drop gender;
// 속성 변경
 alter table student modify gender varchar(2) default 'm' after age; // 수정
// 칼럼명 변경
alter table student change gender gender2 varchar(2) default 'm' after age;

---- 강길순, 유관순만 gender = 'w'로 변경
update student set 
gender = 'w'
where = num 1111 or 5555;

---

======2일차======
[DB 2일차]

DB는 구문을 작성하는 것 자체가 저장
cmd 창에서는 ;을 작성하지 않는다.
MySQL > 이라고 뜨면서 MySQL 창으로 들어가야 그때 세미콜론으로 문장을 완성해준다.

==1일차 과제==
처음 접속 시, 해당 테이블을 확인하기까지의 과정

1) mysql -u유저명 -p비밀번호 : 로그인 ->exit로 빠져나갈 수 있음
(EXIT를 하면 DB와 cmd의 접속이 끊긴다)

show databases; : DB 목록 확인 (어떤 DB를 확인해야할지 확인)

2) use 선택DB명; => DB 선택

show tables; : 전체 테이블 목록 보기 

3) select * from 테이블명 : 해당 테이블의 데이터 보기
(이때부터 전부 구문으로 본다..?)

사실 delete는 db상에서 거의 쓰지 않는다. 회원 탈퇴를 할 경우, 바로 drop 해버리는 것이 아니라, 다른 회원탈퇴 테이블(=지워질 테이블)로 따로 옮겨놓는 것. 정해진 기간 동안 해당 테이블에 저장해두었다가 기간이 끝나면 같이 삭제

고유번호로 붙이는 pk는 절대 재사용되지 않는다!!
auto_increment로 pk를 붙이면 절대 같은 번호가 다시 사용될 수 없다.

PK는 절대 건드리면 안되는 값.

DB는 들어가는 데이터 값도 소문자/대문자 구분 안한다.
한글, 영어만 구분

"" , '' 상관없음~
-------------------------------------------------------
테이블명 : test2

4) 테이블 생성
create table test2 (
id int auto_increment,
name varchar(10) not null,
age int default 20,
address varchar(20),
primary key(id)
); 
 
// 아직 내용은 없다!

＊ desc test2 : 테이블 구조 확인

5) 데이터 삽입
insert into test2(name, age, address) 
values ('홍길동', 23, '서울'), 
('강길순', 24, '인천'), 
('이순신', 22, '서울'), 
('강감찬', 23, '인천'), 
('유관순', 21, '서울');

// auto_increment로 인해서 delete from test2; 를 하고 데이터를 재생성했을 때 다음 번호부터 pk가 생성됨 (1-5번까지 생성했다가 데이터 전부 지우고 다시 생성했는데 6번부터 생성됨)

// auto_increment로 pk 생성하면 다시는 똑같은 번호가 재생성되지 않는다....
drop table test2;로 테이블 걍 지워버림^^
테이블 지워버리고 테이블 재생성 & 데이터 재삽입

=================================================
오늘 진도

1. 테이블 이름 변경 : RENAME
: rename table 테이블명(전) to 테이블명(후);

(ALTER는 테이블 내부를 바꾸는 것이라 테이블 자체를 바꿀 수는 X)
(테이블 이름은 바꿀 수 있지만, db 이름은 못바꾼다!)

-----------------------------------------------------------------
홍길동(id=1) 나이를 20살로 변경
update test2 set age = 20 where id = 1;
// 검색할 때는 id가 가장 빠르다! 중복이 없는 pk를 기준으로 where 조건을 쓰는 것이 가장 빠르다.

강길순 나이를 25살로 변경
update test2 set age = 25 where id = 2;

강감찬과 유관순 나이 20살로 변경
update test2 set age = 20 where id = 4 or 5; // 왜 or쓰니까 다 바뀌냐??
---> update test2 set age = 20 where id = 4 or id = 5;로 작성!!!!!
-------------------------------------------------------------------
2. IN 사용
update test2 set age = 20 where id IN (4,5);

- 조건이 작을 때는 OR 쓰고 막 10명~30명씩 데이터가 많을 때는 IN을 쓰는 것이 효율적
- or에 조건이 많이 붙을수록 or 연산자보다 in 연산자가 실행속도가 빠르다.
- in 연산자 안에 서브쿼리를 넣을 수 있다. 
---------------------------------------------------------------
모든 학생들의 나이를 1살씩 증가
update test2 set age = age + 1;

나이가 26인 학생 삭제
delete from test2 where age = 26;

원하는 속성만 골라서 select
select name, age from test2;
------------------------------------------------------------------
3. AS : 별칭 설정(alias)
- 칼럼, 테이블, 서브쿼리, .... 등에 사용 가능
- 보통 칼럼의 첫 글자를 따서 설정한다..?

select name as '이름', age as '나이' from test2 as a;
(이름, 나이로 필드명이 바뀐 후 출력되는데 기본 필드명이 바뀐 것은 아니다..)
- 이 구문 안에서만 이름, 나이로 쓰이는 것. 바뀐 거 아님!
- 보통 칼럼보다는 테이블 이름에 as를 쓴다. (조인 사용하고 서브쿼리 하고.. 복잡해질 때 사용)
- a.name 으로 부를 수 있다. (이 구문 안에서만)

-> select a.name, a.age from test2 a; 도 가능!

-----------------------------------문제---------------------------------------
student 테이블에서 작성

1. major가 Computer인 학생만 검색
select * from student where major = 'Computer';

2. major가 Computer이고, score 80 이상인 학생 검색
- 전체 필드가 아닌 이름만 검색
- as 사용하여 필드명 변경
select name as '이름' from student where major = 'Computer' and score >= 80;
// 영어에서 따옴표 안붙이면 칼럼명으로 인식한다. 그렇기 때문에 텍스트에는 반드시  ' ' 붙이기.  근데 한글은.. 걍 쓰삼 따옴표 무조건

3. score가 70~90 사이인 학생 검색 (between and)
select * from student where score between 70 and 90;

4. address가 Seoul. Suwon에 사는 학생만 검색
select * from student where address in('Seoul', 'Suwon');
select * from student where address = 'Seoul' or address = 'Suwon';

5. 강감찬의 major를 Computer로 변경
update student set major = 'Computer' where name = '강감찬';

6. 강감찬 데이터 삭제
delete from student where name = '강감찬'; // where num = 4444;
=================================================
4. Like % : 검색할 때 많이 사용함.
- like (문자) % (문자)로 시작하는 필드들을 가져온다.

- address가 s로 시작하는 데이터 검색
: select * from student where address like 's%';
=================================================
5. Distinct : 중복 제거
- 목록화 할 때 사용
- 칼럼 앞에 distinct를 붙인다.

우리 학교 과 출력(중복 없이 출력)
select distinct major from student;

주소 중복없이 출력
select distinct address from student;
=================================================
6. 산술연산자 ( + - * / ) 
=> 나머지 연산자가 없다!

- 값이 null이면 연산이 안됨. ( null + 1 = 1이 될 수 없다! 연산 불가)
(그냥 null 그대로.. 유지됨. null + 1 = null . 에러가 되는 건 아니다. 연산값 자체가 null)
처음부터 값을 가질 수 없는 수의 default는 0으로 두기. 
(ex. 시험을 보기 전인데 점수가 있을 수 없음. null 하면 오류날 수도 있으니까 0으로 설정하기)
그래야 자바 연동했을 때 nullpointer가 안뜬다!!!!!!!

- 비교연산자 (>, <. <=. >=. =. <>)
＊ <> : 같지 않다.
- +=. -=. ++ 사용 불가 (얘네는 자바 문법)
- 논리연산자(and, or, not)
- 우선순위 ( )로 묶기
====================================================
7. 집약과 정렬
 집약 : 열의 데이터 합계, 평균 같은 집계값을 구하기 위한 함수
- count : 검색된 행의 개수
- sum : 합계
- avg : 평균
- max : 최댓값
- min : 최솟값

정렬
- group by : 그룹별로 묶음
- having : 그룹에 조건
- order by : 정렬 (기준을 여러개 줄 수 있다. 
- 각 필드마다 asc, desc 결정해야 함.
ex ) order by age asc, score desc;
- limit : 시작번지, 개수 => 시작번지부터 개수만큼 추출

===============================================
update student set gender = 'w' where num in (1001, 1003, 1005);
===============================================
컴퓨터 학과의 성적 합계 구하기
- major, sum

산술연산자 + 는 같은 튜플의 속성 합계
= 같은 속성끼리의 합 == SUM

(EX. 컴공과 학생들의 점수 합 = SUM, 한 학생의 국어/영어/수학 점수 합 = 그냥 +)

select SUM(score) from student where major = 'Computer';
select SUM(score) as 평균 from student where major = 'Computer';
select major, sum(score) as sum from student where major = 'computer';
select major, sum(score) as sum from student where major = 'Enghlish';

max, avg, min 구하기
select major, max(score) from student group by major;

select major, count(*) from student where major = 'english';
select major, count(num) from student where major = 'english';

select major, sum(score) from student group by major;
// 각 과별로 그룹화
-----------------------------------------------------------------------
8. SQL문 순서 
select 칼럼명 from 테이블명
where 조건
group by 칼럼
having 조건
order by 칼럼
limit 

// 순서 지키기!!!!!!!!!!!!!!!!!! 생략이 가능해도 순서는 지켜라

select major, sum(score) as sum
from student
group by major 
order by sum desc;

select * from student limit 0,3 ;// 0번지부터 3개

---- 1, 2, 3등 출력
select * from student order by score desc limit 0,3;


---- tel null 인자로 검색
select * from student where tel is null;
select * from student where tel is not null;

--- 성별, 성적, 평균 출력
select gender, avg(score) from student group by gender order by desc;
// 이미 gender끼리 묶은 상황에서 score를 출력하면 에러가 남

select count(*), avg(score) as '평균' from student group by gender order by '평균' desc;
// desc를 선택할 수 있는 평균? 카운트?가 2개여서 하나를 선택해줘야함.. 
// 소숫점은 round()로.. 없앤다?

--- 컴퓨터 학과와 영어 학과의 점수 합계만 나타내기
select major, sum(score) as s from student group by major having major in ('computer', 'english');

--- 각 과별 성적 합계가 250 이상인 학과만 출력
select major, sum(score) from student group by major having sum(score) => 250;
// where이 아니라 having에서 조건을 줘야 한다!
// where에서만 줄 수 있는 조건, having에서만 줄 수 있는 조건, 둘 다 줄 수 있는 조건....

--- student 테이블에 grade(학년) 필드 추가하고 각 grade에 맞게 조건.. ㅇ?
-- 20 1 21 2 22 3 23,24 4
alter table student add grade int;

update student set grade = 1 where age = 20;
update student set grade = 2 where age = 21;
update student set grade = 3 where age = 22;
update student set grade = 4 where age in (23, 24);

----강사님 코드----
alter table student add grade int default 1;

--- ☆  조건을 나타내는 함수
if~else , case~when~then문 (=switch)
＊ if문은 mysql에만 존재

update student set grade = case // 위 sql문을 변경
when age = 21 then 2
when age = 22 then 3
else 4
end;

- if문
if(조건식, 참, 거짓)
if(조건식, 참, if(조건식, 참, 거짓(이중 조건식 가능))

- case문
when 조건 then 값
when 조건 then 값
when 조건 then 값
when 조건 then 값
else 값
end;

--------------------------------------------------------------
4학년 값을 졸업테이블로 이동
학년과 나이를 하나씩 올리는데, 4학년은 졸업 테이블로 이동시킨다.
1. 졸업생 테이블 생성
=> 원본 테이블 구조를 복사하여 복사 테이블 생성
- if exists(만약 있다면...)
- if not exists(만약 없다면....) 
// 테이블이 이미 존재하는지 아닌지를 확인함

create table (if not exists) 새로운_테이블명 // 없으면 생성하고 있으면 말아
like 복사할_테이블명;

-> create table if not exists student_4 like student;

2. 4학년 학생을 이동
=> grade가 4인 학생을 복사
=> 서브쿼리를 사용하여 검색 후 복사
=> insert into values(값..)
insert into student_4 ( select * from student where grade = 4);

=> 값...... 검색
select * from student where grade = 4

3. 원본테이블에 grade가 4인 학생을 삭제 (이동 완료)
delete from student where grade= 4;

4. age + 1, grade + 1  증가
 update student set age = age+1, grade = grade+1;


-----------------------------------------------------------------
성적이 90점 이상인 학생들을 장학금 테이블로 복사
 (사라지는 거 아님. 복사만)

장학금 테이블 : student_best
구조 복사하여 생성 후 복사

create table if not exists student_best like student;
insert into student_best (select * from student where score >= 90);

-----------------------------------------------------------------
insert into student_best(Num, name, age) (select num, nume, age from student where score
--------------------------------------------------------------------
SQL 내장함수
1. 산술연산자
- 산술연산자)( + - * /) 
- mod(값1, 값2) : 값1을 값2로 나눈 나머지
- round(값, 자리수) : 반올림
- ceil(값) : 올림 / floor(값) : 버림 -> 자리수 지정
- sprt: 제곱근 / pow : 제곱

문자열 함수
- concat(str, str2) : 문자열 연결
- length(str) : 문자열 길이 (영어는 1, 한글은 2로 친다.)
- char_length(str) : 무조건 1글자로 인식
- substr(str, start, count) : 시작 위치부터 개수만큼
- left(str, count) 왼쪽에서 개수만큼 추출
- right(str, count): 오른쪽에서 개수만큼 추출
- mid(str, start, count) : 시작 위치에서 개수만큼 추출
- replace (str, old, new //old 문자를 찾아 new 문자로 변경
- insert(str, old, new) : old 문자를 찾아 new 문자로 변경
- intsert(str, 위치,길이,삽입할 문자)
: 문자에서 위치의 길이만큼 지우고 삽입
- ucase(str) / lcase(str) 
: 대문자로 변환 / 소문자로 변환
- space(숫자) : 숫자만큼 공백 추가
- trim(문자) : 공백 제거 / ltrim, rtrim
- trime(<both> '-' from '------abcd--') // 원하는 데이터를 지운다
- both(양쪽) / leading(앞)/ trailing(뒤)
- lpad(str, 총길이, 채울문자)/  rpad
: 앞/뒤쪽에 총길이만큼 공백을 삽입, 공백부분에 채울 문자를 채우기
- repeat(str, 횟수) : 문자열을 횟수만큼 반복
- reverse(str) : 문자를 거꾸로 출력


- 날짜함수
- adddate / subdate : 날짜를 기준으로 ~후, ~전 날짜 ★ 많이 씀★
  adddate(날짜, interval 5 day) 
interval 뒤쪽 값 : year, month, day, hour, minute, second 가능★
- curdate(), curtime(), now(), sysdate() : 오늘 날짜/시간★
(now를 가장 많이 사용)
- year(날짜), month(), day(), hour(), minute(), second()★
: 년월일시분초 추출
- date(날짜), time(날짜/시간) : 추출
- datediff(날짜1, 날짜2) : 날짜2에서 날짜1까지 남은 날짜
- dayofweek(날짜) : 요일(일=1, 월=2......) ★
- monthname(날짜) :  월의 영어 이름을 리턴
- dayofyear(날짜) : 1년 중 며칠이 경과했는지 리턴
- last_day(날짜) : 해당월의 마지막 날짜
- time_to_sec(시간) : 시간을 초단위로 변환


- 논리함수
- if문
if(조건식, 참, 거짓) 
if(조건식, 참, if(조건식, 참, 거짓))
- case~when~then문
case
when 조건 then 값 
when 조건 then 값 
when 조건 then 값 
else 값
end;

- 정보함수
- user(), current_user(), session_user() 
: 현재 유저가 누구인지
- database(), schema() : 현재 사용중인 DB명 리턴
----------------------------------------------------------------------
===과제===
- student 테이블에서 다음을 처리
1. 등급(ranking) 칼럼 추가
alter table student add ranking varchar(2);

2. score에 따라 ranking에 A/B/C값을 추가
score >= 90 A
score >= 80 B 나머지는 C

update student set ranking = 'A' where score >= 90;
update student set ranking = 'B' where score >= 80;
update student set ranking = 'C' where score < 80;


3. ranking별 인원수 출력
select ranking, count(*) from student group by ranking ;

4. 성적순으로 1~3등까지만 출력
select * from student order by score desc limit 0,3;


5. 학과의 인원이 3명 미만인 학과를 출력
select major, count(*) from student group by major having  count(major) < 3;

---

======3일차======
day03

database shop

create database shop;
- root DB 생성 후 권한을 부여.

-- 권한을 부여
grant all privileges on shop.* to 'mysqluser'@'localhost' with grant option;

-- 권한 적용
flush privileges;

-- mysqluser 유저 변경
mysql -umysqluser -pmysql

use shop

- 제품테이블(product)
- 구매테이블(buy)

product table
- num int ai pk
- type_a varchar(50) nn
- type_b varchar(50) nn
- name varchar(100) nn
- price int default 0
- amount int default 100
- sale_amount int default 0
- register_date datetime

create table product(
num int auto_increment,
type_a varchar(50) not null,
type_b varchar(50) not null,
name varchar(100) not null,
price int default 0,
amount int default 100,
sale_amount int default 0,
register_date datetime,
primary key (num));

buy table
- num int ai pk
- customer varchar(20)
- product_name varchar(100)
- price int default 0
- amount int default 0
- buy_date datetime default now()

create table buy(
num int auto_increment,
customer varchar(20),
product_name varchar(100),
price int default 0,
amount int default 0,
buy_date datetime default now(),
primary key(num));

insert into product(type_a, type_b, name, price, amount, sale_amount, register_date) values
('티셔츠','긴소매','폴라 티셔츠',15000,34,100,'2024-10-15'),
('티셔츠','반소매','순면라운드 반팔티',15900,10,153,'2024-09-15'),
('티셔츠','민소매','에이 나시',9000,33,33,'2024-09-10'),
('패션운동복','트레이닝상의','피트니스상의',30000,34,55,'2024-12-05'),
('패션운동복','트레이닝하의','피트니스하의',50000,55,34,'2024-12-06'),
('패션운동복','트레이닝세트','피트니스상하의',90000,55,34,'2024-12-07'),
('아우터','재킷','양면 롱 후리스 자켓',23300,100,42,'2024-11-05'),
('아우터','코트','양털 겨울 코트',50000,50,30,'2024-10-31'),
('아우터','코트','트렌치 코트',40000,50,30,'2024-10-31'),
('아우터','패딩','롱 패팅 점퍼',47400,10,45,'2024-11-01');

---------------------------------------------------
- 홍길동이 폴라 티셔츠 5개 구매
insert into buy(customer, product_name, price, amount)
values
('홍길동', '폴라 티셔츠', 15000, 5);

=> 폴라 티셔츠, price를 product table 조회하여 추가
insert into buy(customer, product_name, price, amount)
(select '홍길동', name, price, 5 from product
where num = 1);

- 이순신이 피트니스상의 2벌 구매  (num = 4)

insert into buy(customer, product_name, price, amount)
(select '이순신', name, price, 2 from product
where num = 4);

insert into buy(customer, product_name, price, amount) 
select '이순신', name, price, 7 from product 
where num = 2;

insert into buy(customer, product_name, price, amount) 
select '강감찬', name, price, 3 from product 
where num = 3;

insert into buy(customer, product_name, price, amount) 
select '고길동', name, price, 3 from product 
where num = 10;

insert into buy(customer, product_name, price, amount) 
select '고길동', name, price, 2 from product 
where num = 4;

insert into buy(customer, product_name, price, amount) 
select '강감찬', name, price, 3 from product 
where num = 9;
----------------------------------------------------
- 제품명 별 판매수량 조회
select product_name, sum(amount) from buy
group by product_name;

- 제품명 별 판매금액 조회
select product_name, sum(price) from buy
group by product_name;

select product_name, sum(amount*price) from buy
group by product_name;

- total 필드를 buy 테이블에 추가 (amount 뒤에)
alter table buy add total int after amount;

- total 값을 채우기(amount*price)
update buy
set
total = price * amount;

alter table buy drop total;

- 칼럼 생성시 계산된 값을 자동계산하여 생성하고 싶을 경우
(generated columns)
- stored : 값이 저장되는 방식
(데이터가 입력되거나, 수정될 때, 해당 칼럼도 같이 갱신)
- virtual : 데이터를 저장하지 않고 정의만
(해당 칼럼을 읽으려고 시도할때 계산을 통해 보여주는 것만)
alter table buy add total int generated always as
(price * amount) stored after amount;



- 제품명 별 판매수량 / 판매합계 조회
select product_name, sum(amount), sum(price) from buy
group by product_name;

- 그룹없이 총 매출, 판매금액
select sum(amount), sum(price) from buy;
------------------------------------------------------
1. 최신 상품순으로 정렬 - product테이블
select * from product
order by register_date desc;

2. type_a 별로 카테고리 수, 가격 합계
select type_a, count(type_a),sum(price) from product
group by type_a;

3. price 16000이상인 제품을 할인상품에 해당
   해당 상품 목록 출력
select * from product 
where price >= 16000;

4. 할인 상품의 이름과 할인가격을 출력
   -- 할인가격 (16000원 이상인 제품만 대상으로 10% 할인된 가격)
   -- 할인상품명, 정상가, 할인가
select name, price as '정상가', (price * 0.9) as '할인가'
from product 
where price >= 16000;

  -- price가 16000원 이상이면 할인가, 아니면 할인가 = 정상가

select name, price as '정상가', 
if(price >=16000, round(price * 0.9, 0), price) as '할인가'
from product;

5. 11월에 입고된 상품만 출력 - 내장함수 사용(month(날짜))
select * from product 
where month(register_date) = 11;

select * from product 
where month(register_date) = 11
order by register_date asc;

- 월별 price합계
select month(register_date) as mon, sum(price) as sum
from product
group by mon
order by mon;

- 월별 price 중 가장 큰값 출력
select month(register_date) as mon, sum(price) as sum
from product
group by mon
order by sum desc
limit 0,1;

- max()
select month(register_date) as mon, max(sum(price)) as sum
from product
group by mon;

select max(sum) from (
select sum(price) as sum from product
group by month(register_date)) as p;

select date_format(register_date, '%y-%m') as mon, sum(price) as sum
from product
group by mon
order by mon;

insert into product(type_a, type_b, name, price, amount, sale_amount, register_date) values
('티셔츠','긴소매','라운드 티셔츠',15000,34,100,'2025-10-15');
--------------------------------------------------------

트랜잭션(transaction)
- 하나의 작업을 하기위한 명령어 묶음 단위
- 연관있는 작업들의 모임.
- 하나라도 명령어가 완료되지 못한다면(error) 취소
- 모든 명령어가 완료되어야 진행
- 완료와 취소를 나중에 결정하는 개념
- 데이터의 안전성 확보를 위한 방법

start transaction;
명령어1;
명령어2;
...
rollback; => 위에 작업한 명령어 모두 취소
commit; => 작업완료
DDL / DCL 포함 안됨.


1. insert into product(type_a, type_b, name, price, amount, sale_amount, register_date) values
('티셔츠','긴소매','라운드 티셔츠',15000,34,100,'2025-11-15');
2. 해당 제품 구매
3. 구매테이블 확인

start transaction;
insert into product(type_a, type_b, name, price, amount, sale_amount, register_date) values
('티셔츠','민소매','셔츠',10000,34,100,'2025-11-15');
insert into buy(customer, product_name, price, amount)
(select '도우너', name, price, 5 from product where num = 13);
select * from buy;
rollback;

DB에서 변수의 값을 저장할 경우
- 사용자 변수(전역변수), 지역변수
- 사용자 변수 Session 영역에 저장
- 한 클라이언트에서 정의한 사용자 변수는 다른 클라이언트가 보거나 사용할 수 없음.
SET @cnt := 값;  (:=, =)
select @cnt;
------------------------------------------

insert into product(type_a, type_b, name, price, amount, sale_amount, register_date) values
('티셔츠','민소매','셔츠',10000,34,100,'2025-11-15');
- DB에 가서 방금 insert한 제품의 num를 select
SET @cnt := (select max(num) from product);
insert into buy(customer, product_name, price, amount)
(select '또치', name, price, 5 from product where num = @cnt);

-------------------------------------------
과제
1. buy테이블에서 customer 이름을 홍0동 변경하여 출력 => select
-- 변경된 이름 출력
SELECT INSERT(customer, 2,1, 'O') FROM buy;

2. product 테이블에서 price가 40000 이상인 데이터는
상품명(할인상품)으로 표시 나머지는 상품명 => select
-- product 테이블 값 확인
SELECT * FROM product;
-- price로 구분한 상품명 출력
SELECT
IF(price >= 40000, CONCAT(name, '(할인상품)'), name) AS '상품명' 
// 4만 이상이면 할인상품으로 변경하고 아니면 그냥 name을 쓴다
FROM product;
-- price가 40000 이상 제품 확인
SELECT *
FROM product
WHERE price >= 40000;

3. 월별 매출(price * sale_amount)합계 출력
-- 월별 매출 합계 출력
SELECT MONTH(register_date) AS m, SUM(price * sale_amount) AS '월별 매출'
FROM product
GROUP BY m
ORDER BY m ASC;

4. price가 가장 높은(가장 비싼상품) 출력
-- 가장 비싼 상품 출력
-- LIMIT 사용
SELECT name
FROM product
ORDER BY price DESC
LIMIT 0,1;
>> limit을 썼을 때 문제점 : 가장 큰 값이 2개 이상일 때는 어떻게 할 것인가?
>> 갯수로 자를 때 limit을 사용. (갯수 제한의 의미가 있..어야 함)
// 하지만 여기서는 가장 큰 거 하나를 구하기에.. 중복이 몇 개인지를 알 수 없다.
// limit을 사용할 수 있는 상황, 아닌 상황 구분

-- 서브쿼리 사용
SELECT *
FROM product
WHERE price = 
(SELECT MAX(price)
FROM product
);
// 집계 함수는 그룹을 지었을 때 .. 사용! select, group by에만 사용이 가능하다... 
// 집계 함수에서 그룹을 이미 지은 칼럼은 select로 가져올 수 없다 (이미 그룹으로 하나가 되었기에)

// select name, max(price) from product group by name;은 안된다.
// select * from product where price = max(price);도 안된다.

>> 서브쿼리 사용 
select name from product where price = (select max(price) from prouct);

5. transaction을 사용하여
9월에 입고된 재고 상품을 product_non 테이블로 이동 => 미리(transaction 안먹음)
product_non 테이블은 product와 같은 형식으로 생성
기존 product 테이블에서는 삭제
완료되면 commit;
// 트랜잭션은 실행했다가 다시 되돌려주는 기능. 중간에 오류가 나도 진행은 된다.
// 차례대로 오류가 나는지 확인하면서 트랜잭션 사용
// 트랜잭션은 반대로 돌릴 수 있는 메모리를 따로 갖고 있음
// 트랜잭션에서는 데이터를 여러 개 넣어도 1개의 자리만을 차지한다. 그러므로 pk값을 1만 차지함.
(커밋해야 데이터 개수만큼 공간 차지)
// 트랜잭션에서 한번 넣었다 뺀 데이터도 pk값은 반영되기 때문에 트랜잭션에서 차지한 1자리는 다음번에 출력되지 않는다.
// 트랜잭션...... 성능 저하?? ??

-- 복사된 product_non 테이블 생성
CREATE TABLE product_non
LIKE product;
-- product_non 테이블 구조 확인
DESC product_non;
-- transaction을 사용
START TRANSACTION;
INSERT INTO product_non(
SELECT *
FROM product
WHERE MONTH(register_date) = 9
);
COMMIT;
-- product_non 테이블 값 확인
SELECT * FROM product_non;
-- product 테이블 9월 재고품 확인
SELECT *
FROM product
WHERE MONTH(register_date) = 9;
-- 9월에 입고된 재고 상품 product에서 삭제
DELETE FROM product
WHERE MONTH(register_date) = 9;
-- 삭제 확인
SELECT *
FROM product
WHERE MONTH(register_date) = 9;
// product_non 출력 시 pk값 2,3 출력

6번 같은 경우는 DB에서 처리하고 JAVA에 보내주는게 더 좋다
6. 가장 많이 판매한 제품의 이름을 출력 => buy 테이블 기준
-- buy 테이블 값 확인
SELECT * FROM buy;
-- 가장 많이 판매한 제품 이름 출력
-- LIMIT 사용
SELECT product_name 
FROM buy
GROUP BY product_name
ORDER BY SUM(amount) DESC 
LIMIT 0,1;
// desc와 limit을 같이 적절히 사용하면 될듯
// 근데 limit은 동일한 값이 여러 개 있어도 하나만 출력되기 때문에 동일한 값을 다 출력하고 싶다면.. 서브쿼리 사용
-- 함수 사용
SELECT product_name
FROM buy
GROUP BY product_name
HAVING SUM(amount) = (
SELECT MAX(s)
FROM (
SELECT SUM(amount) AS s
FROM buy
GROUP BY product_name
) AS m);

--- 강사님 코드 (서브쿼리 사용)
select product_name, sum(amount) as s from buy 
group by product_name 
having s = (select select max(total_sum) 
from (select sum(amount) as total_sum from buy group by product_name) as p);
// 서브쿼리 안에 서브쿼리를 한번 더 작성할 경우 as 사용!!

--- 누군가의 코드(ALL 사용)
select product_name, sum(amount) as s from buy group by product_name
having s <= all
(select sum(amount) as total_sum from buy
group by product_name);

-------------------------------------------------------
ALL(and) / ANY(or) 함수
- ANY : 칼럼명 비교연산자 (값 또는 서브쿼리) 여러개의 비교값 중 하나라도 만족하면 true
- ALL : 칼럼명 비교연산자 (값 또는 서브쿼리) 여러개의 비교값 중 모두 만족하면 true


ALL 사용 시
> ALL : 최댓값보다 크면~
>= ALL : 최댓값보다 크거나 같으면~
< ALL : 최소값보다 작으면~
<= ALL : 최소값보다 작거나 같으면~
= ALL : 서브쿼리의 결과가 반드시 하나일 때 가능, 결과가 여러건이면 오류
!= ALL : 서브쿼리의 결과가 반드시 하나일 때 가능, 여러건이면 오류
// != 대신 <> 도 가능 

---

======4일차======
DB 4일차

과제
1. buy테이블에서 customer 이름을 홍0동 변경하여 출력 => select
-- 변경된 이름 출력
SELECT INSERT(customer, 2,1, 'O') FROM buy;

2. product 테이블에서 price가 40000 이상인 데이터는
상품명(할인상품)으로 표시 나머지는 상품명 => select
-- product 테이블 값 확인
SELECT * FROM product;
-- price로 구분한 상품명 출력
SELECT
IF(price >= 40000, CONCAT(name, '(할인상품)'), name) AS '상품명' 
// 4만 이상이면 할인상품으로 변경하고 아니면 그냥 name을 쓴다
FROM product;
-- price가 40000 이상 제품 확인
SELECT *
FROM product
WHERE price >= 40000;

3. 월별 매출(price * sale_amount)합계 출력
-- 월별 매출 합계 출력
SELECT MONTH(register_date) AS m, SUM(price * sale_amount) AS '월별 매출'
FROM product
GROUP BY m
ORDER BY m ASC;

4. price가 가장 높은(가장 비싼상품) 출력
-- 가장 비싼 상품 출력
-- LIMIT 사용
SELECT name
FROM product
ORDER BY price DESC
LIMIT 0,1;
>> limit을 썼을 때 문제점 : 가장 큰 값이 2개 이상일 때는 어떻게 할 것인가?
>> 갯수로 자를 때 limit을 사용. (갯수 제한의 의미가 있..어야 함)
// 하지만 여기서는 가장 큰 거 하나를 구하기에.. 중복이 몇 개인지를 알 수 없다.
// limit을 사용할 수 있는 상황, 아닌 상황 구분

-- 서브쿼리 사용
SELECT *
FROM product
WHERE price = 
(SELECT MAX(price)
FROM product
);
// 집계 함수는 그룹을 지었을 때 .. 사용! select, group by에만 사용이 가능하다... 
// 집계 함수에서 그룹을 이미 지은 칼럼은 select로 가져올 수 없다 (이미 그룹으로 하나가 되었기에)

// select name, max(price) from product group by name;은 안된다.
// select * from product where price = max(price);도 안된다.

>> 서브쿼리 사용 
select name from product where price = (select max(price) from prouct);

5. transaction을 사용하여
9월에 입고된 재고 상품을 product_non 테이블로 이동 => 미리(transaction 안먹음)
product_non 테이블은 product와 같은 형식으로 생성
기존 product 테이블에서는 삭제
완료되면 commit;
// 트랜잭션은 실행했다가 다시 되돌려주는 기능. 중간에 오류가 나도 진행은 된다.
// 차례대로 오류가 나는지 확인하면서 트랜잭션 사용
// 트랜잭션은 반대로 돌릴 수 있는 메모리를 따로 갖고 있음
// 트랜잭션에서는 데이터를 여러 개 넣어도 1개의 자리만을 차지한다. 그러므로 pk값을 1만 차지함.
(커밋해야 데이터 개수만큼 공간 차지)
// 트랜잭션에서 한번 넣었다 뺀 데이터도 pk값은 반영되기 때문에 트랜잭션에서 차지한 1자리는 다음번에 출력되지 않는다.
// 트랜잭션...... 성능 저하?? ??

-- 복사된 product_non 테이블 생성
CREATE TABLE product_non
LIKE product;
-- product_non 테이블 구조 확인
DESC product_non;
-- transaction을 사용
START TRANSACTION;
INSERT INTO product_non(
SELECT *
FROM product
WHERE MONTH(register_date) = 9
);
COMMIT;
-- product_non 테이블 값 확인
SELECT * FROM product_non;
-- product 테이블 9월 재고품 확인
SELECT *
FROM product
WHERE MONTH(register_date) = 9;
-- 9월에 입고된 재고 상품 product에서 삭제
DELETE FROM product
WHERE MONTH(register_date) = 9;
-- 삭제 확인
SELECT *
FROM product
WHERE MONTH(register_date) = 9;
// product_non 출력 시 pk값 2,3 출력

6번 같은 경우는 DB에서 처리하고 JAVA에 보내주는게 더 좋다
6. 가장 많이 판매한 제품의 이름을 출력 => buy 테이블 기준
-- buy 테이블 값 확인
SELECT * FROM buy;
-- 가장 많이 판매한 제품 이름 출력
-- LIMIT 사용
SELECT product_name 
FROM buy
GROUP BY product_name
ORDER BY SUM(amount) DESC 
LIMIT 0,1;
// desc와 limit을 같이 적절히 사용하면 될듯
// 근데 limit은 동일한 값이 여러 개 있어도 하나만 출력되기 때문에 동일한 값을 다 출력하고 싶다면.. 서브쿼리 사용
-- 함수 사용
SELECT product_name
FROM buy
GROUP BY product_name
HAVING SUM(amount) = (
SELECT MAX(s)
FROM (
SELECT SUM(amount) AS s
FROM buy
GROUP BY product_name
) AS m);

--- 강사님 코드 (서브쿼리 사용)
select product_name, sum(amount) as s from buy 
group by product_name 
having s = (select select max(total_sum) 
from (select sum(amount) as total_sum from buy group by product_name) as p);
// 서브쿼리 안에 서브쿼리를 한번 더 작성할 경우 as 사용!!

--- 누군가의 코드(ALL 사용)
select product_name, sum(amount) as s from buy group by product_name
having s <= all
(select sum(amount) as total_sum from buy
group by product_name);

--------------------------------------------------------------------------------------------------------
ALL(and) / ANY(or) 함수
- ANY : 칼럼명 비교연산자 (값 또는 서브쿼리) 여러개의 비교값 중 하나라도 만족하면 true
- ALL : 칼럼명 비교연산자 (값 또는 서브쿼리) 여러개의 비교값 중 모두 만족하면 true


ALL 사용 시
> ALL : 최댓값보다 크면~
>= ALL : 최댓값보다 크거나 같으면~
< ALL : 최소값보다 작으면~
<= ALL : 최소값보다 작거나 같으면~
= ALL : 서브쿼리의 결과가 반드시 하나일 때 가능, 결과가 여러건이면 오류
!= ALL : 서브쿼리의 결과가 반드시 하나일 때 가능, 여러건이면 오류
// != 대신 <> 도 가능 

ANY 사용 시
>ANY  : 최소값보다 크면~
>= ANY : 최소값보다 크거나 같으면~
< ANY  : 최대값보다 작으면~
<= ANY  : 최대값보다 작거나 같으면~
= ANY : in과 같은 기능 (여러 건 넣어도 에러 x)
!= ANY : not in 과 같은 기능 (여러 건 넣어도 에러 x)
--------------------------------------------------------------------------------------------------------
[오늘 진도]

school : 학생이 강의를 수강하는 과정
학생(student) - 수강(attend) - 강의(course)
한 학생이 여러 강의를 수강할 수 있고, 강의는 여러 학생이 수강을 한다.
-> 학생과 강의는 다대다 관계이기 때문에 정규화에서 문제가 발생한다.
-> 둘을 이어주는 '수강' 테이블이 필요
한 학생이 여러 강의를 수강한다. 하나의 강의에 대해 1개의 수강신청이 발생함.
------------------------------------
pk, fk 칼럼명이 동일할 필요는 없다. (학번 -> 학생번호 이런 식으로 가능)
------------------------------------
erd 관계 선 구분하기!
점선과 실선 : 
- 점선: pk-fk의 연결 (비식별 관계)
- 실선: pk끼리의 연결(식별 관계)
------------------------------------
연산이 필요한 값은 int로 두지만, 학기, 학점과 같이 연산이 필요없는 값은 데이터 형식을 varchar로 둔다.
------------------------------------
논리 스키마 : 변수, 데이터 자료형, pk/fk 등... 정함

---------------------------------------------------
root에서 DB 생성 후 권한 부여
-- DB 생성
create database school

// 도메인은 화면에서 처리해주기

-- 외래키 추가하기
alter table attend add foreign key(at_std_num) references student(std_num);
alter table attend add foreign key(at_std_code) references course(co_code);

// fk 추가 시 desc table명; 했을 때 mul이라고 표시된다.
---------------------------------------------------------------------
1. 외래키 추가 
-1) 테이블 생성시 추가
CREATE TABLE 테이블명(
열명칭1 특성1... ,
열명칭2 특성2... ,
열명칭3 특성3...,
Primart key(열명칭),
Foreign key(열명칭) references 참조 테이블명 (열명칭)
(fk 2개일 시 , 찍고 fk키 하나 더 넣으면 됨)


-2) Alter table 추가하는 방법
- Attend 테이블에 외래키 추가

-- 관계 테이블을 마지막에 만들면서 fk 연결해주기!
-- fk 키는 나중에 추가하는 경우가 대부분

ALTER TABLE attend add foreign key(at_std_num) references student(std_num);

2. 외래키 규칙(참조 무결성)
: 참조되는 테이블에 없는 값은 추가할 수 없다.
(원 테이블에 없는 값을 외래키로 넣을 수 없음)
- null값은 가능
----------------------------------------------------------------------------
-- 문제
1. at_year을 2024년으로 업데이트 
update attend set at_year = 2024;

2. at_term은 at_num 1~10 => 1 11~22 => 2로 업데이트
update attend set at_term = 1 where at_num >= 1 and at_num <=10;
update attend set at_term = 2 where at_num >=11 and at_num <=22;

-- 강사님 코드
update attend 
 set at_term = (
if(at_num <= 10, 1, 2)
); ----> 2명만 채우기


3. 1학기의 점수만 채우기
at_mid : 40 / at_final : 40 / at_attend : 10 / at_hw: 10
update attend set at_mid = 40 where at_term = 1;
update attend set at_final = 40 where at_term = 1;
update attend set at_attend = 10 where at_term = 1;
update attend set at_hw = 10 where at_term = 1;

-- 강사님 코드
update attend 
set at_mid = 38, at_final =35, at_attend = 8, at_hw = 9 
where at_num in(1,3,5);

update attend set at_mid = 20, at_final = 31, at_attend = 50, at_hw = 15
where at_num in (2,4,6);

update attend set at_mid = 10, at_final = 26, at_attend = 13, at_hw = 7
where at_num in (7,9);


4. at_score 채우기 (1학기만 채우기
at_ mid + at_final + at_hw + at_attend
>= 90 'A'
>= 80 'B'
>= 70 'C'
>= 60 'D'
'F'

update attend set at_score = 'A'  where(at_mid + at_final + at_hw + at_attend) >= 90 and at_std_num >= 1 and at_std_num <= 10;
update attend set at_score = 'B' where(at_mid + at_final + at_hw + at_attend) >= 80;

update attend set at_score = 'C' where(at_mid + at_final + at_hw + at_attend) >= 70 and at_std_num >= 1 and at_std_num <= 10;

update attend set at_score = 'D' where(at_mid + at_final + at_hw + at_attend) >= 60 and at_std_num >= 1 and at_std_num <= 10;

update attend set at_score = 'F' where(at_mid + at_final + at_hw + at_attend) < 60;

-- 강사님 코드
update attend set at_score = (
case
when (at_mid + at_final + at_hw + at_attend) >= 90 then 'A'
when (at_mid + at_final + at_hw + at_attend) >= 80 then 'B'
when (at_mid + at_final + at_hw + at_attend) >= 70 then C'
when (at_mid + at_final + at_hw + at_attend) >= 60 then 'D'
else 'F'
end
)
where at_term = 1;
----------------------------------------------------------
5. at_repetition 재수강 여부 채우기 (1학기만 채우기)
at_score가 'F'이거나, at_attend이 2이하이면 'y'

update attend set at_repetition = 'y' where at_score = 'F' or at_attend < 2;

update attend set at_repetition = 'y' where at_score = 'F' or at_attend < 2;

update attend set at_score = null where at_term = 2;
update attend set at_repetition = 'n' where at_term = 2;


-- 강사님 코드
update attend set at_repetition =
if(at_score ='F' or at_attend <= 2, 'y', 'n')
where at_term = 1;
-------------------------
6. score별 인원수 집계
select at_score, count(at_score) from attend
group by at_score
having at_score is not null
order by at_score;
------------------------------------------
7. 재수강 인원 수 집계
select at_repetition as '재수강자', count(at_repetition) as '인원' from attend group by at repetition 
having at_repetition = 'y';
------------------------------------------
[ join ]
- 여러 개의 테이블을 묶어 하나의 테이블로 사용하는 것
- 원하는 데이터가 다른 테이블에 흩어져 있을 경우 join을 사용
- 내부조인(inner join) / 외부조인(outer join)
- 일반적인 조인은 내부조인 (inner join / join)
＊ inner join에서 inner는 생략 가능 (디폴트라서 ㄱㅊ)

select 열목록 from 테이블명
(inner) join 참조테이블명
on 조인조건 (fk 연결)
[where 조건]
[group by 열]
[having 조건]
[order by 열]

EX. 재수강자들의 이름을 알아보고 싶음
-> student 테이블에 있는 학생 이름을 가져와야 함
-----------------------------------------
[조인 사용하기]
1. 재수강 y인 사람 가져오기
select * from attend as a 
(inner)  join student as s // 조인 시 약어 주기
on a.at_std_num = s.std_num // 앞에다 테이블명 찍어주기 (설정한 약어) - 칼럼명이 동일한 경우 필수. 
where at_repetition = 'y'; 

((as 붙이셈))
select * from attend as a 
inner join student as s
on a.at_std_num = s.std_num 
where at_repetition = 'y'; 

// 두 테이블이 붙어서 출력됨. 근데 attend 먼저 줬으므로 attend 테이블 먼저 나오고 그 뒤에 student 테이블이 붙여져서 나온다.

// 조인에는 약어가 많이 붙는다.
어느 테이블에 있는 정보인지 테이블명을 앞에 붙여주는 것이 좋다.

2. 학점이 A인 학생들의 명단 출력
- 학번, 이름, 학점만 출력

select a.at_std_num as '학번', s.std_name as '이름', a.at_score as '학점' from attend as a
inner join student as s
on a.at_std_num = s.std_num
where at_score = 'A';

3. attend, student, course 3개 조인
select  * from attend a // 여기서 칼럼을 원하는 것만 골라냄
inner join student s
on a.at_std_num = s.std_num // attend랑 student 연결 
inner join course c
on a.at_co_code - c.co_code // attend랑 course 연결


 4. A를 받은 학생이 어느 과목에서 A를 받았는지 출력
select a.at_std_num as '학번', s.std_name as '이름', c.co_name as '과목명', a.at_score as '학점' from attend as a
inner join student as s
on a.at_std_num = s.std_num
inner join course as c
on a.at_co_code = c.co_code
where a.at_code = 'A';


5. F를 받은 학생 명단 (학번, 이름, 학과 출력)
select a.at_std_num, s.std_name, s.std_major from attend as a
inner join student as s
on a.at_std_num = s.std_num
where a.at_score = 'F';


6. A 학점을 받은 학생 명단(학번, 이름, 과목, 교수명)
select a.at_std_num, s.std_name, c.co_name, c.co_professor from attend as a
inner join student as s
on a.at_std_num = s.std_num
inner join course as c
on a.at_co_code = c.co_code
where a.at_score = 'A';


7. A 학점 학생들의 학번, 이름, 과목, (중간+기말+출석+과제) AS 성적
 select a.at_std.num, s.std_name, c.co_name, sum(at_mid + at_final + at_hw + at.attend) as '성적'  from attend as a
  on a.at_std_num = s.std_num
  inner join course as c
  on a.at_co_code = c.co_code
    where a.at_score = 'A';


8. 과목별 (co_name) 별 중간, 기말 출석 과제 합계 (각각 전체 합계)
// course의 co_code와 attend의 at_co_code

select c.co_name, sum(at_mid), sum(at_final) , sum(at_hw) , sum(at_attend)  from attend as a
inner join course as c
on a.at_co_code = c.co_code
where a.at_term = 1
group by c.co_code
order by c.co_name;

9. 학점별 중간, 기말, 출석, 과제 평균(null은 제외, 학점별 오름차순)
// student의 at_std_num, 
select a.at_score, avg(at_mid), avg(at_final), avg(at_attend), avg(at_hw) from attend as a
inner join student s
on a.at_std_num = s.std_num
group by a.at_score 
having a.at_score is not null
order by a.at_score asc;


10. 강철수가 수강하고 있는 과목명과 교수명 출력
// attned에 칼럼이 필요하진 않지만, student, course에 연결하려면 필요하다.

select c.co_name, c.co_professor from course as c
inner join attend as a
on a.at_co_code = c.co_code
inner join student as s
on a.at_std_num = s.std_num
where std_name = '강철수';


// 접점이 없으면 조인 불가능.

------------------------------------------------------
1. 프로그래밍 일반 과목을 듣는 학생 명단 출력
(학번, 이름, 학과)
select s.std_num, s.std_name, s.std_major from attend as a
inner join student as s
on a.at_std_num = s.std_num
inner join course as c
on a.at_co_code = c.co_code
where co_name = '프로그래밍 일반';
// 비어잇다고?

2. 홍길동 교수가 강의하는 과목을 듣는 학생 명단 출력
(학번, 이름, 학과, 수강과목, 담당교수)
// c 과목명과 
select s.std_num, s.std_name, s.std_major, c.co_name ,c.co_professor 
from student as s
join course as c
where co_professor = '홍길동'; 

3. 전봉준이 획득한 학점(co_point) 합계 => 강나래(5)
(2학기 미포함, 미이수학점은 제외)
(이름, 학점의 합계)
select std_name, s.std_major, sum(c.co_point)
from attend as a
inner join course as c
on a.at_co_code = c.co_code
inner join student as s
on a.at_std_num  = s.std_num
where std_name = '전봉준' and a.at_term = 1 and a.at_repetition = 'n';
// sum 자체가 그룹함수여서 group by를 쓰지 않아도 major 출력 불가!!!

-- 강사님 코드
select s.std_name, sum(c.co_point) as '획득학점'
from student s
join attend a on s.std_num = a.at_std.num
join course c on c.co)code = a.at. (마저적으렴)

select std_name, sum(c.co_point)
from attend as a
inner join course as c
on a.at_co_code = c.co_code
inner join student as s
on a.at_std_num  = s.std_num
where std_name = '강나래' and a.at_term = 1;
--------------------------------------------
-신입생 입학(수강을 안함)
insert into student values
('2024160003','김순이','컴퓨터공학',1,25),
('2024160004','홍순길','디자인',1,20);

- 학생 명단에는 있지만 수강하고 있지 않음
-> student에는 있지만 attend에는 없다.
// 먼저 적히는게 left!!!
select s.* from student as s // s 테이블 전부만 출력
left join attend as a
on a.at_std_num = s.std_num
// where을 입력하지 않으면 새로 입력한 애들이 전부 null로 나온다.
where a.at_num is null;
// left join을 주로 많이 쓴다.





select product_name from buy
group by product_name
order by sum(amount) asc
limit 0,1;