아시타가

CMD 관리자 권한 -> 파일경로 설정(cd \파일경로) -> mysql 접속 -> source db파일.sql 
하면 db내용 불러옮

Inner join -> 다 빼옴
outer join -> 필요한 부분만 뺴오기
cross join -> 행의 갯수 찾는용정도?

concat() 문자열을 이어줌

-- 프로시저 만들고 부르기
use testdb;

drop procedure if EXISTS userproc1;
delimiter $$
create PROCEDURE userproc1(IN username varchar(8))
begin
select * from usertbl where name = username;
end $$
delimiter ;

call userproc1('JSH');

-- 입력 매개 변수가 2개이상
use testdb;
drop procedure if EXISTS userproc1;
delimiter $$
create PROCEDURE userproc1(IN userYear int , IN userHeight int)
begin
select * from usertbl where birthYear > useryear and height > userHeight;
end $$
delimiter ;

call userproc1(1990, 173);

-- IF Else문
use testdb;
drop procedure if exists ifelseproc;
delimiter $$

create procedure ifelseproc(in username varchar(8))
begin
declare byear int; -- 변수 선언
    select birthyear into byear from usertbl where name = username;
    
    if(byear >= 1980) then
select '80년대생이야';
else
select '80년대생이 아니야';
end if;
end $$
delimiter ;

call ifelseproc('JSH');

-- Case문
use testdb;
drop procedure if exists caseproc;
delimiter $$

create procedure caseproc(in username varchar(8))
begin
declare byear int;
    declare tti char(3);
    select birthyear into byear from usertbl where name = username;    
    case
when (byear%12=4) then set tti = '쥐';
when (byear%12=5) then set tti = '소';
when (byear%12=6) then set tti = '호랑이';
when (byear%12=7) then set tti = '토끼';
when (byear%12=8) then set tti = '용';
when (byear%12=9) then set tti = '뱀';
when (byear%12=10) then set tti = '말';
when (byear%12=11) then set tti = '양';
when (byear%12=0) then set tti = '원숭이';
when (byear%12=1) then set tti = '닭';
when (byear%12=2) then set tti = '개';
when (byear%12=3) then set tti = '돼지';
        else set tti = '원숭이';
end case;
    select concat(username, '의 띠 ==>',tti);
end $$
delimiter ; 
call caseproc('JSH');

-- while문
use testdb;
drop procedure if exists gugutbl;
create table gugutbl (txt varchar(100));

drop procedure if exists whileproc;
delimiter $$

create procedure whileproc()
begin
declare str varchar(100);
declare i int;
declare k int;
set i = 2;
    
while(i<10) do
set str = '';
set k = 1;
    
while (k<10) do
set str = concat(str, '  ', i, 'x', k, '=', i*k);
set k = k+1;
end while;
set i = i +1;
insert into gugutbl values(str);
end while;
end $$
delimiter ;

call whileproc();
select*from gugutbl;

