MariaDB Exercises

// MySQL 계정(수퍼유저)에서 사용자 생성(movie)

• sudo -u root mysql // root(수펴유저) 계정으로 mysql 실행

• 또는 아래 방식으로

su - root

mysql // Linux root 계정에서 mysql을 실행하면 수퍼유저로 로그인

• MariaDB[none]>CREATE USER 'movie'@'localhost' IDENTIFIED BY 'futuresoft#00'; // 수퍼유저에서 사용자 계정 movie 생성, localhost에서만 접근

• MariaDB[none]>CREATE USER 'movie'@'%' IDENTIFIED BY 'futuresoft#00'; // 수퍼유저에서 사용자 계정 movie 생성, any hosts에서 접근

• MariaDB[none]>use mysql

• MariaDB[mysql]>SELECT host, user, password FROM user; // 사용자 movie 계정이 생성됨

// Database 생성(root 계정 혹은 createdb 권한이 있는 사용자 계정)

• CREATE DATABASE movielens DEFAULT CHARACTER SET utf8mb4;

• SHOW DATABASES;

// 사용자 계정(movie)에 생성된 데이터베이스 접근 권한 부여

• GRANT ALL PRIVILEGES ON movielens.* TO 'movie'@'localhost'; // localhost에만 권한 부여

• GRANT ALL PRIVILEGES ON movielens.* TO 'movie'@'%'; // any hosts에 권한 부여

• FLUSH PRIVILEGES;

• QUIT

// 새로 생성한 계정(movie)과 데이터베이스(movielens)로 다시 접속

• mysql -hlocalhost -P3306 -umovie -pfuturesoft#00 -Dmovielens

• mysql -hcms.futuresoft.co.kr -P3306 -umovie -pfuturesoft#00 -Dmovielens

// Movielens Table Schema and Sample Data Load

• movielens-mysql.sql // 영화 및 평점관련 테이블 생성 후 샘플 데이터 insert

• mysql -hlocalhost -P3306 -umovie -pfuturesoft#00 -Dmovielens < movielens-mysql.sql

• (혹은 mysql 로그인후 MariaDB[mysql]>source movielens-mysql.sql 로 sql 파일 실행)

• genre(영화분류)

• movie(영화정보)

• muser(고객정보)

• occupation(직업정보)

• movierating(영화평점)

• moviegenre(영화장르)

// Schema와 Sample Data에 일부 내용 보완

ALTER TABLE user RENAME TO muser; // user 테이블명을 muser로 변경

ALTER TABLE muser ADD COLUMN name VARCHAR(40) NOT NULL DEFAULT ' '; // 이름 필드 추가

UPDATE muser SET name = CONCAT('고객', CONVERT(id, CHAR)); // 이름 값 설정

UPDATE muser SET age = 17 WHERE age < 18; // 나이 설정 안된 회원 정보 임의 설정

UPDATE movie SET year = 2001 WHERE year = 0; // 상영년도 설정 안된 데이터 값 임의 설정

ALTER TABLE genre COMMENT = '영화분류';

ALTER TABLE movie COMMENT = '영화정보';

ALTER TABLE moviegenre COMMENT = '영화장르';

ALTER TABLE movierating COMMENT = '영화평점';

ALTER TABLE muser COMMENT = '고객정보';

ALTER TABLE occupation COMMENT = '직업분류';

// 기본 Query 예제

SELECT * FROM genre; // 결과에 모든 필드를 표시, Default 정렬 순서는 PK 순서

SELECT COUNT(*) FROM genre; // 전체 레코드의 갯수 출력

SELECT COUNT(name) FROM genre; // 특정 필드값이 NULL이 아닌 레코드 갯수 출력

SELECT id AS 분류ID, name AS 분류명 FROM genre; // 필드명을 임으로 변경해서 표시(default는 필드정의 명칭)

SELECT name FROM genre; // 결과에 name 필드만 표시

SELECT substr(name, 1, 8) FROM genre;

SELECT substr(name, 3) FROM genre;

SELECT name FROM genre WHERE substr(name, 1, 2) = 'Th'; // 문자의 일부를 완전일치 검색

SELECT name FROM genre WHERE substr(name, 1, 2) LIKE 'Th%'; // Th 로 시작하는 레코드 출력

SELECT id, name FROM genre ORDER BY name ASC; // Ascending Order

SELECT id, name FROM genre ORDER BY name DESC; // Descending Order

SELECT id, name FROM genre WHERE id =1;

SELECT id, name FROM genre WHERE id =1 AND name = 'Action'; // 모든 조건이 만족하는 레코드 출력

