MySQL 기본 사용법 주요 명령어들 정리(feat DDL, DML, DCL)

MySQL DB 접속하기

먼저 mysql을 실행해줍니다. 제가 사용하고 있는 OS는 Ubuntu 20.04 LTS 입니다. 제가 사용하고 있는 init system은 systemd(systemctl)이기 때문에 이를 통해 mysql을 실행해줍니다.

 

$ sudo systemctl start mysql

 

mySQL 상태를 확인해본면 현재 mysql 프로세스가 active(Running) 상태로 전환된 것을 확인할 수 있습니다.

$ sudo systemctl status mysql

 

이제 mySQL으로 접속합니다. root 계정으로 접속하게 되고 초기 비밀번호를 지정하지 않아서 바로 Enter을 쳐서 접속이 가능한 상태입니다. mysql 경로를 정확하게 지정해주면 Access Denied되는 일 없이 접속이 가능합니다.

$ sudo /usr/bin/mysql -u root -p

 

 

 

MySQL DB 사용자 등록

mySQL이 정상적으로 실행되었다면 사용자 등록을 해야 합니다. 현재 등록된 사용자 정보를 조회합니다.

 

$ SELECT User, Host FROM mysql.user;

 

사용자를 새로 생성합니다. user name은 developerBlog로 지정하고 password는 'mysql password'로 지정합니다. 새로운 사용자 계정이 생성되었습니다.

 

mysql > CREATE USER 'developerBlog'@'localhost' IDENTIFIED BY 'mysql password';
mysql > FLUSH PRIVILEGES;

 

 

사용자의 비밀번호를 변경하기 위해서는 아래 명령을 실행해줍니다.

mysql > ALTER USER '사용자ID'@'localhost' IDENTIFIED WITH mysql_native_password BY '변경할 비밀번호';

 

 

 

 

MySQL USER 권한 부여 (DDL + DCL)

새롭게 생성한 User가 사용할 수 있는 Database를 생성합니다.

mysql > CREATE DATABASE database_test;
mysql > show DATABASES;

 

새롭게 생성한 User에게 database_test 데이터베이스를 사용할 권한을 부여합니다. database의 모든 테이블을 새로 생성한 사용자에게 조회, 삭제, 삽입등의 모든 권한을 부여합니다.

mysql > GRANT ALL PRIVILEGES ON database_test.* TO 'developerBlog'@'localhost';
mysql > FLUSH PRIVILEGES;

 

사용자에게 부여된 권한을 조회하기 위한 명령어입니다.

mysql > SHOW GRANTS FOR developerBlog@localhost;

 

 

 

 

 

MySQL Database 사용하기(DDL)

시용할 데이터베이스를 선택합니다.

mysql > USE '데이터베이스 이름';

 

데이터베이스 내의 새로운 테이블을 생성합니다. 데이터베이스 SQL문을 연습하기 위한 용도라면 무료로 임시 데이터를 생성해주는 사이트들이 여럿 있습니다. 제가 사용하는 사이트는 mockaroo 입니다. 각종 테이블들에 관한 SQL문을 자동으로 생성해줍니다.

 

Mockaroo - Random Data Generator and API Mocking Tool | JSON / CSV / SQL / Excel

 

 

mysql > create table temporary_table (
	id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(50),
	gender VARCHAR(50),
	ip_address VARCHAR(20)
);

 

테이블이 생성되면 튜플을 추가해줍니다. Mocharoo에서 SQL문을 자동으로 생성해주기 때문에 생성된 SQL문을 붙여넣으시면 됩니다.

mysql > insert into temporary_table (id, first_name, last_name, email, gender, ip_address) values (1, 'Ruperta', 'Andreazzi', 'randreazzi0@tinyurl.com', 'Female', '111.8.167.25');
mysql > insert into temporary_table (id, first_name, last_name, email, gender, ip_address) values (2, 'Heddie', 'Pringley', 'hpringley1@chronoengine.com', 'Polygender', '42.121.208.48');
mysql > insert into temporary_table (id, first_name, last_name, email, gender, ip_address) values (3, 'Minny', 'Musslewhite', 'mmusslewhite2@state.tx.us', 'Female', '165.94.68.36');
mysql > insert into temporary_table (id, first_name, last_name, email, gender, ip_address) values (4, 'Whit', 'Wayvill', 'wwayvill3@arstechnica.com', 'Male', '24.75.41.168');

 

생성한 테이블들의 목록과 테이블의 스키마를 확인합니다.

mysql > SHOW TABLES;
mysql > DESCRIBE '테이블 이름';

 