-- create -- 
/* USE testdb;

DROP TABLE IF EXISTS usertbl;
CREATE TABLE usertbl
(
userid CHAR(8) NOT NULL,
    name varchar(8) NOT NULL,
    birthYear INT NOT NULL
);

ALTER TABLE usertbl
add CONSTRAINT PK_usertb2_userid
PRIMARY KEY(userid);
    
DESCRIBE usertbl;
*/
-- ---------------------------------------------------------------------------------
-- 제약조건(프라이머리키 설정) --
/*use testdb;
-- 기본키 삭제
-- alter table prodtbl
-- drop primary key;

DROP TABLE IF EXISTS prodtbl;
CREATE TABLE prodtbl
(
prodCode CHAR(3) NOT NULL,
    prodID CHAR(4) NOT NULL,
    prodDate DATETIME NOT NULL,
    prodCur CHAR(10) NULL
);
ALTER TABLE prodtbl
add CONSTRAINT PK_prodtbl_prodcode_prodid 
PRIMARY KEY(prodCode,prodID);
    
-- 제약조건 (foreign 설정)
DROP TABLE IF EXISTS buytbl,usertbl;
CREATE TABLE usertbl
(
userID char(8) not null primary key,
    name varchar(10) not null,
    birthYear int not null
);
-- create table buytbl
-- (
-- num int auto_increment not null primary key,
    -- userID char(8) not null,
    -- prodName char(6) not null,
    -- foreign key(userID) references usertbl(userID) -- FK가 userID로 나옴
-- );
create table buytbl
(
num int auto_increment not null primary key,
    userID char(8) not null,
    prodName char(6) not null
);
alter table buytbl
add constraint FK_usertbl_buytbl -- FK가 
    foreign key(userID)
    references usertbl(userID);
    
 show index from buytbl;
 */
 
 -- ----------------------------------------------------------------------------------------
 -- 제약조건(unique 설정)
 /*use testdb;
 DROP table if exists buytbl,usertbl;
 create table usertbl
 (
userID char(8) not null primary key,
    name varchar(10) not null,
    birthYear int not null,
    email char(30) null unique -- UNIQUE가 email 이름으로 지정됌
 );
 
 show index from usertbl;
 
DROP table if exists buytbl,usertbl;
create table usertbl
(
userID char(8) not null primary key,
name varchar(10) not null,
birthYear int not null,
email char(30) null, 
    constraint UK_email unique(email) -- UNIQUE가 UK_email로 지정됌
);

 show index from usertbl;*/
 
 -- 제약조건 (check제약 조건)
 -- 출생년도가 1900년 이후 , 2023년 이전, 이름은 반드시 넣어야 됌
/*use testdb;
DROP table if exists usertbl;
create table usertbl
(
userID char(8) not null PRIMARY key,
    name varchar(10),
    birthYear int null check (birthYear >= 1900 and birthYear <= 2023),
    mobile1 char(3) null,
    constraint CK_name check(name is not null) -- 이름 반드시 넣음
);

select * from usertbl;

alter table usertbl
add constraint CK_mobile1
    check(mobile1 in('010','011','016','017','018','019'));
 
 select * from usertbl;
 */
 
 -- 제약조건 (default 설정) -- null 값이 없을 때 기본값이 들어가게 해줌.
/*use testdb;
DROP table if exists usertbl;
create table usertbl
(
userID char(8) not null primary key,
    name varchar(10) not null,
    birthYear int not null default '-1',
addr char(2) not null default '서울',
    mobile1 char(3) null,
    mobile2 char(8) null,
    height int null default 170,
    mdate date null    
);

SELECT * from usertbl;
*/
-- alter table 문 사용
-- 테이블에 속성들을 추가/변경/수정/삭제 기능들을 수행함
-- use testdb;
-- 열추가
-- alter table usertbl 
-- add homepage varchar(30)
-- default 'http:// www.xxxx.co.kr' -- DEFAULT 값
        -- null; -- null 값 허용

-- 순서 바꾸기
-- alter table usertbl
-- modify name varchar(8) not null first;

-- 열 삭제
-- ALTER table usertbl
-- drop column homepage;

-- 제약 조건 삭제
-- alter table usertbl
-- drop primary key; -- 프라이머리 키 삭제
-- alter table buytbl
-- drop foreign key FK_usertbl_buytbl; -- foreign 키 삭제

-- view 사용법
-- use testdb;
-- drop view if exists v_usertbl;
-- create view v_usertbl as (select userid ,name,addr from usertbl);
        
-- select * from v_usertbl;
-- ---------------------------------------------------------------------------------------

-- 필요한 부분만 뽑아서 만드는 view
-- inner join을 활용한 필요한 내용들 뽑아내기
-- 1.
-- create view v_userbuytbl
-- as select U.userID , U.name, B.prodName, U.addr , 
-- concat(U.mobile1,U.mobile2) as '연락처' 
    -- from usertbl U -- U라는 별칭을 만듬
-- inner join buytbl B -- B라는 별칭을 만듬
-- On U.userID = B.userID;
            
-- select * from v_userbuytbl where name = '김범수';

