TIL 51 | MySQL 데이터 베이스 및 테이블 생성

CHAEIN·2021년 8월 27일
0

MySQL

목록 보기
1/8
  • CREATE DATABASE
  • SHOW DATABASES
  • CREATE TABLE
  • SHOW TABLES
  • DESCRIBE
  • INSERT INTO ~ VALUES ~
  • UPDATE ~ SET ~
  • DELETE FROM ~
  • ALTER TABLE

    • - 데이터 베이스 만들기

    CREATE DATABASE 데이터베이스이름
# SHOW를 사용하면 mysql에 생성된 모든 데이터 베이스를 확인할 수 있다. 
SHOW DATABASES;

    sys 데이터 베이스

    sys 데이터베이스는 MySQL 서버의 성능 관련 정보들을 갖고있는 데이터베이스로 MySQL 설치시 자동으로 생성된다.

    - 테이블 만들기

    USE 사용할 데이터베이스

# 필요한 컬럼과 데이터 타입, 속성 지정
CREATE TABLE `테이블 이름` (
  `id` int, #Primary key는 맨 아래가 아닌 여기서 지정해줄수도 있다.
  `email` text,
  `age` int,
  `height` double,
  `weight` double,
  `birthday` date,
  PRIMARY KEY (`id`)
)

    SHOW TABLES를 사용하면 해당 데이터 베이스의 테이블 리스트를 확인할 수 있다. 이 명령어를 사용하기 위해선 USE명령어로 사용할 데이터 베이스를 먼저 지정해주어야 한다. 또한 DESCRIBE를 사용하면 해당 테이블의 컬럼과 컬럼 속성을 확인할 수 있다.

    USE 사용할데이터베이스;
SHOW TABLES; # 해당 데이터베이스에 속한 모든 테이블 확인
DESCRIBE 테이블이름;

    - 테이블 이름 변경하기

    RENAME TABEL 현재컬럼이름 To 변경할 이름;
RENAME TABEL student To undergraduate;

    - 테이블 전체 or 컬럼 복사하기

    컬럼/로우 모두 복사

    CREATE TABLE copy_table AS SELECT * from copied_table
# 컬럼과 로우 모두 복사한다.

    컬럼만 복사

    CREATE TABLE copy_table LIKE copied_table
# 컬럼 구조와 속성만 복사한다.

    로우만 복사

    INSERT INTO copy_table SELECT * FROM copied_table
# copied_table의 모든 row들을 copy_table로 복사할 수 있다.
# 두 테이블의 컬럼구조가 일치해야만 가능하다. 

INSERT INTO copy_table 
	SELECT * FROM copied_table
    WHERE major = 101;
# 조건에 해당하는 로우들만 복사하는 것도 가능하다.

    - 테이블 삭제하기

    DROP TABLE copy_table

    - row 추가하기

    만들어진 테이블에 row를 삽입할 땐 INSERT INTO ~ VALUES를 사용한다.

    # 생성할 컬럼 순서대로 값을 괄호로 묶어 삽입.
INSERT INTO `테이블 이름` (`id`, `email`, `age`, `gender`, `height`, `weight`, `birthday`) 
VALUES (1,'codeit@naver.com',28,'m',178.2,70,'1992-01-03');

    만약 모든 컬럼에 값이 있는 로우가 이미 추가되어 있다면 이후에 로우를 추가할 때에는 컬럼 이름을 써주는 부분은 생략할 수 있다.

    INSERT INTO `테이블 이름`
VALUES (1,'codeit@naver.com',28,'m',178.2,70,'1992-01-03');

    만역 일부 컬럼에 값이 없는 로우를 추가하고 싶을 경우 컬럼 이름에서 값이 없는 컬럼은 빼주면 된다.

    INSERT INTO `테이블 이름` (`id`, `email`, `height`, `weight`, `birthday`) 
VALUES (1,'codeit@naver.com',178.2,70,'1992-01-03');
# age와 gender 컬럼에는 자동으로 null 이 들어간다.

    만약 id를 primary key로 지정하고 auto_increment속성을 부여했다면 로우를 추가할 때 id값을 넣지 않아도 자동으로 id 값이 계산되어 들어간다.

    - row 수정하기

    UPDATE 테이블 이름
