DBMS기초(3)-Crud와 연산자

*이 글을 읽기전에 작성자 개인의견이 있으니, 다른 블로그와 교차로 읽는것을 권장합니다.*

데이터베이스를 사용하기 위해선 데이터베이스를 선택해야합니다.

1. 데이터베이스

1-1. 데이터베이스와 테이블

데이터베이스는 데이터를 저장하고 관리하는 시스템이며, 테이블은 데이터를 구조화하고 저장하는 방법 중 하나입니다. 데이터베이스에는 여러 테이블이 포함될 수 있으며, 각 테이블은 서로 다른 유형의 데이터를 저장하는 역할을 합니다.

• use database_이름;
  • 데이터베이스를 선택하는 코드이며, 데이터베이스를 선택하지 않는다면 테이블 이용이 불가능합니다.

# 데이터베이스 선택(사용하기)
use kdt;use kdt;

• desc table_이름; 
  • table을 확인하며, desc는 describe의 준말로, 테이블의 시트를 체크합니다.

# 테이블 확인(desc는 describe준말)
desc member;

사용시 이렇게 table 확인

•  drop table table_이름;
  • 테이블을 삭제합니다.

# 테이블 삭제
drop table member;

위 사진에서 kdt는 데이터베이스, Tables는 테이블

1-2. alter table문

SQL에서 필드(Field)는 레코드(행)에서 각각의 데이터 항목을 의미합니다. 다른 말로 열(column)이라고도 합니다. 데이터베이스 스키마에서 필드는 각 테이블의 구조를 정의하는 중요한 요소 중 하나입니다. alter table문은 필드를 추가, 수정, 삭제하는데 사용하는 문법입니다. alter table문은 열안에 있는 value값을 수정하는 것이 아닌, table의 열 헤더값들을 수정합니다.

alter table table_이름 add column_이름 datatype;

• 필드(열)을 추가합니다. 저장할게 더 생길 경우, 새로 추가하는 필드의 제약조건에  not null을 줄 수 없습니다. (새로 생기는 열값을 채워야 하기 때문)

# 필드 추가: 저장할게 더 생길 경우, 새로 추가하는 필드의 제약조건에 not null을 줄 수 없다
alter table member add mbti varchar(10);

alter table table_이름 modify column_이름 datatype;

• 필드(열)을 수정합니다.

# 필드 수정
alter table member modify column mbti varchar(20);

alter table table_이름 drop column_이름;

• 필드(열)을 삭제합니다.

# 필드 삭제
alter table member drop mbti;

2. CRUD

CRUD는 Create Read Update Delete 4글자의 앞머리만 따온것으로, 데이터 조작기능을 나타내는 약어입니다. 

• Create(생성): 데이터베이스에서 새로운 데이터를 생성합니다.
• Read(읽기): 데이터베이스에서 특정 조건에 맞는 데이터를 검색하거나 가져옵니다.
• Update(갱신): 데이터베이스에 있는 기존 데이터를 수정합니다. 특정 레코드 값을 수정합니다.
• Delete(삭제): 데이터베이스에서 데이터를 삭제합니다. 특정 레코드나 행을 삭제합니다.

2-1. Create(생성)

create table table_이름() 을 통해서 새로운 table을 만듭니다.

create table words(
	eng varchar(50) primary key, #겹치면 안되는 열, 1열
    kor varchar(50) not null, #2열
    lev int default 1 #3열
)

위 코드로 실행된 table words는 열자리에 eng, kor, lev이 생성됩니다. eng은 varchar(50)자료형 타입의 primary key로 제약조건을 설정하였고, kor은 null값을 허용하지 않고, lev은 정수형 자료형타입에 default값으로 1을 설정합니다.

 

생성확인

insert into table_이름 values (); 을 이용해 value값을 넣습니다.