-- 2.
-- use testdb;
-- drop view if exists v_userbuytbl;
-- create view v_userbuytbl
-- as
-- select U.userid as 'USER ID' , U.name as 'USER NAME', B.prodname,
    -- U.addr , concat(U.mobile1 , U.mobile2) as 'MOBILE PHONE'
-- from usertbl U
-- Inner join buytbl B
-- On U.userID = B.userID; 

-- select 'USER ID' , 'USER NAME' FROM v_userbuytbl;

-- ALTER 구문을 이용한 뷰의 수정
/*use testdb;
alter view v_userbuytbl
as 
select U.userid as '사용자 아이디', U.name as '이름', B.prodname as '제품 이름',
U.addr, concat()
*/

-- 뷰 정보 덮어쓰기
-- create OR replace view v_userbuytbl

-- 뷰를 이용한 데이터 수정
-- use testdb;
-- select * from v_userbuytbl where userid = 'JKW';
-- update v_userbuytbl set addr = '부산' where userid = 'JKW';

-- 뷰를 이용한 그룹함수
/* use testdb;
create view s_sum
as
select userid as 'userid' , sum(price * amount) as 'total'
from buytbl group by userid;
        
select * from v_sum;
*/


나중에 정리할 것

 
 

체크하면 끝

- 인덱스

특정 부분을 빨리 찾기 위해 쓰이는 방법

CREATE INDEX (만들어질)인덱스이름 ON 테이블이름(인덱스할 칼럼); -- 인덱스 만듬

Navigator부분 마우스 우클릭하고 Refresh All하면 만들어진 인덱스 보임.

SELECT * FROM 테이블이름 WHERE 칼럼이름 = '찾을 내용';

- VIEW

필요한 데이터만 정리한 뒤 보여줄 목적으로 사용

CREATE VIEW (만들어질)테이블이름 AS SELECT 가져올 칼럼 FROM 테이블이름

AS는 별명을 붙일 때도 사용 되지만 AS아래의 내용을 addTable에 넣을 때도 사용 됩니다.

- PROCEDURE (프로시저)

쿼리문을 한번에 실행하기 위한 방법

- LIMIT

다른 테이블 내용을 몇개만 옮기고 싶을 때 사용

새로운 테이블을 만든 뒤 새로운 테이블이 employees의 테이블 내용 500개를 넣음.

- CREATE TABLE

테이블을 만듬

foreign key (userid) references usertbl(userid) - foreign key를 만드는데 참조할 곳의 칼럼을 지정

- BETWEEN A AND B

A와 B를 만족하는 범위를 설정

- IN

OR 관계를 묶는 연산자

- LIKE

문자열 검색 연산자

- 서브쿼리

() 안에 서브용 쿼리를 작성해서 사용

- ANY / ALL

ANY -> 서브쿼리에서 조건이 하나만 맞아도 실행

ALL -> 서브쿼리에서 조건이 다 맞아야 실행

- ORDER BY

- 테이블 정렬 조회

- DISTINCT

-  공통적인 것 묶음 (교집합)

추후 생기면 더 적음

※ ERD란?

Entity Relationship Diagram : 데이터베이스 구조를 한 눈에 알아보기 위해 그려놓는 다이어그램이다. ERD는 단어에서 의미하는 그대로 ' Entity 개체 '와 'Relationship 관계 '를 중점적으로 표시하는 다이어그램이다.

※ 왜 사용하는가? 

DB의 데이터가 무작위로 많아지면 필요한 데이터를 찾을 때 시간이 걸리기도 하고 , 결과값이 달라질 수 있는데 이걸 해결하기 위해서 모델링이 필요하다. 그 중 하나가 관계도로 모델링을 하는 것인데, 그걸 보기 편하게 보기 위해서 사용한다.

※ 어떻게 사용하는가? 

우선 물품이 양품으로 나왔는데 사용해보니 불량이였고, 어떤 시간 때에 그 불량이 연속적으로 나왔다는 시나리오가 있다고 생각해보자.

그럼 어떤 시간대에 불량이 나왔느지를 알아보고 폐기를 하던 수리를 해야 될 것이다.

