MySQL 기본 문법

• mysql 계정 접속

mysql -u root -p

1. DB 확인

• 데이터 베이스 확인

show databases;

• 데이터 베이스 접속

use [DB명];

• 테이블 목록 확인

show tables;

• 데이터 베이스 생성

create database [DB명] default CHARACTER SET UTF8;

• 데이터 베이스 삭제

DROP DATABASE [DB명]

2. 테이블 확인

• 테이블 생성

create table 테이블명(
		컬럼명 데이터타입..,
		컬럼명 데이터타입.., );

예시) CREATE TABLE todo ( id int(11) NOT NULL AUTO_INCREMENT, # AUTO_INCREMENT : 1부터 자동 증가 water_electrolysis_kW float, sofc_use_kW float, sofc_kW float, PRIMARY KEY(id) );

• 테이블 정보 확인

DESC [테이블명];

• 테이블의 모든 데이터 확인

SELECT * FROM todo;

• 테이블 데이터 추가

INSERT INTO [테이블명] (컬럼1, 컬럼2,...) VALUES (컬럼1 데이터, 컬럼2 데이터..);

예시) INSERT INTO todo (title, name, sequence) VALUES ("sql공부", "yong", 1);

• 테이블 데이터 삭제

DELETE FROM [테이블명] WHERE 조건;

• 테이블 데이터 수정

UPDATE [테이블명] SET 바꿀 속성 WHERE 조건;

예시) IF문처럼 UPDATE하기

UPDATE todo SET type = CASE

WHEN type = TODO THEN DOING

WHEN type = DOING THEN DONE

ELSE DONE

END

WHERE id = 1;

• 테이블명 변경

ALTER TABLE [기존 테이블명] RENAME [변경할 테이블명];

3. 컬럼 추가, 삭제, 변경

• 테이블의 컬럼 맨 뒤에 추가

ALTER TABLE [테이블명] ADD [컬럼명] [자료형];

예시) ALTER TABLE board ADD board_type varchar(100) NOT NULL;

• 테이블의 컬럼 맨 앞에 추가

ALTER TABLE [테이블명] ADD [컬럼명] [자료형] FIRST; 

예시) ALTER TABLE employee ADD working_day varchar(200) NOT NULL FIRST;

• 테이블의 지정 컬럼 뒤 추가

ALTER TABLE [테이블명] ADD [컬럼명] [자료형] AFTER [앞컬럼명];

예시) ALTER TABLE employee ADD name varchar(200) NOT NULL AFTERe working_day;

• 컬럼 삭제

ALTER TABLE [테이블명] DROP [컬럼명];

예시) ALTER TABLE user DROP user_type;

• 컬럼명 변경

ALTER TABLE [테이블명] CHANGE [기존컬럼명] [변경할 컬럼명] [컬럼타입];

예시) ALTER TABLE score CHANGE math math_score int;

• 컬럼 타입만 변경

ALTER TABLE [테이블명] MODIFY [컬럼명] [변경할 컬럼타입];

예시) ALTER TABLE user MODIFY address varchar(128);

• 컬럼 디폴트값 변경

ALTER TABLE [테이블명] ALTER COLUMN [컬럼명] SET DEFAULT [디폴트값 지정];

예시) ALTER TABLE dk_datalogger ALTER COLUMN updated_at SET default current_timestamp();

• 컬럼 10행 보기

SELECT * FROM [테이블명] LIMIT 10;

4. 데이터 추가, 삭제, 변경

• 데이터 추가

INSERT INTO 테이블명 [컬럼1, 컬럼2,..] VALUES (값1, 값2,...);

# 다수추가
INSERT INTO 테이블명 VALUES (값1, 값2,..), (값3, 값4,..);

예제) INSERT INTO table_name (id, name) VALUES (1105, ‘테스트’);

• UPSERT 내용

INSERT INTO a테이블 (aa컬럼, bb컬럼, cc컬럼) SELECT 'aaa', 'bbb', 'ccc' WHERE NOT EXISTS ( SELECT 1 FROM a테이블 WHERE aa컬럼 = 'aaa' );

• 데이터 수정

UPDATE 테이블명 SET [컬럼1 = 수정값1], [컬럼2 = 수정값2] [WHERE 조건];

예제) UPDATE table_name SET name = ‘테스트변경’, country = ‘대한민국’ where id=1105;

• 데이터 삭제

DELETE FROM 테이블 [WHERE 조건];

예제) DELETE FROM table_name where id = 1105;

5. 권한 설정 및 사용자 생성

mysql user, passwd 설정
mysql > CREATE USER '유저명'@'localhost' IDENTIFIED by '비밀번호';

예) CREATE USER 'nepirity'@'localhost' IDENTIFIED by 'admin@nepirity';

• 생성 DB에 권한 주기

# root 계정으로 접속
sudo mysql -u root;

# 권한 주기
mysql > GRANT ALL PRIVILEGES ON *.* TO 'nepirity'@'localhost';

• 외부 접속 허용

# 외부 접속 허용 가능한 계정 생성
mysql > create user 계정ID@'%' identified by '비밀번호';
mysql > grant all privileges on DB이름.* to 계정ID@'%';
mysql > flush privileges;

# 외부 포털 허용
cd /etc/mysql/mysql.conf.d
sudo vim mysqld.cnf

# 주석처리
-> bind-address = ~
   mysqlx-bind-address = 

# mysql 재시작
sudo service mysql restart

• AUTO_INCREMENT 초기화

# 현재부터 값 초기화
ALTER TABLE [테이블명] AUTO_INCREMENT =1; 

#기존의 값을 재정렬 (초기화가 아니라서 재정렬 후 이후의 값으로 auto_increment 해야함)
ALTER TABLE [테이블명] AUTO_INCREMENT =1;
SET @COUNT = 0;
UPDATE [테이블명] SET [테이블명].[컬럼명] = @COUNT:=@COUNT+1;

• db 결과 수직으로 보기

pass ='admin@nepirity'

mysql -h tong.gpt.tg.ne.kr -E -u nepirity -p$pass
mysql -h localhost -E -u nepirity -p$pass

• table 특성 및 만든 것 바로 확인

mysql> show create table [테이블명];

• mysql 시간 영구적으로 설정

[mysqld]
default-time-zone ='Asia/Seoul'

• 테이블에서 컬럼 현재 시간으로 설정

(컬럼명) DATETIME DEFAULT CURRENT_TIMESTAMP NULL

#변경
ALTER TABLE [테이블명] MODIFY 컬럼명 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

• CHARSET 변경

ALTER TABLE movies CONVERT TO CHARACTER SET utf8mb4;

• 테이블별 용량 확인

SELECT TABLE_NAME AS "Tables", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "ems" ORDER BY (data_length + index_length) DESC