MySQL

MySQL 기초

시작

데이터베이스 생성

CREATE DATABASE dbname;

데이터베이스 제거

DROP DATABASE dbname;

데이터베이스 목록 보기

SHOW DATABASES;

데이터베이스 선택

USE dbname;

utf8mb4 설정 데이터베이스 생성

CREATE DATABASE dbname DEFAULT CHARACTER SET utf8mb4;

---

User

유저 목록 보기

SELECT host, user FROM USER;

유저 생성(내부, 외부)

CREATE USER 'theo'@'localhost' identified by '1234';
CREATE USER 'theo'@'%' identified by '1234';

유저의 접근 권한 조회

SHOW GRANTS FOR 'theo'@'localhost';

유저에게 접근 권한 전권 부여

GRANT ALL ON dbname.* to 'theo'@'localhost';

유저로부터 특정 데이터베이스의 모든 권한 삭제

REVOKE ALL ON dbname.* from 'theo'@'localhost';

---

TABLE

테이블 생성하기

CREATE TABLE tbname
(
	columnname datatype,
    columnname datatype
    PRIMARY KEY (columnname)
);

PRIMARY KEY 추가로 지정

ALTER TABLE tbname
ADD PRIMARY KEY (columnname, columnname, ...)

테이블 목록 보기

SHOW TABLES;

특정 테이블 정보 보기

DESC tbname;

테이블명 변경하기

ALTER TABLE tbname RENAME new_tbname;

테이블에 컬럼 추가하기

ALTER TABLE tbname ADD COLUMN columnname datatype;

특정 컬럼 데이터 타입 변경

ALTER TABLE tbname MODIFY COLUMN columnname new_datatype;

특정 컬럼명 변경

ALTER TABLE tbname CHANGE COLUMN columnname new_columnname datatype;

특정 컬럼 삭제

ALTER TABLE tbname DROP COLUMN columnname;

테이블 삭제

DROP TABLE tbname;

---

테이블에 데이터 추가(일부 컬럼 선택 가능)

INSERT INTO tbname (colname1, colname2, colname3, colname4)
VALUES (data1, data2, data3, data4);

테이블에 데이터 추가(전체 컬럼)

INSERT INTO tbname
VALUES (data1, data2, data3, data4)

테이블 내 전체 데이터 조회

SELECT * FROM tbname;

테이블 내 특정 컬럼 조회

SELECT colname1, colname2 FROM tbname;

테이블 내 특정 조건에 부합하는 데이터 조회

SELECT * FROM tbname WHERE colname=condition;

---

데이터 수정

UPDATE person SET age=23 WHERE name='이효리';

데이터 삭제

DELETE FROM person WHERE name='이상순';

---

ORDER BY

SELECT AGE, NAME
FROM celeb
ORDER BY AGE DESC, NAME ASC;

---

비교 연산자

비교 연산자 활용

SELECT NAME, AGE
FROM celeb
WHERE AGE > 29
ORDER BY AGE;

---

논리 연산자

논리 연산자 활용

SELECT * FROM celeb
WHERE (NOT JOB_TITLE='가수' AND SEX='F') OR
(NOT AGE < 40 AND ID % 2 = 1);

---

UNION / UNION ALL

SELECT * FROM CELEB WHERE SEX='F'
UNION -- 은 중복 제거 & UNION ALL은 중복 포함
SELECT * FROM CELEB WHERE JOB_TITLE='가수';

---

JOIN

INNER JOIN

SELECT column1, column2, ...
FROM tableA
INNER JOIN tableB
ON tableA.column = tableB.column
WHERE condition;

LEFT JOIN

SELECT column1, column2, ...
FROM tableA
LEFT JOIN tableB
ON tableA.column = tableB.column
WHERE condition;

FULL OUTER JOIN

MySQL에서는 FULL JOIN을 지원하지 않으므로 LEFT JOIN과 RIGHT JOIN의 UNION을 통해 실행할 수 있다.

SELECT column1, column2, ...
FROM tableA
LEFT JOIN tableB
ON tableA.column = tableB.column
WHERE condition
UNION
SELECT column1, column2, ...
FROM tableA
RIGHT JOIN tableB
ON tableA.column = tableB.column
WHERE condition; 

SELF JOIN

SELECT column1, column2, ...
FROM tableA, tableB
WHERE tableA.column = tableB.column

---

CONCAT

SELECT CONCAT('이름: ', name) FROM celeb;

ALIAS

SELECT name AS '이름', agency as '소속사' from celeb;

SELECT CONCAT(name, ': ', job_title) AS PROFILE FROM celeb;

SELECT s.season, s.episode, c.name, c.job_title
FROM celeb as c, snl_show as s
WHERE c.name = s.host;

SELECT CONCAT(s.season, '-', s.episode, '(',s.broadcast_date, ')') AS '방송정보',
       CONCAT(c.name, '(', c.job_title, ')') AS '출연자정보'
FROM celeb AS c, snl_show AS s
WHERE c.name = s.host;

DISTINCT: 중복 제외

SELECT DISTINCT agency FROM celeb;

LIMIT: SELECT 데이터 개수 설정(상위 n개)

SELECT * FROM celeb ORDER BY age LIMIT 4;

---

Aggregate Function(집계함수)

---

Scalar Function

---

Subquery

Scalar Subquery(SELECT 절에 사용)

SELECT case_number,
	(SELECT avg(case_number)
    FROM crime_status
    WHERE crime_type LIKE '강도' AND status_type LIKE '검거') avg
FROM crime_status
WHERE police_station LIKE '은평' AND crime_type LIKE '강도' AND status_type LIKE '검거';

Inline View(FROM 절에 사용)

SELECT c.police_station, c.crime_type, c.case_number
FROM crime_status c,
	(SELECT police_station, max(case_number) count
    FROM crime_status
    WHERE status_type LIKE '발생'
    GROUP BY police_station) m
WHERE c.police_station = m.police_station
	AND c.case_number = m.count;

Nested Subquery(WHERE 절에 사용)

• Single Row Subquery(비교 연산자랑 사용)

SELECT name FROM celeb WHERE name = (SELECT host FROM snl_show WHERE id = 1);

• Multiple Row Subquery(IN)

SELECT host
FROM snl_show
WHERE host IN (SELECT name
			   FROM celeb
               WHERE job_title LIKE '%영화배우%');

• Multiple Row Subquery(EXISTS)

SELECT name
FROM police_station p
WHERE EXISTS (SELECT police_station
			  FROM crime_status c
              WHERE p.name = c.reference and case_number > 2000);

• Multiple Row Subquery(ANY)

SELECT name
FROM celeb
WHERE name = ANY (SELECT host
                  FROM snl_show);

• Multiple Row Subquery(ALL)

SELECT name
FROM celeb
WHERE name = ALL (SELECT host
                  FROM snl_show
                  WHERE id = 1);

• Multi Column Subquery

SELECT name, sex, agency
FROM celeb
WHERE (sex, agency) IN (SELECT sex, agency FROM celeb WHERE name = '강동원');