MySQL SQL문 기본 : CRUD, 연산자, 서브쿼리, etc...

SQL문 정리

 

DB server(mySQL) ⊃ schema(DB) ⊃ table

 

 

Quick guide

 

⬤ DB

CREATE DATABASE [DB 명];
SHOW DATABASES;
USE [DB 명] => SELECT DATABASE();로 현재 사용 중인 DB 확인 가능

DROP DATABASE [DB 명];

 

⬤ table
CREATE [테이블 명] ([column명] [data type, etc...])
SHOW TABLES;
DESC [테이블 명]

(describe [테이블 명] 과 같다. 테이블에 어떤 열이 있는지는 desc 문을 쓰는 것이 좋다.);

DROP TABLE [TABLE NAME];

 

⬤ CRUD

INSERT INTO [테이블 명](컬럼 명) VALUES (넣을 값);

 

SELECT [원하는 컬럼] FROM [테이블 명] WHERE [조건]...

 

UPDATE [테이블 명] SET [바꿀 컬럼 = 바꿀내용] WHERE [조건]

(where를 안 쓰면 테이블 내용 전체가 바뀌니 조심해야 한다!)

 

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

(where를 안 쓰면 테이블 내용 전체가 바뀌니 조심해야 한다!)

 

TRUNCATE TABLE [테이블 명];

(delete는 롤백이 가능하지만 truncate는 완전 삭제함)

 

 

Examples

 

다음 쿼리들을 살펴보고 무슨 동작을 할지 말할 수 있다면 기본적인 sql을 다룰 수 있다고 할 수 있다.

 

where 과 간단한 연산자

select * from usertbl where birthYear > 1970 and height > 180

select * from usertbl where addr in ("서울", "경기");

select * from member where seq < 3;
select * from member where seq=3 or name='admin';
select * from member where seq in (1, 2, 5);
select * from member where name like 'a%';
select * from member where seq=(case when 1=1 then 1 else 2 end);

// mysql엔 공백이 연결 연산자긴한데 혼란을 주니 사용 ㄴㄴ. concat을 쓰세요
select * from member where name=concat('ad','min'); 

 

 

like

 

%는 무엇이든 올 수 있고, 길이 제한 도 없음

_는 무엇이든 올 수 있지만 1자 길이 제한

www.w3schools.com/sql/sql_like.asp

select * from usertbl where name like '김%';

select * from usertbl where name like '_범수';

SELECT * FROM member WHERE name LIKE '홍%';

name이 '홍'으로 시작하는 것

SELECT * FROM member WHERE name LIKE '%길동';

name이 '길동'으로 끝나는 것

SELECT * FROM member WHERE name LIKE '%길동%';

name에 '길동'이 포함되는 것을 검색 ('길동'만 있어도 잡힘)

 

 

서브쿼리와 all, any, some

 

서브쿼리도 위치에 따라 이름이 달라진다고 한다.

https://www.inflearn.com/course/SQL-인젝션-공격-기본-문법

 

select하는 column에 쿼리를 넣는, 스칼라 서브쿼리는 다음과 같이 사용될 수 있다.

// 스칼라 서브쿼리. name, 'mysql version' 컬럼이 나온다.
select name, (select version()) as 'mysql version' from member;

// 같은 테이블을 재사용시 별칭을 줄 수 있음. 여기서는 a라 주었음.
select name, (select email from member where seq=a.seq) from member a;

// 인라인 뷰. 서브쿼리의 결과물의 별칭을 a라 하고, 그 이후에 sql이 평가됨.
select * from (select * from member)a where a.seq = 1;

// 조관우의 키보다 키 큰 사람 테이블 레코드 가져와
select * from usertbl where height > (select height from usertbl where name="조관우")

// 주소가 서울인 사람들의 키 중 가장 작은 사람의 키보다 큰 사람의 테이블 레코드 가져와
select * from usertbl where height > any (select height from usertbl where addr = "서울")

 

 

order by (정렬)

 

select name, mDate from usertbl order by mDate;
select name, mDate from usertbl order by mDate desc;