테이블 이름을 변경할 때는 아래 명령어를 사용합니다.

mysql > ALTER TABLE temporary_table RENAME tt;

 

테이블의 내용을 확인하기 위해서 기본적인 DML 을 사용합니다. 정상적으로 데이터베이스 내의 테이블을 조회할 수 있습니다.

mysql > SELECT * FROM tt;

 

ALTER 명령어로 테이블에 새로운 필드를 추가할 수 있습니다. 새로운 car 필드가 추가되었습니다.

mysql > ALTER TABLE tt ADD car VARCHAR(50);

 

필드명 변경을 변경합니다.

mysql > ALTER TABLE tt RENAME COLUMN car TO own_car;

 

 

own_car 필드를 삭제합니다.

mysql > ALTER TABLE tt DROP own_car;

 

데이터베이스를 삭제합니다.

mysql > DROP DATABASE '데이터베이스 이름';

 

테이블을 삭제합니다. 뒤에 옵션으로 CASCADE를 사용하면 foreign 키로 연결된 모든 테이블과 함께 삭제하게 됩니다. RESTRICT 키워드를 사용하면 Foreign 키로 연결된 테이블이 있을 경우 삭제를 취소합니다.

mysql > DROP TABLE '테이블 이름';

 

테이블은 그대로 두고 튜플만 삭제합니다.

mysql > TRUNCATE TABLE tt;

 

 

MySQL Database 사용하기(DML)

 

INSERT

INSERT INTO '테이블 명' (필드명,,) VALUES(값,,) 으로 튜플을 추가할 수 있습니다.

mysql > insert into tt (id, first_name, last_name, email, gender, ip_address, car) values (74, 'Sterne', 'Gidney', 'sgidney21@china.com.cn', 'Male', '136.148.151.17', 'Ford');

 

기존 테이블의 내용을 복사해서 새로운 테이블에 삽입할 수 있습니다. 

 

먼저 tt2 테이블을 생성해주고,

mysql> CREATE TABLE tt2(
    -> id INT,
    -> first_name VARCHAR(50),
    -> last_name VARCHAR(50),
    -> email VARCHAR(50),
    -> gender VARCHAR(50),
    -> ip_address VARCHAR(20),
    -> car VARCHAR(50)
    -> );

 

tt 테이블의 내용을 복사해서 tt2로 INSERT해줍니다.

mysql> INSERT INTO tt2(id, first_name, last_name, email, gender, ip_address, car) SELECT * FROM tt;

 

 

UPDATE

특정 조건을 만족하는 필드의 값을 변경할 수 있습니다. ip_addres와 car 필드의 조건을 만족하는 튜플의 gender 값을 변경하는 명령어 입니다.

mysql> UPDATE tt SET gender='Bigender' WHERE ip_address='41.99.38.156' AND car='Bentley';

 

DELETE

특정 조건을 만족하는 튜플을 삭제하는 명령어 입니다. ip_address와 car 조건을 만족하는 튜플을 tt 테이블에서 삭제합니다.

mysql> DELETE FROM tt WHERE ip_address='41.99.38.156' AND car='Bentley';

 

SELECT

select 명령어는 아마 SQL에서 가장 많이 사용되는 명령입니다. 

 

DISTINCT : 중복된 튜플을 제거하기 위해서 사용하는 명령어 입니다. tt 테이블에서 중복을 제거한 gender의 갯수를 출력하는 명령어 입니다. male과 female 뿐만 아니라 다양한 성별이 존재합니다.

mysql> SELECT COUNT(DISTINCT gender) FROM tt;

 

GROUP BY : 지정된 필드 데이터를 기준으로 튜플들을 그룹화 해서 출력합니다. gender 필드를 기준으로 모든 튜플의 갯수를 출력하는 SQL 문입니다.

mysql> SELECT COUNT(*) FROM tt GROUP BY gender;

GROUP BY ~ HAVING : 그룹별 조건을 만족하는 튜플들을 조회합니다. WHERE 조건절 대신 HAVING을 사용합니다.

 

mysql> SELECT COUNT(*) FROM tt GROUP BY gender HAVING gender LIKE "%m%";

 

 

AS :  출력 결과의 값을 정의할 수 있습니다. first_name의 갯수를 'FN' 필드명으로 출력하는 명령어입니다.

mysql> SELECT COUNT(first_name) AS FN FROM tt;

 

AS문은 필드값에 연산을 한 후 출력값을 조정할 수도 있습니다.

mysql> SELECT asset*1001 AS 조정자산 FROM tt;

