[MySQL Database] Table 생성,INSERT, SELECT, WHERE, UPDATE, DELETE, ORDER BY, 비교/논리 연산자, UNION, JOIN, CONCAT, ALIAS, DISTINCT, LIMIT
0
[Chapter 02.] Database 사용해보기
Database 관리
Database 조회
show databases;MySQL에 있는 Databases 목록 확인;: 즉시 실행
Database 생성
create database dbname;
Database 사용
use dbname;해당 데이터베이스로 이동(=사용)
Database 삭제
drop database dbname;
User 관리
User 조회
- 사용자 정보는 mysql에서 관리하므로 일단 mysql 데이터베이스로 이동 후 조회
use mysql;
SELECT host, user FROM user;
User 생성 - localhost
- 현재 PC에서만 접속 가능한 사용자를 비밀번호와 함께 생성
CREATE USER 'username'@'localhost' identified by 'password';
CREATE USER 'username': username이라는 USER를 생성@'localhost': 현재 PC에서만 접속 가능한 유저를 생성identified by 'passworkd': password를 입력한 사용자만 접속 가능
- 외부에서 접속 가능한 사용자를 비밀번호와 함께 생성
CREATE USER 'username'@'%' identified by 'password';
@'%': 외부에서 접속 가능한 유저를 생성
=> 이름(username)이 같지만 호스트 정보가 다르면 생성 가능
User 삭제
접근 범위에 따라 같은 이름의 사용자여도 별도로 삭제
DROP USER 'username'@'localhost'
DROP USER 'username'@'%'
User 권한 관리
실습환경 만들기1 - Database 만들기
1) 권한 관리를 실습하기 위한 Database (testdb) 생성
2) 사용자 (id: noma@localhost, password: 1234) 생성
- 사용자 정보는 mysql에서 관리하고 있기 때문에 use mysql을 (이동)해주고 유저 생성
User 권한 확인
현재 PC에서 접근 가능한 유저의 권한 확인
SHOW GRANTS FOR 'username'@'localhost';
User 권한 부여
사용자에게 특정 데이터베이스의 모든 권한 부여
GRANT ALL ON dbname.* to 'username'@'localhost';
(참고) FLUSH PRIVILEGES; 수정 내용이 적용되지 않은 경우 새로고침
User 권한 삭제
사용자에게 특정 데이터베이스의 모든 권한 삭제
REVOKE ALL ON dbname.* from 'username'@'localhost';
[실습환경 만들기]
zerobase라는 Database 생성
create database zerobase default character set utf8mb4;
default character set utf8mb4;: zerobase라는 데이터베이스는 utf8mb4(다국어+이모지문자)를 지원할 거라고 정의한 것
[Chapter 03.] Table 기초
Table 생성
- Table란? 데이터베이스 안에서 실제 데이터가 저장되는 형태, 행과 열로 구성된 데이터 모음
- Table 생성
CREATE TABLE tablename
(
columnname datatype,
columnname datatype,
...
)Table 조회
show tables;현재까지 생성된 테이블 조회
DESC tablename: 테이블 구조(정보) 조회(DESC: description)
- field: 컬럼
- type: 데이터 타입
- null: null을 허용하느냐 (yes가 디폴트 설정)
- default: 값이 비어있을 때 자동으로 채우는 값 (null이 디폴트 설정)
Table 정보 변경
- 테이블 이름 변경: rename
ALTER TABLE tablename #tablename이라는 테이블을 변경할 것이다
RENAME new_tablename #new_tablename으로 이름을 변경해라
- 테이블 컬럼 추가: add column
ALTER TABLE tablename
ADD COLUMN columnname datatype; #columnname이라는 이름의 컬럼 추가
- 테이블 컬럼의 데이터타입 수정: modify
ALTER TABLE tablename
MODIFY COLUMN columnname new_datatype; - 테이블 컬럼의 이름/데이터타입 변경: change column
ALTER TABLE tablename
CHANGE COLUMN old_columnname new_columnname new_datatype; 
- 테이블 컬럼 삭제: drop column
ALTER TABLE tablename
DROP COLUMN columnname; 
- 테이블 삭제: drop table
[Chapter 04.] INSERT, SELECT, WHERE, UPDATE, DELETE
실습할 테이블 만들기
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)INSERT - 데이터 추가
- INSERT: 데이터를 추가하는 명령어
- 입력한 컬럼 이름의 순서와 값의 순서가 일치해야 함!
- 모든 칼럼값을 추가하는 경우에는 컬럼 이름을 지정하지 않아도 되지만, 입력하는 값 순서가 테이블의 컬럼 순서와 일치해야 함
INSERT INTO tablename (column1, column2, ...)
VALUES (value1, value2, ...);
혹은
INSERT INTO tablename
VALUES (value1, value2, ...);
SELECT - 데이터 조회
- SELECT: 테이블 내의 특정 컬럼에 대한 데이터를 조회
SELECT column1, column2, ... # SELECT *: 전체 컬럼 가져오기
FROM tablename;WHERE - 특정 조건의 데이터 조회
- SQL문에 조건을 추가하며 SELECT뿐만 아니라 UPDATE, DELETE에도 사용 가능
SELECT column1, column2, ... # SELECT *: 전체 컬럼 가져오기
FROM tablename
WHERE condition; #WHERE sex='F' / WHERE age>45
UPDATE - 데이터 수정
UPDATE tablename
SET column1 = value1, column2 = value2, ...
WHERE condition;
# 이효리의 나이를 23세로 수정
update person
-> set age=23
-> where name='이효리';DELETE - 데이터 삭제
# WHERE 조건을 만족하는 데이터를 삭제
DELETE FROM tablename
WHERE condition; [Chapter 05.] Order By
Order By
- select문에서 데이터를 특정 컬럼 기준으로 오름차순(ASC) 혹은 내림차순(DESC) 정렬
- ASC를 생략해도 디폴트가 오름차순 정렬임
- Order by col1, col2 로 두개 이상의 컬럼 지정할 경우, col1의 데이터 값이 동일할 때 col2 기준으로 다시 정렬함. (col1로 정렬한 후 col2로 한번 더 정렬)
SELECT col1, col2, ...
FROM tablename
ORDER BY col1, col2, ... ASC | DESC;order by age desc, name asc;: age 내림차순 정렬한 뒤 name 오름차순으로 정렬
[Chapter 06.] Comparison Operators(비교연산자)
A <> B: A와 B가 같지 않다(A가 B보다 크거나 작다)A !=B: A와 B가 같지 않다
예제
mysql> use zerobase;
Database changed
mysql> create table celeb(
-> id int not null auto_increment primary key,
-> name varchar(32) not null default '',
-> birthday date,
-> age int,
-> sex char(1),
-> job_title varchar(32),
-> agency varchar(32)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc celeb;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | | |
| birthday | date 타입" birthday > 19891231 | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| job_title | varchar(32) | YES | | NULL | |
| agency | varchar(32) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
# 예제 테이블 생성
INSERT INTO celeb VALUES (1, '아이유', '1993-05-16', 29, 'F', '가수, 텔런트', 'EDAM엔터테인먼트');
INSERT INTO celeb VALUES (2, '이미주', '1994-09-23', 28, 'F', '가수', '울림엔터테인먼트');
INSERT INTO celeb VALUES (3, '송강', '1994-04-23', 28,'M', '텔런트', '나무엑터스');
INSERT INTO celeb VALUES (4, '강동원', '1981-01-18', 41, 'M','영화배우, 텔런트', 'YG엔터테인먼트');
INSERT INTO celeb VALUES (5, '유재석', '1972-08-14', 50, 'M', 'MC, 개그맨', '안테나');
INSERT INTO celeb VALUES (6, '차승원', '1970-06-07', 48, 'M', '영화배우, 모델', 'YG엔터테인먼트');
INSERT INTO celeb VALUES (7, '이수현', '1999-05-04', 23, 'F', '가수', 'YG엔터테인먼트');
# age 내림차순, name 오름차순으로 celeb 정렬
mysql> select *
-> from celeb
-> order by age desc, name asc;
+----+-----------+------------+------+------+-------------------------+--------------------------+
| id | name | birthday | age | sex | job_title | agency |
+----+-----------+------------+------+------+-------------------------+--------------------------+
| 5 | 유재석 | 1972-08-14 | 50 | M | MC, 개그맨 | 안테나 |
| 6 | 차승원 | 1970-06-07 | 48 | M | 영화배우, 모델 | YG엔터테인먼트 |
| 4 | 강동원 | 1981-01-18 | 41 | M | 영화배우, 텔런트 | YG엔터테인먼트 |
| 1 | 아이유 | 1993-05-16 | 29 | F | 가수, 텔런트 | EDAM엔터테인먼트 |
| 3 | 송강 | 1994-04-23 | 28 | M | 텔런트 | 나무엑터스 |
| 2 | 이미주 | 1994-09-23 | 28 | F | 가수 | 울림엔터테인먼트 |
| 7 | 이수현 | 1999-05-04 | 23 | F | 가수 | YG엔터테인먼트 |
+----+-----------+------------+------+------+-------------------------+--------------------------+
7 rows in set (0.00 sec)
# age가 50 미만인 sex, agency, name, age 컬럼의 celeb 데이터를
# agency 내림차순 정렬한 후 name 오름차순으로 정렬
mysql> select sex, agency, name, age
-> from celeb
-> where age<50
-> order by agency desc, name asc;
+------+--------------------------+-----------+------+
| sex | agency | name | age |
+------+--------------------------+-----------+------+
| F | 울림엔터테인먼트 | 이미주 | 28 |
| M | 나무엑터스 | 송강 | 28 |
| M | YG엔터테인먼트 | 강동원 | 41 |
| F | YG엔터테인먼트 | 이수현 | 23 |
| M | YG엔터테인먼트 | 차승원 | 48 |
| F | EDAM엔터테인먼트 | 아이유 | 29 |
+------+--------------------------+-----------+------+
6 rows in set (0.00 sec)[Chapter 07.] Logical Operators(논리연산자)
- and가 or 보다 조건 우선순위가 높음. and가 먼저 수행된 다음에 or가 수행됨.
- 홀수 조건: (id%2)=1 / 짝수 조건: (id%2)=0
- date 타입: birthday > 19891231 와 같이 하여 1990년 이후를 조건 걸 수 있음
WHERE columnname BETWEEN val1 AND val2는val1 이상 val2 이하와 동일WHERE columnname IN (val1, val2)는col=val1 or col=val2와 동일WHERE columnname LIKE patternLIKE: 대소문자 구분 없이 반환. (a에 대해 검색하면 A도 나옴)YG%: 'YG'로 시작하는 데이터%엔터테인먼트: '엔터테인먼트'로 끝나는 데이터%가수%: '가수'가 포함된 데이터_G%: 두번째 글자가 G인 데이터가_%: '가'로 시작하고 최소 2글자 이상인 데이터 (언더바_로 글자수 확보)가____%:'가'로 시작하고 최소 5글자 이상인 데이터영%모델: '영'으로 시작하고 '모델'로 끝나는 데이터
# 소속사가 YG이거나 나무엑터스인 연예인 중, 나이가 30 미만인 데이터 검색
mysql> select * from celeb
-> where (agency='YG엔터테인먼트' or agency='나무엑터스') and age<30;
+----+-----------+------------+------+------+-----------+----------------------+
| id | name | birthday | age | sex | job_title | agency |
+----+-----------+------------+------+------+-----------+----------------------+
| 3 | 송강 | 1994-04-23 | 28 | M | 텔런트 | 나무엑터스 |
| 7 | 이수현 | 1999-05-04 | 23 | F | 가수 | YG엔터테인먼트 |
+----+-----------+------------+------+------+-----------+----------------------+
2 rows in set (0.00 sec)
# id가 홀수이면서 남자이거나 / id가 짝수이면서 YG인 연예인 (나이순 정렬)
mysql> select * from celeb where ((id%2)=1 and sex='M') or ((id%2)=0 and agency='YG엔터테인먼트')
-> order by age;
+----+-----------+------------+------+------+-------------------------+----------------------+
| id | name | birthday | age | sex | job_title | agency |
+----+-----------+------------+------+------+-------------------------+----------------------+
| 3 | 송강 | 1994-04-23 | 28 | M | 텔런트 | 나무엑터스 |
| 4 | 강동원 | 1981-01-18 | 41 | M | 영화배우, 텔런트 | YG엔터테인먼트 |
| 6 | 차승원 | 1970-06-07 | 48 | M | 영화배우, 모델 | YG엔터테인먼트 |
| 5 | 유재석 | 1972-08-14 | 50 | M | MC, 개그맨 | 안테나 |
+----+-----------+------------+------+------+-------------------------+----------------------+
4 rows in set (0.00 sec)
# 나이가 20에서 40 사이인 데이터 검색
mysql> select * from celeb where age between 20 and 40;
+----+-----------+------------+------+------+-------------------+--------------------------+
| id | name | birthday | age | sex | job_title | agency |
+----+-----------+------------+------+------+-------------------+--------------------------+
| 1 | 아이유 | 1993-05-16 | 29 | F | 가수, 텔런트 | EDAM엔터테인먼트 |
| 2 | 이미주 | 1994-09-23 | 28 | F | 가수 | 울림엔터테인먼트 |
| 3 | 송강 | 1994-04-23 | 28 | M | 텔런트 | 나무엑터스 |
| 7 | 이수현 | 1999-05-04 | 23 | F | 가수 | YG엔터테인먼트 |
+----+-----------+------------+------+------+-------------------+--------------------------+
4 rows in set (0.00 sec)
# 생년월일이 1980년~1995년 사이가 아니면서 여자이거나, 소속사가 YG이면서 나이가 20세~45세가 아닌 데이터
mysql> select * from celeb where (sex='F' and not birthday between 19800101 and 19951231) or (agency='YG엔터테 인먼트' and not age between 20 and 45);
+----+-----------+------------+------+------+----------------------+----------------------+
| id | name | birthday | age | sex | job_title | agency |
+----+-----------+------------+------+------+----------------------+----------------------+
| 6 | 차승원 | 1970-06-07 | 48 | M | 영화배우, 모델 | YG엔터테인먼트 |
| 7 | 이수현 | 1999-05-04 | 23 | F | 가수 | YG엔터테인먼트 |
+----+-----------+------------+------+------+----------------------+----------------------+
2 rows in set (0.00 sec)
# '아이유','송강','강동원','차승원' 중에 YG 소속이 아니거나 나이가 40~50세인 사람 검색
mysql> select * from celeb where name in ('아이유','송강','강동원','차승원') and (not agency='YG엔터테인먼트'
or age between 40 and 50);
+----+-----------+------------+------+------+-------------------------+------------------------+
| id | name | birthday | age | sex | job_title | agency |
+----+-----------+------------+------+------+-------------------------+------------------------+
| 1 | 아이유 | 1993-05-16 | 29 | F | 가수, 텔런트 | EDAM엔터테인먼트 |
| 3 | 송강 | 1994-04-23 | 28 | M | 텔런트 | 나무엑터스 |
| 4 | 강동원 | 1981-01-18 | 41 | M | 영화배우, 텔런트 | YG엔터테인먼트 |
| 6 | 차승원 | 1970-06-07 | 48 | M | 영화배우, 모델 | YG엔터테인먼트 |
+----+-----------+------------+------+------+-------------------------+------------------------+
4 rows in set (0.00 sec)
# YG로 시작하는 소속사 이름 가진 데이터 검색
mysql> select * from celeb where agency like 'YG%';
+----+-----------+------------+------+------+-------------------------+----------------------+
| id | name | birthday | age | sex | job_title | agency |
+----+-----------+------------+------+------+-------------------------+----------------------+
| 4 | 강동원 | 1981-01-18 | 41 | M | 영화배우, 텔런트 | YG엔터테인먼트 |
| 6 | 차승원 | 1970-06-07 | 48 | M | 영화배우, 모델 | YG엔터테인먼트 |
| 7 | 이수현 | 1999-05-04 | 23 | F | 가수 | YG엔터테인먼트 |
+----+-----------+------------+------+------+-------------------------+----------------------+
3 rows in set (0.00 sec)
# 직업이 하나 이상인 연예인 중 영화배우 혹은 텔런트가 아닌 연예인 검색
mysql> select * from celeb where job_title like '%,%' and not (job_title like '%영화배우%' or job_title like '%텔런트%');
+----+-----------+------------+------+------+---------------+-----------+
| id | name | birthday | age | sex | job_title | agency |
+----+-----------+------------+------+------+---------------+-----------+
| 5 | 유재석 | 1972-08-14 | 50 | M | MC, 개그맨 | 안테나 |
+----+-----------+------------+------+------+---------------+-----------+
1 row in set (0.00 sec)[Chapter 08.] Union
Union
- Union이란? 여러 개의 SQL문을 합쳐서 하나의 SQL문으로 만들어주는 방법(주의: 칼럼 개수가 같아야 함)
- UNION: 중복된 값을 제거한 후 합쳐서 보여준다
- UNION ALL: 중복된 값도 합쳐서 모두 보여준다
# UNION: test2의 중복된 값인 3은 제거됨.
mysql> select * from test1
-> union
-> select * from test2;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
+------+
5 rows in set (0.01 sec)
# UNION ALL: test2의 중복된 값인 3도 포함하여 보여줌
mysql> select * from test1
-> union all
-> select * from test2;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 3 |
+------+
6 rows in set (0.00 sec)[Chapter 09.] Join
실습 테이블 생성
mysql> create table snl_show(
-> id int not null auto_increment primary key,
-> season int not null,
-> episode int not null,
-> broadcast_date date,
-> host varchar(32) not null
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc snl_show;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| season | int | NO | | NULL | |
| episode | int | NO | | NULL | |
| broadcast_date | date | YES | | NULL | |
| host | varchar(32) | NO | | NULL | |
+----------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
INSERT INTO snl_show VALUES (1, 8, 7, '2020-09-05', '강동원' );
INSERT INTO snl_show VALUES (2, 8, 8, '2020-09-12', '유재석');
INSERT INTO snl_show VALUES (3, 8, 9, '2020-09-19', '차승원');
INSERT INTO snl_show VALUES (4, 8, 10, '2020-09-26', '이수현');
INSERT INTO snl_show VALUES (5, 9, 1, '2021-09-04', '이병헌');
INSERT INTO snl_show VALUES (6, 9, 2, '2021-09-11', '하지원');
INSERT INTO snl_show VALUES (7, 9, 3, '2021-09-18', '제시');
INSERT INTO snl_show VALUES (8, 9, 4, '2021-09-25', '조정석');
INSERT INTO snl_show VALUES (9, 9, 5, '2021-10-02', '조여정');
INSERT INTO snl_show VALUES (10, 9, 6, '2021-10-09', '옥주현');
mysql> select * from snl_show;
+----+--------+---------+----------------+-----------+
| id | season | episode | broadcast_date | host |
+----+--------+---------+----------------+-----------+
| 1 | 8 | 7 | 2020-09-05 | 강동원 |
| 2 | 8 | 8 | 2020-09-12 | 유재석 |
| 3 | 8 | 9 | 2020-09-19 | 차승원 |
| 4 | 8 | 10 | 2020-09-26 | 이수현 |
| 5 | 9 | 1 | 2021-09-04 | 이병헌 |
| 6 | 9 | 2 | 2021-09-11 | 하지원 |
| 7 | 9 | 3 | 2021-09-18 | 제시 |
| 8 | 9 | 4 | 2021-09-25 | 조정석 |
| 9 | 9 | 5 | 2021-10-02 | 조여정 |
| 10 | 9 | 6 | 2021-10-09 | 옥주현 |
+----+--------+---------+----------------+-----------+
10 rows in set (0.00 sec)Join: 두 개 이상의 테이블을 결합하는 것
INNER JOIN
두 개의 테이블에서 공통된 요소들을 통해 결합하는 조인 방식
SELECT col1, col2, ...
FROM tableA
INNER JOIN tableB
ON tableA.column = tableB.column
WHERE condition;
#A와 B 테이블의 column 값이 동일한 데이터만 조인
#조인된 데이터에 대해 condition이 일치하는 데이터만 불러옴- 실습 예제
# snl_show 호스트 출연한 celeb 기준으로 celeb 테이블과 snl_show 테이블을 INNER JOIN
mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host
-> from celeb
-> inner join snl_show
-> on celeb.name = snl_show.host;
+----+-----------+----+-----------+
| id | name | id | host |
+----+-----------+----+-----------+
| 4 | 강동원 | 1 | 강동원 |
| 5 | 유재석 | 2 | 유재석 |
| 6 | 차승원 | 3 | 차승원 |
| 7 | 이수현 | 4 | 이수현 |
+----+-----------+----+-----------+
4 rows in set (0.00 sec)LEFT JOIN
두 개의 테이블에서 공통 영역을 포함해 왼쪽 테이블(tableA)의 다른 데이터를 포함하는 조인 방식
SELECT col1, col2, ...
FROM tableA
LEFT JOIN tableB
ON tableA.column = tableB.column
WHERE condition;- 실습 예제
# celeb 테이블에서 snl_show.id, snl_show.host는 없기 때문에 NULL임.
mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host
->from celeb
-> left join snl_show
-> on celeb.name = snl_show.host;
+----+-----------+------+-----------+
| id | name | id | host |
+----+-----------+------+-----------+
| 1 | 아이유 | NULL | NULL |
| 2 | 이미주 | NULL | NULL |
| 3 | 송강 | NULL | NULL |
| 4 | 강동원 | 1 | 강동원 |
| 5 | 유재석 | 2 | 유재석 |
| 6 | 차승원 | 3 | 차승원 |
| 7 | 이수현 | 4 | 이수현 |
+----+-----------+------+-----------+
7 rows in set (0.00 sec)RIGHT JOIN
두 개의 테이블에서 공통 영역을 포함해 오른쪽 테이블(tableB)의 다른 데이터를 포함하는 조인 방식
SELECT col1, col2, ...
FROM tableA
RIGHT JOIN tableB
ON tableA.column = tableB.column
WHERE condition;- 실습 예제
# snl_show 테이블에서 celeb.id, celeb.name 없기 때문에 NULL임.
mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host
-> from celeb
-> right join snl_show
-> on celeb.name = snl_show.host;
+------+-----------+----+-----------+
| id | name | id | host |
+------+-----------+----+-----------+
| 4 | 강동원 | 1 | 강동원 |
| 5 | 유재석 | 2 | 유재석 |
| 6 | 차승원 | 3 | 차승원 |
| 7 | 이수현 | 4 | 이수현 |
| NULL | NULL | 5 | 이병헌 |
| NULL | NULL | 6 | 하지원 |
| NULL | NULL | 7 | 제시 |
| NULL | NULL | 8 | 조정석 |
| NULL | NULL | 9 | 조여정 |
| NULL | NULL | 10 | 옥주현 |
+------+-----------+----+-----------+
10 rows in set (0.01 sec)FULL OUTER JOIN
두 개의 테이블에서 공통 영역을 포함해 양쪽 테이블(tableB)의 다른 데이터를 모두 포함하는 조인 방식
SELECT col1, col2, ...
FROM tableA
FULL OUTER JOIN tableB
ON tableA.column = tableB.column
WHERE condition;=> MySQL에서는 FULL OUTER JOIN을 지원하지 않아 ERROR 발생!
=> MySQL에서는 left join + union + right join으로 구현 가능
- 실습 예제
mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb left join snl_show on celeb.name = snl_show.host
-> union
-> select celeb.id, celeb.name, snl_show.id, snl_show.host from celeb right join snl_show on celeb.name = snl_show.host;
+------+-----------+------+-----------+
| id | name | id | host |
+------+-----------+------+-----------+
| 1 | 아이유 | NULL | NULL |
| 2 | 이미주 | NULL | NULL |
| 3 | 송강 | NULL | NULL |
| 4 | 강동원 | 1 | 강동원 |
| 5 | 유재석 | 2 | 유재석 |
| 6 | 차승원 | 3 | 차승원 |
| 7 | 이수현 | 4 | 이수현 |
| NULL | NULL | 5 | 이병헌 |
| NULL | NULL | 6 | 하지원 |
| NULL | NULL | 7 | 제시 |
| NULL | NULL | 8 | 조정석 |
| NULL | NULL | 9 | 조여정 |
| NULL | NULL | 10 | 옥주현 |
+------+-----------+------+-----------+
13 rows in set (0.01 sec)SELF JOIN
- WHERE 조건과 일치하는 FROM의 여러 테이블들을 불러옴 = INNER JOIN
- 각 테이블에 존재하는 칼럼명이 다르면 해당 칼럼명은 테이블명을 붙이지 않아도 됨.
SELECT col1, col2, ...
FROM tableA, tableB, ...
WHERE condition;- 실습 예제
# INNER JOIN과 동일한 결과
mysql> select celeb.id, celeb.name, snl_show.id, snl_show.host
-> from celeb, snl_show
-> where celeb.name = snl_show.host;
+----+-----------+----+-----------+
| id | name | id | host |
+----+-----------+----+-----------+
| 4 | 강동원 | 1 | 강동원 |
| 5 | 유재석 | 2 | 유재석 |
| 6 | 차승원 | 3 | 차승원 |
| 7 | 이수현 | 4 | 이수현 |
+----+-----------+----+-----------+
4 rows in set (0.00 sec)
# celeb 연예인 중 snl_show에 host로 출연했고,
# 영화배우는 아니면서 YG 소속이거나
# 40세 이상이면서 YG 소속이 아닌
# 연예인의 이름, 나이, 직업, 소속사, 시즌, 에피소드 검색
mysql> select celeb.name, celeb.job_title, celeb.agency, snl_show.season, snl_show.episode from celeb, snl_show where celeb.name=snl_show.host and ((celeb.job_title not like '%영화배우%' and celeb.agency like 'YG%') or (celeb.age >= 40 and celeb.agency not like 'YG%'));
+-----------+---------------+----------------------+--------+---------+
| name | job_title | agency | season | episode |
+-----------+---------------+----------------------+--------+---------+
| 유재석 | MC, 개그맨 | 안테나 | 8 | 8 |
| 이수현 | 가수 | YG엔터테인먼트 | 8 | 10 |
+-----------+---------------+----------------------+--------+---------+
2 rows in set (0.01 sec)
# 칼럼명이 다르면 테이블명을 붙이지 않아도 됨.
mysql> select snl_show.id, season, episode, name, job_title
-> from celeb, snl_show
-> where name = host;
+----+--------+---------+-----------+-------------------------+
| id | season | episode | name | job_title |
+----+--------+---------+-----------+-------------------------+
| 1 | 8 | 7 | 강동원 | 영화배우, 텔런트 |
| 2 | 8 | 8 | 유재석 | MC, 개그맨 |
| 3 | 8 | 9 | 차승원 | 영화배우, 모델 |
| 4 | 8 | 10 | 이수현 | 가수 |
+----+--------+---------+-----------+-------------------------+
4 rows in set (0.00 sec)
# snl_show 출연한 celeb 중, 2020-09-15 이후에 출연했거나,
# 소속사 이름이 '엔터테인먼트'로 끝나지 않으면서
# 영화배우나 개그맨이 아닌 연예인의 celeb id, name, 직업, 소속사 검색
mysql> select celeb.id, name, job_title, agency from celeb, snl_show where name=host and (broadcast_date > '2020-09-15' or not agency like '%엔터테인먼트') and not (job_title like '%영화배우%' or job_title like '%개그맨%');
+----+-----------+-----------+----------------------+
| id | name | job_title | agency |
+----+-----------+-----------+----------------------+
| 7 | 이수현 | 가수 | YG엔터테인먼트 |
+----+-----------+-----------+----------------------+
1 row in set (0.00 sec)[Chapter 10.] Concat, Alias(as), Distinct
Concat
- Concat이란? 여러 문자열을 하나로 합치거나 연결하는 함수
SELECT CONCAT('string1', 'string2', ...) from tablename;
mysql> select concat('이름: ', name) from celeb;
+--------------------------+
| concat('이름: ', name) |
+--------------------------+
| 이름: 아이유 |
| 이름: 이미주 |
| 이름: 송강 |
| 이름: 강동원 |
| 이름: 유재석 |
| 이름: 차승원 |
| 이름: 이수현 |
+--------------------------+
7 rows in set (0.00 sec)Alias
- alias: 칼럼이나 테이블 이름에 별칭 생성 (as 생략 가능)
- 컬럼에 별칭 생성:
SELECT column as alias from tablename; - 테이블에 별칭 생성:
SELECT col1, col2, ... FROM tablename as alias;
mysql> select name as '이름', agency as '소속사' from celeb;
+-----------+--------------------------+
| 이름 | 소속사 |
+-----------+--------------------------+
| 아이유 | EDAM엔터테인먼트 |
| 이미주 | 울림엔터테인먼트 |
| 송강 | 나무엑터스 |
| 강동원 | YG엔터테인먼트 |
| 유재석 | 안테나 |
| 차승원 | YG엔터테인먼트 |
| 이수현 | YG엔터테인먼트 |
+-----------+--------------------------+
7 rows in set (0.00 sec)
# 이름과 직업을 합쳐서 profile이라는 별칭으로 만들어서 검색
mysql> select concat(name, ': ', job_title) as profile from celeb;
+------------------------------------+
| profile |
+------------------------------------+
| 아이유: 가수, 텔런트 |
| 이미주: 가수 |
| 송강: 텔런트 |
| 강동원: 영화배우, 텔런트 |
| 유재석: MC, 개그맨 |
| 차승원: 영화배우, 모델 |
| 이수현: 가수 |
+------------------------------------+
7 rows in set (0.00 sec)
# snl_show에 출연한 celeb을 기준으로 두 테이블을 조인하여
# celeb 테이블은 c, snl_show 테이블은 s라는 별칭을 만들어서 => 타이핑 리소스 줄일 수 있음
# 출연한 시즌과 에피소드, 이름, 직업 검색
mysql> select s.season, s.episode, c.name, c.job_title
-> from celeb as c, snl_show as s
-> where c.name=s.host;
+--------+---------+-----------+-------------------------+
| season | episode | name | job_title |
+--------+---------+-----------+-------------------------+
| 8 | 7 | 강동원 | 영화배우, 텔런트 |
| 8 | 8 | 유재석 | MC, 개그맨 |
| 8 | 9 | 차승원 | 영화배우, 모델 |
| 8 | 10 | 이수현 | 가수 |
+--------+---------+-----------+-------------------------+
4 rows in set (0.00 sec)
# snl_show에 출연한 celeb을 기준으로 두 테이블을 조인하여 다음 데이터 별칭 사용하여 검색
# 방송정보: 시즌, 에피스드, 방송일
# 출연자정보: 이름, 직업
mysql> select concat(s.season, '시즌-', s.episode, '화(', s.broadcast_date,')') '방송정보',
-> concat(c.name,'(',c.job_title,')') '출연자정보'
-> from celeb as c, snl_show as s
-> where name=host;
+---------------------------+------------------------------------+
| 방송정보 | 출연자정보 |
+---------------------------+------------------------------------+
| 8시즌-7화(2020-09-05) | 강동원(영화배우, 텔런트) |
| 8시즌-8화(2020-09-12) | 유재석(MC, 개그맨) |
| 8시즌-9화(2020-09-19) | 차승원(영화배우, 모델) |
| 8시즌-10화(2020-09-26) | 이수현(가수) |
+---------------------------+------------------------------------+
4 rows in set (0.01 sec)Distinct (유니크한 컬럼 값 추출)
- Distinct: 검색한 결과의 중복 제거
SELECT DISTINCT col1, col2, ... FROM tablename;
mysql> select distinct agency from celeb;
+--------------------------+
| agency |
+--------------------------+
| EDAM엔터테인먼트 |
| 울림엔터테인먼트 |
| 나무엑터스 |
| YG엔터테인먼트 |
| 안테나 |
+--------------------------+
5 rows in set (0.01 sec)Limit
- Limit: 검색 결과를 정렬된 순으로 주어진 숫자만큼만 조회
SELECT col1, col2, ... FROM tablename WHERE condition LIMIT number;
# 나이가 가장 적은 연예인 4명 검색
mysql> select * from celeb order by age asc limit 4;
+----+-----------+------------+------+------+-------------------+--------------------------+
| id | name | birthday | age | sex | job_title | agency |
+----+-----------+------------+------+------+-------------------+--------------------------+
| 7 | 이수현 | 1999-05-04 | 23 | F | 가수 | YG엔터테인먼트 |
| 2 | 이미주 | 1994-09-23 | 28 | F | 가수 | 울림엔터테인먼트 |
| 3 | 송강 | 1994-04-23 | 28 | M | 텔런트 | 나무엑터스 |
| 1 | 아이유 | 1993-05-16 | 29 | F | 가수, 텔런트 | EDAM엔터테인먼트 |
+----+-----------+------------+------+------+-------------------+--------------------------+
4 rows in set (0.00 sec)실습 문제
# 이름이 3글자인 연예인 정보 검색
mysql> select concat('이름: ', name, ', 소속사: ',agency) '연예인 정보' from celeb where name like '___';
+--------------------------------------------------------+
| 연예인 정보 |
+--------------------------------------------------------+
| 이름: 아이유, 소속사: EDAM엔터테인먼트 |
| 이름: 이미주, 소속사: 울림엔터테인먼트 |
| 이름: 강동원, 소속사: YG엔터테인먼트 |
| 이름: 유재석, 소속사: 안테나 |
| 이름: 차승원, 소속사: YG엔터테인먼트 |
| 이름: 이수현, 소속사: YG엔터테인먼트 |
+--------------------------------------------------------+
6 rows in set (0.00 sec)
mysql> select c.agency as '소속사 정보', concat('나이: ', c.age, '(', c.sex, ')') as '신상정보', concat(s.season, '시즌-', s.episode, '방송날짜: ', s.broadcast_date) as '출연정보' from celeb c, snl_show s where c.name=s.host and c.agency like '__ 엔터테인먼트' order by s.broadcast_date desc;
+----------------------+---------------+------------------------------------+
| 소속사 정보 | 신상정보 | 출연정보 |
+----------------------+---------------+------------------------------------+
| YG엔터테인먼트 | 나이: 23(F) | 8시즌-10방송날짜: 2020-09-26 |
| YG엔터테인먼트 | 나이: 48(M) | 8시즌-9방송날짜: 2020-09-19 |
| YG엔터테인먼트 | 나이: 41(M) | 8시즌-7방송날짜: 2020-09-05 |
+----------------------+---------------+------------------------------------+
3 rows in set (0.00 sec)
# snl에 출연한 연예인의 정보를 나이 순으로 2개만 검색하여 출력
mysql> select concat('SNL 시즌', s.season, ' 에피소드', s.episode, ' 호스트 ', s.host) 'SNL 방송정보', c.age from celeb c, snl_show s where c.name=s.host order by c.age desc limit 2;
+------------------------------------------------+------+
| SNL 방송정보 | age |
+------------------------------------------------+------+
| SNL 시즌8 에피소드8 호스트 유재석 | 50 |
| SNL 시즌8 에피소드9 호스트 차승원 | 48 |
+------------------------------------------------+------+
2 rows in set (0.00 sec)