select name, height, mDate from usertbl order by height desc, mDate asc;

 

distinct

 

select distinct addr from usertbl;

 

limit

 

select * from usertbl limit 3;
select * from usertbl limit 1, 3; // offset: 1, limit: 3 이므로 1, 2, 3 출력함

 

table 복사

 

create table buytbl2 (select * from buytbl); -- 값 복사는 되지만 PK, FK 등 조건은 복사되지 않음
create table buytbl3 (select userID, prodName from buytbl);

 

groupby와 집계함수(avg, min, max, count, count(distinct), stdev, var_samp, sum 이 주로 사용됨)

 

select userID, sum(amount) from buytbl group by userID;
select userID, sum(amount) as 'total amount' from buytbl group by userID;

select userID, sum(amount * price) as 'purchase price' from buytbl group by userID;

select avg(amount) as '평균 구매 갯수' from buytbl;
select userID, avg(amount) as '평균 구매 갯수' from buytbl group by userID;

 

 

having 절. HAVING절은 반드시 GROUP BY 절 다음에 와야 한다.

 

이걸 사용하는 이유는 집계 함수에서는 where를 쓰면 invalid한 group function이라는 에러가 나오기 때문. 

SELECT 
    userID, SUM(price * amount) AS '총구매액'
FROM
    buytbl
GROUP BY userID
HAVING SUM(price * amount) > 1000;

 

rollup 절. 일종의 중간 집계이다. GROUP BY 절과 함께 사용되어야 한다.

중간 합계와 총합계를 내는 데 사용된다.

SELECT 
    num, groupName, SUM(price * amount) AS '비용'
FROM
    buytbl
GROUP BY groupName , num
WITH ROLLUP;

 

select의 옵션들이 많아서 다 외우지는 못하지만 적어도 아래 순서로 진행되는 것은 외워둬야 한다.

억지로 외우기보다 쓰다보면 외워진다.

SELECT 
  Column
  FROM => 어느 테이블?
  WHERE => 조건, 서브 쿼리가 들어갈 수 있음.
  GROUP BY => 그룹화
  HAVING => 집계함수의 조건
  ORDER BY => 정렬이니 당연히 마지막에 써줘야

 

insert

-- 일반적인 insert
insert testtbl1 values (1, '카뮈', 23);

-- 여러값 넣기
insert testtbl2 value (null, '하니', 28), (null, '광훈', 31), (null, '대런', 25);

-- column을 명시해서 넣을 수 있음
insert testtbl1(id, userName) values (2, '이하이') -- 적지 않은 컬럼은 자동으로 null이 들어감

-- id에 auto_increment를 설정했다면 null값을 넘겨야 함
insert testtbl2 value (null, '지민', 25);

-- 마지막으로 들어간 auto_increment값을 알고 싶다면
select last_insert_id();

-- 테이블의 auto_increment를 100부터 시작하게 하고 싶다면
alter table testtbl2 auto_increment=100;

-- 자동 증가량을 3으로 늘리고 싶다면
set @@auto_increment_increment = 3;

-- insert [테이블] select [컬럼] from [특정 DB의 특정 테이블] 꼴로 다른 테이블 데이터 가져오기 가능
insert into testtbl4 select emp_no, first_name, last_name from employees.employees;

 

insert + ignore/on duplicate

-- 시도해보고 에러가 나면 무시하라
insert ignore membertbl values(2, "안녕", 35);

-- 시도해보고 키가 중복된다면 후술할 내용으로 update하라
insert membertbl values(2, '안녕', 35) on duplicate key update name='안녕', age=35; 

 

 

update

-- update [테이블] set [바꿀 내용] where [조건]
update testtbl4 set Lname='없음' where Fname = 'Georgi';

-- where 없이 update를 쓴다면 해당 테이블 전체 컬럼을 변경하게 되므로 주의!
update buytbl set price = price * 1.5;

 

delete, drop, truncate

-- 특정 행 제거
delete from testtbl4 where Lname='없음';
delete from testtbl4 where Lname='Aamer' limit 5;

 

