데이터베이스 명령어 실습 (feat. Mariadb, HeidiSQL)

 

DBMS (DataBase Management System)의 구조를 이해할 필요가 있다.

DBMS는 DB, 즉 데이터베이스로 구성된다. 

DB -> TABLE -> RECORD > FIELD

순으로 자료가 저장된다. 그리고 각 단계 맞는 특정한 언어와 명령어가 있다. 

 

기본화면이다. MariaDB를 사용하고 있고 HeidiSQL을 사용해서 보기쉬운 UI로 보여준다. 

기본적으로 데이터베이스를 추가/삭제하는 명령어는 

CREATE DATABASE DB_NAME / DROP DATABASE DB_NAME

다른 데이터베이스를 선택하고 싶으면 USE DB_NAME으로 선택한다.

 

데이터베이스를 생성/삭제 할 경우 왼편에 새로고침을 누르면 생성/삭제가 된걸 알수있다. 

아직 데이터베이스 안에 테이블을 생성하지 않았기 때문에 practice_DB는 자료가 들어있지 않은 텅빈 db이다. 

테이블을 생성해보자

 

CREATE TABLE TABLE_NAME{ }; 

() 괄호안에 생성하고자 하는 테이블의 RECORD 정보가 들어가는데 특정 형식으로 나누어진다. 

예시:

create table userTb1
( userID CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 ID (PK)
  name varchar(10) NOT NULL, -- 이름
  birthYear INT NOT NULL, -- 출생년도
  addr char(2) not null, -- 지역
  mobile1 char(3), -- 폰번호 앞자리 3 자리
  mobile2 char(8),
  height SMALLINT, -- 키
  mDate DATE -- 가입일
);

 

첫줄 예시를 보면 빨간색 userID는 필드명이다. 테이블에서 해당 record의 이름이라고 생각하면 된다. 

두번째 주황색은 데이터의 타입이다. char(8)은 문자가 들어가고 괄호안에 숫자는 사이즈를 나타낸다. 

세번째 노란색 NOT NULL은 선택사항이다. 만약 NOT NULL이라고 지정하면 해당 필드를 비우지 않겠다는 말이다.

마지막 네번째 PRIMARY KEY 또한 선택사항이다. 이 키는 밑에서 조금더 설명하겠다. 

 

create TABLE buyTbl(

num int auto_increment not null primary key, -- 순번 pk

userID CHAR(8) NOT NULL, -- ID(FK)

prodName CHAR(6) NOT NULL, -- 물품명

groupName char(4), -- 분류

price int not null, -- 단가

amount smallint NOT NULL, -- 수량

foreign key (userID) references userTbl(userID)

);

buyTbl이라는 테이블 생성 코드이다. 

마지막 라인에 보면 primary key가 아닌 foreign key로 userTbl에 userID와 연결됬다. 

 

** varchar은 char과 비슷한 타입이지만 문자 뿐만이 아닌 숫자 또한 받을수 있으며 메모리가 더 크다.

   auto_increment는 해당 행의 인덱스를 자동으로 올려주는 기능이다.**

데이터베이스 키?

• 기본 키 (Primary Key) #Primary Key는 유일한 값이기 때문에 중복된 값을 가질 수 없다.
• Primary Key는 공백을 가질 수 없습니다.
• Data-Table에 있는 유일하게 구분되는 Data-Key를 기본 키(Primary Key)라 부른다.
• 기본키는 절대 null(아무 값이 없는 상태) 값을 가질 수 없다. 
• 하나 이상의 컬럼이 그룹화 되어 기본키로도 쓰일 수 있다. 

• 외래 키 (Foreign Key) #Foreign Key는 다른 Primary Key를 참조하는 속성 또는 속성들의 집합을 의미한다.
• Foreign Key는 참조 관계의 기본 키와 같은 속성을 가진다.
• 한 Table과 참조되는 다른 Table 간의 연결되는 Primary Key Column을 Foreign Key라 합니다.
• FK가 정의된 테이블은 자식 테이블이다.
• 참조되는 테이블은 부모 테이블이다. 
• 부모 테이블들은 미리 생성되어 있어야 한다. 
• 부모 테이블은 FK로 인해 삭제가 불가능하다. 