insert into words values ('apple','사과',1);
-- insert into words values ('apple','사과',1); 중복데이터 에러
-- insert into words values('banana','바나나'); 컬럼 갯수가 일치하지 않은 에러
insert into words values('banana','바나나',null); #null이 들어감
insert into words values('orange',null,null); #뜻에 null을 넣을 수 없음

insert into words (eng, kor, lev) values ('orange', '오렌지',1);
insert into words (eng, kor) values ('melon', '메론');	#lev열에 default값 1적용
insert into words (lev, eng, kor) values ('2', 'avocado', '아보카도'); #자료 순서도 primary key글자순
-- insert into words (eng) values ('cherry'); 뜻에 null을 넣을 수 없음

자료의 value값들을 추가해줍니다. 'apple','사과',1 로 이루어진 행, 'banana,'바나나',null로 이루어진 행, 'orange',null,null로 이루어진 행을 추가합니다. 밑의 코드들은 열자료밑의 value값에 default=1로 설정한 value값을 넣지 않는 코드가 있습니다. insert into table_이름 (columns_이름) vlaues (values값)에서 columns_이름을 설정할 때, 순서는 상관없이 적을 수 있습니다.

확인

데이터 중복했을 때 나는 오류,  primary key는 중복할 수 없다.

칼럼갯수가 일치하지 않는 에러

해당 열에 null을 넣을 수 없음

kor열의 default값을 설정하지 않아 default값을 출력할 수 없는 에러

데이터 테이플 지우기, 테이블의 모든 행을 삭제하는 기능

sql_safe_updates=0:  sql_safe_updates모드가 활성화된 상태에서 where절없이 사용한다면 에러가 발생합니다. 이때 sql_safe_updates=0를 사용하여 위 보안기능 비활성화됩니다. 따라서, where절없이 update문, delete문을 사용할 수 있습니다. (다만, sql_safe_updates를 비활성화한다면 데이터 전체를 실수로 바꿀 수 있으니 주의해야 합니다.)

• 일시적인 safe모드 해제: set sql_safe_updates=0;
• 영구적인 safe모드 해제: Edit -> Preferences -> SQL Editor-> Safe Updates 체크 해제->workbench 재시작

 

데이터의 안전을 위해서 모든 데이터 반영시키는 작업을 툴에서 막음 safe모드 활성화된 상태

safe updates 체크 해제

workbench 재시작, safe모드 영구해제

set sql_safe_updates=0;

safe모드 일시해제

2-2. Update(갱신)

update문을 사용해 value값을 수정합니다.

• update table_이름 set column_이름1, column_이름2, ... ;
• update table_이름 set column_이름1, column_이름2, ...  where 조건절;

update words set lev=1;
# 모든 유저에게 50 포인트를 더해주기
update member set point=point+50;

update words set lev=2 where eng='avocado'; #eng열 lev열 2로 바꿈
update words set kor='어륀지', lev=2 where eng='orange'; #eng열의 kor열, lev열 바꿈
# member 테이블의 아이디가 'apple'인 회원에 대해 우편번호는 '12345', 주소1은 '서울시 서초구',
# 주소2는 '양재동', 주소3은 'XX아파트 101동'으로 수정하기
update member set zipcode='12345', address1='서울시 서초구', address2='양재동', address3='XX아파트 101동' where userid='apple';

위 코드로 실행된 words table의 value값은 수정됩니다. lev=1로 수정, member table의 point열의 value값은 +50 수정, where 조건절로 eng='avocado'일때 kor='어륀지', lev=2로 수정합니다. 마지막으로 member table은 where조건절로 userid열의 'apple' value값인 행은 zipcode='12345', address1='서울시 서초구', address2='양재동, address3='XX아파트 101동'으로 수정됩니다.

테이블에 직접적으로 value값 수정하기

apply 클릭

적용

적용 완료

2-3. Delete(삭제)

Delete문을 사용해 데이터를 삭제합니다.

• delete from table_이름:
• delete from table_이름 where 조건절;

