SQL 20221118

DAY 24

MySQL설치 방법
• mysql 검색 -> MySQL Installer 8.0.31 용량 적은거 다운 -> custom -> MySQL 8.0.23 -> Authentication Method 아래것
• 제어판 - 시스템 - 고급시스템설정 - 환경변수 - 사용자 변수 Path - 사용자 변수 Path - 새로만들기 (c\Program Files\ MySQL\ SQL Server 8.0\bin)
• cmd -> mysql -u rood -p

Heidisql 또는 Dbeaver 설치
heidisql 검색, dbeaver검색 - Driver properties 다운 - driver properties\allowPublicKeyRetrieval -> ture 설정

SQL

자바에서 dummydata 생성 후 sql에 옮겨도 됨.
dummydata를 생성할 때는 무조건 html파일 형식으로 해야함.
p{ 번호 상승시킬 부분은 $로 표현 }*100 : 100개의 dummy data생성
(; 필요없음)
ctrl+space : dummydata확인
enter : dummydata생성

CURD
-- create
insert into 테이블 (컬럼1, ..., 컬럼 n ) values (값1, ..., 값n)
-- update
update 테이블 set 컬럼 = 값.. where 조건식
-- read
select 선택할 컬럼 from 테이블 where 조건식
-- delete
delete from 테이블 where 조건식

heidi 다운받아도 sql 실행가능
dBeaver 다운로드해서 database 생성

단축키

F9 : 실행
ctrl + enter : 커서가 있는 script 실행
alt+insert : table data에 데이터 입력하는 단축키
-- : 주석 기호
ctrl+shift+delete : 한줄 지움
ctrl+alt +방향키 : 줄단위로 복사
script 실행 후 f5눌러서 실행 하기

• table에 들어가서 create new table
create new column으로 새로 생성해도 되지만 Script에서 생성가능

• Column이름 : 테이블 이름을 따서 prefix를 붙임

• 테이블 생성 후 폴더에 나타나지 않으면 테이블을 클릭 후 F5

• desc 테이블 이름 : 테이블 데이터 정보 출력

SQL test

-- test 데이터 베이스 생성
create database test;
-- test 데이터베이스로 사용 전환
use test;

-- 테이블 생성
create table member_info (
	mi_seq int not null auto_increment primary key,
	mi_id varchar(20) not null,
	mi_pwd varchar(128) not null,
	mi_name varchar(100) not null,
	mi_email varchar(100) not null,
	mi_birth date not null,
	mi_gen int not null default 0,
	mi_reg_dt datetime not null default CURRENT_TIMESTAMP,
	mi_status int not null default 1
);
-- 데이터 입력 (하나)
insert into member_info
(mi_id, mi_pwd, mi_name, mi_email, mi_birth, mi_gen)
values
('user001', '123456', '김데이터', 'data@service.com', '2022-11-17', 0);

-- 데이터 입력 (둘 이상)
insert into member_info
(mi_id, mi_pwd, mi_name, mi_email, mi_birth, mi_gen)
values
('user002', '123456', '김데이터', 'data1@service.com', '2022-11-17', 1),
('user003', '123456', '이데이터', 'data2@service.com', '2022-11-17', 2),
('user004', '123456', '박데이터', 'data3@service.com', '2022-11-17', 0),
('user005', '123456', '최데이터', 'data4@service.com', '2022-11-17', 1),
('user006', '123456', '정데이터', 'data5@service.com', '2022-11-17', 2),
('user007', '123456', '주데이터', 'data6@service.com', '2022-11-17', 0),
('user008', '123456', '장데이터', 'data7@service.com', '2022-11-17', 1);

select * from member_info;
-- delete from member_info;
-- truncate member_info; 