테이블 자체를 삭제하고 싶다면 아래와 같이 delete, drop, truncate를 사용할 수 있다.

delete from bigtbl1;
drop table bigtbl2;
truncate table bigtbl3;

 

쿼리 실행 결과 위와 같은 속도 차이가 존재합니다. 일반적으로 drop과 truncate가 더 빠릅니다.

 

delete는 행을 하나하나 삭제하는 방법이고,

drop은 통째로 테이블 구조를 날려버립니다,

truncate는 delete와 동작이 같지만 롤백이 불가능한 대신 빠르게 삭제됩니다.

 

 

---

 

상세한 설명

 

 

LIKE 질의

 

_는 한 문자, %는 다수의 문자열을 의미한다.

예시에서 KO_는 ko + 문자열 한개를 의미하고, TEL %은 TEL로 시작하는 문자열을 말한다.

 

SELECT * FROM city WHERE CountryCode LIKE "KO_"
SELECT * FROM city WHERE NAME LIKE 'TEL %'

 

서브 쿼리와 any, some, all

 

쿼리 안의 쿼리를 말함.

CountryCode를 특정해서 가져오고 싶은데 기억이 안날 때,

city 테이블에서 NAME이 "Seoul"인 행의 CountryCode를 추출해서 곧바로 사용할 수 있게 된다.

SELECT * FROM city WHERE CountryCode = (SELECT CountryCode FROM city WHERE NAME = "Seoul")

 

any, some

 

다음 쿼리는 SELECT population FROM city WHERE district = "New York"의 결과물은 district가 뉴욕이기만 하면 population을 가져오는데, 결과물을 보면 값이 여러개다.

이 경우 ANY를 써서 해당 값 중 어느 것이라도 해당하기만 하면 불러오도록 할 수 있다. SOME도 같은 결과를 낸다.

위의 결과 중 가장 작은 값(93,994) 보다 크기만 하면 조건에 만족하는 것으로 쿼리를 짜보았다.

SELECT * FROM city WHERE Population > ANY (SELECT population FROM city WHERE district = "New York")

 

all

 

all의 경우 주어진 값 중 가장 높은 값을 의미한다.

 

 

아래 쿼리는 8,008,278보다 인구 수가 많은 행만 가져오도록 처리했다.

SELECT * FROM city WHERE Population > ALL (SELECT population FROM city WHERE district = "New York")

 

 

ORDER BY

 

쉽게 말해 정렬이다. 알다시피 정렬은 값비싼 동작이므로 꼭 필요하지 않다면 쓰지 않는 것이 좋다.

인구 수를 내림차순(DESC)으로 가져오도록 해보았다.

SELECT * FROM city ORDER BY Population DESC

ORDER BY에 인자를 여러 개 줄 수도 있는데, CountryCode를 오름차순으로 먼저 정렬한 다음 해당 묶음 별로 인구수를 내림차순으로 정렬하도록 만들어줄 수 있다.

SELECT * FROM city ORDER BY CountryCode ASC, Population DESC

다른 조건과 혼용하면 다음과 같이 사용할 수 있다.

SELECT * FROM city WHERE CountryCode Like 'KO_' ORDER BY population desc

 

DISTINCT / LIMIT

 

distinct 중복된 것을 제거해준다.

SELECT DISTINCT CountryCode FROM city

 

limit 갯수 제한을 제한해서 가져온다. 

만약 데이터가 수백만개라면 select * 를 날리면 무리가 올 것이다. 빠른 쿼리 결과를 반환받기 위해 LIMIT을 걸어두는 것이 좋다.

SELECT * FROM city
ORDER BY population DESC
LIMIT 10

 

 

Group by와 집계함수 + HAVING/ROLLUP

 

그룹으로 묶어주는 함수. 집계(aggregate) 함수와 함께 사용함.

집계 함수로는 AVG, MIN, MAX, COUNT, COUNT(DISTINCT xxx), STDEV, VARIANCE 등이 존재한다.

STDEV는 표준 편차, VARIANCE는 분산.

 

CountryCode로 그룹화하되 그 중 CountryCode와 가장 큰 population만 보여달라는 것이다.

