통계데이터베이스및실습 (Part 1) Jinseog Kim Dep. of Applied Statistics, Dongguk University Email: jinseog.kim @ gmail.com September 7, 2016 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 1 / 60
1 데이터베이스 2 MySQL 설치 3 기초 SQL 4 고급 SQL 유용한함수들 JOIN inseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 2 / 60
학습목표 1 통계소프트웨어 R을이용한다양한데이터조작을할수있다. 2 MySQL 쿼리문을이용하여데이터베이스자료를핸들링할수있다. 3 통계소프트웨어 R에서 Excel 및 MySQL 데이터베이스자료를분석할수있다. 4 빅데이터의개념을이해하고 R과데이터베이스를이용하여빅데이터를분석할수있다. Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 3 / 60
데이터베이스 1. 데이터베이스 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 4 / 60
MySQL 설치 2. MySQL 설치 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 5 / 60
MySQL 설치 MySQL 설치 MySQL 홈페이지 (http://www.mysql.com/) MySQL 설치파일다운로드페이지 (http://dev.mysql.com/downloads/) Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 6 / 60
MySQL 설치 MySQL 설치 MySQL 설치파일다운로드 : mysql-installer-community-5.6.xx.x.msi Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 7 / 60
MySQL 설치 MySQL 설치 MySQL 설치 1 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 8 / 60
MySQL 설치 MySQL 설치 MySQL 설치 2 : License Agreement Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 9 / 60
MySQL 설치 MySQL 설치 MySQL 설치 3 : Select Products and Features Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 10 / 60
MySQL 설치 MySQL 설치 MySQL 설치 : 프로그램설치 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 11 / 60
MySQL 설치 MySQL 설치 MySQL 설치 : Product Configuration Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 12 / 60
MySQL 설치 MySQL 설치 MySQL 설치 : Product Configuration (Type and Networking) Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 13 / 60
MySQL 설치 MySQL 설치 MySQL 설치 : Product Configuration (Accounts and Roles) Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 14 / 60
MySQL 설치 MySQL 설치 MySQL 설치 : Product Configuration (Windows service setting) Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 15 / 60
MySQL 설치 MySQL 설치 MySQL 설치 : Apply Configurations Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 16 / 60
MySQL 설치 MySQL 설치 MySQL 설치 : Product configurations and installations complete Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 17 / 60
MySQL 설치 MySQL 실행 MySQL 실행 : consol 실행 시작 [MySQL 5.6 command line client] 실행 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 18 / 60
MySQL 설치 MySQL 실행 root 패스워드입력 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 19 / 60
기초 SQL 3. 기초 SQL Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 20 / 60
기초 SQL DATABASE 생성 1 CREATE DATABASE mydb ; Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 21 / 60
기초 SQL DATABASE: 한글처리 문자셋 (Character Set): 문자의인코딩규칙이정의된집합 ( ASCII, EUC-KR, UTF-8 등 ) 콜레이션 (Collation): 문자셋으로인코딩된문자들간비교시사용하는규칙집합 데이터베이스생성시 character set 과 collation 을지정하는방법 : 1 CREATE DATABASE mydb 2 DEFAULT CHARACTER SET u t f 8 3 DEFAULT COLLATE u t f 8 g e n e r a l c i ; 테이블생성시 character set 과 collation 을지정하는방법 : 1 CREATE TABLE mytbl ( 2... 3 ) DEFAULT CHARACTER SET u t f 8 4 DEFAULT COLLATE u t f 8 g e n e r a l c i ; Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 22 / 60
기초 SQL password 변경 : mysql 데이터베이스의 user 테이블의암호를 PASSWORD() 함수이용변경 1 mysql> UPDATE mysql. u s e r SET Password=PASSWORD( 1234 ) where u s e r= u s e r i d ; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 1 mysql> FLUSH PRIVILEGES ; Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 23 / 60
기초 SQL 주석문 (comment) 아래와같은세가지의주석문형태 ( 는뺄셈과혼동권장하지않음 ) 1 mysql> s e l e c t 1+1; # This comment continues to the end of l i n e 2 mysql> s e l e c t 1+1; This comment continues to the end of l i n e 3 mysql> s e l e c t 1 / t h i s i s an in l i n e comment / + 1 ; 4 mysql> s e l e c t 1+ 5 > / t h i s i s a 6 > multiple l i n e comment / 7 > 1 ; Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 24 / 60
기초 SQL 테이블의속성변경 (alter) 컬럼추가 alter table [table_name] add [col_name] [column_type]; student 테이블에 age컬럼추가, 속성을 int alter table student add age int; 결과 age컬럼값들은모두 null값컬럼제거 alter table [table_name] drop [col_name]; student 테이블에서 idnum컬럼제거 alter table student drop idnum; ( 주의 ) 이명령은모든데이터베이스에서지원하지않음 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 25 / 60
기초 SQL 테이블의속성변경 (alter) 칼럼명을변경 alter table [table_name] change [old_name] [new_name] [column_type]; alter table address change name firstname varchar(15); Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 26 / 60
4. 고급 SQL Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 27 / 60
패턴일치기능 (LIKE/REGEXP) 을이용한검색 SQL 에서패턴일치기능은보다빠르고정교하게원하는조건에대한매우유용한검색방법임 LIKE 혹은 REGEXP( 정규표현식 ) 를이용 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 28 / 60
LIKE 를이용한패턴일치검색 LIKE구문에서패턴표현기호 _ : 임의의한문자 % 는임의의수의문자 (0개의문자를포함 ) LIKE를이용한패턴에서대소문자는구분하지않음 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 29 / 60
LIKE 를이용한패턴일치검색 아래는 LIKE를이용한패턴일치검색의사용예이다. 정확하게 5개의글자로이루어진이름 (name) 에대한검색 : 밑줄 (_) 5개를사용하여검색조건을나타낸다. mysql> SELECT * FROM pet WHERE name LIKE " "; +-------+--------+---------+-----+------------+-------+ name owner species sex birth death +-------+--------+---------+-----+------------+-------+ Claws Gwen cat m 1994-03-17 NULL Buffy Harold dog f 1989-05-13 NULL +-------+--------+---------+-----+------------+-------+ 이름 (name) 이 b 로시작하는패턴검색 mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+-----+------------+-----------+ name owner species sex birth death +--------+--------+---------+-----+------------+-----------+ Buffy Harold dog f 1989-05-13 NULL Bowser Diane dog m 1989-08-31 1995-07-29 +--------+--------+---------+-----+------------+-----------+ 이름 (name) 이 fy 로끝나는패턴검색 mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+-----+------------+-------+ name owner species sex birth death +--------+--------+---------+-----+------------+-------+ Fluffy Harold cat f 1993-02-04 NULL Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 30 / 60
정규표현에의한패턴일치 (REGEXP) 정규표현 (regular expression) 에기반한패턴일치검색은 LIKE 대신 REGEXP 을사용한다. 정규표현식설명. 문자하나 * 앞에나온문자의 0개이상의반복 ^ 문자열처음 $ 문자열끝 [ ] 괄호안의문자들중하나이상과일치 { } 반복연산자, 예를들어 n번반복할때 {n} 으로적는다. Table : 정규표현식에사용되는문자및용도 LIKE : 전체값과일치 vs REGEXP : 부분일치 //ffy 가포함된것 SELECT * FROM pet WHERE name REGEXP "ffy"; // 반드시 ffy 인것 SELECT * FROM pet WHERE name LIKE "ffy"; Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 31 / 60
정규표현에의한패턴일치 (REGEXP) [abc] : 문자 a 나 b 나 c 중하나를가르키는표현 [a-za-z] : 알파벳문자를하나라도포함하는표현 ( 정규표현은대소문자를구별 ) x* : x, xx, xxx... 를나타낸다. [0-9]* : 7, 12, 345, 678 등임의의길이를갖는수 ^abc : 처음에 abc 로시작하는패턴 abc$ : abc 로끝나는문자열을 이름 (name) 이소문자 b 혹은대문자 B 로시작하는조건검색 mysql> SELECT * FROM pet WHERE name REGEXP "^[bb]"; +--------+--------+---------+-----+------------+------------+ name owner species sex birth death +--------+--------+---------+-----+------------+------------+ Buffy Harold dog f 1989-05-13 NULL Bowser Diane dog m 1989-08-31 1995-07-29 +--------+--------+---------+-----+------------+------------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 32 / 60
정규표현에의한패턴일치 (REGEXP) fy 로끝나는이름검색 ($ 를사용 ) mysql> SELECT * FROM pet WHERE name REGEXP "fy$"; +--------+--------+---------+-----+------------+-------+ name owner species sex birth death +--------+--------+---------+-----+------------+-------+ Fluffy Harold cat f 1993-02-04 NULL Buffy Harold dog f 1989-05-13 NULL +--------+--------+---------+-----+------------+-------+ 정확하게 5 개의문자로이름어진값검색 // 정규표현 SELECT * FROM pet WHERE name REGEXP "^...$"; // 정규표현 ( 반복연산자 ) SELECT * FROM pet WHERE name REGEXP "^.{5}$"; //LIKE 표현 SELECT * FROM pet WHERE name LIKE " "; Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 33 / 60
집단함수를이용한요약정보의검색 집단함수 COUNT SUM AVG MAX MIN 의미행의개수 NULL을제외한모든행의합계 NULL을제외한모든행의평균값 NULL을제외한모든행의최대값 NULL을제외한모든행의최소값 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 34 / 60
집단함수를이용한요약정보의검색 레코드의수 : count() 함수사용 mysql> SELECT COUNT(*) FROM pet; +----------+ COUNT(*) +----------+ 9 +----------+ 각소유주가소유한애완동물의수 mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ owner COUNT(*) +--------+----------+ Benny 2 Diane 2 Gwen 3 Harold 2 +--------+----------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 35 / 60
집단함수를이용한요약정보의검색 COUNT() 와 GROUP BY 각종에해당하는동물의수 mysql> SELECT species, COUNT(*) FROM pet GROUP BY species; +---------+----------+ species COUNT(*) +---------+----------+ bird 2 cat 2 dog 3 hamster 1 snake 1 +---------+----------+ 성에따른동물의수는다음의 SQL 문으로알아낼수있다. mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex; +------+----------+ sex COUNT(*) +------+----------+ NULL 1 f 4 m 4 +------+----------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 36 / 60
집단함수를이용한요약정보의검색 다음은종과성에따른동물의수를검색한것이다. mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ species sex COUNT(*) +---------+------+----------+ bird NULL 1 bird f 1 cat f 1 cat m 1 dog f 1 dog m 2 hamster f 1 snake m 1 +---------+------+----------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 37 / 60
집단함수를이용한요약정보의검색 바로위의경우와는달리, 특정한동물에대해서만조사해볼수도있다. 개와고양이의경우에만각성에대해몇마리인지조사해보자. mysql> SELECT species, sex, COUNT(*) FROM pet -> WHERE species = "dog" OR species = "cat" -> GROUP BY species, sex; +---------+------+----------+ species sex COUNT(*) +---------+------+----------+ cat f 1 cat m 1 dog f 1 dog m 2 +---------+------+----------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 38 / 60
별명 (AS) 컬럼명을다른이름으로변경하여출력 SELECT COUNT(*) AS N_STUDENT FROM STUDENT; Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 39 / 60
GROUP BY + HAVING HAVING 절은 GROUP BY 절과같이사용되며, GROUP BY 절에명시된열이름에대한제약조건을의미한다. SELECT ID_LEC COUNT(*) AS N_REG, AVG(FINAL) AS MEAN_FINAL FROM LECTURES GROUP BY ID_LEC HAVING COUNT(*) >= 5; Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 40 / 60
유용한함수들 4.1 유용한함수들 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 41 / 60
유용한함수들 난수발생 RAND() SELECT id,name FROM STUDENT ORDER BY RAND() LIMIT 5; +----+--------+ id name +----+--------+ 3 이수만 2 임성훈 6 박수홍 12 정윤호 13 권보아 +----+--------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 42 / 60
유용한함수들 문자열결합 CONCAT() SELECT id,concat(name,"(",alias,")") AS " 이름 ( 별명 )" FROM STUDENT; +----+---------------------+ id 이름 ( 별명 ) +----+---------------------+ 1 조용필 ( 작은거인 ) 2 임성훈 () 3 이수만 () 4 임예진 ()... 9 한채영 ( 바비인형 ) 10 박수애 ( 리틀정윤희 ) 11 허영란 ( 까치 ) 12 정윤호 ( 꼬비 ) 13 권보아 () 14 문근영 ( 국민여동생 ) +----+---------------------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 43 / 60
JOIN 4.2 JOIN Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 44 / 60
JOIN MySQL JOIN Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 45 / 60
JOIN MySQL JOIN LEFT JOIN 1 SELECT a. v1, b. w1 FROM a LEFT JOIN b ON a. k1 = b. k2 ; 2 RIGHT JOIN 1 SELECT a. v1, b. w1 FROM a RIGHT JOIN b ON a. k1 = b. k2 ; 2 INNER JOIN 1 SELECT a. v1, b. w1 FROM a INNER JOIN b ON a. k1 = b. k2 ; 2 OUTER JOIN 1 SELECT a. v1, b. w1 FROM a FULL OUTER JOIN b ON a. k1 = b. k2 ; 2 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 46 / 60
JOIN JOIN v.s. data.table JOIN type DT syntax data.table::merge() syntax INNER X[Y, nomatch=0] merge(x, Y, all=false) LEFT Y[X] merge(x, Y, all.x=true) RIGHT X[Y] merge(x, Y, all.y=true) OUTER - merge(x, Y, all=true) Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 47 / 60
JOIN Join 검색 Join 은여러테이블에저장된데이터를하나의 SQL 문으로한번에검색할수있는기능이다. 이조인기능은현재관계 DBMS 를사실상표준으로만드는데결정적인역할을하였다. 두개이상의테이블을연결하는조인문을사용하기위해서는왜래키 (foreign key) 가적어도하나이상있어야한다. 조인문을작성하는순서 1 SELECT 절에서검색하고하는열이름들을명시한다 2 FROM 절에서 SELECT 절에지정된열이름들의소속테이블이름을명시한다 3 FROM 절에지정된테이블이름이두개이상이면, 조인을위해서 WHERE 절에조인조건절을명시해야한다 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 48 / 60
JOIN Join 검색 두개이상의테이블에서열이름이중복을해결하기위해서테이블이름과열이름을반드시점 (.) 으로구분예 ) professor 와 student 테이블에 id 라는컬럼명을동시일을때 : 조인문에서는 professor.id 와 student.id 를사용조인에서테이블이름을 AS 문을이용하여다른이름으로변경하여사용할수있다. FROM 절에서테이블이름에별명을지정한후, 조인조건절 (where) 에서지정된별명을사용하면효율적이다. Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 49 / 60
JOIN Join 검색 테이블명 컬럼명 데이터형식 NULL유무 기본키 외래키 FK-TABLE FK컬럼명 학과 번호 INTEGER NOT NULL 0 (DEPART) 이름 VARCHAR(50) 교수 번호 INTEGER NOT NULL 0 (PROFESSOR) 이름 VARCHAR(50) 학과번호 INTEGER NOT NULL 0 DEPART 번호 STUDENT 번호 INTEGER NOT NULL 0 ( 학생 ) 이름 VARCHAR(30) 주소 VARCHAR(50) 학년 INTEGER 키 INTEGER 몸무게 INTEGER 별명 VARCHAR(20) 별명이유 VAHRCHAR(50) 학과번호 INTEGER NOT NULL 0 DEPART 번호 CURRICULUM 번호 INTEGER NOT NULL 0 ( 과목 ) 이름 VARCHAR(30) 학점 INTEGER 교수번호 INTEGER NOT NULL 0 PROF 번호 REGIST 번호 INTEGER NOT NULL 0 ( 등록 ) 등록일 DATETIME 학생번호 INTEGER NOT NULL 0 STUDENT 번호 LECTURE 등록번호 INTEGER NOT NULL 0 0 REGIST 번호 ( 수강 ) 과목번호 INTEGER NOT NULL 0 LECTURE 번호 중간성적 DECIMAL(6,2) 기말성적 DECIMAL(6,2) Table : 테이블명세서 Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 50 / 60
JOIN Join 검색 교수의번호, 이름, 학과이름을출력하라. SELECT p.id as 교수번호, p.name as 교수이름, d.name as 학과명 FROM professor as p, depart as d where d.id = p.id; +----------+----------+-------------+ 교수번호 교수이름 학과명 +----------+----------+-------------+ 1 김태길 국문과 2 김봉남 연극영화과 3 조정래 영문과 4 이문열 의류학과 5 안성기 전산학과 6 장미희 철학과 +----------+----------+-------------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 51 / 60
Jinseog SELECT Kimt1.*, Dep. of t2.* Applied FROMStatistics, t1,t2 WHERE Dongguk t1.i1=t2.i2; 통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 52 / 60 JOIN 크로스조인 (full join or cross join) t1 t2 +-------+ +---------+ i1 c1 i2 c2 +-------+ +---------+ 1 a 2 c 2 b 3 b 3 c 4 a +-------+ +---------+ SELECT t1.*, t2.* FROM t1,t2; +------+------+------+------+ i1 c1 i2 c2 +------+------+------+------+ 1 a 2 c 2 b 2 c 3 c 2 c 1 a 3 b 2 b 3 b 3 c 3 b 1 a 4 a 2 b 4 a 3 c 4 a +------+------+------+------+
JOIN 크로스조인 (full join or cross join) 다음의 SQL 문을수행해보고위의결과와비교해보시오. SELECT t1.*, t2.* FROM t1 JOIN t2 WHERE t1.i1=t2.i2; SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1=t2.i2; SELECT t1.*, t2.* FROM t1 CROSS JOIN t2 WHERE t1.i1=t2.i2; SELECT t1.*, t2.* FROM t1 CROSS JOIN t2 ON t1.i1=t2.i2; Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 53 / 60
JOIN 외부조인 (outer join) SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1=t2.i2; +------+------+------+------+ i1 c1 i2 c2 +------+------+------+------+ 1 a NULL NULL 2 b 2 c 3 c 3 b +------+------+------+------+ SELECT t1.*, t2.* FROM t1 RIGHT JOIN t2 ON t1.i1=t2.i2; +------+------+------+------+ i1 c1 i2 c2 +------+------+------+------+ 2 b 2 c 3 c 3 b NULL NULL 4 a +------+------+------+------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 54 / 60
JOIN 내부조인 (inner join) SELECT * FROM (Artist INNER JOIN CD ON CD.ArtistID=Artist.ArtistID) WHERE Artist.ArtistID=22; Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 55 / 60
JOIN 서브쿼리 (sub query, sub select) 하나의 SQL 문처리결과를다른 SQL 문에전달하여새로운결과를검색하는기능이경우 ORDER BY 절은사용이불가다음의예를보자. 이예는학생테이블에서김태희학생과학년이동일한모든학생의이름, 키, 몸무게를검색하라는 sql 문장이다. SELECT name AS 이름, weight AS 몸무게, height AS " 키 ",grade AS " 학년 " FROM student WHERE grade=(select grade FROM student WHERE name=" 김태희 "); Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 56 / 60
JOIN 서브쿼리 (sub query, sub select) 문자열문과를포함하는학과이름을가진학과에소속된모든학생의번호, 이름, 학년, 학과번호를검색하라. 에대한예를보자. 단, 서브쿼리의검색결과가하나이상의행이면, IN, EXISTS 등다중행비교연산자중하나를사용해야한다 SELECT id AS 학번, name as 이름, grade as 학년, did as 학과번호 FROM student WHERE did IN (SELECT id FROM depart WHERE name LIKE '% 문과 %'); Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 57 / 60
JOIN UNION SELECT i1 AS i FROM t1 UNION SELECT i2 AS i FROM t2; +------+ i +------+ 1 2 3 4 +------+ SELECT * FROM t1 UNION SELECT * FROM t2; +------+------+ i1 c1 +------+------+ 1 a 2 b 3 c 2 c 3 b 4 a +------+------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 58 / 60
JOIN UNION SELECT * FROM t3; +------+------+ i3 c3 +------+------+ 2 e 6 a +------+------+ 2 rows in set (0.00 sec) SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3; +------+------+ i1 c1 +------+------+ 1 a 2 b 3 c 2 c 3 b 4 a 2 e 6 a +------+------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 59 / 60
JOIN UNION SELECT * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 1 UNION SELECT * FROM t3 LIMIT 1; +------+------+ i1 c1 +------+------+ 1 a +------+------+ (SELECT * FROM t1 LIMIT 1) UNION (SELECT * FROM t2 LIMIT 1) UNION (SELECT * FROM t3 LIMIT 1); +------+------+ i1 c1 +------+------+ 1 a 2 c 2 e +------+------+ Jinseog Kim Dep. of Applied Statistics, Dongguk통계데이터베이스 University Email: jinseog.kim September @ gmail.com 7, 2016 60 / 60