[MySql] 쿼리 공부하기

MySQL 접속하기

$mysql -u root -p --host=localhost --socket=/tmp/mysql.sock

• -u root : root 사용자로 서버 프로그램 접속 ⇒ 관리자
• —host=localhost : Unix domain socket을 이용한 방식으로 소켓 파일(/tmp/mysql.sock)을 통해서 mysql 서버 프로그램에 접속 (TCP/IP 통신이 아니라 유닉스의 프로세스 간 통신)
• —socket=/term/mysql.sock : 소켓 통신에 사용할 소켓 파일 지정 ⇒ socket 파일을 통해서 컴퓨터 내에서만 통신이 가능

$mysql -u root -p

• default host : localhost
• default socket 파일 위치 : MySQL 서버의 설정파일 (my.cnf)에서 소켓 파일의 위치를 읽어서 접속

SHOW DATABASES; :

mysql 소켓 방식이란, Unix Domain Socket 으로 UNIX의 프로세스 간 통신이 가능한 소켓을 말합니다.

mysql 클라이언트 프로세스와 mysql, 즉 서버 프로세스는 서로 다른 프로세스에서 실행되는데, 이 둘 사이에서 프로세스 간의 통신을 하는 것을 의미합니다.

MySQL은 통신을 할 때, TCP/IP Socket을 이용하는 방식과 Unix Domain Socket을 이용한 방식 둘 다 사용할 수 있다.

TCP/IP Socket은 통신을 위해서 IP와 PORT 번호를 이용해서 접속하듯이 Unix Domain Socket은 socket 파일을 이용해서 통신하는 것을 의미합니다.

TCP/IP를 통해 호스트에 접속하기

$mysql -u root -p --host=127.0.0.1 --port=3306

• -u root : root 사용자로 서버 프로그램 접속
• --host=127.0.0.1 : TCP/IP 통신 방식을 사용하여 MySQL 서버 프로그램에 접속
• --port=3306 : TCP/IP 통신 방식을 사용할 때에는 MySQL 서버 프로그램의 포트 번호를 명시

Unix Domain Socket과 TCP/IP 접속 방식의 차이점

• MySQL 서버 프로그램에 접속할 수 있는 방법은 “Unix Domain Socket”방식과 “TCP/IP”방식이 있다.
• “Unix Domain Socket”은 프로세스 간 통신이 가능한 소켓
• 클라이언트 프로그램(mysql)과 서버 프로그램(mysqld)이 같은 운영체제에서 실행되고 있을 때 사용 가능
• 프로세스 간 통신이 가능하기 때문에 TCP/IP 통신에 비해 더 빠름
• 하지만, “Unix Domain Socket”은 로컬에 설치된 mySQL 서버가 아니라 원격 호스트에 있는 MySQL 서버에 접속할 때에는 사용할 수 없으며, 반드시 TCP/IP 기반의 소켓 통신 방법을 사용해서 접속해야 함.
  • 내가 로컬에서 개발하던 MySQL 서버를 AWS 같은 클라우드 프로바이더를 사용해서 원격에 만들어 놓은 DB에 배포해야 하는 경우, 원격 서버에 접속해야 하는 상황이 많이 발생

ps -ef | grep mysqld 를 이용하여 어떤 소켓에 접속해야 하는지 찾을 수 있다.

mysql.server start 해야 됨.

반대로 끌 때는 mysql.server stop을 해야함.

 

DDL(Data Definition Language)

 : schema 내의 객체를 정의(table)하고 관리할 때 사용되는 쿼리문을 의미

 

• 대표적인 DDL : Create, Alter, Drop
• DDL로 데이터를 구성하는 논리적인 구조(데이터베이스 또는 테이블)을 추가하고 수정하고 삭제할 수 있음
• 추가로 메타데이터(데이터에 대한 데이터)를 다루는 것이 가능

 

 

 

users Table 객체 생성

• DDL(Data Define Language)을 사용해서 데이터베이스에 사용자 테이블(users)을 생성
• 자료형 : INTEGER, VARCHAR, DATETIME 등
• 제약 조건 : NULL, NOT NULL, PRIMARY KEY, AUTO INCREMENT (테이블 혹은 컬럼에 제약을 설정함으로써 저장될 데이터를 제한할 수 있음)

