Weekly Retrospective(주간 회고) - 5주차
1. 데이터베이스
1) 용어
- 데이터베이스 : 기업이나 조직에서 필요한 데이터를 저장해 놓은 데이터 집합
- DBMS(Database Management System)
데이터를 관리하는 시스템, 데이터의 유지보수나 보안을 유지할 수 있도록 제공되는
소프트웨어 DBMS를 이용해서 데이터를 입력, 수정, 삭제, 조회하는 일들을 처리할 수 있다.
- DBA(Database Administrator)
데이터를 관리하는 업무를 담당하는 사람, 데이터설계, 인덱싱, 튜닝, 백업.....
2) DBMS의 장점
- 무결한데이터 유지
- 데이터를 공유
- 데이터의 일관성을 유지
- 중복을 최소화할 수 있다.(관계형데이터베이스의 특징)
3) 관계형데이터베이스
- mysql,oracle..관계형데이터베이스
- 데이터를 관리하는 최소단위는 테이블로 2차원 표의 형태
- 데이터를 처리하기 위한 명령문을 사용
--------
SQL
- 테이블(Relation), 컬럼(attribute), 레코드(tuple)
- 테이블의 무결성이나 작업의 편의성, 공간의 최소화를 위해 테이블을 나눠서 관리한다.
테이블을 분리하는 것을 테이블정규화라 한다.
- 테이블에서 레코드를 구분하는 컬럼을 기본키라 한다.(pk - 기본키는 중복이 없고
반드시 데이터가 있어야한다.)
- 테이블 정규화 후에 기본키 테이블의 기본키를 참조해서 쓰는 테이블을 외래키테이블이라
하고 이런 컬럼을 외래키(fk)
4) SQL
- 데이터의 조회, 수정, 삭제, 입력에 사용되는 언어
DML(데이터조작어) : insert, update, delete
DDL(데이터정의어) : create table, alter table, drop table, 인덱스, 여러 가지 객체들을 관리 ...
DCL(데이터제어어) : grant..
TCL(트랜잭션처리어) : commit,rollback..
Query(데이터조회) : select
- 기본select
- 함수
- 그룹화
- 조인
- 서브쿼리
쇼핑몰
고객
상품
주문일반
주문상품
게시판
Q&A
상품댓글
CGV
영화(영화아이디, 영화명, 사진, 정보, 가격,장르,감독, 제작사, 배우
영화배우(아이디, 배우, 영화아이디)
=> 여러 데이터가 텍스트로 들어가는 경우 json형식, ,로 구분
구매내역
고객
극장
상영관
상영시간
좌석
2. mysql명령
계정접속 -> 데이터베이스접속 -> 테이블사용
1) mysql접속
mysql -u 계정 -p패스워드 데이터베이스명
mysql -u 계정 -p패스워드
mysql -u 계정 -p 데이터베이스명
=> root계정으로 접속
=> -p만 입력하면 패스워드를 입력해야 함
ex) mysql -u root -p
3. 데이터베이스 관련 명령어
mysql을 설치하면 기본으로 제공되는 데이터베이스, mysql을 실행할 때 사용하는 설정 값이나 기본로그에 관련된
데이터
information_schema : db의 메타데이터보관(테이블, 컬럼타입, 접근권한정보...)
performance_schema : db관련 여러 작업을 모니터링할때 사용(락, 싱크로문제 모니터링)
sys : mysql의 실행 이력(유저, 호스트, 세션정보)
mysql : 시스템데이터베이스로 mysql을 운영할때 필요한 정보(권한, 프로시져, 유저정보...)
1) 데이터베이스 목록 확인
show databases
2) 현재 접속 중인 데이터베이스를 확인
select database()
3) 사용자정의 데이터베이스를 생성
create database 데이터베이스명
4) 사용할 데이터베이스 선택
use 데이터베이스명
[미션1]
- root계정에 접속해서 mytestdb데이터베이스 생성
- mytestdb데이터베이스에 접속
- 현재 사용 중인 데이터베이스 조회
- 캡쳐 후 제출
4. 사용자관리 명령어
1) 사용자 생성
=> create user
[형식]
create user '사용자계정'@'호스트명' identified by '패스워드';
-----------
localhost(127.0.0.1), ip, %
접근을제한할 수 있다.
ex) mydb계정생성(패스워드1234)
내부에서만 접근이 가능한 사용자 생성
create user mydb@localhost identified by '1234';
192.168대역의 ip에서만 접근을 허용하는 사용자
create user mydb@'192.168.%' identified by '1234';
외부의 모든 접근을 허용
create user mydb@'%' identified by '1234';
192.168.0.15의 ip에서만 접근을 허용
create user mydb@'192.168.0.15' identified by '1234';
2) 계정삭제
drop user 계정명@호스트
ex) mytest계정의 삭제
5. 권한관련 명령
- 계정을 생성한 후에 사용할 수 있는 권한을 설정해야한다.
- 권한은 root계정에서 정의
- root계정은 보안상의 이유로 localhost 로
[형식]
grant [권한list] on [데이터베이스.테이블] to 계정
=> 권한list를 계정에게 부여(데이터베이스.테이블에서만 사용할 수 있는 권한을 부여)
1) 특정 계정의 권한을 확인
show grants for mydb@localhost
------ ------------
계정 호스트
2) 현재 접속한 계정의 권한을 확인
show grants for current_user
3) 권한부여
[모든권한]
grant all privileges on 데이터베이스명.테이블명 to sample@localhost
grant all privileges on mytestdb.* to sample@localhost
=> mytestdb데이터베이스의 모든 테이블에 대해서 모든 권한을 부여
grant all privileges on *.* to sample@localhost
=> 모든 데이터베이스의 모든 테이블에 대해서 모든 권한을 부여
[ 특정권한]
grant insert,update on mytestdb.emptest2 to sample@localhost
---------------- -----------------------
권한리스트 데이터베이스.테이블명
- mydb@localhost계정이 mytestdb데이터베이스의 emptest테이블에 대해서 insert와 select할 수 있도록
권한을 부여
[미션]
1)계정생성과 권한
sample계정을 localhost로 생성하고 mytestdb데이터베이스의 emptest2테이블에 insert,update권한을 부여
===> root계정
====== sample계정으로 접속 후 테스트 =====================
insert into emptest2 values('bts1','1234');
추가 후 작업상태확인
update emptest2
set pass = '2345';
의 실행확인
실행확인
select * from emptest2;
2) 생성된 계정확인 ====root계정=============
3) sample계정으로 접속 후 현재 계정의 권한을 조회
=> 실행결과 캡쳐 후 제출
6. select
- sql문은 대소문자 구분하지 않는다.
- sql명령문의 끝에는 ;을 추가하기
- select명령문에서 select절 뒤에 *을 추가하면 모든 컬럼을 조회하겠다는 의미
1) 기본select
[구문]
select 컬럼명1, 컬럼명2 ...
from 테이블명1, 테이블명2....
- 컬럼과 컬럼, 테이블과 테이블을 구분하기 위해서 ,(콤마)를 이용해서 구분
select empno,ename,hiredate,sal
from emp;
- 컬럼,테이블에 별칭(alias)을 사용할 수 있다. 별칭을 사용하면 컬럼명 대신에 별칭이 컬럼명 처럼 표시된다.
select 컬럼명 as alias
from 테이블명
or
select 컬럼명 alias
from 테이블명
- 함수나 연산식(사칙연산)을 컬럼에 정의할 수 있다.
- alias에 특수문자나 공백을 포함하고 싶은 경우 ""(큰따옴표)로 묶어서 정의
- null
* null은 컬럼에 값이 없는 것
* null은 연산을 해도 결과가 null
- distinct를 이용하면 중복을 제거
----------
컬럼 1개에 대해서 중복을 제거할 수 있지만 값이 동일한 레코드를 제거하기 위한 목적으로 사용
2) 조건을 적용해서 조회하기
[구문]
select 컬럼명1, 컬럼명2 ...
from 테이블명1, 테이블명2....
where 조건
① 조건
- true나 false를 리턴할 수 있는 식
- 컬럼명 연산자 비교할값
-------
비교연산자 - >, >=, <, <=, <>(ANSI의 표준), !=, =
- 문자열이나 날짜데이터를 표현하는 경우 ""나 ''로 리터럴을 표현
② 조건이 여러 개
- and조건(모든조건이 true)
컬럼명 연산자 비교할값 and 컬럼명 연산자 비교할값 .......
- or조건(조건 중 한 개가 true)
컬럼명 연산자 비교할값 or 컬럼명 연산자 비교할값 .......
[미션1]
sal가 2000이상이고 hiredate가 81/6/1이전에 입사한 사원의 모든 정보출력
select *
from emp
where sal>=2000 and hiredate < '81/6/1'
[미션2]
sal가 1000에서 2000사이인 직원리스트 조회
select *
from emp
where sal>=1000 and sal<=2000;
[미션3]
sal가 1250이거나 2975이거나 3000인 직원의 리스트 조회 ②④⑤⑥⑦⑧⑨⑩
select *
from emp
where sal=1250 or sal=2975 or sal=3000;
③ in연산자
④ between ~ and
⑤ not연산자
- 부정연산자
⑥ null
- null인 데이터를 조회
where 컬럼 is null
- null이 아닌 데이터 조회
where 컬럼 is not null
⑦ like
- 정확하게 일치하지 않아도 비교
- 와일드카드를 해석해서 비교
% : 모든문자열
_ : 글자 한 자리
①②③④⑤⑥⑦⑧⑨⑩
3) 정렬
[구문]
select 컬럼명1, 컬럼명2 ...
from 테이블명1, 테이블명2....
where 조건
order by 정렬할컬럼1 정렬기준
① 오름차순
생략하면 기본값이 오름차순
- 수치데이터 작은값 -> 큰값
1, 2, 3, .... 10
- 문자데이터는 같은 문자열을 모으겠다는 의미
ㄱ, ㄴ ..... ㅎ
② 내림차순
- 큰값 -> 작은값
10,9....1
ㅎ....ㄱ
Z..... A
7. group by
- 그룹별 통계를 계산하고 싶은 경우 사용
- 여러 레코드의 데이터를 그룹으로 묶어서 처리하는 방식
- 그룹함수를 사용
[구문]
select 컬럼명1, 컬럼명2 ...
from 테이블명1, 테이블명2....
where 조건
group by 컬럼명(그룹화 하고 싶은 기준이 되는 컬럼)
having 조건
order by 정렬할컬럼1 정렬기준
1) 그룹함수
sum, avg, max, min, count
2) group by
- group by를 사용하는 경우 select절에는 그룹화할 컬럼과 집계함수만 정의
- group함수는 여러 개 정의하고 사용할 수 있다.
- 조건을 확인해서 group by하기 전에 적용해야하는 조건은 where절에 추가한다.
- group by한 결과에 조건을 적용해야 하는 경우 having절을 이용해서 조건을 추가
[미션]
1. 직업별 인원수와 평균sal출력
2. 직업별 인원수와 평균sal출력 단, job이 PRESIDENT인 경우 제외
select job, count(empno) num,avg(sal) avg
from emp
where job <> 'PRESIDENT'
group by job;
3. 직업별 인원수와 평균sal출력 단, job이 PRESIDENT인 경우 제외하고 인원수가 3명 이상인 결과만 출력하세요
select job, count(empno) num,avg(sal) avg
from emp
where job <> 'PRESIDENT'
group by job
having count(empno)>=3;
4. 매니저가 관리하는 직원의 인원수를 출력
단, mgr이 널인 사원은 제외하고 관리하는 직원이1명인 관리자는 제외하고 출력하세요
select mgr, count(empno)
from emp
where mgr is not null
group by mgr
having count(empno) <> 1;
①②③④⑤⑥⑦⑧⑨⑩
8. 함수
1) 형변환
2) 제어흐름
① if함수(mysql에서만 지원되는 함수)
=> 조건에 따라서 값을 다르게 처리하기 위해 필요한 함수
if(조건, 조건이 만족하면 출력할 값, 조건이 만족하지 않으면 출력할 값)
if(job='PRESIDENT', '대표이사','사원')
------------------- ----------- -------
조건 true false
sal가 3000이상 직원은 deptno를 10과 같은지 비교, sal가 3000이상이 아니면 deptno가 20과 같은지 비교해서
일치하는 레코드를 출력(사번, 성명, sal, deptno)
② case문
case 표현식
when 값1 then 결과1
when 값2 then 결과2
when 값3 then 결과3
.......
end
표현식을 평가해서 값1이면 결과1을 할당, 값2면 결과2를 할당....
select ename,deptno,
case deptno
when 10 then "인사팀"
when 20 then "전산실"
when 30 then "총무과"
else "기타"
end as dept_name
from emp;
case
when 조건식1 then 결과1
when 조건식2 then 결과2
when 조건식3 then 결과3
.......
end
select ename,deptno,sal,
case
when sal>=3000 then "high"
when sal between 2000 and 2999 then "mid"
else 'low'
end as grade
from emp;
- case로 시작하고 end로 종료
- ANSI표준
③ ifnull
=> 컬럼의 값을 평가해서 null인 경우와 null이 아닌 경우 값을 다르게 출력하고 싶은 경우 사용
ifnull(null이 아닌 경우 출력할 값, null경우 출력될 값)
------------------------------
null인지 평가도 하고 출력할 값
컬럼
[미션]
comm이 널이면 1000을 출력 comm널이 아니면 그대로 comm값을 출력
[미션]
comm이 널이면 연봉을 계산했을때 null이 나온다. sal*12+comm을 출력 단, comm이 널(연산결과가 널)
3) 문자열함수
=> 문자열 조작과 관련된 함수
① concat
=> 문자열을 연결해서 출력
concat(문자열1, 컬럼1, 문자열2.....)
② field
field(찾을문자열,문자열1,문자열2,문자열3...)
=> 찾을 문자열과 정확하게 일치하는 문자열을 찾아서 index를 리턴
=> 일치하는 문자열이 없으면 0을 리턴
select field("test","testjava","test","testtest");
③ find_in_set
find_in_set(찾을문자열,"문자열1,문자열2,문자열3....")
=> 찾을 문자열과 정확하게 일치하는 문자열을 찾아서 index를 리턴
=> ,로 구분된 문자열안에서 찾는다.
=> 공백이 있으면 안된다.
select find_in_set("test","testtest,java,test,python");
④ instr
=> 문자열에서 지정한 문자가 위치하는 index를 리턴
=> 왼쪽에서 처음 만나는 문자의 index리턴
instr(전체문자열, 찾을문자)
select * from emp
where instr(ename, 'A') = 2;
⑤ substr
index번째 문자를 추출
substr(문자열,index)
[미션]
ename이 R로 끝나는 사원의 목록을 출력
[미션]
comm이 널이면 연봉을 계산했을때 null이 나온다. sal*12+comm을 출력 단, comm이 널(연산결과가 널)
⑥ format
=> 숫자에 천 단위로 ,를 삽입하기 위해서 사용하는 함수
⑦ upper, lower
⑧ lpad, rpad
=> 전체 문자열의 빈 공간을 지정해 놓은 특수문자로 채워서 리턴하는 함수
lpad("원본문자열" or 컬럼, 전체자리수, 채울특수문자)
⑨ trim, ltrim, rtrim
=> 지정한 문자열이나 컬럼에서 공백이나 문자를 지우기 위해 사용
=> 문자를 지정하지 않으면 공백을 지운다.
trim([[leading | trailing|both] 제거할문자 [from] ] 원본문자열)
--------- ------- --------
앞쪽제거 뒤에서제거 |___ 양끝에서 제거
⑾ repeat
select repeat("java",10);
=> 지정 문자열을 횟수만큼 반복해서 출력
⑿ reverse
select reverse("java");
=> 거꾸로 출력
⒀ replace
select replace("java programming","a","A");
----- ----
old new
=> old문자열을 new문자열로 교체
①②③④⑤⑥⑦⑧⑨⑩
4) 수학삼각
① abs :절대값
② round : 반올림 (소수자리수 지정 가능)
round(숫자,소수자리수)
-2 -1 0 1 2
③ ceil : 올림(무조건정수로)
④ floor : 내림(무조건정수로)
⑤ mod : 나머지구하기
mod(나눠지는숫자, 나눌숫자)
5) 날짜시간
①sysdate()
=> 실행순간의 시스템시간을 반환(실시간으로 시간을 확인하고 싶은 경우)
같은 트랜잭션에서 시간이 달라질 수 있음
②now()
=> 일관된 트랜잭션 처리 시간을 얻고 싶은 경우 사용
=> sql이 실행되기 시작한 시간을 반환
③curtime,curdate
④year, month, day ,hour,minute, second
=> 년,월,일,시,분,초를 각각 추출해서 리턴
⑤adddate
=> 날짜데이터에 더하기
adddate(날짜,숫자)
⑥subdate
=> 날짜데이터 빼기
⑦datediff
=> 두 날짜 사이의 간격을 구하기
datediff(날짜1, 날짜2)
--------
나중날짜
⑧dayofweek
=> 요일번호
1 : 일요일, 7:토요일
dayofweek(날짜)
⑨last_day
=> 지정한 날짜가 속한 달의 마지막날짜를 리턴
⑩quarter
=> 분기
9. 조인
10. 서브쿼리
11. DML
1) insert
=> 데이터삽입
[구문1]
insert into 테이블명 values(값1,값2...)
=> values다음에 값을 지정할때 컬럼 수에 맞게 값을 정의해야 한다.
=> auto_increment가 정의된 경우 null이나 0을 값으로 전달하면 된다.
=> insert하는 경우 초기 값이 없는 경우 보통 null을 정의
[구문2]
insert into 테이블명(컬럼1,컬럼2..) values(값1,값2..)
=> 테이블명 뒤에 정의한 컬럼에 대해서만 values에서 값을 정의한다.
[구문3]
=> 한 번에 여러 개의 레코드를 insert하기 위해서 사용
insert into 테이블명(컬럼1,컬럼2...) values
(값1,값2..), (값1, 값2), (값1,값2)......
insert into mymember(pass,name,addr,point) values
('1234','지민','부산',1000), ('1234','뷔','서울',1000),('1234','남준','서울',1000);
[구문4]
=>서브쿼리로 insert (다른 테이블의 레코드를 조회해서 저장하기)
insert into 테이블(컬럼명1,2,....)
select 문
2) update
=> 데이터수정
[구문]
update 테이블명
set 변경할컬럼1 = 변경할값1, ......
where 조건
=> where절의 조건은 select문과 동일한 방법과 동일한 연산자 모두 사용가능
=> set절에 변경할 컬럼과 값을 정의, where절을 정의하지 않으면 모든 레코드의 값이 변경
[미션]
emp테이블
12. DDL
[테이블]
1) 테이블생성
create table 테이블명(
컬럼명1 데이터타입 auto_increment primary key ,
컬럼명2 데이터타입,
.....
)
[미션]
deptno컬럼과 mgr이 모두 null인 레코드의 deptno만 20번으로 수정될 수 있도록 작업
[미션]
mgr이 null인 emp의 레코드의 mgr을 SMITH의 mgr로 수정하세요. 단, KING은 제외하기
update emp
set mgr =(select mgr
from (select *
from emp) e
where e.ename='SMITH')
where mgr is null and ename <> 'KING';
2) 테이블수정
=>테이블의 구조를 변경
=> alter table명령문
=> 컬럼을 추가, 컬럼을 삭제, 컬럼의 이름변경, 타입변경, 제약조건을 컬럼에 추가 및 변경....
① 컬럼추가
alter table 테이블명
add column 컬럼명 데이터타입
alter table mymember
add column email char(15);
② 컬럼의 데이터타입 수정
alter table 테이블명
modify column 컬럼명 데이터타입(사이즈)
alter table mymember
modify column email varchar(20);
③ 컬럼명변경
alter table 테이블명
change column 원본컬럼명 변경하고싶은컬럼명 데이터타입
alter table mymember
change column email myemail varchar(30);
④ 컬럼삭제
alter table 테이블명
drop column 삭제할컬럼명
alter table mymember
drop column myemail;
⑤ 테이블명변경
alter table 원본테이블명 rename to 변경할테이블명
alter table mymember rename to member1;
4) 테이블삭제
drop table 테이블명
13. auto_increment
=> 자동으로 증가하는 숫자를 저장하고 싶은 경우 사용
=> 1부터 2, 3, 4.....순차적으로 숫자가 자동으로 insert되도록 할 수 있다.
=> auto_increment를 정의한 경우 타입을 정수형으로만 정의해야 한다.
int, bigingt,......
14. 제약조건
=> 데이터를 입력할때 조건을 주고 조건에 만족하지 않으면 데이터 삽입, 수정, 삭제가 불가능하도록 작업
=> 데이터의 무결성
create table mymember(
id int auto_increment primary key, 컬럼레벨에서 제약조건 정의
pass varchar(10),
name varchar(20),
addr varchar(20),
point int);
[구문1 - 테이블이 작성된 후 제약조건을 추가]
alter table 테이블명
add constraint 제약조건명 제약조건(제약조건을 설정할 컬럼)
1) primary key
- not null과 unique모두 해당
- 컬럼레벨로 정의
create table 테이블명(
컬럼명 데이터타입 primary key,
...)
- 테이블을 생성한 후 제약조건을 정의 이미 정의한 테이블을 수정하는 것이므로 alter table명령문이용
alter table 테이블명
add constraint 제약조건종류_테이블명_컬럼명 제약조건(제약조건을 설정할 컬럼)
alter table mydept
add constraint pk_dept_no primary key(deptno);
2) not null
=> null을 삽입할 수 없는 제약조건
=> 테이블을 정의하면서 컬럼을 추가할때 컬럼레벨로 정의
=> 기존데이터가 있는 경우 컬럼에 null이 없어야한다.
=> add constraint로는 추가할 수 없다. alter table의 modify를 이용해서 작업할 수 있다.
alter table member1
modify column name varchar(20) not null;
3) check
=> 컬럼에 insert되는 데이터의 유효성을 검증하기 위해서 사용
=> 잘못된 값이 입력되지 않도록 설정
=> where절에서 조건 주는 방법과 동일한 방법
alter table 테이블명
add constraint check(컬럼값에 대한 조건)
① 숫자가 저장되는 컬럼
=> 특정 값보다 크거나 작거나 어떤 사이 값을 명시해야 하므로 >, >=, <, <=, =등과 같은 연산자와 사용
② 문자가 저장되는 컬럼
alter table member1
add constraint check(addr in ('서울','광주','부산','대구'));
alter table member1
add constraint check(point>=1000 and point<=5000);
①②③④⑤⑥⑦⑧⑨⑩
4) unique
=> 중복된 값을 허용하지 않는 제약조건
alter table 테이블명
add constraint unique(unique제약조건을 설정할 컬럼)
alter table mydept
add constraint unique(dept_name);
5) foreign key
=> 외래키 제약조건
alter table 테이블명
add constraint [제약조건명] foreign key(fk로 정의할 컬럼명) references 기본키테이블(기본키컬럼)
------------------------------
외래키에서 참조해서 사용할
기본키테이블과 컬럼
=> 기본키테이블이 미리 정의되어 있어야 한다.
=> insert하는 경우 references할 테이블에 기본키가 없으면 추가할 수 없다.
=> 이미 데이터가 insert되어 있는 경우 제약조건을 삽입한다면 무결성에 위배되지 않는 데이터만 있어야 제약조건을
설정할 수 있다.
=> pk와 fk는 데이터 타입이 동일해야 한다.
alter table member1
add constraint fk_member1_deptno foreign key(deptno) references mydept(deptno);
6) 제약조건의 확인
=> 제약조건을 정의하는 것도 mysql내부에 제약조건을 저장하는 테이블에 저장된다.
=> 제약조건이 저장되는 테이블은 mysql에서 만들어지는 모든 메타데이터가 저장되는 데이터베이스에 저장된다.
=> information_schema데이터베이스 내부에 TABLE_CONSTRAINTS테이블에 저장
select * from TABLE_CONSTRAINTS
where TABLE_NAME="mydept";
댓글
댓글 쓰기