SELECT CountryCode, MAX(population)
FROM city
GROUP BY CountryCode

 

as를 붙이면 컬럼 명을 변경할 수도 있다.

SELECT CountryCode, AVG(population) AS 'avgPop'
FROM city
GROUP BY CountryCode

 

having 절

 

* group by는 having 절을 겸할 수 있다. WHERE과 비슷하게 조건을 제한하지만 집계 함수에 대해서 조건을 제한하는 개념이다. HAVING절은 반드시 GROUP BY 절 다음에 와야 한다.

 

CountryCode로 그룹 바이를 하되 avgPop이 800000 이상인 것만 

SELECT CountryCode, AVG(population) AS 'avgPop'
FROM city
GROUP BY CountryCode
HAVING avgPop > 800000

 

* rollup은 일종의 중간 집계이다. GROUP BY 절과 함께 사용되어야 한다.

 

SELECT CountryCode, NAME, SUM(population) AS 'SumPop'
FROM city
GROUP BY CountryCode, NAME WITH rollup

SUM 집계 함수를 내되, 곧장 합계를 추출하는 것이 아니라 NAME 별로 어떤 값을 가지고 있고, 그 결과 어떻게 SUM(population)이 도출되었는지 확인할 수 있다.

 

 

JOIN

 

여러 테이블을 join하는데 사용된다.

 

다음과 같은 두 테이블이 있다고 가정하자.

 

city의 CountryCode와 country의 Code가 같으므로 이 컬럼을 이용해 Join해보겠습니다.

city 테이블의 입장에서 country 테이블을 JOIN 합니다.

SELECT * FROM city
JOIN country ON city.CountryCode = country.Code

 

두 테이블이 합쳐진 것을 볼 수 있습니다.

 

만약 2개 이상의 여러 테이블을 JOIN하고 싶다면 어떻게 하면 될까요?

다음과 같이 짜면 됩니다.

 

SELECT * FROM city
JOIN country ON city.CountryCode = country.Code
JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode

 

테이블 복사하기

 

city 테이블과 똑같은 내용으로 테이블을 신설합니다.

CREATE TABLE city2 AS SELECT * FROM city;

 

insert를 통해 내용물을 통째로 넣어주는 방식도 있습니다.

INSERT test2 SELECT * FROM test;

 

ALTER

 

test2 테이블을 수정(ALTER)합니다. 여기서는 col4 열을 추가했습니다.

 

ADD

ALTER TABLE test2 ADD col4 INT NULL

 

MODIFY

수정할 수 있습니다. 여기서는 col4의 자료형을 VARCHAR로 교체했습니다.

ALTER TABLE test2 MODIFY col4 VARCHAR(20) NULL,

DROP

삭제합니다.

ALTER TABLE test2 DROP col4

 

 

---

저는  DB 자체가 처음인데요?

 

DB(스키마)

 

DB(스키마)를 만들기 위해서는 

CREATE DATABASE 원하는 이름;

🛵 ';' 꼭 적을 것 🏍

이렇게 되면 테이블의 모음인 DB(스키마)를 만들게 됩. 이 스키마 안에 원하는 테이블을 만들면 된다.

 

무슨 DB가 있는지 보고 싶으면

SHOW DATABASES;

SHOW DATABASES;

무슨 DB(schema)가 있는지 보여줌.

여기서 특정 DB를 사용하고 싶다면

USE 쓰려는 DB의 이름

USE opentutorials

USE opentutorials

 

 

테이블

 

여기에 사용할 DB를 선택했다면 테이블을 만들어 보자.

참고로 엔터만 치면 알아보기 쉽게 다음 줄로 넘어가되 코드는 연속된다. (->)

CREATE TABLE topic(
-> id INT(11) NOT NULL AUTO_INCREMENT,
-> title VARCHAR(100) NOT NULL,
-> description TEXT NULL,
-> created DATETIME NOT NULL,
-> author VARCHAR(30) NULL,
-> profile VARCHAR(100) NULL,
-> PRIMARY KEY(id)
);

