[11일차] MySQL, SQL명령어(DDL, DML)

1. MySQL 설치하고 실행하기

• AI가 알려주는 MySQL : https://wikidocs.net/226173

• 설치 링크 : https://dev.mysql.com/downloads/installer/

1. MySQL 설치 링크에서 용량이 더 큰 파일을 다운로드 및 설치 파일 실행

• 로그인하지 않고 "No thanks, just start my download"로 다운로드 가능

2. Choosing a Setup Type에서 Full 선택 후 Next -> Execute 선택 -> 설치 완료 -> Next

 

3. Type and Networking에서 Next

• 여기서 3306번 포트 사용 중일 경우,
• cmd에서 'netstat -ano | findstr 3306' 입력 후, 3306 포트 사용 중인 프로그램 확인
  (제일 오른쪽의 PID 번호 확인)
• cmd에서 'tasklist | findstr PID번호' 입력 후, 프로그램 확인
• 윈도우키 -> '서비스' 검색 -> MySQL로 시작하거나 MariaDB로 시작하는 서비스 찾아서 중지
• cmd에서 'netstat -ano | findstr 3306' 입력 후, 프로그램이 나타나지 않는지 확인 후 이어서 진행

4. Authentication Method에서 Use Legacy Authentication ... 선택

• 파이썬과 연동시켜 사용 시 에러 없게 사용하기 위함

5. Accounts and Roles에서 Root Pw를 1234로 설정 -> Next  3번 -> Execute -> Finish -> Next 

 

6. Connect To Server에서 root pw인 1234 입력 후 연결 확인 -> Next -> Execute -> Finish -> Next -> Finish

---

2. 데이터베이스 기초

1. 데이터(data)

• 정보를 나타내는 숫자, 문자, 기호의 집합
• 가공되지 않은 사실이나 값의 집합

2. 데이터베이스(database)

• 체계적으로 구성된 데이터의 집합
• 특정 목적을 위해 조직화되고 관리
• 정보를 효율적으로 저장, 검색, 업데이트, 관리할 수 있는 기술적인 도구와 구조 제공

3. 데이터베이스 관리 시스템(DBMS; DataBase Management System)

• 데이터베이스를 관리할 수 있는 기술적인 소프트웨어

4. 관계형 데이터베이스 시스템(Relational DBMS)

• 데이터를 테이블(표) 형식으로 관리하고, 각 테이블 간의 관계를 설정해서 데이터를 효율적으로 저장하고 검색하는 DBMS의 한 종류
• SQL(Structed Query Language) 사용 : RDB에서 데이터를 정의하고 조작하기 위한 언어
• 단점 : 테이블끼리 관계성을 맺고 무결성을 체크해야하므로 속도가 느림

5. 테이블(Table) : 데이터를 행과 열로 스키마에 따라 저장할 수 있는 구조

 

6. 데이터 타입

• 정수 : int, bigint
• 실수 :
  • float(소수점 여섯째자리까지)
  • double(그 이상)
  • decimal(소수점 개수 지정)
• 문자형 :
  • char(안쓰는 메모리까지 차지하니까 낭비가 됨)
  • varchar(메모리 효율적 사용 가능. 최대 65535byte)
  • text(그 이상)
  • binary(글자 이외의 데이터를 저장. 이미지, 음성, 영상 등)
  • varbinary(메모리 효율저거 사용 가능)
• 날짜형 : date, time, datatime, timestamp

7. 제약조건

• 데이터의 무결성을 지키기 위해 데이터를 입력받을 때 실행되는 검사 규칙
• not nul : null 값을 허용하지 않음
• unique : 중복값을 허용하지 않음. null 값은 허용
• default : null 값을 삽입할 때 기본이 되는 값을 설정
• primary key 
  1. null 값을 허용하지 않음
  2. 중복값을 허용하지 않음
  3. 인덱싱 설정
  4. 기본키로 설정. 참조(외래)키와 쌍으로 연결
• foreign key : 기본키와 쌍으로 연결
• auto_increment : 데이터를 직접 삽입하지 못함. 자동 증가되는 숫자를 삽입
• 스키마 : 데이터베이스의 구조와 제약조건에 관한 명세를 기술한 집합

---

3. SQL 명령어