SELECT id, name FROM genre WHERE id =1 OR name = 'Comedy'; // 어느 조건이건 만족하는 레코드 출력

SELECT id, name FROM genre WHERE id =1 AND name = 'Comedy';

SELECT id, name FROM genre WHERE name LIKE 'A%' ORDER BY name ASC; // A로 시작하는 레코드 출력

SELECT id, name FROM genre WHERE name LIKE '%e' ORDER BY name ASC; // e로 끝나는 레코드 출력

SELECT id, name FROM genre WHERE name LIKE '%me%' ORDER BY name ASC; // me가 포함된 레코드 출력

SELECT id, name, year FROM movie LIMIT 10; // 결과중 10건만 출력

SELECT id, name, year FROM movie WHERE year = 2001; // 숫자 필드 질의

SELECT id, name, year FROM movie WHERE name = 'Jumanji'; // 문자 필드 질의

SELECT id, name, year FROM movie WHERE year BETWEEN 1995 AND 1998; // 특정값 사이 결과 출력

SELECT id, name, year FROM movie WHERE year >= 2000; // 특정 값 이상 결과 출력

SELECT DISTINCT year FROM movie ORDER BY year DESC; // 특정 필드에서 고유한 값의 목록 출력

SELECT COUNT(DISTINCT year) FROM movie; // 중복 함수 결과 - 건수 표시

SELECT movieid, genreid FROM moviegenre;

SELECT id, name, gender, age FROM muser;

SELECT id, name, gender, age FROM muser WHERE id <= 10 ORDER BY id ASC;

SELECT id, name, gender, age FROM muser WHERE id > 100 ORDER BY id ASC;

SELECT * FROM muser WHERE id IN (1, 100, 1000);

SELECT * FROM muser WHERE id IN (SELECT userid FROM movierating WHERE rating >= 4); // Subquery for 고객정보

SELECT * FROM movie WHERE id IN (SELECT movieid FROM movierating WHERE rating < 3); // Subquery for 영화정보

SELECT userid, movieid, rating FROM movierating;

SELECT SUM(rating), MIN(rating), MAX(rating), AVG(rating), STDDEV(rating) FROM movierating;

SELECT userid, movieid, rating FROM movierating WHERE userid=6 AND movieid=1806;

SELECT movieid, ROUND(AVG(rating), 2) FROM movierating GROUP BY movieid; // 여러 결과를 묶어서 하나로 표시(연산 함수와 함께)

SELECT movieid, MAX(rating), MIN(rating), ROUND(AVG(rating), 2) AS avr FROM movierating GROUP BY movieid; // 수치연산 함수 적용

SELECT movieid, rating FROM movierating ORDER BY rating ASC , movieid ASC; // 복수 필드로 정렬

SELECT movieid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY movieid ORDER BY average DESC LIMIT 10; // 숫자 표시형식 지정

SELECT movieid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY movieid HAVING AVG(rating) > 4.50 ORDER BY average DESC; // 영화별

SELECT userid, ROUND(AVG(rating), 2) AS average FROM movierating GROUP BY userid HAVING AVG(rating) > 4.68 ORDER BY average DESC; // 고객별

SELECT id, name FROM occupation;

// SELECT name FROM muser WHERE birthdate ='2020-10-20' // 날짜형에 대한 일치 검색

// SELECT name FROM muser WHERE birthdate BETWEEN '1980-01-01' AND '2000-12-31' // 날짜형에 대한 구간 검색

// JOIN Query 예제

• 상영된 영화의 년도별 상영 건수를 최신 년도 순으로 보여라

SELECT

year AS 상영연도,

count(*) AS 영화편수

FROM movie

GROUP BY year

ORDER BY year DESC;

• 년도별 상영된 영화를 최신 년도순으로 장르명 함께 보여라(영화기준)

SELECT

a.year AS 상영연도,

a.id AS 영화ID,

b.id AS 장르ID,

SUBSTR(a.name,1, 30) AS 영화제목,

b.name AS 영화분류

FROM movie a

LEFT JOIN (

SELECT c.movieid,

g.name,

g.id

FROM moviegenre c

LEFT JOIN genre g

ON c.genreid = g.id) b

ON a.id = b.movieid

ORDER BY a.year DESC, a.name ASC;

// movie를 기준으로 moviegenr

• 년도별 상영된 영화를 최신 년도순으로 장르명 함께 보여라(영화평가기준)

SELECT

a.year AS 상영연도,

a.id AS 영화ID,

b.id AS 장르ID,

SUBSTR(a.name,1, 30) AS 영화제목,

b.name AS 영화분류

FROM movie a