CREATE TABLE comments(
	id INT NOT NULL AUTO_INCREMENT,
	content VARCHAR(3000) NOT NULL,
	user_id INT NOT NULL,
	post_id INT NOT NULL,
	created_at TIMESTAMP NOT NULL DEFAULT NOW(),
	updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (id), 
	FOREIGN KEY (user_id) REFERENCES users (id),
	FOREIGN KEY (post_id) REFERENCES posts (id)
);

TABLE 변경 - 수정 시나리오

• 서비스를 이용하는 사용자들의 나이에 따라서 게시글을 추천해주는 기능이 필요
• 더이상 profile_image 열 (column)이 필요 X
• posts 테이블에서 사용 중이던 content의 저장 공간 부족으로 더 많은 공간 확보가 필요
• 변경 사항 정리
  • users table에 age열을 추가 (자료형 →INT, 제약조건 →NOT NULL )
  • users 테이블의 profile_image열을 삭제
  • posts 테이블의 content 열의 자료형을 VARCHAR에서 TEXT로 변경
  ⇒가장 간단한 방법 : DROP TABLE 로 테이블을 완전히 삭제하고 변경된 구조에 맞게 CREATE TABLE 로 다시 생성하는 것 → 초기 데이터베이스를 처음 구축할 때는 가능, BUT 이미 서비스 되고 있는 경우, 테이블에 많은 데이터가 존재하기 때문에 데이터를 그대로 유지하면서 테이블의 구조를 변경할 수 있는 방법을 적용해야 한다.

DDL을 사용하여 기존에 존재하는 열과 제약 조건을 변경할 수 있다.

• 열 추가(colum)
  • ALTER TABLE 명령에 변경하고자 하는 테이블명(users) / 행위(ADD) /열 이름(age) / 자료형 (INT) / 제약조건 (NOT NULL) 순서로 지정하면, 새롭게 나이를 저장할 수 있는 컬럼을 users Table에 추가할 수 있다.

  mysql> ALTER TABLE users ADD age INT NOT NULL;
  

• 지금처럼 NOT NULL 제약 조건을 붙인 새로운 열을 추가하고 싶다면, 먼저 NOT NULL 제약 조건을 건 다음, 존재하는 데이터들의 age 열의 값을 NULL 이외의 값으로 기본 값을 지정해주어야 한다.
• age 의 Default 값이 NULL로 지정이 되어 있음. → ALTER TABLE users ADD age로 열을 추가할 때, 기존 데이터의 age열은 모두 NULL로 처리된다는 의미
• 열(Colum) 삭제
  • ALTER TABLE로 열의 자료형을 변경할 경우, 변경하고자 하는 테이블명(users) / 행위 (DROP) / 열 이름(profile_image) 순서로 삭제하고 싶은 열을 지정할 수 있음

  mysql> ALTER TABLE users DROP profile_image
  

• 열(colum) 수정
  • ALTER TABLE로 열의 자료형을 변경할 경우, 변경하고자하는 테이블명(posts)/ 행위(MODIFY)/ 열 이름(conten) /자료형(TEXT)/ 제약조건 (NULL) 순서로 변경하고 싶은 열 지정할 수 있음
  • mysql> ALTER TABLE posts MODIFY content TEXT NULL;
• ALTER TABLE users ADD CONSTRAINT users_job

TABLE 삭제

• 테이블 객체는 DROP TABLE 명령으로 스키마에서 지울 수 있음

mysql> DROP TABLE posts;

• DROP TABLE 명령을 사용하는 경우, 테이블 객체 안에 포함된 모든 데이터도 함께 삭제
• 주의 사항
  • 추가로, 테이블은 그대로 두고 데이터만 전부 삭제해야 하는 경우, 이때는 DML (DELETE 또는 TRUNCATE)명령을 사용해서 테이블 내의 데이터만 삭제할 수 있다.

  mysql> DELETE FROM posts WHERE condition;
  //한줄 한줄 수행하는 작업으로 매우 느리다는 점
  mysql> TRUNCATE TABLE posts;
  //한 번에 쏴악 더 빠른 성능, 더 빠른 속도로 데이터들만 삭제가 가능하다는 점
  

mysql> CREATE DATABASE westagram character set utf8mb4 collate utf8mb4_ci;
//character set 설정과 collation 설정을 동시에 해줄 수 있음