create table post_info(
	pi_seq int not null primary key auto_increment,
	pi_title varchar(255) not null,
	pi_content text not null,
	pi_read_count int not null default 0,
	pi_reg_dt datetime not null default CURRENT_TIMESTAMP,
	pi_mod_dt datetime not null default CURRENT_TIMESTAMP,
	pi_status int not null default 1,
	pi_mi_seq int not null
);
insert into post_info(pi_title, pi_content, pi_mi_seq)
values
('점심은 뭘 드셨나요', '점심 잘 먹고 왔는데 왜 배고프져', 1),
('점심은 뭘 드셨나요', '점심 잘 먹고 왔는데 왜 배고프져', 2),
('점심은 뭘 드셨나요', '점심 잘 먹고 왔는데 왜 배고프져', 3);

select * from post_info;
select * from member_info;

-- drop table person;
-- select * from person;
-- drop table member_info;
-- drop table post_info;

desc member_info;
desc post_info;
-- 컬럼명, 타입 모두 가능
alter table post_info change pi_read_count pi_count int;

alter table member_info add column address varchar(255) after mi_gen;
alter table member_info change address mi_address varchar(255);
-- 타입바꾸기
alter table member_info modify mi_name varchar(60);

create table comment_info(
 	-- 댓글들을 구분하기 위한 번호
	ci_seq int not null auto_increment primary key,
	ci_content varchar(255) not null,
	ci_mi_seq int not null, -- 작성한 사용자의 번호
	ci_pi_seq int not null, -- 댓글이 소속된 글의 번호
	ci_reg_dt datetime not null default CURRENT_TIMESTAMP
);

desc comment_info;
insert into comment_info(ci_content, ci_mi_seq, ci_pi_seq)
values
('소고기 사줘요', 1, 1),
('삼겹살 사줘요', 1, 2),
('치킨에 맥주하죠', 2, 1),
('삼겹살에 소주 한잔 하죠', 2, 1),
('파전에 막걸리 한 잔 하죠', 4, 1),
('그냥 잠이나 잘래요', 3, 1);
insert into comment_info(ci_content, ci_mi_seq, ci_pi_seq)
values('그냥 잠이나 잘래요', 3, 1);

select * from comment_info;
-- 데이터 수정
update comment_info set ci_content='따로국밥도 맛있던데'
where ci_seq = 2;

update comment_info set ci_content='갑자기 철판 닭갈비 볶음밥이 떠올라'
where ci_seq = 3;

update comment_info set ci_content='내 콩나물 국밥집 내놔!!ㅠㅠ'
where ci_seq = 4;

delete from comment_info where ci_mi_seq = 3;

select ci_content from comment_info;
select ci_content as content, ci_seq as no, ci_pi_seq as post 
from comment_info;

select mi_id, mi_name from member_info;

select pi_title, pi_content from post_info where pi_seq = 1;
select ci_content, ci_pi_seq from comment_info where ci_pi_seq = 1;

-- mi_name 값이 김OOOO인 것들 
select * from member_info where mi_name like '김%';
select * from member_info where mi_name like '%팔';
select * from member_info where mi_name like '%협%';

update member_info set mi_name = '곽두팔' where mi_seq = 1;
update member_info set mi_name = '서팔광' where mi_seq = 2;

SQL 다양한 예시

select mi_id, mi_name, mi_email, mi_gen from member_info
where 
-- 연령대가 30대 
YEAR(now()) - mi_birth >= 30 and YEAR(now()) - mi_birth < 40
-- 우수회원
and mi_grade = 2;
-- 일반회원의 수
select count(*) from member_info where mi_grade = 1;
-- 18세 미만의 사용자 목록
select * from member_info where YEAR(now()) - mi_birth < 18;
-- 사용자 이름이 김OO 인 데이터 조회 
select * from member_info where mi_name like '김%';
-- 가입 대기 상태의 사용자 목록 조회
select * from member_info where mi_status = 1;
-- 가입 대기 상태의 회원의 가입을 승인 처리
update member_info set mi_status = 2 where mi_status = 1;
-- 우수 회원의 목록 조회
select * from member_info where mi_grade = 2;
-- 우수회원을 VIP 회원으로 등급 승격
update member_info set mi_grade = 3 where mi_grade = 2;
-- 영구정지 상태의 사용자 데이터 말소
delete from member_info where mi_status = 4;
-- 미성년자 회원 데이터 모두 삭제
delete from member_info where YEAR(now()) - mi_birth < 18;