한마디로 primary key는 다른 테이블과 참조되기 위해 쓰는 기본적인 키이고 foreign key는 primary key를 참조하여 연결시킨다. 

 

테이블을 생성하고 잘 만들어졌는지 테이블 select 명령어를 사용해 테이블 레코즈를 확인하려고 하자 권한이 없다는 에러가 발생한다. 데이터 베이스 및 테이블은 보호를 받아야 하므로 접근 제어와 권한 제어를 적용하여야 한다. 따라서 권한 부여에 대하여 학습하여 사용자와 관리자에 대하여 ID와 비밀번호 그리고 접근하려는 IP Address를 적용하여 데이터베이스를 안전하게 관리하여야한다. 

 

예시 1: 아이디(admin), 비밀번호(1234)의 사용자가 특정 IP주소(10.20.30.40)로 myDB에 있는 myTB를 모든 권한을 갖고 접근하도록 하려면 -> GRANT ALL PRIVILEGES ON myDB.myTB to 'admin'@'10.20.30.40' IDENTIFIED BY '1234';

 

예시2: 아이디(admin), 비밀번호(1234)의 사용자가 특정 IP주소(10,20,30,40)로 myDB에 있는 myTB을 select 권한을 갖고 접근하도록 하려면 -> GRABT SELECT_PRIV PRIVILEGES ON myDB.myTB to 'admin'@'10.20.30.40' IDENTIFIED BY '1234';

 

예시3: 아이디(admin), 비밀번호(1234)의 사용자가 특정 IP주소(10.20.30.40)로 myDB에 있는 모든 테이블에 모든 권한을 갖고 접근하도록 하려면 -> GRANT ALL PRIVILEGES ON myDB.* to 'admin'@'10.20.30.40' IDENTIFIED BY '1234';

 

예시4: 아이디(admin), 비밀번호(1234)의 사용자가 어디서든지 myDB에 있는 모든 테이블에 모든 권한을 갖고 접근하도록 하려면 -> GRANT ALL PRIVILEGES ON myDB.* to 'admin'@'%' IDENTIFIED BY '1234'

 

But...

HeidiSQL에서 직접 권한을 바꾸려고 하니 'ACCESS DENIED' 에러가 뜨면서 권한 변경을 할수 없게 만들었다. 

그래서 putty에 접속하여 터미널에서 권한을 바꾸어주었다. 

 

명령어
# mysql -uroot -p  
MariaDB > use mysql
MariaDB > GRANT ALL PRIVILEGES ON DB_NAME.TABLE_NAME TO '유저_이름'@'IP_주소' IDENTIFIED BY '비밀번호';
MariaDB > FLUSH PRIVILEGES; --권한을 부여/변경 했다면 이 명령어를 입력해야 적용된다. 

---

생성된 테이블에 데이터 삽입하는 방법

방법 1: INSERT INTO table_name(fields.....) VALUES(values .....)
방법 2: INSERT INTO table_name VALUES(values...)
방법 3: INSERT INTO table_name VALUES (values.....), (values......), (values.......)

방법 1은 데이터를 추가하고 싶은 필드가 정해져있을때 쓰는 방식이고

방식 2는 모든 필드에 해당 데이터 값을 입력하고 싶을때 사용하고

방식 3은 모든 필드에 2개 이상의 열을 입력하고 싶을때 사용한다. 

 

주의사항

• 컬럼 타입이 char, date, time의 경우는 (' ') 홀따옴표로 묶는다.
• 방법 3에서와 같이 2개 이상의 열을 입력하는 경우 'VALUES'는 한번만 사용하고 쉼표를 사용해서 계속 이어간다.