[MySQL/기본] DDL, DML DCL

SQL쿼리 공부 하기 전 알아야 할 것들

• SQL 명령문은 대소문자 가리지 않습니다
• 문자열 은 ' ~ ' (홀따옴표)로 감쌈,  문자열 내의 내용은 대소문자 가림
• 여러줄에 걸쳐 명령문 입력 가능
• 명령문이 끝나면 반드시   ;   <- 입력
• COMMIT; 을 실행하기 전까지는 임시저장만 되는 것임,  COMMIT; 을 해야만 데이터베이스의 내용이 업데이트 됨.  (ORACLE 의 경우) 
• 하지만 MySQL 의 경우 기본적으로 auto-commit 한다

MySQL 서버 실행 : 맥북 터미널에서 (윈도우는 메뉴 ->  서비스 -> mysql 을 찾아서 서비스를 시작 )

mysql.server start

•  

실행 성공 문구

MySQL 데이터베이스 접속

mysql -u [사용자아이디] -p [데이터베이스이름]

 

해당 명령어를 통해 비밀번호를 입력하면 해당 화면

현재 접속한 사용자 확인

SELECT user();

해당 화면 출력

생성되어있는 사용자 정보

SELECT user, host FROM mysql.user;

 

해당 화면 출력

각종 데이터베이스 적용 명령어 

-- 데이터베이스 생성
CREATE DATABASE [데이터베이스명] ;

-- 유니코드 사용 데이터베이스 생성
CREATE DATABASE [데이터베이스명]  DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;  

-- 데이터베이스 삭제
-- if exists 는 해도 되고 안해도 되지만 있을 경우에 삭제하라는 명령어
DROP DATABASE [IF EXISTS] [데이터베이스명] ;

-- 데이터베이스 조회
SHOW DATABASES;

-- 사용자 생성
CREATE USER '[만들고자 하는 사용자 이름]' IDENTIFIED BY '[암호]';
CREATE USER '[만들고자 하는 사용자 이름]'@'[호스트]' IDENTIFIED BY '[암호]';

-- 사용자 삭제
DROP USER [사용자이름];
DROP USER [사용자이름]@[호스트];


-- 유저 비밀번호 변경
ALTER USER [존재하는 사용자 이름] IDENTIFIED BY '[변경하고자 하는 비밀번호]'

-- 권한 설정
GRANT ALL PRIVILEGES ON [디비명].[테이블명] TO '[사용자]'@'[접속지 주소]' (with grant option);
-- 사용자에게 디비에 있는 모든 권한을 어디에서든 접속해서 볼 수 있도록 설정(연습용 추천)
GRANT ALL PRIVILEGES ON [디비명].* TO '사용자'@'%';
-- grant 명령어 후에는 flush 를 해주어야 실제 적용이 됨
FLUSH PRIVILEGES;

--권한 확인
SHOW GRANTS FOR '[사용자]'@'[호스트]';


--권한 삭제
REVOKE ALL ON [DB이름].[테이블이름] FROM '사용자'@'접속위치';

---

 

DDL : Data Define Language (데이터 정의어)

• DB를 구축하거아 수정할 목적으로 사용하는 언어
• CREATE, ALTER DROP 이 있음

 

• CREATE : 테이블, 뷰, 인덱스 등을 생성할 때 사용하는 명령어
• 아래는 cretea table의 예제

CREATE TABLE  [tablename] (
  [column_name1] INT PRIMARY KEY AUTO_INCREMENT,
  [column_name2] VARCHAR(15) NOT NULL,
  [column_name3] INT
);

------------------------------------------------------

