본격적으로 MySQL 문법을 익혀보자.
•
명령어는 대문자로 쓰는 것이 컨벤션이나 소문자로 써도 실행에는 문제가 없음
•
마지막에 세미콜론(;) 또한 쓰지 않아도 (특히 workbench 등 GUI환경에서는) 실행에는 문제가 없음
1. CREATE
1) Create Database
-- Database 생성
CREATE DATABASE <db_name>;
-- Database 선택
USE <db_name>;
-- 현재 Database 확인
SELECT DATABASE();
SQL
복사
2) Create Table
•
Table을 생성할 때에는 컬럼이름, 데이터타입, 제약조건에 대한 내용이 들어감
-- Table 생성
CREATE TABLE <table_name>(
column_name_1 column_datatype_1 column_constraint_1,
column_name_2 column_datatype_2 column_constraint_2,
...
)
-- 제약조건이 없는 user1 Table 생성
CREATE TABLE user1(
user_id INT,
name Varchar(20),
email Varchar(30),
age INT(3),
rdate DATE
)
-- 제약조건이 있는 user2 Table 생성
CREATE TABLE user2(
user_id INT PRIMARY KEY AUTO_INCREMENT,
name Varchar(20) NOT NULL,
email Varchar(30) UNIQUE NOT NULL,
age INT(3) DEFAULT '30',
rdate TIMESTAMP
)
SQL
복사
2. ALTER
1) Database
•
ALTER를 이용하여 database encoding 변경
-- 현재 문자열 인코딩 확인
show variables like "character_set_database";
-- test 데이터베이스의 문자열 인코딩을 utf8로 변경
ALTER DATABASE test CHARACTER SET = utf8;
ALTER DATABASE test CHARACTER SET = ascii;
-- 문자열 encoding 변경 결과 확인
show variables like "character_set_database";
SQL
복사
2) Table
•
ALTER를 이용하여 table의 column 추가, 삭제, 수정
-- ADD: user2 테이블에 TEXT 데이터 타입을 갖는 tmp column 추가
ALTER TABLE user2 ADD tmp TEXT;
-- MODIFY: user2 테이블에 tmp column을 INT(3) 데이터 타입으로 수정
ALTER TABLE user2 MODIFY COLUMN tmp INT(3);
-- DROP: user2 테이블의 tmp column을 삭제
ALTER TABLE user2 DROP tmp;
SQL
복사
3. DROP
-- Database 삭제
DROP DATABASE <db_name>;
-- Table 삭제
DROP TABLE <table_name>;
SQL
복사
4. INSERT
1) Syntax
-- Table 뒤에 오는 column이름은 생략 가능, 대신 VALUES 뒤에 value값이 순서대로 와야 함
INSERT INTO <table_name>(<column_name_1>, <column_name_2>, ...)
VALUES(<value_1>, <value_2> ...)
-- 여러 row를 한번에 insert 하기
INSERT INTO <table_name>(<column_name_1>, <column_name_2>, ...)
VALUES(<value_1>, <value_2> ...),
(<value_1>, <value_2> ...),
...
(<value_1>, <value_2> ...);
SQL
복사
2) Example
-- user1 테이블에 user_id, name, email, age, rdate를 입력
INSERT INTO user1(user_id, name, email, age, rdate)
VALUES(1, "jane", "jane@gmail.com", 30, now()),
(2, "peter", "peter@gmail.com", 33, '2017-02-20'),
(3, "alice", "alice@gmail.com", 23, '2018-01-15'),
(4, "po", "po@gmail.com", 43, '2002-09-16'),
(5, "andy", "andy@gmail.com", 17, '2016-04-28'),
(6, "jane", "jane1234@gmail.com", 33, '2013-09-02');
SQL
복사
5. SELECT
1) Basic
-- basic syntax
SELECT <column_name_1>, <column)name_2>, ...
FROM <table_name>
-- 전체 컬럼 데이터 조회하기 (table 전체)
SELECT *
FROM user1
-- 일부 컬럼 조회하기
SELECT user_id, name, rdate
FROM user1
SQL
복사
2) ALIAS
•
alias(as)를 이용하여 컬럼명을 변경할 수 있음. as는 생략 가능
SELECT user_id AS "아이디", name AS "이름", rdate AS "등록일"
FROM user1
SQL
복사
3) DISTINCT
•
DISTINCT를 이용하여 특정 컬럼의 중복 데이터를 제거 가능
-- name 컬럼을 중복 제거하여 조회
SELECT DISTINCT(name)
FROM user1
SQL
복사
4) WHERE
•
WHERE절을 이용해서 검색 조건을 추가
•
AND, OR, 연산자 등의 기능을 사용
-- 나이가 30살 이상인 user를 조회
SELECT *
FROM user1
WHERE age >= 30
-- 등록일이 2016-01-01일 이후의 user를 조회
SELECT *
FROM user1
WHERE rdate >= "2016-01-01"
-- 등록일이 2010-01-01에서 2017-12-31 사이인 user를 조회
-- (AND는 둘다 true이면 true, OR도 사용가능)
SELECT *
FROM user1
WHERE rdate >= "2010-01-01" AND rdate <= "2017-12-13"
-- BETWEEN을 사용하여 기간 조회 (숫자데이터도 사용 가능)
SELECT *
FROM user1
WHERE rdate BETWEEN "2010-01-01" AND "2017-12-13"
SQL
복사
5) ORDER BY
•
ORDER BY를 이용하여 특정 컬럼으로 데이터 정렬 가능
-- age로 오름차순 정렬 (ASC는 생략이 가능)
SELECT *
FROM user1
ORDER BY age ASC
-- age로 내림차순 정렬
SELECT *
FROM user1
ORDER BY age DESC
-- age로 내림차순 정렬하고 rdate를 오름차순 정렬
SELECT *
FROM user1
ORDER BY age DESC, rdate
SQL
복사
6) CONCAT
•
CONCAT을 사용하여 SELECT한 데이터를 합쳐서 새로운 컬럼으로 보여줄 수 있음
-- name과 age를 같이 보여주도록 조회
SELECT email, CONCAT(name, "(", age, ")") AS "name_age"
FROM user1
SQL
복사
7) LIKE
•
LIKE를 이용하여 특정 문자열이 들어간 데이터 조회 가능
•
%는 '어떤 문자나'와 같은 의미로 "%gmail" 은 gmail 문자열 앞에 아무 문자나 올수 있음을 의미
•
NOT LIKE를 사용하여 특정 문자가 들어가지 않는 데이터 조회 가능
-- email에 gmail이 들어간 데이터 조회
SELECT *
FROM user1
WHERE email LIKE "%@gmail%"
-- email에 gmail이 들어가지 않는 데이터 조회
SELECT *
FROM user1
WHERE email NOT LIKE "%@gmail%"
SQL
복사
8) IN
•
여러개의 조건을 만족하는 데이터를 조회하고 싶을때 사용
WHERE 절의 조건을 여러개 사용하는 것을 간단하게 만들 수 있음
-- name이 peter 혹은 alice인 데이터 조회
SELECT *
FROM user1
WHERE name="peter" OR name="alice"
-- name이 peter 혹은 alice인 데이터 조회
SELECT *
FROM user1
WHERE name IN ("peter", "alice")
SQL
복사
9) LIMIT
•
LIMIT은 조회하는 데이터의 수를 제한할 때 사용
•
데이터가 너무 많은 경우에는 항상 limit을 사용하여 적은 데이터를 조회해야
조회 시간이나 업데이트시의 실수를 줄일수 있음
-- user1 테이블에서 3개의 데이터 조회
SELECT *
FROM user1
LIMIT 3
-- user1 테이블에서 두번째에서 네번째까지의 3개의 데이터 조회
SELECT *
FROM user1
LIMIT 1,3
SQL
복사
6. UPDATE
•
업데이트시에는 항상 SELECT-WHERE로 변경할 데이터를 확인하고 UPDATE 해줘야 실수를 줄일 수 있음
•
LIMIT도 함께 사용해주는 것이 좋음
-- syntax
UPDATE <table_name>
SET <column_name_1> = <value_1>, <column_name_2> = <value_2>,
WHERE <condition>
-- 예시: jane 이름을 가지고 있는 사람의 나이를 20, 이메일을 jane000@daum.net으로 변경
UPDATE user1
SET age=20, email="jane000@daum.net"
WHERE name="jane"
SQL
복사
7. DELETE
-- syntax
DELETE FROM <table_name>
WHERE <condition>
-- 예시: 2016-01-01 이전 데이터 삭제
DELETE FROM user1
WHERE rdate < "2016-01-01"
SQL
복사
8. GROUP BY
•
GROUP BY는 여러개의 동일한 데이터를 가지는 특정 컬럼을 합쳐주는 역할을 하는 명령
•
sql의 그룹함수: count, min, max, sum, avg
-- world 데이터 베이스로 이동
-- world db는 city, country, countrylanguage 테이블이 있는 데이터 베이스
use world
SQL
복사
1) GROUP BY 함수
(1) COUNT
-- city 테이블의 CountryCode를 묶고 각 코드마다 몇개의 데이터가 있는지 확인
SELECT CountryCode, COUNT(CountryCode)
FROM city
GROUP BY CountryCode
-- countrylanguage 테이블에서 전체 언어가 몇개 있는지 구하시오
SELECT COUNT(DISTINCT(Language)) AS language_count
FROM countrylanguage
SQL
복사
(2) MAX
-- 대륙별 인구수와 GNP 최대 값을 조회
SELECT continent, MAX(Population) AS Population, MAX(GNP) AS GNP
FROM country
GROUP BY continent
SQL
복사
(3) MIN
-- 대륙별 인구수와 GNP 최소 값을 조회 (GNP와 인구수가 0이 아닌 데이터 중에서)
SELECT continent, MIN(Population) AS Population, MIN(GNP) AS GNP
FROM country
WHERE GNP != 0 AND Population != 0
GROUP BY continent
SQL
복사
(4) SUM
-- 대륙별 총 인구수와 총 GNP
SELECT continent, SUM(Population) AS Population, SUM(GNP) AS GNP
FROM country
WHERE GNP != 0 AND Population != 0
GROUP BY continent
SQL
복사
(5) AVG
-- 대륙별 평균 인구수와 평균 GNP 결과를 인구수로 내림차순 정렬
SELECT continent, AVG(Population) AS Population, AVG(GNP) AS GNP
FROM country
WHERE GNP != 0 AND Population != 0
GROUP BY continent
ORDER BY Population DESCs
SQL
복사
2) HAVING
•
GROUP BY에서 반환되는 결과에 조건을 줄 수 있음
-- 대륙별 전체인구를 구하고 5억이상인 대륙만 조회
SELECT continent, SUM(Population) AS Population
FROM country
GROUP BY continent
HAVING Population > 500000000
-- 대륙별 평균 인구수, 평균 GNP, 1인당 GNP한 결과에서 1인당 GNP가 0.01 이상인 데이터를 조회하고 1인당 GNP를 내림차순으로 정렬
SELECT continent, AVG(Population) AS Population, AVG(GNP) AS GNP,
AVG(GNP) / AVG(Population) * 1000 AS AVG
FROM country
WHERE GNP != 0 AND Population != 0
GROUP BY continent
HAVING AVG > 0.01
ORDER BY AVG DESC
SQL
복사
참고 자료
•
패스트캠퍼스 '데이터 사이언스 스쿨 Python 8기' 수업자료