SET 수정할 컬럼 = '수정값'
WHERE 조건 (ex. id = '2') 
# 해당 테이블의 아이디가 2인 row의 컬럼을 '수정값'으로 수정한다.
# 조건 절을 안쓰면 특정 row가 아니라 테이블의 모든 row의 해당 컬럼값을 수정해버린다.

    한개 이상의 컬럼을 수정할 때에는 콤마로 구분해주면된다.

    UPDATE student
SET major = '멀티미디어학과', name = '김코딩'
WHERE id = 2

    기존값을 이용한 연산으로 row를 수정할 수도 있다.

    UPDATE exam SET score = score + 3

    - row 삭제하기

    row를 삭제하는 방법에는 크게 '물리 삭제'와 '논리 삭제'가 있다.

    1) 물리삭제 : row를 바로 삭제해버리는 것

    DELETE FROM student WHERE id = 4;
# WHERE 절을 적지 않으면 모든 로우가 삭제된다.

    모든 로우를 삭제하고 싶을 때는 조건 없는 DELETE FROM을 쓸 수 있지만 TRUNCATE도 쓸 수 있다.

    TRUNCATE student

    2) 논리 삭제 : 삭제해야 할 row를 삭제하지 않고 '삭제여부'를 나타내는 별도의 컬럼을 두고 거기에 삭제되었음을 나타내는 값을 넣는 것

    논리 삭제는 삭제를 할 때 DELETE 문을 쓰는게 아니라

    UPDATE order SET is_cancelled = ‘Y’;

    업데이트 문으로 새로운 컬럼에 삭제되었음을 표기한다. 논리 삭제는 삭제후에도 관련 history를 확인할 수 있다는 점에서 유용하지만 저장 용량이 줄지 않는다는 단점이 있다. 그러나 많은 기업에서 물리 삭제보다 논리 삭제를 많이 사용한다.

    - 컬럼 수정하기

    ALTER TABLE을 사용하면 생성한 테이블을 수정할 수 있다.

    1) ADD 컬럼 추가 

    ALTER TABLE table_name ADD COLUMN ex_column varchar(32) NOT NULL;

    2) RENAME COLUMN ~ TO ~ 컬럼 이름 변겅 

    ALTER TABLE student RENAME COLUMN student_number TO registration_number;

    3) MODIFY 컬럼 타입, 속성 변경 

    ALTER TABLE table_name MODIFY new_column varchar(16) NULL;

    ! DEFAULT 기본값 설정

    not null 설정이 되어있는 컬럼의 경우 row를 추가할 때 해당 컬럼의 값이 없으면 에러가 발생한다. 이를 방지하기 위해 null 이 아닌 값으로 default 설정을 해줄 수 있다. 

    ALTER TABLE table_name MODIFY new_column INT NOT NULL DEFAULT 101;
# 해당 컬럼 값이 없으면 디폴트 값인 101이 값으로 들어간다.

    ! DATE 갱신 및 기본값 설정

    게시글이나 댓글과 같은 데이터의 경우 업로드 시각과 수정 시각을 갱신해야할 때가 있다. 그럴 땐 NOW()함수를 이용하거나 DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP를 사용하면 간단하게 처리할 수 있다.

    1. NOW() 

    INSERT INTO post(title, content, upload_time, recent_modified_time)
VALUES ('기분 좋은 날', '날씨 좋다', NOW(), NOW())
#NOW()를 쓰면 현재 시각이 반영된다. 
    UPDATE post
	SET content = '오늘 날씨 흐림',
    	recent_modified_time = NOW()
    WHERE id = 1;
# 수정할 때에도 NOW()를 쓰면 수정하는 현재 시각이 반영된다.

    2. DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP
    DATETIME 타입 또는 TIMESTAMP 타입의 컬럼에는 DEFAULT CURRENT_TIMESTAMP 라는 속성과,ON UPDATE CURRENT_TIMESTAMP 라는 속성을 줄 수 있다. 

    ALTER TABLE post
MODIFY upload_time DATETIME DEFAULT CURRENT_TIMESTAMP,
MODIFY recent_modified_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP;
    INSERT INTO post(title, content)
VALUES('맛있는 점심', '된장찌개 맛있다.')
# 이때 기본값으로 현재 시각이 들어간다. 
    UPDATE post
	SET content = '김치찌개 맛있다'
    WHERE id = 2;
