9일차 [SQL (DDL,DCL)]

1. SQL 명령어 종류

SQL	명령어	설명
데이터 정의어 (DDL : Data Definition Language)	CREATE ALTER DROP RENAME TRUNCATE	테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 (생성, 변경, 삭제, 이름변경) 데이터 구조와 관련된 명령어들을 말함.
데이터 제어어 (DCL : Data Control Language)	GRANT REVOKE	데이터 보안(계정),무결성,회복 등을 제어하는 명령 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어들을 말함.
데이터 조작어 (DML :  Data Manipulation Language	SELECT	데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE 라고도 함
	INSERT UPDATE DELETE	데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류(데이터 삽입, 수정, 삭제)의 명령어들을 말함.
트랜잭션 제어어 (TCL : Transaction Control Language)	COMMIT ROLLBACK SAVEPOINT	논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말함.

 

2. DDL (테이블 정의,수정)

/*
 * Table
 * MySQL> databse> table > data
 * 
 */

# DDL
show databases; # semi colon으로 block의 단위가 나뉜다.
create
database
dbname;

use dbnaa; # SQL Error [1049] [42000]: Unknown database 'dbnaa'

# 어떤 db를 사용할 것인지 명시해주고 테이블을 만들어야 함.
use dbname; # <== DB 사용 명시
create table student (
	student_id int unsigned auto_increment,
	student_name varchar(10) not null,
	student_address varchar(50) null,
	create_dt timestamp default now(),
	modify_dt timestamp default now(),
	primary key(student_id)
);

create table professor (
	professor_id int unsigned auto_increment,
	professor_name varchar(10) not null,
	create_dt timestamp default now(),
	modify_dt timestamp default now(),
	primary key(professor_id)
);

create table subject(
	subject_cd varchar(10), # cd: code
	subject_name varchar(10) unique, # unique: 유일한, 식별자 역할
	subject_desc text,# desc: description, text: 긴 문단, varchar: 문장
	prof_id int unsigned not null,
	create_dt timestamp default now(),
	modify_dt timestamp default now(),
	primary key(subject_cd), # 기본키의 특징: not null & unique
	foreign key(prof_id) references 
	professor(professor_id) on update cascade );
	#subject table 내의 prof_id는 professor table 의 professor_id와 동일하다.

show databases;

# DDL =============================================아래 student table없다고 에러남..?
# 테이블 수정 (컬럼 생성, 수정, 삭제...)
use dbname;
alter table student add column
student_age int default 20;
alter table student modify column 
student_address varchar(100) not null;
alter table student drop column
student_age;

drop table if exists student;
drop table if exists professor;
# subject와 professor와의 관계는 다대일.
# subject table부터 삭제해야 professor table 삭제 가능
# SQL Error [3730] [HY000]: Cannot drop table 'professor' 
# referenced by a foreign key constraint 'subject_ibfk_1' on table 'subject'.
drop table if exists subject;
drop table if exists professor;

drop database if exists dbname;

# 강사님 코드. 다시 비교 분석해서 뭐가 다른 지 알아낼 것
/*
 * database
 * MySQL > database
 */
show databases;
create database dbname;

use dbname;

/*
 * Table 
 * MySQL > database > table > daba
 */
create table student (
    student_id int unsigned auto_increment,
    student_name varchar(10) not null,
    student_address varchar(50) null,
    create_dt timestamp default now(),
    modify_dt timestamp default now(),
    primary key(student_id)
);

create table professor (
    professor_id int unsigned auto_increment,
    professor_name varchar(10) not null,
    create_dt timestamp default now(),
    modify_dt timestamp default now(),
    primary key(professor_id)
);

create table subject (
    subject_cd varchar(10),
    subject_name varchar(10) unique,
    subject_desc text,
    professor_id int unsigned not null,
    create_dt timestamp default now(),
    modify_dt timestamp default now(),
    # 기본키특징: not null & unique 
    primary key(subject_cd),
    foreign key(professor_id) references 
    professor(professor_id) on update cascade 
);

# 테이블 수정(컴럼 생성, 컴럼 수정, 컴럼 삭제...) 
alter table student add column 
    student_age int default 20; 

alter table student modify column 
    student_address varchar(100) not null;

alter table student drop column 
    student_age;

drop table if exists student;
drop table if exists subject;
drop table if exists professor;
drop database if exists dbname;

ERD 보기: 만든 테이블 우클릭 > 다이어그램 보기/ 내가 손으로 그린 것과 같은지 확인하기 위한 용도

 

professor_id 서로 연결된 걸 볼 수 있다.

 

3. DCL (계정 조회,생성,삭제)

3-1. 계정 조회

# DML
use mysql; # mysql이라는 DB 사용할거야
show tables;

select * from user; 
# select: 테이블 데이터 조회하는 명령어
# *: all(모든) -> 모든 컬럼
# user 컬럼에서 user 데이터가 2개 나오는데, host가 %와 localhost로 다름, 
# unique하게 정의할 수 있는 방법이 있음


desc user;

같은 root user라도 host에 따라 unique하게 정의됨

• DB connection할 때 localhost (ip)로 mysql 연결시 로컬로 연결하겠다는 뜻
• localhost: 외부 접속 불가
• %는 전세계 어디든 접속 가능하다는 말.
• 현재 컴퓨터에서 mysql서버 (논리적 컴퓨터)에 접속

3-2. container 내 sql 서버 접속하기 

• mysql container 클릭 > 점 세개 > open in container > Exec (terminal)
• `mysql -u root -p` > root1234 > `SHOW DATABASES;` > exit

 

3-3. 계정 생성, 조회, 삭제

(root 계정에서 했음)

1) 로컬호스트에서 생성

id:test / ip: localhost 생성

DB접속 테스트시 denied/ 외부(dbeaver) 에선 접속 불가, localhost만 접속 가능해서 그럼.

 

내부 서버 mysql(localhost)에서 접속

2) 외부 접속 가능하게 계정 생성

%로 ip 설정

 

3) 비밀번호 변경 (`set password for 'id'@'ip' = 'new_pw'`)

다시 디비 접속하려니까 비번 변경되어서 오류난 것을 확인

 

4) 사용자 삭제

 

3-4. 계정 권한 주기 (`grant all previleges on DB_name.table_name to 'id'@'ip'`)

root 계정에서 권한을 test1에게 넘기는 명령어 주면

 

test1 계정에서 권한 받아서 조회가 가능함.