RIGHT JOIN (

SELECT

c.movieid,

g.name,

g.id

FROM moviegenre c

LEFT JOIN genre g

ON c.genreid =g.id) b

ON a.id = b.movieid

ORDER BY a.year DESC, a.name ASC;

// moviegenre를 기준으로 moviegenre에 RIGHT JOIN함. moviegenre에 movieid 3건이 movie에 없음

// postgresSQL은 null이 맨처음에 표시되나 MySQL은 맨끝 결과에 표시됨

SELECT *

FROM moviegenre

WHERE movieid NOT IN

(

SELECT id FROM movie

);

SELECT * FROM movie WHERE id = 2821; // movielens에 있는 영화 2821이 movie에 없음

MariaDB [movielens]> SELECT * FROM movie WHERE id = 2821;

Empty set (0.000 sec)

• 영화정보와 영화별 장르정보에 모두 포함된 레코드만 보여라.(아래 2개의 결과건수와 movie 기준 LEFT JOIN 건수가 동일)

SELECT *

FROM movie a

INNER JOIN moviegenre g

ON a.id = g.movieid;

SELECT *

FROM moviegenre g

INNER JOIN movie a

ON a.id = g.movieid;

• 상영된 영화를 년도별 장르별 건수를 년도와 장르순으로 보여라

SELECT

a.year AS 상영연도,

b.name AS 영화분류,

count(*) AS 상영건수

FROM movie a

LEFT JOIN (

SELECT c.movieid,

g.name

FROM moviegenre c

LEFT JOIN genre g

ON c.genreid = g.id) b

ON a.id = b.movieid

GROUP BY a.year, b.name

ORDER BY a.year DESC, b.name ASC;

• 고객들 명단을 직업과 함께 표시하라

SELECT

a.name AS 고객명,

a.gender AS 성별,

a.age AS 나이,

b.name AS 직업

FROM muser a

LEFT JOIN (

SELECT id,

name

FROM occupation) b

ON a.occupationid = b.id

ORDER BY a.name ASC, a.age ASC;

• 고객들의 연령별 인원수를 집계하라.

SELECT b.agerange AS 연령대,

LPAD(FORMAT(COUNT(*), 0), 8, ' ') AS 인원수

FROM (

SELECT

CASE

WHEN age < 20 THEN '20 이하'

WHEN age < 30 THEN '20~29'

WHEN age < 40 THEN '30~39'

WHEN age < 50 THEN '40~49'

WHEN age >= 50 THEN '50 이상'

END AS agerange

FROM muser) b

GROUP BY b.agerange

ORDER BY b.agerange ASC;

• 연령대별 평점 평균을 계산하라

SELECT

a.agerange AS 연령대,

LPAD(FORMAT(COUNT(*), 0), 8, ' ') AS 인원수,

ROUND(AVG(a.rating),2) AS 평점

FROM (

SELECT

CASE

WHEN d.age < 20 THEN '20 이하'

WHEN d.age < 30 THEN '20~29'

WHEN d.age < 40 THEN '30~39'

WHEN d.age < 50 THEN '40~49'

WHEN d.age >= 50 THEN '50 이상'

END AS agerange,

d.rating

FROM (

SELECT b.userid,

c.age,

c.name,

b.movieid,

b.rating

FROM movierating b

LEFT JOIN muser c

ON b.userid = c.id) d

) a

GROUP BY a.agerange

ORDER BY a.agerange ASC;

• 성별 평점 평균을 계산하라

SELECT

a. gender AS 성별,

LPAD(FORMAT(COUNT(*), 0), 8, ' ') AS 인원수,

ROUND(AVG(a.rating), 2) AS 평점

FROM (

SELECT

CASE d.gender

WHEN 'M' THEN '남성'

WHEN 'F' THEN '여성'

END AS gender,

d.rating

FROM (

SELECT b.userid,

c.gender,

b.movieid,

b.rating

FROM movierating b

LEFT JOIN muser c ON

b.userid = c.id) d

) a

GROUP BY a.gender

ORDER BY a.gender ASC;

• 영화별 평가가 100건 이상인 영화에 대한 평점 평균을 계산하고 평점이 높은 순 10건을 표시하라

SELECT

a.name AS 영화제목,

LPAD(FORMAT(COUNT(*), 0), 8, ' ') AS 평가수,

ROUND(AVG(a.rating),2) AS 평점

FROM (

SELECT b.movieid,

c.name,

b.rating

FROM movierating b

LEFT JOIN movie c ON

b.movieid = c.id) a

GROUP BY a.name

HAVING COUNT(*) > 100

ORDER BY AVG(a.rating) DESC LIMIT 10;