delete from words; #전체 테이블 삭제
-- delete from member where userid='avocad'; 데이터가 없어서 삭제되지 않음
delete from member where userid='avocado';

delete from words로 테이블 전체를 삭제합니다. member table의 where조건절로 userid='avocado'행을 삭제합니다.

2-4. Read(읽기)

데이터를 검색해서 특정 열의 value값만 출력합니다. (특정 행렬만 출력)

• select column_이름1, column_이름2, ... from table_이름;
• select column_이름1, column_이름2, ... from table_이름 where 조건절;

insert into words values ('apple','사과',1);
insert into words values('banana','바나나',null); 
insert into words (eng, kor, lev) values ('orange', '오렌지',1);
insert into words (eng, kor) values ('melon', '메론');	
insert into words (lev, eng, kor) values ('2', 'avocado', '아보카도');

insert문으로 행 추가

select eng, kor from words;
select eng from words;
select kor, eng from words;
select 100;
select 100 + 50;
select 100 + 50 as '덧셈'; # as 사용
select 100 + 50 as 덧셈;
select 100 + 50 덧셈;
select 100 + 50 '덧셈 연산한 것'; #따옴표를 사용하는 이유는 띄어쓰기가 있을 수 있기 때문
select eng as '영단어', kor as '뜻', lev as '수준' from words;

eng, kor열만 출력, eng열만 출력, kor,eng순으로 열 출력, 100출력, 100+50=150출력, 150을 '덧셈'으로 출력합니다. as로 출력하려는 열의 이름을 별명으로 바꾸어 출력할 수 있습니다. as 별명에서 별명에 따옴표를 쓰는 이유는 띄어쓰기가 있는 경우도 별명으로 쓰기 때문입니다. eng열을 '영단어', kor열을 '뜻', lev열을 '수준'이란 이름의 열로 바꾸어 출력합니다.

select * from words; #모든컬럼 가져오기
select eng, kor, lev from words;

select null; # 데이터가 없음, insert가 되지 않은 것
select ''; # 해당 셀에 ''데이터가 삽입된 것
select 100+null; #결과: null, null을 더하면 결과값이 null이 나온다
select 100 + ''; # 결과: 100, 연산할 수 있음

select *from table_이름을 사용하면 전체 테이블을 가져옵니다. select null을 사용하면, null열의 null값을 가져와 데이터가 없습니다. select 100+null을 하면 100+null열의 null값을 가져옵니다. 정수형과 null자료형을 더하면 null의 결과값이 출력됩니다. 정수형과 ' '를 한다면 100이 나와, 따옴표는 정수형과 같이 연산할 수 있습니다.

3. 연산자

SQL 연산자, 집계함수라고도 함
     산술 연산자: +, -, *, /, mod(나머지), div(몫)
     비교 연산자: =(같음), <, >, >=, <>(다름)
     대입 연산자: =
     논리 연산자: and, or, not, xor
     기타 연산자
is: 양쪽의 피연산자가 모두 같은 true, 아니면 false
        between A and B: 값이 A보다는 크거나 같고, B보다는 작거나 같으면 true, 아니면 false
        in: 매개변수로 전달된 리스트에 값이 존재하면 true 아니면 false
        like: 패턴으로 문자열을 검색하여 값이 존재하면 true 아니면 false

다음으로 CRUD와 연산자 활용을 알아봅니다.

select userid, name from member where userid='apple';
select userid, name from member where gender='남자';
select userid, name, gender from member where gender='남자';
# point가 150 이상인 member의 아이디, 이름, 포인트를 출력
select userid, name, point from member where point>=150;

첫번째 줄은 member table에서 userid='apple'행의 userid와 name 열만 출력합니다. 두번째 줄은 member table에서 gender='남자'행의 userid와 name 열만 출력합니다. 세번째 줄은 member table에서 gender='남자'행의 userid, name,gender 열을 출력합니다. 네번째 줄은 from member table에서 where point>=150 일때, userid, name, point 열만 출력합니다.

