[MySQL] MySQL 중급

CREATE TABLE AS SELECT

똑같은 테이블을 생성하는 구문이다.

world database안의 city테이블과 동일한 city2테이블을 생성해보자

CREATE TABLE city2 AS SELECT * FROM city;

우클릭 후 Refresh ALL을 클릭

city2 테이블을 확인해보자

SELECT * FROM city2;

city2 테이블이 복사된 것을 확인할 수 있음. 

CREATE DATABASE

• CREATE DATABASE문은 새로운 DB생성
• USE문으로 새 데이터 베이스를 사용하거나, 스키마에서 선택하여 사용가능

CREATE DATABASE sample1;
USE sample1;

CREATE TABLE

스키마에서 테이블 탭 우클릭 후 Create Table 클릭

TABLE의 Column Name과 DataType 결정

 

Data Type은 다음 공식 문서를 참조하자

dev.mysql.com/doc/refman/8.0/en/data-types.html

MySQL :: MySQL 8.0 Reference Manual :: 11 Data Types

 

여기서 Apply를 클릭하면,

 

다음의 창이 생성되는데

SQL 형태의 모양으로 바꿔줌.. 이 내용이 맞다면 Apply를 눌르고 

Finish를 누르면 테이블이 생성 완료된 것이다. 

table을 확인해보자

SELECT * FROM new_table;

---

사실 위의 방법으로도 생성 가능하지만 쿼리문에서 직접 생성할 수 있다.

아래를 살펴보자.

---

SELECT * FROM new_table;
CREATE TABLE new_table2 (
	id INT NOT NULL PRIMARY KEY,
	col1 INT NULL,
	col2 FLOAT NULL,
	col3 VARCHAR(45) NULL
);

각 column을 생성하는데 

col1: INT타입 NULL 허용

col2: FLOAT타입 NULL허용

col3: 가변적 길이를 가지는 문자열 최대길이 45 NULL허용

다음 구문을 실행후 refresh하면,

새로운 TABLE이 생성됨을 확인할 수 있다.

SELECT * FROM new_table2;

ALTER TABLE

TABLE을 생성했는데 수정하고 싶을 때 사용하는 구문이다. 

ADD

ALTER TABLE 문과 함께 ADD문을 사용하면, 테이블에 컬럼을 추가할 수 있다.

col4를 추가하고, 확인하자

ALTER TABLE new_table2
ADD col4 INT NULL;

SELECT * FROM new_table2;

MODIFY

위의 과정에서 col4를 추가 후 자료형을 변경하고 싶을 때 사용한다. 아래의 예를 보자.

DESCRIBE new_table2;

col4가 INT형이다.

ALTER TABLE new_table2
MODIFY col4 VARCHAR(20) NULL;

실행하면.. 자료형이 수정되었다. 

col4가 INT에서 VARCHAR(20)으로 변경

DROP

ALTER TABLE문과 함께 DROP문을 사용하면, 테이블에 컬럼을 제거 할 수 있음

ALTER TABLE new_table2
DROP col4;

col4 제거됨

INDEX (인덱스)

• 테이블에서 원하는 데이터를 빠르게 찾기 위해 사용된다.
• 일반적으로 데이터를 검색할 때 순서대로 테이블 전체를 검색하므로 데이터가 많으면 많을수록 탐색하는 시간이 늘어난다.
• 검색과 질의를 할 때 테이블 전체를 읽지 않기 때문에 빠르다.
• 설정된 컬럼 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우, 인덱스도 함께 수정되어야 한다.
• 인덱스가 있는 테이블은 처리 속도가 느려질 수 있으므로 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 좋다.

CREATE INDEX

CREATE INDEX 이름 을 사용하여 인덱스를 생성한다. 

CREATE INDEX col1IdX
ON new_table (col1);

SHOW INDEX

인덱스 정보를 확인한다.

SHOW INDEX FROM new_table;

new_table에 col1Idx key_name이 추가된 것을 확인 할 수 있다.

위의 PRIMARY키는 CREATE TABLE을 만들때 PRIMARY KEY를 id에 붙인 것을 생각해보자..

기본적으로 id가 PRIMARY KEY라는 기본적인 인덱스를 가지게 된다. (기본적인 키라고 생각하자)

PRIMARY키에 대한 자세한 내용은 아래 사이트에서 참고

tcpschool.com/mysql/mysql_constraint_primaryKey

코딩교육 티씨피스쿨

CREATE UNIQUE INDEX

CREATE UNIQUE INDEX col2IdX
ON new_table (col1);

 

Non_unique가 0이므로 Unique임을 알 수 있음.

FULLTEXT UNIQUE 

일반적인 인덱스와는 달리 매우 빠르게 테이블의 모든 텍스트 컬럼을 검색한다. 