1. 우선 필요한 데이터를 하나 결정한다. 예를들어 양품이 어디 위치에 있는지 알아보자.

테이블을 작성해 보자.

- 비금속은 불량인 제품이다.

- 금속은 양품인 제품이다.

이렇게 테이블을 작성 했을 시, 창고가 더 필요한 경우 창고를 더 만들어 줘야된다. 그러면 칼럼수가 계속 늘어나게 될 것이다. 그러면 연산시간이 더욱 늘어난다. 그러면 내가 필요한 데이터를 정리한 테이블을 만들 수 없을까? 라는 생각이든다.

- 그래서 정규화를 시켜 준다.

https://mangkyu.tistory.com/110

정규화가 이해가 쉽게 나와있다.

생산시간을 기준으로 각각의 테이블을 참조하여 필요한 데이터를 찾아서 볼 수가 있다.

정규화를 하면 무결성이 보장되지만 데이터를 넣고 빼고 하다보면 어쩔 수 없이 데이터를 건들여서 날아가던가 수정이 되던가 그럴 경우가 있다. 그럴 때 확실하게 연결하기 위해서 ERD를 활용한다.

※ ERD로 활용해서 써보자.

mysql-community로 작성함

mysql에서 file -> new model을 선택

1번을 더블클릭하여 2번의 DB의 이름을 설정해주고 3번의 ERD를 만들어준다.

테이블 생성 뒤

테이블을 더블 클릭하면 아래에 테이블 칼럼을 작성할 수 있다.

mem_time -> 생산시간은 유일한 값이여서 PK로 설정했다. DataType은 시간을 넣어야 되니 datetime으로 설정했다.

mem_magagin -> 물품은 금속,비금속 밖에 없으니 varchar(6)으로 설정해도 되지만 넉넉하게 8로 줬다.(한글은 글자당 2byte이므로 비금속이 최대 글자니까 6byte로 생각하면 됌)

나머지도 위에 정규화 테이블처럼 만들어준다.(나머지 생산시간도 PK로 설정함)

- 이제 관계도를 맺어줘야 된다.

생산시간으로 PK로 묶었기 때문에 각각 생산시간으로 관계가 엮인다.

근데 생산시간은 생산한 시간만 있는 고유의 값이므로 각가의 테이블의 생산시간이랑 1:1일 수 밖에 없는 구조이다. 

다음과 같이 연결한다.

확실한 관계도 일 경우 Identifying Relationship을 쓴다.

(여기서는 관계가 확실하게 생산시간으로 했기 때문에 이렇게 씀.)

(보통은 Non-Identifying Relationship을 많이 쓴다.)

- 이제 DB로 만들어보자

클릭한 후

Next

Next

비밀번호 쳐주고 Next

만들어 진 것이 보일 것이다. Next

체크가 다되면 Close하면 된다.

- 이제 DB가 만들어 졌는지 확인해보자

Refresh All하면 DB가 나올 것이다.

칼럼에 속성이 관계도로 의해서 quality_mem_time으로 만들어져, 필요한 정보를 찾을 수 있다.

※ 설치

https://m.blog.naver.com/bjh7007/221829548634

위 사이트에 설치방법은 잘 나와있음 

※ 환경 설정

- CMD 창에서 쓰기 위해서 환경 설정을 해줍니다.

Power shell을 켜고

SETX PATH "%PATH%mysql 서버 설치한 곳 bin폴더" 로 경로 설정합니다.

잘됬으면 성공이라고 뜰 것 입니다.

cmd를 켜시고

mysql -u root -p 후 비밀번호를 치고 접속이 되면 환경설정은 끝난 것 입니다.

※ 기본 명령어

show databases; -> DB보여 줍니다.

use "DBname" -> DB를 사용해서 DB안의 내용을 보겠다는 것입니다.

show tables; -> DB안의 table을 봅니다.

SELECT * FROM "DB.TABLE" -> 테이블의 모든 정보를 다 볼 수 있는 쿼리문 입니다.

SELECT "칼럼" FROM "DB.TABLE"; -> 특정한 칼럼만 볼 수 있습니다.