-- use와 같이 이미 SQL에 있는 명령어로 테이블 이름을 만들고자 할 때에는 백틱문자 `를 사용한다.
create table items (
	id int(10) not null auto_increment,
	content varchar(200),
	created_on date,
	due_date date,
	`use` int(1) not null default 1,
	primary key(id)
)

• PRIMARY KEY : 고유한 키값을 같는 필드
• AUTO_INCREMENT : 레코드가 생성될때마다 자동증가
• NOT NULL : Null 값 허용 안함

 

• ALTER  : TABLE에 대한 정의를 변경하는데 사용되는 명령어
• 아래는 ALTER TABLE에 대한 예제

-- phonebook table을 예제로 구성

-- Modify Column 명령어로 테이블을 수정
ALTER TABLE phonebook
MODIFY COLUMN name varchar(50);

-- ADD Column 명령어로 테이블 추가
ALTER TABLE phonebook
ADD COLUMN age INT(3) DEFAULT 1 CHECK(age >= 0);

-- RENAME: 테이블 이름 변경
ALTER TABLE phonebook
RENAME phonedict;

-- DROP 테이블 칼럼 삭제
ALTER TABLE phonebook
DROP email;

-- CHANGE COLUMN 컬럼 이름 변경
ALTER TABLE phonebook
CHANGE COLUMN phonenum number varchar(20) NOT NULL;

 

 

• DROP : 테이블, 뷰, 인덱스 등을 삭제할 때 사용하는 명령어

-- phonebook table로 예제 구성

-- 존재한다면 테이블 삭제
DROP TABLE [IF EXISTS] [테이블명] [CASCADE | RESTRICT]

• Cascade : 제거할 요소를 참조하는 다른 모든 객체를 함께 제거함
• Restrict : 다른 개체가 제거할 요소를 참조중 일때는 제거를 취소함

---

 

 

 

DML : Data Manipulate Language (데이터 조작어)

• 사용자가 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
• SELECT, INSERT, DELETE, UPDATE 가 있음 [DML 구문은 숙지하고 숙련되어있어야 함!]

 

• INSERT : 삽입문으로 레코드를 생성하는 명령어
• 예제

-- INSERT INTO 구문, VAALUES 뒤에 괄호 안에 테이블에 정의된 컬럼의 순서와 같은 순서로 입력해야함
INSERT INTO [tablename] VALUES(값1, 값2, ...);
-- 혹은 컬럼을 지정해주고 값을 넣어야함
INSERT INTO [tablename] (col1, col2, ...) VALUES(값1, 값2, ...);

-- 테이블을 선언할 때, NOT NULL 등의 제약이 있는데 주의하지않고 INSERT 구문에 NULL 값으로 추가하면 구문에러

 

• UPDATE : 갱신문으로 레코드를 수정하는 명령어
• 예제

 

-- UPDATE [테이블이름] SET [컬럼이름] = [데이터], ... WHERE [조건식]; 이런 형태
-- 다양한 조건식을 사용할 수 있다.
UPDATE phonebook SET name = 'jesse' WHERE id = 4;
UPDATE phonebook SET email = 'jesse@mail.com' WHERE id = 4;
UPDATE phonebook SET email='jake@mail.com', name = '김갑환' WHERE id = 3;


-- null 여부 비교 가능
UPDATE phonebook SET email='mail@default' WHERE email IS NULL;

 

• DELETE : 삭제문으로 레코드를 삭제하는 명령어
• 예제

-- DELETE FROM [테이블 이름] WHERE [조건식]; 형태
-- 주의할 점은 where 조건식이 없을 결루 테이블에 있는 모든 튜플들이 삭제됨

DELETE FROM phonebook WHERE id = 3;

 

• SELECT문은 다음에 포스팅 때 정리할 예정

---

DCL : Data Control Language (데이터 제어어)

• 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용되는 언어
• COMMIT, ROLLBACK, GRANT, REVOKE 등이 있다 [COMMIT. ROLLBACK 은 TCL (Transaction Control Language) 로 따로 분류되기도 한다. ** 본인 생각 매우 중요 **]
  • Transaction의 특성 => ACID
    • Atomicity (원자성) : 모든 작업이 모두 반영되거나 모두 반영되지 않아야함
    • Consistency (일관선) : 미리 정해진 규칙에서만 수정이 가능한 특성
    • Isolation (격리수준, 고립성) : A, B 두개의 트랜잭션이 동시에 일어나고 있을떄, 어느 하나의 트랜잭션이 실행중 일 때에는 다른 트랜잭션이 끼어들 수 없다는 특성
    • Durability (영구성) : 한번 커밋된 트랜잭션의 내용은 영원히 적용되어야 한다는 특성
• COMMIT : 지금까지 적용시킨 것을 영원히 반영하는 명령어
• ROLLBACK : savepoint 를 지정해놓고 작업 하다가 그 savepoint 였던 상태로 다시 되돌릴 수 있는 명령어
• GRANT : 사용자에세 디비, 혹은 테이블에 대한 접근 권한(DML,DDL)을 주는 명령어
• REVOKE : 사용자에게 주었던 권한을 없애는 명령어