COUNT : 갯수
SUM() : 합계
MIN() : 최소값
MAX() : 최대값
AVG() : 평균값
VAR() : 분산
STDEV() : 표준편차

 

조건식 AND 비교

두가지 조건을 만족하는 튜플을 출력합니다. asset 필드값이 30 이상이고, gender 필드값이 female인 튜플을 출력합니다.

mysql> SELECT * FROM tt WHERE asset >=30 AND gender='female';

 

조건식 OR 비교

두 가지 조건 중 한가지만 만족해도 출력하는 명령어 입니다. asset이 30 이상이거나 gender 필드값이 'female'인 튜플을 출력합니다.

mysql> SELECT * FROM tt WHERE asset >=30 OR gender='female';

 

IS NULL vs IS NOT NULL

필드값이 비어있는 튜플을 출력하는 명령입니다.

 

mysql> SELECT first_name FROM tt3 WHERE ip_address IS NULL;

 

 

 

IS NOT NULL 명령어는 필드값이 비어있지 않은 튜플을 출력하는 명령입니다.

mysql> SELECT first_name FROM tt3 WHERE ip_address IS NOT NULL;

 

SELECT ~ LIKE 

LIKE 명령어는 문자열의 포함 여부를 가지고 튜플을 검색할 수 있는 명령입니다. car 필드값에 B가 포함된 튜플을 출력합니다.

 

mysql> SELECT * FROM tt3 WHERE car LIKE '%B%';

 

BETWEEN ~ AND

조건값이 특정 범위 사이에 존재하는 튜플을 출력하는 명령입니다.

 

mysql> SELECT * FROM tt3 WHERE asset BETWEEN 10 AND 30;

 

IN() 

필드값이 해당 조건내에 존재하는 튜플을 출력합니다. gender 필드값이 male, female, Bigender에 속하는 튜플들을 출력합니다.

mysql> SELECT * FROM tt3 WHERE gender IN('male', 'female', 'Bigender');

 

 

SubQuery 사용하기

단일 행 서브 쿼리

 

쿼리를 메인 쿼리와 서브 쿼리로 구성하여 튜플을 검색할 수 있습니다. 서브 쿼리는 반드시 ()로 감싸줘야 하며, 메인 쿼리에서 사용하는 튜플의 수와 같은 개수의 결과값을 반환하는 서브쿼리만 사용이 가능합니다.

 

mysql> SELECT * FROM tt3 WHERE last_name = (SELECT last_name FROM tt3 WHERE first_name='Nealy' AND gender='Male');

 

 

다중 행 서브 쿼리

서브 쿼리에서 반환되는 값이 복수의 값이면 다중 행 서브 쿼리를 사용합니다. IN, ANY, SOME, ALL, EXISTS 예약어와 함께 사용이 가능합니다.

 

IN 예약어를 통해 car 필드값에 'B'가 포함된 튜플의 gender를 반환한 후 여기에 속하는 튜플들의 first_name과 last_name을 출력하는 SQL 문입니다.

mysql> SELECT first_name, last_name FROM tt3 WHERE gender IN (SELECT gender FROM tt3 WHERE car LIKE "%B%");

 

 

하위 쿼리에서 메인 쿼리 조건값이 일치 하는 튜플들을 EXISTS 예약어로 출력할 수 있습니다.

mysql> SELECT first_name, last_name FROM tt3 WHERE EXISTS (SELECT * FROM tt3 WHERE car LIKE "%B%");

 

 

정렬

오름차순과 내림차순으로 출력값을 정렬할 수 있습니다. 1개 이상의 조건을 추가해서 동일한 순위일 경우 추가 정렬할 다중 정렬출력도 가능합니다.

 

// 오름차순 정렬
mysql> SELECT * FROM tt3 ORDER BY asset ASC;

// 내림차순 정렬
mysql> SELECT * FROM tt3 ORDER BY asset DESC;

// 다중 정렬
mysql> SELECT * FROM tt3 ORDER BY asset DESC, first_name ASC;

 

GROUP BY

필드값을 그룹별로 묶어 요약된 정보를 출력할 수 있습니다.

 

mysql> SELECT COUNT(*) FROM tt3 GROUP BY gender;

HAVING 예약어를 통해 그룹화된 튜플들의 출력 조건값을 설정할 수 있습니다. 그룹화된 튜플들의 SUM() 합계가 100 이상인 요약 정보를 출력합니다.

 

mysql> SELECT COUNT(*) FROM tt3 GROUP BY gender HAVING SUM(asset)>100;