# 이 때 recent_modified_time에 수정하는 현재 시각이 갱신되어 들어간다.

    ! UNIQUE 속성

    만약 컬럼에 같은 값을 가진 로우가 추가되는 것을 막고 싶다면 해당 컬럼에 UNIQUE 속성을 해주면 된다.

    ALTER TABLE student MODIFY restration_number INT NOT NULL UNIQUE

    PRIMARY KEY와 UNIQUE는 비슷하지만 PRIMARY KEY는 테이블당 하나만 존재할 수 있고 UNIQUE는 각 컬럼별로 가질 수 있는 속성이라는 점에서 다르다. 또한 PRIMARY KEY에는 null 값이 올 수 없지만 UNIQUE 속성의 컬럼에는 null 값이 올 수 있다.

    4) CHANGE 컬럼 설정과 이름 변경 

    ALTER TABLE table_name CHANGE ex_column new_column int NULL;

    5) DROP COLUMN 컬럼 삭제

    ALTER TABLE table_name DROP COLUMN ex_column;

    6) FIRST 컬럼 순서 가장 앞으로 

    ALTER TABLE student
	MODIFY id INT NOT NULL AUTO_INCREMENT FIRST;

    7) FIRST 컬럼 순서 가장 앞으로 

    ALTER TABLE student
	MODIFY class VARCHAR(30) NULL AFTER name;
# class 컬럼이 name 뒤에 위치한다.

    ! Primary key(기본키)

    테이블에서 하나의 row를 고유하게 식별할 수 있도록 해주는 column. 주로 id라는 이름을 붙인다. 테이블을 생성하고 id 컬럼을 생성하면 primary key 설정을 해주어야한다.

    1) 테이블 생성 시 설정하기

     CREATE TABLE Test 
(
  ID INT PRIMARY KEY,
  Name VARCHAR(30),
  ReserveDate DATE,
  RoomNum INT
);

    2) 테이블 수정시 설정하기

    ALTER TABLE 테이블이름
	ADD 필드이름 필드타입 PRIMARY KEY;
    ALTER TABLE 테이블이름
	MODIFY COLUMN 필드이름 필드타입 PRIMARY KEY

    Primary key는 반드시 not null 상태여야 한다. null은 값이 없는 상태를 나타내는데 not null은 이 컬럼에 반드시 어떤 값이 들어있어야함을 의미한다. Primary key는 각 row를 식별하기 위한 값이기 때문에 값이 존재하지 않으면 안된다. 이 때 not null로 지정해주면 이 컬럼에 null이 들어갔을 때 에러가 발생한다.

    Primary Key에는 크게 두가지 종류가 있다.

    (1) Natural key
    실제로 어떤 개체가 갖고 있는 속성을 나타내는 컬럼이 Primary Key가 됐을 때 이를 Natural Key라고 한다. 사람의 주민등록번호, 책의 ISBN 이 이에 해당한다.

    (2)Surrogate Key
    어떤 개체의 실제 속성은 아니지만 Primary Key로 쓰기 위해 추가한 컬럼을 Surrogate Key라고 한다. 주로 ID 컬럼 값이 이에 해당한다. 이 키에 AI(Auto Increment) 설정을 해주면 row가 추가할 때마다 id 값은 자동으로 그 이전값의 1을 추가한 값이 입력된다.

    ALTER TABLE `copang_main`.`member`
CHANGE COLUMN `id` `id` INT NOT NULL AUTO_INCREMENT ;

    - 제약사항(CONSTRAINT)

    제약 사항 걸기

    ALTER TABLE 테이블 이름
	ADD CONSTRAINT 제약조건이름 CHECK 제약조건

ALTER TABLE student
	ADD CONSTRAINT st_rule CHECK (registration_number < 30000000)
# registration_number가 3천만이 넘어가는 수는 제약조건 st_rule에 의해 추가될 수 없다. 
# 이 제약 조건으로 이해 제약 조건을 위반하는 데이터를 추가할 경우 에러가 발생한다.

    하나의 제약사항에 여러개의 조건을 걸 수도 있다.

    ALTER TABLE student
	ADD CONSTRAINT st_rule
    CHECK (email LIKE '%@%' AND gender IN ('m', 'f')
# email 컬럼에는 @가 포함된 문자열만, gender 컬럼에는 'm'이나 'f'만 들어올 수 있다.

    제약 사항 삭제

    ALTER TABLE student DROP CONSTRAINT st_rule
    profile
    CHAEIN
    다음 포스트

    TIL 52 | MySQL 데이터 조회

    0개의 댓글