# 로그인
select userid from member where userid='apple' and userpw='1111'; #로그인
select userid from member where userid='apple' and userpw='1234'; #로그인실패

where조건절에 해당할 경우 userid 열만 출력합니다.

# words 테이블에서 lev이 null인 데이터를 출력
select * from words where lev='null'; # X
select * from words where lev=null; # X
select * from words where lev is null;
select * from words where lev is not null;

table_이름을 *(asterisk)로 사용할 경우 전체 열을 가져옵니다.

where lev열의 value값이 'null'일 때, table전체를 출력합니다. 밑의 줄 모두 where조건절에 해당할 경우, table전체를 출력합니다.

# member 테이블에서 point가 0이상 150이하인 데이터를 출력
select * from member where point >= 0 and point <= 150;
select * from member where point between 0 and 150;
select * from member where name in ('김사과','반하나','오렌지');

point열의 value값이 0이상 150이하일 경우 table전체를 출력합니다. between연산자를 이용해 0~150조건절을 사용합니다.

select * from member where userid like 'a%'; #a로 시작하는 문자열
select * from member where userid like '%a'; #a로 끝나는 문자열
select * from member where userid like '%a%'; #a를 포함하는 문자열

where조건절을 사용할 때 like연산자는 패턴 매칭을 위해 사용되는 조건 연산자입니다. 와일드카드 문자인 '%'와 '_'을 이용해 패턴을 지정합니다. 위 코드에서는 %를 뒤에 붙여 시작하는 문자열, 앞에 붙여 끝나는 문자열, 앞뒤로 붙여 포함하는 용도로 사용했습니다. 보통 데이터자료에서 '%@gmail.com', '%@naver.com', '@daum.com'처럼 메일 구분으로 사용합니다.

4. 정렬하기

table이 오름차순, 내림차순으로 정렬하는 형태로 출력하기 위해 사용하는 연산자입니다.

• 오름차순 asc
• 내림차순 desc (describe가 아니라, descending입니다)
• select column_이름 from table_이름 order by column_이름 [asc or desc]
• select column_이름1, column_이름2, ... from table_이름 [where 조건절] order by column_이름 [asc or desc]

select * from member order by userid asc; #아이디로 오름차순
select * from member order by userid; #아이디로 오름차순
select * from member order by userid desc; #아이디로 내림차순

from member table에서 userid행의 value값들을 오름차순 정렬해서 select합니다. asc는 default값이니 안써도 asc로 나옵니다. desc는 내림차순으로 출력

# member 테이블의 회원을 포인트순으로 내림차순하고, 포인트가 같다면 userid로 오름차순
select * from member order by point desc, userid asc;

# member 테이블의 여성 회원을 포인트순으로 오름차순하고 포인트가 같다면 userid로 오름차순
select * from member where gender='여자'order by point, userid asc;

정렬조건이 2가지일 경우, 앞 조건부터 순차적으로 작성합니다. order by 정렬조건과 where조건을 같이 써야 할 경우 where조건절이 order by조건절보다 앞순으로 작성합니다.

5. limit

limit는 일부 갯수의 행만 출력하는 연산자입니다.

• select column_이름 .. from table_이름 limit [가져올 로우의 갯수]    
• select column_이름 .. from table_이름 limit [시작로우(인덱스), 가져올 로우의 갯수]

select * from member;
select * from member limit 3;
select * from member limit 2, 2;
# member 테이블의  회원을 포인트순으로 내림차순하고, 포인트가 같다면 userid로 오름차순한 뒤 top3를 출력
select * from member order by point desc, userid asc limit 3;

limit를 사용하면 윗행부터 순서대로 가져옵니다. 세번째줄은 limit 2,2로 2행부터 2개 행을 가져오므로 2,3행만 가져옵니다. 4번째줄 코드는 from meber table에서 point desc차순으로 userid는 asc차순으로 3개 행을 가져옵니다.