[SQL] MySQL

Beekeeper Studio -> MySQL 연결하기 

Beekeeper Studio에서 File > New Window > New Connection Type:MySQL

> Host에 연결된 MySQL 워크벤치의 호스트 입력 

> MySQL의 User, Password 입력 

> Default Database에 테이블 명 입력 

> Connect 

 

---

MySQL 데이터 타입 

MySQL 데이터 타입은 숫자, 문자열, 날짜/시간, JSON, BLOB으로 분류 가능 
- 정수: TINYINT ~ BIGINT

- 소수점 숫자: FLOAT, DECIMAL 

- 문자열: VARCHAR가 가변 길이, TEXT는 대용량 데이터 저장

- 날짜와 시간은 DATE, DATETIME, TIMESTAMP를 필요에 따라 선택
- JSON 데이터를 저장할 경우 JSON 타입을 활용 가능

 

 

CREATE TABLE users (
user_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username CHAR(10) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL UNIQUE,
gender ENUM('Male', 'Female') NOT NULL,
interests SET('Technology', 'Sports', 'Music', 'Art', 'Travel', 'Food', 'Fashion', 'Science') NOT NULL,
bio TEXT NOT NULL, 
-- TINYTEXT profile_picture TINYBLOB, -- TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
-- TINYINT SIGNED: -128 TO 127 UNSIGNED: 0 TO 255
-- SMALLINT: SIGNED: -32768 TO 32767, UNSIGNED: 0 TO 65535
-- MEDIUMINT SIGNED: -8388608 TO 8388607 UNSIGNED: 0 TO 16777215
-- INT SIGNED: -2147483648 TO 2147483647, UNSIGNED: 0 TO 4294967295
-- BIGINT SIGNED: -9223372036854775808 TO 9223372036854775807, UNSIGNED: 0 TO 18446744073709551615
age TINYINT UNSIGNED NOT NULL CHECK (age < 100),
is_admin BOOLEAN DEFAULT FALSE NOT NULL, -- TINYINT(1, 0)
balance FLOAT DEFAULT 0.0 NOT NULL, -- DECIMAL(p,s)
/* TIMESTAMP - '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
DATETIME - '1000-01-01 00:00:00' to '9999-12-31 23:59:59' */
joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- DATETIME YYYY-MM-DD hh:mm:ss
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
birth_date DATE NOT NULL,
bed_time TIME NOT NULL,
graduation_year YEAR NOT NULL, -- 1901 to 2155
-- We also have JSON, GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, & GEOMETRYCOLLECTION

CONSTRAINT chk_age CHECK(age < 100),
CONSTRAINT uq_email UNIQUE(email)
);

INSERT INTO users (
username,
email,
gender,
interests,
bio,
age,
is_admin,
birth_date,
bed_time,
graduation_year
)  

VALUES (
'bizzybee',
'bizzy@bee.com',
'Female',
'Travel',
'I like running and reading',
38,
TRUE,
'1986.04.27',
'23:00',
2011
);

-- Drop Column (컬럼 삭제)
ALTER TABLE users DROP COLUMN profile_picture;

-- Rename Column and also type (컬럼 이름 및 데이터 타입 변경)
ALTER TABLE users CHANGE COLUMN bio about_me TEXT; -- This is used to change both the column name and type of data (이것은 컬럼 이름과 데이터 타입을 모두 변경할 때 사용됩니다)

-- Change the column type only (데이터 타입만 변경)
ALTER TABLE users MODIFY COLUMN about_me TINYTEXT; -- This is used when to change the type of data only (이것은 데이터 타입만 변경할 때 사용됩니다)

-- Rename database (데이터베이스 이름 변경)
ALTER TABLE users RENAME TO customers;
ALTER TABLE customers RENAME TO users;

-- Drop Constraints (제약 조건 삭제)
ALTER TABLE users DROP CONSTRAINT uq_email;
ALTER TABLE users
DROP CONSTRAINT username,
DROP CONSTRAINT chk_age;

-- Add Constraints (제약 조건 추가)
ALTER TABLE users
ADD CONSTRAINT uq_email UNIQUE (email)
ADD CONSTRAINT uq_username UNIQUE (username);

ALTER TABLE users ADD CONSTRAINT chk_age CHECK(age
-- Modify Column (컬럼 수정)
ALTER TABLE users MODIFY COLUMN bed_time TIME NULL;

-- Literally to show all columns (모든 컬럼 보기)
SHOW CREATE TABLE users;

 

 

데이터 타입 변경하기(새로운 칼럼 만들어서 migration)

-- <이미 데이터가 존재하는 칼럼의 데이터 타입 변경하기>

-- 방법. 완전히 새로운 칼럼 만들기
ALTER TABLE users MODIFY COLUMN graduation_year DATE;
-- 에러메세지: 'year' 타입으로 이미 데이터가 존재해서 변경 불가(YEAR 타입 -> DATE 타입으로 직접 변경 불가)  

ALTER TABLE users ADD COLUMN graduation_date DATE;
-- 'date' 타입으로 새로운 칼럼을 만들어버림 

SELECT graduation_year, MAKEDATE(graduation_year, 1) from users;
-- MAKEDATE(YEAR, DAY) 함수: YEAR 값을 기반으로 해당 연도의 1월 1일을 생성

UPDATE users SET graduation_date = MAKEDATE(graduation_year, 1);
-- graduation_year 값을 DATE 형식으로 변환한 후, 새로운 graduation_date 칼럼에 저장

ALTER TABLE users DROP COLUMN graduation_year;
-- YEAR 타입의 'graduation_year' 칼럼은 삭제 

ALTER TABLE users MODIFY COLUMN graduation_date DATE NOT NULL;
-- 'graduation_date' 칼럼 NOT NULL 설정 추가: 처음 칼럼을 만들때 NOT NULL을 넣고 싶으면 디폴트 값을 설정해줘야 한다

---

Generated Columns 

 

- STORED: 생성된 칼럼이 실제로 디스크에 저장(w/CONCAT 함수)

     저장공간 여유가 있고, 조회속도가 빨라야 하는 경우 사용 

- VIRTUAL: 디스크(데이터베이스)에 저장되지 않음(w/SUBSTRING 함수)

     SELECT 할 때마다 매번 새롭게 구한다(여기서는 매번 SUBSTRING 함수를 실행) 

CREATE TABLE user_v2 (
  user_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  full_name VARCHAR(101) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED
);
-- CONCAT 함수를 사용하여 full_name 칼럼의 데이터는 자동으로 생성되도록

INSERT INTO user_v2(
  first_name, last_name, email)
  VALUES ('bizzy', 'bee', 'bizzy@bee.com');
-- full_name 항목을 입력하지 않아도 저절로 생성된다! 
-- full_name 데이터를 입력하려고 하면 에러 발생


ALTER TABLE user_v2 ADD COLUMN email_domain VARCHAR(50) GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL;
-- 이메일 도메인만 추출하기('@' 뒷부분만 추출)