• 문자열은 작은따옴표(')만 사용 가능
• 대소문자 구분하지 않음

1. DDL(Data Definition Language, 데이터 정의어)

• CREATE : 데이터베이스, 테이블, 뷰 등을 생성
• ALTER : 기존 객체(테이블, 뷰 등)의 구조 변경
• DROP : 객체(데이터베이스, 테이블, 뷰, 인덱스 등)를 완전히 삭제
• TRUNCATE : 테이블의 모든 데이터를 삭제하지만, 구조는 남김(롤백 불가)

2. DML(Data Manipulation Language, 데이터 조작어)

• SELECT : 데이터를 조회(검색)
• INSERT : 데이터를 테이블에 삽입
• UPDATE : 데이터를 수정
• DELETE : 데이터를 삭제(롤백 가능)

3. DCL(Data Control Language, 데이터 제어어)

• GRANT : 특정 사용자에게 객체나 작업에 대한 권한 부여
• REVOKE : 특정 사용자에게 부여된 권한을 박탈하거나 회수
• COMMIT/ROLLBACK : 트랜잭션 제어어를 DCL에 포함하여 데이터의 무결성 보장

---

4. SQL 명령어 사용

# DDL

• 데이터베이스 확인하고 (모든 데이터베이스 목록 조회) 생성 및 선택하기

-- 데이터베이스 조회
show databases;

-- 데이터베이스 생성
create database ai;

-- 데이터베이스 선택
use ai;

• 테이블 만들기

테이블명 : member
컬럼명	설명
idx	정수형. 자동증가하는 값. 기본키
userid	가변길이문자형(20). 중복값 허용x. null값 허용x
userpw	가변길이문자형(20). null값 허용x
name	가변길이문자형(20). null값 허용x
hp	가변길이문자형(20). null값 허용x
email	가변길이문자형(50). null값 허용x
ssn1	고정길이문자형(6). null값 허용x
ssn2	고정길이문자형(7). null값 허용x
zipcode	가변길이문자형(5)
address1	가변길이문자형(100)
address2	가변길이문자형(100)
address3	가변길이문자형(100)
regdate	날짜형. 기본값 now()
point	정수형. 기본값 100

create table member (
      idx int auto_increment primary key,
    userid varchar(20) unique not null,
    userpw varchar(20) not null,
    name varchar(20) not null,
    hp varchar(20) not null,
    email varchar(50) not null,
    ssn1 char(6) not null,
    ssn2 char(7) not null,
    zipcode varchar(5),
    address1 varchar(100),
    address2 varchar(100),
    address3 varchar(100),
    regdate datetime default now(),
    point int default 100
);

• 테이블 확인하기

desc member;

• 테이블 삭제하기

-- member 테이블 삭제
drop table member;

• 테이블을 다시 생성한 후, 컬럼 삭제하기

-- member 테이블의 point 컬럼 삭제
alter table member drop point;

-- 테이블 확인
desc member;

• 컬럼 추가하기

-- point 컬럼을 정수형. 기본값을 1000으로 추가
alter table member add point int default 1000;

• 컬럼 수정하기

-- point 컬럼의 기본값을 100으로 수정
alter table member modify column point int default 100;

 

# DML

• 더 간단한 예제로 DML을 다뤄보자
• 테이블 만들기

테이블명 : voca
컬럼명	설명
eng	가변길이문자형(50). 기본키
kor	가변길이문자형(50). null값 허용x
lev	정수형. 기본값 1
regdate	날짜형. 기본값 now()

create table voca (
	eng varchar(50) primary key,
    kor varchar(50) not null,
    lev int default 1,
    regdate datetime default now()
);

• 데이터 추가하기

insert into voca values ('apple', '사과', 1, now());

insert into voca (eng, kor) values ('banana', '바나나');

insert into voca values ('orange', '오렌지', null, null);

-- Error Code: 1062. Duplicate entry 'orange' for key 'voca.PRIMARY'
-- insert into voca values ('orange', '오렌지', null, null);

insert into voca (eng, kor) values ('melon', '메론');

insert into voca (eng, kor, lev) values ('avocado', '아보카도', 2);

• 데이터 삭제하기
  • delete from voca : 모든 데이터를 삭제하는 것 뿐, 테이블의 스키마는 남아있음

-- voca 테이블에서 eng가 'banana'인 데이터 삭제
delete from voca where eng = 'banana';

-- voca 테이블의 모든 데이터 삭제
delete from voca

• 데이터 수정하기

-- voca 테이블의 모든 행의 lev를 1로 수정
update voca set lev = 1;

-- voca 테이블의 eng가 'avocado'인 데이터의 lev를 2로 수정
update voca set lev = 2 where eng = 'avocado';

-- 테이블 확인
desc voca;

---

* ip : 네트워크에 연결된 장치의 고유 주소(인터넷에 참여하는 모든 것들은 ip 주소가 있음)

 

* port : 컴퓨터 안에서 어떤 프로그램과 통신할지 구분하는 번호