-- 사용자로부터 ID와 비밀번호를 입력받아서, 로그인이 가능한지 여부를 체크
select count(*) >= 1 
from member_info where mi_id = 'user05' and mi_pwd = '1234';

-- user04라는 아이디로 가입한 회원이 있는지 조회
select count(*) >= 1 from member_info where mi_id='user04';
-- 사용자 e-mail이 user011@service.com인 사용자가 여부 조회
select count(*) >= 1 from member_info 
where mi_email='user011@service.com';
-- 오름차순
select * from employee order by emp_monthly_pay asc;
-- 내림차순
select * from employee order by emp_monthly_pay desc;
-- 평균, 최소값, 최대값, 합계 조회
select avg(emp_monthly_pay) from employee;
select min(emp_monthly_pay) from employee;
select max(emp_monthly_pay) from employee;
select sum(emp_monthly_pay) * 12 from employee;

Dependency (의존성)

create database school_db;
use school_db;

create table student(
	stu_no int not null auto_increment primary key,
	stu_name varchar(50) not null
);

create table subject(
	sub_no int not null auto_increment primary key,
	sub_name varchar(50) not null,
	sub_credit int not null default 1
);
-- 학생 점수 데이터는 학생과 과목에 대한 정보가 먼저 채워져야, 생성 가능한 테이블
-- 학생 점수 테이블은, 학생테이블과 과목테이블에 의존적(dependency)이다

create table student_score(
	score_no int not null auto_increment primary key,
	score_stu_no int not null,
	score_sub_no int not null,
	score_score int not null default 0
);

insert into student(stu_name)
values
('최시진'),('강승아'),('정예현'),('오승진'),('임현은'),('최서혁'),
('최유진'),('최도유'),('신하은'),('박예진'),('정도은'),('정서은');

insert into subject(sub_name, sub_credit)
values ('객체지향 설계', 3),('알고리즘', 3),('형식언어론', 3),('컴퓨터구조론', 3),
('수치해석학', 3), ('데이터 통신', 3), ('데이터베이스', 3), ('데이터베이스', 3),
('운영체제', 3), ('소프트웨어 공학', 3), ('유닉스시스템', 3), ('이산수학', 3),
('디지털 설계', 3), ('프로그래밍 언어론', 3), ('파일구조론', 3), ('자료구조론', 3);

select * from student;
select * from subject;
update subject set sub_name = '자바프로그래밍' where sub_no = 8;

insert into student_score(score_stu_no, score_sub_no, score_score)
values 
(1,1,95), (1,2,85), (1,3,87), (1,4,78), (1,5,77), (1,6,88),
(2,1,85), (2,2,99), (2,3,77), (2,4,68), (2,5,61), (2,6,81),
(3,1,73), (3,2,75), (3,3,97), (3,4,77), (3,5,67), (3,6,78),
(4,1,91), (4,2,84), (4,3,77), (4,4,68), (4,5,97), (4,6,58),
(5,1,75), (5,2,98), (5,3,99), (5,4,96), (5,5,91), (5,6,94);
-- 뷰 생성 (복잡한 조회문을 저장)
create view score_summary as
select b.stu_name, c.sub_name, a.score_score, c.sub_credit 
from student_score a join student b
on a.score_stu_no = b.stu_no join subject c
on a.score_sub_no = c.sub_no;

-- 학생 별 평균, 최대, 최소 점수
select stu_name, avg(score_score) from score_summary group by stu_name;
select stu_name, max(score_score) from score_summary group by stu_name;
select stu_name, min(score_score) from score_summary group by stu_name;
-- 과목 별 평균, 최대, 최소 점수
select sub_name, avg(score_score) from score_summary group by sub_name;
select sub_name, max(score_score) from score_summary group by sub_name;
select sub_name, min(score_score) from score_summary group by sub_name;
-- 알고리즘 과목의 순위
select * from score_summary where sub_name = '알고리즘' order by score_score desc;