NOT NULL은 required(입력 필수)란 의미이며 VARCHAR 옆의 숫자는 사이즈이다.

AUTO_INCREMENT 요것은 row가 늘어날 때마다 자동으로 +1 씩 하라는 의미이다.

datatype은 알아서 찾아보자.

 

한편, DB에서 만들어 놓은 테이블을 보고 싶다면

SHOW TABLES;

 

이 테이블의 세부사항을 보고 싶다면

DESC (테이블 명)

 

INSERT INTO [테이블 명](컬럼 명) VALUES (넣을 값);

SELECT [원하는 컬럼] FROM [테이블 명] WHERE [조건]...

UPDATE [테이블 명] SET [바꿀 컬럼 = 바꿀내용] WHERE [조건]

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

CRUD

 

자, 테이블도 만들었겠다. CRUD를 해봅니다.

1. create (INSERT)

 

테이블에 row 하나를 추가해보자.

 

INSERT INTO topic(컬럼 명) VALUES (넣을 값);

 

INSERT INTO topic (title, description, created, author, profile) VALUES('MySQL', 'MySQL is ...', NOW(), 'egoing', 'developer');

 

2. Read (SELECT 문을 잘 써라)

 

SELECT [원하는 컬럼] FROM [테이블 명] WHERE [조건]...

 

SELECT * FROM topic

SELECT id, title, created, author FROM topic;
SELECT id, title FROM topic WHERE author="egoing";
SELECT * FROM topic WHERE author="egoing" ORDER BY id DESC;
SELECT * FROM topic WHERE author="egoing" ORDER BY id DESC LIMIT 2;

 

BETWEEN A AND B (A이상 B이하의 값) : 연속적 데이터. 숫자형 데이터에서 사용 가능

FIELD IN (A, B, C) : 특정 FIELD가 A, B, C인 것들 (단일한 특정 값이면 = 을 사용하면 된다)

SELECT * FROM city WHERE population > 7000000
SELECT * FROM city WHERE CountryCode IN("KOR", "USA", "JPN")

 

3. Update

 

UPDATE [테이블 명] SET [바꿀 컬럼 = 바꿀내용] WHERE [조건]

UPDATE topic
SET description = 'Oracle is DBMS', title = 'ORACLE'
WHERE id=2;

 

참고로 SELECT던 UPDATE던 문서를 확인해라. 순서를 알려준다.

(https://dev.mysql.com/doc/refman/8.0/en/update.html)

 

여기를 보면 [] 안에 든 것은 생략할 수 있으며 순서가 제시된 대로 작성만 하면 SQL문을 작성할 수 있다.

 

4. DELETE

 

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

 

문서를 참고하자면, (https://dev.mysql.com/doc/refman/8.0/en/delete.html)

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]

[PARTITION (partition_name [, partition_name] ...)]

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

 

DELETE FROM topic
WHERE id=5;

 

 

CURD는 끝났고 그 외의 것.

 

table의 이름을 바꾸고 싶다면

RENAME TABLE (기존 테이블 명) TO (원하는 테이블 명);

 

테이블의 칼럼의 형태를 확인하고 싶다면

DESC (테이블 명)

 

JOIN하고 싶다면

SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;

 

전체가 아닌 특정 열만 JOIN하고 싶다면

 

SELECT topic.id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;

SELECT topic.id, title, description, created, name, profile
FROM topic
LEFT JOIN author ON topic.author_id = author.id;

 

여기서 name, profile은 author 테이블의 것이지만 알아서 다 넣으니 걱정 말라.

 

문제는 topic.id인데 이는 author에도 topic에도 둘 다 id가 있기 때문에 어느 쪽의 id인지를 표시해줘야 한다는 것이다. 이럴 때는 topic.id와 같이 어디 테이블 소속인지 표시해주면 된다.

 

한편, topic.id를 다른 이름으로 보여주고 싶을 때가 있을 터인데 그 경우에는 다음과 같이 한다.

 

SELECT topic.id AS topic_id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;

SELECT topic.id AS topic_id, title, description, created, name, profile
FROM topic
LEFT JOIN author ON topic.author_id = author.id;