ALTER TABLE new_table
ADD FULLTEXT Col3Idx(col3);

INDEX (인덱스 삭제)

ALTER TABLE 이용

ALTER문을 사용하여 테이블에 추가된 인덱스 삭제함. 

ALTER TABLE new_table
DROP INDEX Col3Idx;

DROP문 이용

• DROP문을 사용하여 해당 테이블에서 명시된 인덱스를 삭제
• DROP문은 내부적으로 ALTER문으로 자동 변환되어 명시된 이름의 인덱스를 삭제

DROP INDEX Col2Idx ON new_table;

 

VIEW

• 뷰(view) 는 데이터베이스에 존재하는 일종의 가상 테이블
• 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하진 않음
• MySQL 에서 뷰는 다른 테이블이나 다른뷰에 저장되어 있는 데이터를 보여주는 역할만 수행
• 뷰를 사용하면 여러 테이블이나 뷰를 하나의 테이블처럼 볼 수 있음

뷰의 장점

• 특정 사용자에게 테이블 전체가 아닌 필요한 컬럼만 보여줄 수 있음
• 복잡한 쿼리를 단순화해서 사용
• 쿼리 재사용 가능

뷰의 단점

• 한 번 정의된 뷰는 변경할 수 없음
• 삽입 , 삭제 , 갱신 작업에 많은 제한 사항을 가짐
• 자신만의 인덱스를 가질 수 없음

CREATE VIEW

CREATE VIEW 문을 사용하여 뷰를 생성한다.

CREATE VIEW testView AS
SELECT Col1, Col2
FROM new_table;

이때도, VIEW를 보고 싶으면 SELECT 구문을 사용하면 된다. 

SELECT * FROM testView;

ALTER VIEW

수정하고 싶을 때

ALTER VIEW testView AS
SELECT Col1, Col2, COl3
FROM new_table;

DROP VIEW

뷰를 삭제하고 싶을 때 사용한다.

DROP VIEW testView;

 

#PROBLEM.7

city, country, counrtylanguage 테이블을 JOIN하고, 한국에 대한 정보만 뷰 생성하기

CREATE VIEW AllView AS 
SELECT city.Name, country.SurfaceArea, city.Population, Countrylanguage.language
FROM city
JOIN country ON city.CountryCode = country.Code
JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode
WHERE city.CountryCode = 'KOR';

SELECT * FROM AllView;

INSERT(쿼리문에서)

DATA를 직접 테이블에 삽입할 때 쓰는 구문

INSERT INTO new_table
VALUE(1, 123, 1.1, "TEST");

SELECT * FROM new_table;

INSERT(Work bench에서)

빈 FIELD에 커서를 눌러 입력할 수 있음

이후, Apply를 클릭하면, 

다음과 같은 창이 출력되며 Apply 후 FINISH하면 적용된다.

INSERT INTO SELECT

new_table에 있는 내용을 new_table2테이블에 삽입한다.

TABLE에 있는 내용을 새로운 TABLE로 복사..

INSERT INTO new_table2 SELECT * FROM new_table;

SELECT * FROM new_table2;

UPDATE

• 기존에 입력되어 있는 값 변경하는 구문
• WHERE절 생략 가능하나 테이블의 전체행의 내용 변경하므로 WHERE 조건을 항상 붙여야 함. (중요)

UPDATE new_table
SET col1=1, col2=1.0, col3='test'
WHERE id =1;

DELETE

• 행 단위로 데이터 삭제하는 구문
  
• DELETE FROM 테이블이름 WHERE 조건 으로 선언
• 데이터는 지워지지만 테이블 용량은 줄어들지 않음
• 원하는 데이터만 지울 수 있음
• 삭제 후 잘못 삭제한 것을 되돌릴 수 있음 (휴지통에 넣었다고 생각하자)

DELETE FROM new_table
WHERE id =1;

SELECT * FROM new_table;

TRUNCATE

• 용량이 줄어 들고, 인덱스 등도 모두 삭제
• 테이블은 삭제하지는 않고, 데이터만 삭제
• 한꺼번에 다 지워야 함
• 삭제 후 절대 되돌릴 수 없음

TRUNCATE new_table;

SELECT * FROM new_table;

DROP TABLE

• 테이블 전체를 삭제, 공간, 객체를 삭제
• 삭제 후 절대 되롤릴 수 없음

DROP TABLE new_table;

SELECT * FROM new_table;

DROP TALBE 실행 후 찾을 수 없다고 오류 발생

삭제 된 것을 확인

DROP DATABASE

해당 데이터 베이스를 삭제함.

데이터 베이스 삭제 시 해당 DB안의 TABLE을 전부 제거해야함.

삭제 된 것을 확인